Pagination and Filtering skill for Claude Code

A list endpoint is done well when every page costs about the same no matter how deep the client goes, paging through while rows are inserted never repeats or skips a row, the order is total and documented, every filter and sort the API accepts is backed by an index, page size is capped, and counts are only as exact as the product actually needs.

Ground rules

• Default to keyset (cursor) pagination. Use offset only for small, bounded lists where users jump to page N (admin tables under roughly 10k rows).
• The sort must be total: end every ORDER BY with a unique column (id). Without a tiebreaker, rows sharing a timestamp come back in arbitrary order across pages.
• Cursors are opaque to clients. Clients pass them back unchanged; the server may change the format at any time.
• Every list has a default and a maximum page size, including internal endpoints.
• Allowed filters and sorts are a whitelist, each with an index. Unknown parameters get 400 on new APIs, so a typo does not silently return everything.
• Match the existing API’s conventions (parameter names, envelope) before introducing new ones; consistency beats the ideal shape.

Offset vs keyset

	Offset (LIMIT 50 OFFSET 5000)	Keyset (WHERE (created_at, id) < (...))
Cost of page N	Reads and discards N x size rows; grows linearly	Constant: index seek + limit
Concurrent inserts/deletes	Duplicates and skips across pages	No duplicates or skips for rows that existed
Jump to arbitrary page	Yes	No (only next/previous, or a jump to a known key such as a date)
Total pages	Needs a count	Not needed
Use for	Small admin tables, reports with page numbers	Feeds, APIs, infinite scroll, exports, anything that grows

Workflow

- [ ] 1. Inspect: table size, existing indexes, current params and envelope
- [ ] 2. Define the sort options and a unique tiebreaker for each
- [ ] 3. Define filters and their syntax (whitelist)
- [ ] 4. Create one index per (equality filters + sort) shape that the API allows
- [ ] 5. Implement keyset queries and cursor encoding
- [ ] 6. Decide what count, if any, to return
- [ ] 7. Prove it: EXPLAIN, paging-under-inserts test, limit enforcement

1. Inspect

SELECT reltuples::bigint FROM pg_class WHERE oid = 'public.orders'::regclass;  -- row estimate
SELECT indexdef FROM pg_indexes WHERE tablename = 'orders';

Read two existing list handlers to learn the envelope and parameter style.

2-3. Request and response shape

GET /v1/orders?status=paid,refunded&createdAt[gte]=2026-10-01T00:00:00Z&sort=-createdAt&limit=50
GET /v1/orders?limit=50&cursor=eyJ2IjoxLCJrIjpb...

200 OK
{
  "data": [ ... ],
  "nextCursor": "eyJ2IjoxLCJrIjpb...",   // null on the last page
  "hasMore": true
}

Syntax defaults (pick once, apply everywhere):

Need	Syntax	Notes
Equality	?status=paid
Any of	?status=paid,refunded	Cap list length (e.g. 50 values)
Range	?createdAt[gte]=...&createdAt[lt]=...	Operators: eq, gt, gte, lt, lte; half-open ranges for time
Sort	?sort=-createdAt (minus = descending), comma for multiple	Server appends id as the tiebreaker silently
Text search	?q=...	Routed to full-text or a search engine, not LIKE '%x%'
Page size	?limit=50	Default 20-50; max 100 (larger only for tiny rows); document clamp vs 400

When a cursor is present, the sort and filters come from the cursor. If the client also sends different filters or sort, return 400 instead of mixing them.

Bulk export is not pagination: offer an async export job for “give me everything”.

4. Index per shape

Order index columns as: equality filters, then sort columns in sort order, then the tiebreaker.

-- serves: tenant_id = ? [AND status = ?] ORDER BY created_at DESC, id DESC
CREATE INDEX CONCURRENTLY orders_tenant_status_created_id
  ON orders (tenant_id, status, created_at, id);

Each user-selectable sort needs its own index shape. Five sort options times three filter combinations is fifteen indexes, which hurts writes; limit the sort options instead. For index design details see postgres-indexing.

5. Keyset query and cursor

-- first page (fetch limit + 1 to know if there is more)
SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1 AND status = ANY($2)
ORDER BY created_at DESC, id DESC
LIMIT 51;

-- next page, cursor decoded to (created_at, id) of the last row returned
SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1 AND status = ANY($2)
  AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 51;

Handler logic:

rows = fetch(limit + 1)
has_more = len(rows) > limit
page = rows[:limit]
next_cursor = encode_cursor({
    "v": 1,
    "k": [page[-1].created_at.isoformat(), page[-1].id],   # full precision
    "s": sort_spec,
    "f": filters_hash,                                     # reject if filters change
}) if has_more else None

Cursor rules:

• Contents: version, last row’s sort-key values and id, sort spec, a hash of the filters. Encode as base64url JSON and sign with an HMAC so clients cannot forge keys that bypass filters (tenant scoping must still come from auth, never the cursor).
• Keep timestamps at database precision. Millisecond truncation of microsecond timestamps skips or repeats rows.
• Invalid or tampered cursor -> 400 with a problem document, not a 500.

MySQL needs the expanded OR form for full index use; mixed sort directions, nullable sort columns, previous-page queries, mutable sort keys, count estimates and complete Python and TypeScript cursor helpers are in references/keyset-sql.md. Read it when the sort is not a simple same-direction pair or when the database is not PostgreSQL.

ORM cursor helpers often accept only one unique column. For compound keys write the WHERE clause explicitly rather than paging by id alone and sorting by something else.

6. Counts

COUNT(*) over a filtered large table scans every matching row (PostgreSQL has no cached count under MVCC). Pick the cheapest option the UI accepts:

Need	Return
“Is there more?”	hasMore only (default)
A rough size	Estimate from the planner, labeled totalEstimate
“1,000+ results”	Capped count: count(*) over a subquery with LIMIT 1001
Exact total for small sets	Exact count, opt-in (?include=total), with a timeout
Exact totals on huge sets	Maintained counter table or materialized aggregate, updated by triggers or jobs

Never compute an exact count on every page request by default.

7. Concurrency semantics (document them)

• Keyset with an immutable key: rows inserted after the client’s cursor position (for a DESC feed, newer rows) are not seen in this pass; rows that existed are each seen once. To show new items at the top, poll separately for rows newer than the first cursor.
• Deleted rows simply disappear; no shift.
• Offset: an insert before the current page shifts everything, so the next page repeats a row; a delete skips one.
• Sorting by a mutable column (updated_at, score) can move rows across the cursor. Use immutable keys for listing, or freeze a snapshot bound in the cursor.

GraphQL

Use the Relay connection shape: first/after and last/before, edges { node cursor }, and pageInfo { hasNextPage hasPreviousPage startCursor endCursor } (PageInfo and both booleans non-null; start and end cursors nullable). Back it with the same keyset query and cursor encoding; each edge cursor encodes that row’s key. Make totalCount nullable and resolve it only when selected, with the count rules above. For schema concerns see graphql-schema.

Gotchas

• ORDER BY created_at alone: ties at the same timestamp reorder between queries.
• Sorting in application memory after LIMIT: the page is sorted, the dataset is not.
• OFFSET used for “infinite scroll” over a table that grows; deep pages time out and duplicates appear.
• Filters applied after pagination (in code), returning pages with 3 of 50 rows.
• Case-insensitive sort (ORDER BY lower(name)) without an expression index on lower(name); and collation differences between the database and the cursor values.
• NULL in a sort column making rows vanish from keyset comparisons.
• limit=100000 accepted because the max was only enforced in the UI.
• Cursors that embed raw SQL fragments or column names chosen by the client.

Verification

1. EXPLAIN (ANALYZE, BUFFERS) for page 1 and a deep page: same plan, an Index Scan (or Index Only Scan) on the intended index, no Sort node over many rows, rows read close to limit + 1, similar execution time.
2. Paging test: seed rows with duplicate timestamps, page through with a small limit while another connection inserts and deletes; assert no duplicates and that every row present for the whole run appears exactly once, in order.
3. Limit test: limit=0, limit=-1, limit=10000, non-numeric -> documented clamp or 400.
4. Cursor test: tampered cursor -> 400; cursor reused with different filters -> 400.

Output contract

## Endpoint: GET /v1/<resource>
- Pagination: keyset | offset (why), default/max limit, clamp or 400
- Sorts: <list> -> tiebreaker id, index per sort
- Filters: <param: operators> -> index
- Count: none | estimate | capped | opt-in exact
- Cursor: fields, signing, rejection rules
- Concurrency semantics (one paragraph for API docs)
## EXPLAIN evidence (page 1 vs deep page)
## Tests: paging-under-writes, limits, cursor tampering