Work / Bordereaux Management System
Bordereaux Management System
A delegated-authority insurance data platform: monthly bordereaux in, a checked and traceable ledger out.
What it does
Coverholders send monthly spreadsheets of policies, premiums and claims. It keeps each original file untouched, maps its columns, checks every row against versioned rules and the binder's terms, and routes problems to an exception workflow where nobody approves their own fix. What it publishes is an event history with exact capacity splits, queryable by every party to the contract, and every published value traces back to the spreadsheet cell it came from.
- A coverholder uploads a spreadsheet; the original bytes are kept unchanged
- Columns are mapped; an uncertain mapping waits for a data steward
- Every row is checked against versioned rules and the binder's terms
- Errors are corrected or waived by someone other than the requester
- An approver publishes; events, splits and audit commit together
- Any value traces back to its source cell, and past states can be replayed
The full-scale simulator rerun on the final code was interrupted; the smaller runs pass on it.
How it works
A policy is a sequence of events (new business, endorsements, instalments, cancellations, reinstatements and corrections), and its state is replayed from them, so late and backdated reports land in the right place and any past state can be reproduced exactly as it was known at the time. Money is held in exact minor units and split between carriers so the parts always add up. Files are parsed in an isolated process with limits, and spreadsheet formulas are never run. A separate simulator generates a whole synthetic market, sends its files through the real workflow and checks the outcome against its own independently computed ledger.
Skills shown
- Data engineering
- Messy spreadsheets become raw, staged, canonical and published layers, with versioned mappings and cell-level lineage.
- Databases & SQL
- An event-sourced SQLite model with point-in-time queries, immutability triggers and a scoped, read-only SQL explorer.
- Security
- Tenant isolation, four-eyes approvals, CSRF protection and lockout, and file defences against ZIP bombs and macros.
- APIs & backend
- A job runner with leases and retries; publication is all-or-nothing, proven against a worker killed mid-write.
- Testing & QA
- An independent simulator and oracle, fault injection, an adversarial code review and real-browser accessibility sweeps.
Worth knowing
- On a simulated market of 497,284 policies, every policy, claim, carrier total and point-in-time view matched the simulator's independent ledger exactly.
- A separate reviewer attacked the code and confirmed 15 defects; each fix has a test that was shown to fail on the old code.
- Access is checked on the server for every page, export and query; ad-hoc SQL runs read-only in an isolated process, limited to the caller's contracts.
- A file of 100,000 rows goes from upload to published in about 30 seconds on a laptop.