Migrations That Survive Production

There is a specific failure that shows up in every project past its first year. The schema exists in three places: the migrations directory, a schema.sql someone dumped once, and the actual production database, which quietly disagrees with both.

Nobody decides to do this. It accumulates.

One directory owns the schema

The rule I now apply from the first commit: exactly one directory contains migrations, and it lives in the service that owns the database. Not the frontend repo. Not a shared infra repo. The service.

When Velvynote's backend was split out, the first thing that moved was the schema, and the first thing that got deleted was the old supabase/migrations folder that had been shadowing it. Two ordered sequences of migrations against one database is not redundancy — it is a race.

Baselines are allowed

The objection to consolidating is always the same: we would lose the history. You would, and it does not matter. A migration's job is to get a database from the previous state to the next one. Once the previous state is "does not exist", the entire history collapses into one file:

migrations/
  0001_baseline.ts          # applies db/0001_baseline.sql
  0002_security-fixes.ts
  0003_admin-ensure-capacity.ts
db/0001_baseline.sql        # the full starting schema

0001 is not a lie about history. It is a statement that history before this point is not executable any more, which was already true.

Keep a snapshot, and never apply it

The one artefact worth keeping alongside the migrations is a dump of the live schema, committed and regenerated on every deploy. It is never applied to anything. It exists so that a diff against it tells you, in one command, whether production has drifted:

pg_dump --schema-only --no-owner "$DATABASE_URL" > schema.sql
git diff --exit-code schema.sql

If that command fails in CI, someone changed production by hand. That is worth failing a build over.

Down migrations are mostly a fiction

Every framework offers migrate:down, and it is genuinely useful in development, where you are iterating on 0007 and want to try again. In production it is close to useless: the down migration for "dropped a column" is "recreate the column, without the data", which is not a rollback, it is a differently broken state.

Plan forward instead. Add the new column, backfill it, switch the reads, and drop the old one in a later deploy. Four boring migrations that are each individually safe to run beat one clever migration you would need to reverse under pressure.