Rate Tables & Rating Engine

The rating engine in @openinsure/rating computes gross premium from a structured set of factors stored in the rate_tables database table. This page documents the schema, how the waterfall is applied, how to seed demo data, and how to use the interactive rater in the Underwriting Workbench.

Database Schema

Rate tables are stored in the rate_tables table in PlanetScale, defined in packages/db/src/supabase/schema/rate-tables.ts.

CREATE TABLE rate_tables (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id            uuid NOT NULL REFERENCES organizations(id),
  program_id        uuid NOT NULL,
  version           text NOT NULL,
  line_of_business  text NOT NULL,
  effective_date    timestamptz NOT NULL,
  expiration_date   timestamptz,
  base_rates        jsonb NOT NULL,
  factor_tables     jsonb NOT NULL,
  authority_factors jsonb,
  minimum_premiums  jsonb NOT NULL,
  rules             jsonb NOT NULL DEFAULT '[]',
  class_codes       jsonb,
  territory_factors jsonb,
  ilf_tables        jsonb,
  condition_rules   jsonb,
  is_active         boolean NOT NULL DEFAULT true,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

GIN indexes cover base_rates, factor_tables, rules, class_codes, and territory_factors for efficient JSONB path queries.

JSONB Column Shapes

base_rates

State-keyed map of base loss costs. The fallback key __ is used when no state-specific rate exists.

{
  "TX": { "perUnit": 1.85, "minPrem": 750, "basis": "revenue" },
  "CA": { "perUnit": 2.4, "minPrem": 1000, "basis": "revenue" },
  "__": { "perUnit": 2.0, "minPrem": 750, "basis": "revenue" }
}

perUnit is in dollars per exposure unit. The rating engine calls toCentsPerUnit() internally before any arithmetic to avoid floating-point drift.

basis controls how exposure is measured:

Value	Exposure unit
revenue	Per $1,000 gross revenue
payroll	Per $100 payroll
value	Per $100 insured value
employees	Per employee

class_codes

Array of class code definitions with rate modifiers:

[
  { "code": "41677", "description": "Trucking — long haul", "modifier": 1.25 },
  { "code": "41680", "description": "Trucking — local", "modifier": 1.1 },
  { "code": "99999", "description": "Miscellaneous", "modifier": 1.0 }
]

territory_factors

State/territory multiplicative factors applied after the class code modifier:

{
  "TX": 0.95,
  "CA": 1.3,
  "FL": 1.2,
  "NY": 1.45,
  "__": 1.0
}

minimum_premiums

State-keyed minimum premium floor (in dollars). Applied after the full factor waterfall:

{
  "TX": 500,
  "CA": 750,
  "__": 500
}

factor_tables

Arbitrary named factor tables for LOB-specific adjustments (ILFs, schedule credits, deductible factors):

{
  "deductible": {
    "$1000": 0.92,
    "$2500": 0.85,
    "$5000": 0.78,
    "$10000": 0.7
  },
  "schedule_credit_max": 0.25
}

Rating Engine Waterfall

The engine is implemented in packages/rating/src/ with the explainability layer in explain.ts.

Factor Application Order

1. Loss cost lookup (getLossCost() in loss-costs.ts)

   • Looks up base_rates[state] (or base_rates['__'] as fallback).
   • Converts perUnit dollars to cents via toCentsPerUnit().
   • Multiplies by the exposure units to produce basePremium.

2. Territory factor — multiplies basePremium by territory_factors[state].

3. Class code modifier — multiplies by the matching class_codes[].modifier.

4. Schedule modifier — manual credits/debits applied by the underwriter (bounded by factor_tables.schedule_credit_max).

5. Experience mod — actuarial modification based on loss history (experience-mod.ts).

6. LOB-specific factors — line-of-business engine factors (telematics score, FMCSA safety rating, CSA score, etc.).

7. AI risk adjustment — optional ±5 % adjustment from computeAiRiskNarrative() when signals exceed threshold.

8. Expense loading — 1 / permissibleLossRatio where PLR is set per program.

9. State premium tax — applied as a surcharge after the base premium.

10. Minimum premium floor — result is raised to minimum_premiums[state] if below floor.

Explainability (explainRating)

Every quote can be decomposed into per-factor dollar contributions for underwriter transparency and FCRA adverse action notices:

import { explainRating } from '@openinsure/rating/explain';

const explanation = explainRating(ratingResult, ratingInput);
// explanation.factors — ordered by dollar impact (largest first)
// explanation.adverseFactors — surcharge factors only
// explanation.adverseActionSummary — plain-language notice if premium > baseline by 5%

// Example output
{
  basePremium: 4200,
  finalPremium: 5418,
  netAdjustment: 1218,
  factors: [
    { name: "Territory: CA",              factor: 1.30, dollarImpact:  1260, isSurcharge: true  },
    { name: "Classification (class code)",factor: 1.25, dollarImpact:  1050, isSurcharge: true  },
    { name: "Experience mod",             factor: 0.95, dollarImpact:  -210, isCredit:    true  },
  ],
  requiresAdverseNotice: true,
  adverseActionSummary: "Your premium is higher than the standard rate due to: Territory: CA (+$1260), Classification (+$1050)…"
}

Seeding Demo Data

Demo rate tables are in packages/db/seeds/demo-data.sql. Run the seed against your local or branch PlanetScale database:

# Apply against local DATABASE_URL
psql $DATABASE_URL -f packages/db/seeds/demo-data.sql

# Or via the Supabase MCP seed endpoint (dev branch)
pnpm --filter @openinsure/db seed

The demo seed includes rate tables for:

• Commercial Auto (commercial_auto) — trucking class codes, state territory factors for TX/CA/FL/NY
• General Liability (general_liability) — revenue-based exposure, schedule credit table
• Workers Compensation (workers_comp) — NCCI class code structure, experience mod factors

Admin UI — Rate Table CRUD

Rate tables are managed in the Admin portal at /rate-tables. The UI provides:

• List view — all active rate tables for the org, grouped by line of business
• Detail view — full JSONB editors for each column with schema validation
• Version history — audit trail of all past versions linked to program_id
• Import — bulk-load rate tables from a structured JSON file

Import API

POST /v1/rate-tables/:id/import
Content-Type: application/json
Authorization: Bearer <token>

{
  "base_rates": { "TX": { "perUnit": 1.85, "minPrem": 750, "basis": "revenue" }, "…": "…" },
  "class_codes": [ { "code": "41677", "description": "Long haul", "modifier": 1.25 } ],
  "territory_factors": { "TX": 0.95, "CA": 1.30 },
  "minimum_premiums": { "TX": 500, "CA": 750, "__": 500 }
}

The import endpoint validates the incoming JSON against the rate table schema, creates a new version record, and sets is_active = true while marking the previous version inactive.

Note

Imports are org-scoped. You cannot import into a rate table that belongs to a different organization.

Interactive Rater (Underwriting Workbench)

The Underwriting Workbench (apps/underwriting-workbench) includes an InteractiveRater component that calls the rating explain endpoint with live DB data.

POST /v1/rating/explain
Content-Type: application/json

{
  "submissionId": "sub_01J8…",
  "overrides": {
    "scheduleModifier": 0.90,
    "state": "TX"
  }
}

Response:

{
  "ratingResult": { "basePremium": 4200, "finalPremium": 5418, "…": "…" },
  "explanation": {
    "factors": ["…"],
    "adverseActionSummary": null
  }
}

The InteractiveRater lets underwriters:

1. Override individual factors — adjust schedule modifier, select an alternate class code, or change the state without re-running the full submission flow.
2. See real-time dollar impact — each change re-calls /v1/rating/explain and updates the factor waterfall display.
3. Export the explanation — generates a PDF adverse action notice via @openinsure/documents.

Caution

Premium overrides made in the InteractiveRater are not automatically written back to the submission. The underwriter must explicitly click “Apply Quote” to persist the adjusted premium.