One question, nine implementations, eight of them wrong
After fixing the endpoint that wrote to the wrong row, the obvious next question was whether anything else in the system resolved a driver the same careless way. The answer was eight more places.
The list is worth reading because of how ordinary it is: the bookings list, the booking detail screen, a lookup by reservation code, an active-booking view, a photo-fetch endpoint, two customer-facing endpoints, and the booking PDF generator. Every one of them answered “who is the driver on this leg” with its own single-row or multi-row fetch on the booking id and the leg name. None filtered out soft-deleted rows. None ordered the results.
One place did it correctly — the assignment board — because it had been rewritten a fortnight earlier and the author had happened to think about it.
What that costs, measured
Before touching any code, the population was measured, because “this could be wrong” and “this is wrong on 81 bookings” are different conversations to have with a client.
Eighty-one legs across eighty production bookings had both a live row and a soft-deleted row, so the answer those nine endpoints returned was arbitrary. On sixteen of them the two rows named different people, which means the screen was naming the wrong driver — not failing, naming somebody else.
Eighteen legs had only a soft-deleted row, of which seventeen named a driver. Those endpoints reported a phantom assignment: a driver, with a name and a phone number, on a leg the assignment board correctly showed as unassigned. Two systems, same data, opposite answers.
The identical counts appeared in the test database, which mattered because it ruled out the possibility that this was an artefact of one production clean-up rather than a code defect.
The cause is not carelessness, it is the absence of an answer
Nine developers-worth of code wrote nine lookups because there was nothing to call. “Who is this leg assigned to” is a domain question with a real answer — the newest live row for that leg — and that answer existed nowhere as a function. It existed as a habit, imperfectly held.
Any business question asked in more than two places needs to exist as one callable thing. Not a documented convention, not a comment, not a base class. A function with a name that is the question. The rule is not about duplication for its own sake; it is that a convention held in nine heads decays, and the decay is invisible because each individual copy looks reasonable in isolation.
So a single utility now answers it: soft-deleted rows excluded, newest live row first, leg matched by prefix. Every one of the nine read paths calls it.
Prefix matching, and a mistake made twice in one afternoon
The leg name is free text. It has been written at least six different ways over the life of the system, by different screens and different imports.
The first draft of the shared utility matched it against a fixed list of spellings — which is precisely the mistake that another service’s own code comments warn about, a few files away, in words to the effect that a list silently drops any spelling nobody thought of. Having read that warning while investigating a different bug, I then wrote the thing it warns against, and caught it before wiring anything up.
It matches on a case-insensitive prefix instead. That was then verified rather than assumed: the prefixes classify 100% of live rows with zero unclassified, on both databases.
A denylist of known-bad values and an allowlist of known-good values fail in opposite directions, and for free-text data the allowlist is the one that fails silently. A row spelled a way nobody listed does not raise anything. It simply stops being found, and the screen shows a blank where a person’s name should be.
Verifying by agreement rather than by assertion
The strongest check available here was not a unit test. It was that one correct implementation already existed.
The new predicate was run against every live leg in both databases and compared against the assignment board’s answer: 5,425 legs in production and 5,337 in the test copy, with zero disagreements. And the eighteen phantom legs now correctly return no driver.
That is a different kind of evidence from a test suite. A test asserts that the code does what its author expected. An agreement check asserts that two independently-written implementations, one of which is known to be right, produce the same answer across the entire real population. When you have a second implementation available — an old report, a screen that works, an export somebody trusts — compare against it before you compare against your own expectations.
The part that was not a code problem
Fixing the reads exposed the data. Seventeen legs, all on expired or completed bookings, had only soft-deleted rows, because a duplicate clean-up months earlier had soft-deleted 115 rows and left some legs with nothing live. Every screen now correctly reported no driver on a pick-up that had definitely happened.
It could not be rebuilt from the audit log, because every historic removal entry had been written naming no driver — a separate defect fixed the same week, too late for the rows that needed it.
The repair was a migration that restores exactly one row per orphaned leg, the newest one that names a driver, guarded by an existence check so it cannot resurrect a duplicate or run twice. Dry-run read-only first on both stacks: seventeen rows each, zero duplicates created. Applied, and blank legs went from eighteen to one on both. The remaining one has no driver on any row, so there is nothing to restore.
Then the verification that actually mattered: reinstating rows with an active status on old bookings could plausibly have resurfaced old jobs on drivers’ phones or inflated the dashboard. It did not, because the app’s job list is date-windowed and the monthly count had by then been changed to exclude closed bookings. That was checked by running the real service functions against production data, not by reasoning that it should be fine.
When you repair data, name the thing you are afraid the repair will do, and then go and check that specific thing. “Verified the migration ran” is not verification. “Verified that the driver whose seventeen expired bookings were just reinstated still sees two jobs and not nineteen” is.