# Wrap Multi-Step Writes in Transactions with Drizzle

Yes. An agent finds the sign-up, invite and delete-account actions that write several rows with no transaction, moves them into db.transaction and tests it.

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

## What the agent does

The agent reads the server actions before touching the schema. In an app laid out like nextjs/saas-starter, `app/(login)/actions.ts` holds `signUp`, which inserts a user with `db.insert(users).values(newUser).returning()`, then either accepts a pending invitation with `db.update(invitations).set({ status: 'accepted' })` or inserts a team with `db.insert(teams).values(newTeam).returning()`, and then runs `Promise.all` over `db.insert(teamMembers)`, a `logActivity` call that inserts an `activityLogs` row, and `setSession`. Two early returns sit between those writes: `Invalid or expired invitation.` and `Failed to create team.` both return after the user row exists, so a failed sign-up leaves an owner with no team whose email is now taken. `deleteAccount` logs the activity, soft-deletes the user by setting `deletedAt` to the current timestamp and rewriting the email, and only then deletes the `teamMembers` row; `removeTeamMember` and `inviteTeamMember` each write a row and then log it. `lib/db/drizzle.ts` builds `db` from `drizzle(postgres(process.env.POSTGRES_URL), { schema })`, and `lib/db/schema.ts` declares `users`, `teams`, `teamMembers`, `activityLogs` and `invitations` with foreign keys between them.

The write sequences move out of the actions into `lib/db/accounts.ts`, one exported function per sequence: `createUserWithTeam`, `deleteUserAccount`, `removeMember` and `inviteMember`. Each runs inside `db.transaction(async (tx) => …)` and does every insert, update and delete through `tx`. `logActivity` gains a first parameter for the executor, typed from `db.transaction`'s callback, defaulting to `db`, so the activity row commits or rolls back with the write it describes. The early returns become thrown errors with a small `AccountError` class that carries the message the action returned before; the action catches it outside the transaction and returns the same `{ error, email, password }` object, so `app/(login)/login.tsx` and the forms see no difference. `setSession` and `redirect` stay in the action after the transaction resolves, because a cookie cannot be rolled back and Next's `redirect` works by throwing.

The invitation branch gets one more fix while it is open: the pending invitation is read and then updated in two statements, so two sign-ups with the same invite link could both accept it. Inside the transaction the agent makes the update conditional, `where(and(eq(invitations.id, …), eq(invitations.status, 'pending')))` with `.returning()`, and treats zero returned rows as the invalid-invitation error. The same pattern covers `inviteTeamMember`, whose two existence checks and insert now run in one transaction.

The repository has no test runner and no `.github` directory, so the proof is a new one: `vitest` as a dev dependency, a `test` script in `package.json`, and `lib/db/accounts.test.ts` that runs against a real database. The sandbox has no Docker, so instead of the compose file `lib/db/setup.ts` writes, the run installs PostgreSQL with apt, creates a database, sets `POSTGRES_URL` and runs `pnpm db:migrate`. The tests call `createUserWithTeam` with no invite and assert one user, one team, one membership and two activity rows; call it with an invite whose email does not match and assert no user row was written; accept the same invitation from two concurrent calls and assert exactly one succeeds; and run `deleteUserAccount` and assert the membership and the soft-delete land together. `pnpm build` and `pnpm lint` run after, since the actions file is a `'use server'` module that `next build` type-checks.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Wrap the multi-step database writes in this Next.js and Drizzle app in transactions. Read app/(login)/actions.ts, lib/db/drizzle.ts, lib/db/schema.ts, lib/db/queries.ts, lib/auth/middleware.ts, lib/db/setup.ts and package.json first. Move the write sequences of signUp, deleteAccount, removeTeamMember and inviteTeamMember into lib/db/accounts.ts as functions that run inside db.transaction, passing the transaction to logActivity through a new executor parameter that defaults to db. Turn the early returns inside those sequences into a thrown AccountError that the actions catch and map to the same error objects as today. Keep setSession and redirect outside the transaction. Make accepting an invitation a conditional update on status = 'pending' with returning, so two sign-ups cannot both accept it. Add vitest and a test script, install PostgreSQL in the sandbox, run pnpm db:migrate against it, and write lib/db/accounts.test.ts covering the no-invite path, a mismatched invite that writes nothing, two concurrent accepts of one invitation and account deletion. Run pnpm test, pnpm lint and pnpm build, and open a pull request with the 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

The pull request adds `lib/db/accounts.ts` and `lib/db/accounts.test.ts`, changes `app/(login)/actions.ts` and `package.json`, and leaves `lib/db/schema.ts` and the migration under `lib/db/migrations/` untouched, because no column changes. Its description pastes the vitest run with the four tests named, the `pnpm lint` output and the `next build` route table, and quotes the two early returns it moved so a reviewer can see which failure used to leave an orphaned user.

A sign-up that fails after the user insert now leaves no row, so the person can try again with the same email instead of hitting the `Failed to create user` branch that reads the email as taken. An invitation accepts once. An activity log row never describes a write that rolled back, which matters because `getActivityLogs` in `lib/db/queries.ts` is what the dashboard shows the team.

Before merging, a reviewer checks how long the transactions hold their locks, since `hashPassword` and the Stripe calls in `createCheckoutSession` must stay outside them, and decides whether `signIn`, whose only write is the activity row, needs the same treatment or can stay as it is.

## Run it on every Linear issue labelled agent

Label the issue that describes which action to make atomic, with the file and the rows it writes, and an automation in the Project with a `linear` trigger on `linear.issue.labeled` and the label `agent` starts one run per labelled issue, with the issue's text after the automation's prompt. The prompt asks for the same shape every time: the write sequence into `lib/db/accounts.ts` inside `db.transaction`, the same error objects returned to the form, and a vitest test against a real database. A run whose issue names an action that already runs in one statement opens no pull request and says so on the issue.

Each run opens its own pull request that links the issue, so the person who labelled it reviews the diff and the test output there. The automation's `max_runs_per_hour` caps a burst of labels, and a label added twice is deduplicated on Linear's delivery id.

## Why run it on Rush?

A transaction bug shows up only under a failure between two writes, which never happens on a laptop with a fresh database. The agent reads every write in the action and the returns between them, and its tests force the failure, a mismatched invitation and two concurrent accepts, against a real PostgreSQL in its sandbox rather than a mocked `db`.

The run keeps every command and its output, so the reviewer reads the test run and the build without installing pnpm, PostgreSQL or setting `POSTGRES_URL`. Several apps built from the same starter can be fixed at once, each in its own sandbox, and one whose actions already use `db.transaction` gets only the tests.

## Frequently asked questions

### Why move the code into lib/db instead of wrapping the action?

A server action in `app/(login)/actions.ts` reads cookies, hashes passwords and calls `redirect`, none of which belongs inside a transaction. Putting the writes in `lib/db/accounts.ts` keeps the transaction short and gives the test a function it can call without a request, because `validatedActionWithUser` in `lib/auth/middleware.ts` needs a signed-in user from `getUser()`.

### Does logActivity change for callers I have not touched?

No. The executor parameter defaults to `db`, so `signIn`, `signOut`, `updatePassword` and `updateAccount` call it exactly as before. Only the four moved sequences pass `tx`.

### Why does the test need a real PostgreSQL?

A transaction's rollback and a conditional update's row count are database behaviour, and a mocked `db` proves only that the mock was written the way the code expected. The test runs `pnpm db:migrate` against a database the run installs, the same schema production has.

### What about the Stripe calls in sign-up?

`createCheckoutSession` runs after the transaction has committed, as it does today after `redirectTo === 'checkout'`. A payment call inside a transaction would hold a lock for the length of a network request, so the agent leaves it where it is and says so in the pull request.

## More Drizzle jobs

- [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/)
- [Review Drizzle Migrations in Every Pull Request](https://getrush.ai/run/review-database-migrations/drizzle/)

## The same job on other stacks

- [Wrap Multi-Step Writes in GORM Transactions](https://getrush.ai/run/add-transactions/gorm/)
- [Add Transactions to a Mongoose App](https://getrush.ai/run/add-transactions/mongoose/)
- [Add Transactions to a Prisma App](https://getrush.ai/run/add-transactions/prisma/)

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