Designing an Append-Only Ledger for Group Pension Administration

This is part of the engineering series behind our group pension administration case study. The system replaced spreadsheets that already produced audited figures. That meant the ledger could not merely end at the same current balance. It had to explain how the balance arose, keep two sources of member money separate through every event, and reconstruct the position at an earlier date without restoring an old workbook.

The design starts from a refusal: no account balance is the master fact. Transactions and their entries are the facts. A displayed balance is a query over those entries up to a value date. Once that is true, reversals, as-at reporting, reconciliation and audit all become consequences of the same model rather than separate features trying to repair an editable total.

The ledger is append-only and double-entry

Every money movement is a transaction containing entries that sum to zero. The database does not accept a posting whose debits and credits fail to balance. An accepted transaction is never edited or deleted. If the economic event was wrong, a reversal neutralises it and a corrected transaction records what should have happened. All three remain visible.

This is more than an audit preference. Editing a contribution amount after interest has been credited makes every downstream number ambiguous. Did the original amount earn interest? Was the correction known at the time? Which figure appeared on the statement? Append-only posting keeps the sequence intact, so later calculations can use the state that actually existed at their value date.

Each transaction also carries its operational evidence: actor, reason, source document, approval and dates. The ledger records the economic event; the surrounding workflow proves who was permitted to cause it. An unexplained balanced journal is still a control failure, so accounting validity and business authority are checked together at the posting boundary.

Employee and employer money never become one balance

A group pension contribution has at least two sources with different rights. Employee money and employer money can follow different vesting, interest, fee and payout rules. When a member leaves, the employee source remains theirs, while only a portion of the employer source may vest. The unvested employer amount can move to the smoothing reserve. Combining the sources at contribution and trying to reconstruct them at benefit time would make the most consequential calculation depend on historical guesswork.

The chart of accounts therefore keeps member accounts per source. Interest, fees, forfeiture and payout preserve that identity from end to end. Reports may present a total where useful, but the underlying entries retain the source split. A summary is allowed to add the figures; no posting is allowed to forget where they came from.

Receipt and allocation are different economic facts

A sponsor can send one payment covering many members. The cash arriving does not prove how it should be allocated. We post the receipt from cash clearing into suspense, then post member contribution allocations out of suspense as a separate transaction. The two events can occur at different times and carry different evidence.

The benefit is structural. When every allocation is complete, suspense returns to zero. A remaining balance is unallocated cash by definition, not a condition a reconciliation script has to infer from two unrelated totals. The account model creates the exception queue. Correct modelling does work that would otherwise be assigned to a fragile validation rule.

The workflow can now distinguish cash not yet explained, a contribution batch not yet approved and an approved allocation not yet posted. Those are operationally different states. A spreadsheet can show a single variance column, but it cannot reliably enforce which state permits the next action or prevent the same receipt from being allocated twice.

Opening balances enter through the same control model

Migration cannot bypass the ledger merely because the money predates the application. Opening positions are staged, validated and posted through maker-checker into opening-equity accounts. The import template makes the employee and employer sources explicit, duplicate detection blocks repeated staging, and the posting retains the source file and both actors.

This gives the new ledger a defensible first event. It does not invent the history before cutover, but it records exactly which accepted position began the reconstructable period. A later as-at query can distinguish the imported opening position from every contribution, fee, interest credit and benefit posted afterwards.

Balances are queries with dates

A current balance is the sum of applicable entries. An as-at balance is the same query with a date boundary. There is no snapshot copied into a master field and no special reconstruction routine that has to reverse-engineer prior states. The ledger already carries value dates, so the system can answer a member-position question for an earlier scheme year using the same source of truth as today's dashboard.

Value date and entry date are separate. The first says when the event belongs economically; the second says when somebody recorded it. A late-entered contribution can belong to an earlier scheme period without pretending it was keyed earlier. Both facts survive, allowing calculation rules and operational reports to use the date appropriate to their question.

Derived balances also make integrity sweeps meaningful. The system can recalculate account positions from entries and compare them with cached or summarised views. If a performance cache drifts, it can be rebuilt. If an append-only entry is wrong, the correction is another business event. The recovery path follows from which layer is authoritative.

The chart of accounts makes those queries legible. Member accounts sit beside suspense, bank and cash clearing, sponsor receivables, plan earnings, fee income, the smoothing reserve and opening equity. The names are not merely report labels; they describe why each side of a transaction exists. A contribution allocation cannot balance against an arbitrary convenience account without making the unexplained amount visible somewhere else.

Money crosses every boundary as a decimal

Amounts use fixed-precision database columns and decimal arithmetic in the domain code. They are serialized as strings through application boundaries instead of becoming native floating-point values in the browser or server. This prevents a correct stored amount from acquiring a binary rounding error merely because it crossed an API.

Rounding occurs half-up at posting, where the financial event becomes durable. When a control total is split across members, we allocate the rounded remainders deterministically so the parts add back to the whole. Interest is first rounded to the fund-level control amount and then distributed. The ledger never accepts a story in which every individual line looks plausible but their sum differs from the posted total.

This discipline is intentionally unglamorous. A prototype can display two decimal places while calculating with floating point underneath. An audited system has to say where rounding occurs, what happens to the final cent and why rerunning the allocation produces the same answer.

The annual close is an ordered posting process

Closing a scheme year is not a flag applied to a finished spreadsheet. Contributions must be allocated, members classified into the appropriate funds, interest posted, then fees, then forfeitures, followed by the audit charge from the smoothing reserve. Changing the order changes money, so the sequence is encoded and guarded rather than described only in an operating procedure.

Completion waits for the active, vested, termination and smoothing-reserve positions to reconcile exactly with the revenue-and-expenditure summary. The system does not offer a tolerance to make close succeed. Reopening a closed year requires separate senior approvals and leaves a complete record, because reopening changes the context in which later reports and statements were produced.

Forfeiture posts when the benefit is paid

For a terminating member, unvested employer money moves to the smoothing reserve. We post that movement with the benefit payment rather than earlier in the case. Before payment, the case can still be cancelled or changed. Posting forfeiture too soon would move real money based on an outcome that had not yet become final and would require compensating work for an ordinary cancellation.

The benefit transaction can therefore express the complete economic outcome: the member's employee balance, the vested employer portion, any applicable deductions, the amount paid and the employer amount forfeited. Entries balance as one event, while the case workflow supplies the approval and regulatory evidence required to reach it.

Reports read the ledger instead of maintaining parallel totals

Member statements, contribution reports, movement reports, termination schedules and fund summaries are projections of the same postings. A report is not allowed to maintain its own authoritative balance column. This reduces the number of places where employee and employer sources, signs or date boundaries can drift apart.

Generated documents still record which period and rule context they represent, and delivery is tracked outside the ledger because sending a statement is not a money movement. The boundary keeps financial truth in the journal while allowing operational evidence—generation, secure-link delivery and access—to have its own lifecycle.

Atomic posting keeps partial truth out

A transaction either writes all of its entries and audit references or none of them. The system cannot post the member credit and fail before clearing suspense, or move forfeiture without recording the matching source reduction. Validation happens before commit, and database atomicity protects the boundary after validation.

Idempotency protects the retry path. If an operator repeats a completed transition after a timeout, the application finds the existing financial event instead of creating a second one. Balanced entries prevent incomplete journals; idempotent commands prevent complete journals from being duplicated.

The spreadsheet still matters

We did not declare the ledger correct because its design looked like accounting. Historical scheme-year fixtures were run through the live database and compared with the audited workbooks to the cent. The workbook was independent evidence, and matching it was the entry condition for replacing it. The Spreadsheet Was the Acceptance Test covers that verification method in full.

The ledger adds what the workbook cannot preserve: the immutable path, authority at posting, reproducible as-at positions and structural reconciliation. It respects the old result without inheriting the old mechanism. That is the correct posture when replacing a financial process that already passes audit.

The takeaway

The useful ledger decisions are the ones that remove later ambiguity. Separate money whose rights differ. Separate cash receipt from allocation. Separate value date from entry date. Correct with a new event. Derive balances from the event history. Reconcile exact totals before close. With those boundaries in place, an audit trail is not a report assembled after the work; it is the work's natural shape.