What the agent does
The agent starts from how the schema and the queries are owned, because in a sqlc project the Go code is generated and the change has to land in SQL. In a repository laid out like techschool/simplebank, sqlc.yaml reads the schema from db/migration and the queries from db/query, generates into db/sqlc with sql_package: pgx/v5, and sets emit_interface and emit_json_tags. The migrations are five numbered pairs run by golang-migrate (make migrateup, make new_migration name=…). accounts has id, owner, balance, currency and created_at; migration 2 adds the owner_currency_key constraint, one account per owner and currency; entries and transfers reference accounts.id. db/query/account.sql holds seven queries, and DeleteAccount is a plain DELETE FROM accounts WHERE id = $1. No HTTP route in api/account.go and no gRPC method in gapi/ calls it: it exists for the store and its test.
Two things in that reading shape the change. A bank cannot hard-delete an account that entries and transfers still point at, which is the case for soft delete here. And the unique constraint on (owner, currency) would stop a person who closed their EUR account from opening a new one, because the closed row still holds the pair. So the agent writes migration 000006_add_account_deleted_at: add deleted_at timestamptz, drop owner_currency_key, and create a unique index on (owner, currency) WHERE deleted_at IS NULL. The down migration drops the index and restores the constraint; its description says that step fails on purpose if two rows share a pair by then, rather than deleting one of them.
The queries change in account.sql only. DeleteAccount becomes UPDATE accounts SET deleted_at = now() WHERE id = $1 AND deleted_at IS NULL; GetAccount, GetAccountForUpdate, ListAccounts, UpdateAccount and AddAccountBalance gain AND deleted_at IS NULL. That last one matters: TransferTx in db/sqlc/tx_transfer.go moves money with AddAccountBalance, so a transfer into a closed account now returns ErrRecordNotFound instead of crediting it. make sqlc regenerates models.go, account.sql.go and querier.go; Account gains a DeletedAt pgtype.Timestamptz field with a deleted_at JSON tag, which GET /accounts/:id will now show as null on a live account. make mock regenerates db/mock/store.go since the Store interface embeds Querier.
In its sandbox the agent starts Postgres 14 the way .github/workflows/test.yml does, runs make migrateup, then make test, which is go test -v -cover -short ./.... TestDeleteAccount in db/sqlc/account_test.go passes unchanged, because it asserts GetAccount returns ErrRecordNotFound afterwards. The agent adds three tests beside it: the entries of a closed account are still readable with GetEntry; CreateAccount for the same owner and currency succeeds after the close, which exercises the partial index; and TransferTx into a closed account fails with ErrRecordNotFound and leaves both balances unchanged. It runs make migratedown1 and make migrateup1 once to show the pair is reversible, and quotes the output.
Start it with one API call
POST https://api.phoenix.so/o/{org}/sessions with this body:
{
"agent": "claude",
"input": "Add soft delete to the accounts table in this sqlc project. Read sqlc.yaml, Makefile, db/migration, db/query/account.sql, db/sqlc/store.go, db/sqlc/tx_transfer.go, db/sqlc/account_test.go, api/account.go and doc/db.dbml first. Write a new golang-migrate pair that adds a nullable deleted_at timestamptz to accounts, drops the owner_currency_key constraint and replaces it with a unique index on (owner, currency) where deleted_at is null; the down migration reverses it. Change DeleteAccount to set deleted_at, and add a deleted_at IS NULL condition to every account query including AddAccountBalance. Run make sqlc and make mock. Update doc/db.dbml. Start Postgres, run make migrateup and make test. Add tests that entries of a closed account are still readable, that the same owner and currency can be opened again, and that a transfer into a closed account fails without changing balances. Run migratedown1 and migrateup1 once. Open a pull request quoting 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
What comes back is a pull request that touches SQL first and Go second: two new migration files, one edited query file, the regenerated models.go, account.sql.go, querier.go and db/mock/store.go, the accounts table in doc/db.dbml with its new column and index, and three new tests. No handler changes, because the repository exposes no delete over HTTP or gRPC and the existing reads simply stop seeing closed rows.
The description quotes make test with every package green, the three new test names in the -v output, and the migrate log for the down and up of migration 6. It also says, in one paragraph, what the partial index changes: the constraint a reviewer would otherwise look for in db.dbml is now an index with a WHERE, and the reason is the reopened-currency case.
Before merging, a reviewer decides two things the agent left as notes: whether deleted_at should appear in the account JSON that GET /accounts/:id returns, which the emit_json_tags setting makes automatic, and whether an admin path to reopen an account is wanted, which would be one more query in account.sql.
Run it on every Linear issue labelled agent
The change is small but easy to get half right, so a team puts it behind a label. An automation in the repository's Project with the prompt above and a linear trigger on linear.issue.labeled for the label agent starts a run when someone files "closing an account deletes its transfer history" and labels it. The trigger adds the issue after the prompt, so the run reads which table the issue names and scopes the migration to it.
Each run works on its own branch in its own sandbox with its own Postgres, and opens one pull request that links the issue. A second issue about another table, sessions say, gets a second run and a second migration numbered after the first, because the run reads db/migration at start rather than assuming.
The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.
Why run it on Rush?
Soft delete in a sqlc project is five places that must agree: the migration, every query that reads the table, the generated code, the mock, and the schema doc. A person doing it by hand usually forgets one, and the one forgotten is the money-moving query that still credits a closed account. In a Rush sandbox the agent reads the queries and the transaction code before it writes, regenerates rather than edits generated files, and runs the suite against a real Postgres before anyone reviews.
The record of the run keeps the migration log and both test runs, and the pull request quotes them, so the reviewer sees the partial index working and the transfer refused rather than reading a description of it. Across every service a team keeps on sqlc, the same request runs in parallel, one sandbox and one pull request each.
Frequently asked questions
Why replace the unique constraint instead of keeping it?
A constraint on (owner, currency) counts closed rows, so a person who closed a USD account could never open another. A unique index with a WHERE deleted_at IS NULL clause enforces the rule for live accounts only. Postgres supports that on an index but not on a table constraint, which is why the migration drops one and creates the other.
Do the generated files get edited by hand?
No. The agent changes db/query/account.sql and the migration, then runs make sqlc, so models.go, account.sql.go and querier.go come out of the generator. make mock regenerates the gomock store. Hand edits to those files would be lost on the next generate, and the pull request says which files are generated.
What happens to a transfer into a closed account?
TransferTx updates balances with AddAccountBalance, which now carries a deleted_at IS NULL condition. An update that matches no row returns ErrRecordNotFound from the :one query, the transaction rolls back, and the new test asserts both balances are unchanged. The HTTP handler already maps that error to 404.
Can a closed account be reopened?
Not with this pull request. The agent keeps the scope to closing and reading, and notes in the description that reopening is one more query, an UPDATE that clears deleted_at, plus a check that the (owner, currency) pair is free again under the partial index.