> 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.
What data fields are required to model a complete insurance policy lifecycle?
A policy lifecycle record needs, at minimum: policy_id, quote_id (traceability back to quotation), insured_id (FK to customer), product_code, line_of_business, effective_date, expiry_date, written_premium, payment_frequency, status (enum: QUOTED/BOUND/ISSUED/IN_FORCE/CANCELLED/LAPSED/RUN_OFF), cancellation_reason, cancellation_date, created_at, updated_at. For property: add location_id, tiv (Total Insured Value), construction_type. For liability: add limit_per_occurrence, aggregate_limit, retroactive_date. The status field drives all downstream calculations (UPR, earned premium, reserve release).
How do I compute Unearned Premium Reserve (UPR) programmatically?
UPR for a single policy at reporting date D: upr = written_premium × (remaining_days / total_days) where remaining_days = MAX(0, expiry_date − D) and total_days = expiry_date − effective_date. Aggregate by LOB: SELECT lob, SUM(written_premium * GREATEST(0, expiry_date - :report_date) / NULLIF(expiry_date - effective_date, 0)) AS upr FROM policies WHERE status = 'IN_FORCE'. For multi-year policies, apply the same formula per policy-year segment, not the full contract term.
What is the minimum data required for an actuarial loss triangle?
A loss development triangle requires: accident_year (or accident_quarter), development_age (months from accident year start to evaluation date), cumulative_paid_losses, cumulative_incurred_losses (paid + case reserve). These four fields, grouped by LOB and carrier, allow calculation of Loss Development Factors (LDFs) using the chain-ladder method. Builders must store losses at this granularity - not just total balances - to support actuarial extraction without custom aggregation.
How do I model a consumer complaint in code?
Minimum complaint record: complaint_id, policy_id (nullable if pre-policy), complainant_type (FIRST_PARTY, THIRD_PARTY, REGULATOR), received_at (TIMESTAMPTZ), channel (PHONE, EMAIL, MAIL, PORTAL, REGULATOR_FORWARDED), complaint_reason_code (controlled vocabulary aligned to NAIC complaint codes), complaint_text, status (RECEIVED / UNDER_REVIEW / RESOLVED / ESCALATED / REGULATOR_REFERRED), resolution_at, resolution_code, regulatory_filing_ref (if filed with DoI). Many states require complaints to be reported annually to the DoI - store state and regulator_notified boolean with timestamp.
What is the difference between a claim reserve and IBNR?
A case reserve (claim reserve) is an estimate of the future payout for a known, reported claim - set by the claims adjuster for each open claim. IBNR (Incurred But Not Reported) is an actuarial aggregate estimate for losses that have occurred but have not yet been reported to the insurer. IBNR is not claim-level; it is a balance sheet liability calculated from development patterns. Builders store case reserves per claim record; IBNR is stored as an actuarial estimate record at the LOB/period level, not attached to individual claims.
How should reinsurance cessions be modelled in a data schema?
Three tables: (1) reinsurance_treaties - treaty_id, treaty_type (QS/SURPLUS/XL/STOP_LOSS), attachment_point, limit, cession_percentage, reinsurer_id, effective_date, expiry_date. (2) cession_statements - statement_id, treaty_id, period_start, period_end, ceded_premium, ceded_losses, ceded_lae. (3) claim_reinsurance_recoveries - recovery_id, claim_id, treaty_id, gross_loss, ceded_amount, net_retained. Never combine gross and net amounts in a single field - regulatory filings require gross, ceded, and net separately for each period and LOB.
What Solvency II QRT data points does a policy system need to supply?
QRT S.05.01 (Premiums, claims and expenses by LOB) requires: written premium, earned premium, claims incurred, claims paid, operating expenses - all split by Solvency II LOB classification (not the internal product code). QRT S.12 (Life) and S.17 (Non-Life) require best estimate technical provisions. QRT S.19 (Claims development triangles) requires accident year / development year cumulative paid/incurred data. Builders must maintain a mapping table from internal product codes to Solvency II LOB codes and ensure all transactions are tagged at write time, not remapped at reporting time.
How do I implement the Solvency Capital Requirement (SCR) calculation?
The standard formula SCR is a modular calculation: market risk, counterparty default risk, life underwriting risk, non-life underwriting risk, health underwriting risk - aggregated with a correlation matrix. For non-life underwriting risk the premium and reserve risk sub-module uses volume measures (net earned premium by LOB) and standard deviations from EIOPA calibrations. Builders typically import the EIOPA standard parameters from a configuration table rather than hardcoding them, allowing updates when EIOPA revises calibrations. The SCR output (a single capital figure) and its decomposition by module are stored in a periodic calculation record with calculation_date, method (STANDARD_FORMULA/INTERNAL_MODEL), scr_total, and sub-module breakdowns.
What fields are mandatory in a state insurance regulatory filing?
US state filing requirements vary by state but NAIC-aligned common fields include: filing_type (ANNUAL_STATEMENT, QUARTERLY, RATE_FILING, FORM_FILING), naic_company_code, state_of_domicile, filing_state, doi_tracking_number, filing_date, period_start, period_end, status (PENDING/APPROVED/OBJECTED/WITHDRAWN). Rate and form filings additionally need effective_date_requested, approval_date, objection_reason (if objected). Builders must track filings per DoI - a single filing may be submitted to multiple states with separate tracking numbers and approval workflows.
What is IFRS 17 and which measurement model applies to most non-life policies?
IFRS 17 is the international accounting standard for insurance contracts, effective January 2023. It introduces three measurement models: (1) General Measurement Model (GMM) - for long-duration life and complex contracts; (2) Premium Allocation Approach (PAA) - a simplification for short-duration contracts (≤1 year coverage) that approximates the liability for remaining coverage as an unearned premium liability; (3) Variable Fee Approach (VFA) - for participating contracts. Most non-life property and casualty policies qualify for PAA. Builders must implement a qualification check per group of contracts and record the elected measurement model as a field on the contract group record.
How does the NAIC complaint handling process translate to a data model state machine?
NAIC complaint codes define: the reason for complaint (15+ categories: claim handling, marketing, underwriting, billing, policy servicing, etc.) and the disposition (upheld, denied, withdrawn, etc.). A state machine for complaint handling has states: RECEIVED → ACKNOWLEDGED → INVESTIGATION → PENDING_RESPONSE → RESOLVED → CLOSED (terminal). Escalation path: any state → REGULATOR_ESCALATED. Each state transition must record transitioned_at, transitioned_by_user_id, and reason_note. SLA targets (often 30 days for resolution) are tracked via deadline_at = received_at + INTERVAL '30 days' (varies by state DoI).
How should endorsements be stored relative to the base policy?
An endorsement changes one or more terms of an existing policy mid-term. Model as an endorsements table: endorsement_id, policy_id, endorsement_number (sequential per policy), effective_date, endorsement_type (ADD_COVERAGE, REMOVE_COVERAGE, CHANGE_LIMIT, CHANGE_ADDRESS, ADD_VEHICLE, etc.), change_description, additional_premium (positive or negative), issued_at. Never overwrite the base policy record directly - the current policy state is the base policy plus all effective endorsements ordered by effective_date. This append-only pattern supports mid-term audit queries and regulatory reconstruction of policy terms at any date.
What data does catastrophe modelling require from the policy system?
Cat model inputs per location: location_id, latitude, longitude, geocode_quality_code (precision indicator), construction_type (ISO construction class), occupancy_code (ISO occupancy class), year_built, number_of_stories, tiv (Total Insured Value), replacement_cost_value, deductible_amount, policy_limit. Cat model outputs to store: model_vendor, model_version, peril (HURRICANE/EARTHQUAKE/FLOOD/WILDFIRE), return_period (years), modeled_loss_amount, aep_exceedance_probability, run_date. Store outputs in a cat_model_results table keyed on (location_id, model_vendor, model_version, peril, return_period).
How do I implement the CSM (Contractual Service Margin) under IFRS 17?
The CSM represents unearned profit in a group of insurance contracts. At initial recognition: CSM_0 = PV(future cash inflows) − PV(future cash outflows) − RA_0 where RA is the risk adjustment. If this is negative (onerous group), recognise a loss immediately and set CSM = 0. Each subsequent period: adjust CSM for: (a) interest accretion at the locked-in discount rate, (b) changes in estimates of future cash flows that relate to future service, (c) release to P&L as insurance service is provided (CSM amortised over coverage units). Builders implement CSM as a cohort-level calculation updated on every transaction touching that cohort.
What is the difference between occurrence and claims-made coverage for builders?
An occurrence policy covers events that happen during the policy period, regardless of when the claim is filed. A claims-made policy covers claims first made during the policy period (with a retroactive date). This distinction affects the reserve calculation and run-off exposure. For occurrence policies, IBNR can develop over many years after policy expiry. For claims-made, the primary tail risk is in the extended reporting period (ERP or "tail coverage"). Builders must store coverage_trigger (OCCURRENCE/CLAIMS_MADE) and retroactive_date (for claims-made) on every professional and liability policy, as these fields determine claim coverage eligibility.
How should policy cancellation and lapse be handled in the data model?
Cancellation (insurer-initiated or voluntary) requires: cancellation_date, cancellation_reason_code (NON_PAYMENT, UNDERWRITING, VOLUNTARY_INSURED, REGULATORY, FRAUD), return_premium_amount (short-rate or pro-rata calculation), return_premium_paid_at. Lapse is non-payment cancellation after a grace period - store grace_period_end_date and transition to LAPSED status if payment not received. Reinstatement: create a reinstatement record linking back to the lapsed policy. Regulatory rules vary by state - some DoIs require advance notice (e.g., 10/20/30 days depending on cancellation reason). Store cancellation_notice_sent_at and notice_days_required per state.