Design API pagination that stays correct as data changes

Pagination is often introduced as a performance feature, but it also defines what a client is allowed to assume about a changing collection. A list of orders, incidents, or webhook events can be inserted and deleted while a user is moving through pages. If the API does not define an ordering and a boundary rule, a client may see the same row twice, miss a row, or believe that an empty page means there is no more data. Treat pagination as part of the data contract, not as a UI detail.

Start by naming the collection's natural order. It might be creation time plus a unique identifier, a sequence assigned by the source, or a domain-specific priority. The order must be deterministic when two records share the primary sort value. A database query without an explicit order is not a contract, even if it appears stable in a small development dataset. Every paginated request should produce the same ordering rule and expose enough information for the client to request the next boundary safely.

A useful response contains the records, the requested page size or effective limit, and a continuation signal. A next cursor is usually safer than a boolean because it tells the client exactly how to continue without reconstructing state. Include a previous cursor only when reverse navigation has clear semantics. Keep metadata names stable and avoid making clients infer pagination from record count; a full page can still be the final page.

Limit the maximum page size on the server and document what happens when a client asks for an invalid or oversized value. Silently accepting a huge limit creates memory and latency surprises. You can clamp the value, return a validation error, or use a documented default. Whichever choice you make, make it visible in the response or documentation. A client that can understand the effective limit is easier to operate than one that guesses from behavior.

Suppose the first request returns records ordered by created_at descending. Between requests, a new record is inserted at the top. An offset-based second request can shift every later record and create a duplicate or gap. A cursor-based request can anchor itself to the last observed sort value, so the new record does not move the boundary. Cursor pagination does not freeze the collection, but it makes the movement predictable.

Always add a unique tie-breaker to the sort. created_at alone is not enough when several rows share a timestamp, and a cursor that stores only that timestamp can repeat or skip rows. Use a lexicographic comparison over the sort columns, such as created_at and id. Encode the comparison state into an opaque cursor so clients do not need to know the database schema or construct unsafe predicates themselves.

A cursor can contain the last sort values, a direction, a filter fingerprint, and a version. Encode and sign it so clients can store it without modifying it. Opaque does not mean secret; do not put personal data or credentials into a cursor. A short expiration can prevent an old cursor from creating expensive queries after the data model changes, but do not make normal user navigation fail unnecessarily quickly.

Validate that a cursor belongs to the requested collection, filters, tenant, and sort direction. A cursor generated for one query should not be reusable to bypass a tenant filter or change a visibility rule. If the cursor cannot be decoded or its version is unsupported, return a clear client error and let the client restart from the first page. Never fall back to interpreting untrusted cursor text as a raw database expression.

Rows can be deleted after a client receives a cursor. The next page should continue from the boundary, not fail because the boundary row disappeared. That is another reason to store the sort values in the cursor rather than relying on a row lookup. If a row's sort value changes, it may move across a boundary. For feeds where that matters, use an immutable event sequence or snapshot token rather than a mutable business timestamp.

Do not force cursor pagination onto a report that needs a stable multi-hour export. Provide an asynchronous export job that records the filter, authorization context, snapshot policy, and output location. The job can read from a consistent snapshot or a materialized view and expose progress separately. A user-facing list and a compliance export have different freshness and resource needs, so they should not share an accidental pagination contract.

A request that asks for the next page should use an index aligned with the filters and ordering. Offset queries become increasingly expensive as the database walks past more rows, while a cursor predicate can seek directly into an ordered index. Verify the plan with realistic filters and tenants. A cursor alone does not make a query fast if the database still sorts a large intermediate result or joins unindexed membership data.

Apply a maximum look-ahead and reject pathological filters before they consume a worker. Count totals are especially expensive on large collections; make total_count optional or provide an approximate value with an explicit label. Cache only public or authorization-safe pages. A cached page tied to a user or tenant must include the relevant authorization context in its key, and a stale page should never reveal a row that the user can no longer access.

Test an empty collection, a collection smaller than one page, an exact page boundary, duplicate sort values, concurrent insertion, deletion of the cursor row, edits to the sort field, invalid cursors, changed filters, and maximum limits. Test forward and reverse navigation only if both are supported. Assert that a sequence of pages has no duplicates and no gaps for a controlled dataset. Include authorization changes between requests so a later page cannot expose newly restricted records.

Document the freshness promise in plain language. A live list may change between pages; a cursor preserves a deterministic traversal of the ordering, not a database snapshot. If users need a frozen view, create an explicit snapshot or export. A good pagination contract reduces client complexity because it tells consumers what the boundary means, how to continue, and what a restart should do when the collection changes.

Use a deterministic sort with a unique tie-breaker and make the cursor carry the complete boundary state. Keep the cursor opaque, signed or authenticated, bounded in size, and tied to the filter and sort that created it. Return the effective limit and a continuation signal so clients do not infer completion from record count.

Insert and delete records between page requests, create equal sort timestamps, change filters, and request an expired or tampered cursor. Assert no duplicate or missing records within the documented consistency model. Measure page latency, cursor rejection rate, maximum response size, and database rows scanned; a fast endpoint that scans the entire table is not resilient.

Version cursor formats and keep a compatibility window when changing sort keys. Reject cursors that do not match the query shape instead of returning a misleading page. If a cursor bug is discovered, preserve the old decoder long enough for in-flight clients to finish, then publish a new version and monitor duplicate or gap reports.

Use the database's keyset-pagination guidance, HTTP caching and API contract conventions, and the provider's maximum page-size policy. Document whether the API promises a snapshot, a moving collection, or best-effort traversal.