Define a Unique Order Before Asking for Pagination

Before asking an agent to add pagination, define the complete row order and what happens when data changes between pages. A timestamp alone may leave tied rows unresolved. A unique order settles those ties, but it does not freeze the dataset across requests.

A useful pagination brief therefore names two policies: how rows are ordered, and whether the reader continues a retained result or sees live changes on every request. Then it gives complete page examples that the builder can check.

The following fictional list uses eight records. Its dates and IDs are authored sample data, not application logs. We will keep one result snapshot while browsing, then explicitly restart to see an insertion and deletion.

Give tied rows a complete order

Here is the initial dataset, deliberately shown out of order:

Choose these rules for the example:

• Sort created_at from newest to oldest.
• Break every timestamp tie by unique integer id, ascending.
• Require both fields. Missing timestamps or duplicate IDs are invalid input to this example's result set, rather than silently assigned an order.
• Keep the fields' meanings consistent: the timestamp is a UTC creation time, and the ID is an integer, not a formatted string sorted by its label.

The complete expected order is:

101, 102, 103, 104, 105, 106, 107, 108

For PostgreSQL 18, the ordering portion could be expressed as:

ORDER BY created_at DESC, id ASC

PostgreSQL's version 18 LIMIT/OFFSET documentation explains why limited results need a predictable, unique order. That supports specifying the tie-breaker. It does not supply this application's snapshot policy. No PostgreSQL query was executed for this guide.

Choose what stays fixed while the reader browses

For this fictional list, choose a retained-result policy. Opening the list captures snapshot S1, including membership, row values, filters, and ordering. Subsequent pages use that same result until the reader selects Restart.

This is a requirement to implement, not a property obtained merely by adding ORDER BY. The builder must establish how the selected application can retain and identify that result. If it cannot do so, it should propose an explicit live-data alternative rather than label live pages as a snapshot.

With page size 3, all three pages of S1 are:

Joining these page lists reproduces the full eight-ID order exactly once. The last page is short because only two rows remain. A request after the end should return an empty page with no further continuation; the interface should not offer another Next action.

An entirely empty snapshot also has an empty first page and no continuation. It should display an empty-result state, rather than pretending that page 1 is still loading.

Keep an old snapshot separate from a new result

Now suppose these changes occur after the reader has viewed page 1:

1. Insert ID 109, with creation time 2026-01-15T10:15:00Z.
2. Delete ID 104 from the current underlying dataset.

Under the chosen policy, continuing S1 still returns 104, 105, 106 on page 2. The retained result includes the historical row 104. Its presence describes S1; it is not evidence that the current dataset still contains that row. This guide specifies the result-list display, not the behavior of opening a current record that has since been deleted.

Reopening page 2 of S1 should reproduce that same page while S1 remains available. If S1 is no longer available, show a clear restart requirement. Do not silently replace it with a newer result under the old snapshot identity.

Selecting Restart captures S2 from the changed dataset. Its complete order is:

109, 101, 102, 103, 105, 106, 107, 108

The new three-page sequence is:

These are two separate complete results. Combining page 1 from S1 with pages 2 and 3 from S2 would not constitute either snapshot.

See how a live offset can cross the boundary

Consider a different policy: every page request reads current data. If the reader saw S1's first page and then requested offset 3 against the changed dataset, that second request would return:

103, 105, 106

ID 103 would appear again, even though the sort order is unique. The input set changed before the offset was applied. The unique tie-breaker solved ordering within each result; it did not preserve membership across the two requests.

This counterexample is not a reason to ban offsets. Offsets over the same retained result give the page slices shown above. A cursor may instead describe a position after an ordering key, but its relationship to live insertions, deletions, and snapshot identity still needs a policy.

Choose an implementation after settling those user-visible rules. This small example does not compare database performance or declare either position scheme universally preferable.

Define a page-size change explicitly

In this example, changing the page size resets to the first page of the same retained snapshot. It does not capture a new snapshot or reinterpret an old page number.

For S1 at size 4:

After page 2, there are no more rows. This policy makes the result of changing size checkable, rather than leaving the builder to guess whether the reader's old row position or page number should be preserved.

Copy this pagination brief

Plan pagination for the attached eight-row fictional dataset.

Order:
created_at descending, then unique integer id ascending.
Both fields are required. Do not rely on timestamp ties retaining the
input or database's incidental order.

Browsing policy:
Opening the result captures S1 membership, values, filters, and order.
Continue that same retained result while paging. An explicit Restart
captures the current dataset as a new result. If a retained result is
unavailable, request a restart rather than silently substituting new data.

Expected S1 pages at size 3:
[101,102,103], [104,105,106], [107,108].
After page 1, insert 109 at 2026-01-15T10:15:00Z and delete current row 104.
Continuing or reopening S1 page 2 still gives [104,105,106].

After Restart, expected S2 pages:
[109,101,102], [103,105,106], [107,108].
Do not combine page slices from different results.

Changing page size to 4 resets to the first page of the same snapshot.
S1 then yields [101,102,103,104], [105,106,107,108].
An empty result and an after-end request have no next continuation.

Inspect the application's current ordering, page-position and retained-
result mechanisms. Recommend offset or cursor handling against these
requirements; explain what establishes result identity and availability.
If retention is unsupported, identify the gap before proposing changes.

Return a bounded plan and checks for ties, all three pages, size changes,
empty/end cases, insertion/deletion, and reopening a page. Keep current-
record navigation separate from displaying a historical result row.
Use local fixtures only. Do not connect a database, call a live API,
modify production data, install packages, or deploy in this step.

The accompanying in-memory Python calculation reproduced both full orders, their page slices, the size-change example, and the live-offset duplicate on Python 3.12.14. That checks the arithmetic of these authored examples; snapshot retention, database behavior, and interface rendering still require implementation-specific evidence.