Implement cursor pagination without losing or repeating records

A support queue lists tickets newest first. The client requests the next page using the last ticket's timestamp. Several tickets share that timestamp, so some never appear. An offset-based version has a different problem: a new ticket inserted between requests shifts the positions and repeats an item. Pagination needs an explicit ordering and a clear statement about what changing data can do.

This example assumes MongoDB with immutable createdAt values and ObjectId identifiers. It uses descending order by creation time and identifier. The identifier provides a unique tie-breaker; it is not treated as a precise business creation clock. Tenant and visibility filters are part of every query.

Define a total order

Sorting only by a nonunique timestamp does not establish a stable position for equal values. Add a unique tie-breaker and include it in both the sort and cursor. For newest-first tickets, order by createdAt descending and _id descending. The cursor identifies the last returned pair.

State whether the sort fields can change. A queue ordered by mutable priority or last activity can move records between pages while a reader browses. A cursor still helps traversal, but it cannot prevent every repeat or omission when the order changes. If the product requires a fixed export, use a snapshot-oriented process or a captured membership strategy.

Clarify whether the user wants a live feed or an exact traversal. A live feed can tolerate new items appearing above the current cursor. An accounting export cannot silently omit a row because its priority changed. The pagination contract should describe this difference before selecting a query pattern.

Build the boundary predicate

For descending order, the next page contains rows with an earlier timestamp, plus rows at the same timestamp with a smaller identifier. The compound condition must match the sort direction. Reversing only one part creates gaps.

const boundary = cursor ? {
  $or: [
    { createdAt: { $lt: cursor.createdAt } },
    { createdAt: cursor.createdAt, _id: { $lt: cursor.id } }
  ]
} : {}

const rows = await tickets.find({
  tenantId: actor.tenantId,
  ...boundary
}).sort({ createdAt: -1, _id: -1 }).limit(pageSize + 1).toArray()

This outline assumes validated cursor values already converted to Date and ObjectId and a validated tenant context. If other filters use logical operators, compose them safely rather than spreading objects that can overwrite each other. The extra row determines whether another page exists; return only the requested page size.

Build the next cursor from the last returned row, not the extra probe row. Otherwise the probe record can be skipped. Return a null cursor when there is no next page and document how clients should stop.

Validate cursor scope

Encode both timestamp and identifier in an opaque application token. Base64 encoding is a transport representation, not integrity protection. If tampering matters, sign the token or validate its semantics strictly. Always enforce authorization independently; a signed cursor is not permission to read another tenant.

Include or bind filter identity when a cursor must apply to a particular search or status view. Reusing a cursor from the open queue in the closed queue can produce confusing results even if it does not leak data. Reject incompatible cursors with a stable client error rather than a driver exception.

Limit page size on the server. A caller requesting an enormous page can defeat the bounded-work advantage. Define minimum, maximum, and default values, and validate malformed numbers. Do not accept a raw query parameter as a database operator object.

Match the index to the query

A compound index beginning with the tenant equality field followed by the ordered fields is a reasonable candidate for this query shape. Verify it with the actual filter and sort plan. Additional status or visibility predicates may change the best index and the examined-document count.

Do not add an index solely because the example contains those fields. Inspect existing indexes, query distribution, and write costs. A query returning a few rows can still examine many documents if a selective predicate is not supported. Choosing an index from a slow query describes that evidence.

Offset pagination remains useful for small stable datasets or interfaces requiring numbered pages. Its cost and shifting positions should be understood. Cursor pagination trades easy page-number jumping for efficient continuation under a defined order. It does not automatically provide a frozen snapshot.

Test boundary cases explicitly

Create a synthetic dataset with many equal timestamps, more than one page, and identifiers on both sides of a boundary. Traverse all pages without modifying data and assert every expected identifier appears exactly once. Assert the same total order as a complete sorted query.

Test a page with exactly the limit, a page with limit plus one, and an empty result. Test invalid cursor encoding, invalid timestamp, invalid identifier, and a cursor from another filter. Check that these cases produce deliberate responses and never remove the tenant predicate.

Insert a new ticket above the current cursor between requests. Under the live-feed contract, it should not shift older rows into repeats; it will appear when the reader refreshes from the top. Then update a mutable sort field in a separate test to demonstrate the documented limitation. Do not hide that limitation by testing only immutable fixtures.

Review generated predicates with concrete pairs

An AI assistant can easily produce a plausible but reversed comparison. Give it three pairs: one earlier timestamp, one equal timestamp with smaller identifier, and one equal timestamp with larger identifier. Ask which pairs should be included after the cursor and why. Validate the answer against the actual query.

Ask it to inspect filter composition and cursor construction. A common subtle bug is building the cursor from the extra row or omitting the tie-breaker from the encoded token. Another is decoding dates as strings while the collection stores Date values, creating a type mismatch rather than a chronological comparison.

Keep example identifiers synthetic. There is no need to paste customer ticket content to review the ordering algorithm. Deterministic fixtures reveal the important behavior more clearly than a random production sample.

Choose consistency from the task

For an interactive queue, an immutable creation order with refresh-at-top behavior can be simple and honest. For an export, capture a cutoff or use a database-supported snapshot strategy and verify its limits under the deployed topology. A cutoff alone does not freeze mutable rows, so state exactly what it guarantees.

API contracts should document cursor shape and errors. Authorization tracing ensures every continuation request stays within its data scope. A good pagination implementation is the agreement between those contracts and the storage order.