Reference data

ptah seed applies SQL seed files imperatively — “run these INSERTs once.” Ptah also supports a declarative model for reference/lookup tables: declare the rows a table should contain, and let Ptah diff them against the live table and generate a reversible data migration (INSERT/UPDATE/DELETE) that reconciles the two.

Atlas keeps declarative data and data-migration generation in its proprietary Pro build (an Atlas account and the closed-source binary). Ptah provides it as an MIT, local, no-account, embeddable capability.

Declaring managed data

Attach a //ptah:schema:data annotation to a Go entity. It names the target table, the key column(s) that identify a row, and a YAML file holding the desired rows:

//ptah:schema:data table="countries" key="code" file="countries.yaml"
type Country struct {
    //ptah:schema:field name="code" type="VARCHAR(2)" primary="true"
    Code string
    //ptah:schema:field name="name" type="VARCHAR(255)" not_null="true"
    Name string
}

The row-data file is resolved relative to the Go source directory, and planning reads it from there. A path that leaves the project the command was pointed at is refused, naming what it reached for:

error: managed data file "../../secret.yaml" for table regions: ".../secret.yaml" is outside allowed root ".../project"

The project is the first --root-dir, or the working directory when only --schema-file was given. It is the project rather than the directory holding the schema because ptah schema export writes a row path back out of the directory it exports into, and both spellings stay readable as long as they stay inside the project. This is the boundary file() in atlas.hcl already has: a declaration is data, and a desired schema is not always one the reader wrote.

The file itself is a top-level YAML list of column maps:

- code: US
  name: United States
- code: CZ
  name: Czechia

Use a comma-separated key for composite keys (key="tenant_id,code").

Add an optional schema="..." to target a table in a non-default schema; both the live-row read and the generated DML are then schema-qualified (for PostgreSQL, "reference"."countries"). Omit it to use the connection’s default schema.

Generating a data migration

ptah migrations data \
  --root-dir ./models \
  --db-url postgres://user:pass@localhost/db \
  --migrations-dir ./migrations

For each managed table, Ptah loads the desired rows, reads the live rows for the managed columns (the key columns plus every column named in the desired rows), and diffs them by key:

• a desired row with no live match → INSERT;
• a key present in both whose managed columns differ → UPDATE of the changed columns;
• a live row with no desired match → DELETE.

It writes an ordinary migration pair (NNNNNNNNNN_data.up.sql / .down.sql) and refreshes ptah.sum, so the data migration applies and rolls back like any other. --dry-run prints the SQL instead of writing files; a run with no drift writes nothing.

A row file naming a column the live table does not carry is refused, naming the columns: the read asks the database for a name it cannot resolve, and SQLite answers such a name with the name itself, which would reach the rollback as the value each row is restored to. Apply the schema change first, or take the column out of the row file.

Which live table a declaration names, and which of that table’s columns it names, are decided under the engine’s own identifier rules. Oracle folds a bare name, so a table created as regions is REGIONS in its catalog and a declaration written as regions with a code key names it and its CODE column. An engine that keeps case keeps it, so there REGIONS and regions are two tables. The same rules answer for ptah schema drift, ptah schema compare and the data stage of ptah schema plan, which read the declared rows through one comparison.

Safety gates

A data migration is applied through the ordinary migration path, where neither the lint nor the safety gate classifies row INSERT/UPDATE/DELETE as destructive. So ptah migrations data gates destructive changes when it generates them:

• Unless --allow-destructive is set, a migration that would UPDATE or DELETE existing rows is refused with a per-table summary of the volume. Insert-only migrations are additive and are always allowed.
• Naming a table with --protected-table refuses any change to it — insert, update, or delete — unless --allow-prod is also set, mirroring the protected-target posture of ptah seed. An entry matches a managed table by its bare name (regions) or its schema-qualified name (reference.regions), so either spelling protects a table declared with schema="...".

Both gates run before any SQL is emitted, so they apply to --dry-run too: combine --allow-destructive --dry-run to preview a destructive change without writing files.

Reversibility

The generated down is the exact inverse of up: an inserted row’s down is a keyed DELETE, an update’s down restores the prior values, and a deleted row’s down re-inserts it. Applying up then down restores the original table contents.

Values are rendered as dialect-correct, safely-escaped SQL literals, so a value containing quotes, backslashes, or semicolons cannot break out of its literal. A value may span lines: the newline stays inside the literal, and the statement that carries it stays one statement.

The statement is the one the engine accepts, which is not the same spelling everywhere. ClickHouse refuses a plain UPDATE on a table without a materialized _block_number column, so a row change there is written as ALTER TABLE ... UPDATE; the server applies that as a background mutation, so the new value appears shortly after the statement returns rather than at once. A key column holding NULL is addressed with IS NULL: = NULL is UNKNOWN for every row, so the statement would succeed and match nothing.

On Oracle the statements name tables and columns the way the schema renderer created them: bare wherever Oracle accepts a bare name, so a declared regions reaches the REGIONS table rather than a quoted "regions" that does not exist. A BOOLEAN column is NUMBER(1) there and takes 1 or 0. A DATE or TIMESTAMP column takes a typed TIMESTAMP '...' literal, because Oracle refuses a plain string for a moment. Write a declared moment as 2024-03-01, 2024-03-01 12:30:45 or RFC 3339; other text for such a column is refused when the statements are rendered.

Managed tables are ordered by the schema’s foreign-key dependency graph: INSERTs run parents-first and DELETEs children-first, so a migration spanning FK-related reference tables applies (and rolls back) without violating a foreign key. The rank is one declaration, read by the migration body and by the plan ptah schema apply prepares. A managed table is matched to its //ptah:schema:table definition by qualified name, falling back to the bare table name when the schema attributes are not both set; a bare name two schemas share resolves to neither, and tables with no matching definition fall back to alphabetical order after the ones that have it.

Rows inside one table follow the same rule where the table references itself – a category tree, an org chart: a child row is written after its parent and removed before it. Two rows that reference each other stay in key order, because no arrangement satisfies a cycle and the server refuses it whichever row runs first.

Emptying a populated table’s desired set generates a reversible full-table delete: up deletes every live row and down re-inserts it from the table’s complete column set, read from the live schema so the rollback restores whole rows rather than the key columns alone.

The re-inserted columns carry the spelling the catalog reports, except the key columns, which carry the spelling the declaration gave them, because that is the name each live row is matched on. The two differ only where the engine folds names and both spellings therefore reach the same column: an Oracle rollback of a code-keyed table reads INSERT INTO regions (LABEL, code).

Generated/computed columns are excluded from the re-insert because the database recomputes them and inserting an explicit value for them errors; on rollback they recompute from the restored base columns.

Auto-increment and serial columns that accept explicit inserts (MySQL AUTO_INCREMENT, SQLite AUTOINCREMENT, PostgreSQL SERIAL, and GENERATED BY DEFAULT AS IDENTITY on PostgreSQL and Oracle) are re-inserted with their original values preserved.

If the table has an identity column that rejects explicit inserts — SQL Server IDENTITY, or GENERATED ALWAYS AS IDENTITY on PostgreSQL and Oracle — the full row cannot be restored, so this case is refused with an error naming the column; keep at least one desired row for such a table. The all-delete change is destructive, so it still requires --allow-destructive, and --protected-table still applies.

Reference rows in a schema plan

ptah migrations data writes a data migration into a directory, which is the versioned workflow. Direct schema changes reconcile the same rows without a migration file: ptah schema plan and ptah schema apply compare the declared rows against the database and include the statements that reach them.

The data stage runs whether or not the schema changed. A release that only edits a reference row changes no DDL, and a planner that stopped at the schema diff would report an empty plan for a change its author made.

What the plan carries is decided by what the declaration owns:

• a table the plan is about to create holds nothing, so every declared row is an INSERT and nothing is read back from a table that is not there;
• a column no declaration names is never read and never written, so a value beside the managed ones survives reconciliation untouched;
• a repeated reconciliation over converged rows plans nothing, including a date or timestamp column: the driver returns a moment where the declaration wrote text, and the two are compared as the instant they name. In a text column both spellings stay separate values. A declared number or boolean is compared the same way with the text a driver returns for a numeric column, which is how the Oracle driver reads NUMBER: 30 meets "30" and true meets "1".

The plan orders the statements the way the migration body does, from the same dependency rank: every INSERT and UPDATE parents-first, then every DELETE children-first. Two reference tables joined by a foreign key are therefore applied in one run, against the constraint the same plan created a few statements earlier.

On SQLite, a plan that also rebuilds a table carries the rows inside the rebuild’s PRAGMA foreign_keys pair, ahead of the pragma that turns enforcement back on. The rows then run with enforcement suspended, like the rebuild, and the foreign-key check the apply runs before it commits refuses the whole plan when a row names a parent that does not exist. See SQLite.

Each planned statement writes or removes one row and is carried whole. A declared value that spans lines keeps its newline inside the literal, so the statement keeps the severity and the place in the order of the row it changes.

Severity is assigned by what the statement does to the rows, not by what a SQL analyzer makes of it:

Statement	Severity
INSERT of a declared row	safe
UPDATE of managed columns	warning
DELETE of a row the declaration no longer holds	destructive

The analyzer reads all three as safe, because none of them removes a table or tightens a constraint. That is true about the schema and false about the rows, and a plan whose deletions read as safe is a plan an approval policy waves through.

Fencing a table off

--protected-table names a declared row set the plan or the apply must not change, and it repeats: ptah schema apply --protected-table reference.regions. An entry matches the way ptah migrations data reads it — by the bare name or the schema-qualified one, case-insensitively — because one predicate answers for both paths.

The fence has no override here. A severity is a question put to a policy, and every mechanism that rates a statement can answer yes: an approval, a flag, a policy that permits destructive changes. A fenced table is the statement that no such yes exists for it, so a refusal a caller could wave through would be the severity it already has. Where the change is wanted, the fence is what changes: drop the entry, or write the rows as a migration with ptah migrations data --allow-prod, which is the path that asks a person.

It refuses a change, not a run. A fenced table the declaration already agrees with plans nothing and passes, which is what lets an entry sit in a deployment’s configuration permanently. The flag reads PTAH_PROTECTED_TABLE, so an unattended reconciler declares the fence in its environment rather than in a command line it does not write.

The fence is read where the change is planned, on both paths. A plan file saved before the entry existed, and a data migration already written, are applied as written: the statements are there and the declaration is not.

Convergence includes the rows. A rehearsal or a post-apply verification whose schema matches and whose reference table does not is not a verification of the desired schema.

Reference rows in a drift check

ptah schema drift compares the declared rows too, and reports the difference as counts: one entry per drifted table under managed_data, plus the data_rows_inserted, data_rows_updated and data_rows_deleted findings. It reads the rows and never publishes one, so a caller that must not carry row data — a pipeline archiving the document, an operator writing it into a status field — reports the drift without reading a value. The SQL that closes the difference is what ptah migrations data and ptah schema plan answer with, and asking for it is the deliberate next step.

The drift check and the plan classify an UPDATE differently on purpose. The plan rates a statement, and overwriting managed columns is a warning there because the author asked for the new value. The drift finding rates what the database is about to lose: a live value nobody declared is gone once the statement runs, and it is the same loss --allow-destructive gates here. So data_rows_updated is destructive and a schema drift --severity destructive gate fails on a hand-edited reference row.

Declarative data versus ptah seed

ptah seed remains the imperative path — it runs environment-scoped SQL seed files once and tracks them in schema_seeds. Declarative reference data instead describes the desired rows and computes the migration to reach them, so drift (a changed lookup value, a removed row) is reconciled rather than reapplied. Use seed for one-off setup data, managed data for tables whose exact contents Ptah should own.

Embedding

The pieces are exported for embedding: migration/datadiff computes the diff and renders it as scripts (Render) or as one statement per element (RenderStatements), dbschema.ReadTableRows reads the live rows, and core/goschema carries the managed-data model — so the whole pipeline can be driven from Go with no CLI, account, or cloud.