Proposal: add multi-schema support to PostKit DB workflows

[nipunappri]

Summary

PostKit currently manages a single PostgreSQL schema per project. This proposal adds support for multiple schemas in one PostKit project by orchestrating multiple single-schema pgschema runs, while preserving backward compatibility for existing single-schema setups.

Why

Projects often split database objects across schemas such as public, audit, and api. Today those projects must either flatten everything into one schema, manage some schemas outside PostKit, or split work across multiple PostKit projects.

Proposed approach

• Replace db.schema with ordered db.schemas[]
• Generate one desired-state file per schema
• Run pgschema plan --schema <name> once per schema
• Merge the per-schema outputs into one PostKit plan and migration flow
• Keep infra, grants, and seeds as shared concerns outside per-schema pgschema planning
• Track fingerprints per schema instead of one global fingerprint

Important upstream constraint

This proposal is intentionally based on what upstream pgschema supports today:

• pgschema operates on one schema per invocation
• Upstream multi-tenant guidance is to loop over schemas and create one plan per schema
• Cross-schema references are not reliably handled by the embedded planner alone
• .pgschemaignore is global, not per-schema

v1 support matrix

Mode	Support	Notes
Single schema	Supported	Existing behavior remains supported
Multiple isolated schemas	Supported	One pgschema plan run per schema
Multiple schemas with cross-schema references and external plan DB	Supported with caveats	Requires referenced schemas and extensions in the plan DB
Multiple schemas with cross-schema references and no external plan DB	Not supported in v1	Upstream planner limitation

Key design decisions

• Multi-schema support is a PostKit orchestration layer, not native all-schema planning
• path: "." remains only as legacy single-schema compatibility mode
• True multi-schema projects use one subdirectory per schema
• Apply ordering and planning visibility are treated separately
• Per-schema generated artifacts must be cleaned up explicitly

Implementation areas

• Config and type updates for schemas[]
• Per-schema schema generation
• Per-schema planning and merged plan output
• Per-schema fingerprint tracking in session state
• Per-schema artifact lifecycle and cleanup
• Updated docs and migration guidance

Open questions

1. Should missing schema directories fail fast in multi-schema mode?
2. Should PostKit detect likely cross-schema references and surface a targeted error?
3. Should runtime migration from schema to schemas[] remain in-memory only, or be written back to config?