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). Pending release- or data-category migrations block startup by default and must be applied by the operator first (see stella system migrations-run below). 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 manual release migration, not an auto-applied startup migration).

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. Prefer the CLI, which also runs checksum validation and reports pending startup/release counts:

stella system migrations-status --module <module> --verbose
stella system migrations-verify --module <module>

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 and Line-Ending Self-Heal

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 that owns a PostgreSQL schema (CoC §2.7), not just Authority.

Operator runbook

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, applied_at from <schema>.schema_migrations order by applied_at;"

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.Database/Migrations/Release/001_v1_platform_database_release_baseline.sql 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.Database/Migrations/Release/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. No executable SQL changed. Any database that applied these migrations before that change must be patched — see Operator step below — or the owning service aborts at startup with a checksum mismatch.

Why the comment could not simply be edited:

• The checksum is taken over the whole file (MigrationChecksum.Compute), so a comment edit is genuine content drift, not a formatting difference.
• 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.
• FailOnChecksumMismatch is not operator-configurable: every registration uses the default true and no configuration is bound to it.

Editing one therefore costs a per-database operator step and a crash-loop window on any estate that already applied the migration. Whenever it is done, treat it exactly like the baseline re-slim above: verify the shipped file’s canonical hash, then run the guarded UPDATE … SET checksum = <new> WHERE migration_name = '<name>' AND checksum = '<old>' against each affected database before rolling the new image, and update the pin test in the same change.

Operator step (2026-07-30 re-pin)

Run before rolling images built from the de-identified migrations. 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).

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';

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.

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)
• docs-archive/implplan/SPRINT_20260518_056_Bluegreen_compose_operator_runbook.md