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

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