Add an Append-Only Audit Log

Record who changed which record, when, and from where in an append-only table written in the same transaction as the business change.

~35 mindatabaseauditsecurity

Task

Create an audit trail for sensitive mutations that answers who, what, when, and which request, without allowing ordinary application code to rewrite history.

When to use this

A support or compliance request asks who changed a customer's billing address. Application logs are incomplete, mutable, and not tied to the database transaction that made the change.

Prerequisites

A relational database with transactions
An authenticated actor identity available to the mutation path
A retention and access policy for audit data

Steps

1. Create an audit table with actor, action, entity, before/after snapshots, request id, and timestamp; do not store secrets.

CREATE TABLE audit_events (
  id          bigserial PRIMARY KEY,
  actor_id    text NOT NULL,
  action      text NOT NULL,
  entity_type text NOT NULL,
  entity_id   text NOT NULL,
  before      jsonb,
  after       jsonb,
  request_id  text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_events_entity_idx
  ON audit_events (entity_type, entity_id, created_at DESC);

Expected output: The table exists with an index for entity history queries.

2. Write the audit row in the same transaction as the business update so a failed update cannot leave a phantom audit event.

await client.query('BEGIN');
try {
  const before = await client.query(
    'SELECT * FROM customers WHERE id = $1 FOR UPDATE', [id]
  );
  await client.query(
    'UPDATE customers SET billing_address = $1 WHERE id = $2',
    [address, id]
  );
  await client.query(
    `INSERT INTO audit_events
       (actor_id, action, entity_type, entity_id, before, after, request_id)
     VALUES ($1, $2, $3, $4, $5, $6, $7)`,
    [actorId, 'customer.billing_address.update', 'customer', id,
     before.rows[0], { billing_address: address }, requestId]
  );
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
}

Expected output: A successful update has exactly one audit row; a failed update has none.

3. Revoke UPDATE and DELETE on the audit table from the application role and verify the history query.

REVOKE UPDATE, DELETE ON audit_events FROM app_role;

Expected output: Application code can insert and select audit rows but cannot alter or delete them.

Troubleshooting

Problem

Audit rows exist for changes that rolled back

Solution

The audit insert is outside the transaction or uses a separate connection. Use the same client and transaction.

Problem

The audit table grows without bound

Solution

Define retention and partition by month; archive old partitions to immutable storage rather than deleting randomly.

Limitations

An audit log is not a backup; protect it with separate access controls and backups.
Avoid storing full row snapshots when they contain secrets or personal data; store only the fields required by policy.

Audit the mutation, not the request

An HTTP access log records that a request happened. An audit event records the state transition: actor, action, entity, before, after, and request id. The two are complementary. Access logs help with traffic and errors; audit events answer accountability questions and must be protected from tampering.

Write the audit row in the same transaction as the business change. If the update rolls back, the audit event must roll back too. If the audit insert fails, the business change should fail rather than commit without a trail. That is a deliberate availability trade-off: regulated mutations are not allowed to proceed unaudited.

BEGIN;
UPDATE customers SET billing_address = $1 WHERE id = $2;
INSERT INTO audit_events (actor_id, action, entity_type, entity_id, after, request_id)
VALUES ($3, 'customer.billing_address.update', 'customer', $2, $4, $5);
COMMIT;

Protect history

Application code should insert and read audit events, not update or delete them. Grant only the required privileges and keep a separate retention role for archival. Encrypt backups and restrict access to the audit table more tightly than to business tables. If the audit trail is legally sensitive, consider hash chaining or a write-once external store.

Design the payload

Store the fields that answer the question, not the entire row by default. Full snapshots are convenient but increase exposure of personal data and make retention harder. Redact secrets, use stable entity identifiers, and include the request id so the event can be correlated with logs and traces without storing raw headers.

Verify the transaction boundary

Expected outcome: a successful update has exactly one audit row with the actor, entity id, and request id, while a deliberately failed update has none. Confirm by forcing the business update to fail after the audit insert and querying the table; the count must remain unchanged. Also confirm that the application role can insert and select but receives a permission error on update or delete.