File-based migrations

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

apps/api/migrations/
├── 20260415_120000_split_address_out.ts
├── 20260520_093000_merge_emails.ts
└── 20260603_140000_normalize_orders.ts

Filename = <UTC-timestamp>_<slug>.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():

// 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.',
  phase:       'before-schema',

  up: async ({ raw, log }) => {
    const sql = raw('a table split the planner cannot plan: rows move between tables')
    // 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 ({ raw }) => {
    const sql = raw('the inverse of the split above')
    // 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 for MySQL forward-roll semantics). down runs the same way for rollback.

#What the runner provides

up: (ctx) => Effect.gen(function* () {
  ctx.raw        // the escape hatch: raw('why') → the @effect/sql SqlClient (sql.unsafe / sql`…` / sql.onDialectOrElse)
  ctx.store      // the app's DataStore over this database: codecs, query builder, updateMany, transactional
  ctx.dialect    // 'postgres' | 'mysql' | 'mariadb' | 'mssql' | 'sqlite' | 'turso'
  ctx.schema     // live-schema questions + dialect-aware schema operations
  ctx.each       // visit a table in pages keyed by id
  ctx.rewrite    // read, patch, write back under an optimistic guard, repeat until stable
  ctx.expect     // a post-condition the runner checks after up()
  ctx.skipUnless // a precondition; records the migration as skipped when it does not hold
  ctx.transactional // run a scope on one transaction
  ctx.log        // structured logger scoped to this migration (info / warn)
  ctx.appliedAt  // ISO-8601 — when this migration started (deterministic across the up/down pair)
})

A migration body is code, not SQL. Data goes through store, shape goes through schema, and the raw client sits behind raw('why') — the reason is required, voltro doctor lists every migration that uses it (migration-raw-sql), and a body written against it is bound to the dialect it was written on. Keep it for the one statement nothing below expresses, and let the reason say which. store is the same data store the application runs on, bound to the database the migration is running against. A row read through it has its JSON parsed and its timestamps as Dates on every dialect, and a row written through it is encoded the same way; the driver's own representation of a DATETIME or a LONGTEXT JSON column never reaches the migration.

up: ({ store }) => Effect.gen(function* () {
  const rows = yield* Effect.promise(() => store.query(database.vacations.descriptor))
  for (const row of rows) {
    yield* Effect.promise(() => store.update('vacations', row.id, { startDate: calendarDayUtc(row.startDate) }))
  }
}),

A read through a descriptor is checked against the CURRENT declaration at compile time: a column renamed after this migration was written fails the build, not the deploy. A read by table name goes to the database as it is, decoded through whatever the current declaration still knows about the columns; a column it no longer declares passes through as the driver returned it. Voltro keeps no per-migration copy of the schema the way a historical model would — a migration runs exactly once per database, against the database as it is at that moment, and ctx.schema is how a body asks what that is.

#Schema questions and operations

yield* ctx.schema.tableExists('vacations')          // boolean
yield* ctx.schema.columnExists('timers', 'roadmapId')
yield* ctx.schema.indexExists('timers', 'timers_roadmapId_idx')
yield* ctx.schema.foreignKeyExists('timers', 'roadmapId')

yield* ctx.schema.dropForeignKey('timers', 'roadmapId')   // 'applied' | 'already-absent'
yield* ctx.schema.dropIndex('timers', 'timers_roadmapId_idx')
yield* ctx.schema.dropColumn('timers', 'roadmapId')       // drops the FK and the indexes on it first
yield* ctx.schema.renameColumn('timers', 'year', 'startedYear') // 'already-present' when only the new name exists

yield* ctx.schema.createTable(table('archive', { id: id(), body: json() }))
yield* ctx.schema.addColumn('timers', 'startedYear', integer().nullable())
yield* ctx.schema.alterColumn('timers', 'startedYear', { nullable: false, default: 2026 })
yield* ctx.schema.addIndex('timers', ['startedYear'])
yield* ctx.schema.addForeignKey('timers', 'roadmapId', { table: 'roadmaps' }, { onDelete: 'setNull' })
yield* ctx.schema.renameTable('timers', 'roadmapTimers')
yield* ctx.schema.truncate('scratch')            // 'applied' | 'already-absent'; no per-row change event
yield* ctx.schema.dropTable('scratch')

// Or declare the table as it should be from here on and let the planner do it:
yield* ctx.schema.evolve(table('timers', { id: id(), roadmapId: reference(() => roadmaps).nullable(), startedYear: integer() }))

evolve is the Voltro-native form: the same planner that drives voltro db apply diffs the declaration against the live table and applies the difference — add, drop, rename, retype, index, constraint — with the same per-dialect statements, the same three-step backfills and the same convergence proof (an evolve whose re-plan is not empty fails). A drop needs no dropped() marker here: calling evolve from a migration is the explicit intent the marker stands for elsewhere. addColumn refuses a NOT NULL column without a default on a table with rows — add it nullable, fill it with rewrite, then alterColumn(…, { nullable: false }).

Every operation reads the live schema first and answers already-absent / already-present when there is nothing to do. That is the portable IF EXISTS: MySQL has no such clause for a foreign key, and its two "already gone" error codes are not what a migration should be matching on. The statement each one runs is the dialect's own — DROP FOREIGN KEY on the MySQL family, DROP CONSTRAINT elsewhere, sp_rename with its CHECK constraints carried across on SQL Server, a table rebuild on SQLite — and it is the same statement the planner's applier would emit for the same change.

#Paging and rewriting rows

const visit = yield* ctx.each('vacations', async (row) => { /* … */ }, { batchSize: 500 })

const report = yield* ctx.rewrite('vacations', (row) =>
  isCalendarDay(row.startDate) ? undefined : { startDate: calendarDayUtc(row.startDate) },
  { batchSize: 500 })
// report: { passes, scanned, rewritten, contended }

each visits every row in pages keyed by id. rewrite reads a page, computes a patch per row (undefined for a row already in shape), and writes each patch under an optimistic guard: the UPDATE carries WHERE id = ? AND <each patched column> = <the value just read>, so a row another process changed between the read and the write is not overwritten — it is counted as contended and read again on the next pass. The rewrite repeats until a pass rewrites nothing and contends on nothing, which is the state a migration needs while the previous deploy is still writing, without a hashing function a dialect may not have. A JSON column cannot be compared by value everywhere; guard a rewrite of one on a column that can ({ guard: ['updatedAt'] }).

Temporal guards keep the database's full precision. The callback still receives normal decoded values (including JavaScript Dates), but comparison values are captured alongside the row in the same query, before Date decoding can discard fractions of a millisecond. This covers both explicit guard columns and implicit guards on patched timestamp/date columns. A concurrent change within the same millisecond still counts as contention; timestamps are not rounded to make a guard match. Exhausting maxPasses does not by itself prove that another writer exists: check competing writes and whether the remaining guard values can round-trip exactly before increasing the limit.

Which rows they see. Both page through the store by table name, not through a declared descriptor — so a softDelete() table's automatic deletedAt IS NULL scope does not apply, and a soft-deleted row is visited and rewritten like any other. That is deliberate rather than an oversight: a migration repairs a shape, and a soft-deleted row carries the same broken shape. Restore it a month later and it would be the only row left holding the defect. The same holds for tenant scoping, and for the same reason. Both scopes are applied by the runtime's store middleware — the layer a request handler is given — not by the store itself. A migration is handed the store directly and never passes through that layer, so it sees the whole table: every tenant's rows, and the soft-deleted ones. Read ctx.store as "the app's store, bound to this database", not as "the store a handler sees".

That is the right default for a migration, and it is also the sharp edge: a rewrite over a multi-tenant table rewrites every tenant's rows. Scope it yourself when you mean one tenant — each/rewrite take no scope of their own.

What a patch looks like for a json() column. Return the value, not a string. The write goes through store.updateMany, so the column's codec encodes it; handing it a pre-serialised string double-encodes the column, and nothing fails at the time — it surfaces when something reads the row back.

A patch entry equal to what was read is dropped. rewrite compares every entry with Object.is against the value it read and writes only what differs. So a callback that mutates the row in place and returns it writes nothing, silently: the entries compare equal to themselves, the report counts the row as scanned and not as rewritten, and the pass ends "stable". Return a fresh object — a copy, or a newly built patch — never the row you were handed.

#Post-conditions and preconditions

up: (ctx) => Effect.gen(function* () {
  yield* ctx.skipUnless(ctx.schema.tableExists('vacations'), 'no vacations table on this database')
  // …
  ctx.expect('every vacation starts on a calendar day', () =>
    ctx.store.query(database.vacations.descriptor).then((rows) => rows.every((r) => isCalendarDay(r.startDate))))
}),

expect registers a check the runner evaluates after up returns. Every one must hold, or the migration fails and is not recorded, so the next run applies it again. It is the "read it back and check" every data migration ends with, as a contract the runner enforces. skipUnless stops the body when its condition is false and records the migration as applied with skipped: <reason> in its notes — it never runs again on this database. A migration that must not be recorded when its precondition fails should fail instead.

A skip is permanent, so the run says when it could never have been anything else. A before-schema body runs before the plan is computed. If it guards on a table the plan is about to CREATE, that guard is the only outcome it can reach on that run — and the skip has been recorded, so the body is never offered again. The runner measures which objects the body found absent and compares them against what the plan creates:

db apply: 20260922_090000_backfill_roadmap_issue_links skipped on the very
deploy that creates what it was waiting for, and will never run again
  created: roadmap_issue_links
  fix: declare the body phase: 'after-schema' so it runs once the plan has been
       applied, then re-issue it under a new migration id (the recorded one is spent)

It is a warning rather than a refusal: both facts only exist after the body has run and been recorded, and refusing there would leave the schema unapplied on top of a spent migration. The phase is the answer; this is the net under it.

#Testing a body without a database

A migration is code, so the data half of it is unit-testable: build the context over the in-memory store and leave sql out. each, rewrite (temporal guards included — the store holds the exact Date that was written, so there is no precision to lose), expect, skipUnless, store, log, the two read-only ctx.schema probes the store can answer about itself (tableExists, columnExists) and a Promise body under transactional run against the store; the dialect is the label the body reads through ctx.dialect.

import { Effect } from 'effect'
import { InMemoryDataStore } from '@voltro/runtime'
import { failedExpectations, makeFileMigrationContext } from '@voltro/database/sql'
import migration from '../migrations/20260915_054800_vacation_days'

const store = new InMemoryDataStore({
  vacations: [{ id: 'v1', startDate: new Date('2026-06-01T13:00:00Z'), updatedAt: new Date('2026-06-01T13:00:00Z') }],
})
const { ctx, expectations } = makeFileMigrationContext({
  store, dialect: 'postgres', migrationId: migration.id, appliedAt: new Date().toISOString(),
})
const result = migration.up(ctx)
await (Effect.isEffect(result) ? Effect.runPromise(result) : result)
expect(await Effect.runPromise(failedExpectations(expectations))).toEqual([])

So the guard a file migration usually opens with runs unchanged, in the generator form and the Promise form alike:

up: (ctx) => Effect.gen(function* () {
  yield* ctx.skipUnless(ctx.schema.tableExists('vacations'), 'no vacations table on this database')
  // …
}),

The store's table map is its schema. A table is in the store because it was seeded or written to, so new InMemoryDataStore({ vacations: [] }) says "the table exists and is empty" and a store without it says "no such table" — the two states a guard written for a database that predates the table has to tell apart. columnExists reads the keys the held rows carry: a column no row carries is absent, and a table holding no rows refuses rather than answering false, because an empty table is not evidence either way.

What genuinely needs a connection refuses with a typed error rather than running against nothing: raw(), an Effect body under transactional (it runs on the transaction's connection), and every ctx.schema member that can only be answered from a catalog — snapshot, indexExists, foreignKeyExists and every schema change. A store has no indexes and no constraints, and a fabricated false is worse than a refusal, because a migration branches on it. Each refusal names the operation, what is missing and what to do instead:

MigrationNeedsSqlClient: migration 20260915_054800_vacation_days: schema.indexExists() —
the in-memory store has no indexes — every read of it is a scan — so there is no honest answer
to give, and `false` would claim an index is absent from a store that could never have had one.
Run this migration against a database, or guard on `schema.tableExists` / `schema.columnExists`,
which the store can answer.

The requirement is in the type as well: FileMigrationContextInput is either a client plus its store, or a store that cannot run SQL — a SQL-backed store without its client does not compile, and a placeholder handed in as sql is refused at construction with the same error. A body that reaches a catalog operation is tested against a real dialect; the dialect parity harness is the place for that.

#A body that changes after it was applied

A migration is recorded by id and runs exactly once per database. The file it came from is not frozen — you can edit it afterwards and nothing re-runs, which means the file can quietly stop describing what actually ran. So the runner records a hash of the body it applied, and reports one that no longer matches:

file migration: the body on disk is not the one that was applied —
the file no longer describes this database  id=20260915_054800_vacation_days

voltro db status shows the same thing as (body changed since it was applied!). It warns rather than fails, on purpose: nothing can be re-run to fix it — the change is already in the database — and the edit is usually deliberate. What was missing is that anyone could tell. A migration applied before this shipped carries no recorded hash, and is reported as "cannot compare" rather than as changed.

An edit the framework made is not an edit of yours. The 0.78.0 upgrade inserted phase: 'before-schema' into every migration that had none — including ones that had already run — and the field is required, so the file cannot go back. The check recognises the body as it was before that insert, in every layout the upgrade writes, so those migrations do not warn. A change you make on top of it still does. The warning is said once per process, not once for each pass a boot runs.

#Transactions

export default migration({
  id: '20260915_054800_vacation_days',
  description: 'Store vacation dates as calendar days.',
  transactional: true,
  up: ({ rewrite }) => Effect.gen(function* () {
    yield* rewrite('vacations', (row) => /* … */)
  }),
  down: irreversible('the original time-of-day is not recoverable'),
})

A migration is transactional by default: the whole body and the ledger row that records it run in one transaction, on every dialect. A body that fails or crashes leaves nothing behind — not half its rows, and not a record for work that did not finish. transactional: false is the opt-out for a body that must run statement by statement. The decision is per migration, not per dialect, because what matters is DDL: MySQL and MariaDB commit implicitly on every ALTER, so a transactional migration there is data only. The runner measures that rather than trusting it — a body that committed its own transaction from inside is recorded (it will not run twice) and the run fails with the migration's name and the fix: transactional: false, or the schema change through ctx.schema in a migration of its own. ctx.transactional(({ store, raw }) => …) does the same for one scope inside an otherwise per-statement body; an Effect returned from it runs on the transaction's connection, so sql statements yielded inside are part of the unit.

The migration's own transaction is the small unit; the DEPLOY is the large one. voltro db apply treats every pending migration and the schema plan as all-or-nothing on every dialect — one transaction on Postgres and SQL Server, an undo on the others: when a later step fails, every migration this run applied is reversed through its down, newest first. That is why down is required and irreversible('…') is a declaration rather than an omission: an irreversible migration is the one thing a failed deploy leaves standing, and voltro db apply names it before the run starts. See the CLI page.

#Phases: before or after the declarative apply

Every migration declares WHERE it runs relative to the planner's diff. There is no default — the answer cannot be inferred from the body, and getting it wrong is silent:

export default migration({
  id:          '20260922_090000_backfill_roadmap_issue_links',
  description: 'Lift ticket keys into the new roadmapIssueLinks table.',
  phase:       'after-schema',
  transactional: false,
  up: (ctx) => Effect.gen(function* () {
    yield* ctx.rewrite('roadmapIssueLinks', (row) => /* … */)
  }),
  down: irreversible('the lifted keys have no column to go back to'),
})

phase	runs	for
'before-schema'	first, BEFORE the plan is computed	a shape change the planner must not compute against: a table split, a merge, a foreign key dropped so the planner does not trip over it, a type change with a USING expression
'after-schema'	last, AFTER the plan has been applied and the plugin steps and change triggers have converged	a body that NEEDS the new shape: a back-fill into a table this deploy creates, a column filled from its neighbours

Both phases run in the SAME job — voltro db deploy (the production step), voltro migrate, voltro db apply, and the voltro dev auto-migrate — under the same failure gate, and on a dialect that holds a deploy in one transaction inside the same unit. voltro db files has no apply of its own, so it runs the after-schema phase only once the schema plan is empty (below). That is the point of the second phase rather than a boot hook: the job has the time budget for a back-fill over millions of rows and an exit code somebody reads, where a boot hook runs on every replica on every start.

A boot hook is still the right home for a REPAIR that must survive a database copy — it is not a one-shot step and should not be recorded as one.

voltro db apply --plan refuses on a pending before-schema migration, because that body changes the shape the reviewed plan was computed against. It does NOT refuse on a pending after-schema one: that body runs once the plan has landed, so it cannot invalidate it, and the run applies it.

voltro db status names the phase of every pending and applied file:

  ✓ 20260415_120000_split_address_out  [applied]  [before-schema]
  · 20260922_090000_backfill_roadmap_issue_links  [pending]  [after-schema]

#Ordering vs the planner

before-schema 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 — the before-schema files run first (writing the new state), the planner runs second (a clean diff against the new declared schema, no-op), and the after-schema files run last

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 <id> invokes a file-based migration's down body. The <id> is the positional migration id (the basename minus .ts):

voltro db rollback-file 20260415_120000_split_address_out

Run from the app directory, or select it explicitly with --root ./apps/api. The migration ID is never a project path. Both --root path and --root=path work before or after the ID; the explicit root also determines which app configuration supplies the database dialect.

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 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.

A migration that repairs a broken shape has no inverse — its inverse would be the defect. Say so:

import { irreversible } from '@voltro/database'

down: irreversible('the original time-of-day is not recoverable'),

voltro db rollback-file then refuses with that reason before it runs anything, and the ledger row stays. A down that only logs "nothing to undo" and returns would have let the rollback delete the record of a change that is still in the database.

#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 of EITHER phase has never run against that database, and names the pending ids:

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 deploy .` runs the
  before-schema files, the schema plan and the after-schema files in one step.

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.

Where several processes DO apply them at once — voltro dev replicas with auto-migrate on, or two migrate jobs against one database — they serialise on the migration lock, and each re-reads what is still pending once it holds it. A migration's body therefore runs once, and a process that waited logs the ids another process applied meanwhile instead of running them again.

#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:

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:

# 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 <root>/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:

voltro db files

It runs the before-schema files, then the after-schema files only if the schema plan is empty. There is no declarative apply here, and an after-schema body reads the shape that apply produces — a back-fill into a table this deploy creates would meet a table that does not exist yet. So while a plan is pending, the after-schema files are reported as waiting and the command exits 0; voltro db apply --plan or voltro db deploy runs them once the plan has landed:

db files: 1 after-schema migration(s) wait for the schema plan (1 pending operation(s))
  — `voltro db apply --plan` or `voltro db deploy` runs them once it has landed
  waiting: 20260922_090000_backfill_roadmap_issue_links
db files: applied 1 migration(s)
  20260415_120000_split_address_out [before-schema] (482ms)

--phase before-schema or --phase after-schema runs one pass. Asking for the after-schema pass while a plan is pending is refused (exit 1), not attempted.

It's a distinct command from voltro db apply (which runs the planner-based auto-diff, with both file phases around it) and from voltro db migrate (which runs defineMigration *.migration.ts steps, and exits 1 over pending files of this kind rather than reporting nothing to do). 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.

The prod pipeline page covers the deploy-step apply flow for planner-based changes.

#Programmatic runner preparation

The low-level runFileBasedMigrations(sql, context) and rollbackFileBasedMigration(sql, context) functions from @voltro/database/sql accept an optional context.beforeExecute Effect. Use it to prepare migration-owned resources before a body can change the schema. It is a runner option, not a field on migration().

For a forward pass, the Effect runs once under the migration lock after the applied ledger has been rechecked, and only if the selected phase still has pending bodies. For rollback, it runs after the ledger, file and reversibility checks, including the second ledger check under the lock. Empty passes, already-applied passes and refused rollbacks do not run it. A failure prevents body execution and leaves migration receipts unchanged.

Preparation is outside each forward body's own transaction: its side effects are not automatically undone if a later body fails. An enclosing caller-owned transaction can still roll back its SQL effects. A body's skipUnless guard also runs after preparation. This hook does not add cross-process SQLite coordination; CLI migration jobs supply their outer coordinator separately. Serving processes still do not execute migrations.

#Reviewed SQLite decimal conversion

The server-only @voltro/database/sql entry point exposes reviewed, offline conversion operations for SQLite, native Turso and libSQL. These are explicit physical-schema migration tools. Ordinary decimal() and numeric() declarations now select exact encoded TEXT storage on these backends. Existing NUMERIC columns are not automatically converted: application startup refuses their physical representation, even without automatic schema migration. The following reviewed conversion is required before those applications restart. A NUMERIC source may already have lost digits; no operation can infer the original exact value from a rounded observation.

Stop every application reader and writer, including other replicas, for capture and cutover. The migration lock coordinates migration runners; it does not fence ordinary application traffic. Use an explicitly nontransactional before-schema file through voltro db files --phase before-schema. Do not wrap publication in ctx.transactional, and do not weaken db apply's enclosing transaction to make it fit this operation.

1. Capture the source schema with readSqliteTableSchema and observations with forEachSqliteDecimalSourcePage on a reserved SQL connection. readSqliteDecimalSource is the collecting alternative for bounded datasets. Capture alone performs no conversion.
2. Persist reviewed SqliteDecimalReviewedCell decisions. NUMERIC observations require an explicit resolution; preserve their original observation and never convert them through a JavaScript number. Provide a repeatable iterable ordered by sqliteDecimalDecisionIdentity(cell) using lexical </> ordering, with no duplicate identities. A one-shot generator is unsuitable because verification reads the decisions again.
3. Build a SqliteRetainedSourceRequest from the stable migration id, source table, target identity, captured schema and reviewed keys/columns/cells. Supply the intended TableSnapshot and replacement triggers. prepareSqliteDecimalStage validates and stages without publishing; publishSqliteDecimalStage also performs preparation when needed. Changed observations or schema refuse publication rather than applying stale decisions.
4. Publication records the replacement and its receipt together. Retrying after a crash verifies that receipt rather than overwriting subsequent live writes. rollbackSqliteDecimalPublication verifies whether the retained source can be restored safely; it refuses incompatible live changes. cleanupSqliteDecimalPublication removes owned retained artifacts only after their integrity checks; do not treat cleanup as an unconditional delete.

The following helper creates a migration from already reviewed artifacts. In the actual timestamped migration file, default-export its result; the request's migration id must match that filename. Keep the reviewed inputs available for retry and rollback.

import {
  migration,
  publishSqliteDecimalStage,
  rollbackSqliteDecimalPublication,
  type SqliteRetainedSourceRequest,
  type SqliteDecimalTargetTrigger,
  type TableSnapshot,
} from '@voltro/database/sql'

export const reviewedDecimalMigration = (
  request: SqliteRetainedSourceRequest,
  target: TableSnapshot,
  triggers: ReadonlyArray<SqliteDecimalTargetTrigger> = [],
) => migration({
  id: request.migrationId,
  description: 'Publish reviewed exact decimal storage',
  phase: 'before-schema',
  transactional: false,
  async up(ctx) {
    await ctx.store.run(publishSqliteDecimalStage(
      ctx.raw('Publish reviewed decimal storage while application traffic is stopped'),
      request, target, triggers,
    ))
  },
  async down(ctx) {
    await ctx.store.run(rollbackSqliteDecimalPublication(
      ctx.raw('Restore the retained decimal source after verifying rollback safety'),
      request, target, triggers,
    ))
  },
})

The physical target's decimal columns carry sqliteDecimalEncoding: 'sortable-v1', precision and scale. Storage snapshots carry physical default literals; materialised declarations recover their logical decimal defaults. These low-level tools do not by themselves establish that an application's current schema is ready to read the converted table. Verify the matching declaration and normal application startup before allowing traffic again.