wowprompt

Cursor-based pagination for a list endpoint and its query

Replaces slow offset pagination on a list endpoint with cursor-based paging: a stable sort, an opaque cursor, the database query and index, the response format and tests for rows added or deleted between pages. Use it when deep pages get slow, or when users see duplicate or missing items while scrolling a feed or an admin list.

Endpoint code with an encoded cursor and a keyset query, the supporting index, a sample response with a next cursor, and a test list for edge cases
The output this prompt produced, unedited.

The prompt

Role

You are a backend engineer who has moved large tables off offset pagination. You care about stable ordering, fast queries at any depth, and cursors clients cannot tamper with to read data they should not see.

Task

Rewrite the list endpoint below to use cursor-based pagination.

Inputs

  • Current endpoint code and query: [paste]
  • Language, framework and database: [e.g. Node.js with PostgreSQL, PHP with MySQL]
  • Default sort and other sorts users can pick: [e.g. newest first, price low to high]
  • Filters on the list: [e.g. category, status, search term]
  • Page size limits: [e.g. default 20, maximum 100]
  • Table size and existing indexes: [rows, index list]

Deliverables

  1. Sort keys that are unique, adding the primary key as a tie-breaker.
  2. Cursor: the last row's sort values, encoded as an opaque string and validated on the way in.
  3. Keyset query: WHERE (created_at, id) < (cursor values) ORDER BY created_at DESC, id DESC LIMIT size + 1, adapted to the database given.
  4. The index that serves each sort and filter combination.
  5. Response shape with items, next cursor and a has-more flag; a previous cursor only if the client needs it.
  6. A crawlable fallback for public pages, if the list is a public page.

Output format

  • Endpoint code and query in one code block, using parameterised queries.
  • The CREATE INDEX statements.
  • A sample request and response.
  • | Test | Setup | Expected |, covering the last page, an empty list, rows inserted between requests, a deleted cursor row and a tampered cursor.

Quality rules

  • Never build SQL by concatenating cursor values; bind every value.
  • A cursor must still respect the user's permissions and filters; never trust it as proof of access.
  • Do not promise a total count on every request if it needs a full table scan; offer it separately.
cursor paginationkeyset paginationapi paginationoffset pagination performanceinfinite scroll apidatabase index

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity