## 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

```text
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