2026-10-19 · Architecture
When the guest’s local time is stored as UTC
A booking platform was about to grow from one country to several. Then “3 pm” has to mean 3 pm wherever the party takes place, everywhere. The review found an error that was invisible in a single country.
A booking’s time lived in a timestamptz column, as an instant. What went in, though, was the guest’s local time, read as UTC. As long as the site ran in one country and displayed the time with the same clock, nobody noticed. A shift of one to two hours appeared only where instants are really computed: payment deadlines, free cancellation, conversion between zones.
Old data without a source
About 18,000 rows no longer referenced a calendar date from which the time could be recomputed. The distribution of hours showed what they contained: the stored “UTC” times peak at 2 to 3 pm and spread from 10 am to 5 pm — exactly the times guests pick for parties. The picture is the same before and after the server switched to UTC. If the values were correct, the peak would sit one to two hours earlier.
The fix is not a single UPDATE. An audit writes an immutable manifest with the class, old value and new value of every row. The apply step then runs row by row under locks, rechecks everything the value was derived from, skips anything that changed, and can be rolled back. On a copy of the data: every value fixed in one run, and a second audit finds zero changes.
Three rounds, two models
Only one place now writes instants: from the calendar date and the venue’s zone. Claude and Codex reviewed the model independently, and twice more after the fixes. They found two rules for the same thing (code and a trigger set the zone differently), an invariant held only by code that now lives as a CHECK in the database, a deadlock when moving a date, and bookings that were guessed on a zone change instead of being sent to a human.
Round one had 6 P1 findings and about 15 P2, the last round no P0. The fixes themselves had introduced two new bugs; the next round caught both. Before every new constraint, a copy of the production database was checked: not a single row violated it.
A column type does not guarantee what a value means. And an invariant held only by code holds until the first raw INSERT.