Audit logs that actually scale

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 we landed on after two production weeks spent recovering from getting it wrong.

What an audit log is for#

Three audiences, each with different demands:

1. Compliance. Auditors need a tamper-evident record of who did what. They will never query it directly; they will ask for an export.
2. 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.
3. 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 worked beautifully for the first few months. Then four things hit at once:

1. The table crossed 400 million rows.
2. A "show me last week" query started running for 12 seconds.
3. A "DELETE FROM audit_logs WHERE created_at < ?" maintenance job we ran weekly took down replication for forty minutes.
4. A customer asked us to delete one user's audit trail (GDPR right-to- erasure). Doing it required a row-by-row delete that locked the table for six minutes, during which every write blocked.

We needed 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 400M rows; 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#

These numbers are with about 800 million rows total, across 14 monthly partitions. 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.