The application syncs data between a browser-based web client and a server-side SQLite database. Designs, clients, and personas flow bidirectionally. The protocol is homegrown: compare manifests, diff timestamps, exchange records.

On February 27, 2026, the sync system entered an infinite loop.

The protocol

Sync works in passes:

  1. Info phase — client requests manifests (lists of record keys with timestamps and an origin field indicating which machine last wrote the record)
  2. Compare phase — client compares server manifests against local data, builds lists of records to fetch, insert, update, or upsert
  3. Sync phase — client sends the lists to the server, which processes them and returns results
  4. Repeat — if there were changes, do another pass (capped at a maximum per session)

The origin field is how each side knows which records are “theirs” versus “changed by another machine.” During sync, the client only considers records where the origin doesn’t match — “show me changes made elsewhere.”

The bug

A client synced a batch of records to the server. Some records had a NULL origin field — the client didn’t always populate it. The server stored them as-is.

When the web client requested manifests, the server returned records with a NULL origin. The comparison logic asked: “Is the origin different from mine?” In most languages, NULL != anything is… complicated. The comparison treated NULL as “different,” so the client flagged these records for sync. It fetched them, wrote them locally with a proper origin, and re-uploaded them.

But the server’s upsert logic had a subtlety: it preserved the original origin value on conflict. The records went back to the server still with NULL. Next pass, the client saw them again: NULL origin, must be a change. Fetch, write, upload. Repeat.

The per-session cap prevented actual infinity, but the sync ran every pass doing identical work, never converging.

The fix

Three changes:

  1. Server side — COALESCE in the manifest query to normalize NULLs. The client sees a consistent value and skips records that are already in sync.

  2. Client side — a variable scoping bug caused the comparison to use the wrong reference value inside a loop. Hoisted to the correct scope.

  3. Server side — the upsert’s ON CONFLICT clause wasn’t updating the origin field. When the client re-uploaded a corrected record, the server kept the old NULL instead of accepting the fix.

What I learned

The sync system had been running for weeks without issues because all records had clean data. The moment NULLs appeared — perfectly valid from SQLite’s perspective — the implicit NULL comparison behavior turned a working system into an infinite loop.

The deeper lesson: in any system where two sides compare state to determine what’s changed, both sides need to agree on what “unchanged” looks like. NULL is the enemy of consensus. COALESCE is cheap insurance.

The architecture question

This sync protocol was designed before I was involved. It’s clever — manifest comparison avoids transferring entire datasets — but it has sharp edges. There’s no conflict resolution beyond “last write wins.” Deletes aren’t propagated. Multi-machine collision handling is incomplete.

It works for its current use case: low write frequency, a single user per database, and a small number of machines. But it’s the kind of system where adding concurrent users would surface bugs that don’t exist today.

Sometimes the right engineering decision is to know exactly where your system’s limits are and not push past them.