An audit log that a superuser can quietly edit isn’t an audit log — it’s a support tool that happens to look like one. If you’re building this for customers to trust (security reviews, compliance, “who deleted this record”), the design has to assume someone with database access might want to cover their tracks, including you.

What to record

For every sensitive action, capture:

• Who. The authenticated user ID, not just a session or IP.
• What. The action name (order.refunded, user.role_changed) — a fixed vocabulary, not a free-text description you’ll regret parsing later.
• When. Server timestamp, not client-supplied.
• Before and after. For updates, store the actual field values that changed, not just “record 123 was updated.” “The plan changed from Pro to Free” is auditable. “Record updated” is not.
• Context. IP address, user agent, and — if it exists — the API key or impersonation session that made the request. “An admin did this while impersonating the customer” is a different story than “the customer did this.”

CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  actor_id uuid NOT NULL,
  action text NOT NULL,
  resource_type text NOT NULL,
  resource_id uuid NOT NULL,
  before jsonb,
  after jsonb,
  ip_address inet,
  created_at timestamptz NOT NULL DEFAULT now(),
  prev_hash text NOT NULL,
  row_hash text NOT NULL
);

Where to store it

Append-only, and separate from your operational data. A dedicated table is fine to start, but revoke UPDATE and DELETE on it at the database level for every role except a migration bootstrap role:

REVOKE UPDATE, DELETE ON audit_log FROM app_user;

That one line matters more than most of the schema design. Without it, “append-only” is a convention your application code follows until a bug, a bad migration, or a bored engineer with a database console breaks it.

Make tampering detectable

Even with DELETE revoked, someone with superuser database access can still edit rows directly. You can’t prevent that, but you can make it detectable with a hash chain: each row’s hash includes the previous row’s hash, so editing any row breaks every hash after it.

function hashRow(row, prevHash) {
  return crypto
    .createHash('sha256')
    .update(prevHash + JSON.stringify({ actor: row.actor_id, action: row.action, before: row.before, after: row.after }))
    .digest('hex')
}

Store prevHash and the computed rowHash on each insert. Periodically — or on demand for a compliance review — walk the table and recompute the chain. If any row’s stored hash doesn’t match what you compute from its content and the previous row’s hash, something was altered, and you know exactly where the chain breaks. This doesn’t stop tampering; it makes tampering provable, which is what customers and auditors actually ask for.

What breaks if you skip this

The failure mode isn’t dramatic, it’s a support ticket six months in: “Someone on our team says they didn’t delete this record, can you tell us who did?” If your log only stores “record deleted” with no actor, or if you logged the action in application code that a raw SQL migration bypassed, you have nothing to hand them. That erodes trust faster than the missing feature would have.

Retention

Decide retention before storage costs force the decision for you. Audit logs grow forever if you let them — archive rows older than your compliance window (a year is common) to cold storage (S3, Glacier) instead of deleting them, since “we don’t have that record anymore” is its own credibility problem in a security review.