Skip to content

diagnostic_engine_build_spec.md

Latest commit

 

History

History
895 lines (754 loc) · 36.1 KB

diagnostic_engine_build_spec.md

File metadata and controls

895 lines (754 loc) · 36.1 KB

Diagnostic Engine — Build Specification

For Claude Code: Python-based Risk Diagnostic Tool for mock_pas.db

Build Status

Item Detail
Status Built and verified — V1 + V2 + V3 + V4 extensions complete
Build date 2026-04-22 (V3 complete); 2026-04-24 (V4 complete); 2026-04-30 (V4 bug fixes — see v4 addendum)
Manifest coverage 36 / 36 defects detected (100%)
Checks implemented 45 / 45 (Tiers 1–6)
Python requirement 3.6 or later (stdlib + sqlite3 only — no pip installs for CLI; flask + anthropic for V4 web interface)

V1 covers Tiers 1–4 (28 checks, D01–D24). V2 adds Tier 5 business logic (10 checks, D25–D30) — see diagnostic_engine_build_spec_v2.md. V3 adds Tier 5 calculation + Tier 6 external reference (7 checks, D31–D36) — see diagnostic_engine_build_spec_v3_addendum.md. V4 adds a Flask web application with AI-powered NL query interface, interactive dashboard, and AI narrative report — see diagnostic_engine_build_spec_v4_addendum.md.

Known deviations from original V1 spec

Three deviations were identified and resolved during V1 build. See the relevant check entries in Section 3.2 for full detail.

Check Deviation Status
CHK_T3_06 TRANSACTION table contains only TXN_TYPE='PREM' — no SURR/CLAIM/MATURITY records exist. Substituted a payment-gap approach (>730 days between consecutive payments on SR/DC/MT policies). Fully documented in code. Necessary adaptation — detects D18 correctly
CHK_T3_04 Original spec said return both rate records per inversion pair; initial build returned only one. Fixed to return both via UNION. Fixed
CHK_T4_02 Original spec said group by PLAN_CODE and report count per plan; initial build returned individual RATE_IDs. Fixed to return plan codes with counts. Fixed

Known deviations from V3 spec

Check Deviation Status
CHK_T5_11 Detection threshold is 0.95 (spec says 0.90). 1/1.08 ≈ 0.926, which is above 0.90 — the spec threshold is mathematically impossible to trigger with an 8% modal loading omission. Raised to 0.95 to correctly detect the defect. Necessary adaptation
CHK_T5_15 Spec testing checklist said passes=True; check correctly fails with 8 records because the cumulative bonus check catches the same D30 unit-error. D30 is detected redundantly by both CHK_T5_06 and CHK_T5_15. Expected behaviour — manifest coverage unaffected

Minor cosmetic differences (console output only — no functional impact)

Location Spec As built
Progress lines (1 issue) / (0 issues) (1 issue(s))
Passed checks row — no issues ✓ no issues
Findings label Seeded defect: Seeded defect(s):

1. Overview

Build a Python diagnostic tool that connects to mock_pas.db, runs a library of SQL-based risk checks organised across four tiers, and produces both a structured findings file and a human-readable report.

The tool must:

  • Identify all 24 seeded defects catalogued in defect_manifest.json
  • Run generalised checks that would catch similar issues beyond the seeded defects
  • Produce a scorecard comparing findings against the manifest (ground-truth validation)
  • Require no external dependencies beyond Python standard library and sqlite3 (which is bundled with Python — no pip installs needed)

2. File Structure to Produce

mock_pas/
├── mock_pas.db                  # Existing — do not modify
├── defect_manifest.json         # Existing — read-only input
├── requirements.txt             # V4: flask>=2.3.0, anthropic>=0.25.0
├── app.py                       # V4: Flask web application entry point
├── start_web.sh                 # V4: convenience start script (defaults to port 5001)
├── run_all.sh                   # Shell script: runs run_diagnostics.py then report.py (CLI)
├── diagnostic/
│   ├── checks.py                # Check function library (one function per check)
│   ├── run_diagnostics.py       # Runner: executes all checks, writes findings.json
│   ├── report.py                # Reporter: reads findings.json, prints formatted report
│   ├── findings.json            # Output: generated by run_diagnostics.py
│   ├── report.html              # Output: generated by report.py
│   ├── nl_query.py              # V4: NL→SQL engine (Anthropic API)
│   ├── report_ai.py             # V4: AI narrative report generator (Anthropic API)
│   ├── draft_checks.py          # V4: draft check CRUD and Python codegen
│   ├── draft_checks/            # V4: runtime-generated draft .json files
│   ├── templates/               # V4: Jinja2 HTML templates
│   │   ├── base.html
│   │   ├── home.html
│   │   ├── select_checks.html
│   │   ├── dashboard.html
│   │   ├── nl_query.html
│   │   ├── review_checks.html
│   │   └── coming_soon.html     # placeholder for future pages
│   └── static/                  # V4: CSS and JS
│       ├── style.css
│       └── app.js
└── reference_data/
    ├── regulatory_params.json
    ├── mortality_cso2001.csv
    └── generate_reference_data.py

3. Component 1 — checks.py

This file contains all check functions. It is a pure library — it does not run anything on import. The runner (run_diagnostics.py) imports and calls these functions.

3.1 General structure of every check function

Every check function must follow this exact signature and return format:

def check_<name>(conn: sqlite3.Connection) -> dict:
    """
    One-sentence description of what this check detects.
    Tier: <1|2|3|4>
    Risk category: <Data Integrity|Structural|Actuarial Logic|Governance>
    Seeded defects covered: <D01, D04> or "None — generalised check only"
    """
    cursor = conn.cursor()

    # --- SQL query ---
    cursor.execute("""
        SELECT ...
        FROM ...
        WHERE ...
    """)
    rows = cursor.fetchall()

    affected_keys = [str(row[0]) for row in rows]

    return {
        "check_id":       "CHK_<ID>",          # e.g. "CHK_T1_01"
        "tier":           1,                    # integer 1–4
        "tier_label":     "Data Integrity",     # matches tier number
        "severity":       "HIGH",               # HIGH / MEDIUM / LOW
        "table":          "POLICY",             # primary table checked
        "field":          "SUM_ASSURED",        # primary field checked (or "MULTIPLE")
        "check_name":     "Null sum assured on inforce policy",
        "description":    "One to two sentences explaining what was found and why it matters.",
        "affected_count": len(affected_keys),
        "affected_keys":  affected_keys[:20],   # cap display at 20 records
        "passed":         len(affected_keys) == 0,
        "seeded_defects": ["D01"],              # list of manifest IDs this covers
        "remediation":    "One sentence on how to fix."
    }

Rules:

  • passed is True only when affected_count == 0 (no issues found)
  • affected_keys should be the primary key of the affected table (POLICY_NO, RATE_ID, etc.)
  • Cap affected_keys at 20 items in the return value to keep findings.json readable
  • Every function must handle SQL exceptions gracefully: wrap the execute in try/except, return "passed": False and "affected_count": -1 with an error description if the query fails — this itself is a signal the schema may be malformed

3.2 Full list of check functions to implement

Implement all 28 V1 checks below. V2 addendum (diagnostic_engine_build_spec_v2.md) adds 10 Tier 5 checks (CHK_T5_01–T5_10) for a total of 38. V3 addendum (diagnostic_engine_build_spec_v3_addendum.md) adds 7 more checks (CHK_T5_11–T5_15, CHK_T6_01–T6_02) for a final total of 45. See §3.3 for the complete as-built registry.

Checks marked [SEEDED] directly target one or more seeded defects. Checks marked [GENERAL] are generalised — they catch whole categories of issues and will find the seeded defect as one instance among potentially many. Most checks should be GENERAL, using the seeded defect as a test case.

TIER 1 — Data Integrity Checks

CHK_T1_01 — Null or zero sum assured on active policies [SEEDED: D01, D04]

Detect: POLICY records where STATUS_CODE = 'IF' AND (SUM_ASSURED IS NULL OR SUM_ASSURED <= 0)
Table: POLICY | Field: SUM_ASSURED | Severity: HIGH

CHK_T1_02 — Out-of-range premium rate values [SEEDED: D02]

Detect: PREMIUM_RATE records where RATE_PER_1000 <= 0 OR RATE_PER_1000 > 5000
Table: PREMIUM_RATE | Field: RATE_PER_1000 | Severity: HIGH
Note: 5000 per 1000 SA = 500% — no realistic product exceeds this

CHK_T1_03 — Impossible date of birth [SEEDED: D03]

Detect: LIFE records where DOB > DATE('now') OR DOB < '1900-01-01'
Table: LIFE | Field: DOB | Severity: HIGH

CHK_T1_04 — Spreadsheet error artifacts in text fields [SEEDED: D05]

Detect: LIFE records where any of ID_NO, FULL_NAME contain '#VALUE!', '#N/A',
        '#REF!', '#DIV/0!', '#NUM!', '#NAME?', '#NULL!'
Also check: SYSTEM_PARAMETER.PARAM_VALUE for same patterns
Table: LIFE, SYSTEM_PARAMETER | Field: MULTIPLE | Severity: MEDIUM
Use: WHERE field LIKE '#%'

CHK_T1_05 — Null mandatory fields across core tables [SEEDED: D06]

Detect: Run NULL checks on the following NOT-logically-nullable fields:
  POLICY: POLICY_NO, PLAN_CODE, STATUS_CODE, ISSUE_DATE, CURRENCY_CODE, AGE_CALC_METHOD
  LIFE:   POLICY_NO, LIFE_TYPE, FULL_NAME
  BENEFIT: POLICY_NO, BENEFIT_TYPE, STATUS
  PREMIUM_RATE: PLAN_CODE, GENDER, SMOKING_STATUS, RATE_PER_1000, EFFECTIVE_DATE
Return one result row per table/field combination that has any NULLs.
Table: MULTIPLE | Field: MULTIPLE | Severity: HIGH

CHK_T1_06 — Negative or zero benefit amounts on active benefits

Detect: BENEFIT records where STATUS = 'ACTIVE' AND (BENEFIT_AMOUNT IS NULL
        OR BENEFIT_AMOUNT <= 0)
Table: BENEFIT | Field: BENEFIT_AMOUNT | Severity: HIGH
Note: generalised check — no specific seeded defect, but a realistic risk

TIER 2 — Structural / Referential Integrity Checks

CHK_T2_01 — Orphaned policy records (invalid plan code) [SEEDED: D07]

Detect: POLICY records where PLAN_CODE NOT IN (SELECT PLAN_CODE FROM PRODUCT)
Table: POLICY | Field: PLAN_CODE | Severity: HIGH

CHK_T2_02 — Multiple life assured records on same policy [SEEDED: D08]

Detect: POLICY_NO values in LIFE where COUNT of LIFE_TYPE = 'LA' > 1
SQL: SELECT POLICY_NO, COUNT(*) as cnt FROM LIFE
     WHERE LIFE_TYPE = 'LA' GROUP BY POLICY_NO HAVING cnt > 1
Table: LIFE | Field: LIFE_TYPE | Severity: MEDIUM

CHK_T2_03 — Overlapping effective date ranges in premium rate table [SEEDED: D09]

Detect: PREMIUM_RATE records for the same (PLAN_CODE, GENDER, SMOKING_STATUS, ISSUE_AGE)
        combination where date ranges overlap.
SQL approach: self-join on matching key columns where:
  a.RATE_ID < b.RATE_ID
  AND a.EFFECTIVE_DATE <= COALESCE(b.EXPIRY_DATE, '9999-12-31')
  AND COALESCE(a.EXPIRY_DATE, '9999-12-31') >= b.EFFECTIVE_DATE
Return: both RATE_ID values via UNION (one SELECT for a.RATE_ID, one for b.RATE_ID)
Table: PREMIUM_RATE | Field: EFFECTIVE_DATE / EXPIRY_DATE | Severity: MEDIUM

CHK_T2_04 — Bonus records on non-participating policies [SEEDED: D10]

Detect: POLICY_BONUS records linked to policies on non-participating products
SQL: SELECT pb.* FROM POLICY_BONUS pb
     JOIN POLICY p ON pb.POLICY_NO = p.POLICY_NO
     JOIN PRODUCT pr ON p.PLAN_CODE = pr.PLAN_CODE
     WHERE pr.PARTICIPATING_FLAG = 'N'
Table: POLICY_BONUS | Field: POLICY_NO | Severity: MEDIUM

CHK_T2_05 — Orphaned benefit records (non-existent policy) [SEEDED: D11]

Detect: BENEFIT records where POLICY_NO NOT IN (SELECT POLICY_NO FROM POLICY)
Table: BENEFIT | Field: POLICY_NO | Severity: LOW

CHK_T2_06 — Policy sold by terminated agent [SEEDED: D12]

Detect: POLICY records where AGENT_CODE references a terminated agent AND
        ISSUE_DATE > AGENT.TERMINATION_DATE
SQL: SELECT p.POLICY_NO, p.ISSUE_DATE, a.AGENT_CODE, a.TERMINATION_DATE
     FROM POLICY p JOIN AGENT a ON p.AGENT_CODE = a.AGENT_CODE
     WHERE a.STATUS = 'TERMINATED'
       AND a.TERMINATION_DATE IS NOT NULL
       AND p.ISSUE_DATE > a.TERMINATION_DATE
Table: POLICY | Field: AGENT_CODE | Severity: LOW

CHK_T2_07 — Policies with no life assured record

Detect: POLICY_NO values in POLICY with no corresponding LIFE record
        where LIFE_TYPE = 'LA'
SQL: SELECT p.POLICY_NO FROM POLICY p
     LEFT JOIN LIFE l ON p.POLICY_NO = l.POLICY_NO AND l.LIFE_TYPE = 'LA'
     WHERE l.LIFE_ID IS NULL
Table: POLICY | Field: POLICY_NO | Severity: HIGH
Note: Every policy must have exactly one life assured — this is a critical gap

CHK_T2_08 — Policies with no benefit record

Detect: POLICY_NO values in POLICY with STATUS_CODE = 'IF' and no BENEFIT records
Table: POLICY | Field: POLICY_NO | Severity: HIGH
Note: An inforce policy with no defined benefits cannot be administered

TIER 3 — Actuarial / Business Logic Checks

CHK_T3_01 — Lapsed policy with future next premium due date [SEEDED: D13]

Detect: POLICY records where STATUS_CODE = 'LP'
        AND NEXT_PREM_DUE_DATE > DATE('now')
Table: POLICY | Field: NEXT_PREM_DUE_DATE | Severity: HIGH

CHK_T3_02 — Issue date after maturity date [SEEDED: D14]

Detect: POLICY records where MATURITY_DATE IS NOT NULL
        AND ISSUE_DATE > MATURITY_DATE
Table: POLICY | Field: ISSUE_DATE / MATURITY_DATE | Severity: HIGH

CHK_T3_03 — Gaps in premium rate table by age [SEEDED: D15]

Detect: For each (PLAN_CODE, GENDER, SMOKING_STATUS) combination, find missing
        integer ages in the sequence between MIN(ISSUE_AGE) and MAX(ISSUE_AGE).
SQL approach:
  1. Get distinct age series per combination
  2. Use a WITH RECURSIVE CTE to generate expected integer sequence
  3. LEFT JOIN to find gaps
Return: PLAN_CODE, GENDER, SMOKING_STATUS, and list of missing ages
Table: PREMIUM_RATE | Field: ISSUE_AGE | Severity: MEDIUM

CHK_T3_04 — Non-monotonic premium rates with age (term products) [SEEDED: D16]

Detect: For TERM products (PRODUCT_TYPE = 'TERM'), find PREMIUM_RATE records where
        the rate at age N is lower than the rate at age N-1 for the same combination.
SQL approach: self-join PREMIUM_RATE on same PLAN_CODE/GENDER/SMOKING_STATUS
        where b.ISSUE_AGE = a.ISSUE_AGE + 1 AND b.RATE_PER_1000 < a.RATE_PER_1000
Return: Both rate records in each inversion pair via UNION
        (one SELECT for a.RATE_ID at age N, one for b.RATE_ID at age N+1)
Table: PREMIUM_RATE | Field: RATE_PER_1000 | Severity: HIGH

CHK_T3_05 — Policy issued outside product entry age limits [SEEDED: D17]

Detect: POLICY records where ISSUE_AGE < PRODUCT.MIN_ENTRY_AGE
        OR ISSUE_AGE > PRODUCT.MAX_ENTRY_AGE
SQL: JOIN POLICY to PRODUCT on PLAN_CODE
Table: POLICY | Field: ISSUE_AGE | Severity: HIGH

CHK_T3_06 — Premium collected after policy exit date [SEEDED: D18]

Detect: PREMIUM_HISTORY records with STATUS = 'PAID' where the policy
        STATUS_CODE IN ('SR','DC','MT') and PAID_DATE is anomalously late
        relative to the prior premium payment history for that policy.

IMPORTANT — as-built deviation from original spec:
  The original spec called for joining to the TRANSACTION table on
  TXN_TYPE IN ('SURR','CLAIM','MATURITY') to obtain the exit date.
  In mock_pas.db, the TRANSACTION table contains only TXN_TYPE = 'PREM'
  records — no surrender, claim, or maturity transaction records were seeded.
  Following the spec literally would make this check permanently return zero results.

  Substituted approach: for SR/DC/MT policies, flag any PAID premium whose
  PAID_DATE is more than 730 days after the immediately preceding PAID premium
  for the same policy. This reliably detects D18 (a 2019 payment on a policy
  whose last normal payment was 2012) and the logic is documented in the code.

  If the TRANSACTION table is later populated with proper exit-type records,
  the original spec approach should be reinstated:
    1. Identify exit transaction date per policy from TRANSACTION table
       (TXN_TYPE IN ('SURR','CLAIM','MATURITY'), STATUS = 'POSTED')
    2. Join to PREMIUM_HISTORY to find PAID premiums after that date

Table: PREMIUM_HISTORY | Field: PAID_DATE | Severity: MEDIUM

CHK_T3_07 — Surrender value factor exceeding 1.0 [SEEDED: D19]

Detect: SURRENDER_VALUE_FACTOR records where SV_FACTOR > 1.0
Also flag: SV_FACTOR < 0
Table: SURRENDER_VALUE_FACTOR | Field: SV_FACTOR | Severity: MEDIUM

CHK_T3_08 — Valuation basis referencing expired mortality table [SEEDED: D20]

Detect: VALUATION_BASIS records where EXPIRY_DATE IS NULL (i.e. currently active)
        but the referenced MORT_TABLE_CODE has an EXPIRY_DATE < DATE('now')
SQL: SELECT vb.BASIS_CODE, vb.MORT_TABLE_CODE, mt.EXPIRY_DATE
     FROM VALUATION_BASIS vb
     JOIN MORTALITY_TABLE mt ON vb.MORT_TABLE_CODE = mt.TABLE_CODE
     WHERE (vb.EXPIRY_DATE IS NULL OR vb.EXPIRY_DATE >= DATE('now'))
       AND mt.EXPIRY_DATE < DATE('now')
     GROUP BY vb.BASIS_CODE
Table: VALUATION_BASIS | Field: MORT_TABLE_CODE | Severity: HIGH

CHK_T3_09 — Issue age inconsistent with date of birth and issue date

Detect: POLICY records where the stored ISSUE_AGE materially differs from the
        age derivable from LIFE.DOB and POLICY.ISSUE_DATE.
        For ANB: expected age = year(ISSUE_DATE) - year(DOB) + 1 adjustment
        For ALB: expected age = floor((ISSUE_DATE - DOB) / 365.25)
        Flag where |stored_age - calculated_age| > 1
SQL: JOIN POLICY to LIFE (LIFE_TYPE='LA'), compute age, compare to ISSUE_AGE
Table: POLICY | Field: ISSUE_AGE | Severity: MEDIUM

TIER 4 — Governance / Audit Checks

CHK_T4_01 — Stale valuation parameters (not updated in 5+ years) [SEEDED: D21]

Detect: SYSTEM_PARAMETER records where PARAM_CATEGORY = 'VALUATION'
        AND (LAST_UPD_DATE IS NULL
             OR LAST_UPD_DATE < DATE('now', '-5 years'))
Table: SYSTEM_PARAMETER | Field: LAST_UPD_DATE | Severity: HIGH

CHK_T4_02 — Premium rate records with no expiry date [SEEDED: D22]

Detect: PREMIUM_RATE records where EXPIRY_DATE IS NULL, grouped by PLAN_CODE.
SQL: SELECT PLAN_CODE, COUNT(*) AS open_rate_count
     FROM PREMIUM_RATE
     WHERE EXPIRY_DATE IS NULL
     GROUP BY PLAN_CODE
     ORDER BY PLAN_CODE
Return: affected_keys are PLAN_CODE strings with counts e.g. "WL99 (52 records)".
        affected_count is the number of distinct plan codes affected (not total records).
        The description also reports the total record count across all plans.
Table: PREMIUM_RATE | Field: EXPIRY_DATE | Severity: MEDIUM

CHK_T4_03 — System parameter type mismatch [SEEDED: D23]

Detect: SYSTEM_PARAMETER records where PARAM_TYPE = 'NUMERIC'
        but PARAM_VALUE cannot be cast to a real number.
SQL approach: Use CASE WHEN CAST(PARAM_VALUE AS REAL) IS NULL
              AND PARAM_TYPE = 'NUMERIC' THEN 1 ELSE 0 END
        Note: In SQLite, CAST of a non-numeric string returns 0, not NULL.
        Use: WHERE PARAM_TYPE = 'NUMERIC'
               AND PARAM_VALUE NOT GLOB '[0-9]*'
               AND PARAM_VALUE NOT GLOB '-[0-9]*'
               AND PARAM_VALUE NOT GLOB '[0-9]*.[0-9]*'
               AND PARAM_VALUE != '0'
        Note: '-[0-9]*' glob added (beyond original spec) to correctly pass
              negative numeric values such as '-1.5'.
Table: SYSTEM_PARAMETER | Field: PARAM_VALUE | Severity: LOW

CHK_T4_04 — Overlapping effective date ranges in valuation basis [SEEDED: D24]

Detect: VALUATION_BASIS records where date ranges overlap.
SQL approach: self-join on all rows where:
  a.BASIS_CODE < b.BASIS_CODE (avoid duplicate pairs)
  AND a.EFFECTIVE_DATE <= COALESCE(b.EXPIRY_DATE, '9999-12-31')
  AND COALESCE(a.EXPIRY_DATE, '9999-12-31') >= b.EFFECTIVE_DATE
Return: both BASIS_CODE values via UNION
Table: VALUATION_BASIS | Field: EFFECTIVE_DATE / EXPIRY_DATE | Severity: HIGH

CHK_T4_05 — Parameters with no effective date (unversioned hardcodes)

Detect: SYSTEM_PARAMETER records where EFFECTIVE_DATE IS NULL
        AND EXPIRY_DATE IS NULL. These are floating parameters with no
        temporal scope — a classic legacy system governance failure.
Table: SYSTEM_PARAMETER | Field: EFFECTIVE_DATE | Severity: MEDIUM

3.3 Helper utilities to include in checks.py

The following utilities reflect the as-built V3 final state (45 checks, Tiers 1–6). The V1 checks listed in §3.2 were extended by the V2 and V3 addendum specifications.

import os, json, csv

def get_db_connection(db_path: str) -> sqlite3.Connection:
    """Return a sqlite3 connection with row_factory set to sqlite3.Row."""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn

def load_reference_data(ref_dir: str) -> dict:
    """
    Load external reference data from the reference_data/ directory.
    Returns a dict with keys 'regulatory' and 'mortality_cso2001'.
    Raises FileNotFoundError with a clear message if either file is missing.
    Required for Tier 6 checks (CHK_T6_01, CHK_T6_02).
    """
    reg_path = os.path.join(ref_dir, 'regulatory_params.json')
    mort_path = os.path.join(ref_dir, 'mortality_cso2001.csv')
    if not os.path.exists(reg_path):
        raise FileNotFoundError(f"Regulatory params not found: {reg_path}")
    if not os.path.exists(mort_path):
        raise FileNotFoundError(f"Mortality reference table not found: {mort_path}")
    with open(reg_path) as f:
        regulatory = json.load(f)
    mortality = {}
    with open(mort_path, newline='') as f:
        for row in csv.DictReader(f):
            key = (row['GENDER'], int(row['AGE']), row['SMOKING_STATUS'])
            mortality[key] = float(row['QX_REFERENCE'])
    return {"regulatory": regulatory, "mortality_cso2001": mortality}

def get_all_checks() -> list:
    """Return an ordered list of all 45 check functions (Tiers 1–6)."""
    return [
        # Tier 1 — Data Integrity (6 checks)
        check_t1_01_null_sum_assured,
        check_t1_02_invalid_premium_rate,
        check_t1_03_impossible_dob,
        check_t1_04_spreadsheet_artifacts,
        check_t1_05_null_mandatory_fields,
        check_t1_06_invalid_benefit_amounts,
        # Tier 2 — Structural (8 checks)
        check_t2_01_orphaned_plan_code,
        check_t2_02_duplicate_life_assured,
        check_t2_03_overlapping_rate_dates,
        check_t2_04_bonus_on_nonpar_policy,
        check_t2_05_orphaned_benefits,
        check_t2_06_terminated_agent,
        check_t2_07_no_life_assured,
        check_t2_08_no_benefit_record,
        # Tier 3 — Actuarial Logic (9 checks)
        check_t3_01_lapsed_future_prem_due,
        check_t3_02_issue_after_maturity,
        check_t3_03_rate_table_age_gaps,
        check_t3_04_nonmonotonic_rates,
        check_t3_05_issue_age_outside_limits,
        check_t3_06_premium_after_exit,
        check_t3_07_sv_factor_exceeds_one,
        check_t3_08_expired_mortality_table,
        check_t3_09_issue_age_inconsistent,
        # Tier 4 — Governance (5 checks)
        check_t4_01_stale_valuation_params,
        check_t4_02_rate_no_expiry,
        check_t4_03_param_type_mismatch,
        check_t4_04_overlapping_val_basis,
        check_t4_05_unversioned_parameters,
        # Tier 5 — Business Logic / Calculation (15 checks)
        # CHK_T5_01–T5_10 added by V2 addendum; CHK_T5_11–T5_15 added by V3 addendum
        check_t5_01_smoker_rate_below_ns,
        check_t5_02_ri_sar_exceeds_sa,
        check_t5_03_policy_term_date_mismatch,
        check_t5_04_ppt_exceeds_policy_term,
        check_t5_05_bonus_year_exceeds_duration,
        check_t5_06_bonus_amount_inconsistent,
        check_t5_07_female_rate_above_male,
        check_t5_08_ri_premium_implausible,
        check_t5_09_par_policy_no_bonus,
        check_t5_10_premium_below_rate_table,
        check_t5_11_modal_premium_no_loading,
        check_t5_12_substandard_no_extra_loading,
        check_t5_13_nonpar_death_benefit_exceeds_sa,
        check_t5_14_endowment_maturity_below_sa,
        check_t5_15_cumulative_bonus_inconsistent,
        # Tier 6 — External Reference / Regulatory (2 checks)
        # Added by V3 addendum; gracefully skip when --ref-data not provided
        check_t6_01_val_rate_exceeds_regulatory,
        check_t6_02_qx_deviation_from_reference,
    ]

4. Component 2 — run_diagnostics.py

This is the entry-point script. It imports checks.py, runs every check function, and writes all results to findings.json.

4.1 Full script logic

"""
run_diagnostics.py
Usage: python run_diagnostics.py [--db path/to/mock_pas.db] [--manifest path/to/defect_manifest.json]
Defaults: looks for mock_pas.db and defect_manifest.json in the parent directory.
"""

The script must:

  1. Parse arguments — accept --db and --manifest flags with sensible defaults (look one directory up from the diagnostic/ folder).

  2. Load the manifest — read defect_manifest.json and build a lookup dict keyed by defect_id so findings can be cross-referenced.

  3. Run all checks — iterate over get_all_checks(), call each function with the connection, collect results. Print a one-line progress indicator for each check:

    [PASS] CHK_T1_01 — Null sum assured on inforce policy (0 issues)
[FAIL] CHK_T1_02 — Out-of-range premium rate values (1 issue)

  4. Compute manifest coverage — for every finding where passed = False, look up which seeded defect IDs the check covers. Build a set of discovered defect IDs and compare against the full manifest. Report:

    Manifest coverage: 36 / 36 defects detected
Missed defects:    (none)

  5. Write findings.json with the structure below.

4.2 findings.json structure

{
  "run_timestamp": "2026-04-22T14:32:00",
  "database": "mock_pas.db",
  "total_checks": 45,
  "checks_passed": 16,
  "checks_failed": 29,
  "manifest_total": 36,
  "manifest_detected": 36,
  "manifest_missed": [],
  "findings": [
    {
      "check_id": "CHK_T1_01",
      "tier": 1,
      "tier_label": "Data Integrity",
      "severity": "HIGH",
      "table": "POLICY",
      "field": "SUM_ASSURED",
      "check_name": "Null sum assured on inforce policy",
      "description": "1 inforce policy has a NULL sum assured. This makes the policy
                       unadministrable — no claim, surrender, or valuation can proceed.",
      "affected_count": 1,
      "affected_keys": ["SG01-2010-000042"],
      "passed": false,
      "seeded_defects": ["D01"],
      "remediation": "Recover SUM_ASSURED from original policy documents or TRANSACTION history."
    }
  ]
}

5. Component 3 — report.py

Reads findings.json and produces two outputs: a formatted console report and report.html for browser viewing.

5.1 Console report format

The following example reflects the V3 final state (45 checks, 36 seeded defects, Tiers 1–6). The V1 spec originally showed 28 checks and 4 tiers; V2 and V3 addenda extended those totals.

============================================================
  POLICY ADMINISTRATION SYSTEM — DIAGNOSTIC REPORT
  Database:   mock_pas.db
  Run date:   2026-04-22 14:32:00
  Checks run: 45  |  Passed: 16  |  Failed: 29
============================================================

MANIFEST COVERAGE
  Seeded defects detected:  36 / 36  (100.0%)
  Undetected defect IDs:    (none)

------------------------------------------------------------
SUMMARY BY TIER
------------------------------------------------------------
  Tier 1 — Data Integrity          6 checks   3 failed   3 passed
  Tier 2 — Structural              8 checks   6 failed   2 passed
  Tier 3 — Actuarial Logic         9 checks   6 failed   3 passed
  Tier 4 — Governance              5 checks   4 failed   1 passed
  Tier 5 — Business Logic         15 checks   8 failed   7 passed
  Tier 6 — External Reference      2 checks   2 failed   0 passed

TIER LEGEND
  Tier 5 findings: teal left border in HTML report
  Tier 6 findings: purple left border in HTML report

SUMMARY BY SEVERITY
  HIGH    findings: 23
  MEDIUM  findings:  5
  LOW     findings:  1

------------------------------------------------------------
FINDINGS (failed checks only, sorted by Tier then Severity)
------------------------------------------------------------

[HIGH]  CHK_T1_01 | Tier 1 | POLICY.SUM_ASSURED
  Null or zero sum assured on active policies
  2 policies affected: SG01-2010-000042, SG01-2012-000031
  An inforce policy cannot be administered without a sum assured.
  Remediation: Recover from policy documents or TRANSACTION history.
  Seeded defect(s): D01, D04

[HIGH]  CHK_T1_02 | Tier 1 | PREMIUM_RATE.RATE_PER_1000
  Out-of-range premium rate value
  1 rate record affected: RATE_ID 74
  A negative rate (-999) cannot produce a valid premium.
  Remediation: Replace with correct rate from product pricing schedule.
  Seeded defect(s): D02

  ... (continue for all failed checks)

------------------------------------------------------------
PASSED CHECKS
------------------------------------------------------------
  CHK_T1_06  Negative benefit amounts on active benefits      ✓ no issues
  CHK_T5_07  Female rate above male rate                      ✓ no issues
  CHK_T5_09  Par policy in force >2 years with no bonus       ✓ no issues
  CHK_T5_10  Annual premium below rate table                  ✓ no issues
  ... (list all passed checks in one compact block)

============================================================
  END OF REPORT
============================================================

5.2 HTML report

Generate report.html as a self-contained single file (no external CSS or JS dependencies). Include:

  • A header bar showing database name, run date, and overall pass/fail count
  • A summary scorecard table: one row per tier, columns for checks run / failed / passed
  • A severity badge count: HIGH / MEDIUM / LOW counts prominently displayed
  • A manifest coverage section: X / 36 defects found, list any missed
  • A findings section: one card per failed check, colour-coded by severity (red border = HIGH, amber = MEDIUM, grey = LOW) Additional left-accent by tier: teal (#0d9e75) for Tier 5, purple (#6f42c1) for Tier 6
  • A tier accent legend in the report header explaining teal = Tier 5, purple = Tier 6
  • A passed checks section: compact table listing check ID and name

Use only inline CSS. No Bootstrap, no external fonts, no JavaScript. Colours: HIGH = #dc3545 border, MEDIUM = #fd7e14 border, LOW = #6c757d border. Background: white. Font: system-ui, sans-serif. Keep it clean and printable.

6. run_all.sh

#!/bin/bash
set -e
cd "$(dirname "$0")"

echo "Running diagnostic checks..."
python3 diagnostic/run_diagnostics.py --db mock_pas.db --manifest defect_manifest.json

echo ""
echo "Generating report..."
python3 diagnostic/report.py --findings diagnostic/findings.json

echo ""
echo "Done. Open diagnostic/report.html to view the full report."

7. Detailed Implementation Notes for Claude Code

7.1 SQLite connection handling

  • Always use conn.row_factory = sqlite3.Row so rows behave like dicts
  • Never close the connection inside a check function — let the runner manage it
  • Wrap every cursor.execute in try/except sqlite3.Error; return a failed result with description "SQL error: {e}" rather than raising — the runner should continue even if individual checks fail
  • IMPORTANT: TRANSACTION is a reserved keyword in SQLite. Always quote it as "TRANSACTION" in SQL strings when referencing the TRANSACTION table.

7.2 The age gap check (CHK_T3_03) — implementation guide

This is the most complex check. Use a WITH RECURSIVE CTE:

WITH RECURSIVE age_series(plan_code, gender, smoking_status, age) AS (
    SELECT plan_code, gender, smoking_status, MIN(issue_age)
    FROM premium_rate
    GROUP BY plan_code, gender, smoking_status

    UNION ALL

    SELECT a.plan_code, a.gender, a.smoking_status, a.age + 1
    FROM age_series a
    JOIN (
        SELECT plan_code, gender, smoking_status, MAX(issue_age) as max_age
        FROM premium_rate
        GROUP BY plan_code, gender, smoking_status
    ) m ON a.plan_code = m.plan_code
         AND a.gender = m.gender
         AND a.smoking_status = m.smoking_status
    WHERE a.age < m.max_age
)
SELECT a.plan_code, a.gender, a.smoking_status, a.age AS missing_age
FROM age_series a
LEFT JOIN premium_rate r ON a.plan_code = r.plan_code
    AND a.gender = r.gender
    AND a.smoking_status = r.smoking_status
    AND a.age = r.issue_age
WHERE r.rate_id IS NULL;

Return affected_keys as "PLAN_CODE/GENDER/SMOKING_STATUS/AGE" composite strings.

7.3 The issue age consistency check (CHK_T3_09) — implementation guide

SQLite date arithmetic uses julianday():

SELECT p.policy_no,
       p.issue_age AS stored_age,
       p.age_calc_method,
       l.dob,
       p.issue_date,
       CASE p.age_calc_method
           WHEN 'ANB' THEN
               CAST((julianday(p.issue_date) - julianday(l.dob)) / 365.25 AS INTEGER) + 1
           WHEN 'ALB' THEN
               CAST((julianday(p.issue_date) - julianday(l.dob)) / 365.25 AS INTEGER)
           END AS calculated_age
FROM policy p
JOIN life l ON p.policy_no = l.policy_no AND l.life_type = 'LA'
WHERE l.dob IS NOT NULL
  AND p.issue_age IS NOT NULL
  AND ABS(p.issue_age -
      CASE p.age_calc_method
          WHEN 'ANB' THEN CAST((julianday(p.issue_date) - julianday(l.dob)) / 365.25 AS INTEGER) + 1
          WHEN 'ALB' THEN CAST((julianday(p.issue_date) - julianday(l.dob)) / 365.25 AS INTEGER)
      END) > 1;

7.4 Manifest cross-referencing in run_diagnostics.py

Build the mapping at startup:

with open(manifest_path) as f:
    manifest = json.load(f)

# Build: defect_id -> defect metadata
manifest_lookup = {d["defect_id"]: d for d in manifest["defects"]}

# Build: check_id -> list of seeded defect IDs it covers
# (derived from the "seeded_defects" field in each check's return dict)

After all checks run, determine coverage:

detected_defect_ids = set()
for result in all_results:
    if not result["passed"]:
        for did in result.get("seeded_defects", []):
            detected_defect_ids.add(did)

all_defect_ids = set(manifest_lookup.keys())
missed = all_defect_ids - detected_defect_ids
coverage_pct = len(detected_defect_ids) / len(all_defect_ids) * 100

7.5 Generalisation principle

Every check should be written as a general rule, not a lookup of a known bad key. Correct pattern:

# CORRECT — general check
WHERE STATUS_CODE = 'IF' AND SUM_ASSURED IS NULL

Incorrect pattern:

# WRONG — hardcoded to known defect key
WHERE POLICY_NO = 'SG01-2010-000042'

The seeded defect should be discovered as a natural result of the general rule — not targeted directly. This is what makes the tool genuinely useful on a real system.

8. Testing Checklist for Claude Code

Functional checks

  • python3 diagnostic/run_diagnostics.py runs without errors
  • findings.json is created and is valid JSON
  • python3 diagnostic/report.py runs without errors
  • report.html is created and opens correctly in a browser
  • Console output shows progress line for each of the 45 checks (V3 final state)

Coverage checks

  • manifest_detected in findings.json is 36 / 36 (100%)
  • CHK_T1_01 finds SG01-2010-000042 in affected_keys (D01)
  • CHK_T1_02 finds the negative rate record (D02) — RATE_ID 74
  • CHK_T1_03 finds the future DOB record (D03) — LIFE_ID 8
  • CHK_T3_02 finds the issue-after-maturity policy (D14) — SG01-2014-000073
  • CHK_T3_04 finds the non-monotonic rate at age 55 for TRM20/M/NS (D16) — RATE_IDs 26, 27, 37, 38
  • CHK_T4_01 finds the stale valuation parameter (D21) — PARAM_IDs 16, 1, 2

Edge case checks

  • Run against an empty database — all checks return passed=True gracefully (not verified during build; error-handling structure should handle it correctly)
  • Run with a missing manifest file — script exits with a clear error message, not a Python traceback
  • The age gap CTE check (CHK_T3_03) completes in under 10 seconds (achieved: 0.02s)
  • findings.json affected_keys never exceeds 20 items per finding