Prove a Generated Migration Can Undo Itself Before It Touches Your Data

A migration has not earned a place in your deploy until its rollback path has been run against a disposable copy and the resulting schema matches the starting point. Forward success is the easy half, and with generated SQL the down script is usually where the assistant quietly goes wrong.

When a model writes an ALTER TABLE, it often gets the forward statement close enough to pass a syntax check, because the intended new shape is part of the prompt. The matching DROP COLUMN, constraint restore, or index rebuild is much harder for the model to reconstruct, because it has to infer the previous state from context and it rarely asks for the baseline schema. If that failure shows up for the first time during a production rollback, you are now debugging the original deploy problem and the cleanup at the same time.

The safer routine is to treat rollback as a property of the migration, not as a review note at the bottom of a pull request. You capture the schema before applying the generated up.sql, apply the up script to a throwaway database, apply the matching down.sql, and compare the resulting schema with the starting schema. This is a small reproducible harness, and it remains useful even if you swap the model or write the SQL by hand.

If you have access to MonkeyCode's free model endpoint and a free server slot, the loop is cheap enough to run on every pull request rather than only when someone remembers to test the down path by hand. Disclosure: This article was prepared as part of MonkeyCode's product outreach.

The harness does not require the model to be perfect on the first pass. It only needs the model to revise a failing down script after seeing the exact schema objects that did not return. A useful sequence is to ask for up.sql and down.sql together from the same migration intent, run the pair, and then feed the mismatch back with a request to change only the down script. The more the two files share context, the more likely the pair is reversible, but the harness is what turns a plausible answer into proof.

#!/usr/bin/env python3
# rollback_contract.py -- apply up/down to throwaway SQLite databases and compare schema
import os
import shutil
import sqlite3
import sys

def schema(db_path):
    con = sqlite3.connect(db_path)
    query = 'select type, name, tbl_name, sql from sqlite_master order by type, name, tbl_name'
    data = con.execute(query).fetchall()
    rows = [
        tuple(r) for r in data
        if r[0] != 'table' or not (r[1] or '').startswith('sqlite_')
    ]
    con.close()
    return rows

def apply(db_path, script_path):
    con = sqlite3.connect(db_path)
    with open(script_path) as f:
        con.executescript(f.read())
    con.commit()
    con.close()

baseline_sql, up_sql, down_sql = sys.argv[1], sys.argv[2], sys.argv[3]
for path in ('baseline.db', 'candidate.db'):
    if os.path.exists(path):
        os.remove(path)

apply('baseline.db', baseline_sql)
before = schema('baseline.db')
shutil.copyfile('baseline.db', 'candidate.db')

apply('candidate.db', up_sql)
apply('candidate.db', down_sql)
after = schema('candidate.db')

if before == after:
    print('ROLLBACK OK')
else:
    print('ROLLBACK MISMATCH')
    before_keys = {row[:3] for row in before}
    after_keys = {row[:3] for row in after}
    print('only in before:', sorted(before_keys - after_keys))
    print('only in after:', sorted(after_keys - before_keys))
    sys.exit(1)

Run it from the command line with the checked-in baseline and the two generated files:

python rollback_contract.py baseline.sql up.sql down.sql

The script compares only the rows returned from sqlite_master, so it catches missing tables, leftover tables, changed table SQL, missing indexes, and missing triggers. It does not prove that row data survives, and it does not prove that your migration is fast under load. Those are separate checks.

When the harness prints ROLLBACK MISMATCH, the two sets at the bottom are the input to the model's next attempt. If the after set has a leftover index, ask the model to remove that object in the down script. If the before set has a constraint that never came back, ask the model to restore that constraint, not to rewrite the entire migration. Narrow feedback prevents the model from accidentally changing the forward path that already worked.

One common trap is using the same database file for the baseline and candidate runs. The copy is important because an up script can mutate the baseline in ways that make the comparison meaningless. Another trap is running the down script on a database that already failed partway through the up script; the cleanest test starts from a fresh candidate after a successful up, because that is the state a rollback would actually be asked to repair.

The harness as written is SQLite-specific, so adapt the schema query for Postgres, MySQL, or whatever your production database uses. Schema equality can also pass while user data is permanently lost, so this check is not a substitute for reviewing destructive operations such as dropping columns or tables. If your rollback requires manual data reconstruction, the schema check is only one part of the rollback plan, not the whole plan. Teams that already use a migration tool with reversed schema diffing may not need this as a gate, but it still works as a fast local sanity check before a human spends time on the rest of the change.

Once the loop is running, make it the first job in the migration pipeline, not the last, so a generated down script fails against a disposable database long before it can fail against yours.