Why deleted notes came back in Noesis

Noesis keeps one wall of pins in sync across my phone, my tablet and the web. One day I deleted a pin on my phone, emptied it from the trash, and found it still sitting on the wall on the tablet.

Last-write-wins, and a pin from the future

Sync in Noesis is last-write-wins. The Cloudflare Worker accepts an update only when it’s at least as new as what D1 already holds:

WHERE excluded.updated_at >= thoughts.updated_at

The bug was in what “new” meant. A pin placed on the wall for a future date (say 26 September) was created with updatedAt set to that future date. When I deleted it on the 23rd, the delete carried the real time, which was older than the 26th. The server rejected the delete, so on every other device the pin was never deleted.

The fixes

• updatedAt is always the real time. The pin’s place on the wall comes from createdAt; updatedAt only records when it changed.
• Timestamps only move forward. Every save, delete and restore writes max(now, previous + 10 ms), so a mutation can never lose to an older one.
• The server clamps the future. An updated_at ahead of the server clock is clamped to now.
• Purged means gone. Permanently deleted pins go into a purged_thoughts table, and inserts check it first, so a stale device can’t bring one back.
• No demo pins. New installs used to seed starter pins, which then synced into real accounts. That seed is gone.

Finally I cleaned up the data that was already wrong: future timestamps in D1 were clamped to now, and the stuck pins were purged for good.

What I took from it

In a last-write-wins system, a timestamp has one job: ordering writes. Once it also meant “where this pin goes on the wall”, it broke the ordering.