Skip to main content
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/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:
ValueExposure unit
revenuePer $1,000 gross revenue
payrollPer $100 payroll
valuePer $100 insured value
employeesPer 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. :::

​
Rate Table Comparison

The platform provides two comparison capabilities: market benchmarking against industry data, and side-by-side diffing of two rate table versions.

​
Market Benchmark Comparison

Compare a rated premium against market benchmark data for a given class code, state, and line of business: 
GET /v1/rate-tables/compare?premium=5418&classCode=41677&state=TX&lob=commercial_auto
Authorization: Bearer <token>
The endpoint looks up the most recent benchmark record matching the class/state/LOB combination from the market_benchmarks table, then calls compareToMarket() from @openinsure/rating to produce a positioning result relative to the market distribution:
  • medianRate — The 50th percentile market rate
  • p25Rate — The 25th percentile (lower quartile)
  • p75Rate — The 75th percentile (upper quartile)
The response indicates whether the quoted premium falls below, within, or above the interquartile range — helping underwriters assess competitiveness. All four query parameters (premium, classCode, state, lob) are required. Returns 404 if no benchmark data exists for the combination.

​
Market Benchmark Data Management

Benchmarks are managed through two endpoints: 
GET /v1/rate-tables/benchmarks
Authorization: Bearer <token>
Lists all market benchmark records for the organization, with optional filtering by lob, state, and classCode. 
POST /v1/rate-tables/benchmarks/import
Authorization: Bearer <token>
Content-Type: application/json

{
  "benchmarks": [
    {
      "classCode": "41677",
      "state": "TX",
      "lob": "commercial_auto",
      "medianRate": 5200,
      "p25Rate": 4100,
      "p75Rate": 6800,
      "effectiveDate": "2025-01-01"
    }
  ]
}

​
Rate Table Version Diff

Compare two rate table versions side-by-side to understand what changed between releases: 
GET /v1/rate-tables/:id/compare/:otherId
Authorization: Bearer <token>
The endpoint loads both rate tables and produces a structured diff across five JSONB columns:
SectionWhat is compared
baseRatesState-keyed base loss costs (perUnit, minPrem, basis)
factorTablesNamed factor tables (deductible factors, schedule credit limits)
territoryFactorsState territory multiplicative factors
rulesRating rules and conditions
conditionRulesConditional rating logic
Each section returns an array of changes, where each change includes the key, the left value (from the first table), and the right value (from the second table). Only keys where the values differ are included. The response also includes metadata for each side: 
{
  "left": {
    "id": "rt_01J8...",
    "version": "2025-Q1",
    "lineOfBusiness": "commercial_auto",
    "effectiveDate": "2025-01-01"
  },
  "right": {
    "id": "rt_01J9...",
    "version": "2025-Q2",
    "lineOfBusiness": "commercial_auto",
    "effectiveDate": "2025-04-01"
  },
  "baseRates": [
    {
      "key": "CA",
      "left": { "perUnit": 2.4, "minPrem": 1000, "basis": "revenue" },
      "right": { "perUnit": 2.6, "minPrem": 1100, "basis": "revenue" }
    }
  ],
  "factorTables": [],
  "territoryFactors": [{ "key": "FL", "left": 1.2, "right": 1.25 }],
  "rules": [],
  "conditionRules": []
}
Returns 404 if one or both rate tables are not found. Both tables must belong to the authenticated organization.