Skip to content

README.md

Latest commit

 

History

History
250 lines (189 loc) · 12.9 KB

README.md

File metadata and controls

250 lines (189 loc) · 12.9 KB

Life Policy Admin System — Mockup Database and Tool (Prototype)

This is a proof-of-concept prototype for educational use only. It is not open source and may not be used commercially. See LICENSE for full terms.

1. Overview

This repository contains a self-contained prototype of a life insurance Policy Administration System (PAS) risk diagnostic tool, built for educational and demonstration purposes. It simulates a Southeast Asian insurance context (Singapore SGD / Malaysia MYR, with mock MAS-aligned regulatory parameters) and is intended to illustrate how data quality, actuarial logic, and regulatory compliance defects can be systematically detected within a PAS.

The tool was developed iteratively across four versions (V1–V4) and is not intended for production use.

2. Background & Purpose

Life insurance Policy Administration Systems accumulate data across decades. Errors — ranging from simple null fields to subtle actuarial miscalculations — can compound over time and are difficult to surface without a structured diagnostic approach.

This prototype was built to:

  • Demonstrate how a tiered risk check framework can be applied to life insurance PAS data
  • Provide a realistic synthetic dataset (with deliberately seeded defects) to validate diagnostic logic against known ground truth
  • Explore how AI capabilities (natural-language querying, narrative report generation) can assist with policy data review
  • Serve as a learning and reference tool for those interested in insurance data quality, actuarial systems, or diagnostic tooling

The tool is a proof of concept only. It has not undergone full testing or validation and should not be used in any real-world or production context.

3. What It Does

Component Description
Mock PAS Database SQLite database with ~1,100 records across 15 tables, 250 synthetic policies, and 36 deliberately seeded defects
Diagnostic Engine 45 risk check functions organised across 6 tiers, covering data integrity through regulatory compliance
CLI Mode Run all diagnostics and generate reports from the command line (no external dependencies beyond Python stdlib)
Web Dashboard Flask-based UI with interactive charts, check selector, drill-down interpretation, and AI-powered narrative reports
Natural-Language Query Engine Ask questions in plain English; the tool generates and executes SQL via the Anthropic API
Draft Check Promotion Create new diagnostic checks via the UI (from NL queries), review the generated Python, and commit them to the check registry

4. Risk Tier Framework

The diagnostic engine applies 45 checks organised into 6 tiers of increasing complexity:

Tier Category Description
1 Data Integrity Missing, null, or invalid field values
2 Referential / Structural Integrity Foreign key violations, orphaned records, duplicates
3 Actuarial Logic Age inconsistencies, premium rate monotonicity, coverage gaps
4 Governance Stale parameters, versioning gaps, overlapping records
5 Business Logic & Calculation Bonus consistency, modal factor correctness, rate loading validation
6 External Reference & Regulatory Valuation interest rates vs. mock MAS prescribed limits, mortality rate deviation from CSO 2001

Each check returns a standardised result including: tier, severity, description, affected policy keys, pass/fail status, cross-reference to seeded defects, and remediation guidance.

5. Architecture & Tech Stack

Languages & Runtimes

  • Python 3.6+ — core application and diagnostic engine
  • SQL (SQLite) — database queries and schema
  • HTML / CSS / JavaScript — web UI

Frameworks & Libraries

  • Flask >=2.3.0 — web framework and routing
  • Anthropic SDK >=0.25.0 — Claude API calls (claude-sonnet-4-6) for NL query and AI reports
  • sqlite3 — bundled with Python; no install required
  • Chart.js — client-side charting (loaded from CDN)
  • Jinja2 — server-side template rendering (included with Flask)

Build & Tooling

  • Bash scripts — database build, verification, and run orchestration

6. Database Schema

The mock PAS database (mock_pas/mock_pas.db) contains 15 tables:

# Table Description
1 AGENT Insurance agents (active and terminated)
2 PRODUCT Product definitions (TRM20, TRM30, WL99, END20, END25)
3 MORTALITY_TABLE CSO 2001 mortality rates by age, gender, and smoking status
4 VALUATION_BASIS Actuarial valuation basis definitions
5 SYSTEM_PARAMETER System-wide parameters (modal factors, valuation parameters)
6 POLICY ~250 policy records (statuses: IF, LP, SR, MT, DC, PU; currencies: SGD, MYR)
7 LIFE Life assured and policy owner records
8 BENEFIT Benefit records (death, TPD, accidental death riders)
9 PREMIUM_RATE Premium rates by plan, gender, smoking status, and age
10 SURRENDER_VALUE_FACTOR Surrender value factors by policy duration
11 BONUS_DECLARATION Declared bonus rates by product and year
12 POLICY_BONUS Reversionary and terminal bonuses per policy
13 PREMIUM_HISTORY Premium payment history (paid and outstanding)
14 TRANSACTION Policy transaction records
15 REINSURANCE Reinsurance arrangement records

Seeded Defects

36 defects (D01–D36) are deliberately injected into the database across Tiers 1–6. The ground-truth catalogue is in mock_pas/defect_manifest.json, which documents each defect's ID, tier, affected table/field, and the policy keys impacted.

Reference Data

File Description
mock_pas/reference_data/mortality_cso2001.csv CSO 2001 reference mortality rates (ages 18–75, M/F, smoker/non-smoker)
mock_pas/reference_data/regulatory_params.json Mock MAS prescribed maximum valuation interest rates, minimum surrender value factors, and product entry age limits

7. Getting Started

Prerequisites

  • Python 3.6 or later
  • pip
  • An Anthropic API key — required only for AI features (NL query, AI reports). The tool runs without one in CLI mode and degrades gracefully in web mode.

Installation

# Clone the repository
git clone https://github.com/chadwickkcc/Life-Policy-Admin-System---Mockup-Database-and-Tool---Prototype.git
cd "Life-Policy-Admin-System---Mockup-Database-and-Tool---Prototype"

# Install dependencies
pip install -r mock_pas/requirements.txt

Build the Database

cd mock_pas
./build.sh

This rebuilds mock_pas.db from scratch by running all SQL scripts in order (schema → seed data → 250 policies → 36 seeded defects).

Run — CLI Mode

cd mock_pas
./run_all.sh

Executes all 45 diagnostic checks and generates diagnostic/findings.json and diagnostic/report.html.

Run — Web Mode

cd mock_pas
./start_web.sh

Starts Flask on http://localhost:5001. Enter your Anthropic API key on the home page to enable AI features.

8. Project Structure

.
├── LICENSE
├── README.md
│
├── mock_pas_build_spec.md              # V1 database schema & seeded defects spec
├── mock_pas_build_spec_v2_addendum.md  # V2 defect additions (D25–D30)
├── mock_pas_build_spec_v3_addendum.md  # V3 defect additions (D31–D36)
├── diagnostic_engine_build_spec.md     # V1 diagnostic engine spec (Tiers 1–4, 28 checks)
├── diagnostic_engine_build_spec_v2.md  # V2 Tier 5 check extensions
├── diagnostic_engine_build_spec_v3_addendum.md  # V3 Tier 5/6 additions
├── diagnostic_engine_build_spec_v4_addendum.md  # V4 Flask web interface & AI features
├── reference_data_spec.md              # Reference data definitions
├── policy_admin_erd.html               # Interactive ER diagram
├── mock_pas_erd.png                    # ER diagram (image)
└── mock_pas_export.xlsx                # Sample data export
│
└── mock_pas/                           # Main application directory
    │
    ├── mock_pas.db                     # SQLite database (540 KB, 36 defects applied)
    ├── schema.sql                      # CREATE TABLE statements (15 tables)
    ├── seed_data.sql                   # Reference data (agents, products, parameters)
    ├── policies.sql                    # ~250 clean policy records
    ├── gen_policies.py                 # Generates policies.sql reproducibly
    ├── defects.sql                     # Injects D01–D24
    ├── defects_v2.sql                  # Injects D25–D30
    ├── defects_v3.sql                  # Injects D31–D36
    ├── defect_manifest.json            # Ground-truth defect catalogue (D01–D36)
    ├── manifest_addendum.json          # D25–D30 entries
    ├── manifest_v3_addendum.json       # D31–D36 entries
    ├── build.sh                        # Master build script
    ├── verify_v2.sh                    # Spot-check queries for D25–D30
    ├── verify_v3.sh                    # Spot-check queries for D31–D36
    ├── run_all.sh                      # CLI runner (diagnostics + report)
    ├── start_web.sh                    # Starts Flask on localhost:5001
    ├── app.py                          # Flask application (routes, check runner)
    ├── requirements.txt                # flask>=2.3.0, anthropic>=0.25.0
    │
    ├── diagnostic/
    │   ├── checks.py                   # 45 risk check functions (Tiers 1–6)
    │   ├── run_diagnostics.py          # Orchestrator; outputs findings.json
    │   ├── report.py                   # Generates console + HTML report
    │   ├── nl_query.py                 # Natural-language-to-SQL engine (Anthropic API)
    │   ├── report_ai.py                # AI-powered narrative report generation
    │   ├── draft_checks.py             # Draft check CRUD + Python code generation
    │   ├── findings.json               # Last run results (generated at runtime)
    │   ├── report.html                 # Last run HTML report (generated at runtime)
    │   ├── draft_checks/               # User-created check drafts (JSON, runtime)
    │   ├── templates/                  # Jinja2 templates (base, home, dashboard, etc.)
    │   └── static/                     # CSS and JavaScript (style.css, app.js)
    │
    └── reference_data/
        ├── regulatory_params.json      # Mock MAS prescribed parameters
        ├── mortality_cso2001.csv       # CSO 2001 reference qx values (ages 18–75)
        └── generate_reference_data.py  # Generates mortality_cso2001.csv

9. Version History

Version Key Changes
V1 Base database schema (15 tables), 24 seeded defects (D01–D24), 28 diagnostic checks covering Tiers 1–4, CLI mode, HTML report output
V2 +6 Tier 5 business logic defects (D25–D30), additional Tier 5 checks, defects_v2.sql and verify_v2.sh
V3 +6 Tier 5/6 calculation and regulatory defects (D31–D36), Tier 6 external reference checks (45 checks total), defects_v3.sql and verify_v3.sh
V4 Flask web interface, interactive dashboard with Chart.js, natural-language query engine, AI-powered drill-down interpretation and narrative reports, draft check promotion workflow, 25 bug fixes (tracked in diagnostic_engine_build_spec_v4_addendum.md)

10. Known Limitations & Disclaimer

  • Proof of concept only. This tool has not undergone full testing, security review, or validation. It may contain bugs, inaccuracies, or incomplete implementations.
  • Not for production use. SQLite is not designed for production-scale workloads or concurrent access.
  • AI features require an Anthropic API key. The natural-language query engine and AI report features call the Anthropic API (claude-sonnet-4-6). These features are unavailable without a valid key; the rest of the tool continues to function.
  • Synthetic data only. All policies, persons, agents, and financial figures in this database are entirely fictional. Any resemblance to real entities is coincidental.
  • Not professional advice. The diagnostic outputs do not constitute actuarial, legal, regulatory, or financial advice. Do not use them for real-world decision-making.
  • Use at your own risk. The author accepts no liability for any loss or damage arising from use of this software. See LICENSE for full terms.

11. License

This project is not open source. The source code is made available for educational reference only. Commercial use, redistribution, and modification are not permitted.

See LICENSE for full terms.

For enquiries: chadwickkcc@gmail.com