ci: bootstrap the stack from schema.sql only; restore historical migrations

↗ view on GitHub · Amal · 2026-08-05 · ab716822

Addresses willchen96's review on PR #256.

WHY THIS MATTERS

The previous revision of this workflow built the CI database as:

    empty database -> current schema.sql -> every dated migration

and called it a "schema-drift smoke test". But that sequence is not a real
installation path. Per README.md, this repo has two deliberately separate
database artifacts:

  - backend/schema.sql      -> the COMPLETE shape for FRESH databases
  - backend/migrations/     -> incremental steps that move OLDER, already-
                               deployed databases forward from the version
                               they are on

A fresh install runs schema.sql and stops. An existing deployment runs only
the migrations dated after its version. Nobody ever replays the full
migration history on top of the current snapshot - so when five old
migrations "failed" under that replay, they were not broken; the harness
was. They had run correctly on the era-appropriate schemas they were
written for.

WHAT IS MIGRATION IMMUTABILITY

Once a migration has shipped and real deployments have executed it, the
file becomes a historical record of "the change that was required at that
point in time". Editing it afterwards cannot help any database that already
ran it - it only makes the repo's history diverge from what production
actually executed, which is exactly the record you need intact when
debugging a deployment later. (Tools like Flyway enforce this with
checksums: a modified applied migration is a hard error.) The previous
revision rewrote five historical migrations to satisfy the artificial
replay; worse, the column-existence guards it added made those backfills
silently no-op on unexpected schemas - converting the loud failure a drift
check exists to produce into a silent skip.

HOW THIS COMMIT FIXES IT

1. The five historical migrations are restored byte-for-byte to their
   state on main (20260424_01, 20260427_01, 20260602_01, 20260613_02,
   20260613_05).
2. The workflow's bootstrap step now loads schema.sql only - the same
   thing backend/scripts/test-stack.sh does locally and the same thing the
   README documents for a fresh deployment. The suite therefore tests the
   real contract: "a fresh Mike database enforces deny-all RLS and the
   auth<->API contract."

Real drift protection (does baseline-plus-migrations equal the current
snapshot?) needs a pinned baseline dump from an older release, the
migrations dated after it, and a pg_dump schema diff against a
schema.sql-built database. That is a separate change, designed separately.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Repository open-legal-products/mike
Author Amal <mamalanand3@gmail.com>
Authored
Committed
Parents 2288419a
Stats 6 files changed , +54 , -135
Part of Add schema drift checks for database upgrades

Capture this commit into my fork

Download a Markdown prompt that tells Claude how to port this exact commit into your working tree. Run it via claude -p < capture-commit-ab716822.md from inside the repo you want the change in.

⬇ Download capture-commit-ab716822.md