Migration Runner: Version Order, Drift, Undo and Repeatables

Migration Runner: Ordering, Drift, Undo, Baselines and Repeatables

You've joined the platform team that owns dbmigrate, the tool every service uses to roll schema changes forward. It works like Flyway or Alembic, except that the database is simulated: running a migration just means "record it in the history table". Last week staging applied V2_10__add_audit *before* V2_9__add_index and nobody noticed until the audit table's index was missing in production. That incident is Part 1. Parts 2 and 3 are the roadmap behind it.

Codebase

How your code is run

run(operations: list[list]) -> list executes the operations in order against an empty directory and an empty history, and returns one result per operation. Every operation is [name, *args].

• Errors of add_file, remove_file, generate (and baseline) are returned as "error: <message>". migrate and undo never return a bare error string: their failure message goes in the result's "error" field (no "error: " prefix) next to whatever was applied or undone before the failure.
• "error: unknown operation" for an unknown name. "error: invalid arguments" for a wrong number of arguments, a wrong argument type, a non-list operation or an empty list. (true/false are not integers; allow_out_of_order must be a real bool; generate needs major >= 0 and count >= 1.)
• add_file checks in this order: arguments, invalid file name <name>, duplicate version <v>. remove_file of a file that is not there returns "error: no such file <name>".
• A label names a migration in results and messages: V2.3 for a versioned migration (V plus its canonical version), R__views for a repeatable one.
• A history row is {"rank", "type", "version", "description", "checksum", "success"}. type is "V" for now; version is the canonical version string.

File names and versions. V2_3__add_index.sql is the up migration of version 2.3 and U2_3__add_index.sql its down migration. R__views.sql is a repeatable migration. The description is [A-Za-z0-9_]+; any other file name is invalid. Versions are integer components separated by _ in file names and . everywhere else, and are compared component by component as integers, so 2.10 is newer than 2.9. A version's *canonical text* drops leading zeros and trailing zero components (V02_0 and V2 are both "2"; V1_10_0 is "1.10"; V1_05 is "1.5"). Two V files, or two U files, with the same canonical version cannot coexist (duplicate version 2). dbmigrate/versions.py has the grammar. R and U files exist in every Part but migrate ignores them until Parts 2 and 3 give them meaning.

Checksums. A file's checksum is the rolling hash in dbmigrate/checksum.py of its *normalized* text: \r\n becomes \n, trailing spaces and tabs are removed from every line, and trailing blank lines are removed. Leading whitespace and blank lines at the start are significant, and so is everything in the middle. "" hashes to "00000000", "a" to "00000061", "ab" to "00000c21".

Simulated failure. If any line of a file (after stripping it) starts with --!fail, executing that file fails and the detail is the rest of that line (statement rejected if it is empty). The raw text is inspected, not the normalized text.

---

Part 1: Make migrate trustworthy

migrate must apply every pending versioned migration, in version order, and refuse to do so when the directory and the history disagree. The current code gets several of the rules below wrong; the rest are rules you need to preserve. Find out which is which by reading the code and the failing test.

1. Failed rows block. If the history has any failed row, migrate applies nothing and returns blocked: <label of the lowest-rank failed row> failed, run repair. Check this first.
2. Drift. For every successful applied row whose file still exists, the file's current checksum must equal the checksum recorded when it was applied. Otherwise migrate applies nothing and returns drift: V<version> (<description>) was applied with checksum <recorded> but the file now has <current> for the drifted file with the lowest version. An applied file that was deleted is not drift.
3. Pending means a V file with no successful row for its version.
4. Out of order. *Latest applied* is the highest version among successful rows (not the most recent row). If any pending file is older than it, migrate applies nothing and returns out of order: V<lowest such pending version> is older than applied V<latest>, unless allow_out_of_order is true.
5. Pending files are applied one at a time in version order (late ones included, so with allow_out_of_order the late files go first). Each application appends a row with the file's checksum at that moment. If the file fails, its row is recorded with success: false, migrate stops and returns the labels applied so far plus the error <label> failed: <detail>, e.g. V2 failed: duplicate column.
6. Checks 1 to 4 run in that order and the first one that triggers wins; nothing is applied in those cases.
7. Ranks are 1, 2, 3, ... in the order rows were appended and are never reused, even after repair removed the row that held one.
8. repair removes every failed row (so the failed version is pending again and the next migrate retries it) and returns their labels in rank order. Editing a file whose row failed is not drift.

Worked example (checksums are shown so you can verify your hash)

[["add_file", "V2_10__add_audit.sql", "CREATE TABLE audit (id INT);"],
 ["add_file", "V2_9__add_index.sql", "CREATE INDEX ix ON t (id);"],
 ["add_file", "V1__init.sql", "CREATE TABLE t (id INT);"],
 ["migrate"],
 ["history"]]

returns

["7bbff389", "bee6ff47", "10b471e2",
 {"applied": ["V1", "V2.9", "V2.10"], "error": null},
 [{"rank": 1, "type": "V", "version": "1", "description": "init", "checksum": "10b471e2", "success": true},
  {"rank": 2, "type": "V", "version": "2.9", "description": "add_index", "checksum": "bee6ff47", "success": true},
  {"rank": 3, "type": "V", "version": "2.10", "description": "add_audit", "checksum": "7bbff389", "success": true}]]

And after ["migrate"] applied V1__init.sql, replacing it with CREATE TABLE t (id BIGINT); and migrating again returns {"applied": [], "error": "drift: V1 (init) was applied with checksum 10b471e2 but the file now has 02871c20"}.

---

Part 2: Undo

Releases sometimes have to be rolled back. Teams ship a down migration next to each up migration (U2_3__add_index.sql undoes V2_3__add_index.sql). Add ["undo", target], where target is a dotted version like "2.9" (leading zeros and trailing zero components are fine: "02.09", "2.10.0"), which reverts every applied migration newer than target. ["undo", "0"] reverts everything. It returns {"undone": [labels, in the order they were undone], "error": null or message}.

1. A target that is not a dotted version ("x", "2.", "") returns {"undone": [], "error": "invalid version <target>"}; a non-string argument is "error: invalid arguments".
2. If the history has a failed row: blocked: <label> failed, run repair (same rule as migrate).
3. The migrations to revert are the successful V rows with a version greater than the target, reverted in descending version order. (With out-of-order history, rank order is not version order.)
4. Every one of them needs a down file with the same canonical version. If any is missing, nothing is run and the result is missing down migration for V<version>, naming the first missing one in revert order (the highest). Undo checks 1 to 4 in the order given.
5. Each down file runs in turn. On success the version's history row is deleted (ranks of deleted rows are not reused) and its label is added to undone. If a down file fails, that version's row is kept but flipped to success: false, undo stops, and the error is undo of V<version> failed: <detail>; the earlier reverts stay done. As for any failed row, migrate is then blocked until repair, which removes the row, so the version counts as not applied.
6. Undo does not look at up files at all: drift in them and missing up files are irrelevant. Down files are read when the undo runs, and their checksums are never recorded.
7. After an undo, versions are pending again, and migrate's rules from Part 1 apply to the shrunken history (the latest applied version may now be lower).

Worked example

[["add_file", "V1__init.sql", "CREATE TABLE t (id INT);"],
 ["add_file", "V2__users.sql", "CREATE TABLE users (id INT);"],
 ["add_file", "V3__orders.sql", "CREATE TABLE orders (id INT);"],
 ["add_file", "U2__users.sql", "DROP TABLE users;"],
 ["add_file", "U3__orders.sql", "DROP TABLE orders;"],
 ["migrate"], ["undo", "1"], ["history"], ["migrate"], ["history"]]

The undo returns {"undone": ["V3", "V2"], "error": null} and the first history is only the rank-1 row for V1. The second migrate applies ["V2", "V3"] and the final history lists V1 at rank 1, V2 at rank 4 and V3 at rank 5. With only U3 present (no U2), ["undo", "0"] returns {"undone": [], "error": "missing down migration for V2"} and changes nothing, while ["undo", "2"] succeeds.

---

Part 3: Baselines, repeatables, and big repos

Two requirements from the teams adopting the tool, and a scale problem that came with them.

Baseline. Teams with an existing database want to adopt dbmigrate without replaying history. ["baseline", version] records "everything up to this version is already in the database".

1. version is dotted like the undo target. Not a dotted version: "error: invalid version <version>" (non-string: invalid arguments). If the history already has any row at all: "error: history not empty". Checked in that order. Success returns true and appends the row {"type": "B", "version": <canonical>, "description": "baseline", "checksum": null, "success": true}.
2. Every V file whose version is at or below the baseline is considered applied without ever running: migrate never makes it pending, never reports it as out of order and never checks it for drift, no matter when the file is added or edited. It can never be undone.
3. undo with a target below the baseline returns cannot undo below baseline <baseline> (checked after the blocked check, before the missing-down check). A target at or above it is fine.

Repeatable migrations. R__views.sql holds things like views and stored procedures that should be re-created whenever their definition changes.

1. After all pending versioned migrations of a migrate call have succeeded, every repeatable that is *pending* is applied, in order of description (plain string comparison, so B_x comes before a_x). A repeatable is pending if it has no successful row or its current checksum differs from the checksum of its newest successful row. Applied repeatables appear in applied as R__<description> after the versioned labels. Their rows have type: "R" and version: null.
2. If anything stops the run earlier (blocked, drift, out of order, a failing versioned migration) no repeatable runs. A failing repeatable is recorded as a failed row, ends the run with R__<description> failed: <detail>, and blocks later runs like any failed row (blocked: R__views failed, run repair); the repeatables after it do not run. repair removes it; the next migrate retries it. A changed repeatable file is never drift. A deleted one is ignored.
3. Repeatable and baseline rows are not versioned migrations: they must not count as "latest applied", as drift candidates, or as something undo can revert.
4. Undo invalidates repeatables. Views depend on tables, so whenever an undo reverted at least one version (including one that then stopped on a failing down file), the recorded checksum of every R row is set to null in the history, which makes all repeatables pending again. An undo that reverted nothing changes nothing.
5. Scale. Real repos have hundreds of files and migrate runs on every deploy. A run that applies nothing new must cost little: do not re-hash every file or re-scan the whole history for every file on each call. The suite includes a repo with 500 applied migrations and dozens of deploys, and the whole suite has to finish in a few seconds.

Everything from Parts 1 and 2 still holds, with the new row types in the history.

Worked example

[["add_file", "V1__init.sql", "CREATE TABLE t (id INT);"],
 ["add_file", "R__views.sql", "CREATE VIEW v AS SELECT 1;"],
 ["migrate"], ["migrate"],
 ["add_file", "R__views.sql", "CREATE VIEW v AS SELECT 2;"],
 ["migrate"], ["history"]]

returns {"applied": ["V1", "R__views"], "error": null}, then {"applied": [], "error": null}, then (after the edit) {"applied": ["R__views"], "error": null}, and a history of the V1 row (rank 1), an R row for views with checksum ca2c9a81 (rank 2) and another with ca2c9aa0 (rank 3).

Baselining: with V1, V2, V3 in the directory, ["baseline", "2"] returns true, the next migrate returns {"applied": ["V3"], "error": null}, and the history is the baseline row at rank 1 and V3 at rank 2.