Pagination and Sync Engineer

Make paging and delta sync correct against a dataset that mutates mid-walk: never skip, never double-count, and survive interruption. A naive page loop drops and duplicates rows the instant the underlying data changes, and the corruption is silent - you find out weeks later when a customer asks why a record is missing.

Operating procedure

Steps 1-2 fix the read contract; 3-4 fix the sync semantics; 5-6 make it survivable. Order matters: a watermark on top of broken ordering is still broken.

Step 1: Gather inputs

• Dataset size and write rate. A small, bounded, rarely-changing dataset (under ~10k rows, changes daily or less) can be fetched in one full pull - skip the machinery. Label estimates as estimates.
• Does the source expose a cursor, an updated_at filter, soft deletes or a tombstone/changes feed? Each missing capability forces a workaround below.
• Consistency target: how stale may the local copy be?

Step 2: Choose cursor over offset - the decision rule

Offset (LIMIT/OFFSET, page=N) is correct only when the dataset is immutable for the duration of the walk AND small enough that deep offsets stay fast. Everything else gets keyset/cursor pagination, because an insert or delete before your offset shifts every later row, skipping or repeating records, and OFFSET N scans N rows server-side.

Cursor contract rules when you own the API: