Dev Handbook

0016-stable-pagination-tiebreaker

3 min read

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).
  • 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.

Graph View

Backlinks