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 AGENTS.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.

Addendum 2026-09-25 — owner-ruled exceptions and their register

An applied migration’s executable SQL MAY be edited only under an owner ruling that names the migration, the reason, and the one accepted edited form. The rule above (“no migration file is edited”) has exactly the exceptions listed in the register below, and each entry must carry all of the following:

  1. Why forward-only could not fix it. The defect sits in a migration that fresh databases must still run, so no later migration can repair it.
  2. Equivalence where the file already ran. Estates that applied the original do not re-run it. The edit must produce the same effect as the original on every database state the original could have met; any other behaviour is a new migration.
  3. A ruled-edit pin. A MigrationChecksumReconciliationPin with RuledEditNormalizedChecksum and Ruling set, registered on every host that runs the set. It matches the stored row by the historical whole-file checksum or the recorded normalized checksum. It reconciles only when the shipped file normalizes to the ruled form, audited as ruled-edit-reconciliation, and expires on use. Any further edit fails closed.
  4. Proof. The historical file is kept as a test fixture. Tests prove that a fresh database converges, that a database holding the original reconciles without re-running it, and that the same database fails closed without the pin.

ADR-004 exception register

RulingMigrationReasonAccepted editReconciliation proof
Owner ruling 2026-09-25, SPRINT_20260925_001 CIC-2, option (a)src/Notify/__Libraries/StellaOps.Notify.Persistence.Consolidated/Migrations/008_notify_retention_mechanisms.sql, applied as f736019e17 (checksum b6495c1d…, normalized 5ab1b446…)Its unkeyed UPDATE notify.escalation_states targets a FORCE RLS table whose policy raises without app.tenant_id. Every fresh database failed there under the NOBYPASSRLS runtime role, even with no row, so notify-web could not start from an empty install.The UPDATE runs inside a DO block only when pg_relation_size('notify.escalation_states') > 0 (normalized 45b96793…). A 0-byte heap holds no row, so skipping it changes nothing; a populated table runs the UPDATE exactly as shipped.NotifyMigrationChecksumPins; NotifyMigration008RuledEditTests (fresh NOBYPASSRLS convergence; reconciled estate not re-run; unpinned estate fails closed; populated pre-008 database still backfilled); StartupMigrationHostTests ruled-edit cases; StartupMigrationForceRlsDmlConformanceTests keeps the tuple pinned by name.
Owner ruling 2026-09-27, SPRINT_20260927_003 PCD-64, option Asrc/Findings/__Libraries/StellaOps.Findings.Persistence/Migrations/001_findings_consolidated_baseline.sql, last shipped as f7bbc06d8f (checksum f768ba57…, normalized 0d657baf…; every shipped version from 4383338052 on normalizes to that value)It created findings_ledger_admin when absent and then ran COMMENT ON ROLE findings_ledger_admin. Both need CREATEROLE (the comment also ADMIN OPTION on the role), which the NOCREATEROLE Findings owner does not hold, so every fresh stellaops_findings failed inside 001 with 42501 (measured on PostgreSQL 18.1 by PCD-8 and PCD-38). The only other fix was a permanently widened owner (CREATEROLE plus ADMIN OPTION), which the ruling rejected.Both statements removed (normalized f22e5bbe…). The install path creates the role as the superuser and the Findings host asserts it before any migration (FindingsDatabaseRoles), so wherever the edited file can run the removed CREATE ROLE was a no-op. The role comment is not set on fresh installs; nothing reads it.FindingsMigrationChecksumPins; FindingsMigration001RuledEditTests (pin bound to the historical fixture; reconciled estate not re-run; unpinned estate fails closed); FindingsLedgerAdminRoleColdStartTests (fresh NOCREATEROLE owner converges; absent role fails with the named preflight).

Where the reconciliation record lands: a database that applied the original 008 keeps its notify.schema_migrations row. The first start of a host carrying the edited file rewrites only that row’s checksum and appends one row to notify.schema_migration_reconciliations with reason ruled-edit-reconciliation (MigrationChecksumReconciliation.RuledEdit). The historical file is kept as src/Notify/__Tests/StellaOps.Notify.Persistence.Consolidated.Tests/Fixtures/008_notify_retention_mechanisms.f736019e17.sql.txt. The edit landed in 59ab3b0a31.

Currency note (2026-09-26, verified against 942a18727c). The register holds exactly one entry. Re-verify: rg -n "RuledEditNormalizedChecksum\s*=" src --glob "*.cs" --glob "!**/__Tests/**" returns only NotifyMigrationChecksumPins.cs, and the architecture conformance pack (StartupMigrationForceRlsDmlConformanceTests) stays green. A second ruled edit adds a row here in the same change as its pin.

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