A status only your own app can change will never be true for long
After the previous fix landed, the owner checked the live app again. Trips completed now read nine, and the pick-up and drop-off split added up to nine, so that part was right. The tile underneath it said 86 assigned this month for one driver.
Eighty-six is not a number of jobs a person does in a month.
The tile was captioned “trips scheduled for you this month that are still open” and it counted assignment rows with status Assigned. That status only ever leaves Assigned when a driver completes a handover inside the app. There is no other transition out of it. Nothing in the office portal changes it, nothing in the upstream sync changes it, and no scheduled job touches it.
So every booking the office finished by other means, and every booking that simply lapsed, keeps that status for ever. This one driver had 1,385 such rows across the whole history. Of the 86 in the current month, seventy were expired or completed, eight were awaiting a refund and one was cancelled. Three were genuinely open. Eighty-three already had their drop-off date in the past.
The mistake was trusting our own row
The row said Assigned. The row was not lying — it was accurately recording that nobody had ever told it otherwise. The mistake was treating a field that only one client can write as if it described the world.
The booking itself knew perfectly well what had happened. It had a status of Expired, or Completed, or Refund Issue Pending, and the upstream rental system had its own status saying the same thing. Two authoritative sources sat one join away from a tile that was reading a third, weaker one.
When a status can only advance through one code path, it does not describe the entity. It describes whether that code path ran. That is a useful fact in its own right — it tells you how many jobs went through the app, which is exactly what the completed count wanted. It is not a fact about whether work is outstanding.
The fix was to stop asking the assignment row and start asking the booking: an assignment counts as open only if the booking still has driver work left in it. Returned, Refund Issue Pending, Refund Done, Cancelled and Expired are all excluded, plus the upstream statuses Cancelled and Completed, because the upstream system can close a booking before our own status catches up.
That exclusion list is deliberately wider than the codebase’s existing constant for terminal statuses, and the reason is worth stating: a booking in Returned is still open as far as the office is concerned, because the settlement has not been done. But the driving is finished, and this is a driver-facing number. The same booking is open on one screen and closed on another, correctly, and a shared constant would have forced them to agree.
A shared “is this finished” list is usually wrong, because finished is a question about a role, not about a record. Where two audiences legitimately disagree, give them two predicates and a comment saying why, rather than one predicate and an argument every six months.
Measuring three definitions before picking one
Three candidate definitions of “open” were written and each was run against production before any code changed. All three converged on the same answer, and the three surviving rows for that driver were exactly the bookings his app was already listing on its home screen.
That last check is the one that made it safe to ship. The app’s booking list and the app’s dashboard tile are two independent queries over the same data, written at different times by different reasoning, and they had been disagreeing by a factor of nearly thirty. Making them agree is a stronger signal than any test, because it is an external consistency check rather than an assertion about the code.
The change was query-only. No migration, no app rebuild — the caption became true rather than needing to change, which is the ideal shape for this kind of fix.
Verifying an ORM, not a query
There was one real risk in shipping it, and it was not the SQL. The new predicate builds a nested and/or combination inside an include’s where clause, and the question was whether the ORM emits what the developer intended. SQL being right does not prove that the ORM generates that SQL.
So verification ran the real summary function against each database directly, rather than checking the deployed commit and calling it done. Every driver’s legs summed to their total, every open count matched the value computed independently in the database console, and the driver who had read 86 now read 3.
Where a change lives inside a query builder rather than in a query, the thing to verify is the builder’s output, not your model of it. The cheapest way is usually to call the real function against real data and compare against the same figures computed by hand. It is the same discipline as the dry run in the previous piece: produce a prediction precise enough that being wrong is visible.
The pattern to look for in your own system
Go through the statuses in your schema and, for each one, write down every code path that can change it. Where the answer is “one, and it is the mobile client”, you have a field that describes an app’s behaviour and is being read as if it described reality.
The tell is usually a count that grows and never shrinks. A driver with 1,385 rows in one status is not a data problem, it is a modelling statement: this field has no exit other than success. Real work has other endings — cancelled, lapsed, done by somebody else, done by hand, forgotten. If none of those endings can write to the field, the field is only ever going to be half of the story, and the half it holds will get less true every month.