Data trust

Hundreds were prosecuted on the strength of a computer record. The record could not explain itself.

A shortfall on a screen became evidence of a crime. What a database can record so that its numbers can be challenged.

Published 8 min read

Jump to the append-only ledger
A red-brick Post Office building in Surbiton, England, photographed in 1994

Photo: Surbiton Post Office. 1994, Felix O, CC BY-SA 2.0, via Wikimedia Commons.

The British Post Office scandal is, at the human level, about people. Subpostmasters ran Post Office branches, were told by an accounting system that money was missing, and were prosecuted, and some were sent to prison. The software behind it was called Horizon.

This article is about the database lesson in the story, and it is deliberately narrow. It does not try to explain every fault found in Horizon, and it makes no claim about how any single shortfall arose. For that, the court judgments and the public inquiry are the right sources.

What the record shows

According to the Wikipedia account of the scandal, Horizon was developed by Fujitsu for the Post Office under a contract conceived in 1996. The first version, Legacy Horizon, ran from 1999 to 2010 and was replaced by Horizon Online. Between 1999 and 2015, more than 900 subpostmasters were prosecuted, the Post Office itself brought about 700 of those prosecutions, and 236 people were sent to prison.

From first prosecutions to quashed convictions

  1. 1999

    Horizon goes into use. Prosecutions of subpostmasters based on its data begin in the years that follow.

  2. 2019

    In Bates and Others v Post Office, the High Court (Mr Justice Fraser) finds that Horizon contained bugs, errors and defects, and examines remote access to branch accounts. The case settles for £58 million.

  3. April 2021

    The Court of Appeal quashes 39 of 42 appellants' convictions after a hearing in March 2021, finding that a fair trial had not been possible.

  4. January 2024

    ITV's drama Mr Bates vs The Post Office brings the story to a wide audience.

  5. May 2024

    Parliament passes the Post Office (Horizon System) Offences Act 2024, which quashes qualifying convictions without case-by-case appeals. Scotland passes a similar law.

Source: Wikipedia: British Post Office scandal

By February 2024, the same account says, more than 4,000 subpostmasters had been identified as eligible for compensation, and the final cost is expected to exceed £1 billion. The Post Office Horizon IT Inquiry began in 2020 and became a statutory public inquiry in 2021.

The 2024 Act's explanatory notes describe a blanket approach: instead of reviewing each case, it quashes convictions for qualifying offences, including theft and fraud, for subpostmasters and others who worked in branches, including volunteers. The Scottish Act, passed separately, covers offences between 23 September 1996 and 31 December 2018.

The lesson: a number is not evidence of itself

A row in a database tells you what is stored now. It does not tell you who put it there, when, from where, or whether anything else changed it. If that row decides whether someone is accused, billed or denied care, "the system says so" is a thin foundation.

Two questions matter whenever data is used against a person. Can the data be wrong, and can anyone tell? The first is a fact about software. The second is a design choice you make before anything goes wrong.

Record changes instead of overwriting them

An append-only ledger never edits or deletes. A correction is a new row that points to the row it corrects. The balance at any moment is the sum of the entries. This SQLite example enforces that with triggers, and it was run to confirm that both an update and a delete are refused.

SQL
CREATE TABLE ledger (
  id INTEGER PRIMARY KEY,
  account TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  note TEXT NOT NULL,
  corrects_id INTEGER REFERENCES ledger(id),
  recorded_by TEXT NOT NULL,
  recorded_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER ledger_no_update BEFORE UPDATE ON ledger
BEGIN SELECT RAISE(ABORT, 'ledger rows are append-only'); END;

CREATE TRIGGER ledger_no_delete BEFORE DELETE ON ledger
BEGIN SELECT RAISE(ABORT, 'ledger rows are append-only'); END;

INSERT INTO ledger (account, amount_cents, note, recorded_by)
VALUES ('branch-17', 50000, 'cash deposit', 'terminal-3');

UPDATE ledger SET amount_cents = 0;
-- Error: ledger rows are append-only

To fix a mistake, add a reversing entry and a new correct one, both pointing at the original through corrects_id. The history then shows the mistake, the correction and who made each. That is more useful in a dispute than a tidy table that has lost its past.

SQL
INSERT INTO ledger (account, amount_cents, note, corrects_id, recorded_by)
VALUES ('branch-17', -50000, 'reverse: wrong amount', 1, 'user-42'),
       ('branch-17',  45000, 'cash deposit, corrected', 1, 'user-42');

SELECT account, SUM(amount_cents) AS balance_cents
FROM ledger GROUP BY account;   -- 45000

A change log you can query

For tables that must be editable, record every change in a separate audit table. This trigger stores the old and new values and who made the change. Your application sets the acting user; here it is passed through a column on the table.

SQL
CREATE TABLE branch_cash (
  branch TEXT PRIMARY KEY,
  cash_cents INTEGER NOT NULL,
  last_changed_by TEXT NOT NULL
);

CREATE TABLE branch_cash_audit (
  id INTEGER PRIMARY KEY,
  branch TEXT NOT NULL,
  old_cents INTEGER,
  new_cents INTEGER,
  changed_by TEXT NOT NULL,
  changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER branch_cash_log AFTER UPDATE ON branch_cash
BEGIN
  INSERT INTO branch_cash_audit (branch, old_cents, new_cents, changed_by)
  VALUES (OLD.branch, OLD.cash_cents, NEW.cash_cents, NEW.last_changed_by);
END;

Now a question that matters in a dispute is answerable: show every change to this branch's balance, in order, with who made it.

SQL
SELECT changed_at, old_cents, new_cents, changed_by
FROM branch_cash_audit
WHERE branch = 'branch-17'
ORDER BY id;

Record who and from where, especially for remote changes

The Bates judgment looked closely at remote access: whether people at the supplier could alter branch accounts, and whether subpostmasters could see that they had. The point for any system is that changes made from outside the normal user flow need their own, more visible log.

  • Store the actor for every change: a person, a service account or a supplier login.
  • Mark changes made by administrative or remote tools differently from changes made in the normal application.
  • Show the affected user the history of their own account. A person who can see the log can challenge it.
  • Keep the logs for as long as a dispute could take. These cases ran for decades.

Make tampering detectable

An audit table can itself be edited by someone with enough access. A hash chain makes silent edits visible: each entry stores a hash that covers its own content and the previous entry's hash. Change any old row and every later hash stops matching. This Python and SQLite sketch was run to confirm that a changed amount is caught.

Python
import hashlib, sqlite3

c = sqlite3.connect(':memory:')
c.execute('CREATE TABLE log (id INTEGER PRIMARY KEY, body TEXT, prev TEXT, hash TEXT)')

def add(body):
    prev = c.execute('SELECT hash FROM log ORDER BY id DESC LIMIT 1').fetchone()
    prev = prev[0] if prev else ''
    h = hashlib.sha256((prev + body).encode()).hexdigest()
    c.execute('INSERT INTO log (body, prev, hash) VALUES (?, ?, ?)', (body, prev, h))

def verify():
    prev = ''
    for body, stored_prev, h in c.execute('SELECT body, prev, hash FROM log ORDER BY id'):
        if stored_prev != prev or hashlib.sha256((prev + body).encode()).hexdigest() != h:
            return False
        prev = h
    return True

add('branch-17 +50000'); add('branch-17 -50000'); add('branch-17 +45000')
print(verify())                      # True
c.execute("UPDATE log SET body = 'branch-17 +0' WHERE id = 1")
print(verify())                      # False

A chain proves that something changed, not what the truth was. For stronger guarantees, publish the latest hash somewhere outside your control, such as in a signed message to an external party, so you cannot quietly rebuild the whole chain.

Reconcile against something other than yourself

Many ledger systems add an independent check: money in must equal money out, or the branch's total must match a second record kept elsewhere. A query that lists disagreements between two sources catches silent drift early.

SQL
-- Branches where the ledger and the cash-count sheet disagree
SELECT l.account,
       SUM(l.amount_cents) AS ledger_cents,
       s.counted_cents
FROM ledger l
JOIN cash_count s ON s.branch = l.account
GROUP BY l.account, s.counted_cents
HAVING SUM(l.amount_cents) <> s.counted_cents;

A disagreement is a prompt to investigate, not a verdict. The failure in this story was treating the shortfall as proof of wrongdoing without asking whether the system could be wrong.

The process matters as much as the schema

  • Tell users how a figure is produced and give them a way to dispute it.
  • Record disputes and their outcomes. A pattern of disputes against one system is itself data.
  • Treat repeated, unexplained shortfalls as a possible fault in the software, not only as a fault of the person.
  • Do not let a single system's output be the only evidence in a decision that harms someone.

Double-entry: every movement has a counterpart

Accountants solved a version of this problem centuries ago. In double-entry bookkeeping, every transaction is recorded twice, as a debit in one account and an equal credit in another. If the books are consistent, the debits and credits of each transaction match. A query can check that in one pass. This was run on sample data.

SQL
CREATE TABLE journal (
  id INTEGER PRIMARY KEY,
  txn_id INTEGER NOT NULL,
  account TEXT NOT NULL,
  debit_cents INTEGER NOT NULL DEFAULT 0,
  credit_cents INTEGER NOT NULL DEFAULT 0
);

INSERT INTO journal (txn_id, account, debit_cents, credit_cents) VALUES
  (1, 'cash', 50000, 0), (1, 'deposits', 0, 50000),
  (2, 'cash', 100, 0),   (2, 'fees', 0, 90);

-- Transactions whose two sides disagree
SELECT txn_id, SUM(debit_cents) AS debits, SUM(credit_cents) AS credits
FROM journal
GROUP BY txn_id
HAVING SUM(debit_cents) <> SUM(credit_cents);
-- 2 | 100 | 90

Transaction 2 is out by ten cents and the query finds it. The more important question comes next: why? A mismatch can be a typing error, a software bug, a missing row or a deliberate change. The audit trail from earlier is what lets you tell them apart.

Make disputes a first-class record

People who are told their numbers are wrong need a way to say "I do not agree" and to have that recorded. A dispute table turns complaints into data, and patterns in data are how a software fault gets noticed. This was run on sample data.

SQL
CREATE TABLE dispute (
  id INTEGER PRIMARY KEY,
  account TEXT NOT NULL,
  opened_at TEXT NOT NULL,
  reason TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('open', 'upheld', 'rejected')),
  resolved_at TEXT
);

-- Accounts with repeated disputes
SELECT account, COUNT(*) AS disputes
FROM dispute
GROUP BY account
HAVING COUNT(*) >= 2;

-- Disputes by how often they are upheld: a high rate points at the system, not the person
SELECT status, COUNT(*) FROM dispute GROUP BY status;

When many accounts report the same kind of unexplained shortfall, the likelier explanation is something in the shared system. The scandal shows what happens when that signal is ignored, or is treated as a personal failing. A dispute table is a small tool, and it only helps if someone reads it and has the power to act.

Let people see and export their own history

The person whose account is affected should be able to see every entry that touches it, with who made it and when. That includes changes made centrally. A simple, always-available view is better than a request process that takes weeks.

SQL
CREATE VIEW my_ledger AS
SELECT recorded_at, account, amount_cents, note, recorded_by, corrects_id
FROM ledger
ORDER BY id;

-- Export for a dispute, to a file the person can keep
-- sqlite3: .mode csv, .headers on, .once history.csv, then SELECT * FROM my_ledger WHERE account = 'branch-17';

Time, order and identity

  • Store timestamps in UTC, and show local time only when displaying. A shortfall that appears at midnight can be a time-zone artefact.
  • Use an increasing identifier, as well as a timestamp, to fix the order of events. Clocks on different machines disagree.
  • Record the system version that produced each entry. A bug fixed in one release explains a pattern before it.
  • Keep old software versions and their logs available for as long as records made with them may be disputed.

Questions to ask of any system whose output can harm someone

  1. 01Can it be wrong?Assume yes. Ask how often, and how you would know.
  2. 02Can anyone change a record without leaving a trace?If the answer is yes, whoever can do so can also explain discrepancies away.
  3. 03Can the affected person see what the system holds about them?If not, they cannot challenge it.
  4. 04Who investigates when the numbers and the person disagree?A named role, with the access to read logs and the independence to follow them.
  5. 05What happens to the person while the question is open?Systems that treat a shortfall as guilt until proven otherwise cause harm even when they are later shown to be wrong.

Questions that come up

Does an audit log prove the data is right?
No. It shows how the data came to be what it is. That is what allows someone to challenge a number with evidence.
Should corrections ever edit the original row?
For records that carry legal or financial weight, add a correcting row and keep the original.
Would any of this have stopped the scandal?
The sources reviewed do not say so, and this article does not claim it. The point is that systems used against people need records that can be inspected and challenged.
Is a hash chain overkill?
For most apps, a plain audit table is enough. A chain is worth considering where records might be disputed years later.

Sources