The dashboard showed twice the cameras we sold
The internal portal showed 8 units sold of a camera model the store had sold 4 times. I counted the month's receipts by hand and the receipts won: September so far reported 21 units against 14 sold, 50% air in the number I use to decide what to restock. The dashboard counted the same camera twice and did it with complete conviction.
I negotiate quotas and orders with the brands using that number. A 50% inflation makes me buy stock I won't move. Nobody had touched the sales code in months, and the duplicates had been showing up every month since April.
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 since April, which is why nobody had noticed a thing.
The dashboard doesn't read the ERP database, the store's inventory and invoicing system. It reads a spreadsheet the team fills in by hand, and a script syncs it every night. That script built the key for each sale from the position of the row inside the sheet.
When somebody pasted a block of sales again further down, because the order shifted or because they 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 camera is its serial number. A physical 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 as an exchange and gets resold under another receipt is still a valid sale. With the serial alone I would have blocked real cases from the counter.
Straps don't have serials
Batteries, filters and straps go into the sheet without a serial number. There I tried the broad rule first: delete any row repeated with the same offset relative to another. I ran it dry and it would have deleted around 50 real sales, from receipts where the customer 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 cover at least 2 different receipts. A customer can buy three straps; three 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.
76 rows fewer and an auditor that doesn't delete
76 duplicate rows came out, from 3,171 to 3,095, reviewed one by one against the receipts before the deletion and with no false positive. September so far now matches the sales sheet we keep on the side: the 14 real units are the ones the dashboard shows.
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 approximate and doesn't identify the sale, so while accessories get recorded without a serial, the control depends on somebody reading that report.
The cp backup came out without the latest sales
Before deleting the 76 rows 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.
I wrote three rules into the module's documentation. Before accepting a deduplication I check whether the key describes the real-world object or the place where it was written down. Every deletion rule gets a dry run that counts how many good rows it would have taken. And if a unique index goes months without rejecting anything, I go and see what it's comparing. The dashboard still counts with the same conviction, but now it asks each camera for its serial number before adding it up.
