Personal finance aggregator: Rule-based categorisation - Part 12

~10 min read

The category column has been in the transactions table since post 4. For two weeks of commits it held nothing, and the Home page's "Spending by category" chart ran on a constant called SPEND_BY_CATEGORY (post 5). This is the post where that finally became real. It's the biggest single feature in the project so far - the first commit alone is a large one - and most of the interesting decisions are about what not to store.

The shape: three sources, one precedence order

I wanted to avoid two failure modes. A pure rules system means writing a rule for every Netflix-shaped thing. A pure automatic system means being stuck when it guesses wrong. So a transaction's category comes from three places, in strict order of authority:

A manual override on that transaction.
A keyword rule I've written.
A detected subscription (a heuristic, below).
Otherwise, Uncategorised.

That's the whole of resolve_category():

def resolve_category(transaction, rules, subscription_keys):
    if transaction.get("category"):
        return transaction["category"]
    rule_match = match_rule(transaction, rules)
    if rule_match:
        return rule_match
    if (transaction["bank"], transaction["id"]) in subscription_keys:
        return "Subscriptions"
    return UNCATEGORISED

The ordering has a nice property: the heuristic is a fallback, never a verdict. If it wrongly calls something a subscription, I write a rule and the rule wins.

Rules: a substring match, and no priority field

A rule is a keyword and a category. It matches if the keyword appears, case-insensitively, in the transaction's merchant or description. When several rules match, the longest keyword wins, with ties broken by which rule was created first:

best = min(matches, key=lambda rule: (-len(rule["keyword"]), rule["created_at"]))

The reasoning is in the docstring: "more specific beats generic (a TESCO EXPRESS rule outranks TESCO for the same transaction)". And the payoff of computing priority from keyword length, rather than storing a priority number, is a sentence I enjoyed writing in the commit message: it avoids "needing a rule-reordering UI". There's no priority to drag around, because specificity is the priority.

Subscriptions: a heuristic with numbers I can defend

The subscription detector groups debits by (bank, account, merchant, amount) and flags a group when it has at least three occurrences, each 21-40 days after the last. Every threshold has a reason written next to it:

Three occurrences is "the smallest count that's both a meaningful repeat signal and actually reachable" inside the 90-day sync window; two same-amount debits to one merchant is easy to hit by coincidence.
21-40 days covers calendar-month variation (28-31 days) plus a few days of direct-debit drift, "without also catching fortnightly or quarterly charges".

Note the dependency: those numbers are sized around the sync window. (The window later shrank to 30 days for routine syncs - see post 17 - but detection runs over everything stored in the database, which accumulates beyond any single fetch, so it isn't limited by it.)

Never store the answer

The most consequential decision: categories are computed on every request and never persisted, except the manual override, which has to be. The commit message gives the reasoning: a personal-scale SQLite database "is cheap enough to recompute over on every request, and it means editing a rule reclassifies existing history immediately with no backfill step."

I got to confirm that directly in the smoke test, and it's worth quoting because it shows what the design buys: add a "tesco" rule in Settings, and TESCO's row flips from Uncategorised to Groceries with no resync, while Netflix charges - three occurrences, 28-30 days apart - were already showing as Subscriptions before any rule existed. A stored category would have needed a migration for every rule edit. Computing it means a rule change is instant and reversible.

The cost is that the API has to hand the frontend both values. apply_categories() returns the resolved category and also keeps the raw stored value as category_override, because the UI has to tell "auto-resolved to Bills" apart from "explicitly set to Bills" when it draws the override dropdown.

Categories become user data

The first version had a fixed set of six categories in a Python tuple. A later commit (Make categories user-configurable...) replaced it with a per-user categories table, seeded once with the original six and their colours so existing data didn't change. Two consequences are worth noting.

Renaming cascades. Rules and overrides store the category as a plain string, not a foreign key, so renaming "Eating out" has to rewrite every rule and every override that used the old name. A string key was the simple choice, and the cascade is the price.

Colour follows identity, not rank. The chart colours come from the validated palette in post 5, which I'd checked for colour-blind distinguishability. The rule I stuck to is that a category's colour comes from a stored slot, not its position in a sorted list - otherwise "Groceries" would change colour whenever it overtook "Bills". Adding categories meant extending the palette beyond the original five hues, and I re-ran the validator script against the app's real surface colours, in light and dark, rather than eyeballing new ones. Slots 1-7 are assignable and slot 8 is reserved for Uncategorised. A later change removed the hard ceiling: once all seven validated colours are taken, a category can use a custom colour from a picker instead, with color_slot and custom_color kept mutually exclusive.

Transfers aren't spending

Another change fixed a distortion I'd otherwise have lived with. Moving £500 from current to savings isn't income on one side and spending on the other, but a naive sum treats it as exactly that. The fix is an is_transfer flag on a category: transactions that resolve to a flagged category stay fully visible and filterable on the Transactions page, but drop out of the income/outgoings summary and every spending-by-category view. Because that exclusion depends on resolved categories - which come from rules and overrides - it can't be done in raw SQL, so the SQL monthly_summary from post 7 was replaced by a Python summarize() that runs after categorisation.

The UI that makes rules cheap to write

A rule system only works if adding a rule is nearly free. Three small UI changes did that:

Each row on the Transactions page has a category dropdown for the manual override, and a later commit added a category filter built from the categories actually present in the current results - so Uncategorised is filterable, and a category nothing resolves to doesn't clutter the list.
Rules in Settings are nested under their category, so it's obvious which categories have no rules yet.
The add quick add rule commit puts a + beside each transaction's category that opens a modal with the keyword pre-filled from the merchant and the category pre-selected. After saving, the page refetches, because the new rule may re-categorise other rows. A follow-up (enable modal for categories) moved the category add/edit forms in Settings into a modal too.

Two bugs worth owning

Another commit fixed a small one that's a common trap: GET /api/categories was returning is_transfer and is_manual as SQLite's raw 0/1 instead of JSON booleans, because that route had no response_model to coerce them. A response model isn't only documentation - it's the layer that turns a database's idea of a boolean into the API's.

The same commit added a "Hide Uncategorised" toggle to the Home spending cards, for the common case where the unsorted remainder dwarfs everything you've categorised and flattens the chart.

Previous: ← Seeing everything in one place · Next: Periods, charts, and a phone-sized UI →