intermediate · investigation

The List That Skipped Rows

A partner's nightly sync intermittently misses orders and occasionally imports the same order twice. Their reconciliation report shows gaps that never occur during quiet hours.

Evidence

sync log, 02:00 (order volume ~40/min):
  GET /orders?offset=0&limit=100&sort=created_desc   -> ids [9001..8902]
  GET /orders?offset=100&limit=100&sort=created_desc -> ids [8905..8806]   <- 8903, 8904 never seen
                                                                              8905 seen twice

between the two calls: 3 new orders inserted (ids 9002-9004)
newest-first sort + fixed offsets => every insert shifts the whole window

Investigate

Inspect Partner client code

The client pages correctly per the docs: offset += limit until an empty page. No bug on their side.

Inspect Pagination mechanism under writes

Offset pagination over `created_desc` ordering: each insert during the scan shifts every subsequent row down, so page boundaries slide — rows dodge the window (skipped) or reappear in the next page (duplicated).

Inspect Ordering stability

Sort is on `created_at` with no tiebreaker; equal timestamps make ordering nondeterministic between queries, adding a second source of drift.

Inspect Why quiet hours are clean

No concurrent inserts, no window shift — which is why the partner's own testing (run at low volume) never reproduced it.