Cursor pagination over data that never stops changing

Most list endpoints start like this: ?page=3&limit=50. It is easy to build and easy to explain. It also stops being correct the moment the data changes while someone is reading it.

On the APIs I work on at Auth0, a lot of list calls do not come from a person clicking through a dashboard. They come from customer automation that walks every page to sync its own system. That client does not notice a missing row. It just ends up with the wrong data.

What goes wrong with offsets

Say a client has read pages one and two, 50 rows each.

• A new row is inserted near the top. Everything shifts down by one, so the last row of page two is now the first row of page three. The client reads it twice.
• A row is deleted near the top. Everything shifts up by one, so the first row of page three is now on page two, which the client already read. It never sees that row.

Nothing failed. Every request returned 200. The data is still wrong.

Page by position, not by count

A cursor remembers where the last page ended, not how many rows came before it. The next request asks for rows after that position.

SELECT id, name, created_at
FROM members
WHERE tenant_id = $1
  AND (created_at, id) > ($2, $3)
ORDER BY created_at, id
LIMIT 51;

Two small things make this work.

The order needs a unique tiebreaker. created_at alone is not enough, because two rows can share a timestamp and come back in a different order on the next request. Adding id makes the order total, so the position is always exact.

The query asks for one more row than the page size. If 51 rows come back, there is a next page. No separate count query.

The cursor is part of the contract

Clients store cursors, retry with them, and sometimes come back hours later. So I treat the cursor as part of the API, even though the docs call it opaque:

• Encode it, so clients cannot build their own.
• Put a version in it, so the format can change later.
• If a cursor no longer decodes, return a clear error. Never quietly return a different page.

If a customer can store it, it is part of the API.

What you give up

• No jumping to page 40. Clients go forward, or backward with the comparison flipped.
• You need an index that matches the order exactly, with the tenant column first.
• Every sort option becomes its own cursor format with its own tiebreaker.

For an API other systems automate against, I think these are the right costs. A list that is a bit less flexible but never skips a row is one people can actually build on.