How to Add an Audit Log to an AI-Built App

The request arrives in the same sentence every time: "a customer says their plan was downgraded and nobody did it, can you check who changed it?" If your app cannot answer that, you are about to spend an afternoon reconstructing history from application logs that were never designed to hold it.

An audit log fixes that, and it is one of the cheapest things to retrofit. Here is how to add an audit log to an AI-built app in a way that answers the question when it comes, rather than producing a table full of rows nobody can read.

Audit log versus the other logs

Three things get called logging and only one of them is this.

Application logs record what the software did: requests, errors, timings. They are for engineers, they are noisy, and they usually expire in days.

Analytics events record what users did in aggregate: page views, funnels, feature usage. They are sampled, approximate, and nobody minds if one is lost.

An audit log records who changed what, when, and to what value. It is read by humans investigating a specific incident, it must be complete, and losing a row defeats the purpose. If you are tempted to use your existing analytics setup for this, do not. Different guarantees, different consumers.

The table

One table covers the great majority of cases.

create table audit_log (
  id           bigserial primary key,
  occurred_at  timestamptz not null default now(),
  actor_type   text not null,        -- user | system | api_key | agent
  actor_id     text,                 -- null for system
  actor_label  text,                 -- email or name, denormalised on purpose
  action       text not null,        -- subscription.downgraded
  entity_type  text not null,        -- subscription
  entity_id    text not null,
  before       jsonb,
  after        jsonb,
  metadata     jsonb default '{}',   -- ip, request id, reason
  constraint audit_log_actor_ck check (actor_type <> 'user' or actor_id is not null)
);

create index audit_log_entity_idx on audit_log (entity_type, entity_id, occurred_at desc);
create index audit_log_actor_idx  on audit_log (actor_id, occurred_at desc);

Four details in there are deliberate.

actor_label is denormalised deliberately. If a user is deleted or renames themselves, the log should still say who did it at the time. Joining to a live users table gives you the current truth, not the historical one, and historical truth is the entire point.

actor_type includes agent because AI-built apps increasingly have non-human actors. When an automation or a model-driven job changes a record, the log needs to say so rather than attributing it to whichever service account it authenticated with.

before and after hold only the fields that changed, not whole rows. Full snapshots make the table enormous and the diffs unreadable.

The two indexes reflect the two questions you will actually ask: what happened to this record, and what did this person do.

Where to write from

The single most common mistake is writing audit entries from wherever you happened to think of it, so half the write paths log and half do not. A log with holes in it is worse than none, because it produces confident wrong conclusions.

Pick one boundary and hold it.

Service layer. Every state change goes through a function that writes the change and the audit row in the same transaction. Best fit for most apps: you keep business context like the reason for the change, and the transaction guarantees they cannot diverge.

Database triggers. A trigger on each audited table captures changes regardless of what wrote them, including manual fixes run in a console at 2am. That completeness is a genuine advantage. The cost is that triggers see rows, not intent, so you get "status changed from active to cancelled" without knowing whether that was a user cancelling or a billing job.

Both. Triggers as the safety net for completeness, service layer for the semantically rich entries. This is where mature systems land, and it is more machinery than a young app needs.

Start at the service layer. Move to both when someone changes data outside the app and you cannot see it.

Four decisions that decide whether it is useful

Name actions as past-tense business events. subscription.downgraded beats update_subscription. Someone scanning a hundred rows during an incident is reading for meaning, not for function names, and CRUD verbs force them to reconstruct meaning from the diff every single time.

Never log secrets. Passwords, tokens, API keys, and full payment details must not reach before or after. Maintain an explicit redaction list and apply it at the write path, because a log that must be sanitised later is a log nobody will sanitise. The same discipline applies to environment variables and secrets generally.

Make it append-only in practice. No update or delete grants on the table for the application role. If you use row-level security, an insert-only policy for the app and read access limited to admins is a five-line change that makes tampering visible. The people most likely to want a row gone are exactly the ones you are logging.

Decide retention deliberately. Audit data is often the record you need long after everything else has been pruned, and some of it falls under record-keeping obligations such as Article 30 of the GDPR. Pick a number, write it down, and partition by month if volume warrants it.

What to log, and what not to

Log every change to anything a user would argue about: permissions and roles, billing and plan changes, ownership transfers, data exports, destructive deletes, authentication events that matter such as password and email changes, and any admin action taken on behalf of a user.

Do not log reads by default. Read logging multiplies volume by an order of magnitude and is rarely what anyone asks about. Add it only for genuinely sensitive records, and then only for those.

Impersonation deserves a special mention: when support staff act as a user, log both identities. Without that, the log shows the customer doing something they did not do, which is the exact failure the log exists to avoid.

Making it readable

A table nobody can query is a backup you never test. Two things make it usable.

Put a filtered view in your admin dashboard: filter by entity, by actor, by date range, and render before and after as a field-level diff rather than raw JSON. Half an hour of work, and it is the difference between answering the customer in two minutes and in two hours.

Then show users their own history. An account activity page listing logins, permission changes, and billing events resolves a real share of "I did not do that" tickets without anyone from your team getting involved.

Verify it works

Do this now rather than during an incident.

1. Make a change through the app, and confirm the row appears with the right actor.

2. Make the same change directly in the database, and see whether anything is logged. If not, you now know the boundary of your coverage.

3. Try to update or delete an audit row using the application's credentials. It should fail.

4. Check that a redacted field is genuinely absent, not merely absent from the UI.

5. Ask someone unfamiliar with the code to answer "who downgraded this account and when" using only the log.

Step five is the real test. Everything else is mechanics. The organisational counterpart, covering AI tool usage across a team rather than inside one app, is in keeping an audit trail of AI use, and access control that the log records is in role-based permissions.

FAQ

Should the audit log live in the same database?

Same database is fine and much simpler to keep consistent, since you can write the change and the audit row in one transaction. Move it elsewhere only when volume or a formal separation-of-duties requirement forces it.

How big will this table get?

Smaller than expected if you log changes rather than reads. A few hundred writes a day is tens of thousands of rows a year, which Postgres handles without complaint. Partition by month if you get into the millions.

Can an AI coding agent add this for me?

Yes, and it is a good task for one, since the pattern is well established. Review two things carefully in the output: that every write path actually logs, and that redaction is applied before the insert rather than after.

What if my app is already live without one?

Add the table and start writing today. You cannot backfill history you never recorded, but the gap only grows while you wait for a better moment.