import

retrofit import bootstraps an oplog from one or more .sql files. It walks the DDL in retrofit’s vocabulary and appends the equivalent op add calls to a fresh (or extended) oplog.

Read concepts.md first for the oplog / cut / element identity vocabulary. This doc covers what import sees, what it produces, what it cannot produce, and why.

Usage

Pass one or more <source> paths (or - for stdin). Schema selection follows the six-step resolver chain: pass --schema <name> explicitly, or rely on [directory_scope] / implicit oplog-dir / default_schema.

retrofit import schema.sql                            # resolver picks schema
retrofit import --schema sales schema.sql             # explicit
retrofit import --schema sales 01_tables.sql 02_views.sql  # batch, argv order
retrofit import --schema sales --from-file order.txt  # curated ordering (see below)
retrofit import --schema sales - < schema.sql         # stdin (once per invocation)
retrofit import --schema sales schema.sql --dry-run   # parse, validate, skip the write
retrofit import --schema sales schema.sql --seal      # append a Seal after the import
retrofit import --schema sales extra.sql             # extend a populated oplog
retrofit import --schema sales schema.sql --force     # re-bootstrap; discard prior content
retrofit import --schema sales seed.sql --label env:dev       # label preserved seed rows
retrofit import --schema sales lookups.sql --purpose ref      # classify all preserved DML as ref
retrofit import --schema sales schema.sql --skip-manifest skips.jsonl   # skipped statements as JSONL
retrofit import --schema sales schema.sql --summary-file summary.json   # aggregate summary as JSON
retrofit import --schema sales schema.sql --engine-version 17 # pin the imported schema's engine version

When the source carries custom DML (INSERT/UPDATE/DELETE/ MERGE), import preserves it into data files rather than dropping it, one file per table, not one per run (see § “Custom DML statements: the data file” below). --purpose classifies each statement a reference-data declaration does not claim first; --label (repeatable) names the dataset the records belong to, which classifies them seed and stamps the labels on them when neither --purpose nor a reference-data declaration classifies them first (rung 3 of the chain under purpose below).

If --schema <name> names an unregistered schema, import lazy-creates it. It mints the oplog (<name>.oplog in cwd, or at the project root when cwd is outside it), appends [[schema]], auto-pins default_schema on the first-ever schema, then proceeds with the DDL. Lazy-create fires only on the explicit --schema form; the resolver chain never invents names.

--dry-run skips the oplog write and the [[schema]] engine_version update, but not lazy-create: against an unregistered schema it still leaves the one-op bootstrap oplog and the [[schema]] row on disk. To preview with no side effect, run schema create first, then import --dry-run.

Multi-file batches are all-or-nothing: parse + validate runs over the combined op stream, appended events flush only after the whole batch passes, and the oplog file is written atomically once.

Each appended op renders as + <opid> <Operator> <target> (text) or a {"type":"appended",...} record (JSON), followed by a terminal {"type":"ok",...}. The resolved schema name appears as a note: schema-context: <name> line on stderr (text mode) or as the schema field on the terminal ok envelope (JSON mode). See cli-output-contract.md for the full shape.

Source order

Multi-file batches are imported in argv order. import does not re-sort sources; whatever order the shell hands the CLI is the order the ops mint into the oplog. The contract is deterministic and silent, which means shell-glob ordering bugs are silent too.

The common trap: a Flyway / Liquibase-style tree of V<N>__<desc>.sql filenames imported via a bare glob.

retrofit import --schema mig V*.sql      # WRONG for V1..V20

The shell expands V*.sql lexicographically (V1, V10, V11, V2, V20), so the resulting oplog interleaves V10 / V11 before V2. A misordered tree either refuses at the first ALTER whose target a later file creates (validation-error), or imports cleanly and shows up later as unexpected snapshot output.

Recipe: your own files

If you author the source tree, use a zero-padded numeric prefix. The shell glob then sorts correctly with no extra ceremony:

01_tables.sql  02_constraints.sql  10_indexes.sql  20_views.sql

retrofit import --schema mig *.sql   # bare glob is correct

Recipe: third-party migration trees

If you’re importing someone else’s tree (Flyway, Liquibase, Goose, …), you can’t rename the files. Sort the argv with a version-aware comparator before passing it to retrofit:

# sort -V is widely available on macOS and GNU coreutils,
# though not strictly POSIX. The $(…) recipe assumes whitespace-free
# basenames: fine for Flyway/Liquibase trees, but quote or use a
# shell array if your filenames could contain spaces.
retrofit import --schema mig $(ls V*.sql | sort -V)

# zsh: numeric glob qualifier
retrofit import --schema mig V*.sql(n)

Sort the file list, not a concatenated stream: argv mode preserves per-file error context (parse-error: cannot parse SQL at V07__foo.sql) where the stdin pipe (cat $(…) | retrofit import - …) collapses the location to -. Piping works, but if any source is malformed you lose the file name in the failure envelope.

Caveat: the tree has to build from the oplog’s state

import validates each statement against the state the oplog and the earlier statements built, so a migration tree imports when its files, in order, build the schema from the oplog’s current state (nothing, on a first import). A tree that assumes objects created outside it fails at the first statement that does not validate, one error per run: fix or remove that statement and re-run, so a tree with N such statements takes N runs.

--allow-capability-gaps does not rescue this. It only demotes capability-gap skips (statements retrofit would otherwise drop because no operator covers them) from a failing exit back to exit 0 + a warning. It does not relax structural validation-errors or rule-* refusals; those still abort the import regardless of --allow-capability-gaps.

For such a tree, apply it to a PostgreSQL database and run drift over a pg_dump --schema-only of the result, or import that dump.

Caveat: dotted minor versions (`V2.1`, `V17.1`)

Flyway permits V<major>.<minor>__<desc>.sql for hotfix migrations between integer majors (V2 < V2.1 < V3). sort -V doesn’t quite agree: it sorts V2.1__b.sql before V2__c.sql because the comparison after V2 is .1__b.sql vs __c.sql, and . (0x2E) sorts before _ (0x5F). The version segment is right, the trailing-suffix interleaving is wrong. If your tree mixes integer-only and dotted versions:

Confirm the sorted argv before importing (ls V*.sql | sort -V and read the output), or
Pass the files explicitly via --from-file order.txt (see “Curated ordering via --from-file” below).

Padding majors (V01, V02, V10) only fixes the V1/V10/V2 trap for integer-only versions; it does not resolve the dotted-minor interleaving (the . < _ byte ordering still places V02.1 before V02).

Curated ordering via `--from-file`

For trees the shell can’t sort correctly (dotted-minor versions, hand-curated subsets, mixed authoring sources), keep the curated order in a newline-delimited text file alongside the .sql files:

# migrations/order.txt: curated for dotted-minor sequencing
V1__init.sql
V2__seed.sql
V2.1__hotfix_seed.sql      # interleaves after V2 per Flyway semantics
V3__indexes.sql

Then pass the file to import:

retrofit import --schema mig --from-file migrations/order.txt

Rules:

Paths in the file resolve relative to the file’s directory, not the process CWD. So --from-file migrations/order.txt works from any working directory; the listing is portable. Absolute paths in the file are honored verbatim.
Blank lines and #-prefixed comment lines (with optional leading whitespace) are ignored, so the file can be self-documenting.
Combining with positional sources is supported: positionals are imported first, then the file list, in declared order.
- (stdin) inside --from-file is rejected; stdin imports stay one-source-per-invocation. Pipe via stdin in a separate invocation if you need it, then import the remaining files.
Importing a migration history this way? Reach for --seal-per-file to seal each file as its own checkpoint, rather than collapsing the whole history into one prefix.

Sealing imported batches

A correctly-ordered multi-file batch is a stable baseline worth sealing. --seal derives a Seal reason from the argv basenames in order (e.g. imported V01__init.sql, V02__add_index.sql, …), which doubles as a record of the order you imported in. When two sources in the batch share a basename, each colliding name widens to the shortest trailing path no other source ends with, so a Diesel history (<version>_<name>/up.sql) reads imported 001_a/up.sql, 002_b/up.sql. The widened label stays relative (never the absolute path), and a batch with no collision keeps its basenames:

retrofit import --schema mig $(ls V*.sql | sort -V) --seal

--seal emits a single trailing Seal after the whole import, collapsing the batch into one sealed prefix. When the sources are an existing migration history (one already-stable checkpoint per file, the case --from-file is built for), use --seal-per-file instead to emit one Seal after each source’s ops:

retrofit import --schema mig --from-file migrations/order.txt --seal-per-file

Each migration becomes its own sealed segment, as if you had run import --seal once per file; with no seal at all, later work has no stable point to anchor on.

--seal-per-file composes with the reason rules above:

Bare, each per-file Seal derives its reason from that file’s basename, widened against the rest of the batch when the basename collides (every Diesel or Prisma migration is up.sql or migration.sql), so identify names the migration a database is at.
--seal-per-file --seal=<text> applies the literal <text> to every per-file seal.
A single-source import is a plain --seal: one Seal over the same span, with the same reason.
An empty (zero-op) source mints no seal, so there is one seal per source that minted ops, not one per input file. The summary says so (sealed 41 ops across 82 sources (2 sources minted no ops, so were not sealed)), and the import-summary record carries the count as sources_zero_op.

Every Seal import appends passes the gate op seal runs over the range it is about to seal, before anything is written: a reference record the range would cover with no witness inside it refuses data-witness-missing (exit 1), naming the record. That includes a record this import itself mints, when the source carries rows for a reference table beside the DDL; the recourse is to import without --seal, then retrofit data witness, then retrofit op seal. A reference replace anchored in the range with no upsert form at its anchor refuses replace-no-upsert-form (exit 2), as op seal does (docs/migrate.md). --dry-run rehearses the same refusals.

See --help-all for the full seal-reason rendering rules.

What import recognizes

The walker’s vocabulary maps DDL statements to operator descriptors in internal/oplog/catalog/operators.go. The recognized set:

CREATE TABLE: including inline column constraints, GENERATED ALWAYS AS (<expr>) STORED, and identity columns (GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY).
CREATE INDEX: btree, gin, gist, brin, hash, spgist; predicate (WHERE) and access method (USING) bind as first-class params.
ALTER TABLE … ADD CONSTRAINT { PRIMARY KEY | FOREIGN KEY | CHECK | UNIQUE }: including the legacy named NOT NULL shape via CHECK (col IS NOT NULL), which folds to one AddNotNull per column. (The inline CREATE TABLE … UNIQUE (cols) and the standalone ALTER TABLE … ADD CONSTRAINT … UNIQUE (cols) spellings both mint AddUnique, as does UNIQUE USING INDEX <idx> when the referenced index is constraint-eligible. A UNIQUE carrying a facet AddUnique cannot reproduce stays skip-manifested: NULLS NOT DISTINCT, INCLUDE (…), WITH (…), USING INDEX TABLESPACE, USING <method>, or a missing column list in any form other than that eligible USING INDEX one.)
ALTER TABLE … { ADD | DROP } COLUMN: paired cross-table DROP COLUMN + ADD COLUMN of an unreferenced column coalesce into a single MoveColumn (simple) op.
ALTER TABLE … ALTER COLUMN: SET DEFAULT, DROP DEFAULT, SET NOT NULL → AddNotNull, DROP NOT NULL → DropNotNull, DROP EXPRESSION (clears a STORED generated expression; PG13+), ADD GENERATED, DROP GENERATED, SET GENERATED (identity mutations).
ALTER TABLE … ALTER CONSTRAINT … { DEFERRABLE | NOT DEFERRABLE } and ALTER TABLE … RENAME CONSTRAINT.
ALTER TABLE … REPLICA IDENTITY ....
ALTER TABLE … RENAME TO → RenameTable.
ALTER TABLE … RENAME COLUMN → RenameColumn.
ALTER INDEX … RENAME TO → RenameIndex.
ALTER VIEW … RENAME TO → RenameView.
DROP TABLE [IF EXISTS] <name>[, <name>…]: mints one DropTable per tracked-live target (PG permits comma lists).
DROP INDEX [IF EXISTS] <name>[, <name>…]: mints one DropIndex per tracked-live target.
DROP TYPE [IF EXISTS] <name>[, <name>…]: mints one DropType per tracked-live target.
CREATE VIEW / CREATE OR REPLACE VIEW / DROP VIEW: single-schema forms; cross-schema views are skipped.
CREATE MATERIALIZED VIEW / DROP MATERIALIZED VIEW: single-schema; cross-schema matviews are skipped.
CREATE FUNCTION / CREATE TRIGGER: single-schema; bodies and WHEN clauses are element-ID-keyed.
CREATE POLICY and row-level-security toggles.
CREATE EXTENSION / DROP EXTENSION / SET search_path: cluster-scope extension lifecycle; SET search_path mutates the registered search path captured at bootstrap.
COMMENT ON TABLE | COLUMN | FUNCTION. A function comment resolves its signature as ALTER FUNCTION … RENAME does, so pg_dump’s f(n integer) names f(integer); a bare COMMENT ON FUNCTION f resolves only when one overload of f is live, and any other miss records a comment-function-unresolved skip.
COMMENT ON CONSTRAINT … ON <table>, for a primary key, foreign key, unique or check constraint, resolved by the name the constraint holds at the statement, so a later rename keeps the comment; <table>_pkey also names an inline PRIMARY KEY. Any other target (a PostgreSQL 18 named NOT NULL, an unknown name) records a comment-constraint-unresolved skip. COMMENT ON CONSTRAINT … ON DOMAIN stays in the comments bucket.
DO $$ … $$; anonymous blocks → RunRawScript, unless a data-motion recognizer intercepts the block first.

Unresolved trigger callbacks

When CREATE TRIGGER names a callback function that the input and current cut do not define, import accepts the trigger and mints a synthetic CreateFunction for the unresolved callback. The synthetic op has language=internal, appears in op show, and contributes to the imported op count even though the user did not author function DDL. It exists only to give the referencing CreateTrigger an element identity in the oplog.

The round-trip is deliberately asymmetric: snapshot suppresses the synthetic function’s DDL because it has language=internal, but still emits the referencing CREATE TRIGGER. The emitted DDL therefore applies successfully only when the target database already provides <name>; otherwise PostgreSQL rejects the trigger at apply time with function … does not exist. Import exits 0: an unresolved callback is reported as a finding, not refused.

Text mode reports the condition as:

finding: function-synthesized-stub: callback <name> is unresolved; a stub was synthesized and will fail at apply if <name> does not exist in the target database

See CLI output contract for the text and JSON channel shapes.

`DROP … IF EXISTS` liveness

DROP TABLE, DROP INDEX, and DROP TYPE with IF EXISTS are liveness-aware. Import evaluates liveness independently at each target’s source position. A tracked-live target mints its DropTable, DropIndex, or DropType; an ordinary absent target is suppressed without an op or skip-manifest entry. In a mixed target list, live targets therefore mint while absent targets do not. The bare form remains explicit drop intent and follows the same mint path, but fails or records a skip when its target cannot be resolved instead of using IF EXISTS suppression.

This per-target rule preserves a pg_dump --clean --if-exists prologue as a no-op against a fresh import target without treating every guarded drop as prologue noise. DROP SCHEMA IF EXISTS is the deliberate carve-out: it stays suppressed because the catalog has no DropSchema operator. A table name still recorded as an unminted deferred-data-motion target is also not treated as an ordinary absent table; its drop receives the never-silent cross-skipped-data-motion-target disposition.

What import cannot produce, and why

import records each change as the source states it. A second pg_dump --schema-only is a state, not a change: on a populated oplog import refuses it with state-source-on-populated-oplog (see When a bare re-import is not what you meant), and it is drift’s input.

Two shapes are the exception, recognized only when the source carries their full evidence: a cross-table DROP COLUMN + ADD COLUMN of an unreferenced column coalesces into MoveColumn (simple), and a solid-match ExtractTable is recognized (see below).

Data-motion SMOs

import recognizes one data-motion shape from a migration script: a solid-match ExtractTable. The source must be fully consumed (every column reparented into a child, source constraint/index-free, source DROP TABLE’d), matching the replay op exactly. On a match, import mints the ExtractTable and emits an import-extract-recognized advisory.

The source-surviving column-extract shape (ExtractTable’s survivor arm, CREATE child + INSERT … SELECT + ALTER TABLE source DROP COLUMN, with no source DROP TABLE) is authorable with op add. import does not recognize it: recognition keys on a DROP TABLE, which this shape lacks, so it takes the import-data-motion-deferred path below.

Everything that only partially matches (the source-surviving column-extract, and the other four data-motion shapes CopyTable, JoinTable, SplitTable and MergeTable, plus the partition converts) emits the plain primitives import already produces (CreateTable + DropColumn + …) with a never-silent import-data-motion-deferred advisory pointing at op add. Coalesce in the unsealed tail, or author the op directly. See drift.md § “Data-motion SMOs” for the op add recipes.

A child born by CREATE TABLE … AS SELECT in a deferred shape is not minted at all, so every later statement against it (ALTER TABLE, CREATE INDEX, DROP TABLE, and row statements such as an UPDATE backfill) lands on the skip manifest as cross-skipped-data-motion-target until an in-schema CREATE TABLE reuses the name. The rows are not captured as a data record: author the data-motion op first, then capture them at HEAD.

Out-of-vocabulary statements: the skip manifest

Sequences (standalone), privileges (GRANT/REVOKE/OWNER), cross-schema objects, composite-type attribute mutations (ALTER TYPE … {ADD|DROP|ALTER} ATTRIBUTE: composites are born-complete; mutate via Drop+Create), and any other DDL outside the vocabulary land on a skip manifest. Each skipped statement carries its kind, name, schema, source line, and the raw text; nothing is silently dropped.

In JSON mode that per-statement detail rides intermediate {"type":"skipped",…} stream records; the terminal envelope carries only the count, as an integer skipped field. Text mode has no per-statement surface at all: it renders one aggregate summary line, tallying the skips by friendly kind and adding a disposition breakdown when the set spans more than one class. Use --skip-manifest to get the individual records in a text-mode run. See cli-output-contract.md for the record’s field set.

Declarative-partition DDL is still skip-manifested (recognition is op add-authored). The structured skip is the contract: you can see exactly what import passed over.

A legacy inheritance child (CREATE TABLE c (…) INHERITS (p), no PARTITION OF) is skip-manifested whole as pg-legacy-inherits-partition, a loss-bearing gap: no op models the inherited columns or the link. Every later statement against the child (CREATE INDEX, ALTER TABLE, row statements, DROP TABLE) lands on the manifest as cross-skipped-legacy-inherits-child until a plain CREATE TABLE reuses the name.

`--skip-manifest <path>`: the skips as a file

--skip-manifest <path> writes one JSONL record per skipped statement to <path>. It is a convenience sink over the stream above, not a second classification pass: the file carries the same records, projected through the same helper as the wire event, including raw_text, which is un-truncated on both. Read the file surface as the base record: a wire event may carry an extra key the projection adds on top (the zero-op-step advisory adds source, naming the same input the record’s src_file already points at).

retrofit import --schema sales schema.sql --skip-manifest skips.jsonl

{"kind":"CreateSeqStmt","line":2,"name":"sq","raw_stmt_kind":"CreateSeqStmt","raw_text":"CREATE SEQUENCE sq","schema":"","src_file":"schema.sql"}

Keys serialize in alphabetical order, so the bytes are stable across runs, but key by name, not by position. schema is empty for a statement that carried no qualification, and src_file names the file line counts within, so a record stays locatable across a multi-file import. Treat the field set as open and tolerate keys you don’t recognize.

The write happens at terminal emission, independent of output mode: a run that exits 2 because it dropped gap-class statements still writes the file, which is the debugging case the flag exists for. That covers the dispositional exits: the ones that classified a skip set and then failed on it. A run refused before terminal emission (state-source-on-populated-oplog, data-file-collision, parse-error, and the other pre-terminal refusals) writes neither sink. The sinks are written by the terminal emitter, so a run that returns ahead of it produces no file, including when the walker had already accumulated skip records, as a data-file-collision refusal has. Their exit codes vary by error class: most refuse with exit 2, but parse-error exits 1.

`--summary-file <path>`: the aggregate as a file

--summary-file <path> is the file-sink twin for the aggregate. It writes the import-summary record to <path> as a single JSON object (not JSONL; there is exactly one summary per run):

retrofit import --schema sales schema.sql --summary-file summary.json

{"imported":2,"run_raw_script":0,"skipped":1,"skipped_by_class":{"decision":1},"skipped_by_kind":{"CreateSeqStmt":1}}

imported, run_raw_script, skipped, and skipped_by_kind are the normative keys; skipped_by_class, sealed, and sources_zero_op join conditionally. skipped_by_class splits the same skipped total along the disposition axis rather than re-basing it. The per-kind and per-class tallies live only here; they are deliberately not duplicated onto the terminal envelope, so a consumer that wants them must read this record.

Unlike the skip stream, the import-summary record is never streamed to stdout in text mode; --summary-file is how a text-mode run gets it. Like --skip-manifest, the write lands on a dispositional exit-2 run and is skipped by the same pre-terminal refusals.

Both flags can be set on the same run: the two sinks compose, and neither suppresses the other. They must name different files; pointing both at one path refuses as summary-file-collides-with-skip-manifest rather than letting the summary object clobber the JSONL.

Custom DML statements: the data file

Custom data-migration DML (INSERT, UPDATE, DELETE, and MERGE) is not part of retrofit’s vocabulary, but import must not destroy it. Rather than skip-manifest it (which would only report the loss), import sets each such statement aside, verbatim, into a runnable per-import file: the data file.

Each import that encounters custom DML mints one data file per table it writes:

Name: <schema>.<table>.<YYYYMMDDHHMM>[.append][.p<K>].data.sql, the table and then the UTC minute the run captured it at. The stamp is opaque and never read back.
Location: the schema’s data_root, a [[schema]] field in retrofit.toml scaffolded by schema create; unset, it inherits [project] data_root, then defaults to data under the project root. A seed record lands under the first seed_paths entry instead, when one is declared.
Key: {table, band, label-set, anchor}, carried in the header. A record of another band (ref, seed, or the history band that transition and cleanse share) at the same anchor is another key and is left standing, so a --purpose transition import beside a reference record is a second file, never a rewrite.
Re-capture at a held key: a floor is overwritten, an append accumulates, the floor’s own source moves it, a witnessed key refuses data-witnessed-key, and identical records mint nothing. See § “Re-ingesting into the corpus” below.
Name collision: a computed name already held by a record of a different key takes the next minute, and the next, until the name is free, so two records of one table minted in one minute are two files.
Readers use the header: every fact comes from the header, never the name, so a moved or renamed file still resolves.

The user-facing sentence is one table per file, and it is the grain to curate by: rm data/<label>/c.* removes table c at every anchor. Two shapes hold more than one file for a table: a capture whose record count exceeds the page bound is a page set (.p2, .p3, … with page=K/N on each header), and a floor plus later appends at one anchor are separate files. Removing one member of either does not curate the table out (the sibling still speaks for it), so curate the set, and let retrofit data check confirm the corpus reads cleanly before you commit.

Why these statements can’t live in the oplog: an op carrying an opaque DML body would be silently broken by a later element-ID rename (the journal would render knowingly-broken SQL), and the only opaque-body op retrofit has (RunRawScript) is restricted to DO blocks. So custom DML lives outside the oplog, where a later rename can’t reach it. The files stay consumable: snapshot and migrate discover the corpus automatically (see below and migrate.md / snapshot.md). The corpus is every addressed *.data.<format> under data_root (.data.sql; a legacy *.dml.sql is still read, and only an obsolete header refuses with data-file-malformed).

import never runs a data file. The rows reach a database only when you apply them: run the file yourself, or apply the SQL snapshot or migrate emits, which carries the bodies (migrate splices each in after its anchor op’s DDL). The skipped_by_kind counts for InsertStmt/UpdateStmt/DeleteStmt/MergeStmt in the import summary record tell you how many statements landed in data files.

Each file is line-oriented. A v1 header tuple names the import source, the one table the file declares, the anchor it is pinned at, and the file’s axes (purpose, disposition, body, format, labels, and page on a paginated capture). Every record rides inside the file’s single body fence: a marker line, its verbatim text, and an end line that closes the body. The body is written exactly as captured and appends nothing to it: a statement that arrives without a trailing ; is stored without one, and migrate supplies the terminator when it renders:

-- retrofit:data-file v1 source="01_seed.sql" table=["public","users"] anchor-op=988edcb612ab purpose=seed disposition=append body=data format=sql labels="[\"env:dev\"]"
-- retrofit:dml-section
-- retrofit:dml seq=1 src-file="01_seed.sql" src-line=42 kind=InsertStmt
INSERT INTO users (id, email) VALUES (1, 'a@example.com');
-- retrofit:dml-end
-- retrofit:dml seq=2 src-file="01_seed.sql" src-line=44 kind=UpdateStmt
UPDATE users SET active = true WHERE id = 1;
-- retrofit:dml-end
-- retrofit:dml-section-end

A body=none file is a floor (it declares the table empty at its anchor) and carries no fence at all: the header is the whole file.

The body is the whole lines between the marker line and the end line. The newline just before the end line is part of the framing, not of the body, so a body never gains a trailing newline its author did not write. Everything in between is kept byte for byte: no escaping, no unescaping, no normalization.

The retrofit:data-file / retrofit:dml marker tokens are the on-disk wire grammar and are kept stable for re-ingest; the file itself is what you call a data file. The header tuple carries the file’s facts, in this fixed order:

source: where the file came from (the import source, a drift dump, or scaffold). Provenance prose; nothing tests it.
table: the one table the file speaks for, as a two-element JSON array ["<schema>","<relname>"], each half spelled exactly as the oplog spells it. Every record body must write that table and no other (H-5); a record that writes another relation belongs in its own file.
anchor-op: the 12-hex OpID of the op minted immediately before the statements’ source position. migrate splices the bodies back in just after that op’s DDL. A statement with no preceding minted DDL in its source file falls back to that run’s first minted op; a data-only import (zero DDL) instead anchors at the current oplog head (see below). An authored statement whose table does not stand at its anchor refuses with dml-table-not-found, and an empty oplog models no table, so a data-only import that captures a statement into one refuses that way (a statement no rung classifies is listed, not captured). Statements anchored at different ops land in different files.
purpose: the producer-assigned classification (see below). Absent means unclassified.
disposition: append, replace or restate. replace declares the file is the table’s full observed row set at its anchor; import infers it from a TRUNCATE / unqualified-DELETE shape: the INSERTs for that table that follow the arming statement mint as one replace file, and an arming statement with no following INSERT mints a replace body=none floor. Everything else is append; --disposition overrides the inference run-wide. restate (hand-authored or scaffolded only) restates the table’s rows for exactly the file’s label set, ignoring that chain’s earlier records. A replace or restate file must carry a fold-band purpose (seed or ref). Records of one (table, label-set) chain are versioned by their anchor-op position: the latest floor at-or-before a render point supersedes earlier state, while snapshot --at still renders older versions.
body: data (the fence holds the rows) or none (a floor declaring the table empty; no fence). Absent means data.
format: sql, the inline body’s serialization; written on a body=data file only, and the file’s extension must agree with it (.data.sql).
labels: the optional opaque label set (see --label), emitted only when the file carries at least one label.
page: K/N on a paginated capture only.

Each record marker inside the fence carries:

seq: a 1-based counter in capture order, one stream across every file a run mints, so records merge back into source order across files. Two records at one anchor with the same seq in different files are an order ambiguity, refused with data-file-order-ambiguous.
src-file (relative to the project root) and src-line: where the statement came from. JSON-quoted, like source. A hand-authored record carries src=hand instead.
kind: the AST node kind (InsertStmt, …). A replace file admits InsertStmt (and hand-authored CopyStmt) records only.
end: the optional framing tag (see Escalated framing below). Present only when the body needs one; it names the end line that closes this record. It is the marker’s last token.

A pg_dump -- TABLE DATA band is not captured by import: the run lists the band’s tables (data-band-listed) with a hint to retrofit data capture <source>, and mints nothing for them. Capture accepts INSERT-style dumps; regenerate a COPY ... FROM stdin dump with pg_dump --inserts or --column-inserts first. See data.md.

retrofit data check [<path>…] runs the file-level checks over the working tree (every discovered *.data.<ext> file under data_root by default), reporting each refused file with the predicate id that refused it and naming any *.data.* file the walk would not read. It emits no SQL and mints nothing; run it before committing a hand-edited corpus.

Escalated framing: when a body contains marker-like lines

The default end line is the fixed text -- retrofit:dml-end. That works until a body itself contains a line starting with -- retrofit:dml, at which point the reader could not tell the body’s line from the real frame. So:

A record must use escalated framing whenever any line of its body begins, at column 0, with the literal -- retrofit:dml. Escalated framing adds an end=<tag> token to the marker line and closes the body with -- retrofit:dml-end:<tag> instead:

-- retrofit:dml seq=3 src-file="03_notes.sql" src-line=7 kind=InsertStmt end=1
INSERT INTO notes VALUES ('a literal
-- retrofit:dml-end
inside a string, so this record escalated its tag');
-- retrofit:dml-end:1

A tagged record is closed only by its own tagged end line. Inside one, every other marker-like line (including -- retrofit:dml-section-end) is ordinary body text. That is what makes escalated framing the way to carry self-referential content.

Choosing a tag:

A tag is a decimal integer with no leading zeros (1, 2, 17).
Tags are unique across the whole file, not per record and not per section. The tag assigned is the lowest one still free in the file, in record order, so the first escalating record usually takes 1, the second 2, and so on. A record that does not escalate consumes no tag, which is why a file may legally hold end=1, then an untagged record, then end=2.
A tag must also be absent from the body it closes: if the body already contains the line -- retrofit:dml-end:<tag> at column 0, that tag would close the record early, so retrofit skips it. Tag allocation may therefore skip integers: a body containing -- retrofit:dml-end:1 gets end=2 even when 1 is otherwise free.
When you add or copy a record by hand, pick a tag that is both free across the entire file and absent from that record’s body. Reusing one fails the next read with data-file-malformed, and the message names the lowest tag still available across the file; if that tag collides with a line in your body, take the next one that does not.

The literal prefix is -- retrofit:dml and nothing wider: the sibling -- retrofit:raw, -- retrofit:snapshot-data and -- retrofit:data markers belong to other features and ride as ordinary body content, so they never force escalation.

One placement rule follows from all this: a record whose body contains a column-0 -- retrofit:dml-section-end line cannot be placed inside a section, because its body would swallow the section’s own terminator. That shape fails with data-file-malformed on write and on read. The same body outside a section is fine.

If a data file will not parse

A framing fault fails the read with data-file-malformed, naming the record and line. The common ones:

A body’s end line is missing, or was damaged; a CRLF rewrite is the usual cause, since end-line recognition is byte-exact. When only trailing whitespace differs, the message says so, because the fix is your editor’s line-ending setting rather than the body.
A body contains a -- retrofit:dml line but the record was not escalated. The remedy is the tag escalation above.
Anything other than blank lines and ordinary -- comments sits between one record’s end line and the next marker line.
A record’s body is empty.
A marker line carries the retired bytes= field. That token framed bodies before end lines did; delete it from each marker line and close each body with a -- retrofit:dml-end line.

`purpose`: how import classifies preserved DML

Each preserved statement is tagged with a purpose so downstream verbs know what to do with it. The enum is:

transition: one-time custom DML tied to a schema change. migrate splices it inline, right after its anchor op’s DDL.
cleanse: like a transition, but emitted under a run-once marker so a re-applied migration won’t repeat it.
ref: production reference data that belongs to the current state. Always applied by both snapshot and migrate; never label-filtered.
seed: datasets (fixture / environment rows). Opt-in on both consumers by naming the dataset with --label <label>; otherwise excluded and counted, cause not-selected.
unclassified: the fail-closed default (an absent purpose= token folds to this). migrate refuses unclassified bodies unless you pass --allow-unclassified-dml; snapshot excludes and counts them.

Classification is a four-rung chain, decided once per statement; the first rung that applies wins:

The reference-data declaration: an oplog attribute, not configuration. A literal INSERT the reference fold admits (an ON CONFLICT clause, for one, fails it) into a table whose resolved read at the run’s cut is reference_data=true classifies ref, whatever --purpose or --label says. ref is never label-selected.
--purpose <value>: a run-level assertion stamped onto every other statement the run preserves.
--label dataset: a run that declares one or more --label values resolves its records to seed. A label names a dataset, and a dataset is a seed band by construction, so the band follows from what you already said rather than being asked for twice. Any other statement into a declared reference table never resolves to seed: without --purpose it is listed with cause no rung.
Residual: listed, not minted. There is no producer-default transition or seed rung: a purpose must be asserted, target a declared reference table, or follow from --label. A flagless authored DML statement is therefore reported on the skipped stream with cause no purpose or label and lands in no data file.

`--label`: opaque dataset names

--label <label> (repeatable) declares a dataset for the run. It is rung 3 of the chain above: absent --purpose and outside a reference-data declaration, the records it covers resolve to seed, and the labels are then stamped onto them. Labels select cohorts within the seed band, so only seed records carry them: a record that rungs 1 or 2 classified ref / transition / cleanse, or that the residual lists, is never labeled. The value is free-form (a key:value form is accepted but not required); labels are sorted and deduped on disk. They carry no meaning to retrofit; they exist so you can later name the dataset at emit time with --label <label> on snapshot / migrate.

Re-ingesting into the corpus

A re-ingest whose records anchor at a new op mints a new data file. At a key that already holds a record:

Identical records mint nothing.
A divergent floor (disposition=replace or restate) at an unwitnessed key replaces that key’s version as a set: matched files are rewritten in place under their original names, surplus pages are deleted and named in the report, missing pages are minted, and each replaced file is reported overwrote: with its digest (git retains the old bytes).
An append accumulates: the incoming statements join the standing record after its own, reported data-appended: with the digest and the statements added. Two data-only imports into one table move no head, so they share the anchor and leave both sources' statements in the one record, in import order.
Accumulation is per source file. Re-importing an unchanged src-file is a no-op, re-importing it changed replaces that source’s statements rather than adding them again, and a statement restating a keyed row another source states supersedes that source’s statements. The header’s source= stays the first writer’s, each statement keeps its own src-file/src-line, and a statement another source already holds is skipped as a duplicate.
An append of a different purpose than the standing record’s is not merged into it and overwrites it.
A witnessed key refuses data-witnessed-key.

Importing the source a floor was captured from (data capture s.sql, then import s.sql) moves the floor rather than deduplicating against it. The last verb run on a source decides what the key says about it, whatever the bytes, so the floor’s file retires and s.sql’s statements become the key’s append (replace → append, joining a standing append there, if any). The report names the clear leg migrate no longer renders and the way back:

moved ref: zip - s.sql is now a delta at <anchor>, not the whole table (replace → append)
  note: rows the table held that s.sql does not are no longer deleted at migrate
  to restore the whole table: retrofit data capture --schema app -- s.sql

({"type":"data-moved",…} with table, anchor, labels, src_file, from, to and path under --as json). Only the floor’s own source moves it: another source restating the floor’s rows is still the silent duplicate skip, and a source from stdin has no identity to move by. The way back holds at identical rows too: data capture s.sql on the moved key mints the floor again and reports the move back (overwrote @<anchor> (append → replace), or supersedes once HEAD has moved; see docs/data.md). --dry-run reports the move as would move … and writes nothing.

--purge-superseded opts out of keeping superseded cross-position blocks, except a witnessed record, sealed or in the tail, which the purge never touches: each is left byte-intact and reported as kept: <schema.table>@<anchor> <labels> <path> (witnessed by <opid>; …) ({"type":"data-file-kept",…} under --as json).

A data-only import (a source carrying zero DDL) leaves the oplog byte-unchanged and anchors its records at the current oplog head. Against an empty oplog it refuses with dml-table-not-found: no table stands at the head its records would anchor on.

PG18-gated statements

import parses with the PostgreSQL 17 grammar (libpg_query v6), so PG18-only syntax (NOT ENFORCED, generated VIRTUAL columns, WITHOUT OVERLAPS) does not parse: the run exits 1 with parse-error. PostgreSQL versions gives every verb’s row at each major.

`--engine-version <major>`: pinning the target

--engine-version <major> sets the engine version the imported schema is pinned to. On a mint (the run that creates the oplog), one resolved value seeds both the oplog’s # engine-version: header and the schema’s [[schema]] engine_version override, so the two start equal. They are independently mutable thereafter; see the adopt-run caveat below.

retrofit import --schema sales schema.sql --engine-version 17

Resolution runs in this order, first hit wins:

--engine-version
the prior oplog header, whenever the run adopted or extended an existing oplog
[project] engine_version in retrofit.toml
the built-in default

Rung 2 is the one worth knowing: when importing onto a populated oplog, an oplog already pinned to a major keeps that pin without the flag, and omitting the flag does not fall back to the project default.

--engine-version does not move an existing oplog pin. On a run that adopts or extends an oplog, the existing header block is preserved verbatim, so the flag reaches only the [[schema]] override; passing a major that differs from the header leaves the two pointing at different values. What happens next depends on which way they diverge:

Override above the pin (--engine-version 18 on a 17-pinned oplog): the run succeeds, and the next bare import re-resolves from the header and overwrites the override back to 17. The pin change simply does not stick.
Override below the pin (--engine-version 17 on an 18-pinned oplog): the run also succeeds, but it leaves the project wedged: a target may not lag the pin, so the next command that loads the project the normal way refuses with engine-version-target-below-oplog until you restore the override or lower the pin. schema engine with an explicit --schema is the way out: its write path is exempt from that invariant precisely so it can repair the mismatch.

Use retrofit schema engine <major> --schema <name> rather than an import flag: it is the verb that moves both axes together, and the one the refusal hint points at.

A value this build does not recognize is refused as unknown-engine-version before anything is written; the envelope lists the supported majors.

Re-running import

The default behavior on a populated oplog is to extend it: the new ops chain off the existing tail. This is the case for multi-file schemas where 01_tables.sql was imported first and 02_indexes.sql was added later, and it produces the same ops as one multi-file invocation on a fresh schema, apart from the minted ids.

There is no flag to ask for that; extending is what a bare import does. One opt-in changes it:

--force: discard the existing oplog content and re-bootstrap from the new sources. The prior tail is gone. Useful when re-importing from an authoritative pg_dump.

When a bare re-import is not what you meant

Extending by default makes one mistake cheap to make: offering a whole-schema state source (a pg_dump --schema-only of the current database) to an oplog that already has content. That source describes where the database is, not the steps that got it there, so appending it would mint a second copy of every object rather than the delta. The run refuses with state-source-on-populated-oplog before writing anything.

The refusal’s hint names the three verbs that each do something different with that file:

retrofit drift <source>: the database moved out of band and you want the oplog to converge on it. Mints the difference between the cut and the source as new ops, appended to the existing tail. The history is preserved; this is the usual answer.
retrofit import --force <source>: you want to start over from the source. The existing oplog content is discarded and replaced by a fresh bootstrap. Prior history is gone. A branch that appended to the old history and is then merged with the restarted one holds two CreateSchema roots of one schema, which validate and reconcile refuse: take the restarted side and add the branch’s ops again on it.
retrofit data capture <source>: the part you wanted was the rows, not the schema. Captures the source’s data into the corpus and leaves the oplog untouched. This verb is named only when the source actually carries rows (INSERT or COPY); a COPY band must first be regenerated in INSERT form with pg_dump --inserts or --column-inserts.

An oplog that holds ops but declares no matching CREATE SCHEMA refuses with schema-not-in-oplog instead. That one is decided on oplog state alone, independently of what the source looks like, so it is a different question from the state/steps confusion above.

Failure modes

source-empty: a <source> that is a stream (-, <(…), a FIFO or another descriptor) delivered zero bytes, which on a pipe is the signature of a producer that failed upstream. Exit 2, Param source, refused before any parse; a zero-byte .sql file is read as it always was. Check the producer, or save its output to a .sql file and pass the path.
kind-mismatch: a <source> that is not a .sql file path, -, or a stream, such as a URL scheme, a non-.sql extension, or an extension-less regular file. The hint names each accepted form (retrofit import - < <file>, retrofit import <(…)).
data-band-over-cap: a table’s INSERT band in one source is larger than the schema’s data_band_max_bytes (or the built-in default when the key is absent, including a schema this run would lazily create). The bound is checked per source file before any source is parsed and before anything is registered or written, so a batch with one over-cap source refuses whole; every over-cap (source, table) is named in one envelope. Raise data_band_max_bytes for the named schema, or remove the key for the built-in default.
state-source-on-populated-oplog: the source is a whole-schema state dump and the target oplog is already populated. See When a bare re-import is not what you meant for the three recovery verbs the hint names.
schema-not-in-oplog: the target oplog holds ops but declares no CREATE SCHEMA whose name folds to the resolved --schema. Evaluated on oplog state alone; --force re-bootstraps past it, and schema remove drops the file.
unknown-flag: a flag import does not register. The retired flag that asked import to extend a populated oplog is one: import extends one by default, so the flag was withdrawn with no alias and no deprecation window, and typing it now lands here with a hint saying to drop it.
rule-* (and explicit wire-stable codes like change-primary-key-dangling-fk): an emitted op failed its precondition validator (e.g., CreateTable on a table that already exists in the cut, AddColumn on a column that already exists). The envelope names the first failing op. When the source shape suggests the database has moved without you (duplicate CreateTable, dangling FK reference on a populated oplog), the hint points at retrofit drift. See drift.md for the converge-to-state use case.
schema-mismatch: --schema <X> was passed, but the source declares or references a different user schema (CREATE SCHEMA <Y> or <Y>.<table>). Pass --schema <Y> or split the source.
default-schema-unset: the resolver chain produced no result (no --schema, no -f, no [directory_scope] match, no implicit oplog-dir match, no default_schema). Pass --schema, --chdir into a project dir, or run schema set-default.
no-project: running outside a retrofit.toml-walkable tree. import needs a registry to mint into; run retrofit init first.
label-empty: a --label value was empty or whitespace-only.
skip-manifest-clobbers-managed-file: the --skip-manifest or --summary-file path resolves to a file the run manages: the oplog, retrofit.toml (wherever --config or RETROFIT_CONFIG put it), or any name the run could mint or read under the data root, whether or not that file exists yet and whether data_root is declared or defaulted. Any other name under the data root is allowed, at any depth. The check follows where the path lands, so .., symlinks and case variants on a case-insensitive filesystem are caught, and it runs before anything is written. Choose a path outside the managed files.
summary-file-collides-with-skip-manifest: --summary-file and --skip-manifest resolve to the same file. The two sinks compose, so the summary object would land last and overwrite the per-skip JSONL; the run refuses in the same preflight instead. Give each flag its own path.
data-file-collision: a re-ingest collides with another key’s data file at the mint path, a hand-authored (src=hand) file, or a claim sharing some but not all of its labels. Relabel the new set as its own cohort, or rm the existing file if it should not exist.
data-witnessed-key: the mint key carries a governing witness. Capture at HEAD, retrofit op remove <witness opid> for a tail witness nothing anchors on, or retrofit op unseal when the key is sealed.
dml-table-not-found: an authored data statement’s table does not stand at the op its rows anchor on (for a data-only import, the oplog head; an empty oplog models no table). Import or author the table first.
import-move-column-fold-mismatch: import folded an ADD COLUMN, a key-correlated UPDATE (a down’s pad INSERT first) and a DROP COLUMN into one keyed MoveColumn, but the second pass minted no move or derived another arm than the fold was judged by (exit 2). The source is refused rather than the statement dropped; the message names the statement and, for an arm mismatch, both arms. The hint asks you to report the script and, as a workaround, to split the move into DropColumn + AddColumn and author the data step with op add.
parse-error: the source SQL failed to parse. Line number in the envelope.
arg-binding: - (stdin) was combined with other sources, appeared more than once, or appeared inside --from-file (stdin can only be drained once and is excluded from curated listings); or the invocation supplied no sources at all (no positionals and no --from-file, or a --from-file whose body parsed to zero paths).
io-error: the source file is unreadable, the oplog cannot be written. Detail line carries the syscall reason.

Scope contract

import’s in-scope set is every operator whose DDL the walker recognizes, plus the two shapes recognized from their full evidence (see What import cannot produce, and why). The recognized DDL shapes are listed in § What import recognizes above; the full per-operator inventory is in smo-catalog.md.

The pg_dump round-trip story (what import accepts that pg_dump emits, where the parities sit) lives in pg_dump-parity.md.

concepts.md: oplog, cut, element identity.
drift.md: the “modify a dump, reconcile” verb for what import cannot infer.
pg_dump-parity.md: option-by-option audit of import’s pg_dump alignment.
smo-catalog.md: every operator with status.
cli-output-contract.md: per-verb stdout / stderr / exit-code shape.