Migration Recovery Runbook

Sprint: SPRINT_20260518_055_Upgrade_graceful_drain_and_forward_only_migrations Task: A7 - Document forward-only migration recovery

Overview

Stella Ops database migrations are forward-only. Services apply embedded SQL migrations at startup and record them in each schema’s schema_migrations table (columns: migration_name, category, checksum, applied_at, applied_by, duration_ms). There is no supported down-migration path.

The recorded checksum is re-validated against the embedded SQL on every startup so an accidental edit to an already-applied migration is caught (see Checksum Validation and Line-Ending Self-Heal). Since 46de746725 (StellaOps.Infrastructure.Postgres), the checksum is computed over LF-normalized SQL and a stored legacy checksum that is only the un-normalized (CRLF) hash of the same SQL self-heals at startup instead of faulting the service. Genuine content drift still fails closed. This removed the most common reason operators previously had to hand-edit schema_migrations or restore a snapshot.

Startup only auto-applies startup-category migrations (and seed-category migrations when the owning module opts in via RunSeedMigrations, e.g. Authority with AUTHORITY_BOOTSTRAP_ENABLED=true). Migration category is derived from the filename: 001-099 = startup, 100+ = release, S### = seed, DM### = data. As a safety override, any filename containing rollback (case-insensitive) is classified as release regardless of its numeric prefix (so e.g. 007_enable_rls_rollback.sql is treated as a release migration, not an auto-applied startup migration).

Release and Data categories must not exist (since 2026-09-14, SPRINT_20260722_021 PLT-4, DC-26). The central migrator and its stella system migrations-run | migrations-status | migrations-verify CLI were deleted on the precondition that no such file exists anywhere under src/**/Migrations/*.sql; NoManualCategoryMigrationsTests guards the tree. There is no runtime applier for those bands. If one appears, the owning host fails closed with:

Pending release migration(s) for <Module> have no runtime applier since 2026-09-14: renumber the file into the Startup band (001-099) so the owning host applies it, or apply it by hand per docs/runbooks/database/migration-recovery.md and record the ledger row.

The procedure for both remediations is Pending Release/Data-band Migration. Every service converges its own database from its own embedded Startup-band SQL at host start (one service = one database + own role, ADR-039); there is no manual or disaster-recovery applier any more.

Rollback from a bad schema-changing upgrade means restoring the PostgreSQL compose volume from a pre-upgrade snapshot and then starting the earlier service binaries. This restores every service that shares the PostgreSQL instance to the same point in time.

Policy reference: ADR-004: Forward-Only Database Migrations.

Quick Reference

Snapshot Before Upgrade

Run the snapshot before starting any service binary that can apply migrations. Use the canonical compose volume name compose_postgres-data.

Snapshot file naming convention:

pg-snapshot-pre-<service>-<yyyyMMddTHHmmssZ>-<targetGitSha>.tar.gz

Example:

svc=evidence-locker
target_git_sha=$(git rev-parse --short=12 HEAD)
timestamp=$(date -u +%Y%m%dT%H%M%SZ)
snapshot="pg-snapshot-pre-${svc}-${timestamp}-${target_git_sha}.tar.gz"

docker compose stop postgres
docker run --rm \
  -v compose_postgres-data:/data \
  -v "$(pwd):/out" \
  alpine tar czf "/out/${snapshot}" -C /data .
docker compose start postgres

Verify the snapshot exists before continuing:

ls -lh "pg-snapshot-pre-${svc}-"*.tar.gz

Post-Upgrade Verification

After the upgraded service starts, verify the expected schema migration rows. Each schema owns its own schema_migrations table. The host validates every applied checksum and applies pending Startup-band migrations at boot, so its log is the first check:

docker compose logs --since 5m <svc> | grep 'Migration:'
# expected: "Migration: Database is up to date for <Module>." or the applied files;
# a "Checksum mismatch" or "Pending manual-category migrations" line means the host stopped

The service’s own doctor check (db.migration-status, GET /doctor/<service>/checks) reports the same state. (The former stella system migrations-status | migrations-verify commands were deleted 2026-09-14, SPRINT_20260722_021 PLT-4.)

To inspect rows directly, query the schema’s schema_migrations table. Example for the evidence schema:

docker compose exec postgres psql -U stellaops -d stellaops \
  -c "select migration_name, category, applied_at from evidence.schema_migrations order by applied_at desc limit 10;"

For another schema, replace evidence.schema_migrations with <schema>.schema_migrations.

Also verify the service route is healthy through the gateway before declaring the upgrade complete:

docker compose ps <svc>
docker compose logs --since 2m <svc>

Checksum Validation, Line-Ending Self-Heal and Comment-Only Reconciliation

The startup migration host (src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/StartupMigrationHost.cs) records a SHA-256 checksum for each applied migration and re-validates it on every boot. This is the forward-only integrity guard: a migration that has already been applied must never change, so a mismatch normally means the embedded SQL was edited after the fact (ADR-004). The default posture is fail-closed (StartupMigrationOptions.FailOnChecksumMismatch = true): a genuine mismatch calls IHostApplicationLifetime.StopApplication() and the service refuses to start.

What changed (durable line-ending fix, 46de746725)

Before this change the checksum was a hash of the raw embedded-resource bytes. A clone with Windows CRLF line endings produced a different hash than a clone with LF endings for byte-identical SQL, so a service rebuilt from a different-line-ending checkout could fail closed on a non-drift. The canonical recovery for that false positive used to be manual: hand-edit the stored checksum, or restore a snapshot.

That class of incident no longer needs operator intervention. The checksum is now computed by MigrationChecksum.Compute(...), which normalizes all line endings to \n before hashing, so the same logical SQL hashes identically regardless of how it was checked out. On startup, for each already-applied migration the host (StartupMigrationHost.ValidateChecksumsAsync):

1. If the stored checksum equals the normalized checksum — the row is already canonical; it is left untouched.
2. Otherwise, if MigrationChecksum.IsSelfHealableLegacyChecksum(...) matches — i.e. the stored value is the raw, un-normalized hash of the same SQL under a different line-ending rendering (a pre-normalization host build, or a CRLF checkout that recorded the row) — the host self-heals: it re-records the normalized checksum with an UPDATE ... WHERE migration_name = @name issued inside the migration advisory lock (so there is no multi-instance race), logs a Reconciled line-ending checksum drift warning, and startup proceeds.
3. Otherwise it is genuine content drift: the host records a Checksum mismatch error and fails closed (default), exactly as before.

ADR-004 forward-only is preserved: nothing is downgraded, no migration SQL is re-run, no migration file is edited, and migration 004 (the stellaops_authority schema migration that originally surfaced the drift) was left untouched. The self-heal only reconciles the recorded hash of an already-applied migration to its line-ending-normalized form. The fix lives in the shared AddStartupMigrations(...) path, so it covers every service on that path (CoC §2.7), not just Authority — and, since 2026-08-11, the three services that run their own bespoke SQL migration runner carry the same contract too (see Bespoke runners carry the same contract).

What changed (comment-only reconciliation, 2026-08-10)

A checksum over the whole file cannot tell a comment edit from a DDL edit, and on 2026-08-10 that cost the estate its authentication service. Two comment-only commits — cbec0b6db3 (de-identifying a customer’s names in three frozen migrations) and cebd0316b1 (a docs-archived/ → docs-archive/ path in one comment) — made every Authority image built after 2026-07-30 crash-loop at exit 139 against every database holding the originals. Not one SQL statement changed. The de-identification was deliberate and is not reversible, so the guard had to learn the difference instead.

MigrationSqlNormalizer reduces migration SQL to a comment-free, whitespace-collapsed form. Outside string literals it strips -- line comments and /* */ block comments (which nest in PostgreSQL) and collapses runs of whitespace; inside single-quoted, E'', double-quoted and dollar-quoted spans it copies through verbatim, because a -- in a seeded value is data and 'a b' is not 'a b'. Whitespace normalizes on the same terms as comments — re-indenting a statement does not change what it does.

It refuses rather than guesses, and a refusal means fail-closed:

A dollar-quoted body (a PL/pgSQL function, say) is treated as opaque data, so editing a comment inside a function body still fails closed. That is the conservative direction on purpose.

schema_migrations gains a normalized_checksum column, written when a migration is applied and backfilled on any boot where the whole-file checksum still matches. When the whole-file checksums disagree but the normalized ones agree, the host rewrites the recorded checksum, logs at WARN, and writes an audit row to <schema>.schema_migration_reconciliations. ADR-004 holds: no migration is re-run, only the bookkeeping row moves.

Databases that diverged before this mechanism existed have no baseline to compare against — only the whole-file hash of the applied content was ever kept — so each needs an owner-declared pin (MigrationChecksumReconciliationPin, see Content-Frozen Migrations). A pin is not an exemption: the host honours it only when the stored checksum matches it exactly and the current file normalizes to the normalized hash the pin declares for the applied content. It therefore binds one exact pair of contents, expires the moment the row is reconciled, and a later DDL edit on top of a pinned file fails closed again.

Bespoke runners carry the same contract (2026-08-11)

Three services do not use the shared AddStartupMigrations(...) path — they run their own SQL migration runner against their own version ledger. Until 2026-08-11 they were exposed: a comment-only edit to one of their applied migrations either crash-looped the service (fail-closed on the whole-file hash) or was silently swallowed (fail-open). SPRINT_20260811_001 closed that gap by porting the same mechanism into each, reusing the public MigrationSqlNormalizer (the single source of truth for “comment-only”) rather than re-implementing it. Their ledgers are not re-homed to schema_migrations — all three are retirement-bound and a ledger migration would be thrown away at consolidation.

The audit table for each is created lazily on the first reconciliation in that schema (same reason as the shared path — a clean converge must leave no trace of the mechanism), so relation "…schema_migration_reconciliations" does not exist is the normal answer for a schema that has never reconciled. The operator symptoms below apply to all three; the <schema> placeholder is timeline, export_center or evidence_locker for these services. Their runbook for genuine drift is identical: a schema change is a new migration file, never an edit to an applied one, and a pre-mechanism row that really was comment-only is reconciled with a reviewed MigrationChecksumReconciliationPin, never a hand-edit of the ledger.

Two runners the survey checked and cleared (they store migration names only, with no checksum column, so a comment edit is simply skipped rather than bricking or being swallowed): BinaryIndexMigrationRunner (binaries.schema_migrations) and PluginRegistryMigrationRunner (<schema>.plugin_migrations). Platform’s PlatformSetupMigrations (guided setup, Platform’s own module only) delegates the apply to the shared MigrationRunner, so it is already covered; Platform’s ReleaseMigrationRunner was deleted with the central migrator on 2026-09-14.

Operator runbook

Positional diffing against the applied content is not possible — only its hash was retained, never its text. The diagnosis classifies the divergence; to locate it you must diff the migration file against the revision the running binary was built from.

To inspect the recorded checksums directly (replace <schema> with the module schema, e.g. authority):

docker compose exec postgres psql -U stellaops -d stellaops \
  -c "select migration_name, category, checksum, normalized_checksum, applied_at from <schema>.schema_migrations order by applied_at;"

To read the reconciliation audit trail for a schema:

docker compose exec postgres psql -U stellaops -d stellaops \
  -c "select migration_name, reason, old_checksum, new_checksum, justification, reconciled_by, reconciled_at
      from <schema>.schema_migration_reconciliations order by reconciled_at;"

ERROR: relation "…schema_migration_reconciliations" does not exist is a normal answer, not a fault. The ledger is created by the first reconciliation in a schema, so its absence means none has ever occurred there — which is the case for most schemas. It is created on demand rather than alongside schema_migrations because this runner converges every service’s schema, and a table present in all of them is a change to every service’s schema shape: creating it eagerly broke three service families’ whole-schema assertions (exact table set, the P13 retention-class sweep, and the RLS posture check) without anything in those services changing. To test for it without an error:

docker compose exec postgres psql -U stellaops -d stellaops \
  -c "select to_regclass('<schema>.schema_migration_reconciliations') is not null as ever_reconciled;"

Notes:

• FailOnChecksumMismatch is the only knob that turns the genuine-drift guard off; leave it at its default true in production. The self-heal path runs regardless of that flag and never needs it relaxed — a self-healable legacy checksum is not treated as a mismatch in the first place.
• The self-heal is idempotent and concurrency-safe: it runs under the same pg_advisory_lock the migration runner already holds, so two instances booting at once cannot double-apply or race the UPDATE.

Baseline Re-Slim Checksum Mismatch (Migration.Platform.Release)

Incident class documented 2026-07-04 after the SPRINT_20260703_006 live E2E pass. This is the one known benign instance of the “genuine content drift” symptom above, and the only case where a targeted schema_migrations UPDATE is the correct recovery.

Symptom

platform-web crash-loops on startup (observed container exit code 139 in the 2026-07-04 live pass). Logs from the Migration.Platform.Release logger category (the category is Migration.<moduleName>, see src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/MigrationServiceExtensions.cs:45; platform-web registers the module at src/Platform/StellaOps.Platform.WebService/Program.cs:584-590 with schemaName: "release", moduleName: "Platform.Release") show:

Migration: Checksum mismatch for '001_v1_platform_database_release_baseline.sql': expected 'f16e70d9010942c0...', found 'b169cfa032ad08d7...'
Migration checksum validation failed for Platform.Release. See logs for details.

then StartupMigrationHost stops the application (src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/StartupMigrationHost.cs:114-119, FailOnChecksumMismatch defaults to true at :646), and the container restarts into the same failure.

Root cause

Commit 331e8f5755 (“PAC-6b … re-slim the release baseline (pre-release)”) rewrote the already-applied baseline in place: src/Platform/__Libraries/StellaOps.Platform.Persistence/Migrations/_archived/pre_1.0/mig061/Release/v1/001_v1_platform_database_release_baseline.sql (archived path since 2026-09-14, SPRINT_20260914_005 PEF-2; the file content is unchanged, so the hashes below still reproduce) lost ~2,570 lines of relocated DDL (the analytics.* star-schema, the four release.security_*_projection tables, and release.security_risk_snapshot — all moved to Findings-owned baselines) and gained 18 lines of RELOCATED comments. That changes the canonical LF-normalized SHA-256 (MigrationChecksum.Compute, src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/MigrationChecksum.cs:37-42) from

• recorded (pre-re-slim): b169cfa032ad08d7003629f25ae7138354d9275568cf97f609ba98db99fa7ea6

to

• expected (shipped): f16e70d9010942c0244299788ca3cd0175bc6c116c8bde1edfb71fdcdcf57d2c

The re-slim was justified as “pre-release” — i.e. assuming no database carried the applied row — but any DB migrated before the re-slim (the live compose stack, CI lanes, lab VMs) does carry it. The line-ending self-heal does not apply: this is a real content change, so the forward-only guard fails closed by design.

Non-destructive recovery

The re-slim only removed DDL from the baseline (relocated ownership); every object the shipped baseline would create already exists on the affected database, and nothing new needs to run. The recorded row and the shipped file describe the same applied state, so reconciling the recorded hash is safe — this is the baseline-re-slim analogue of the line-ending self-heal, done manually.

1. Verify the shipped baseline really hashes to the expected value (guards against reconciling onto a genuinely tampered file). Reproduce MigrationChecksum.Compute — SHA-256 over CRLF→LF-normalized content:

   # from the repo checkout that built the running image
   python3 - <<'EOF'
   import hashlib
   p = 'src/Platform/__Libraries/StellaOps.Platform.Persistence/Migrations/_archived/pre_1.0/mig061/Release/v1/001_v1_platform_database_release_baseline.sql'
   raw = open(p, 'rb').read().decode('utf-8')
   norm = raw.replace('\r\n', '\n').replace('\r', '\n')
   print(hashlib.sha256(norm.encode('utf-8')).hexdigest())
   EOF
   # must print: f16e70d9010942c0244299788ca3cd0175bc6c116c8bde1edfb71fdcdcf57d2c
   

2. Re-record the checksum for exactly that row (the old checksum in the WHERE clause makes the statement a no-op anywhere it does not apply):

   docker compose exec postgres psql -U stellaops -d stellaops_platform -c "
   UPDATE release.schema_migrations
      SET checksum = 'f16e70d9010942c0244299788ca3cd0175bc6c116c8bde1edfb71fdcdcf57d2c'
    WHERE migration_name = '001_v1_platform_database_release_baseline.sql'
      AND checksum      = 'b169cfa032ad08d7003629f25ae7138354d9275568cf97f609ba98db99fa7ea6';"
   -- expect: UPDATE 1
   

3. Restart platform-web and verify it passes the migration check (docker compose logs --since 2m platform-web — no Checksum mismatch, service healthy).

Nothing is downgraded and no SQL is re-run; only the recorded hash of an already-applied migration is reconciled to the file that now ships. If the hash in step 1 does not match the runner’s expected value, STOP — that is genuine drift; follow the row above (“Genuine content drift”) instead.

Proposed durable fix (Platform team to decide — NOT implemented)

Either option prevents the next baseline re-slim from crash-looping every already-migrated database; the runner change is deliberately not made here:

• Option A — fresh-DB-only guard for baseline re-slims. Treat any change to an applied 001_* baseline as build-breaking unless explicitly waived: a CI check compares each baseline’s canonical checksum against the previous release’s and fails on drift, so a “pre-release” re-slim is only possible while it is provably pre-release (no shipped image ever recorded the old hash).
• Option B — runner tolerance for re-slimmed baselines. Extend the StartupMigrationHost.ValidateChecksumsAsync self-heal (analogous to MigrationChecksum.IsSelfHealableLegacyChecksum) with a narrowly-scoped case: when the mismatching migration is the 001_* baseline and every object the shipped baseline creates already exists in the schema, re-record the checksum inside the advisory lock and log a WARN instead of failing closed. Genuine edits that add/change DDL still fail closed because the object-existence probe fails.

Option A keeps the guard maximally strict (preferred for supply-chain posture); Option B removes the operator step at the cost of a more permissive guard. Do not implement B without an explicit ADR-004 amendment.

Content-Frozen Migrations (checksum re-pin, 2026-07-30)

Three applied migrations are content-frozen: each is pinned by a checksum test, and editing one — including a comment — is genuine drift that stops the service on any database that already applied it.

All three previously carried third-party names in SQL comments and were held back from the repository-wide de-identification pass for exactly this reason. On 2026-07-30 the comments were de-identified anyway, as a deliberate pre-release decision, and the pins were re-cut in the same change.

The per-database operator step this section used to mandate did not happen, and the cost of that is the reason comment-only reconciliation exists. Between 2026-07-30 and 2026-08-10 every Authority image built from main crash-looped at exit 139 against the lab database, which was discovered only when the DOC-5 window tried to deploy one; the window had to pin Authority to bl2-9796c887-20260724 to keep authentication up. The lesson is not that the step was skipped — it is that a procedure requiring a hand-written UPDATE against every database in the estate before every image roll is not a control that holds.

Current behaviour, by database state:

Authority’s two pins are declared in src/Authority/__Libraries/StellaOps.Authority.Persistence/Postgres/AuthorityMigrationChecksumPins.cs and guarded by AuthorityMigrationChecksumPinTests, which fails the build if a later edit to S001 or S039 reaches a statement. The ReleaseOrchestrator pair (004, 007) has no pin: those databases were patched, or were provisioned fresh. If one turns up unpatched, add a pin rather than reviving the manual step.

Why a comment edit was ever fatal, and why the fix could not be a migration:

• The checksum is taken over the whole file (MigrationChecksum.Compute), so a comment edit was indistinguishable from content drift.
• The line-ending self-heal does not cover it: IsSelfHealableLegacyChecksum only recognizes the raw LF/CRLF renderings of the same text.
• A forward-only repair migration cannot fix it. Checksum validation is step 4 of StartupMigrationHost.StartAsync and pending migrations are applied at step 6, so a migration that would re-record the checksum never runs — the host has already called StopApplication(). Authority additionally owns a separate physical database (ADR-039), which no other service may write. This is exactly why the repair had to live in the validation step itself.
• FailOnChecksumMismatch is not operator-configurable: every registration uses the default true and no configuration is bound to it.

Manual fallback: guarded checksum UPDATE

Needed only for a pre-mechanism database with no pin, and preferred only when adding a pin is not practical. Both statements are guarded on the old value, so they are idempotent and a no-op on a database that never applied the old content (a fresh database records the new checksum directly). Unlike the automatic path, this leaves no audit row.

Authority database (stellaops_authority on the shipped compose):

UPDATE authority.schema_migrations
   SET checksum = 'd5fbdfec2c2669d1b24f5de6fe662416a6d032476b9f053dd3bd59f83032057a'
 WHERE migration_name = 'S001_v1_authority_operational_baseline.sql'
   AND checksum = 'e0cb2c3d30847732158abab7ef2d5c8fec108b58035c265d4640d9046a245306';

-- S039 was edited by cebd0316b1 (docs-archive path, in a comment) and was NOT
-- listed here before 2026-08-10, which is part of why the outage went unnoticed:
-- the 07-30 operator step covered only the three migrations that commit touched.
UPDATE authority.schema_migrations
   SET checksum = '93141891072398093d117e63b7900e062c0eb69abace6cf6c4a17fd9fc180a16'
 WHERE migration_name = 'S039_disable_tester_client_baseline.sql'
   AND checksum = '7759c92368d8da91c7b02a342122622556c6a583e95955129039c2b2080a43d0';

Platform database (stellaops_platform on the shipped compose) — both ReleaseOrchestrator schemas live here:

UPDATE release.schema_migrations
   SET checksum = 'e4dfab1e5641cdddd249cd0244b3b0390b31437083afccee414e756d3f49bca0'
 WHERE migration_name = '004_environment_requires_human_approval.sql'
   AND checksum = '4b8de07bf5b62d190ced17f5a27db8a624e11797093c611556d5336cc88c49b9';

UPDATE release_orchestrator.schema_migrations
   SET checksum = '82973f5ef345eb39a8965576d23f917a7a527232c2a7e97b86d1055f2b96db9f'
 WHERE migration_name = '007_registry_upstreams.sql'
   AND checksum = '2449d1a9863dba6369477d69b173d02659139e67aef0a68f68c7b70a33f676dd';

Each statement must report UPDATE 1 on a database that applied the old content, and UPDATE 0 on one that did not. A database left unpatched fails closed: Authority / release-orchestrator log Migration: Checksum mismatch for '<name>' and stop.

Currency (2026-08-12): the lab estate hit exactly this on the first release-orchestrator rebuild after 2026-07-30 — its rows in stellaops_platform (release.schema_migrations 004, release_orchestrator.schema_migrations 007) still held the pre-edit checksums and the fresh image failed closed (the rows predate normalized_checksum, so comment-only reconciliation has no baseline and cannot self-heal them). Both guarded UPDATEs above reported UPDATE 1; the next boot converged clean. Any other estate that last rebuilt release-orchestrator before 2026-07-30 will hit the same stop on its next image roll — the patch is this section, not a code change.

Pending Release/Data-band Migration (no runtime applier)

Since 2026-09-14 (SPRINT_20260722_021 PLT-4, DC-26). The owning host stops with Pending manual-category migrations block startup for <Module>; no runtime applier exists and names the file(s). A Release-band (100+ or rollback in the name) or Data-band (DM) file should never reach a build — NoManualCategoryMigrationsTests fails the tree — so this section exists for the case where one slipped past, or where an estate database already holds such a row from the pre-2026-09-14 CLI.

Choose one of the two remediations the host names.

Option A — renumber into the Startup band (preferred)

Precondition: the file has not been applied to any database (no row in <schema>.schema_migrations). Renaming an unapplied file is not an edit to applied history and ADR-004 is preserved.

1. Rename the file to the next free Startup-band number for that module (NNN_<description>.sql, 001–099, no rollback in the name).
2. Make sure its SQL is idempotent (IF NOT EXISTS, IF EXISTS) and stays backward compatible for the N-1 binary; split a breaking change across releases instead.
3. Rebuild and redeploy the owning service. Expected result: the host applies the file at boot and logs Migration: Applied 1 migration(s) for <Module>.

Stop condition: the file was already applied somewhere under its old name. Do not rename it; use Option B for that database and file a new Startup-band migration for everything else.

Option B — apply by hand and record the ledger row

Use only when Option A is not possible. The owning role, database and schema are the service’s own (STELLAOPS_POSTGRES_<SERVICE>_CONNECTION); never apply another service’s migration.

1. Snapshot first (Snapshot Before Upgrade) and keep the service stopped (docker compose stop <svc>).

2. Apply the SQL inside a transaction with the module schema on the search path:

   docker compose exec -T postgres psql -U <owner_role> -d <db> -v ON_ERROR_STOP=1 \
     -c "BEGIN;" -c "SET LOCAL search_path TO <schema>, public;" \
     -f /path/to/<migration_file>.sql -c "COMMIT;"
   

   Expected result: COMMIT with no error. On any error the transaction rolls back; fix the SQL or restore the snapshot before retrying.

3. Compute the checksum exactly as the host does — SHA-256 over the file with line endings normalized to \n, lowercase hex (MigrationChecksum.Compute):

   python3 - <<'EOF'
   import hashlib
   p = '/path/to/<migration_file>.sql'
   raw = open(p, 'rb').read().decode('utf-8')
   norm = raw.replace('\r\n', '\n').replace('\r', '\n')
   print(hashlib.sha256(norm.encode('utf-8')).hexdigest())
   EOF
   

4. Record the ledger row (category release or data to match the filename):

   docker compose exec postgres psql -U <owner_role> -d <db> -c "
   INSERT INTO <schema>.schema_migrations (migration_name, category, checksum, applied_by, duration_ms)
   VALUES ('<migration_file>.sql', '<release|data>', '<sha256 from step 3>', '<operator id>', 0)
   ON CONFLICT (migration_name) DO NOTHING;"
   -- expect: INSERT 0 1
   

   The normalized_checksum column is backfilled by the host on its next boot because the whole-file checksum matches.

5. Start the service and verify: docker compose up -d <svc> then docker compose logs --since 2m <svc> | grep 'Migration:'. Expected result: the pending-manual error is gone and the host reports the database up to date.

6. Record the file name, checksum, database and snapshot name in the incident or release evidence (Incident Notes), and open the source fix that removes the Release/Data-band file from the tree (Option A for future estates).

Rollback Procedure

Use rollback only when the service owner decides the upgrade must be abandoned and the pre-upgrade database state is required.

Warning: this restores the whole PostgreSQL volume. All services sharing this PostgreSQL instance roll back together. This is the tradeoff accepted by ADR-004.

svc=evidence-locker
snapshot=pg-snapshot-pre-${svc}-<timestamp>-<gitsha>.tar.gz

docker compose stop "${svc}" postgres
docker volume rm compose_postgres-data
docker volume create compose_postgres-data
docker run --rm \
  -v compose_postgres-data:/data \
  -v "$(pwd):/in" \
  alpine tar xzf "/in/${snapshot}" -C /data
docker compose start postgres
docker compose up -d "${svc}"

After restore, verify:

docker compose ps postgres "${svc}"
docker compose exec postgres psql -U stellaops -d stellaops \
  -c "select migration_name, category, applied_at from evidence.schema_migrations order by applied_at desc limit 10;"
docker compose logs --since 2m "${svc}"

When Not To Roll Back

Do not restore the PostgreSQL volume only because a new additive migration was applied. If the migration only adds tables, columns, or indexes and the previous service binary remains forward-compatible with that schema, prefer redeploying the service binary and leaving the database in place.

Do not use this runbook for partial service rollback when other upgraded services have already written data that must be retained. In that case, follow the Sprint 056 blue/green operator runbook when the schema is additive-only, or escalate to the service owner for a data-preserving remediation.

Incident Notes

Record the following in the incident or release evidence:

• Snapshot filename and target git SHA.
• Services stopped for the rollback.
• Migration rows observed before and after restore.
• Whether every affected gateway route recovered.
• Any data loss window created by restoring the shared PostgreSQL volume.

Related Documentation

• ADR-004: Forward-Only Database Migrations
• Platform Architecture Overview
• Persistence csproj contract: archived-migrations exclude - the csproj-level convention whose violation causes the leaf-name-collision class of incident this runbook recovers from (Sprint 20260512_026 / 028 / 034 in Scanner.Storage).
• Migration infrastructure source of truth: src/__Libraries/StellaOps.Infrastructure.Postgres/Migrations/ (StartupMigrationHost.cs, MigrationRunner.cs, MigrationCategory.cs, MigrationChecksum.cs — the LF-normalizing checksum + legacy self-heal predicate added in 46de746725)
• Migration CLI: src/Cli/StellaOps.Cli/Commands/SystemCommandBuilder.cs (stella system migrations-run | migrations-status | migrations-verify) — deleted 2026-09-14 (SPRINT_20260722_021 PLT-4, DC-26) together with the central migration-plugin mechanism; guarded by src/__Libraries/__Tests/StellaOps.Infrastructure.Postgres.Tests/NoManualCategoryMigrationsTests.cs
• docs-archive/implplan/SPRINT_20260518_056_Bluegreen_compose_operator_runbook.md