Every serious application needs an audit log. Not just for compliance checkboxes, but because the question "what happened and who did it" comes up constantly in production. A customer disputes a charge. A team member claims they never deleted that record. A regulator asks for evidence of access controls. Without a proper audit trail, you are guessing, and guessing does not hold up in court or in a customer support ticket.
We have built audit logging systems for full stack applications handling millions of events daily. The patterns that work are not complicated, but they need to be implemented correctly from the start. Retrofitting audit logging into an existing system is significantly harder than building it in from day one.
What to Log
The first decision is scope. At minimum, every audit log entry should capture who performed the action, what they did, when they did it, where the request came from, and what changed. In practice, this means a schema that includes:
- actor_id: The user or system identity that performed the action
- actor_type: Whether the actor is a user, admin, API key, system process, or automated job
- action: A structured verb like "user.created", "invoice.deleted", "permission.granted"
- resource_type: The entity type affected (user, order, document, setting)
- resource_id: The specific entity ID
- changes: A JSON diff of the before and after state for mutations
- metadata: IP address, user agent, session ID, request ID
- timestamp: Server side UTC timestamp, never trust the client clock
The changes field is the most valuable and most commonly skipped. Logging that someone "updated a user record" is far less useful than logging that they changed the email from alice@old.com to alice@new.com. Store both the old and new values for every field that changed. This is critical for debugging, compliance, and dispute resolution.
A common mistake is logging too little early on and then scrambling to add coverage when an auditor or customer asks for evidence. Log aggressively from the start. Storage is cheap. Answering "we do not have that data" is expensive.
Schema Design for PostgreSQL
For PostgreSQL based systems, which covers most of what we build, the audit log table should be append only. No updates, no deletes. This is a fundamental constraint that ensures the log itself cannot be tampered with.
```sql
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
actor_id uuid,
actor_type text NOT NULL DEFAULT 'user',
action text NOT NULL,
resource_type text NOT NULL,
resource_id text,
changes jsonb,
metadata jsonb DEFAULT '{}',
ip_address inet,
user_agent text,
request_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
```
Index strategy matters for query performance. At minimum, create indexes on (resource_type, resource_id) for "show me everything that happened to this entity" queries, on (actor_id, created_at) for "show me everything this user did" queries, and on (created_at) for time range scans. If you are using BRIN indexes on the timestamp column, you can get excellent compression because audit logs are naturally time ordered.
Partitioning by month or quarter is essential once you cross a few million rows. PostgreSQL's native declarative partitioning works well here. Create partitions automatically with a cron job or pg_partman. Without partitioning, queries against months of audit data slow to a crawl, and dropping old data requires expensive DELETE operations instead of instant partition drops. We discuss related schema strategies in our database schema design guide.
Write Performance
Audit logging cannot slow down the operations it is recording. If inserting an audit log entry adds 50ms to every API request, your users pay the price. There are three approaches to keeping writes fast.
Synchronous inserts into the audit table within the same database transaction as the business operation. This is the simplest approach and guarantees consistency. If the business operation commits, the audit log commits. If it rolls back, the audit log rolls back too. This works well up to moderate throughput because a single INSERT into an append only table with a few indexes is typically under 1ms on PostgreSQL.
Asynchronous writes via a background queue. The application publishes an audit event to a message queue (SQS, Redis streams, or a Kafka topic) and a worker process writes it to the audit table. This decouples write latency from the business operation but introduces the risk of losing events if the queue consumer fails. You need dead letter queues and retry logic to make this reliable.
Database triggers that automatically capture changes without application code. A trigger on each audited table can copy the old and new row values into the audit log. This guarantees coverage (developers cannot forget to add audit logging to a new endpoint) but adds overhead to every write and makes the trigger code a potential bottleneck. We use triggers for critical tables where coverage cannot be optional and application level logging for everything else.
For most applications, synchronous inserts are the right default. Switch to async when write volume makes synchronous inserts a measurable bottleneck, typically above 10,000 audit events per second.
Querying and Presenting Audit Data
An audit log that nobody can search is just expensive storage. The most common query patterns are:
1. Entity history: "Show me everything that happened to order #12345" (filter by resource_type and resource_id, ordered by created_at)
2. User activity: "Show me everything this user did in the last 7 days" (filter by actor_id and time range)
3. Action search: "Show me all permission changes this month" (filter by action pattern and time range)
4. Forensic investigation: "Show me all actions from this IP address" (filter by metadata)
Build a dedicated audit log viewer in your admin panel. It should support filtering by actor, resource, action type, and time range. Display the changes field as a readable diff: field name, old value, new value. This is not a nice to have. Your support team will use this daily, and in our experience building ongoing management systems, a good audit viewer cuts investigation time from hours to minutes.
For API design, expose audit history as a nested resource: `GET /api/orders/12345/audit-log`. This makes it natural for frontend components to fetch and display entity history.
Immutability and Tamper Evidence
The audit log must be tamper evident. If someone with database access modifies log entries, you should be able to detect it. Several techniques help here.
Revoke write permissions. The application should INSERT into the audit table using a role that has INSERT but not UPDATE or DELETE permissions. Separate the admin access that can read everything from the service access that can only append.
Hash chaining. Each audit log entry includes a hash of the previous entry, creating a blockchain style chain. If any entry is modified, the chain breaks and tampering is detectable. This adds complexity but is sometimes required for financial services compliance.
External replication. Stream audit logs to an immutable external store (S3 with Object Lock, or a dedicated log management service like Datadog or Elasticsearch). Even if someone compromises the primary database, the external copy serves as evidence. This is a pattern we implement regularly for clients with compliance requirements, and it fits naturally into a cloud and DevOps strategy.
Retention and Archival
Not all audit data needs to live in your primary database forever. Define retention policies based on regulatory requirements and practical needs. SOC 2 typically requires 1 year. HIPAA requires 6 years. GDPR complicates things by requiring deletion of personal data upon request, which conflicts with immutable audit logs. The standard approach is to pseudonymize actor data in audit logs when a user requests deletion, replacing their identity with a hash while preserving the event record.
For long term storage, archive old partitions to cheaper storage. Export monthly partitions to Parquet files on S3 and drop the partition from PostgreSQL. If you need to query archived data, tools like Athena or DuckDB can scan Parquet files without loading them back into the database.
Common Mistakes
Logging at the wrong layer. If you only log at the API layer, you miss database triggers, background jobs, and admin console actions. If you only log at the database layer, you miss context like which API endpoint triggered the change. The best systems log at both layers and correlate them via request ID.
Not logging failures. Failed login attempts, denied permission checks, and validation errors are often more important for security investigations than successful operations. Log what people tried to do, not just what they accomplished.
Storing raw sensitive data. Audit logs that contain full credit card numbers, passwords, or health records become a liability. Mask sensitive fields in the changes payload before writing. Log that the credit card was updated but store only the last four digits.
If you are building a system that needs production grade audit logging, from schema design to compliance requirements, let us know what you are working on. We have implemented these patterns across industries and can help you get it right from the start.