# database.migrations > Voltro's planner-based migration system — diff your declared schema against the live DB, classify each change, refuse-to-apply anything risky without explicit intent. Dev auto-applies, prod refuses. --- ## Migrations _Voltro's planner-based migration system — diff your declared schema against the live DB, classify each change, refuse-to-apply anything risky without explicit intent. Dev auto-applies, prod refuses._ Voltro's migrator does not generate or apply migration files. Instead, on every `voltro dev` boot — and any time you run `voltro db plan` — it: 1. **Introspects** the live database via `information_schema.*` (or the dialect-specific equivalent), building a `SchemaSnapshot`. 2. **Diffs** that against your declared schema (every `*.entity.ts` / `*.schema.ts` file in the project plus the framework's bookkeeping tables). 3. **Classifies** each pending DDL op into one of seven `OperationClass`es — `safe`, `needs-default`, `needs-backfill`, `needs-rename-annotation`, `lossy`, `online-required`, `multi-step`. 4. **Refuses** to apply anything that can't be made safe automatically. The diff output tells you exactly which DSL annotation to add (`.backfill()`, `.renamedFrom()`, `dropped()`, …). 5. **Applies** the rest under an advisory lock + records the result in `_voltro_migration_plans` with a fingerprint of the post-apply schema. On a dialect that cannot apply the whole plan in one transaction (MySQL / MariaDB / SQLite / Turso), each operation is also recorded in a **resume ledger** (`_voltro_migration_ops`) as it lands, so a crashed apply is continued rather than re-planned blind — see [multi-dialect](./multi-dialect.md). There are no generated SQL files to commit, no `migrations/` directory to rebase, no checksum manifest to repair. The source of truth is your schema TypeScript; the DB is the slave. ## The four-phase model ``` ┌───────────────┐ ┌─────────────────┐ ┌──────────────┐ ┌──────────────┐ │ declare │ → │ plan (diff + │ → │ review │ → │ apply │ │ schema in TS │ │ classify) │ │ (CLI / UI) │ │ (dev: auto, │ │ │ │ │ │ │ │ prod: CLI) │ └───────────────┘ └─────────────────┘ └──────────────┘ └──────────────┘ ``` - **dev**: phases 2–4 are automatic on boot. A blocked plan refuses the boot with a structured error pointing at the fix. When the declared schema is unchanged since the last apply, dev short-circuits on a **fingerprint check** (one indexed query) and skips the full introspect entirely — so reboots against a large schema stay fast. Force a full re-introspect (drift recovery) with `VOLTRO_MIGRATE_FORCE=1`. - **prod**: phase 2 (planning) is automatic, but phase 4 (apply) NEVER happens during a serving process. The fingerprint of the declared schema must already match `_voltro_migration_plans.fingerprint` from a prior explicit `voltro db apply` — mismatch → refuse to boot. This is the constraint repeated across the docs: **schema changes mid-rolling-deploy without review are not allowed**. ## A first session ```bash # 1. Edit apps/api/database/users.entity.ts — add a required column. export const users = table('users', { id: id(), email: text(), // NEW: required, no default }) # 2. voltro dev. The planner classifies "ADD COLUMN email NOT NULL" # on a populated table as `needs-backfill`. No backfill declared # → boot refuses with: # # auto-migrate: REFUSED — 1 blocked operation(s): # - add-column [users]: NOT NULL column on a table whose row count is unknown # fix: declare `email: ().backfill(sql\`...\`)` OR `.default(value)` # so existing rows survive the migration voltro dev . # 3. Add the backfill annotation in the schema: export const users = table('users', { id: id(), email: text().backfill(sql`'unknown-' || id || '@local'`), }) # 4. Boot again. The planner classifies the same op as `needs-backfill` # with a declared backfill — applier runs ADD nullable → UPDATE via # the SQL expression → SET NOT NULL inside one transaction. voltro dev . ``` Same column. Same migration. The first attempt refuses loudly; the second succeeds silently. The DSL annotation IS the migration plan. ## The seven operation classes Every pending op is stamped with one of seven `OperationClass` values — `safe`, `needs-default`, `needs-backfill`, `needs-rename-annotation`, `lossy`, `online-required`, `multi-step` — which drives whether it auto-applies or refuses-to-plan pending a DSL annotation. The full trigger + default-policy table, with a worked example and the exact fix for each blocked case, lives on the dedicated [Operation classes](./operation-classes.md) page. ## The CLI surface ```bash # Planner-based (declarative diff) voltro db plan # diff + color-coded classes + fix hints voltro db plan --against # diff vs a remote env's inspect endpoint (see cross-env-sync) voltro db apply # execute (dev only — refuses on NODE_ENV=production) voltro db apply --note '...' # apply with a freeform note recorded in history voltro db plans [--limit 20] # history from _voltro_migration_plans, newest first voltro db drift # live-vs-baseline check — exit 0 match, 3 no baseline, 4 drift voltro db drift --accept # record the CURRENT live schema as the baseline (refuses unless db plan is empty) voltro db squash --before # consolidate history into one snapshot voltro db restore-snapshot # restore VOLTRO_SOFT_DROP=1 columns from a plan # File-based escape hatch — migration() up/down files under migrations/ voltro db files # apply pending migration() files voltro db rollback-file # run a migration() file's down body # defineMigration step runner (separate system, _voltro_migrations table) voltro db migrate # apply pending *.migration.ts steps voltro db rollback [--to ] # undo applied *.migration.ts steps voltro db status # list applied / pending *.migration.ts ``` `plan` + `apply` are the planner-based commands. There are TWO distinct file-based runners, intentionally not unified: the `migration()` runner (`files` / `rollback-file`, records into `_voltro_migration_plans` with `source: 'file'`) is the escape hatch the planner points you at for table-splits and cross-table data moves; the `defineMigration` runner (`migrate` / `rollback` / `status`, its own `_voltro_migrations` table) runs hand-authored `*.migration.ts` step files. See [File-based migrations](./file-based.md) for the `migration()` path — the one most apps reach for. Note: `voltro db plan` is flagless beyond `--against` / `--token` — there is no `--json` or `--sql`, and `voltro db apply` takes only `--note`. The prod flow is a plain `voltro db apply` run as an explicit deploy step ([prod pipeline](./prod-pipeline.md)), not a pre-serialised plan file. ## Framework tables ride the same differ The `_voltro_*` tables the framework owns are planned, classified and applied by exactly the same code as yours — on every dialect. A framework release that adds a table, adds a column or reshapes one lands on the boot that follows your upgrade, wherever your own schema changes land. There is no separate command and no dialect-specific step. One asymmetry is deliberate and worth knowing if you ever read a plan: a framework-owned table that **nobody declares** — `cluster_*` from the workflow engine, a plugin's table after you removed the plugin — is never planned for a drop. "Nobody declared it, so do not drop it" and "we declare it, so keep it current" are different rules; collapsing them is what once made framework tables evolve on postgres and nowhere else. ## Where to go next | Topic | Page | |---|---| | Every operation class with concrete examples + each fix | [Operation classes](./operation-classes.md) | | SQL vs JS backfill, performance, batch tuning | [Backfill](./backfill.md) | | `.renamedFrom()` + `dropped()` lifecycle + when to remove the markers | [Rename and drop](./rename-and-drop.md) | | MySQL implicit commit, SQLite table rewrite, per-dialect atomicity matrix | [Multi-dialect strategy](./multi-dialect.md) | | CONCURRENTLY / batched backfill / shadow-column for large tables | [Online migrations](./online.md) | | File-based escape hatch for table-split / merge / data moves | [File-based migrations](./file-based.md) | | The plan-review-apply pipeline for production | [Prod pipeline](./prod-pipeline.md) | | Local devtools dashboard walkthrough | [Devtools UI](./devtools-ui.md) | | Cloud dashboard walkthrough + multi-tenant boundaries | [Cloud UI](./cloud-ui.md) | | What can be reversed (and why planner plans have no auto-rollback) | [Rollback](./rollback.md) | | Drift detection + recovery | [Drift](./drift.md) | | Consolidating an aged history into one snapshot | [Squashing](./squashing.md) | | `VOLTRO_SOFT_DROP=1` + restore-snapshot — the only path that recovers dropped-column data | [Soft-drop recovery](./rollback-snapshots.md) | | `voltro db plan --against ` for pre-deploy preview | [Cross-environment sync](./cross-env-sync.md) | | The most common "why is my boot refusing?" cases | [Troubleshooting](./troubleshooting.md) | --- ## Operation classes _The seven classification buckets the planner sorts every diff into — what triggers each, what the planner does by default, and the exact DSL annotation that turns a blocked op into an allowed one._ Every concrete DDL operation the planner emits gets stamped with one of seven `OperationClass` values. The class drives the default policy + the refuse-to-plan message you'll see when something needs human input. This page enumerates them with a fixture-style example per class. ## safe The op is reversible AND has no effect on existing data. Auto-applied on every dev boot + by `voltro db apply` in any env. Triggers: - `CREATE TABLE` (no live data possible) - `ADD COLUMN ` — new column starts NULL - `ADD INDEX` (small tables; large tables get promoted to `online-required`) - `DROP INDEX` - `ADD UNIQUE` — adding `.unique()` to an existing column whose live values are already distinct (emitted as the `__key` constraint). Applies on the next boot; if the column already holds duplicates the DB rejects it **at migrate time** (fail-fast) rather than letting an `ON CONFLICT` upsert break at runtime. Pre-dedup a populated column with `.unique({ dedup })` — see the decision table. `DROP UNIQUE` (removing `.unique()`) is safe too. - `ADD CHECK` on a new column - Widening a type (`varchar(50)` → `varchar(255)`, `int` → `bigint`) - Dropping `NOT NULL` (NULL → optional is monotonic) ```ts // Before: export const users = table('users', { id: id(), email: text() }) // After — ADD bio (nullable) → safe. export const users = table('users', { id: id(), email: text(), bio: text().nullable(), }) ``` Plan output: ``` ✓ ALTER TABLE users ADD COLUMN bio text # nullable column add — no backfill needed ``` ## needs-default ADD NOT NULL column where the schema declares `.default(value)`. The planner emits `ADD COLUMN … NOT NULL DEFAULT ` in one statement. Postgres 11+ records the default in the catalog without rewriting the table — instant on a 100M-row table. ```ts export const users = table('users', { id: id(), plan: text().default('free'), }) ``` Plan output: ``` ⊕ ALTER TABLE users ADD COLUMN plan text NOT NULL DEFAULT 'free' # literal default ``` Cross-dialect note: MySQL strict mode + a `TEXT` column with a `DEFAULT` clause throws at DDL time. Use `varchar(N)` (`.maxLength(N)` on `text()`) for text columns that need a default on MySQL. The framework's [multi-dialect strategy](./multi-dialect.md) page covers the other landmines. ## needs-backfill ADD NOT NULL column on a populated table where the schema declares `.backfill()`. The planner emits a three-step plan inside one transaction (or one forward-roll group on mysql/mariadb): 1. `ADD COLUMN ` (nullable) 2. `UPDATE
SET = ` 3. `ALTER COLUMN SET NOT NULL` ```ts export const users = table('users', { id: id(), email: text().backfill(sql`'unknown-' || id || '@local'`), }) ``` Plan output: ``` ⊕ ALTER TABLE users ADD COLUMN email text # 3-step: ADD nullable ⊕ UPDATE users SET email = 'unknown-' || id || '@local' # → run backfill ⊕ ALTER TABLE users ALTER COLUMN email SET NOT NULL # → SET NOT NULL ``` Without the `.backfill()` annotation: **blocked**. The fix hint surfaces in both `voltro db plan` and the boot refuse message: ``` ✗ ALTER TABLE users ADD COLUMN email text # NOT NULL column on a table whose row count is unknown ! fix: declare `email: text().backfill(sql`...`)` OR `.default(value)` so existing rows survive ``` For the SQL-vs-JS backfill trade-off + per-batch tuning see [Backfill](./backfill.md). ## needs-rename-annotation Column X disappeared from the declared schema AND column Y appeared with similar shape. The planner won't silently turn that into `DROP X` + `ADD Y` (data loss). It refuses-to-plan unless the new column carries `.renamedFrom('X')` — the explicit signal that intent is RENAME, not DROP+ADD. ```ts // Before: export const users = table('users', { id: id(), firstName: text() }) // After — without the marker, planner refuses: export const users = table('users', { id: id(), givenName: text() }) // With the marker — planner folds the diff into one RENAME op: export const users = table('users', { id: id(), givenName: text().renamedFrom('firstName'), }) ``` The annotation stays in the code until the rename has been applied in every env you care about (dev, staging, prod). `_voltro_migration_plans` records the applied rename so the planner won't re-emit; removing the marker earlier yields a clear refuse-to-plan ("did you remove `.renamedFrom('firstName')` before staging migration applied? Re-add the marker oder apply against staging first"). [Rename and drop](./rename-and-drop.md) covers the full lifecycle. ## lossy The op destroys data. Refuse-to-plan unless the developer declared intent explicitly OR `VOLTRO_DESTRUCTIVE_OK=1` was set. Triggers: - `DROP COLUMN` (live data is gone after apply) - `DROP TABLE` - Narrowing a type (`varchar(255)` → `varchar(50)` with strings longer than 50) - `DROP UNIQUE` constraint that other code might depend on - `DROP INDEX` that an FK depends on ```ts // Drop a column INTENTIONALLY — declare `dropped()`: export const users = table('users', { id: id(), legacy: dropped(), // ← explicit. Planner classifies lossy, allows apply. }) ``` ``` ⊕ ALTER TABLE users DROP COLUMN legacy # column dropped via `dropped()` marker — intentional ``` Without the marker: ``` ✗ ALTER TABLE users DROP COLUMN legacy # column missing from declared schema ! fix: if intentional, add `legacy: dropped()` to the schema. If a typo, restore the field ``` DROP TABLE has no equivalent annotation — the table simply being missing from the declared set is the signal. Set `VOLTRO_DESTRUCTIVE_OK=1` to allow it (loud warning), or use a [file-based migration](./file-based.md) for cross-table data moves the diff can't infer. `VOLTRO_DESTRUCTIVE_OK=1` only relaxes the refusal when EVERY blocked op is `lossy`. Rename-without-marker and NOT-NULL-without-backfill stay firm regardless — those are sloppy declarations, not intentional destruction. ## online-required The op operates on a table whose row count exceeds the planner's online threshold (default 50k; tunable via `online-after` per project). Auto-rewritten to a non-blocking variant: - `ADD INDEX`: Postgres `CREATE INDEX CONCURRENTLY`, MySQL/MariaDB `ALGORITHM=INPLACE LOCK=NONE`, MSSQL `WITH (ONLINE = ON)`, SQLite no-op - `UPDATE` backfill: batched (default 1k rows/batch, configurable `.backfill(sql, { batchSize: 5000, sleepMs: 50 })`) - Type rewrites that need shadow-column-swap (`ALTER COLUMN TYPE` on large tables) [Online migrations](./online.md) walks through every variant with sizing + tuning guidance. ## multi-step Operations the planner can't infer from a structural diff alone: - Splitting a table (e.g. extract address fields to a separate `addresses` table with FK back) - Merging two tables - Type changes that need a custom `USING` expression (`text → integer` requires `USING col::integer`) - Data moves that span multiple tables atomically The planner refuses-to-plan these + points at the [file-based migrations](./file-based.md) escape hatch. You write the migration body explicitly (up/down SQL or Effect program) and the framework picks it up in timestamp order before the next auto-diff pass. ## Decision table at a glance | Want to … | Add this to the schema | |---|---| | Add a required column with a constant default | `.default(value)` | | Add a required column on a populated table | `.backfill(sql\`expr\`)` | | Rename a column without losing data | `.renamedFrom('oldName')` on the new column | | Drop a column intentionally | `legacy: dropped()` at the field-map slot | | Drop a table intentionally | (remove from declared set) + `VOLTRO_DESTRUCTIVE_OK=1` for one apply | | Add a unique constraint on a populated column with dupes | `.unique({ dedup: 'fail' / 'suffix-counter' })` | | Add an FK on a populated column with orphans | `reference(() => target, { orphanPolicy: 'fail' / 'null' / 'delete' })` | | Change a column type with a custom cast | `.narrowedFrom('', { using })` on the column — the planner downgrades the blocked-lossy type change to `needs-backfill` and threads the `USING` cast (see [rename and drop](./rename-and-drop.md)) | | Move data across tables atomically | [file-based migration](./file-based.md) | --- ## Rename and drop _The two annotations that gate destructive-looking changes — .renamedFrom() turns a DROP+ADD diff into a RENAME, dropped() turns a refused DROP into an allowed one. Lifecycle + when to remove the markers._ The planner refuses to silently rename or drop columns. Both ops can look identical structurally — column X gone, column Y present — but mean very different things. The annotations give you the vocabulary to declare intent. ## `.renamedFrom(oldName)` ```ts // Before: export const users = table('users', { id: id(), firstName: text() }) // After — without the marker: export const users = table('users', { id: id(), givenName: text() }) // → planner classifies as DROP firstName + ADD givenName // → ADD givenName lands as `needs-backfill` (blocked, no backfill declared) // → boot refuses // With the marker: export const users = table('users', { id: id(), givenName: text().renamedFrom('firstName'), }) // → planner folds the diff into one RENAME COLUMN op, classified `safe` // → boot applies it via `ALTER TABLE users RENAME COLUMN firstName TO givenName` ``` The marker says: "the column previously named `firstName` should be the column now declared as `givenName`". The planner verifies the live DB has a column called `firstName` matching the new column's shape (type, nullable, default). Mismatch → refuse with a helpful error. ## When `.renamedFrom()` doesn't fold If the live DB doesn't have a column called `firstName`, the marker is a no-op: - Maybe the rename was already applied → `firstName` is gone, `givenName` is there → diff is empty → no folding needed - Maybe you typo'd the old name → live has `first_name` not `firstName` → planner falls back to treating `givenName` as a new column (which IS `needs-backfill` → refuses) The marker isn't validated against the live DB at schema-build time — it would have to introspect during type-checking, which is expensive. The runtime check fires at plan time. ## The indexes come with it Renaming a column is a metadata-only operation. Its **indexes** used to not be: index names are derived (`
__idx`), and no dialect renames an index when the column under it is renamed — so the planner saw `users_firstName_idx` on one side and `users_givenName_idx` on the other, and planned `DROP INDEX` + `CREATE INDEX`. On a large table that is a full B-tree rebuild: minutes of IO, and without `CONCURRENTLY` a write lock, behind a rename that was supposed to be instant. The planner now folds that into a `rename-index` operation, which is a catalog-only statement everywhere it is emitted: ``` ✓ rename-column users.firstName → users.givenName # catalog-only ✓ rename-index users_firstName_idx → users_givenName_idx # catalog-only, no rebuild ``` You do not annotate anything for this — it follows from the column rename you already declared. Four cases deliberately still plan as drop + create, because pairing an old index with a new one has no evidence to stand on in them: - **sqlite** — it has no rename statement at all. The plan you read matches what runs. - **UNIQUE indexes** — they are constraint objects, and the syntax to rename one diverges by dialect. - **Expression / json-path indexes** — the database normalises their key text, so there is no shape to compare; only the name, which is the thing that changed. - **Two same-shaped indexes renamed at once** — nothing says which became which. Rebuilding both is slower; renaming the wrong one is worse. ## Renaming a TABLE The same problem one level up, and with more at stake: a table rename and a drop+create look identical to the differ — old table gone, new table present — except that guessing wrong costs every row. So it needs a marker too, and it reads like its column counterpart: ```ts // Before: export const notes = table('notes', { id: id({ prefix: 'note' }), body: text() }) // After — without the marker: export const notes = table('archive_notes', { id: id({ prefix: 'note' }), body: text() }) // → planner sees DROP TABLE notes + CREATE TABLE archive_notes // → the DROP is `lossy` and blocked; nothing happens until you acknowledge it // With the marker: export const notes = table('archive_notes', { id: id({ prefix: 'note' }), body: text() }) .renamedFrom('notes') // → one `rename-table` op, classified `safe` // → `ALTER TABLE notes RENAME TO archive_notes` — catalog-only, the rows stay put ``` Unlike an index rename, every dialect has this statement — sqlite included — so there is no dialect on which this falls back to a rebuild. **Its indexes come with it.** The same derivation that bites a column rename bites harder here: `notes_pkey` and `notes__idx` are named after the table, and no dialect renames them when the table is renamed. The planner emits a `rename-index` for each so the catalog catches up: ``` ✓ rename-table notes → archive_notes ✓ rename-index notes_pkey → archive_notes_pkey ``` Without that the plan would try to drop the primary-key index and re-add it as a plain UNIQUE, which postgres refuses outright. **Three cases where the planner will NOT fold the rename**, each because folding it could destroy data rather than move it — and none of them is silent: - **The old name is still declared by something.** If your schema still has a `notes` table, it is yours and stays put; the new table is created empty. This is a legitimate outcome (it is what lets a framework plugin reclaim a name without taking yours), so the plan runs — and the `create-table` line says why the marker was not applied. - **The new name already exists in the database.** → **refuses to plan.** Both tables exist and only you know which holds the real rows. The fix tells you to move them and drop one, or drop the empty one so the rename can run. Until then the old table is untouched. - **Two tables both claim the same old name.** → **refuses to plan.** Nothing says which should receive the rows; remove the marker from all but one. The last two refuse rather than degrade, because the quiet outcome — an empty plan reading "schema up to date" while the old table still holds every row — is the one that loses data by inaction. **Lifecycle.** Same as `.renamedFrom()` on a column: a marker whose old table is not in the database is a silent no-op, so it stays in your source across a staged rollout and comes out once every environment has applied it. **One constraint worth knowing:** a table whose name starts with `_` cannot derive a typeid prefix, so it needs an explicit `id({ prefix: '…' })`. You will hear about it at declaration, not at runtime. ## Lifecycle — when to remove the marker Keep the marker until the rename has been applied in EVERY env you care about (dev, staging, prod). The framework tracks applied ops in `_voltro_migration_plans`: ``` dev ← rename applied at 2026-04-15. Marker can come out. staging ← rename applied at 2026-04-18. Marker can come out. prod ← rename NOT YET APPLIED. ``` Pull the marker too early and the next `voltro db plan` against prod sees: ``` ✗ ALTER TABLE users DROP COLUMN firstName ✗ ALTER TABLE users ADD COLUMN givenName text ! fix: did you remove `.renamedFrom('firstName')` before staging migration applied? Re-add the marker OR run `voltro db apply` against staging first. ``` Practical rule: the marker stays in the codebase across the rollout. Once `voltro db drift` shows clean against the last env (usually prod), the rename is fully applied + the marker can come out in a follow-up PR. ## `dropped()` ```ts import { dropped } from '@voltro/database' // Drop a column on a populated table — without the marker, refused: export const users = table('users', { id: id() }) // `legacy` simply gone // → planner sees `users.legacy` in live but not declared → classifies lossy → blocked // With the marker: export const users = table('users', { id: id(), legacy: dropped(), // ← explicit intent }) // → planner classifies lossy + ALLOWED (intent declared) // → applier emits ALTER TABLE users DROP COLUMN legacy ``` The marker fills the field-map slot the column used to occupy, telling the planner: "this column existed in the live DB AND is intentionally going away". The planner now classifies the drop as lossy-but-intended, which auto-applies. `dropped()` is a column-shape no-op at runtime (the migration emitter emits a DROP COLUMN, then the column is gone). It's purely planner metadata. ## Lifecycle — when to remove `dropped()` After the drop has been applied in every env, remove the field-map entry entirely. The next plan sees nothing to do for that column (the live DB no longer has it, the declared schema no longer references it). If you pull the `dropped()` marker before the drop has applied to all envs, the planner sees the column in live + the column ABSENT from declared → classifies as a fresh `DROP COLUMN` → blocked again with the same "add `dropped()` marker" fix. You'd just have to re-add it; no harm, no data loss. ## Dropping a table There's no `dropped()` equivalent for tables. The table simply being absent from the declared set IS the signal: ```ts // Remove the entire users.entity.ts file or its export from database/index.ts // → planner sees `users` in live but not in declared → lossy DROP TABLE → blocked ``` To allow it, set `VOLTRO_DESTRUCTIVE_OK=1` on the apply: ```sh VOLTRO_DESTRUCTIVE_OK=1 voltro db apply --note 'retiring users table after migration to user_accounts' ``` `VOLTRO_DESTRUCTIVE_OK=1` only relaxes the refusal when EVERY blocked op is `lossy`. If the plan also has a rename-without-marker or a NOT-NULL-without-backfill, those stay refused regardless. For complex multi-table retirements (move data out, then drop), use a [file-based migration](./file-based.md) — explicit ordering + a transaction wrapped around the data move. ### Name the tables, not the whole run `VOLTRO_DESTRUCTIVE_OK=1` acknowledges **every** lossy op in the plan. That is rarely what you mean — a plan with one intended drop and three other lossy ops would have all four approved by a single `1`. Give it a comma-separated list instead: ```sh # Only these tables — every other lossy op in the plan stays refused. VOLTRO_DESTRUCTIVE_OK=users,legacy_notes voltro db apply --note 'retiring the pre-migration tables' ``` An op the list does not name stays blocked, and a plan with anything still blocked is refused as a whole. Half a plan applied is how a schema ends up in a state neither the declaration nor the database describes. There is deliberately **no `.dropped()` marker for a table**, unlike for a column. A dropped column leaves a slot worth documenting in the declaration; a dropped table leaves nothing, so the marker would be a dead entry you have to remember to delete. ## `.narrowedFrom()` for type changes A bare column type change is **refuse-to-plan** — the planner blocks it (the same way a `drop-column` without `dropped()` is blocked), because a raw `ALTER COLUMN … TYPE` may not be value-preserving and fails outright at the DB for non-implicit casts. Acknowledge the change with `.narrowedFrom(, { using })`: the planner downgrades it to `needs-backfill` and threads the cast into the applier's `ALTER COLUMN … TYPE … USING ` (and the online shadow-column copy). ```ts // Before: status: text() // After: export const orders = table('orders', { id: id(), status: text().oneOf(['pending', 'shipped', 'delivered']).narrowedFrom('text', { using: 'status::status_enum', }), }) ``` - **`from`** is the type the LIVE DB currently has. It MUST equal the live column type — a stale `from` (the column already changed, or you named the wrong prior type) is ignored and the change stays blocked-lossy. - **`using`** is the raw cast expression spliced verbatim into `ALTER COLUMN … TYPE … USING ` (postgres) / the batched shadow-copy (`shadow := (old)`) on the online path. It's developer-authored migration SQL — keep it portable or dialect-correct for your target. - **Omit `using`** when the conversion is implicit on the dialect (e.g. `varchar → text`): the planner still downgrades the change, and the applier emits a plain `ALTER COLUMN … TYPE` with no `USING`. For a non-implicit cast with no `using`, the DB rejects the apply — declare the cast. ## `orphanPolicy` — adding an FK to a populated column Promoting an existing `text()` column to `reference()` (common when a column already holds the target's id as a plain string — e.g. data migrated from another system) is NOT a type change: a `reference` is TEXT-storage on every dialect, so the planner collapses the type diff to a no-op. The only real change is the FK CONSTRAINT, which `db apply` adds with `ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY …`. By default the FK-add just applies — classified `needs-backfill`, exactly like tightening a column to NOT NULL: the existing rows must already satisfy it. If a row is an **orphan** (its value points at a target that doesn't exist) the `ADD CONSTRAINT` fails at the DB, the whole apply rolls back atomically, and the failing statement is surfaced. `orphanPolicy` (on the reference) tells the applier to clear orphans FIRST so it can't fail: ```ts // Was `authorId: text()`. The column already has data, possibly with orphans. authorId: reference(() => users, { orphanPolicy: 'null' }).nullable(), ownerId: reference(() => orgs, { orphanPolicy: 'delete' }), ``` - **`'fail'` (default)** — bare `ADD CONSTRAINT`, **applies** (`needs-backfill`, not blocked). The DB rejects it only if an existing row is an orphan — then declare `'null'` / `'delete'` and re-apply. (The planner is pure — it can't read row counts, so it can't distinguish a clean / empty table from one with orphans; blocking by default would refuse every clean case too.) - **`'null'`** — the applier runs `UPDATE child SET col = NULL WHERE col` *references a missing target* BEFORE `ADD CONSTRAINT`. Requires the column be `.nullable()` (else the NULL-out would violate NOT NULL — the planner blocks it with that exact hint). - **`'delete'`** — the applier runs `DELETE FROM child WHERE col` *references a missing target* first (removes the orphan ROWS — destructive). `'null'` / `'delete'` apply via plain `db apply` — the policy IS the acknowledgement, exactly like `.narrowedFrom(...)` for a type change — and show in the plan as `lossy` with a reason naming the orphan handling. `orphanPolicy` is planner metadata only (no runtime/query effect), and once applied a re-plan is a no-op (introspection reports the FK; the policy is stripped from the comparison). On **sqlite** an FK change rebuilds the table, so the orphan pre-step is skipped — clean the orphans yourself there. ## Markers don't pile up Each marker maps to ONE applied op. The next migration after a rename + drop has clean code: ```ts // Before the rollout: export const users = table('users', { id: id(), givenName: text().renamedFrom('firstName'), legacy: dropped(), }) // After the rollout finished + applied in every env: export const users = table('users', { id: id(), givenName: text(), // legacy: dropped() removed entirely — the field-map slot disappears too. }) ``` The cleanup is a separate PR after the migration has rolled out. Don't mix the rollout PR with the cleanup PR — the markers ARE the migration's audit trail until it's applied everywhere. ## `voltro evolve` — the schema-evolution copilot Adding the `.renamedFrom()` marker by hand is easy for one column. The hard part of changing an EXISTING schema is the *rest*: which handlers read or write that column, whether a rename is safe or needs a backfill, and getting the annotation onto the entity AND every call site without missing one. `voltro evolve` does that reconnaissance and proposes a reviewable plan. Given a change, it reads the **observed graph** (`app.graph.observed.generated.json`, recorded by `voltro dev` — see [`voltro check`](/docs/cli/inspect)) plus the declared manifest, enumerates the real blast radius, and prints three things: a proposed **codemod**, a branch-verified **backfill** plan, and a `voltro check` **verify** step. ```bash voltro evolve rename-column notes.title --to heading # dry-run: plan + codemod preview voltro evolve rename-column notes.title --to heading --write # apply the codemod voltro evolve retype-column orders.total --to numeric # reshape → manual codemod + steps voltro evolve split-column users.name --into firstName,lastName voltro evolve rename-table note --to notes voltro evolve drop-column notes.legacy voltro evolve rename-column notes.title --to heading --json # for CI / an agent loop ``` It is **dry-run by default** (mirrors `voltro generate`); `--write` applies the codemod through the same `runCodemods` toolkit as `voltro update`. `--json` emits the whole plan for CI or an agent. ### The blast radius is observed, not guessed ```text change: rename-column notes.title → heading blast radius (observed + declared): query(notes.list) — observed read mutation(notes.update) — observed update ⚠ 1 declared but NEVER exercised — column use UNKNOWN: notes.archive codemod: entity: rename 'notes.title' → 'heading' and add .renamedFrom('title') (catalog rename — data preserved) annotate 2 handler site(s) that reference the old field backfill (dry-runs on branch notes-pr-0): [safe] catalog RENAME — no data movement verify: voltro check ``` A handler that a `voltro dev` session or a test actually ran is reported with what it did (`observed read` / `observed update`). A handler that is *declared* to touch the table but was **never exercised** is flagged `UNKNOWN` and listed separately — it is never folded into "safe", because no run proves what it does with the column. That honesty is the point: the tool tells you exactly where it cannot vouch for the change. ### What the codemod does per kind - **`rename-column`** gets a real `transform`: it renames the field in the `*.entity.ts` AND chains **`.renamedFrom('old')`** (so the differ plans a catalog RENAME, not the lossy drop+create described [above](#renamedfromoldname)), then annotates the handler sites the blast radius found. - **`retype-column` / `split-column` / `drop-column` / `rename-table`** are reshaping changes with no single mechanical rewrite, so they get a **`manual`** codemod: a generated, numbered checklist of the edits + the annotation to add, printed for you to apply. `voltro evolve` produces the plan; it does not apply the schema change. **`voltro check` is the gate on the result**, and `voltro db apply` lands it — after `--write`, review the annotated handlers, then run those two. --- ## Backfill _Two backfill flavors — server-side SQL expressions and per-row JS functions. Decision rubric, batch tuning, performance trade-offs, and what happens when a backfill itself fails._ When you add a NOT NULL column to a populated table, the planner needs a value for every existing row before it can land the `SET NOT NULL` constraint. The `.backfill()` annotation declares that value. The planner classifies the op as `needs-backfill` (allowed), the applier runs the three-step plan. Two flavors: - **SQL backfill** — `.backfill(sql\`expression\`)`. The applier emits one `UPDATE` statement; the database does the work. - **JS backfill** — `.backfill((row) => value)`. The applier streams rows in batches, calls the function locally, writes back. ```ts export const users = table('users', { id: id(), // SQL backfill — an @effect/sql Statement.Fragment; one round-trip, scales linearly. email: text().backfill(unknownEmailExpr), // JS backfill — when SQL can't express what you need. embedding: text().backfill(async (row) => embed(row.title), { batchSize: 500, // rows per batch, default 1000 sleepMs: 25, // ms between batches, default 0 }), }) ``` ## When to pick which | Need | Pick | |---|---| | Constant value for every row | `.default(value)` — not a backfill at all, the DDL DEFAULT clause does it | | Expression of existing column values (`id || '@local'`, `LOWER(email)`, `created_at + interval '1 day'`) | **SQL** | | Read another table's row (`(SELECT id FROM tenants WHERE name = '...' LIMIT 1)`) | **SQL** | | Call an embedding model / image classifier / external HTTP API | **JS** | | Compute a value with a JS library that has no SQL equivalent (`slugify`, `tokenize`, `parse`) | **JS** | The bias is firmly toward SQL. Performance is in different leagues — SQL backfills hit a million rows in seconds; JS backfills hit the same set in minutes-to-hours depending on what the function does. ## SQL backfill ```ts email: text().backfill(sql`'unknown-' || id || '@local'`), ``` The expression goes inside `UPDATE
SET = `. You can reference: - Other columns of the same row (`id`, `created_at`, etc.) — by name, no aliasing - Constants and literals — `'@local'`, `42`, `true` - Standard SQL functions — `LOWER()`, `COALESCE()`, `EXTRACT()`, `||`, … - Subqueries — `(SELECT id FROM tenants WHERE name = 'acme')` — including correlated ones - Dialect-specific functions when you know which DB you're on **Don't** reference columns the planner is about to drop / rename in the same plan — the UPDATE runs AFTER the ADD COLUMN but BEFORE any drops, so renamed columns are still under their old name at backfill time. The planner orders the plan deterministically (renames first as `RENAME COLUMN`, then ADD/ALTER); the backfill sees the post-rename names. ### Cross-dialect SQL idioms The `sql\`...\`` fragment is the same `@effect/sql` template you use in custom handlers. Use `sql.onDialectOrElse({...})` for expressions that vary: ```ts const timestampNow = sql.onDialectOrElse({ mysql: () => sql`NOW(6)`, mssql: () => sql`SYSUTCDATETIME()`, orElse: () => sql`now()`, }) createdAt: timestamp().backfill(timestampNow), ``` The framework's `sql.onDialectOrElse` resolves at compile time, so each dialect only emits its own branch. See [Multi-dialect strategy](./multi-dialect.md). ## JS backfill ```ts embedding: text().backfill(async (row) => { const text = `${row.title} ${row.body}` return embed(text) // calls an external embedding model }, { batchSize: 500, sleepMs: 25, }), ``` The function receives the full row (with `id` and every existing column). The applier: 1. Streams rows: `SELECT id FROM
ORDER BY id` with cursor pagination 2. For each batch of `batchSize`: - Calls the function for each row, in parallel - Writes back: `UPDATE
SET = $1 WHERE id = $2` for each result 3. `sleepMs` between batches to let normal traffic breathe `batchSize` controls memory + concurrency (each batch holds N row promises). `sleepMs` reduces contention with concurrent writes — set it to ~20–50 ms when the function makes external API calls (the API quota matters more than throughput). **The function MUST be deterministic + idempotent for the same input row.** A crash mid-backfill restarts the batch; non-idempotent functions double-charge external APIs or write duplicate side effects. ## Performance Order-of-magnitude rules of thumb for postgres on a modest VM: | Rows | SQL backfill | JS backfill (pure CPU) | JS backfill (calls 50 ms API) | |---|---|---|---| | 1k | < 50 ms | ~200 ms | ~25 s | | 10k | ~300 ms | ~2 s | ~5 min (batched, 500-wide) | | 100k | ~3 s | ~25 s | ~50 min | | 1M | ~30 s | ~5 min | unfeasible — use a separate workflow | | 10M | ~5 min | ~50 min | unfeasible | JS backfill is 10×–100× slower than SQL for the same data; with external calls it's 1000× slower. The CLI surfaces an estimate ahead of apply: ``` ⊕ needs-backfill (1) — declared + ALTER TABLE posts ADD COLUMN slug text → backfill: js fn (est. 50,000 rows, ~8 minutes — consider .backfill(sql) variant?) ``` The estimate is conservative (counts rows + multiplies by a per-row JS-fn cost factor). It's a hint, not a refusal — if you genuinely need the JS function, set `--note 'backfill via embedding model, expected duration'` so the history row records WHY the slow path was chosen. ## Validate a JS backfill before applying A JS backfill is regular TypeScript — the safest way to confirm it produces sensible output before committing a long UPDATE run is to call the function directly in a unit test (`voltro test`) over a handful of representative rows. There is no `voltro db backfill --dry-run` subcommand; `.backfill()` only runs as part of `voltro db apply`. ## Failure handling If the backfill `UPDATE` fails mid-flight: - **Postgres / MSSQL / SQLite**: the whole 3-step is inside one transaction. The failure rolls back the ADD COLUMN too — the schema returns to its pre-apply state. The plan stays pending; fix the backfill expression + re-apply with `voltro db apply`. - **MySQL / MariaDB**: DDL is implicit-commit. The ADD COLUMN landed. The UPDATE rolled back to its savepoint, but the nullable column is now on the table. The next `voltro db plan` will see the column present but nullable + emit the remaining `SET NOT NULL` step, which `voltro db apply` then applies against the current state. The [multi-dialect strategy](./multi-dialect.md) page covers the forward-roll mechanics in detail. If a JS backfill function throws partway through, the applier stops the batch loop, records the affected row id range in the log, leaves the column nullable (no `SET NOT NULL`), and exits non-zero. Re-run `voltro db apply` once you've fixed the function — because each batch's `UPDATE … WHERE IS NULL` only touches rows that haven't been filled yet, the re-run picks up where it stopped without double-writing completed rows. ## What about updating an existing column? `.backfill()` ONLY applies to ADD-COLUMN ops. Updating values on an existing column isn't a migration concern — it's regular data work. Write a one-off mutation or a `*.subscribe.ts` handler that watches for the trigger condition, OR a workflow if it spans steps. The migration system stays out of "change data in this column" jobs. --- ## Multi-dialect strategy _How the planner + applier behave across Postgres, MySQL, MariaDB, MSSQL, SQLite, Turso — the atomicity matrix, the resume ledger that carries a crashed apply on the non-transactional dialects, SQLite's table-rewrite mechanic, plus the per-dialect DDL idioms the framework hides._ The planner produces ONE `MigrationPlan` regardless of dialect. The applier executes it per-dialect, dispatching through `sql.onDialectOrElse` for every emit + falling back to runtime probes when behaviour diverges. The same `voltro db apply` invocation against the same schema produces structurally identical results on every backend. What ISN'T uniform: **transactional DDL semantics**. | Dialect | Plan applied atomically | Advisory-lock mechanism | Recovery after a mid-plan crash | |---|---|---|---| | Postgres | ✓ one transaction | `pg_advisory_lock(KEY)` | nothing to recover — rolled back | | MSSQL | ✓ one transaction | `sp_getapplock` | nothing to recover — rolled back | | **MySQL / MariaDB** | **✗ implicit commit per DDL** | `GET_LOCK('voltro_migration', N)` | resume ledger | | **SQLite / Turso** | **✗ per statement** | process-local mutex | resume ledger | This is the operationally heaviest cross-dialect difference. The rest of the page covers what changes. **SQLite is on the non-atomic side, and the reason is Turso.** SQLite the engine *can* do transactional DDL. The applier does not use it, because SQLite and Turso share one dialect token and Turso rejects DDL inside its default transaction — the applier cannot wrap one without wrapping the other. Both therefore take the per-statement path and the resume ledger below. **On Postgres one class of operation is still not covered by the transaction:** `online-required` ops (`CREATE INDEX CONCURRENTLY`, the shadow-column type swap) are *rejected* inside a transaction, so they run after the commit. They are ledgered like a MySQL plan. ## Postgres / MSSQL — transactional happy path A multi-step plan runs inside one `BEGIN ... COMMIT`. Mid-flight failure rolls EVERYTHING back; the next plan diff is identical to the pre-apply one. There's nothing to resume — re-running the apply re-runs the plan from scratch. The advisory-lock variants serialise concurrent applies — two operators running `voltro db apply` against the same DB at the same time go through serially. **The lock is scoped to your configured schema.** With `DB_SCHEMA` set, the lock key (Postgres) / lock name (MySQL, MSSQL) is derived from the schema, so two apps sharing one database in different schemas do not serialise — or defer — each other's migrations and trigger repairs. Without `DB_SCHEMA` (or with `DB_SCHEMA=public`) every instance takes one stable framework-wide key, which is what makes a rolling deploy safe: old and new replicas contend on the same lock. On MySQL/MariaDB `GET_LOCK` is server-wide; setting `DB_SCHEMA` to your database name un-shares the lock between two apps on one server. **Transient DDL failures are retried, per the dialect's own predicate.** A `CREATE TABLE IF NOT EXISTS` that meets SQLite/Turso's schema lock (`database is locked` / `SQLITE_BUSY`), a MySQL lock-wait timeout, or a Postgres/MSSQL deadlock victim during the boot auto-migrate is retried with bounded attempts and exponential backoff instead of failing the boot on the first attempt — only statements that are safe to re-run, and on Postgres/MSSQL as a fresh transaction (their deadlock classes roll the whole transaction back). `VOLTRO_MIGRATION_DDL_RETRIES` moves the retry count (default 4; `0` disables). ## MySQL / MariaDB / SQLite / Turso — the resume ledger Every DDL statement implicitly commits. A 5-op plan on MySQL is effectively 5 separate "atomic statements" with the prior ones already committed when a later one fails. If op 5 of 5 fails, ops 1–4 stay applied: ``` plan applying (mysql, env=prod): 1. create-table audit_logs ✓ 42ms 2. add-column users.email ✓ 18ms 3. backfill users.email ✓ 12s 4. alter-column users.email SET NOT NULL ✓ 8ms 5. add-index audit_logs(actorId) ✗ ER_DUP_KEYNAME ``` The `_voltro_migration_plans` row is still written only on a fully successful apply — that row means "this schema is live", and a half-applied plan is not. What the applier DOES write as it goes is a per-operation **resume ledger**, `_voltro_migration_ops`: - every operation that will run outside a transaction is inserted `pending` **before any DDL runs**, so a crash on operation 1 still leaves the whole intended sequence on disk; - each row flips to `started` immediately before its statement and `applied` immediately after; - the rows are **deleted** once the apply converges and the plan row lands. The ledger is a work queue, not a history — the history is `_voltro_migration_plans.operations`. There is still no `--resume` / `--abort` flag, because there is nothing to choose. Recovery is to re-run the apply once the cause is fixed: ```sh voltro db apply ``` The next apply finds the ledger, says so in the log, and continues the interrupted run: ``` [voltro:migrate] migration resume: found an interrupted run (plan_msocz71h_x0qq5d) — 4 of 5 operation(s) completed, in flight: add-index index:audit_logs.audit_logs_actorId_idx [voltro:migrate] migration resume: continuing run plan_msocz71h_x0qq5d — replaying 1 operation(s), 4 already applied ``` Three things are worth knowing about how it decides. **The ledger cannot be atomic with the DDL it records** — on MySQL the statement commits itself, so there is always a window where the DDL landed and the `applied` flip did not. That window is not eliminated, it is BOUNDED: the ledger is written strictly sequentially, so **at most one operation can be `started`**, and it is the only one whose outcome is unknown. That one is resolved by asking the planner — the fresh diff was computed against the live database moments ago, so an operation it no longer mentions has already taken effect. Completed operations are never re-attempted. **If you edited the schema in response to the failure, the recorded plan is dropped** and the freshly-diffed one is applied instead — the old plan aims at a target nobody wants any more. The interruption is still logged, and the artefacts below are still reconciled first. **Two operations are repaired rather than re-diffed.** The Postgres online type change (shadow-column swap) and the SQLite table rebuild both build a temporary object and swap it into place, and interrupted mid-swap they leave a live schema that means something ELSE to a differ — a half-finished shadow swap looks like a *missing column*, and the plan a blind re-diff produces for that is `add-column`, which succeeds and loses the data sitting in `__old`. The applier reconciles `__shadow` / `__old` and `
__voltro_rebuild` from their observable state before anything else reads the schema. If a shadow-swap state cannot be classified, the apply **refuses**, changes nothing, and names the three columns to inspect. **What has not changed: convergence still gates the fingerprint.** After the DDL — resumed or not — the applier re-plans against the live schema and refuses to record a fingerprint while anything remains. A resumed run is held to exactly the same standard as a fresh one, and an apply that does not converge KEEPS its ledger, because an unfinished run's record is the only thing that tells the next boot it is looking at a half-migrated schema. If someone finished the failed op out of band (a `mysql` shell, a corrective hot-fix), the fresh diff sees it as present and it is skipped. ## SQLite — table rewrite mechanic SQLite lacks `ALTER COLUMN`. For type changes, the applier auto-emits the standard pattern: 1. `CREATE TABLE _new ()` 2. `INSERT INTO _new SELECT (with cast) FROM ` 3. `DROP TABLE ` 4. `ALTER TABLE _new RENAME TO ` 5. Recreate every index + every FK the old table had The plan output flags this as "rewrite table" so you know what's happening: ``` ⊕ ALTER TABLE users ALTER COLUMN status TYPE varchar(20) # rewrite table (SQLite has no ALTER COLUMN) → recreates 3 indexes, 2 incoming FKs ``` Caveats: - **Foreign keys referencing the rewritten table** get dropped and recreated. If they declared `ON DELETE CASCADE`, the recreate restores it; ordering matters internally. - **Multi-rewrite plans on SQLite are brittle.** A plan that rewrites 3 related tables in one apply may have intermediate states where a FK temporarily references a non-existent table. The framework orders them topologically; mixed rename + rewrite within one plan can hit edge cases. If a SQLite multi-rewrite refuses, [file-based migrations](./file-based.md) let you control ordering manually. ## Per-dialect DDL idioms hidden from you The framework emits the right dialect-native idiom for every concept. You don't write these by hand: | Concept | Postgres | MySQL / MariaDB | MSSQL | SQLite | |---|---|---|---|---| | Identifier quoting | `"name"` | `` `name` `` | `[name]` | `"name"` | | `CREATE TABLE IF NOT EXISTS` | native | native | `IF NOT EXISTS (SELECT * FROM sys.tables...) EXEC(...)` | native | | Auto-increment id | `BIGSERIAL` | `BIGINT AUTO_INCREMENT` | `BIGINT IDENTITY(1,1)` | `INTEGER PRIMARY KEY AUTOINCREMENT` | | Booleans | native `boolean` | `tinyint(1)` (0/1) | `bit` (0/1) | `integer` (0/1) | | JSON column | `jsonb` | `json` | `nvarchar(max)` | `text` | | Timestamp with tz | `timestamptz` | `datetime(6)` | `datetime2` | `datetime` | | `now()` default | `now()` | `CURRENT_TIMESTAMP(6)` | `SYSUTCDATETIME()` | `current_timestamp` | | `RETURNING *` on INSERT | native | NOT available (separate SELECT) | `OUTPUT INSERTED.*` | native | | `LIMIT N OFFSET M` | native | native | `OFFSET M ROWS FETCH NEXT N ROWS ONLY` | native | | FK with cascade | native | native | native | native (must `PRAGMA foreign_keys = ON`) | The DDL emitter under `@voltro/database/src/migrate.ts` is one of the densest cross-dialect dispatch files in the codebase. Bug reports for "X doesn't work on dialect Y" usually trace to a missing branch there. ## Boot-log shape per dialect `voltro dev` prints a one-line dialect summary during the auto-migrate phase: ``` [voltro:dev] auto-migrate: planning schema dialect=postgres env=dev tables=22 [voltro:dev] auto-migrate: applied 3 op(s) in 412ms [safe=3 needs-default=0 needs-backfill=0 rename=0 lossy=0] fingerprint=8f507ba1e1aadad5 ``` For MySQL the line notes the non-atomic-DDL constraint (a failed op leaves earlier ops committed; re-run apply and the resume ledger continues from there): ``` [voltro:dev] auto-migrate: planning schema dialect=mysql env=dev tables=22 (implicit-commit DDL — re-run apply after a mid-plan failure) ``` For SQLite the line notes the single-process-only constraint: ``` [voltro:dev] auto-migrate: planning schema dialect=sqlite env=dev tables=22 (single-process — no concurrent appliers possible) ``` ## Replication caveats during apply If read-replicas are configured (`DB_REPLICA_URLS` set), the applier ALWAYS targets the primary. Replicas catch up via their normal replication stream. There's a window after apply where the replica fingerprint differs from primary — visible in `voltro db drift` if it's run against the replica URL during that window. For multi-region deploys, time the apply against primary's region + accept the inter-region replication lag as the propagation time. Drift is measured against the **primary** fingerprint — the canonical schema authority that the applier always targets; replicas converge to it through their replication stream, so a transient post-apply mismatch on a replica is replication lag, not drift. To check a specific replica during that window, run `voltro db drift` against its URL. ## When the dialect rejects something the planner emitted This shouldn't happen — the framework's per-dialect DDL emitter is the test surface for every code path. If you see a dialect-side error during an apply that looks like the framework emitted invalid SQL: 1. Capture the failing SQL from `voltro logs --trace `. 2. File an issue with: the schema diff, the dialect + version, the exact error message. 3. Workaround: drop down to a [file-based migration](./file-based.md) with hand-written DDL for the affected op. The framework can't auto-fix every dialect's pathological cases (MariaDB's RETURNING gap on UPDATE, MySQL's strict-mode rejection of TEXT defaults, MSSQL's optimizer quirks with FILTER + RAISERROR). Where the test suite has caught those, the emitter has the right branch. Where it hasn't, file the report — the matrix grows from real failures. --- ## Online migrations _Auto-promoted CONCURRENTLY indexes, batched backfills with progress reporting, and shadow-column rewrites for tables that can't be locked during apply. Threshold tuning + per-dialect mechanics._ The applier auto-rewrites ops that would block writes on large tables into their online variants. Cross the threshold and the planner promotes `safe` → `online-required`, executes a non-blocking variant, and reports progress through the CLI + boot log. ## Threshold The threshold defaults to 50,000 rows per table. Override it with the `--online-after ` flag on `voltro db plan` / `voltro db apply`, or the `VOLTRO_ONLINE_THRESHOLD` env var (the flag wins). At or above `n` rows an index/column change is planned as an ONLINE (non-blocking) operation: ```bash voltro db plan --online-after 10000 # treat tables ≥ 10k rows as online VOLTRO_ONLINE_THRESHOLD=0 voltro db apply # every change online (most conservative) ``` The threshold check runs at plan time. The applier counts rows with a fast `EXPLAIN`-derived estimate (Postgres `reltuples`, MSSQL `sys.dm_db_partition_stats.row_count`, MySQL `information_schema.tables.table_rows`); exact counts only happen during the actual op if needed. ## ADD INDEX → online via CONCURRENTLY ```ts export const posts = table('posts', { ..., authorId: reference(() => users), }).index('byAuthor', ['authorId']) ``` On a 1M-row `posts` table, the plan output marks the index add as online: ``` ◷ CREATE INDEX byAuthor ON posts(authorId) # online — postgres CONCURRENTLY ``` Per-dialect mechanism: - **Postgres**: `CREATE INDEX CONCURRENTLY` — no table lock, can read + write during build. ~3× slower than blocking + can't run inside a transaction. The applier moves it OUT of the main migration tx and runs it as its own step. - **MySQL 8 / MariaDB 10.6+**: `ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE LOCK=NONE`. Slightly weaker guarantee than postgres (briefly takes a metadata lock at start + end) but unblocks DML throughout. - **MSSQL 2019+**: `CREATE INDEX ... WITH (ONLINE = ON)`. Enterprise Edition required for ONLINE=ON; Standard Edition falls back to blocking + the applier emits a loud warning. - **SQLite**: no-op. SQLite locks anyway + the framework targets single-process deploys. If the CONCURRENTLY build fails partway through (out of disk, killed by an admin, network blip), Postgres leaves the index in an `INVALID` state. The next plan run detects it + reissues the build: ``` ◷ DROP INDEX byAuthor (invalid from prior partial build) + recreate ``` ## Batched backfill with progress A backfill on > threshold rows gets rewritten to batched: ``` ⊕ ALTER TABLE posts ADD COLUMN slug text → backfill (batched, 1000 rows/batch, 50ms sleep): est. 50,000 rows, ~12s ⊕ ALTER TABLE posts ALTER COLUMN slug SET NOT NULL ``` Per-batch progress shows up in `voltro logs --tail 50` while the apply runs: ``` backfill posts.slug: 12,000 / 50,000 (24.0%) — ETA 8s backfill posts.slug: 24,000 / 50,000 (48.0%) — ETA 5s backfill posts.slug: 36,000 / 50,000 (72.0%) — ETA 3s backfill posts.slug: completed — 50,000 rows in 11.8s ``` Inside the framework the batched form is: ```sql UPDATE posts SET slug = LOWER(REPLACE(title, ' ', '-')) WHERE id > $cursor AND slug IS NULL ORDER BY id LIMIT 1000 ``` `$cursor` is the last batch's max id. The query plan is index-only on the primary key, so it stays fast as the table grows. `WHERE slug IS NULL` lets the same batch resume after a crash without double-updating completed rows. Override the batching parameters per-column when you know more than the planner does: ```ts slug: text().backfill(sql`lower(replace(title, ' ', '-'))`, { batchSize: 5000, // big batches for cheap CPU-only updates sleepMs: 0, // no breathing room needed }), embedding: text().backfill(async (row) => embed(row.title), { batchSize: 100, // small batches — each row is an HTTP call sleepMs: 200, // throttle external API quota }), ``` ## Shadow-column rewrite For type changes on a large table that can't be done via `ALTER COLUMN TYPE` (Postgres requires a rewrite + table lock; MySQL likewise for non-trivial converts), the applier auto-emits a shadow-column pattern: 1. ADD COLUMN `_new ` nullable 2. Backfill batched: `UPDATE ... SET _new = (::)` in chunks 3. SET NOT NULL on `_new` (if applicable) 4. Atomic swap inside a brief metadata lock: rename `` → `_old`, rename `_new` → ``, drop `_old` The atomic swap is the only blocking step + holds the lock for ~1 ms. Apps that pin connections might see a brief query error during the swap; pooled connections re-issue + succeed on the second attempt. The plan output flags this clearly: ``` ◷ ALTER TABLE users ALTER COLUMN created_at TYPE timestamptz # shadow-column swap (4 steps: add → backfill → swap → drop) ``` Schema dependencies: indexes + FKs referencing the column are recreated against the new column in the same swap step. ## Cross-dialect quirks | Mechanism | Postgres | MySQL 8+ | MariaDB 10.6+ | MSSQL 2019+ Ent | SQLite | |---|---|---|---|---|---| | Online index build | `CONCURRENTLY` | `INPLACE LOCK=NONE` | `INPLACE LOCK=NONE` | `WITH (ONLINE = ON)` | no-op | | Resumable failed index | INVALID state + recreate | partial drop + retry | partial drop + retry | requires DBA intervention | no-op | | Batched backfill | identical | identical | identical | identical | identical (single-process) | | Shadow-column type change | yes | yes | yes | yes | no (table rewrite from [multi-dialect](./multi-dialect.md)) | After a large backfill you'll usually want fresh planner statistics on the affected table. The applier does NOT emit `ANALYZE` / `UPDATE STATISTICS` for you — run it yourself once the apply completes (e.g. `ANALYZE posts;` on postgres) if the post-backfill query plans look stale. ## What CONCURRENTLY doesn't help with Online migrations are about **writes during apply**. They don't help with: - Reads during apply — never blocked by either path - Long-running transactions that hold a lock the apply needs — the apply waits regardless - Replication lag — the new index propagates to replicas at their own pace For a multi-hour migration on a 100M-row table, the right pattern often isn't "make it online" but "split into many small applies + apply during low-traffic windows". The framework's planner-based system lets you express that as multiple deploys, each with a small focused plan, instead of one monolithic migration. The [prod pipeline](./prod-pipeline.md) page covers the deploy-cadence side. --- ## File-based migrations _The escape hatch for migrations the declarative diff can't infer — table splits, table merges, atomic cross-table data moves. Up/down convention, ordering, and how the planner integrates them._ The planner handles structural diffs — column additions, drops, renames, type changes — where the OLD and NEW shapes can be computed from declared schema vs live introspection. It refuses to plan migrations that need DATA SEMANTICS the diff can't see: - Splitting one table into two (extract address fields to a new `addresses` table with FK back) - Merging two tables (move both `personal_emails` + `work_emails` into one `emails` table tagged by type) - Atomic data moves across tables (move all `orders` of status `archived` into `archived_orders` with a different schema) - Type changes that need a custom transformation (parse a JSON column into structured columns) For these, write an explicit migration file. ## File convention ```text apps/api/migrations/ ├── 20260415_120000_split_address_out.ts ├── 20260520_093000_merge_emails.ts └── 20260603_140000_normalize_orders.ts ``` Filename = `_.ts`. The timestamp orders applies — sortable + globally unique without coordination. Slugs are for humans + filed alongside the timestamped name in `_voltro_migration_plans.id` for findability. The file exports a default `migration()`: ```ts // apps/api/migrations/20260415_120000_split_address_out.ts import { migration } from '@voltro/database' export default migration({ id: '20260415_120000_split_address_out', description: 'Move users.address* fields into a separate addresses table with FK back.', up: async ({ sql, log }) => { // 1. CREATE the new table: await sql.unsafe(` CREATE TABLE addresses ( id text PRIMARY KEY, userId text NOT NULL REFERENCES users(id) ON DELETE CASCADE, street text NOT NULL, city text NOT NULL, postalCode text NOT NULL, createdAt timestamptz NOT NULL DEFAULT now() ) `) await sql.unsafe(`CREATE INDEX addresses_user_idx ON addresses(userId)`) // 2. Move the data set-wise with one INSERT…SELECT — the file // context exposes the @effect/sql SqlClient, not the DataStore: await sql.unsafe(` INSERT INTO addresses (id, "userId", street, city, "postalCode") SELECT gen_random_uuid()::text, id, "addressStreet", "addressCity", COALESCE("addressPostalCode", '') FROM users WHERE "addressStreet" IS NOT NULL AND "addressCity" IS NOT NULL `) log.info('migrated address fields → addresses') // 3. DROP the old columns: await sql.unsafe(`ALTER TABLE users DROP COLUMN "addressStreet"`) await sql.unsafe(`ALTER TABLE users DROP COLUMN "addressCity"`) await sql.unsafe(`ALTER TABLE users DROP COLUMN "addressPostalCode"`) }, down: async ({ sql }) => { // Reverse — required. The runner won't accept a migration without one. await sql.unsafe(`ALTER TABLE users ADD COLUMN addressStreet text`) await sql.unsafe(`ALTER TABLE users ADD COLUMN addressCity text`) await sql.unsafe(`ALTER TABLE users ADD COLUMN addressPostalCode text`) await sql.unsafe(` UPDATE users SET addressStreet = a.street, addressCity = a.city, addressPostalCode = a.postalCode FROM addresses a WHERE users.id = a.userId `) await sql.unsafe(`DROP TABLE addresses`) }, }) ``` The `up` body runs inside a transaction (on dialects that support it; see [multi-dialect](./multi-dialect.md) for MySQL forward-roll semantics). `down` runs the same way for rollback. ## What the runner provides ```ts up: async (ctx) => { ctx.sql // @effect/sql SqlClient with sql.unsafe / sql tagged-template / sql.onDialectOrElse ctx.log // structured logger scoped to this migration (info / warn) ctx.appliedAt // ISO-8601 string — when this migration started (deterministic across the up/down pair) } ``` The file context is `{ sql, log, appliedAt }` — there is NO `store` handle here. The escape hatch is deliberately SQL-level: you're doing the structural moves the typed DataStore can't express, so you drive them with `sql.unsafe(...)` for raw DDL and `sql\`...\`` / `sql.onDialectOrElse(...)` for parameterised statements. Move data set-wise with `INSERT … SELECT` / `UPDATE … FROM` rather than a per-row JS loop — it's one round-trip and stays inside the migration's transaction on the dialects that support transactional DDL. ## Ordering vs the planner File-based migrations are applied IN TIMESTAMP ORDER, BEFORE the planner-based diff runs. So a typical boot looks like: ``` [voltro:dev] migrations: file-based pending → 1 [voltro:dev] migration 20260415_120000_split_address_out applying [voltro:dev] migrated 1247 users → addresses [voltro:dev] migration 20260415_120000_split_address_out applied in 482ms [voltro:dev] auto-migrate: planning schema dialect=postgres env=dev tables=23 [voltro:dev] auto-migrate: schema up to date fingerprint=8f507ba1e1aadad5 ``` The file ran first, dropped the columns, created the new table. The planner then diffs the (now mutated) live shape against the declared schema — and finds it up to date, because the declared schema also has `addresses` as a separate table + `users` without the address columns. This ordering is critical: file-based migrations MUTATE state the planner sees. Sequence: 1. Edit `users.entity.ts` to remove the `address*` columns + add the new `addresses` entity file 2. Write the file-based migration that physically moves the data + drops the columns 3. Boot — file-based runs first (writes the new state), planner runs second (sees a clean diff against the new declared schema, no-op) If you skip step 2 + just edit the schema, the planner refuses to plan: dropping `addressStreet` is `lossy` (no `dropped()` marker), creating `addresses` is `safe`. The plan would refuse + the boot would fail until you add `dropped()` markers... but then you'd lose the data. The file-based migration moves the data BEFORE the planner sees the columns are gone. ## Tracking File-based migrations land in the same `_voltro_migration_plans` table as planner-based ones, with `source: 'file'`: ``` plan_mig_5k78 fp=... env=dev src=file ops=1 3.2s 2026-04-15 12:00:00 by=boot:dev plan_mig_5k79 fp=... env=dev src=auto-diff ops=0 12ms 2026-04-15 12:00:03 by=boot:dev ``` The `voltro db plans` command shows both side by side in the same timeline. Drift detection compares against the latest fingerprint regardless of source. ## When NOT to use file-based migrations The escape hatch is for situations the diff genuinely can't infer. Don't reach for it for: - ADD NOT NULL column — that's `.backfill()` on the column declaration - Rename column — that's `.renamedFrom()` on the new column - Drop column — that's `dropped()` - Drop table — remove from declared set + `VOLTRO_DESTRUCTIVE_OK=1` for the apply A file-based migration for any of these defeats the planner's safety story. The DSL annotations carry their fix-hint into the developer's editor; the file is just "trust me, this works". ## Idempotency File-based migrations are NOT auto-idempotent. The runner checks `_voltro_migration_plans` for a row with the same `id` and skips if found. The migration body itself must NOT assume it ran from a clean slate IF you're going to edit it after applying (the framework refuses to re-apply a modified file silently — see Drift docs). Practical rule: once a file-based migration is applied in any env, it's frozen. Subsequent corrections are NEW migrations with NEW timestamps that read the half-applied state + finish the job. ## Rollback `voltro db rollback-file ` invokes a file-based migration's `down` body. The `` is the positional migration id (the basename minus `.ts`): ```sh voltro db rollback-file 20260415_120000_split_address_out ``` This is the planner-side file runner (`migration()` → `down`). It is a DIFFERENT subcommand from `voltro db rollback`, which drives the separate `defineMigration` step-based runner and takes `--to`, not a positional id — see [the overview](./index.md) for the two runners. `rollback-file` refuses on `NODE_ENV=production` (rollback runs as an explicit deploy step there). If `down` throws, the rollback is treated as failed — the schema stays in the half-rolled-back state + the operator handles it manually. The framework can't auto-recover from a broken inverse. ## `voltro serve` refuses to boot while any are pending Serve's schema guard is a DECLARATIVE fingerprint diff — the declared schema against the last applied plan. A file-based migration exists for the changes a state diff cannot infer: a data move, a backfill, a cross-table rewrite. Those move **no fingerprint at all**, so the guard passed and production ran un-migrated with nothing said. On a real deploy environment (`NODE_ENV=production` / `staging`), against a SQL store, `voltro serve` now refuses to boot while any migration file has never run against that database, and names the pending ids: ```text serve: refusing to boot — 2 pending file-based migration(s) have never run against this database. They perform the changes a schema diff cannot infer (data moves, backfills, table splits), so the declarative fingerprint check below cannot see them. Run them from your pre-deploy job — `voltro db migrate .` (schema + files) or `voltro db files .` (files alone). ``` It does **not** apply them, and that is deliberate: a rolling deploy starts N replicas, each would try, and the migration lock turns that into N-1 processes blocked on boot. `VOLTRO_AUTO_MIGRATE=0` bypasses this exactly as it already bypassed the fingerprint check — one switch for "no boot-time schema checks". A local `voltro serve` is untouched: `voltro dev` applies migrations there, so a preview serve has nothing to report. ## Remote databases: boot will not apply them unattended `voltro dev` applies pending migration files at boot. Against a local database that is the whole point of the escape hatch. Against a remote one it means that saving a file is enough to change production — before review, before CI, without typing a command. So when the configured database is **not local** and there are pending migration files, dev boot refuses instead: ```text file-based migrations: refusing to auto-apply 1 file-based migration(s) to a REMOTE database (ep-cool-dawn.eu-central-1.aws.neon.tech). Pending: • 20260725_150000_drop_legacy_subscriptions ``` A database counts as **local** when its host is loopback (`localhost`, `127.0.0.1`, `::1`), a private LAN address (`10.x`, `192.168.x`, `172.16–31.x`), `host.docker.internal`, a `.local` / `.localhost` name, a `file:` / `sqlite:` URL, or a **bare hostname** like `postgres` or `db` — only a container network resolves those, so `docker compose up` keeps working untouched. Everything else — a managed provider, any dotted public hostname, an unparseable `DB_URL` — counts as remote. Three ways forward: ```sh # 1. Point the app at a local database (what dev boot assumes) DB_URL=postgres://app:app@localhost:5432/app voltro dev # 2. Apply them deliberately, once voltro db files . # 3. Accept unattended applies for this environment VOLTRO_REMOTE_MIGRATIONS_OK=1 voltro dev ``` The gate is **silent when nothing is pending**, which is the normal case — running dev against a remote database is unaffected until the moment a file would actually execute against it. It refuses rather than skipping quietly: a skipped migration leaves the database in a shape the app does not expect (a half-done table split, a column the handlers already read), and the failures that follow point everywhere except at the cause. It does **not** try to detect destructive SQL. In arbitrary SQL that is not decidable, so such a check would be either leaky or noisy. What the gate separates is the two things boot used to conflate: *saving a file* and *applying it to production*. ## Applying file-based migrations from the CLI `voltro db files` applies pending file-based migrations under `/migrations/` — the same runner the boot path invokes, exposed as a CLI command for when you've set `VOLTRO_AUTO_MIGRATE=0` and apply schema as an explicit step: ```sh voltro db files ``` It's a distinct command from `voltro db apply` (which runs the planner-based auto-diff). The two histories both land in `_voltro_migration_plans` — file-based rows carry `source: 'file'`, planner rows `source: 'auto-diff'` — so `voltro db plans` shows them inline. On a normal boot, file-based migrations run FIRST (before the planner diff), so by the time `voltro db apply` would run, the file has already mutated the live shape. The [prod pipeline](./prod-pipeline.md) page covers the deploy-step apply flow for planner-based changes. --- ## Prod pipeline _How schema changes flow from a PR through review to a production database. Why `voltro db apply` runs as an explicit deploy step, never on boot. Per-env fingerprint check + the refuse-to-boot behaviour._ The framework's hard rule: **`voltro start` (the production runtime) NEVER auto-applies migrations**. The dev-mode behaviour where `voltro dev` boots refuse-to-start on a blocked plan + auto-apply otherwise is intentionally not extended to prod. Schema changes mid-rolling-deploy without review are the largest data-risk class the framework could create; we don't. What prod boot DOES is compare the declared-schema fingerprint against the latest `_voltro_migration_plans.fingerprint`. Match → serve traffic. Mismatch → refuse to boot with a structured error. ## The end-to-end flow ``` ┌─────────────────────────────────┐ │ 1. Developer edits schema in PR │ └────────────────┬────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ 2. CI runs `voltro db plan` │ │ against a staging-style │ │ snapshot. The printed plan │ │ (classes + fingerprints) goes │ │ into the PR for review │ └────────────────┬─────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ 3. Reviewer reads the plan │ │ classification, fingerprints │ │ + DSL annotations │ │ Approves PR │ └────────────────┬─────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ 4. Merge to main │ │ CI/CD deploys new image │ │ BEFORE traffic switch a │ │ one-shot job runs │ │ `voltro db apply` against │ │ the prod DB (re-diffs live) │ └────────────────┬─────────────────┘ │ ▼ ┌───────────────────────────────────┐ │ 5. Traffic switches │ │ Prod boot: fingerprint match │ │ → serves │ └───────────────────────────────────┘ ``` There's a slot for the apply step in every common deploy tool (k8s init container, ECS task pre-deploy hook, Heroku release phase, Cloud Run job-on-deploy, Fly.io `release_command`). The shape is identical: run a one-shot container/process that holds the migration credentials + executes `voltro db apply`. It re-diffs the live DB against the deployed code's declared schema and applies the resulting plan — there is no pre-serialised plan file to pass; the apply re-computes the diff at run time. The serving process never gets the migration-grade credentials. ## Per-env fingerprint check `voltro start` (prod runtime) does this on every boot: ```ts const declaredFp = fingerprintSchema(declaredSnapshot(tables)) const lastApplied = await sql` SELECT fingerprint FROM _voltro_migration_plans ORDER BY appliedAt DESC LIMIT 1 ` if (lastApplied?.fingerprint !== declaredFp) { // PROD-MISMATCH outcome → refuse-to-boot. process.exit(1) } ``` The structured error on mismatch: ``` [voltro:start] auto-migrate: SCHEMA FINGERPRINT MISMATCH — declared = a8f2c9d10b3f4e62 live = 8f507ba1e1aadad5 Run `voltro db apply --plan plan.json` from the deploy pipeline before serving. exit 1 ``` This propagates as a non-zero exit code, k8s + ECS + Cloud Run mark the pod/task `CrashLoopBackOff` / failed deployment → automatic rollback to the previous image. Operators see the loud crash + know to run the apply step. `VOLTRO_AUTO_MIGRATE=0` skips this check entirely — useful when migrations are handled by a separate ops process + the boot doesn't need to verify. Tradeoff: a drift goes undetected until the next manual `voltro db drift` run. ## Previewing the plan in CI Run `voltro db plan` against a snapshot of the prod schema (NOT prod itself — never expose prod credentials to CI). Add `--json` to emit the plan as a machine-readable artifact you can save and commit: ```sh DB_URL= voltro db plan --json > plan.json ``` The reviewer reads either the printed plan or the JSON. The saved `plan.json` then becomes the input to `voltro db apply --plan plan.json` (see below), applying the EXACT reviewed diff. A bare `voltro db apply` (no `--plan`) re-diffs live at deploy time instead — both flows are supported; the plan-file flow is the one the prod refuse-to-boot message points you to. ```sh # CI step — runs in staging or a snapshot-replica env, against a real # connection: DB_URL= voltro db plan ``` The printed plan goes into the PR for the reviewer: ``` schema diff: 4 operations, 0 blocked ✓ ALTER TABLE users ADD COLUMN bio text # safe ⊕ ALTER TABLE users ADD COLUMN email text # backfill: sql`...` ⊕ UPDATE users SET email = 'unknown-' || id || '@local' ⊕ ALTER TABLE users ALTER COLUMN email SET NOT NULL ✓ CREATE INDEX users_email_idx ON users(email) # safe safe: 2 needs-default: 0 needs-backfill: 1 rename: 0 lossy: 0 blocked: 0 fingerprint: 8f507ba1e1aadad5 → a8f2c9d10b3f4e62 ``` A reviewer reads the SQL the applier will emit + checks the fingerprints. Blocked ops (red `✗`) MUST be resolved in the PR before merge — `voltro db apply` refuses any plan with a blocked op (exit 2). ## Applying against prod The deploy job runs `voltro db apply` (re-diffing the deployed code's declared schema against the live prod DB): ```sh voltro db apply --note 'PR #1234 — add user emails' ``` The applier: 1. Introspects the live DB and plans the diff fresh (it does NOT ingest a plan file — the diff is computed against live at apply time) 2. Refuses (exit 2) if any op is blocked, or refuses (exit 3) if `NODE_ENV=production` — so a bare apply runs in a one-shot job with `NODE_ENV=staging`, holding migration credentials, NOT in the serving process > **`NODE_ENV` unset is no longer "not production".** Every `voltro db …` > and `voltro migrate` invocation resolves an unset `NODE_ENV` to > `production`, exactly as `voltro serve` and `voltro start` do — so a bare > `voltro db apply` in a pipeline that forgot the variable now refuses (exit > 3) instead of silently applying an un-reviewed diff to production. Set > `NODE_ENV=development` for a local database; use the `--plan` path below > for a real one. > > This is not only about the refusal. `_voltro_traces` and `_voltro_undo_log` > are created only when tracing / undo capture are on, and both are *on > unless production* — so an apply with `NODE_ENV` unset used to DECLARE two > tables the serving container did not. The declared set is what the schema > fingerprint hashes, so the apply recorded a fingerprint the container could > not reproduce and `voltro serve` refused to boot with `prod-mismatch`, > telling you to run the apply you had just run. 3. Acquires the advisory lock + executes the plan 4. Records the result in `_voltro_migration_plans` with `source: 'auto-diff'` + `notes: 'PR #1234 — add user emails'` ### Applying a reviewed plan (`--plan`, works under `NODE_ENV=production`) `voltro db apply --plan plan.json` applies a plan saved by `voltro db plan --json`. Unlike a bare apply it is **allowed when `NODE_ENV=production`** — because it re-introspects live and refuses unless BOTH fingerprints still match the saved plan: - `fromFingerprint` — the live schema the plan was generated against. Live drifted since? → aborts (exit 2, "live schema has drifted"). - `toFingerprint` — the declared schema the plan targets. Schema files changed since? → aborts (exit 2). So `--plan` can only ever apply the exact diff that was reviewed — never a stale or drifted one. That safety is what lets it run directly on the prod runtime, with no `NODE_ENV`-unset dance. It still refuses any plan with a blocked op (exit 2) and records the result with `source: 'file'`. ```sh voltro db apply --plan plan.json --note 'PR #1234 — add user emails' ``` Because the diff is recomputed against live, an apply on an already- up-to-date DB is a clean no-op (`schema is up to date — nothing to apply`). That's what makes the apply safe to run in every pod of a stateless deploy. Both spellings of the flag work: `--plan plan.json` and `--plan=plan.json`. ### The FIRST deploy, against an empty database Nothing special is required, and the plan you review is the whole story: on a database with no tables, `voltro db plan --json` includes the framework's own `_voltro_*` tables (the migration ledger, api keys, kv, outbox, traces …) plus `actors`, alongside your own. They are part of the declared schema, so they are planned, classified and applied by exactly the same code as your tables — expect a first-deploy plan to be ~20 operations larger than the diff you wrote. Two consequences worth knowing: - The reviewed plan is complete. `db apply --plan` creates nothing beside it, so the fingerprint the plan was generated against is still the live schema when the guard checks it. (It did not used to be: the ledger tables were created before the fingerprint was taken, so the first deploy of every new database refused with `the live schema has drifted` one second after the plan was generated. Fixed.) - One table is deliberately absent from the plan: `_voltro_migration_ops`, the crash-resume ledger. It has to exist before the very first plan runs — the plan that creates everything else — so `voltro db apply` creates it itself, under the migration lock. A live `_voltro_*` table your schema does not declare is never planned for a drop, so it does not show up in the next diff either. ## Apply timing relative to deploy Two orderings, both common: **Apply before image swap** (recommended): the new image is deployed but not serving yet. The apply runs against the live DB. Then traffic switches. - New schema is in place when the new code starts serving → no version mismatches - Old code is still serving until the swap → it must tolerate the new schema for a brief window - Constraints: every migration must be backward-compatible with the OLD code for the swap window. ADD columns (the OLD code ignores them) ✓. DROP columns (the OLD code might still write to them) ✗ → requires a 2-deploy dance (deploy 1: stop writing to col, deploy 2: drop col). **Apply after image swap**: traffic is on the new code, the apply runs after. The new code must tolerate the OLD schema until the apply finishes. - New code is in place during apply → mid-apply rollback is easier (just rollback the apply, the new code can still talk to the old shape if you designed for it) - Migration is the LAST step → if it fails, the new code is already serving + needs the new schema. Outage. For most teams the first is safer (the framework's `_voltro_migration_plans.environment` tracking expects this pattern). For specific workloads where a partial migration would be catastrophic (massive backfills, multi-hour rewrites), the apply runs first as a one-shot job, the deploy follows when it's done. ## Multi-instance prod The advisory lock around `voltro db apply` serialises concurrent applies. Two instances of the apply job racing the same plan → one acquires the lock, the other blocks until the first finishes + observes the post-apply fingerprint matches (it's a no-op now), exits 0. This is the same mechanism that lets you run the apply in EVERY pod of a stateless deploy (the second-through-Nth no-op out fast) — useful when the deploy pipeline can't single out a designated migration runner. ## Rollback paths If the apply itself fails partway: - **Postgres / MSSQL / SQLite**: the transaction rolled back → live DB unchanged → fix the migration + re-run `voltro db apply` (it re-diffs from the unchanged state) - **MySQL / MariaDB**: DDL is implicit-commit, so completed ops stayed. Re-run `voltro db apply` — it re-diffs against the half-applied live shape and emits only the remaining ops. See [multi-dialect](./multi-dialect.md). If the apply succeeded but the new code is broken + needs to be rolled back: - The new-code rollback ≠ the schema rollback. The image deploys can revert via your normal CI/CD path; the schema stays at the new fingerprint. - There is no `voltro db rollback ` for a planner plan. To back out a `safe` change, ship a schema PR that re-declares the old shape and apply it as a new forward plan; for `lossy` changes the old data is gone — restore from backup. See [Rollback](./rollback.md). This is why the migration story is conservative + the prod refuse-to-boot is strict: once data is gone, no automated reversal brings it back. ## What about staging? The apply records `environment: 'staging'` when `NODE_ENV=staging`, else `dev` — each env's rows are tracked in the same `_voltro_migration_plans` table, tagged by environment. The CI flow typically applies to staging first, runs smoke tests against the new code, then to prod. The apply is idempotent against re-runs: because it re-diffs live each time, a second run against an already-migrated DB no-ops out. The cloud dashboard surfaces per-env state with a multi-env tab in the [cloud UI](./cloud-ui.md). ## Rehearsing a migration against real data The strongest check on this pipeline is not that each command exits 0 — it is that **no row moved that you did not ask to move**. This loop was built for a MariaDB cutover and caught three defects the framework's own suite did not, which is why it is written up here. 1. **Restore a backup into a throwaway database.** ```sh voltro data backup ./rehearsal . DB_URL=$SCRATCH_URL voltro data restore ./rehearsal ``` Use `data backup` / `data restore` — the NATIVE path — not the logical `data export`. The logical exporter re-shapes rows through the **current** declared schema, and the state a rehearsal exists to migrate *from* is precisely the one that does not match it. 2. **Take an exact census, before.** ```sql SELECT table_name, COUNT(*) FROM ... -- one COUNT(*) per table ``` It must be `COUNT(*)`. `information_schema.TABLE_ROWS` is an **estimate** on InnoDB — routinely off by thousands, and it is what a fast version of this check would reach for. The slowness is the point. 3. **Run the exact production command sequence** — the same one your deploy Job runs, in the same order: ```sh voltro db files . voltro db plan --json > plan.json voltro db apply --plan plan.json ``` 4. **Take the census again, and require the difference to be explainable.** A healthy run moves one row: the `_voltro_migration_plans` ledger entry. Anything else is a question, not a result. **Two things the census must get right**, both learned by using it: - **A soft drop is not a loss.** With `VOLTRO_SOFT_DROP=1` a dropped table reappears as `__dropped_` with its rows intact. Reporting that as a vanished table trains people to ignore the check; reporting it as clean hides a real drop. Give it its own category. - **A new table is not a discrepancy.** A migration that adds one produces a table with no "before" count. Say so explicitly rather than letting a zero read as data loss. To rehearse a schema several months old — the realistic case — craft the backup deliberately: a table a later migration added, a column a later one narrowed, a column the schema no longer declares. The planner is state-based, so it diffs live against declared and never replays a history; a six-month-old dump costs exactly one diff. ## File-based migrations in this pipeline `voltro db apply` runs pending `migrations/*.ts` **first**, then diffs — the same order the boot path uses. `voltro db apply --plan plan.json` does **not** run them. It **refuses** when any are pending: ```txt db apply --plan: refusing — 2 pending file-based migration(s). 20260714_090000_split_full_name 20260721_143000_backfill_slug These perform the changes a state diff cannot infer, so they change the shape this plan was computed against. Apply them and regenerate the plan: voltro db files . voltro db plan --json > plan.json voltro db apply --plan plan.json ``` That is not caution for its own sake. A saved plan was computed and reviewed against an earlier state; a file migration performs exactly the kind of change (a table split, a cross-table data move) that makes the plan stale. Running the migrations first would trip the fingerprint guard immediately afterwards and leave a half-applied deploy; running them after would apply a plan reviewed against a state that no longer exists. **So a pipeline that uses the saved-plan form needs `voltro db files` as its own step**, before the plan is generated: ```bash voltro db files . # authored data steps voltro db plan --json > plan.json # diff, now against the corrected shape voltro db apply --plan plan.json # reviewed, fingerprint-guarded ``` If you use plain `voltro db apply` instead, the first step is already included. --- ## Cross-environment migration sync _voltro db plan --against — diff your local declared schema against a remote env's live DB before pushing, over the framework's inspect endpoint. No DB connection from the CLI._ `voltro db plan --against ` fetches a remote environment's live schema via the framework's `/_voltro/inspect/migrations` endpoint and diffs it against your LOCAL declared schema — a "what would my branch do if I shipped it to staging right now?" pre-deploy preview. No database connection from the CLI to the remote; everything goes through HTTP. ## How it works 1. Fetch `/_voltro/inspect/migrations` on the remote URL. 2. Read the introspected schema from `drift.liveSnapshot` — the same `SchemaSnapshot` shape `introspectSchema` produces locally. The drift payload carries it alongside the fingerprints (`liveFingerprint`, `lastAppliedFingerprint`). 3. Run the planner against (local-declared, remote-live). 4. Render the plan output marked with the remote URL so you know it's not a local diff: ``` [--against https://staging.example.com/_voltro/inspect/migrations] — diff vs remote live schema: schema diff: 3 operations, 0 blocked ✓ CREATE INDEX users_email_lower_idx ON users (lower("email")) ✓ ALTER TABLE posts ADD COLUMN searchVec tsvector ... ✓ CREATE INDEX posts_search_gin ON posts (searchVec) ``` The exit code is `2` when the plan has blocked ops, `0` otherwise — so a CI job can gate a deploy on a clean diff against the target env. ## Auth The inspect endpoint accepts a bearer token: ```bash voltro db plan --against https://prod.example.com --token $PROD_INSPECT_TOKEN ``` Without `--token`, the CLI reads the `VOLTRO_INSPECT_TOKEN` env var. Without either, the request goes unauthenticated (only works if the remote has `VOLTRO_INSPECT_TOKEN` unset, which you should **NEVER** do on prod). The token is the same one the remote app boots with: ```bash # On the remote side: VOLTRO_INSPECT_TOKEN= voltro start ``` See [Introspection](/docs/cli/inspect) for the full auth configuration. ## URL shape The CLI accepts either form: ```bash voltro db plan --against https://staging.example.com voltro db plan --against https://staging.example.com/_voltro/inspect/migrations ``` If the URL doesn't end in `/_voltro/inspect/migrations`, the CLI appends it. The trailing-slash variant works too. ## CLI flags | Flag | Required | Description | |--------------|----------|--------------------------------------------------------------| | `--against` | yes | URL of the remote env (base URL OR full inspect URL) | | `--token` | no | Bearer token. Falls back to `VOLTRO_INSPECT_TOKEN` env | ## See also - [Migration overview](/docs/database/migrations) — the plan/apply lifecycle this slots into - [Introspection](/docs/cli/inspect) — the `/_voltro/inspect/*` surface this command consumes - [Drift detection](/docs/database/migrations/drift) — fingerprint-based drift surfacing the same inspect endpoint emits --- ## Migration squashing _voltro db squash — Rails-style consolidation. Mark every applied-before-T migration as squashed, leave a single snapshot row that new envs boot against._ After 2 years of incremental migrations the history table is hundreds of entries. Fresh environments take minutes to bootstrap. `voltro db squash` consolidates everything applied before a cut-off date into one synthetic snapshot row. ## When to squash - A new environment (laptop, staging) takes >30s to boot because of the migration replay. - The migration history has hundreds of entries and is becoming unreadable in `voltro db plans`. - You've shipped a major schema overhaul and the pre-overhaul history is no longer useful for debugging. Don't squash: - If you're still iterating on the schema in dev — the history is your audit trail. - Right before a major release — wait until the release lands everywhere first. - File-based migrations — squashing only affects the declarative- diff (`auto-diff`) entries. File-based migrations keep their separate rollback path. ## Quick start ```bash voltro db squash --before 2026-06-01 --note 'consolidate v1 migrations' ``` ``` ✓ squashed 14 migration plan(s) snapshot id: plan_squash_l4f2m1 fingerprint: sha256:a8f2… ``` ## What the command does 1. **SELECT** every `auto-diff` row in `_voltro_migration_plans` applied before the `--before` cut-off (where `squashedAt IS NULL`). 2. **Validates** the latest pre-squash row's fingerprint matches the current declared-schema fingerprint. If they diverge, the squash refuses — your working tree carries un-applied changes that would silently be locked in. 3. **Marks** all eligible rows squashed by setting `squashedAt` to the current timestamp. The rows STAY (audit trail preserved); they just no longer participate in boot-time replay. 4. **Inserts** a synthetic snapshot row with: - `source: 'squash-snapshot'` - `fingerprint: ` - `notes: ` - `operations: { kind: 'squash-snapshot', rowsSquashed: }` New environments booting against the squashed history skip past the squashed rows + use the snapshot's fingerprint as their starting point. ## The fingerprint check The most common gotcha: ``` $ voltro db squash --before 2026-06-01 squash refused: latest pre-squash fingerprint (a8f2…) differs from current declared-schema fingerprint (b3c1…). This means the working tree carries un-applied changes — squashing now would lock the drift in. Fix: run `voltro db apply` first to align the live schema with the working tree, then re-squash. ``` The squash captures the fingerprint of the **most recent applied migration**. If your declared schema has uncommitted changes, the snapshot would say "this is the post-squash schema" but the actual schema (live DB) doesn't match. New envs would boot against the snapshot fingerprint, see a different declared fingerprint, and refuse to start. Resolution: apply pending migrations FIRST, then squash. ## CLI flags | Flag | Required | Description | |--------------|----------|--------------------------------------------------------------| | `--before` | yes | ISO-8601 date — rows applied before this point are squashed | | `--note` | no | Human-readable note recorded on the snapshot row | ## What happens on next boot A new env booting against a squashed history: 1. Reads the latest `_voltro_migration_plans` row. 2. If `source = 'squash-snapshot'`, treats its fingerprint as the baseline + skips replaying the squashed entries. 3. Diffs current declared schema against the baseline. 4. Applies only post-squash migrations (anything with `squashedAt IS NULL` AND `appliedAt > snapshot.createdAt`). ## What stays available - **The squashed rows** — still in `_voltro_migration_plans`. Query them via `voltro db plans` or read the table directly. - **File-based migrations** — untouched by squash. Their up/down pairs remain available for rollback. - **Drift detection** — `voltro db drift` compares the live DB fingerprint against the most recent `_voltro_migration_plans` row (squash snapshot OR a post-squash entry). ## What's GONE - **Rolling back a squashed migration** — the rollback path is one-way once a row is marked squashed. To recover, manually UPDATE the row's `squashedAt` back to NULL + DELETE the snapshot row. The framework doesn't auto-emit a reverse squash. - **Replay against a fresh DB** — new environments use the snapshot fingerprint, not the original migration sequence. The squashed rows are audit, not replayable. ## See also - [Migration overview](/docs/database/migrations) — the full plan/apply lifecycle the squash slots into - [Drift detection](/docs/database/migrations/drift) — `voltro db drift` compares against whichever row is most recent (squash or apply) - [Soft-drop recovery](/docs/database/migrations/rollback-snapshots) — the orthogonal "I dropped something and want it back" path --- ## Rollback _What can be reversed and what can't. Planner-applied plans are NOT auto-reversible — rollback is file-based only. The recovery paths for an applied schema change, and why forward-fix usually beats rollback._ There is no rollback for a planner-applied plan. The `_voltro_migration_plans` history is an append-only record of what ran; the applier does not store an inverse plan and `voltro db` has no `rollback ` subcommand. Reversing a planner change means either re-declaring the prior schema and applying that as a NEW forward plan, or — for destroyed data — restoring from backup. What CAN be reversed mechanically is a **file-based** migration, via its explicit `down` body. ## What "rollback" means per change kind | Change kind | Reverse path | |---|---| | Planner plan with only `safe` ops (ADD nullable, ADD index, widen type) | Re-declare the old shape in TS → `voltro db apply` runs the inverse as a NEW forward plan | | Planner plan with `lossy` ops (DROP column/table, narrow type) | Not reversible — the data is gone. Restore from backup, or [restore-snapshot](./rollback-snapshots.md) if it was applied with `VOLTRO_SOFT_DROP=1` | | `migration()` file (`migrations/_.ts`) | `voltro db rollback-file ` runs its `down` body | | `defineMigration` step file (`*.migration.ts`) | `voltro db rollback [--to ]` runs its steps' `undo` in reverse | The rest of this page covers each path. ## Reversing a planner change — forward-apply the old shape A planner plan is a function of (declared schema, live schema). To undo one, make the declared schema describe the PRIOR state again and apply: ```sh # 1. Revert the *.entity.ts edit (e.g. git revert the schema PR). # 2. voltro db plan shows the inverse diff: voltro db plan # ✓ ALTER TABLE users DROP COLUMN bio # inverse of the ADD that shipped # 3. Apply it as a new forward plan: voltro db apply --note 'reverting bio column — PR #1234 backed out' ``` This works cleanly ONLY when every op in the inverse diff is itself `safe`. If the original plan added a NOT NULL column you now want gone, the inverse is a plain DROP (safe). But if the original plan DROPPED a column, the inverse is an ADD that the planner classifies `needs-backfill` — and the data that column held is already gone, so no backfill expression brings it back. That asymmetry is the whole reason lossy ops are gated behind `dropped()` / `VOLTRO_DESTRUCTIVE_OK=1` in the first place. The reverting apply lands a new `_voltro_migration_plans` row. The original plan stays in history; `voltro db plans` shows the apply and its reversal as two separate rows so the timeline is auditable. ## What can't be recovered by forward-apply ### Data from lossy ops ```ts export const users = table('users', { id: id(), legacy: dropped(), // applied → DROP COLUMN legacy }) ``` After apply, `users.legacy` and its data are gone. The applier does not snapshot column data before dropping (that would mean duplicating the table at apply time, which doesn't scale). Recovery options: - If the plan was applied with `VOLTRO_SOFT_DROP=1`, the column was RENAMED to a sidecar instead of dropped — `voltro db restore-snapshot ` brings it back. See [Soft-drop recovery](./rollback-snapshots.md). - Otherwise: restore from your DB's normal backup / PITR system. This is why `dropped()` is an explicit annotation — it signals "I have a backup OR I really mean it". ### Backfilled values A `needs-backfill` plan computed values from the SQL expression / JS function. Forward-applying a DROP of that column discards the values. The expression is preserved in the plan's `operations` JSON, so re-applying the same forward plan reproduces the same values IF the source data is unchanged — but a structural reversal does not restore them. ## Reversing a file-based migration ### `migration()` files — `rollback-file` ```sh voltro db rollback-file 20260415_120000_split_address_out ``` Runs the file's `down` body. The `` is positional — the migration's id (its filename minus `.ts`). `rollback-file` refuses on `NODE_ENV=production` (schema rollback runs as an explicit deploy step there). If `down` throws, the rollback is failed and the schema stays half-reverted — the framework can't auto-recover from a broken inverse. A non-destructive `down` only restores STRUCTURE, not data the `up` destroyed. Design the pair so `up` MOVES data it would otherwise drop: ```ts up: async ({ sql }) => { await sql.unsafe(`CREATE TABLE obsolete_archive AS SELECT * FROM obsolete`) await sql.unsafe(`DROP TABLE obsolete`) }, down: async ({ sql }) => { await sql.unsafe(`CREATE TABLE obsolete AS SELECT * FROM obsolete_archive`) await sql.unsafe(`DROP TABLE obsolete_archive`) }, ``` Higher disk cost during apply (two copies briefly), but the rollback is meaningful. ### `defineMigration` step files — `rollback` The separate step-based runner reverses with `voltro db rollback` (newest applied step) or `voltro db rollback --to ` (back through several). It re-runs each migration's `undo` effects in reverse step order against the `_voltro_migrations` table. This is a DIFFERENT runner from the `migration()` path above — see [the overview](./index.md) for why both exist. ## Rollback on prod `voltro db rollback-file` / `voltro db rollback` refuse during a serving prod process — schema changes (forward or reverse) run as explicit deploy steps, never on boot. For a planner change you want backed out in prod, ship a schema PR that re-declares the old shape and apply it through the same [prod pipeline](./prod-pipeline.md) as any other change. ## When to design for reversal vs forward-fix | Scenario | Recovery path | |---|---| | New code crashes on boot, needs reverting | Image rollback + (if schema is incompatible) a forward-apply of the old shape | | New code is fine but the new schema has a bug | NEW migration that fixes the bug — don't reverse to a broken intermediate | | A `lossy` apply destroyed data you needed | Restore from backup / PITR, or `restore-snapshot` if soft-dropped | | Critical bug in production, need to undo NOW | Restore from backup — faster than re-planning when speed matters | For production incidents, treat backup + PITR as the first-line option, not schema reversal. Once data is gone, no forward plan brings it back — which is exactly why the framework's apply path is conservative and the prod refuse-to-boot is strict. --- ## Soft-drop recovery _VOLTRO_SOFT_DROP=1 renames dropped columns instead of deleting them. voltro db restore-snapshot brings them back. (Distinct from Rollback — this is the one path that recovers DROP-COLUMN data.)_ > This page is the ONLY mechanism that recovers data from a > `DROP COLUMN`. The sibling [Rollback](./rollback.md) page covers > reversing structural changes via forward-apply and file-based `down` > bodies — neither of which brings back dropped data. If you dropped a > column and want its data back, you needed `VOLTRO_SOFT_DROP=1` set at > apply time; that's what this page is about. By default Voltro's applier issues `ALTER TABLE ... DROP COLUMN` for any lossy migration. Once that runs the data is gone — your only options are a full DB restore or replaying from a backup. `VOLTRO_SOFT_DROP=1` rewrites every `drop-column` op into a RENAME. The data stays in a sidecar column named `__dropped_`. `voltro db restore-snapshot ` walks the migration's operations + RENAMEs them back. ## When to use Set `VOLTRO_SOFT_DROP=1` in your apply pipeline as a default safety net. The cost is one extra column per drop (data still takes disk space until GC); the benefit is a one-command recovery window. Specifically helpful for: - **Reversible production migrations** — drop a column, realize 30 minutes later it broke a downstream report, restore it. - **Pre-release schema churn** — drop columns liberally during pre-release, recover when you change your mind. - **High-stakes drops** — set `VOLTRO_SOFT_DROP=1` per-migration via shell env var for the specific apply. ## Apply with soft-drop ```bash VOLTRO_SOFT_DROP=1 voltro db apply ``` The applier looks at every op in the plan. For `drop-column` ops it emits a RENAME instead of a DROP: ```sql -- Without VOLTRO_SOFT_DROP ALTER TABLE "users" DROP COLUMN IF EXISTS "legacy_email"; -- With VOLTRO_SOFT_DROP=1 ALTER TABLE "users" RENAME COLUMN "legacy_email" TO "legacy_email__dropped_20260603145522"; ``` The migration row still says "drop-column applied" in `_voltro_migration_plans` — the planner doesn't know the rename happened. The post-apply fingerprint is what matters for boot checks (and that's based on the declared schema, where the column genuinely doesn't exist anymore). ## Restore ```bash voltro db restore-snapshot plan_01j5xkqyz... ``` ``` ↩ restored users.legacy_email (from legacy_email__dropped_20260603145522) ✓ restored 1 column(s) from plan 'plan_01j5xkqyz...' ``` The command: 1. Loads the migration plan by id. 2. Walks every op in `operations`, finds the `drop-column` ones. 3. For each `(table, column)`, queries `information_schema.columns` for a `__dropped_*` match. 4. RENAMEs the most-recent match back to the original name. If no snapshot matches (the plan was applied without `VOLTRO_SOFT_DROP`), the command logs a warning + skips that column but continues with the others. ## Workflow 1. Apply a risky migration with soft-drop enabled. 2. The dropped column survives as `__dropped_`. 3. Test the post-apply state. 4. Either: - **It's fine.** Wait for GC (see below) to actually drop the column. - **It broke something.** Run `voltro db restore-snapshot ` to bring the column back. 5. Re-fix your schema in code (re-declare the column in the `.entity.ts` file) + run a normal `voltro db apply` to put the schema back in shape. Step 5 is important — the framework's declared schema is the source of truth. Just restoring the snapshot brings the data back but the declared schema still says the column shouldn't exist; the next apply would re-drop it. ## The differ leaves snapshots alone A soft drop leaves an object in the database that no schema declares — that is the whole point of it. The planner treats every `__dropped_` as framework-managed and never plans a drop for it, the same way it skips `_voltro_*` and the cluster engine's tables. This holds for columns and for tables. You do not need `VOLTRO_DB_IGNORE_TABLES` for a snapshot, and you should not add one: that list is for YOUR unmanaged infra tables, and an entry there would still be in your config long after `gc-snapshots` reclaimed the snapshot. ## GC Snapshot columns aren't automatically dropped. They survive until you: - Run a manual `ALTER TABLE ... DROP COLUMN __dropped_` (or a wrapping migration that handles it). - Drop the table entirely. Reclaim the space once your "is the migration confirmed safe?" review window has passed with `voltro db gc-snapshots --before ` — it permanently drops every `__dropped_` column AND table older than the date (the `` stamp drives the comparison). Add `--dry-run` to preview: ```bash voltro db gc-snapshots --before 2026-01-01 --dry-run # preview voltro db gc-snapshots --before 2026-01-01 # drop them (NOT reversible) ``` ## Limitations - **Restore needs `VOLTRO_SOFT_DROP=1` at apply time.** Without it the drop is a hard `DROP COLUMN` / `DROP TABLE` and the data is gone — `restore-snapshot` can only bring back what was soft-dropped (renamed to `__dropped_`). It restores both columns AND tables. - **Lossy at the row level.** This recovers DROP COLUMN (everything from before the drop is in the snapshot column). It does NOT recover UPDATE/DELETE row-data — those are forever. For that, use database backups. - **No effect on planner classification.** Lossy ops are still classified as lossy by `voltro db plan`. `VOLTRO_SOFT_DROP` is about HOW the drop happens, not WHETHER it's classified safe. The lossy refusal still requires explicit `dropped()` annotation or `VOLTRO_DESTRUCTIVE_OK=1`. ## See also - [Migration overview](/docs/database/migrations) — the full lifecycle - [Operation classes](/docs/database/migrations/operation-classes) — the 7-class taxonomy including `lossy` - [Drift detection](/docs/database/migrations/drift) — for out-of-band changes --- ## Drift detection _How the framework detects schema drift (live DB ≠ last applied fingerprint), what causes drift, and how to reconcile it — with the planner's introspection, or via corrective plan, or by accepting + re-baselining._ Drift = the live database's schema doesn't match the fingerprint of the last applied `voltro db apply` plan. It's a passive detection — the framework only knows about drift after introspecting + comparing fingerprints. The detection itself is cheap (one COUNT + one fingerprint compare per check); the reconciliation path depends on cause. ## How drift gets detected Three trigger paths: 1. **Manual** — `voltro db drift` runs the check on demand: exit 0 match, 3 no baseline, 4 drift 2. **On boot (dev)** — every `voltro dev` boot runs the planner, which detects drift implicitly (the plan will be non-empty) 3. **Periodic (cloud)** — the cloud dashboard polls each app's `/_voltro/inspect/migrations` endpoint; drift state is in the response All three paths produce the same `DriftSnapshot` shape: ```ts { isDrifted: boolean, liveFingerprint: string, // current introspected schema fingerprint lastAppliedFingerprint?: string, // the BASELINE the newest row recorded lastAppliedAt?: string, // when it was applied lastAppliedId?: string, // plan id } ``` `isDrifted: false` ↔ `liveFingerprint === lastAppliedFingerprint`, **or** no baseline was recorded — nothing compared is not the same as nothing changed, and it is never reported as drift. ### What it compares, and what it does not The baseline is `_voltro_migration_plans.liveFingerprint`: the fingerprint of the **live** schema as it was immediately after the last `voltro db apply`. Not `fingerprint`, which is the **declared** snapshot's hash — introspection cannot recover everything a declaration carries (generated expressions, `maxLength`, sensitivity markers), so a live hash and a declared hash never agree and comparing them reports drift on every clean database. Both sides hash the **whole** live schema, framework tables included. A framework upgrade that adds a `_voltro_*` column therefore shows as drift until the next `db apply` records a new baseline — honest, since the live schema did change, and it self-heals on the apply the upgrade needs anyway. ### No baseline yet A ledger row written before `liveFingerprint` existed has no baseline, and so does a database whose schema was already current when it upgraded. `voltro db drift` says so and **exits 3**: ```sh $ voltro db drift db drift: no drift baseline recorded yet — cannot compare ``` Run `voltro db apply` once — a no-op apply backfills the baseline too. Until then use `voltro db plan`, which compares declared against live directly. **Exit 3 is deliberately not 0.** "Did not compare" is not "clean", and a CI gate on the exit code has to be able to tell them apart — otherwise it passes vacuously on a stable schema, which is the failure drift detection exists to prevent. | exit | meaning | |---|---| | 0 | compared, live matches the baseline | | 3 | no baseline — did NOT compare | | 4 | compared, live diverged | ## Common causes ### Manual DDL Someone ran `ALTER TABLE ...` or `CREATE INDEX ...` via psql / DataGrip / Adminer instead of the framework. The live DB has changes the planner's history doesn't reflect. ```sh $ voltro db drift db drift: live schema DIVERGED from last applied state baseline: 8f507ba1e1aadad5 at 2026-06-15 14:32:00 (plan_mig_5k78) live now: a8f2c9d10b3f4e62 Something changed the live schema after the last apply. This command can see THAT it changed, not what or who — the fingerprints are hashes, not a diff. Your DECLARED schema is already satisfied — `voltro db plan` reports 0 operations against this database. So the live schema is not wrong, only unrecorded: something applied a change without going through the planner (a hand-run ALTER, a DBA window, a restored dump), or it touched a table your code does not declare. If that was deliberate and the schema is right, record it: voltro db drift --accept It updates the latest ledger row's baseline to the live schema and invents no history entry. Drift then measures from here. ``` When `db plan` is NOT empty it says that instead, with the count — so the two cases are told apart by the command rather than left to you. It used to close by asserting that a zero-operation plan meant "a table your code does not declare", which is one of two possibilities and the less likely one. ### Out-of-band auto-applier Multiple tools applying to the same DB (the framework + a separate Flyway / Liquibase process / hand-written deploy script). The other tool's changes don't go through `_voltro_migration_plans`. ### Truncated history table `_voltro_migration_plans` was truncated, restored from a backup, or the DB was restored to a point-in-time before the latest applies. The live schema is post-apply but the table doesn't know it. ### Replica fingerprinted instead of primary The drift detector ran against a read-replica that's lagging. Wait for the replica to catch up + re-check. (The framework's drift detector targets primary by default; this only bites when the user explicitly points the check at a replica URL.) ## Reconciliation paths ### Path 1 — accept the live state When the live DB IS what you want (the manual DDL is correct, only bypassing the planner was sloppy), first make sure your declaration says so: edit the `*.entity.ts` files until `voltro db plan` diffs empty. Then record the live schema as the baseline: ```sh voltro db plan # must report 0 operations — declared == live voltro db drift --accept ``` `--accept` writes the current live fingerprint onto the newest ledger row. Drift measures from there, and the next `voltro db drift` exits 0. **It refuses unless `db plan` is empty**, and that guard is the whole point. Accepting a schema with operations still outstanding would record "this is what we applied" over a state nobody applied, and every later drift check would measure against that fiction. If operations are pending, run `voltro db apply` — that applies them AND writes a real baseline of its own. It backfills the newest row rather than inserting one, because no migration ran and a history entry claiming otherwise would be worse than the gap it fills. **An empty `voltro db apply` does NOT re-baseline an existing baseline.** It writes no DDL and no history row, so there is nothing for a `--note` to attach to — it will tell you the note was ignored rather than swallow it. (It does fill a baseline that is still NULL, which is a different case: a database that never had one.) `--accept` is the command whose job is to say "the live schema is right, the ledger just did not know". ### Path 2 — corrective plan against drift When the live DB has accumulated cruft + the declared schema is what you want: ```sh voltro db plan # see the diff between code + live voltro db apply # execute the diff, removing the drift ``` The plan diff will show the corrective ops: ``` schema diff: 2 operations, 0 blocked ✗ DROP INDEX manual_idx_we_forgot_to_remove # safe (no FK depends on it) ⊕ ALTER TABLE users ADD COLUMN missing_field text # backfill: sql`'default'` fingerprint: a8f2c9d10b3f4e62 → 8f507ba1e1aadad5 ``` Apply lands the corrections + the new fingerprint matches the declared schema. ### Path 3 — declared schema needs updates The live DB has a column the declared schema doesn't reference, and you WANT to keep that column in the schema. Update the schema TS file to add it: ```ts // users.entity.ts export const users = table('users', { id: id(), email: text(), extra_field: text().nullable(), // add to declared }) ``` Now the live shape matches the declared shape after the next plan (which will be empty). The drift "fixed itself" through code changes. ## Drift on prod Production drift is the most important to catch quickly because it suggests an unauthorised change to the production DB. The cloud dashboard's drift detector runs every 5 minutes against each customer's prod app + surfaces the divergence as soon as it appears. Surfaces: - Dashboard banner on the affected app's Migrations tab - Audit log row tagged `drift.detected` Out-of-band channels (Slack, email, PagerDuty) are intentionally NOT in the framework. Subscribe to the audit log via your existing observability stack — every drift event is a row your SIEM / monitoring already consumes, and your team's incident process kicks in from there. The org's incident response process kicks in from there. Common immediate actions: 1. Check the audit log for any non-CI DB access 2. Run `voltro db drift` against a snapshot to confirm the divergence (the check is a structural fingerprint compare — it tells you THAT the schema diverged, not which rows changed) 3. Decide: corrective plan or re-baseline? ## What the dashboard shows The Migrations tab's drift banner renders when `isDrifted: true`: ``` ⚠ Schema drift detected last applied: 8f507ba1e1aadad5 at 2026-06-15 14:32:00 live now: a8f2c9d10b3f4e62 → Run `voltro db plan` to see what your code expects vs the live DB. ``` Click → expands to a comparison view showing the introspected live shape + the declared shape, highlighting the divergent tables. (Cloud dashboard only; local devtools shows just the banner without the comparison view.) ## What about replicas? Each replica has its own catch-up state. The framework's drift detector compares against PRIMARY by default; replicas catch up via the normal replication stream + reach the same fingerprint within their lag window. If you specifically want to monitor replica drift (rare; mostly relevant during major maintenance windows), the cloud dashboard's Settings page allows enabling "Replica drift monitoring" which polls each replica URL separately + alerts on lag > N minutes. ## Drift after rollback `voltro db rollback` itself records a new row in `_voltro_migration_plans`, so the fingerprint of that row matches the post-rollback state. No drift gets reported as a side-effect of rollback. If something else changed the live DB between the original apply + the rollback, that drift was already present + the rollback doesn't surface it differently. Run `voltro db drift` after rollback to confirm reconciliation if you're suspicious. ## Detecting drift is the easy part The hardest part of drift response is figuring out **what** changed + **who** did it. The framework can tell you that the fingerprints differ + show the structural diff. It can't tell you who ran the DDL or why. Pair the framework's drift detector with: - Database audit logs (Postgres `pgaudit`, MySQL audit plugin, MSSQL Audit, SQLite no-op) - Network access logs (who reached the DB during the drift window) - Application logs filtered by trace id (if the drift happened during a request, trace shows the caller) - The team's normal incident response (Slack channel for accidental changes, post-mortem cadence) ## TL;DR ``` Detect: voltro db drift Fix code: voltro db apply (apply corrective plan from current diff) Adopt DB: edit the *.entity.ts to match live until db plan is empty, then voltro db drift --accept Backup: if data was lost, restore from your DB backup system — the framework can't help ``` --- ## Devtools UI _Walkthrough of the local devtools dashboard's Migrations tab — drift banner, pending plan card, and history timeline. What's clickable, what's not, and when the Apply button appears._ The local devtools dashboard (`http://localhost:5179`) ships a per-app Migrations tab that mounts the shared `MigrationsPage` component from `@voltro/devtools-ui`. The cloud dashboard mounts the same component over different transport — see [Cloud UI](./cloud-ui.md). To reach it: `voltro dev` boot launches the dashboard automatically, pick an app from the sidebar, click the **Migrations** tab between Database and Data. ## Layout ``` ┌──────────────────────────────────────────────────────────────┐ │ Migrations │ │ App: myApi · http://localhost:4000 │ ├──────────────────────────────────────────────────────────────┤ │ ⚠ Schema drift detected │ ← drift banner │ last applied: 8f507ba1e1aadad5 at 2026-06-15 14:32:00 │ only when drifted │ live now: d32149b280101693 │ │ → Run `voltro db plan` to see what your code expects. │ ├──────────────────────────────────────────────────────────────┤ │ Pending plan — 3 ops │ ← pending card │ ✓ ALTER TABLE users ADD COLUMN bio text │ classifications + │ ⊕ ALTER TABLE users ADD COLUMN email text │ fix hints │ # NOT NULL, declared backfill: sql`'unknown-' || id` │ │ ⊕ UPDATE users SET email = ... │ │ │ │ from 8f507ba1e1aadad5 → to a8f2c9d10b3f4e62 │ ├──────────────────────────────────────────────────────────────┤ │ Applied history — up to 20 plans │ ← history timeline │ plan_mig_5k78 fp=d3214928 env=dev src=auto-diff ... │ │ plan_mig_5k77 fp=b414a413 env=dev src=auto-diff ... │ │ plan_mig_5k76 fp=bd27c13e env=dev src=auto-diff ... │ │ ... │ └──────────────────────────────────────────────────────────────┘ ``` Auto-refreshes every 10 seconds. The transport is HTTP — the page fetches `GET /_voltro/inspect/migrations` directly + renders the JSON. ## Drift banner Renders at the top in rose when `drift.isDrifted: true`. Surfaces: - **last applied fingerprint** + the time it was applied - **live fingerprint** computed from the current introspection - A pointer at the CLI command to investigate The banner is informational — it doesn't block anything. The Apply button below it (when present) still works; the plan diff will include reconciling changes to bring live back to declared. Common drift causes: - A `psql` (or equivalent) session ran DDL outside the planner - A team member applied a plan but the row didn't make it into `_voltro_migration_plans` (rare; would mean the applier crashed after DDL but before writing the row) - The `_voltro_migration_plans` table itself got truncated / restored from a backup For non-trivial drift, run `voltro db drift` in the shell — same diagnosis, with a copy-pasteable fix path. ## Pending plan card Renders the `MigrationPlan` from the planner. - One line per `PlannedOperation` with a color-coded badge for its `OperationClass`: - `safe` / `needs-default` → emerald - `needs-backfill` / `needs-rename-annotation` → amber - `lossy` → rose - `online-required` → cyan - `multi-step` → fuchsia - The op description follows the badge — e.g. `ALTER TABLE users ADD COLUMN email text NOT NULL` - A dim-text reason follows: `# backfill declared (kind=sql) — applier will run 3-step add/update/set-not-null` - For blocked ops, a red `! fix:` hint underneath: `! fix: declare email: text().backfill(sql`...`) ...` Below the op list, a chip row summarises counts per class. Below that, the from/to fingerprint short forms. ### The Apply button The shared `MigrationsPage` component renders an "Apply plan" button only when BOTH the caller's `canApplyMigration` capability is set AND the host wires an optional `useApplyPlan` hook (and the pending plan has zero blocked ops). The local devtools is single-user, single-machine, so it can wire that hook; the cloud dashboard never does (see [Cloud UI](./cloud-ui.md)). When the Apply button isn't wired, the page surfaces the next-best thing: run `voltro db apply` from the project root + the page auto-refreshes within 10 seconds to show the post-apply state. That CLI path carries `--note '...'`, runs with whatever credentials are in the operator's shell, and exits with a status code CI/CD can act on — and it's the only path that ever reaches production, since `voltro db apply` refuses on `NODE_ENV=production`. ## History timeline Lists rows from `_voltro_migration_plans` newest-first, capped at 20. Per-row: - **plan id** — `plan_mig_5k78`, the typeid from the row - **fp** — 16-char short fingerprint of the post-apply state - **env** — dev / staging / prod (color-coded chip) - **src** — auto-diff (planner) or file (file-based migration) - **op count** — how many ops were in that plan - **duration** — milliseconds the apply took - **applied at** — ISO timestamp - **applied by** — CLI user / `boot:dev` / service principal - **notes** — the freeform `--note` string if provided, dim italic Click a row to expand → shows the operations JSON (the full `PlannedOperation[]` that ran). Useful for "why is `users.legacy` gone?" — find the plan that dropped it, see exactly what executed. ### No per-row Rollback button History rows are read-only — there is NO Rollback button on a plan row. That matches the runtime: planner-applied plans have no auto-rollback (see [Rollback](./rollback.md)). The page DOES surface a separate restore control for soft-dropped columns (when a plan was applied with `VOLTRO_SOFT_DROP=1`, the sidecar columns get a per-plan "Restore" button driving `voltro db restore-snapshot`). Reversing a file-based migration's `down` body is a CLI-only action (`voltro db rollback-file `). ## Loading + error states The page wraps the data fetch in a `DataSource`: - Pending: shows a loading card while the first fetch is in flight - Error: renders the error message in rose - Data + error both undefined → page is in initial state, no flicker A common error: the framework's inspect endpoint isn't reachable. Verify with `curl -sS http://localhost:4000/_voltro/inspect/migrations` — should return JSON. If not, check that the app is running + `VOLTRO_INSPECT` isn't `off`. ## Multi-app The dashboard's app picker shows every running voltro process (from `~/.voltro/runtime-registry.json`). Each app gets its own Migrations tab; the data is per-app + the URL includes the app id. If multiple apps target the same database, they're showing the same `_voltro_migration_plans` rows — applied plans are global. Drift detection runs against the live DB per-app inspect, so if one app is talking to a different db (env var override etc.) you might see drift on one but not the other. ## Source code The shared component lives in `voltro/packages/devtools-ui/src/pages/MigrationsPage/page.tsx`. The local devtools wiring is in `voltro-devtools/apps/dashboard/src/pages/apps/[appId]/migrations/page.tsx`. Both repos are open to extension. --- ## Cloud UI _Cloud dashboard's Migrations tab — what it shows, the submit→review→approve workflow with HMAC-signed review URLs, why it never applies (and never will), how it differs from the local devtools UI, and the multi-tenant boundary._ The cloud dashboard's per-app Migrations tab mounts the same `MigrationsPage` component the local devtools uses — the page itself is portable. The difference is transport + capabilities. ## Transport Local devtools fetches `/_voltro/inspect/migrations` directly over HTTP. The cloud dashboard can't — it doesn't have direct network access to the customer's app, and the inspect endpoint isn't internet-exposed in a sane deploy. Instead, the cloud dashboard subscribes via cloud-RPC: ```ts useAppInspectMigrationsStatus(appId) // → calls the cloud-api's `apps.inspectMigrationsStatus({appId})` query // → cloud-api looks up the app row (tenant-scoped to the caller) // → cloud-api calls `/_voltro/inspect/migrations` with the app's stored inspectToken // → cloud-api returns the response to the dashboard ``` Three indirection levels: dashboard → cloud-api → customer-app → DB. Each hop is auth-scoped — the dashboard only sees apps the calling user has access to; the cloud-api enforces caller-owns-tenant on every request; the customer-app verifies the inspectToken. Live data: the subscription stays open; deltas push when the underlying `_voltro_migration_plans` row set changes (the customer-app's reactive engine emits a change event on insert, which propagates through the cloud-api's RPC proxy back to the dashboard subscription). Same UX as the local devtools' 10-second polling, but push-driven. ## The Apply button is gone — permanently Apply locally with `voltro db apply` (the dev CLI). The cloud dashboard never applies — by design; it shows the plan, the approval workflow gates a human-triggered apply, and the apply itself runs in your own pipeline. Reasoning: - Cloud-applied plans would need to hold migration-grade credentials in the cloud-api. Today the inspectToken authorises READ-only access to inspect endpoints; an apply endpoint would require an entirely separate trust boundary. - Cloud-applied plans would mean the cloud platform owns the operational responsibility for the customer's schema. We don't want to. - The plan model is designed for human review BEFORE apply. A button click that says "apply now" without forcing the operator through `voltro db plan` first → review → deploy isn't the safety story the docs promise. - For prod specifically: the rule is `voltro db apply` runs as an explicit deploy step (and refuses on `NODE_ENV=production`). No browser button bypasses that. What the cloud dashboard DOES instead: surfaces the pending plan (ops + classifications + fingerprints) read-only, and points the operator at the CLI command to run in their own pipeline: ``` Pending plan — 3 ops · 0 blocked Apply via your deploy pipeline: voltro db apply # re-diffs live + applies; run as a deploy step ``` The apply re-diffs the live DB against the deployed code, so the dashboard doesn't hand out a serialised plan to feed back in — the plan it shows is the PREVIEW, and `voltro db apply` recomputes the same diff at deploy time. This keeps the dashboard the source of truth for what's pending, while never executing. ## Multi-environment view A per-app cloud dashboard has multiple environments. The Migrations tab surfaces all of them with sub-tabs: ``` ┌────────────────────────────────────────────────────────────┐ │ Migrations │ │ App: myApi · project: acme · org: acme-inc │ ├────────────────────────────────────────────────────────────┤ │ [ dev ] [ staging ] [ prod ] │ ← env tabs ├────────────────────────────────────────────────────────────┤ │ │ └────────────────────────────────────────────────────────────┘ ``` Each tab shows its own `MigrationsStatus`. Switching tabs is instant + the data is per-env subscription. Common workflow: 1. Reviewer opens the PR's preview env (`dev`) tab → confirms the plan ran cleanly there 2. Reviewer switches to `staging` → sees the SAME fingerprint applied → fingerprint chain looks healthy 3. Reviewer switches to `prod` → fingerprint is still old → operator hasn't run apply yet, that's expected, OK to merge When prod drifts (the production fingerprint is older than staging), the prod tab shows the drift banner. Out-of-band alerting (Slack ping, email, PagerDuty) is intentionally NOT the framework's job — your deploy pipeline already has hooks for it (GitHub/GitLab Actions, k8s controllers, ArgoCD, etc.). The dashboard is the source of truth; whatever notification system your team already runs subscribes to the relevant pipeline events. ## Pending-plans queue + approval workflow The cloud-api ships a human-gated approval flow on top of the read-only view above: an operator's deploy pipeline **submits** a plan, reviewers **approve** (or reject) it, and only then does a human run `voltro db apply` from the pipeline. The cloud still NEVER applies the schema itself — approval gates the human apply, it does not perform it. ### Submit a plan The pipeline computes the plan with `voltro db plan --json` and posts it to the `migrations.submitPlan` mutation. The plan lands as `pending` in the `migration_plans` table (tenant-scoped) and the mutation returns the plan id plus an **HMAC-signed shareable review URL**: ```ts const { id, status, reviewUrl } = await submitPlan.mutate({ orgId, // = the active tenant id appId, // which deployed app the plan targets env: 'staging', // free-form env tag the pipeline submits fingerprint: livePlan.toFingerprint, operations: livePlan.operations, // [{ kind, table, classification, … }] summary: livePlan.summary, // per-classification counts }) // status === 'pending' // reviewUrl === 'https://dashboard…/migrations/review?t=' ``` The review URL carries a token whose body is `{ planId, exp }`, base64url-encoded, with an HMAC-SHA256 signature appended. The token is signed with `VOLTRO_CLOUD_SECRET` (falling back to `VOLTRO_SESSION_SECRET`), so it can't be forged for a plan the reviewer was never sent, and it expires after 7 days. A reviewer opening the link hands the token to `migrations.reviewByToken`, which verifies the signature + expiry server-side (constant-time compare) before returning the plan — no interactive session required. ### Review + decide The dashboard's per-app Migrations tab renders a **Pending Approvals** section above the read-only history. It subscribes to `migrations.pendingApprovals` (a reactive, tenant-scoped query over `migration_plans`) — each plan expands to its operation list + per-classification summary badges, with **Sign / Approve** and **Reject** buttons plus an optional note. Signing a decision calls the `migrations.approve` mutation, which — in one transaction — appends an immutable row to `migration_approvals` and flips the plan's `status` to `approved` / `rejected`. The subscription drops the plan from the queue on the status flip. Approval is **role-gated**: only org `owner` / `admin` can sign a decision (the same role guard the member-invite flow uses). A reviewer without the role sees a typed `MigrationApprovalForbidden` rejection. ### Still no apply from the browser Approval is distinct from apply, by design. An approved plan tells the operator "a reviewer signed off"; the operator still runs `voltro db apply` as an explicit deploy step (which re-diffs live and refuses on `NODE_ENV=production` without a pre-reviewed plan). When the apply lands, the pipeline reports back and the plan's `status` moves to `applied`. No browser button ever executes DDL. ## Audit log The cloud-api's audit log records the events it observes — who read the migrations tab, who submitted / approved / rejected each plan (the `migration_approvals` table is the immutable decision trail, stamped with `signedBy` + `decidedAt`), and (via the customer app's own history) which plans were applied and when. ## What's NOT in the cloud UI - **Apply button** — see above - **Rollback button** — there is no per-plan rollback anywhere (the runtime has no auto-rollback for planner plans — see [Rollback](./rollback.md)), so the dashboard surfaces none - **Inline plan editing** — plans are immutable; correct via a new PR - **Cross-app comparison** — each app's tab is independent ## Permissions Reading the Migrations tab requires the `canViewMigrationDetails` capability + caller-owns-tenant on the app. The framework's `Subject` model + per-org role mapping carries this; the dashboard hides the tab from users who lack the cap. Signing an approval decision is gated further: only org `owner` / `admin` can call `migrations.approve` — the server enforces this regardless of which buttons the UI renders. ## Comparison with local devtools | | Local devtools | Cloud dashboard | |---|---|---| | Transport | HTTP fetch (direct) | Cloud-RPC subscribe (proxied) | | Auth | none (local-only) | session + tenant scope + per-app cap | | Multi-env | no (per app at a time) | yes (tabs per env) | | Apply | cap-gated (single-user) | NEVER | | Approval workflow | no | submit → review → approve (owner/admin) | | Audit log | local log buffer | persisted in cloud-api | | Drift alerts | banner only | banner only (in-app) | | Live updates | 10s polling | push via subscription | ## Source code - Component: `voltro/packages/devtools-ui/src/pages/MigrationsPage/page.tsx` — shared - Cloud wiring: `voltro-cloud/apps/voltro-cloud/dashboard/src/pages/_/p/[orgSlug]/[projectSlug]/apps/[appSlug]/migrations/page.tsx` - Cloud-api proxy: `voltro-cloud/apps/voltro-cloud/api/queries/apps.inspectMigrationsStatus.query.ts` + `.query.server.ts` - Approval schema: `voltro-cloud/apps/voltro-cloud/api/database/migrationPlans.entity.ts` + `migrationApprovals.entity.ts` - Approval rpc: `migrations.submitPlan` + `migrations.approve` (mutations), `migrations.pendingApprovals` (query), `migrations.reviewByToken` (action) - Review-URL signing: `voltro-cloud/apps/voltro-cloud/api/lib/migrationReviewUrl.ts` (HMAC-SHA256 over `{planId, exp}`, `VOLTRO_CLOUD_SECRET` / `VOLTRO_SESSION_SECRET`) --- ## Troubleshooting _The most common "why is my voltro dev refusing to boot?" cases with copy-paste fixes. Each pattern maps a planner error message to the schema annotation that resolves it._ Every refuse-to-boot from the planner includes a structured fix hint. This page is the comprehensive catalog of those hints + the schema edit each one wants. ## "NOT NULL column on a table whose row count is unknown" Full error: ``` auto-migrate: REFUSED — 1 blocked operation(s): - add-column [users]: NOT NULL column on a table whose row count is unknown fix: declare `email: text().backfill(sql`...`)` OR `.default(value)` so existing rows survive the migration ``` What happened: you added a non-nullable column to a populated table without telling the planner how to populate it for existing rows. Fix — pick ONE: ```ts // Option A — constant default (planner emits ADD COLUMN ... NOT NULL DEFAULT value) plan: text().default('free'), // Option B — SQL expression (planner emits ADD nullable → UPDATE → SET NOT NULL) email: text().backfill(sql`'unknown-' || id || '@local'`), // Option C — JS function (slower, only when SQL can't express what you need) embedding: text().backfill(async (row) => embed(row.title)), // Option D — make it nullable bio: text().nullable(), ``` Decision rubric in [backfill.md](./backfill.md). ## "column missing from declared schema" (DROP COLUMN refused) Full error: ``` ✗ ALTER TABLE users DROP COLUMN legacyField ! fix: if intentional, add `legacyField: dropped()` to the schema. If a typo, restore the field ``` What happened: the live DB has a column the declared schema doesn't reference. Could be deliberate (you want to drop it) or accidental (someone deleted the field from the schema by mistake). Fix — pick ONE: ```ts // Option A — declare intent. Column goes away on next apply. export const users = table('users', { id: id(), legacyField: dropped(), // ← explicit. Planner allows the drop. }) // Option B — typo, restore the field. export const users = table('users', { id: id(), legacyField: text(), }) // Option C — use VOLTRO_DESTRUCTIVE_OK for one-off applies (loud warning): // VOLTRO_DESTRUCTIVE_OK=1 voltro db apply ``` ## "table missing from declared schema" (DROP TABLE refused) ``` ✗ DROP TABLE oldUsersTable ! fix: if intentional, set VOLTRO_DESTRUCTIVE_OK=1 OR add a file-based migration. If a typo, restore the table declaration ``` Fix: ```ts // Option A — restore the table declaration (probably the right answer if surprised) export const oldUsersTable = table('oldUsersTable', { id: id(), ... }) // Option B — file-based migration that moves data out + then drops. // The file context is { sql, log, appliedAt } — no store; use sql: // migrations/20260415_retire_old_users.ts export default migration({ id: '20260415_retire_old_users', description: 'Move oldUsersTable rows into users, then drop it.', up: async ({ sql }) => { await sql.unsafe(`INSERT INTO users (id, ...) SELECT id, ... FROM "oldUsersTable"`) await sql.unsafe(`DROP TABLE "oldUsersTable"`) }, down: async ({ sql }) => { /* recreate + restore as far as possible */ }, }) // Option C — one-off destructive apply: // VOLTRO_DESTRUCTIVE_OK=1 voltro db apply --note 'retiring oldUsersTable per ticket #...' ``` **Delete the entity and drop the table in the SAME change.** The intuitive order — remove the code first, sort the schema out after — is the broken one: with `VOLTRO_AUTO_MIGRATE=1` the very next boot sees an undeclared table, refuses, and the app crashloops until the drop is authorised. There is nothing to recover from, but the app is down while you work it out. Take the entity out together with the `VOLTRO_DESTRUCTIVE_OK` apply that removes its table, or leave the entity in place until you are ready to run both. **Three tables this never proposes dropping:** - **`actors`** — the framework-provided audit subject. You don't declare an `actors.entity.ts`; `db plan` / `db apply` auto-include the built-in `actors` in the declared set, so it's never a DROP candidate. (Declare your own `actors` with extra columns and that takes precedence.) - **Your own UNMANAGED infra tables** — a table you keep outside the Voltro schema (a migration id-map, a legacy audit table). List them in `VOLTRO_DB_IGNORE_TABLES` (comma-separated) and the diff leaves them alone instead of planning a DROP: ```bash VOLTRO_DB_IGNORE_TABLES=_strapi_id_map,_legacy_audit voltro db apply ``` The **same env var is honoured by the `voltro dev` boot auto-migrate**, not just the `db plan` / `db apply` CLI — set it in the app's environment and the boot diff leaves the listed tables alone too, so a fresh clone with a Strapi→Voltro `_strapi_id_map` sitting in the DB won't refuse-to-boot on a `drop-table`. (The framework already self-excludes its own `_voltro_*` / `cluster_*` runtime tables; this is the user list on top of that.) - **Soft-drop snapshots** — a `__dropped_` left behind by `VOLTRO_SOFT_DROP=1`. The differ treats it as framework-managed until `voltro db gc-snapshots` reclaims it, so do NOT add one to `VOLTRO_DB_IGNORE_TABLES`. ## A migrate / apply DDL statement failed — find which one When `voltro migrate` / `db apply` hits a DDL error, the CLI names the **failing statement** plus the driver's fields, not just a stack: ``` ═══ migrate: statement failed ═══ statement: CREATE INDEX "users_orgId_idx" ON "users" ("orgId") db.message: column "orgId" does not exist db.code: 42703 ``` A `column … does not exist` on a `CREATE INDEX` usually means the column was never added to an EXISTING table: `voltro migrate` (auto-migrate) is `CREATE TABLE IF NOT EXISTS` — it does NOT `ADD COLUMN` to a table that already exists. To evolve an existing table's columns, use the declarative path (`voltro db plan` → `db apply`), which orders `ADD COLUMN` before the index. Set `VOLTRO_MIGRATE_DEBUG=1` to trace every statement as it executes. ## "users.givenName looks like a new required column on a populated table" Full error: ``` ✗ ALTER TABLE users ADD COLUMN givenName text # NOT NULL column on a table whose row count is unknown ``` What probably happened: you renamed `firstName` → `givenName` in the schema, but didn't tell the planner it's a rename. The planner sees `firstName` gone + `givenName` new + classifies each separately. Fix: ```ts export const users = table('users', { id: id(), givenName: text().renamedFrom('firstName'), }) ``` The planner folds the diff into one `ALTER TABLE users RENAME COLUMN firstName TO givenName`, classified `safe`. After the rename is applied in every env, the marker can be removed (covered in [rename-and-drop.md](./rename-and-drop.md)). ## "the migration did not converge" (apply refuses to record a fingerprint) ``` applyPlan: the migration did not converge. 31 operation(s) were executed without error, but re-planning against the live schema still finds 31: - alter-column-default todos.attachments … No fingerprint was recorded — recording one would make the next boot report "schema up to date" for a schema that was never applied. ``` Every statement ran and the database accepted every one of them, and none of them changed anything. That is possible because DDL that changes nothing succeeds exactly as quietly as DDL that works — `ALTER COLUMN x TYPE text` on a column that is already `text` is a valid, successful no-op. This message exists because the alternative is worse. Before the convergence check, such a plan reported `applied 31 op(s)`, recorded a fingerprint, and every later boot short-circuited on "schema up to date" — for a schema that had never been applied. One app ran that way for two releases. The apply now proves it worked before it records anything: the same planner, run against the database as it now is, must have nothing left to do. **It is a framework bug, not a mistake in your schema.** The named operations emit DDL that does not take effect. Report the operation kinds plus the column types involved. **Read the named operations before you trust the "no-op" wording**, though — the message states a CAUSE, and a cause can be wrong. One release told users `drop-table __dropped_` was a no-op when the DDL had worked perfectly: `VOLTRO_SOFT_DROP=1` had renamed the table, and the planner then read its own snapshot as one more undeclared table and proposed dropping it again. Fixed, and worth knowing as the shape to look for: an operation naming an object that the PREVIOUS operation created or renamed is a planner blind spot, not dead DDL. In the meantime the schema is unchanged and safe — nothing was half-applied, and no fingerprint was written, so `voltro db plan` still shows you the truth. If you need to move forward before a fix lands, apply the equivalent DDL by hand and re-run `voltro db plan` to confirm it converges. ## "Schema fingerprint mismatch" (prod refuse) Full error: ``` [voltro:start] auto-migrate: SCHEMA FINGERPRINT MISMATCH — declared = a8f2c9d10b3f4e62 live = 8f507ba1e1aadad5 Run `voltro db apply --plan plan.json` from the deploy pipeline before serving. exit 1 ``` What happened: the production runtime checked its declared schema's fingerprint against the latest `_voltro_migration_plans.fingerprint` and they don't match. The framework refuses to start serving because it doesn't know what to do — auto-apply on prod isn't allowed (see [prod-pipeline.md](./prod-pipeline.md)). Fix: 1. Preview the plan against the prod DB shape (NOT prod credentials — use a staging-replica snapshot): ```sh DB_URL= voltro db plan ``` 2. Review the printed plan in the PR. 3. Run apply from the CI/CD step (a one-shot job with migration credentials and `NODE_ENV` unset / `staging` — `voltro db apply` re-diffs live and refuses on `NODE_ENV=production`): ```sh voltro db apply --note 'PR #1234' ``` 4. Re-deploy. The new boot's fingerprint check passes. `--plan` IS a real flag, and it is the better answer here. This note used to say it was not, and told you to run a plain `voltro db apply` instead — which recomputes the diff and therefore applies something nobody reviewed. The claim came from a defect, not from the design: the app root was resolved as "the first argument that does not start with `-`", so `--plan plan.json` handed the plan FILE to schema discovery (`no schema files found, root: …/plan.json`) while `--plan=plan.json` worked. Both spellings work now. Prefer the reviewed form in a pipeline: ```sh voltro db plan --json > plan.json # review this in the PR voltro db apply --plan plan.json # apply exactly it, fingerprint-guarded ``` A plain `voltro db apply` stays correct for a developer machine, where the diff you would review is the one you just wrote. If the cause is drift (someone DDL'd prod manually), see [drift.md](./drift.md) for reconciliation paths. ### If the two processes are the same image: compare their `env:` blocks A fingerprint mismatch does not always mean the schema changed. The declared table set is what gets hashed, and until 0.35.0 three RUNTIME flags could move it — so a pre-deploy migrate Job and the pods it feeds could disagree while running identical code against one database. That was reported as a green migrate job followed by every pod in CrashLoopBackOff. The chart gave the Job its own `env:` list (`NODE_ENV`, `DB_*`) while `CDC: "0"` lived in the pods' block, because change data capture reads as a runtime concern. Measured on mariadb at `NODE_ENV=production`, each flag flipped alone: | flag | effect on the DECLARED set | | --- | --- | | `CDC=0` | removes `_voltro_cdc_offsets` | | `VOLTRO_UNDO=on` | adds `_voltro_undo_log` | | `VOLTRO_TRACING_PERSIST=all` | adds `_voltro_traces` | **From 0.35.0 none of them does.** `_voltro_cdc_offsets` follows the DIALECT, so a mariadb/mssql app declares it either way; the other two are declared in `app.config.ts` and the env vars only choose what a process captures. **And from 0.48.0 `NODE_ENV` does not either.** Until then, `_voltro_traces` and `_voltro_undo_log` defaulted to on outside production — safe for the fingerprint, which only ever compares processes inside ONE deployment, and unsafe for the reader that spans two. `voltro data` is that reader: a bundle exported from a development database carries the tables that database has, and a staged (non-destructive) `replace` needs every bundle table to exist in the target. One source tree therefore produced a bundle a production target could not stage, and the run fell back to truncating it — with nothing red anywhere. Both are declared in every environment now. An unused declared table is an empty table; a schema that differs per environment is a class of failure. Declare them only to leave one OUT: ```ts export default { // Declared in EVERY environment by default. Set `false` to keep a table out — // and then set it in every environment, or two deployments of one source tree // declare different schemas again. `VOLTRO_UNDO` / `VOLTRO_TRACING_PERSIST` // still decide what a process WRITES; they no longer decide what exists. schema: { undo: false, traces: false }, } ``` Setting a capture flag ON without the declaration now refuses the boot and names the field, rather than writing to a table nobody created. The refusal also prints which of the three tables this process decided and from which input, so the comparison is a glance rather than a hash diff. Do compare the two `env:` blocks anyway if you use plugins: a plugin's `extendSchema.tables` is your code and may read anything, which is the one part the framework cannot guarantee for you. ## "duplicate column name in plan" (logically invalid plan) ``` db apply: refusing — plan contains 2 ops targeting users.email (CREATE + DROP) This usually means the planner couldn't determine the correct order. Hand-edit the plan JSON or use a file-based migration to express the intent explicitly. ``` What happened: the diff produced both an ADD and DROP for the same column → ambiguous intent. Fix: it's almost always a schema edit ordering problem. Either: - The schema was edited twice + both edits are in the diff (rebase the PR; squash the two commits) - A column was renamed + another column was added with the same name (use `.renamedFrom()` on the second) - A file-based migration is racing the planner (sequence them differently — file before planner) ## "VOLTRO_DESTRUCTIVE_OK relaxes lossy ops only; plan also contains rename-without-marker" ``` auto-migrate: REFUSED — 2 blocked operation(s): - drop-column [users.legacy]: lossy fix: if intentional, add `legacy: dropped()` to the schema - rename-column [users.givenName from firstName]: rename without marker fix: declare `.renamedFrom('firstName')` on the new column VOLTRO_DESTRUCTIVE_OK=1 was set but at least one blocked op is NOT lossy. The flag only relaxes lossy ops; other refuse cases (rename, NOT-NULL-no-backfill, multi-step) stay firm. ``` What happened: you reached for `VOLTRO_DESTRUCTIVE_OK=1` to bypass a refuse, but the plan has a non-lossy refuse too. The flag is intentionally narrow. Fix: address the non-lossy refuse first (add the rename annotation in the example above). Then the flag relaxes the remaining lossy op. ## "live introspection failed; cannot diff" ``` db plan: live introspection failed cause: Connection refused at localhost:5432 ``` What happened: the framework can't reach the DB. The planner needs a live introspection to compute the diff. Fix: - Check the DB is running (`docker ps`, `systemctl status postgres`) - Check the connection URL: `echo $DB_URL` matches what the DB expects - Check credentials: `psql $DB_URL -c 'SELECT 1'` should succeed - If using cloud, check the inspectToken: `curl -H "Authorization: Bearer $TOKEN" "$APP_URL/_voltro/inspect/app"` should return JSON ## "advisory lock held; refusing to wait" ``` db apply: refusing — advisory lock 8732891 is held by another process (pid 4892) This usually means another `voltro db apply` is running. Wait for it to finish or kill the holder. ``` Fix: - If a real apply is running elsewhere, wait - If the holder is stuck (`pid 4892` died without releasing): - Postgres: `SELECT pg_advisory_unlock(8732891);` (run as the same user that acquired) - Or kill the postgres backend: `SELECT pg_terminate_backend()` - For MySQL: `SELECT RELEASE_LOCK('voltro_migration')` from the same connection (different connection won't release) The lock is per-database-cluster, not per-deploy. Two prod regions hitting the same DB cluster race; the second blocks until the first releases. ## `voltro dev` boot hangs at "auto-migrate: planning schema" (0/1, no error) The boot-time auto-migrate takes the **same** migration advisory lock as `db apply`. If a prior boot crashed while holding it (its DB connection still open) or a sibling pod holds it, the boot would otherwise wait on the lock — the pod sits at `auto-migrate: planning schema`, readiness never flips, and no error line prints. The boot now **fails fast** instead of hanging: it polls the lock to a deadline (default **30s**) and then aborts with a clear message rather than blocking forever. ``` could not acquire the postgres migration advisory lock within 30s. Another migration is in progress, or a prior boot crashed while holding it. ``` Fix: - A crashed process's **session-level** advisory lock is released the moment its DB connection closes — so a truly dead holder frees the lock on its own; just restart. - If a live-but-stuck backend holds it, find + terminate it: `SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%advisory%'` → `SELECT pg_terminate_backend()`. - Long, legitimate migrations on a big schema can outlast 30s — raise the ceiling with **`VOLTRO_MIGRATION_LOCK_TIMEOUT_MS`** (milliseconds). Related: if the boot instead REFUSES with a `drop-table` blocker for a table you want to keep (a `_strapi_id_map`-style leftover), that's the ["table missing from declared schema"](#table-missing-from-declared-schema-drop-table-refused) case — `VOLTRO_DB_IGNORE_TABLES` unfreezes it. ### Variant: it hangs even with the lock free (large / FK-dense schema) Same symptom, different cause. If nothing else holds the lock and the boot **still** sits at `auto-migrate: planning schema`, the **schema introspection** is the bottleneck — the step that reads the live database shape before diffing. It only runs on a real diff (a no-diff boot skips it via the schema fingerprint), which is why adding a single column can trigger it while an unchanged restart boots fine. The cause is almost always a **large, foreign-key-dense schema** (hundreds of tables, thousands of FKs). Introspection reads foreign keys and primary keys directly from `pg_catalog` (index-backed, filter pushed down) rather than the `information_schema` constraint views — those can't push the per-batch table filter down, so each batch re-scans the whole catalog. On a 500-table / 2600-FK schema that is the difference between **> 2 minutes (hangs)** and **well under a second**. If introspection ever degenerates again it **fails fast** instead of hanging: every introspection statement runs under a `statement_timeout` (default **30s**), so a runaway query aborts with an actionable error rather than freezing the pod at 0/1. ```text schema introspection exceeded VOLTRO_INTROSPECT_TIMEOUT_MS (30000ms) — the schema is very large / FK-dense or the database is slow. ``` Fix: - Raise the ceiling for a legitimately huge schema with **`VOLTRO_INTROSPECT_TIMEOUT_MS`** (milliseconds; `0` disables it entirely). - Prefer a **direct (non-pooler) connection** for migrations via **`DB_DIRECT_URL`** — so a large introspection response isn't mis-framed by a transaction-mode pooler. - **`VOLTRO_DB_IGNORE_TABLES` does not help here** — it filters the *diff*, which runs **after** introspection; the introspection cost is independent of it. ## A MySQL/MariaDB apply failed midway `voltro db apply` on **postgres / mssql** is ATOMIC: every op runs in ONE transaction, so a failure on op N rolls the WHOLE plan back — nothing is committed, no half-applied schema. (`online-required` `CREATE INDEX CONCURRENTLY` ops run after the commit — they can't be in a transaction — so a failure THERE can leave the index half-built; re-apply finishes it.) > **Run migrations through a SESSION connection, not a transaction-mode > pooler.** Because the whole plan is one transaction, a large apply (many > ops + big backfill `UPDATE`s + index builds) is ONE long-lived > transaction. A transaction-mode pooler (Supabase Supavisor on `:6543`, > PgBouncer in `transaction` mode) can't hold a multi-statement transaction > reliably and will abort it — surfacing as an opaque `Failed to execute > statement (at sql.transaction)`. Point `DB_URL` at the **direct / session > connection** (`:5432`, or a session-mode pooler) for `db apply`; raise > `statement_timeout` for that session if a single index build is slow. This > is the same constraint every migration tool has (Prisma/Drizzle/etc.) — > the transaction pooler is for app traffic, the direct connection is for > migrations. The failing statement itself is now logged with its SQL + > `db.code` (e.g. `57014` statement timeout) so you can see which op stalled. **MySQL and MariaDB** (and sqlite / turso) implicit-commit every DDL statement, so THERE a plan that fails on op N leaves ops 1..N-1 committed. The `_voltro_migration_plans` row is still written only on full success — that row means "this schema is live". What the apply DOES write as it goes is a per-operation **resume ledger** (`_voltro_migration_ops`): every op is recorded before any DDL runs, flipped to `started` before its statement and `applied` after, and the rows are deleted once the apply converges. There is still no `--resume` / `--abort` flag because there is nothing to choose. Recovery is just to re-run the apply. It finds the ledger, logs `migration resume: found an interrupted run …`, reconciles any half-finished shadow-column swap or table rebuild, skips the ops that already took effect, and replays the rest — including a `.backfill()` that was only partly done, which a plain re-diff cannot express. Fix the cause of the failed op first (e.g. the `ER_DUP_KEYNAME` that stopped op N), then: ```sh voltro db apply --note 'completing partial apply after fixing op N' ``` If a deploy reverted the code that referenced the half-applied schema, the re-diff naturally reflects the new declared shape — no separate abort step is needed; the next `voltro db plan` already shows the correct remaining work. See [multi-dialect](./multi-dialect.md). ## `relation "..._uq" already exists` (42P07) on re-apply A composite (multi-column) `.unique([a, b])` constraint that ALREADY exists in the DB is now introspected (postgres reads it back from `pg_constraint`), so `db apply` matches it against the declared schema and emits nothing. On older builds it wasn't read back, so the planner re-emitted `ADD CONSTRAINT … UNIQUE` for the existing one → `42P07 relation "_uq" already exists`, and the plan never reached "up to date". If you see this, update the framework. (Single-column `.unique()` was never affected — it round-trips via the column's `unique` flag.) ## `db plan` keeps showing `CREATE INDEX` for indexes that already exist If `db plan` always lists `add-index` for `expressionIndex(...)` / `jsonIndex(...)` indexes that demonstrably exist in the DB — and you never see a matching `drop-index` — that's an introspection gap (now fixed). On postgres an expression key carries a `0` in `pg_index.indkey` (it has no backing column), and the old introspect query INNER-joined `pg_attribute` on the column → the whole index disappeared from the live snapshot. The declared index then had nothing to match → re-emitted every run, but never converged to "up to date". (`CREATE INDEX … IF NOT EXISTS` made each re-emit a silent no-op, so it wasn't data-destructive — just a plan that never went empty.) The fix introspects expression indexes (with a NULL column + an `expression` flag) and matches them by NAME + uniqueness, since the DB normalises the expression text (`(lower("email"))` → `lower(email)`) and it can't round-trip byte-for-byte. Plain-column indexes were never affected. If you see this, update the framework. **Second cause — the same table name in two schemas.** If the phantom `add-index` is for PLAIN-column indexes (often camelCase like `"
_tenantId_idx"`) and your DB has the SAME table names in more than one schema — classically a `public` legacy/migration copy alongside the app's own schema (e.g. `voltro`) — that was a separate introspection bug (now fixed). The table-list query joined `pg_class` by NAME, so a name present in both schemas fanned out to two rows → the table was listed twice → its columns and index-columns were accumulated twice in the BUILT snapshot (`["tenantId"]` became `["tenantId","tenantId"]`) → the planner diffed `["tenantId"] != ["tenantId","tenantId"]` and re-emitted forever. The raw `pg_*` catalog looks correct (the duplication is in introspect's built output, not the SQL) — to confirm it's THIS, call the inspect endpoint and look for doubled columns: `curl "$API/_voltro/inspect/migrations" | jq '.drift.liveSnapshot.tables[] | select(.name=="
") | .indexes'`. The fix scopes the table list to `current_schema()` by OID (+ defensive dedup), so each table is read once. Point `DB_SCHEMA` / the connection's `search_path` at your app schema and update the framework. ## `duplicate index name '' across tables '' and ''` Index names are unique **per schema**, not per table, in every dialect. If you gave the SAME explicit name to indexes on two different tables (`.index('byStatusStart', …)` on both `ab_tests` and `tournaments`), boot / `db plan` now fails loud with this error instead of silently creating only one and re-emitting the rest forever. Fix: rename the collisions to distinct, table-scoped names (`abTestsByStatusStart`, `tournamentsByStatusStart`). Auto-named indexes (`.index([col])` → `
__idx`) are table-prefixed and never collide — only hand-picked names can. (If you're updating from an older build that let these through, expect this error on first boot for every pre-existing collision — rename each one it names.) ## `auto-named index '
__idx' … exceeds the 63-byte … limit` The framework derives an FK auto-index name from the table + column name (`
__idx`). On a long junction table that can exceed 63 bytes — and the DB **silently truncates** index names (postgres → 63 bytes, dropping the `_idx` suffix), so the declared name (`…_idx`) never matches the live (truncated) one and `db plan` re-emits it forever. The explicit-index path was always length-validated; this closes the gap for the **auto** path — it now hard-fails at boot (same policy as every other identifier: no silent truncation). Two fixes, your choice: - Add an explicit short name for that FK column — `.index('', [''])` — which replaces the auto-index, OR - Shorten the table / column name. (Updating from an older build that truncated these? Expect the error on first boot for each one — apply one of the two fixes per index it names.) ## `db plan` re-emits `alter-column-default` for a `json().default({…})` column A `json()` column with an OBJECT default (`json().default({ a: 1 })`) had two problems on postgres (both now fixed): (1) the default was **silently dropped** — the DDL emitter only handled scalar defaults, so the column got no default at all (an omitted field inserted `NULL`, not the object); and (2) even once present, the comparison didn't match — postgres stores a jsonb default in canonical text (`'{"a": 1}'::jsonb`: spaces after `:`/`,` and keys reordered by length/bytes), which never equals the declared JS object's `JSON.stringify`, so `db plan` re-emitted `alter-column-default` every run. The fix emits object defaults as `''::jsonb` AND canonicalises both sides (key-sorted, space-free) before comparing. Update the framework. Cross-dialect: object literal defaults are now emitted to DDL on **every** dialect, in each one's json idiom — postgres `''::jsonb`, mysql/mariadb `(CONVERT('' USING utf8mb4))` (a literal default is rejected on a JSON column), mssql/sqlite a `''` string literal — and the comparison canonicalises each engine's introspected form (postgres reorders + spaces; mysql wraps in `cast(…)`; mssql wraps in `('…')`). Arrays are unaffected (an array default stays as-is — its `json[]` vs native `array()` column is ambiguous). If you need a per-insert dynamic value instead of a fixed literal, use a factory `.default(() => ({ … }))` (the store applies it at insert). > **`CAST(… AS JSON)` is MySQL-only, and shipping it for both cost a > part-applied migration.** MariaDB has no JSON *type* — the column is > `LONGTEXT` with `CHECK (json_valid(...))` — so `AS JSON` is not a cast target > it accepts, and `json().default([])` died mid-plan with > `ERROR 1064 … near 'JSON))'`. The obvious MariaDB spelling > `DEFAULT ('[]')` then fails on MySQL with > `ERROR 1101 BLOB, TEXT, GEOMETRY or JSON column can't have a default value`, > because a parenthesised literal is still a literal there. `CONVERT(… USING > utf8mb4)` is the one expression both accept; measured against MariaDB 11.8.8 > and MySQL 8.4.10, and covered by an integration test that boots both. > Reported against 0.30.2, fixed in 0.31.0. ## MySQL `db plan` / `db apply` crashes: `Cannot read properties of undefined (reading 'toLowerCase')` MySQL 8 returns `information_schema` result columns in UPPERCASE (`DATA_TYPE`, `COLUMN_NAME`, …) where MariaDB returns lowercase. The introspector read the lowercase fields, so on MySQL the type mapper got an `undefined` data type and the whole introspect (every `db plan` / `db apply`) crashed. Fixed — the introspector now lowercases each `information_schema` row's keys (no-op on MariaDB). If you hit this on MySQL, update the framework. (MariaDB was never affected, which is why it went unnoticed — the introspect tests run on MariaDB.) ## "no schema files found" ``` db plan: no schema files found hint: looked for *.entity.ts / *.schema.ts / schema.ts root: /home/me/myproject/apps/api ``` What happened: the discovery walker didn't find any schema files under the project root. Fix: - Check you're running the command from the right directory (`pwd`) - Check your entity files match the convention (`apps/api/database/*.entity.ts`) - Run from the project dir, or pass the path as a POSITIONAL arg (`voltro db plan ./apps/api`) — there is no `--root` flag; the CLI resolves the root from the first non-flag argument, defaulting to the current working directory ## "N reactive table(s) have NO change trigger in the database" (postgres) ```text auto-migrate: 500 reactive table(s) have NO change trigger in the database — ab_test_results, ab_test_variants, ab_tests, … (+492). Writes to them will not reach another instance's subscribers; a single instance is unaffected, which is why this stays invisible until you scale out. Run `voltro db apply` to install them. ``` On postgres, reactivity is carried by DDL: a per-table `framework_changes_
` trigger that `NOTIFY`s the CDC channel. The declared schema and the database can disagree about which tables have one. **Run `voltro db apply`.** It converges the triggers as its own step, and it does so **even when the schema diff is empty** — the usual case here, because a missing trigger is not a shape difference and `db plan` will correctly report `0 operations`: ```text $ voltro db apply schema diff: 0 operations, 0 blocked (schema is up to date) db apply: installing change triggers on 500 table(s) db apply: change triggers converged (1501 statement(s)) ``` `db apply --plan` converges them too, so the pre-deploy Job pattern needs no extra step. **Why a table ends up without one.** The trigger DDL is emitted by the full-schema path — a fresh database — so any table that arrived while your app was already running, or during a release that installed none, has no trigger. A restored dump can do it too (triggers travel with a full dump, but not with a schema-only or `--no-triggers` one), as can a hand-run `DROP TRIGGER` during an incident. **Why it stays invisible.** A single instance's own writes reach its own subscribers through the in-process path. The trigger is what carries a write to the *other* instances, so the symptom only appears when you scale out — subscriptions that quietly stop updating, with nothing in the logs. The mirror case is reported the same way: a `.nonReactive()` table that still carries a trigger keeps paying `REPLICA IDENTITY FULL` and a `NOTIFY` on every write for a subscription nobody receives. `db apply` removes both. **Boot converges them too, on both boot paths.** `voltro dev` and `voltro serve` run the same check-and-repair at startup, so a schema-only restore or a `CDC=0` → `CDC=1` flip no longer waits for someone to notice: ```text reactive triggers: converged at boot — installed 500, removed 0 (1501 statement(s)) ``` Three things about it are worth knowing before you deploy a fleet: - **It does not queue.** The repair takes the migration advisory lock with `pg_try_advisory_lock` and SKIPS if anything holds it, so N replicas booting together produce one repairing and N-1 logging `another instance … is converging it`. A concurrent `voltro db apply` holds the same lock, so the two can never run each other's DDL. The lock is scoped to your configured `DB_SCHEMA`, so `another instance` really means an instance of YOUR deployment — a second app sharing the database in a different schema takes a different key and neither defers the other. - **It never fails a boot.** A check that cannot run warns and the process continues; reactivity may be degraded, and that is still better than a diagnostic taking the app down. - **It is a tunable**, `reactiveTriggers` in `app.config.ts`, default `'repair'`: ```ts export default defineApiApp({ store: 'postgres', reactiveTriggers: 'report', // 'repair' (default) · 'report' · 'off' }) ``` `VOLTRO_REACTIVE_TRIGGERS` overrides the field. `VOLTRO_AUTO_MIGRATE=0` downgrades `'repair'` to `'report'` — that variable means "this boot issues no DDL", and it is deliberately not read as "and say nothing". ## When the fix hint doesn't match reality The fix hints come from the planner's classification logic — they should always be actionable. If you see one that doesn't make sense given your code: 1. Check git: were there uncommitted schema changes you forgot about? `git status` 2. Check the introspect output: `curl /_voltro/inspect/migrations | jq '.pending'` → see the raw plan 3. File an issue with the schema + the inspect JSON + the message Hint mismatches are bugs in the planner's classification — they're rare but always worth reporting because they're typically reproducible. ## Where to learn more - [Operation classes](./operation-classes.md) — the seven classes + per-class examples - [Backfill](./backfill.md) — SQL vs JS + the dry-run pattern - [Rename and drop](./rename-and-drop.md) — the `.renamedFrom()` + `dropped()` lifecycle - [Drift](./drift.md) — when the live DB diverged - [Multi-dialect strategy](./multi-dialect.md) — why MySQL + forward-roll - [Prod pipeline](./prod-pipeline.md) — the deploy-step apply pattern --- ## Adopting a table into a plugin's _voltro db adopt — move an app's grown table into a plugin-owned one, with a snapshot, a count verify and the drop last._ An app that did not start on a green field already has a table for half the plugins it installs. `voltro db adopt` is the way **into** the plugin's table, so using the plugin does not mean running a second source of truth beside it. ```bash voltro db adopt --from ai_flows --into _voltro_ai_flows --map ./ai-flows.map.ts voltro db adopt --from ai_flows --into _voltro_ai_flows --map ./ai-flows.map.ts --apply ``` **Dry run unless you pass `--apply`.** The interesting failure here is irreversible and the interesting output is the refusal, so nothing is written until you say so. A refused plan prints no steps at all, rather than a preview of something that will not happen. ## The map file is yours ```ts // ai-flows.map.ts export default { map: { name: 'name', mode: { expr: `CASE WHEN "allowDeviation" THEN 'agentic' ELSE 'deterministic' END` }, costMicroUsd: { expr: '"totalCostCents" * 10000' }, }, leaveUnset: ['id'], } ``` Read `target: source` — fill the plugin's column FROM this expression of mine, which is the direction the SQL runs. A string is a source column; `{ expr }` is raw SQL against the source row, for the unit conversions and merged fields no tool can infer. Those are domain knowledge, and a tool inventing them silently corrupts data. `leaveUnset` is how "the target's own default fills this" stops looking like "I forgot it". ## What it refuses - **a NOT NULL target column nobody maps to** — the alternative is a silent zero that reads as real data forever after; - **a target table that already holds rows** — adopt MOVES rows into a table, it does not merge into one somebody else already wrote; - **a typo on either side of the map.** A source column nobody carries across is reported but not fatal: dropping a dead column is deliberate often enough, and "I forgot this" and "I decided" look identical in a map file. ## The order, and why the drop is last 1. **snapshot** — `
__adopt_snapshot`, a real table in the same database, so restoring is a statement rather than an operational procedure at 2am. It keeps the columns the adopt left behind. 2. **copy** 3. **verify by count** — this catches the one failure that is otherwise invisible: a `WHERE` inside a raw expression silently dropping rows. 4. **drop the source** — last, and only if the counts match. On a mismatch **both tables stay** and the command says so. The snapshot is never removed after a failed verify — it exists for exactly the run that goes wrong. `--keep-source` copies and verifies without dropping at all. ## Ids, if the typeid prefixes differ The dry run says so before anything runs, because discovering it after the copy is discovering it too late: every row gets a new id, so every reference to the old table has to be rewritten — **including ids embedded in JSON columns**. Rewriting them is **not** automatic. Those ids live in your columns and inside your JSON, and only you know where. The translation table is what the command owes you; the rewrite is what you owe yourself. Doing it automatically is the one place here where being wrong would be silent. ## After the move Your rows are now in a table whose shape the framework evolves — and nothing special happens to them. They migrate exactly like every other row, through the same declarative differ. A narrowing can fail on your data, loudly, the same way it would on anybody's.