# Reference data Declare reference/seed rows and generate reversible data migrations from the drift against a live database. Source: https://docs.ptah.run/v0.8.1/versioned/reference-data/ [`ptah seed`](../../operate/seed-data/) 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: ```go //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: ```text 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: ```yaml - 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 ```bash 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: `INSERT`s run parents-first and `DELETE`s 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](../../databases/sqlite/#what-renders-natively). 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`](../../direct/compare-and-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.