gnomonlearn
Docs/PostgreSQL/RFC-0054

RFC-0054 · DDL Emission Pipeline

Versioned draft snapshot

RFCs, reference catalogs, and evidence reflect Spec 3b2e3e7a114fb826b1445d39a48756b8fff629cd. Counts and pending/status notes belong to that snapshot. Read the dated project update for later progress.

Source repositories are private during staging. Links marked “repository access required” need access to the linked repository.

Status Draft corpus. Reference catalogs and RDF projections have generation drift checks; Gnomon proofs and projection semantics remain unverified for release.

View the generated reference catalog →

Read design questions and decisions →


Reference catalog: the diagnostics, linked-RFC graph, defined terms, and normative-rule index for this RFC are in the reference catalog, generated from the structured Lean module. This document carries the prose: design rationale, entity-shape definitions in context, and migration notes.


1. Purpose

The DDL emission pipeline is the codegen step that transforms gnomon declarations into SQL DDL statements. It is what makes Gnomon’s “no migration files” promise (KP27) operational: developers edit .gnm modules, the compiler emits a deterministic install plan of CREATE/ALTER/DROP statements, and the runtime applies them transactionally.

This RFC specifies:

  • The build-time codegen step that produces SQL DDL artifacts from db.* modules (table/function/index/policy/trigger creates) and from application modules (view-mapping outputs).
  • The install-plan computation: diffing declared state against installed state to produce ordered DDL operations.
  • The bootstrap.sql generation contract — the committed lockfile artifact.
  • Round-trip determinism: fingerprint(generated) === fingerprint(existing) as a regression oracle.
  • The destructive-operation gate (KP26).
  • The migration-free schema model: db/migrations/* as a derived artifact, never hand-authored.

The pipeline is the bridge that lets Gnomon modules be the source of truth for the PostgreSQL schema. After this RFC lands, the legacy db/migrations/000_initial_schema/*.sql files become a generated output, not a hand-maintained set.


2. Definitions

TermMeaning
DDL emissionThe compile-time step that transforms gnomon declarations into SQL DDL statements.
Install planThe set of ordered DDL operations needed to bring an installed schema into agreement with declared state.
Declared stateThe schema implied by the compiled module bundle.
Installed stateThe schema actually present in the target PostgreSQL database (or a snapshot thereof).
Plan diffThe deterministic comparison: declared - installed = create/alter operations; installed - declared = drop operations.
Bootstrap lockfileThe committed bootstrap.sql artifact regenerated by this pipeline from db.graph + db.ids + db.audit.
Round-trip fingerprintSHA-256 of canonical-sorted SQL output; used to verify generation determinism and parity with an existing schema.
Destructive operationA DDL statement that destroys data: DROP TABLE, DROP COLUMN, ALTER TYPE with non-trivial cast, TRUNCATE.

3. Pipeline Phases

The DDL emission pipeline runs as part of the module compile pipeline (RFC-0040 §11) and the install pipeline (RFC-0041 §3). It has three stages:

Stage 1 — Codegen (build time)
  Input:  compiled module bundle (RFC-0002)
  Output: per-module SQL artifacts in the bundle's DDL section

Stage 2 — Plan computation (compile time or pre-deploy)
  Input:  declared SQL (Stage 1 output) + installed SQL (snapshot or live DB)
  Output: ordered install plan with create/alter/drop ops

Stage 3 — Apply (deploy time)
  Input:  install plan + target database
  Output: applied schema; new installed-state snapshot

Stage 1 runs in CI and at local build. Stage 2 may run offline (against a snapshot — KP27) or online (against a live DB). Stage 3 runs only at deploy.

3.1 Stage 1: Codegen

For each module’s compiled bundle, the codegen pass emits SQL artifacts grouped by category:

CategorySource declarationsOutput
Schemasmodule namespaceCREATE SCHEMA <name> AUTHORIZATION <role>
Rolesdb-role declarations (RFC-0040 §5.1)CREATE ROLE, GRANT membership
Typesdb.pg.enum, db.pg.composite, db.pg.domain declarationsCREATE TYPE, CREATE DOMAIN
Tablesdb.pg.table declarations + composed db.pg.aspect columnsCREATE TABLE, CREATE INDEX
Functionsdb.pg.function declarations + .sql body assets (KP25)CREATE OR REPLACE FUNCTION
Triggersdb.pg.trigger declarationsCREATE TRIGGER
Policiespredicate_policy declarations + on-write aspects (RFC-0042 emission)CREATE POLICY
Viewsentity-type declarations (RFC-0053)CREATE OR REPLACE VIEW
Indexesview-mapping indexes (RFC-0053 §4) + explicit db.pg.indexCREATE INDEX
Extensionsdb-extension declarationsCREATE EXTENSION IF NOT EXISTS
Grantsderived from db-role + capability scopeGRANT ... ON ... TO ...

Each SQL statement is canonicalized: keywords uppercased, identifiers lowercased, whitespace normalized, comment metadata stripped, statement-terminator added.

3.2 Stage 2: Plan computation

The planner takes two inputs:

  • Declared SQL — the compile output (Stage 1).
  • Installed SQL — either a snapshot artifact from the previous deploy or a live pg_dump --schema-only of the target DB.

It produces an install plan: a DAG of InstallPlanEntry records (RFC-0005 / RFC-0041 §6.2) where each entry is one DDL operation.

The diff algorithm:

  1. Object catalog: build a map (category, qualified-name) → canonical-sql for both declared and installed.
  2. Create: declared entries not in installed → CREATE op (bcRisk: BC_SAFE typically; BC_BREAKING if constrains).
  3. Alter: declared entries in installed with different canonical SQL → ALTER op (or DROP+CREATE if alter is unsafe).
  4. Drop: installed entries not in declared → DROP op (bcRisk: BC_BREAKING).
  5. Topological sort: dependencies (FKs, view-on-table, function-on-type) determine order.
  6. Destructive flagging: each DROP / unsafe ALTER is flagged for the operator-acknowledgment gate (§5).

3.3 Stage 3: Apply

The install engine (RFC-0041 §3) executes the install plan in a transaction. Per-module install transactions ensure atomicity (RFC-0041 §6.1). On failure, the transaction rolls back; the operator sees the exact statement that failed.

Apply is identical for “fresh DB” (declared state populated empty installed state) and “incremental” (declared state evolves an existing installed state). The only difference is the size of the diff.


4. Determinism

4.1 Canonical SQL form

Every emitted SQL statement is canonical (RFC-0002 §Determinism):

  • Keywords uppercase, identifiers lowercase
  • Statement terminators: single ;
  • Whitespace: single space between tokens; line breaks at clause boundaries (FROM, WHERE, JOIN)
  • Identifier quoting: only when reserved or case-sensitive
  • Object property/column ordering: lexicographic by name
  • Comments: stripped from the canonical form (preserved as separate metadata for tooling)

The canonical form is bytewise deterministic. Two compilations of the same module on different machines, in different timezones, with different filesystem orderings produce byte-identical SQL.

4.2 Round-trip fingerprint

For every SQL artifact, the pipeline computes a SHA-256 fingerprint. The full set of fingerprints constitutes the bundle’s ddl-fingerprint (added to RFC-0002’s bundle metadata).

The round-trip oracle (Track D step D5):

fingerprint(generated_from_gnomon) === fingerprint(existing_legacy_sql_canonicalized)

When this holds for the legacy migration files, the pipeline is provably faithful — the legacy SQL can be retired.

4.3 Reproducibility

Two independent CI runs of the same module bundle MUST produce identical fingerprints. Divergence is a determinism violation (RFC-0002 §Reproducibility) and MUST be reported with one of the failure-taxonomy codes (M0150–M0154).


5. Destructive Operation Gate (KP26)

5.1 Default-deny

The install plan refuses to execute destructive operations unless:

  • The module declares allow-destructive: true for the affected object, OR
  • The plan is invoked with --allow-destructive, OR
  • The change is part of a two-phase deprecation lifecycle (§5.3).

A plan containing destructive operations without explicit acknowledgment MUST exit with non-zero status and emit M54010.

5.2 What counts as destructive

OperationDestructive?Why
DROP TABLEAlwaysIrrecoverable data loss
DROP COLUMNAlwaysField-level data loss
ALTER COLUMN ... TYPE (non-trivial cast)SometimesLossy cast (e.g., TEXT → INTEGER); safe casts (TEXT → VARCHAR(N) widening) are non-destructive
DROP INDEXNeverPerformance impact only
DROP VIEWNever (data is in underlying tables)Re-creatable from declarations
DROP MATERIALIZED VIEWSometimesCached data discarded; re-computable but expensive
DROP POLICYSometimesAuthorization regression possible
DROP FUNCTIONSometimesCaller breakage
TRUNCATEAlwaysData loss by definition
DROP SCHEMA CASCADEAlwaysMass deletion
REVOKESometimesCapability removal

The compiler emits a destructive-operation report alongside every plan with M54011 (info per operation) listing each potentially destructive op with severity.

5.3 Two-phase deprecation

Destructive ops on user-facing objects (entity-types, public APIs) follow a two-phase deprecation lifecycle:

  1. Phase 1: mark the object deprecated: true (RFC-0035 core.lifecycle.Deprecated). The object physically exists; views stop projecting it; reads still work.
  2. Phase 2: after N deploy cycles with no references, the object is physically removed.

CI tracks deprecation timing; M54020 fires when a deprecated object is removed before its grace period.

5.4 CI gate

CI MUST reject plans containing unacknowledged destructive ops. PRs that intentionally include them MUST be tagged (destructive-op: true PR label) and reviewed by an explicit signoff.


6. Bootstrap.sql Generation

6.1 Inputs

bootstrap.sql is generated from a fixed set of platform modules’ compiled bundles:

  • db.pg (extensions, base types, role declarations)
  • db.ids (ID generators)
  • db.audit (audit framework)
  • db.graph (the parts marked bootstrap: true)
  • core (the bootstrap kind-definitions and the system installer principal record)

6.2 Algorithm

1. Collect all declarations from the bootstrap modules tagged `bootstrap: true`.
2. Run Stage 1 codegen on the collected declarations.
3. Concatenate in dependency order:
   a. Extensions
   b. Schemas + owner roles
   c. Domain and base types
   d. Tables (with column-aspect composition)
   e. Functions (signatures + .sql bodies inlined)
   f. Triggers
   g. RLS policies
   h. Initial seed rows (reserved namespaces, system installer principal, bootstrap predicates)
4. Emit bootstrap.sql with header containing format version + source modules + content hash.

6.3 Lockfile semantics

bootstrap.sql is committed to the repository. CI:

  1. Regenerates bootstrap.sql from the current source modules.
  2. Computes its fingerprint.
  3. Compares against the committed file’s fingerprint.
  4. Fails on mismatch (M51040 from RFC-0051 §13).

PR review sees the SQL diff explicitly when a graph-substrate change is made. This is the same lockfile pattern as Cargo.lock or pnpm-lock.yaml.

6.4 Tagging declarations as bootstrap

Within db.graph, only a subset of declarations are bootstrapped (Phase 1). The rest are installed by the normal pipeline (Phase 2). The bootstrap: true annotation marks Phase-1 declarations:

table resource {
  type: db.pg.table
  with:
    aspects: [graph-resource-identity, timestamps, soft-delete, audited-creator, data-jsonb]
    bootstrap: true             # this table is in bootstrap.sql
}

Functions, indexes, and triggers can be similarly tagged. Most platform-module declarations leave bootstrap unset — they’re Phase-2 declarations that the install pipeline applies after first boot.


7. Module SQL Artifact Layout

Each module’s compiled bundle includes a structured SQL artifact section:

bundle/
├── manifest.json
├── declarations/
│   ├── entities.json
│   ├── operations.json
│   └── ...
├── ddl/
│   ├── schemas.sql           # CREATE SCHEMA, CREATE ROLE
│   ├── extensions.sql        # CREATE EXTENSION
│   ├── types.sql             # CREATE TYPE, CREATE DOMAIN
│   ├── tables.sql            # CREATE TABLE + indexes
│   ├── functions.sql         # CREATE FUNCTION + bodies
│   ├── triggers.sql          # CREATE TRIGGER
│   ├── policies.sql          # CREATE POLICY (RLS)
│   ├── views.sql             # CREATE VIEW (entity-type views)
│   ├── grants.sql            # GRANT statements
│   └── seed.sql              # INSERT for bootstrap rows
├── ddl-fingerprint.json      # per-file SHA-256 + composite fingerprint
└── view-mapping.json         # Gnomon-name ↔ PG-identifier mapping (RFC-0053 §3.3)

Files are emitted in dependency order; the install plan can apply them sequentially or per-statement.

7.1 Per-statement vs per-file granularity

Stage 2 plan computation operates at per-statement granularity for diff fidelity. Stage 3 apply MAY apply per-file (atomic per-file transactions) or per-statement (atomic per-statement, less common).

The trade-off:

  • Per-file: simpler, but a failure in one statement rolls back the whole file.
  • Per-statement: finer rollback, more transactions, slower.

The default is per-file with batched statement application within the file. Operators can override per-module via configuration.


8. Migration-Free Schema Model (KP27)

8.1 The promise

Gnomon eliminates db/migrations/ directories. There are no hand-authored sequential migration scripts. The install plan IS the migration, computed on demand.

8.2 Developer workflow

1. Edit a `.gnm` module to add a field, table, or function.
2. `pnpm gnomon compile` runs Stage 1 codegen.
3. `pnpm gnomon plan --dry-run` runs Stage 2 against a snapshot, shows the diff.
4. PR is reviewed; CI Stage 1 + Stage 2 reproduce the same plan.
5. Deploy runs Stage 3 against the live DB.
6. Plan + applied DDL is recorded in audit (RFC-0047) for future diffing.

No migration files written. No numbering. No merge conflicts on migration sequences. The PR’s diff is the .gnm change; the plan’s DDL is shown in CI output for review.

8.3 Emergency rollback

Rollback is supported via the install plan’s reverse mode (RFC-0041 §12) or via snapshot-based rewind:

pnpm gnomon schema snapshot          # capture current state
pnpm gnomon schema plan --target <previous-snapshot>   # produce rollback DDL
pnpm gnomon schema apply --plan rollback.json

The snapshot is a committed compiled-bundle artifact. Rollback produces a plan that takes installed state from current to previous.


9. Cross-Module Dependencies in DDL

Some DDL statements span modules: e.g., a db.audit.install_audit_trigger(...) call on a db.graph.resource table. The pipeline handles this via:

  • Static dependencies: declared in module.gnm’s modules: list. Install order respects them (RFC-0041 §4).
  • Cross-module function calls: the function’s CREATE statement appears in the declaring module; calls from other modules’ DDL reference the qualified function name. Install order ensures the function exists before any caller.
  • View dependencies: an entity-type view depends on graph.resource. View creation orders after table creation (RFC-0052 §6).

Cross-module FK constraints SHOULD be avoided (RFC-0053 §8.1 — relations belong in graph.statement); when used, they require strict install ordering.


10. Invariants

  1. Stage 1 codegen output is bytewise deterministic for identical inputs (§4.1).
  2. The round-trip fingerprint test passes for the legacy schema after migration (§4.2, RFC-0051 §14.1).
  3. Destructive operations require explicit acknowledgment (§5.1, M54010).
  4. bootstrap.sql is regenerated by CI and verified by diff (§6.3, M51040 from RFC-0051).
  5. The migration-free model means no db/migrations/ files in source — only .gnm modules and the generated bootstrap.sql lockfile (§8.1).
  6. Per-module install transactions are atomic (§3.3, RFC-0041 §6.1).
  7. Cross-module DDL ordering respects manifest modules: dependencies (§9, RFC-0041 §4).
  8. The compiled bundle includes a ddl/ section with per-category SQL files and a ddl-fingerprint.json (§7).
  9. View-mapping outputs (RFC-0053) are part of the DDL emission — entity-type changes flow through the same pipeline (§3.1 view category).
  10. The destructive-op two-phase deprecation lifecycle is the recommended path for removing user-facing objects (§5.3).

11. Compatibility and Migration

11.1 From legacy db/migrations/

Migration steps:

  1. Run the existing reverse mapper (packages/sql-ast-analyzer/) on db/migrations/000_initial_schema/*.sql → produces 8 .gnm modules.
  2. Validate via round-trip fingerprint (§4.2): fingerprint(emit_from_modules) === fingerprint(canonicalize(legacy_sql)). Track D step D5.
  3. Once the round-trip is green, retire the migration files. The compiled modules become the source of truth.
  4. Future schema changes flow through .gnm edits, not new SQL files.

The legacy reverse mapper has 92.5% derivability (sql-ast-track-findings.md §2). The remaining 7.5% (imperative SET/DO/DROP statements) are operational scripts that don’t fit the declarative model and remain as one-off operator commands invoked manually.

11.2 Existing deployments

Existing PostgreSQL deployments continue to operate unchanged. The pipeline runs incremental plans against the installed state; small deltas are applied; the legacy schema satisfies the new declarations.

11.3 Round-trip validation in CI

CI MUST run the round-trip test:

generated = ddl-emit(modules)
legacy    = canonicalize(read("db/migrations/000_initial_schema/*.sql"))
assert fingerprint(generated) === fingerprint(legacy)   # OR diff-only-allowed

A diff is allowed during the migration window (Track D phase B); after retirement of migration files, the assertion becomes strict.


Diagnostics

The diagnostic catalog for this RFC is authored in the structured Lean module and rendered in reference catalog § Diagnostics. Inline mentions in the prose above (e.g., “rejected with MNNxxx”) cross-link to the catalog at render time.

Linked References

The cross-RFC dependency graph is rendered in reference catalog § Linked References. Out-of-KG pointers to legacy code or external resources, when present, remain in this document under “Source-tree references” below.