Database Schema

OpenInsure uses PlanetScale Vitess 22.0 as its primary database, accessed from Cloudflare Workers via 6 Hyperdrive bindings for connection pooling and read-query caching at the edge. The ORM is Drizzle 0.45+ with a schema-first approach — all tables are defined in TypeScript and migrations are generated by the Drizzle CLI.

The database is split into 5 domain keyspaces (vertical split, cutover 2026-03-27): oi_global, oi_policies, oi_submissions, oi_claims, oi_billing. Each keyspace has its own connection pool (Hyperdrive binding) and its own deploy-request lifecycle.

Note

Internal operational reference. This page is the public summary. The full reference (deploy-request workflow, runbooks, CLI, REST API, limits, gotchas) lives in the internal docs/PSCALE_MANUAL.md and docs/HYPERDRIVE_MANUAL.md.

Package Layout

packages/db/
├── src/
│   ├── schema/                       # Drizzle table definitions
│   │   ├── policies.ts               # one file per table family
│   │   ├── claims.ts
│   │   ├── ...
│   │   └── domain/
│   │       ├── global.ts             # oi_global keyspace barrel
│   │       ├── policies.ts           # oi_policies keyspace barrel
│   │       ├── submissions.ts        # oi_submissions keyspace barrel
│   │       ├── claims.ts             # oi_claims keyspace barrel
│   │       └── billing.ts            # oi_billing keyspace barrel
│   ├── domain-clients.ts             # createGlobalDb / createPoliciesDb / etc.
│   └── index.ts                      # Legacy createDb() (monolithic)
├── migrations/
│   ├── mysql/                        # PRIMARY: drizzle-kit-generated SQL files
│   │   └── meta/_journal.json        # Migration tracking
│   └── baseline/                     # Full schema snapshot for fresh databases
├── drizzle.config.ts
├── INFRASTRUCTURE.md                 # Internal: topology + connection paths
└── SECURITY.md                       # Internal: credential management

Database Connection (in Workers)

Each domain has its own Hyperdrive binding. The factory functions in domain-clients.ts return a typed Drizzle instance scoped to that keyspace.

import {
  createGlobalDb,
  createPoliciesDb,
  createClaimsDb,
  createSubmissionsDb,
  createBillingDb,
} from '@openinsure/db';

// In a Hono handler (apps/api/src/routes/...):
const policiesDb = createPoliciesDb(env.HYPERDRIVE_POLICIES);
const policies = await policiesDb
  .select()
  .from(schema.policies)
  .where(eq(schema.policies.orgId, orgId)); // orgId scoping is mandatory

The middleware in apps/api/src/middleware/database.ts injects db, globalDb, policiesDb, submissionsDb, claimsDb, billingDb, and orgId onto every request context. Routes pull what they need from there.

Multi-Tenant Isolation (orgId Scoping)

PlanetScale Vitess does not support row-level security. Tenant isolation is enforced at the application layer: every multi-tenant query MUST include eq(table.orgId, orgId).

// Correct
const polices = await policiesDb
  .select()
  .from(schema.policies)
  .where(eq(schema.policies.orgId, orgId));

// Incorrect — would return all tenants' data
const policies = await policiesDb.select().from(schema.policies);

The orgId value comes from the JWT, never from user input. Cross-org roles (superadmin, system, auditor) may pass ?orgId=<uuid> as a query parameter; normal users cannot override their JWT orgId. The middleware enforces this distinction.

Danger

Forgetting orgId in a WHERE clause is a security incident. A lint rule and integration tests guard against it; see the internal PLAYBOOK.md for the review checklist.

The 5 Keyspaces

The vertical-split cutover completed 2026-03-27. VTGate routes queries to the correct keyspace via MoveTables routing rules established during the cutover. The legacy monolithic HYPERDRIVE binding still works but new code should use the domain-specific bindings.

Keyspace	Tables	Domain	Schema barrel
oi_global	53	Orgs, users, producers, rules, programs, reference data	packages/db/src/schema/domain/global.ts
oi_policies	28	Policies, endorsements, coverages, drivers, vehicles	packages/db/src/schema/domain/policies.ts
oi_submissions	33	Submissions, quotes, motor carriers, FMCSA, MVR, loss runs	packages/db/src/schema/domain/submissions.ts
oi_claims	29	Claims, claimants, disbursements, settlement, subrogation	packages/db/src/schema/domain/claims.ts
oi_billing	47	Billing, payments, GL, chart of accounts, reinsurance	packages/db/src/schema/domain/billing.ts

Total: 190 tables as of 2026-04-06. Each keyspace is currently unsharded (NUM_SHARDS=1). The future sharding plan (when needed) is org_id via an xxhash vindex on the four tenant-scoped keyspaces. oi_global stays unsharded forever — its reference tables (organizations, programs, producers, platform_carriers) must be co-located with every shard.

Hard Rules (Don’t Violate)

1. Every multi-tenant query must include eq(table.orgId, orgId) — no RLS in MySQL
2. No foreign key constraints — PS recommendation; we replace FKs with app-level deletion order, indexes on FK-equivalent columns, and batch cleanup jobs
3. No cross-keyspace joins — VTGate cannot push them; compose in app code
4. All DDL on main goes through deploy requests — safe migrations are enabled and direct DDL is rejected
5. Migrations are idempotent — Vitess does not support IF NOT EXISTS on ALTER TABLE
6. Each migration file’s statements run individually — there is no transactional DDL on Vitess; if statement N fails, statements 1..N-1 are NOT rolled back
7. Never delete or modify an applied migration — the hash in __drizzle_migrations will mismatch and block all future migrations
8. 20-second transaction timeout is hard — do not wrap long-running batch jobs in a single txn

Branches

Branch	Purpose
main	Production. Safe migrations enabled — direct DDL is rejected; all changes via deploy requests
ci-test	Long-lived shared dev branch. Direct DDL allowed.
<feature-*>	Transient per-PR branches. Created from main, used to author one migration, deleted post-DR.

Always pass --auto-delete-branch when creating a deploy request — it removes the source branch automatically when the DR completes, which keeps the dev-branch hour count under the monthly cap.

Migration Workflow

1. Edit a schema file in packages/db/src/schema/<table>.ts
2. (If a brand-new table) import it into the appropriate domain barrel: packages/db/src/schema/domain/<keyspace>.ts
3. make db-generate — drizzle-kit creates a new SQL file in packages/db/migrations/mysql/ (named <NNNN>_<slug>.sql)
4. Inspect the generated SQL — confirm no PG types, no FK constraints, --> statement-breakpoint between statements
5. make vitess-up && make db-migrate — apply locally to confirm
6. Open a feature PR; CI creates a PlanetScale dev branch and applies the migration there
7. Open a deploy request: pscale deploy-request create ai --from <branch> --into main --notes "..." --auto-delete-branch
8. Review the DR diff in the PlanetScale dashboard → approve
9. Deploy: pscale deploy-request deploy ai NN
10. 30-minute revert window — pscale dr revert ai NN if needed
11. The dev branch is auto-deleted once the DR completes

Drivers & Connection Pooling

Two drivers, one Drizzle dialect (mode: 'planetscale'):

Environment	Driver	Why
Workers	@planetscale/database (HTTP fetch)	No raw TCP from a Worker
Local dev	mysql2	Talks to docker-compose Vitess testserver
Tests	mysql2	Same as local dev

Selection happens automatically in packages/db/src/domain-clients.ts based on the connection string format. Workers see a Hyperdrive binding object exposing connectionString, host, user, password, database — never raw PlanetScale credentials.

For the full Hyperdrive story (the 6 bindings, query caching behavior, smart placement, troubleshooting), see the Hyperdrive page.

Local Development

make vitess-up           # docker-compose vitess testserver — 5 keyspaces, port 13306
make vitess-shell KS=oi_global   # mycli into a specific keyspace
make vitess-status       # table counts per keyspace
make vitess-reset        # wipe + re-init after schema drift

The local Vitess testserver (vitess/vttestserver:v21.0.2-mysql80) mirrors the production 5-keyspace topology exactly. Docker compose definition lives in docker-compose.yml.

Database Migrations

Deploy-request workflow, branch lifecycle, and the migration TUI.

Hyperdrive

The 6 bindings, query caching, smart placement, and credential lifecycle.

Packages Overview

All @openinsure/* packages, exports, and consumers.