Opensure Insurance Schema v2.2.1

Complete database schema for multi-tenant insurance operations.

Version: 2.2.1 Release Date: 2025-11-25 Total Tables: 42 (35 existing + 7 new) Total Columns: 571

What's New in v2.2.1

New Tables (+7)

Table	Columns	Purpose
`client_locations`	22	Client property locations for multi-location risk tracking
`client_notes`	9	User notes attached to clients
`clients_chat`	13	LLM chat history for clients (placeholder)
`marketfinder_clients`	36	Prospects from Chrome Extension before conversion
`marketfinder_client_locations`	22	Property locations for MarketFinder prospects
`marketfinder_clients_notes`	9	User notes for prospects (copied on conversion)
`marketfinder_clients_chat`	13	LLM chat history (NOT copied on conversion)

Codex-Recommended Enhancements (+68 fields across 20 tables)

audit_logs: +1 (correlation_id)
clients: +4 (client_type, website, primary_contact_id, source_system)
policies: +10 (program_id, product_code, bound_at, quote_expires_at, source_channel, broker_reference, lender_reference, currency_code, external_ref, source_system)
policy_coverages: +2 (sort_order, section_code)
policy_risks: +2 (client_location_id, sort_order)
invoices: +5 (balance_due, written_off_amount, last_reminder_at, external_ref, source_system)
invoice_items: +3 (tax_code, gl_account_code, quantity)
invoice_payments: +3 (currency_code, fx_rate, original_currency_code)
reinsurers: +1 (external_ref)
reinsurance_agreements: +4 (reinsurer_id, reinsurance_broker_id, agreement_number, currency_code)
policy_cessions: +2 (ceded_commission, currency_code)
uw_submissions: +7 (status, submitted_at, updated_at, underwriter_id, decline_reason, source_channel, target_carrier_id)
tasks: +2 (completed_at, completed_by)
consents: +3 (ip_address, user_agent, document_hash)
claims: +6 (closed_at, loss_location_id, reported_by_contact_id, cat_event_code, external_ref, source_system)
claim_reserves: +3 (currency_code, reserve_type, coverage_id)
claim_payments: +3 (currency_code, coverage_id, funding_source)
claim_recoveries: +1 (currency_code)
claim_parties: +2 (organization_name, policy_role)

MarketFinder Conversion Flow

When a prospect is converted to a client:

marketfinder_clients          → clients                 (COPY)
marketfinder_client_locations → client_locations        (COPY)
marketfinder_clients_notes    → client_notes            (COPY)
marketfinder_clients_chat     → clients_chat            (NOT COPIED - stays in MarketFinder)

Chat history remains with the prospect record for audit purposes.

Schema Overview

Core Tables

Table	Purpose
`audit_logs`	Immutable audit trail for compliance
`clients`	Policyholders and customers
`policies`	Insurance policies
`claims`	Insurance claims with loss details

Policy Extensions

Table	Purpose
`policy_coverages`	Coverage limits and deductibles
`policy_exclusions`	Policy exclusions and limitations
`policy_risks`	Exposures/risks (property, vehicles, trips)
`policy_risk_properties`	Property-specific risk details
`policy_risk_vehicles`	Vehicle-specific risk details
`policy_risk_trips`	Travel-specific risk details
`policy_endorsements`	Mid-term adjustments
`policy_renewals`	Renewal tracking
`policy_cancellations`	Cancellation tracking
`policy_dependents`	Life/health dependents
`policy_beneficiaries`	Life insurance beneficiaries
`policy_documents`	Policy-level documents
`policy_cessions`	Reinsurance cessions

Billing & Invoicing

Table	Purpose
`invoices`	Billing invoices
`invoice_items`	Line items
`invoice_payments`	Payment records

Claims Management

Table	Purpose
`claim_status_history`	Immutable status change log
`claim_reserves`	Financial reserves
`claim_payments`	Payment ledger
`claim_recoveries`	Subrogation/salvage recoveries
`claim_parties`	Additional parties (witnesses, attorneys)
`claim_documents`	Claim attachments
`claim_notes`	Internal/external notes

Underwriting

Table	Purpose
`uw_submissions`	Submissions and questionnaires
`uw_scores`	Scores and decisions

Reinsurance

Table	Purpose
`reinsurers`	Reinsurance companies
`reinsurance_agreements`	Treaties and facultative agreements

Operations

Table	Purpose
`producers`	Brokers with commission tracking
`tasks`	Workflow tasks
`consents`	KYC/compliance consent tracking

PHI Segregation

Table	Purpose
`private.client_health_profiles`	Segregated health information

MarketFinder (NEW in v2.2.1)

Table	Purpose
`marketfinder_clients`	Prospects from Chrome Extension
`marketfinder_client_locations`	Prospect property locations
`marketfinder_clients_notes`	Prospect notes
`marketfinder_clients_chat`	LLM chat history

Client Extensions (NEW in v2.2.1)

Table	Purpose
`client_locations`	Client property locations
`client_notes`	Client notes
`clients_chat`	Client chat history

Enums

Line of Business (lob)

property, auto, liability, workers_comp, travel, life, health, marine, cyber, other

Claim Status (claim_status)

open, pending_info, under_review, approved, denied, litigated, closed

Claim Type (claim_type)

first_party, third_party, liability, workers_comp, health, property_auto, other

Cause of Loss (cause_of_loss)

fire, water, collision, theft, wind, liability, hail, flood, earthquake, hurricane, vandalism, cyber, other

Priority

low, normal, high

Payment Type (payment_type)

indemnity, expense

Payment Method (payment_method)

check, eft, ach, wire, card, other

Recovery Type (recovery_type)

subrogation, salvage, contribution, other

Policy Status (policy_status)

quote, bound, active, expired, cancelled, declined, pending, unpaid, completed

Endorsement Action (endorsement_action)

add, remove, modify, correct, cancel, reinstate

Billing Status (billing_status)

draft, invoiced, part_paid, paid, void, refunded, chargeback

Receivable Type (receivable_type)

premium, fee, tax, installment, endorsement_delta

Party Role (party_role)

insured, additional_insured, loss_payee, mortgagee, broker, producer, beneficiary, dependent, claimant, witness, attorney, vendor

Multi-Tenant Isolation

All tables enforce tenant_id at the database level. Row-level security (RLS) policies should be applied in production.

sql

-- Example RLS policy
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON clients
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id'));

Migration Safety

The schema uses idempotent migrations:

CREATE TABLE IF NOT EXISTS - Creates tables only if missing
CREATE TYPE IF NOT EXISTS (wrapped in DO blocks) - Creates enums safely
CREATE INDEX IF NOT EXISTS - Creates indexes safely
DROP TRIGGER IF EXISTS before CREATE TRIGGER - Safe trigger updates

Safe to run multiple times - will NOT drop existing data.

Table Details

audit_logs

Column	Type	Nullable	Default
id	BIGSERIAL	NO
tenant_id	VARCHAR(26)	NO
action	VARCHAR(50)	NO
entity_type	VARCHAR(100)	NO
entity_id	UUID	YES
actor_id	UUID	YES
actor_email	VARCHAR(255)	YES
ip_address	INET	YES
user_agent	TEXT	YES
request_method	VARCHAR(10)	YES
request_path	TEXT	YES
old_values	JSONB	YES
new_values	JSONB	YES
created_at	TIMESTAMPTZ	NO	NOW()
correlation_id	VARCHAR(50)	YES

clients

Column	Type	Nullable	Default
id	UUID	NO	gen_random_uuid()
uuid	UUID	NO	gen_random_uuid()
tenant_id	VARCHAR(26)	NO
email	VARCHAR(255)	YES
name	VARCHAR(255)	NO
company	VARCHAR(255)	YES
phone	VARCHAR(50)	YES
address	TEXT	YES
city	VARCHAR(100)	YES
region	VARCHAR(100)	YES
country	VARCHAR(100)	YES
postal_code	VARCHAR(20)	YES
external_ref	TEXT	YES
date_of_birth	DATE	YES
tax_id_last4	VARCHAR(4)	YES
preferred_language	VARCHAR(10)	YES
marketing_opt_in	BOOLEAN	YES	FALSE
kyc_level	VARCHAR(20)	YES
created	TIMESTAMPTZ	NO	NOW()
modified	TIMESTAMPTZ	NO	NOW()
client_type	VARCHAR(20)	YES	'individual'
website	VARCHAR(255)	YES
primary_contact_id	UUID	YES
source_system	VARCHAR(50)	YES

marketfinder_clients

Column	Type	Nullable	Default
id	UUID	NO	gen_random_uuid()
uuid	UUID	NO	gen_random_uuid()
tenant_id	VARCHAR(26)	NO
email	VARCHAR(255)	YES
name	VARCHAR(255)	NO
company	VARCHAR(255)	YES
phone	VARCHAR(50)	YES
address	TEXT	YES
city	VARCHAR(100)	YES
region	VARCHAR(100)	YES
country	VARCHAR(100)	YES	'Canada'
postal_code	VARCHAR(20)	YES
external_ref	TEXT	YES
date_of_birth	DATE	YES
tax_id_last4	VARCHAR(4)	YES
preferred_language	VARCHAR(10)	YES
marketing_opt_in	BOOLEAN	YES	FALSE
kyc_level	VARCHAR(20)	YES
user_id	VARCHAR(26)	NO
webpage_url	VARCHAR(200)	YES
naics_code	VARCHAR(10)	YES
industry	VARCHAR(255)	YES
operations	TEXT	YES
recommended_coverages	JSONB	YES	'[]'
classification_confidence	NUMERIC(3,2)	YES
source	VARCHAR(50)	YES	'chrome_extension'
status	VARCHAR(20)	YES	'draft'
converted_to_client_id	UUID	YES
converted_at	TIMESTAMPTZ	YES
is_active	BOOLEAN	NO	TRUE
created	TIMESTAMPTZ	NO	NOW()
modified	TIMESTAMPTZ	NO	NOW()
client_type	VARCHAR(20)	YES	'organization'
website	VARCHAR(255)	YES
primary_contact_id	UUID	YES
source_system	VARCHAR(50)	YES	'chrome_extension'

Changelog

v2.2.1 (2025-11-25)

Added 7 new tables for MarketFinder Chrome Extension support
Added client_locations, client_notes, clients_chat tables
Added 68 Codex-recommended fields across 20 tables
Enhanced multi-currency support
Added external system integration fields

v2.2.0 (2025-11-08)

Initial production schema
35 tables for comprehensive insurance operations
Multi-tenant isolation with tenant_id
PHI/PII segregation in private schema

Last Updated: 2025-11-25

Opensure Insurance Schema v2.2.1 ​

What's New in v2.2.1 ​

New Tables (+7) ​

Codex-Recommended Enhancements (+68 fields across 20 tables) ​

MarketFinder Conversion Flow ​

Schema Overview ​

Core Tables ​

Policy Extensions ​

Billing & Invoicing ​

Claims Management ​

Underwriting ​

Reinsurance ​

Operations ​

PHI Segregation ​

MarketFinder (NEW in v2.2.1) ​

Client Extensions (NEW in v2.2.1) ​

Enums ​

Line of Business (lob) ​

Claim Status (claim_status) ​

Claim Type (claim_type) ​

Cause of Loss (cause_of_loss) ​

Priority ​

Payment Type (payment_type) ​

Payment Method (payment_method) ​

Recovery Type (recovery_type) ​

Policy Status (policy_status) ​

Endorsement Action (endorsement_action) ​

Billing Status (billing_status) ​

Receivable Type (receivable_type) ​

Party Role (party_role) ​

Multi-Tenant Isolation ​

Migration Safety ​

Table Details ​

audit_logs ​

clients ​

marketfinder_clients ​

Changelog ​

v2.2.1 (2025-11-25) ​

v2.2.0 (2025-11-08) ​