An audit log exists to answer one question: who changed this, and what did it look like before. It is consulted rarely and always at a bad moment — a disputed invoice, a timesheet somebody says they did not edit, a deletion nobody admits to.

Which makes it the worst possible table to discover is empty.

The trap

The natural design is a foreign key from the audit row to the thing it describes. It is clean, it is referentially sound, and it means the audit row is deleted the moment anybody deletes the subject.

So the one event you most need recorded — the deletion — is the one event that removes its own record. And if the subject’s deletion cascaded through several tables, the audit rows for all of them go at the same time.

The alternative of RESTRICT is worse in its own way: now nothing can ever be deleted, because an audit row always references it.

The answer is that an audit log should not have a foreign key to its subject at all. Store subject_type and subject_id as plain columns. No constraint, no cascade, no referential integrity — on purpose.

That feels wrong if you are used to constraining everything, and it is correct here. The audit row is not a child of the subject. It is a statement about a moment in time, and that statement remains true after the subject is gone. It is the only thing that still is.

What to store

Who, twice. The user id and the user’s name as text, copied at the time of writing. The id lets you join while the user exists; the text is what saves you when the user has been deleted and all you have is a number. “User 47 approved this” is not an answer anybody accepts.

What, as a verb. Created, updated, deleted, approved, switched. A fixed vocabulary, not free text, so the log can be filtered.

On what, as plain columns. Type and id. “leave_request”, 1284.

Before and after — the changed fields only. Storing the entire row twice makes the table enormous and the diff unreadable. Store the keys that changed with their old and new values. A field that did not change does not need to be in the record.

When and from where. Timestamp and IP. The IP earns its place about once a year and that once is usually important.

Acting as someone else

Any system where an administrator can act on behalf of a member — filing leave for someone on a train, correcting a forgotten clock-out, viewing the app as that member to reproduce a problem — needs two user columns, not one.

The actor is who performed it. The subject is whose record it was. Collapse them and the log says the member did something they did not do, which is worse than not logging it, because now there is a confident wrong answer where there used to be an absence.

This matters most for impersonation. If an admin can view the product as a member, every action taken during that session must record both identities. Otherwise the log shows the member deleting their own time entry at 11pm, and nobody can prove that is not what happened.

What should not be in it

Audit logs leak, because they are the first thing exported when somebody is investigating something.

Never record passwords, tokens or session identifiers — not even hashed, not even partially. Never record the full contents of a row that contains personal data when only one field changed. Never record a request body wholesale, because somebody will eventually post a credential to an endpoint and it will be preserved for years in a table nobody thought of as sensitive.

Append only

Nothing updates an audit row. Nothing deletes one except a retention job with an explicitly longer period than everything else, and ideally not even that.

If the application has a generic admin interface over its tables, exclude this one. The ability to edit the audit log makes the audit log worthless, and the person most likely to use that ability is the person you would most want it to have caught.

Retention, separately

Audit rows are small and they are the record of last resort. Whatever your screenshot retention is, this is not it.

The failure to avoid is a retention job that treats every table the same and prunes the audit log alongside the activity samples. That is how a system ends up with a perfect record of the last thirty days and nothing at all about the year in which the disputed thing happened.

Read it once a quarter

Not to check for wrongdoing — to check it works. Pick a change you know about, find it in the log, and confirm the row says what happened.

Audit logs fail silently. A code path that was added last month without logging produces no error and no row, and you find out when you go looking for an event that was never recorded. Ten minutes a quarter is the only way to know the thing you are relying on is actually running.