I keep a surprises.md file in the repo. It is not documentation. It is a list of things that made me say huh out loud, usually at night. The entry from three weeks ago is one line:
SELECT COUNT(*) FROM highlights WHERE deleted_at IS NOT NULL;-> 41,208
Forty-one thousand rows with a tombstone. A year and a bit of reading, marked gone. Nobody deleted them. The system did, on its own, because two devices disagreed about who was allowed to speak last.
The original decision, and why it was right then
When I built the highlight sync, the constraint was simple: you can highlight a verse on a phone with no signal, and it should still be there when you open the app on a different device. That means local writes are authoritative until they are not. I did not want a server round-trip in the middle of someone's quiet time.
So I made highlights a last-write-wins register. Every row had a server_seq column, a monotonic integer bumped on every write by the server. When a device pushed its local queue, the server compared the incoming server_seq to what it had. Higher number wins. Lower number is dropped.
That was the whole policy. It was cheap to reason about, it never required a merge UI, and it meant the common case -- one device, occasionally syncing -- was trivially correct. I wrote a test for it. The test passed for two years.
The problem is that server_seq is a server number. It knows nothing about the order in which the user made changes. And in a last-write-wins register, that is the only order that matters.
What actually happened
Two devices, one account. Device A is offline for a week. Device B syncs first. The server's server_seq climbs. Then Device A comes back and pushes the queue it accumulated during the week -- ten highlights, all created before the ones on Device B, but all carrying local timestamps that the server never looked at. The server assigns them new server_seq values. They win.
An hour later, Device B syncs again, pushing its own queue. Its local changes are older than Device A's, but the server has no way to know that, so it bumps server_seq again. They win. Device B overwrites the week. Device A syncs, overwrites Device B. Back and forth.
Except it was not symmetric. The real damage was the tombstone. When a highlight was removed on one device -- say, because it belonged to a reading plan the user had already finished -- that removal was queued as a delete. The delete carried no timestamp either. On the next sync, the server applied it blindly, filtered it through last-write-wins, and the row was marked deleted_at.
The user's other device still had the highlight. It would sync and resurrect it. Then the first device would sync again, and the tombstone would win again by server_seq. After a few cycles, the resurrection stopped. The row stayed dead. The user saw nothing. There was no error, no notification, no conflict panel. Just a verse that used to be highlighted, and then was not.
Forty-one thousand rows is what that looks like at scale.
The mechanism I should have shipped
Last-write-wins is not wrong. It is wrong as a default with no tiebreaker. The fix was not to build a merge UI or a CRDT. It was to give the server one more number: a client timestamp, in milliseconds, taken from the device clock at the moment the change was made.
The rule became: if two writes collide on the same row, the one with the later client_ts wins. If client_ts is equal -- which happens more than you would think, because mobile clocks are coarse -- fall back to server_seq, which is at least deterministic.
That is it. One column. The diff is eleven lines of migration and a slightly different UPSERT.
The reason it took me three days instead of an hour is that I did not trust the client clock. And I still do not, entirely. Devices lie. I have seen a phone set to 1970 because the battery died and the OS did not have a chance to set the clock. So the server clamps any incoming client_ts to [min_reasonable, now + 5 minutes]. A write that arrives outside that window gets the server's current time instead, and it is logged. We have had eleven of those in two months. All from the same model of cheap Android tablet, all with dead batteries.
If the client clock is wrong by more than the clamp, you are back to nondeterministic ordering. That is a real hole. I have not closed it. My current position is that a device with a clock wrong by hours is a device whose user has bigger problems, and that the clamp at least stops the silent overwrite. The tombstone problem, specifically, is now solved by the timestamp, because a delete made last week has a client_ts of last week and will not outrank a highlight made five minutes ago.
What to check in your own schema
If you have a table that syncs and a last_write_wins policy, open the migration and look for a client-side clock column. If it is not there, you have the same bug I had. It will not announce itself. It will show up as a user complaint that something they highlighted is missing, and you will not be able to reproduce it, because to reproduce it you need two devices, one offline for a week, and a delete queued in the gap.
I ran this query against production before I shipped the migration:
SELECT user_id, COUNT(*) AS tombstones
FROM highlights
WHERE deleted_at IS NOT NULL
GROUP BY user_id
ORDER BY tombstones DESC
LIMIT 20;
The top row had 412 tombstones. That user had two devices and a habit of reading offline on a plane. The data was still there -- soft delete -- so I could reconstruct the highlights from the tombstones and the local queues. I wrote a one-off script to un-delete anything with a client_ts later than its deleted_at minus a small grace window. It restored most of them. It could not restore the ones where both timestamps were absent, because those rows predate the migration.
I do not have a good answer for those. I have an email drafted to the affected users that I have not sent.
Where I landed, and where I have not
I still do not love the client clock. It is a number I do not control, written by hardware I do not own, and I am using it to decide whose edit survives. But the alternative -- server sequence as the only ordering -- was demonstrably worse. The server sequence is a fact about when the server heard about the change, not about when the change happened. I had been treating those as the same thing for two years.
The uncomfortable part is that this was not a bug in the code. The code did what it was written to do. It was a bug in the model, and the model was my decision, and it was correct for the constraints I had at the time. The constraint that changed was the number of devices per user. When I wrote it, most users had one. When it broke, most users had two or three, and the ones with two or three were the ones who read the most.
I keep coming back to something I have written on the whiteboard above my desk: the interface is the only part anyone sees, and the data model is the only part that matters. It is not clever. It is just true, and I forget it every time a feature looks small.
The migration went out on a Tuesday. I watched the tombstone count for a week. It stopped climbing. I have not looked at it since, which is either confidence or avoidance, and I am not sure which.
