Why does a scanned price overwrite matter if I only care about what I pay now?

Because the question after a price increase is a comparison, and an overwritten value has nothing to compare against. If the scanner replaces last month amount with this month amount, the only number left is the new one, and no screen can show the size of the jump.

What goes wrong when invoice rows have no unique key?

One real charge can be written twice if the mailbox is scanned twice. A comparison feature then compares a charge against its own copy, finds equal amounts, and reports no change. That is worse than an empty table, because nobody looks twice at a green number.

What is a safe identity for an invoice row?

The providers own invoice number or the message id is the strong form. When only email receipts are available, a composite of the mail integration, the vendor, the invoice date and the amount is the practical one, with its weakness written down: two identical charges on the same day collapse into one row.

SaaSClerk Blog · How the invoice ledger is built

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

Short answer: an email is an event and a database row is a projection of it. If the projection cannot be rebuilt from what is still sitting in the mailbox, then every scan is a partial rewrite of the only record you have. Two small decisions keep that from happening: never overwrite a parsed price without a history row, and give the invoice table a key before a single scan writes to it.

Every spend tool starts the same way. It reads receipts out of an 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.

What the first version did

The scanner matched a vendor name and a price out of each email, found the existing subscription, and updated it. On a match the price field was assigned the newly parsed number. There was no history in that line: if a vendor raised its price, the previous figure disappeared, and the dashboard could only show today.

That looks harmless until someone asks the question they actually have: what changed at the last renewal. The answer needs two points in time per vendor. An overwritten value leaves one.

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. Scan the same mailbox twice and one real charge becomes two identical rows. A comparison view built on that data compares a charge against its own copy, sees equal amounts, and prints unchanged. A confident all-clear on duplicate rows is worse than an empty table, because a green number stops the next question.

Two fixes, in this order

Give the invoice an identity. A composite unique key across the mail integration, the vendor, the invoice date and the amount. It is a weak identity and the weakness is written down below.
Write with one upsert. A unique-key conflict with an empty update. A check-then-insert pair looks equivalent and is not: the scan is a long job, and two runs can pass the check together.

The acceptance test is short: run the scan twice and the row count on the second run does not move. Anything else you measure, including the number of subscriptions found, is allowed to change.

Where the weak identity bites

Two charges of the same amount, from the same vendor, on the same day collapse into one row under that key. We accept it because our first source is email receipts, where the alternative is no key at all, and the moment a vendor invoice number or a message id is available it should replace the composite.

The general rule outlives this 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.

What this means when you audit your own spend

The same test works by hand. Keep every billing email for twelve months, one line per charge with its date and amount, and compare adjacent lines per vendor rather than reading a single total. A subscription that raised its price shows up as a step. A subscription that was charged twice shows up as a pair on one date, which is exactly the row a subscription list built from a single current total can never show you.

SaaSClerk keeps that ledger from one mailbox, read-only, and compares adjacent invoices per vendor: up, down, unchanged, or first seen after connecting. The scan is free against one mailbox. The honest status of the feature today is that the key and the comparison have shipped and the renewal delta view is wired, while a mailbox still has to be connected before there is anything to compare.

Run the arithmetic first

Before changing how you track anything, price the waste you already have: the unused-seat calculator takes seat counts and prices and returns the monthly and yearly figure, with no account required.