pagination-guard

Pagination is almost always built and tested against a table that holds still — write the query, hit page 1, hit page 2, confirm nothing repeats, ship it. Production tables don't hold still: rows get inserted, deleted, and updated while a client is still working through page 3. Most write-ups of pagination correctness focus on performance (OFFSET getting slower the deeper you page) and treat the correctness question — does paging still return every row exactly once when the underlying set changes mid-scan — as solved by whichever library handles the SQL. It isn't solved by the library. It's solved, if it's solved at all, by two separate decisions the code has to get right: whether the sort order is actually deterministic, and whether each page is defined relative to a stable value or a volatile position. Get either wrong and the bug is invisible in every test that pages through a static fixture, and real only once a second concurrent writer exists — which is every production table with live traffic.

This is a documented, repeatedly-filed bug class, not a hypothetical. Strapi issue #18727 shows the tiebreaker half: a GraphQL list sorted by a non-unique field returned the same entries on two different pages while other entries never appeared at all, because rows tied on the sort key could land on either side of a page boundary depending on incidental physical order — adding the row's own unique id as a secondary sort key fixed it. GitLab issues #1952 and #37078 show the same shape independently (sorting by a mutable "weight"/milestone field let issues appear on multiple pages while others went missing once a list reached three-plus pages). SeaQL/seaography issue #19 shows the other half directly: page-based (offset) pagination skips or repeats a row whenever something is inserted or deleted between one page fetch and the next, because OFFSET N is a position in the result set, not a reference to any particular row — and the position moves every time a row ahead of it is added or removed.

Step 1: find every paginated read and classify its pagination style

Grep for the shape: a page/offset/limit query param feeding LIMIT/OFFSET (or an ORM's .skip()/.offset()/.paginate()), a cursor/after/before param, or a client-side "load more"/infinite-scroll loop that re-issues the same list query with an incrementing page number. List every consumer of each one: an admin table with page controls (usually tolerant of a rare glitch), a "load more" feed (same), but also anything that treats pagination as a complete enumeration — a full export, an incremental sync that pages through "everything since last run," a "select all N matching pages" bulk action, a search-index rebuild walking a table page by page. The second group is where a skipped or duplicated row is a real data-integrity bug, not a UX blemish, because the caller believes it saw every row exactly once and acts on that belief (a customer's export is missing a record, a sync job's cursor advances past a row it never actually processed).

Step 2: does the sort have a unique, stable tiebreaker?

Look at the ORDER BY (or the ORM equivalent) behind every paginated query found in Step 1. If it sorts by a single non-unique column — created_at, updated_at, a weight/priority/rank field, a status enum — any rows that tie on that column have no defined relative order. The database is free to return them in a different relative order from one query to the next (a different query plan, a different physical page layout after a vacuum/compaction, a replica answering instead of the primary), and paging slices through that undefined order: a tied row can land just before the page boundary on one request and just after it on the next, so the same page split returns it twice, or returns neither half correctly and the row is never returned at all. This is exactly what Strapi #18727 and GitLab #1952/#37078 hit. The fix is a secondary sort key that's actually unique (the primary key, or a composite that's guaranteed unique) appended to every ORDER BY a paginated query uses — not assumed, checked: a sort on created_at alone with millisecond (or coarser) timestamp precision and any realistic write volume will produce ties.

Step 3: for offset pagination, does a concurrent insert/delete shift the window?

Even with a fully deterministic sort order, LIMIT/OFFSET pagination has a structural problem Step 2 doesn't fix: OFFSET N means "skip the first N rows of the current result set," and "the first N rows" is a moving target. If a row ordered before the current offset is deleted between page fetches, every row after it shifts up by one position — the next OFFSET N now lands one row later than intended, and the row that was about to be the first result on this page was already returned as the last result on the previous page, or the row that should appear here is silently skipped. Insert a row ahead of the offset and the mirror bug fires: a row the client already saw shifts back into view and gets returned again. Neither requires a bug in the query itself — the query is correct for the table as it exists at the instant it runs; the bug is that two sequential page requests don't run against the same instant, and offset has no way to express "continue from where I actually left off" other than a number that silently means something different each time. For any Step-1 consumer that needs a complete enumeration, offset pagination cannot give that guarantee once the mutation rate during the scan is nonzero — this isn't a tuning problem to fix with a bigger page size or a faster query.

Step 4: for cursor/keyset pagination, is the cursor actually anchored on a stable, unique value?

Keyset (cursor) pagination fixes Step 3 by defining each page relative to the last row actually seen (WHERE (sort_col, id) > (last_sort_col, last_id) ORDER BY sort_col, id LIMIT N) instead of a position — a row inserted or deleted anywhere else in the table has no effect on where the next page starts, because the cursor names a value, not a slot. That guarantee only holds if the cursor is built the way it claims to be. Check for the common ways a "cursor" is really Step 2 or Step 3's bug with better branding:

• The cursor is an offset or page number, opaque-encoded. Base64-decode a sample cursor; if it's just {"offset": 40} or {"page": 3} with a checksum, it has exactly Step 3's problem — renaming the parameter didn't change what it means.
• The cursor is anchored on a mutable column with no unique tiebreaker. A cursor built from updated_at alone (or any field a normal write changes) reintroduces Step 2's tie problem, plus a sharper failure: a row that gets updated between pages can jump to a position the cursor already passed (permanently skipped, cursor never returns to it) or jump ahead of where it would otherwise have been returned (duplicated). The fix is the same one Step 2 names — append a genuinely immutable, unique column (the primary key, a creation-order sequence) to both the ORDER BY and the cursor's comparison tuple.
• The cursor is read-after-write inconsistent. If pagination reads from a replica that lags the primary, a row visible when the cursor was issued can not yet exist on the replica that serves the next page (or vice versa) — a different failure mode from Steps 2–3, but one that produces the identical symptom (skip/duplicate) and is worth naming separately so the fix (read-your-writes routing, or accepting eventual consistency explicitly) isn't confused with an ORDER BY fix.

Step 5: match the finding's severity to what the consumer actually needs

Not every instance is equally serious — say so plainly rather than reporting every paginated endpoint as equally broken. An infinite-scroll feed or an admin table that occasionally shows a duplicate card for one scroll is a real bug worth fixing but rarely urgent. A full data export, an incremental sync that advances a "processed up to here" cursor, or anything feeding a billing/compliance/audit calculation from "every row, exactly once" is a data-integrity incident waiting to happen the first time it runs against a table someone is actively writing to — flag these as the priority, and name the specific consumer (not just "pagination is unsafe in general").

Report

BLOCK for: a Step-1 consumer that needs a complete enumeration (export, sync, select-all-pages action, reindex) built on offset pagination with nonzero concurrent write volume during the scan (Step 3); any paginated query — offset or cursor — sorted without a unique tiebreaker, on a column that can plausibly tie (Step 2); or a "cursor" that decodes to a bare offset/page number (Step 4). CAUTION for: an infinite-scroll/admin-table consumer with the same Step 2/3 gaps, where the cost of an occasional skip/duplicate is a UX nit rather than a data-integrity incident, but the fix is the same so it should still be scheduled; or a cursor anchored on a mutable column with a unique tiebreaker present (closes Step 2's tie but not Step 4's "row jumped past the cursor" case) — name that residual gap rather than calling it fully fixed. Name the exact query, its ORDER BY, and whether the pagination style is offset or keyset for every finding — "pagination looks fine" is not a finding; either the sort is deterministic, the page boundary is anchored on a value a concurrent write can't move, and the consumer's own correctness need is met, or it isn't.

Boundaries

• Not selectall-guard's territory — that skill audits whether an admin bulk action re-resolves its filter or acts on a stale snapshot once it starts writing; this skill audits whether a read-only paginated listing, export, or sync stays correct while concurrent writes happen elsewhere, independent of any action being taken on the results.
• Not export-guard's territory — that skill audits silent row-count caps/timeouts truncating an export and spreadsheet type-mangling of identifier columns; this skill audits whether the export's underlying pagination itself skips or duplicates rows, a different failure that produces a wrong-but-complete-looking file rather than a visibly truncated one.
• Not stale-guard's territory — that skill audits cache invalidation correctness; a pagination bug exists even with no cache in the picture at all, straight against the primary datastore.
• Doesn't design a pagination scheme from scratch or pick offset vs. keyset for a greenfield endpoint — it audits whether an existing implementation stays correct once real concurrent writes happen during the scan, and names which structural category (Step 2 tiebreaker, Step 3 offset-shift, Step 4 cursor-anchoring) the gap falls into.
• Can't prove a specific historical export or sync actually skipped or duplicated a row from source code alone — it identifies whether the implementation is capable of that failure and says plainly when confirming an actual incident needs the export's row count/checksum against the table's state at the time, not a code read.