On the second of January we started overwriting last year's orders

Finance reopened the previous year's wholesale figures in the second week of January and found that a total they had signed off in December had moved. About eleven thousand pounds, downward, on a set of books that were closed.

We had been taking order confirmations from a wholesale partner for three years. Each confirmation carries a reference number, described in their documentation as unique, and we had used it as the natural key: an upsert on their reference, so that a resend or a correction updates the row rather than creating a second one. That had worked perfectly through several thousand orders and two rounds of resends.

Their reference numbers restart at one every January.

They are unique within a year, which is genuinely what their system guarantees, because their own database holds the year in a separate column and their interface has never needed to show it. It is not in the payload. It is not in the documentation, which says unique and means unique here. And we had onboarded them in March, so we had never once seen a year boundary with this partner until the one that broke us.

So on the second of January, order number 1 arrived and updated the row written on the second of January the year before. Then 2, then 3. Nine hundred and forty rows of last year's orders had their amounts, addresses and line items replaced by this year's, silently and successfully, by a mechanism we had built specifically so that repeated messages would be harmless.

Rebuilding took four days from their export and a backup.

Two things changed. The key for that integration is now the reference together with the year we derive from the confirmation date, under a rule we wrote down with them in an email that both sides keep. More usefully, the upsert no longer pretends that everything is an update. If a write would change a field we consider immutable on an existing row, the customer, the amount, the original date, it fails loudly instead of succeeding, which caught two unrelated bugs in its first fortnight.

Then we went through every external identifier we store and wrote down the scope it is unique within. Three of the eleven were narrower than we had assumed. Unique is never a complete sentence, and the missing half of it is unique within what.

– Sergey Shinder