# Add an Audit Log to a SQLModel App

Yes. An agent adds an audit_log table with its Alembic migration, records who changed what in every write route, adds a superuser read, and opens a PR.

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

## What the agent does

The agent reads the data layer before it designs the table, because an audit log is only as good as its coverage of the writes. In a backend laid out like fastapi/full-stack-fastapi-template, `backend/app/models.py` defines `User` and `Item` as SQLModel tables with UUID primary keys, a `created_at` set by `get_datetime_utc`, and `Item.owner_id` with `ondelete="CASCADE"`. The writes are in `backend/app/api/routes/items.py` (create, update, delete) and `users.py` (create, update me, change password, delete me, signup, update by id, delete by id), plus `crud.py` for the ones the login and signup paths share. `SessionDep` and `CurrentUser` in `api/deps.py` give every route a session and the caller. Migrations live in `backend/app/alembic/versions`, five of them chained from `e2412789c190` to `fe56fa70289e`, and `alembic/env.py` points `target_metadata` at `SQLModel.metadata`. CI in `.github/workflows/test-backend.yml` brings up Postgres with `docker compose up -d --wait db mailpit`, runs `scripts/prestart.sh` (`alembic upgrade head`), then `scripts/tests-start.sh`, and fails under 90% coverage.

The table goes in `models.py` beside the others: `AuditLog` with `id`, `created_at`, `actor_id` (nullable, since a signup has no caller yet), `action` such as `item.update`, `entity` and `entity_id`, and a `changes` JSON column holding the fields that changed, before and after, with `hashed_password` never written. The agent runs `alembic revision --autogenerate -m "add audit_log"` so the file chains from `fe56fa70289e`, then reads the generated script, because autogenerate puts a `sa.JSON` where Postgres should have `JSONB` and emits no index on `(entity, entity_id)`; it fixes both by hand, the way the earlier revisions in the directory were adjusted.

Recording is one function, `crud.record_audit(session, actor, action, obj, before, after)`, called inside the same session as the write and before the `session.commit()` each route already has, so the row and the change land in one transaction or neither does. Each route in `items.py` and `users.py` takes the model's `model_dump()` before `sqlmodel_update` and after, diffs them, and passes the result. Delete routes record the final state as `before`; the cascade delete of a user's items is written as one `user.delete` row with the item ids, not one row per item, which the pull request says in a sentence. Password changes record the action with an empty diff.

Reading the log is a new superuser route, `GET /audit-log/` with `skip` and `limit` and optional `entity_id`, under `Depends(get_current_active_superuser)` like `GET /users/`, returning `AuditLogsPublic` with `data` and `count` to match the shape `ItemsPublic` uses. In its sandbox the agent runs `docker compose up -d --wait db`, `uv run bash scripts/prestart.sh` to apply the migration, and `uv run bash scripts/tests-start.sh`. It adds tests under `tests/api/routes/test_audit_log.py` in the pattern of `test_items.py`: an item update writes one row whose `changes` names only `title`; a normal user gets 403 on the read route; the `conftest.py` teardown that deletes `Item` and `User` is extended to delete `AuditLog` first. It runs `uv run bash scripts/generate-client.sh` so the frontend's generated client knows the route, and quotes `coverage report` staying over 90%.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add an audit log to this FastAPI and SQLModel backend. Read backend/app/models.py, backend/app/crud.py, backend/app/api/routes/items.py, backend/app/api/routes/users.py, backend/app/api/deps.py, backend/app/alembic/env.py, the files in backend/app/alembic/versions, backend/tests/conftest.py and .github/workflows/test-backend.yml first. Add an AuditLog table with actor_id, action, entity, entity_id, created_at and a JSONB changes column; generate the Alembic revision from the latest head and fix its types and add an index on (entity, entity_id). Add a crud.record_audit function and call it in every create, update and delete route in the same transaction, diffing before and after and never writing hashed_password. Add a superuser-only GET /audit-log/ route with skip, limit and entity_id, returning data and count. Start the database with docker compose, run scripts/prestart.sh and scripts/tests-start.sh, add tests for one recorded update and a 403 for a normal user, extend the conftest teardown, run scripts/generate-client.sh, keep coverage above 90 percent, and open a pull request quoting the results.",
  "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 with a new model and public schema in `models.py`, one hand-corrected Alembic revision chained from the current head, a `record_audit` function in `crud.py`, a call in each of the nine write routes, a new `audit_log.py` route registered in `api/main.py`, two tests, one line in `conftest.py`, and the regenerated frontend client. The description lists the routes and what each records, so a reviewer can check coverage of the writes without reading every diff hunk.

The test output is quoted: the whole suite green under `coverage run`, the two new tests named, and the coverage percentage after the change. The migration is shown applying from `fe56fa70289e` with the `JSONB` type and the index in the script, not the autogenerated `JSON`.

Before merging, a reviewer settles two policies the agent left as notes: how long rows are kept, since nothing deletes them yet and a retention job is a separate change, and whether `changes` for a user update should mask `email` the way it already skips the password hash.

## Run it on every Linear issue labelled agent

An audit log tends to arrive as a compliance ask, which makes it a good first label to automate. An automation in the backend's Project with the prompt above and a `linear` trigger on `linear.issue.labeled` for the label `agent` starts a run when someone files "we need to know who changed a customer's item" and labels it. The trigger adds the issue after the prompt, so the run reads which models the issue names and records those writes first.

Every run has its own sandbox, its own Postgres from `docker compose`, and its own branch, and opens one pull request that links the issue. A later issue to add a retention sweep runs the same way and chains its revision after the one this run wrote, because each run reads the versions directory before generating.

## Why run it on Rush?

The hard part of an audit log is not the table, it is the nine routes that each have to record in the same transaction as their write, and the autogenerated migration that looks right and is not. Done by hand on a Friday, one route is missed and the log is trusted anyway. In a Rush sandbox the agent reads every write path first, records inside the existing commits, corrects the revision it generated, and proves the coverage with a test before anyone reviews.

The record of the run keeps the migration output, the test run and the coverage report, and the pull request quotes them, so the reviewer checks the route list against the diff instead of taking the word "complete" on trust. For every backend a team keeps on this template, the request runs in parallel, one sandbox and one pull request each.

## Frequently asked questions

### Why not a SQLAlchemy event listener instead of calls in each route?

A listener on after_flush would catch every write, but it has no caller: the request's user lives in the route's dependency, not in the session. The explicit call passes the actor and names the action, and the pull request lists the routes so a reviewer can see none were skipped.

### Does the log row land even if the write fails?

No. record_audit adds the row to the same session before the route's existing session.commit(), so the write and the log commit together or roll back together. The test for an item update checks there is exactly one row after the request.

### What does the migration look like after the fix?

The autogenerated file is kept but edited: the changes column is postgresql.JSONB rather than sa.JSON, and an op.create_index on (entity, entity_id) is added with a matching drop in downgrade. The revision id chains from the current head, so alembic upgrade head applies it in order.

### Who can read the log?

Only superusers. The read route depends on get_current_active_superuser, the same dependency that guards listing users, and a test asserts a normal user's token gets 403. Writes are recorded for everyone, including signups, where actor_id is null.

## More SQLModel jobs

- [Add Cursor Pagination to a SQLModel API](https://getrush.ai/run/add-cursor-pagination/sqlmodel/)
- [Add Database Indexes to a SQLModel App With Alembic](https://getrush.ai/run/add-database-indexes/sqlmodel/)
- [Add Email Verification to a FastAPI App on SQLModel](https://getrush.ai/run/add-email-verification/sqlmodel/)

## The same job on other stacks

- [Add an Audit Log of Row Changes to a Supabase Project](https://getrush.ai/run/add-audit-log/supabase/)
- [Add an Audit Log to a Django REST Framework API from a Linear Issue](https://getrush.ai/run/add-audit-log/django-rest-framework/)
- [Add an Audit Log to a Fastify API](https://getrush.ai/run/add-audit-log/fastify/)

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