Nearly every business application grows an audit log, and nearly every one of them is write-only. It fills up for three years, reaches forty gigabytes, gets excluded from backups because it is slowing them down, and is opened exactly twice — both times by somebody who gives up after ten minutes and goes to ask a colleague what happened instead.

That is a failure of design, not of discipline. An audit log is a reporting feature that happens to be written by other features, and if you build it as a dump of model changes you get a dump of model changes. This is how we built the one in Happy Tracker, which gets opened by real administrators and answers their questions in one query.

Start from the question, not the schema

Before writing a migration, write down the questions the log has to answer. Ours came from actual support conversations, and there were only four kinds:

• Who changed this person’s hourly rate, and when? A payroll dispute. Somebody’s invoice is wrong and nobody admits to editing anything.
• What happened to this record? A leave request that was approved and is now rejected. An invoice that was sent twice.
• What did this administrator do last month? Usually asked after somebody leaves, or after a permission is discovered to be wider than intended.
• Who deleted this? The one that arrives as an emergency, with an hour of somebody’s day already lost.

Every one of those is a filter on who, what it happened to, or what kind of thing happened, narrowed by date. That is the whole schema. Anything the table holds that does not serve one of those three filters is weight.

If you cannot name three questions your audit log will answer, you are not building an audit log. You are building a table that makes people feel better about deletions.

The table

Schema::create('audit_logs', function (Blueprint $table) {
    $table->id();
    $table->foreignId('organization_id')->constrained();
    $table->foreignId('actor_id')->nullable()->constrained('users');
    $table->foreignId('impersonated_by')->nullable()->constrained('users');
    $table->string('action', 64);                 // noun.verb
    $table->nullableMorphs('subject');            // subject_type, subject_id
    $table->json('details')->nullable();
    $table->string('ip', 45)->nullable();
    $table->timestamp('created_at')->useCurrent();

    $table->index(['organization_id', 'subject_type', 'subject_id', 'created_at']);
    $table->index(['organization_id', 'actor_id', 'created_at']);
    $table->index(['organization_id', 'action', 'created_at']);
});

Three things are deliberately absent, and each absence is a decision.

There is no updated_at. An audit row is a fact about a moment. If anything in your application can modify one, the log is not evidence of anything. Make the model insert-only and revoke UPDATE on the table if your hosting lets you.

There is no before-and-after snapshot of the model. More on that below, because it is the decision that determines whether the table is usable in two years.

There is no free-text message column. The moment one exists, half your entries become sentences that cannot be grouped, counted or filtered. The structure is the message.

The impersonation column

impersonated_by looks like a niche field until the first time an owner views the application as one of their staff to reproduce a bug, and changes something while they are in there. Without that column, the log says the staff member did it. The log is now actively lying, which is worse than having no log.

Any feature that lets one person act as another needs a second actor column. There are no exceptions worth making.

Name actions as noun dot verb

This is the smallest decision in the design and it has the largest effect on whether anybody can use the result.

// Ungroupable. Every one is a special case.
'updated_the_rate', 'user rate change', 'RATE_UPDATED', 'edit'

// Groupable. Prefix-searchable. Reads correctly in a UI.
'member.rate_changed'
'member.role_changed'
'member.removed'
'leave.approved'
'leave.rejected'
'leave_type.archived'
'invoice.sent'
'project.archived' 

With a consistent prefix, “show me everything that happened to members” is action LIKE ’member.%’ and it uses the index. Without it, that query is a list of twelve string literals that somebody has to maintain, and it will be wrong within a month because a new action was added and nobody updated the list.

Two rules keep the vocabulary healthy. The verb is past tense, because the log records things that have already happened. And the noun is the business concept, not the table name — leave.approved, not leave_requests.updated. The person reading the log is thinking about leave, not about your schema.

Keep the full list in one class so it can be enumerated for a filter dropdown and so a typo becomes a failing test rather than a row that never matches anything:

final class AuditAction
{
    public const MEMBER_RATE_CHANGED = 'member.rate_changed';
    public const LEAVE_APPROVED      = 'leave.approved';
    public const INVOICE_SENT        = 'invoice.sent';
    // ...
}

Record where the intent is

The appealing implementation is a model observer. Attach it once, and every create, update and delete anywhere in the application is logged with no further effort. It is five lines and it covers everything.

It is also close to useless, and the reason is worth understanding.

An observer fires on save(). It does not know whether that save came from an administrator approving a leave request, from the employee correcting a typo in the reason field, from a nightly reconciliation job, or from a database seeder during a deploy. It sees that status went from pending to approved and records updated.

The controller knows all of it. It has the authenticated user, the request, the route, the reason field the approver typed, and — crucially — the intent, because the intent is the name of the method:

public function approve(LeaveRequest $leave, ApproveLeaveRequest $request)
{
    $this->authorize('approve', $leave);

    DB::transaction(function () use ($leave, $request) {
        $leave->approve($request->user(), $request->note);
        $leave->user->leaveBalance()->deduct($leave->days);
    });

    Audit::record(AuditAction::LEAVE_APPROVED, $leave, [
        'days'     => $leave->days,
        'type'     => $leave->leaveType->name,
        'note'     => $request->note,
        'balance'  => $leave->user->leaveBalance()->remaining(),
    ]);

    return new LeaveResource($leave);
}

That single row tells a reader everything they will want to know, in the words they would use. An observer could not have produced it at any price, because the information does not exist below the controller.

There is a real cost: you have to remember to call it, and a missed call is a missing entry. Accept that trade. An incomplete log of meaningful events beats a complete log of meaningless ones. Nobody has ever been helped by discovering that row 4,102 was updated at 14:33.

The actions people forget to log

The obvious candidates — create, approve, delete — get logged because somebody asked for them. The ones that generate the most confused support tickets are settings changes, because nobody remembers making them and the effect shows up days later.

• Permission and role changes. The first question after any “why can this person see that?”
• Organisation settings. Screenshot frequency, idle timeout, whether web clock-in is allowed. Somebody toggled it, the team noticed a week later, and by then nobody admits to the toggle.
• Plan and billing changes. Including the automated ones, with the actor left null and the action named for the system.
• Failed authorisation attempts on anything sensitive. Not every 403, just the ones on money and people.

Where the observer does earn its place

One case: deletions. A delete can happen from a controller, a cascade, a cleanup command or a console session, and the fact that a record no longer exists is worth capturing regardless of intent. A deleting observer that records the model type, the id and a short identifying label is genuinely useful, and it is the entry people look for most often in an emergency.

What goes in the details blob

This is where audit tables go to die. The instinct is to store $model->getOriginal() and $model->getAttributes(), because it is two lines and it feels thorough.

Do the arithmetic. A time entry has fourteen columns. Store both versions as JSON and every edit costs roughly a kilobyte. A modestly busy tenant generates tens of thousands of edits a month. Multiply by a few hundred tenants and two years, and you have a table nobody can query, on a disk nobody wants to pay for.

Worse, it is unreadable. Nobody wants to diff two JSON objects in their head to discover that the rate changed from 900 to 1,200.

Store the fields that matter to the question, already resolved:

Audit::record(AuditAction::MEMBER_RATE_CHANGED, $member, [
    'from'     => 900,
    'to'       => 1200,
    'currency' => 'INR',
    'effective_from' => '2026-10-01',
]);

Four fields, about eighty bytes, and it renders directly as a sentence: “Priya changed Ram’s rate from ₹900 to ₹1,200 per hour, effective 1 October.” That is what an administrator wants to read.

Two things must never enter the blob: passwords, tokens or card details, obviously, but also anything you would have to redact later. Audit rows are immutable by design, which means a mistake in what you store there is permanent. The same discipline that applies to structured application logs applies here, with the added constraint that you cannot rotate the file away.

Keeping it off the critical path

An audit write is an extra INSERT on a request that was already doing work. One is fine. The trouble starts when a page performs three audited actions and the log adds three round trips, or when the log table is on a slower disk, or when its indexes get large enough that inserts start to cost.

The pattern that costs nothing: buffer during the request, flush after the response has been sent.

class AuditRecorder
{
    private array $buffer = [];

    public function record(string $action, ?Model $subject, array $details = []): void
    {
        $this->buffer[] = [
            'organization_id' => app('tenant')->id,
            'actor_id'        => auth()->id(),
            'impersonated_by' => session('impersonated_by'),
            'action'          => $action,
            'subject_type'    => $subject ? $subject->getMorphClass() : null,
            'subject_id'      => $subject?->getKey(),
            'details'         => $details ? json_encode($details) : null,
            'ip'              => request()->ip(),
            'created_at'      => now(),
        ];
    }

    public function flush(): void
    {
        if (! $this->buffer) {
            return;
        }

        try {
            AuditLog::insert($this->buffer);      // one query, N rows
        } catch (\Throwable $e) {
            Log::error('audit flush failed', ['error' => $e->getMessage()]);
        } finally {
            $this->buffer = [];
        }
    }
}

Call flush() from terminable middleware. Three audited actions become one insert, after the user already has their response.

The try block is the part to think about carefully. Swallowing the failure means an audit row can be lost without anyone knowing, which for a convenience log is the right trade — a failed log write should never roll back a leave approval that genuinely happened.

If the log is a compliance requirement rather than a convenience, invert that entirely: write inside the same transaction as the change, let it throw, and let the whole operation fail. Then the record and its audit entry are atomic and cannot disagree. Which of the two you are building is a business decision, and it is worth making it explicitly rather than discovering it during an inspection.

The subject that no longer exists

A polymorphic subject is a pointer, and pointers dangle. The single most-wanted audit entry — the one recording a deletion — is by definition the one whose subject can no longer be loaded. Render that naively and the most important row in the table displays as “deleted (unknown)”.

The fix is to denormalise a label at write time. One extra key in the details blob:

Audit::record(AuditAction::LEAVE_TYPE_ARCHIVED, $leaveType, [
    'label' => $leaveType->name,          // 'Annual Leave' - frozen here
    'used_by' => $leaveType->requests()->count(),
]);

The label is captured as it was at the moment of the action, which is also more truthful than resolving it later. If somebody renames a project from “ML Direct” to “ML Phase 2”, the audit row from March should still say what it said in March. A live join would quietly rewrite history every time a name changed.

This is the general principle for anything in the details blob: store the resolved value, not a reference to one. The blob is a photograph, not a window.

Timestamps and who is reading them

Store created_at in UTC like everything else, and render it in the organisation’s timezone with the offset visible. An audit log is read during arguments, and “14:33” with no zone attached is the sort of detail that turns a five-minute check into an hour.

Show the full timestamp to the second. Relative times — “3 days ago” — are pleasant in a notification feed and actively unhelpful here, because the reader is trying to line this log up against a payroll run, an email, or somebody’s recollection of a Tuesday.

Testing it

An audit log is easy to test and almost never tested, which is why so many of them have gaps nobody notices until the gap is the thing being looked for.

public function test_approving_leave_writes_one_audit_row(): void
{
    $leave = LeaveRequest::factory()->pending()->create();

    $this->actingAs($this->manager)
        ->postJson("/api/leave/{$leave->id}/approve", ['note' => 'ok'])
        ->assertOk();

    $this->assertDatabaseHas('audit_logs', [
        'action'       => AuditAction::LEAVE_APPROVED,
        'subject_type' => LeaveRequest::class,
        'subject_id'   => $leave->id,
        'actor_id'     => $this->manager->id,
    ]);
}

Write one of these for every action that touches money, permissions or someone’s time record. They are three lines each, they run in milliseconds, and they fail the day somebody refactors a controller and drops the Audit::record call in the process — which is exactly how gaps appear.

Add one more that nobody writes: assert that every constant in AuditAction has a formatter, or at least renders without throwing. Otherwise the first time a new action appears in the interface is in front of a customer.

Retention, and the size nobody plans for

An audit table only grows. Plan the ending at the start.

• Partition or prune by date. Monthly partitions, or a scheduled job that deletes rows older than the retention period in small batches. Deleting two million rows in one statement will lock the table at exactly the wrong moment.
• Retain by action, not uniformly. session.login is worth ninety days. member.rate_changed and invoice.sent are worth seven years, because they relate to money.
• Never log reads. Recording every page view multiplies the table by a hundred and answers nothing. The exception is deliberate: viewing somebody’s salary, exporting a member list, downloading a full backup. Those are events, not page views.
• Export before pruning. Compressed NDJSON to object storage costs almost nothing and means “we delete after one year” does not mean “it is gone”.

The screen, which is the actual product

A log nobody can read is not an audit log. The table is the easy half.

Two entry points cover almost everything people need. The first is a history tab on the record itself — every audit row whose subject is this leave request, this member, this invoice, newest first. This is where ninety per cent of real usage happens, because people arrive at the log already knowing which record they are asking about.

The second is a filterable list with three controls: actor, action prefix, and date range. Those three map exactly onto the three indexes, so every combination is fast, and there is nothing in the interface that produces a slow query.

Render each row as a sentence, not as columns of raw data. That means one small formatter per action:

public function describe(AuditLog $log): string
{
    $d = $log->details;

    return match ($log->action) {
        AuditAction::MEMBER_RATE_CHANGED =>
            sprintf('changed the hourly rate from %s to %s', $d['from'], $d['to']),
        AuditAction::LEAVE_APPROVED =>
            sprintf('approved %d days of %s leave', $d['days'], $d['type']),
        default => str_replace(['.', '_'], [' ', ' '], $log->action),
    };
}

It is tedious and it is what makes the feature worth having. The default arm means a new action always renders as something readable even before anyone writes its formatter.

What to do on Monday morning

1. Write down three questions your audit log must answer, taken from real support conversations rather than imagination. If you cannot find three, do not build one yet.
2. Query your existing log for the most common action value. If it is updated or saved, you have an observer log, and it is telling you nothing.
3. Check the size: SELECT COUNT(*), AVG(LENGTH(details)) FROM audit_logs. An average above about 400 bytes means you are storing model dumps.
4. Add impersonated_by if any feature lets one user act as another. Until then, some of your rows name the wrong person.
5. Add the two composite indexes and run EXPLAIN on the history query for a single record. It should be a range scan of a few rows, never a table scan.
6. Pick a retention period per action group and schedule a batched prune. Do it now, at ten million rows, rather than later at four hundred million.

The test of an audit log is not whether it records everything. It is whether an administrator with a question and no technical help can open it, find the answer in under a minute, and close it again. Everything in the design above exists to serve that single moment, and the features that do not serve it — the model snapshots, the read logging, the free-text messages — are the same features that make the table too big to open.