The billing job ran twice for six weeks and the totals still balanced

The most expensive bugs don’t crash. They compute confidently, produce plausible output, and get verified by something that shares their assumptions.

This one cost us about eleven thousand pounds in refunds, a considerably larger amount in goodwill, and one very bad conversation with a customer’s finance director who had noticed before we did — which is the detail that made it a real incident rather than an anecdote.

The job

Usage-based billing. A nightly job walked the previous day’s metered events, aggregated them per account, and wrote charge records. Straightforward. It had run every night for something like three years.

It took about eighteen minutes. It was scheduled hourly during a catch-up window — a design decision from before my time, intended to let it retry if a run failed, with the aggregation being idempotent by account and date so a re-run would just overwrite.

Except the idempotency was implemented as “delete existing charge rows for this account and date, then insert the new ones.” Which is idempotent when the job runs alone, and is something else entirely when two copies run at the same time.

The growth curve

The event volume grew. Gradually, in the way that never triggers anything, because every week was only slightly bigger than the last.

Eighteen minutes became twenty-five. Then forty. Then, on the first heavy month of the year, seventy — and the job was on an hourly schedule.

So at some point past the hour mark, run N+1 started while run N was still going. Both walked the same event set. Both did the delete-then-insert. And in the window between one job’s delete and its insert, the other job’s insert had already landed — so the delete removed rows the other run had just written, and then both inserted their own copies, and depending on exactly how the interleaving fell, an account could end up with its charges present once, twice, or occasionally not at all.

Mostly twice. The double-insert was the most common interleaving because of how the batches were ordered, though we only worked that out afterwards, with a whiteboard and considerable bad language.

Why nothing alerted

This is the part worth reading.

The job exited zero. Both copies did. Neither hit an error — they were performing legal database operations, in a legal order, with no constraint that made a duplicate charge row invalid. From the process’s perspective nothing whatsoever went wrong.

The monitoring was on job completion. Green. Twice as green as usual, in fact, though nothing was counting invocations.

Runtime alerting didn’t exist. Nobody had ever set a threshold on how long the job took, because for three years it had taken eighteen minutes and there was no reason to think about it. The gradual creep from eighteen to seventy happened over about fourteen months and every individual day looked like the previous one.

And the reconciliation report matched. Every day. This is the one that cost us the six weeks.

There was a daily reconciliation: total charges generated versus total usage metered. It ran, it matched, somebody’s dashboard was green, and everybody who looked at it concluded billing was healthy.

It matched because the reconciliation query aggregated the charge table with the same grouping logic the billing job used. Same helper module. Same assumption that a given account-and-date appeared once. The report was summing the duplicate rows into a single group and comparing that to the usage total — and the sum of a duplicated set, grouped and deduplicated by the same flawed logic, comes out correct.

We had built a verifier that shared a code path, and therefore a blind spot, with the thing it was verifying. That’s not a monitoring gap. That’s a monitoring system that was structurally incapable of detecting the failure it existed to detect.

How we found out

Not from our systems. A customer’s finance team ran their own reconciliation against their own usage records and emailed to ask why two identical charges appeared on the same date, twice in one month.

The first response internally was that they’d misread their invoice. I want to be honest about that, because it’s the ugliest part of the story: the first hour after that email was spent looking for an explanation that didn’t involve us being wrong. Our dashboard was green, our reconciliation matched, so obviously the customer was mistaken.

Somebody eventually ran SELECT account_id, charge_date, COUNT(*) ... HAVING COUNT(*) > 1 against the raw table — a query nothing in our stack had ever run, because everything queried through the aggregating helper — and got several thousand rows back.

The room went quiet. That’s not a figure of speech; I remember the specific quality of the silence.

The fix, in three parts

Immediately: a lock. One line in the crontab.

0 * * * * /usr/bin/flock -n /var/lock/billing.lock /usr/local/bin/billing-run

flock -n takes an exclusive lock and doesn’t run if it’s already held. The lock is released when the process dies, however it dies, including SIGKILL and including a power cut — which is precisely why it’s better than the pidfile somebody proposed instead. Six weeks of damage, prevented permanently, by fourteen characters.

Then: a real uniqueness constraint. A unique index on (account_id, charge_date, line_item_type). The database should have been refusing the second insert from the beginning. The application-level “we only insert once” assumption had no enforcement behind it, and an invariant with no enforcement is a comment.

Adding that constraint to a table with existing duplicates was its own two-day project, which is a good argument for adding constraints while the table is small and boring.

Finally: verification that doesn’t share code. The reconciliation was rewritten to query the raw tables directly, with its own SQL, written by someone who deliberately hadn’t read the billing job. It’s uglier. It duplicates logic. Somebody flagged that in review as a maintainability concern and they were right.

They were right and we shipped it anyway, because a verifier that shares implementation with the thing it verifies is not a verifier. It’s a second opinion from the same person. The whole value of an independent check is the independence, and “don’t repeat yourself” is exactly the wrong instinct here — the repetition is the mechanism.

What I take from it

Two things, and the second one is the one I actually think about.

The mechanical lesson: any job that can run concurrently with itself, will. Not on day one — on the day the data grows past the schedule interval, which is a day nobody marks on a calendar. flock costs nothing. Put it on every periodic job you own, including the ones that “obviously finish in seconds,” because that’s what this one did for three years.

The other lesson is about the dashboard. We had a green reconciliation report and it made us slower, not faster. It gave everyone a reason to dismiss the customer’s report. A monitoring system that can’t detect a class of failure is neutral; one that actively reassures you about a failure that’s happening is worse than nothing at all.

When you build a check, the question isn’t “does this pass when things are healthy.” It’s “what would have to be true for this to pass while things are broken.” We never asked. The answer was: they’d have to share a bug. And they did.