Skip to main content

Documentation Index

Fetch the complete documentation index at: https://handbook.mhchq.ai/llms.txt

Use this file to discover all available pages before exploring further.

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 ········· 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 ···· 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 (190 tables across 5 keyspaces)
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-04-06)

All databases are fully migrated and in sync.
DatabaseStatusDetail
PlanetScale aimain (latest DR #15: crm-integrations)190 tables across 5 keyspaces
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/<table>.ts (and add to domain/<keyspace>.ts barrel if a new table)
  2. make db-generate — drizzle-kit creates packages/db/migrations/mysql/00NN_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

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