PolicyOne Data Model
This section documents the PolicyOne schema as it actually exists today. Pages are added incrementally — one entity group at a time.
Core Entity Chain
Pages in this section
| Page | What it covers |
|---|---|
| Submission | How a submission is created, what it owns, and its full status lifecycle |
More pages will be added as each entity group is documented.
New Tenant Setup Sequence
When onboarding a new carrier, create records in this exact order. Each step depends on the prior step's IDs. After step 19 the carrier is ready to receive submissions.
Design Principles
1. Multi-Tenant SaaS Isolation
Every top-level table carries a tenantId column. PostgreSQL Row-Level Security
(RLS) policies enforce that no query — regardless of which application layer
issued it — can read or write data belonging to a different tenant. A tenantId
is a UUID foreign key to org.tenants.
2. Immutable Event Stream
Policies are never mutated in place. All state changes — endorsements,
cancellations, reinstatements, renewals — are recorded as rows in
policy.policy_transactions. The current state of a policy is always derivable
from its transaction stream. A snapshot cache (policy.policy_snapshots) is
maintained for read performance but is never the source of truth.
3. Effective-Dating on All Mutable Risk Entities
Risk objects (vehicles, drivers, locations, buildings) carry effective_from
and effective_to timestamps. effective_to IS NULL means "currently active".
A change creates a new row; the prior row's effective_to is set. This mirrors
Guidewire's effective-dating pattern and gives a full point-in-time audit trail.
4. Internal vs. External User Separation
Carrier employees and external producers always live in separate tables with separate session infrastructure. This is a hard security boundary enforced at the database level, not just at the application layer. See 03-security.md for full details.
5. Discriminator Pattern Over Table-Per-Subtype
Where entities share the same lifecycle and most columns (e.g. personal-auto
vehicles and commercial-auto vehicles), a single unified table is used with a
lobCode discriminator column. LOB-specific columns are nullable and documented
per LOB. This mirrors Guidewire's Coverage and InsuredItem pattern.
6. Cross-System References Are varchar, Not FKs
PolicyOne integrates with PartyOne, BillingOne, ClaimsOne, and ProductOne.
References to entities in those systems (customer IDs, party IDs, producer codes)
are stored as varchar columns, never as foreign keys. This preserves independent
deployability of each product in the platform.
7. PII Is Either Hashed or Encrypted — Never Plain Text
License numbers, EINs, SSNs, and TOTP secrets are stored exclusively as hashes (HMAC-SHA-256) or ciphertext (AES-256). Plain-text sensitive values are held only in application memory during the request lifecycle.
Database Schema Namespaces
PolicyOne uses PostgreSQL schemas (namespaces) to separate logical domains:
| PG Schema | Purpose | Key Tables |
|---|---|---|
org | Carrier organization, users, sessions | tenants, users, sessions, agencies, producers |
security | RBAC, permissions, UW authority, API clients | roles, permissions, uw_authority_profiles, api_clients |
policy | Full policy data model | policies, policy_transactions, coverages, lob_vehicles |
audit | Immutable SOC2 / GDPR audit log | audit_log |
Layer Map
Document Sequence
Read documents in this order for a complete understanding:
| # | Document | What You Learn |
|---|---|---|
| 01 | Organization | How carrier org hierarchy is structured; users and sessions |
| 02 | Distribution | Agencies, producer codes, how BillingOne gets commission data |
| 03 | Security | RBAC, UW authority, internal vs. external, API clients |
| 04 | Policy Lifecycle | Submission → Quote → Bind → Policy end-to-end |
| 05 | Party Model | How people and entities are stored; cross-system identity |
| 06 | Product Catalog | Product versions, coverage menus, UW rules |
| 07 | Policy Structure | Policies, LOBs, terms, party roles |
| 08 | Transactions | Immutable event stream; endorsements, cancellations, OOS |
| 09 | Coverages | Coverage terms, insured items, coverage assignments |
| 10 | LOB — Auto | Vehicles, drivers, violations, driver-vehicle assignments |
| 11 | LOB — Dwelling | Locations, buildings, features, BPP, occupancies |
| 12 | Cost & Rating | Rating results, premiums, adjustments, taxes |
| 13 | Audit Log | SOC2, GDPR/CCPA event log, retention |
Setup Sequence for a New Tenant
When onboarding a new carrier, create records in this exact order. Each step depends on the prior step's IDs.
1. org.tenants ← create the carrier record
2. org.business_units ← Personal Lines, Commercial Lines, etc.
3. org.offices ← regional offices
4. org.teams ← underwriting teams, CSR teams
5. security.roles ← create carrier-custom roles (or use OOTB)
6. security.permissions ← OOTB permissions ship with the platform
7. security.role_permissions ← assign permissions to roles
8. org.auth_provider_configs ← SAML / OIDC config
9. org.users ← carrier employees
10. org.user_roles ← assign roles to users with LOB/state scope
11. security.uw_authority_profiles ← define UW authority tiers
12. security.uw_authority_assignments ← assign profiles to underwriters
13. org.agencies ← appointed agencies
14. org.agency_appointments ← appoint agencies per state × LOB
15. org.producers ← individual licensed producers
16. org.producer_licenses ← state license records
17. org.producer_agency_memberships ← link producers to agencies with producerCode
18. policy.product_catalog ← synced automatically from ProductOne webhook
19. policy.uw_rules ← synced automatically from ProductOne webhook
After step 19, the carrier is ready to receive submissions.