An Audit Log You Cannot Quietly Edit

In the last article of The Second Pattern I said that most teams reaching for event sourcing actually want an audit log, and that an append-only table written in the same transaction as your normal state gets you there for a day’s work. I still think that. But I skipped over something, which is that the table is only append-only because everyone agrees not to change it.

UPDATE audit_events
SET metadata = jsonb_set(metadata, '{amount}', '"40.00"')
WHERE team_id = 4 AND seq = 1872;

That runs. Nothing complains, no other row changes, and the next person to read the log sees an event that says forty pounds. The old value has gone, and so has any sign that it was ever there. Anyone with write access to the database can do that, whether it’s a bad migration or a support engineer tidying up what they think is a typo, and your own code can do it on a bad day too.

I’ve built one of these properly, in Laravel, and the chain itself turned out to be the easy bit. It’s a page of code. Nearly everything that went wrong went wrong around it.

A chain, in a page

The idea is old and small. Every row stores a hash of its own contents plus the hash of the row before it. Change any row and its hash no longer matches its contents. Recompute that hash to cover your tracks and the next row no longer links to it, because the next row was chained to the original. To hide one edit you have to rewrite every row after it.

Here’s the table, with one chain per team:

Schema::create('audit_events', function (Blueprint $table) {
    $table->ulid('id')->primary();
    $table->foreignId('team_id')->constrained();
    $table->unsignedBigInteger('seq');
    $table->string('action');
    $table->string('actor')->nullable();
    $table->jsonb('metadata')->default('{}');
    $table->timestampTz('occurred_at', precision: 3);
    $table->timestampTz('recorded_at', precision: 3);
    $table->char('previous_hash', 64)->nullable();
    $table->char('hash', 64);

    $table->unique(['team_id', 'seq']);
});

seq is the chain’s order, and the only one it has. It’s tempting to lean on the ULID or the timestamp, but the hash links rows in one specific sequence, and that sequence needs to be a gapless number you can check.

The hash itself is one line:

hash('sha256', ($previousHash ?? '').AuditCanonical::encode($event));

The first real decision is hiding in encode().

Canonical is something you decide

Whatever you hash, you’re going to recompute later from the stored row, maybe years from now and maybe in a different language, by an auditor who has no particular reason to trust you. If the same event can be encoded two ways, the chain becomes unverifiable and nobody finds out until someone actually checks.

json_encode() on its own isn’t enough. Key order follows insertion order, so ['a' => 1, 'b' => 2] and ['b' => 2, 'a' => 1] hash differently even though they say the same thing. There’s a standard for this, RFC 8785, the JSON Canonicalization Scheme, but I didn’t take a dependency on it. I wrote down a canonical form that covers exactly the fields I hash, and made that the contract:

final class AuditCanonical
{
    public static function encode(AuditEvent $event): string
    {
        return json_encode([
            'action' => $event->action,
            'actor' => $event->actor,
            'metadata' => self::sortKeys($event->metadata),
            'occurred_at' => self::timestamp($event->occurred_at),
            'recorded_at' => self::timestamp($event->recorded_at),
            'seq' => $event->seq,
        ], JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_PRESERVE_ZERO_FRACTION);
    }

    public static function timestamp(DateTimeInterface $moment): string
    {
        return CarbonImmutable::instance($moment)
            ->utc()
            ->format('Y-m-d\TH:i:s.v\Z');
    }

    private static function sortKeys(array $value): array
    {
        $isList = array_is_list($value);
        $value = array_map(fn ($item) => is_array($item) ? self::sortKeys($item) : $item, $value);

        if (! $isList) {
            ksort($value, SORT_STRING);
        }

        return $value;
    }
}

Every decision in there is one you’d have to explain to someone reimplementing it. Object keys are sorted at every depth, but lists keep their order, because in a list the order is the content. Nulls are written out, never dropped, since an absent field and a null one are different statements. Timestamps are UTC at millisecond precision, because the offset a caller happened to send is presentation, not identity. If you have something that’s really a set, like the targets of an event, sort it too, or the same two targets listed the other way round will hash differently.

Write these rules down somewhere a stranger can find them. The code isn’t the spec. The spec is what someone needs in order to check your chain without your code.

Two checks, because there are two crimes

Verification walks the chain in seq order and rebuilds every hash from the stored row:

$previous = null;

foreach (AuditEvent::where('team_id', $team->id)->orderBy('seq')->lazy() as $event) {
    if ($previous !== null && $event->seq !== $previous->seq + 1) {
        return Verification::broken($event->seq, 'missing_event');
    }

    $recomputed = hash('sha256', ($event->previous_hash ?? '').AuditCanonical::encode($event));

    if (! hash_equals($event->hash, $recomputed)) {
        return Verification::broken($event->seq, 'hash_mismatch');
    }

    if ($previous !== null && $event->previous_hash !== $previous->hash) {
        return Verification::broken($event->seq, 'link_mismatch');
    }

    $previous = $event;
}

return Verification::passed($previous?->seq, $previous?->hash);

I report the two failures separately because they mean different things. A hash_mismatch says the row’s own contents were changed. A link_mismatch says the row is perfectly intact but no longer follows from the one before it, which means something was spliced in or swapped out.

Go back to that UPDATE. Run it and leave the hash column alone, and the first check catches it on that row, while everything after it still links happily, because the hash they were chained to was never touched. Now recompute that row’s hash after editing it. The first check passes. The second one fails on the next row, because the next row was chained to the original.

And a broken chain is a finished verification. Record it as one, with the sequence it broke at, and stop. If you throw an exception instead, your queue may well retry it, and a broken chain must never be retried until it looks fine.

Verify what was stored

This is where I actually lost the time.

My first tests passed. They appended a few events, ran verification and got a pass. They also built the hash from the request payload I’d just sent in, not from the row in the database, and when I finally pointed verification at the stored rows every single chain was broken from its very first event.

The cause was a timestamp. I was hashing a value object built from the request, which held 10:15:30.123, and Postgres was storing 10:15:30. Without a $dateFormat on the model, Eloquent formats dates with the grammar’s Y-m-d H:i:s, so everything after the second is simply gone. Nothing errors, nothing warns, and the row looks completely fine. I re-ran it on a fresh install of current Laravel before writing this, because I didn’t want to repeat a claim that had been fixed, and it still behaves the same way:

It’s the assignment that loses it, not the read. Eloquent formats the value the moment you set the attribute, and a model with no $dateFormat reads a millisecond value back perfectly well, which is exactly why it’s so easy to miss. If you hash the model’s own attribute after setting it, the way the code in this article does, the chain will even verify. It’s just quietly lost the milliseconds you thought you were recording, and the first time anything hashes the original value it won’t match. The fix is to treat the column’s precision and the model’s format as a pair:

class AuditEvent extends Model
{
    use HasUlids;

    public $timestamps = false;

    protected $dateFormat = 'Y-m-d H:i:s.vP';

    protected function casts(): array
    {
        return [
            'metadata' => 'array',
            'occurred_at' => 'immutable_datetime',
            'recorded_at' => 'immutable_datetime',
        ];
    }
}

And floor to the millisecond before you hash, not after. There’s a nastier version of the same bug for anything that writes microseconds, through a .u in $dateFormat or a raw string: Postgres rounds when it fits a value into timestampTz(3), but PHP’s v format truncates. Hand it .123999 and Postgres stores .124, while your hash said .123. Roughly half of your events would verify and half wouldn’t, which is a much harder thing to debug than all of them failing. Flooring with startOfMillisecond() on the way in means there’s nothing left to round.

This bit me four times, in four different disguises: the chain hash, a signed attestation, a model’s date cast reformatting on save, and a completed_at column that nothing hashed yet but something was going to. So the rule I keep now is wider than timestamps. If a value is hashed, signed or compared, write it at exactly the precision the column keeps. And a verification test that doesn’t rebuild from the persisted row isn’t testing verification. Make the test read only what a customer could read.

One writer at a time

Each event hashes the one before it, so two requests appending at the same moment will both read the same head, both hash against it, and fork the chain. PHP can’t hold that ordering for you between requests, because there’s no long-lived process to keep it in. The serialisation point has to be the database.

DB::transaction(function () use ($team, $pending) {
    DB::statement('SELECT pg_advisory_xact_lock(?, ?)', [self::LOCK_NAMESPACE, $team->id]);

    $head = AuditEvent::where('team_id', $team->id)->orderByDesc('seq')->first();

    $seq = $head?->seq ?? 0;
    $previousHash = $head?->hash;
    $recordedAt = CarbonImmutable::now()->startOfMillisecond();

    foreach ($pending as $event) {
        $event->team_id = $team->id;
        $event->occurred_at = $event->occurred_at->startOfMillisecond();
        $event->recorded_at = $recordedAt;
        $event->seq = ++$seq;
        $event->previous_hash = $previousHash;
        $event->hash = hash('sha256', ($previousHash ?? '').AuditCanonical::encode($event));
        $event->save();

        $previousHash = $event->hash;
    }
});

The xact matters. A transaction-scoped advisory lock is released when the transaction ends, however it ends, so a crash halfway through can’t wedge a team’s chain. The session-scoped version, pg_advisory_lock, looks almost identical and is a trap on any host that puts PgBouncer in transaction mode in front of Postgres, which is how a lot of serverless Postgres is served, Neon included. The pooler hands the server connection to someone else between transactions, the session lock stays behind on it, and appends start interleaving without a single error anywhere.

Take seq from the head under the lock too, not from an auto-increment. Postgres sequences skip numbers when a transaction rolls back, and once gaps are normal a gap stops being evidence.

And your test suite can’t prove any of this. RefreshDatabase wraps each test in a transaction that never commits, and everything runs on the one connection, so there’s nothing to race against. I ended up writing a script that forks real writers against a real database, a couple of dozen at a time appending a few hundred events between them, and checks afterwards that seq is gapless and the chain verifies from the first event. It’s not elegant, but it’s the only thing that actually tells you the lock works.

Retention may only remove a prefix

Sooner or later someone is going to want old events deleted, and deleting the start of a chain breaks verification from the start. So the rule is that retention only ever removes a prefix: everything up to some point, never anything from the middle. A gap where verification begins can be explained. A gap anywhere else is tampering, always, and you don’t want a forger to be able to dress a deletion up as retention by choosing where to cut.

That’s why retention has to be measured on recorded_at, the time you received the event, not occurred_at, the time the caller says it happened. The caller controls occurred_at, so a policy based on it can require deleting something from the middle of the chain. recorded_at is assigned by you and agrees with seq.

Then record the deletion as an event in the surviving chain, carrying the last deleted seq and its hash, so verification has an anchor to start from. You can keep a checkpoint row as well for convenience, but be honest with yourself about it: it lives in the same database as the events, so anyone who can delete events can write one. The event in the chain is the part with any weight, because removing it later breaks everything after it.

I also blocked deletes at the database:

CREATE OR REPLACE FUNCTION block_audit_deletes() RETURNS trigger AS $$
BEGIN
    IF current_setting('audit.allow_deletes', true) IS DISTINCT FROM 'on' THEN
        RAISE EXCEPTION 'audit_events is append-only';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_events_block_delete
    BEFORE DELETE ON audit_events
    FOR EACH ROW EXECUTE FUNCTION block_audit_deletes();

Retention opts in with SET LOCAL audit.allow_deletes = 'on', and there’s a catch in that too. SET LOCAL lasts until the outermost transaction ends, not the one you’re in. Laravel’s nested transactions are savepoints, and committing a savepoint doesn’t undo it. Only rolling one back does. Wrap the retention service in another transaction, which is every test using RefreshDatabase, and the exemption stays switched on for everything that runs afterwards. I turn it off again in a finally straight after the delete, so it covers exactly one statement.

Be clear about what the trigger buys you, though. It stops a stray delete() in application code. It does nothing at all against someone with database access, who can set the same flag.

Who can actually beat it

Everything so far catches an edit made by someone who didn’t recompute the hashes afterwards. But anyone who can write to the database can also recompute them. Change a row, rehash it and every row after it, and the chain is internally consistent again. Verification passes.

So a chain checked only against itself proves nothing against the people most likely to tamper with it: someone with database access, an insider, or you, the operator. It makes tampering expensive and it makes accidents loud, and both of those are worth having. But on its own it isn’t evidence.

What turns it into evidence is a hash that lives somewhere the editor can’t reach. Return the seq and hash of every event to whoever sent it, so the sender holds a receipt. Publish the current head so a customer can record it on their own schedule. Let verification take a list of hashes the customer kept and check each one is still there, unchanged. A rewritten chain might be perfectly consistent with itself, but it won’t match the receipts already sitting in someone else’s system.

Signing helps, within limits. I signed checkpoints and verification results with an Ed25519 key, using PHP’s sodium functions, and published the public key. That stops someone who has only reached the database from forging a checkpoint, because the key isn’t in the database. It does nothing against someone who’s compromised the whole application, because then they have the key too, and nothing in the product should claim otherwise. What signing actually gets you is a document a customer can keep and check later without trusting you. And sign the verification result whether the chain passed or broke. An attestation that only exists when the answer is good is marketing, not evidence.

What it can’t tell you

A hash chain proves what you were told, and when. It doesn’t prove any of it was true. A client sending false events gets a perfectly valid chain full of false events.

That includes your own code. In my build, changing a team’s retention period wrote a retention_changed event into that team’s chain, which felt like exactly the right thing to do. Then I found that retention_days was missing from the model’s fillable attributes, so the update was being dropped silently while the chain recorded it as done. The log was tamper-evident, verifiable, and wrong. An entry that’s simply false is worse than no entry at all, and mass-assignment protection is a really plausible way to produce one, because it fails quietly on purpose.

So the action now re-reads the model and confirms the change actually landed before it records anything. Before you write an event saying something changed, confirm that it changed. The chain should never be the only place a change is claimed to exist.

The same thinking applies to who gets to write what. Any event your platform writes about itself, like a key being issued or a retention change, needs a namespace that clients can’t use, enforced at ingest. Otherwise a customer can forge an entry that looks like it came from you, and every one of those entries stops being worth anything.

A hash chain doesn’t stop anyone editing your audit log. It makes an edit cost a rewrite of everything after it, and it only becomes evidence once one of its hashes is somewhere you can’t reach.