What the agent does
With sqlc the pagination lives in the SQL, so the agent reads db/query before any Go. In a project like techschool/simplebank three queries page by offset: ListAccounts in account.sql, ListEntries in entry.sql and ListTransfers in transfer.sql all end in ORDER BY id, LIMIT and OFFSET. Only one is reachable over HTTP. api/server.go registers GET /accounts, whose handler in api/account.go binds page_id and page_size and computes the offset as (req.PageID - 1) * req.PageSize; the other two are called from tests alone. The agent then reads the schema for how long each list can get. Migration 000002_add_users.up.sql puts a unique constraint on an account's owner and currency, and util/currency.go accepts three currencies, so one owner holds at most three accounts while the handler insists on a page_size of at least five. Entries and transfers per account have no such ceiling.
That changes where the work goes, and the agent says so instead of converting whatever the issue happened to name. A cursor on the accounts list would be machinery around three rows. The lists that will grow are entries and transfers, so those get keyset queries: ListEntriesAfter filters on account_id and id > sqlc.arg(after_id), orders by id and takes a limit, and the transfers query does the same with its from_account_id OR to_account_id condition. The existing offset queries stay, because their tests and any caller of the generated methods still use them, and deleting a generated method is a breaking change to the Querier interface that the mock in db/mock/store.go implements.
Generated types are checked, not assumed. The agent installs the sqlc release named in the generated headers, 1.22.0, confirms that regenerating the untouched repository changes nothing, and reads each new params struct. The simple form is inferred correctly: id > sqlc.arg(after_id) yields an int64 field. The form people reach for when sorting newest first is not. A row comparison such as (created_at, id) < (sqlc.arg(before_created_at), sqlc.arg(before_id)) generates a params struct on this version in which BeforeID is a time.Time, the type of the first column, and nothing warns about it. Casting each argument in the SQL, ::timestamptz and ::bigint, produces the right fields. The agent keeps the cast form wherever a cursor has two parts and quotes both structs in the pull request.
For the index it reads 000001_init_schema.up.sql: entries are indexed on account_id, transfers on each account column and on the pair. A keyset query on entries wants account_id and id together, so the agent adds migration 000006 with that composite index and its down file, and checks the plan with EXPLAIN in a database it sets up itself, PostgreSQL from system packages with the root role and simple_bank database that app.env expects, migrated with golang-migrate as make migrateup does. New tests in entry_test.go and transfer_test.go insert more rows than a page, walk the cursor to the end, and assert each id appears once and in order, then insert and delete between pages to show nothing is skipped or repeated. Adding methods to Querier breaks the committed mock until make mock regenerates it, and make test runs last.
Start it with one API call
POST https://api.phoenix.so/o/{org}/sessions with this body:
{
"agent": "codex",
"input": "An issue labelled pagination asks for cursor pagination in this Go project that uses sqlc with PostgreSQL. Read sqlc.yaml, the Makefile, every file in db/query and db/migration, the hand-written and generated files in db/sqlc, db/mock, the api package's routes and list handlers, and the tests first. For each list query, say which code calls it, whether it is reachable over HTTP, and how many rows it can return given the schema's constraints; put the work where lists actually grow and say so if that differs from what the issue names. Install the sqlc version named in the generated files' headers and confirm that sqlc generate on the untouched repository produces no diff. Write keyset queries as new named queries beside the offset ones; do not remove or change existing queries. After generating, read every new params struct and check each field's Go type against the column it is compared with; where sqlc infers a wrong type, fix it with explicit casts in the SQL and show the struct before and after. Add a migration with any index the new queries need, with a down file, using the repository's numbering. Install PostgreSQL from system packages, create the role and database the test config expects, apply the migrations with the tool the Makefile uses, and check each new query's plan with EXPLAIN on a table with enough rows to matter. Add tests that walk the cursor across more rows than one page and that insert and delete rows between pages. Regenerate mocks with the repository's make target in a separate commit. Only change an HTTP handler if the issue asks for it, and then keep the old parameters working. Run make test and open a pull request with the per-query findings, the generated types, the plans and the test output.",
"resources": [
{
"url": "https://github.com/your-org/your-app"
}
]
}- 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.
- 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.
- 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
The pull request adds queries to db/query, their generated code, one migration pair, tests, and a regenerated mock in its own commit. Its description starts with the per-query findings, which may not be what the issue's author expected: which lists are bounded by the schema and were left on offset, and which were given a cursor. For the accounts endpoint it states the numbers it read, three currencies and a minimum page size of five, and recommends against a cursor there.
The middle of the description is about types. It shows the row-comparison query as first written, the params struct sqlc 1.22.0 generated for it with the timestamp type on the id field, and the cast version with the correct struct. This is the part a reviewer should not skim, because the wrong struct compiles and fails only when a real id is sent as a time. The EXPLAIN output for each new query, before and after the index migration, follows, and then make test from the sandbox's PostgreSQL.
What remains for a person is the API shape, if an endpoint is to use the new queries: whether the cursor is the raw id or an opaque string, and whether old page_id callers keep working. The agent changes no handler unless told to, and lists the two list queries that have no endpoint at all so the team can decide whether they need one.
Run it on every GitHub issue labelled pagination
Add an automation to the Project in the console with a github trigger on issues.labeled and pagination in its labels. The saved prompt carries the method and no table names: find which lists grow, keep the offset queries, verify generated types, add the index, walk the cursor in a test. The labelled issue is appended after the prompt, and it is the only place the endpoint is named.
A github trigger fires for owners, members and collaborators of the repository and never for bots or forks. Because each run regenerates querier.go and the mock, two pagination issues at once produce pull requests that conflict in generated files; the simple rule is to merge one and re-run the other, which costs one more labelled event.
The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.
Why run it on Rush?
This job has two places where a plausible answer is wrong, and both are found only by running tools. The type sqlc infers for a cursor argument is visible only in generated output from the pinned version, and whether an index serves a keyset query is visible only in a plan from a real PostgreSQL. The agent has both in its sandbox, and the generated structs and plans it read are kept in the run's record.
It also needs a database that can be filled, walked and thrown away. On a laptop that is the shared development database or a container someone has to remember to remove. A run starts from an empty machine with the versions the repository names, and returns a pull request whose claims can each be traced to a command in the transcript at GET /o/{org}/sessions/{id}/messages.
Frequently asked questions
Why keep the offset queries?
They are part of the generated interface that other code and the mock depend on, and removing a method breaks every implementer at compile time. Offset is also still the right tool for small, bounded lists. The agent adds keyset queries alongside and leaves retirement of the old ones to a later change.
Can I sort by created_at instead of id?
Yes, with the id as a tiebreak, because two rows can share a timestamp. That is the row-comparison form, and on sqlc 1.22.0 it needs explicit casts on both arguments to generate the right Go types. It also wants an index on the same columns in the same order.
Does this apply with newer sqlc versions?
The approach does. Whether the inference problem remains depends on the release, so the agent does not upgrade the generator as a side effect; it uses the pinned version and reports what that version does. If you want an upgrade, ask for it as its own run, since it can change every generated file.
How should the cursor be exposed to API clients?
As an opaque string that encodes the last row's sort values, returned with each page and accepted on the next request. Clients then cannot build cursors by arithmetic. The agent implements that in a handler only when the issue asks for an endpoint change.