The last-write-wins clock was wrong, and I wrote it

I was brushing my teeth on a Tuesday morning, phone propped on the cistern, when the idea dropped: a better title for Sunday's sermon. I opened the app, started a note on Matthew 11, and saved. The phone had been offline since the previous night — patchy signal in the flat, nothing unusual. The app queued the write locally and I forgot about it.

At my desk, forty minutes later, I opened the same note on the laptop and rewrote the first line. Different angle. Then the phone reconnected.

The conflict lasted eleven seconds of user-visible weirdness and about four hours of my week. What it actually exposed was a decision I'd made eighteen months earlier, on purpose, and written down in a comment I still agree with. The decision was: the last write wins, and the clock decides what's last.

That sentence is correct until it isn't, and it stopped being correct somewhere between when I wrote it and that Tuesday.

What the schema looked like

The notes table was small and honest. SQLite on each device, a sync endpoint on the server, JSON over the wire:

CREATE TABLE notes (
  id           TEXT PRIMARY KEY,
  body         TEXT NOT NULL,
  updated_at   INTEGER NOT NULL,
  deleted      INTEGER NOT NULL DEFAULT 0
);

updated_at was Date.now() at write time. That was the whole clock. When two devices synced, the server picked the row with the larger updated_at and overwrote the loser. For the first year, that was fine, because the app was single-device for most people. The second device was usually a reinstall.

Then the reading plans shipped, and plans have notes attached, and people started reading on a phone and annotating on a tablet in the evening. The percentage of accounts with two or more devices writing the same note in the same week went from something I never measured to something the support inbox was telling me about every few days.

Why last-write-wins was defensible

I want to be fair to the decision, because it was mine and it was right.

At the time, the alternative was a per-field merge with a vector clock or a CRDT, and that would have meant the note body, the title, the tags, the reading-plan anchor, and the deleted flag all merging independently. The cost of the note feature — which was, at the time, a textarea and a save button — would have ballooned into something I could not test properly on a Tuesday. Offline sync is one of those areas where you can spend six weeks on an edge case that three users will ever touch.

So the comment said: // last-write-wins by wall clock. revisit if multi-device note editing becomes common. That is, I think, still the right shape of note. The problem is nobody revisits.

What revisits you is the user.

The failure mode is not what you think

The classic story about last-write-wins is that you lose writes. In practice, on a phone with a flaky connection, that's not usually what happened. The phone's clock was fine. The server's clock was fine. What broke was the ordering between them.

Here's what the log actually showed, from a repro I squeezed out of the support thread:

[t=0]     phone:   note.body = 'A'   updated_at = 1714992000000
[t=300]   laptop:  note.body = 'B'   updated_at = 1714992000300
[t=310]   laptop -> server sync, server stores body='B'
[t=9h20m] phone comes online, pushes note
                    phone.updated_at = 1714992000000
                    server.updated_at = 1714992000300
                    server keeps 'B'   // because 1714992000300 > 1714992000000
[t=9h21m] phone pulls, gets 'B', clobbers local 'A'

The phone edit was newer in human time. It was older in wall-clock time, because the phone had been offline when it was made and the clock on the edit was set at write time, not at sync time. The phone lost. From the user's side, the app had reached into a note they'd carefully written that morning and replaced it with an older version from the laptop.

That's the thing about clocks in offline-first systems: the timestamp of an edit should be the time the edit happened, not the time the edit reached you. I remember writing that sentence into the scratch file and then staring at it, because the second half is obvious and the first half is what I'd shipped.

The fix, and what it cost

The mechanism I ended up with is small. It's called a hybrid logical clock, and it lives on every row.

Each note carries two integer fields, hlc_ts and hlc_count, both monotonically increasing, both advanced on every local write and reconciled on every sync. The rule is three lines:

• On a local write, take the current (ts, count) and increment count.
• On a received remote write, set ts = max(local_ts, remote_ts), and if they're equal, count = max(local_count, remote_count) + 1.
• Comparison is (ts, count) lexicographic, ties broken by device id.

That's it. The clock is still a scalar, which means the merge is still "the later one wins," but "later" now means "after, causally," not "after, according to one device's wall clock." The 9-hour gap in the log above now produces an HLC on the phone that is strictly greater than the laptop's, and the phone's edit wins, which is what the user expected.

The cost: migration of every existing row to seed hlc_ts from updated_at and hlc_count = 0; a change to the sync endpoint that compares (hlc_ts, hlc_count, device_id) instead of updated_at; and — this is the part I underestimated — a change to the pull path, which now has to reject a remote row the client has already seen at a higher HLC. That last one is where my first attempt failed. I'd moved the compare into the push handler and left pull alone, and the laptop cheerfully overwrote the phone every time the phone was the one that had edited last. Three days of thinking I'd broken it.

What I still don't know

The HLC fixes the ordering. It does not fix the thing underneath, which is that I still throw away the loser's body. A user who edits the same note on two devices offline and reconnects loses the second edit entirely. The apologetic in the release notes says "the most recent version is kept," which is accurate and also the kind of sentence that quietly admits you didn't solve the problem.

A CRDT would keep both. A three-way merge would keep both and try to combine them. Both are real options and both have real costs, and I haven't decided whether they're worth it yet. What I do know is that the HLC was the right next step because it made the failure orderable — the same reconciliation now either works or doesn't, and I can see which.

Which is, I think, the general shape of this kind of debt. The original decision wasn't wrong. The assumption it rested on — one writer at a time — was wrong, and nothing in the code told me that. The comment did. I just didn't read it for eighteen months.

So now the comment has a line under it. // HLC as of 2024-05; see sync.md. Revisit if merge (not order) becomes the complaint.

That's the part I'd say to anyone building an offline-first thing right now. You don't have to get the merge right on day one. You do have to write down which assumption your design is resting on, so that the version of you who has forgotten can find it when the assumption stops being true.

I have a phone in my pocket with a note on Matthew 11 that I've now lost twice. I keep it there as a reminder.