Cursor Pagination Snapshot Consistency: Query Identity, Stable Member Sets, and Expiry Contracts

REST API Pagination Design and Database Cursor vs OFFSET have already explained the performance and composite sort keys of keyset, but WHERE (created_at,id) < cursor only fixes the boundary of the previous page, not the entire result set. If an old record updates during paging and crosses the boundary, it may appear again; moving backward may skip it forever. Deletes, changed filter fields, and read-replica lag can also create empty pages. A production API must first answer: "is the user looking at a live window, or traversing the same snapshot?"

Write the consistency level into the contract

LIVE_KEYSET allows each page to read current data, only guaranteeing full order within a single query and correct boundaries—suitable for dynamic feeds; it must publicly state that cross-page results may change due to updates. Exports, audits, and batch processing use SNAPSHOT_TRAVERSAL: the first request fixes the member semantics, and subsequent pages must come from the same snapshot. You cannot use a single cursor=true parameter to imply two different guarantees.

The signed page.cursor.v1 must contain at least: tenant/principal scope, endpoint and API revision, normalized filter digest, sort schema/direction, snapshot ID, complete boundary tuple, authorization epoch, page-size cap, issue/expiry time, and signing key generation. Clients can lower limit, but cannot change filter, sort, or raise the cap; any mismatch returns a stable CURSOR_CONTRACT_MISMATCH. Base64 is only encoding; you must use server-side signing to prevent constructing cursors that cross tenant boundaries. HTTP Link relations can expose next/prev per RFC 8288, but the real state remains inside the opaque cursor.

Sorting must form a total order, e.g. (updated_at DESC, id DESC), and indexes, NULL ordering, and database collation are all bound to sortSchema=v3. REST API Versioning is used to migrate to a new sort contract; old cursors continue to decode within their compatible reader and expiry window, and you must not guess-read them with new comparison rules.

Snapshot implementation depends on whether data is mutable

For append-only collections whose sort fields are immutable, you can record a highWatermark at the first page and then constrain with sequence <= watermark; this excludes new inserts, but whether deletes are visible still needs to be specified by the contract. For collections that update sort or filter fields, a high watermark is not enough: you need to query bi-temporal/versioned rows by valid_from <= snapshot < valid_to, or materialize an ordered member ID list with version digest on the first request. When reading each item, current authorization is still re-verified; snapshot consistency cannot restore access to revoked data.

PostgreSQL REPEATABLE READ can give a single transaction a consistent snapshot, but holding a transaction across arbitrary HTTP paging for a long time pins xmin, blocks vacuum, and turns connection leaks into storage risk. Short exports can be continuously read by a server-side job within one bounded transaction; interactive paging should use recyclable historical versions, materialized member lists, or a search-snapshot service with a TTL, rather than treating a database snapshot ID as a permanent cursor. Background Job Queues are better suited for long exports, where the client only polls the job artifact.

The snapshot registry state is ISSUED -> ACTIVE -> COMPLETED | EXPIRED | REVOKED, recording the member/watermark digest, reader version, expiry, and max page count. Each cursor carries a random transition ID; the first read uses CAS to create a PageRead(transitionId, boundaryDigest, resultDigest, nextCursorDigest) and only increments the page count once; duplicate requests return the same page and same next cursor without consuming quota again. If the boundary or request digest differs for the same transition, it's a conflict; whether forking reads from different issued cursors is allowed must also be declared in the contract. On expiry, return SNAPSHOT_EXPIRED and a restart entry point; you cannot silently switch to a new snapshot to continue.

Deletes, replication lag, and permissions are independent axes

When a record in the materialized member set is deleted by business logic, an audit-type snapshot can return a versioned tombstone; ordinary lists can skip it, but must keep scanning until the page is full or the end is reached—you cannot mistakenly judge hasMore=false just because the filtered array is insufficient. When the authorization epoch increases, old cursors are invalidated entirely or re-verified item by item; no scheme is allowed to return revoked content. The lag introduced by Database Read Replicas must not let pages cross different watermarks; snapshots must be routed to a replica that can prove it has caught up to the corresponding revision, otherwise fall back to the primary or return temporarily unavailable.

The failure suite creates identical timestamps at page boundaries, updates a record from the third page to the first, deletes the last item of the previous page, changes a filter field, inserts data after the watermark, switches to a lagging replica, rotates the signing key, tampers with tenant/filter/limit, and replays the same cursor, and lets the snapshot expire mid-request. For a complete traversal of fixed fixtures, the member ID/version multiset must exactly equal the first-page snapshot oracle; revoked permission items can only decrease and leave a reason, never leak.

The launch gate requires that across 100,000 rounds of randomized concurrent mutation, unexplained duplicates and missing items are both 0, cross-tenant or signature bypass is 0, snapshot-expiry-miscontinuation is 0, P99 page query meets the interface SLO, the maximum snapshot age and materialized bytes are below hard limits, and insufficient replica watermark is fully observable. First shadow-decode and compare against existing cursors, then let 5% of exports use snapshot v1. On anomaly, stop issuing new snapshots and let issued ones drain within a fixed window through a compatible reader; the rollback entry point does not delete the registry, nor silently interpret old cursors as live queries.

Further Reading

• REST API Pagination Design
• Database Cursor vs OFFSET
• Database Read Replicas
• REST API Versioning