# Add Soft Delete to a Drizzle Schema

Yes. The agent adds a nullable deleted-at column with its migration, filters every read and write on it, hides it from the API schemas and tests each path.

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

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

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

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

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

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

## More Drizzle jobs

- [Wrap Multi-Step Writes in Transactions with Drizzle](https://getrush.ai/run/add-transactions/drizzle/)
- [Add updatedAt Columns That Actually Update to a Drizzle Schema](https://getrush.ai/run/add-updated-at-timestamps/drizzle/)
- [Migrate a Drizzle Schema from SQLite to PostgreSQL](https://getrush.ai/run/migrate-sqlite-to-postgres/drizzle/)

## The same job on other stacks

- [Add Soft Delete to a Fastify API from a Linear Issue](https://getrush.ai/run/add-soft-delete/fastify/)
- [Add Soft Deletes to a Filament Resource](https://getrush.ai/run/add-soft-delete/filament/)
- [Add Soft Delete to GORM Models](https://getrush.ai/run/add-soft-delete/gorm/)

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