Entity Relationship Model
Full entity relationship map for the policy.* schema. Each diagram below focuses on one domain cluster. Hover any diagram and click Expand to view it fullscreen.
1 — Party (Customer) Cluster
All identity and contact data lives here. parties is the customer entity — there is no separate Customer table.
2 — Submission & Quote Cluster
The risk intake container and its priced output. A submission and its first quote are always created together atomically.
3 — Policy Cluster
Created at bind. A submission is linked directly to a policy and policy_term at bind time — there is no separate transaction bridge table. The bound submission itself is the transaction record.
4 — Full Join Map (Simplified)
How all clusters connect end-to-end. Solid lines = FK constraints. Dashed lines = soft UUID references (no DB FK).
Key Design Decisions
submissions is the transaction record
There is no separate policy_transactions table. A submission that has been bound carries all bind-time data directly: policy_id, term_id, sequence_number, bound_at, is_out_of_sequence. The join from a policy to its history is:
FROM policy.policies p
JOIN policy.submissions s ON s.policy_id = p.id
ORDER BY s.sequence_number
policy_number lives on policy_terms, not policies
Carriers may issue a new policy number on renewal. policies.id is the stable cross-term key; policy_terms.policy_number is the human-readable number for each renewal cycle.
-- Get policy number for a policy
SELECT pt.policy_number
FROM policy.policy_terms pt
WHERE pt.policy_id = $policyId
AND pt.term_number = 1 -- or the active term
customer_id is a soft cross-system reference
Both policies.customer_id and submissions.customer_id store the parties.id UUID as a varchar with no FK. This allows a party to be shared across tenants and referenced from outside the policy schema without enforcing a DB-level constraint.
parties is the Customer entity
There is no separate "Customer" table. policy.parties serves as the customer record:
| Concept | Field |
|---|---|
| Customer UUID | parties.id |
| Customer number | parties.party_number (e.g. CUS-2026-000004) |
| Person identity | first_name, last_name, date_of_birth |
| Org identity | legal_name, dba_name, entity_type |
| Contact info | party_contact_points (separate table) |
| Gov IDs | party_identifiers (hashed — never plain text) |
A single party can appear on multiple policies across multiple tenants via submission_parties.
Billing routing via billingMethod + parties[]
BillingOne determines who receives the invoice by combining quote_billing.billing_method with the roles in submission_parties:
billing_method | Bill-to target |
|---|---|
direct_bill | Party with role = named_insured, order_num = 0 |
agency_bill | Agency resolved from submission.agency_id |
mortgage_bill | Party with role = mortgagee (carries loan_number) |
This means no explicit billTo field is needed — all possible bill-to parties are already in submission.parties[] with their roles.
Full ER Diagram
BillingOne Sync Event
When a quote is bound, PolicyOne fires a BillingOnePolicySyncEvent to POST /api/v1/policies/sync. The payload assembles data across the full join chain above:
parties → customer.* (displayName, email, phone, address)
submissions → submission.submissionId / submissionNumber / sequenceNumber / transactionType
submission_parties → submission.parties[] (all roles incl. mortgagees)
quotes → quote.quoteId / premium breakdown
quotes.rating_response → premium.chargeItems (taxes, surcharges, fees)
quote_billing → billing.paymentPlanCode / billingMethod
policy_terms → policyNumber / termNumber
policies → policyId / customerId / tenantId
See policy-bound.schema.json for the full contract and billing-event.sample.json for a real payload from POL-PA-2026-000001.
Entity Relationship Model
Full entity relationship map for the policy.* schema. Each diagram below focuses on one domain cluster. Hover any diagram and click Expand to view it fullscreen.
1 — Party (Customer) Cluster
All identity and contact data lives here. parties is the customer entity — there is no separate Customer table.
2 — Submission & Quote Cluster
The risk intake container and its priced output. A submission and its first quote are always created together atomically.
3 — Policy & Transaction Cluster
Created at bind. policy_transactions is the bridge back to the submission — the only join path between policies and submissions.
4 — Full Join Map (Simplified)
How all clusters connect end-to-end. Solid lines = FK constraints. Dashed lines = soft UUID references (no DB FK).
Key Design Decisions
No direct FK between policies and submissions
policy_transactions.submission_id is a plain uuid with no FK constraint — intentional to avoid a circular schema import. The canonical join is:
FROM policy.policies p
JOIN policy.policy_transactions pt ON pt.policy_id = p.id
JOIN policy.submissions s ON s.id = pt.submission_id
parties is the Customer entity
There is no separate "Customer" table. parties.party_number (CUS-2026-000004) is the customer number. One party can appear on multiple policies via submission_parties.
Billing routing: billingMethod + parties[]
BillingOne derives who receives the invoice from:
billing_method | Bill-to target |
|---|---|
direct_bill | Party with role = named_insured, order_num = 0 |
agency_bill | Agency resolved from submission.agency_id |
mortgage_bill | Party with role = mortgagee (carries loan_number) |
No explicit billTo field needed — all bill-to candidates are already in submission_parties[] with their roles.
Full ER Diagram
Key Design Decisions
No direct FK between policies and submissions
policy_transactions.submission_id is a plain uuid column with no FK constraint. This is intentional — it avoids a circular schema import between policy.ts and submission.ts. The join is always done at query time:
FROM policy.policies p
JOIN policy.policy_transactions pt ON pt.policy_id = p.id
JOIN policy.submissions s ON s.id = pt.submission_id
customer_id is a soft cross-system reference
Both policies.customer_id and submissions.customer_id store the parties.id UUID as a varchar with no FK. This allows a party to be shared across tenants and referenced from outside the policy schema without enforcing a DB-level constraint.
parties is the Customer entity
There is no separate "Customer" table. policy.parties serves as the customer record:
| Concept | Field |
|---|---|
| Customer UUID | parties.id |
| Customer number | parties.party_number (e.g. CUS-2026-000004) |
| Person identity | first_name, last_name, date_of_birth |
| Org identity | legal_name, dba_name, entity_type |
| Contact info | party_contact_points (separate table) |
| Gov IDs | party_identifiers (hashed — never plain text) |
A single party can appear on multiple policies across multiple tenants via submission_parties.
Billing routing via billingMethod + parties[]
BillingOne determines who receives the invoice by combining quote_billing.billing_method with the roles in submission_parties:
billing_method | Bill-to target |
|---|---|
direct_bill | Party with role = named_insured, order_num = 0 |
agency_bill | Agency resolved from submission.agency_id |
mortgage_bill | Party with role = mortgagee (carries loan_number) |
This means no explicit billTo field is needed — all possible bill-to parties are already in submission.parties[] with their roles. A third-party payer (e.g. a parent paying for a child's policy) is modelled as an additional party with an appropriate role.
BillingOne Sync Event
When a quote is bound, PolicyOne fires a BillingOnePolicySyncEvent to POST /api/v1/policies/sync. The payload assembles data across the full join chain above:
parties → customer.* (displayName, email, phone, address)
submissions → submission.submissionId / submissionNumber
submission_parties → submission.parties[] (all roles incl. mortgagees)
quotes → quote.quoteId / premium breakdown
quotes.rating_response → premium.chargeItems (taxes, surcharges, fees)
quote_billing → billing.paymentPlanCode / billingMethod
policy_transactions → sequenceNumber / transactionKind / occurredAt
policies → policyId / policyNumber / customerId / tenantId
See policy-bound.schema.json for the full contract and billing-event.sample.json for a real payload from POL-PA-2026-000001.