how-to

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.

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:

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:

ptah migrations edit \
  --version 2 \
  --up-file ./reviewed_up.sql \
  --migrations-dir ./migrations \
  --db-url "$DATABASE_URL"

Expected output includes:

Edited migration 2
Wrote ./migrations/ptah.sum

Editing an applied version is refused (exit 2):

error: migration version 1 is already applied; refusing to modify applied history (use --force to override)

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:

ptah migrations rebase \
  --version 2 \
  --migrations-dir ./migrations \
  --db-url "$DATABASE_URL"

Expected output includes:

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):

error: migration version 1785255952 is already last; rebase would not move it

Rebase complements the --exec-order policies on Apply migrations: 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 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 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:

ptah migrations rm \
  --version 3 \
  --migrations-dir ./migrations \
  --db-url "$DATABASE_URL"

Expected output includes:

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) 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):

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:

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 <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 <previous> 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, 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.

ptah migrations repair \
  --version 5 \
  --resume-from 2 \
  --migrations-dir ./migrations \
  --db-url "$DATABASE_URL"

Expected output includes:

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):

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):

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.

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.

ptah migrations set \
  --version 5 \
  --migrations-dir ./migrations \
  --db-url "$DATABASE_URL"

Expected output includes:

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:

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:

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.

Next steps

History too long rather than wrong? Checkpoints.
Undoing an applied migration instead of editing a pending one? Roll back migrations.
Keeping edits honest in CI? Integrity and safety.