Declare a deterministic total order
A database does not promise a useful result order without an explicit ordering rule. Choose a sort key and a unique tie breaker, such as creation time followed by resource identifier. Define null handling and direction. Two records with the same timestamp must still have a stable relative position.
Offset pagination is straightforward for small or stable collections but can skip or repeat records when inserts and deletions shift positions. Large offsets may also require substantial database work. A cursor can encode a continuation position, but it does not create a consistent snapshot unless the implementation deliberately provides one.
Continue from the last returned record
For ascending order by created_at and id, a keyset query can continue after the last returned pair. The index should support the tenant filter and ordering used in production. Ensure the chosen keys are nonnull or explicitly handle null ordering, and avoid mutable sort values when a predictable traversal is important.
This continuation query uses stable, nonnull ordering fields and parameters bound by the database driver. The surrounding service must enforce tenant authorization and define snapshot consistency separately.
SELECT id, created_at, status
FROM invoices
WHERE tenant_id = $1
AND (created_at, id) > ($2, $3)
ORDER BY created_at ASC, id ASC
LIMIT $4;Bind cursors to the authorized query context
Treat an opaque cursor as an implementation detail. Bind its continuation state to the sort, filters, tenant, and relevant version or snapshot. Protect integrity with a server stored reference or an authenticated encoding. Base64 alone neither prevents tampering nor hides sensitive content.
Set a documented lifetime and return a clear restart condition for expired or invalid cursors. Revalidate the caller's current permissions on each page. Possession of a cursor must not preserve access after revocation or permit switching the tenant or filters to extract a different dataset.
State the consistency a traversal provides
A live collection, a time bounded export, and a database snapshot solve different needs. A live keyset traversal may miss records inserted behind the current position. A snapshot can stabilize visibility but consumes resources and needs an expiration policy. A bulk export job may be more suitable for a complete audit dataset.
Test empty collections, equal timestamps, page size boundaries, deleted continuation records, revoked access, and concurrent inserts. Avoid promising an exact total count unless its cost and consistency are understood. Return an explicit continuation indicator so consumers do not infer completion from an assumed page size or a displayed estimate.