Pagination Cursor Designer

---
name: pagination-cursor-designer
description: 'Design pagination for list endpoints and UIs: choose cursor/keyset or offset with drift and deep-page costs stated, define a versioned integrity-protected cursor holding sort keys, direction and sort/filter fingerprint, require deterministic ordering and a concurrency stability policy, set page-size bounds and honest end signaling, and apply tenant/permission scope server-side from trusted context on every page. Encoded cursor keys are decodable; encrypt if confidential. Choose load-more, numbered, or infinite-scroll UI with tradeoffs. Owns pagination mechanics within the endpoint contract owned by api-event-architect. Use for pagination design or skip/repeat bugs. Do NOT design routes/rate limits (api-event-architect) or tune one query plan (query-plan-reader).'
---

# Pagination Cursor Designer

## Purpose

Pagination breaks in ways a single request never shows: a row is
inserted while a user reads page two, and page three now repeats a row
or skips one; someone deep-links to `?page=900` and the database scans
nine hundred pages to serve one; a client stores `nextCursor` and it
silently starts returning another tenant's rows because the tenant
filter was not reapplied from trusted context on every page. This skill produces the pagination
design for a list surface — the cursor's contents and stability
contract, the total ordering that makes "next" deterministic, page-size
bounds, honest end-of-results signaling, and the tenant/permission
predicate applied server-side on every page — plus the surface pattern
(load-more, numbered pages, infinite scroll) with its tradeoffs. It owns
the pagination MECHANISM; the route shape, versioning, and rate limits
of the endpoint it rides on belong to `api-event-architect`.

## Use When

- Use when: designing pagination for a new list/collection endpoint or
  its user interface (UI), or choosing between cursor and offset paging.
- Use when: a list skips, repeats, or reorders rows as underlying data
  changes (the classic offset-drift bug), or deep pagination is slow.
- Use when: adding "load more", infinite scroll, or numbered pages and
  the traversal semantics (stability, end signaling) need pinning.
- Use when: an existing cursor leaks internal ids/offsets, breaks when
  the sort changes, or lets a client page across a tenant boundary.
- Do NOT use when: the task is the API contract itself — resource
  routes, versioning/deprecation, envelopes, rate limits — that is
  `api-event-architect`; this skill designs the pagination inside it.
- Do NOT use when: one specific query is slow and needs plan analysis
  (index use and sargability, meaning whether a filter can use an index) —
  that is `query-plan-reader` *(manual-only)*; this skill decides
  the pagination model, then hands the keyset query to it if needed.
- Do NOT use when: the question is whether to cache list responses —
  that is `caching-strategy-designer` (a paginated list's cache key
  must include the cursor, which this skill defines).

## Inputs to Inspect

1. The list's access pattern: expected result-set sizes, how deep users
   actually page (most stop at page 1–2; some export everything), and
   whether the client needs random access to page N or only sequential.
2. Write concurrency on the collection: insert/delete rate relative to
   read, which determines how badly offset paging drifts.
3. The sort/filter surface: every sortable column and filter the client
   can set — the cursor must encode enough to resume any of them, and
   changing the sort invalidates an existing cursor.
4. The tenant/authorization model (from `multi-tenant-data-architect` /
   `authorization-matrix-designer` outputs if present): the predicate
   that scopes rows to the caller, which MUST be part of the query, not
   reconstructed from a client-supplied cursor.
5. Any existing pagination in the codebase and its bugs: offset drift
   reports, `page`/`limit` params, cursor formats already exposed to
   clients (a live contract that constrains changes).

## Workflow

When the human must choose a count strategy or page experience, define the
terms; explain each viable option's purpose, user benefit, drawback, and
money/setup/maintenance cost or uncertainty. Recommend the fit from list size,
churn, and user need before asking one decision question. Keep factual access-
pattern discovery and authorization for contract changes separate.

1. **Choose the model against the access pattern.** Offset/limit is
   acceptable ONLY for small, low-churn, human-browsed lists where
   random page access matters and drift is tolerable — state that
   ceiling. Default to cursor (keyset) paging for anything large,
   high-churn, or deep: it avoids offset-shift drift and its cost per page
   stays roughly proportional to the page size rather than the page depth,
   but mutable sort keys and deletes still need a snapshot or
   explicit stability policy.
   Record why, per surface.
2. **Define the total ordering.** Keyset paging requires a strict, total
   order — pick the sort column(s) PLUS a deterministic tiebreaker
   (commonly the primary key) so no two rows compare equal. Without the
   tiebreaker, rows sharing a sort value straddle a page edge and
   repeat/vanish. State the exact ORDER BY.
3. **Specify the cursor contents and encoding.** The cursor carries the
   last row's ordering tuple (sort value(s) + tiebreaker) and the
   direction, deterministic tiebreaker, and versioned fingerprint of the
   normalized sort/filter query. Treat it as an opaque client
   contract, not a secret: Base64 is decodable. Version and integrity-protect
   the payload; encrypt if ordering keys are confidential. A primary key may
   be a tiebreaker if its visibility is acceptable; otherwise use a suitable
   surrogate. Verify the fingerprint against the current query and reject a
   changed sort/filter. Never trust tenant scope from the cursor or expose a
   raw Structured Query Language (SQL) offset.
4. **Bind tenant/permission scope into the query, not the cursor.** The
   authorization predicate is applied server-side on every page from
   trusted context (session/token), never trusted from cursor contents.
   A tampered or stale cursor must at worst return the caller's own rows
   or an error — never another scope's. This is a correctness boundary.
5. **Bound page size.** Set a sane default and a hard maximum; reject or
   clamp over-max requests. Unbounded `limit` is a denial-of-wallet and
   memory risk — cross-reference `ai-cost-guardrail-designer` /
   rate-limit policy only as the enforcement home, not the value.
6. **Signal boundaries honestly.** Return the next cursor (null/absent at
   the end) and, for bidirectional UIs, a previous cursor. Do NOT return
   a total count unless it is cheap and correct — an approximate or
   expensive `COUNT(*)` on every page is a common performance trap; if a
   total is truly needed, price it and consider a separate estimate.
7. **Handle sort/filter changes and edge cases.** Define behavior when
   the client changes sort or filter mid-traversal (the old cursor is
   invalid — reset to first page, state this), when a cursor points at a
   now-deleted row (resume from the next row in order), and empty/first/
   last pages.
8. **Pick the surface pattern.** Map the model to the user experience: "load more"/
   infinite scroll pairs naturally with cursors; numbered pages imply
   offsets or a page→cursor map and random access (call out the cost);
   pick per surface and state the tradeoff. Note deep-link/back-button
   behavior for infinite scroll.
9. **Deliver** the design in the Output Format with the exact ordering,
   cursor schema, size bounds, and the tenant-scope guarantee.

Decision tables (cursor vs offset, tiebreaker selection, total-count
strategies) and a worked keyset query with a versioned cursor struct:
[references/pagination-design-sheet.md](references/pagination-design-sheet.md).

## Output Format

```
PAGINATION DESIGN — <surface/endpoint>
Model:        cursor (keyset) | offset  — rationale + access pattern
Ordering:     ORDER BY <sort cols>, <tiebreaker> <dir>   (strict total order)
Cursor:       versioned, integrity-protected token of { v, keys[], dir }; encrypt confidential keys
Page size:    default=<n> max=<n>  (over-max: clamp|reject)
Scope:        auth/tenant predicate applied server-side every page (NOT from cursor)
End signal:   nextCursor null at end; prevCursor=<...>; total=<none|estimate|exact+cost>
Sort change:  cursor invalidated → reset to first page (stated to client)
Deleted-row:  resume from next row in order
Surface:      load-more | infinite | numbered — tradeoff noted
User choice (if needed): <explained count/page options, costs/unknowns, pros/cons, recommendation + why>
Boundaries:   contract/routes/rate-limits → api-event-architect; keyset query tuning → query-plan-reader
```

## Validation Checklist

- [ ] Any count or page choice put to the human explains terms, costs, and
      tradeoffs before one question; the answer alone changes no API contract.

- [ ] The ORDER BY is a strict total order — a deterministic tiebreaker
      is present, so no two rows tie.
- [ ] The cursor is versioned and integrity-protected. Encoding is not
      confidentiality; keys and any ID tiebreaker meet the visibility rule,
      or the payload is encrypted. No raw offset/position is exposed.
- [ ] The tenant/authorization predicate is applied server-side on every
      page from trusted context — a tampered cursor cannot cross scope.
- [ ] Page size has a default AND an enforced maximum.
- [ ] End-of-results is signaled by an absent/null next cursor; any total
      count is justified as cheap-and-correct or omitted.
- [ ] Behavior is defined for sort/filter change mid-traversal and for a
      cursor pointing at a deleted row.
- [ ] The surface pattern is chosen with its random-access/deep-link
      tradeoff stated.
- [ ] Endpoint contract concerns are handed to `api-event-architect`, not
      redesigned here.

## Gotchas

- Offset paging over changing data is not "mostly fine" — a single
  insert above the current offset shifts every subsequent row by one,
  duplicating one and hiding one on the next page. It is a correctness
  bug, not a nicety.
- A cursor without a tiebreaker breaks precisely on the rows users care
  about — the burst created at the same second, the batch imported with
  one timestamp. Equal sort values need a deterministic unique tiebreaker;
  a primary key is suitable only when its exposure policy allows it.
- `COUNT(*)` for "page X of Y" is often the most expensive part of a
  list endpoint and gets re-run on every page. Decide the total-count
  cost deliberately; frequently the honest answer is "no total".
- Trusting scope from the cursor is a tenant-isolation hole: if the
  cursor says `tenant=7` and the server believes it, a forged cursor
  reads tenant 7. Scope always comes from the authenticated context.
- Base64 encoding is reversible: signing prevents tampering, not disclosure.
  Use encryption when cursor keys must remain confidential, and never treat
  a decodable internal ID as hidden merely because it is encoded.
- Infinite scroll destroys deep-linking and the back button unless the
  cursor/position is reflected in URL state — design that, or users lose
  their place.
- Changing the default sort silently invalidates every stored cursor
  clients hold; treat cursor format and default ordering as part of the
  endpoint's compatibility surface (coordinate with `api-event-architect`).

## Stop Conditions

- The authorization/tenant predicate that scopes the list is undefined
  or ambiguous → stop and obtain it from `authorization-matrix-designer`
  / `multi-tenant-data-architect` outputs or a human before fixing the
  cursor; guessing here builds a cross-tenant paging hole.
- The request is really to design the endpoint contract (routes,
  versioning, envelope, rate limits) → route to `api-event-architect`;
  this skill designs only the pagination inside that contract.
- A required exact total count on a huge, high-churn collection is
  mandated despite its cost → surface the performance tradeoff and the
  explained alternatives (estimate, separate count endpoint) and a reasoned
  recommendation to a human rather than baking `COUNT(*)` into every page.
- Changing an already-published cursor format would break live clients →
  flag it as a contract-breaking change for `api-event-architect`'s
  versioning/deprecation process; do not silently alter the format.

## Supporting Files

- [references/pagination-design-sheet.md](references/pagination-design-sheet.md)
  — cursor-vs-offset decision table, tiebreaker selection, total-count
  strategies, and a worked keyset query with a versioned cursor struct.
- `evals/evals.json` — behavior cases including offset-drift diagnosis,
  the tenant-scope-in-cursor refusal, and the total-count trap.
- `evals/trigger-evals.json` — discrimination against `api-event-architect`
  (the contract), `query-plan-reader`, and `caching-strategy-designer`.