The dashboard showed more units of one camera model than the receipts recorded. I counted by hand and the receipts won: the dashboard was counting the same camera twice, and it did so with complete conviction.
An inflated number fools exactly the people who check it most. Nobody had touched that code in months, and the duplicates kept adding up without a sound.
The unique index never rejected a row
My first theory was the obvious one: that the uniqueness constraint, the rule that stops the same row from being saved twice, had been dropped in some migration. I went to check and it was active. It hadn't rejected a single row in all those months, which is why nobody suspected it.
The records live in a spreadsheet that a script syncs into the database, and that script built each sale's key from the position of the row inside the sheet.
When a block of sales got pasted again further down, because the order shifted or because it got copied from another tab, those rows got new positions. New positions meant new keys, so the unique index compared different keys and let them through without an error in the logs.
It's like counting the guests at an event by writing down the chair each one sat in: if somebody stands up and changes seats, you count them twice, because the chair doesn't tell you who was there. To count properly you have to ask each guest for their ID.
Wait, the key was the row position?
It always was
A sale's ID card
The only thing that identifies a physical camera is its serial number. The same camera can't show up twice on the same receipt, so the key became a unique index on serial plus receipt.
I made it partial on purpose: sales without a serial number don't collide with each other, and a camera that comes back on an exchange and sells again under another receipt is still a valid sale. With the serial alone I would have blocked legitimate cases.
Straps don't have serials
Batteries, filters and straps enter the sheet without a serial number, so they needed another rule. I tried the broad one first: if a row showed up repeated further down, and its neighbours repeated at the same distance, I treated it as a pasted block and deleted it. I ran it dry, deleting nothing and only counting, and it would have deleted legitimate sales, receipts where somebody did buy three identical straps. It was the tidiest deletion rule I ever wrote and also the most wrong.
I swapped it for a conservative cutoff: delete a repeated block only if the rows are contiguous and span at least 2 different receipts. A receipt with three straps is normal; several receipts in a row repeating identically and in the same order is a re-pasted block. I'd rather leave a doubtful duplicate in the table than delete a good sale.
A cleanup reviewed by hand and an auditor that doesn't delete
The duplicates came out in a single cleanup, reviewed one by one before deletion and with no false positive. The dashboard squared with the receipts again.
The auditor script stayed running: it walks the table, reports candidate duplicates and deletes nothing on its own, because deletion requires a confirmation typed by hand. The contiguous-block rule is an approximate criterion, and an approximate criterion doesn't deserve the delete button.
The cp backup came out incomplete
Before deleting anything I copied the SQLite database with cp, the way I copy any file. SQLite in WAL mode (write-ahead log: a separate file where the database notes recent changes before folding them into the main one) keeps the latest transactions outside the main file, so the backup came out without exactly what mattered to me. Now I take backups with VACUUM INTO, the database's own command.
These days every deletion rule gets a dry run first, and before accepting a deduplication I check whether the key describes the real-world object or the place where it was written down. The dashboard still counts with the same conviction, but now it asks every camera for its ID card before adding it up: the serial number.
