Skip to content
Tahir Aslanli

06Insurance reconciliation

Bordereaux Reconciler

Complete · frozenDeployed / live on Render

Free demo — may take ~1 min to wake.

In short

What it does
Checks the monthly spreadsheets insurance partners send against the insurer's ledger, traces every figure to its source cell, and flags unclear rows for review.
Why it matters
Partners use different column names and number formats, and the errors that matter most do not look wrong.
What I built
Built an engine that works out each spreadsheet's layout on its own, keeps money exact, and gives every row a clear outcome.
Key result
Not one row was wrongly marked as matching in a held-back test set of 715 rows with 90 planted errors, and every row got exactly the status the answer key expected.

Overview

Insurance reconciliation where every canonical value carries the spreadsheet cell it came from, money is exact decimal throughout, and the one role a model is allowed — proposing a column mapping — is built but not wired in.

Problem
Coverholders send monthly bordereaux with no standard: one writes Gross Premium, another GWP (excl IPT), a third Total Payable and means something else. 1,234.56 and 1.234,56 are the same number written by different people and different numbers read by the wrong parser. Someone reconciles this by hand, and the errors that matter do not look wrong.
Built
Ingestion under conventions the coverholder declares, column mapping from the header and the shape of the values beneath it, canonicalisation to exact decimals with the source cell attached to every value, reconciliation into six statuses, a human-confirmation step for mappings, and Terraform for Azure that passes terraform validate in CI and has never been applied.
Hard part
Mapping headers nobody wrote down. Each column is profiled — share of amounts, dates, identifiers, closed vocabularies, magnitude rank among the money columns — and that evidence is combined with the header, so the mapping holds where header-string methods collapse.

Evidence

0

rows wrongly marked MATCHED in a frozen hold-out of 715 rows seeded with 90 discrepancies

0.913

column-mapping accuracy on the hold-out files; the best of four header-matching baselines reaches 0.652

Hold-out numbers were visible in debug output before scoring, so the project treats this figure as slightly weaker; the development figure is also 0.913.

13,718

canonical cells traced to their source cell — 0 incomplete

19 / 19

planted defects caught

Column-mapping accuracy against four predeclared header-string baselines

Hold-outDevelopment
Column-mapping accuracy against four predeclared header-string baselines
MethodHold-outDevelopment
value shape + header (system)0.9130.913
curated synonyms0.6520.837
fuzzy header0.6300.707
normalised header0.5220.620
exact header0.3480.326

The margin is wider on the hold-out (+0.26) than on development (+0.08): the hold-out carries the unseen-vocabulary variants, where header-string methods collapse and value-shape evidence does not.

Source: artifacts/ · graded by a kill test committed before implementation

Architecture

  1. 01Input

    Coverholder file

    CSV or XLSX · declared decimal and date conventions

  2. 02Code

    Column profiling

    value shape + header evidence

  3. 03Code

    Column mapping

    deterministic mapper · a model port may only name canonical fields, not wired in

  4. 04Human

    Mapping confirmed

    a person confirms · the demo's mappings are seeded

  5. 05Code

    Canonicalise

    exact Decimal + source cell on every value

  6. 06Store

    PostgreSQL ledger

    canonical rows · NUMERIC(18, 4) · lineage

  7. 07Gate

    Reconcile vs carrier ledger

    six statuses

Branch · Cannot be read exactly

  1. ↳01Human

    Quarantine queue

    a queue, not a bin

A row that cannot be read exactly is quarantined, never coerced.

Statuses: MATCHED, MISMATCH, MISSING, DUPLICATE, AMBIGUOUS, REVIEW. MATCHED is constructed in exactly one place in reconcile.py, and a test asserts it over the module's syntax tree.

  • InputArrives from outside the system
  • CodeDeterministic code
  • HumanA person decides
  • GateDecides whether work proceeds
  • StoreDurable state

Engineering notes

Exact money, enforced at runtime

No premium, deduction or ledger amount is stored, parsed or compared as a float. The money module raises on a float at runtime rather than trusting the type checker, money columns in PostgreSQL are NUMERIC(18, 4), deductions are stored in JSON as strings, and rounding is half away from zero because that is what the finance systems a coverholder uses do.

Tolerances are configuration: a tolerance names the fields it covers, and a field it does not name requires exact agreement. No model, heuristic or scoring function can influence one.

Where the model is allowed to be

A model is confined to one permitted role: proposing a mapping from source columns to canonical fields, for a person to confirm. That port is built and tested but not wired in — the deterministic mapper produces every mapping. The proposal type has no field capable of holding money, the reconciler does not import the provider, and every proposal is validated against the canonical field set before a human sees it. A spreadsheet cell reading 'ignore previous instructions' is inert because a proposal can only name a field from a closed set.

Infrastructure as code, validated and not applied

Terraform composes a platform module into three environment roots — dev, bench and prod — declaring Azure Container Apps, Azure Database for PostgreSQL and keyless Blob storage that would hold the raw spreadsheets behind every lineage record. All three pass terraform validate against the real azurerm 5.6.0 schema in CI, and a committed residency manifest must match the Terraform inputs.

Nothing has been applied: there is no Azure subscription for this build, and the live demo runs on Render's free tier.

Screens

Overview screen with the false-MATCHED banner reading zero and ingestion countersOverview screen with the false-MATCHED banner reading zero and ingestion counters
The overview: the false-MATCHED banner renders whether the count is zero or not.Captured from the live deployment
Row lineage screen tracing each canonical value to its sheet, row and source headerRow lineage screen tracing each canonical value to its sheet, row and source header
Lineage: every value traced to the cell it came from.Captured from the live deployment
Evidence screen listing each published figure with the artifact that produced itEvidence screen listing each published figure with the artifact that produced it
Evidence: every figure traced to the artifact that produced it.Captured from the live deployment

Limitations

As the project states them. Read these before relying on any number above.

  • No Azure deployment exists: the Terraform is validated in CI and has never been applied.
  • No live model call: the port, validation boundary and residency record are built and tested, but no live arm is wired in and no key exists, so no cost or latency is published.
  • Hold-out numbers were visible in debug output before the scoring run (0.7826). The repository discloses every change made in between and asks sceptical readers to treat the hold-out figure as slightly weaker.
  • Two development variants, multi-commission and split-tax layouts, get REVIEW instead of a decision: the adapter they need is not built.
  • The demo's free Render PostgreSQL expires on 24 October 2026; after that only the evidence page keeps working unless the database is re-provisioned.
  • The corpus is synthetic, generated from a committed seed; no redistributable real bordereaux exist.

Facts and stack

Corpus
18 schema variants · 2,333 canonical rows
Re-ingestion
16 files × 3, 0 second versions
Portability
725 non-insurance rows through the same engine
CI
fast · terraform · evidence · falsifiability · docker

Stack

  • Python 3.12
  • FastAPI
  • PostgreSQL 16
  • SQLAlchemy
  • Alembic
  • openpyxl
  • Terraform
  • Azure — validated, never applied
  • Docker
  • GitHub Actions
  • Render

Skills shown

  • Insurance reconciliation
  • Deterministic money
  • Data lineage
  • Human-confirmed mapping
  • Terraform / IaC
  • Residency manifest as code