agent skipped the down-migration test and the rollback script references columns that no longer exist

TL;DR

The down migration was written once and never executed, so it references a schema that later migrations changed: it is fiction, not a rollback plan. Fix it to reverse exactly what the up migration did against the schema as it exists now, prove it with migrate-up-then-down on a scratch database, and add that test to CI for every migration. An untested down migration is a plan you have never read.

The query

agent skipped the down-migration test and the rollback script references columns that no longer exist

Use this when

• Rollback script references columns that no longer exist
• Down migration was never tested and now fails
• Need up/down migration testing in CI
• An agent skipped testing the down migration

Not for

• Rollbacks that fail because data cannot be restored (a data problem, not a script problem)
• Intentionally irreversible migrations (document them; do not fake a down)
• Up migrations that fail (fix the up direction first)

Steps

1. Read the down migration against the current schema

Open the down function and compare every statement against the schema as it exists after the up migration plus all later migrations. Mark every reference to a column, table, or constraint that no longer exists or has changed.

Expected output: the list of stale references. This is the complete repair list.

2. Rewrite the down to reverse the up exactly

The down migration must undo precisely what its up did: drop what up added, re-add what up dropped (with types), restore what up altered. Where the schema moved on (later migrations changed the same objects), the down must account for the current shape, not the shape at write time.

Expected output: a down migration whose every statement targets objects that exist.

3. Prove it: up then down on scratch

On a fresh database, run the migration up, then immediately down, then up again:

alembic upgrade [rev] && alembic downgrade -1 && alembic upgrade [rev]

(or the equivalent for your tool). Expected output: all three steps succeed, and the schema after the cycle matches the schema after a single up.

4. Add the up/down test to CI

Every migration gets the up-down-up cycle on a scratch database in CI. A migration whose down fails never merges.

Expected output: stale down migrations break the build at pull-request time instead of during a production rollback.

5. Teach the agent the reversibility rule

The agent's migration procedure: no migration is "done" until its down has executed successfully on scratch. Irreversible migrations are declared explicitly and get a compensating plan instead of a fictional down.

Expected output: down migrations stop being write-only.

Tool compatibility

alembic, django, rails, flyway (undo), liquibase (rollback). The up-down-up test pattern maps to every tool.

Variant phrasings

down migration references dropped columns

Steps 1 and 2: diff the down against the current schema, rewrite it to reverse the up exactly.

test rollback migration CI

Step 4: up-down-up on scratch for every migration, in CI.

rollback script never tested fails

Step 3: the up-down-up cycle is the test; run it before trusting any rollback.

Why it happens

Down migrations are written against a schema that keeps changing: later migrations alter the objects the down references, but nobody re-reads the down. The up gets executed constantly (every deploy, every fresh database); the down gets executed only in emergencies, which is the worst time to discover it is stale. Testing is the only thing that keeps the two in sync.

Edge cases

• A down that restores dropped columns restores them empty: data loss is inherent to rolling back a drop. Note it in the migration; the test proves the mechanics, not the data.
• Some downs are only valid immediately after their up (before later migrations build on it): document the valid rollback window, or the test gives false confidence.
• Destructive ups deserve a backup before the up runs, not just a tested down: the down reverses schema, backups reverse data loss.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_SzNHOU8DbgRwDih56qiASA