Dipen Modi

My database had money that no transaction created

I had just finished migrating my banking project from a JSON file to PostgreSQL. Eighteen accounts, all of them fictional villains from the movie Despicable Me, moved across without a single error. I ran a query to spot-check the result and got this:

                  username   | balance_cents
                -------------+---------------
                 test_user   |          5000
                 BadBoy      |             0
                 MrScientist |          5000

test_user had fifty dollars, which was right. BadBoy had nothing, which was right. MrScientist had fifty dollars, and I had never touched that account.

The thing that made it interesting

A few days earlier I had added a transaction ledger to the project. Every deposit and withdrawal appends a row recording what happened, how much, and what the balance became. The idea is that the balance is a claim and the ledger is the evidence.

So I asked the ledger where the fifty dollars came from:

                SELECT t.type, t.amount_cents, t.created_at
                FROM transactions t
                JOIN accounts a ON a.id = t.account_id
                WHERE a.username = 'MrScientist';
                 type | amount_cents | created_at
                ------+--------------+------------
                (0 rows)

Nothing. Fifty dollars sitting in an account, and no record of anyone putting it there.

Working out what happened

There were three possibilities and only one of them meant my code was fine.

The migration reads clients.json and writes rows into Postgres. So either the source file already had this problem, or the migration created it. I checked the source:

{'username': 'MrScientist', 'name': 'Norman Osborn', 'account_number': 'BE015', 'balance': 50.0, 'transactions': []}

Fifty dollars, empty transaction list. The migration had copied exactly what it was given. My code was not the problem.

What actually happened is that at some point during development, before the Transaction class existed, I ran the terminal app and deposited fifty dollars into that account to check that deposits worked. The balance moved. Nothing recorded it, because at that moment nothing could record it. Then every subsequent migration carried that orphaned balance forward, faithfully, into the new system.

The bug was not in the migration. It was in the data, and it predated the feature that would have caught it.

Why this is worth writing about

The temptation was to run one UPDATE, set the balance to zero, and forget about it. I did run that update, because I had endpoints to finish. But the principle underneath is one I had not properly understood until I hit it.

I had been thinking of the ledger as a feature — something users can look at, a statement screen. It is not primarily that. It is an invariant:

Every cent in a balance should be explained by the ledger.

Once you state it that way, the account with fifty unexplained dollars is not a cosmetic problem. It is a violation of the property that makes the system trustworthy. And a system that quietly tolerates one violation will tolerate others, because nothing is checking.

What a real migration does

The correct fix is not to zero the balance. Zeroing it destroys information — that fifty dollars might have been real, and in a production system it certainly would be. You cannot make history correct by deleting it.

What real financial systems do when moving data between platforms is create an opening balance entry: a synthetic transaction, dated at the moment of migration, that says this account started here, prior history unavailable. It does not pretend to know what happened before. It just makes the ledger complete going forward, so the invariant holds from that point on.

For my project that means the migration script should do something like:

if balance_cents > 0 and not transactions:
                    insert_transaction(
                        type="opening_balance",
                        amount_cents=balance_cents,
                        balance_after_cents=balance_cents,
                        created_at=migration_time,
                    )

That needs a schema change too — the CHECK (type IN ('deposit', 'withdrawal')) constraint would reject it, which is the database correctly refusing a transaction type it has never heard of.

The test I should have had

The satisfying version of this story ends with a test that would have caught it:

def test_ledger_explains_every_balance(conn):
                    """No account may hold money the ledger cannot account for."""
                    for account in load_accounts(conn).values():
                        ledger_total = sum(
                            t.amount if t.type == "deposit" else -t.amount
                            for t in account.transactions
                        )
                        assert ledger_total == account.balance

That test is four lines and it asserts the entire integrity property of the system. It would have failed the moment I migrated. I did not have it, because I was thinking about the ledger as something to display rather than something to verify.

That is the actual lesson, and it took an account of a fictional supervillain holding fifty phantom dollars to teach me.

What I changed

I have gone back and forth on whether the honest thing is to fix it properly before writing this. I decided it is more useful to publish the version where I found it, understood it, and consciously deferred the real fix — because that is what the week actually looked like.

← All posts

Comments