Concepts

The mental model behind retrofit.

For installation and a hello-world session see getting-started.md. For the rules retrofit upholds see principles.md.

The premise

A traditional migration tool manages what the schema is. You hand-write DDL, the tool tracks which scripts have run, and the production schema is the sum of those scripts.

What retrofit manages is how the schema changes. You don’t write DDL. You record semantic operations (rename this column, cast this type, extract this wide table into two narrower ones) and retrofit materializes the SQL.

The shift matters because some changes are identity-preserving and DDL can’t see the difference. A column rename and a drop-then-add leave the same end-state schema. Only the change record says the column’s identity carried over: the foreign key still points at it and its rows are still there.

The oplog

The oplog is an append-only file of operators. One per line. Three header lines, then the ops:

# retrofit-oplog-format: 1
# retrofit-model-version: 1
# engine-version: 17
CreateSchema(01bd95b8116c, prev=000000000000, id=a9c670f51071, name="users")
CreateTable(b2c822ec011a,  prev=01bd95b8116c, in=a9c670f51071, id=e295fa023ed9, name="people", reference_data=false)
AddColumn(45bcdb3fca16,    prev=b2c822ec011a, in=e295fa023ed9, id=3befa1c90631, name="id", type="bigint", identity=by_default)

One oplog file per registered schema. Plain text: git diffs it cleanly, you can read it in a code review, you can grep it.

Each line has four kinds of slot:

• <opid>: a 12-char hex identity minted when the op is constructed, drawn at random like an ElementID rather than derived from what the op says. It names the act, not the content: running the same command twice appends two ops with two ids, because it happened twice. An id is minted once and never re-minted for an unchanged line: reordering an op, rewriting its prev=, rebasing it through reconcile, or revising it to a value it already carries all leave it alone. Uniqueness within a log is load-bearing (a duplicate opid is a validate failure); reproducibility across two independent runs is not, and never was: replaying the same commands on a fresh oplog mints new ids. Same oplog → same output; same commands → equivalent, not identical, bytes.
• prev=<opid>: predecessor. Forms a singly-linked chain through the file. The first op in a schema has prev=000000000000. Nothing an op carries feeds its id, prev included, so revising an op early in the chain doesn’t cascade-rewrite later opids.
• <role>=<value>: payload. Element references (in=, on=, columns=…) bind by ElementID, not by name. Strings, hex IDs, enums, lists.
• id=<minted-id>: for an op that creates an element, the new element’s ElementID.

The slots keep two layers apart: the op’s own id and prev= name ops, and the element slots (in=, on=, id=, columns=, …) name ElementIDs. The catalog types every slot as one or the other, so this layer separation is a property of the format, not a rule an operator declares, and op info does not list it. An opid written into an element slot names no element, and the verbs that read the log refuse it as rule-exists-and-alive.

ElementID

Every element an op creates has an ElementID: schemas, tables, columns, constraints, indexes, views and materialized views, types, functions, triggers, policies and extensions.

The ElementID is minted at creation by the operator that births the element (AddColumn for columns, CreateTable for tables, …) as a random 48-bit draw. It is not derived from the element’s name, so two oplogs describing the same schema hold different ElementIDs. A few derived identities follow other anchors. The children import mints for a recognized ExtractTable derive from the source DDL and reproduce on re-import. The cpk.fk and detach.col children replay synthesizes follow a drawn id= or opid, so they are stable within a log and differ across independent imports. The cpk.fk children follow ChangePrimaryKey’s id=, so op revise of that op keeps them. A DetachPartition records the ids of the columns and CHECKs it mints in its column-ids and adjacent-ids lists, and replay mints those ids; only one whose lists are empty (written before the lists existed) has replay synthesize detach.col and detach.adj ids that follow its opid.

An ElementID is permanent for the element’s lifetime. It survives renames. It survives MoveColumn. It survives ExtractTable and JoinTable. It is not reused. It is invalidated only by tombstone ops (DropTable, DropColumn, …).

This holds for containers too, not only leaves. A ExtractTable that leaves live columns unassigned (the survivor arm) does not consume its source: the source survives the transform in place under the same ElementID and the same birth op, holding the unassigned columns. Only the --into children mint fresh ElementIDs. --rest <name> renames that survivor in place, which is a name transition, not a new identity. The survivor’s PK constraint name, inbound foreign keys, triggers, privileges and PG table OID are all continuing facts about the same table, never re-created.

When you write op add RenameColumn on=users.email name=email_address, retrofit resolves users.email to the column’s ElementID and stores that ID in the rename op, not the name. Snapshot then renders the latest name for that ID. The foreign key that referenced the column referenced the ID, not the name; nothing about the FK has to change.

Ops bind to identity; a name is an attribute the latest op sets.

Snapshot vs. oplog

Two views of the same schema, and one store beside them:

• Oplog: the append-only history. Authored. Versioned in git. Says how the schema came to be.
• Snapshot: canonical DDL emitted by retrofit snapshot. Says what the schema currently is. Reproducible from the oplog.
• Data files: runnable per-table <schema>.<table>.<YYYYMMDDHHMM>….data.sql files under the schema’s data_root (default: data under the project root) where import sets aside verbatim custom DML statements (INSERT/UPDATE/DELETE/MERGE) that fall outside retrofit’s vocabulary. Custom DML can’t live in the oplog (a later rename would break an opaque body), so it lives here. Each record is tagged with a purpose (transition / cleanse / ref / seed / unclassified); snapshot and migrate discover the corpus automatically: ref rows always apply, while seed rows are opt-in when an emit command names their dataset with --label. See import.md § “the data file”.

You commit the oplog. The snapshot is build output.

oplog (authored, versioned)
   │
   │  retrofit snapshot
   ▼
DDL (build output, deterministic, discardable)

The snapshot can be taken at any point in the oplog with snapshot --at <opid>, which gives you the schema as it stood when that op was the tail. Replay is total: same oplog, same op, same snapshot bytes, every time.

Replay and genealogy

When retrofit needs to know “what does the schema look like right now?” (for snapshot, for op add validation, for drift detection), it replays the oplog through a deterministic engine that builds a genealogy forest: a content-addressed tree where each addressable element is a vertex, and each op contributes one or more edges.

Replay enforces structural rules (you can’t DropColumn a column that doesn’t exist, you can’t AddPrimaryKey to a column that’s already in another PK), normalizes SQL semantics (an identity= column is implicitly not_null), and produces vertex content hashes that anchor drift detection.

You don’t see the genealogy directly. You see its outputs: snapshot DDL, op add validation errors, drift questions.

The unsealed tail

The oplog has two regions: sealed and unsealed.

• Unsealed tail: the working surface. You can append (op add), edit (op revise), or remove (op remove) ops freely. You can open the editor buffer (op revise -i) and delete lines. No confirmation step guards it, because no database has applied it. That holds only while nobody has, so a tail shared through git is sealed before anyone applies it.
• Sealed range: frozen history. Once you op seal, the range is locked: a Merkle digest over the sealed ops’ canonical bytes is recorded, and any in-place edit to a sealed op breaks verification. Sealing is the act that promises “this is what production saw.”

You seal a range when you ship it. New work goes on a fresh unsealed tail behind the seal.

Recovery is git: put the file back from version control.

Rows in a database are the asset (principle #1); the oplog is source code. The destructive prologue (snapshot --include-drop=scoped|schema) is where row loss enters, and it is opt-in.

Forks

prev= normally forms a single chain. A fork is the exception: two or more ops sharing the same prev= (a branch point with more than one successor). Forks arise the usual way branches diverge: two git branches each op add onto the same op, and the merge=union .gitattributes rule concatenates both appends into one file. The result is a valid oplog with a non-linear tail: every op is still there, but replay order across the branch point is ambiguous.

retrofit reconcile closes forks by rebasing onto a linear tail: it picks an order for the divergent blocks and rewrites prev= so the chain is single again (opids are unaffected, since nothing an op carries feeds its id). It is non-interactive, so the flags carry the decision:

• --keep / --swap: the bias. Prefer the first-seen block order, or the second-seen.
• --clean / --accept-dirty: the strictness. Write only if a conflict-free linear order exists, or write the requested order even when one block’s ops depend on the other’s (a dirty tail you then resolve with op revise).
• <fork-selector> / --all: reconcile one named fork, or every leaf fork in line order.

reconcile --all --clean refuses fork-nested (exit 1) when one oplog holds two or more independent forks; run reconcile <fork-selector> --clean once per fork.

A fork of three or more blocks closes two blocks at a time:

• Steps run in line order, each joining the result so far with the next block.
• --swap reverses each step’s order, except at a step with one sealed side, which writes the Seal first under either bias. seal-forces-order refuses the bias only when every step is such a step.
• Under --clean the run stops at the first step that does not close and writes nothing. It refuses with that step’s code, its message prefixed step k of n: fork-no-clean when the pair has no clean order, otherwise the step’s own refusal (a seal refusal, for one).
• Under --accept-dirty steps close as under --clean until the first with no clean order. From there the requested order is written, the run exits 2, and every later step is reported unchecked. op revise mends the linear result.
• A fold whose every step closes clean refuses --accept-dirty (flag-not-applicable).

validate reports forks, and by default fails the run on one; reconcile resolves them. A Seal inside a fork constrains the order: a single sealed block is written first (seal-forces-order), both blocks sealed needs an explicit choice (fork-concurrent-seals), and a Seal spanning both blocks refuses (fork-straddles-seal). A selector that matches no fork refuses too (fork-selector-unknown). Recovering from a merge maps each refusal to the command that clears it.

One fork shape is worth naming because an opid is a draw. Two developers on two clones run the same op add against the same predecessor. Each mints its own id, so git merge sees two different lines, the merge=union rule concatenates both, and the result is a fork holding the same act twice. reconcile --clean refuses it (fork-no-clean): replaying either order charges the duplicated act against the rule that governs it (rule-unique-name-in for a CreateTable, and so on), so neither order is clean. Because the two lines differ, merge=union keeps both, and neither act is lost. Drop the one you don’t want, or take the tail deliberately with --accept-dirty and resolve it with op revise.

Recovering from a merge

After a git merge of two branches that each appended ops, the fork is usually noticed by whichever command next reads the oplog, not by reconcile. The refusal it prints carries a hint: line naming the next step; this table collects them in one place.

The usual path is retrofit validate to confirm the fork, bare retrofit reconcile to see whether a clean order exists (it writes nothing), then reconcile --clean or reconcile --accept-dirty.

What an op actually carries

Take this line:

AddColumn(45bcdb3fca16, prev=b2c822ec011a, in=e295fa023ed9, id=3befa1c90631, name="id", type="bigint", not_null=false, identity=by_default)

• 45bcdb3fca16: the opid. Drawn at mint time, never computed from the line’s content, so it names the act that authored this op rather than the bytes the op carries.
• prev=b2c822ec011a: predecessor. Rewriting it does not touch this op’s opid: the opid was drawn once and does not follow the chain.
• in=e295fa023ed9: target table (an ElementID; the table this column lives in).
• id=3befa1c90631: the ElementID this op mints (the new column’s identity).
• name="id": the user-facing name. Mutable via RenameColumn.
• type="bigint": SQL type.
• not_null=false, identity=by_default: column attributes as the user wrote them. The persisted op preserves the original input verbatim; the PG semantic implication (identity= ⇒ not_null) is applied at replay, not at op add time, so the oplog never silently rewrites what you said.

Replay reads this op, adds a column vertex under the table vertex and applies the identity-implies-not_null rule; snapshot emits the column declaration from the result.

Operators by category

The operator catalog is grouped by what kind of change the op expresses. The full list with implementation status lives in smo-catalog.md. The categories:

• Schema: CreateSchema. Root element.
• Table: CreateTable, DropTable, RenameTable, CopyTable, ExtractTable / JoinTable (column-axis split/merge), SplitTable / MergeTable (row-axis split/merge), MoveColumn, SetReplicaIdentity, SetTableComment, and the declarative partitioning ops below.
  • Partitioning: CreatePartition (born-as-child PARTITION OF), AttachPartition / DetachPartition (subordinate an existing table, emancipate one back), PartitionTable / DepartitionTable (identity-preserving convert between a plain heap and a partitioned parent). These are Table-category ops, not a category of their own: partitioning is a physical storage attribute of a table that persists across the change, so the op acts on a table the way a rename or a comment does.
• Column: AddColumn, DropColumn, RenameColumn, CastColumn, CopyColumn, SetDefault / DropDefault, identity ops (AddIdentity, DropIdentity, SetIdentityGeneration), NOT NULL ops (AddNotNull, DropNotNull), DropGeneratedExpression, SetColumnComment.
• Constraint: AddPrimaryKey, ChangePrimaryKey, DropPrimaryKey, AddForeignKey, DropForeignKey, AddUnique, DropUnique, AddCheck, DropCheck, RenameConstraint, ValidateConstraint, constraint-attribute ops (deferrable, initially-deferred, enforced, inherit), SetConstraintComment.
• Index: CreateIndex, DropIndex, RenameIndex.
• View: CreateView, ReplaceView, RenameView, DropView, and the materialized-view ops (CreateMaterializedView, ReplaceMaterializedView, RenameMaterializedView, DropMaterializedView).
• Type: CreateType, DropType, RenameType, AddEnumValue, RenameEnumValue.
• Policy: CreatePolicy, ReplacePolicy, RenamePolicy, DropPolicy, SetRowLevelSecurity.
• Function: CreateFunction, ReplaceFunction, RenameFunction, DropFunction, SetFunctionComment.
• Trigger: CreateTrigger, RenameTrigger, DropTrigger, EnableTrigger, DisableTrigger.
• Extension: CreateExtension, DropExtension (under the schema-root category).
• Raw: RunRawScript (DO $$ … $$; escape hatch).
• Log: Seal. Not a schema operation; pins log integrity.

The change operators (Rename, Cast, Extract/Join, Split/Merge, the partition converts, Copy, Move) are the ones a migration tool can’t express. Create/Add/Drop exist to bootstrap and trim the schema; the change operators are what drives architectural evolution.

The five data-motion change operators (CopyTable, ExtractTable, JoinTable, SplitTable, MergeTable) are authored via op add and replay/render correctly through snapshot. Two verbs also recognize a subset: drift surfaces Copy / Extract / Join / Merge (plus the partition converts) as questions from an end-state diff, and import mints a solid-match ExtractTable from a migration script. SplitTable is recognized by neither from end-state, because its per-row predicate leaves no schema residue; author it with op add. See drift.md and import.md for the recognizer details.

SplitTable (the PRISM SMO listed here, a row-axis refactoring that consumes the source) is unrelated to PG’s declarative PARTITION OF clause (a physical storage attribute of a persisting table). Declarative partitioning is owned by the five partitioning ops (CreatePartition, AttachPartition, DetachPartition, PartitionTable, DepartitionTable), not by SplitTable. The distinction is which table survives: a split consumes its source and forks lineage into siblings, while a partition convert keeps the parent’s identity and reorganizes its storage. INHERITS + CHECK is likewise a partition signal, never a split.

Authoring ops: import vs drift

Two ingress verbs, opposite postures. import is imperative: it reads a .sql source and appends one op per recognized DDL statement, with no inference. drift is declarative: it reads a .sql source as the database’s current state and emits the ops that converge the oplog to that state.

Decision rule: “Have I authored operations I want recorded?” → import. “Has the database moved without me, and I want the oplog to catch up?” → drift. See import.md and drift.md for the full per-verb behavior.

Schema identity and the resolver

A retrofit project can hold one schema or many. Every schema has four properties; the CLI’s job is to map a verb invocation to a single registered schema by reading those properties.

• Name: the registry key. Case-insensitive (PostgreSQL unquoted-identifier rule).
• Path: the [[schema]].path in retrofit.toml. Where the oplog file lives.
• Scope: directories that resolve to this schema. Implicit from dirname(path), plus optional explicit overlays in the top-level [directory_scope] TOML table.
• Default pin: default_schema in retrofit.toml. Last resort for schema-context verbs.

The resolver chain

Every schema-context verb (op*, snapshot, migrate, reconcile, drift, import, schema move, schema engine, schema scope add, schema set-default) runs the same six-step chain. The first step that returns a schema wins:

1. --schema <name> flag.
2. -f <path> (mutex with --schema). With a registry, looks up [[schema]] by path; without a registry, opens the file directly.
3. [directory_scope] longest-prefix match against cwd.
4. Implicit oplog-dir longest-prefix match (dirname(path)).
5. default_schema fallback.
6. Refuse with default-schema-unset.

In a single-schema project, step 5 carries the load and the user types nothing. In a monorepo where each schema’s oplog lives in its own subdir, step 4 carries it; again there is nothing to type. The [directory_scope] table at step 3 is the explicit overlay when your source dirs and oplog dirs don’t line up.

--chdir, -C <path> is the root-persistent flag that reroots cwd for the whole run. It affects steps 3 and 4, the retrofit.toml discovery walk, and any relative path passed to a verb.

Never silent at resolution

Every schema-context verb names its resolved schema:

• Text mode: the registered schema-context advisory renders as a note: schema-context: <name> line on stderr before the verb’s main output.
• JSON mode: a "schema":"<name>" field on the terminal {"type":"ok",...} envelope.

-f <path> is a path-as-identity selector: direct reads suppress the preamble (no resolver leg fired). In CI, set RETROFIT_REQUIRE_EXPLICIT_SCHEMA=1 to refuse with explicit-schema-required when neither --schema nor -f is passed. The default is permissive so single-schema projects keep zero ceremony.

Three lazy-create doors

Scaffolding a new schema does not need a schema create first. Three authoring entry points lazy-create the schema when the explicitly named schema isn’t yet registered:

• retrofit import --schema <new> <source>: mints the oplog, registers [[schema]], auto-pins default_schema on the first-ever schema, then imports the DDL.
• retrofit op add CreateSchema <new>: the bare-form symmetry path.
• retrofit schema create <new> [<location>] [--scope <dir>...] [--is-default] [--engine-version <X>]: the rich-form authoring path with project-setup flags.

Lazy-create fires only on an explicit name; a schema the resolver chain reaches is already registered. A typo on --schema mints a new schema; recovery is schema remove. See getting-started.md for the typical flows.

Stale data references and repair

A preserved data record is captured against the schema as it stood at its anchor: the op it is filed under. The schema keeps moving after that. By the time the record is emitted, a column it names may have been renamed, its table may have been renamed or dropped, or a column may have gained a constraint the stored rows cannot satisfy.

That is decided per record, at emit time, and the stored file is never rewritten. There are three outcomes:

• The table is gone. The record is omitted and reported as excluded, cause table-dropped. Its rows have nowhere to land, so there is nothing to emit and nothing to fix.
• The record can be made true. The emitted text is rewritten through the oplog: a renamed table or column is emitted under its current name, and a column dropped from an INSERT column list (or an UPDATE ... SET left-hand side) is removed along with its value. The emitted body carries a comment naming each repair, one courtesy line per repaired table lands on stderr, and a machine-readable record rides the JSON stream. Your data file on disk is untouched.
• The record cannot be made true. A column the body reads was dropped, a new NOT NULL column has no default, a cast is lossy, two captures of one table have forked. The run exits non-zero with stale-dml-reference, naming every refused record and its reason; the hint names the recourse: re-capture the table, or for a forked chain retire one file.

Repair follows identity, not names. Two references name the same thing when they resolve to the same object in the schema at the record’s anchor, so a table renamed twice still repairs, and a column dropped and re-added under the same name is refused rather than silently reused.

A name retrofit does not model (a function, a sequence, an unmanaged table) is left exactly as written. --verbose lists them per body so you can see what was passed through untouched.

Capturing from a corpus label

Rows captured under a cohort label (data capture <source> --label <l>) land on that label’s seed[<l>] chain. Once the table is declared reference data (schema ref-tables add <t>), data capture --from-label <l> [--table <t>] promotes them: the corpus is the source, so no file operand is given and no database is read. The verb folds the label’s chain for each selected table at the current head and captures the result on the ref band as one literal INSERT with an explicit column list, each value copied from the stored records. Every selected table is checked before anything is written: an undeclared named table, a chain that does not fold offline, or an empty fold refuses the whole run. The seed chain itself is left as it is.

Which point a record is rendered against

A record is rendered against two points in the oplog: its anchor, and the point the verb is emitting for. Only the second one moves, which is why one stored file can emit verbatim, repaired, or refused across runs without changing on disk.

• migrate has a window, --from to --to, and emits the records whose anchor falls inside it, each rendered against the window’s end. A record anchored before --from is not emitted at all; a dml-behind-window warning names it so nothing is dropped silently. Widen the window if you meant to include it. An anchor the oplog does not contain cannot be placed in any window, and the run stops with dml-anchor-unresolved.
• snapshot has no window. --at <opid> is the one point it renders for (the tail when omitted), and every opted-in record is rendered against it. --at the record’s own anchor therefore emits the stored body byte-for-byte; --at a later op emits it repaired, or refuses, as the ops in between require.
• On snapshot, a record whose anchor precedes the history you have (an oplog that starts after it, or an op it never contained) has no schema to be resolved against. Its names are left exactly as written and the body is emitted verbatim, exit 0; --verbose lists them as unmanaged references, the same way an unmodelled name is listed.

Attesting a stale reference

--allow-stale-dml on migrate and on snapshot downgrades every refusal in the run to a warning: repairs are still applied, the body is emitted under a -- WARNING: stale reference comment naming the reason, and the run exits 0. It is a deliberate override, not a repair: you are asserting you know the emitted SQL is right anyway.

One case it does not cover: two data files claiming the same table under one label set through a rename (a forked chain). There is no emission that is correct for both, so the run refuses with or without the flag. Retire one of the files and re-run.

Consents

A consent is an input that clears a refusal a verb would otherwise raise. Every consent is one spelling, naming one fact, in one kind, and the spelling is the same token on every verb that takes the fact. There are two kinds:

• apply context: a standing fact about where the script runs. The flag takes no value.
• sealed history: operations a Seal attested are rewritten. The side-effect arm (reconcile, import --force) is attested one Seal at a time, by opid: --allow-sealed-rewrite=<seal opid>, repeatable and comma-separated. A value attests the Seal it names and selects nothing; a Seal left unnamed refuses seal-removal-refused. The same kind, as the command’s own named act, is taken by op unseal, op truncate --drop-seals and schema remove (unless --keep-oplog), which take no spelling: the command is the attestation.

No consent is needed to emit an authored drop, whatever it drops. No consent is asked, either, for rows a rendered step loses (a down that drops a column written since, or narrows a type and rounds) or for a rule a rendered step re-imposes for PostgreSQL to check at apply; the render’s report and script name each.

The table below holds every consent and every outside row, each with its class. An outside row clears a refusal, or answers a warning as --allow-unnamed-label does, but accepts no fact of a kind above. The code holds the same table; a test checks this one against it.

The emit envelope

Your runner applies the emitted migration, and retrofit cannot observe whether it has opened a transaction around the file. --envelope (or [project] envelope in retrofit.toml) states it, with three values: own (retrofit writes its own BEGIN;/COMMIT;; the default, see migrate.md), caller (you opened a transaction; retrofit writes none), and independent (each statement applies on its own).

Two consequences follow:

• The value decides who owns the mid-file boundary. A statement like ALTER TYPE … ADD VALUE needs the enclosing transaction closed around it. Under own retrofit emits that boundary. Under the other two it cannot (a bare COMMIT; would end your transaction), so it emits none and says so on stderr rather than shipping a file that fails at apply.
• The file states its own obligation. Every emitted script carries one -- envelope: … line naming what the consumer must do. Its audience is the human at review and post-mortem time; no runner parses it.

The mapping from runners to values is yours; migrate.md lists each runner’s value with its conditions. A runner that wraps only part of a file is out of scope (migrate.md): no fourth value could describe it, because the cut lives in the runner’s configuration.

Output contract

Every verb’s stdout/stderr is structured. --as=text is the default; --as=json emits NDJSON: zero-or-more intermediate {"type":"<name>",...} records followed by exactly one terminal {"type":"ok"} or {"type":"failed",...} record. The single type discriminator names every record on stdout. Errors land on stdout (not stderr) so a single jq filter handles success and failure.

The full contract (which helper to call, per-verb shapes) is in cli-output-contract.md.

What retrofit is not

• Not a migration runner. It emits DDL; running it against your database is your job (or your CI’s). retrofit migrate emits a forward script between two cuts (or the reverse, with --down), but it still writes to stdout; applying it is your pipeline’s responsibility.
• Not a schema-as-code DSL. Schema-as-code tools have you author target state and the tool diffs. Here the input is the ops, not the target.
• Not a backup. Sealed history is tamper-evident, not durable. Your oplog file is in your git repo; that’s where durability comes from.
• Pre-1.0: breaking changes land without back-compat shims and are called out in commits.