Your billing inbox is an append-only log. Our database was not.

Every spend-tracking tool starts the same way. It reads receipts out of a billing inbox, copies what it parses into rows, and then keeps scanning. The mail server holds the messages forever and never changes them. The rows are ours, and we are free to rewrite them badly.

We rewrote ours badly twice before the second fix held. Both bugs were invisible in the UI, because in both cases the UI was showing a number that could not be wrong yet.

What the first version did

The scanner matched a vendor name and a price out of each email, found the existing subscription record, and updated it. On a match, the price field was simply assigned the parsed number:

update.monthlyCost = parsedCost

There was no history in that line. If a vendor raised its price, the previous figure disappeared. If the parser misread a renewal date, the old one was gone too.

That looks harmless until someone asks the question they actually have: what changed at the last renewal? The answer requires two points in time per vendor. An overwritten value leaves one. No amount of UI work recovers a comparison from a single number.

Then the invoice table turned out to be worse

Invoices were written with a plain insert on every scan, and the table had no unique key of any kind. So scanning the same mailbox twice turned one real charge into two identical rows.

Worse than duplicates: the comparison view compared a charge against its own copy, saw equal amounts, and printed unchanged. A confident all-clear on duplicate data is worse than an empty table, because a green number stops the next question.

Two fixes, in this order

1. Give the invoice an identity

A composite unique key across the mail integration, the vendor, the invoice date, and the amount:

@@unique([emailIntegrationId, providerName, invoiceDate, amount])

It is a weak identity and we wrote the weakness down next to it: two charges of the same amount, from the same vendor, on the same day collapse into one row. We accept that because our first source is email receipts, where the alternative is no key at all. The moment a vendor invoice number or a message id is available, it should replace the composite — a vendor-issued number identifies the charge itself, not the shape of the email that announced it.

2. Write with one upsert

A unique-key conflict with an empty update:

await prisma.invoice.upsert({
  where: { compositeKey },
  create: { ...row },
  update: {},
})

A check-then-insert pair looks equivalent and is not. The scan is a long job, and two runs can pass the existence check together, then both insert. The database is the only place where that race can be decided.

The acceptance test we actually run

Run the scan twice. The invoice row count on the second run does not move. Everything else you measure — subscriptions found, vendors matched, emails parsed — is allowed to change.

If you only watch "did the scan finish", you will never notice that it finished by adding a second copy of everything.

Where this bites outside our product

The same test works by hand, and it is cheap:

1. Keep every billing email for twelve months.
2. Write one line per charge: date, vendor, amount.
3. Compare adjacent lines per vendor instead of reading a single total.

A vendor that raised its price shows up as a step. A subscription that was charged twice shows up as a pair on one date — exactly the row a "current subscriptions" screen can never show you, because it stores one number per vendor with no date attached.

The general rule outlives the product: an event log you cannot re-read is a state store you cannot audit. If the mailbox still has the messages, a rebuild is always possible. If the table is the only copy, a bug in the parser is a permanent loss.

Where we are

The unique key and the upsert have shipped, and the renewal delta view (up, down, unchanged, or first seen after connecting) is wired and compares adjacent real invoices. A mailbox still has to be connected before there is anything to compare, which is the honest state of it today.

The article that describes this on our own site is the billing inbox as an append-only log. If you want the arithmetic before the architecture, the unused-seat calculator returns a monthly and yearly waste figure with no account.

Written with an AI clerk in the loop. The bugs, the fixes, and the acceptance test are from our own commit history.