I kept a spreadsheet of my own spending. It decayed after thirteen days. So I did what everyone does and installed an expense tracker, and it told me I had spent ₹696 on a lunch that cost ₹348, because the bank SMS and the monthly statement had both seen it, and the app had no way to know they were the same meal.

That is not a bug in one app. It is the default outcome of the schema every one of them uses.

The line of DDL that causes it

The obvious way to stop duplicates is a uniqueness constraint:

UNIQUE (user_id, source, external_id)

The source is part of the key. So an SMS saying ₹348 and a statement row saying ₹348 can never collide. Not because the matching is weak, but because the schema has made agreement structurally impossible. Two rows. ₹696. I wrote this constraint myself before I understood what it did.

A transaction is not a row you insert

It is a conclusion you reach.

So the primitive is not the transaction, it is the observation: what one source claims it saw, written to an append-only log that is never edited and never deleted. A new observation is matched against the ledger on amount, account and a date window. A hit attaches it to the existing transaction as another witness. A miss creates a new one.

Deduplication stops being drop the row and becomes add a witness, and one mechanism now handles overlapping statements, cross-source confirmation, provenance and conflict, instead of four special cases.

It is event sourcing, without needing Kafka to say so. Money events arrive at human speed, and the projection lives in the same Postgres transaction as the event, so merges are atomic instead of eventually consistent.

The payoff is that why does this say ₹348 has an answer. Two observations, matched on a one-day UPI window, statement won on amount, SMS kept its timestamp. Nothing about the number is unexplainable.

Every number ships with its denominator

An average is a fraction, and a fraction with an unstated denominator is a guess wearing a decimal point. If the app has SMS coverage for nine days of a thirty-day month, the honest output is ₹412 a day over nine days, not a monthly total that silently treats twenty-one missing days as zero spending.

So the system tracks coverage windows per account per source, and there is a screen whose entire job is to show what the app cannot see. It is the least marketable feature in the product and the reason the rest of it can be trusted.

Where the model is, and where it is not

Deterministic, always: every total, average and percentage. Duplicate matching. Transfer detection. Recurring detection. Balance validation.

Model-assisted and reversible: turning BHARATPE9847 into the tea shop. First-pass category for a description no rule has seen. Phrasing facts that have already been computed.

Never: computing an amount, deciding what is a duplicate, writing to the ledger.

Categorisation runs on a pinned local embedding model rather than an API, because a classifier you bill per transaction cannot be unit-tested, cannot be asserted on in CI, and ships every merchant name you have ever paid to someone else’s server. A cosine score can be pinned to a number in a test. A vendor’s new model version cannot silently re-categorise last month.

The parts that are hostile

A bank statement is attacker-controllable input the moment you accept uploads: decompression bombs, malformed xref tables, nested streams. That parser runs in its own process: non-root, no network, hard memory and CPU and wall-clock caps, read-only filesystem, tmpfs scratch, allowed to die. It is the only process boundary in the system that earns its keep. The password for an encrypted statement lives in memory and never touches disk.

What I got wrong

I audited the whole thing before a user could, and wrote the results down unedited. Totals were computed per import, so importing ₹500 and then ₹300 displayed ₹300. The review queue existed, worked, and was unreachable from any real code path. Row-level security was enabled but not FORCEd while the application owned the tables, which means it was decorative. The webhook had no authentication of any kind. Money was NUMERIC(18,2) in Postgres, float in Python and REAL in SQLite, so development and production could disagree on a rounded total.

All of those are fixed now, each in its own migration and its own commit. None of them were hard. They were the difference between a demo and something that handles other people’s money.