The click that did nothing was writing to a row nobody reads
The report was that removing a driver from a booking did not work. Press the deallocate control on the booking screen, the request returns 200, the screen still shows the driver. Do it from the assignment board instead and it works fine.
The owner’s hypothesis was that the two screens were writing different driver references — one storing an id, the other something else. It was worth checking and it was wrong, and the way it was ruled out is the part worth keeping.
Rather than reasoning about the two components, every live assignment row in production was checked at once: 5,425 of them. Zero dangling driver references. Zero drivers without a ten-digit phone number. Zero mismatches between the two identifiers a booking carries. Both screens feed the same dropdown, bound to the same field. The row stored is identical whichever screen writes it.
The difference was not what gets written. It was which row.
Two endpoints, two lookups, one of them careless
The assignment board posts to a create-or-update endpoint whose lookup filters out soft-deleted rows, orders by id descending and matches spelling variants of the leg name. It finds the newest live row, which is the row the portal renders.
The booking screen calls a different endpoint, whose lookup is a bare single-row fetch on three columns. No soft-delete filter. No ordering. An exact string match on the leg name.
With more than one matching row, that returns an arbitrary one. Not the first, not the newest — the database is under no obligation to be consistent about it, and it is not. So the deallocation landed on a soft-deleted row that no screen displays, wrote its change there faithfully, and returned success. The booking’s own log recorded the whole thing: a driver assigned to the drop-off leg at 09:57:06, then an assignment update to that leg eighteen seconds later naming no driver at all — two rows, one live and holding the driver, one deleted and now holding the removal.
An unordered single-row read against a non-unique key does not fail loudly. It succeeds quietly, on a row of the database’s choosing. That is the worst available failure mode, because every layer above reports success and the only symptom is a human saying “I pressed it and nothing happened”.
Ordering was not enough, and finding that out was the real work
The obvious fix is to make the second lookup match the first: filter the deleted rows, order by id descending, match leg spellings the same way. That was done, and then the fix was verified against real data, which is where the actual problem turned up.
The key those endpoints use is not unique. It cannot be. The create path falls back to a hard-coded literal for the upstream booking reference on any booking that did not come from upstream — counter bookings, bookings made in our own system. At the time of the investigation the test database held six live pick-up rows across six different customers’ bookings, all carrying the identical key.
So the endpoint could update a completely different customer’s booking. Ordering does not fix that; ordering just makes it deterministic which stranger you write to.
The key changed to the booking’s own id wherever the caller supplies it, matching the precedence the create path already used, and the four places in the portal that had been omitting that field from their request bodies were corrected. On the six colliding bookings the old key matched six rows each and the new key matches exactly one — that booking’s own. Swept across all 5,425 live rows in production and 5,337 in the test copy, every row resolves to exactly one, with zero ambiguous and zero newly missing, which is what proves that adding the soft-delete filter did not lose anything that used to be found.
Verify a fix by re-running the measurement that found the bug, on the whole population. Not on the reported case. The reported case was one booking; the sweep is what showed the fix was complete and, more importantly, showed it had not broken the rows that were previously working.
The spelling list that could not see its own caller
One more defect fell out while wiring this up, and it is a small classic.
The helper that expands a leg name into its variants listed three spellings of “pick-up” — three ways somebody had written it at some point. It did not list the caller’s own spelling. So a row stored under a spelling that normalises into a bucket without being listed in that bucket would have been invisible to every lookup using the helper.
The general form: a list of known variants is a denylist wearing an allowlist’s clothes. It handles the cases somebody thought of and silently drops the rest. Where the values are free text written by several teams over several years, match on a prefix or a normalised form, and treat the list as documentation rather than as the rule.
The audit trail that recorded nothing useful
The last thing this touched was the log. Every deallocation had been recording “driver removed — null”, which is useless, and it had been doing so for the entire history: 36 historic removal entries, none of them naming who was taken off.
The cause was one expression. The logging read the supplied driver id, falling back to the value on the loaded record — but the record had already been mutated in place by the update, so the fallback read the new value, which was null. Snapshot before the write, not after.
That mattered more than it looks. When a later clean-up left seventeen legs with only soft-deleted rows and every screen reported no driver on pick-ups that had definitely happened, the audit log was the only place the answer could have come from. It could not, because of this one line. The rows were eventually reinstated from the deleted rows themselves, guarded so the migration could not resurrect a duplicate or run twice; seven rows whose driver id had been nulled in place are unrecoverable.
An audit log written from a mutated object is not an audit log. Capture the before-state as the first statement in the handler, while it is still the before-state. It costs one line, and the day it matters is the day the row it describes no longer exists.