/api-pagination

name: api-pagination
description: Implement correct, fast API pagination — cursor vs offset trade-offs, opaque cursor encoding, stable sort keys, page-size limits, total-count costs, and consumer-side iteration that survives inserts, deletes, and retries. With runnable cursor checks.
type: skill
version: 1.0.0
updated: 2026-07-10

API Pagination

Pagination looks trivial until data changes underneath the reader. Offset pagination silently **skips or duplicates rows** when inserts land mid-iteration; cursor pagination survives churn but demands a stable sort key and an opaque token contract. This skill covers both sides: designing the API and consuming one correctly. Deep detail lives in `references/`; copy-paste material in `examples/`; a runnable cursor round-trip check in `scripts/`.

Decision: offset vs cursor

| Situation | Use | |---|---| | Admin tables, small datasets, "jump to page 7" required | Offset (`LIMIT/OFFSET`) | | Feeds, sync endpoints, anything users scroll | Cursor (keyset) | | Data changes while clients iterate | Cursor — offset will skip/duplicate | | Deep pages (offset > ~10k rows) | Cursor — `OFFSET n` scans and discards n rows |

Offset's failure mode is correctness, not just speed: a row inserted before your current position shifts everything, so page 3 re-shows an item from page 2 (duplicate) or swallows one (skip).

Keyset mechanics (the part people get wrong)

The sort key must be **unique and immutable**. `created_at` alone is not unique — two rows with the same timestamp make the cursor ambiguous and rows vanish. Always add a tiebreaker:

-- WHERE clause for "next page after (2026-07-01T12:00:00Z, id 4711)" descending
SELECT * FROM items
WHERE (created_at, id) < ('2026-07-01T12:00:00Z', 4711)
ORDER BY created_at DESC, id DESC
LIMIT 51;  -- page_size + 1 to detect has_more without COUNT(*)

Fetch `page_size + 1` rows: if you get 51, there is a next page and the 51st row's key becomes the next cursor. This avoids `COUNT(*)`, which on large tables costs more than the page itself (see `references/counting-and-totals.md`).

Cursor token contract

Cursors are **opaque to clients** — never let them parse or build one. Encode the keyset values plus the sort direction, base64url it, and (for public APIs) HMAC it so tampering is detectable. Include a version byte so you can change the encoding later. Full recipe: `references/cursor-encoding.md`; runnable round-trip: `scripts/check-cursor.mjs`.

Response envelope

{
  "data": [ ... ],
  "page_info": { "has_more": true, "next_cursor": "djEuMj..." }
}

Rules: `next_cursor` is absent (not empty-string) on the last page; page size is capped server-side (a `limit=10000` request gets the cap, documented, not an error); the default page size appears in the docs and never changes silently. Consumer-side iteration rules — retries, cursor expiry, resumption — live in `references/consuming-pages.md`.

Pitfalls

• **Mutable sort keys** (`updated_at`) — rows teleport across pages as they update. Sort on immutable

columns or accept re-delivery and dedupe client-side.

• **Cursor built from row position** instead of row values — it is offset with extra steps and

inherits every offset bug.

• **`COUNT(*)` on every page** — the count is stale by the time the client renders it; provide totals

on a separate, cacheable endpoint if the product truly needs them.

• **Breaking cursor encoding on deploy** — clients hold cursors across releases; version the token.
• **Missing composite index** — keyset needs an index on exactly `(created_at, id)` in sort order, or

every page is a scan.

Verification

Run `node scripts/check-cursor.mjs` — encodes, decodes, tampers, and versions a cursor and asserts every property above. For the SQL side, `examples/keyset-queries.sql` includes an `EXPLAIN` you can run to confirm the index is used. Pairs with `rest-api-design` (envelopes, limits) and `sql-index-tuning` (composite indexes).