ADR-004: Forward-Only Database Migrations

Status: Accepted Date: 2026-05-18 Sprint: SPRINT_20260518_055_Upgrade_graceful_drain_and_forward_only_migrations.md

This record states the platform’s official position on database schema rollback: Stella Ops migrations are forward-only, and the supported recovery path for a bad schema-changing upgrade is a PostgreSQL snapshot restore — not a reverse migration. Read it before authoring a migration or planning an upgrade-rollback procedure. Operators should also read the Migration Recovery Runbook.

Context

Stella Ops services apply PostgreSQL schema changes at startup through AddStartupMigrations(...) and the shared migration runner in src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/MigrationRunner.cs. That runner is forward-only by construction:

The repository-wide startup migration requirement is documented in CLAUDE.md section 2.7. Manual postgres-init/ scripts are bootstrap fallbacks only; they are not the migration authority for running installations.

The upgrade-version-skew QA plan tracks this as TOPO-131. The honest recovery path for a bad schema-changing upgrade is a PostgreSQL snapshot restore, not a reverse migration.

Decision

Stella Ops database migrations are officially forward-only.

Operators MUST snapshot the PostgreSQL compose volume before every service upgrade that can apply database migrations. If a schema-changing upgrade must be rolled back, operators restore the pre-upgrade PostgreSQL snapshot and then start the earlier service binaries.

Schema-changing migrations MAY be additive-only, such as adding a table, column, or index. They MAY also be breaking when a release requires it. In both cases, the reverse path is the same: restore PostgreSQL from a pre-upgrade snapshot.

The executable recovery procedure is documented in Migration Recovery Runbook.

Addendum 2026-08-10 — reconciling the recorded checksum is not a downgrade

An applied migration’s recorded checksum MAY be rewritten, without re-running the migration, when the host has proved that the embedded SQL and the applied SQL are the same statements. Nothing else about the row changes, and no SQL executes.

This addendum exists because the whole-file checksum could not tell a comment edit from a schema edit. Two comment-only commits (cbec0b6db3, cebd0316b1) therefore made every Authority image built after 2026-07-30 refuse to start against every database holding the pre-edit content — found live on 2026-08-10, when the only available mitigation was pinning the deployed image. The prescribed remedy at the time was a hand-written UPDATE against every database in the estate before every image roll, which is not a control that holds.

Constraints on the reconciliation, all enforced in src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/:

  1. Proof, not assertion. Equivalence is established by comparing SQL that has had comments and inter-token whitespace removed by MigrationSqlNormalizer, against a normalized_checksum the applying host recorded. Literals and dollar-quoted bodies are compared verbatim; the normalizer refuses on ambiguity and a refusal fails closed.
  2. Fail-closed is unchanged for real changes. Any difference in executable SQL still calls StopApplication(), exactly as before.
  3. Auditable. Every reconciliation writes a row to <schema>.schema_migration_reconciliations recording the migration, module, reason, both checksums, the normalized checksum, a justification, the host and the time.
  4. Rows predating the mechanism need an owner-declared pin, which is honoured only when it matches the stored checksum exactly and the current file normalizes to the value the pin declares — so a pin binds one exact pair of contents and expires on use.

Forward-only is untouched: no migration is re-run, no migration file is edited, no schema is reverted, and a schema change is still a new migration file. The recovery path for a bad schema-changing upgrade remains a snapshot restore.

Consequences

Positive

Tradeoffs

Future Work

Any future downgrade-supporting feature is a new ADR. It must define the contract, tooling, test matrix, and operator evidence separately from this forward-only baseline.

Alternatives Considered

Down Migrations

Rejected. They double authoring cost, are hard to test against real production data, and often fail to reverse lossy changes. In this product, they would also conflict with the current startup migration runner.

Blue/Green PostgreSQL

Rejected for this baseline. Running two PostgreSQL clusters with bidirectional or cutover replication is out of scope for the self-hosted SMB target. Operators who need partial service rollback should use the Sprint 056 blue/green service runbook only when the database migration is additive-compatible.

References