Personal finance aggregator: Seeing everything in one place - Part 11

~9 min read

By post 8 the app could pull balances and transactions from every linked bank. What it couldn't do was make that data legible - or hold the parts of my financial picture that no bank API will ever give me, like a car loan. This post is a run of smaller commits, spread over 28-29 September, that were each about the same thing: making the Accounts and Home pages answer "what do I actually have, and what do I owe?" without squinting.

It's less a single design story than five related fixes, ordered roughly by how much they taught me.

Aliases: names the bank chose aren't names I'd choose

Banks label accounts however their own systems do. The add account alias commit lets me rename any account on the Accounts page, and the interesting part is where the alias is stored:

ACCOUNT_ALIASES_SCHEMA = """
CREATE TABLE IF NOT EXISTS account_aliases (
    bank TEXT NOT NULL,
    account_id TEXT NOT NULL,
    alias TEXT NOT NULL,
    PRIMARY KEY (bank, account_id)
);
"""

A separate table, not an alias column on accounts. The docstring says why: it "survives save_accounts()'s full-refresh-on-every-sync". The accounts table is a cache that gets deleted and rewritten on every sync, because balances change; anything I typed by hand into that table would be wiped the next night. The rule I took from it: user-authored data never lives in a table the sync owns. That same rule comes back for manual accounts below, and for categories in post 12.

There's one awkward join. Transactions only carry an account's display name, not its account_id, so showing an alias next to a transaction means joining through accounts on (bank, name) - the weaker key - rather than (bank, account_id). The code has a comment owning that compromise.

A debug page, because I kept wanting to look inside

The add debug data page commit added a page that lists every raw table and dumps it, and later the raw JSON files that fetches cache under storage/inbound/. When data looks wrong in a dashboard, the first question is always "is it wrong in the database, or wrong in how I'm showing it?", and without this I answered that by opening sqlite3 on the server.

Two small bits of care are worth recording because the page reads arbitrary-looking input:

• dump_table() has to interpolate a table name into SQL (SQLite can't bind an identifier as a parameter), so it checks the name against the TABLES tuple first. The docstring is blunt that this check "is the only thing standing between it and arbitrary SQL."
• The file endpoints never build a path from the request. The requested filename must match a name that iterdir() itself returned, so ../ or odd encodings can't reach outside the cache directory.

(It was later split into global and per-user sections - see post 10 - and gained a sync-log viewer in post 17.)

Accounts versus cards, and logos

A later commit added a "Credit card" badge to the Accounts page, so the two kinds of thing are distinguishable at a glance - the kind field had existed since the very first fetch, it just wasn't shown. Another added provider logos: TrueLayer's connection metadata includes a logo_uri, so fetch() captures it and stores it as provider_logo_url on each account. Same deal as the schema changes before it: a PRAGMA table_info check adds the column to an existing table, which is safe because accounts is a full-refresh cache with nothing to migrate.

"Synced N ago": the data was already there

One small commit is a good reminder to check what you already store. The connections table had carried an updated_at timestamp since the sync started writing it; it just wasn't rendered. The commit adds a relative "Synced 3 hours ago" note beside each bank's account count. A stale sync is the most common reason a number looks wrong, and until then the app gave no hint of it. The commit message also records how it was verified: seeding a scratch storage tree and driving the real dev servers with Playwright, with auth headers faked via route interception since there's no login page any more.

The sign of a debt

This is the one with an actual bug in it. In post 8 I excluded cards from the Total balance because a card's balance is money owed. But TrueLayer reports that figure as a positive number, while this app's convention everywhere else is the same as a transaction: negative means money out. The Fix card balance sign... commit fixes it at the source:

TrueLayer reports a card's "current" balance as a positive amount owed; fetch() now negates it for kind == "cards" so it matches this app's own convention (negative = debt, same sense as a negative transaction amount) instead of looking like money held. "available" (remaining credit) is untouched - it isn't debt.

The detail I like is the last sentence: available is remaining credit, a positive amount you can still spend, not a debt, so it keeps its sign. Only the field that actually means debt flips. The same commit adds an "Account balances" section to Home, listing every account and card - cards in red, other accounts in green - and the verification note spells out the check: a card renders as -£250.00 in red, a current account as £1,234.56 in green, and Total balance still excludes the card.

(There's a sequel. Card transactions have the same problem - they arrive as positive magnitudes with a transaction_type saying which way the money went - and it took until a later commit to normalise those. See post 15.)

Things the bank can't tell you: manual accounts

Another commit adds the other half of the picture: a loan, say, or anything not reachable through open banking. It also adds totals to the Home page: a "total credit" (the sum of real accounts) and a "total debt" (the sum of everything else - cards, and now manual accounts).

A manual account is a name and a balance you maintain from a "Manual accounts" card on the Accounts page. The storage decision follows the rule from the alias section: it gets its own manual_accounts table rather than a column on accounts, because accounts is wiped on every sync and a manual account "has no sync source, so it can never live there." To avoid a parallel code path everywhere, list_accounts() UNION ALLs the two tables, so manual accounts flow through every existing consumer - the list, the alias endpoint, the debug view - without each learning about them. The UI generalises the colour split from kind === 'cards' to kind !== 'accounts', so the red-for-debt styling covers them with no special case.

The balance is entered as a positive "amount owed" and stored negated, the same convention as a synced card. That was the first version. A follow-up, later the same day, made the account type selectable - debt, or savings/credit - after noticing "previously every manual account was hardcoded as debt." It also fixed a bug found in the process: switching an account's kind without re-entering its balance left the balance with the wrong sign. If you store a sign, changing the thing that determines the sign has to re-store the number.

What these have in common

None of these is large; the sign fix is one line of arithmetic. But taken together they're the difference between a dashboard that holds data and one you can trust at a glance. Two rules fell out that I've kept applying: keep user-authored data out of tables a sync rewrites, and pick one sign convention and normalise at the boundary where data enters.

Previous: ← One storage tree per user · Next: Rule-based categorisation →