Database Migrations

OpenInsure runs six databases across three hosting providers. Each has its own migration strategy, CLI, and deploy cadence.

Topology

┌─────────────────────────────────────────────────────────────────────┐
│ CLOUDFLARE EDGE                                                     │
│                                                                     │
│  oi-sys-api ──Hyperdrive──▶ PlanetScale (ai)                       │
│  oi-sys-auth ──D1──▶ oi-auth                                       │
│  oi-sys-uw ──D1──▶ oi-submissions                                  │
│  KV (cache, sessions, config)  ·  R2 (documents, assets)           │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────────────┐
│ PLANETSCALE (pushdown org)                                          │
│  ai ········· 171 tables · Drizzle ORM · Branches: main, ci-test   │
│  spicedb ···· SpiceDB datastore · Branch: main                     │
└─────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────┐
│ FLY.IO (iad)                                                        │
│  openinsure-spicedb ······· SpiceDB serve (reads PlanetScale)       │
│  openinsure-documenso-db ·· Fly Postgres (Prisma, auto-migrate)     │
│  openinsure-tigerbeetle ··· TigerBeetle ledger (compile-time)       │
└─────────────────────────────────────────────────────────────────────┘

Database Inventory

Database	Engine	CLI	Migration Tool	Purpose
pushdown/ai	Vitess 22.0	pscale	Drizzle ORM	Primary transactional (171 tables)
pushdown/spicedb	Vitess 22.0	pscale	SpiceDB binary	Zanzibar authorization
oi-submissions	D1 (SQLite)	wrangler	D1 migrations	Aspire UW form data
oi-auth	D1 (SQLite)	wrangler	D1 migrations	Identity store
documenso-db	Postgres	flyctl	Prisma	E-signature documents
TigerBeetle	TigerBeetle	flyctl	Compile-time	Double-entry ledger

Current State (2026-03-22)

All databases are fully migrated and in sync.

Database	Status	Detail
PlanetScale ai	main (DR #7)	171 tables
PlanetScale spicedb	main	At head
D1 oi-submissions	Production	15 migrations
D1 oi-auth	Production	2 migrations
Fly documenso-db	Production	Prisma auto
TigerBeetle	Running	Compile-time

tip

Run make db-status or pnpm cli db status to verify at any time.

CLI Commands

openinsure db status            # All 6 databases in one view (~2s)
openinsure db migrate           # Run all pending migrations (parallel)
openinsure db migrate --ps      # PlanetScale only
openinsure db migrate --d1      # D1 only (submissions + auth)
openinsure db migrate --spicedb # SpiceDB only
openinsure db deploy            # Interactive PlanetScale deploy request

make db-status                  # Delegates to CLI
make db-migrate                 # Delegates to CLI
make db-migrate-ps-deploy       # Delegates to CLI

Cron Schedule (9 triggers)

Cron	UTC	Job
0 3 * * *	03:00	Earned premium batch (GL + TigerBeetle)
0 4 * * 0	04:00 Sun	D1 retention cleanup
0 5 * * *	05:00	Infrastructure health rollup
0 6 * * *	06:00	Portfolio sweep
0 7 * * *	07:00	Ledger reconciliation (TigerBeetle vs GL)
0 8 * * *	08:00	Payment reminders
0 9 * * *	09:00	Renewal notices
0 18 * * 1-5	18:00 weekdays	FL DHSMV export
*/15 * * * *	every 15m	Mailbox ingest

TigerBeetle Integration

TigerBeetle is the authoritative double-entry ledger for all financial transactions.

9 account types per org: CARRIER_PAYABLE, MGA_FIDUCIARY, MGA_REVENUE, PRODUCER_PAYABLE, TAX_AUTHORITY_PAYABLE, LOSS_FUND, CLAIMS_PAID, RESERVES, REINSURER_PAYABLE.

Three active data flows:

1. Earned premium (03:00 UTC cron) — posts LOSS_FUND to CARRIER_PAYABLE transfer after GL journal entries
2. Bordereau settlement — SettlementService moves funds from payable accounts to external payout accounts
3. Ledger reconciliation (07:00 UTC cron) — compares TigerBeetle balances against PlanetScale GL per org

Graceful degradation: if TigerBeetle is down, GL posting succeeds and reconciliation catches drift.

Migration Runbooks

PlanetScale (ai)

1. Edit schema in packages/db/src/schema/ 2. Push: make db-migrate-ps (opens tunnel to ci-test) 3. Verify: pscale branch diff ai ci-test 4. Deploy: pnpm cli db deploy (interactive: create, approve, deploy DR) 5. Regenerate types: make db-generate

D1 (oi-submissions, oi-auth)

1. Add .sql file to apps/underwriting-workbench/migrations/ or apps/auth/migrations/ 2. Test locally: make db-migrate-d1-local 3. Apply remote: make db-migrate-d1

SpiceDB

Auto-migrates on container restart (spicedb datastore migrate head). Manual: make db-migrate-spicedb.

Ownership Boundary

YOU manage (Drizzle / Wrangler):
  pushdown/ai ········ Drizzle schema + migrations
  oi-submissions ····· SQL migration files
  oi-auth ············ SQL migration files

SELF-MANAGED (by application binary):
  pushdown/spicedb ··· spicedb datastore migrate head
  documenso-db ······· Prisma migrate (on deploy)
  TigerBeetle ········ Compile-time schema (no DDL)

Troubleshooting

D1 "duplicate column name" — Column exists but migration wasn't recorded. Mark as applied:

npx wrangler d1 execute <DB> --remote --config <CONFIG> \
  --command "INSERT INTO d1_migrations (name) VALUES ('<FILE>')"

PlanetScale "BLOB/TEXT in key" — Change text() to varchar('col', { length: 255 }) in Drizzle schema.

PlanetScale "column cannot be null" — Existing rows have NULLs. Make column nullable or add default.

TigerBeetle health failing — Check proxy: curl https://openinsure-tigerbeetle.fly.dev/health

Database Schema

Table definitions and ORM patterns.

Billing (TigerBeetle)

Fiduciary split flow and ledger accounts.

CI/CD

How migrations run in CircleCI.