All jobs

Add an Audit Log to a Sequelize and Express API

Yes. An agent adds an audit log table to your Sequelize app, records every create, update and delete through hooks in the same transaction, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads how writes reach the database before it hooks them. In an app laid out like sequelize/express-example, sequelize/index.js opens a SQLite file at sqlite-example-database/example-db.sqlite with logQueryParameters and benchmark on, runs each file in modelDefiners to define user, instrument and orchestra, and calls applyExtraSetup, which declares orchestra.hasMany(instrument) and instrument.belongsTo(orchestra). express/app.js builds the REST routes in a loop over routes, mounting getAll, getById, create, update and remove for each controller under /api/<name> and wrapping each in makeHandlerAwareOfAsyncErrors. The three controllers in express/routes/ write with models.x.create(req.body), models.x.update(req.body, { where: { id } }) and models.x.destroy({ where: { id } }); sqlite-example-database/setup.js rebuilds the database with sequelize.sync({ force: true }) and seeds four users, three orchestras and ten instruments each. package.json pins sequelize 6.3 and sqlite3 5, has no test script, and .npmrc turns off the lockfile.

The detail that decides the design is that update and destroy in the controllers are the static, bulk forms. Sequelize fires beforeBulkUpdate and afterBulkUpdate for those, with the where clause but no instances, so a hook there cannot say which row changed or what it looked like before. The agent therefore adds individualHooks: true to the three update and destroy calls, which makes Sequelize load the matching rows and fire afterUpdate and afterDestroy per instance, and wraps each controller write in sequelize.transaction so the change and its audit row commit or roll back together. Each hook receives options.transaction and passes it on, which is what keeps the log consistent.

It defines sequelize/models/audit-log.model.js with model, recordId, action (create, update or delete), before and after as DataTypes.JSON, which SQLite stores as text and PostgreSQL as native JSON, changedFields, and actor, read from an X-Actor request header the agent threads through options because the app has no authentication to take a user from. A new sequelize/audit.js registers afterCreate, afterUpdate and afterDestroy on every model except the log itself; the update hook uses instance.changed() and instance.previous() for the before image and the list of fields, so an update that changes nothing writes no row. The model is appended to modelDefiners, so setup.js creates the table with the others, and a read-only audit-log controller joins the routes map so GET /api/audit-log?model=user&recordId=1 lists a record's history, newest first.

There is no test runner, so the agent adds scripts/check-audit.js, run as npm run check-audit, which resets the example database, starts the app on a free port, performs a create, an update, a no-op update and a delete against /api/users with an X-Actor header, then reads the log and asserts four facts with Node's assert: three rows, the right actions in order, a before image on the update that matches the created row, and nothing recorded for the no-op. It runs npm run setup-example-db, npm start to confirm the server boots with the new model, and the check script, and keeps the output.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add an audit log to this Sequelize and Express app and open a pull request. Read package.json, .npmrc, index.js, express/app.js, express/helpers.js, every file under express/routes/, sequelize/index.js, sequelize/extra-setup.js, every model under sequelize/models/, and sqlite-example-database/setup.js first. Add an audit log model with model name, record id, action, before and after JSON images, changed fields, actor and timestamp, register it with the other models, and install afterCreate, afterUpdate and afterDestroy hooks on every other model that write a row in the same transaction. The controllers use static update and destroy with a where clause, so pass individualHooks so per-row hooks fire, wrap each write in a transaction, and take the actor from an X-Actor header. Record nothing for an update that changes no field. Add a read-only route that lists a record's history. Add a check script with no new test framework that resets the database, exercises create, update, no-op update and delete, and asserts on the rows. Run the database setup, start the server once, run the check script, and open a pull request with its 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 sequelize/models/audit-log.model.js, sequelize/audit.js, express/routes/audit-log.js and scripts/check-audit.js, and changes sequelize/index.js, express/app.js and the three controllers to pass a transaction, individualHooks and the actor. Its description shows the rows the check script read back, including the JSON before and after images for the update, and the server's startup lines with the new table created.

After merging, every write through the API leaves a row that says what changed, from what, by whom and when, and the history of one record is one GET away. Writes made outside Sequelize, such as raw SQL or a bulkCreate with hooks off, are not recorded, which the description states so nobody assumes the log is complete by construction.

Before merging, a reviewer decides whether the actor should come from real authentication rather than a header, whether before images of large rows should be trimmed to the changed fields only, and how long rows are kept, since the table only grows.

Run it on every Linear issue labelled agent

A Rush automation with a linear trigger starts one run for each issue that gets the label, in the Project that has this repository, with this prompt as its instructions; the trigger adds the issue's title and description after the prompt. An issue that asks for one more model to be audited, or for the log to carry a request id, becomes a run that reads the hooks already in place and extends them, and opens a pull request that links the issue. An automation run has its own max_runs_per_hour cap.

A team with several services on this layout sends the body once per repository to POST /o/{org}/sessions instead, each with its own repository in resources and a request_id that is a UUID v5 of the repository name, so a retried call returns the run already started.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

An audit log in Sequelize is mostly about the cases that silently miss: bulk update and destroy that skip per-row hooks, a log row committed while the change rolls back, an update that changed nothing but logged a row anyway. The agent reads the controllers, finds the three static calls, and fixes each case where it happens, then proves it with a script that drives the real API against the real database in its own sandbox.

The check script's output and the server start stay with the run and are pasted into the pull request, so the reviewer sees the rows rather than a promise. Several services can be done at once, each in its own sandbox.

Frequently asked questions

Why change the controllers instead of only adding hooks?

Because Model.update and Model.destroy with a where clause fire bulk hooks that carry no instance, so a hook alone cannot record the row's before image. Passing individualHooks: true makes Sequelize fetch the rows and fire per-instance hooks, at the cost of one extra query per write.

Does it work on PostgreSQL or MySQL too?

Yes. The model uses DataTypes.JSON, which Sequelize maps to native JSON on PostgreSQL and MySQL and to text on SQLite, and the hooks and transactions are dialect-independent. Only the example database setup is SQLite-specific.

What happens to the log when a write fails?

Nothing is kept. The hook writes inside the same transaction as the change, so a failed insert or a constraint error rolls back both, and the check script's no-op update shows that an update with no changed fields writes no row at all.

Can I read the history of one record?

Yes, through the read-only route the agent adds, filtered by model and record id and ordered newest first. It is mounted the same way as the other routes, so it inherits the async error wrapper in express/app.js.

More Sequelize jobs

The same job on other stacks

Keep reading