> Disclaimer: This content is for educational and reference purposes for software builders only. It does not constitute legal, regulatory, actuarial, or financial advice. Consult a qualified professional before making decisions based on this content.
1. Model the Policy as an Append-Only Event Log
Never update a policy record in-place. Every change - endorsement, cancellation, reinstatement, premium adjustment - must be recorded as a new event with a timestamp, actor, and reason code. The current policy state is computed by replaying events from inception. This pattern satisfies regulatory reconstruction requirements (DoI audits, Solvency II Article 272 governance), prevents accidental data loss, and enables mid-term actuarial snapshots at any historical date.
2. Tag Every Transaction with Line of Business at Write Time
LOB (Line of Business) must be a mandatory, validated field on policies, claims, premiums, and financial transactions - never derived or inferred at reporting time. LOB drives regulatory filings (NAIC statutory filings, Solvency II QRT S.05.01), actuarial triangles, and management reporting. Storing LOB on each row eliminates reclassification errors and allows accurate extraction without knowledge of future regulatory mapping changes.
3. Store Earned Premium Separately from Written Premium
Written premium is recognised at policy inception; earned premium accrues over the coverage period. Maintain both figures: written_premium (constant once bound) and earned_premium_to_date (computed field or materialised column updated at each reporting date). For UPR calculations: upr = written_premium − earned_premium. Never conflate the two - regulatory filings, loss ratios, and IFRS 17 PAA liability for remaining coverage all depend on the earned/unearned split being correct.
4. Implement Claim Reserves at the Individual Claim Level
Case reserves must be stored per claim, not aggregated at the portfolio level. Each reserve revision must be a new record (append-only) with revised_at, revised_by, previous_reserve, new_reserve, and reason. This granularity is mandatory for: loss triangle construction (actuarial LDF derivation), IBNR validation (comparing actual development against model), regulatory examination, and fraud detection (patterns in reserve revisions). IBNR is a separate aggregate estimate at LOB/period level - never collapse IBNR into individual claim records.
5. Separate Gross, Ceded, and Net Amounts in Every Table
For every financial quantity that is reinsured, store three columns: gross_amount, ceded_amount, net_retained_amount with the constraint net_retained_amount = gross_amount − ceded_amount. Never store only net amounts. Regulatory filings (Schedule P in NAIC, QRT S.17 in Solvency II), management reporting, and reinsurance settlement all require the gross and ceded figures independently. Violating this pattern requires expensive reconstruction at audit time.
6. Record the Cat Model Run Metadata with Every Modelled Loss Estimate
When storing catastrophe model output, always record alongside the loss figure: model_vendor (e.g., RMS, AIR, CoreLogic), model_version, peril, return_period_years, and run_date. Cat model results are version-sensitive - the same portfolio will produce different loss estimates across model versions. Storing the run metadata allows historical comparisons, regulatory disclosure (cat model version used in SCR calculation), and reinsurance contract audit trails.
7. Use Controlled Vocabularies for Complaint Reason Codes
Insurance complaint reason codes must be mapped to a regulated taxonomy. In the US, align with NAIC complaint reason codes (15+ standard categories). In the EU, align with EIOPA guidelines on complaints handling. Never use free-text complaint categories - they prevent mandatory aggregate reporting to the DoI, make trend analysis impossible, and fail regulator examinations. Implement the controlled vocabulary as a reference table with the effective date range, so that updates to the taxonomy are versioned and historical records retain their original code.
8. Store Every Regulatory Filing with Status Tracking
Each regulatory submission (state rate filing, annual statement, Solvency II QRT, SFCR) must have a corresponding filings record that tracks: filing type, jurisdiction, submission date, tracking reference (DoI control number or XBRL filing ID), current status (PENDING / APPROVED / OBJECTED / WITHDRAWN), approval date, and objection details. Implement a filing state machine - never infer approval status from absence of objection. Many DoI workflows silently approve filings after a waiting period; this silence-is-approval rule must be encoded as a scheduled job that transitions status after the regulatory deadline.
9. Implement the IFRS 17 PAA Eligibility Check as a Documented Decision
For every group of insurance contracts, the decision to apply the Premium Allocation Approach (PAA) versus the General Measurement Model (GMM) must be formally documented. The primary criterion - coverage period ≤ 12 months - must be evaluated at initial recognition and stored as a field on the contract group record alongside the assessment date and assessor ID. For multi-year policies that contain annual coverage periods, document the specific eligibility analysis. Auditors will require evidence that the PAA election was made with a documented rationale, not assumed by default.
10. Version the Actuarial Assumptions Table
Actuarial parameters (discount rates, loss development factors, tail factors, claim frequency/severity assumptions) must be versioned with effective dates, not updated in place. Each actuarial_run record should reference the actuarial_assumptions_version used. This allows: (a) reconstruction of any historical reserve figure, (b) attribution analysis (how much of reserve development is model change vs. experience), (c) Solvency II Article 78 governance evidence that assumptions were independently reviewed and signed off.
11. Enforce the No-Premium-Leakage Rule in Reinsurance Settlement
When calculating the amount due to a reinsurer under a proportional treaty, always apply: ceded premium = written premium × cession_percentage. Never apply the cession rate to earned premium for premium settlement (settlements are on written basis; experience refunds or sliding scales are separate calculations). Implement this as a database constraint or application-level assertion - mistakes here directly affect P&L and can trigger reinsurance disputes.
12. Record All Solvency II QRT Outputs with the XBRL Taxonomy Version
Every Solvency II QRT submission must record: taxonomy_version (EIOPA publishes annually), reporting_period, submission_deadline, actual_submission_date, entity_xbrl_id (LEI or XBRL entity identifier), and the full set of reported data points keyed to EIOPA concept references (e.g., S.05.01.01.01 - written premiums, gross). Storing data against concept references rather than human-readable labels prevents mapping errors when EIOPA updates the taxonomy. Implement an automated QRT extraction that maps internal LOB codes to EIOPA LOB classifications via a configuration table updated each taxonomy cycle.
13. Implement Catastrophe Aggregate Monitoring with Real-Time Accumulation
Aggregate reinsurance treaties (stop-loss, aggregate XL) have attachment points defined as a percentage of earned premium or an absolute loss amount across all occurrences in the treaty period. Builders must implement real-time aggregate accumulation: sum of claim_reinsurance_recoveries.gross_loss for claims within the treaty period. Alert when accumulation reaches 70% and 90% of the attachment point. Failure to monitor accumulation means the ceding company may inadvertently cross the attachment without triggering the recovery calculation.
14. Store Consumer Complaint SLA Deadlines as Computed Columns
Every consumer complaint must have a resolution_deadline stored as a computed column at creation time: resolution_deadline = received_at + state_sla_days where state_sla_days comes from a state_complaint_slas configuration table (not hardcoded). Each US state DoI specifies its own complaint resolution SLA (typically 15–30 working days). Implement a daily job that flags complaints approaching their deadline (deadline_approaching_at = resolution_deadline − INTERVAL '5 days') and sends alerts to the handling team. Breaching complaint SLAs can result in DoI fines and creates evidence of systemic claims handling failures.