An audit log table is a write-heavy, read-rare, retention-long beast. It will be your largest table within a year, your slowest to query, the first one customers ask to filter, and the last one anyone wants to touch. Get the shape wrong and it eats your database. Get it right and it disappears.
This essay is about the shape that holds up, and why the naive version fails.
What an audit log is for#
Three audiences, each with different demands:
- Compliance. Auditors need a tamper-evident record of who did what. They will never query it directly; they will ask for an export.
- Support. Customer support reads "what happened to invoice 4729 last Tuesday." They want a tenant-scoped, entity-scoped, time-windowed view. Five queries per day per support agent, expected to return under a second.
- Detective. When something is wrong, an engineer wants "show me every action this user took in the last 24 hours" or "show me every write to the contacts table." Rare, but unforgiving on response time when needed.
Optimising for one without thinking about the others gives you a log that disappoints all three.
What broke at month nine#
Our first audit log was a wide table on the same database as everything else:
CREATE TABLE audit_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED,
action VARCHAR(120) NOT NULL,
resource VARCHAR(120) NOT NULL,
resource_id BIGINT UNSIGNED,
payload JSON,
ip VARCHAR(45),
user_agent VARCHAR(255),
created_at TIMESTAMP NOT NULL
);
CREATE INDEX idx_audit_tenant_created ON audit_logs (tenant_id, created_at);It works well at first. Then four things tend to hit at once:
- The table grows to hundreds of millions of rows.
- A "show me last week" query gets slower and slower.
- A "DELETE FROM audit_logs WHERE created_at < ?" maintenance job can stall replication.
- A customer asks to delete one user's audit trail (GDPR right-to- erasure). Doing it requires a row-by-row delete that can lock the table, during which every write blocks.
You need a new shape. Here it is.
The shape that scales#
Three changes, each independently load-bearing:
1. Partition by month#
Time-range partitioning, on created_at, in monthly partitions.
Postgres declarative partitioning or MySQL PARTITION BY RANGE.
CREATE TABLE audit_logs (
id BIGINT UNSIGNED AUTO_INCREMENT,
tenant_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED,
action VARCHAR(120) NOT NULL,
resource VARCHAR(120) NOT NULL,
resource_id BIGINT UNSIGNED,
payload JSON,
ip VARCHAR(45),
user_agent VARCHAR(255),
created_at TIMESTAMP NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p_2026_05 VALUES LESS THAN (UNIX_TIMESTAMP('2026-06-01')),
PARTITION p_2026_06 VALUES LESS THAN (UNIX_TIMESTAMP('2026-07-01')),
-- ...
PARTITION p_future VALUES LESS THAN MAXVALUE
);Two wins immediately:
- Time-windowed queries prune to a single partition. "Last week" no longer scans the whole table; it scans last month's partition.
- Retention deletes become
ALTER TABLE DROP PARTITION p_2025_05, which is instant and does not touch replication. The forty-minute outage from before becomes a metadata change.
2. Append-only at the application layer#
The table has no UPDATE or DELETE from application code. Ever.
We enforce this with a database trigger that raises on any non-INSERT
operation against the audit table:
CREATE TRIGGER audit_logs_no_updates
BEFORE UPDATE ON audit_logs
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'audit_logs is append-only';
END;This is not theatre. The trigger has caught a "fix the user_id" hotfix
twice and a forgotten update() call in a service three times. Each
of those was a near-miss for compliance.
3. Async write through a buffered queue#
In the broken version, every write was a synchronous insert in the same transaction as the original action. Even at 1ms per insert, on high-throughput tenants this added up. We moved to:
class AuditLogger {
public function log(string $action, $resource, array $payload = []): void {
$entry = [
'tenant_id' => app(TenantContext::class)->id(),
'user_id' => auth()->id(),
'action' => $action,
'resource' => is_object($resource) ? class_basename($resource) : $resource,
'resource_id'=> is_object($resource) ? $resource->id : null,
'payload' => json_encode($payload),
'ip' => request()->ip(),
'user_agent' => substr((string) request()->userAgent(), 0, 255),
'created_at' => now()->toDateTimeString(),
];
$this->queue->push($entry);
}
}The queue is a Redis list flushed every 500ms or every 1000 entries (whichever first) by a worker that does a single multi-row insert. Latency on the hot path drops to microseconds. Insert throughput goes up an order of magnitude because we batch.
Querying it without melting it#
Three indexes, all leading with tenant_id because audit reads are
always tenant-scoped:
-- Per-resource-instance lookups (the support query)
CREATE INDEX idx_audit_tenant_resource ON audit_logs
(tenant_id, resource, resource_id, created_at);
-- Per-user lookups (the detective query)
CREATE INDEX idx_audit_tenant_user ON audit_logs
(tenant_id, user_id, created_at);
-- Time-window scans (the auditor export)
-- Already covered by partitioning + (tenant_id, created_at) primary key tail.Notice: there is no full-text index on payload. We do not query JSON
contents. If we ever need to ("which payloads contained this customer
email"), we ship a separate ETL into a search index. Mixing audit
storage with search lookup on the same table burns the budget for both.
What "GDPR delete" means here#
Right-to-erasure on an append-only log is awkward. We do not delete
rows. We redact them: a separate audit_log_redactions table
records (audit_log_id, redacted_at, reason). A redacted row's
payload is replaced with {"redacted": true} via a one-shot
maintenance job that runs against the specific id, on the specific
partition, at low traffic times. The append-only invariant survives
because we never delete; we only rewrite the payload of a row we
know exists.
This is a defensible legal position; we run it past counsel before we ship it; your jurisdiction may differ. The point is that "we use a log" does not exempt you from data-protection law.
What the dashboard looks like#
| View | Expected cost | What it does |
|---|---|---|
| Activity for one invoice | Fast | Per-resource read against idx_audit_tenant_resource |
| Last 24 h for one user | Fast | Per-user read against idx_audit_tenant_user |
| All last week, this tenant | Moderate | Single-partition scan |
| Auditor export, last quarter | Slower | Few-partition stream to S3 |
| Retention drop | Near-instant | ALTER TABLE DROP PARTITION |
The shape, not the index strategy, is what makes it fast.
What's next#
The next part of the multi-tenant series jumps to the application
layer: how we structure permission checks so that a missing
@can('edit', $resource) cannot quietly let a tenant edit another
tenant's data. Series page tracks the rest.














