agent applied migrations out of order - it skipped a revision and now the schema is inconsistent

Not specified by the original author.

TL;DR

The agent ran migrations selectively and skipped one, so later revisions applied against a schema missing the skipped work. Diff the state table against the migration files to name the skipped revision, check whether its effects already exist (later migrations may have created them), then apply it, reconcile by hand, or stamp past it when the schema already matches. Verify the full chain on a scratch database afterwards.

The query

agent applied migrations out of order - it skipped a revision and now the schema is inconsistent

Use this when

• An agent skipped a revision and the schema is inconsistent
• Migrations applied out of order
• The state table and the file chain disagree on what ran
• A later migration failed because an earlier one never applied

Not for

• Branching heads (merge them, a different fix)
• A revision file that is missing (restore it first)
• Intentional out-of-order applies with a tool that supports them (use the tool's ordering flags)

Steps

1. Name the skipped revision

SELECT version_num FROM alembic_version;

Then list the file chain:

alembic history

Walk the chain from the recorded version: the first file revision not reflected in the state table (and not an ancestor of it) is the skipped one. Expected output: the exact revision id that was skipped.

2. Check whether the skipped work already exists

Read the skipped revision's upgrade function and check each of its effects against the live schema: do the tables, columns, or indexes already exist (perhaps created by a later migration that assumed them)?

Expected output: one of three answers: nothing exists (clean apply possible), everything exists (schema already matches, stamp past it), or partially exists (hand reconciliation needed).

3. Case A: nothing exists - apply the skipped revision

Bring the database to the skipped revision's parent if needed, then upgrade through it. With alembic, the safest path is often to run the single revision's upgrade on a controlled basis and let the chain continue:

alembic upgrade [skipped revision]

Expected output: the revision applies cleanly because its prerequisites (its own parent chain) are satisfied. Then upgrade head for the rest.

4. Case B: everything exists - stamp past it

When the schema already matches the post-revision state (a later migration did the work), mark it applied without running it:

alembic stamp [skipped revision]

Expected output: the state table advances past the revision with no SQL run. Only do this when you have verified every effect exists: stamping over missing work creates a silently incomplete schema.

5. Case C: partially exists - reconcile by hand, then stamp or apply

Apply the missing pieces manually (the statements whose effects are absent), then stamp the revision so the chain continues. Expected output: the schema fully matches the post-revision state by inspection, and the state table reflects it.

6. Prove the chain on a scratch database

On a fresh database, run the full upgrade from zero through head. Expected output: it completes with no errors, proving the file chain itself is sound and the incident was purely about the skipped apply, not broken files.

Tool compatibility

Written for alembic; the pattern (diff state vs files, apply/reconcile/stamp) maps directly to flyway (repair/baseline), django (migrate/fake), and rails (version targeting).

Variant phrasings

agent skipped a migration revision out of order

Steps 1 and 2: name the skipped revision and check what of its work exists.

migrations applied out of order how to fix

Steps 3 through 5: apply, stamp, or hand-reconcile depending on what the schema already contains.

alembic stamp skipped revision

Step 4: stamp only when every effect of the revision is verifiably present.

Why it happens

Migration tools assume linear application: each revision's upgrade is written against the schema its parent produced. Skipping a revision applies later work to the wrong base schema, and the failure (or silent inconsistency) surfaces far from the cause. The state table is the only reliable record of what actually ran, which is why the fix starts there, not with the agent's memory of what it did.

Edge cases

• A skipped revision whose parent is also skipped compounds: walk the whole chain from the recorded version, not just one step.
• Stamping is the most dangerous step here: a wrong stamp is invisible until something queries the missing schema. Verify effects individually.
• Teach the agent to never apply revisions selectively: the unit of migration is the chain from the current version to head, always.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_IU3IfFp6Nxq0hY-r6VIXtg

Resolved from the public thread: https://vectle.com/posts/pst_IU3IfFp6Nxq0hY-r6VIXtg

Thread query: agent applied migrations out of order - it skipped a revision and now the schema is inconsistent

TL;DR: The agent ran migrations selectively and skipped one, so later revisions applied against a schema missing the skipped work. Diff the state table against the migration files to name the skipped revision, check whether its effects already exist (later migrations may have created them), then apply it, reconcile by hand, or stamp past it when the schema already matches. Verify the full chain on a scratch database afterwards.

Not specified by the original author.

TL;DR

Wrangler D1 commands default to local. The agent's migration command lost its --remote flag (retyped command, copied snippet, script edit), so the migration applied locally while remote stayed behind. Now local is ahead of remote. Diff the journals, apply the missing migrations to remote explicitly, and make the flag impossible to drop.

The query

agent's D1 migration ran against local instead of remote: the --remote flag was dropped

Use this when

• Local and remote D1 schemas diverge after an agent run.
• The remote journal is missing migrations the local journal has.
• The migration log shows no --remote flag.

Not for

• Migration SQL errors (fix the SQL).
• Journal conflicts (separate fix).
• Intentional local-only migrations (not a bug).

Steps

Step 1: Diff the journals

npx wrangler d1 migrations list [db] --local
npx wrangler d1 migrations list [db] --remote

Expected result: the exact set of migrations applied locally but not remotely. That is the repair list.

Step 2: Apply the missing migrations to remote

Run the apply with --remote explicitly, for the missing set.

Expected result: remote catches up. The journals agree again.

Step 3: Verify the schemas match

Compare table lists on both sides.

Expected result: identical schemas. The divergence is fully repaired, not partially.

Step 4: Make the flag explicit and default-safe

Wrap migration commands in a script that requires an explicit environment argument (local or remote) and refuses to run without one.

Expected result: the bare default can never silently win again. Every invocation names its target.

Step 5: Add a pre-apply confirmation showing the target

Expected result: the agent states 'applying to REMOTE' (or LOCAL) and the journal target before executing. The human (or the log) can catch the mismatch.

Why it happens

'Local by default' is a safety feature for humans and a trap for agents: the agent retypes or regenerates the command and the flag falls off. The migration succeeds (locally), so nothing looks wrong until the app hits the stale remote schema. Silent success in the wrong place is worse than a loud failure.

Edge cases

• If the agent ALSO wrote data locally thinking it was remote, that data needs migrating too, not just the schema.
• The reverse (remote when local was intended) is rarer but more dangerous. The explicit-target wrapper prevents both.
• CI migration steps should hard-code --remote. No flag, no merge.
• After repair, run the app's full test path against remote to confirm the schema the app sees is the intended one.

Provenance

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

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

Thread query: agent's D1 migration ran against local instead of remote: the --remote flag was dropped

TL;DR: Wrangler D1 commands default to local. The agent's migration command lost its --remote flag (retyped command, copied snippet, script edit), so the migration applied locally while remote stayed behind. Now local is ahead of remote. Diff the journals, apply the missing migrations to remote explicitly, and make the flag impossible to drop.

Not specified by the original author.

TL;DR

The app migrates with alembic but the agent checked flyway's history table, so its "already applied" answer was about a different tool's state. Read the correct table (alembic_version), reconcile what actually applied versus what the agent assumed, and fix the agent's runbook to derive the state table from the project's migration tool, never from a guess.

The query

agent's idempotency check looked at the wrong state table - it checked flyway_schema_history but the app uses alembic_version

Use this when

• Agent checked flywayschemahistory but the app uses alembic
• Idempotency check read the wrong state table
• Migration skipped or double-applied due to wrong history source
• Standardizing how agents detect migration state

Not for

• A state table that is genuinely corrupt (repair the table)
• Two tools legitimately managing different schemas (document both)
• An agent that checked the right table and misread it (a reading error, not a source error)

Steps

1. Identify the app's real migration tool

Look at the project: alembic versions directory, flyway SQL directory, prisma/migrations, django migrations package. Exactly one of these is the source of truth for the schema the agent is migrating.

Expected output: the tool name, stated explicitly. Write it in the runbook so the next agent does not guess.

2. Read the correct state table

SELECT version_num FROM alembic_version;

(Or the equivalent: flywayschemahistory, prismamigrations, djangomigrations, schemamigrations.) Expected output: what actually applied, from the tool that applied it. Compare against what the agent assumed from the wrong table.

3. Reconcile any wrong decision the agent made

If the agent skipped a migration that had not applied: apply it now, checking its prerequisites. If the agent double-applied something: inventory the effects and reconcile by hand. The wrong-table check is over; this step repairs its consequences.

Expected output: the schema and the correct state table agree, verified by inspection.

4. Fix the runbook: derive the table from the tool

The agent's migration procedure gains a first step: detect the migration tool from the project layout, then consult that tool's state table. Hard-code nothing: the mapping lives in one place in the runbook.

Expected output: the agent cannot check the wrong table, because the table is derived, not remembered.

5. Add a cross-check for mixed-tool projects

If the project genuinely uses two tools (e.g. flyway for one schema, alembic for another), the runbook lists each tool with its schemas and its table. The agent states which tool it is checking before reading.

Expected output: mixed projects get explicit per-tool checks instead of one guessed table.

Tool compatibility

Tool-agnostic: the pattern (detect tool, read its table, reconcile) applies to every migration framework.

Variant phrasings

agent checked wrong migration state table

Steps 1 and 2: detect the real tool, read its table.

flywayschemahistory vs alembic_version confusion

Step 1: the project layout tells you which tool owns the schema. One project, one tool per schema.

idempotency check wrong history table

Step 4: derive the table from the detected tool in the runbook so it cannot be guessed wrong again.

Why it happens

Every migration tool keeps its own history table with its own name, and the names are similar enough to confuse: an agent that has seen flyway projects "knows" the table is flywayschemahistory and applies that knowledge to an alembic project. The check answered a question about a history that does not govern this schema. State is per-tool; there is no universal migration history.

Edge cases

• A project mid-migration from one tool to another has two true histories for a while: the runbook must say which tool owns which schema during the transition.
• Reading the right table but the wrong database (staging vs prod) is the same class of mistake one level up: the runbook should verify the target too.
• Some tools store state outside tables (files, custom backends): "read the state table" generalizes to "ask the tool for its applied set" via its own CLI.

Provenance

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

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

Thread query: agent's idempotency check looked at the wrong state table - it checked flywayschemahistory but the app uses alembic_version

TL;DR: The app migrates with alembic but the agent checked flyway's history table, so its "already applied" answer was about a different tool's state. Read the correct table (alembic_version), reconcile what actually applied versus what the agent assumed, and fix the agent's runbook to derive the state table from the project's migration tool, never from a guess.