An insurance API returns policy and claim lists that can be very large. Which pagination approach should be used, and what breaks with the obvious one?

What breaks with offset pagination

• Correctness under concurrent writes. If a record is inserted while a client is paging, later pages shift and the client sees an item twice or misses one entirely. For a claims or transactions list, a missed record is a correctness problem rather than a cosmetic one.
• Performance at depth. OFFSET 100000 requires the database to traverse and discard a hundred thousand rows. Deep pages get progressively slower, and the slowest requests come from the clients doing the most work — usually the integrations that matter most.
• Unbounded cost. Nothing stops a caller requesting a very deep page, so the API's worst case is set by the most aggressive client.

Why cursors work

A cursor encodes a position in a stable sort order — typically a monotonic identifier or a timestamp plus a tiebreaker. The next page is "everything after this position", which is an index seek regardless of depth, and which is unaffected by insertions elsewhere.

The details that matter

• The sort key must be stable and unique. Sorting by a mutable field such as updated-at means a record can move between pages; a tiebreaker on a unique identifier is required.
• Opaque cursors. Encode the position rather than exposing it, so the internal sort key can change without breaking clients who have stored a cursor.
• A maximum page size enforced server-side, with the actual size returned, since clients will request everything otherwise.
• State what happens to records modified during iteration. A client paging through a week of claims while claims are updated needs to know whether it sees a consistent snapshot or a moving one. Both are defensible; leaving it undefined is not.

The other requirement in a regulated domain

For bulk access — reconciliation, reporting, audit — pagination is the wrong mechanism entirely. An export endpoint producing a point-in-time snapshot as a file is faster for the client, far cheaper for the platform, and gives the consistency guarantee that page-by-page iteration cannot. Offering it removes most of the deep-pagination load from the API.