When tests auto-generate their schema and production applies versioned migrations, what breaks?

## Two schemas, two build paths A schema can reach a database by two routes. **Generation** derives it from the application's declared model at start-up: the framework reads the mapped types and emits the tables it thinks they need. **Migration** applies an ordered, append-only history of explicit change steps, each recorded so it runs exactly once. Production almost always uses the second route, because it must transform data that already exists rather than create a schema from nothing. When the suite runs against an engine substitute, generation is tempting: it is instant, it needs no dialect-specific migration steps, and it always produces a schema matching today's code. That convenience is exactly the problem — it produces *a* schema, not *the* schema. ## What goes untested If no test ever runs the migration history, the history has no test coverage at all. Its characteristic defects are then discovered by the deploy: - **Ordering between steps.** A step reads a column another step has not created yet, or a backfill runs before the rows it must fill exist. On a fresh generated schema, ordering is irrelevant; on a history, it is the whole contract. - **Backfills over real data.** A step that populates a new column from an old one is trivially correct on an empty table. Against populated data it meets null values, mixed casing, out-of-range values and rows written concurrently while the step runs. - **Constraints added ahead of the data.** Adding a non-null or unique constraint succeeds on an empty schema and fails on production data that violates it. - **Defaults applied only forward.** Some engines apply a new default only to rows inserted afterwards, leaving existing rows null. Generation never has existing rows, so the gap is invisible. - **Operations that lock or rewrite.** A change that rewrites a table or takes an exclusive lock is instant on an empty schema and an outage on a large one. This is about duration and locking, not correctness, and generation cannot show it. - **Idempotency and rerun behaviour.** A partially applied step that is retried must not double-apply. Nothing in a generated schema exercises the runner's record-keeping. ## Drift between the two artefacts Even ignoring migration defects, the generated schema is usually a *different* schema. Generation approximates: it often omits secondary and partial indexes, chooses a default numeric precision and scale rather than the declared one, picks its own text length and collation, skips check constraints, and simplifies cascade rules. Tests therefore run without the index that makes the production query viable, with a wider numeric type that never rounds, and with a text column that compares differently. The drift is one-directional and grows: someone adds an index or a check constraint in a migration for a production incident, the code model never mentions it, and the generated schema never gains it. Nothing fails, so nobody notices. ## The worked case A renewal history table gets a new column holding the renewal boundary date, added by a migration plus a backfill that derives it from the previous expiry. The generated test schema has the column from the moment the model declares it, so every test row has it populated by the object that created it. The backfill itself — which derives the boundary by adding a month and then subtracting a day — is never executed by any test. In production it runs over 41,283 existing rows and lands one day early for every subscription created on the 31st of a month, an off-by-one at the boundary that only shows up when those subscriptions renew. The suite was green throughout, because the suite never ran the step that was wrong. ## Making provenance the same The durable fix is to build the test schema the way production builds it: run the same ordered migration steps, with the same runner, before the suite starts. Then a migration defect fails a test. Costs are real and worth naming — the run is slower, the steps must be expressible on whichever engine the suite uses, and dialect-specific steps may not run on a substitute at all. That last point is often the moment a team stops substituting the engine for its persistence tier and moves those cases to the real engine, because a history full of engine-specific steps cannot be honestly rehearsed anywhere else. Intermediate positions exist and should be argued on their merits: apply migrations in the suite that touches the real engine while letting fast logic cases keep a generated schema; or keep generation and add a scheduled check that compares the generated schema against a schema built by the migration history, failing on any difference. Both are defensible. What is not defensible is believing a green suite says anything about migrations it never ran. A separate discipline worth keeping: migrations that only ever run forward on the deploy still deserve rehearsal against realistic data volume, because duration and locking are properties of the data, not of the step.