Building an Event-Sourced Video Moderation Audit Log in PHP and SQLite

Last month a creator emailed us asking why one of their videos vanished from search on DailyWatch. It wasn't deleted. It wasn't flagged for copyright. It had simply stopped appearing in our FTS5 index. When I went to investigate, I hit the wall every moderation system eventually hits: our videos table only stored the current state. status = 'hidden', updated_at = 2026-06-14. Who hid it? Which rule fired? Was it a human, a cron job, or a Cloudflare Worker reacting to an abuse signal? The row couldn't tell me, because a row is a snapshot, and a snapshot forgets.

That gap is not a logging problem you can paper over with a few error_log() calls. It's a modeling problem. The moment you need to answer "how did this video get into this state, and could we have prevented it," you need the full history of decisions, not the last one. This is exactly what event sourcing is for, and you do not need Kafka, a service mesh, or a 12-person platform team to do it. You need an append-only table, a disciplined write path, and a way to rebuild state from events. This post walks through the moderation audit log we run in production on PHP 8.4 and SQLite, including the FTS5 integration that lets our team search a year of moderation history in milliseconds.

The problem with mutable moderation state

Most moderation systems start the same way. You have a status column, you flip it, and you write a row to a moderation_log table so there's some record. That log is usually an afterthought: it's write-only, nobody reads it programmatically, and it drifts out of sync with the real state because the status update and the log insert are two separate statements that can partially fail.

The deeper issue is that the log and the state are derived from different sources of truth. The videos.status column is authoritative for reads, and the moderation_log is authoritative for audits, and there is no guarantee they agree. When they disagree — and they will — you cannot tell which one is lying.

Event sourcing inverts this. The event log becomes the single source of truth. The videos.status column becomes a projection: a cache you can throw away and rebuild at any time by replaying events. If the projection and the events disagree, the events win, always, and you fix the projection by rebuilding it. This one property — that current state is a pure function of the event history — is what makes the system auditable. You can answer "why is this video hidden" because the answer is literally the sequence of events that produced that state.

Here is the core distinction, made concrete:

• State-oriented: UPDATE videos SET status='hidden' WHERE id=? — the previous status is gone forever.
• Event-oriented: INSERT INTO moderation_events (video_id, type, payload) VALUES (?, 'VideoHidden', ?) — nothing is ever overwritten, and the current status is computed from the sequence.

Designing the event schema

An event is an immutable fact about something that already happened. That framing matters. Events are named in the past tense — VideoHidden, VideoRestored, ModeratorNoteAdded — because you are recording history, not issuing commands. A command (HideVideo) can be rejected; an event (VideoHidden) cannot, because by the time it exists, the thing has occurred.

The table itself is deliberately boring. That is a feature. The whole point is that the storage layer stays dumb and append-only while the meaning lives in the payload.

CREATE TABLE moderation_events (
    seq         INTEGER PRIMARY KEY AUTOINCREMENT,
    video_id    TEXT    NOT NULL,
    type        TEXT    NOT NULL,
    payload     TEXT    NOT NULL,          -- JSON
    actor_id    TEXT    NOT NULL,          -- 'human:ibt', 'cron:abuse-scan', 'cf:worker'
    actor_kind  TEXT    NOT NULL,          -- human | cron | system
    occurred_at TEXT    NOT NULL,          -- ISO-8601 UTC
    request_id  TEXT,                      -- ties event to an HTTP request / cron run
    prev_seq    INTEGER                    -- last seq for this video, for optimistic concurrency
);

CREATE INDEX idx_events_video ON moderation_events(video_id, seq);
CREATE INDEX idx_events_type  ON moderation_events(type, seq);

A few decisions worth calling out. seq is a global autoincrement, so events have a total order across every video — this makes replay deterministic and gives you a natural cursor for downstream consumers. actor_kind separates humans from automation, which turns out to be the single most useful field when you're debugging "why did this happen at 3am" (answer: a cron job did it, and now you know which one). request_id lets you correlate an event with the exact HTTP request or cron invocation that produced it, which is priceless when a single request emits several events. And prev_seq is the seatbelt: it lets you detect concurrent modifications before you append, which I'll come back to.

Critically, there is no UPDATE and no DELETE on this table, ever. Enforce it at the boundary in code, and if you want belt-and-suspenders, enforce it with a trigger:

CREATE TRIGGER moderation_events_immutable_update
BEFORE UPDATE ON moderation_events
BEGIN
    SELECT RAISE(ABORT, 'moderation_events is append-only');
END;

CREATE TRIGGER moderation_events_immutable_delete
BEFORE DELETE ON moderation_events
BEGIN
    SELECT RAISE(ABORT, 'moderation_events is append-only');
END;

Now the database itself refuses to let anyone — including a future, sleep-deprived version of me — mutate history.

The append path in PHP 8.4

Writing an event has to be atomic and concurrency-safe. On DailyWatch a video can be touched by a human moderator and an automated abuse scan within the same second, and I never want the second writer to silently clobber the first's reasoning. The append function reads the current head sequence for the video, and refuses to write if someone else has appended since the caller last looked.

PHP 8.4's property hooks and readonly classes make the event objects pleasant to model, and typed constants keep the event names honest.

<?php
declare(strict_types=1);

final readonly class ModerationEvent
{
    public function __construct(
        public string $videoId,
        public string $type,
        public array  $payload,
        public string $actorId,
        public string $actorKind,
        public string $requestId,
    ) {}
}

final class EventStore
{
    // Whitelist of known event types. Unknown types are a programming error.
    private const array TYPES = [
        'VideoHidden', 'VideoRestored', 'VideoFlagged',
        'FlagCleared', 'ModeratorNoteAdded', 'VideoPurged',
    ];

    public function __construct(private \PDO $db) {}

    /**
     * Append an event using optimistic concurrency.
     * $expectedHead is the last seq the caller saw for this video (0 = brand new).
     * Throws on a concurrent write so the caller can re-read and retry.
     */
    public function append(ModerationEvent $e, int $expectedHead): int
    {
        if (!in_array($e->type, self::TYPES, strict: true)) {
            throw new \InvalidArgumentException("Unknown event type: {$e->type}");
        }

        $this->db->beginTransaction();
        try {
            $head = (int) $this->db->query(
                'SELECT COALESCE(MAX(seq), 0) FROM moderation_events'
                . ' WHERE video_id = ' . $this->db->quote($e->videoId)
            )->fetchColumn();

            if ($head !== $expectedHead) {
                throw new \RuntimeException(
                    "Concurrency conflict on {$e->videoId}: expected head $expectedHead, found $head"
                );
            }

            $stmt = $this->db->prepare(
                'INSERT INTO moderation_events'
                . ' (video_id, type, payload, actor_id, actor_kind, occurred_at, request_id, prev_seq)'
                . ' VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
            );
            $stmt->execute([
                $e->videoId,
                $e->type,
                json_encode($e->payload, JSON_THROW_ON_ERROR),
                $e->actorId,
                $e->actorKind,
                gmdate('Y-m-d\TH:i:s\Z'),
                $e->requestId,
                $expectedHead ?: null,
            ]);

            $seq = (int) $this->db->lastInsertId();
            $this->db->commit();
            return $seq;
        } catch (\Throwable $t) {
            $this->db->rollBack();
            throw $t;
        }
    }
}

Because SQLite serializes writes with a database-level lock, the beginTransaction/commit pair guarantees the read-check-write is atomic. On LiteSpeed with multiple PHP workers hammering the same SQLite file, I run in WAL mode (PRAGMA journal_mode=WAL) so readers never block the single writer — that combination handles our moderation throughput without breaking a sweat, since moderation writes are rare compared to reads.

Rebuilding current state by folding events

The projection is where event sourcing earns its keep. Current state is just a left-fold over the event stream: start from an empty aggregate, apply each event in seq order, and whatever you have at the end is the truth. There's no separate "is this in sync" question because the projection is definitionally the replay result.

<?php
declare(strict_types=1);

final class VideoModerationState
{
    public string $status = 'visible';     // visible | hidden | purged
    public array  $activeFlags = [];       // reason => flaggedBy
    public array  $notes = [];
    public ?string $lastActor = null;
    public int    $head = 0;               // last applied seq

    public static function replay(\PDO $db, string $videoId): self
    {
        $state = new self();
        $stmt = $db->prepare(
            'SELECT seq, type, payload, actor_id FROM moderation_events'
            . ' WHERE video_id = ? ORDER BY seq ASC'
        );
        $stmt->execute([$videoId]);

        foreach ($stmt as $row) {
            $p = json_decode($row['payload'], true, flags: JSON_THROW_ON_ERROR);
            $state->apply($row['type'], $p, $row['actor_id']);
            $state->head = (int) $row['seq'];
        }
        return $state;
    }

    private function apply(string $type, array $p, string $actor): void
    {
        $this->lastActor = $actor;
        match ($type) {
            'VideoHidden'        => $this->status = 'hidden',
            'VideoRestored'      => $this->status = 'visible',
            'VideoPurged'        => $this->status = 'purged',
            'VideoFlagged'       => $this->activeFlags[$p['reason']] = $actor,
            'FlagCleared'        => $this->activeFlags = array_diff_key(
                                        $this->activeFlags, [$p['reason'] => true]
                                    ),
            'ModeratorNoteAdded' => $this->notes[] = $p['text'],
            default              => null,
        };
    }
}

Replaying from scratch on every read would be wasteful, so the projection also gets written to a plain videos table that the rest of the app reads from — search, the watch page, sitemaps. That table is a disposable cache. If it ever looks wrong, I regenerate it:

function rebuildProjection(\PDO $db, string $videoId): void
{
    $state = VideoModerationState::replay($db, $videoId);
    $db->prepare('UPDATE videos SET status = ?, updated_at = ? WHERE id = ?')
       ->execute([$state->status, gmdate('Y-m-d\TH:i:s\Z'), $videoId]);
}

This is the moment the earlier creator email got easy to answer. I replayed the video's events, saw a VideoFlagged with reason: 'automated-duplicate' emitted by cron:abuse-scan, followed by nothing that cleared it — the flag had silently dropped it from the index. A false positive from a dedup heuristic. The event log didn't just tell me what happened; it told me which system to fix.

Making history searchable with FTS5

An audit log you can't search is a graveyard. Once you have thousands of events, "find every video a specific moderator hid last quarter" or "show me all notes mentioning copyright" needs to be fast. SQLite's FTS5 gives us full-text search over the event payloads with zero extra infrastructure, which fits our Cloudflare-fronted, LiteSpeed-origin setup where I refuse to add a search server just for internal tooling.

We maintain an FTS5 virtual table that indexes a flattened, human-readable version of each event, kept in sync with triggers so the search index is itself just another projection of the event log.

CREATE VIRTUAL TABLE moderation_events_fts USING fts5(
    video_id UNINDEXED,
    type,
    actor_id,
    body,                         -- flattened searchable text from payload
    content='moderation_events',
    content_rowid='seq',
    tokenize="unicode61 remove_diacritics 2"
);

-- Keep the index in sync. moderation_events is append-only,
-- so we only ever need the INSERT trigger.
CREATE TRIGGER moderation_events_ai AFTER INSERT ON moderation_events BEGIN
    INSERT INTO moderation_events_fts(rowid, video_id, type, actor_id, body)
    VALUES (
        new.seq,
        new.video_id,
        new.type,
        new.actor_id,
        new.type || ' ' || new.actor_id || ' ' ||
            coalesce(json_extract(new.payload, '$.reason'), '') || ' ' ||
            coalesce(json_extract(new.payload, '$.text'),   '')
    );
END;

Because the source table is append-only, the FTS integration is trivially correct — there's no UPDATE/DELETE trigger to get wrong, no risk of the index drifting from a mutated row. Querying it is a normal MATCH:

SELECT e.seq, e.video_id, e.type, e.actor_id, e.occurred_at,
       json_extract(e.payload, '$.reason') AS reason
FROM moderation_events_fts f
JOIN moderation_events e ON e.seq = f.rowid
WHERE moderation_events_fts MATCH 'copyright AND actor_id:human*'
ORDER BY e.seq DESC
LIMIT 50;

That query — every copyright-related action taken by a human, most recent first — returns in single-digit milliseconds against hundreds of thousands of events. The same FTS5 engine that powers video discovery on the public site powers the moderation console, which means one less moving part to operate.

Operational lessons from running this in production

A few things I only learned by shipping it:

• Snapshots matter once histories get long. A video that's been flagged, restored, and re-flagged fifty times is cheap to replay. A pathological one isn't. We periodically write a snapshot row ({state, head_seq}) and replay only events after the snapshot. The rule: a snapshot is an optimization, never a source of truth — you must be able to delete every snapshot and lose nothing.
• Version your event payloads. Add a "v": 1 field to every payload from day one. When VideoFlagged grows a new field, your apply() match arm can branch on version instead of guessing. Retrofitting versioning onto a million existing events is miserable.
• actor_kind pays for itself. The first dashboard I built was "events per hour by actor_kind." A spike in system events with no matching human events is almost always a misbehaving cron rule, and the log points straight at it.
• Don't put PII in payloads you can't delete. Append-only cuts both ways. If a payload might need erasure for a legal request, store a reference (an ID) and keep the erasable data in a mutable side table, so a VideoPurged event stays truthful while the sensitive blob can still be scrubbed.
• Cache the projection at the edge, invalidate on append. The public videos projection is served through Cloudflare and LiteSpeed's page cache. Every successful append() clears the relevant cache keys, so moderation actions take effect on the next request without ever exposing the event store to public traffic.

Conclusion

The shift that made moderation on DailyWatch debuggable wasn't a new framework — it was refusing to overwrite the past. An append-only moderation_events table, a whitelisted append path guarded by optimistic concurrency, a projection you can rebuild by folding events, and an FTS5 index that turns a year of history into a searchable console: that's the entire system, and it runs on the same PHP 8.4 and SQLite stack we already had. When a creator asks why their video disappeared, I no longer guess from a mutated status column. I replay the events and read the answer. Start with the event table and the immutability triggers; the projection and search layers are just functions of that log, and once you internalize that, the whole design falls out naturally.