Replacing the spreadsheet as the system of record for a regulated pension product

A regulated financial product, registered with the central bank, subject to quarterly returns to the supervisor, requiring a revenue-authority clearance certificate before a single payout, and carrying anti-money-laundering reporting obligations, was being administered in spreadsheets.

The spreadsheets, to be fair, worked. They produced numbers that passed audit. What they could not do was scale, survive the departure of the person who understood them, enforce a single control, or reconstruct how a balance came to be.

Group pensions are also harder than they look. Employee money and employer money are tracked separately at every step: separate balances, separate interest, separate vesting, separate payout rules, never co-mingled. Every plan has its own scheme year, its own vesting ladder, its own fees and eligibility. Earnings follow a declared-rate model with a smoothing reserve; there are no unit prices. How much of the employer's money a leaver keeps depends on why they left and how long they served, and whatever isn't vested is forfeited to the smoothing reserve, which is real money moving between real accounts. And two regulators sit inside the day-to-day operating flow itself.

We did not start from a specification. We started from a meeting with the department and their signed, controlled Standard Operating Procedure, the real document describing how the work was actually done, plus a real certificate as a worked example of an output. We kept those raw inputs in a separate folder from the specification so evidence and interpretation could never blur.

Before building anything, we had the emerging design attacked. An adversarial review against industry-standard practice called the design "closer to a third-party-administrator back-office workflow and spreadsheet math than an industry-standard recordkeeping platform." We accepted about half of it, rejected a third of it in writing with reasons, and deferred the rest into an explicit out-of-scope list. The earnings model it challenged was defended on evidence: the product genuinely is declared-rate deposit administration, and the signed procedure proves it. That defence then became part of how the system is explained to users.

Only then did we lock fifty decisions, each with a rationale citing its evidence, under a standing rule that no unanswered business question is allowed to block the build. Every open question gets a buildable default, and the eventual answer becomes a configuration change.

The payoff shows in the commit history: 65 feature commits and 4 fix commits. Several of those four fixes were substantial. What the ratio measures is rework, and rework stayed close to zero because everything had been decided first.

Late in the build we re-read the system against its own stated principles and found five places where it violated them. All five are recorded in the decision log as corrections of earlier decisions, and none of them was found by a failing test.

We shipped an outbox with no consumer: statement delivery was specified, built, and the messages were enqueued and never sent. We captured beneficiaries at enrolment, complete with shares, and the death benefit paid the member record instead. Rule versions, the one mutation path that re-prices everything, could be changed by a single person. The annual close, the largest money event in the system, could be run end to end by one actor, which contradicted the maker-checker principle we'd written down on day one. And a plan's investment model and its earnings mode could be set inconsistently, silently running declared-rate interest and an investment fee on the same plan.

The pattern across all five: a feature that exists, passes review, and quietly does nothing, or does something adjacent to what was intended. Each one was found by re-reading the system against its own principles and asking where, exactly, each principle was enforced.

The ledger and calculation engine were only the centre of the system. Around them we built the operating cycle the workbook had been carrying implicitly: plan and member setup, staged opening balances, contribution imports with member matching, the employee and employer split, cash receipt into suspense, allocation, exception handling, maker-checker posting, interest, fees, forfeiture, benefits, annual close and as-at statements.

The reporting side covers member movements, contributions, active and vested populations, terminations, refund schedules, compliance work and the quarterly supervisory return. Documents are produced from the same controlled state: enrolment certificates, member material and statements delivered through secure links, with evidence attached where a workflow requires it. An integrity job and operations runbook sit beside the user interface because a financial system also needs to explain how it is operated when nobody is clicking through a demo.

The contribution flow shows why this could not be a set of forms over a balance table. Receiving money and allocating it are separate postings. A sponsor payment first enters suspense; member allocations clear it. If suspense is not zero, there is unallocated cash by construction, and the reconciliation report exposes it without somebody remembering to run a separate check. The control comes from modelling two different facts as two different facts.

The calculation core is a pure domain module. It knows money, rules, calculations and ledger postings, and it cannot import the web framework, database or file handlers. The boundary is enforced by the codebase, which lets the engine be proved independently before any screen is allowed to post money. The build order followed that boundary: foundations, calculation engine and golden fixtures, one complete vertical slice, then the rest of the operating cycle.

The primary deployment is on infrastructure controlled by the organisation, including the database, documents, mail and background work. Cloud services remain possible behind adapters, but live personal data does not move to an unapproved environment. That was not a hosting detail postponed until launch; for a regulated financial institution, data residence and the ability to replace an integration are part of the product architecture.

We also kept version one disciplined. It administers the defined-contribution product and does not pretend to calculate defined benefits. It does, however, capture the historical facts a future product would need and puts benefit calculation behind a replaceable engine. You can add a calculation later. You cannot recreate salary or service history that the system never collected.