API Pagination skill for Codex

Pagination is correct when walking every page returns every matching row exactly once, even while rows are inserted or deleted, and when page 10,000 is as fast as page 1. That requires a total, deterministic order and a cursor that encodes the position, not a row count.

Choose the method

Need	Method
Feeds, timelines, exports, sync, infinite scroll, any large or growing table	Keyset (cursor) pagination - the default
Admin tables needing “jump to page 37” on small, bounded data (< ~10k rows)	Offset/limit with a total count
Incremental sync of changes since last run	Keyset on (updated_at, id) plus a stored high-water mark
Search results from a search engine	The engine’s own mechanism (search_after, scroll/PIT), not SQL offsets

Offset pagination skips or repeats rows when data changes between requests, and the database still reads and discards every skipped row (OFFSET 100000 reads 100,000 rows).

Workflow

- [ ] 1. Read the endpoint, its query and existing conventions
- [ ] 2. Define a total order with a unique tiebreaker
- [ ] 3. Write the keyset query and supporting index
- [ ] 4. Encode an opaque cursor bound to sort and filters
- [ ] 5. Shape the response and limits
- [ ] 6. Test page walks under concurrent writes

1. Read first

Check AGENTS.md and existing list endpoints for the response shape (next_cursor, page_token, Link header, Relay connections) and parameter names. New endpoints must match the existing convention.

2. Total order

• Every sort must end in a unique column: ORDER BY created_at DESC, id DESC. created_at alone ties on bulk inserts and causes skipped or duplicated items.
• Each user-selectable sort maps to a whitelisted column list with the tiebreaker appended server-side.
• Sort columns must be non-null, or the query must handle nulls explicitly (NULLS LAST plus COALESCE in the comparison); nullable sort keys are the most common keyset bug.

3. Keyset query and index

PostgreSQL (row-value comparison uses the index when all columns sort the same direction):

SELECT id, created_at, status, total
FROM orders
WHERE account_id = $1
  AND status = ANY($2)
  AND (created_at, id) < ($3, $4)      -- cursor position; omit on first page
ORDER BY created_at DESC, id DESC
LIMIT $5 + 1;                           -- fetch one extra to know if more exist

CREATE INDEX CONCURRENTLY orders_account_created_id
  ON orders (account_id, created_at DESC, id DESC);

Mixed directions (ORDER BY priority DESC, id ASC) cannot use a single row comparison; expand it:

WHERE (priority < $1) OR (priority = $1 AND id > $2)

On MySQL, prefer the expanded form as well and confirm with EXPLAIN that it is a range scan on the composite index. ORMs: Prisma’s cursor option and Django’s CursorPagination (DRF) implement keyset; check the generated SQL includes the tiebreaker.

4. Cursor encoding

• Cursors are opaque to clients: base64url of a small JSON object holding the last row’s sort values, sort key, direction and a hash of the filters.
• Reject a cursor whose filter hash or sort does not match the current request with 400, otherwise a client changing filters mid-walk gets nonsense.
• If cursors must not be tampered with (they contain tenant-scoped values), sign them with an HMAC. Never put raw SQL fragments or offsets in a cursor.

import { createHmac, timingSafeEqual } from 'node:crypto';

type Cursor = { v: [string, string]; s: string; f: string }; // values, sort, filter hash

export function encodeCursor(c: Cursor, key: Buffer): string {
  const body = Buffer.from(JSON.stringify(c)).toString('base64url');
  const mac = createHmac('sha256', key).update(body).digest('base64url').slice(0, 16);
  return `${body}.${mac}`;
}

export function decodeCursor(raw: string, key: Buffer): Cursor | null {
  const [body, mac] = raw.split('.');
  if (!body || !mac) return null;
  const expected = createHmac('sha256', key).update(body).digest('base64url').slice(0, 16);
  if (mac.length !== expected.length || !timingSafeEqual(Buffer.from(mac), Buffer.from(expected))) return null;
  try { return JSON.parse(Buffer.from(body, 'base64url').toString()); } catch { return null; }
}

Serialize timestamps at full database precision (microseconds in Postgres). Truncating to milliseconds in the cursor skips rows created in the same millisecond.

5. Response shape and limits

{
  "data": [ { "id": "ord_9" }, { "id": "ord_8" } ],
  "next_cursor": "eyJ2IjpbIjIwMjYtMDMtMDFUMTI6MDA6MDAuMTIzNDU2WiIsIm9yZF84Il0...",
  "has_more": true
}

• limit default 20-50, hard maximum 100-200; clamp or reject above the maximum (document which).
• has_more comes from the extra row fetched with LIMIT n + 1, not from a count.
• Omit next_cursor (or return null) on the last page. Never return a cursor that leads to an empty page if you can avoid it.
• Backward pagination (prev_cursor) reverses the comparison and order, then reverses the result in memory. Add it only if a UI needs it.
• Link headers (RFC 8288) with rel="next" are a good addition for HTTP clients and CLIs; keep them in sync with the body.
• Total counts: make them opt-in (?include=total_count), cache them, or return an estimate. COUNT(*) on large filtered tables often costs more than the page itself.

6. Test under change

Write a test that:

1. Seeds rows with identical created_at values to exercise the tiebreaker.
2. Walks all pages with a small limit, inserting and deleting rows between requests.
3. Asserts no ID appears twice and every pre-existing, undeleted ID appears once.
4. Asserts a tampered cursor and a cursor reused with different filters return 400.

Gotchas

• ORDER BY on a non-unique column is the root cause of almost every “item shows up on two pages” report.
• Cursors built from updated_at miss rows updated during the walk unless the sync logic expects to re-read them; document the guarantee.
• Applying permissions or soft-delete filters after LIMIT returns short pages and wrong has_more; filter in SQL.
• Cursors that never expire break when you change the sort or schema; include a cursor version field so old ones can be rejected cleanly.
• Exposing offset-based page= for public APIs invites scrapers to request deep pages that hit your slowest queries.

Verification

• EXPLAIN (ANALYZE, BUFFERS) on the first page and on a deep cursor shows an index scan with similar timings; no Sort node over the full filtered set.
• The page-walk test above passes.
• curl the endpoint with limit above the maximum and with a corrupted cursor and confirm the documented behavior.

Output template

## Pagination for GET <path>
- Method: keyset on (<cols>) <direction>, tiebreaker <col>
- Index: <definition>
- Cursor: <fields, signed?, version>
- Limits: default <n>, max <n>
- Total count: <none | opt-in | estimate>
- Tests: page walk under concurrent writes, tampered cursor, filter mismatch