A Cursor Is a Position, Not a Page Number

A list can look simple while hiding a difficult promise: after someone reads the first page, what exactly should the next page contain?

That question becomes harder when records arrive, disappear, or change while the person is browsing. Pagination is part of the API contract because it defines how a reader moves through a changing collection.

A useful starting point is to treat a cursor as a position inside a particular ordering. That makes the query easier to reason about and exposes the promises it cannot make.

Consider an activity list sorted by creation time, newest first. Several records can share the same timestamp. If the query orders only by that timestamp, their relative positions remain unspecified.

Add a unique tie-breaker:

ORDER BY created_at DESC, id DESC

Assume both columns are non-null, the identifier is unique, and neither value changes after creation. Every record now has a distinct position.

PostgreSQL’s LIMIT and OFFSET documentation explains why limited results need a unique ordering. Without it, different requests can return inconsistent subsets.

The tie-breaker belongs in the public behavior of the endpoint, even if clients never see the SQL. Changing it later changes what an existing cursor means.

An offset says how many records to skip. A cursor can describe the last position already returned.

For our descending list, the next request asks for records before that position:

SELECT id, created_at, title
FROM activities
WHERE project_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;

Here, $1 comes from the authorized request scope. $2 and $3 are the previous page’s final timestamp and identifier. $4 is a validated fetch limit.

PostgreSQL compares these row values from left to right: an earlier timestamp qualifies; when timestamps match, the identifier decides. Its row comparison documentation also describes null behavior, which is why this example assumes non-null ordering columns.

If the order changes, the continuation predicate must change with it. Mixed ascending and descending directions need a predicate designed for those directions; copying this comparison mechanically would be a mistake.

Suppose the endpoint returns up to 20 items. Fetch 21.

If the query returns 21 records, send the first 20 and expose a continuation cursor. Build that cursor from the twentieth record, the last item actually delivered. The extra record is evidence that more matching data existed when the query ran.

If the query returns at most 20 records, return them with no next cursor.

This keeps the boundary easy to explain. Building the cursor from the extra record would skip an item the client never received.

It also avoids requiring an exact total for every request. An exact count can still be useful, but it is a separate piece of information with its own query cost and consistency behavior.

A position only makes sense inside the collection that produced it.

A cursor for one project should not silently become a cursor for another. A cursor for open records should not continue a list of closed records. Changing the search text or sort order should start a fresh traversal.

A conceptual cursor payload might include:

{
  "version": 1,
  "sort": "created-desc-id-desc",
  "filterFingerprint": "server-computed-query-fingerprint",
  "after": {
    "createdAt": "2026-10-05T06:00:00.123456Z",
    "id": "record-1042"
  }
}

Preserve the timestamp precision used by the database. Rounding a boundary value during serialization can move the boundary and repeat or skip records.

Treat this payload as an internal format. Encode it for transport and sign it when the server must detect modification. Base64 encoding alone provides neither integrity nor confidentiality.

Validate the version, value types, size, and query fingerprint before using the cursor. Continue to establish authorization from the current request. A valid cursor never grants access to its recorded scope.

Imagine that a person reads the newest 20 records. Before the next request, another record arrives above the boundary.

The continuation query still moves toward older records. That new record will appear when the person refreshes the beginning of the list. This can be an appropriate contract for an activity feed.

Other changes are more disruptive. If a previously viewed record moves below the boundary because its sort value changes, it may appear again. If an unseen record moves above the boundary, the ongoing traversal may miss it.

Even immutable ordering values do not create a snapshot. Records can be deleted, new records can be inserted behind the boundary, and filter membership can change.

Choose the promise the product actually needs. A live list can describe results as changing during navigation. A reproducible export needs a separate consistency strategy, such as materializing the selected records and their required values. Freezing only their identifiers does not freeze mutable content.

A timestamp cutoff can exclude some newer records, but it cannot by itself preserve earlier values, deletions, or backdated inserts.

A first-page test cannot establish pagination correctness.

Build a small fixture with repeated timestamps and enough records for several pages. Follow every cursor and compare the collected identifiers with the expected ordered list. Assert both completeness and uniqueness.

Then test the boundaries that make the contract meaningful:

• Delete the record that supplied the cursor. Continuation should still work from its stored values.
• Insert a newer record between requests. Verify the documented live-list behavior.
• Change a filter or project. Reject the old cursor rather than quietly reusing it.
• Try malformed, modified, and unsupported-version cursors.
• Preserve timestamp precision through an encode-and-decode round trip.

These checks examine the reader’s journey through the collection. They catch mistakes that a single successful query can conceal.

Cursor pagination works best when its rules fit into a short explanation: records have a unique order, continuation begins after the last delivered position, and the cursor belongs to the same authorized query.

Write that explanation before designing the token. It will reveal whether ordering values can change, whether nulls need special treatment, and whether the product really needs a frozen result.

The next page should be something the system can explain precisely. A cursor is useful because it gives that explanation a concrete boundary.