What the agent does
Soft delete in Drizzle is a column and a discipline: the column takes a minute, and the discipline is every query that must now ignore deleted rows. The agent begins by listing those queries. In a project like w3cj/hono-open-api-starter there is one table, tasks, declared with sqliteTable in src/db/schema.ts, and five handlers in src/routes/tasks/tasks.handlers.ts. create inserts a row; the other four work on rows that exist: list calls db.query.tasks.findMany(), getOne calls findFirst with a where callback, patch runs db.update(tasks).set(updates).where(eq(tasks.id, id)).returning(), and remove runs db.delete(tasks) and answers 404 when result.rowsAffected is 0.
The schema change is deletedAt: integer({ mode: "timestamp" }), nullable, in the same style as the createdAt and updatedAt columns beside it. With casing: "snake_case" in drizzle.config.ts, drizzle-kit generate writes a one-statement migration, an ALTER TABLE on tasks that adds deleted_at as an integer, plus a snapshot and a journal entry. SQLite adds a nullable column to a populated table without complaint, so existing rows simply read as not deleted. The agent creates .env from .env.example first, because the kit loads src/env.ts and exits without one.
Then the handlers. remove becomes an update that sets deletedAt to the current time where(and(eq(tasks.id, id), isNull(tasks.deletedAt))), so deleting a task twice answers 404 the second time through the same rowsAffected check. That is the easy half, and stopping there is the classic mistake: with only that change the project's twelve tests still pass, and GET /tasks/{id} goes on returning the deleted task with status 200, because no existing test reads a task after deleting it. The agent adds isNull(tasks.deletedAt) to list, to the where of getOne and to the where of patch, so a deleted task cannot be read, listed or edited.
The column also leaks into the API unless it is stopped. selectTasksSchema comes from createSelectSchema(tasks) and insertTasksSchema from createInsertSchema(tasks) with id, createdAt and updatedAt omitted. Left alone, every response gains a deletedAt field and the create and patch bodies start accepting one, which would let a client mark a task deleted, or restore one, through PATCH. The agent omits deletedAt from the insert schema and from the select schema, and strips it from what the handlers return. New tests in tasks.test.ts cover each path: delete answers 204 and the row is still in the table with deleted_at set, then get, patch and a second delete answer 404, the list no longer contains it, and a create body carrying deletedAt cannot set it.
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 tasks table in this Drizzle project. Read drizzle.config.ts, the schema file, the database client module, the env module, every route, handler and test for tasks, the migrations directory and package.json first. Create .env from .env.example. Record the results of typecheck, lint and tests before changing anything and list what already fails. Add a nullable deletedAt timestamp column in the style of the existing timestamp columns and generate the migration with drizzle-kit generate; commit the SQL, the snapshot and the journal entry, and do not edit earlier migrations. Change the delete handler to set deletedAt on a row that is not already deleted and keep its 404 for a missing or already deleted row. Add the not-deleted condition to every query that reads or updates tasks: list, get one and patch. Omit deletedAt from the insert, patch and select schemas so no request can set it and no response exposes it, and check that the task schemas in the generated OpenAPI document are unchanged. Add tests: delete returns 204 and the row remains with deleted_at set when read directly through the database client; get, patch and a second delete return 404; list omits it; a create or patch body with deletedAt does not set it. Apply the migrations with drizzle-kit migrate to a scratch database that already holds a row and confirm the new one applies. Add no restore endpoint and no purge job; list them as follow-ups. Open a pull request with the migration SQL and the output of typecheck, lint and tests.",
"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
A pull request with one new migration and its snapshot, the schema change, the four handlers that work on existing rows and the new tests. The description quotes the generated SQL, lists each query that gained the not-deleted condition with its file and function, and pastes typecheck, lint and test output from before and after, with anything that failed before marked as existing.
A section named for what is not included follows: there is no restore endpoint, deleted rows are never purged, and updatedAt changes when a task is deleted because the column's $onUpdate hook runs on every update. Each has a sentence on what it would take, so the team can decide instead of discovering it.
A reviewer greps for the table name and checks every query against the list in the description; a query added by another branch since the run began is the one place a deleted row can still surface. They also open the API reference the app serves and confirm the task schema shows no new field.
Run it on every table that must keep deleted rows
The usual way in is a developer who is asked for this in the middle of other work. They send this body to POST /o/{org}/sessions from their terminal or their editor's MCP client with the table's name changed, and go back to what they were doing; the pull request is on the session when the run completes. Sending a request_id, a UUID made once for the job, makes a retried call return the same run.
For a schema with many tables, one run per table keeps each pull request small enough to review. A script sends the body once per table with a request_id that is a UUID v5 of the repository and table name. Each run generates its own migration, so merge them one at a time and let the later ones regenerate against the new snapshot; two branches that each add the next numbered migration will conflict in meta/_journal.json.
The use case this job follows, with its automation and trigger: Hand off a long task.
Why run it on Rush?
The change is small in lines and wide in reach, and the part that goes wrong is the part nobody ran. In its own sandbox the agent applies the migration to a database with a row in it, runs the suite before and after, and reads the deleted row back through the database client to prove it is still there. Those commands and their output are kept in the transcript at GET /o/{org}/sessions/{id}/messages.
It is also work that fits being handed off. The developer who was asked for it keeps their branch and their local dev.db untouched while the run uses its own checkout, and what returns is a pull request that already carries its test results.
Frequently asked questions
Why not filter deleted rows in one place, like a view or a helper?
In a schema with one table and four queries on existing rows, explicit conditions are the smaller change and the easier review. For a larger schema, say so in the prompt and the agent wraps the condition in a helper each query imports. Drizzle applies no global filter of its own, so some query somewhere always has to opt in.
What happens to unique constraints once rows are soft deleted?
A deleted row still occupies its unique value, so a new row with the same value is refused. The tasks table here has no unique column besides its primary key, so nothing changes. Where one exists, the agent reports it and proposes a partial unique index limited to rows that are not deleted.
Does this work on Postgres and MySQL schemas?
Yes, with the dialect's own column type: a nullable timestamp in place of SQLite's integer timestamp. The handler and schema changes are the same. The agent reads the dialect from drizzle.config.ts and tests the migration against that database in its sandbox.
Will the existing tests need to change?
In w3cj/hono-open-api-starter, no. The existing delete test expects status 204 and still gets it. The new behaviour is covered by added tests, which is why the description lists them separately from the twelve that were there.