ADR-0016: Every paginated query needs a unique sort tiebreaker
Status: Accepted
Date: 2026-07-12
Scope: core (any offset-paginated query — JPA/SQL, Spring Data Page<T>, etc.)
Context
Offset pagination (LIMIT n OFFSET m) fetches a list one page at a time — page 1 is
“the first n”, page 2 is “skip n, take n”, and so on. Each page is a separate
query. The database only guarantees the order you explicitly ask for; for rows
that are tied on the sort key it may return them in any order, and that order can differ between the page-1 query and the page-2 query.
When it does, a row sitting near a page boundary lands in a different position in
each query, so it can be returned on two pages (duplicated) or on none
(silently skipped). Nothing is missing from the table — it just falls through the
crack between two page windows. Adding or deleting any row reshuffles the ties, so
the skipped row appears to randomly come and go.
This is not theoretical. In Expenses, the customer expense list sorted by ORDER BY e.purchaseDate DESC only. Many expenses share a purchaseDate (a busy
day, and especially installment slices — a converted purchase produces N slices all
dated the same day). Once a day’s tied rows exceeded the page size, a page boundary
cut through the tie group and an expense (“Receita Federal”) vanished from the list
while still existing in the database — reappearing whenever an add/delete reshuffled
the order. A sweep found the same latent defect in every paginated query
(expenses, installments, income entries, and seven admin/system lists).
Decision
Every paginated query MUST end with a total order — append the row’s unique key
(id) as the final ORDER BY term. The business sort stays first (for the order
users see); the id only breaks ties, making pagination deterministic across the
separate page queries.
Query owns its ORDER BY (JPA @Query, native SQL): append , <alias>.id
(DESC to match). Example: ORDER BY e.purchaseDate DESC, e.createdAt DESC, e.id DESC.
Sort comes from a Pageable (Spring Data derived queries / no ORDER BY in
the query): append the id in the adapter before querying. Expenses uses the shared
helper Pageables.withIdTiebreaker(pageable) (adds id DESC unless the sort
already includes it).
List (non-paginated) queries do NOT need this — they return every row in one
query, so an unstable tie order only affects display order, never completeness.
Only add a tiebreaker where a Page<T>/LIMIT..OFFSET is involved.
Keyset (“seek”) pagination is the more scalable long-term answer for very large
lists, but is out of scope here; the id tiebreaker fixes correctness for the offset
pagination we use today.
Consequences
Pagination is deterministic: no skipped or duplicated rows across pages, and the
order is stable between requests.
Negligible cost — id is the primary key, already indexed.
New reviewer checklist item: a Page<T> query without a unique final sort key is
a bug, even if it “looks fine” in testing (the defect only surfaces once a tie
group crosses a page boundary).
Example
Real code from the repos — optional
Agents: safe to skip. The Decision above is the specification; everything below is
illustration. Skipping this block costs you nothing normative and saves tokens. Expand it
when you need the shape rather than the rule — porting the pattern to a new project, or
answering “what does this actually look like”.
/** Returns pageable with a trailing id DESC tiebreaker (unless it already sorts by id). */public static Pageable withIdTiebreaker(Pageable pageable) { if (pageable.getSort().getOrderFor("id") != null) { return pageable; } Sort sorted = pageable.getSort().and(Sort.by(Sort.Direction.DESC, "id")); // An unpaged request has no page number or size to read — Unpaged.getPageNumber() // throws — so it can only carry the sort. if (pageable.isUnpaged()) { return Pageable.unpaged(sorted); } return PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), sorted);}
Two call sites, one rule. Where the sort arrives as a Pageable, the adapter runs it
through this helper before querying. Where the ORDER BY lives in the @Query, the
tiebreaker is appended there by hand instead:
ORDER BY e.purchaseDate DESC, e.createdAt DESC, e.id DESC
The failure this prevents is not a crash. Rows tied on purchaseDate can come back in a
different order for page 1 and page 2, so a row near the boundary is returned twice or
never — silently, and only under real data.
Incident + fix: Expenses backend JpaExpenseRepository.findAllWithFilters and the Pageables helper (infrastructure/persistence/common/).
Companion frontend concern: don’t filter server-paginated data on the client (it
makes counts and cross-page search wrong) — push filters into the query instead.