Where Did We Stop? A Checkpoint Ledger for Restarting a Paginated Sync

A sync that walks an API page by page will eventually be interrupted. The question on restart is which cursor to ask for first. Guess too far ahead and records are skipped. Guess too far back and you re-read records, which is safe only if applying them twice is harmless. This article proposes a ledger to make the answer explicit. It is a conditional design suggestion, not a description of any particular API, which may behave differently.

The ledger

Keep one row per page attempt with these columns:

run id
page number
request cursor (the token used to ask for this page)
response next cursor (what the remote side returned)
item count received
state: requested, received, applied, committed
note

A page is committed only after every item on it has been applied locally and that fact is stored durably. The checkpoint is the request cursor of the first page that is not committed. Store the request cursor, not the next cursor, because the next cursor only matters once the current page is safely finished.

Restart rule

On restart, find the first row that is not committed and request it again with its stored request cursor. Apply items by a stable item key so a repeated item replaces itself and does not duplicate. A local record saying a page was done is a claim about local work only. It does not prove the remote side saw or did anything, so never describe the result as exactly once. The honest description is at least once reading with idempotent local application.

Worked hypothetical

A fictional source has 5 pages of 100 items, 500 items total. The run crashes during page 3.

Page 1: committed, 100 items.
Page 2: committed, 100 items.
Page 3: state applied for 60 items, not committed.
Pages 4 and 5: no rows yet.

Restart requests page 3 with its stored request cursor. All 100 items arrive, the 60 already applied are replaced by key, and 40 are new. After commit, the local store holds 300 items, not 360. Pages 4 and 5 then bring it to 500. If the count after page 3 were 360, the key rule has failed and the run should stop.

Decision branches

Branch A, cursor still accepted: resume as above.

Branch B, cursor rejected or expired: stop and mark the run needs decision. Do not guess a nearby cursor. A person chooses between a fresh full read and another documented strategy.

Branch C, remote data changed while paused: a repeated page may differ. Prefer the newer content by item key, and note the difference in the ledger.

Branch D, the sync also writes to the remote side: if a write response was lost, mark that item unknown. Never repeat an externally uncertain write automatically. Check the remote state by a read first, or hand it to a person.

Stop conditions

Stop the run when the ledger has two rows for the same page with different request cursors and no recorded reason, when committed counts fail to match applied counts, or when a rejected cursor appears. Stopping is cheaper than a silent gap.

Proposed acceptance tests

These are proposals, not results.

Interrupt a stub sync after applying part of a page and check restart begins at that page.
After resume, check the local item count equals the distinct keys seen, not the sum of attempts.
Make the stub reject the stored cursor and check the run stops in needs decision, with no substitute cursor.
Simulate a lost write response and check the item is flagged unknown and not sent again unprompted.
Delete the ledger row for a committed page and check the sync does not claim completeness.

Small practical notes

Write the ledger row before the request goes out, so a crash always leaves a trace. Keep old runs for a short review period so a reader can reconstruct what happened. Review the ledger by hand after any run that stopped.