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.
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.
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.
| 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 |
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.
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
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 |
- 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.
# 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.txtcd mock_pas
./build.shThis rebuilds mock_pas.db from scratch by running all SQL scripts in order (schema → seed data → 250 policies → 36 seeded defects).
cd mock_pas
./run_all.shExecutes all 45 diagnostic checks and generates diagnostic/findings.json and diagnostic/report.html.
cd mock_pas
./start_web.shStarts Flask on http://localhost:5001. Enter your Anthropic API key on the home page to enable AI features.
.
├── 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
| 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) |
- 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.
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