0038. Expand/contract migrations

• Status: accepted
• Date: 2026-10-28

Context

The strict runtime revision check in ADR 0011 refused every newer journal entry. A candidate build that committed additive SQL but was not promoted, a failed migration batch, or Vercel Instant Rollback could therefore make the serving release fail on cold starts. The migration journal is append-only and each SQL file’s checksum is immutable; applied files cannot acquire an in-file marker.

Decision

• Every SQL migration has one expand or contract declaration in packages/db/migrations/kinds.json. All migrations through 0019 are contract. The generated runtime revision list contains the declared kind. A missing or orphan declaration fails the migration test and generation command. CI compares declarations with the merge base and refuses changes to existing keys.
• expand promises that the previous runtime can continue to read and write after the SQL and deployment grants are applied. contract requires the new runtime. The runner creates the journal if absent, adds its kind column if absent, then writes each migration’s kind in the same transaction as its SQL and journal entry. Existing journal rows become contract. The old runner selects version, name, checksum explicitly, so the added column does not break that query.
• A runtime verifies its own applied migrations by version, checksum and kind as a prefix. It accepts only a contiguous suffix of expand entries, and logs their version and name. It rejects an unknown contract, a gap, a changed checksum or kind, and a database behind its code. The migration command still refuses all unknown applied entries before it reapplies grants or jobs: running an older deployment command is not a rollback procedure.
• The migrations:lint gate uses pgsql-parser 17.9.17, the maintained MIT-licensed PostgreSQL 17 parser backed by libpg_query. It fails closed on unknown SQL statement shapes. It permits new tables, empty schemas, sequences, enum types and views (without replacement); comments; simple inserts into tables created in the same migration; new schema-qualified functions only when the name is unused and the exact signature is revoked from PUBLIC in the same file; SET lock_timeout to a constant on an existing function, whose previous callers then answer as before or end sooner with SQLSTATE 55P03; and policies on newly created tables. A foreign key reachable from an existing table’s delete must cascade through new tables at every edge, or terminate at a nullable, unchecked, non-key SET NULL column. NO ACTION and RESTRICT on that path require contract. Cascades into existing tables require contract, because they could silently delete old-runtime rows. Cascades into new tables are allowed, but after rollback an old-runtime delete silently deletes rows that the new runtime wrote. If a feature needs restriction semantics, application code must enforce them during the expand window; the old runtime cannot do so. Foreign-key targets must use keys the previous runtime treats as immutable because the default ON UPDATE NO ACTION can block its updates. On existing tables, indexes use plain columns without a predicate, and added-column defaults must be non-null constants (optionally cast); integer columns need integer literals. A new column’s CHECK may compare that column with a constant only when every default, including a later SET DEFAULT, is proven to satisfy the comparison. SET DEFAULT is permitted only on a column added in the same migration. Drops, renames, changes to existing columns or RLS policies, and other unreviewed operations are refused. Domain-typed added columns can conceal NOT NULL or CHECK constraints, so authors must review the domain’s effect on old inserts. This syntactic policy cannot prove semantic compatibility; the PostgreSQL semantic matrix replays representative old writes, while a future CI job should run the previous release’s runtime against the PR schema once releases exist.
• An expand migration runs with SET LOCAL lock_timeout = '5s' per transaction. A non-concurrent index build on an existing table takes a SHARE lock and blocks writes throughout the build; authors must plan for that duration. A plain index on an unbounded text or jsonb column can also reject a large old-runtime write when an index entry exceeds PostgreSQL’s size limit. The timeout limits waiting to acquire locks, not time spent holding one.
• applyGrants resets role privileges on every deploy. grant-floor.json records table, column, function and schema privileges needed by the previous release. CI refuses a floor removal or a removal from the merge-base inventory unless the same diff adds a contract declaration. A new contract closes the rollback window, so an unrelated contract declaration also permits the removal. pnpm --filter @guestcraft/db migrations:grant-floor regenerates the floor from the inventory in the release-please PR. The first release has not happened yet; review that PR’s generated floor before merging it.

Rollout and recovery

There is no bootstrap migration. The first runtime with this compatibility check can survive later expand-only migrations. A runtime built before this change still uses a strict equality check and cannot cold-start after any unknown migration commits. Deploy that first compatible runtime with the same roll-forward readiness as earlier releases; its old SQL selector reads the extended journal, but its strict equality rule cannot be changed retroactively.

For a breaking schema change, first release additive expand SQL while both old and new code can run. In a later release, switch the application to the new objects while retaining the old ones. Only after every runtime that needs the old objects is drained, ship a contract migration to remove them. A contract migration closes the rollback window: an old runtime refuses to start and recovery is roll-forward or a separately evaluated backup restore. Never edit an applied SQL file or journal row. Failed batches retain earlier committed migrations; a previous runtime stays available only if every extra committed entry is expand and the SQL/grants actually honor the promise.

Consequences

The runtime accepts a newer additive schema through PgBouncer’s transaction pool. It logs tolerated entries without SQL or data values. The migration owner still needs a direct or session-pooled connection and its advisory lock. New migrations must be classified and linted before release. SQL parser allowlisting is intentionally narrower than all possible safe PostgreSQL changes; a migration that needs a different shape requires a reviewed linter extension or a contract rollout.