Every serious application eventually needs audit logs. Who did what, when, from where, and why. The default approach — “log to a file, done” — fails at the first compliance audit. This article covers what an audit log should look like and how to build it without regret.

What an audit log is

Audit log is not the same as application log. Application logs are for debugging; they can be verbose, contain secrets, get deleted after a week. Audit logs are for reconstructing who did what — they need to be durable, tamper-evident, and queryable for years.

A useful mental rule: if a security team or regulator might ask “did user X do Y?”, the answer comes from the audit log.

What to capture

For every security- or business-sensitive action:

  • Who — user ID, service identity, API key ID
  • What — action name (user.deleted, order.refunded, permissions.granted)
  • When — server time, precise to milliseconds
  • Where — IP address, user agent, region
  • Target — what entity was affected (target user ID, order ID, etc.)
  • Before / After — diff of what changed (for mutations)
  • Context — request ID, session ID, auth method used
  • Reason — if provided (for actions requiring justification)

Don’t capture:

  • Passwords, tokens, API keys
  • Full credit card numbers
  • Sensitive PII unless required by regulation

The data model

CREATE TABLE audit_events (
    id             UUID PRIMARY KEY,
    occurred_at    TIMESTAMPTZ NOT NULL,
    actor_type     TEXT NOT NULL,        -- 'user', 'service', 'api_key', 'system'
    actor_id       TEXT NOT NULL,
    action         TEXT NOT NULL,        -- 'user.created', etc.
    target_type    TEXT,
    target_id      TEXT,
    ip_address     INET,
    user_agent     TEXT,
    request_id     TEXT,
    before_state   JSONB,
    after_state    JSONB,
    metadata       JSONB
);

CREATE INDEX idx_audit_occurred ON audit_events (occurred_at DESC);
CREATE INDEX idx_audit_actor ON audit_events (actor_type, actor_id, occurred_at DESC);
CREATE INDEX idx_audit_target ON audit_events (target_type, target_id, occurred_at DESC);
CREATE INDEX idx_audit_action ON audit_events (action, occurred_at DESC);

Indexes on actor, target, action + time are the common query patterns. Time-based partitioning for large datasets.

Capturing events

Three approaches, used together:

Application-level

For explicit business actions, emit from code:

@Service
public class UserService {
    private final UserRepository repo;
    private final AuditLogger audit;

    @Transactional
    public void deleteUser(UUID userId, AuthContext ctx) {
        User user = repo.findById(userId).orElseThrow();
        repo.softDelete(userId);
        
        audit.log(AuditEvent.builder()
            .action("user.deleted")
            .actor(ctx.userId(), "user")
            .target(userId, "user")
            .beforeState(user)
            .context(ctx)
            .build());
    }
}

Database-level

For generic DB changes, use triggers or CDC (Debezium):

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_events(id, occurred_at, actor_id, action, target_type, target_id, before_state, after_state)
    VALUES (
        gen_random_uuid(), now(),
        current_setting('app.current_user', true),
        TG_OP || ' ' || TG_TABLE_NAME,
        TG_TABLE_NAME, NEW.id::text,
        row_to_json(OLD), row_to_json(NEW)
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Captures everything but lacks business context. Pair with app-level for the “why”.

Access-level

HTTP interceptor logs every request with user + action mapping. Useful for read auditing in regulated contexts.

Making it tamper-evident

Auditors ask: “How do we know no one edited the logs?” Options:

Append-only DB user. Audit log writer has INSERT-only privileges. No UPDATE or DELETE.

Hash chaining. Each row includes hash of the previous row. Tampering requires rewriting everything after.

audit_row.hash = sha256(prev_row.hash + audit_row.fields)

External immutable storage. Write audit events to object storage with retention-lock. Original copy in DB for query; immutable copy for verification.

For most commercial uses, append-only DB user is enough. Financial / healthcare might require stronger.

Querying

The main use cases:

  • Who did X? Query by action + target.
  • What has user Y done? Query by actor.
  • What happened to entity Z? Query by target.
  • Compliance report. All actions in date range, filtered by type.

With the indexes above, all of these are fast up to hundreds of millions of rows.

For larger datasets, archive old data to Elasticsearch or analytics DB; keep recent data in Postgres for active queries.

Retention

Depends on regulation:

  • GDPR: retention proportional to purpose
  • SOX: 7 years
  • HIPAA: 6 years
  • PCI: 1 year minimum
  • General business: 2-3 years is common

Design for longest required retention + extra buffer. Move old data to cheaper storage, not delete.

Reading, not just writing

Many audit logs are write-only — nobody reads them until an incident. That’s fine for worst-case, but:

  • Real-time dashboards. Suspicious activity should be visible immediately.
  • Alerts. “User X accessed Y after hours” triggers a review.
  • User-facing history. Users see their own action history in the UI.

A read-useful audit log prevents incidents. A write-only one documents them after the fact.

Common mistakes

Logging everything at one level. No distinction between “logged in” (routine) and “permissions escalated” (sensitive). Tag severity.

Swallowing exceptions. Audit write fails; business operation succeeds. Now there’s an action without audit. Inside the same transaction = fails together.

PII in audit logs. “User Alice deleted user Bob’s account” becomes a privacy request target. Use IDs, not names, where possible.

Audit log in the same table as business data. Grow audit rows mixed with business rows. Separate table minimum; separate DB ideal.

Never querying it. If nobody ever reads it, you won’t notice when it breaks.

Sampling and filtering

High-volume actions (page views, API calls) shouldn’t go to the audit log verbatim — would overwhelm. Either:

  • Log at a higher level (“user started session”) with volume events in app logs
  • Sample (1 in 100 page views) for analytics purposes, not compliance
  • Distinguish security-relevant actions from routine traffic

Closing note

Audit logs are insurance. You don’t appreciate them until you need them — usually during an incident or audit. The patterns above let you produce logs that serve both debugging and compliance, stay readable at scale, and resist tampering. Most teams regret not investing earlier; few regret investing early. Do it once, do it right, and the audit log becomes the most-trusted artifact in your operations toolkit.