Database Migrations

OpenInsure runs five active databases across three hosting providers. Each has its own migration strategy, CLI, and deploy cadence. The former SpiceDB datastore is legacy authz rollback state and is not part of the normal migration path.

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 ········· 190 tables across 5 keyspaces (vertical split 03-27)  │
│               oi_global · oi_policies · oi_submissions ·            │
│               oi_claims · oi_billing                                 │
│               Drizzle ORM · Branches: main + ci-test + transient    │
│  spicedb ···· Legacy SpiceDB datastore · Branch: main              │
└─────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────┐
│ FLY.IO (iad)                                                        │
│  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 (190 tables across 5 keyspaces)
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-04-06)

All databases are fully migrated and in sync.

Database	Status	Detail
PlanetScale ai	main (latest DR #15: crm-integrations)	190 tables across 5 keyspaces
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            # Active 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 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/<table>.ts (and add to domain/<keyspace>.ts barrel if a new table)
2. make db-generate — drizzle-kit creates a new SQL file in packages/db/migrations/mysql/ (named <NNNN>_<slug>.sql)
3. Inspect SQL — confirm idempotent, no FK constraints, --> statement-breakpoint between statements
4. make vitess-up && make db-migrate — apply locally to confirm
5. Open feature PR; CI creates a PS dev branch and applies the migration
6. pscale deploy-request create ai --from <branch> --into main --notes "..." --auto-delete-branch
7. Review the DR diff in the dashboard → approve
8. pscale deploy-request deploy ai NN — cuts over (30-minute revert window)
9. Branch is auto-deleted once the DR completes

For the full reference (concepts, deploy-request lifecycle states, instant DDL caveats, ghost tables, the 30-minute revert window, cost cap, incident response), see the internal docs/PSCALE_MANUAL.md.

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

Legacy SpiceDB

Do not run SpiceDB datastore migrations during normal development or deploys. Production API authorization uses AUTHZ_BACKEND=can; the pushdown/spicedb database is retained only for rollback/decommission work tracked in docs/operations/2026-05-17-spicedb-retirement/.

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):
  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 the DOKS pod: make tb-health (or kubectl exec -n openinsure tigerbeetle-0 -c tigerbeetle -- /tigerbeetle version)

Database Schema

Table definitions and ORM patterns.

Billing (TigerBeetle)

Fiduciary split flow and ledger accounts.

CI/CD

How migrations run in Woodpecker CI.