Personal finance aggregator: Cash, and an audit trail - Part 18
~7 min read
Everything the app tracks so far lives somewhere with an API, or at least a login: banks through TrueLayer, Hargreaves Lansdown through its website, and loans typed in by hand as manual accounts. The one pot of money it couldn't see was the physical kind - the notes and coins in a wallet or a drawer. Four commits close that gap and then answer a question it raised: when a balance changes, why? This post covers the cash account type (add full cash tracking account type, reduce cash denominations) and the audit trail that followed (add account audit history, view history from home page).
A balance you can't type
A cash account is a manual account with a flag, is_cash, and one rule: you never enter its balance. You tell it which notes and coins went in or out, and the balance is whatever that adds up to. A hand-typed £47.50 that doesn't match the wallet is exactly the drift the feature exists to prevent, so the API simply refuses it - a PATCH with a balance on a cash account is a 400 saying the balance "comes from its notes and coins - add or remove some".
Like budgets.py and savings.py before it, the logic is a module of pure functions, cash.py, with no database in sight. Amounts are whole pence throughout, so adding up a wallet never meets float rounding; the API converts to pounds only at the edge. The denominations are a small table, largest first, which is also the order the UI lists them in:
DENOMINATIONS = (
(5000, "£50", "note"),
(2000, "£20", "note"),
(1000, "£10", "note"),
(500, "£5", "note"),
(200, "£2", "coin"),
(100, "£1", "coin"),
(50, "50p", "coin"),
)
That's the shorter list. The first version went all the way down to 1p, and the follow-up commit trimmed it to the denominations "worth counting": notes down to £5, coins down to 50p. Nobody tracks their coppers in an app, and a longer list is a longer form to fill in every time.
The one interesting function is apply_changes. It takes the current counts and a dict of signed changes - positive adds, negative removes - and returns the new counts, raising ValueError for an unknown denomination or for removing more than is held. "A wallet can't hold minus two tenners." It never modifies its input, which is what makes the storage side all-or-nothing: the new counts are fully computed and validated before anything is written, so a request that adds a £10 and removes two £20s from a wallet holding one £20 changes nothing at all. A test pins exactly that.
Storage: counts in one table, the balance derived
Counts live in their own table, cash_holdings, keyed (account_id, pence). The comment on its schema states the invariant: the account's balance "is always derived from these, never entered by hand". apply_cash_changes writes the new counts and then calls the same update_manual_account every other manual balance change goes through, so the daily balance-history snapshot (post 13) and everything that reads list_accounts() pick cash up for free. A cash account shows in the totals, on the Savings page and in the charts exactly like any other money-you-hold account, because as far as they're concerned it is one.
Two creation paths matter. A new cash account is forced to kind='accounts' (money you hold) with a balance of zero, whatever the request says - a test sends balance: 999, kind: "cards" and checks both are ignored. And an existing manual account can be converted with convert-to-cash: you enter what's actually in the wallet and its total replaces the old hand-typed balance. That's the one place a balance is overwritten rather than adjusted, so it's guarded: only accounts you hold money in can be converted (not a loan), an already-cash account can't be converted again, and an empty set of counts is rejected, since "the balance becomes their total" would silently zero the account.
On the page, a cash row gets a "Notes & coins" button. The dialog lists every denomination with how many are held, an Add/Remove toggle, and a count box per row; when removing, each box's max is what's held. The API's GET returns every denomination with a count of zero for ones never held, so the UI never needs to know the list itself - the same instinct as the categories seeded in post 12.
The next question: why did it change?
Cash made an existing gap obvious. The balance-history chart records one point per account per day, which is right for a line on a graph and useless as a record. It can say a wallet went from £43 to £23; it can't say a £20 note was taken out, or when, or whether a figure moved because of a sync or because I edited it.
So the second piece is an audit trail, in a new table, account_events. It is deliberately separate from account_balance_history: that one upserts a single row per day so the chart stays tidy, this one appends a row per change and never merges. Each row has an account, a timestamp, the old and new balance, and a kind saying why it moved:
created- a manual account was added;manual- a hand-entered balance was edited;cash- notes and coins were added or removed, with the signed{pence: count}stored as JSON alongside;sync- a provider refresh found a different balance.
A single function, record_account_event, is the only writer, and its one rule is in its docstring: write a row only if the balance actually moved (or it's the account's first sighting, old_balance NULL). That keeps the trail meaningful. Re-syncing an unchanged account every night would otherwise bury the interesting rows under hundreds of identical ones; so would saving a rename, which touches the account but not its balance. Both are tested.
The sync path needed care
The manual paths were easy because the old balance is one query away. Synced accounts were the awkward case, because save_accounts is a full refresh: per bank it deletes the cached rows and re-inserts them (post 8), so by the time it's looking at the new balance the old one has been deleted. The fix is a single read at the top of the function, before the DELETE, into a {(bank, account_id): balance} dict, and then a comparison per account afterwards. It's three lines, but it's the kind that goes wrong silently if someone later tidies the order. The comment above it says why it must stay where it is.
Cash events are recorded from apply_cash_changes via two extra keyword arguments on update_manual_account (event_kind, cash_changes), rather than by a second code path, so a change to a cash balance can't be logged without the balance actually changing, or the other way round. Zero-valued entries are dropped before storage, so an event only lists what really moved.
Reading it back
GET /api/accounts/events?bank=…&account_id=… returns the trail newest first. Both identifiers are query parameters, not path segments: a bank name can contain anything, and a / or a space in a path segment is a routing problem waiting for the one bank that has one. The storage layer returns cash changes as {pence: count}; the route decorates each with the label and note-or-coin kind from the same DENOMINATIONS table, so the frontend renders "Added 2 × £20 notes" without carrying its own copy of the list.
The page itself, /accounts/:bank/:accountId, is an ordinary React Router detail view like the budget drill-downs (post 15): a back link, the account's name and balance, and a list of events. Each shows what happened, when, the new balance and the change in pounds, green for up and red for down. For a cash event the individual notes and coins appear beneath, pluralised properly ("1 × £20 note", "3 × £1 coins"). Reached from a History button on every row of the Accounts page and, in the last commit, from a "View history" button on each balance chart on the home page - the chart tells you that a balance moved and the button is one click to why.
Honest about the gaps
Two limits are worth stating rather than hiding. First, there's no backfill: the table is created empty, so changes before it existed simply aren't there, and the page says so when an account has no events instead of showing a blank. Second, deleting a manual account deletes its trail - an audit log for something that no longer exists would be orphaned rows with no way to view them.
One mundane cost is worth recording too. A great many existing tests build their in-memory database table by table rather than through connect(), so adding a table that save_accounts and update_manual_account now write to broke dozens of them at once, all with no such table: account_events. The fix was mechanical - export the schema constant and create it in each fixture - but it's a reminder of what those hand-built fixtures cost: every new table is a sweep through the tests. It's an argument for a single make_test_db() helper, and a candidate for the next tidy-up.
What this adds up to
The app now covers the whole picture: open-banking accounts, a brokerage with no API, hand-tracked debts, and a wallet. The audit trail makes the last two kinds trustworthy, since "I typed that" and "the wallet changed" are now recorded rather than remembered. And the shape of the solution echoes earlier posts: pure functions for the arithmetic, per-account state in tables keyed beside the account, and a rule written down at the one place that enforces it.
Previous: ← Making the nightly sync boring · Next: Card statements and due dates → · Back to series index
