Audit Logging System Design for Technical Founders

Audit logging sits at the intersection of security, compliance, and product value. It is one of the most underspecified systems in early-stage SaaS products — founders typically add it as an afterthought, resulting in audit logs that do not capture the right events, cannot be queried efficiently, and fail to satisfy compliance requirements when they matter most.

Technical founders who design their audit logging system deliberately from the start avoid expensive retrofits later. This guide covers the full system design: event schema, immutability guarantees, storage architecture, query patterns, and how to expose audit logs to customers as a product feature.

Audit Log Event Schema Design

The schema is the most consequential audit logging decision. A poorly designed schema requires expensive migrations later; a well-designed schema supports a wide range of query patterns and compliance requirements without modification.

Required Fields

{
  "id": "uuid-v4",                    // unique event identifier
  "timestamp": "ISO8601-with-tz",     // when the event occurred (server time)
  "actor_id": "user_id",              // who performed the action
  "actor_type": "user|system|api",    // actor classification
  "actor_ip": "ip-address",           // source IP for security investigations
  "actor_user_agent": "string",        // client information
  "tenant_id": "org_id",              // which organization owns this event
  "resource_type": "string",           // what type of resource was affected
  "resource_id": "string",             // specific resource identifier
  "action": "string",                  // what happened (read/create/update/delete)
  "outcome": "success|failure",        // result of the action
  "metadata": "jsonb",                 // action-specific contextual data
  "previous_state": "jsonb|null",      // state before the action (for updates)
  "new_state": "jsonb|null"            // state after the action (for updates)
}

Action Naming Convention

Use a consistent resource.action naming pattern: user.created, document.deleted, permission.granted, api_key.rotated. This allows prefix-based filtering ("show me all user events") without custom logic. Avoid action names that require a lookup table to interpret — they should be readable in a raw log query.

State Capture for Updates

For mutation events (updates, permission changes), capture both previous_state and new_state. This enables point-in-time reconstruction and makes the audit log genuinely useful for investigations rather than just recording that something changed. Be selective about what you include — not the entire object, but the specific fields that changed and are relevant to auditability.

Immutability Architecture

Audit logs must be immutable — once written, they cannot be modified or deleted by the application. This is a compliance requirement for most regulated industries and a security requirement in all industries: an attacker who can modify audit logs can cover their tracks.

Database-Level Immutability

Revoke UPDATE and DELETE privileges from the application database user on the audit log table. The application user can INSERT but not modify existing rows. This prevents application-level bugs and most attack vectors from corrupting audit logs.

-- Create audit table
CREATE TABLE audit_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ...
) WITH (autovacuum_enabled = false);  -- Prevents row cleanup

-- Revoke modification permissions
REVOKE UPDATE, DELETE, TRUNCATE ON audit_events FROM app_user;
GRANT INSERT, SELECT ON audit_events TO app_user;

Cryptographic Chaining

For high-security requirements, implement cryptographic chaining: each audit event includes a hash of the previous event's content. Any modification to a historical record breaks the chain, making tampering detectable. This pattern mirrors blockchain append-only structures but applied to a traditional database.

-- Each record includes a hash chain
ALTER TABLE audit_events ADD COLUMN chain_hash TEXT;
-- chain_hash = SHA256(previous_chain_hash || event_data)

Write-Ahead Log (WAL) Archival

For the highest immutability guarantee, archive your database WAL to immutable storage (S3 Object Lock, Azure Immutable Blob Storage) in real time. This creates a record of every database operation that is stored independently of the database itself — even a compromised database server cannot retroactively alter what the WAL recorded.

Storage Architecture

Single Database (Pre-Seed to Seed Stage)

At low volume, store audit events in your primary database in a dedicated audit schema. Separate schema prevents audit log queries from affecting application query performance. Add appropriate indexes: (tenant_id, timestamp DESC) for tenant-scoped queries, (actor_id, timestamp DESC) for user activity queries, (resource_type, resource_id, timestamp DESC) for resource history queries.

Separate Audit Database (Series A+)

When audit log write volume exceeds 10% of primary database write throughput, move to a dedicated audit database. Read replicas for query workloads. TimescaleDB or ClickHouse are appropriate choices — both are optimized for append-only time-series data and provide efficient range query performance on timestamp columns.

Tiered Retention

Customer-Facing Audit Log as a Product Feature

Exposing audit logs to customers turns a compliance requirement into a product differentiator. Enterprise buyers specifically evaluate audit log quality when selecting vendors — the ability to see who did what and when is a trust signal that supports the buying decision.

What to Expose

Surface audit events at two levels:

Customer Query Patterns

The most common customer queries for audit logs:

Design your UI and API to make these queries straightforward without requiring customers to understand your underlying data model.

Frequently Asked Questions