The uniqueness check that lied to me for months

The worst bugs don't crash. They smile at you.

The one that taught me the most sat quietly in production for the better part of a year, doing exactly what I'd told it to do, corrupting a little more data every week, and never once throwing an error. I found it the way you find most of these: someone in finance pulled a report, the numbers didn't reconcile, and a chain of "that's weird" led eventually to my code. I remember the exact feeling — not panic, something slower and worse. The dawning certainty that the thing you were proud of has been lying to you for a long time.

Here's what I'd built, and why it looked so reasonable.

The code that looked careful

We had a rule: a given external ID could only ever map to one record. One customer, one account row. Simple invariant. And I enforced it the way most people enforce it the first time, before they've been burned:

row = db.find(external_id)
if row is None:
    db.insert(external_id, ...)
else:
    db.update(row.id, ...)

Look at it. It's defensive. It checks first. It doesn't blindly insert. In code review it reads as the careful version — the junior would have just inserted and hoped, and here I am handling the exists-case explicitly. Everyone nodded. I nodded. It shipped.

The bug is the gap between line one and line three. Between the find and the insert there is a window — microseconds, usually — where another request can run the exact same find, also get None, and also decide to insert. Two requests, same instant, same external ID, both convinced they're the first. You get two rows where the universe permits one. This is a read-then-write race, and it is one of the oldest mistakes in the book. I knew what a race condition was. I could have lectured you on them. I still wrote this one, because under the pressure of a real feature it doesn't look like a race — it looks like diligence.

Why it hid for months

If the bug had duplicated every record, I'd have caught it in a day. It didn't. It duplicated the ones that got hit twice in the same eyeblink — a retry that fired a hair early, two tabs, a webhook delivered twice, a mobile client on a flaky connection re-sending. A tiny fraction of traffic. A few rows a week.

And the aggregates hid it. The dashboards summed over everything, and a handful of doubled rows in a sea of correct ones moves a total by a rounding error. Every high-level number looked fine. The corruption was real but it was below the noise floor of every report anyone actually looked at — until one customer's account happened to accumulate enough duplicates that their individual total went visibly wrong, and someone finally pulled the thread.

That's the trap with silent corruption. It doesn't announce itself when it happens. It waits until the damage is large enough to surface on its own, by which point it's everywhere and it's old.

Three weeks of "works on my machine"

I could not reproduce it. That was the part that nearly broke me.

Locally, single-threaded, my careful check worked perfectly every single time — because locally there's no concurrency to open the window. I wrote a test. The test passed. Of course it passed; the test called the function once and asked if the result was right. A test that runs your code once can never catch a bug that only exists when your code runs twice at the same time. I stared at green checkmarks for days while production kept quietly minting duplicates.

The breakthrough wasn't clever. It was mechanical. I stopped trying to understand it in my head and started trying to force it: I wrote a script that fired the same request a few hundred times concurrently, hammering one external ID. On the first run — duplicates. There it was, reproduced on demand, ugly and obvious. The moment you stop testing "does it work" and start testing "what happens when fifty of these land at once," the bug isn't subtle anymore. It was never subtle. I'd just been asking it the wrong question.

That's the lesson I'd tattoo on a junior if I could: a race condition is invisible to any test that doesn't create contention. If your concurrency test isn't concurrent, it's theater.

The fix was the easy part

The fix took an afternoon. You move the invariant to the one place in the whole system that actually serializes concurrent writers: the database.

UNIQUE constraint on external_id

That's it. That's the whole thing. With a real unique constraint, the second insert doesn't race — it fails, loudly, and the application catches the failure and does the update instead (an upsert / INSERT ... ON CONFLICT). The window closes because the database refuses to hold two rows, and it refuses atomically, which my application code could never do no matter how carefully I wrote the check.

This is the part everyone gets wrong, and I got wrong for years: an invariant enforced above the database is a suggestion. Your application is many processes on many machines; none of them can see what the others are doing in the microsecond that matters. The database is the one place where concurrent writers are forced into a line. If a rule must be true — uniqueness, a non-negative balance, a foreign key that really exists — it belongs where the serialization happens, as a constraint, not as an if statement hoping to win a race it doesn't even know it's running.

The part that almost couldn't be fixed

Here's why I said "almost." Stopping new corruption was an afternoon. Undoing months of existing corruption was three weeks of the most careful work I've ever done, and there was a real chance the answer was going to be "you can't."

You can't just delete duplicate rows. Which of the two is the real one? They'd both been updated since — payments applied to one, a profile edit on the other. The duplicates weren't copies; they'd diverged. Merging them wrong would turn a data-integrity bug into a data-loss bug, and this time it'd be me deleting real customer history on purpose.

What saved me was something I'd had the sense to build for an unrelated reason: an append-only audit log of every write. Every mutation, in order, with a timestamp. It meant the current corrupted rows weren't the only source of truth — I could replay the history for each tangled ID and reconstruct what the single correct row should have been, event by event. I wrote a reconciler that, for every duplicated external ID, walked the log, rebuilt the intended state, wrote it to the surviving row, and archived the other. I ran it in dry-run against a copy until the output stopped surprising me. Then I ran it for real, in batches, checking totals after each one.

It worked. Every account came back to a single correct row, and the finance numbers reconciled to the cent. But I want to be honest about the margin: if I hadn't happened to have that audit log, the corrupted data would have been genuinely unrecoverable. The invariant would have been restorable going forward and the past would just have been lost. I got to fix it because a version of me from a year earlier had, for a completely different reason, made the system remember what it did. That was luck wearing the costume of foresight.

What I actually took away

Two things, and they've outlasted every framework I was using at the time.

Put invariants where the concurrency is serialized. If a rule has to hold, it goes in the database as a constraint. Not because app-level checks are lazy — mine wasn't lazy, it was careful — but because careful can't beat a race, and only the database gets to decide the order.

Make violations loud. The reason this cost months instead of minutes is that the failure was silent — it corrupted instead of crashing. Given the choice, I now design so that a broken invariant throws. A loud failure at 2 a.m. is a page and a fix. A silent one is a finance report a year later and a three-week excavation. I'll take the page every time.

I still write the careful-looking check sometimes, out of habit. The difference is that now there's a unique constraint underneath it doing the actual work — and I know which one of them I'm trusting.