Writing Paginated List Endpoints With LLMs in November 2026: Use Cursors Instead of Offsets, Sort on a Unique Key, and Cap the Page Size

Almost every backend has a list endpoint: orders, invoices, comments, audit events. Ask an LLM to write one and you will usually get something that works on day one and quietly breaks at month six. The typical first draft uses LIMIT and OFFSET, sorts on a timestamp that is not unique, and accepts whatever page size the caller sends. None of that shows up in a demo with twelve rows. All of it shows up when the table has two million rows and people are writing to it while others read.

This guide covers how to get a solid paginated endpoint out of an AI coding assistant: what to tell it up front, what to check in the code it gives you, and which tests catch the bugs that matter.

A ribbon bookmark and a paper bookmark in a book

A cursor works like a bookmark: it remembers exactly where you stopped, no matter how many pages get added before it. Image: Ribbon bookmark and a paper bookmark by PJeganathan, CC BY-SA 3.0, via Wikimedia Commons.

Why the default draft goes wrong

Offset pagination is the pattern most common in tutorials, so it is what models reach for. It looks like this:

SELECT id, created_at, total
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 50 OFFSET 1000;

There are three problems hiding in that query:

  • It gets slower the deeper you go. The database still has to walk past the first 1,000 rows to throw them away. On page 2 that is cheap. On page 4,000 it is a full scan of most of the index.
  • Rows shift between requests. If a new order is inserted while a client is paging, every row moves down by one. The client sees the last row of page 1 again as the first row of page 2. If a row is deleted, the client skips one without ever knowing.
  • The order is not fully defined. Two orders created in the same millisecond have no guaranteed order relative to each other. The database is free to return them in a different order on the next query, so a row can appear on two pages or on none.

Offset pagination is fine for small, mostly static admin tables where users want to jump to "page 7". For anything that grows, changes while people read it, or gets crawled by API clients and sync jobs, use cursor pagination (also called keyset pagination).

Tell the model the shape you want before it writes anything

The single most useful thing you can do is state the design in the prompt instead of hoping the model picks it. A prompt that works well:

Write a GET /orders endpoint in [your framework] backed by PostgreSQL.
Requirements:
- Cursor (keyset) pagination, not OFFSET.
- Sort by created_at DESC, then id DESC as a tiebreaker. id is unique.
- The cursor is opaque to clients: base64url-encoded JSON of the last
  row's (created_at, id), plus a version field.
- Query params: limit (default 50, max 200), cursor (optional).
- Reject a limit above 200 or below 1 with a 400, do not silently clamp.
- Reject a malformed cursor with a 400 and a clear message.
- Response: { "items": [...], "next_cursor": string or null }.
- Fetch limit + 1 rows to know whether there is a next page.
- Filter by the authenticated customer; never trust a customer_id param.
Here is the table schema: [paste the real CREATE TABLE and indexes]

Pasting the real schema matters. Without it, the model guesses column names and types, and a guess like "created_at is a string" leads to cursor comparisons that sort "10" before "9".

What correct keyset code looks like

The core of cursor pagination is a row comparison. With PostgreSQL you can compare tuples directly:

SELECT id, created_at, total
FROM orders
WHERE customer_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;   -- pass limit + 1

For the first page, drop the tuple condition. For later pages, $2 and $3 come from the decoded cursor. The direction of the comparison must match the sort: descending sort, less-than comparison. Models sometimes get this backwards, and the result is an endpoint that returns page 1 forever. Check it by reading the query, not by trusting the variable names.

Not every database supports tuple comparison the same way. If yours does not, or if the planner does not use the index for it, the expanded form is equivalent:

WHERE customer_id = $1
  AND (created_at < $2 OR (created_at = $2 AND id < $3))

Then you need an index that matches the filter and the sort, in that order:

CREATE INDEX orders_customer_created_id
  ON orders (customer_id, created_at DESC, id DESC);

Ask the model to include the index in its answer, then run EXPLAIN on the query against a copy of real data. You want to see an index scan with no separate sort step. If you see a sort node over many rows, the index does not match the ORDER BY.

The limit + 1 trick

To know whether a next page exists, fetch one extra row. If you get back limit + 1 rows, drop the last one from the response and build the cursor from the last row you kept. If you get limit or fewer, set next_cursor to null. This avoids a separate COUNT(*) query, which is expensive on large tables and is often not needed at all.

A common model mistake is building the cursor from the extra row instead of the last returned row. That skips one item at every page boundary. It is easy to spot in review: find the line that builds next_cursor and check which row it uses.

Rows of books on a library shelf

Shelving only works when every book has one fixed place in the order. Your sort key needs the same guarantee. Image: Bookshelf in East Community Library at St. Petersburg College by MatthewHoobin, CC0, via Wikimedia Commons.

Make the cursor opaque and validate it

Clients should treat the cursor as a meaningless string they hand back to you. Encoding it (for example, base64url of a small JSON object) keeps clients from building their own cursors and lets you change the format later. Include a version field so a future change can reject or translate old cursors cleanly.

Opaque is not the same as trusted. A cursor comes from the client, so decode it defensively:

  • Reject anything that does not decode, does not parse, or is missing fields. Return a 400, not a 500.
  • Check types: the timestamp must parse as a timestamp, the id must be the type your id column uses.
  • Pass the values as query parameters. Never interpolate them into SQL text. LLM drafts occasionally build the WHERE clause with string formatting when the cursor logic gets complicated; that is an injection hole.
  • Keep authorization in the query. The cursor only says where to resume. It must never carry the customer id or tenant id that decides what the caller may see.

If you are worried about clients tampering with cursors to probe data, you can sign them with an HMAC. For most endpoints that is unnecessary as long as the authorization filter is in the query, because a forged cursor can only move the starting point within data the caller may already see.

Cap the page size, and say so

An endpoint that accepts limit=1000000 is an easy way to take down your own database. Set a default, set a maximum, and document both. Whether you reject or clamp an oversized limit is a design choice; rejecting with a 400 is clearer for API clients because they learn the rule immediately instead of wondering why they got fewer rows than they asked for. Whatever you choose, make the model's code match it, and test it.

Filters and sorting options multiply the work

Once product asks for "sort by total" or "filter by status", each combination needs its own thought:

  • Every sort option needs a unique tiebreaker. Sorting by total alone is not stable; sort by total, id.
  • The cursor must record which sort it was created under. A cursor from a created_at sort is meaningless under a total sort. Store the sort key in the cursor and reject a mismatch.
  • Changing filters should start from page 1. If the request's filters do not match the ones the cursor was created with, reject the cursor or ignore it explicitly.
  • Each sort and filter combination that matters for performance needs a matching index. Limit the options you offer rather than indexing everything.

When you ask the model to add a new sort option, ask it to list the index it requires and the cursor fields it adds. If it cannot name both, the change is not done.

Tests that catch the real bugs

A handful of tests cover most of what goes wrong. Ask the model to write these, then read them to make sure they assert something meaningful:

  1. Walk every page. Insert 237 rows, page through with limit=50, and assert you collected exactly 237 unique ids in the expected order, with the last page having 37 items and a null next_cursor.
  2. Duplicate sort values. Insert 120 rows that all share the same created_at. Page through and assert no id is missing or repeated. This is the test that proves the tiebreaker works.
  3. Exact page boundary. Insert exactly 100 rows with limit=50. The second page must have 50 items and a null cursor, not an empty third page.
  4. Writes during paging. Fetch page 1, insert new rows that sort before it, then fetch page 2 with the cursor. Assert page 2 contains no rows from page 1.
  5. Bad input. A garbage cursor, a cursor with a wrong type, limit=0, and limit=10000 each return a 400.
  6. Wrong user. A cursor obtained by customer A, sent by customer B, returns only B's rows.

Run the boundary and duplicate tests against the real database engine you use in production, not an in-memory substitute. Collation, timestamp precision, and tuple comparison behavior differ between engines, and those differences are exactly where pagination bugs live.

When you still need total counts and page numbers

Some interfaces genuinely need "page 3 of 12". If so, keep in mind that an exact COUNT(*) on a big filtered table can cost more than the page query itself. Options include showing an approximate count, capping the count ("1,000+ results"), caching it briefly, or offering page numbers only on small admin views and cursors everywhere else. Make that decision yourself; a model will add an exact count to every response by default if you let it.

A short review checklist

  • Sort includes a unique tiebreaker column.
  • Cursor comparison direction matches the sort direction.
  • The next cursor is built from the last returned row, not the extra one.
  • Cursor values are decoded defensively and passed as parameters.
  • Authorization filter lives in the query, never in the cursor.
  • Page size has a default and a maximum that are enforced and documented.
  • An index matches the filter plus sort, confirmed with EXPLAIN.
  • Tests cover duplicates, exact boundaries, writes during paging, and bad input.

Pagination is a small piece of code with a long tail of edge cases. An LLM can write it quickly, but only if you hand it the design and then verify the few lines where it usually slips: the sort, the comparison, the cursor row, and the limit.

Comments

Popular posts from this blog

Grok Bot - a step closer to AGI

Tencent Hy4 Preview: Open 770B MoE Built for Real Work

Structured Outputs and Constrained Decoding for Reliable LLM APIs in Production (September 2026)