Systems and records
Database migration agent evaluation: when a schema change runs cleanly and breaks the business
By SourceX Editorial · Updated
10 sections
Short answer
To test whether a technically successful schema migration preserves business behavior, run the agent's migration against a realistic copy of the data, check a written list of business invariants before and after (counts, totals, relationships, status meanings), run the application's own tests, then roll back and check the invariants again. A database migration agent evaluation should fail any migration that completes without error but changes what the business data means.
Evaluation split
Database migration agent
Key takeaways
- A migration that exits cleanly proves the statements ran, not that the data still means the same thing.
- Write business invariants as executable checks and run them before the migration, after it and after rollback.
- Many migration tools treat renames as drop and add, and many operations are irreversible or lose data on the way back.
- Pin the database version, data snapshot and application commit so a second reviewer can rebuild the run.
- Grade invariants, application tests, rollback, locking behavior and the agent's own warnings as separate scores.
How do you test whether a technically successful schema migration preserves business behavior?
You test it on data, not on the migration log. Load a realistic snapshot of the database, compute a set of business invariants that must hold, apply the agent's migration, compute them again, run the application tests that exercise the affected rules, then apply the rollback and compute them a third time. The migration passes only if every invariant that should survive did survive, the tests pass, and the rollback brings the business state back.
Coding agent benchmarks usually grade a patch by whether tests pass. That works for logic bugs, but a schema migration can pass every test in the repository and still delete a column of customer notes, turn missing discounts into zero discounts, or remap an order status so that refunds look like shipments. Tests are written against the schema the developer had in mind; invariants are written against what the data means to the business.
This guide covers why clean migrations break things, a catalog of invariant types with example checks, how to test rollback honestly, a field-level task specification, how to make the run reproducible, a grading rule, an illustrative task and the cases where the evaluation should stop.
Why does a migration that runs cleanly still break the business?
Because the database only checks the rules it was told about, and most business rules live outside it. A migration can satisfy every constraint and still change meaning. Generated migrations are a common source: Alembic's documentation says its autogenerate feature cannot detect table or column renames, which come out as a drop of the old column and an add of a new one, and that candidate migrations must always be reviewed by hand. An agent that accepts the generated file has written a migration that runs perfectly and empties the renamed column.
Other failures are about time rather than meaning. PostgreSQL's documentation for ALTER TABLE says an ACCESS EXCLUSIVE lock is taken unless a subform says otherwise, and that adding a column with a volatile default rewrites the whole table and its indexes. On a small test database that finishes instantly; on a production-sized table it can block every read and write for the duration. An evaluation that never measures lock time or rewrite behavior will rate that migration as safe.
The common semantic breaks are narrowing a type so values are truncated, backfilling a new column with a default that changes meaning, mapping old status codes to a new set incorrectly, converting timestamps without a time zone rule, adding a cascade that deletes child rows, and splitting or merging columns in a way that loses edge cases.
Which business invariants should a migration task check?
Check the invariants that the business would notice if they changed, and write each as a query with an expected relationship between its value before and after. Pick them with the person who owns the data, not only with the engineer, because the owner knows that an invoice total must equal its lines and that a canceled order must never become active again.
Run every invariant on the snapshot before the agent sees the task and discard any that already fail; a check that is false on clean data measures data quality, not the migration. Keep the invariant queries independent of the new schema where possible, for example by reading through a view that exists before and after, so that a dropped column makes the check fail loudly instead of silently returning nothing.
Some invariants are expected to change, and the task must say how. If the requirement archives closed orders older than a given year, row conservation becomes a stated difference, and the check compares the archived count with the count the requirement implies.
| Invariant type | Example check | Expected before and after | Break it catches |
|---|---|---|---|
| Row conservation | Count of orders, customers, invoices | Equal, unless the task says rows are archived | Accidental deletes and cascade deletes |
| Value conservation | Sum of invoice line amounts per invoice and overall | Equal to the cent after any type change | Rounding, truncation and dropped rows |
| Referential meaning | Every order still points to the same customer key | Same pairs before and after | Re-keying and wrong join backfills |
| Status semantics | Distribution of order states and allowed transitions | Same counts per mapped state | Wrong enum or code mappings |
| Null versus default | Count of rows where a discount is unknown | Unknown stays unknown, not zero | Backfills that invent values |
| Uniqueness the business relies on | One active contract per customer and product | Zero violations | Constraints dropped during a table rebuild |
| Derived values | Balances recomputed from transactions match stored balances | Equal | Logic moved from triggers or code into columns |
| Time meaning | Orders placed per local business day | Same per day | Time zone conversion errors |
Does the rollback actually restore the business state?
A rollback counts only if the invariants after rollback match the invariants before the migration, not if the down migration exits without error. Framework documentation is explicit that reversibility is limited. Django's documentation says removing a field is reversible, apart from the data loss, only when the field is nullable or has a default, and that a RunPython data step with no reverse_code is irreversible. The Rails migration guide says change_column is irreversible unless the developer writes a reversible block.
So the evaluation has to ask a sharper question than whether a down migration exists. It should ask whether the data that existed before the change comes back. For destructive steps the honest answers are restore from backup, keep the old column until a later release, or accept the loss with sign-off; an agent that writes a down migration recreating an empty column has produced a schema rollback and a data loss.
Run the sequence the same way for every task:
- Snapshot the data and compute every invariant.
- Apply the agent's forward migration and record duration, locks taken and errors.
- Compute the invariants and run the selected application tests.
- Apply the agent's rollback, or the restore procedure the agent specified.
- Compute the invariants again and compare with the first snapshot.
- Apply the forward migration a second time to confirm it is repeatable.
The domain invariant and rollback evaluation task, field by field
Each task is one schema change request with everything needed to grade it. Build tasks from real change requests, keep the requirement as it was written, and keep the human-written migration as a reference rather than the only correct answer, since several migrations can satisfy the same invariants.
At a glance
The domain invariant and rollback evaluation task, field by field
| Field | What it holds | Validation check | Why it matters |
|---|---|---|---|
| task_id and requirement | Stable ID and the change request text with its version | Requirement text frozen; version recorded | Grades the agent on what was actually asked |
| base_commit and schema | Application commit and schema before the change | Commit resolves; schema dump matches the snapshot | Defines the starting point |
| data_snapshot | Masked copy of production-shaped data with row counts | Masking keeps keys, distributions and edge cases | Invariants need realistic data to fail |
| engine_and_version | Database engine, exact version and key settings | Pinned image or package version | Lock and rewrite behavior differ by version |
| invariants | Executable checks with expected before and after relation | Each check runs on the snapshot and passes before the change | The core of the grade |
| app_tests | Tests that must newly pass and tests that must keep passing | Both lists run green on the reference migration | Catches code that depends on the old shape |
| rollback_expectation | Reversible, restore from backup, or accepted loss with reason | One value per task, set by the data owner | Stops agents being rewarded for empty down migrations |
| operational_limits | Maximum lock time and whether the change must run online | Measured on the snapshot size | Catches table rewrites on large tables |
| reference_migration | The human-written migration and its review comments | Passes every invariant and test | Proves the task is solvable |
| provenance_and_rights | Repository owner, data owner approval, masking method | Approvals recorded before the task is used | Code and data rights travel with the task |
Can a separate reviewer rebuild the environment and confirm the result?
They can if the task pins every input and the harness runs in a container, and the evaluation should be built so that this is the normal case. SWE-bench is a useful pattern: each task instance carries the repository, a base commit, the problem statement, a gold patch, a test patch, and two lists of tests, FAIL_TO_PASS and PASS_TO_PASS, and its harness applies a generated patch and runs the repository's tests in a Docker environment so results are consistent across machines.
A migration task needs the same discipline plus the database. Pin the engine image and version, ship the masked snapshot or a deterministic generator with a seed, record the invariant queries in the task rather than in a separate script, and log the forward and rollback runs with timings. Map the test lists directly: tests that must newly pass are the requirement, tests that must keep passing protect existing behavior, and invariants are a third list that protects the data.
Store the reviewer's rebuild as part of the result. A run that a second person reproduced from the task alone, with matching invariant values and timings within a stated tolerance, is evidence; a run that only worked on the original engineer's laptop is an anecdote. When a rebuild disagrees, keep both logs and treat the task as unstable until the cause is found.
How should the agent's migration be scored?
Score each part separately so that a strong overall number cannot hide a destructive change, and fail the task outright when a conservation invariant breaks without the requirement asking for it.
At a glance
How should the agent's migration be scored
- Invariants: every invariant holds as specified after the forward migration; any unexpected loss of rows or value is a hard fail.
- Application tests: the must-newly-pass and must-keep-passing lists both pass.
- Rollback: invariants after rollback equal the original, or the agent correctly declared the step irreversible and specified a restore path.
- Operations: lock duration and table rewrites stay within the task's limits on the snapshot.
- Disclosure: the agent warned about irreversible or blocking steps in its own explanation; silent destructive steps score lower than declared ones.
Illustrative task: replacing a free-text status with an enum
This task is invented for illustration. A subscription billing application stores order status as free text. The requirement asks for a status column limited to five values and removal of the old column. The snapshot holds a few hundred thousand orders, and a profiling query shows nine distinct text values, including two spellings of canceled and a legacy value, on hold, used by a few hundred orders.
An agent's migration that maps the obvious values and sends everything else to the default value active will run without errors, pass the application tests, which only create new orders, and fail the status semantics invariant: the count of active orders rises by the number of on-hold and misspelled rows. The same migration's rollback recreates the text column from the enum, so the original spellings and the on-hold state are gone, and the rollback invariant fails too.
A passing migration adds the new column alongside the old one, maps every observed value explicitly, refuses to proceed if an unmapped value appears, and leaves the old column in place for a later release so rollback is a matter of switching reads back. The exception: if the data owner signs off that on-hold orders are to be treated as canceled, that mapping becomes correct, the expected distribution changes, and the task records the decision in its requirement version.
Where invariants run out and a human has to decide
Stop and involve the data owner when the requirement intends to change meaning, for example merging two customer types or redefining what counts as an active account, because the expected after state is a business decision the evaluation cannot infer. Bring in a database administrator for tasks on very large tables, replication setups or engines where lock behavior differs from the documented default, since a snapshot may not reproduce production timing.
Leave out tasks whose data cannot be masked without destroying the edge cases that make the invariants fail, and tasks where the repository or schema belongs to a client under terms that do not allow evaluation use. State the limits with any published results: invariants catch only what someone wrote down, and a migration graded on one application's data says little about another's.
Migration histories as licensed evaluation material
Real schema change histories, with the change request, the migration, its review comments, the incidents it caused and the fix, are what make tasks like these hard and realistic. Owning the repository and the database does not settle whether they can be licensed: client code, customer data inside the snapshot and contractor agreements each need review, and masking the data does not by itself create a licensing right.
Software companies with that kind of change history can bring it to SourceX, which handles rights review, preparation and delivery of selected records licensed to AI developers. The first conversation is a short assessment of the company with nothing uploaded, and migration records move only after the company signs an agreement whose scope and terms it has approved.
Frequently asked questions
Why are passing tests not enough to accept an agent's migration?
Tests check the behavior a developer chose to test, usually on new rows, while a migration changes existing data. A migration can pass every test and still lose a renamed column or remap statuses. Invariants computed on a realistic snapshot catch those changes.
What is a domain invariant in a database migration?
It is a business fact about the data that should hold before and after the change, written as a query, such as invoice totals equaling the sum of their lines or each order keeping its customer. The evaluation compares the query results across the forward migration and the rollback.
Is every migration supposed to be reversible?
No. Some steps, such as dropping a column without a default or a data transformation without reverse code, cannot be undone from the schema alone. The evaluation should reward an agent that declares such steps and names a restore path, and penalize one that writes a down migration that silently loses data.
How large should the data snapshot be?
Large enough to contain the edge cases the invariants test and to show lock and rewrite behavior on the biggest tables in scope. Record row counts per table in the task so results can be compared.
Can production data be used in a migration evaluation?
Only as a masked copy that keeps keys, distributions and edge cases, and only with the data owner's approval. Customer data and client code also raise rights questions that need review before the task is shared outside the company.
Sources
- Alembic autogenerate cannot detect table or column renames, which appear as drop and add pairs, and its candidate migrations must always be reviewed. Source
- PostgreSQL ALTER TABLE takes an ACCESS EXCLUSIVE lock unless noted, and a column added with a volatile default rewrites the table and its indexes. Source
- Django's RemoveField is reversible, apart from data loss, only for nullable fields or fields with a default, and RunPython without reverse_code is irreversible. Source
- The Rails migration guide says change_column is irreversible unless a reversible block is provided. Source
- SWE-bench task instances carry a base commit, gold patch, test patch and FAIL_TO_PASS and PASS_TO_PASS test lists. Source
- SWE-bench applies generated patches and runs the repository's tests in containerized Docker environments for consistent results. Source
See if your company qualifies
A short company assessment. No data uploads are needed.