# Add Soft Delete to a sqlc Project

Yes. An agent adds deleted_at by migration, rewrites the sqlc queries to skip closed rows, swaps the unique constraint for a partial index, and opens a PR.

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

## 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`

```json
{
  "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"
    }
  ]
}
```

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 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.

## 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.

## More sqlc jobs

- [Add Bulk Inserts to a sqlc and pgx Project](https://getrush.ai/run/add-bulk-inserts/sqlc/)
- [Check sqlc Generated Code in CI With sqlc diff and sqlc vet](https://getrush.ai/run/add-codegen-check-to-ci/sqlc/)
- [Add Cursor Pagination to sqlc Queries](https://getrush.ai/run/add-cursor-pagination/sqlc/)

## The same job on other stacks

- [Add Soft Delete to a SQLModel and FastAPI App](https://getrush.ai/run/add-soft-delete/sqlmodel/)
- [Add Soft Delete to a Supabase Table](https://getrush.ai/run/add-soft-delete/supabase/)
- [Add Soft Delete to a tRPC and Prisma App](https://getrush.ai/run/add-soft-delete/trpc/)

## 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/)
