PostgreSQL Schema Migrations

Rolling deploy safety

Apply expand-compatible migrations before deploying service versions that depend on new columns/tables. Destructive contract migrations and large data migrations are separate release steps. See Running Cluster Upgrades for the full upgrade sequence.

Overview

PostgreSQL and YugabyteDB schema changes are managed through embedded, versioned migrations:

Scenario	What to use
Fresh install	Bootstrap baseline schemas and expand migrations via frameworks cluster init or frameworks cluster provision
Upgrading	frameworks cluster migrate applies migrations from pkg/database/sql/migrations/
Release adds a database	frameworks cluster release apply or frameworks cluster migrate --phase expand creates it from its baseline

New service databases

A release can introduce a service that owns a new database (for example lookout). Declare the database in postgres.databases. Before the expand migrations, cluster migrate --phase expand (and therefore cluster release apply) probes every manifest database that has an embedded baseline:

• Absent database: created with the same owner role, runtime role, and grants that provisioning uses, then its current baseline is applied. The baseline writes the _schema_baseline marker, so the database starts at the current floor with no migration history.

• Database present but its schema has no tables: the roles are ensured and the baseline is applied. This resumes a bootstrap that stopped after creating the database.

• Database with tables and a _schema_baseline marker or migration history: already initialized. Nothing is applied; its migrations run as usual.

• Database with tables but no marker and no migration history: its provenance is ambiguous. It may be an interrupted YugabyteDB baseline, a manual restore, or an imported database. Release and migration commands refuse it before sending baseline SQL. Inspect the database first, then opt in only when completing it against the current baseline is appropriate:

  frameworks cluster migrate --phase expand --complete-interrupted-baselines
  # or, as part of the complete release:
  frameworks cluster release apply --complete-interrupted-baselines

  The opted-in completion verifies the result:

  1. It records a pending marker (baseline-verification-pending), so the database stays unverified if the run stops at any later point.
  2. It creates a temporary reference database on the same server, named <database>__baseline_check_<16 hex characters>, and drops any reference an earlier interrupted run left for that database.
  3. It applies the baseline again to the database, which creates only the missing objects, and applies the same baseline to the reference.
  4. It compares the service schema of both: tables, columns (type, nullability, default), constraints, indexes (including validity), triggers, functions, views, sequences, types, and row-security policies. Ownership and grants are not compared because the role reasserts them on every apply. It then drops the reference.
  • Identical: the pending marker becomes the baseline’s floor and the expand migrations continue.
  • Different: the release stops and prints every differing object with the expected (baseline) and actual definition, for example column lookout.incidents.note / expected: (absent) / actual: text. The database keeps its pending marker, so every later release refuses it the same way until its schema matches the baseline, and floor-dependent commands refuse it too.

The reference database is dropped whether the comparison passes, fails, or the apply itself fails. Re-applying a baseline runs its data statements again, including seed statements and backfills. The explicit flag is therefore required even when the database has no migration history.

After the bootstrap the CLI probes again and stops unless every database is initialized. Regional YugabyteDB aliases (such as foghorn_eu) are created per cell from their logical baseline. With --dry-run only read-only probes run: the output lists each database that would be created or completed and verified, no database, marker, or baseline SQL is sent, and the ledger checks skip those databases.

frameworks cluster upgrade <service> checks the service’s own database first and refuses with the command to run when it is missing, empty, or not yet verified.

Migration structure

Migrations are organized by target database, release version, and phase:

pkg/database/sql/migrations/
└── purser/
    └── v0.3.0/
        ├── expand/
        │   ├── 001_add_rating_tables.sql
        │   └── 002_add_rating_index.notx.sql
        ├── postdeploy/
        │   └── 001_require_rating_v2_complete.sql
        └── contract/
            └── 001_drop_legacy_rating_json.sql

The first path segment after migrations/ must match a configured database name, for example purser, quartermaster, or commodore. The CLI embeds these files, applies only migrations whose database exists in the manifest, and records applied files in that database’s _migrations table.

Release and recovery procedure

For a normal platform release, use the lifecycle command that owns migration and service ordering:

frameworks cluster release plan --version vX.Y.Z
frameworks cluster release apply --version vX.Y.Z --dry-run
frameworks cluster release apply --version vX.Y.Z --yes

It runs expand migrations, platform artifact upgrades, declared transitions, and postdeploy migrations in the required order. Contract migrations stay deferred until the rollback window closes.

Use the following lower-level flow only for migration development, diagnosis, or a release-specific recovery runbook:

1. Check release notes for required migrations
2. Confirm the phase. Routine rolling upgrades apply expand migrations. postdeploy migrations run after new binaries are live. contract migrations require the release’s explicit cleanup runbook.
3. Apply migrations through the CLI:

frameworks cluster migrate validate
frameworks cluster migrate --phase expand

4. Verify the changes:

frameworks cluster doctor

cluster doctor checks the _migrations ledger of every database against the target release and reports missing entries. To capture the ledger rows themselves, run frameworks cluster snapshot --skip-logs; the snapshot includes each database’s migration ledger.

5. Deploy a specifically diagnosed service version:

frameworks cluster upgrade quartermaster --version v1.1.0

6. Run required data migrations after compatible binaries are deployed. Catalog-declared service-owned background migrations are managed with frameworks cluster data-migrate. If a service has not adopted the data-migrations command surface, the CLI reports that as a blocker instead of treating the migration as complete.

Do not substitute cluster upgrade --all for cluster release apply: direct upgrade does not run declared release transitions and fails closed when one gates a downstream service.

Migration prechecks

Some migrations cannot succeed while certain rows exist, for example a unique index over data that still has duplicates. Such a migration can have a precheck: a read-only query that returns exactly the rows the migration would fail on. The PostgreSQL and YugabyteDB migrate roles apply pending migrations one at a time, and run an item’s precheck in a READ ONLY transaction immediately before that item, after every earlier item has applied.

When the precheck returns rows, the item is refused before its first statement and nothing of it runs. The failure names the migration and the precheck file, the total number of offending rows, and up to 50 of them as JSON:

quartermaster/v0.3.0/expand/005_node_identity_unique_indexes.notx.sql was not applied: its precheck
prechecks/quartermaster/v0.3.0/expand/005_node_identity_unique_indexes.notx.sql found 2 row(s) the
migration would fail on. Nothing of this item ran. Resolve these rows, then rerun the migration.
First 2: {"node_id":"...","cluster_id":"...","last_seen":"...","fingerprint_id":"...",...}; ...

Earlier items in the same run stay applied and recorded; the refused item and later items stay pending. Resolve the rows with the command the release’s upgrade notes name, then rerun the migration (cluster release apply or cluster migrate). Prechecks do not run under --dry-run, so a dry-run does not show a refusal in advance.

The v0.3.0 prechecks guard the Quartermaster node-fingerprint unique indexes. Once Quartermaster runs a release with the operator fingerprint RPCs, list and remove duplicate bindings with frameworks admin nodes fingerprints.

Writing migrations

Guidelines for contributors:

1. Use IF NOT EXISTS guards - migrations must be idempotent
2. Prefer expand-only changes - add columns/tables, avoid drops
3. Use defaults or NULL for new columns - safe for rollback
4. Update bootstrap schema - corresponding file in pkg/database/sql/schema/ must reflect final state
5. One migration per logical change - easier to track and debug
6. Put the file in the correct phase directory - expand, postdeploy, or contract
7. Do not hide large data rewrites inside schema migrations - use a resumable background data migration when row counts, locks, external calls, or billing semantics make the work operationally significant
8. Use .notx.sql only when PostgreSQL requires autocommit - for example CREATE INDEX CONCURRENTLY; keep the file idempotent because the SQL and _migrations insert cannot be one database transaction in that mode

Run the validator before opening a PR:

frameworks cluster migrate validate

Example migration file:

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'quartermaster') THEN
        CREATE TABLE IF NOT EXISTS quartermaster.bootstrap_tenant_aliases (
            alias       TEXT PRIMARY KEY,
            tenant_id   UUID NOT NULL REFERENCES quartermaster.tenants(id) ON DELETE CASCADE,
            created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
        );
    END IF;
END
$$;

Example layout:

pkg/database/sql/migrations/purser/v0.3.0/
├── expand/
│   ├── 001_add_billing_rating_tables.sql
│   └── 002_add_rating_lookup_index.notx.sql
├── postdeploy/
│   └── 001_require_billing_rating_v2_complete.sql
└── contract/
    └── 001_drop_legacy_billing_json.sql

Do not place files directly under pkg/database/sql/migrations/vX.Y.Z/. The runner treats that as an invalid layout and fails instead of silently ignoring the file.

Background Data Migrations

Schema migrations make the destination shape available. They do not guarantee existing rows already contain the new semantic data.

Use SQL data migrations only when the transform is small, deterministic, and safe to run inside the migration budget. Prefer service-owned background data migrations for large tables, tenant-by-tenant work, external lookups, billing/rating recalculation, or anything that must expose progress.

Background data migrations should be:

• idempotent - safe to re-run after partial completion
• batched and throttled - avoid long locks and transaction bloat
• observable - report scanned/changed/skipped/error counts
• resumable - persist progress or use deterministic checkpoints
• tenant-scoped - every query must preserve tenant_id boundaries
• verified - include a dry-run or check mode where practical

For billing migrations, do not rewrite finalized invoices unless the release is explicitly correcting historical data. Prefer effective-dated pricing history, new draft invoice recalculation, and immutable invoice snapshots for audit.

Local compose

The root Docker Compose stack is a seeded development environment. PostgreSQL loads baseline schema files and demo seeds only when the postgres_data volume is first created. For local development, it is acceptable to reset the seeded database instead of preserving data through every incompatible schema change:

docker compose down -v
docker compose up -d

Do not use that shortcut for operator-managed clusters. Deployed clusters still need migrations, background data migrations, and rollback-safe release sequencing.

Rollback

• Nullable columns are safe to leave - older service versions simply won’t populate them
• Don’t drop columns during rollback unless explicitly required
• Re-deploying an older service version is usually sufficient
• Do not depend on down migrations for normal rollback - rollback should usually mean redeploying the previous binary while the expanded schema stays in place
• Contract migrations end the normal rollback window - after old fields are dropped or constraints are narrowed, recovery usually requires a forward fix or database restore

Troubleshooting

“column does not exist” errors:

ERROR: relation "quartermaster.bootstrap_tenant_aliases" does not exist

Cause: Service deployed before migration was applied. Fix: Rerun frameworks cluster migrate --phase expand (migrations are idempotent), confirm the ledger with frameworks cluster doctor, then restart the service.

The ledger lists the migration but the object is missing:

Run frameworks cluster doctor --deep. Its read-only schema capability packs exercise the objects and grants each service needs against the live engine and name the ones that fail. Capture frameworks cluster snapshot --skip-logs with that output: a ledger row for an object that does not exist means the database was changed outside the migration path.