Modernising around a 40-year-old core: a read-only automation layer that runs month-end by itself

Every piece of work here had to be justified against the obvious objection: why build anything on a system we're replacing? Four answers became the spine of the whole portfolio.

A replacement is years away, and the business has a monthly heartbeat to keep beating in the meantime. Everything cleaned, reconciled, or codified now is migration-ready data later, because a record that is wrong today is a migration record that fails tomorrow. The business rules had never been written down anywhere, so reverse-engineering and documenting them produces a deliverable that outlives the core and becomes the specification the eventual replacement is measured against. And nothing we built writes back to production. The whole layer is read-only by design, which is what made it approvable at all on an out-of-support box.

The before-state was the same everywhere. An analyst signs into a terminal emulator, runs hand-written SQL, drops the results into Excel, and works manually from there: filtering, re-typing, lookups across half a dozen exported sheets, re-running when something doesn't tie out. A monthly report pack took the better part of a week. The same business rule, such as what counts as an active policy or how a billing mode annualises, was re-implemented slightly differently in every spreadsheet. Numbers disagreed between reports, and a copy-paste slip could put a wrong figure in front of the board or a regulator.

We treated the legacy core as a read-only system of record and built a thin, disciplined, auditable layer beside it: Python over ODBC, one canonical policy extract, and one shared rules module that every downstream tool imports.

The rules module carries shared SQL fragments as well as constants: the in-force filter and the premium expressions, consolidated out of hand-copied duplicates across the analytics tools. We proved the consolidation safe by checking that the generated SQL is byte-identical before and after. A passing test suite tells you less than a comparison against something that existed independently of the change.

From that foundation the suite grew to roughly two dozen tools: the monthly report suite, premium notices and dunning, collection files for payment providers, policy movement snapshots, the AGM member list, data-quality and book-analytics worklists, and two independent commission engines.

The commission audit asks whether the system followed its own setup. It reconciles what was paid against the configured rates, which makes it a consistency check, and a consistency check can never catch a rate that was configured wrong in the first place.

So we built a second opinion. The commissions team never worked from the system's commission tables. Every month they built the payable figure by hand in Excel, through a seven-stage pipeline that narrowed tens of thousands of premium rows down to a few thousand policies, pricing from premium actually received, with the plan year derived from the policy's issue date. That basis is independent of the system's configuration, which is exactly what makes it a second opinion. In one respect it was quietly more correct than the system it was checking: deriving plan year from issue date sidesteps a defect our audit engine found independently, a stored duration field that runs backwards on some policies.

We reproduced their method exactly, then extended it. The tool's acceptance test was matching their workbook's row count to the row, including the rows they hold over to the following month. From there it added what a spreadsheet can't do: pricing every row against both the team's rate grid and the system's configuration and flagging disagreements, deriving the plan year both ways, and computing manager overrides for the first time. The fragile part of their process was a plan mapping that existed only in one person's head. Nobody had to write it down; the tool watched it being applied once, learned it, and saved it as a versioned, inspectable file.

The organisation now has two independent commission engines, plus the team's own workbook as a third. Where all three agree, the number is trustworthy. Where they diverge, there is a specific, listable, explainable reason.

Mid-project, we reviewed the whole suite under a rule we set for ourselves: read-only, no fixes, job = find and document. Reviewing and fixing at the same time resolves each finding at whatever depth you understood it in the moment, and because each finding disappears as you go, you never see the pattern across them. The fixes came later, as one dense block of commits, each verified against the live read-only database with before-and-after figures.

The headline finding sat in our own documentation. It recommended routing client joins through a cross-reference table we believed mapped an old client record to a new one for the same person. Verified against live data, the belief collapsed: only a negligible share of pairs matched on basic identity fields. Whatever that table is, it is no same-person map. The code had been correct all along, and following our own written advice would have attached the wrong person's details across a material part of the book. Two published data-quality checks built on the same premise were withdrawn, in the open. A check catalog that shrinks when its premise fails is a catalog you can trust.

The review also kept its own first drafts visible. Severity ratings are marked confirmed, revised, or withdrawn after live verification, so readers can see where the evidence changed our view instead of being shown a polished history after the fact.

Unattended does not mean invisible. Every tool launch appends its script, arguments, timestamps, user and exit status to a run ledger. The logging function is deliberately unable to raise an exception: instrumentation is there to explain a run, never to break the work it is observing. The cycle then records whether each step ran, skipped, deferred or failed, which matters because those outcomes demand different responses.

Distribution is configuration rather than code. A completed workbook is copied only if it exists, and each output has named destinations and rules. If an external input is missing, the cycle prepares the month folder and the inputs it can derive, then skips the dependent step without copying a half-complete result. That makes rerunning safe: the operator can supply the missing file and resume without reconstructing what the previous attempt did.

We made empty output suspicious as well. In one collection pipeline, a single blank spreadsheet cell changed a status column's inferred type, which made every valid row fail a filter without raising an error. The job completed and produced almost nothing. The correction normalised the values more defensively, added a regression test, and, more importantly, added a warning when zero rows survive a filter that normally produces work. A crash asks for attention. A silent zero looks finished.

The month-end cycle therefore automates more than a sequence of commands. It carries operational intent: wait for freshness, prepare what can be prepared, distinguish deferral from failure, distribute only complete artefacts, and leave enough evidence that the next person can tell what happened without reading the source code.