1,022 migrations behind a schema drift gate
Kept the schema honest across 1,022 migrations with a CI gate that
builds the database both ways — a fresh install, and an install plus
every migration — and fails when the two disagree.
2025

Situation. A schema is described twice in most projects: once by the
initial setup that builds it from scratch, and once by the
accumulated migrations that grew it. Both are supposed to produce
the same database. Nothing checks that they do, so they drift — and
the drift is invisible until a fresh environment behaves differently
from production, usually at the worst moment.

Task. The two descriptions had to be provably identical,
automatically, rather than periodically believed to be.

Action. The database is versioned as 1,022 migrations, numbered from
036 through 1102, and the ordering discipline around them is
unglamorous and non‑negotiable. What makes it hold is a CI job that
builds the database twice on every change: once from the fresh
initial schema, once from the initial schema plus every migration
replayed in order — and then compares the two. Not just the
structure, which is the easy half, but the seeded data too, because
a migration that backfills a lookup table wrongly is exactly as
damaging as one that forgets a column, and only one of those shows
up in a schema diff. Any disagreement fails the build with the
difference printed.

Result. A fresh environment and a long‑lived one are the same
database, and that is checked rather than assumed. The cost lands on
whoever writes a migration: it has to work replayed and it has to
work from cold, which is more thought than a quick ALTER usually
gets. That is the point — the alternative is finding out during a
restore, when the answer matters and there is no time to work it
out.

Categories:
1Databases	/categories/databases/	platform.engineer.company	70
1PostgreSQL	/categories/postgresql/	platform.engineer.company	70
1Data Governance	/categories/data-governance/	platform.engineer.company	70
1DevOps	/categories/devops/	platform.engineer.company	70
1Reliability & Backups	/categories/reliability/	platform.engineer.company	70
1Migrations & Modernization	/categories/migration/	platform.engineer.company	70
1Testing & QA	/categories/testing/	platform.engineer.company	70

Services:
1Database Administration (DBA)	/services/database-administration/	platform.engineer.company	70
1Database Migration & Modernization	/services/database-migration/	platform.engineer.company	70
1AI Data Readiness	/services/ai-data/	platform.engineer.company	70
1DevOps & CI/CD Automation	/services/devops-cicd/	platform.engineer.company	70

hThis page on the web	URL:https://platform.engineer.company/portfolio/1022-migrations-behind-a-schema-drift-gate/	platform.engineer.company	70
