Twelve commits to land one migration: the 0024 saga

When one database migration costs you a full day

Migration 0024 for StatPro adds two columns to the questions table, a foreign key with a NOT VALID constraint (so it enforces new writes without scanning the full historical table), and a set of trigram and full-text search indexes. On paper this is a single ALTER TABLE plus an index creation step. In practice it took twelve commits across ten hours to get it through the production pipeline.

Each commit fixed a different way the migration could fail silently or refuse to run at all. Here is what happened, in order.

The ECS task that vanished

The migration runs inside an ECS task launched by a GitHub Actions workflow. The first failure: the task lifecycle check only accepted PENDING, RUNNING, and STOPPED as valid states. But ECS has a longer lifecycle: PROVISIONING, ACTIVATING, DEPROVISIONING, and crucially DELETED. If the task got cleaned up before the workflow polled it, the receipt check would fail with an unexpected status and block the pipeline.

# Before: three states, everything else was an error
[[ "$status" == PENDING || "$status" == RUNNING || "$status" == STOPPED ]]

# After: the full ECS lifecycle, with DELETED as an explicit failure
case "$postlaunch_status" in
  PROVISIONING|PENDING|ACTIVATING|RUNNING|DEACTIVATING|STOPPING|DEPROVISIONING|STOPPED) ;;
  DELETED) echo "Migration task was deleted before a zero-exit receipt" >&2; exit 1 ;;
  *) echo "Unexpected migration task lifecycle state: $postlaunch_status" >&2; exit 1 ;;
esac

The fail-closed guarantee is the key design principle here: the pipeline accepts a broader set of in-flight states so it does not false-alarm, but it still refuses to record a success receipt if the task disappeared before producing one.

Archive permissions and the absent-object problem

The migration writes cryptographic receipts to an immutable archive bucket. The next failure: the task IAM role could not distinguish between an object that does not exist and an object it does not have permission to read. Both returned the same access-denied error. The fix was granting bucket-level listing permission so the code can check for absence explicitly rather than guessing from a denied error.

Receipt generation collisions

Two more commits fixed how the migration task generates its evidence receipts. The task was reusing receipt file names across retries, which meant a partial receipt from a failed run could be confused with a complete one from a successful run. The fix isolated receipt generations by run, so each attempt produces its own set of artifacts. A separate fix normalized the task separator in file names so the receipts were deterministic.

The SQL alias that Postgres reserved

This is where it got interesting. The migration SQL includes a precondition block that validates the search-index function and all ten prebuilt indexes before it will run. Inside that block, a column alias in the SELECT INTO query collided with a Postgres reserved word. The migration would fail with a syntax error deep inside a DO block:

-- The alias "strict" is reserved in certain contexts
function_metadata.proisstrict AS strict  -- fails
-- Renamed to avoid the collision
function_metadata.proisstrict AS is_strict  -- works

Then the same migration hit a second alias problem: strict was used as a PL/pgSQL variable name in the IF condition, which also conflicts. One commit fixed the SQL alias, another fixed the PL/pgSQL variable reference. Two commits, same root cause, two different places it surfaced.

FK columns decoded wrong

After the SQL fixes, the migration ran but the postbuild step that validates the foreign key started reporting wrong data. The migration added a catalog_id column and the postbuild retention step was decoding the FK columns incorrectly, reading the wrong column order from the result set. A two-line fix in the decoder resolved it.

What I learned

The pattern across all twelve commits is the same: the migration had a fail-closed design where every step produces a verifiable receipt, and every receipt gets checked. That design is what made the failures visible. Each bug was a place where the receipt-checking logic was stricter than the thing it was checking: the ECS lifecycle had more states than the poller knew about, the archive had absence-vs-denied ambiguity, the SQL had alias collisions the author did not expect, and the FK decoder had a column-order assumption.

The fix each time was the same move: expand the model of reality to match what is actually happening, then make the check precise about what it accepts and what it rejects. Not loosening the gate, just making it see the full picture.

The other thing that shipped

While migration 0024 was being debugged, the public web shell was also restructured. The old marketing homepage is gone; the actual stats search experience is the public home now. The landing page loads the same reference data, leaders, and on-this-day content the app uses, so visitors see the product immediately instead of a marketing page first. The old home had 327 lines of page code and 82 lines of home-data fetching; both are deleted. The reference stats home now serves the root URL directly.

Also shipped: a database-first question reconciliation tool that compares the certified question catalog against what the production database actually contains, running through a read-only loopback tunnel with bounded row counts and immutable evidence output. The first production reconciliation evidence was recorded at midnight.