Trips completed this month" actually meant "assigned this month

A driver’s app shows him four numbers for the month: trips completed, pick-ups, drop-offs and assigned. The owner opened the live app, looked at his own fleet’s figures, and asked why they were wrong.

The query behind “trips completed” counted assignment rows whose status was Handover and whose updated timestamp fell inside the month. That reads as reasonable. The status only becomes Handover when a driver completes the job in the app, so the row’s last update ought to be the moment the work was done.

The updated timestamp had never moved on a single row. The assignment table is declared with automatic timestamps switched off in the ORM, and the column carries only a database default with no trigger behind it — so it is written once, at insert, and never again. All 221 live Handover rows in production had an updated timestamp exactly equal to their created timestamp. Not approximately. Exactly, on every row.

So “trips completed this month” meant “assigned this month”. A job assigned on the 28th and handed over on the 2nd was counted in the month it was assigned. A job assigned in July and completed in August was counted in neither, because by the time it was Handover its timestamp was still July’s and July’s query had already run.

Why nobody caught it

The two failure modes cancel out at steady state. In a month where roughly as many jobs cross the boundary in each direction, the total looks plausible. It is only wrong per-driver, per-month, and only by a few, and the person reading it has no independent source to check it against — which is the whole reason the tile exists.

There was a second, louder symptom that had been present all along and read as a different bug: the pick-up and drop-off breakdown did not add up to the total. That was a separate defect, in the leg matching rather than the dates, but it is worth noting that the number people complained about was not the number that was actually broken. The stats disagreed with each other, so somebody looked, and looking is what found the timestamp.

The fix, and the property that mattered most

The owner chose a real column over deriving the date from photo timestamps, which was the alternative. A completedAt column, stamped once on the transition into Handover through a single helper wired into all three write paths that can make that transition.

The important word is once. The helper deliberately refuses to overwrite an existing value. A re-save cannot move the work date. A status-only update from the app — which happens, because the phone sends status changes without a driver id — cannot move it either. That refusal is the entire point, because moving on every write is exactly the failure the automatic timestamp had. Replacing an always-stale field with an always-fresh one would have been a different wrong answer.

A business event needs a column that records that event and nothing else. Not the row’s update time, not a modified-at, not “the last time anything about this record changed”. Those answer a storage question. “When was this trip completed” is a domain question, and the two only coincide when the row is written once and never touched again — which is never true for long.

Backfilling a date you do not have

The column was new, so 221 rows of history had nothing in it. The only available evidence of when a handover happened was the creation time of the handover photos attached to it.

That evidence is imperfect and the migration file says so, in a comment, in the file. Photo creation time is upload time, not capture time — the phone queues photos and uploads them in a batch, and the median gap between the first and last photo on an assignment is zero minutes across 3,687 assignments, which tells you they all land together rather than as the driver takes them. It is good enough to attribute a job to a month. It is not good enough to measure how long a handover took.

The backfill was dry-run read-only against production first, and the results were stated as numbers rather than as an assurance: 219 of 221 rows get a date, 2 with no photos stay null and are excluded rather than counted in the wrong month, 8 rows move month, and the current-month figure changes for exactly one driver, from ten to nine.

Then the migration ran on production and the result matched the dry run exactly, including the per-driver figures. That is a small thing and it is the only reason to bother dry-running: not to find out whether it will work, but to have a prediction specific enough that a mismatch would be obvious.

Backfilling from a proxy is legitimate. Backfilling from a proxy without writing down what the proxy actually measures is not — because the next person will read that column as exact and build a duration metric on it.

The metrics that were not there at all

The same investigation went looking for what else could be measured and found two gaps worth recording, both of the same kind.

The assignment table has a startTime column. It is null on all 5,473 rows. Nothing ever wrote it. So handover duration is not measurable, and neither is punctuality, and both had been assumed available because the column existed.

A column is not data. An empty column in a schema is a stronger illusion than a missing one, because it survives a code review, appears in every model, and answers “do we track that?” with a yes. Before promising any metric, select from the column and count the non-nulls. It takes seconds and it has repeatedly been the difference between a report and a fiction.

The habit worth taking

Pick any dashboard you own that counts things “this month”. Then, for each figure, name the exact column the month is taken from and go and check that column moves when the event it represents happens. Not that it should move. Check a row.

In this system that check took one query — comparing created and updated timestamps across Handover rows — and it returned zero rows where they differed, which is the kind of result that settles an argument in one line. The reason to do it deliberately is that a report which is wrong by a few percent looks exactly like a report which is right, and the person best placed to notice is the one who is being asked to trust it.