What this prompt does
This prompt directs the AI, as a senior API engineer, to replace offset pagination with cursor-based pagination and specify it tightly enough to build - returning concrete request and response shapes, not hand-waving. You supply the [endpoint], the [sort_order], the [page_size], and the [datastore], and it returns annotated request/response JSON for the first, next, and last pages plus the index DDL.
The deliverables are sequenced to cover every place offset pagination fails. It defines an opaque base64 cursor built from the sort key plus a tie-break id and explains why a composite key is required, guarantees order stability under concurrent inserts, updates, and deletes, generates next/prev links with a clear has_more signal, handles total-count tradeoffs, lays out a migration plan so existing offset clients keep working during rollout, and specifies the index on [datastore] to keep each page O(log n). The [sort_order] and tie-break together are what make cursors stable; [datastore] decides the exact index DDL.
When to use it
- A list endpoint gets slower on every page because offset pagination scans and discards rows.
- Rows are duplicated or skipped when someone writes to the collection mid-scroll.
- You need stable ordering on a high-write collection under concurrent inserts and deletes.
- You are migrating an existing API and must keep offset clients working during the transition.
- You need the exact index to keep each page fast on your specific database.
Example output
You get annotated request and response JSON for three cases - first page, next page, and last page - showing the opaque cursor, the next/prev links, and the has_more or end-of-list signal. Alongside that, you get the index DDL for [datastore] and a migration plan describing dual support for offset clients and a deprecation window.
Pro tips
- Nail order stability (deliverable two) first; ordering bugs that skip or duplicate rows are the ones that reach support tickets.
- Set
[sort_order]to a field with a reliable secondary tie-break, because a non-unique sort key alone produces unstable cursors. - Match
[datastore]to your real database and version so the index DDL is directly usable. - Choose
[page_size]deliberately; larger pages reduce round-trips but increase per-request cost and memory. - Decide total-count handling up front - exact counts are expensive on large collections, so estimated or omitted is often the right call.
- Keep the offset endpoints alive through the migration window; cutting them over instantly breaks any client mid-scroll.
- Make the cursor opaque base64 so clients treat it as a token and cannot construct their own, which keeps you free to change its internals later.
- Watch the prev-direction case as carefully as next; reverse pagination has its own off-by-one and ordering pitfalls that are easy to miss in a quick test.
