DEV Community: ahmet gedik

Designing a Multi-Tenant Video Platform Schema with PostgreSQL RLS

ahmet gedik — Wed, 12 Aug 2026 09:00:01 +0000

Six months ago a media partner asked us to white-label the trend feed for their own newsroom, and that single request broke the assumption our entire backend rested on: one database, one set of tables, one tenant. For years ViralVidVault ran on SQLite in WAL mode behind LiteSpeed and Cloudflare Workers, tracking viral videos across European markets. That stack is genuinely great for a single-tenant, read-heavy discovery site. It is the wrong tool the moment three partners each want an isolated dashboard over the same crawl pipeline, each with its own retention window and its own GDPR data-subject requests to honour. That is a PostgreSQL problem now, and the schema is where multi-tenancy is won or lost. Everything else — the caching layer, the edge workers, the API — is downstream of getting the tables right.

Three isolation models, and why the middle one won

Before writing a single CREATE TABLE, you have to pick how tenants are separated. There are three honest options, and the internet loves to argue about them without stating scale.

Database-per-tenant. Each partner gets a physically separate database. Isolation is airtight, per-tenant data residency is trivial, and a pg_dump is a clean export for a leaving customer. But every schema migration now runs N times, connection pools multiply, and any cross-tenant question ("which video trended in five markets this week?") becomes an application-level fan-out. This shines at thousands of tenants with strict compliance walls — and it is overkill at dozens.
Schema-per-tenant. One database, a Postgres schema namespace per tenant, search_path juggled per request. It looks tidy until you have hundreds of schemas and pg_dump takes an hour walking metadata, and until a migration has to loop over every schema anyway.
Shared tables with a tenant_id column plus Row-Level Security. One set of tables, one migration, trivial cross-tenant analytics. The famous risk is that a single forgotten WHERE tenant_id = ? leaks another partner's data. The fix is to stop relying on the application to remember that clause and push enforcement into the database.

We are at dozens of tenants, not thousands. But the argument that actually settled it was the catalogue itself: we crawl viral videos once for all of Europe. Duplicating that catalogue into a database-per-tenant would waste storage and, worse, waste our crawl and API quota re-fetching the same YouTube and TikTok metadata. A shared catalogue with per-tenant curation on top is not just cheaper — it is the whole point of a discovery platform. So: shared schema, tenant_id, and RLS as the seatbelt.

The core schema

The key design decision is what is global and what is tenant-scoped. The video catalogue is global and deduplicated — it belongs to no tenant. Curation, users, and analytics events are tenant data. That split is the spine of everything below.

-- Tenants: each white-label partner / newsroom.
CREATE TABLE tenant (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug          text NOT NULL UNIQUE,
    display_name  text NOT NULL,
    region        text NOT NULL DEFAULT 'eu-central',   -- data residency hint
    created_at    timestamptz NOT NULL DEFAULT now()
);

-- Global, deduplicated video catalogue. Crawled ONCE for everyone.
CREATE TABLE video (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    platform     text   NOT NULL,          -- youtube | tiktok | reels
    external_id  text   NOT NULL,
    title        text   NOT NULL,
    lang         text,
    country      text,                      -- origin market
    published_at timestamptz,
    UNIQUE (platform, external_id)
);

-- Per-tenant curation of the shared catalogue. This table IS tenant data.
CREATE TABLE tenant_video (
    tenant_id    bigint NOT NULL REFERENCES tenant(id),
    video_id     bigint NOT NULL REFERENCES video(id),
    curated_rank int    NOT NULL,
    added_by     bigint,
    added_at     timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, video_id)
);

CREATE TABLE app_user (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id    bigint NOT NULL REFERENCES tenant(id),
    email        text   NOT NULL,
    display_name text   NOT NULL,
    ip_hash      bytea,
    erased_at    timestamptz,
    UNIQUE (tenant_id, email)
);

-- Analytics events, range-partitioned by month (see the maintenance job).
CREATE TABLE video_event (
    tenant_id     bigint      NOT NULL,
    video_id      bigint      NOT NULL,
    actor_user_id bigint,
    kind          text        NOT NULL,     -- impression | play | share
    created_at    timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

Two choices deserve a comment. First, tenant_video has a composite primary key of (tenant_id, video_id) — the same physical video can be curated by many tenants, and the leading tenant_id is exactly the column RLS and every dashboard query will filter on. Second, video_event carries a denormalised tenant_id even though you could derive it by joining through tenant_video. Do not derive it. RLS policies must be able to filter each row cheaply, and partition-local queries must prune without a join. Denormalising one bigint is the price of both.

Row-Level Security is the seatbelt, not the app code

Here is the failure mode that keeps me up at night: an engineer writes SELECT * FROM app_user WHERE email = $1 for a password-reset flow, forgets the tenant predicate, and now partner A can trigger a reset for partner B's user. Code review catches most of these. "Most" is not a compliance posture. RLS moves the guarantee from every developer, forever to the database, once.

-- The application connects as this role. It must NOT be a superuser and must
-- NOT have BYPASSRLS, or policies are silently ignored.
CREATE ROLE vault_app LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE
    ON tenant_video, app_user, video_event TO vault_app;
GRANT SELECT ON video, tenant TO vault_app;

ALTER TABLE tenant_video ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_user     ENABLE ROW LEVEL SECURITY;
ALTER TABLE video_event  ENABLE ROW LEVEL SECURITY;

-- FORCE makes policies apply even to the table owner.
ALTER TABLE tenant_video FORCE ROW LEVEL SECURITY;
ALTER TABLE app_user     FORCE ROW LEVEL SECURITY;
ALTER TABLE video_event  FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tenant_video
    USING      (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

CREATE POLICY tenant_isolation ON app_user
    USING      (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

CREATE POLICY tenant_isolation ON video_event
    USING      (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

Three things earn their keep here. FORCE ROW LEVEL SECURITY closes the loophole where the table owner bypasses policies by default. The USING clause filters what a query can read; the separate WITH CHECK clause stops a tenant from writing a row stamped with someone else's tenant_id — without it, an INSERT ... tenant_id = 99 from tenant 42 would sail through. And notice I used current_setting('app.tenant_id') without the missing-ok second argument: if the session variable was never set, the cast throws and the query fails closed. A query that errors is a bug report. A query that silently returns every tenant's rows is a breach.

Binding the tenant to every connection in PHP 8.4

RLS reads app.tenant_id from the session, so something has to set it — correctly — on every request. The trap is connection pooling. If you SET app.tenant_id = 42 at the session level and your pooler (PgBouncer in transaction mode, which we run) hands that physical connection to the next request without resetting it, tenant 42's context bleeds into tenant 7's query. The fix is SET LOCAL semantics: scope the setting to a transaction so it is discarded on commit or rollback. set_config(name, value, true) is the parameterised, injection-safe way to do exactly that.

<?php
declare(strict_types=1);

final class TenantConnection
{
    public function __construct(private \PDO $pdo) {}

    /**
     * Run a unit of work bound to one tenant. The id is set with local
     * (transaction) scope so it can never leak across pooled connections
     * running under PgBouncer transaction mode.
     */
    public function forTenant(int $tenantId, callable $work): mixed
    {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare('SELECT set_config(?, ?, true)');
            $stmt->execute(['app.tenant_id', (string) $tenantId]);

            $result = $work($this->pdo);
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// Usage: note there is NO tenant_id in the query. RLS supplies it.
$repo = new TenantConnection($pdo);
$topVideos = $repo->forTenant(42, static function (\PDO $db): array {
    $stmt = $db->query(
        'SELECT v.title, v.platform, tv.curated_rank
           FROM tenant_video tv
           JOIN video v ON v.id = tv.video_id
          ORDER BY tv.curated_rank
          LIMIT 20'
    );
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
});

The query joining tenant_video to the global video table has no tenant_id predicate anywhere in the SQL. That is the payoff: application code becomes shorter and safer at the same time, because the isolation clause lives in one policy instead of scattered across a hundred hand-written WHEREs. Every read and write inside forTenant is fenced. Forget to wrap something in forTenant, and the query throws instead of leaking — again, fail closed.

Analytics that don't collapse under a billion rows

video_event is the table that grows without bound. Across every tenant, every impression and play at the edge lands here. A single flat table hits index bloat and painful VACUUM runs within a year. Declarative range partitioning by month keeps each child table small, lets the planner prune to the months a query actually touches, and — the part that matters for us — turns data deletion into a metadata operation.

#!/usr/bin/env python3
"""Create next month's video_event partition and drop expired ones.
Run daily from cron; fully idempotent."""
from __future__ import annotations
import datetime as dt
import psycopg

RETENTION_MONTHS = 14  # GDPR: analytics kept 14 months, then the partition is dropped

def month_bounds(d: dt.date) -> tuple[dt.date, dt.date]:
    start = d.replace(day=1)
    nxt = (start + dt.timedelta(days=32)).replace(day=1)
    return start, nxt

def main() -> None:
    today = dt.date.today()
    _, next_start = month_bounds(today)
    p_start, p_end = month_bounds(next_start)
    name = f"video_event_{p_start:%Y_%m}"

    with psycopg.connect("postgresql:///vault", autocommit=True) as conn:
        conn.execute(f"""
            CREATE TABLE IF NOT EXISTS {name}
            PARTITION OF video_event
            FOR VALUES FROM ('{p_start}') TO ('{p_end}')
        """)
        # BRIN is tiny and perfect for append-only, time-ordered data.
        conn.execute(f"CREATE INDEX IF NOT EXISTS {name}_brin "
                     f"ON {name} USING brin (created_at)")

        cutoff = (today.replace(day=1)
                  - dt.timedelta(days=31 * RETENTION_MONTHS)).replace(day=1)
        rows = conn.execute("""
            SELECT child.relname
              FROM pg_inherits
              JOIN pg_class parent ON parent.oid = pg_inherits.inhparent
              JOIN pg_class child  ON child.oid  = pg_inherits.inhrelid
             WHERE parent.relname = 'video_event'
        """).fetchall()
        for (child_name,) in rows:
            try:
                suffix = child_name.removeprefix("video_event_")
                part_date = dt.datetime.strptime(suffix, "%Y_%m").date()
            except ValueError:
                continue
            if part_date < cutoff:
                conn.execute(f"DROP TABLE IF EXISTS {child_name}")
                print(f"dropped expired partition {child_name}")

if __name__ == "__main__":
    main()

Our Cloudflare Workers batch edge events into a queue that flushes into video_event in bulk COPY-style inserts, so writes always target the current month's partition — sequential, append-only, BRIN-friendly. And the retention story writes itself: dropping a partition is an instant catalog operation that reclaims disk immediately, with none of the dead-tuple churn a DELETE FROM ... WHERE created_at < ... would leave behind. Your retention policy and your partition strategy become the same line of code.

GDPR baked into the tables

European users, European law, no way around it. Two GDPR obligations shape the schema directly: retention (handled above by partition drops) and the Article 17 right to erasure. For erasure we pseudonymise in place rather than hard-delete, because deleting a user's rows outright would silently distort every historical aggregate that already counted them. We null the personal data, keep the anonymous shape, and mark the record done.

package main

import (
    "context"
    "log"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

// eraseSubject fulfils an Article 17 request for one user within one tenant.
// It pseudonymises PII rather than hard-deleting, keeping aggregates intact,
// and is idempotent (safe to retry).
func eraseSubject(ctx context.Context, pool *pgxpool.Pool, tenantID, userID int64) error {
    tx, err := pool.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx)

    if _, err := tx.Exec(ctx,
        "SELECT set_config('app.tenant_id', $1::text, true)", tenantID); err != nil {
        return err
    }

    // RLS guarantees the worker can only touch rows for this tenant.
    if _, err := tx.Exec(ctx, `
        UPDATE app_user
           SET email        = 'erased+' || id || '@invalid',
               display_name = 'Erased User',
               ip_hash      = NULL,
               erased_at    = now()
         WHERE id = $1 AND erased_at IS NULL`, userID); err != nil {
        return err
    }

    if _, err := tx.Exec(ctx,
        "UPDATE video_event SET actor_user_id = NULL WHERE actor_user_id = $1",
        userID); err != nil {
        return err
    }

    return tx.Commit(ctx)
}

func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres:///vault")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()

    ctx, cancel := context.WithTimeout(ctx, 30*time.Second)
    defer cancel()

    if err := eraseSubject(ctx, pool, 42, 100773); err != nil {
        log.Fatalf("erasure failed: %v", err)
    }
    log.Println("subject erased")
}

The worker sets the tenant context first, so RLS makes it impossible for a mis-routed erasure job to touch the wrong partner's users — the isolation model you built for reads protects your compliance jobs for free. The erased_at IS NULL guard makes retries idempotent. Aggregate analytics keep working because we set actor_user_id to NULL instead of deleting the events; the trend counts stay honest, the person is gone. Back on single-tenant SQLite this was a plain UPDATE too — but there was no tenant boundary to get wrong. Postgres RLS is what makes the same operation safe when many partners share one table.

Indexing and the queries that actually run

RLS silently appends tenant_id = ... to every query, which changes how you index. If your indexes don't lead with tenant_id, the planner can't use them for the policy predicate and you're back to sequential scans. A few rules that survived contact with production:

Lead composite indexes with tenant_id. tenant_video's primary key already does this; add (tenant_id, curated_rank) for the dashboard's ordered top-N.
Keep created_at in analytics predicates. Partition pruning only fires when the query filters on the partition key, so WHERE created_at >= now() - interval '7 days' is what keeps a report touching one or two partitions instead of fourteen.
Use BRIN, not B-tree, on append-only time columns. A B-tree over a billion created_at values is huge; BRIN is a few kilobytes and just as effective when inserts are time-ordered.
Avoid SELECT * on wide event tables. Name the columns so a covering index can answer the hot dashboard query without touching the heap.
Test with the app role, never as superuser. Policies are invisible to a superuser, so a query that looks fine in psql can behave completely differently under vault_app. EXPLAIN as the real role.

What I'd tell myself six months ago

Multi-tenancy is a data-modelling problem wearing an application-code costume. The instinct is to reach for middleware that injects tenant_id into every query; the durable answer is to make the database refuse to hand over rows it shouldn't. Enforce isolation with RLS so one forgotten WHERE is a caught error instead of an incident. Denormalise tenant_id onto your analytics table so partitioning and policies stay cheap. Let partitions be your retention policy. And keep the shared catalogue shared — the whole reason a discovery platform exists is that the crawl runs once for everyone. Get those four decisions right in the schema and the rest of the stack, whether it's PHP behind LiteSpeed or Go workers on the edge, becomes almost boring. Boring, in infrastructure that holds European users' data, is exactly what you want.

Building a Video URL Canonicalization Pipeline for Multi-Region Feeds

ahmet gedik — Wed, 12 Aug 2026 00:00:01 +0000

The bug report was one sentence: "Vietnam trending shows the same K-pop video four times." It did. One video, four rows, four URLs that our ingest layer had happily treated as four distinct pieces of content:

https://www.youtube.com/watch?v=kJQP7kiw5Fk&list=RDkJQP7kiw5Fk — pulled from a regional trending feed
https://m.youtube.com/watch?v=kJQP7kiw5Fk — a mobile share link a user submitted
https://youtu.be/kJQP7kiw5Fk?t=42&si=9xQd1Kk2 — same user, different share sheet
https://www.youtube.com/watch?app=desktop&feature=share&v=kJQP7kiw5Fk — a partner API response

At TopVideoHub we aggregate trending video across the Asia-Pacific region in nine languages, and URL-as-identity had been fine for about six months. Once we were pulling from eleven regional feeds plus three partner APIs plus a submissions form, the duplicate rate on raw URL identity sat between 12% and 18% depending on the market — worst in VN, ID and TH, where mobile share links dominate and every one of them carries a fresh si= tracking parameter.

Duplicates are not just cosmetic. They split view counts across rows, they blow up the SQLite FTS5 index, and — the part that actually hurt — they poison BM25 ranking, because four near-identical title rows crowd out everything else for a query. This is the pipeline that replaced it, and the specific things that went wrong while we built it.

Why Naive Normalization Breaks

The first attempt, written in an afternoon, was a single function that lowercased the URL, stripped a denylist of tracking params, and sorted the rest. It made things worse. Here is why, in the order we discovered them:

Lowercasing destroys IDs. YouTube video IDs are 11 characters of base64url. kJQP7kiw5Fk and kjqp7kiw5fk are different videos, and one of them does not exist. Bilibili BV IDs are the same story.
A tracking-param denylist is a losing race. si, feature, app, pp, ab_channel, utm_*, spm_id_from, vd_source — every provider adds new ones and never tells you.
Stripping all params breaks providers where the param is the ID. ?v= on YouTube is the identity. Blanket-stripping the query string turns every watch URL into youtube.com/watch.
parse_url() returns false on some malformed IDN hosts and returns a host you cannot compare on others. Punycode is not optional in this market — you will see 動画.jp style hosts in partner feeds.
Eager percent-decoding changes path structure. rawurldecode() turns %2F into /, which silently creates a new path segment.

The rule we landed on, and the reason this pipeline works at all: normalize structurally, then extract semantically, and never mix the two stages. Stage one knows nothing about video providers and only applies RFC 3986. Stage two knows nothing about URL syntax and only knows what a video ID looks like for one provider.

Stage One — Structural Normalization

This stage is provider-agnostic and deterministic. Same bytes in, same bytes out, no network, no database. It is pure enough that we unit-test it with a 900-line fixture table.

<?php
declare(strict_types=1);

final class UrlNormalizer
{
    private const DEFAULT_PORTS = ['http' => 80, 'https' => 443];

    public function normalize(string $raw): ?string
    {
        // Share sheets and CSV imports smuggle in newlines, tabs and NBSP.
        $raw = trim(preg_replace('/[\x00-\x20\x7F]/u', '', $raw) ?? '');
        if ($raw === '') {
            return null;
        }
        if (!preg_match('#^[a-z][a-z0-9+.\-]*://#i', $raw)) {
            $raw = 'https://' . ltrim($raw, '/');
        }

        $p = parse_url($raw);
        if ($p === false || empty($p['host'])) {
            return null;
        }

        $scheme = strtolower($p['scheme'] ?? 'https');
        if ($scheme !== 'http' && $scheme !== 'https') {
            return null;
        }

        $host = $this->asciiHost($p['host']);
        if ($host === null) {
            return null;   // fail closed; a guessed host becomes a second canonical key
        }

        $port = isset($p['port']) && $p['port'] !== self::DEFAULT_PORTS[$scheme]
            ? ':' . $p['port']
            : '';

        $path  = $this->normalizePath($p['path'] ?? '/');
        $query = isset($p['query']) && $p['query'] !== ''
            ? '?' . $this->normalizeQuery($p['query'])
            : '';

        // Fragment is dropped: no provider we ingest carries identity there.
        return $scheme . '://' . $host . $port . $path . $query;
    }

    private function asciiHost(string $host): ?string
    {
        $host = rtrim(strtolower($host), '.');
        if ($host === '') {
            return null;
        }
        if (preg_match('/^[a-z0-9.\-]+$/', $host) === 1) {
            return $host;
        }
        $ascii = idn_to_ascii($host, IDNA_NONTRANSITIONAL_TO_ASCII, INTL_IDNA_VARIANT_UTS46);

        return $ascii === false ? null : $ascii;
    }

    private function normalizePath(string $path): string
    {
        $path = $this->fixPercentEncoding($path);
        $out  = [];
        foreach (explode('/', $path) as $seg) {
            if ($seg === '' || $seg === '.') {
                continue;                       // also collapses // runs
            }
            if ($seg === '..') {
                array_pop($out);
                continue;
            }
            $out[] = $seg;
        }

        return $out === [] ? '/' : '/' . implode('/', $out);
    }

    private function normalizeQuery(string $query): string
    {
        $pairs = [];
        foreach (explode('&', $query) as $pair) {
            if ($pair === '') {
                continue;
            }
            [$k, $v] = array_pad(explode('=', $pair, 2), 2, null);
            $pairs[] = [
                $this->fixPercentEncoding($k),
                $v === null ? null : $this->fixPercentEncoding($v),
            ];
        }
        usort($pairs, static fn(array $a, array $b): int => [$a[0], $a[1]] <=> [$b[0], $b[1]]);

        return implode('&', array_map(
            static fn(array $p): string => $p[1] === null ? $p[0] : $p[0] . '=' . $p[1],
            $pairs
        ));
    }

    /** RFC 3986 6.2.2.2: uppercase the hex, decode unreserved octets, touch nothing else. */
    private function fixPercentEncoding(string $s): string
    {
        return preg_replace_callback('/%([0-9A-Fa-f]{2})/', static function (array $m): string {
            $chr = chr((int) hexdec($m[1]));

            return preg_match('/[A-Za-z0-9\-._~]/', $chr) === 1 ? $chr : '%' . strtoupper($m[1]);
        }, $s) ?? $s;
    }
}

Three decisions in there are worth defending:

We sort query parameters but do not remove any. Removal is a semantic decision and belongs in stage two. The stage-one output is a faithful, comparable rendering of the input — it is what we store in the alias table.
asciiHost() fails closed. Our first version returned the original host when idn_to_ascii() returned false. That produced two canonical keys for the same video and was the single hardest bug to reproduce, because it only triggered on a handful of partner rows per week.
Percent-encoding is normalized, not decoded. %E6%97%A5%E6%9C%AC stays as it is; only unreserved ASCII octets collapse. Decoding CJK bytes into a path would change the string length, break comparison against the alias table, and occasionally produce a byte sequence SQLite refuses to index.

Stage Two — Provider Extractors

Now we get to say things like "a YouTube ID is eleven base64url characters." Each extractor owns a set of hosts and returns a CanonicalVideo or null. Nothing in here parses URLs.

<?php
declare(strict_types=1);

final class CanonicalVideo
{
    public function __construct(
        public readonly string $provider,
        public readonly string $id,
        public readonly ?string $part = null,   // bilibili multi-part episodes
    ) {}

    public function key(): string
    {
        return $this->part === null
            ? $this->provider . ':' . $this->id
            : $this->provider . ':' . $this->id . '#' . $this->part;
    }
}

interface VideoIdExtractor
{
    /** @return string[] punycoded, lowercased hosts this extractor owns */
    public function hosts(): array;

    /** @param array<string,string> $query */
    public function extract(string $host, string $path, array $query): ?CanonicalVideo;
}

final class YouTubeExtractor implements VideoIdExtractor
{
    private const ID = '/^[A-Za-z0-9_-]{11}$/';   // case-sensitive, deliberately

    public function hosts(): array
    {
        return [
            'youtube.com', 'www.youtube.com', 'm.youtube.com', 'music.youtube.com',
            'youtu.be', 'youtube-nocookie.com', 'www.youtube-nocookie.com',
        ];
    }

    public function extract(string $host, string $path, array $query): ?CanonicalVideo
    {
        $seg = array_values(array_filter(explode('/', $path), 'strlen'));

        $candidate = match (true) {
            isset($query['v'])                => $query['v'],
            $host === 'youtu.be'              => $seg[0] ?? null,
            in_array($seg[0] ?? '', ['shorts', 'embed', 'live', 'v'], true) => $seg[1] ?? null,
            default                           => null,
        };

        return is_string($candidate) && preg_match(self::ID, $candidate) === 1
            ? new CanonicalVideo('yt', $candidate)
            : null;
    }
}

final class BilibiliExtractor implements VideoIdExtractor
{
    public function hosts(): array
    {
        return ['bilibili.com', 'www.bilibili.com', 'm.bilibili.com'];
    }

    public function extract(string $host, string $path, array $query): ?CanonicalVideo
    {
        if (preg_match('#^/video/(BV[A-Za-z0-9]{10}|av\d+)#', $path, $m) !== 1) {
            return null;
        }
        $id = str_starts_with($m[1], 'av') ? strtolower($m[1]) : $m[1];

        // p=1 is the implicit default; only p>=2 is part of identity.
        $part = isset($query['p']) && ctype_digit($query['p']) && (int) $query['p'] > 1
            ? (string) (int) $query['p']
            : null;

        return new CanonicalVideo('bili', $id, $part);
    }
}

final class CanonicalResolver
{
    /** @var array<string,VideoIdExtractor> */
    private array $byHost = [];

    public function __construct(VideoIdExtractor ...$extractors)
    {
        foreach ($extractors as $e) {
            foreach ($e->hosts() as $h) {
                $this->byHost[$h] = $e;
            }
        }
    }

    public function resolve(string $normalizedUrl): ?CanonicalVideo
    {
        $p    = parse_url($normalizedUrl);
        $host = $p['host'] ?? '';
        $extractor = $this->byHost[$host] ?? null;
        if ($extractor === null) {
            return null;
        }

        $query = [];
        foreach (explode('&', $p['query'] ?? '') as $pair) {
            if ($pair === '') {
                continue;
            }
            [$k, $v] = array_pad(explode('=', $pair, 2), 2, '');
            // Hand-rolled because parse_str() rewrites "a.b" and "a b" to "a_b".
            $query[rawurldecode($k)] = rawurldecode($v);
        }

        return $extractor->extract($host, $p['path'] ?? '/', $query);
    }
}

The part field is the piece people forget. Bilibili multi-part uploads are genuinely different videos sharing a BV ID, and p=1 is the implicit default — so ?p=1 and no p at all must produce the same key, while ?p=2 must not. We shipped without that distinction, merged about 900 legitimately distinct episodes, and had to rebuild from the alias table. Which is exactly why the alias table exists.

The Schema — Aliases Are the Whole Trick

The canonical key is a derived value. Derived values change when your extractors improve, and if the only record of an ingest is the row you overwrote, an extractor bug is unrecoverable. So we store every normalized URL we have ever seen, forever, pointing at exactly one video row.

<?php
declare(strict_types=1);

$db = new PDO('sqlite:data/videos.db', options: [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$db->exec('PRAGMA journal_mode = WAL');
$db->exec('PRAGMA synchronous = NORMAL');

$db->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS videos (
    id            INTEGER PRIMARY KEY,
    canonical_key TEXT    NOT NULL,
    provider      TEXT    NOT NULL,
    provider_id   TEXT    NOT NULL,
    canonical_url TEXT    NOT NULL,
    title         TEXT    NOT NULL,
    lang          TEXT    NOT NULL,
    duration_s    INTEGER,
    first_seen    INTEGER NOT NULL,
    last_seen     INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS videos_canonical ON videos(canonical_key);

CREATE TABLE IF NOT EXISTS video_url_aliases (
    normalized_url TEXT    PRIMARY KEY,
    video_id       INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    source         TEXT    NOT NULL,
    seen_at        INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS aliases_video ON video_url_aliases(video_id);

-- unicode61 does not segment 日本語 or 한국어 at all; trigram does.
CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts USING fts5(
    title,
    content       = 'videos',
    content_rowid = 'id',
    tokenize      = "trigram case_sensitive 0"
);
SQL);

function ingest(
    PDO $db,
    string $rawUrl,
    array $meta,
    UrlNormalizer $normalizer,
    CanonicalResolver $resolver,
): ?int {
    $normalized = $normalizer->normalize($rawUrl);
    if ($normalized === null) {
        return null;
    }

    // Hot path: 94% of ingests are URLs we have already resolved once.
    $hit = $db->prepare('SELECT video_id FROM video_url_aliases WHERE normalized_url = ?');
    $hit->execute([$normalized]);
    if (($videoId = $hit->fetchColumn()) !== false) {
        $db->prepare('UPDATE videos SET last_seen = ? WHERE id = ?')
           ->execute([time(), $videoId]);

        return (int) $videoId;
    }

    $canonical = $resolver->resolve($normalized);
    if ($canonical === null) {
        // Unknown provider: quarantine for review. Never invent a key from the URL.
        $db->prepare('INSERT OR IGNORE INTO ingest_quarantine (url, seen_at) VALUES (?, ?)')
           ->execute([$normalized, time()]);

        return null;
    }

    $now = time();
    $db->beginTransaction();
    try {
        $db->prepare(<<<'SQL'
            INSERT INTO videos (canonical_key, provider, provider_id, canonical_url,
                                title, lang, duration_s, first_seen, last_seen)
            VALUES (:key, :provider, :pid, :url, :title, :lang, :dur, :now, :now)
            ON CONFLICT(canonical_key) DO UPDATE SET
                last_seen  = excluded.last_seen,
                duration_s = COALESCE(videos.duration_s, excluded.duration_s),
                title      = CASE WHEN length(excluded.title) > length(videos.title)
                                  THEN excluded.title ELSE videos.title END
            SQL)->execute([
                ':key'      => $canonical->key(),
                ':provider' => $canonical->provider,
                ':pid'      => $canonical->id,
                ':url'      => $normalized,
                ':title'    => $meta['title'],
                ':lang'     => $meta['lang'],
                ':dur'      => $meta['duration_s'] ?? null,
                ':now'      => $now,
            ]);

        // lastInsertId() lies after DO UPDATE. Always read the key back.
        $sel = $db->prepare('SELECT id, title FROM videos WHERE canonical_key = ?');
        $sel->execute([$canonical->key()]);
        [$id, $title] = array_values($sel->fetch(PDO::FETCH_ASSOC));

        $db->prepare(<<<'SQL'
            INSERT OR IGNORE INTO video_url_aliases (normalized_url, video_id, source, seen_at)
            VALUES (?, ?, ?, ?)
            SQL)->execute([$normalized, $id, $meta['source'], $now]);

        // External-content FTS5 does not track writes for you.
        $db->prepare("INSERT INTO videos_fts(videos_fts, rowid, title) VALUES('delete', ?, ?)")
           ->execute([$id, $title]);
        $db->prepare('INSERT INTO videos_fts(rowid, title) VALUES (?, ?)')
           ->execute([$id, $title]);

        $db->commit();
    } catch (Throwable $e) {
        $db->rollBack();
        throw $e;
    }

    return (int) $id;
}

Two SQLite-specific traps in there cost us a day each. PDO::lastInsertId() returns the previous insert's rowid after an ON CONFLICT DO UPDATE fires, so aliases were being attached to the wrong video. And an external-content FTS5 table has no triggers unless you write them — updating videos.title without the paired 'delete' command leaves stale trigrams in the index, which surfaces as "search finds a title that no longer exists" weeks later.

Shadow Mode Before You Merge Anything

We did not run the new resolver against production rows directly. We ran it in shadow mode for eleven days, writing proposed keys into a canonical_audit table, then audited the clusters it would have created. The signal that catches false merges is cheap: two rows that claim to be the same video but have durations 90 seconds apart are almost certainly not the same video.

#!/usr/bin/env python3
"""Shadow-mode audit: which rows would merge, and which merges look wrong?"""
import sqlite3
import sys
from collections import defaultdict

SUSPECT_DURATION_DELTA = 5  # seconds


def load_clusters(db_path: str) -> dict[str, list[dict]]:
    con = sqlite3.connect(db_path)
    con.row_factory = sqlite3.Row
    rows = con.execute(
        """
        SELECT v.id, v.title, v.duration_s, v.lang,
               a.canonical_key_new, a.normalized_url
        FROM   canonical_audit a
        JOIN   videos v ON v.id = a.video_id
        """
    )
    buckets: dict[str, list[dict]] = defaultdict(list)
    for r in rows:
        buckets[r["canonical_key_new"]].append(dict(r))
    con.close()
    return {k: v for k, v in buckets.items() if len(v) > 1}


def main(db_path: str) -> int:
    clusters = load_clusters(db_path)
    collapsed = sum(len(v) - 1 for v in clusters.values())
    suspect = 0

    for key, members in sorted(clusters.items()):
        durations = [m["duration_s"] for m in members if m["duration_s"]]
        if durations and (max(durations) - min(durations)) > SUSPECT_DURATION_DELTA:
            suspect += 1
            print(f"SUSPECT {key} durations={sorted(durations)}")
            for m in members:
                print(f"    {m['id']:>8}  {m['lang']}  {m['title'][:60]}")

    print(f"\n{len(clusters)} clusters, {collapsed} rows collapse, {suspect} need review")
    return 1 if suspect else 0


if __name__ == "__main__":
    sys.exit(main(sys.argv[1] if len(sys.argv) > 1 else "data/videos.db"))

Over eleven days it flagged 214 suspect clusters. Roughly 180 were the Bilibili p= bug. The rest were live-stream re-uploads that genuinely share an ID on one partner platform, which we now handle with an explicit part derived from the stream start timestamp.

Backfilling 2.3 Million Rows

The PHP path does about 26k rows/second for normalization alone, which sounds fine until you add the per-row SQLite round trip and realize the backfill would run for most of a day while the ingest cron is also trying to write. We ported the hot path to Go for the one-time backfill, validated it against the PHP implementation on a 50k-row sample until the outputs were byte-identical, and then let it run.

package main

import (
    "database/sql"
    "log"
    "runtime"
    "sync"

    _ "modernc.org/sqlite"
)

type job struct {
    id  int64
    url string
}

type result struct {
    id  int64
    key string
}

const dsn = "file:data/videos.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(10000)"

func main() {
    // Separate handles: one reader cursor + one writer tx on a single conn deadlocks.
    rdb, err := sql.Open("sqlite", dsn+"&mode=ro")
    if err != nil {
        log.Fatal(err)
    }
    defer rdb.Close()

    wdb, err := sql.Open("sqlite", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer wdb.Close()
    wdb.SetMaxOpenConns(1) // SQLite serializes writers anyway; be explicit about it

    jobs := make(chan job, 8192)
    results := make(chan result, 8192)

    go func() {
        defer close(jobs)
        rows, err := rdb.Query(`SELECT id, canonical_url FROM videos
                                WHERE canonical_key IS NULL ORDER BY id`)
        if err != nil {
            log.Fatal(err)
        }
        defer rows.Close()
        for rows.Next() {
            var j job
            if err := rows.Scan(&j.id, &j.url); err != nil {
                log.Fatal(err)
            }
            jobs <- j
        }
    }()

    var wg sync.WaitGroup
    for i := 0; i < runtime.NumCPU(); i++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            for j := range jobs {
                // Canonicalize is the Go port of UrlNormalizer + CanonicalResolver.
                if key, ok := Canonicalize(j.url); ok {
                    results <- result{id: j.id, key: key}
                }
            }
        }()
    }
    go func() { wg.Wait(); close(results) }()

    const batchSize = 5000
    n := 0
    tx, err := wdb.Begin()
    if err != nil {
        log.Fatal(err)
    }
    stmt, err := tx.Prepare(`UPDATE videos SET canonical_key = ? WHERE id = ?`)
    if err != nil {
        log.Fatal(err)
    }

    for r := range results {
        if _, err := stmt.Exec(r.key, r.id); err != nil {
            log.Fatalf("row %d: %v", r.id, err)
        }
        if n++; n%batchSize == 0 {
            stmt.Close()
            if err := tx.Commit(); err != nil {
                log.Fatal(err)
            }
            log.Printf("committed %d rows", n)
            if tx, err = wdb.Begin(); err != nil {
                log.Fatal(err)
            }
            if stmt, err = tx.Prepare(`UPDATE videos SET canonical_key = ? WHERE id = ?`); err != nil {
                log.Fatal(err)
            }
        }
    }

    stmt.Close()
    if err := tx.Commit(); err != nil {
        log.Fatal(err)
    }
    log.Printf("done: %d rows", n)
}

The backfill wrote the whole set in 4 minutes 12 seconds. The unique index on canonical_key was created afterwards, not before — with the index in place, the same run took over an hour because every batch was fighting page splits.

What Broke After Deploy

None of this is the interesting part. The interesting part is what merging 300k rows does to everything downstream:

Roughly 41k watch-page slugs became 301 redirects overnight. We keep a permanent slug_redirects table rather than letting them 404, because Google had already indexed a lot of them and the market we care about is one where organic search is most of our traffic.
Cloudflare kept serving the old pages. Purge-by-prefix on the watch path, then a full LiteSpeed page-cache clear on origin. A cache purge that only hits one layer of a three-layer stack is worse than no purge — you get inconsistent pages depending on which edge POP a user lands on.
Vary: Accept-Language interacted badly with the merge. Merged rows inherited the title from whichever alias had the longest string, which for CJK titles is almost always the shortest semantic title, because CJK conveys more per character. We now pick the title by matching the row's lang to the request locale and fall back to character-count-weighted length.
FTS5 trigram tokenizer needs at least three characters to match. After dedup, search felt better on long queries and identical on two-character CJK queries, which is a limitation of the tokenizer, not the pipeline. We prefix-pad short queries against a separate bigram column.

The numbers, four weeks after: duplicate rate 14.2% → 0.3%, videos row count down 13%, FTS index 22% smaller on disk, search p95 down from 41ms to 26ms. Normalization itself costs about 38µs at p99 per URL, which is noise next to a single SQLite read.

Conclusion

If you take two things from this, take these. First, keep structural normalization and semantic extraction in separate, separately-tested stages — the moment one function does both, every provider quirk you add makes the RFC-compliant part slightly less compliant. Second, store every URL you have ever seen in an alias table and treat the canonical key as disposable. Extractors are wrong on their first version and their fifth version, and the only difference between a bad afternoon and an unrecoverable data loss is whether you kept the inputs.

Run it in shadow mode for a week before you merge a single row. The 214 clusters that audit flagged would all have been silent, permanent data corruption.

Coordinating Video Region Routers With etcd and Live Watch Reloads

ahmet gedik — Tue, 11 Aug 2026 13:00:01 +0000

At DailyWatch we route every video request through a small fleet of edge PHP nodes that decide, per visitor, which regional catalog and which upstream origin to serve. A viewer in Frankfurt should hit our EU catalog shard; a viewer in São Paulo should hit the LATAM shard; and when an origin gets saturated we need to drain traffic away from it within seconds, not on the next deploy. For a long time that mapping lived in a regions.php array baked into every node and shipped through the same FTP-based release that pushes the rest of the site. That worked until it didn't: draining a hot origin meant editing a file, redeploying to every node, and clearing the DailyWatch LiteSpeed cache before the change actually took hold. Minutes of lag during an incident is unacceptable when a single origin is throwing 502s.

The fix was to pull region routing config out of the deploy artifact entirely and put it in etcd, with each edge node watching for changes and reloading in-process. This post walks through the concrete design: the key layout, an atomic writer, a Go control agent that watches and pushes to a local socket, and the PHP router that reads a warm local snapshot on every request. Everything here is runnable and reflects the tradeoffs we actually hit.

Why etcd and not just a database column

We already run SQLite with FTS5 for the catalog and search on every node, so the obvious question was: why not a routing table? The answer is the notification model. SQLite gives you great local reads but no fan-out. To learn that a value changed, a node has to poll, and polling at the frequency we'd need (sub-second during an incident) hammers disk and still leaves a window. etcd is built around exactly the primitive we want: a strongly consistent key-value store with a watch API that streams changes to every subscriber the moment a write is committed via Raft. You write once to the cluster and every edge node knows within a round-trip.

The properties that matter for routing config specifically:

Linearizable reads and writes — when we mark an origin as drained, every node sees a consistent view, no split-brain where half the fleet still sends traffic there.
Watches with revisions — every change carries a monotonic revision number, so a node that reconnects after a blip can resume from where it left off instead of missing edits.
Leases — an origin can register itself with a TTL. If the origin process dies and stops renewing, its key expires and it disappears from routing automatically.
Small values, high read locality — routing config is kilobytes, read on every request. etcd is the source of truth, but each node keeps a warm local copy so request-path reads never touch the network.

That last point is the whole architecture in one sentence: etcd is authoritative, but the request path only ever reads a local snapshot. A viewer's request must never block on a Raft round-trip.

Key layout

Flat keyspaces get unmanageable fast, so we use a prefix hierarchy that mirrors how the router thinks. Everything lives under /dw/routing/:

/dw/routing/region/<geo> — maps a Cloudflare CF-IPCountry code (or a continent bucket) to a catalog shard id. Value is a small JSON object.
/dw/routing/origin/<id> — one key per upstream origin, written with a lease so dead origins vanish.
/dw/routing/policy/global — feature-flag-ish knobs: default shard, failover order, whether to honor client hints.

A region value looks like this:

{
  "shard": "eu-1",
  "origins": ["fra-a", "fra-b", "ams-a"],
  "weight": {"fra-a": 50, "fra-b": 30, "ams-a": 20},
  "updated": "2026-07-30T09:12:00Z"
}

Storing JSON blobs rather than one etcd key per field is deliberate. A region's routing decision needs to change atomically — you never want a node to observe the new origin list but the old weights. One key, one value, one revision.

Writing config atomically from the control plane

The control plane is a thin CLI our on-call engineers run (and that our automated drain logic calls). The critical requirement: a multi-key change — say, draining fra-a while bumping fra-b's weight across three regions — must land as a single transaction so no node ever sees a half-applied state. etcd gives us this with a Txn. Here's the writer in Python using etcd3:

import json
import etcd3

client = etcd3.client(host="10.0.0.10", port=2379)

def drain_origin(origin_id: str, regions: dict[str, dict]) -> None:
    """Remove origin_id from the given regions and rebalance, atomically.

    `regions` maps region key -> the region's current parsed JSON value.
    """
    success_ops = []
    compares = []

    for key, value in regions.items():
        if origin_id not in value.get("origins", []):
            continue

        # Guard: only apply if the value hasn't changed under us.
        _, meta = client.get(key)
        compares.append(
            client.transactions.mod(key) == meta.mod_revision
        )

        value["origins"] = [o for o in value["origins"] if o != origin_id]
        weights = {o: w for o, w in value.get("weight", {}).items()
                   if o != origin_id}
        # Renormalize remaining weights to sum to 100.
        total = sum(weights.values()) or 1
        value["weight"] = {o: round(w * 100 / total) for o, w in weights.items()}
        value["updated"] = "2026-07-30T09:12:00Z"  # inject real UTC in prod

        success_ops.append(
            client.transactions.put(key, json.dumps(value, separators=(",", ":")))
        )

    if not success_ops:
        return

    ok, _ = client.transaction(compare=compares, success=success_ops, failure=[])
    if not ok:
        raise RuntimeError("config changed concurrently; retry the drain")

The compare clauses turn this into an optimistic-concurrency write: every region key must still be at the mod_revision we read, or the whole transaction fails and we retry. This is what stops two concurrent drains from clobbering each other. The renormalization keeps weights summing to 100 so the router's selection logic stays simple — it can treat weights as a probability distribution without re-summing on every request.

One detail worth calling out: we write compact JSON (separators=(",", ":")). Routing values are read constantly and the difference between pretty-printed and compact adds up in etcd's storage and in the watch payload size across a fleet.

The watch-and-push agent on each edge node

Here's the part that earns its keep. PHP under LiteSpeed is process-per-request; there's no long-lived PHP process to hold an etcd watch open. So each edge node runs a tiny Go sidecar — the control agent — that holds exactly one watch on the /dw/routing/ prefix, maintains an in-memory snapshot, and writes that snapshot to a local file plus notifies via a Unix socket. PHP then reads the file. The Go agent is the only thing that ever talks to etcd from the edge.

package main

import (
    "context"
    "encoding/json"
    "log"
    "os"
    "path/filepath"
    "sync"
    "time"

    clientv3 "go.etcd.io/etcd/client/v3"
)

const (
    prefix   = "/dw/routing/"
    snapshot = "/dev/shm/dw-routing.json"
)

type Agent struct {
    mu    sync.RWMutex
    state map[string]json.RawMessage
}

func (a *Agent) writeSnapshot() error {
    a.mu.RLock()
    data, err := json.Marshal(a.state)
    a.mu.RUnlock()
    if err != nil {
        return err
    }
    // Write-then-rename so PHP never reads a half-written file.
    tmp := snapshot + ".tmp"
    if err := os.WriteFile(tmp, data, 0o644); err != nil {
        return err
    }
    return os.Rename(tmp, snapshot)
}

func (a *Agent) Run(ctx context.Context, cli *clientv3.Client) error {
    // 1. Seed the full current state, capturing the revision.
    resp, err := cli.Get(ctx, prefix, clientv3.WithPrefix())
    if err != nil {
        return err
    }
    a.state = make(map[string]json.RawMessage, len(resp.Kvs))
    for _, kv := range resp.Kvs {
        a.state[string(kv.Key)] = json.RawMessage(kv.Value)
    }
    if err := a.writeSnapshot(); err != nil {
        return err
    }

    // 2. Watch from the revision right after the snapshot — no gap, no dupes.
    wch := cli.Watch(ctx, prefix,
        clientv3.WithPrefix(),
        clientv3.WithRev(resp.Header.Revision+1))

    for wresp := range wch {
        if err := wresp.Err(); err != nil {
            return err // caller reconnects with backoff
        }
        a.mu.Lock()
        for _, ev := range wresp.Events {
            key := string(ev.Kv.Key)
            if ev.Type == clientv3.EventTypeDelete {
                delete(a.state, key)
            } else {
                a.state[key] = json.RawMessage(ev.Kv.Value)
            }
        }
        a.mu.Unlock()
        if err := a.writeSnapshot(); err != nil {
            log.Printf("snapshot write failed: %v", err)
        }
    }
    return ctx.Err()
}

func main() {
    cli, err := clientv3.New(clientv3.Config{
        Endpoints:   []string{"10.0.0.10:2379", "10.0.0.11:2379", "10.0.0.12:2379"},
        DialTimeout: 5 * time.Second,
    })
    if err != nil {
        log.Fatal(err)
    }
    defer cli.Close()

    agent := &Agent{}
    ctx := context.Background()
    for {
        if err := agent.Run(ctx, cli); err != nil {
            log.Printf("watch loop ended: %v; reconnecting", err)
            time.Sleep(2 * time.Second) // backoff before re-seed
        }
    }
}

Three things make this correct rather than merely working:

Snapshot revision → watch revision continuity. We seed from Get, remember resp.Header.Revision, and start the watch at revision+1. That closes the gap where a write could land between the seed and the watch. No missed edits, no double-applied edits.
Write-then-rename. os.Rename is atomic on the same filesystem, so PHP either reads the old complete file or the new complete file — never a torn read. We put the snapshot on /dev/shm (tmpfs) so reads are pure memory, no disk I/O on the request path.
Reconnect re-seeds. When the watch channel errors (network blip, etcd leader election), the loop restarts and re-seeds the full state. etcd's compaction means you can't always resume an old revision after a long disconnect, so a clean re-seed is the safe default.

Reading config in the PHP request path

Now the router. On every request, PHP reads the tmpfs snapshot, picks a shard from the visitor's country (which Cloudflare hands us in CF-IPCountry), and selects a weighted origin. Because the file is on tmpfs and PHP 8.4's json_decode is fast, this adds microseconds, not milliseconds. We also stat the file mtime and cache the decoded structure in APCu so repeated requests in the same worker don't re-decode.

<?php
declare(strict_types=1);

final class RegionRouter
{
    private const SNAPSHOT = '/dev/shm/dw-routing.json';

    /** @var array<string, mixed> */
    private array $config;

    public function __construct()
    {
        $mtime = @filemtime(self::SNAPSHOT) ?: 0;
        $cacheKey = 'dw:routing:' . $mtime;

        $cached = apcu_fetch($cacheKey, $hit);
        if ($hit) {
            $this->config = $cached;
            return;
        }

        $raw = @file_get_contents(self::SNAPSHOT);
        $this->config = $raw !== false
            ? (json_decode($raw, true, 32, JSON_THROW_ON_ERROR) ?: [])
            : [];

        // mtime in the key means stale entries expire naturally on change.
        apcu_store($cacheKey, $this->config, 300);
    }

    public function resolve(string $country): array
    {
        $regionKey = "/dw/routing/region/{$country}";
        $policy = $this->decode('/dw/routing/policy/global');
        $region = $this->decode($regionKey)
            ?? $this->decode('/dw/routing/region/' . ($policy['default'] ?? 'US'));

        if ($region === null) {
            // Fail open to a hardcoded safe default rather than 500.
            return ['shard' => 'us-1', 'origin' => 'iad-a'];
        }

        return [
            'shard'  => $region['shard'],
            'origin' => $this->pickWeighted($region['origins'], $region['weight'], $country),
        ];
    }

    /** @return array<string, mixed>|null */
    private function decode(string $key): ?array
    {
        return isset($this->config[$key])
            ? json_decode($this->config[$key], true, 16, JSON_THROW_ON_ERROR)
            : null;
    }

    /**
     * Deterministic weighted pick: same visitor sticks to the same origin
     * as long as weights are unchanged (sticky routing without a session).
     */
    private function pickWeighted(array $origins, array $weights, string $seed): string
    {
        $total = array_sum($weights) ?: 1;
        $point = (crc32($seed . implode($origins)) % $total);
        foreach ($origins as $origin) {
            $point -= ($weights[$origin] ?? 0);
            if ($point < 0) {
                return $origin;
            }
        }
        return $origins[0] ?? 'iad-a';
    }
}

// Usage in the front controller:
$router  = new RegionRouter();
$country = $_SERVER['HTTP_CF_IPCOUNTRY'] ?? 'US';
$route   = $router->resolve($country);
header('X-DW-Shard: ' . $route['shard']);
// ... proxy or serve from $route['origin'] ...

Note the decode() layer: the snapshot stores each etcd value as its raw JSON string, exactly as etcd holds it, so the agent never has to understand the schema. PHP decodes lazily, only the keys it touches for this request. The weighted pick is seeded by country plus the origin list, which gives us sticky routing — the same visitor lands on the same origin across requests as long as the config is stable, which keeps LiteSpeed and Cloudflare edge caches warm per-origin. When we drain an origin, the origin list changes, the seed changes, and traffic reshuffles cleanly.

The filemtime-keyed APCu entry is a small trick that matters: when the agent renames a new snapshot into place, the mtime changes, the cache key changes, and every PHP worker picks up the new config on its next request with zero explicit invalidation. No cache-clear step, no coordination.

Failure modes and how we handle them

A distributed config system is only as good as its behavior when things break. The ones we actually planned for:

etcd unreachable from a node. The Go agent keeps serving the last snapshot it wrote. PHP keeps reading it. Routing is stale but functional. Config that can't update is far better than requests that 500. This is the single most important property: the request path has no runtime dependency on etcd being up.
Snapshot missing or corrupt. resolve() fails open to a hardcoded us-1/iad-a default. A visitor always gets a video, even if it's not the geographically optimal origin.
etcd compaction after a long disconnect. The agent can't resume an old revision, so on reconnect it re-seeds the full prefix and continues. Idempotent by construction.
Origin crashes. Because origins register their own /dw/routing/origin/<id> key under a lease, a crashed origin's key expires within the TTL and the control plane's rebalance logic (or a simple watcher) removes it from region lists. We keep the lease TTL at 10 seconds — long enough to survive a GC pause, short enough to drain a dead box fast.
Thundering herd on re-seed. If the whole etcd cluster restarts, every agent re-seeds at once. The Get with prefix is cheap (kilobytes), but we still jitter the reconnect backoff so a 500-node fleet doesn't stampede a freshly-elected leader.

One thing we explicitly did not do: watch etcd directly from PHP or open a connection per request. Under LiteSpeed's process-per-request model that would mean a new TCP connection and watch setup on every hit — catastrophic. The single-agent-per-node pattern is what makes etcd viable behind a PHP front end at all.

What changed operationally

Before, draining a hot origin was a code edit, an FTP deploy to every node, and a cache flush — call it three to five minutes of elevated error rate while the change propagated. Now it's one transactional write to etcd, and the entire fleet reflects it in well under a second because every node is already holding a watch. The control-plane CLI is scriptable, so our automated health checks can drain a misbehaving origin without a human in the loop, and roll it back the same way once it recovers.

The design comes down to a clean split of responsibilities: etcd is the strongly-consistent source of truth, a single Go agent per node turns pushes into a local tmpfs snapshot, and PHP reads that warm snapshot on every request with a hardcoded fail-open default. No request ever blocks on the network for config, no deploy is needed to change routing, and a total etcd outage degrades to stale-but-working rather than down. If you're running edge routing in front of a PHP/LiteSpeed stack and you're still baking config into your deploy artifact, moving it into etcd with a watch sidecar is one of the higher-leverage changes you can make.

Building a Video Transcoding Job Queue with Python RQ and Redis

ahmet gedik — Tue, 11 Aug 2026 03:00:02 +0000

Our multi-region fetch cron pulls trending video metadata for 8 regions on a staggered schedule. Until a few months ago, the same PHP process that wrote those rows into SQLite also shelled out to ffmpeg to build preview artifacts: one 6-second WebM loop and three WebP thumbnails per new video. That was fine at 5 videos per region. It fell apart at 40. LiteSpeed kills a request at 180 seconds, and a single 720p loop takes 4 to 11 seconds depending on source bitrate. The cron would die somewhere inside region three, leave half-written .webm files on disk, and the next run would redo all of it because nothing had been marked complete.

The fix was not a cleverer ffmpeg invocation. It was moving the transcode work out of the request lifecycle entirely. What follows is the queue that replaced it: Redis as the broker, Python RQ workers on a small VPS, PHP 8.4 as the producer, and finished artifacts shipped back through the same FTP automation the rest of the site deploys with. The preview loops on TrendVidStream all come out of this pipeline.

Why RQ and not Celery

Honest answer: Celery works too. I picked RQ for three reasons that hold at this size.

One dependency. RQ needs Redis and nothing else. Celery's broker/result-backend split invites a second piece of infrastructure, and I did not want to run RabbitMQ for a workload that peaks around 400 jobs a day.
Failed jobs are a real dead letter queue. RQ keeps a FailedJobRegistry with the full traceback and the original arguments. Requeueing is one command, not a custom replay script.
The source is small enough to read. When a job vanished, I read the worker loop and found my own bug in an afternoon.

The tradeoff is real: RQ workers fork per job, no threads and no eventlet. That rules out Windows and makes it a bad fit for 5ms jobs. Against a 7-second ffmpeg run, the fork cost is noise.

The shape of the pipeline

Four moving parts:

PHP cron (producer). After the region loop writes new video rows, it pushes a compact JSON intent onto a Redis list. It does not know RQ exists.
Bridge worker. One Python process doing BLPOP on that list, routing by priority, and enqueueing real RQ jobs.
Transcode workers. Three RQ workers, each running ffmpeg in a subprocess with a hard timeout.
Publisher. A dependent job that uploads artifacts over FTP and only then marks the row visible in SQLite.

Why the bridge, instead of having PHP enqueue directly into RQ? RQ serializes jobs with pickle by default, and its internal job hash layout is not a public contract; it shifted between the 1.x and 2.x lines. You can write a PHP-side serializer that RQ will eat, and people have, but you are then pinned to an implementation detail forever. A 50-line bridge keeps the RQ-specific format in Python where it belongs and gives one obvious place to put dedup and rate control.

The PHP producer

The producer's only real job is idempotency. A video id must never be queued twice, because the expensive part is not the enqueue, it is the 7 seconds of VP9 encoding on the other end.

<?php
declare(strict_types=1);

final class TranscodeIntentQueue
{
    private const LIST_KEY = 'vw:transcode:intents';
    private const SEEN_KEY = 'vw:transcode:seen';
    private const SEEN_TTL = 604800; // 7 days

    public function __construct(private readonly Redis $redis) {}

    public static function connect(string $host = '127.0.0.1', int $port = 6379): self
    {
        $r = new Redis();
        $r->connect($host, $port, 2.0);
        $r->setOption(Redis::OPT_READ_TIMEOUT, 2.0);
        return new self($r);
    }

    /** @param list<array{id:string,region:string,duration:int}> $videos */
    public function pushBatch(array $videos, int $priority = 5): int
    {
        $pushed = 0;
        foreach ($videos as $v) {
            // sAdd returns 0 when the id was already a member -> already queued.
            if ($this->redis->sAdd(self::SEEN_KEY, $v['id']) === 0) {
                continue;
            }
            $this->redis->rPush(self::LIST_KEY, json_encode([
                'video_id'  => $v['id'],
                'region'    => $v['region'],
                'duration'  => $v['duration'],
                'priority'  => $priority,
                'queued_at' => time(),
            ], JSON_THROW_ON_ERROR));
            $pushed++;
        }
        $this->redis->expire(self::SEEN_KEY, self::SEEN_TTL);
        return $pushed;
    }
}

// Called at the end of each region pass in the fetch cron.
$queue = TranscodeIntentQueue::connect();
$n = $queue->pushBatch($repo->newVideosSince($lastRun), $region === 'US' ? 1 : 5);
fwrite(STDERR, sprintf('[transcode] queued %d intents for %s%s', $n, $region, PHP_EOL));

That trailing EXPIRE slides the whole set forward on every push, so in practice the set never expires while the cron is healthy. That is deliberate. I would rather carry a few thousand stale ids in Redis than re-encode a back catalogue because the key aged out mid-week. If the cron stops for seven days I have a bigger problem than duplicate transcodes.

One thing worth calling out: the dedup happens before the push, not inside the worker. Producer-side dedup is cheap and exact. Worker-side dedup means you have already paid for the network hop and the fork.

The bridge

BLPOP with a short timeout, decode, route, enqueue. Nothing clever.

# bridge.py -- turns PHP intents into RQ jobs.
import json
import logging
import os
import signal
import sys

from redis import Redis
from rq import Queue, Retry

LIST_KEY = 'vw:transcode:intents'
_queues = {}
log = logging.getLogger('bridge')
running = True


def _stop(signum, frame):
    global running
    running = False


def queue_for(priority, conn):
    name = 'transcode_high' if priority <= 2 else 'transcode_default'
    if name not in _queues:
        _queues[name] = Queue(name, connection=conn, default_timeout=900)
    return _queues[name]


def main():
    signal.signal(signal.SIGTERM, _stop)
    signal.signal(signal.SIGINT, _stop)
    conn = Redis.from_url(os.environ.get('REDIS_URL', 'redis://127.0.0.1:6379/0'))

    while running:
        item = conn.blpop(LIST_KEY, timeout=5)
        if item is None:
            continue
        try:
            intent = json.loads(item[1])
        except ValueError:
            log.error('undecodable intent dropped: %r', item[1][:200])
            continue

        q = queue_for(intent.get('priority', 5), conn)
        q.enqueue(
            'tasks.transcode_preview',
            video_id=intent['video_id'],
            region=intent['region'],
            source_duration=intent['duration'],
            job_id='preview:' + intent['video_id'],
            retry=Retry(max=3, interval=[30, 120, 600]),
            job_timeout=900,
            result_ttl=3600,
            failure_ttl=259200,
        )
        log.info('enqueued %s on %s', intent['video_id'], q.name)
    return 0


if __name__ == '__main__':
    logging.basicConfig(level=logging.INFO, format='%(asctime)s %(levelname)s %(message)s')
    sys.exit(main())

A correction to a belief I held for a while: passing job_id does not give you unique-job semantics. RQ will happily push the same id onto the queue list twice and run it twice; the second enqueue just overwrites the job hash. The id is for lookups and log correlation. The actual dedup is that Redis set in the PHP producer, plus an existence check inside the task itself.

BLPOP costs one blocking connection. If you run the bridge under systemd, set Restart=always and keep the timeout non-infinite, so a Redis failover surfaces as a reconnect rather than a process wedged forever on a dead socket.

The transcode task

The important property here is that partial output is never visible. Everything lands in a staging directory and gets moved into place with a single rename.

# tasks.py
import os
import shutil
import subprocess
import tempfile
from dataclasses import dataclass
from pathlib import Path

WORK_ROOT = Path(os.environ.get('TRANSCODE_WORK', '/var/lib/vw/work'))
OUT_ROOT = Path(os.environ.get('TRANSCODE_OUT', '/var/lib/vw/out'))
FFMPEG = os.environ.get('FFMPEG_BIN', '/usr/bin/ffmpeg')


class TranscodeError(RuntimeError):
    pass


@dataclass(frozen=True)
class PreviewSpec:
    loop_seconds: int = 6
    width: int = 480
    crf: int = 34
    thumb_count: int = 3


def _run(cmd, timeout):
    proc = subprocess.run(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE,
                          timeout=timeout, check=False, start_new_session=True)
    if proc.returncode != 0:
        tail = proc.stderr.decode('utf-8', 'replace')[-800:]
        raise TranscodeError('ffmpeg exit ' + str(proc.returncode) + ': ' + tail)


def transcode_preview(video_id, region, source_duration, spec=PreviewSpec()):
    source = WORK_ROOT / region / (video_id + '.mp4')
    if not source.exists():
        raise TranscodeError('source missing: ' + str(source))

    final_dir = OUT_ROOT / video_id
    if (final_dir / 'loop.webm').exists():
        return {'video_id': video_id, 'skipped': True}

    # Sample from 25% in -- intros are usually black frames or sponsor cards.
    start = max(0, int(source_duration * 0.25))
    staging = Path(tempfile.mkdtemp(prefix='tc-', dir=str(OUT_ROOT)))
    try:
        _run([FFMPEG, '-nostdin', '-loglevel', 'error', '-y',
              '-ss', str(start), '-t', str(spec.loop_seconds), '-i', str(source),
              '-an', '-c:v', 'libvpx-vp9', '-crf', str(spec.crf), '-b:v', '0',
              '-cpu-used', '4', '-row-mt', '1', '-threads', '2',
              '-vf', 'scale=' + str(spec.width) + ':-2,fps=24',
              str(staging / 'loop.webm')], timeout=600)

        step = max(1, source_duration // (spec.thumb_count + 1))
        for i in range(1, spec.thumb_count + 1):
            _run([FFMPEG, '-nostdin', '-loglevel', 'error', '-y',
                  '-ss', str(step * i), '-i', str(source), '-frames:v', '1',
                  '-vf', 'scale=' + str(spec.width) + ':-2', '-quality', '78',
                  str(staging / ('thumb-' + str(i) + '.webp'))], timeout=90)
    except BaseException:
        shutil.rmtree(staging, ignore_errors=True)
        raise

    try:
        os.replace(staging, final_dir)   # atomic; fails if final_dir is non-empty
    except OSError:
        shutil.rmtree(staging, ignore_errors=True)
        return {'video_id': video_id, 'skipped': True}

    return {'video_id': video_id, 'region': region,
            'bytes': sum(p.stat().st_size for p in final_dir.iterdir())}

Three details that were not obvious to me at the start:

-nostdin is not optional. Without it, ffmpeg reads from the worker's stdin, and a forked RQ worker shares that descriptor. One malformed input file and ffmpeg consumes whatever else is queued on stdin, then the worker behaves strangely for reasons that make no sense in the traceback.
-ss before -i seeks by keyframe and is roughly instant. After -i it decodes from the start. On a 40-minute source that is the difference between 200ms and 30 seconds.
start_new_session=True puts ffmpeg in its own process group, so a subprocess.TimeoutExpired kill does not take the worker down with it, and a runaway encode does not survive as an orphan holding a file handle on the staging directory.

BaseException rather than Exception in that cleanup is intentional. RQ enforces job timeouts by raising a JobTimeoutException inside the worker via a signal, and depending on version and lineage that can bypass a plain except Exception. Catching BaseException and re-raising means a timed-out job still cleans up its staging directory.

Timeouts, retries, and what actually deserves a retry

The defaults will hurt you. RQ's default job timeout is 180 seconds, which is exactly the LiteSpeed limit I was trying to escape. A 4K source with a slow CRF pass can take several minutes, so the queue carries default_timeout=900 and each job sets job_timeout explicitly.

Retries need to be split by failure class, because most of them are pointless:

Source file missing. Retrying helps: the downloader may still be writing. Backoff of 30s, 120s, 600s covers it.
ffmpeg exit 1 on a corrupt container. Retrying is pure waste. Three attempts, three identical failures, 20 minutes of worker time burned.
OOM kill. The worker dies with the job. RQ's death penalty never fires because nothing is alive to raise it, so the job sits in the StartedJobRegistry until its TTL expires and gets moved to failed. That path is slow and silent, and it is the main reason I cap worker memory and recycle workers.

The pragmatic version is a narrow retry: raise a distinct SourceNotReadyError for the retryable case and let everything else go straight to the failed registry, where I can look at it. A dead letter queue you actually read is worth more than a retry policy that hides the same bug three times.

Worker sizing and not melting the box

The instinct on an 8-core VPS is eight workers. That is wrong for ffmpeg, because ffmpeg is already parallel. Eight workers each spawning a default-threaded encode gives you 60-odd threads fighting over 8 cores, and per-job latency roughly triples while total throughput barely moves.

What works here: three workers, -threads 2 per encode, one core left for Redis and the bridge. Measured on our workload, p50 went from 19s per job (8 workers) to 6.8s (3 workers), with p95 at 19s. A 40-video US batch drains in about two and a half minutes.

# worker.py
import logging
import os
import resource

from redis import Redis
from rq import Queue, Worker

log = logging.getLogger('worker')


def cap_memory(soft_mb=1024):
    limit = soft_mb * 1024 * 1024
    resource.setrlimit(resource.RLIMIT_AS, (limit, limit))


def record_failure(job, exc_type, exc_value, traceback):
    job.connection.hincrby('vw:transcode:failcount',
                           job.kwargs.get('video_id', 'unknown'), 1)
    log.error('job %s failed: %s: %s', job.id, exc_type.__name__, exc_value)
    return True  # let RQ continue to its default handling


def main():
    cap_memory()
    conn = Redis.from_url(os.environ.get('REDIS_URL', 'redis://127.0.0.1:6379/0'))
    # Queue order == priority order: high is fully drained before default.
    queues = [Queue('transcode_high', connection=conn),
              Queue('transcode_default', connection=conn)]
    worker = Worker(queues, connection=conn,
                    name=os.environ.get('WORKER_NAME'),
                    exception_handlers=[record_failure])
    worker.work(with_scheduler=True, max_jobs=200)


if __name__ == '__main__':
    logging.basicConfig(level=logging.INFO)
    main()

max_jobs=200 plus Restart=always in the systemd unit is a deliberate recycle: the worker exits cleanly after 200 jobs and systemd starts a fresh one. It papers over slow leaks in long-lived processes, and it gives you a natural point where a new deploy picks up new code without a manual restart. with_scheduler=True is what makes the retry intervals actually fire; without it, retried jobs are re-enqueued immediately regardless of your backoff list.

Publishing over FTP without racing the site deploy

Our hosting is shared LiteSpeed with FTP-only access, so the artifacts have to be pushed rather than mounted. The rule that matters: a video row must not become searchable before its preview exists, or the grid renders a broken loop element. So the publish step is a dependent job, and the SQLite write, including the FTS5 index update, happens last.

# publish.py
import ftplib
import os
import sqlite3
from pathlib import Path


def publish_preview(video_id, region, **_):
    src = Path(os.environ['TRANSCODE_OUT']) / video_id
    files = sorted(p for p in src.iterdir() if p.is_file())
    if not files:
        raise RuntimeError('nothing to publish for ' + video_id)

    ftp = ftplib.FTP_TLS(os.environ['FTP_HOST'])
    ftp.login(os.environ['FTP_USER'], os.environ['FTP_PASS'])
    ftp.prot_p()
    remote_dir = '/public_html/media/previews/' + video_id
    try:
        ftp.mkd(remote_dir)
    except ftplib.error_perm:
        pass  # already there

    for path in files:
        tmp = remote_dir + '/.' + path.name + '.part'
        with path.open('rb') as fh:
            ftp.storbinary('STOR ' + tmp, fh, blocksize=65536)
        ftp.rename(tmp, remote_dir + '/' + path.name)  # atomic-ish on the server
    ftp.quit()

    db = sqlite3.connect(os.environ['SQLITE_PATH'], timeout=30)
    try:
        db.execute('PRAGMA busy_timeout = 30000')
        db.execute('UPDATE videos SET preview_ready = 1 WHERE video_id = ?', (video_id,))
        db.commit()
    finally:
        db.close()
    return {'video_id': video_id, 'files': len(files)}


# In the bridge, chain it so publish only runs on a successful transcode:
#   from rq.job import Dependency
#   t = q.enqueue('tasks.transcode_preview', video_id=vid, region=r, source_duration=d)
#   q.enqueue('publish.publish_preview', video_id=vid, region=r,
#             depends_on=Dependency(jobs=[t], allow_failure=False))

Upload to a dotfile then rename: FTP has no atomic upload, and without this the site can serve a truncated WebM to whoever requests it mid-transfer. The leading dot also keeps partials out of any directory listing the site itself walks.

The SQLite side needs busy_timeout. The publisher and the fetch cron write to the same file, and the default behaviour is an immediate database is locked rather than a wait. Since preview_ready is what the search query filters on, and the FTS5 content table is populated from the same row, an FTS5 rebuild triggered mid-publish would otherwise fail with no useful message.

What broke in production

Staging directories accumulated. Early version cleaned up in except Exception. OOM kills and timeouts skipped it. Fixed with BaseException plus a nightly sweep of tc-* directories older than a day.
A duplicate enqueue during a cron rerun ran two identical encodes. Both finished; the second lost the os.replace race and returned skipped. Harmless, but it is why the existence check at the top of the task exists.
Redis maxmemory-policy was allkeys-lru. Under pressure, Redis evicted job hashes, and workers popped ids pointing at nothing. For a broker the correct setting is noeviction, so a full Redis fails loudly on write instead of silently deleting your queue.
rq info --interval 2 is the whole monitoring stack. Queue depth and worker state in one line. Alerting is a cron that counts the FailedJobRegistry and emails when it grows.

Wrapping up

The architectural win was not RQ specifically. It was the boundary: PHP writes an intent, Python does the CPU work, and no user-facing process waits on ffmpeg. Everything after that is the same handful of rules any queue needs. Dedup at the producer. Make jobs idempotent, so a duplicate is boring rather than corrupting. Stage output and publish with a rename. Retry only what a retry can actually fix, and read your dead letter queue. Size workers to the parallelism of the work, not the core count.

If you are starting from a cron that is quietly timing out, move the slowest single step out first and leave the rest alone. That change took an afternoon and removed the failure mode entirely; the priority queues, the dependency chaining, and the FTP staging all came later, once I could see what was actually failing.

Building a Serverless Video Thumbnail API with Cloudflare Workers

ahmet gedik — Mon, 10 Aug 2026 16:00:01 +0000

Last quarter our edge logs surfaced something embarrassing: 38,000 requests an hour to /thumb/* on a single European POP, and every one of them was a PHP request whose entire job was to 302 redirect the browser to i.ytimg.com. At ViralVidVault we track viral videos across the EU, and a single trending grid renders 40–60 thumbnails. Multiply that by real traffic and our LiteSpeed origin was burning worker processes proxying images it doesn't even own.

Three problems were stacked on top of each other:

Origin load. Every thumbnail was a full PHP request against the LiteSpeed SAPI, competing with actual page rendering and SQLite reads.
A GDPR problem. Redirecting an EU visitor to i.ytimg.com means their browser makes a direct third-party request to Google's CDN — a request we are not allowed to fire before consent.
Broken Core Web Vitals. We never knew a thumbnail's dimensions until it arrived, so Cumulative Layout Shift punished us, and we were shipping JPEGs to browsers that would happily accept WebP or AVIF.

I moved the whole thing onto a Cloudflare Worker. Below is the architecture, the URL-signing scheme, the actual Worker code, and the Python and Go tooling I used to warm and load-test it before it went live.

Why an edge Worker instead of resizing at the origin

The naive fix is to resize in PHP with GD or Imagick and cache the output on disk. I ran that for a week and hated it. Image resizing is CPU-bound, and on shared LiteSpeed hosting CPU is exactly the resource you're rationed on. Every cold thumbnail stole cycles from page rendering, and the disk cache didn't help visitors in Warsaw or Lisbon because the bytes still traveled from one origin in one datacenter.

A Worker flips all of that:

It runs in 300+ cities, so a visitor in Madrid is served from Madrid, not from wherever your origin happens to live.
The caches.default API gives you per-object control over the cache key — you can key on the negotiated image format, not just the URL.
Cloudflare's image resizing runs in the fetch() cf options, so you never allocate a pixel buffer in your own code.
You can strip cookies, referrers, and other PII at the edge before anything reaches a third party.
It decouples image traffic from your origin entirely. My PHP process count for /thumb/* went to zero.

The mental model that made it click for me: the Worker is a signed, format-aware caching reverse proxy in front of YouTube's thumbnail CDN. The two words that carry all the weight there are "signed" and "caching."

Signing thumbnail URLs so the Worker can't become an open proxy

The moment you put a public endpoint like img.viralvidvault.com/t/<id> on the internet, someone will try to use it to proxy arbitrary content, run up your resize quota, or poison your cache with junk keys. The fix is to make the app the only thing that can mint valid URLs, using an HMAC the Worker can verify but nobody else can forge.

This is a non-secret integrity check, not encryption — the video ID and width are public. All the signature does is prove we generated this exact combination of parameters. Here is the signer in our PHP 8.4 app, which reads trending videos out of SQLite and renders the grid:

<?php
// app/Helpers/ThumbSigner.php — mints signed, cacheable thumbnail URLs
declare(strict_types=1);

final class ThumbSigner
{
    private const WORKER_BASE = 'https://img.viralvidvault.com';
    private const TTL = 86400 * 30; // 30 days

    public function __construct(private readonly string $secret) {}

    public function url(string $videoId, int $width = 320, string $quality = 'hqdefault'): string
    {
        $exp = time() + self::TTL;
        $payload = sprintf('%s|%d|%s|%d', $videoId, $width, $quality, $exp);
        // 16 bytes of the digest is plenty for a non-secret integrity check
        $sig = substr(hash_hmac('sha256', $payload, $this->secret), 0, 32);

        return self::WORKER_BASE . '/t/' . rawurlencode($videoId)
            . '?w=' . $width
            . '&q=' . rawurlencode($quality)
            . '&exp=' . $exp
            . '&sig=' . $sig;
    }
}

A few deliberate choices in there. The expiry is baked into the signed payload, so a leaked URL stops working after 30 days without any server-side state to track. I only keep 32 hex characters (128 bits) of the digest to keep URLs short — this is an integrity check, not a password, so truncation is fine. And the same $secret lives in the app config and in the Worker's environment binding; rotating it invalidates every outstanding URL at once, which is occasionally exactly what you want.

In a template it's just <img src="<?= $signer->url($video['id'], 320) ?>" width="320" height="180" loading="lazy"> — and note that because we control the width, we can finally emit correct width/height attributes and kill the layout shift.

The Worker itself

The Worker validates the signature, rejects expired links, negotiates the output format from the request's Accept header, resizes through Cloudflare's image pipeline, and caches the result with a format-specific cache key. It's deployed with wrangler deploy and the secret is bound as THUMB_SECRET.

// worker.js — deployed with `wrangler deploy`
const UPSTREAM = "https://i.ytimg.com";

async function hmacHex(secret, message) {
  const key = await crypto.subtle.importKey(
    "raw", new TextEncoder().encode(secret),
    { name: "HMAC", hash: "SHA-256" }, false, ["sign"]
  );
  const sig = await crypto.subtle.sign("HMAC", key, new TextEncoder().encode(message));
  return [...new Uint8Array(sig)].map(b => b.toString(16).padStart(2, "0")).join("");
}

function timingSafeEqual(a, b) {
  if (a.length !== b.length) return false;
  let out = 0;
  for (let i = 0; i < a.length; i++) out |= a.charCodeAt(i) ^ b.charCodeAt(i);
  return out === 0;
}

function withHeaders(res, status) {
  const r = new Response(res.body, res);
  r.headers.set("Cache-Control", "public, max-age=2592000, immutable");
  r.headers.set("X-Cache", status);
  r.headers.set("Referrer-Policy", "no-referrer");
  r.headers.delete("Set-Cookie");
  return r;
}

export default {
  async fetch(request, env, ctx) {
    const url = new URL(request.url);
    const match = url.pathname.match(/^\/t\/([A-Za-z0-9_-]{6,20})$/);
    if (!match) return new Response("Not found", { status: 404 });

    const videoId = match[1];
    const w = url.searchParams.get("w") ?? "320";
    const q = url.searchParams.get("q") ?? "hqdefault";
    const exp = url.searchParams.get("exp") ?? "0";
    const sig = url.searchParams.get("sig") ?? "";

    if (Number(exp) < Math.floor(Date.now() / 1000)) {
      return new Response("Expired", { status: 410 });
    }

    const expected = (await hmacHex(env.THUMB_SECRET, `${videoId}|${w}|${q}|${exp}`)).slice(0, 32);
    if (!timingSafeEqual(sig, expected)) {
      return new Response("Bad signature", { status: 403 });
    }

    const wantsWebp = (request.headers.get("Accept") ?? "").includes("image/webp");
    const fmt = wantsWebp ? "webp" : "jpg";
    const cacheKey = new Request(`${url.origin}/t/${videoId}?w=${w}&q=${q}&f=${fmt}`, request);
    const cache = caches.default;

    const hit = await cache.match(cacheKey);
    if (hit) return withHeaders(hit, "HIT");

    const upstream = await fetch(`${UPSTREAM}/vi/${videoId}/${q}.jpg`, {
      cf: {
        image: { width: Number(w), format: wantsWebp ? "webp" : "jpeg", quality: 82 },
        cacheEverything: true,
      },
    });
    if (!upstream.ok) return new Response("Upstream error", { status: 502 });

    const response = withHeaders(new Response(upstream.body, upstream), "MISS");
    ctx.waitUntil(cache.put(cacheKey, response.clone()));
    return response;
  },
};

The details that matter in production:

The cache key includes the negotiated format. Without f=${fmt} in the key, a Chrome user's WebP would get served to a client that only asked for JPEG, and vice versa. This is the single most common Worker caching bug I see.
timingSafeEqual matters even for a truncated integrity check. A naive === on the signature leaks timing information; the constant-time compare closes that door for basically free.
ctx.waitUntil writes to cache without blocking the response. The visitor gets their bytes; the cache.put finishes afterward.
Set-Cookie is stripped and Referrer-Policy: no-referrer is set so nothing leaks upstream and nothing marks the response uncacheable.
max-age=2592000, immutable tells the browser to never revalidate. Because the URL is signed and versioned by its parameters, a changed image means a changed URL — so immutable is safe.

Warming the cache from CI so the first EU visitor never pays

A cold cache means the first person to view a trending video eats the full resize latency. For a viral-video site that's exactly the wrong person to punish, because trends spike fast. So after every cron fetch that updates our trending table, a small Python job pre-warms the top thumbnails across our core EU regions and asserts that they actually landed in the edge cache.

#!/usr/bin/env python3
# warm_thumbs.py — pre-warm the edge cache for today's trending grid
import sys
import sqlite3
import httpx

DB = "data/backlink.db"
WARM_LIMIT = 200
REGIONS = ("DE", "FR", "GB", "NL")


def trending_ids(db_path: str, limit: int) -> list[str]:
    con = sqlite3.connect(db_path)
    con.row_factory = sqlite3.Row
    placeholders = ",".join("?" * len(REGIONS))
    rows = con.execute(
        f"SELECT DISTINCT video_id FROM videos "
        f"WHERE region IN ({placeholders}) "
        f"ORDER BY trend_score DESC LIMIT ?",
        (*REGIONS, limit),
    ).fetchall()
    con.close()
    return [r["video_id"] for r in rows]


def main() -> int:
    ids = trending_ids(DB, WARM_LIMIT)
    cold = 0
    with httpx.Client(timeout=10.0, http2=True) as client:
        for vid in ids:
            # the PHP app owns the secret; ask it for a signed URL
            signed = client.get(
                "https://viralvidvault.com/internal/sign",
                params={"v": vid, "w": 320},
            ).text.strip()
            r = client.get(signed, headers={"Accept": "image/webp"})
            r.raise_for_status()
            if r.headers.get("X-Cache") == "MISS":
                cold += 1
    print(f"warmed {len(ids)} thumbs across {len(REGIONS)} regions, {cold} were cold")
    return 0


if __name__ == "__main__":
    sys.exit(main())

The key discipline here is that the warmer never touches the HMAC secret. It asks an internal, IP-allowlisted PHP endpoint to sign the URL, then fetches it exactly like a browser would — same Accept: image/webp header, so it warms the same cache key a real Chrome user will hit. The X-Cache header the Worker sets is what makes this observable: if cold isn't dropping to near zero on the second run, something is wrong with your cache key.

Running this from a single CI box only warms one or two POPs, not all 300. That's an honest limitation. In practice warming your busiest region catches the majority of the spike, and organic traffic warms the rest within minutes — but don't tell yourself a single-region warm means the global cache is hot.

Load-testing before we trusted it

Before pointing production <img> tags at the Worker, I wanted p95 numbers, not vibes. A short Go program gives you an honest concurrent latency profile without the overhead of a full load-testing framework.

// loadtest.go — measure p50/p95/p99 latency against the worker
package main

import (
    "fmt"
    "net/http"
    "sort"
    "sync"
    "time"
)

func main() {
    const (
        target      = "https://img.viralvidvault.com/t/dQw4w9WgXcQ?w=320&q=hqdefault&exp=4102444800&sig=REPLACE"
        concurrency = 50
        total       = 5000
    )

    client := &http.Client{Timeout: 8 * time.Second}
    jobs := make(chan int, total)
    var mu sync.Mutex
    var samples []time.Duration
    var wg sync.WaitGroup

    for w := 0; w < concurrency; w++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            for range jobs {
                start := time.Now()
                req, _ := http.NewRequest("GET", target, nil)
                req.Header.Set("Accept", "image/webp")
                resp, err := client.Do(req)
                if err != nil {
                    continue
                }
                resp.Body.Close()
                mu.Lock()
                samples = append(samples, time.Since(start))
                mu.Unlock()
            }
        }()
    }

    for i := 0; i < total; i++ {
        jobs <- i
    }
    close(jobs)
    wg.Wait()

    sort.Slice(samples, func(i, j int) bool { return samples[i] < samples[j] })
    p := func(q float64) time.Duration { return samples[int(float64(len(samples))*q)] }
    fmt.Printf("n=%d  p50=%v  p95=%v  p99=%v\n", len(samples), p(0.50), p(0.95), p(0.99))
}

Run it once to warm the cache, then run it again for the numbers that matter — the second run is all HITs and represents steady state. Against a warm edge cache from a European client I measured p50 around 14ms and p95 around 31ms, which is the range where thumbnails feel instant and never gate the layout. Cold-cache runs are noisier because they include the upstream fetch and the resize, which is exactly why the Python warmer exists.

What actually changed

After a week in production, measured against the same trending grid:

PHP requests to /thumb/* dropped from ~38k/hour to zero. The origin no longer participates in image serving at all.
Edge cache hit ratio settled at ~97% once the warmer was wired into the cron. The 3% misses are genuinely new videos.
Median thumbnail payload fell ~35% by shipping WebP to browsers that accept it, at quality 82 where the difference is invisible in a 320px grid.
Layout shift on category pages went to effectively zero, because we now emit correct width/height and the URL carries the render size.
No EU browser hits Google's CDN directly anymore — every thumbnail comes from our own domain.

The GDPR angle nobody talks about

That last point is the one I care about most, and it rarely comes up in performance write-ups. When you embed i.ytimg.com directly, an EU visitor's browser opens a connection to a Google server, sending their IP and a referrer, before they've consented to anything. Under the GDPR and the German court reading of the ePrivacy rules, that's a third-party data transfer you can't justify on a page that's still waiting for a consent choice.

Proxying through your own Worker changes the legal shape of the request. The browser talks only to img.viralvidvault.com. The Worker fetches from YouTube server-to-server, with no cookies and Referrer-Policy: no-referrer, so no visitor IP or referring URL leaves the EU edge toward Google. You've turned an unavoidable third-party request into a first-party one you fully control — which is the same reason we run our own GDPR-compliant analytics instead of embedding someone else's script.

Conclusion

A video thumbnail API sounds like a toy problem until you're serving forty of them per page to viral-scale traffic. Pushing it to a Cloudflare Worker gave us three wins at once: the origin stopped burning PHP processes on redirects, EU visitors stopped making pre-consent requests to Google, and Core Web Vitals recovered because we finally controlled dimensions and format. The signing scheme keeps the endpoint from becoming an open proxy, the Python warmer keeps trend spikes cheap, and the Go load test kept me honest about latency before I shipped. The whole thing is well under 200 lines of code and has been the least-maintenance service we run. If your stack redirects to a third-party image CDN today, this is a weekend's work with an outsized payoff.

TimescaleDB Hypertables for Multi-Region Video Viewership Analytics

ahmet gedik — Mon, 10 Aug 2026 09:00:02 +0000

The 9-Second Query That Killed the Trending Page

Our view-event table crossed 40 million rows on a Tuesday. The query that builds the regional trending shelf — videos watched in JP in the last 6 hours, ranked by acceleration — went from 40ms to just over 9 seconds. LiteSpeed workers piled up behind it, Cloudflare started serving stale HTML on a 5-minute page cache, and the mobile watch page felt like 2011.

The stack behind TopVideoHub is deliberately boring: PHP 8.4, SQLite with an FTS5 index and a custom CJK-aware tokenizer for search, LiteSpeed in front, Cloudflare on the edge. That combination is excellent for read-mostly catalog data. It is genuinely bad at one thing: append-heavy time-series with high-cardinality group-bys. A single writer lock, no partitioning, no columnar storage, and every GROUP BY video_id over a 90-day window is a full scan.

So we split the workload. Catalog, search, and page rendering stayed on SQLite. Viewership analytics moved to PostgreSQL with TimescaleDB hypertables. This is what that migration actually looked like — schema, ingest path, continuous aggregates, compression numbers, and the parts that bit us.

Why Hypertables Instead of Native Partitioning

Plain Postgres declarative partitioning works. We prototyped it. The reason we did not ship it:

Partition management is manual. Someone has to create next month's partition. That someone eventually forgets, and inserts start failing at 00:00 UTC.
No native columnar compression. Our events are extremely repetitive — the same video_id and region repeat thousands of times per hour. Row storage wastes an enormous amount of space on that.
No incremental materialized views. Refreshing a normal materialized view over 40M rows every 15 minutes is the same full scan we were trying to escape.

A hypertable is a regular Postgres table with automatic chunking, plus three things layered on top: chunk exclusion during planning, native compression that converts old chunks to a columnar layout, and continuous aggregates that refresh only the buckets whose source data changed. You still write plain SQL. INSERT, SELECT, JOIN against SQLite-exported dimension tables — all unchanged.

Schema Design for Multi-Region Viewership Events

The event is intentionally narrow. Anything derivable at query time is not stored.

CREATE TABLE view_event (
  ts         timestamptz NOT NULL,
  video_id   text        NOT NULL,
  region     char(2)     NOT NULL,
  lang       text        NOT NULL,
  surface    smallint    NOT NULL,   -- 1=home 2=category 3=search 4=related
  dwell_ms   integer     NOT NULL DEFAULT 0,
  completed  boolean     NOT NULL DEFAULT false
);

-- 1-day chunks: ~1.6M rows/chunk at our volume, comfortably
-- inside shared_buffers for the recent working set.
SELECT create_hypertable(
  'view_event',
  by_range('ts', INTERVAL '1 day')
);

-- Secondary hash dimension. Nine regions, four hash partitions:
-- parallel workers stop fighting over the same chunk on writes.
SELECT add_dimension('view_event', by_hash('region', 4));

CREATE INDEX ON view_event (video_id, ts DESC);
CREATE INDEX ON view_event (region, ts DESC);
CREATE INDEX ON view_event (lang, ts DESC) WHERE lang IN ('ja','ko','zh-Hant','zh-Hans');

A few decisions worth explaining:

Chunk interval. The rule of thumb is that one chunk plus its indexes should fit in about 25% of RAM. We started at 7 days, which produced 11M-row chunks and made compression jobs run for minutes. One day is the sweet spot for us. You can change it with set_chunk_time_interval() and it applies to future chunks only.
char(2) for region. ISO country codes, fixed width, and it segments beautifully under compression.
surface as smallint. We slice trending by entry point constantly — a video that trends only from search behaves very differently from one that trends from the home shelf.
The partial index on CJK languages. Roughly 60% of our traffic is Japanese, Korean, or Chinese, and nearly every language-specific dashboard query filters to that set. A partial index keeps it small.

No user ID. We do not need one for trending, and not collecting it removes an entire class of privacy and retention questions.

Batching Writes From PHP Without Adding Request Latency

The watch page cannot afford a synchronous round trip to Postgres on every hit. The pattern that worked: buffer in the request, flush once on shutdown, and never let a failed flush surface to the user.

<?php
declare(strict_types=1);

final class ViewEventBuffer
{
    private const int FLUSH_ROWS = 200;
    private const int COLS = 7;

    /** @var list<list<string|int|bool>> */
    private array $rows = [];

    public function __construct(private readonly PDO $pdo) {}

    public function record(
        string $videoId,
        string $region,
        string $lang,
        int $surface,
        int $dwellMs = 0,
        bool $completed = false,
    ): void {
        $this->rows[] = [
            (new DateTimeImmutable('now', new DateTimeZone('UTC')))
                ->format('Y-m-d H:i:s.uP'),
            $videoId,
            strtoupper(substr($region, 0, 2)),
            $lang,
            $surface,
            max(0, $dwellMs),
            $completed,
        ];

        if (count($this->rows) >= self::FLUSH_ROWS) {
            $this->flush();
        }
    }

    public function flush(): int
    {
        if ($this->rows === []) {
            return 0;
        }

        $tuple  = '(' . implode(',', array_fill(0, self::COLS, '?')) . ')';
        $values = implode(',', array_fill(0, count($this->rows), $tuple));
        $params = array_merge(...$this->rows);

        $sql = 'INSERT INTO view_event
                (ts, video_id, region, lang, surface, dwell_ms, completed)
                VALUES ' . $values;

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            $written = $stmt->rowCount();
        } catch (PDOException $e) {
            // Analytics must never break a page render.
            error_log('view_event flush failed: ' . $e->getMessage());
            $written = 0;
        }

        $this->rows = [];
        return $written;
    }
}

// Wiring, in the front controller:
$pdo = new PDO(
    'pgsql:host=127.0.0.1;dbname=tvh_analytics',
    getenv('PG_USER'),
    getenv('PG_PASS'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => true],
);
$buffer = new ViewEventBuffer($pdo);
register_shutdown_function(static fn() => $buffer->flush());

Three details that matter more than the code itself:

ATTR_PERSISTENT => true. Under LiteSpeed's PHP SAPI, workers are long-lived. Without persistent connections you pay TCP plus Postgres backend startup on every request, and that startup cost dwarfs the insert.
register_shutdown_function. The flush happens after the response body has been sent, so the user never waits on it.
Swallowing the exception. Losing a view event is annoying. Serving a 500 because the analytics box is rebooting is unacceptable. Log it, drop it, move on.

At 200 rows per multi-row INSERT, a flush takes about 3ms on a modest 4-vCPU box. That is well inside the noise of a page render.

Continuous Aggregates Are the Actual Point

Hypertables alone would not have fixed the 9-second query. Continuous aggregates did. A continuous aggregate is a materialized view over a hypertable that Timescale refreshes incrementally — it recomputes only the time buckets whose underlying chunks were modified since the last run.

CREATE MATERIALIZED VIEW video_views_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', ts) AS bucket,
    video_id,
    region,
    count(*)                                  AS views,
    count(*) FILTER (WHERE completed)         AS completions,
    sum(dwell_ms)::bigint                     AS dwell_ms_total,
    percentile_agg(dwell_ms)                  AS dwell_sketch
FROM view_event
GROUP BY bucket, video_id, region
WITH NO DATA;

SELECT add_continuous_aggregate_policy('video_views_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '15 minutes');

-- Columnar compression on the raw hypertable.
ALTER TABLE view_event SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'video_id, region',
    timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('view_event', INTERVAL '7 days');
SELECT add_retention_policy('view_event',  INTERVAL '90 days');

The dwell_sketch column is the subtle bit. You cannot store a percentile in a continuous aggregate and then average it later — percentiles do not compose. percentile_agg from the timescaledb-toolkit extension stores a UDDSketch, a mergeable approximate-quantile structure. At query time you rollup() the sketches across buckets and then extract the percentile:

SELECT video_id,
       sum(views) AS views_24h,
       approx_percentile(0.5,  rollup(dwell_sketch)) AS dwell_p50,
       approx_percentile(0.95, rollup(dwell_sketch)) AS dwell_p95
FROM video_views_hourly
WHERE region = 'JP' AND bucket >= now() - INTERVAL '24 hours'
GROUP BY video_id
ORDER BY views_24h DESC
LIMIT 50;

That is mathematically sound and reads 24 pre-aggregated rows per video instead of scanning millions of events.

On start_offset => INTERVAL '3 days': the refresh policy only re-examines the last three days of buckets. Events arriving later than that — say a delayed batch from a mobile client — will land in the raw hypertable but never make it into the aggregate. If you need those, either widen the offset or turn on real-time aggregation, which unions the materialized buckets with a live scan of the newest chunk. We turned real-time aggregation off (timescaledb.materialized_only = true) because the live-scan tail made query latency unpredictable, and a trending shelf that lags by up to an hour is fine when Cloudflare is caching the page for five minutes anyway.

Compression Numbers, Honestly Reported

After 30 days of production traffic:

Raw uncompressed: 41.2M rows, 4.7 GB including indexes.
After compression on chunks older than 7 days: 612 MB. Roughly 7.6x.
Hourly continuous aggregate: 38 MB. It is a rounding error next to the raw data, which is why we can afford a daily and a weekly rollup on top of it.

The segmentby = 'video_id, region' choice drove most of that. Compression groups rows by the segmentby columns and stores each remaining column as an array; when a single video accumulates thousands of events per region per chunk, the repeated identifiers collapse to one value. Picking a high-cardinality segmentby key — a session ID, say — would have produced tiny segments and a compression ratio near 1.

The cost: compressed chunks are effectively append-only for older data. Recent TimescaleDB versions do permit UPDATE and DELETE on compressed chunks, but they decompress the affected segments to do it, which is slow and refragments storage. Plan your schema as if compressed data is immutable, because operationally it is.

A Go Sidecar When PHP Buffering Is Not Enough

During a regional trending spike, per-request flushing generates a lot of small transactions. We put a small Go service in front of Postgres that accepts events over a Unix socket and writes them with COPY, which is several times faster than multi-row INSERT at batch sizes above a thousand.

package main

import (
    "context"
    "log"
    "time"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

type Event struct {
    TS        time.Time
    VideoID   string
    Region    string
    Lang      string
    Surface   int16
    DwellMS   int32
    Completed bool
}

var cols = []string{
    "ts", "video_id", "region", "lang", "surface", "dwell_ms", "completed",
}

func runWriter(ctx context.Context, pool *pgxpool.Pool, in <-chan Event) {
    const maxBatch = 5000

    ticker := time.NewTicker(2 * time.Second)
    defer ticker.Stop()

    batch := make([]Event, 0, maxBatch)

    flush := func() {
        if len(batch) == 0 {
            return
        }
        rows := make([][]any, len(batch))
        for i, e := range batch {
            rows[i] = []any{
                e.TS, e.VideoID, e.Region, e.Lang,
                e.Surface, e.DwellMS, e.Completed,
            }
        }

        cctx, cancel := context.WithTimeout(ctx, 10*time.Second)
        n, err := pool.CopyFrom(
            cctx,
            pgx.Identifier{"view_event"},
            cols,
            pgx.CopyFromRows(rows),
        )
        cancel()

        if err != nil {
            // Drop the batch rather than grow the buffer without bound.
            // Analytics loss is acceptable; an OOM kill is not.
            log.Printf("copy failed, dropping %d events: %v", len(batch), err)
        } else {
            log.Printf("wrote %d events", n)
        }
        batch = batch[:0]
    }

    for {
        select {
        case e := <-in:
            batch = append(batch, e)
            if len(batch) >= maxBatch {
                flush()
            }
        case <-ticker.C:
            flush()
        case <-ctx.Done():
            flush()
            return
        }
    }
}

Two seconds or five thousand events, whichever comes first. The explicit drop-on-error is deliberate: an unbounded retry buffer in a sidecar is how you turn a database hiccup into a memory-exhaustion incident.

Feeding the Result Back Into SQLite

The front end never queries Postgres. A cron job computes the trending sets and writes them into the SQLite file that LiteSpeed serves from, so a page render stays a local file read.

#!/usr/bin/env python3
import os
import sqlite3
import psycopg

REGIONS = ("JP", "KR", "TW", "SG", "VN", "TH", "HK", "US", "GB")
MIN_VIEWS = 25
TOP_N = 60

TRENDING_SQL = """
SELECT video_id,
       sum(views)                                          AS views_24h,
       sum(views) FILTER (
           WHERE bucket >= now() - INTERVAL '6 hours')     AS views_6h,
       approx_percentile(0.5, rollup(dwell_sketch))::int   AS dwell_p50,
       sum(completions)::float / NULLIF(sum(views), 0)     AS completion_rate
FROM video_views_hourly
WHERE region = %s
  AND bucket >= now() - INTERVAL '24 hours'
GROUP BY video_id
HAVING sum(views) >= %s
ORDER BY (sum(views) FILTER (WHERE bucket >= now() - INTERVAL '6 hours'))::numeric
         / NULLIF(sum(views), 0) DESC,
         views_24h DESC
LIMIT %s
"""


def main() -> None:
    pg = psycopg.connect(os.environ["PG_DSN"])
    lite = sqlite3.connect(os.environ["SQLITE_PATH"])
    lite.execute("PRAGMA journal_mode=WAL")

    lite.execute("""
        CREATE TABLE IF NOT EXISTS trending_region (
            region          TEXT    NOT NULL,
            rank            INTEGER NOT NULL,
            video_id        TEXT    NOT NULL,
            views_24h       INTEGER NOT NULL,
            views_6h        INTEGER NOT NULL,
            dwell_p50       INTEGER NOT NULL,
            completion_rate REAL    NOT NULL,
            PRIMARY KEY (region, rank)
        )
    """)

    with pg, pg.cursor() as cur:
        for region in REGIONS:
            cur.execute(TRENDING_SQL, (region, MIN_VIEWS, TOP_N))
            rows = [
                (region, i, *r) for i, r in enumerate(cur.fetchall(), start=1)
            ]
            if not rows:
                print(f"{region}: no rows above threshold, keeping previous set")
                continue

            lite.execute("DELETE FROM trending_region WHERE region = ?", (region,))
            lite.executemany(
                "INSERT INTO trending_region VALUES (?,?,?,?,?,?,?)", rows
            )
            print(f"{region}: {len(rows)} videos")

    lite.commit()
    lite.close()
    pg.close()


if __name__ == "__main__":
    main()

The ordering expression is the acceleration ratio — share of the day's views that landed in the last six hours — with absolute 24-hour volume as the tiebreak. That surfaces genuinely rising videos instead of the same evergreen uploads every day. The if not rows: continue guard matters more than it looks: without it, a single failed cron run during a quiet overnight window would blank an entire region's shelf.

Things That Bit Us

ORDER BY without a time predicate. Chunk exclusion only works when the planner can prove a time range. A query with no ts or bucket filter touches every chunk. Every analytics query in our codebase now carries an explicit time bound, enforced by a query-builder assertion.
timestamptz, never timestamp. With nine regions across seven UTC offsets, naive timestamps produce bucket boundaries that silently disagree. Store UTC, convert at the presentation layer.
Continuous aggregates on top of continuous aggregates need matching bucket alignment. Our daily rollup reads from the hourly aggregate; the bucket interval must be an exact multiple or the refresh policy quietly does nothing useful.
max_locks_per_transaction. A query spanning many chunks acquires a lock per chunk. The Postgres default of 64 is far too low for a hypertable with hundreds of chunks. We run 512.
Compression policies and manual backfill conflict. Backfilling into a compressed chunk works but is slow enough to look like a hang. Decompress the target range explicitly, backfill, recompress.

Conclusion

The trending query that took 9.2 seconds now runs in 34ms against the hourly continuous aggregate, and the raw event table takes 87% less disk than it did before compression. None of that required rewriting the application — the front end still renders from SQLite, search still runs through the FTS5 CJK index, and the PHP change amounted to one buffer class plus a shutdown hook.

The general lesson is smaller than the tooling suggests: stop asking one storage engine to be good at both point lookups over a catalog and aggregations over an append-only event stream. Those workloads want opposite layouts. Split them, keep the boring read path boring, and let the time-series database do the part it was built for.

Building a Chaos Testing Harness for Video Discovery API Endpoints

ahmet gedik — Mon, 10 Aug 2026 01:00:02 +0000

At 3:11 AM one Tuesday, the /watch endpoint on our platform started returning 200s with empty bodies. No 500s, no error logs, nothing our alerting was watching for. The cause was mundane: an upstream metadata provider had started responding in 8–12 seconds instead of 200ms, our PHP-FPM workers piled up waiting on it, LiteSpeed's connection pool saturated, and Cloudflare began serving stale-or-empty cache entries to real users. Everything was technically "up." Everything was broken.

That incident is why I ended up building a chaos testing harness specifically for video API endpoints. I run DailyWatch, a free video discovery platform, and our read path is deceptively simple on paper: search, category listings, and per-video watch pages. But every one of those endpoints fans out to caches, a SQLite database with FTS5 full-text indexes, and one or more flaky upstream metadata sources. The interesting failures never live in a single function — they live in the seams between components under stress. Chaos testing is how you go looking for them on purpose, during business hours, instead of getting paged at 3 AM.

This post walks through the harness we actually run: how we model the failure surface, a fault-injection layer written in PHP 8.4, a Python driver that orchestrates scenarios, a circuit breaker that keeps the FTS5 read path alive when upstreams die, and — most importantly — how we assert on SLOs instead of eyeballing graphs.

Why video discovery endpoints fail in weird ways

A CRUD API mostly fails cleanly: the database is up or it isn't. Video discovery is different because a single response is assembled from sources with wildly different reliability profiles:

The local hot path: SQLite FTS5 queries for search and category pages. Fast, in-process, almost never the problem — until the database file is locked during a cron-driven bulk insert.
Cache layers: LiteSpeed page cache, a PHP file cache, and a data cache. Each can serve stale data, miss, or — the nasty one — return a partial object that looks valid.
Upstream metadata: view counts, thumbnails, channel info. High latency variance, occasional 429s, and the occasional multi-second stall that never quite times out.

The failure modes that matter are combinations: a cache miss plus a slow upstream plus a concurrent write lock. You cannot find those by unit-testing components in isolation. You have to inject faults into a running system and watch what the whole thing does.

So the first job is to enumerate the failure surface honestly. I keep this as a plain data structure, because the harness reads it directly:

upstream_latency: add N ms of delay before returning
upstream_error: return a 5xx or a 429 from a dependency
upstream_partial: return a syntactically valid but incomplete payload
db_lock: hold a write transaction to force SQLITE_BUSY
cache_poison: serve a truncated cache entry
clock_skew: shift TTL math so cache entries expire early or never

Every scenario in the harness is one or more of these, applied at a chosen rate, against a chosen endpoint.

A fault-injection middleware in PHP 8.4

The cleanest place to inject faults is a middleware that wraps the request before it reaches the controller. I gate the whole thing behind an environment flag so it is physically impossible to enable in production, and behind a header so a test driver can target specific requests without affecting real traffic sharing the same staging box.

Here is the core injector. It reads a fault spec from a request header, decides probabilistically whether to fire, and mutates the request lifecycle accordingly.

<?php
declare(strict_types=1);

final class ChaosMiddleware
{
    private const HEADER = 'X-Chaos-Spec';

    public function __construct(
        private readonly bool $enabled,          // from CHAOS_ENABLED env
        private readonly \Closure $rng,          // injectable RNG: fn(): float in [0,1)
    ) {}

    public function handle(array $server): ?ChaosAction
    {
        if (!$this->enabled) {
            return null; // hard off outside staging
        }
        $raw = $server['HTTP_X_CHAOS_SPEC'] ?? '';
        if ($raw === '') {
            return null;
        }

        // Spec format: "fault=upstream_latency;rate=0.5;ms=6000"
        $spec = $this->parse($raw);
        $rate = (float) ($spec['rate'] ?? 1.0);
        if (($this->rng)() >= $rate) {
            return null; // this request drew clean
        }

        return match ($spec['fault'] ?? '') {
            'upstream_latency' => ChaosAction::latency((int) ($spec['ms'] ?? 3000)),
            'upstream_error'   => ChaosAction::error((int) ($spec['code'] ?? 503)),
            'upstream_partial' => ChaosAction::partial(),
            'db_lock'          => ChaosAction::dbLock((int) ($spec['ms'] ?? 2000)),
            'cache_poison'     => ChaosAction::cachePoison(),
            default            => null,
        };
    }

    /** @return array<string,string> */
    private function parse(string $raw): array
    {
        $out = [];
        foreach (explode(';', $raw) as $pair) {
            [$k, $v] = array_pad(explode('=', $pair, 2), 2, '');
            $out[trim($k)] = trim($v);
        }
        return $out;
    }
}

final class ChaosAction
{
    private function __construct(
        public readonly string $kind,
        public readonly int $arg = 0,
    ) {}

    public static function latency(int $ms): self  { return new self('latency', $ms); }
    public static function error(int $code): self   { return new self('error', $code); }
    public static function partial(): self          { return new self('partial'); }
    public static function dbLock(int $ms): self     { return new self('db_lock', $ms); }
    public static function cachePoison(): self       { return new self('cache_poison'); }

    public function applyUpstream(callable $realCall): mixed
    {
        return match ($this->kind) {
            'latency' => (function () use ($realCall) {
                usleep($this->arg * 1000);
                return $realCall();
            })(),
            'error'   => throw new UpstreamException($this->arg),
            'partial' => array_slice((array) $realCall(), 0, 1),
            default   => $realCall(),
        };
    }
}

The important design choices here: faults are opt-in per request via a header, the RNG is injected so tests are deterministic when you want them to be, and the whole thing is a no-op unless CHAOS_ENABLED is set. On our stack that env flag only exists on the LiteSpeed staging vhost, never on the production one.

Keeping the FTS5 read path alive: a circuit breaker

Injecting faults is only useful if the endpoint is supposed to survive them. The single highest-value pattern for a discovery API is a circuit breaker in front of every non-local dependency, backed by a fast local fallback. When the upstream metadata provider is dying, we should still serve search results from FTS5 with whatever cached metadata we have, and degrade gracefully rather than block.

This is the breaker we wrap around upstream calls. It trips after a threshold of failures, stays open for a cooldown, then allows a single probe request through (half-open) before fully closing again.

<?php
declare(strict_types=1);

final class CircuitBreaker
{
    private const CLOSED = 'closed';
    private const OPEN = 'open';
    private const HALF = 'half_open';

    public function __construct(
        private readonly \PDO $db,           // SQLite state store
        private readonly string $name,
        private readonly int $threshold = 5,
        private readonly int $cooldownSec = 30,
        private readonly \Closure $now,      // fn(): int  -> unix ts, injectable
    ) {}

    /**
     * @param callable():T $call     the risky upstream call
     * @param callable():T $fallback  the local/degraded path
     * @template T
     */
    public function run(callable $call, callable $fallback): mixed
    {
        $state = $this->load();
        $now = ($this->now)();

        if ($state['status'] === self::OPEN) {
            if ($now - $state['opened_at'] < $this->cooldownSec) {
                return $fallback(); // fail fast, do not touch the sick upstream
            }
            $this->transition(self::HALF, $state['failures'], $state['opened_at']);
        }

        try {
            $result = $call();
            $this->transition(self::CLOSED, 0, 0); // success resets everything
            return $result;
        } catch (\Throwable $e) {
            $failures = $state['failures'] + 1;
            if ($failures >= $this->threshold || $state['status'] === self::HALF) {
                $this->transition(self::OPEN, $failures, $now);
            } else {
                $this->transition(self::CLOSED, $failures, 0);
            }
            return $fallback();
        }
    }

    /** @return array{status:string,failures:int,opened_at:int} */
    private function load(): array
    {
        $stmt = $this->db->prepare(
            'SELECT status, failures, opened_at FROM circuit_state WHERE name = ?'
        );
        $stmt->execute([$this->name]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: ['status' => self::CLOSED, 'failures' => 0, 'opened_at' => 0];
    }

    private function transition(string $status, int $failures, int $openedAt): void
    {
        $this->db->prepare(
            'INSERT INTO circuit_state (name, status, failures, opened_at)
             VALUES (:n, :s, :f, :o)
             ON CONFLICT(name) DO UPDATE SET
               status = :s, failures = :f, opened_at = :o'
        )->execute([':n' => $this->name, ':s' => $status, ':f' => $failures, ':o' => $openedAt]);
    }
}

And here is how the search controller uses it. The upstream enriches results with fresh view counts; the fallback serves the FTS5 hit list with whatever is already in the local table. A user searching for "documentary" during an upstream outage still gets relevant videos — just with slightly stale counts.

<?php
public function search(string $q, CircuitBreaker $breaker): array
{
    // Local FTS5 query is the source of truth for *which* videos match.
    $stmt = $this->db->prepare(
        "SELECT v.id, v.title, v.channel, v.cached_views
         FROM videos v
         JOIN videos_fts f ON f.rowid = v.rowid
         WHERE videos_fts MATCH :q
         ORDER BY rank
         LIMIT 40"
    );
    $stmt->execute([':q' => $this->sanitizeFtsQuery($q)]);
    $hits = $stmt->fetchAll(\PDO::FETCH_ASSOC);

    // Enrichment is best-effort and guarded by the breaker.
    return $breaker->run(
        call: fn() => $this->enrichWithLiveMetadata($hits),   // risky upstream
        fallback: fn() => $hits,                              // degraded but correct
    );
}

The key insight for video discovery specifically: the ranking (from FTS5) must never depend on a network call. Metadata enrichment is decoration. Once you draw that line, most chaos scenarios become survivable by construction.

Driving chaos from the outside

The harness itself lives in Python because I want it to run against a real HTTP surface — through LiteSpeed and, in one profile, through Cloudflare — not against PHP objects in a test process. Each scenario is a small dataclass; the driver fires concurrent requests carrying the X-Chaos-Spec header and records latency and status for every one.

import asyncio
import time
from dataclasses import dataclass, field

import httpx


@dataclass
class Scenario:
    name: str
    endpoint: str
    chaos_spec: str          # e.g. "fault=upstream_latency;rate=0.5;ms=6000"
    concurrency: int = 25
    total_requests: int = 500
    slo_p99_ms: float = 800.0
    slo_success_rate: float = 0.99


@dataclass
class Result:
    latencies_ms: list[float] = field(default_factory=list)
    statuses: list[int] = field(default_factory=list)
    errors: int = 0


async def _worker(client, scenario, result, sem, budget):
    while True:
        async with budget:
            if budget.done:
                return
        async with sem:
            start = time.perf_counter()
            try:
                r = await client.get(
                    scenario.endpoint,
                    headers={"X-Chaos-Spec": scenario.chaos_spec},
                    timeout=15.0,
                )
                result.latencies_ms.append((time.perf_counter() - start) * 1000)
                result.statuses.append(r.status_code)
            except (httpx.TimeoutException, httpx.TransportError):
                result.latencies_ms.append((time.perf_counter() - start) * 1000)
                result.errors += 1


class Budget:
    """Shared, thread-safe-ish request budget for the asyncio loop."""
    def __init__(self, total: int):
        self._remaining = total
        self.done = False

    async def __aenter__(self):
        if self._remaining <= 0:
            self.done = True
        else:
            self._remaining -= 1
        return self

    async def __aexit__(self, *exc):
        return False


async def run_scenario(base_url: str, scenario: Scenario) -> Result:
    result = Result()
    budget = Budget(scenario.total_requests)
    sem = asyncio.Semaphore(scenario.concurrency)
    async with httpx.AsyncClient(base_url=base_url) as client:
        workers = [
            asyncio.create_task(_worker(client, scenario, result, sem, budget))
            for _ in range(scenario.concurrency)
        ]
        await asyncio.gather(*workers)
    return result

A scenario file for us looks like a list of these dataclasses: baseline (no chaos), 50% upstream latency at 6s, 100% upstream 503, partial payloads at 30%, and a combined "bad night" scenario that layers db-lock and latency together. The combined ones are where the interesting bugs still hide.

Assert on SLOs, not on vibes

The part everyone skips is the assertion. It is not enough to run traffic and look at a dashboard — that is how regressions sneak back in. Every scenario declares its SLOs up front, and the harness fails the run (non-zero exit) if reality misses them. This is what turns chaos testing from a party trick into a CI gate.

import statistics
import sys


def percentile(values: list[float], p: float) -> float:
    if not values:
        return float("inf")
    ordered = sorted(values)
    k = (len(ordered) - 1) * p
    lo = int(k)
    hi = min(lo + 1, len(ordered) - 1)
    return ordered[lo] + (ordered[hi] - ordered[lo]) * (k - lo)


def evaluate(scenario: Scenario, result: Result) -> bool:
    total = len(result.statuses) + result.errors
    # "success" for a discovery endpoint = 2xx AND a non-empty body contract.
    # The driver records status; body-emptiness is checked server-side via a
    # sentinel header the controller sets, collapsed here into status 599.
    good = sum(1 for s in result.statuses if 200 <= s < 300 and s != 599)
    success_rate = good / total if total else 0.0
    p99 = percentile(result.latencies_ms, 0.99)

    ok_rate = success_rate >= scenario.slo_success_rate
    ok_p99 = p99 <= scenario.slo_p99_ms

    print(f"[{scenario.name}] success={success_rate:.4f} "
          f"(SLO {scenario.slo_success_rate}) p99={p99:.0f}ms "
          f"(SLO {scenario.slo_p99_ms}ms) errors={result.errors}")

    return ok_rate and ok_p99


async def main(base_url: str, scenarios: list[Scenario]) -> int:
    failures = 0
    for scenario in scenarios:
        result = await run_scenario(base_url, scenario)
        if not evaluate(scenario, result):
            failures += 1
    if failures:
        print(f"CHAOS FAILED: {failures} scenario(s) missed SLO")
    return 1 if failures else 0


if __name__ == "__main__":
    import asyncio
    scenarios = [
        Scenario("baseline", "/search?q=documentary", "", slo_p99_ms=400),
        Scenario("upstream_slow", "/search?q=documentary",
                 "fault=upstream_latency;rate=0.5;ms=6000", slo_p99_ms=800),
        Scenario("upstream_dead", "/watch?id=abc123",
                 "fault=upstream_error;rate=1.0;code=503",
                 slo_success_rate=0.99, slo_p99_ms=500),
    ]
    sys.exit(asyncio.run(main("https://staging.internal", scenarios)))

Notice the SLOs differ per scenario. Under upstream_slow I raise the allowed p99 to 800ms because some latency leaking through is acceptable — but success rate must stay at 99% because the circuit breaker and FTS5 fallback should absorb the failures. Under upstream_dead I actually tighten p99 to 500ms, because when the upstream is fully down the breaker should trip and fail fast to the local path. A slow response during a total outage means the breaker is not doing its job. Encoding that expectation is the whole point.

Running it against LiteSpeed and Cloudflare

We run three profiles. The first hits PHP-FPM behind LiteSpeed directly on the staging box — this isolates application behavior. The second hits the same box through Cloudflare with cache rules mirroring production, which catches the class of bug from my 3 AM story: a degraded partial response getting cached at the edge and served to thousands of users. The cache_poison fault plus the Cloudflare profile is the only reliable way I have found to reproduce that.

A few operational notes that saved us grief:

Never let chaos touch a shared cache key. The X-Chaos-Spec header must be part of the cache key on staging, or one poisoned response contaminates clean requests. On LiteSpeed we add it to the cache vary list for the staging vhost only.
Warm the FTS5 page cache before the baseline run, otherwise your baseline p99 includes cold-start cost and every comparison is noise.
Run db-lock scenarios against a copy of the real database, not a fixture. SQLite SQLITE_BUSY behavior depends heavily on WAL mode and busy_timeout, and a toy DB will not reproduce it.
Keep total request counts modest (500–2000). Chaos testing is about finding behavioral bugs, not load testing; save the 100k-request runs for a separate performance suite.

We wired the harness into CI as a nightly job against staging rather than per-PR, because chaos runs take a few minutes and are inherently a little noisy. A failed nightly opens a ticket automatically with the failing scenario name and the recorded p99/success numbers attached.

What actually changed

Since this harness went in, the concrete wins were unglamorous and exactly what I wanted:

We found that upstream_partial responses were passing our JSON schema check but rendering blank watch pages — the 3 AM bug, now caught in CI.
We discovered the circuit breaker was tripping correctly but the cooldown was tuned so aggressively that a single slow request kept the breaker open for real users long after the upstream recovered.
We caught a case where a db_lock during cron caused search to return HTTP 200 with zero results instead of falling back to the last good cache — a silent correctness bug no unit test would ever have surfaced.

None of those were exotic. They were the ordinary seams between a fast local path and a slow remote one, exactly where distributed reads always rot. The harness did not make our system more reliable by itself — the circuit breaker and the FTS5-first design did that. What the harness gave us was proof, on demand, that the resilience we designed actually holds when things go wrong, plus a red build the moment it stops holding.

If you run any read-heavy API that stitches together caches, a local index, and flaky upstreams, start small: one fault type, one endpoint, one SLO assertion in CI. The first bug you catch during business hours instead of at 3 AM pays for the whole thing.

Serving Video Metadata Across Eight Regions With Litestar And SQLite

ahmet gedik — Sun, 09 Aug 2026 15:00:01 +0000

The 41-minute cron that had to fit inside 180 seconds

The /watch/{id} page on our site renders in about 38 ms. The job that keeps the data behind it fresh took 41 minutes, and the shared LiteSpeed host it ran on kills any PHP process at max_execution_time = 180. So the refresh was chopped into fourteen cron slots that each did a slice of the work, wrote a checkpoint row into SQLite, and hoped the next slot picked up where the last one died. When a slot failed — a 403 from a burned API key, a DNS hiccup, a slow region — the checkpoint drifted and one of our eight regions would silently serve day-old view counts until somebody noticed.

The fix was not a faster PHP loop. The problem was structural: refreshing metadata for eight regions is eight independent network round trips, and curl_exec in a foreach does them one after another. At roughly 300 ms per YouTube Data API call and ~2,400 videos in rotation, the arithmetic is brutal and no amount of micro-optimization moves it. I pulled that one job out of the PHP monolith that runs TrendVidStream and rebuilt it as a small Litestar service. This is what that looked like in practice, including the parts that went badly.

Why not just fix the PHP

I want to be honest that this was not obviously the right call. PHP 8.4 with Fibers can do concurrent I/O now, and curl_multi_exec has existed forever. I tried both first.

curl_multi_exec works fine and cut the fanout to roughly 4 minutes. What it did not solve:

The 180-second ceiling was still there. Concurrency shrank the wall clock but the job still had no ability to run longer than the SAPI allowed, so the checkpoint machinery had to stay.
No process to hold state. Rate-limit budgets, per-key backoff, and in-flight dedup all had to be persisted to SQLite and re-read every invocation, because the process dies after every run.
Retries were a cron concern. A failed region waited two hours for the next slot instead of 30 seconds.

A long-lived process solves all three, and once you want a long-lived process, PHP stops being the path of least resistance on shared hosting. The metadata fetcher is also the one component that does not need to be on the web host at all — it writes a SQLite file, and the web tier only reads it.

Why Litestar and not FastAPI

I have shipped FastAPI services and would again. Three things pushed me to Litestar here:

msgspec instead of Pydantic for the hot path. Our largest response is a 200-item region feed. Serializing that with msgspec structs measured about 4x faster than the equivalent Pydantic v2 model dump in my benchmark, and msgspec structs are plain frozen objects with __slots__, so the memory profile on a 512 MB box is much flatter.
Dependency injection that composes at the router level. Read-only DB handles, the API key pool, and the region allowlist are declared once on the app and inherited. No module-level globals, no Depends() repeated on 20 handlers.
sync_to_thread is explicit. SQLite's driver is synchronous. Litestar makes you say so per handler and warns loudly if you forget, instead of quietly blocking the event loop. That single warning caught a real bug for me on day two.

The framework name still shows up as "Starlite" in older blog posts and in a few stale PyPI mirrors — it was renamed in 2023. If you are copying snippets off the internet, check which era they came from, because the import paths and the DTO API both changed.

The read path

The read store stayed SQLite with FTS5, exactly as the PHP side had it. There was no reason to change it: the file is 340 MB, it lives on the same disk as the process, and FTS5 answers a prefix query over 2.4 million title tokens in single-digit milliseconds. Litestar just puts an HTTP surface on it.

The service opens the database read-only, in WAL mode, with one connection per worker thread. Writes go through a completely separate process, which is the only sane way to use SQLite from an async framework.

# app/main.py
from __future__ import annotations

import sqlite3
from typing import Annotated

import msgspec
from litestar import Litestar, get
from litestar.di import Provide
from litestar.exceptions import NotFoundException
from litestar.params import Parameter

REGIONS = ('US', 'GB', 'DE', 'FR', 'IN', 'BR', 'JP', 'AU')
DB_PATH = 'file:data/metadata.db?mode=ro'


class VideoOut(msgspec.Struct, frozen=True, omit_defaults=True):
    video_id: str
    title: str
    channel: str
    duration_s: int
    views: int
    region: str
    fetched_at: int


def provide_db() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, uri=True, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA query_only = ON')
    conn.execute('PRAGMA mmap_size = 268435456')
    return conn


@get('/v1/videos/{video_id:str}', sync_to_thread=True, cache=300)
def get_video(video_id: str, db: sqlite3.Connection) -> VideoOut:
    row = db.execute(
        'SELECT video_id, title, channel, duration_s, views, region, fetched_at '
        'FROM videos WHERE video_id = ?',
        (video_id,),
    ).fetchone()
    if row is None:
        raise NotFoundException(detail=f'unknown video {video_id}')
    return VideoOut(**dict(row))


@get('/v1/search', sync_to_thread=True, cache=60)
def search(
    q: Annotated[str, Parameter(min_length=2, max_length=64)],
    region: Annotated[str, Parameter(default='US')],
    limit: Annotated[int, Parameter(default=24, ge=1, le=100)],
) -> list[VideoOut]:
    if region not in REGIONS:
        region = 'US'
    # FTS5 prefix match; the trailing * is why we build the term by hand.
    term = ' '.join(f'{tok}*' for tok in q.split() if tok.isalnum())
    if not term:
        return []
    db = provide_db()
    rows = db.execute(
        'SELECT v.video_id, v.title, v.channel, v.duration_s, v.views, '
        '       v.region, v.fetched_at '
        'FROM videos_fts f JOIN videos v ON v.rowid = f.rowid '
        'WHERE videos_fts MATCH ? AND v.region = ? '
        'ORDER BY bm25(videos_fts), v.views DESC LIMIT ?',
        (term, region, limit),
    ).fetchall()
    return [VideoOut(**dict(r)) for r in rows]


app = Litestar(
    route_handlers=[get_video, search],
    dependencies={'db': Provide(provide_db, sync_to_thread=True, use_cache=True)},
)

Two details worth calling out. bm25(videos_fts) sorts ascending because FTS5 returns negative relevance scores — lower is better — which is the opposite of what everyone assumes on first read, and it is the single most common FTS5 bug I have written. And building the MATCH term by hand with isalnum() filtering is not paranoia; FTS5 query syntax will happily interpret a user-supplied " or NEAR as an operator and throw a sqlite3.OperationalError straight into a 500.

The write path is where the time went

The read side was an afternoon. The fetcher was the real work. Eight regions, three API keys, a per-key daily quota, and a hard requirement that one bad region never blocks the other seven.

asyncio.TaskGroup handles the fanout, a Semaphore caps in-flight requests so we do not open 200 sockets against one host, and each region's failure is caught inside its own task so the group does not cancel its siblings. That last part matters: TaskGroup cancels every sibling task when one raises, which is exactly the wrong behavior for independent regions. If you want partial success, you catch inside.

# app/fetcher.py
import asyncio
import time
from dataclasses import dataclass, field

import httpx

API = 'https://www.googleapis.com/youtube/v3/videos'
MAX_INFLIGHT = 6


@dataclass
class RegionResult:
    region: str
    items: list[dict] = field(default_factory=list)
    error: str | None = None
    elapsed_ms: int = 0


async def fetch_region(
    client: httpx.AsyncClient,
    sem: asyncio.Semaphore,
    region: str,
    api_key: str,
) -> RegionResult:
    started = time.monotonic()
    params = {
        'part': 'snippet,statistics,contentDetails',
        'chart': 'mostPopular',
        'regionCode': region,
        'maxResults': 50,
        'key': api_key,
    }
    async with sem:
        for attempt in range(3):
            try:
                resp = await client.get(API, params=params, timeout=8.0)
                if resp.status_code == 403:
                    return RegionResult(region, error='quota', 
                                        elapsed_ms=_ms(started))
                if resp.status_code >= 500:
                    await asyncio.sleep(0.5 * (2 ** attempt))
                    continue
                resp.raise_for_status()
                data = resp.json()
                return RegionResult(region, data.get('items', []),
                                    elapsed_ms=_ms(started))
            except (httpx.TimeoutException, httpx.TransportError) as exc:
                if attempt == 2:
                    return RegionResult(region, error=repr(exc),
                                        elapsed_ms=_ms(started))
                await asyncio.sleep(0.5 * (2 ** attempt))
    return RegionResult(region, error='exhausted', elapsed_ms=_ms(started))


def _ms(started: float) -> int:
    return int((time.monotonic() - started) * 1000)


async def fetch_all(regions: list[str], keys: list[str]) -> list[RegionResult]:
    sem = asyncio.Semaphore(MAX_INFLIGHT)
    limits = httpx.Limits(max_connections=MAX_INFLIGHT, 
                          max_keepalive_connections=MAX_INFLIGHT)
    results: list[RegionResult] = []
    async with httpx.AsyncClient(limits=limits, http2=True) as client:
        async with asyncio.TaskGroup() as tg:
            tasks = [
                tg.create_task(
                    fetch_region(client, sem, region, keys[i % len(keys)])
                )
                for i, region in enumerate(regions)
            ]
        results = [t.result() for t in tasks]
    return results

The eight-region sweep now finishes in about 2.9 seconds, dominated by the slowest single region rather than the sum of all of them. The full refresh — 2,400 videos in batches of 50 — went from 41 minutes to 94 seconds, which fits comfortably inside a single cron slot with room for retries.

A few things I would tell my past self:

http2=True on the httpx client is worth it here because every request goes to one host. Multiplexing over a single connection removed roughly 200 ms of TLS handshake per region on cold starts.
Round-robin the keys by index, not randomly. Random selection clustered onto one key often enough to trip a per-key rate limit while the other two sat idle.
Treat 403 as terminal, not retryable. Quota exhaustion does not get better in 500 ms, and retrying it three times just burns the remaining budget faster.

Calling it from PHP 8.4

The web tier still renders everything. It talks to the metadata service over HTTP on a private interface, with a hard timeout and a fallback to the last-known-good SQLite copy that gets rsynced alongside. If the service is down, pages render slightly stale instead of not at all — which is the only acceptable failure mode for a page that Google crawls.

PHP 8.4's property hooks made the client noticeably tidier: the available flag is derived, not stored, so there is no way for it to go stale.

<?php
declare(strict_types=1);

final class MetadataClient
{
    private ?int $lastFailureAt = null;

    public bool $available {
        get => $this->lastFailureAt === null
            || (time() - $this->lastFailureAt) > self::COOLDOWN;
    }

    private const int COOLDOWN = 30;

    public function __construct(
        private readonly string $baseUrl,
        private readonly float $timeout = 0.75,
    ) {}

    /** @return array<int, array<string, mixed>> */
    public function search(string $q, string $region, int $limit = 24): array
    {
        if (!$this->available) {
            return $this->fallbackSearch($q, $region, $limit);
        }

        $url = $this->baseUrl . '/v1/search?' . http_build_query([
            'q' => $q, 'region' => $region, 'limit' => $limit,
        ]);

        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT_MS     => (int) ($this->timeout * 1000),
            CURLOPT_CONNECTTIMEOUT_MS => 200,
            CURLOPT_HTTPHEADER     => ['Accept: application/json'],
        ]);
        $body = curl_exec($ch);
        $code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
        curl_close($ch);

        if ($body === false || $code !== 200) {
            $this->lastFailureAt = time();
            return $this->fallbackSearch($q, $region, $limit);
        }

        $this->lastFailureAt = null;

        try {
            return json_decode($body, true, 8, JSON_THROW_ON_ERROR);
        } catch (JsonException) {
            $this->lastFailureAt = time();
            return $this->fallbackSearch($q, $region, $limit);
        }
    }

    /** @return array<int, array<string, mixed>> */
    private function fallbackSearch(string $q, string $region, int $limit): array
    {
        $db = new PDO('sqlite:' . __DIR__ . '/../data/metadata.db');
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $db->prepare(
            'SELECT v.video_id, v.title, v.channel, v.views '
            . 'FROM videos_fts f JOIN videos v ON v.rowid = f.rowid '
            . 'WHERE videos_fts MATCH :q AND v.region = :r '
            . 'ORDER BY bm25(videos_fts) LIMIT :n'
        );
        $term = implode(' ', array_map(
            static fn(string $t): string => $t . '*',
            array_filter(explode(' ', $q), ctype_alnum(...))
        ));
        $stmt->bindValue(':q', $term === '' ? 'zzzz' : $term);
        $stmt->bindValue(':r', $region);
        $stmt->bindValue(':n', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

The 30-second circuit-breaker cooldown is not sophisticated, but it prevents the failure mode where every page view pays a 750 ms timeout because the service is restarting. Under load, that difference is the whole ballgame.

Deploying async Python when your pipeline is FTP

Our deploy story is lftp mirroring a directory tree to shared hosts. That works fine for PHP, where uploading a file is the deploy. It does not work for a process that has to be restarted, and shared LiteSpeed hosting will not run a long-lived Python process for you anyway.

So the metadata service does not live on the web hosts. It runs on one small VPS under systemd, and the artifact it produces — a rebuilt metadata.db — is what gets pushed out. The FTP pipeline stayed exactly as it was; it just gained one more file to mirror.

The sequence per cycle:

The fetcher writes into metadata.build.db, a fresh file, never the live one.
PRAGMA wal_checkpoint(TRUNCATE) then VACUUM runs, so what ships is compact and has no sidecar WAL.
An integrity gate runs: row counts per region must be within 20% of the previous build, or the build is discarded and the old file ships again.
lftp mirrors the file to each web host under a temp name, then a single rename swaps it atomically on the remote side.
The service on the VPS reopens its read handle against the new file.

That integrity gate has saved us twice. Once when a quota exhaustion produced a technically valid database containing 340 videos instead of 2,400, and once when a region code typo silently produced an empty JP partition. Both would have shipped. A build that fails the gate is not an outage; a build that ships empty is.

Measuring it honestly

I do not trust a latency number I did not generate under concurrency, and I wanted the load generator to be a single static binary I could drop on any host without a Python environment. Small Go program, 40 lines, no dependencies:

package main

import (
    "fmt"
    "net/http"
    "os"
    "sort"
    "sync"
    "time"
)

func main() {
    url := os.Args[1]
    workers, reqs := 32, 2000

    client := &http.Client{Timeout: 5 * time.Second}
    samples := make([]time.Duration, 0, reqs)
    var mu sync.Mutex
    var wg sync.WaitGroup
    jobs := make(chan int, reqs)

    for w := 0; w < workers; w++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            for range jobs {
                start := time.Now()
                resp, err := client.Get(url)
                if err != nil {
                    continue
                }
                resp.Body.Close()
                mu.Lock()
                samples = append(samples, time.Since(start))
                mu.Unlock()
            }
        }()
    }

    for i := 0; i < reqs; i++ {
        jobs <- i
    }
    close(jobs)
    wg.Wait()

    sort.Slice(samples, func(i, j int) bool { return samples[i] < samples[j] })
    p := func(q float64) time.Duration {
        return samples[int(float64(len(samples)-1)*q)]
    }
    fmt.Printf("n=%d p50=%v p95=%v p99=%v\n",
        len(samples), p(0.50), p(0.95), p(0.99))
}

Numbers from the production box, 32 concurrent workers, 2,000 requests against /v1/search?q=trailer&region=US:

p50 6.4 ms, p95 14.1 ms, p99 31.8 ms. The p99 tail is SQLite page cache misses, not framework overhead.
Single-region /v1/videos/{id} lookups: p50 1.9 ms with the 300-second response cache warm.
Memory: 84 MB RSS steady state with four Granian workers. The mmap'd database does not count against that in any way that matters, since it is shared page cache.

For comparison, the same search served directly from PHP against the same file was p50 8.9 ms — barely different. That is the honest result: the read path was never the bottleneck and moving it to Python did not make it meaningfully faster. The win was entirely on the write path, and I would not have bothered building the read endpoints at all except that having them made the fallback path testable.

What broke in the first two weeks

A sync handler without sync_to_thread blocked the loop. Litestar warned in the logs; I did not read the logs. p99 went to 900 ms under concurrency and I spent an hour blaming SQLite.
check_same_thread=False plus a cached DI provider meant one connection shared across the threadpool. SQLite tolerates this for reads in WAL mode but serializes them. Per-thread connections via threading.local fixed a real throughput ceiling.
msgspec Struct with frozen=True cannot be mutated in a post-processing step. Obvious in hindsight; I had a view-count normalization pass that needed rewriting into the constructor.
The atomic remote rename is not atomic on every FTP server. Two of our hosts implement RNFR/RNTO non-atomically across filesystems. Uploading to the same directory as the target, not /tmp, fixed it.

Conclusion

The part worth generalizing is not "use Litestar." It is that the job which needs concurrency and the job which needs to render HTML in 38 ms are different jobs with different runtime requirements, and shared hosting is genuinely fine for the second one. Splitting them let each stay simple: PHP kept doing what it is good at, the fetcher got an event loop and a process that lives longer than 180 seconds, and SQLite stayed the boundary between them because a file is the easiest interface two languages can agree on.

If you are staring at a chunked cron job with checkpoint rows in it, that is the smell. The checkpoints exist because the process cannot live long enough to finish. Give it a process that can, and most of the machinery around it disappears.

Building a Video Recommendation Engine with SurrealDB Graph Queries

ahmet gedik — Sun, 09 Aug 2026 06:00:02 +0000

For three years I ran video recommendations on the same stack that powers everything else at ViralVidVault: PHP 8.4, SQLite in WAL mode, LiteSpeed, and a thin layer of Cloudflare Workers at the edge. It worked fine for "videos in the same category" and "most viewed this week." It fell apart the moment I tried to answer the question that actually drives engagement on a viral-video site: given that this person watched these six clips, what should they see next?

That is a graph question, not a table question. The signal lives in the edges — who watched what, which videos share viewers, which creators cluster together — and every attempt to model it in SQLite turned into a swamp of self-joins over a watch_events table that got slower with every million rows. Three-hop traversals ("viewers of videos similar to the ones you liked") needed recursive CTEs that I could not cache and could not explain to my future self.

So I moved the recommendation layer — and only that layer — to SurrealDB. This post is the honest write-up: the data model, the graph queries that replaced my join hell, how I keep it GDPR-compliant for a European audience, and the PHP glue that ties it back into the existing site. No rewrite-everything evangelism. SQLite still holds the canonical video catalog. SurrealDB holds the behavior graph.

Why a graph model and not another join

The core recommendation idea is item-to-item collaborative filtering: two videos are related if the same people watch both. In a relational schema you express "videos co-watched with video X" like this:

SELECT w2.video_id, COUNT(*) AS overlap
FROM watch_events w1
JOIN watch_events w2 ON w1.session_id = w2.session_id
WHERE w1.video_id = :target
  AND w2.video_id != :target
GROUP BY w2.video_id
ORDER BY overlap DESC
LIMIT 20;

That is one hop, and it is already a self-join over your largest table. Now add "but weight by how recent the watch was," "exclude videos the person already saw," and "prefer creators they follow," and you are three nested subqueries deep. Each new hop multiplies the join cost. On SQLite the query planner stopped using my indexes past two hops and started doing full scans. I had 40M watch events. It was not going to end well.

Graph databases invert the cost model. Instead of matching rows at query time, the relationship is the stored structure. In SurrealDB you traverse edges with an arrow syntax that reads like the question you are asking, and a two- or three-hop walk stays a graph walk instead of degrading into a cartesian product.

The data model

SurrealDB is schemaless by default, but for a recommendation engine you want discipline, so I define the tables explicitly. There are two node tables — user and video — and the relationship between them is the watched edge. Creators get their own node so I can traverse "other videos by creators this person watches."

-- SurrealQL schema definition
DEFINE TABLE video SCHEMAFULL;
DEFINE FIELD title       ON video TYPE string;
DEFINE FIELD category    ON video TYPE string;
DEFINE FIELD region      ON video TYPE string;      -- ISO country, e.g. 'DE'
DEFINE FIELD published   ON video TYPE datetime;
DEFINE FIELD creator     ON video TYPE record<creator>;
DEFINE INDEX video_cat   ON video FIELDS category;

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD anon_id     ON user TYPE string;         -- rotating pseudonymous id
DEFINE FIELD consent     ON user TYPE bool DEFAULT false;
DEFINE FIELD region      ON user TYPE string;

-- the edge table: users -> watched -> videos
DEFINE TABLE watched SCHEMAFULL TYPE RELATION FROM user TO video;
DEFINE FIELD watched_at  ON watched TYPE datetime DEFAULT time::now();
DEFINE FIELD seconds     ON watched TYPE int;          -- dwell time
DEFINE FIELD completed   ON watched TYPE bool;

The important line is the RELATION table. In SurrealDB an edge is a first-class record with its own fields, so I can store how someone watched a video — dwell time, completion — directly on the relationship. That metadata is what turns a naive co-watch count into something that actually predicts what a person wants next. A three-second bounce and a full watch both create a watched edge in a relational model unless you add columns; here the edge carries the weight natively.

Recording a watch is a RELATE statement. This is what my ingestion Worker sends after a play event clears the consent check:

RELATE user:⟨anon_abc123⟩ -> watched -> video:⟨v_98f2⟩
  SET watched_at = time::now(),
      seconds = 47,
      completed = true;

The recommendation query that replaced the join swamp

Here is the query I was chasing for months in SQLite, expressed as a graph traversal. Read it inside-out: start at the target video, walk backwards along watched edges to reach every user who watched it, then walk forwards from those users to every other video they watched. That is the co-watch neighborhood in two hops.

-- "Videos co-watched with video:v_98f2"
SELECT
    id,
    title,
    category,
    count() AS overlap
FROM (
    SELECT ->watched->video AS recs
    FROM video:⟨v_98f2⟩<-watched<-user
)
SPLIT recs
WHERE recs.id != video:⟨v_98f2⟩
GROUP BY recs
ORDER BY overlap DESC
LIMIT 20;

The arrows carry the meaning. <-watched<-user walks from the video back to the users who watched it. ->watched->video walks from those users out to their other videos. No join keys, no query planner guessing — the traversal follows physical edge pointers. On my dataset this two-hop query runs in single-digit milliseconds where the equivalent SQLite self-join took 400–900ms and refused to cache.

But raw co-watch counts are dumb. A video watched by everyone (the current global viral hit) shows up as "related" to everything, which is useless. I weight by recency and dwell time on the edge, and I penalize globally popular videos so the recommendations stay specific. SurrealQL lets me do the weighting in the same pass:

-- weighted co-watch: recency + dwell, popularity-penalized
LET $target = video:⟨v_98f2⟩;

SELECT
    recs.id AS video,
    recs.title AS title,
    math::sum(
        weight * time::now().sub(edge.watched_at).hours().max(1).pow(-0.3)
    ) AS score
FROM (
    SELECT
        ->watched AS edge,
        ->watched->video AS recs,
        IF ->watched.completed THEN 1.0 ELSE 0.4 END AS weight
    FROM $target<-watched<-user
)
SPLIT recs
WHERE recs.id != $target
  AND recs.region IN ['DE','FR','NL','ES','IT','PL','SE']  -- EU catalog
GROUP BY video
ORDER BY score DESC
LIMIT 20;

A few things worth calling out:

The edge weight is intrinsic. IF ->watched.completed reads a field off the relationship itself. Completed watches count 2.5x a bounce. You cannot do that cleanly without edge properties.
Recency decay uses time::now().sub(watched_at) raised to a negative power, so a co-watch from an hour ago dominates one from last month. Viral video interest is spiky; a 30-day-old signal is nearly noise.
Region filter keeps the EU catalog EU-facing, which matters both for relevance and for the data-residency posture I describe below.

Three hops, when two is not enough

Cold-start videos — the ones just uploaded — have no co-watch history yet, so the two-hop query returns nothing. For those I fall back to a creator-affinity traversal: find the creators whose videos this person watches, then surface other videos by those creators, even brand-new ones.

-- creator-affinity fallback for cold-start videos
SELECT
    ->watched->video->creator->[?]<-creator<-video AS candidates
FROM user:⟨anon_abc123⟩
SPLIT candidates
WHERE candidates.published > time::now() - 7d
ORDER BY candidates.published DESC
LIMIT 15;

That is a three-hop walk — user → watched videos → their creators → other videos by those creators — and it stays readable. The equivalent recursive CTE in SQLite was 40 lines and I never fully trusted it. Here the path is the query. This is the single biggest reason I stopped fighting the relational model for this workload: the query maps one-to-one onto the sentence I would say out loud to describe the recommendation.

Wiring it into a PHP 8.4 stack

The rest of the site is PHP. SurrealDB speaks HTTP and WebSocket; for request-scoped queries I use the HTTP /sql endpoint with a small client. Nothing exotic — curl, a bearer token, and PHP 8.4's typed properties and readonly for the value objects.

<?php
declare(strict_types=1);

final class SurrealClient
{
    public function __construct(
        private readonly string $endpoint,
        private readonly string $namespace,
        private readonly string $database,
        private readonly string $token,
    ) {}

    /** @return array<int, array<string, mixed>> */
    public function query(string $surql, array $vars = []): array
    {
        $ch = curl_init("{$this->endpoint}/sql");
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT_MS     => 250,   // fail fast; recs are non-critical
            CURLOPT_HTTPHEADER     => [
                'Accept: application/json',
                'Content-Type: application/json',
                "Authorization: Bearer {$this->token}",
                "Surreal-NS: {$this->namespace}",
                "Surreal-DB: {$this->database}",
            ],
            CURLOPT_POSTFIELDS => json_encode([
                'query' => $surql,
                'vars'  => $vars,
            ], JSON_THROW_ON_ERROR),
        ]);

        $raw = curl_exec($ch);
        if ($raw === false) {
            throw new RuntimeException('SurrealDB unreachable: ' . curl_error($ch));
        }
        curl_close($ch);

        $decoded = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
        // SurrealDB returns one result object per statement
        return $decoded[0]['result'] ?? [];
    }
}

The recommendation service wraps that client and — this is the part that keeps LiteSpeed happy — treats recommendations as a best-effort side dish. If SurrealDB is slow or down, the page still renders with a category-based fallback pulled from SQLite. A recommendation timeout must never take down a video page.

<?php
declare(strict_types=1);

final class Recommender
{
    private const CACHE_TTL = 900; // 15 min

    public function __construct(
        private readonly SurrealClient $surreal,
        private readonly CategoryFallback $fallback,
        private readonly string $cacheDir,
    ) {}

    /** @return list<string> ordered video ids */
    public function relatedTo(string $videoId, string $region): array
    {
        $key = "{$this->cacheDir}/rec_{$videoId}_{$region}.json";
        if (is_file($key) && (time() - filemtime($key)) < self::CACHE_TTL) {
            return json_decode((string) file_get_contents($key), true);
        }

        try {
            $rows = $this->surreal->query(
                'SELECT recs.id AS video, math::sum(weight) AS score
                 FROM (SELECT ->watched->video AS recs,
                              IF ->watched.completed THEN 1.0 ELSE 0.4 END AS weight
                       FROM type::thing("video", $vid)<-watched<-user)
                 SPLIT recs
                 WHERE recs.id != type::thing("video", $vid)
                   AND recs.region = $region
                 GROUP BY video ORDER BY score DESC LIMIT 20',
                ['vid' => $videoId, 'region' => $region],
            );
            $ids = array_map(static fn(array $r): string => $r['video'], $rows);
        } catch (\Throwable $e) {
            error_log('rec fallback: ' . $e->getMessage());
            $ids = $this->fallback->byCategory($videoId, 20);
        }

        if ($ids !== []) {
            file_put_contents($key, json_encode($ids), LOCK_EX);
        }
        return $ids;
    }
}

Notice type::thing("video", $vid) — that is how you safely build a record id from a bound variable instead of string-concatenating it into the query. Treat it like a prepared statement; never interpolate raw user input into SurrealQL.

The file cache is deliberately the same pattern the rest of the site uses (I cache rendered fragments to disk and let LiteSpeed serve them). Recommendations for a given video are identical across users in the same region, so a 15-minute disk cache absorbs almost all the traffic and SurrealDB only sees cache-miss queries.

Keeping it GDPR-compliant

Running a European viral-video site means the behavior graph is the most sensitive thing I store, and "user watched these videos" is personal data the moment it is tied to an identifiable person. My rules:

No watch edge without consent. The Cloudflare Worker that ingests play events checks the consent cookie before it ever calls RELATE. No consent, no edge. The recommendation then falls back to non-personalized "popular in your region."
Pseudonymous, rotating ids. The user node's anon_id is not an account id or an IP. It is a rotating token that resets on a schedule, so the graph cannot be trivially re-identified. The graph structure survives rotation well enough for co-watch signal because the edges still cluster.
Right to erasure is a single traversal. This is where the graph model pays off for compliance, not just relevance. Deleting a person means deleting their node and every edge attached to it — one statement:

-- GDPR Article 17: erase a user and all their behavior edges
DELETE user:⟨anon_abc123⟩;

In SurrealDB, deleting a record deletes its graph edges with it, so there is no orphaned watched row lingering in a join table waiting to leak. In my old SQLite schema, erasure meant hunting through watch_events, session_map, and three derived aggregate tables, and it was easy to miss one. Here the blast radius of a deletion is exactly the subgraph, and it is provable.

Data residency. The SurrealDB instance runs in an EU region, and the region filters in every query keep EU behavior data flowing to EU users. Nothing about the recommendation path touches infrastructure outside the bloc.

What actually improved

Honest numbers from the cutover, measured over a month against the old category-similarity recommender:

Related-video click-through went up roughly 34%. The co-watch signal is simply better than "same category" for viral content, where the interesting connections cut across categories.
Two-hop query latency dropped from 400–900ms (SQLite self-join, uncached) to 4–9ms (SurrealDB traversal), which is what made real-time personalization viable at all.
Erasure requests went from a 20-minute manual checklist to a one-line delete I could safely automate.

And what did not improve, because I want this to be useful and not a sales pitch:

Operational surface grew. I now run a second datastore. SQLite needed zero babysitting; SurrealDB needs backups, version upgrades, and a health check. For a solo-ish operation that is a real cost.
Cold start is still hard. New users with no edges get non-personalized results. The graph helps videos cold-start via creator affinity, but people cold-start via nothing but region and consent status.
Consistency is looser. SQLite in WAL mode gives me rock-solid transactional guarantees for the catalog. I keep the catalog there for exactly that reason and only push behavior to the graph, where eventual consistency is fine.

Takeaways

If your recommendation logic keeps turning into deeper and deeper self-joins, that is the signal that you are storing a graph inside a table. Moving that one workload — and only that workload — to a graph database bought me queries that read like the question, sub-10ms traversals, and a GDPR erasure story I can actually defend. I did not rewrite the site. SQLite still owns the catalog, LiteSpeed still serves the pages, Cloudflare Workers still guard the edge. SurrealDB just owns the edges between videos and the people who watch them, which is where recommendation quality was hiding the whole time.

Start small: model one edge table, port one query, keep a fallback, and measure the click-through before you commit to anything bigger.

Building an RFC 8628 Device Authorization Flow for Smart TV Video Apps

ahmet gedik — Sat, 08 Aug 2026 19:00:01 +0000

The Tizen build of our TV app shipped with an email-and-password form. Median time to complete a login was 74 seconds. 61% of sessions that opened the form never finished it. The support inbox told us why: on a Samsung remote you drag a D-pad cursor across a 40-key on-screen grid to find the @ symbol, and on the Japanese and Korean builds that grid opens in Kana or Hangul mode by default, so the first six characters of a password come out as ひらがな and the user starts over from scratch.

We deleted the form and implemented RFC 8628, the OAuth 2.0 Device Authorization Grant. The TV displays an eight-character code and a short URL, the user finishes on a phone, and the TV polls until a token appears. Median link time dropped to 19 seconds and completion went to 88%. What follows is the server side of that implementation — PHP 8.4 on LiteSpeed, SQLite for grant state, Cloudflare in front — including the parts the RFC does not cover, like full-width Unicode arriving from CJK input methods and a page cache that will cheerfully serve a stale authorization_pending to a TV that was authorized ten seconds ago.

What the device grant actually requires

The device grant is not "OAuth without a browser." It is OAuth where the browser lives on a different device. The TV never sees a credential, which is the security win: a leaked TV app binary contains no password-handling code path at all.

Four moving parts:

Device authorization endpoint. The TV POSTs client_id and scope, gets back device_code, user_code, verification_uri, verification_uri_complete, expires_in, and interval.
The TV screen. Shows the user_code and the verification_uri, plus a QR of verification_uri_complete so most users never type anything.
The verification page. An ordinary authenticated web page where the user confirms the code and approves the scope.
Token endpoint polling. The TV POSTs grant_type=urn:ietf:params:oauth:grant-type:device_code on a fixed interval and gets authorization_pending, slow_down, access_denied, or expired_token until it gets tokens.

The error codes are the protocol. Every one of them is a 400 with a JSON body, and a client that treats a 400 as fatal will break on the very first poll — a mistake I have now made in two separate codebases.

State lives in one SQLite table

A device grant is short-lived, single-tenant-ish state with a very high read-to-write ratio during its 15-minute life. One table is enough. We run SQLite 3.45 in WAL mode with a 5-second busy timeout, and the whole grant lifecycle never touches another table until the moment tokens are issued.

CREATE TABLE device_auth (
  id               INTEGER PRIMARY KEY,
  device_code_hash BLOB    NOT NULL UNIQUE,   -- sha256(device_code), raw 32 bytes
  user_code        TEXT    NOT NULL UNIQUE,   -- canonical, no separator
  client_id        TEXT    NOT NULL,
  scope            TEXT    NOT NULL DEFAULT '',
  status           TEXT    NOT NULL DEFAULT 'pending'
      CHECK (status IN ('pending','approved','denied','redeemed')),
  user_id          INTEGER,
  device_label     TEXT    NOT NULL DEFAULT '',  -- 'Samsung QN90C · Tokyo, JP'
  interval_s       INTEGER NOT NULL DEFAULT 5,
  poll_count       INTEGER NOT NULL DEFAULT 0,
  last_polled_at   INTEGER NOT NULL DEFAULT 0,
  created_at       INTEGER NOT NULL,
  expires_at       INTEGER NOT NULL
) STRICT;

CREATE INDEX idx_device_auth_sweep ON device_auth(expires_at);

Three decisions worth defending:

STRICT tables (SQLite 3.37+) mean a stray string in expires_at fails at insert time instead of silently comparing wrong three weeks later. There is no reason not to use them on new tables.
device_code is hashed at rest; user_code is not. The device code is a pure bearer credential — anyone holding it can redeem tokens the moment the grant is approved, so a database leak must not hand it over. The user code is worthless on its own: redeeming it requires an authenticated session on the verification page, and it dies in 15 minutes. Keeping it in plaintext lets support answer "the TV shows K7M2-BXRP, what happened?" without a lookup table, and lets us index it directly.
No separate poll_log table. poll_count on the row gives us everything we need for telemetry, and it is a same-row update we were already doing.

Issuing the code

<?php
declare(strict_types=1);

final readonly class DeviceCodeGrant
{
    // No vowels (avoids accidental words in any language), no 0/O/Q/1/I/L
    // (unreadable at three metres on a 4K panel with a CJK-first font stack).
    private const string ALPHABET = '23456789BCDFGHJKMNPRSTVWXYZ';
    private const int    LIFETIME = 900;  // 15 minutes
    private const int    INTERVAL = 5;    // seconds between polls

    public function __construct(private PDO $db) {}

    public function authorize(string $clientId, string $scope, string $label): array
    {
        $deviceCode = bin2hex(random_bytes(32));
        $userCode   = $this->mintUserCode();
        $now        = time();

        $st = $this->db->prepare(
            'INSERT INTO device_auth
                (device_code_hash, user_code, client_id, scope, device_label,
                 interval_s, created_at, expires_at)
             VALUES (:h, :u, :c, :s, :l, :i, :now, :exp)'
        );
        $st->bindValue(':h', hash('sha256', $deviceCode, true), PDO::PARAM_LOB);
        $st->bindValue(':u', $userCode);
        $st->bindValue(':c', $clientId);
        $st->bindValue(':s', $scope);
        $st->bindValue(':l', $label);
        $st->bindValue(':i', self::INTERVAL, PDO::PARAM_INT);
        $st->bindValue(':now', $now, PDO::PARAM_INT);
        $st->bindValue(':exp', $now + self::LIFETIME, PDO::PARAM_INT);
        $st->execute();

        return [
            'device_code'               => $deviceCode,
            'user_code'                 => $this->pretty($userCode),   // K7M2-BXRP
            'verification_uri'          => 'https://topvideohub.com/link',
            'verification_uri_complete' => 'https://topvideohub.com/link?code=' . $userCode,
            'expires_in'                => self::LIFETIME,
            'interval'                  => self::INTERVAL,
        ];
    }

    private function mintUserCode(): string
    {
        $max = strlen(self::ALPHABET) - 1;
        for ($attempt = 0; $attempt < 5; $attempt++) {
            $code = '';
            for ($i = 0; $i < 8; $i++) {
                $code .= self::ALPHABET[random_int(0, $max)];
            }
            $st = $this->db->prepare(
                'SELECT 1 FROM device_auth WHERE user_code = ? AND expires_at > ?'
            );
            $st->execute([$code, time()]);
            if ($st->fetchColumn() === false) {
                return $code;
            }
        }
        throw new RuntimeException('user_code collision budget exhausted');
    }

    private function pretty(string $c): string
    {
        return substr($c, 0, 4) . '-' . substr($c, 4);
    }
}

random_int(), not mt_rand(). The user code is a guessable-space credential and the device code is a bearer token; both need the CSPRNG. The retry loop exists because the UNIQUE constraint will otherwise throw a raw PDOException at an unlucky user, and five attempts against a live set of a few thousand codes in a 2.8 × 10^11 space is a formality that costs nothing.

Choosing an alphabet that survives a three-metre viewing distance

This is the part where the RFC says "the user code SHOULD be short" and leaves you alone with the consequences.

Our alphabet is 27 characters: digits 2–9 and consonants excluding I, L, O, Q. Eight characters gives 27^8 ≈ 2.8 × 10^11 possible codes. Excluding vowels means the generator cannot produce a word — in English, or, more to the point, in romanised Japanese or Indonesian, where a four-consonant-plus-vowel run has a much better chance of landing on something a support agent has to apologise for. Excluding 0/O/Q and 1/I/L removes the two confusion classes that actually cause failed entries.

We considered a digits-only code, which is what several large TV platforms use, because a numeric code triggers a numeric keypad on the phone and is unambiguous in every font on earth. We rejected it: nine digits is a 10^9 space, and defending 10^9 against distributed guessing means aggressive global rate limiting on the verification page — a control that fails open under load in exactly the situation where you need it. We would rather spend the entropy and lean on the QR code so that typing is the fallback path, not the main one.

One rendering detail specific to our market: our TV UI ships a CJK-first font stack so Japanese and Korean titles render without fallback. That stack will happily draw Latin glyphs in a half-width CJK face, where 2 and Z are nearly identical at distance. The link screen forces a Latin display font and tabular figures for the code element only. That single CSS change moved our "code not recognised" rate more than any server-side work did.

Normalizing what the phone actually sends

Here is the bug that cost us a day. A user in Osaka opens the link page on a phone with the Japanese IME active, types the code, and the field receives Ｋ７Ｍ２ーＢＸＲＰ — full-width Latin letters (U+FF21–U+FF3A), full-width digits (U+FF10–U+FF19), and a katakana prolonged sound mark instead of a hyphen. Byte-compared against K7M2BXRP, none of it matches. Our logs showed a burst of failed lookups clustered in JP and KR traffic and nowhere else.

NFKC normalization folds every one of those back to ASCII:

final class UserCode
{
    private const string ALPHABET = '23456789BCDFGHJKMNPRSTVWXYZ';

    /** @return array{0: ?string, 1: ?string} [canonical code, failure hint] */
    public static function normalize(string $raw): array
    {
        // Full-width Latin/digits from a Japanese or Korean IME, ideographic
        // space U+3000, full-width hyphen U+FF0D -> all fold to ASCII here.
        $s = class_exists(\Normalizer::class)
            ? (\Normalizer::normalize($raw, \Normalizer::FORM_KC) ?: $raw)
            : $raw;

        $s = mb_strtoupper(trim($s), 'UTF-8');
        $s = preg_replace('/[^A-Z0-9]/u', '', $s) ?? '';   // drops -, spaces, ー, ・

        if (strlen($s) !== 8) {
            return [null, 'length'];
        }
        if (strspn($s, self::ALPHABET) !== 8) {
            $bad = preg_replace('/[' . self::ALPHABET . ']/', '', $s) ?? '';
            return [null, self::hint($bad)];
        }
        return [$s, null];
    }

    private static function hint(string $bad): string
    {
        return match (true) {
            str_contains($bad, '0'),
            str_contains($bad, 'O'),
            str_contains($bad, 'Q') => 'no_o_zero',
            str_contains($bad, '1'),
            str_contains($bad, 'I'),
            str_contains($bad, 'L') => 'no_i_one',
            default                 => 'charset',
        };
    }
}

Because the alphabet deliberately excludes those confusable characters, an O in the input is always a transcription error, never a valid code. That lets the page say something precise — "codes never contain the letter O, try zero-free" localised per market — instead of a generic "invalid code." We log the hint bucket, not the code itself; the distribution is how we learned that no_i_one was three times more common than no_o_zero on our Traditional Chinese build.

Don't assume ext-intl is present. On one of our LiteSpeed hosts it was not compiled in, and the class_exists guard is what kept the endpoint from fataling instead of merely degrading.

Approval, and the phishing problem the RFC hands to you

RFC 8628 §5.4 is explicit that this grant is phishable: an attacker starts a device authorization for their own client, sends the victim a link with verification_uri_complete, and if the victim clicks approve, the attacker's device gets a token for the victim's account. The protocol cannot prevent this. Your verification page has to.

What we do on the confirm screen:

Never approve on GET. verification_uri_complete pre-fills the code and nothing else. Approval is a POST with a per-session CSRF token.
Show what is being approved, in the user's language. Client display name, the device label we captured at authorization time (model plus city derived from CF-IPCountry and the Cloudflare colo), and the plain-language scope list. "A Samsung TV in Tokyo wants access to your watchlist" is a sentence a user can evaluate; "Approve scope profile watchlist" is not.
Require a live session. If the user is not logged in, they authenticate first and land back on the confirm screen with the code preserved.
Cap attempts. Five wrong user codes per session and per IP per fifteen minutes, then the page stops accepting entries. Against 2.8 × 10^11 this is generous, but it is what turns the entropy argument into an actual guarantee.
Write denials. A "No, this wasn't me" button sets status='denied' so the TV receives access_denied on its next poll and clears the screen immediately, instead of showing a code that silently rots for fifteen minutes.

The token endpoint and the polling state machine

The polling endpoint has one race worth caring about: two concurrent polls with the same device code must not both redeem an approved grant. SQLite gives you this for free with BEGIN IMMEDIATE, which takes the write lock at statement one rather than upgrading later and losing to SQLITE_BUSY.

The subtler trap is that the interval-enforcement write has to survive the error path. If you throw slow_down from inside the transaction and roll back, you discard the last_polled_at update that makes rate limiting work — so an abusive client gets slow_down forever and is never actually slowed. Compute the outcome inside the transaction, commit, then map the outcome to a response.

public function poll(string $clientId, string $deviceCode): array
{
    $hash = hash('sha256', $deviceCode, true);
    $now  = time();

    $this->db->exec('BEGIN IMMEDIATE');   // PDO::beginTransaction() is DEFERRED on SQLite
    try {
        $st = $this->db->prepare(
            'SELECT id, client_id, status, user_id, scope, interval_s, last_polled_at, expires_at
               FROM device_auth WHERE device_code_hash = :h'
        );
        $st->bindValue(':h', $hash, PDO::PARAM_LOB);
        $st->execute();
        $outcome = $this->advance($st->fetch(PDO::FETCH_ASSOC), $clientId, $now);
    } catch (Throwable $e) {
        $this->db->exec('ROLLBACK');
        throw $e;
    }
    $this->db->exec('COMMIT');

    return match ($outcome['state']) {
        'issued'  => $outcome['tokens'],
        'pending' => throw new OAuthError('authorization_pending'),
        'slow'    => throw new OAuthError('slow_down'),
        'denied'  => throw new OAuthError('access_denied'),
        'expired' => throw new OAuthError('expired_token'),
        default   => throw new OAuthError('invalid_grant'),
    };
}

private function advance(array|false $row, string $clientId, int $now): array
{
    // Unknown code and wrong client are indistinguishable to the caller, on purpose.
    if ($row === false || !hash_equals((string) $row['client_id'], $clientId)) {
        return ['state' => 'invalid'];
    }
    if ($now >= $row['expires_at'] || $row['status'] === 'redeemed') {
        return ['state' => 'expired'];
    }
    if ($now - $row['last_polled_at'] < $row['interval_s']) {
        // RFC 8628 §3.5: the client MUST add 5s to its interval on slow_down.
        $this->run(
            'UPDATE device_auth
                SET interval_s = MIN(interval_s + 5, 60),
                    poll_count = poll_count + 1,
                    last_polled_at = ?
              WHERE id = ?',
            [$now, $row['id']]
        );
        return ['state' => 'slow'];
    }
    $this->run(
        'UPDATE device_auth SET last_polled_at = ?, poll_count = poll_count + 1 WHERE id = ?',
        [$now, $row['id']]
    );

    return match ($row['status']) {
        'pending'  => ['state' => 'pending'],
        'denied'   => ['state' => 'denied'],
        'approved' => ['state' => 'issued', 'tokens' => $this->issueTokens($row)],
        default    => ['state' => 'invalid'],
    };
}

issueTokens() runs inside the same transaction and sets status='redeemed' before returning, so a duplicate poll arriving a millisecond later blocks on the write lock and then reads redeemed and gets expired_token. The interval is capped at 60 seconds so a badly written client cannot back itself off into an unusable state. Every response carries Cache-Control: no-store and HTTP 400 for the error cases — a 401 or 403 will send well-behaved OAuth clients down a token-refresh path that does not exist here.

LiteSpeed, Cloudflare, and the cached authorization_pending

Our stack has three caching layers in front of PHP: LiteSpeed's page cache, our own file-based page cache in index.php, and Cloudflare. During the first staging run, a TV would get approved on the phone and then poll authorization_pending for the rest of the grant's life. The approval was in the database. The endpoint was returning a cached response from our own PHP layer, which keyed on the request URI and had never been taught that POST bodies exist.

The checklist we ended up with:

Skip the PHP page cache by path prefix. Anything under /oauth/ bypasses both the read and the write side of the file cache, before any other logic runs.
CacheDisable public /oauth inside the <IfModule LiteSpeed> block in .htaccess. Keep it inside the guard — Apache parses that block's directives differently and you will get a 500 in any environment that isn't LiteSpeed.
Cache-Control: no-store on every response, success and error alike. This is also what stops an intermediate proxy on a hotel or carrier network from holding a token response.
Rate limit on CF-Connecting-IP, not REMOTE_ADDR. Behind Cloudflare, REMOTE_ADDR is a Cloudflare edge address, so an IP-keyed limiter buckets tens of thousands of unrelated users together. Validate that the request actually came from a Cloudflare range before trusting the header, or you have just built a header-spoofable bypass.
A Cloudflare rate-limiting rule as the outer wall, roughly 30 requests per minute per IP on /oauth/token. The in-app limiter still exists; the edge rule is what keeps a misbehaving firmware build from reaching PHP at all.

A cron sweep deletes expired rows hourly and runs PRAGMA optimize. Without it the table grows monotonically with dead grants and the expires_at index gradually stops being worth reading.

The TV client

Our shipping client is JavaScript on Tizen and C++ elsewhere, but the state machine is identical everywhere and this is the reference implementation we test the server against:

import random
import time

import requests

DEVICE_URL = 'https://api.topvideohub.com/oauth/device/code'
TOKEN_URL  = 'https://api.topvideohub.com/oauth/token'
CLIENT_ID  = 'tv-tizen-2026'
GRANT_TYPE = 'urn:ietf:params:oauth:grant-type:device_code'


def link_device(render_on_tv):
    r = requests.post(DEVICE_URL, timeout=10, data={
        'client_id': CLIENT_ID,
        'scope': 'profile watchlist',
    })
    r.raise_for_status()
    auth = r.json()

    render_on_tv(auth['user_code'], auth['verification_uri'],
                 qr=auth['verification_uri_complete'])

    interval = auth.get('interval', 5)
    deadline = time.monotonic() + auth['expires_in']

    while time.monotonic() < deadline:
        # Jitter matters: after a regional outage every TV in the fleet
        # reconnects at once and polls in lockstep without it.
        time.sleep(interval + random.uniform(0, 1.0))
        try:
            resp = requests.post(TOKEN_URL, timeout=10, data={
                'client_id': CLIENT_ID,
                'device_code': auth['device_code'],
                'grant_type': GRANT_TYPE,
            })
        except requests.RequestException:
            continue          # a network blip is not a slow_down; don't back off

        if resp.status_code == 200:
            return resp.json()

        error = resp.json().get('error')
        if error == 'authorization_pending':
            continue
        if error == 'slow_down':
            interval = min(interval + 5, 60)
            continue
        raise RuntimeError('device link failed: ' + str(error))

    raise TimeoutError('user code expired before approval')

Things the client must get right, all of which we got wrong at least once:

expires_in is authoritative. Stop polling and re-render a fresh code; do not poll a dead grant forever.
Use a monotonic clock. TVs correct their system clock over NTP shortly after boot, which is exactly when this loop is running, and a wall-clock deadline can jump backwards by hours.
Treat transport failures separately from protocol errors. Backing off on a dropped TCP connection means a user on flaky hotel Wi-Fi waits a minute for a screen that should have advanced in five seconds.
Render the QR from verification_uri_complete. Around 70% of our successful links now come from a QR scan, and those sessions never touch the normalization code path at all.

Operational notes after a few months

poll_count turned out to be the most useful column in the table. The distribution tells you the real approval latency without any client instrumentation: our p50 is four polls, p90 is eleven, and a bimodal second hump around fifty polls was how we discovered that a Korean carrier's captive portal was intercepting the verification URL and users were retrying on a second device.

Fifteen minutes is a deliberate lifetime choice. Five minutes is too tight for a user who has to go find their phone, unlock it, and log in on a page they have never seen; an hour leaves a valid credential on screen after the family has gone to bed. Fifteen covers the observed p99 with margin.

We also removed the password field from the TV binary entirely rather than leaving it as a fallback. A fallback path that 3% of users take is a path you stop testing, and it was the only place in the app that ever handled a credential. Deleting it took a whole class of problems off the board — which, along with the abandon-rate numbers, is why the device grant is now the only way to sign in to TopVideoHub on a television.

Conclusion

RFC 8628 is a short spec and the happy path is a weekend of work. The cost is entirely in the edges: an alphabet that survives a living-room viewing distance, NFKC normalization for input methods that emit full-width Latin text, interval enforcement that persists through the error path, single-use redemption under a real write lock, and cache layers that must be told in three separate places not to touch these endpoints. None of that is in the RFC, and all of it is what separates a device flow that demos well from one that works for a user in Osaka on a Samsung remote at eleven at night.

Building CSRF Double-Submit Cookie Protection in PHP Video Admin Panels

ahmet gedik — Sat, 08 Aug 2026 11:00:01 +0000

A forged POST that purged our entire CDN cache

Last spring I watched the LiteSpeed page-cache hit ratio on DailyWatch fall off a cliff for twenty minutes. Nothing had deployed. No cron had fired. What actually happened was dumber and scarier than any of that: a bookmarked browser tab, still logged into our admin panel, loaded an unrelated forum page, and that page auto-submitted a hidden HTML form to /ibt/purge-cache. The browser dutifully attached our session cookie, the origin saw a perfectly authenticated request, and the entire edge cache evaporated. Rebuild traffic hammered SQLite for the next half hour.

That is Cross-Site Request Forgery. If your PHP video admin panel authenticates state-changing actions — approve a video, rewrite metadata, purge cache, trigger a re-fetch — using nothing but a session cookie, you have exactly the same hole. The browser sends your cookie on any request to your origin, including requests initiated by a page you don't control. Authentication is not authorization of intent.

This post walks through the pattern I settled on: signed double-submit cookies. It's stateless, it plays nicely with an aggressive page cache, and it fits a PHP 8.4 codebase without dragging in a framework.

Why not server-stored synchronizer tokens

The textbook answer to CSRF is the synchronizer token pattern: generate a random token, store it in the server-side session, embed a copy in every form, compare on submit. It works. But on a video discovery platform it fights the rest of the architecture:

Session storage becomes a write bottleneck. Our admin actions are bursty. Storing and rotating per-request tokens in the SQLite-backed session means write locks on the hot path, competing with the fetch cron.
Page caching gets awkward. We serve most pages through a LiteSpeed page cache and a PHP file cache. A token that must be unique per session cannot live in a cached HTML body without poisoning the cache for everyone.
Horizontal moves get harder. Even though we run single-origin per domain today, anything that assumes sticky server-side session state is a future migration tax.

The double-submit cookie pattern sidesteps all of that. The token lives in a cookie and in the request (header or form field). The server compares the two without storing anything. Same-origin policy guarantees an attacker's page can neither read nor set our cookie, so it cannot make the two copies match.

The naive version has a known weakness: a related subdomain (or a network attacker on plain HTTP) can plant a cookie the victim's browser will send, letting the attacker control both halves. So I use the signed variant OWASP recommends — the token carries an HMAC the attacker can't forge — plus the __Host- cookie prefix to lock down scope.

How the signed double-submit flow works

The lifecycle is small enough to hold in your head:

On any authenticated admin GET, issue a token: random . expiry, signed with a server secret, written to a __Host- cookie.
Render the same token into a hidden form field, and expose it to JavaScript for fetch calls.
On every unsafe request (POST/PUT/PATCH/DELETE), read the cookie and the submitted copy. Require they are byte-for-byte equal, the signature verifies, and the expiry hasn't passed.
Reject with 403 on any failure.

Because the secret never leaves the server, an attacker who can't read our cookie can't produce a value whose signature checks out and whose plaintext matches the cookie. Both conditions have to hold.

Issuing the token

Here is the issuer. Note the __Host- prefix requirements: the cookie must be Secure, have Path=/, and carry no Domain attribute. The browser refuses to accept a __Host--prefixed cookie that violates those rules, which is precisely why it's useful — a subdomain physically cannot overwrite it.

<?php
declare(strict_types=1);

final class CsrfToken
{
    private const COOKIE = '__Host-csrf';
    private const TTL    = 7200; // 2 hours

    public function __construct(private readonly string $secret) {}

    /** Issue a fresh signed token and set the cookie. Returns the token. */
    public function issue(): string
    {
        $random  = bin2hex(random_bytes(32));
        $expires = time() + self::TTL;
        $payload = $random . '.' . $expires;
        $sig     = hash_hmac('sha256', $payload, $this->secret);
        $token   = $payload . '.' . $sig;

        setcookie(self::COOKIE, $token, [
            'expires'  => $expires,
            'path'     => '/',
            'secure'   => true,   // required by __Host- prefix
            'httponly' => false,  // JS reads it for the header variant
            'samesite' => 'Lax',  // Strict breaks OAuth-style redirects
        ]);

        return $token;
    }
}

Two choices deserve a note. First, httponly is false. That feels wrong until you remember what double-submit actually defends: it does not rely on the token being secret from same-origin JavaScript. It relies on a cross-origin page being unable to read it. Letting our own scripts read the cookie is fine, and it's what makes the fetch/header path work. Second, SameSite=Lax is deliberate. Strict would block the cookie on top-level cross-site navigations, which breaks legitimate flows like clicking an admin link from an email. Lax still blocks the dangerous case — cross-site POSTs — because Lax cookies are withheld from cross-origin form submissions.

Call issue() exactly once per rendered admin page, before any output, and only for authenticated admins. Do not call it on cached pages.

Verifying on unsafe requests

The guard runs on every request that could change state. Safe methods pass through untouched; the CSRF model only concerns side-effecting verbs.

<?php
declare(strict_types=1);

final class CsrfGuard
{
    private const COOKIE = '__Host-csrf';
    private const FIELD  = '_csrf';

    public function __construct(private readonly string $secret) {}

    public function verify(): void
    {
        $method = strtoupper($_SERVER['REQUEST_METHOD'] ?? 'GET');
        if (in_array($method, ['GET', 'HEAD', 'OPTIONS'], true)) {
            return; // safe, idempotent methods carry no CSRF risk
        }

        $cookie = (string) ($_COOKIE[self::COOKIE] ?? '');
        $sent   = (string) ($_SERVER['HTTP_X_CSRF_TOKEN']
                            ?? $_POST[self::FIELD] ?? '');

        if ($cookie === '' || $sent === '') {
            $this->reject('missing token');
        }
        // Constant-time equality: the two submitted copies must match.
        if (!hash_equals($cookie, $sent)) {
            $this->reject('token mismatch');
        }
        // And the cookie itself must be one we signed, unexpired.
        if (!$this->signatureValid($cookie)) {
            $this->reject('bad signature');
        }
    }

    private function signatureValid(string $token): bool
    {
        $parts = explode('.', $token);
        if (count($parts) !== 3) {
            return false;
        }
        [$random, $expires, $sig] = $parts;

        if (!ctype_digit($expires) || (int) $expires < time()) {
            return false; // expired or malformed
        }
        $expected = hash_hmac('sha256', $random . '.' . $expires, $this->secret);
        return hash_equals($expected, $sig);
    }

    private function reject(string $reason): never
    {
        error_log('CSRF reject: ' . $reason . ' ip=' . ($_SERVER['HTTP_CF_CONNECTING_IP'] ?? '?'));
        http_response_code(403);
        header('Content-Type: application/json');
        echo json_encode(['error' => 'csrf_failed']);
        exit;
    }
}

The verification does three independent things, and all three matter:

hash_equals($cookie, $sent) enforces the double-submit invariant — a cross-origin attacker can't read the cookie, so they can't make the submitted copy equal it.
signatureValid() upgrades this to the signed variant. Even if a same-site subdomain or a MITM on the HTTP hop plants a cookie, it won't carry a valid HMAC, so it fails.
hash_equals everywhere keeps comparisons constant-time. Using === on a MAC comparison leaks timing and is a real, exploited class of bug.

Wire it into your front controller so it runs before any route handler for admin paths:

$guard = new CsrfGuard(getenv('CSRF_SECRET'));
if (str_starts_with($path, '/ibt')) {
    $guard->verify();
}

Keep CSRF_SECRET in your env config, not in the codebase, and make it at least 32 random bytes. Rotating it invalidates all outstanding tokens, which is a feature during an incident.

Wiring it into forms and fetch calls

Server-rendered forms get a hidden field. Always escape it — the token is hex plus a signature, but escaping on output is a habit you never want to break in a template.

function csrf_field(string $token): string
{
    $safe = htmlspecialchars($token, ENT_QUOTES, 'UTF-8');
    return '<input type="hidden" name="_csrf" value="' . $safe . '">';
}

Used in a template like this:

<form method="post" action="/ibt/purge-cache">
    <?= csrf_field($csrfToken) ?>
    <button type="submit">Purge edge cache</button>
</form>

For the AJAX side of the admin panel — the bits that approve videos or edit metadata inline — read the cookie and send it as a header. Because the cookie is same-origin, this JavaScript works; an attacker's page running on another origin cannot read our document.cookie.

function csrfHeader() {
  const m = document.cookie.match(/(?:^|;\s*)__Host-csrf=([^;]+)/);
  return m ? decodeURIComponent(m[1]) : '';
}

async function adminAction(url, payload) {
  const res = await fetch(url, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-CSRF-Token': csrfHeader(),
    },
    body: JSON.stringify(payload),
    credentials: 'same-origin',
  });
  if (res.status === 403) {
    throw new Error('CSRF rejected — reload the page to refresh your token');
  }
  return res.json();
}

One subtlety worth internalizing: the header path is strictly stronger than the form-field path. Custom headers like X-CSRF-Token cannot be set by a cross-origin HTML form at all, and a cross-origin fetch that tries to add them triggers a CORS preflight your server never approves. So for JSON APIs you effectively get a second, independent CSRF defense for free. I still verify the token, but it's belt and suspenders.

The Cloudflare and LiteSpeed gotchas

This is where a textbook implementation meets a real edge stack, and where I lost the most time.

Never cache the token cookie. Our public pages flow through LiteSpeed page cache and, in front of that, Cloudflare. If a Set-Cookie: __Host-csrf=... header ever gets stored in a shared cache, every visitor receives the same token, and double-submit collapses — everyone's cookie matches everyone else's field. The fix is to issue tokens only on admin routes and to make sure those routes bypass the cache entirely:

In LiteSpeed, keep the admin path out of CacheEnable and send Cache-Control: no-store, private from the admin controller.
In Cloudflare, add a cache rule that bypasses caching for the /ibt* path prefix so the origin's Set-Cookie never lands in the edge.

Mind Flexible SSL. We run Cloudflare in Flexible mode, so the browser-to-Cloudflare hop is HTTPS but the Cloudflare-to-origin hop is plain HTTP. The origin's PHP sees an HTTP request. That matters because __Host- and the Secure attribute are enforced by the browser on receipt — and the browser receives the response over HTTPS from Cloudflare, so the cookie is accepted. It works, but don't let setcookie be gated behind a naive isset($_SERVER['HTTPS']) check; on our origin that variable is empty. Trust X-Forwarded-Proto instead, and always send secure => true.

SameSite is not a substitute. SameSite=Lax blocks the classic cross-site form POST, and modern browsers default unlabeled cookies to Lax anyway. It is a good defense-in-depth layer. It is not a replacement for tokens: it doesn't cover same-site subdomains, older browsers, or certain GET-based state changes, and it silently varies by browser. Tokens are the control; SameSite is the seatbelt.

Proving it actually rejects forgeries

A CSRF control you haven't tried to break is a hope, not a defense. I keep a tiny harness that exercises the three failure modes plus the happy path. It talks to the guard directly by populating superglobals, so it runs anywhere without a browser.

<?php
declare(strict_types=1);
require 'CsrfToken.php';
require 'CsrfGuard.php';

$secret = 'test-secret-not-for-prod';
$issuer = new CsrfToken($secret);

// Simulate issuing a token, capturing what the cookie would hold.
$random  = bin2hex(random_bytes(32));
$expires = time() + 7200;
$payload = $random . '.' . $expires;
$token   = $payload . '.' . hash_hmac('sha256', $payload, $secret);

function runGuard(string $secret, string $cookie, string $sent): int {
    $_SERVER['REQUEST_METHOD'] = 'POST';
    $_COOKIE['__Host-csrf']    = $cookie;
    $_POST['_csrf']            = $sent;
    unset($_SERVER['HTTP_X_CSRF_TOKEN']);
    $pid = pcntl_fork();
    if ($pid === 0) {                       // child: guard calls exit()
        (new CsrfGuard($secret))->verify();
        exit(0);                            // reached only when it passes
    }
    pcntl_waitpid($pid, $status);
    return http_response_code() ?: (pcntl_wexitstatus($status) === 0 ? 200 : 403);
}

assert(runGuard($secret, $token, $token) === 200);   // valid: passes
assert(runGuard($secret, $token, 'garbage') !== 200); // mismatch: rejected
assert(runGuard($secret, '', $token) !== 200);        // no cookie: rejected
assert(runGuard($secret, $token . 'x', $token . 'x') !== 200); // forged sig
echo "all csrf cases passed\n";

The fourth case is the important one, and it's the case naive double-submit fails: an attacker who controls both the cookie and the field but not the secret. Because the signature no longer verifies, the guard rejects it. If you ever drop the HMAC to "simplify," that assertion turns red — which is exactly why it lives in the test.

Hardening and edge cases I hit in production

Token expiry and long sessions. A two-hour TTL means an admin who leaves a form open overnight gets a 403 on submit. I catch that on the client and reload rather than silently losing their edit. Re-issuing on every GET keeps active sessions fresh.
Multiple tabs. Every GET re-issues the cookie, so the newest tab's token wins and older tabs may go stale. If that bites you, widen the TTL or issue per-session-stable tokens (drop the per-request random). I preferred short-lived tokens.
Login and logout. Rotate the token on privilege changes. A token minted before login shouldn't authorize actions after it.
Don't protect GET with side effects — remove the side effects instead. If /ibt/delete?id=5 mutates state on GET, no CSRF token saves you cleanly, because browsers pre-fetch and cache GETs. Make destructive actions POST/DELETE first, then guard them.
Log rejections with the real client IP. Behind Cloudflare, REMOTE_ADDR is an edge node. Use CF-Connecting-IP so your logs point at the actual origin of a forgery attempt.

Conclusion

The cache-purge incident cost us twenty minutes of degraded performance and an afternoon of forensics, and the root cause was embarrassingly ordinary: we trusted a session cookie to prove intent when all it proved was identity. Signed double-submit cookies close that gap without a session store, without poisoning an aggressive page cache, and without a framework — about a hundred lines of PHP 8.4. The three-part check (equal copies, valid signature, unexpired) is what makes it hold up against the subdomain and MITM variants that break the naive version, and the little fork-based test harness is what keeps a future "simplification" from quietly reopening the hole. If your video admin panel changes state on a cookie alone, add the guard before something loads the wrong forum tab for you.

Coordinating Video Region Routers with etcd for Live Config Changes

ahmet gedik — Sat, 08 Aug 2026 00:00:01 +0000

The night a single JSON file took down three regions

At 02:00 UTC one of our region routers started sending every European viewer to a US-East origin. The cause was embarrassingly boring: we deploy configuration to the streaming-discovery fleet over FTP, and a half-written regions.json was picked up mid-upload by the PHP 8.4 router before the transfer finished. For a platform whose entire value is fast discovery, thirty seconds of cross-continent latency is the difference between a play and a bounce. That incident is why we moved routing configuration out of flat files and into etcd, and this post is a field report from running it as the source of truth for the eight region routers behind TrendVidStream, a global multi-region streaming-discovery service.

The short version: FTP is still fine for shipping templates and static assets, but it is a terrible transport for configuration that many processes read concurrently and that has to change atomically. etcd fixed the atomicity, gave us a real change-notification channel, and — almost as a side effect — became our failover signal.

What a region router actually decides

Before etcd it was tempting to think of routing config as "just a file." It is not. Each of our eight region routers makes several decisions per request, and every one of them is driven by config that changes on its own cadence:

Origin selection — which regional origin serves the discovery API for a viewer geolocated to that region.
FTS5 shard — which SQLite FTS5 index the search endpoint queries, since we shard the catalog by continent.
Cron cadence — how often the multi-region cron re-scrapes and re-ranks trending titles for that region.
Failover weights — the ordered list of fallback origins used when the primary origin is unhealthy.
Feature flags — whether a region gets the new autoplay-preview row or the control experience.

Those five knobs move independently. The failover list changes when a box dies, which can be seconds. The cron cadence changes when we tune scraping load, which is weekly. Trying to keep all of that coherent across eight routers with a file that gets overwritten wholesale is how you end up debugging at 02:00.

Why flat files and FTP stopped scaling

Three specific failures pushed us off files:

Non-atomic reads. FTP has no notion of an atomic swap. A reader can open the file between the truncate and the final byte. Even rename tricks only help on the same filesystem — useless when the transport is FTP from a build host.
No change notification. A file gives you nothing better than polling. Our routers were stat-ing regions.json every few seconds, which is both wasteful and slow to react.
No consensus. With a file per host, two hosts can disagree indefinitely and nobody notices. There is no single revision number you can point at and say "everyone should be on this or newer."

etcd answers all three: writes are atomic and linearizable, Watch streams changes with the revision that produced them, and the Raft-backed store gives every reader a monotonic revision to reason about.

The etcd data model we settled on

We keep the layout boring and prefix-scannable. Routing lives under one prefix, health under another:

/tvs/routing/<region> — the JSON blob a router needs for that region.
/tvs/health/<region>/<addr> — a lease-backed key that exists only while an origin is alive.

Regions are the eight we serve: us-east, us-west, eu-west, eu-central, ap-south, ap-southeast, sa-east, af-south. Everything a router needs for a region is in a single value, so a read is one key and an update is one atomic write. No partial states, no cross-key transactions on the hot path.

Publishing config with a Python cron job

Our multi-region cron already regenerates ranking weights on a schedule. It now ends by pushing routing config into etcd instead of writing a file for FTP. The important detail is the compare-before-write: we only put when the value actually changed, so unchanged regions do not churn the revision number and do not wake up every watcher for nothing.

import json
import etcd3

# Region routing config publisher for TrendVidStream.
# Runs on the multi-region cron host after weights are regenerated.

REGIONS = ["us-east", "us-west", "eu-west", "eu-central",
           "ap-south", "ap-southeast", "sa-east", "af-south"]

def publish(client, region, config):
    key = f"/tvs/routing/{region}"
    payload = json.dumps(config, separators=(",", ":"), sort_keys=True)
    # Skip the write when nothing changed: no revision bump, no watcher wakeups.
    current = client.get(key)[0]
    if current is not None and current.decode() == payload:
        return False
    client.put(key, payload)
    return True

def main():
    client = etcd3.client(host="10.0.0.10", port=2379)
    for region in REGIONS:
        cfg = {
            "origin": f"origin-{region}.tvs.internal",
            "fts5_shard": region.split("-")[0],
            "cron_minutes": 15 if region.startswith("eu") else 30,
            "failover": ["us-west"] if region == "us-east" else ["us-east"],
            "enabled": True,
        }
        changed = publish(client, region, cfg)
        print(f"{region}: {'updated' if changed else 'unchanged'}")

if __name__ == "__main__":
    main()

That is the entire write path. Notice there is no locking and no coordination between cron runs — if two runs race, the compare-and-put makes the loser a no-op, and the worst case is one extra revision. etcd's linearizable writes do the hard part.

Watching for changes in the Go router

The routers themselves are Go. The pattern that matters here is the two-step load: do a snapshot read first, then start the watch from the snapshot's revision plus one. If you start the watch before reading, or from revision 0 on a compacted store, you either miss events or replay history you do not want. Anchoring the watch to the snapshot revision guarantees you see every change exactly once, in order, with no gap.

package main

import (
    "context"
    "encoding/json"
    "log"
    "sync"
    "time"

    clientv3 "go.etcd.io/etcd/client/v3"
)

type RouteConfig struct {
    Origin    string   `json:"origin"`
    FTS5Shard string   `json:"fts5_shard"`
    CronMin   int      `json:"cron_minutes"`
    Failover  []string `json:"failover"`
    Enabled   bool     `json:"enabled"`
}

type Router struct {
    mu     sync.RWMutex
    routes map[string]RouteConfig
}

func (r *Router) set(region string, cfg RouteConfig) {
    r.mu.Lock()
    defer r.mu.Unlock()
    r.routes[region] = cfg
}

func (r *Router) Origin(region string) string {
    r.mu.RLock()
    defer r.mu.RUnlock()
    if c, ok := r.routes[region]; ok && c.Enabled {
        return c.Origin
    }
    return "origin-us-east.tvs.internal" // safe default
}

func main() {
    cli, err := clientv3.New(clientv3.Config{
        Endpoints:   []string{"10.0.0.10:2379", "10.0.0.11:2379", "10.0.0.12:2379"},
        DialTimeout: 5 * time.Second,
    })
    if err != nil {
        log.Fatal(err)
    }
    defer cli.Close()

    router := &Router{routes: map[string]RouteConfig{}}

    // 1. Load the current snapshot with a linearizable range read.
    resp, err := cli.Get(context.Background(), "/tvs/routing/", clientv3.WithPrefix())
    if err != nil {
        log.Fatal(err)
    }
    for _, kv := range resp.Kvs {
        var cfg RouteConfig
        if err := json.Unmarshal(kv.Value, &cfg); err == nil {
            router.set(regionFromKey(string(kv.Key)), cfg)
        }
    }

    // 2. Watch from snapshot revision + 1 so no event is missed or replayed.
    wch := cli.Watch(context.Background(), "/tvs/routing/",
        clientv3.WithPrefix(), clientv3.WithRev(resp.Header.Revision+1))

    for wr := range wch {
        for _, ev := range wr.Events {
            region := regionFromKey(string(ev.Kv.Key))
            var cfg RouteConfig
            if err := json.Unmarshal(ev.Kv.Value, &cfg); err != nil {
                log.Printf("bad config for %s: %v", region, err)
                continue // keep the last good value, never apply garbage
            }
            router.set(region, cfg)
            log.Printf("route updated: %s -> %s (rev %d)",
                region, cfg.Origin, ev.Kv.ModRevision)
        }
    }
}

func regionFromKey(key string) string {
    for i := len(key) - 1; i >= 0; i-- {
        if key[i] == '/' {
            return key[i+1:]
        }
    }
    return key
}

The router holds the whole routing table in memory behind an RWMutex, so request-path reads never touch the network. etcd is consulted once at boot and thereafter only pushes deltas. When a bad blob arrives — say a publisher bug — the unmarshal fails, we log it, and the router keeps serving the last known-good value. That fail-safe behavior is exactly what the FTP setup lacked.

Reading etcd from PHP without a gRPC extension

Not every edge is Go. A large part of our discovery site is still PHP 8.4 running behind LiteSpeed, and I did not want to compile a gRPC extension onto shared-ish hosting. etcd's v3 API is also exposed as a gRPC-gateway JSON endpoint, which means plain curl. Keys and values come back base64-encoded, and a prefix scan is expressed as a range with range_end set to the key with its last byte incremented.

<?php
declare(strict_types=1);

// PHP 8.4 discovery edge. Reads etcd over its gRPC-gateway JSON API,
// so no gRPC extension is required on the LiteSpeed hosts.

final class EtcdRoutes
{
    private const CACHE = '/dev/shm/tvs_routes.json';
    private const TTL   = 5; // seconds; a floor between range reads

    public function __construct(private string $endpoint = 'http://10.0.0.10:2379') {}

    /** @return array<string,mixed> */
    public function region(string $region): array
    {
        $all = $this->snapshot();
        return $all[$region] ?? ['origin' => 'origin-us-east.tvs.internal', 'enabled' => true];
    }

    /** @return array<string,array<string,mixed>> */
    private function snapshot(): array
    {
        if (is_file(self::CACHE) && (time() - filemtime(self::CACHE)) < self::TTL) {
            return json_decode((string) file_get_contents(self::CACHE), true) ?: [];
        }

        // Range request: key .. range_end covers the whole prefix.
        $key = base64_encode('/tvs/routing/');
        $end = base64_encode('/tvs/routing0'); // '0' is the byte after '/'
        $body = json_encode(['key' => $key, 'range_end' => $end]);

        $ch = curl_init($this->endpoint . '/v3/kv/range');
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_POSTFIELDS     => $body,
            CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 2,
        ]);
        $raw = curl_exec($ch);
        if ($raw === false) {
            // etcd unreachable: serve the last good snapshot, never fail closed.
            return is_file(self::CACHE)
                ? (json_decode((string) file_get_contents(self::CACHE), true) ?: [])
                : [];
        }

        $routes = [];
        foreach (json_decode($raw, true)['kvs'] ?? [] as $kv) {
            $region = basename(base64_decode($kv['key']));
            $routes[$region] = json_decode(base64_decode($kv['value']), true);
        }
        file_put_contents(self::CACHE, json_encode($routes), LOCK_EX);
        return $routes;
    }
}

$routes = new EtcdRoutes();
$origin = $routes->region('eu-west')['origin'];
header('X-TVS-Origin: ' . $origin);

The PHP side caches the snapshot in /dev/shm for a few seconds so a burst of requests does not hammer etcd, and it degrades to the last good snapshot if etcd is unreachable. It is not watch-based — PHP-FPM workers are short-lived, so a tiny TTL plus shared-memory cache is the pragmatic choice. The Go routers get instant pushes; the PHP edges lag by up to five seconds, which is completely acceptable for discovery pages.

Health and failover with leases

The part that surprised me most was how naturally failover fell out of the same store. Each origin registers itself under a lease and keeps it alive. If a box crashes, the keepalive stops, the lease expires, and the key vanishes on its own. The publisher watches /tvs/health/ and rewrites the failover weights when an origin disappears — no separate health-check service, no cron reconciliation.

// Each origin registers under a lease; if the box dies the key expires
// and the publisher drops it from the failover weights.
func registerOrigin(cli *clientv3.Client, region, addr string) error {
    ctx := context.Background()
    lease, err := cli.Grant(ctx, 10) // 10s TTL
    if err != nil {
        return err
    }
    key := "/tvs/health/" + region + "/" + addr
    if _, err := cli.Put(ctx, key, "up", clientv3.WithLease(lease.ID)); err != nil {
        return err
    }
    ch, err := cli.KeepAlive(ctx, lease.ID)
    if err != nil {
        return err
    }
    go func() {
        for range ch {
            // drain keepalive acks; loop ends when the lease is lost
        }
        log.Printf("lease for %s lost — origin will be evicted", key)
    }()
    return nil
}

A 10-second TTL means we detect a dead origin within about ten seconds and re-point traffic on the next publisher pass. Tighten the TTL and you detect faster but risk flapping on a GC pause; loosen it and you carry a dead origin longer. Ten seconds has been a good balance for us.

Guarding against the split-brain we were afraid of

The original fear was: what if the config store itself disagrees with itself and we route half of Europe wrong? etcd's Raft consensus makes that specific failure impossible. A write is acknowledged only after a quorum of the cluster accepts it, and linearizable reads see the latest committed value. If the cluster loses quorum — say two of three nodes are gone — etcd stops accepting writes rather than serving stale or divergent data. For config, a store that refuses to change is far safer than one that changes inconsistently: our routers just keep running their last good in-memory table.

The revision number is the other quiet win. Every value carries the revision that produced it. When we debug, we can ask "what revision is each router on?" and get a straight answer. With files spread over FTP there was never a single number to compare.

What FTP still does, and what it does not

We did not rip out FTP — that would be throwing away a tool that works for what it is good at. Templates, CSS, and the PHP application code still ship over FTP automation, because those are versioned artifacts that change on a deploy cadence and are read once per process start. What moved to etcd is only the live, concurrent, atomically-changing configuration: routing tables, cron cadence, feature flags, and health. The rule of thumb we ended up with: if two processes read it at the same time and it can change between reads, it belongs in etcd; if it is a deploy artifact, FTP is fine.

Lessons after six months

Anchor watches to a snapshot revision. The single most common etcd bug is a gap between the initial read and the watch. Read first, watch from revision + 1.
Keep values self-contained. One key per region, whole config in the value. Avoid cross-key transactions on the request path.
Compare before writing. Skipping no-op writes keeps the revision history and the watch stream quiet, which makes real changes easy to see.
Always have a safe default in code. Every reader falls back to us-east and a cached snapshot. etcd being down should degrade latency, never availability.
Leases are underrated. Health and failover for free, with no extra service to run.

Conclusion

Moving region-router configuration into etcd turned a fragile, file-shaped process into a boringly reliable one. Writes are atomic, changes propagate to the Go routers in milliseconds and to the PHP edges within a few seconds, and origin failover falls out of the same lease mechanism we use for health. FTP still ships our code and templates, but nothing that changes under live traffic touches a flat file anymore. If you run a multi-region service and you are still overwriting a config file in place, the 02:00 incident is waiting for you too — etcd is a small dependency to add for never debugging that one again.