Database Schema

OpenInsure uses PlanetScale (Vitess 22.0) as its primary database, accessed from Cloudflare Workers via Hyperdrive connection pooling. 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.

Package

Database schema, migrations, and query utilities live in packages/db:

packages/db/
├── src/
│   └── supabase/
│       └── schema/           # Table definitions (Drizzle)
│           ├── index.ts      # Central re-export
│           ├── submissions.ts
│           ├── policies.ts
│           └── ...
├── migrations/
│   └── postgres/             # SQL migration files (0000–0081+)
│       ├── 0000_kind_famine.sql
│       ├── ...
│       └── meta/             # Drizzle migration journal
├── seeds/
│   └── demo-data.sql         # Demo seed data
└── drizzle.config.ts         # Drizzle CLI config

Database Connection

import { createDb } from '@openinsure/db';

// In a Cloudflare Worker handler:
const db = createDb(env.HYPERDRIVE);
// env.HYPERDRIVE is the Cloudflare Hyperdrive binding
// configured in wrangler.toml under [[hyperdrive]]

Hyperdrive pools TCP connections to PlanetScale, giving Workers the connection efficiency of a traditional server. Max 5 connections per client; Hyperdrive handles pooling across Worker instances.

Multi-Tenant Isolation (orgId Scoping)

All queries run inside a transaction that applies the orgId filter, ensuring every SELECT, INSERT, UPDATE, and DELETE is scoped to the authenticated user’s organization.

await db.transaction(async (tx) => {
  await setOrgContext(tx, orgId); // MUST be called inside transaction
  const policies = await tx.select().from(schema.policies);
  // Only returns policies for this org
});

Schema — Table Groups

The schema is organized into domain groups. All 78+ tables are in packages/db/src/supabase/schema/.

Core Platform

Table	Description
`organizations`	Tenant organizations
`users`	Authenticated users (synced from `oi-sys-auth` D1)
`api_keys`	API key credentials with role and org scoping
`activities`	Activity diary — all user actions
`notifications`	Notification queue (in-app, email, SMS)
`webhooks`	Webhook event delivery log
`saved_views`	User-defined data view configurations
`chat_history`	AI agent conversation transcripts

Producers & Programs

Table	Description
`producers`	Producer/agency profiles and license status
`programs`	Underwriting program definitions
`delegated_authority`	DA agreement terms and authority limits

Submissions & Underwriting

Table	Description
`submissions`	Submission lifecycle (new → quoted → bound → declined)
`submission_conditions`	Conditions attached to a submission
`submission_notes`	Notes with visibility control (internal/external)
`submission_events`	Activity timeline per submission
`submission_documents`	Document attachments to submissions
`quote_options`	Quote variants (coverage options, pricing tiers)
`rules`	Underwriting rules configuration
`underwriting_rules`	Rules engine evaluation results
`approvals`	Approval queue items with audit trail

Aspire Application Detail (Commercial Auto)

These tables store the detailed application data entered through the Aspire form tab system. Each table has an application_id FK to submissions.

Table	Description
`application_insured`	Insured company info (name, DBA, tax ID, contact details)
`application_contacts`	Contact records (corporate, inspection) with name, email, phone, cell
`application_addresses`	Mailing and physical addresses
`application_coverage`	Coverage config (CSL limit, deductibles, hired auto, waivers)
`application_operations`	Operations (max radius, mileage distribution %, Canada/Mexico)
`application_vehicles`	Vehicle schedule (VIN, year, make, model, body type, ownership, ACV)
`application_vehicle_coverages`	Per-vehicle comp/collision limits and deductibles
`application_drivers`	Driver roster (name, DOB, license, MVR points, violations, accidents)
`application_driver_general_info`	Aggregate driver info (total employees, hiring rates, MVR review)
`application_driver_violations`	Individual driver violation records
`application_prior_carriers`	Prior carrier history (carrier, dates, coverage type, reason for change)
`application_loss_records`	Loss history (per-year accidents, claims, incurred, loss ratio)
`application_work_experience`	Work experience (new ventures — company, role, dates, supervisor)
`application_officers`	Officers/stockholders (title, name, ownership %)
`application_underwriting_qa`	Knockout questions (~23 yes/no fields for referral triggers)
`application_cargo`	Cargo coverage (limit, deductible, commodities)
`application_physical_damage`	Physical damage (comp/collision limits and deductibles)
`application_trailer_interchange`	Trailer interchange (limit, deductible, trailer types)
`application_additional_coverage`	Misc coverages (rental reimbursement, towing, medical payments)
`application_additional_interests`	Additional interests (mortgagee, loss payee, lienholder)
`application_filings`	Regulatory filings (BMC-91, BMC-34, Form E/H, MCS-90)
`application_attachments`	File attachments stored in Cloudflare R2

Policies

Table	Description
`policies`	Policy header (number, status, dates, premium)
`policy`	Extended policy detail (jacket data)
`coverages`	Line-level coverage records per policy
`endorsements`	Policy modification history
`interests`	Additional insureds, mortgagees, loss payees
`drivers`	Driver records (commercial auto)
`vehicles`	Vehicle/equipment schedule
`vehicle_coverages`	Per-vehicle coverage elections
`documents`	Policy documents with R2 key references
`coi`	Certificate of Insurance records
`esign`	E-signature audit trail (Documenso)

Rating

Table	Description
`rate_tables`	Rate table definitions and factor matrices
`rating`	Rating results with canonical payload and audit trail
`rating_results`	Source-tagged rating outputs (manual vs system)
`market_benchmarks`	Peer market pricing benchmarks

Claims

Table	Description
`claims`	Claim header (number, status, loss date)
`claim_lifecycle`	Claim status transition history
`claim_notes`	Claim notes with visibility
`claim_tasks`	Claim work items and diary entries
`claimants`	Claimant party information
`claim_transactions`	Financial transactions (payments, recoveries)
`litigation_timers`	Statute of limitations countdown
`disbursements`	Claim payment disbursements
`subrogation`	Subrogation tracking and recovery
`fraud_feedback`	Fraud model feedback and outcome labels

Financials

Table	Description
`billing`	Invoice and billing records
`invoice_line_items`	Line-item detail for invoices
`chart_of_accounts`	General ledger account hierarchy
`general_ledger`	Double-entry ledger transactions
`reconciliation`	Ledger reconciliation state
`disbursements`	Payment disbursement records
`ap_transactions`	Accounts payable transaction log
`reinsurance`	Reinsurance placement, cession, and recovery
`premium_finance`	Premium finance contracts
`loss_reserves`	Actuarial loss reserve records
`bordereaux`	Bordereaux submission records
`bordereaux_reviews`	Carrier bordereaux review outcomes

Compliance

Table	Description
`compliance`	Compliance filing calendar
`filings`	Individual regulatory filing records
`blacklist`	Blocked entities and IP addresses
`report_schedules`	Scheduled automated report configuration
`statutory_reporting`	Statutory exhibit data

Captive / RRG

Table	Description
`captive_accounting`	Captive fund accounting records
`loss_runs`	Loss history import and analysis
`inspections`	Risk inspection records
`smart_contracts`	Blockchain integration hooks

Infrastructure

Table	Description
`vectors`	Embedding vectors for semantic search
`territories`	Geographic territory and radius-of-operations
`commodity_mix`	Commodity exposure schedule
`motor_carriers`	DOT/FMCSA motor carrier detail
`telematics`	GPS and driver safety score data
`forms`	Form template library
`document_requirements`	Required document checklists by program

Migrations

Schema files live in packages/db/src/schema/ and are pushed to PlanetScale via Drizzle:

# Generate a new migration from schema changes
cd packages/db
pnpm drizzle-kit generate

# Apply migrations to the database
pnpm drizzle-kit push

# Or run via the migration job (CI/CD)
DATABASE_URL=$DATABASE_URL pnpm drizzle-kit migrate

The CircleCI db-migrate workflow runs on master push and applies pending migrations to the PlanetScale production branch.

Key Migrations

Migration	Purpose
`0010_row_level_security.sql`	Org-scoped tenant isolation setup
`0023_performance_scaling_indexes.sql`	Composite indexes for common query patterns
`0071_rls_comprehensive.sql`	Comprehensive tenant isolation gap closure
`0072_foreign_keys.sql`	FK constraints across all domain tables
`0074_general_ledger.sql`	Double-entry ledger table
`0080_documents_r2_key.sql`	R2 object key column on documents
`0081_submissions_carrier_id.sql`	Carrier ID on submissions for gateway routing

Seed Data

Demo data seeds a realistic policy portfolio for development and demos:

pscale shell openinsure main < packages/db/seeds/demo-data.sql

Seeded records:

Type	Count	Notable IDs
Organization	1	`00000000-0000-4000-a000-000000000001`
Programs	2	GL `...000011`, WC `...000012`
Rate Tables	2	GL `...000021`, WC `...000022`
Users	4	matt, michael, sean, jd (`10000000-...000X`)
Policies	2	WC Metro `...000201`, GL Acme `...000202`
Claims	3	Open `...000301`, Closed `...000302`, Pending `...000303`

PlanetScale Branching

PlanetScale’s database branching lets developers create isolated database branches for feature development:

# Create a branch for a feature
pscale branch create openinsure feat/my-feature

# Run migrations against the branch
DATABASE_URL=$(pscale connect openinsure feat/my-feature --execute 'echo') pnpm drizzle-kit push

# Delete branch when done
pscale branch delete openinsure feat/my-feature

Manage branches via pscale CLI or the PlanetScale dashboard at app.planetscale.com.

Packages Overview

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

Architecture

Edge/origin split, Hyperdrive, and data flow.