# Maintain migration history Edit, reorder, and delete unapplied migrations with the integrity file kept true, and repair a dirty revision state after a partial failure. Source: https://docs.ptah.run/v0.8.1/versioned/maintain-history/ Review feedback, a merge race, or a failed deploy means a migration has to change after it was written. This page shows the four maintenance verbs — `edit`, `rebase`, `rm`, and `repair` — that modify history while keeping `ptah.sum` true and applied history protected. The rule all four enforce: **applied history is immutable**. With `--db-url` set, each verb checks the revision table and refuses to touch a migration the database has already run, unless you override with `--force`. Prerequisites: a migration directory sealed with `ptah migrations hash`. The examples use a directory where version `1` is applied and versions `2` and `3` are not: ```text 0000000001_init.up.sql (applied) 0000000001_init.down.sql 0000000002_add_posts.up.sql (pending) 0000000002_add_posts.down.sql 0000000003_add_tags.up.sql (pending) 0000000003_add_tags.down.sql ptah.sum ``` ## Edit a migration `ptah migrations edit` opens a version's up/down pair and rewrites `ptah.sum` afterward, so the edit and the re-hash cannot drift apart. Interactively it uses `$VISUAL`, then `$EDITOR` (or `--editor`); in scripts, `--up-file` and `--down-file` replace a half non-interactively: ```bash ptah migrations edit \ --version 2 \ --up-file ./reviewed_up.sql \ --migrations-dir ./migrations \ --db-url "$DATABASE_URL" ``` Expected output includes: ```text Edited migration 2 Wrote ./migrations/ptah.sum ``` Editing an applied version is refused (exit `2`): ```text error: migration version 1 is already applied; refusing to modify applied history (use --force to override) ``` :::caution When `--db-url` is omitted, the applied-state check is skipped entirely. Pass the database URL whenever one exists, and treat `--force` as a deliberate decision to make the directory disagree with deployed databases. ::: ## Move a migration to the end (rebase) Two branches each added a migration; the merge landed yours below a version that is already applied elsewhere. Instead of hand-renaming files, `ptah migrations rebase` re-timestamps a pending migration to the end of history and rewrites `ptah.sum`: ```bash ptah migrations rebase \ --version 2 \ --migrations-dir ./migrations \ --db-url "$DATABASE_URL" ``` Expected output includes: ```text Rebased migration 2 to 1785255952 ./migrations/1785255952_add_posts.down.sql ./migrations/1785255952_add_posts.up.sql Wrote ./migrations/ptah.sum ``` The pair keeps its description and content; only the version changes, so the migration now applies after everything else. A migration that is already last is refused (exit `2`): ```text error: migration version 1785255952 is already last; rebase would not move it ``` Rebase complements the `--exec-order` policies on [Apply migrations](../apply/): rebase fixes the directory once, execution policy decides how an unfixed out-of-order migration is treated at run time. ### Applied is not the same as published Rebase refuses a migration that the target database has already applied. That check reads one database, and it is the only one Ptah can make: nothing in the directory records where else the migration has been. A migration that has been pushed to a registry is one of those places. The [OCI artifact](../../operate/oci-registry/) is immutable, so renumbering the local files does not rewrite it -- it produces a directory whose migration identities disagree with an artifact someone may already be deploying. Re-hashing afterwards and passing `--verify-sum` does not catch this either: both prove the directory agrees with its own integrity file, which the renumbered directory does. So rebase only what is still local. No single command answers "has this version been published": `ptah oci tags` lists the tags a repository carries, and which versions each one contains takes pulling that reference with `ptah migrations pull` and reading the directory. In practice the pipeline knows -- a version is published once its branch has merged and the publish job has run -- and that is the line to rebase behind. There is no mechanism that refuses a rebase because a version was published. [Deliver a schema change](../../operate/deliver/) states the same boundary from the delivery side: regeneration belongs before the publication step, and promotion reuses a reviewed artifact rather than rebuilding one. ## Delete a migration (rm) `ptah migrations rm` deletes a version's pair and rewrites `ptah.sum`: ```bash ptah migrations rm \ --version 3 \ --migrations-dir ./migrations \ --db-url "$DATABASE_URL" ``` Expected output includes: ```text Removed ./migrations/0000000003_add_tags.down.sql Removed ./migrations/0000000003_add_tags.up.sql Wrote ./migrations/ptah.sum ``` Like the other verbs, it refuses an applied version without `--force`. Deleting applied history strands the databases that ran it — roll the migration back first (see [Roll back migrations](../rollback/)) if the change itself must be undone. ## Repair a dirty revision state A migration that fails partway — typically under `--tx-mode none`, where earlier statements are already committed — leaves a **dirty** revision row. Every later `up` refuses until the state is resolved. The failure looks like this (exit `2`): ```text error: error running migrations: failed to apply migration 5: failed to execute migration SQL: sqlite: SQL execution failed: SQL logic error: no such table: missing_table (1) SQL: INSERT INTO missing_table (id) VALUES (1) ``` `ptah migrations status` names the dirty version, how far it got, and the failing statement: ```text Status: ❌ Dirty migration state detected Dirty Migration: version=5 state=failed direction=up applied=1/2 Error Statement: INSERT INTO missing_table (id) VALUES (1) This migration stopped after 1 of 2 statements. Run 'ptah migrations repair --version 5 --resume-from 2' to run the rest, or repair with --force once you have run them yourself. ``` **The hint reads the row, because each shape wants a different verb.** `applied=1/2` is the one above: the migration changed the database and the rest of it has to run or be run by hand. `applied=0/N` means no statement reached the database, which a transaction that rolled back and a run that never got its lock both leave, and there the answer is `ptah migrations up --allow-dirty` rather than a repair. Repair refuses that row instead of recording a migration that never ran, and `--force` is how an operator who applied it themselves overrides the refusal. That reading of `applied=0/N` holds wherever the count is a witness. On ClickHouse, Oracle and Spanner every statement is durable the moment it runs, so Ptah writes no per-statement witness and zero says only that nothing was recorded: the body may have run in full, in part, or not at all. The hint says so there, repair does not refuse, and what ends the row is to finish the body by hand and then record it. The MySQL family is not in that group -- it keeps its DDL and loses its DML on a rollback, which is why it carries a witness statement by statement and reads zero the same way PostgreSQL does. A run that died while a statement was executing reads `applied=0/N` too, and the hint reads the recorded failure to tell it apart. Whether that statement committed was never recorded, and the statements after it did not run, so no verb finishes the migration on its own: a rerun can repeat what committed, which is why `ptah migrations up --allow-dirty` and `ptah migrations repair --resume-from` both refuse the row. Inspect the database, apply what is missing yourself, then `ptah migrations repair --version ` records the result. A rollback interrupted the same way is recorded with `--force` once you restore what it reverted, or with `ptah migrations set --version ` if you finish the rollback by hand — version 0 where the migration is the oldest one in the directory. `direction` says which body left the row dirty, and repair follows it. Every example on this page is `direction=up`; for `direction=down` see [Finishing an interrupted rollback](../rollback/#finishing-an-interrupted-rollback), where `--resume-from` runs the remaining *down* statements and a finished rollback removes the revision instead of marking it applied. The statement is recorded as it was written. Where one carries bytes that are not valid UTF-8, such as a binary value written as a text literal, each of those bytes is recorded as a `\xFF` escape. The revision table stores this text as UTF-8, and MySQL and MariaDB refuse a value that is not, so the escape keeps the server the only thing that decides whether a statement runs. The rest of the statement is unchanged. An interruption during a `no_transaction` statement is more ambiguous. Before each SQL-backed statement, Ptah durably records the last known completed statement and marks the next statement's outcome as unknown. After success, it advances the progress count and clears the marker. A process exit, context cancellation, or deadline while `ExecContext` is in flight preserves that marker instead of replacing it with an ordinary failure. If the process exits between those writes, the statement may or may not have committed. Inspect the database before changing the revision row. Ptah rejects `--resume-from` while the unknown-outcome marker is present because automatic replay could duplicate committed SQL. Repair without `--resume-from` only after you have reconciled the schema by hand. Fix the migration file (it is unapplied, so `edit` applies), re-hash, and repair. On a `direction=up` row, `--resume-from` executes the remaining up statements — here starting at the second — before marking the migration applied. Before any resume skips committed statements, Ptah verifies their source prefix. Editing the unapplied suffix is allowed; changing or losing the recorded prefix metadata fails closed. Resumed SQL uses the same in-flight marker and per-statement durable checkpoint protocol as the original `no_transaction` run, so a second failure records its absolute progress instead of replaying statements that already committed. The resumed SQL runs on one fresh pinned database session. Ptah replays recognized session-control statements such as `SET search_path` from the verified committed prefix before it runs the unapplied suffix; recognized durable DDL and DML remain skipped. It refuses resume when the prefix created a temporary object, or when a statement may have session-local effects Ptah cannot classify safely. In those cases, inspect and reconcile the database, then choose explicit metadata reconciliation rather than guessing how to reconstruct the old session or replaying migration SQL. Repair also refuses a migration whose selected recovery body contains top-level transaction-control statements. Recovery executes resumed SQL as independent autocommit statements, so accepting a nested `BEGIN`, `COMMIT`, `ROLLBACK`, savepoint, autocommit toggle, or implicit-transaction toggle would make its durable progress counters untrustworthy. `RepairMigration` acquires the same session advisory lock as migration up and down. It holds the lock across revision inspection, resumed SQL, safety checks, and the final metadata write. The native repair command currently uses the default lock name and waits indefinitely. ```bash ptah migrations repair \ --version 5 \ --resume-from 2 \ --migrations-dir ./migrations \ --db-url "$DATABASE_URL" ``` Expected output includes: ```text Repaired migration 5 ``` Status then reports the version as applied and the directory continues normally. Without `--resume-from`, `repair` resolves the revision row for state you have already fixed by hand in the database. Repairing a version that is not dirty is refused (exit `2`): ```text error: migration 1 is not dirty; rerun with --force to rewrite it ``` `--force` rewrites (or creates) the revision row anyway — a last resort for reconciling revision metadata that no longer matches reality. ### Repair over a half-built concurrent index On PostgreSQL, a `CREATE INDEX CONCURRENTLY` that fails partway leaves an **invalid** index behind. The leftover keeps the name, so the generated `IF NOT EXISTS` form of the same statement is skipped rather than retried and reports no error. Repair refuses to record such a migration (exit `2`): ```text error: migration 5 cannot be repaired: PostgreSQL reports index "public"."idx_members_email" (indisvalid=false, indisready=false) unusable, so recording the migration applied would report a constraint that is not enforced; run REINDEX INDEX CONCURRENTLY "public"."idx_members_email", or drop the index and rerun the migration, then repair again ``` Refusing leaves a dirty state you can still see, rather than a green one that is wrong: an invalid unique index enforces nothing, so duplicate rows keep being accepted while `status` reports the database up to date. Rebuild the index with the `REINDEX INDEX CONCURRENTLY` the message names — or drop it and rerun the migration — and repair again. `--force` does not bypass this. It relaxes a precondition about the revision row; the index being unusable is a fact about the database, and the fix for it is `REINDEX`. Only indexes named by conditional creates in the selected direction are checked, so an unrelated invalid index elsewhere never blocks a repair and an unconditional create retains PostgreSQL's normal error semantics. Other dialects have no concurrent index build to leave half-finished and are unaffected. `ptah migrations up` refuses on the same grounds, so `--allow-dirty` cannot be used to walk past it either. Ordinary rollback also checks conditional creates in its down body before deleting the revision. A failed transactional check rolls the body back; a failed `none` check keeps a dirty down-direction row with its completed progress. See [Apply migrations](../apply/#failure-modes). ## Set the revision boundary (set) `repair` fixes one dirty row and `baseline` only records existing history as applied. `ptah migrations set` moves the whole revision boundary to an arbitrary version, in both directions, without executing any SQL: every migration through `--version` is recorded as applied (dirty rows are marked applied, missing rows are inserted), and revision rows above `--version` are removed. ```bash ptah migrations set \ --version 5 \ --migrations-dir ./migrations \ --db-url "$DATABASE_URL" ``` Expected output includes: ```text Current version is 5 (2 set, 1 removed): + 4 (add_orders) + 5 (add_index) - 6 (drop_legacy) ``` Each line names the row by the identity the revision table holds, which is the string that finds it: an Atlas repeatable is `R` or `3R`, not the number beside it. The removals are listed in ascending order. `--version 0` names the state where no migration is applied. It removes every revision row and records none, and it says so instead of naming a version the migration directory has no file for: ```text No migration is recorded as applied (2 removed): - 1 (create_users) - 2 (add_orders) ``` That is what clears the row a rollback finished by hand leaves behind when the migration it reverted was the oldest one in the directory: `--force` would record that migration applied, which is the opposite outcome. A negative version is refused. This is a metadata-only operation for databases whose schema was changed outside the migration flow. It never runs or reverts migration SQL — the database schema itself is untouched. `--revision-format atlas` targets Atlas revision bookkeeping (`atlas_schema_revisions`) instead of Ptah's native table, and `--dry-run` validates the inputs without changing anything. Rows that Ptah writes in Atlas revision mode preserve Atlas's filename description, empty successful error fields, checksum hash, and operator metadata. Missing rows created by this metadata-only operation store the write timestamp with zero duration; existing rows keep their timing metadata. When Ptah executes migration SQL, it instead stores the migration lifecycle start and full elapsed duration in nanoseconds. Atlas CE can read both forms, but exact dynamic timing equality is not claimed: Atlas CE v1.2.0 can persist a near-final timestamp and write-order-dependent duration. PostgreSQL-family revision tables use `TIMESTAMPTZ` for `executed_at`, matching Atlas-created tables. ## Who owns the metadata tables Ptah creates the revision table and the operation log with `CREATE TABLE IF NOT EXISTS`, so a table already standing under that name would be adopted whatever put it there. A run refuses a metadata table the connecting role does not own, before anything reads or writes it: ```text refusing to use metadata table schema_migrations: it is owned by "someone_else" and this connection runs as "app", so it is not the table Ptah would have created. ``` The owner has to be the connecting role itself. A group role the application merely belongs to is not an answer: every other member of that role can change the table, which is the arrangement the refusal exists to catch. Where a table has to stay owned by another role, `PTAH_ALLOW_FOREIGN_METADATA_TABLE=1` accepts it as it stands. The refusal does not tell you to transfer the table. `ALTER TABLE ... OWNER TO` keeps the triggers, defaults and policies it carries, and the check would then accept it because the owner matches, so the remedy would be the last step of what it refused. Move any rows worth keeping and let Ptah create the table. The refusal is ownership rather than a list of what a table may carry: on the PostgreSQL family a trigger, a rule, a default expression on a column Ptah does not write and a row-level policy each run somebody else's SQL under the migration role, and provenance covers the next one too. Engines whose catalog cannot answer are outside it: the MySQL family, SQL Server, SQLite, ClickHouse and Spanner. On MySQL and MariaDB a trigger runs as its definer rather than as the connected account, so the same table gains its author a hook on every migration and not the migration role's privileges. SQL Server records no creator for an ordinary object -- the catalog reports the schema's owner -- so the answer there would say nothing about who made the table. The check covers a dry run too, because a dry run reads the table, and it runs again after the create, so a table placed between the two statements is caught rather than adopted. A name the target would truncate is refused, because the DDL and the writes would address one table while every catalog lookup named another. An overlong revision table or schema stops the run; an overlong derived log name only turns the log off for it, since the work itself is still recordable. Reading the log is refused the same way: a policy or an expression the server evaluates during a `SELECT` runs the other role's SQL with the reader's privileges, so a read is not safe by being a read. A foreign **log** table warns and the migration runs. Ptah cannot record what it did without a revision table, so that refusal is terminal; the log is a record beside the work, and failing the run would hand anyone who can create a table in the metadata schema a way to stop every migration. ## Atlas-compatible surface In the `ptah-compat` drop-in binary, `migrate edit`, `migrate rebase`, and `migrate rm` forward to these native commands for drop-in Atlas familiarity, with `--dir` mapping to the migrations directory. Its `migrate set` is the Atlas spelling of `ptah migrations set` with Atlas revision bookkeeping preselected. Squashing history is its own verb pair — see [Checkpoints](../checkpoints/). ## Next steps - History too long rather than wrong? [Checkpoints](../checkpoints/). - Undoing an applied migration instead of editing a pending one? [Roll back migrations](../rollback/). - Keeping edits honest in CI? [Integrity and safety](../integrity-and-safety/).