design-api-pagination

name	design-api-pagination
description	Designs paginated list endpoints that stay correct and fast under concurrent writes — cursor/keyset pagination over a stable total ordering with a unique tie-break key (e.g. ORDER BY created_at DESC, id DESC and WHERE (created_at,id) < (?,?)), opaque base64url-encoded cursors that bind sort+filter so they can't be tampered or reused across queries, a sane page_size default (20-50) and hard cap (100), and the {data, next_cursor, has_more} envelope (fetch limit+1 to compute has_more without a COUNT) — instead of OFFSET/LIMIT, which gets O(n) slow on deep pages and skips/duplicates rows when items are inserted or deleted mid-scan; covers REST and GraphQL Relay connections (edges/node/cursor + pageInfo.hasNextPage/endCursor), forward+backward paging, and why total counts are expensive and usually optional.
when_to_use	Building or fixing a list/feed/search endpoint that returns many rows and needs paging, an infinite-scroll or "load more" API, a stable cursor under live inserts/deletes, or migrating a slow OFFSET endpoint to keyset; or implementing a GraphQL Relay connection. Distinct from api-design-review (reviews the whole API surface/REST conventions; this owns the pagination mechanics specifically) and optimize-sql-query (builds the covering composite index that makes the keyset WHERE/ORDER BY fast; this decides the cursor/ordering contract that index must serve).

When to Use

Reach for this skill when an endpoint returns a list that's too big for one response and must page through it correctly:

• "Add pagination to this list/feed/search endpoint" / "support infinite scroll / load-more"
• "Our ?page=500 query takes 8 seconds — deep OFFSET is killing us"
• "Users see duplicate or missing rows while scrolling a live feed" (rows inserted/deleted mid-scan)
• "Design the cursor — should it be opaque? what goes in it?"
• "Implement a GraphQL Relay connection (edges/pageInfo/cursors)"
• "We need stable ordering with a tie-break so pages don't shuffle"
• "Do we have to return a total count?" (usually no — it's the expensive part)

NOT this skill:

• Reviewing the whole REST/HTTP API surface — resource naming, status codes, versioning, error shape → api-design-review (this skill is only the pagination contract within it)
• Defining the serialized field types / GraphQL schema contract in general → rest-graphql-contract (this skill specifies the connection/cursor shape it slots into)
• Building the composite/covering index that makes the keyset WHERE (a,b) < (?,?) fast, EXPLAIN-tuning the scan → optimize-sql-query (this skill defines the ordering the index must support)
• Caching list responses / CDN / ETag for pages → caching-strategy
• Rate-limiting how many pages a client can pull → rate-limiting
• Throttling/queuing expensive list jobs → message-queue-jobs
• Designing the underlying table/keys → design-relational-schema (this skill consumes the unique key it needs as a tie-break)

Steps

1. Default to keyset (cursor) pagination; reach for OFFSET only for small, static, jump-to-page-N admin tables. The two models:

   	Offset/limit	Keyset/cursor
   Query	ORDER BY ... LIMIT 20 OFFSET 980	WHERE (sort_key,id) < (?,?) ORDER BY ... LIMIT 20
   Deep-page cost	O(offset) — DB scans + discards all skipped rows	O(1) w/ index — seeks straight to the cursor
   Concurrent insert/delete	skips or duplicates rows (offset shifts under you)	stable — anchored to a value, not a position
   Jump to page N	yes	no (sequential only)
   Total pages	derivable (needs COUNT)	not directly

   Offset is fine for a 200-row config table behind admin; for any feed, search, timeline, or table that grows or is written concurrently, keyset is the default.

2. Pick a stable total ordering with a unique tie-break — this is the whole game. The ORDER BY columns must be (a) the user-visible sort and (b) made total by appending a unique, immutable column (the PK) so no two rows compare equal. A non-unique sort (ORDER BY created_at alone) lets rows with the same timestamp straddle a page boundary → duplicates or skips.

   -- newest-first feed, made total by id tie-break
   ORDER BY created_at DESC, id DESC
   

   The cursor encodes the full sort tuple of the last row returned: (created_at, id). Use row-value comparison so it's one index seek:

   WHERE (created_at, id) < (:last_created_at, :last_id)   -- DESC page
   ORDER BY created_at DESC, id DESC
   LIMIT :page_size + ;
   

Common Errors

• OFFSET for deep pages on a growing table. OFFSET 100000 scans and throws away 100k rows; latency grows linearly with page depth. Fix: keyset/cursor (step 1).
• Non-unique ORDER BY (no tie-break). ORDER BY created_at with duplicate timestamps → rows straddle page boundaries, appear twice or vanish. Fix: append the unique PK to make ordering total (step 2).
• Exposing a raw offset/id/timestamp as the "cursor." Clients build their own, you can't change the format, and they construct invalid ones. Fix: opaque base64url token, documented as un-parseable (step 3).
• Cursor not bound to the query. Client keeps the cursor but switches sort or filter → garbage page or skipped rows. Fix: encode a filter/sort hash in the cursor and 400 on mismatch (step 3).
• No page-size cap. ?page_size=1000000 OOMs the server. Fix: default 20–50, clamp to max 100 (step 4).
• Separate COUNT(*) on every page for has_more. Doubles DB load. Fix: fetch limit + 1 and check for the extra row (step 4).
• Mandatory total_count. Forces a full count scan, and it's wrong under concurrent writes anyway. Fix: omit by default; opt-in / cached / estimated (step 5).
• Sorting on a mutable column without telling anyone. Ordering by updated_at lets a row jump pages mid-scroll → silent dup/skip. Fix: prefer immutable sort (created_at/id); if mutable, document the behavior.
• Missing/mismatched index. Keyset query without a composite index matching column order+direction → full sort per page, no speedup. Fix: index the exact (filter…, sort…, id) tuple, verify no Sort in EXPLAIN (step 8).
• Row-value comparison with mixed sort directions. (a,b) < (?,?) is wrong when a and b sort opposite ways. Fix: expand to the explicit OR-chain predicate (step 2).
• GraphQL inventing its own {items, nextPage} instead of Relay connections. Breaks Relay/Apollo client cache + tooling assumptions. Fix: follow edges/node/cursor + pageInfo (step 7).
• Off-by-one at the boundary. Forgetting to drop the +1 probe row leaks it into data and as the cursor. Fix: slice to , derive from the last row.

Verify

1. Deep page is fast: request the millionth row's page; latency ≈ first page (constant), not linear. EXPLAIN ANALYZE shows an index range scan, no Sort node, near-zero rows filtered.
2. Stable under inserts: start paging, insert/delete rows ahead of and behind the cursor mid-scan; assert no row appears twice and no existing-before-the-cursor row is skipped (the offset failure mode).
3. Tie-break holds: seed many rows with identical sort values (same created_at); page through and assert every row appears exactly once across page boundaries.
4. Cursor is opaque + bound: decode shows no client-meaningful offset; reusing a cursor with a changed filter/sort returns 400, not a corrupt page.
5. Page size enforced: page_size=1000000 returns ≤100; page_size=0/negative is rejected.
6. End-of-list is clean: the last page returns has_more=false and next_cursor=null; clients never need an extra empty request to detect the end.
7. has_more without COUNT: confirm the query plan fetches limit+1 and runs no COUNT(*) unless include_total is explicitly set.
8. Bidirectional round-trip: page forward N then backward N lands on the original rows in the original display order (slice was re-reversed correctly).
9. Relay conformance (GraphQL): first/after and last/before work; pageInfo.hasNextPage/endCursor are correct; mixing first with before is rejected; totalCount is opt-in.

Done = list endpoints use keyset cursors over a unique-tie-break total ordering, cursors are opaque base64url tokens bound to their query, page size is defaulted and hard-capped, has_more comes from limit+1 (no mandatory COUNT), the {data,next_cursor,has_more} (or Relay connection) envelope is stable, a composite index backs the ordering, and the consistency/perf tests in checks 1–9 pass under concurrent writes.