Most audit-log implementations are fine until the table crosses ten million rows, then everything degrades at once. This lab walks through a Laravel-shaped audit log designed to stay queryable at 100M+ rows on commodity MySQL: append-only writes, monthly partitions, the right indexes, and a query layer that does not collapse the database when finance asks "who changed this invoice."
The full course extends this audit-log pattern to per-tenant partitioning, retention policies, and the broadcasting layer the same SaaS shipped in production.
The standard "throw it in audit_logs and add an index" approach works until row 10 million, then writes start blocking reads, the index fragments, and the support team's "who changed this invoice last Tuesday" query takes 40 seconds. The fix is not "add more indexes." The fix is to design the table for the access pattern from day one: append-only, partitioned by month, with two indexes that match the only two queries you will ever run.
CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT, occurred_at DATETIME(6) NOT NULL, actor_id BIGINT UNSIGNED NULL, actor_type VARCHAR(60) NOT NULL, entity_id BIGINT UNSIGNED NOT NULL, entity_type VARCHAR(60) NOT NULL, action VARCHAR(40) NOT NULL, payload JSON NULL, PRIMARY KEY (id, occurred_at), KEY idx_entity (entity_type, entity_id, occurred_at), KEY idx_actor (actor_type, actor_id, occurred_at))ENGINE=InnoDBPARTITION BY RANGE (TO_DAYS(occurred_at)) ( PARTITION p_2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')), PARTITION p_2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')), PARTITION p_max VALUES LESS THAN MAXVALUE);
Three things that look small but are not:
The primary key is composite. (id, occurred_at). MySQL requires every partitioning column to be in every unique key. Drop occurred_at from the PK and partitioning fails on ALTER.
The two indexes are (entity_type, entity_id, occurred_at) and (actor_type, actor_id, occurred_at). These match the only two real queries: "show me the history of this entity" and "show me what this user did." Any other index is speculative bloat.
The payload is JSON, not a normalised set of field_name/old_value/new_value rows. Auditing is not your data warehouse. You write once and read rarely; flat denormalised JSON is correct here.
public function up(): void{ DB::statement(<<<'SQL' CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT, occurred_at DATETIME(6) NOT NULL, actor_id BIGINT UNSIGNED NULL, actor_type VARCHAR(60) NOT NULL, entity_id BIGINT UNSIGNED NOT NULL, entity_type VARCHAR(60) NOT NULL, action VARCHAR(40) NOT NULL, payload JSON NULL, PRIMARY KEY (id, occurred_at), KEY idx_entity (entity_type, entity_id, occurred_at), KEY idx_actor (actor_type, actor_id, occurred_at) ) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(occurred_at)) ( PARTITION p_max VALUES LESS THAN MAXVALUE ) SQL);}
We start with a single p_max partition and add monthly partitions via a scheduled command (next step). Trying to define every partition upfront is the wrong shape: you would need a migration every month forever.
A scheduled job runs on the first of every month and adds the next partition before any rows can land in p_max. Below is a compact version that lives in app/Console/Commands/RotateAuditPartitions.php:
public function handle(): int{ $next = now()->addMonth()->startOfMonth(); $name = 'p_' . $next->format('Y_m'); $boundary = $next->copy()->addMonth()->format('Y-m-d'); DB::statement("ALTER TABLE audit_logs REORGANIZE PARTITION p_max INTO ( PARTITION {$name} VALUES LESS THAN (TO_DAYS('{$boundary}')), PARTITION p_max VALUES LESS THAN MAXVALUE )"); return self::SUCCESS;}
Two early-morning failure modes are worth knowing. First, REORGANIZE is blocking on MySQL 5.x; on 8.0 with ALGORITHM=INPLACE it is not, but it locks for metadata briefly. Schedule it in your low-traffic window. Second, if you skip a month (job failure, server down), the next run is fine: it rebuilds p_max from whatever boundary it is at.
The writer is a single Eloquent observer plus a queue job. The observer captures the change atomically; the queue job persists it. Splitting them keeps the request path fast.
class AuditedObserver{ public function updated(Model $model): void { if (! $model->isDirty()) return; AuditLog::dispatch( actorId: auth()->id(), actorType: auth()->user() ? 'user' : 'system', entityId: $model->getKey(), entityType: $model->getMorphClass(), action: 'updated', payload: $model->getDirty(), ); }}
Notice the DATETIME(6) precision in the timestamp. Microseconds matter when two writes land in the same millisecond and you need a deterministic ordering for the entity-history view.
Both should return in single-digit milliseconds at any scale. If they do not, the index is wrong, not the partitioning. EXPLAIN the query and confirm Using index appears in the Extra column.
This is the table and the writer. It is not a retention policy (deleting partitions older than X), not a per-tenant scoping (every row carries an implicit tenant_id in production), and not a broadcast layer (so the admin UI updates live when an audit row lands). The full Multi-tenant Laravel course covers all three on top of this exact table.