Schema Migrations

SQL files in migrations/, applied by scripts/migrate.ts via bun run migrate.

Run

bun run migrate

Idempotent — running twice in a row is a no-op the second time.

Tracking table

schema_migrations (version 000). Source: migrations/000_schema_migrations.sql.

CREATE TABLE IF NOT EXISTS schema_migrations (
  version    TEXT PRIMARY KEY,
  name       TEXT,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

name is nullable so legacy migrations that insert without supplying a name still work (e.g. 006_domain_registry.sql).

How the runner works

File: scripts/migrate.ts.

1. ensureTrackingTable() — bootstrap schema_migrations if missing.
2. Read all *.sql from migrations/, sort by filename.
3. Compare against SELECT version FROM schema_migrations.
4. For each unapplied file: stream into psql -v ON_ERROR_STOP=1 -f <file>.
5. Insert row into schema_migrations on success.

Why psql, not Bun.sql

// scripts/migrate.ts:114-117
// psql honours embedded BEGIN/COMMIT and CREATE INDEX CONCURRENTLY without
// wrapping in an implicit transaction (unlike Bun.sql.unsafe's extended-protocol path).

Bun.sql is still used for the schema_migrations bookkeeping itself.

Recent fixes

• 007 collision resolved — duplicate version 007 was renamed to 009.
• CREATE INDEX CONCURRENTLY — was previously wrapped in BEGIN/COMMIT, which Postgres rejects. Fixed by streaming bodies through psql.

Mark-only mode

bun run migrate --mark-applied <version>

Records a row in schema_migrations without executing DDL. Used to onboard a database that already has the schema.

See also

Database Schema, Local Development.