Cursor-based sync is harder than it looks

We started with a batch importer and spent six weeks regretting it.

The first version of Pale Horizon pulled everything from every connected source on a nightly schedule, diffed the result, and wrote the difference. It worked fine on our own workspace, which had about four thousand objects and no malformed rows.

It fell apart on the first real customer.

What actually broke

A batch import that fails halfway leaves the graph in a state no retry can reason about. You cannot tell whether an object is missing because it was deleted upstream or because the import died before reaching it. So you cannot safely re-run, and you cannot safely skip.

Partial failure is not an edge case. On a corpus of any real size it is the normal case, and the batch model has no answer for it.

Moving to a cursor

We switched to pulling from a per-source cursor: an opaque token the source gives us representing "everything up to here". Each pull returns a page of changes plus a new cursor. Two invariants made this safe:

• The cursor only advances after the page is committed. If we crash mid-page, we replay the same page. That means every write must be idempotent.
• Deletes arrive as tombstones, not as absences. An object missing from a page means "unchanged", never "gone".

The idempotency part

Every object carries a source-assigned id and a source-assigned version. An upsert is a compare-and-set on that pair:

INSERT INTO objects (source, src_id, version, payload)
VALUES ($1, $2, $3, $4)
ON CONFLICT (source, src_id) DO UPDATE
  SET version = EXCLUDED.version,
      payload = EXCLUDED.payload
  WHERE objects.version < EXCLUDED.version;

The WHERE clause is the whole trick. Replaying a page is free — the row is already at that version, so the update matches nothing. Out-of-order delivery is also free, because an older version never overwrites a newer one.

What we would still change

Cursors are per-source, and sources disagree about what a cursor means. Two of ours return a timestamp that is not monotonic under clock skew, which we now defend against by tracking a high-water mark and refusing to advance backwards. It is ugly and it is load-bearing.

The batch importer is gone. Nothing about it is missed.