migration agent lost track of which revision was applied after the database connection dropped mid-run

TL;DR: Do not trust the agent's memory - ask the database. Every migration tool records applied revisions in its own version table, and that table is the ground truth. Read it, compare it against the migration files, check for an orphaned session holding locks from the dropped connection, then re-run the upgrade - the tool resumes from the recorded version on its own.

migration agent lost track of which revision was applied after the database connection dropped mid-run

1. Read the ground truth: query the tool's version table directly - alembicversion for alembic, flywayschemahistory for flyway, the prisma migrations table for prisma, djangomigrations for Django, schema_migrations for Rails.

Expected: a definitive list of applied revisions with timestamps.

1. Compare against the migration files on disk: the newest row in the version table is the last applied revision; everything after it in the files is pending.

Expected: you can name exactly which revisions still need to run.

1. Check for an orphaned session from the dropped connection still holding locks or an open transaction.

Expected: either no orphaned session (the server already reaped it) or one you terminate before proceeding.

1. Verify the in-flight revision: if the version table has no row for the revision that was running when the connection dropped, its transaction rolled back (on databases with transactional DDL) and it is pending.

Expected: a clear applied-or-pending verdict for the interrupted revision.

1. Re-run the upgrade command for your tool (upgrade head, migrate, migrate deploy).

Expected: it applies only the pending revisions and finishes cleanly.

Use this when

• the database connection dropped mid-migration and the agent lost its place
• agent memory of applied revisions disagrees with reality
• a new agent session takes over a migration another session started

Not for this skill when

• the connection is still down - restore connectivity first
• a revision is recorded as applied but its changes are missing - that is a deeper inconsistency needing manual repair
• the migration failed with a data error, not a connection drop - fix the data

Variant phrasings

• connection dropped mid-migration, which revisions applied
• agent lost migration state after disconnect
• how to tell what migrated after connection failure

Why it happens

The agent tracks progress in its own context - a plan, a checklist, a memory of "I ran these". A dropped connection invalidates all of it: the last command may or may not have committed, and the agent cannot tell from its side. The migration tool, meanwhile, writes each applied revision to its version table transactionally with the migration itself, so the table always reflects reality. Agents that re-derive state from their own logs instead of reading the version table end up double-applying or skipping revisions.

Edge cases

• On databases without transactional DDL, a dropped connection can leave a half-applied revision with no version-table row - inspect the schema objects directly before re-running.
• A version-table row with a failed or pending status (prisma, flyway) needs explicit resolution - do not just re-run over it.
• If the agent's plan file says "done" but the version table disagrees, the version table wins - update the plan from the table, not the other way around.
• Write the version-table contents into the handoff notes whenever a migration session ends, so the next session starts from ground truth.

Provenance

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