# Switch a Fastify API From Offset to Cursor Pagination

Yes. An agent adds keyset cursor pagination to your Fastify list route, keeps page working for now, indexes the sort columns, tests it and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-cursor-pagination/fastify/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads the list route and its repository first. In a Fastify API like `fastify/demo`, `package.json` pins `fastify` 5 with `@fastify/type-provider-typebox`, `typebox`, `knex` on `mysql2`, `postgrator` migrations under `migrations/`, `@fastify/swagger` with its UI, and runs tests with `node --test` through `tsx` over `test/**/*.ts` with `c8` coverage after `npm run db:seed`. `src/routes/api/tasks/index.ts` serves `GET /` with `querystring: QueryTaskPaginationSchema` and `response: { 200: TaskPaginationResultSchema }`, and returns `tasksRepository.paginate(request.query)`. In `src/schemas/tasks.ts`, that query schema is `page` from 1 with a default of 1, `limit` from 1 to 100 with a default of 10, optional `author_id`, `assigned_user_id` and `status` filters, and `order` as `asc` or `desc` defaulting to `desc`; the result is `{ total, tasks }`. `migrations/002.do.tasks.sql` creates `tasks` with an `INT AUTO_INCREMENT` id, `created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP` and `updated_at` that updates itself.

`src/plugins/app/tasks/tasks-repository.ts` is where the offset lives. `paginate` computes `offset = (q.page - 1) * q.limit`, selects `*` with `count(*) OVER() as total`, applies the three filters, then `.limit().offset()` and `.orderBy([{ column: 'created_at', order }, { column: 'id', order }])`, with a comment explaining that `created_at` stores whole seconds so tasks created in the same second tie and `id` breaks the tie. That ordered pair is exactly a keyset: the agent keeps it and makes the cursor an opaque base64url encoding of the last row's `created_at` and `id`. A request with `cursor` adds `where (created_at, id) < (?, ?)` for `desc` or `>` for `asc`, which MySQL evaluates as a row comparison, skips the `count(*) OVER()` and the offset, fetches `limit + 1` rows, and returns `next_cursor` only when the extra row exists. A request with `page` and no `cursor` goes through the old path unchanged, so existing clients keep working for a release.

The schema and docs follow. `QueryTaskPaginationSchema` gains an optional `cursor` string with a pattern for base64url, `page` becomes optional with no default, and the route rejects a request that sends both with `reply.badRequest`, which `@fastify/sensible` provides. `TaskPaginationResultSchema` gains an optional `next_cursor`, and `total` becomes optional and is present only on the offset path, because a window count over a keyset query is the thing cursors exist to avoid. Because the schemas feed `@fastify/swagger`, the generated OpenAPI picks up the new parameter and field with their descriptions. A new pair of migrations, `006.do.tasks_created_at_id.sql` and its `undo`, adds a composite index on `(created_at, id)` so the keyset predicate and the sort use one index; the filters on `author_id` and `status` stay as they are.

Tests go in `test/routes/api/tasks/tasks.test.ts` beside the existing cases, using `injectWithLogin` from `test/helper.ts`, which logs in with the seeded password through `/api/auth/login` and sets the session cookie. The new cases walk the whole seeded table by `next_cursor` with a small `limit` and assert no duplicate and no missing `id`, insert a task mid-walk and show the walk is unaffected, check that `asc` reverses the pages, that a malformed cursor is a 400 from the schema, and that `page` still answers with `total`. The agent runs what `.github/workflows/ci.yml` runs with its MySQL 8.4 service: `npm run build -- --noEmit`, `npm run lint`, `npm run db:migrate` and `npm run test` with `RATE_LIMIT_MAX=4` and `CAN_SEED_DATABASE=1`, after starting MySQL in its sandbox with the same user and database the workflow names.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Switch the tasks list route in this Fastify API from offset pagination to keyset cursor pagination, keeping page working during the transition. Read package.json, src/app.ts, src/routes/api/tasks/index.ts, src/schemas/tasks.ts, src/schemas/common.ts, src/plugins/app/tasks/tasks-repository.ts, migrations/002.do.tasks.sql, test/helper.ts, test/routes/api/tasks/tasks.test.ts and .github/workflows/ci.yml first. Keep the created_at then id ordering and make the cursor an opaque base64url encoding of the last row's created_at and id. With a cursor, use a row comparison on (created_at, id), fetch limit plus one rows, skip the window count and offset, and return next_cursor only when more rows exist. Without a cursor, keep the page path and its total. Reject a request that sends both. Add cursor and next_cursor to the TypeBox schemas with descriptions so swagger shows them, and add a do and undo migration for a composite index on (created_at, id). Add tests that walk the seeded table by cursor with no duplicates or gaps, survive an insert mid-walk, reverse with asc, reject a malformed cursor and keep page working. Start MySQL in the sandbox as the CI workflow does, run npm run build -- --noEmit, npm run lint, npm run db:migrate and npm run test, and open a pull request that documents the new parameter and the deprecation of page.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a pull request that changes `src/schemas/tasks.ts`, `src/plugins/app/tasks/tasks-repository.ts` and `src/routes/api/tasks/index.ts`, adds the two migration files and the new test cases, and nothing else. The description shows a request and response with `cursor` and `next_cursor`, explains the row comparison and why `total` is absent on the cursor path, states that `page` is kept for now, and quotes the type check, lint and test summaries with the coverage line.

The transcript holds the MySQL start, the migration run with the new index, each `npm run test` run including the walk over the seeded rows, and the swagger JSON the agent fetched from the running app to confirm the parameter appears, so a reviewer sees the route exercised against a real database. `GET /o/{org}/p/{project}/sessions/{id}/changes` returns the diff file by file.

Before merging, a reviewer checks that the cursor encodes `created_at` at the precision the column stores, since whole seconds are what make the `id` tie-break necessary, that the row comparison direction flips with `order`, that a cursor from an `asc` walk is rejected or handled on a `desc` request, and that the undo migration drops only the new index. A follow-up message to the same session applies the same cursor to the users list, and the sandbox still has MySQL running with the seed.

## Run it on every list route that has outgrown offsets

A backend engineer hands the route off from the terminal: one `POST /o/{org}/sessions` with the repository in `resources`, this prompt with the route named, and a `request_id` for the route so a retried request returns the run already started. Close the laptop; read the pull request from the console or from `GET /o/{org}/sessions/{id}` when it is done.

A team converting several list routes across services sends one request per repository, each in its own sandbox with its own MySQL, and a `webhook_url` on the key posts `run.completed` with a `session_url` for each pull request as it lands.

## Why run it on Rush?

Keyset pagination is only proven by walking a real table: the ties on `created_at`, the row comparison and the index all behave differently against MySQL than in a mocked repository. In a Rush sandbox the agent starts MySQL as the CI workflow does, migrates and seeds, walks the table in the tests, and keeps every run in the record.

The pull request carries the evidence a reviewer would otherwise gather by hand: the test walk with no gaps, the swagger output showing the new parameter, and the index migration applied. Several services can get the same change at once, each in its own sandbox, each ending in its own pull request.

## Frequently asked questions

### Do existing clients that send page break?

No. A request with `page` and no `cursor` goes through the offset path and still receives `total`. The pull request marks `page` as deprecated in the schema description so swagger shows it, and a later run can remove it once clients have moved.

### What is in the cursor?

The last row's `created_at` and `id`, encoded as base64url so clients treat it as opaque. The server decodes and validates it and answers 400 to anything malformed. No secret is needed because the values are already visible in the rows the client received.

### Why is total missing on the cursor path?

The offset path computes `count(*) OVER()` on every request, which scans the filtered set and is the cost cursors remove. The cursor path answers with `next_cursor` instead, so a client knows whether more rows exist without a count; if you need a count, a separate endpoint or a cached figure is the usual answer.

### Does this work on Postgres or SQLite?

The row comparison `(created_at, id) < (?, ?)` is standard SQL that MySQL and Postgres both support; SQLite needs it rewritten as an `or` of two conditions, which the agent does if the repository's Knex client is `better-sqlite3`. The index migration syntax follows whatever the existing migrations use.

## More Fastify jobs

- [Add Health Checks to a Fastify App](https://getrush.ai/run/add-health-checks/fastify/)
- [Serve OpenAPI Docs from a Fastify API from a Linear Issue](https://getrush.ai/run/add-openapi-docs/fastify/)
- [Add OpenTelemetry Tracing to a Fastify API](https://getrush.ai/run/add-opentelemetry/fastify/)

## The same job on other stacks

- [Add Cursor Pagination to a Gin API from a Linear Issue](https://getrush.ai/run/add-cursor-pagination/gin/)
- [Add Cursor Pagination to a GORM API](https://getrush.ai/run/add-cursor-pagination/gorm/)
- [Add Cursor Pagination to a Hono API on Workers KV](https://getrush.ai/run/add-cursor-pagination/hono/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
