Skip to content

Audit logs that actually scale

Append-only, partitioned, queryable. The schema that holds up at production scale.

Published

11 min read

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:

  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 works well at first. Then four things tend to hit at once:

  1. The table grows to hundreds of millions of rows.
  2. A "show me last week" query gets slower and slower.
  3. A "DELETE FROM audit_logs WHERE created_at < ?" maintenance job can stall replication.
  4. 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#

ViewExpected costWhat it does
Activity for one invoiceFastPer-resource read against idx_audit_tenant_resource
Last 24 h for one userFastPer-user read against idx_audit_tenant_user
All last week, this tenantModerateSingle-partition scan
Auditor export, last quarterSlowerFew-partition stream to S3
Retention dropNear-instantALTER 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.

Read this article in Arabic · العربية

Share this article

Ali Haitham Yosef

Ali Haitham Yosef, founder of Ali Yosef Software in Damascus. Programming since 2015.

Ali's personal site

Does your project look like what you read?

Write what you need in your own words, and we decide together whether what we offer fits your company.

Start a conversation