Skip to main content
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

DatabaseEngineCLIMigration ToolPurpose
pushdown/aiVitess 22.0pscaleDrizzle ORMPrimary transactional (171 tables)
pushdown/spicedbVitess 22.0pscaleSpiceDB binaryZanzibar authorization
oi-submissionsD1 (SQLite)wranglerD1 migrationsAspire UW form data
oi-authD1 (SQLite)wranglerD1 migrationsIdentity store
documenso-dbPostgresflyctlPrismaE-signature documents
TigerBeetleTigerBeetleflyctlCompile-timeDouble-entry ledger

​
Current State (2026-03-22)

All databases are fully migrated and in sync.
DatabaseStatusDetail
PlanetScale aimain (DR #7)171 tables
PlanetScale spicedbmainAt head
D1 oi-submissionsProduction15 migrations
D1 oi-authProduction2 migrations
Fly documenso-dbProductionPrisma auto
TigerBeetleRunningCompile-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)

CronUTCJob
0 3 * * *03:00Earned premium batch (GL + TigerBeetle)
0 4 * * 004:00 SunD1 retention cleanup
0 5 * * *05:00Infrastructure health rollup
0 6 * * *06:00Portfolio sweep
0 7 * * *07:00Ledger reconciliation (TigerBeetle vs GL)
0 8 * * *08:00Payment reminders
0 9 * * *09:00Renewal notices
0 18 * * 1-518:00 weekdaysFL DHSMV export
*/15 * * * *every 15mMailbox 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.