agent looped on offset pagination while records shifted - results moved between pages and rows were silently skipped

TL;DR

Offset pagination assumes the list stands still, and live lists do not. Page by a stable, unique key instead - request records after the last seen ID - so inserts and deletes shift offsets without skipping rows. If the provider offers cursor pagination, use it; if not, keyset pagination on the ID column gets you the same stability.

The query

agent looped on offset pagination while records shifted  -  results moved between pages and rows were silently skipped

Steps

1. Confirm rows are actually being skipped

Paginate the same list twice and compare the collected IDs. Or fetch with offset and separately fetch the full count: if collected rows are fewer than the count and no error occurred, shifting offsets are the cause.

Expected: two runs returning different row sets, or a collected count below the known total.

2. Check whether the provider offers cursor pagination

Read the list endpoint's docs for a cursor or after-parameter. If it exists, switch the generated client to it - provider cursors are stable against inserts by design.

Expected: a yes or no on cursor support, and the parameter name if yes.

3. Fall back to keyset pagination on a stable key

If there is no cursor, page with a filter on a unique, ordered column: request records where the ID is greater than the last seen ID, ordered by ID ascending, with a fixed limit. Each page starts exactly where the previous one ended, regardless of what was inserted or deleted ahead of it.

Expected: pagination code with no offset arithmetic anywhere.

4. Sort by something immutable

Make sure the sort key cannot change between pages. Auto-increment IDs or creation-ordered UUIDs work; sorting by an editable field like name or status reintroduces the shifting problem through a different door.

Expected: an ORDER BY (or sort parameter) on an immutable, unique column.

5. Verify with a shifting dataset

Run the fixed client against a list while inserting and deleting records mid-pagination. Confirm every expected ID appears exactly once in the collected set.

Expected: zero missing and zero duplicated IDs under concurrent writes.

Use this when

• Full-list syncs come back short with no errors
• The dataset changes while pagination runs (new signups, new orders, live feeds)
• The generated code uses offset and limit parameters
• Two identical runs return different row sets

Not for this skill when

• The dataset is static during pagination (offset is fine there)
• Rows are missing because of a filter or permission, not shifting (check the query)
• The provider's cursor itself is unstable (cursor bug, different fix)
• The API returns an error mid-pagination (error-handling problem)

Variant phrasings

incremental sync missed new records

Same family: an unstable page boundary drops rows. Keyset pagination on the ID (step 3) plus a high-water mark for the sync gives a complete incremental feed.

pagination returned duplicates after inserts

Duplicates are the mirror image of skips - rows shifting forward get fetched twice. The stable-key approach in steps 3 and 4 fixes both.

Why it happens

Offset means "skip the first N rows," and N is counted against the live list at request time. When a new record lands at the top between page one and page two, every row shifts down one position: the row that was at position N moves to N+1, the offset skips it, and it never appears. The agent generated textbook offset pagination because the docs' example used a static dataset where the bug is invisible.

Edge cases

• Provider only offers offset: use keyset-style filtering anyway if the API supports ID filters; if it truly offers only offset, snapshot the list (export endpoint) or accept and document the gap.
• Non-unique sort keys: two rows sharing a sort value need a tiebreaker. Use the ID as the secondary sort key so the ordering is total.
• Deleted rows: keyset pagination handles deletes gracefully (the filter just skips them), unlike offset which shifts everything after the gap.
• Very large offsets are slow on some providers: another reason to prefer keyset - "after ID X" stays fast while "offset 900000" gets slower on every page.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_EWlSMno2JdkDKusLzCO3zw