An audit log the application cannot edit

A clinical system has to answer “who changed this?” years later, and the answer is only worth something if nobody could have changed the answer. So we enforce append-only where it can’t be forgotten, in the database’s grants:

grant select, insert on platform.audit_event to app_rw;

No update, no delete. The application role can’t rewrite history, even with a bug.

In the same transaction

recordAudit(tx, ctx, input) takes the caller’s transaction and appends to it, so the change and its record commit or roll back together. There’s no moment where a change exists without its event, and no failed change leaves one behind.

What an event holds

• The actor: a type (user, system, api_app, workflow, support, patient) and an id.
• The action, the entity type and the entity id.
• changes, built by a small helper that keeps only the keys whose values differ.
• A reason, the request id, the IP address and the user agent.
• A client id, when the event concerns something on a client’s record.

That last field exists for one report. An access report for one client has to find every event about them, whatever the entity type, and a dedicated column means it doesn’t have to know them all.

Built to keep

The table is partitioned by range on its timestamp, with the time inside the primary key (tenant_id, at, id). A default partition keeps inserts working however maintenance is going. It carries tenant_id and the same forced row level security as everything else, so a clinic reads only its own trail.