| name | cursor-pagination-design |
| description | Playbook for designing cursor (keyset) pagination on list endpoints: cursor encoding, response envelope, query parameter contract, and the migration path away from offset pagination. |
Cursor Pagination Design
When to Use This Skill
Use whenever you are building or reviewing a list endpoint. Default to cursor (keyset) pagination. Use offset/page only when: the dataset is static, or the consumer requires random-access page-jumping (e.g. a UI with a page-number picker on a dataset guaranteed < 10 000 rows). Document the trade explicitly when you choose offset.
Why Cursor Beats Offset
| Concern | Offset/Page | Cursor (Keyset) |
|---|
| Deep-page performance | Full OFFSET N scan — degrades O(N) | WHERE id > cursor — index seek, O(1) |
| Consistency during mutations | Rows inserted/deleted shift pages → duplicates/skips | Cursor anchored to a row; inserts/deletes after it are invisible |
| Real-time / high-write tables | Unreliable | Stable |
| Random-access (jump to page 42) | Supported | Not supported — use if required |
Response Envelope
{
"data": [ { "id": "u_01", "name": "Alice" }, "..." ],
"pagination": {
"nextCursor": "eyJpZCI6InVfMTAwIn0",
"hasMore": true,
"pageSize": 50
}
}
| Field | Type | Notes |
|---|
data | array | The page of results. |
pagination.nextCursor | string|null | Opaque base64-encoded cursor. null when no more pages. |
pagination.hasMore | boolean | Convenience flag (derived from nextCursor != null). |
pagination.pageSize | integer | Actual count returned (≤ requested limit). |
Do not expose total by default — a COUNT(*) on large tables is expensive. Provide it as an opt-in (?includeTotalCount=true) only when UX demands it.
Query Parameter Contract
GET /users?limit=50&cursor=eyJpZCI6InVfMTAwIn0&sort=createdAt_asc
| Parameter | Default | Constraint |
|---|
limit | 20 | Server enforces max (e.g. 100); reject > max with 422 + Problem Details |
cursor | omitted (first page) | Opaque — treat as a black box; validate signature before use |
sort | stable default (e.g. id_asc) | Only supported sort expressions; reject unknown with 422 |
Cursor Encoding
The cursor encodes the last-seen keyset values (not a row offset). Common strategies:
- Single-key:
{ "id": "u_100" } → base64url → URL-safe string.
- Composite sort:
{ "createdAt": "2026-01-15T10:00:00Z", "id": "u_100" } — include the unique tiebreaker (id) to handle duplicate sort-key values.
- Signed cursor: HMAC-sign the JSON payload to prevent cursor manipulation (prevents a client from crafting cursors that bypass authorization scoping).
import base64, json, hmac, hashlib
def encode_cursor(payload: dict, secret: bytes) -> str:
raw = json.dumps(payload, separators=(",", ":")).encode()
sig = hmac.new(secret, raw, hashlib.sha256).hexdigest()[:16]
return base64.urlsafe_b64encode(raw + b"." + sig.encode()).decode().rstrip("=")
SQL Query Pattern
SELECT * FROM users
WHERE (created_at, id) > (:cursorCreatedAt, :cursorId)
ORDER BY created_at ASC, id ASC
LIMIT :limit + 1;
Fetch limit + 1 rows. If you get limit + 1 back, hasMore = true; return only limit rows and encode the last returned row's keyset as nextCursor.
Pitfalls
- Exposing the cursor as a raw unencoded offset or row ID — reveals internal keys and allows parameter manipulation.
- Using
OFFSET behind a "cursor" parameter — defeats the purpose; degrades on deep pages.
- Missing the unique tiebreaker in a composite sort — causes duplicate or missing rows when sort-key values collide.
- Accepting any caller-supplied cursor without validation — enables injection / authorization bypass; sign cursors.
- Omitting
limit enforcement server-side — unbounded queries are a DoS and a cost vector (OWASP API4).
- Not advertising
pageSize in the response — consumers can't detect partial pages or truncation.
See Also
