How to Add Pagination to an AI-Built App

Ask an AI builder for a paginated list and you will get LIMIT 20 OFFSET 40. It works, it demos perfectly, and it breaks in a specific way once real data arrives: users skip records and see others twice, without anything appearing to go wrong. Switching to keyset pagination fixes it, and the change is small enough to make before launch.

The reason this is worth a post of its own is that the bug is invisible in testing. Test data does not change while you page through it. Production data does.

Why offset pagination breaks

Offset pagination says "skip the first 40 rows, give me the next 20". The skip is computed against the result set at the moment of the query, and the result set moves.

Walk through it. Your list is sorted newest first, 20 per page.

1. A user loads page 1. They see records 1 to 20.

2. While they read, two new records are created. Everything shifts down by two.

3. The user clicks page 2, which runs OFFSET 20. The rows now at positions 21 and 22 are the ones that were at 19 and 20 a moment ago.

4. The user sees two records they already saw, and never sees the two that got pushed past the boundary.

Deletions do the same thing in reverse, causing records to be skipped entirely. On a busy list the effect is constant, and nobody reports it as a bug because it does not look like one. It looks like the list is a bit odd.

There is a second problem that arrives later. OFFSET 100000 requires the database to count through one hundred thousand rows before discarding them. Offset queries get linearly slower the deeper you go, so page 1 is instant and page 500 times out. If your app has ever felt fine until someone went deep into a list, this is a likely cause. It belongs alongside the other reasons an AI-built app gets slow.

Keyset pagination, which does not

Keyset pagination, also called cursor pagination, asks a different question. Instead of "skip 40 rows", it asks "give me the 20 rows after this specific one".

-- Page 1
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Page 2: pass the last row of page 1 back in
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-09-15 10:30:00', 8814)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Because the boundary is a value rather than a count, inserts and deletes elsewhere in the table cannot shift it. The user gets each record exactly once. And with an index on (created_at DESC, id DESC), the database seeks straight to the boundary instead of counting, so page 500 costs the same as page 2.

Two details that matter and that generated code usually misses:

Always include a tiebreaker. Sorting by created_at alone is not enough, because two rows can share a timestamp, and the boundary comparison then becomes ambiguous. Adding id as a second sort key makes the ordering total. This is the single most common way a hand-rolled keyset implementation goes subtly wrong.

Use row-value comparison. (created_at, id) < (?, ?) is the correct form. Writing it as created_at < ? OR (created_at = ? AND id < ?) is equivalent but easier to get wrong, and most databases optimise the row-value form better. PostgreSQL's row constructor comparison documents the semantics.

The trade you are making

Keyset pagination is not free. It gives up the ability to jump to an arbitrary page.

This is why the right answer depends on the surface.

Use keyset for infinite scroll, feeds, activity logs, notifications, anything ordered by time, and any list that is long or changing. This covers most lists in most apps.

Offset is fine for small, static, admin-facing tables where numbered pages are genuinely useful and the data barely moves. A settings list of 60 rows does not need this.

Both is reasonable on an admin table that needs numbered pages: keyset for the API, offset for the specific screen where a human wants page 12.

Getting your AI builder to produce the right one

Generated code defaults to offset because offset is what almost all tutorial material shows. You have to ask for the alternative by name and specify the details, or you will get offset wearing a cursor's clothes.

Implement keyset (cursor) pagination for the posts list.

- Sort by created_at DESC with id DESC as a tiebreaker.
- Use row-value comparison: WHERE (created_at, id) < (?, ?)
- Add a composite index on (created_at DESC, id DESC).
- The API returns { items, nextCursor }. nextCursor is null on the last page.
- Encode the cursor as base64 of "created_at|id". Decode and validate it
  server side. Reject a malformed cursor with 400, do not fall back to page 1.
- Do not use OFFSET anywhere in this endpoint.

That last line earns its place. Without it, a later edit will frequently reintroduce offset for "the page count feature", and you will have both.

The validation instruction matters too. A cursor is user-supplied input that goes into a query. Decode it, check the shape, and reject what does not parse. Silently falling back to the first page turns an invalid cursor into an infinite loop for any client that keeps following nextCursor.

What to check before you ship

• Make data change while you page. Load page 1, insert a few rows, then go to page 2. With offset you will see repeats. This is the test that catches the bug, and it cannot be automated away with a static fixture.

• Confirm the index exists and is used. Run EXPLAIN on the page 2 query. If you see a sequential scan, the composite index is missing or the sort order does not match it.

• Go deep. Page to the end of a large list and watch the response times. Flat is correct.

• Break the cursor on purpose. Send garbage. You want a 400, not a stack trace and not page 1.

• Check the empty and single-page cases. nextCursor must be null when there is nothing more, or clients will loop.

Pagination interacts with a couple of other things worth getting right at the same time. If the list is searchable, the filter has to be part of the keyset query rather than applied after, which is covered in adding search to an AI-built app. If you are caching list responses, the cursor belongs in the cache key, or users will be served someone else's page. Adding caching goes into that.

And if this list is exposed to other developers, the cursor is now part of your contract. Opaque, base64-encoded cursors are the convention precisely so you can change the underlying sort later without breaking clients. Adding a public API covers the versioning side.

The database you picked also shapes this: composite indexes and row-value comparison are well supported in Postgres and MySQL, and less so elsewhere. Choosing a database for an AI-built app is worth reading before you commit. For the wider build, see how to build an app with AI.

FAQ

What is wrong with LIMIT and OFFSET?

Two things. The offset is computed against a result set that changes, so inserts and deletes cause users to see duplicate records and miss others. And deep offsets are slow, because the database counts through every skipped row.

What is keyset pagination?

Pagination that asks for rows after a specific value rather than after a count. Because the boundary is a value, it stays correct when rows are added or removed elsewhere in the list.

Can I still show page numbers with keyset pagination?

Not directly. Keyset supports next and previous, not arbitrary jumps. If you need numbered pages, use offset on that screen specifically, or run a separate count query and accept its cost.

Do I need an index for pagination?

Yes. A composite index matching your sort order, including the tiebreaker column, is what makes keyset pagination constant-time. Without it you have added complexity for no performance benefit.

Why does my AI builder always use OFFSET?

Because it dominates tutorial material, so it is the statistically likely completion. Ask for keyset pagination by name, specify the tiebreaker and the index, and tell it not to use OFFSET in that endpoint.