agent scheduled migration ran twice: double-applied

Not specified by the original author.

agent scheduled migration ran twice: double-applied

TL;DR

The scheduled migration fired twice (retry plus schedule, or two schedulers), and it was not idempotent, so effects doubled. Make migrations idempotent first; then dedupe the trigger. Double-run is a matter of time; idempotency is the defense.

agent scheduled migration ran twice: double-applied

Steps

1. Confirm the double application: duplicated rows, double charges, repeated side effects. Expected: The doubling is proven.

2. Repair the data URIs dedupe or reverse the second application carefully. Expected: The state is correct.

3. Make the migration idempotent: re-running it must be a no-op. Expected: The class of bug is fixed.

4. Dedupe the trigger: one scheduler, retry-aware, with run locking. Expected: Double fires stop.

5. Add a migration ledger: record completed migrations and skip recorded ones. Expected: The ledger is the backstop.

When to use

• a migration applies twice
• scheduled jobs retry
• the migration is not idempotent

When not to use

• the migration is idempotent (then double-run is harmless; still fix the trigger)
• it ran once but looks doubled (check the data)
• the duplication is in the report, not the data (check)

Tool compatibility

• any scheduled migration
• idempotency patterns

Variant phrasings

• "migration ran twice"
• "scheduled migration double applied"
• "migration not idempotent double run"

Why it happens

Schedulers retry, clocks skew, and deploys overlap: the same migration firing twice is normal operating conditions, not a freak event. A non-idempotent migration turns 'twice' into 'corrupted'. Idempotency is the primary fix; trigger dedup is secondary. The ledger makes both verifiable.

Edge cases

• the repair needs care; reversing a half-doubled migration is delicate
• the ledger must be transactional with the migration or it lies
• test double-run explicitly; it is a first-class scenario

Provenance

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

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

Thread query: agent scheduled migration ran twice: double-applied

TL;DR: The scheduled migration fired twice (retry plus schedule, or two schedulers), and it was not idempotent, so effects doubled. Make migrations idempotent first; then dedupe the trigger. Double-run is a matter of time; idempotency is the defense.

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.

Not specified by the original author.

TL;DR: Postgres resolves the deadlock itself by aborting one transaction, so first find out which instance won by reading the migration state table. Stop the loser, finish the plan with exactly one runner, then add a serialization guard - an advisory lock, a CI concurrency group, or a deploy mutex - so two agents can never race again.

two agent instances ran the migration at the same time - both tried to apply the same revision and one deadlocked

1. Identify both runners and stop the one that lost. Check the agent harness for the two process or run ids, and confirm which one got the deadlock error.

Expected: one runner confirmed dead or stopped, one runner (or none) still alive.

2. Read the migration state table to learn exactly where the surviving transaction got to. Alembic: SELECT versionnum FROM alembicversion. Flyway: SELECT version, success FROM flywayschemahistory ORDER BY installed_rank DESC LIMIT 3.

Expected: a definitive applied list. The deadlock abort rolled back the loser's in-flight work, so the state table reflects only committed migrations.

3. If the winner died too (for example the harness killed both), re-run the remaining migrations with exactly one instance.

Expected: a single runner applies the pending migrations and the state table matches the file list.

4. Verify the schema has no partial damage: compare the final schema against the migration intent, and check for duplicate objects the two runners may have both created outside transactions.

Expected: no duplicate tables, indexes, or constraints; schema matches the plan.

5. Add a mutual-exclusion guard before the next run. Options: wrap the migration command in pgadvisorylock, set a CI concurrency group on the migration job, or use a deploy mutex so only one deploy migrates at a time.

Expected: you can start two runs and watch the second wait or exit instead of racing.

Use this when

• Two agents, deploys, or CI jobs ran the same migration at the same time
• One runner died with a deadlock error mentioning the other
• The migration state table and the agents' memories disagree
• You need a single-runner guarantee for future migrations

Not for this skill when

• Only one runner ever ran and the deadlock came from application traffic - that is a lock-ordering problem, not a duplicate-runner problem
• The two instances were intentionally running different migrations - serialize only what must be serial
• The database does not do DDL transactionally - partial DDL from the loser may need manual cleanup beyond re-running
• You want parallel data backfills - those need partition-based coordination, not a global mutex

Variant phrasings

• two agents ran migration at same time deadlock
• concurrent db migrate deadlock, which one won
• duplicate migration runners same revision
• how to prevent two deploys migrating at once

Why it happens

Two writers applying the same DDL take catalog locks in slightly different orders, and Postgres detects the cycle and aborts one of them with a deadlock error. Most migration tools assume they are the only writer - their locking (when they have any) is per-migration or advisory, and two separate agent processes do not share it. The loser rolls back cleanly, but the agents' logs now tell two conflicting stories, and only the state table knows the truth.

Edge cases

• The deadlock error text names the aborted query, not the survivor. Read the state table; do not trust either agent's log alone.
• If both runners were killed by the harness, nothing applied after the last committed migration - safe to re-run singly, but verify first.
• Advisory-lock guards only work if every runner uses them. One bare runner bypasses the guard, so enforce it in the shared runbook or CI job, not in each agent's memory.
• On MySQL, the loser may leave partial DDL behind since DDL is not transactional there. Inspect the schema for half-created objects before re-running.

Provenance

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

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

Thread query: two agent instances ran the migration at the same time - both tried to apply the same revision and one deadlocked

TL;DR: