When an AI coding assistant writes a list endpoint, review what happens between the first page and the next. Require an unambiguous ordering, a continuation rule that matches it and an explicit decision about changes made while someone browses. A successful first-page response cannot establish those properties.
This matters for internal work queues, customer portals and reporting tools. A skipped request can disappear from an operator’s current view; a repeated record can make an export look more complete than it is. The relevant acceptance question is which records the endpoint promises to return under specified conditions.
The examples below are fictional design exercises, not observed software failures. We have not run a database benchmark, tested a coding model or deployed the proposed endpoint. The recommendations are an editorial review method grounded in database documentation and engineering guidance.
Specify whether the user is browsing or taking an inventory
Before requesting code, decide what the list represents. A live queue can intentionally reflect current status each time someone requests another page. An inventory used for reconciliation may instead need the records and values as they existed at one defined point.
Those are different contracts. A label such as “export all” should not silently mean “visit successive views of a changing table.” Write down whether new records should appear, whether changed records may move, and what deleted records mean for the result.
For our fictional queue, assume records have an immutable, non-null creation timestamp and unique numeric ID. Operators browse newest first. The initial contract permits live status changes and tells users to refresh to see newly arrived work. A separate reconciliation export needs a consistent dataset; it will require its own supported generation process.
Making that choice early gives the coding assistant something testable to implement. It also prevents a developer from promising snapshot behavior simply because the generated code contains a cursor parameter.
Make equal sort values an ordinary case
The PostgreSQL 18 LIMIT documentation warns that selecting portions of a query requires an ordering that uniquely determines the rows. Without it, different limits or offsets can produce inconsistent subsets. It also notes the cost of computing rows that a large offset then skips.
For the fictional queue, “newest first” needs more precision than ordering only by creation time. Suppose three requests were created at exactly the same stored timestamp:
| Request ID | Creation time, UTC | Intended position |
|---|---|---|
| 503 | 09:00:00 | First |
| 502 | 09:00:00 | Second |
| 501 | 09:00:00 | Third |
| 500 | 08:59:00 | Fourth |
With a page size of two, the proposed order is creation time descending, then ID descending. Page one should contain 503 and 502. The next boundary must retain both the timestamp and ID of 502. Comparing only the timestamp with “earlier than 09:00:00” would skip 501.
Shopify’s August 2019 engineering account of relative cursors explains the same general problem for non-unique sort fields: retaining a unique secondary key avoids dropping records tied on the primary sort value. It also describes a navigation tradeoff: continuing from a position does not provide arbitrary page-number jumps. This is historical practitioner guidance, not a current performance claim about Shopify’s APIs or your database.
The worked queue is our own illustration. Its acceptance rule should be established independently of whatever query the assistant produces.
Review the continuation predicate with the ordering
For this deliberately restricted example, after the last returned pair (09:00:00, 502), the next records are those with an earlier creation time, or the same creation time and a smaller ID. Both components descend. Keeping those directions together is essential to the proposed order.
Ask the coding assistant to explain that rule using the four fixture records. Then inspect the generated query or query-builder output. A fluent explanation is useful for review, but the implementation must actually apply the stated filter, ordering and limit.
Do not generalize this simple rule blindly. Nullable sort fields, mixed ascending and descending directions, text collation and joins need their own treatment. A join that returns several rows per request can invalidate a boundary intended to identify unique requests; our SQL join review guide covers that separate cardinality problem.
GitLab’s keyset pagination documentation describes primary-key tie-breakers, special configuration for nullable ordering and checking the generated query’s index support. It explicitly recommends examining performance for both the first page and the continuation page. These are details of GitLab’s implementation and useful review questions; they do not establish that another framework automatically supplies the same behavior.
Preserve stored timestamp precision in the continuation representation. Rounding the boundary to what the interface displays changes the position when the database stores finer precision. Define how the application validates the token, maintains query scope and handles incompatible or expired cursors. An encoded position is not an authorization grant: apply the endpoint’s access checks on every request.
Separate position stability from a frozen dataset
Even a correctly ordered cursor does not answer every concurrency question. In the fictional queue, insert request 504 with a later timestamp after page one has been read. Continuing below 502 will not include it. That is consistent with our stated browsing contract: refreshing starts a new traversal that can show newer work.
Now imagine sorting by an editable priority field instead. A request already seen could move behind the cursor and appear again. An unseen request could move ahead of it and be missed during that traversal. A unique ID resolves ties; it does not prevent the primary sort value from changing.
The PostgreSQL 18 isolation documentation explains that at Read Committed, successive SELECT commands can observe different committed data, even inside one transaction. Repeatable Read provides a different snapshot behavior within a transaction. These database guarantees should not be confused with separate HTTP requests automatically sharing one database snapshot.
For a consistent export, ask the technical owner to choose and verify a supported snapshot or materialization design with an appropriate lifetime and resource budget. Saving only a set of IDs does not freeze the associated values if those values can later change. Likewise, a creation-time cutoff does not freeze mutable status, amount or ownership fields.
Avoid holding a database transaction open across an operator’s browsing session merely to imitate a snapshot. Establish the actual operational constraints with the database owner. If the product only supports live traversal, state that limitation instead of generating a reassuring export label.
Test transitions that exercise the actual promise
Our proposed review starts with the four-record fixture and checks the combined sequence, not just the number of rows on each page. With unchanged data, it should be 503, 502, 501, 500 exactly once in that order. A count of four would also accept one repeated record and one missing record.
Then introduce changes deliberately:
| Proposed case | What the reviewer should establish |
|---|---|
| Equal timestamps across a boundary | Neither a tied record nor its unique key is lost |
| Newer insertion after page one | Continuation and refresh follow the documented browsing contract |
| Deletion of the last returned record | A saved boundary still behaves as the endpoint specifies |
| Mutable sort value crosses the boundary | The behavior is understood and acceptable, or that ordering is disallowed |
| Filter changes while retaining an old cursor | The application rejects or restarts an incompatible traversal |
| Empty final page or exact page-size multiple | End-of-list handling terminates without a loop |
These are proposed acceptance cases, not tests executed for this article. Add permission changes and the actual nullable-field cases where relevant. For a snapshot export, compare both identifiers and required values with the agreed frozen reference.
Finally inspect the executed query plans and realistic data volume in the intended environment. Correct membership and acceptable query cost are separate checks. Retain the relevant fixture, ordering definition and consistency decision beside the endpoint so a later AI-assisted refactor can be assessed against the same promise.
Our agent pagination guide covers following continuation markers and qualifying incomplete retrieval. This guide addresses the earlier boundary: whether the endpoint itself delivers the sequence its consumer expects. Review one generated list route at that boundary before treating a polished first page as completed work.