Database Schema

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.

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)

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

​

Multi-Tenant Isolation (orgId Scoping)

// 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);

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

​

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

​

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 packages/db/migrations/mysql/00NN_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

​

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

​

Database Migrations

​

Hyperdrive

​

Packages Overview