DEV Community: ahmet gedik

Building Video Recommendation Graphs With Apache AGE and Cypher

ahmet gedik — Thu, 23 Jul 2026 04:00:01 +0000

The join that killed our "related videos" panel

DailyWatch surfaces trending and evergreen videos to a global audience, and the single most valuable piece of real estate on a watch page is the "related videos" rail. For a long time that rail was powered by a SQLite query that joined a videos table against a video_tags bridge table, counted shared tags, and ordered by the overlap. It worked until it didn't.

The problem is that "related" is rarely one hop deep. A user watching a cooking video does not just want other videos with the same tags — they want videos watched by people who watched this video, videos from channels that frequently co-appear in the same sessions, and videos two or three hops away in a co-view network. Expressing "videos co-watched with videos co-watched with X, excluding what X's channel already published" in SQL means a stack of self-joins, temporary tables, and recursive CTEs that nobody on the team wanted to touch. Each new relationship type added another join and another 40ms to the query.

That is a graph problem wearing a relational costume. This post walks through how I prototyped the co-view network on DailyWatch using Apache AGE, the PostgreSQL extension that adds openCypher graph queries on top of a normal Postgres database, so we could keep our existing relational tables and add graph traversal without standing up a separate Neo4j cluster.

Why Apache AGE instead of a dedicated graph database

Before writing a line of Cypher I made a short list of what actually mattered for a small team running a free product on a tight budget:

No new operational surface. We already run Postgres for a couple of internal services. AGE is an extension (CREATE EXTENSION age;), not a new server to monitor, back up, and secure.
Mixed workloads in one place. I can join a graph traversal result against a plain relational videos table in a single statement. A separate graph database would force a network round trip and application-side joins.
openCypher is portable. If we ever outgrow AGE, the Cypher queries mostly transfer to Neo4j or Memgraph. The mental model does not get thrown away.
SQL escape hatch. AGE returns graph query results as a set of agtype columns you can wrap in a normal SELECT, so existing tooling, connection pools, and PHP PDO code keep working.

The honest tradeoff: AGE is not as fast as a purpose-built graph engine at very deep traversals over hundreds of millions of edges, and its query planner is younger than Postgres core's. For a co-view network of a few million videos and edges, where most useful queries are two to three hops, it is more than enough. Our production discovery stack still leans on SQLite FTS5 behind LiteSpeed and Cloudflare for full-text search; AGE is a complementary system for relationship queries, not a replacement for search.

Modeling videos and channels as a graph

The first step is deciding what the nodes and edges are. For the co-view network I settled on:

Nodes: Video (with id, title, channel_id, views) and Channel (with id, name).
Edges: PUBLISHED from a Channel to a Video, and CO_VIEWED between two Video nodes carrying a weight property — how many sessions watched both.

After installing AGE and loading it into the session, you create a graph namespace and start defining vertices and edges with Cypher. AGE ships a cypher() function that you call from inside a SQL statement.

-- Load the extension and put its operators on the search path
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

-- One graph namespace holds all our video relationships
SELECT create_graph('dailywatch');

-- Create a couple of channels and videos
SELECT * FROM cypher('dailywatch', $$
    CREATE (c:Channel {id: 'ch_101', name: 'WeeknightKitchen'}),
           (v1:Video {id: 'vid_a1', title: '15-Minute Garlic Noodles',
                      channel_id: 'ch_101', views: 48210}),
           (v2:Video {id: 'vid_b2', title: 'One-Pan Lemon Chicken',
                      channel_id: 'ch_101', views: 33190}),
           (c)-[:PUBLISHED]->(v1),
           (c)-[:PUBLISHED]->(v2)
$$) AS (result agtype);

A few things worth calling out. The graph name ('dailywatch') is passed as a string to every cypher() call, and the trailing AS (result agtype) is mandatory — AGE forces you to declare the shape of the returned columns in SQL, because SQL is statically typed and Cypher is not. If your RETURN clause yields three values, you declare three columns.

Loading edges in bulk from your relational tables

You almost never build the graph by hand. In our case the co-view weights are computed by a nightly batch job that reads watch sessions and produces pairs of video ids with a count. The cleanest way to get that data into AGE is to generate the co-view edges from a query, matching existing Video nodes by id and creating the relationship between them.

Here is the Python loader I use. It reads pre-aggregated co-view pairs and issues one MERGE per pair so the load is idempotent — rerunning it updates weights instead of duplicating edges.

import psycopg2

def load_coview_edges(conn, pairs):
    """pairs: iterable of (video_id_a, video_id_b, weight)"""
    cur = conn.cursor()
    cur.execute("LOAD 'age';")
    cur.execute('SET search_path = ag_catalog, "$user", public;')

    cypher = """
        SELECT * FROM cypher('dailywatch', $$
            MATCH (a:Video {id: $aid}), (b:Video {id: $bid})
            MERGE (a)-[e:CO_VIEWED]->(b)
            SET e.weight = $w
            RETURN e
        $$, %s) AS (edge agtype);
    """

    for a_id, b_id, weight in pairs:
        params = psycopg2.extras.Json(
            {"aid": a_id, "bid": b_id, "w": weight}
        )
        cur.execute(cypher, (params,))

    conn.commit()
    cur.close()


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=discovery user=age_writer")
    sample = [
        ("vid_a1", "vid_b2", 512),
        ("vid_a1", "vid_c3", ើ 331),
        ("vid_b2", "vid_c3", 198),
    ]
    load_coview_edges(conn, sample)

The parameter passing here is the part people trip over. AGE lets you pass a single SQL parameter into a Cypher query — a JSON object — and reference its keys inside Cypher as $aid, $bid, and $w. You must pass it as a JSON value (psycopg2's Json adapter), and the placeholder count in SQL (%s) has to match. Do not try to string-format video ids directly into the Cypher text; that path is both a SQL injection risk and a quoting nightmare.

For a first bulk load of millions of edges, per-row MERGE is too slow. In that situation I stage the pairs in a plain relational table and use AGE's load_edges_from_file helpers, or generate the graph inside a single Cypher statement that UNWINDs a list. The per-row loop above is what I run for the incremental nightly deltas, which are only a few thousand changed pairs.

Querying the co-view network

Now the payoff. The "related videos" rail becomes a two-hop traversal: start at the video being watched, walk CO_VIEWED edges, and rank the destinations by edge weight, excluding videos from the same channel so the rail does not fill up with the current creator's back catalog.

SELECT * FROM cypher('dailywatch', $$
    MATCH (start:Video {id: 'vid_a1'})-[e:CO_VIEWED]->(rec:Video)
    WHERE rec.channel_id <> start.channel_id
    RETURN rec.id, rec.title, e.weight
    ORDER BY e.weight DESC
    LIMIT 12
$$) AS (video_id agtype, title agtype, weight agtype);

That single query replaced roughly 40 lines of SQL with three self-joins. But the real win is depth. Suppose a freshly ingested video has almost no direct co-views yet — a cold-start problem. We can reach through one intermediate video to find second-degree neighbors and still surface something relevant:

SELECT * FROM cypher('dailywatch', $$
    MATCH (start:Video {id: 'vid_new'})
          -[:CO_VIEWED]->(mid:Video)
          -[:CO_VIEWED]->(rec:Video)
    WHERE rec.id <> start.id
      AND rec.channel_id <> start.channel_id
    RETURN rec.id, rec.title, count(*) AS paths
    ORDER BY paths DESC
    LIMIT 12
$$) AS (video_id agtype, title agtype, paths agtype);

The count(*) here counts how many distinct two-hop paths reach each candidate, which is a surprisingly good relevance signal: a video reachable through many different intermediates is broadly related, not a fluke of one weird session. Trying to write that path-counting logic in relational SQL means a recursive CTE plus manual cycle detection. In Cypher it is one MATCH pattern.

Wiring it into a PHP watch page

On the application side, AGE results come back as agtype, which is a superset of JSON. From PHP 8.4 I query it over PDO exactly like any other Postgres result and decode the values. Because agtype string values arrive wrapped in quotes, I strip them before use.

<?php
declare(strict_types=1);

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

    /** @return list<array{id:string,title:string,weight:int}> */
    public function forVideo(string $videoId, int $limit = 12): array
    {
        $this->pdo->exec("LOAD 'age'");
        $this->pdo->exec('SET search_path = ag_catalog, "$user", public');

        $sql = <<<SQL
            SELECT * FROM cypher('dailywatch', \$\$
                MATCH (s:Video {id: \$vid})-[e:CO_VIEWED]->(r:Video)
                WHERE r.channel_id <> s.channel_id
                RETURN r.id, r.title, e.weight
                ORDER BY e.weight DESC
                LIMIT \$lim
            \$\$, :params) AS (id agtype, title agtype, weight agtype)
        SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':params' => json_encode([
                'vid' => $videoId,
                'lim' => $limit,
            ], JSON_THROW_ON_ERROR),
        ]);

        $out = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_NUM) as $row) {
            $out[] = [
                'id'     => trim((string) $row[0], '"'),
                'title'  => trim((string) $row[1], '"'),
                'weight' => (int) $row[2],
            ];
        }

        return $out;
    }
}

A couple of production notes from wiring this up behind LiteSpeed and Cloudflare:

Cache the rail, not the query. The related-videos rail changes slowly — new co-view weights land once a night. We render it into the watch page HTML and let LiteSpeed's page cache and Cloudflare hold it for hours. AGE only gets hit on a cache miss, which keeps the extension well away from the hot path.
Read replica for graph reads. The nightly loader writes to the primary; the watch pages read from a replica. Graph traversals are read-heavy and benefit from being isolated from write contention.
Set a statement timeout. A malformed or unexpectedly deep traversal can run long. SET statement_timeout = '250ms' on the connection used for page rendering means a pathological query fails fast instead of holding a worker.

Indexing so traversals stay fast

The naive graph query above does a label scan to find the start node. On a graph of any size you want an index on the property you look nodes up by. AGE stores each vertex label as its own table under the graph's schema, so you index it like any Postgres table, reaching into the properties column with the same agtype operators Cypher uses.

-- Index the id property of Video vertices for fast MATCH lookups
CREATE INDEX video_id_idx
    ON dailywatch."Video"
    USING btree (agtype_access_operator(properties, '"id"'::agtype));

-- And an index on the CO_VIEWED edge weight for ORDER BY
CREATE INDEX coview_weight_idx
    ON dailywatch."CO_VIEWED"
    USING btree (agtype_access_operator(properties, '"weight"'::agtype));

Without the first index, every MATCH (v:Video {id: ...}) scans the whole Video table. With it, the start-node lookup is a single index probe and the traversal only touches the edges connected to that node. On our dataset that took the two-hop related query from roughly 180ms to under 15ms. Run EXPLAIN on the wrapping SQL statement to confirm the planner actually uses the index — because the graph query is generated inside cypher(), you occasionally need to nudge it by making sure the property access expression in your index exactly matches what the query generates.

What I would watch out for

A few sharp edges I hit that are worth knowing before you commit:

agtype quoting leaks everywhere. String values come back JSON-quoted. Build one decode helper and route every result through it, or you will scatter trim($x, '"') across your codebase.
The single-parameter rule. You get exactly one SQL parameter into a Cypher call, and it has to be a JSON object. Design your loaders around passing a map, not positional args.
Planner immaturity on deep queries. Three hops is fine. Once you go four or five hops over a dense graph, test with real data volumes — the plan can degrade, and you may need to restructure the pattern or add intermediate WITH clauses to bound the working set.
Version pinning matters. AGE tracks specific Postgres major versions closely. Pin both in your infrastructure so an unplanned Postgres upgrade does not leave the extension unbuildable.
It is a complement, not a replacement. Full-text discovery still belongs in a search engine. AGE is for the relationship questions that search cannot answer cheaply.

Conclusion

The co-view network on DailyWatch stopped being a pile of self-joins the moment we modeled it as an actual graph. Apache AGE let us do that without adopting a whole new database — the graph lives next to our relational tables, speaks openCypher, and returns results we can join and cache with the tooling we already run. The wins were concrete: the related-videos query dropped from dozens of lines of SQL to a single readable pattern, cold-start videos get sensible recommendations through two-hop traversals, and with the right property indexes the traversals run in single-digit milliseconds behind our existing cache layers.

If you have a "things related to things related to things" problem trapped inside recursive CTEs, try modeling it as nodes and edges in AGE before you stand up a separate graph cluster. Start with one graph namespace, load a slice of your data, write the traversal you have been dreading in SQL, and measure. The Cypher version is almost always shorter, and more importantly, it is the version you will still understand six months from now.

Building a Multi-Region Health-Check Aggregator for Video CDN Origins

ahmet gedik — Wed, 22 Jul 2026 19:00:00 +0000

Last month one of our eight edge regions started returning HTTP 200 for manifest requests but serving a truncated HLS playlist — the origin was up, the CDN was up, TLS was valid, and every naive uptime monitor in the world was green. Viewers in Singapore just saw a spinner. That incident is the reason I stopped trusting single-probe uptime checks and built a proper multi-region health-check aggregator for our video origins. I run TrendVidStream, a multi-region streaming-discovery platform that fans traffic across eight geographic regions, and "is the server responding" turned out to be almost useless as a health signal for video delivery. What you actually need is a correlated view: the same set of semantic checks, run from every region, folded into one status table you can reason about.

This post walks through the aggregator we run today. It's deliberately boring technology — PHP 8.4 for the reporting layer, SQLite with FTS5 for storage and searchable incident history, a Go probe binary for the actual concurrent checking, and cron plus FTP automation to distribute the probes to each region. No Kubernetes, no time-series database, no service mesh. It fits on the same LiteSpeed shared hosts that serve the site, and it has caught three real incidents that Pingdom-style monitors missed entirely.

Why HTTP 200 Is a Lie for Video

A video request is not a single round trip. A viewer hitting a playback URL triggers a chain: DNS resolution, the master .m3u8 manifest, a media playlist per rendition, then a stream of .ts or .m4s segments. Any one of those can fail while the others succeed, and the failure modes are region-specific because different regions resolve to different edge PoPs.

The checks that actually matter for us are:

Manifest fetch + parse. The master playlist must return 200 and parse into at least one valid variant stream with a BANDWIDTH attribute. A byte-truncated manifest returns 200 with a Content-Length mismatch.
Segment reachability. Pull the first segment referenced by the lowest-bitrate rendition and confirm it's non-empty and has a video/* or application/octet-stream content type.
Time-to-first-byte per region. Absolute latency matters less than drift — a region whose TTFB doubled over an hour is degrading even if it's still "up."
TLS expiry horizon. Certs that expire in under 14 days are a slow-motion outage.
Cross-region consistency. If seven regions see manifest revision v=812 and one sees v=799, that region is serving stale cache.

None of these are expressible as "ping the URL and check the status code." They need a probe that understands the protocol.

The Probe: A Small Go Binary

I chose Go for the probe specifically because it cross-compiles to a single static binary I can drop onto any host, and because goroutines make concurrent per-region checks trivial. Each region runs the same binary from cron; the region identity comes from an environment variable. The probe emits one JSON line per check to stdout, which the cron wrapper ships back to the aggregator.

package main

import (
    "crypto/tls"
    "encoding/json"
    "fmt"
    "io"
    "net/http"
    "os"
    "strings"
    "time"
)

type Result struct {
    Region    string  `json:"region"`
    Target    string  `json:"target"`
    Check     string  `json:"check"`
    OK        bool    `json:"ok"`
    TTFBms    int64   `json:"ttfb_ms"`
    Status    int     `json:"status"`
    Detail    string  `json:"detail"`
    CertDays  int     `json:"cert_days"`
    CheckedAt int64   `json:"checked_at"`
}

func probeManifest(region, url string) Result {
    r := Result{Region: region, Target: url, Check: "manifest", CheckedAt: time.Now().Unix()}
    client := &http.Client{Timeout: 8 * time.Second}

    start := time.Now()
    resp, err := client.Get(url)
    if err != nil {
        r.Detail = err.Error()
        return r
    }
    defer resp.Body.Close()
    r.TTFBms = time.Since(start).Milliseconds()
    r.Status = resp.StatusCode

    // Capture TLS expiry horizon on the same connection.
    if resp.TLS != nil && len(resp.TLS.PeerCertificates) > 0 {
        leaf := resp.TLS.PeerCertificates[0]
        r.CertDays = int(time.Until(leaf.NotAfter).Hours() / 24)
    }

    body, _ := io.ReadAll(io.LimitReader(resp.Body, 1<<20))
    text := string(body)

    // Semantic validation: a real master playlist advertises variants.
    if resp.StatusCode != 200 {
        r.Detail = fmt.Sprintf("status %d", resp.StatusCode)
        return r
    }
    if !strings.Contains(text, "#EXTM3U") {
        r.Detail = "missing #EXTM3U header"
        return r
    }
    if !strings.Contains(text, "BANDWIDTH=") && !strings.Contains(text, "#EXTINF") {
        r.Detail = "no variants or segments in manifest"
        return r
    }
    r.OK = true
    r.Detail = "ok"
    return r
}

func main() {
    region := os.Getenv("PROBE_REGION")
    if region == "" {
        region = "unknown"
    }
    tlsCfg := &tls.Config{MinVersion: tls.VersionTLS12}
    http.DefaultTransport.(*http.Transport).TLSClientConfig = tlsCfg

    enc := json.NewEncoder(os.Stdout)
    for _, target := range os.Args[1:] {
        enc.Encode(probeManifest(region, target))
    }
}

A few deliberate choices worth calling out. The io.LimitReader cap at 1 MiB stops a misbehaving origin from streaming us a whole video during a health check. Reading the TLS peer certificate off the same response means we get cert expiry for free without a second handshake. And the probe never exits non-zero on a failed check — a failed check is data, not a probe error, so it goes into the JSON stream like everything else. The cron wrapper only cares whether the binary ran.

Getting Probes to Eight Regions Without a Fleet

Here is the part people over-engineer. You do not need agents-as-a-service or a config-management stack to run a probe in eight regions. Each of our regional hosts already runs cron for content fetch jobs. We ship the probe binary and a tiny wrapper to each host over FTP as part of the same deploy that pushes the site, then a per-host crontab entry runs it and POSTs the results back.

The deploy side is a shell loop over an lftp mirror — the same FTP automation pattern the rest of our infrastructure uses. The critical, hard-won lesson: the host list file must have Unix line endings, or a stray \r corrupts the remote path and the upload silently lands in the wrong directory.

#!/usr/bin/env bash
set -euo pipefail

# One line per region: label host user pass remote_dir
# File MUST be LF-only — verify with `cat -A regions.conf` (no ^M).
while IFS=' ' read -r label host user pass dir; do
  [[ "$label" =~ ^#.*$ || -z "$label" ]] && continue
  echo ">> shipping probe to ${label} (${host})"
  lftp -u "${user},${pass}" "${host}" <<EOF
set ssl:verify-certificate no
set net:timeout 20
put -O "${dir}/bin" ./probe-linux-amd64 -o probe
put -O "${dir}/bin" ./run_probe.sh
chmod 755 ${dir}/bin/probe ${dir}/bin/run_probe.sh
bye
EOF
done < regions.conf

echo "all regions updated"

The per-host run_probe.sh is what cron actually invokes. It sets the region label, runs the probe against our origin URLs, and pipes the JSON lines back to the aggregator's ingest endpoint. If the POST fails, it spools the lines to a local file so the next run can retry — we never want a network blip between a region and the aggregator to look like an origin outage.

#!/usr/bin/env bash
set -uo pipefail
export PROBE_REGION="${1:-unknown}"
INGEST="https://status.trendvidstream.com/ingest"
SPOOL="$(dirname "$0")/spool.ndjson"
TOKEN_FILE="$(dirname "$0")/../.probe_token"

targets=(
  "https://cdn.trendvidstream.com/live/master.m3u8"
  "https://cdn.trendvidstream.com/vod/trending/master.m3u8"
)

# Append any spooled lines from previous failed runs, then this run's output.
{ [[ -f "$SPOOL" ]] && cat "$SPOOL"; ./bin/probe "${targets[@]}"; } > /tmp/probe_out.$$

if curl -fsS -m 15 \
     -H "Authorization: Bearer $(cat "$TOKEN_FILE")" \
     -H "Content-Type: application/x-ndjson" \
     --data-binary @/tmp/probe_out.$$ "$INGEST" >/dev/null; then
  : > "$SPOOL"          # success — clear the spool
else
  cp /tmp/probe_out.$$ "$SPOOL"   # failure — keep for next run
fi
rm -f /tmp/probe_out.$$

Each region's crontab staggers the run so we don't hammer the origin from eight places simultaneously — region offsets of a couple minutes are enough:

# US-East
*/5 * * * *  /home/site/monitor/bin/run_probe.sh us-east
# EU-West (offset 2 min)
2-59/5 * * * *  /home/site/monitor/bin/run_probe.sh eu-west

The Aggregator: PHP 8.4 and SQLite FTS5

The ingest endpoint and the status dashboard are plain PHP 8.4 backed by SQLite. SQLite is the right call here for the same reasons it's the right call for the rest of the site: no separate database process, the whole thing is one file I can scp for a backup, and it comfortably handles the write volume of eight regions posting a handful of checks every five minutes.

The schema has two tables. checks is the raw append-only log. Then an FTS5 virtual table over the human-readable detail field so that when I'm debugging at 2 a.m. I can full-text search incident history — "show me every stale cache event in ap-southeast last week" — without writing LIKE '%...%' scans across a growing table.

<?php
declare(strict_types=1);

final class HealthStore
{
    private \PDO $db;

    public function __construct(string $path)
    {
        $this->db = new \PDO('sqlite:' . $path);
        $this->db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->db->exec('PRAGMA journal_mode=WAL');
        $this->db->exec('PRAGMA busy_timeout=5000');
        $this->migrate();
    }

    private function migrate(): void
    {
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS checks (
                id         INTEGER PRIMARY KEY,
                region     TEXT NOT NULL,
                target     TEXT NOT NULL,
                check_type TEXT NOT NULL,
                ok         INTEGER NOT NULL,
                ttfb_ms    INTEGER NOT NULL,
                status     INTEGER NOT NULL,
                cert_days  INTEGER NOT NULL,
                detail     TEXT NOT NULL,
                checked_at INTEGER NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_checks_recent
                ON checks(target, region, checked_at DESC);

            CREATE VIRTUAL TABLE IF NOT EXISTS checks_fts
                USING fts5(detail, region UNINDEXED, content='checks', content_rowid='id');

            CREATE TRIGGER IF NOT EXISTS checks_ai AFTER INSERT ON checks BEGIN
                INSERT INTO checks_fts(rowid, detail, region)
                VALUES (new.id, new.detail, new.region);
            END;
        SQL);
    }

    public function ingest(array $rows): int
    {
        $stmt = $this->db->prepare(<<<SQL
            INSERT INTO checks
                (region, target, check_type, ok, ttfb_ms, status, cert_days, detail, checked_at)
            VALUES
                (:region, :target, :check_type, :ok, :ttfb_ms, :status, :cert_days, :detail, :checked_at)
        SQL);

        $this->db->beginTransaction();
        $n = 0;
        foreach ($rows as $r) {
            $stmt->execute([
                ':region'     => (string)($r['region'] ?? 'unknown'),
                ':target'     => (string)($r['target'] ?? ''),
                ':check_type' => (string)($r['check'] ?? 'manifest'),
                ':ok'         => !empty($r['ok']) ? 1 : 0,
                ':ttfb_ms'    => (int)($r['ttfb_ms'] ?? 0),
                ':status'     => (int)($r['status'] ?? 0),
                ':cert_days'  => (int)($r['cert_days'] ?? 0),
                ':detail'     => (string)($r['detail'] ?? ''),
                ':checked_at' => (int)($r['checked_at'] ?? time()),
            ]);
            $n++;
        }
        $this->db->commit();
        return $n;
    }
}

The ingest endpoint itself is thin: authenticate the bearer token in constant time, read the NDJSON body, decode line by line skipping malformed lines rather than rejecting the whole batch, and hand the rows to ingest().

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

$expected = trim((string)file_get_contents(__DIR__ . '/.ingest_token'));
$auth = $_SERVER['HTTP_AUTHORIZATION'] ?? '';
$given = str_starts_with($auth, 'Bearer ') ? substr($auth, 7) : '';

if (!hash_equals($expected, $given)) {
    http_response_code(401);
    exit('unauthorized');
}

$raw = file_get_contents('php://input') ?: '';
$rows = [];
foreach (explode("\n", $raw) as $line) {
    $line = trim($line);
    if ($line === '') continue;
    $decoded = json_decode($line, true);
    if (is_array($decoded)) $rows[] = $decoded;
}

$store = new HealthStore(__DIR__ . '/data/health.db');
$count = $store->ingest($rows);

header('Content-Type: application/json');
echo json_encode(['ingested' => $count]);

Aggregation: Turning Rows Into a Verdict

Raw checks are noise. The value of an aggregator is the roll-up. For each target, I compute a per-region latest state and a cross-region consensus. The two questions the dashboard answers are "which regions are unhealthy right now?" and "is any single region an outlier?" — the second is how we caught the stale-cache incident.

The query that drives the dashboard pulls the most recent check per (target, region) using a window function, which SQLite 3.25+ supports natively:

public function currentStatus(int $staleAfter = 900): array
{
    $sql = <<<SQL
        WITH ranked AS (
            SELECT *, ROW_NUMBER() OVER (
                       PARTITION BY target, region
                       ORDER BY checked_at DESC
                   ) AS rn
            FROM checks
            WHERE checked_at > :floor
        )
        SELECT target, region, ok, ttfb_ms, cert_days, detail, checked_at
        FROM ranked WHERE rn = 1
        ORDER BY target, region
    SQL;

    $stmt = $this->db->prepare($sql);
    $stmt->execute([':floor' => time() - 3600]);
    $latest = $stmt->fetchAll(\PDO::FETCH_ASSOC);

    $out = [];
    foreach ($latest as $row) {
        $t = $row['target'];
        $out[$t] ??= ['regions' => [], 'healthy' => 0, 'total' => 0, 'ttfbs' => []];
        $stale = (time() - (int)$row['checked_at']) > $staleAfter;
        $healthy = ((int)$row['ok'] === 1) && !$stale;

        $out[$t]['regions'][$row['region']] = [
            'healthy'   => $healthy,
            'ttfb_ms'   => (int)$row['ttfb_ms'],
            'cert_days' => (int)$row['cert_days'],
            'detail'    => $stale ? 'stale (no recent probe)' : $row['detail'],
        ];
        $out[$t]['total']++;
        if ($healthy) $out[$t]['healthy']++;
        if ($healthy) $out[$t]['ttfbs'][] = (int)$row['ttfb_ms'];
    }

    // Outlier detection: a healthy region whose TTFB is >2.5x the median.
    foreach ($out as $t => &$agg) {
        $ttfbs = $agg['ttfbs'];
        sort($ttfbs);
        $n = count($ttfbs);
        $median = $n ? ($ttfbs[intdiv($n, 2)]) : 0;
        foreach ($agg['regions'] as $region => &$r) {
            $r['slow'] = $median > 0 && $r['ttfb_ms'] > $median * 2.5;
        }
        $agg['median_ttfb'] = $median;
        unset($agg['ttfbs']);
    }
    return $out;
}

The roll-up gives me a compact verdict per target: 6/8 healthy, median TTFB 140 ms, with two regions flagged and one flagged as slow even though it's technically up. That "slow but up" flag is the single most useful output of the whole system, because it's a leading indicator. Regions almost never fail cold; they drift, TTFB creeps, then a segment fetch times out. Catching the drift buys you the window to fail over before viewers notice.

The staleness check is just as important as the OK flag. If a region's cron stops posting — the host is down, the FTP deploy corrupted the wrapper, the token rotated — the absence of data is itself an outage signal. Treating "no recent probe" as unhealthy rather than "last known good" is the difference between a monitor you trust and one that lies to you during exactly the incidents you built it for.

Alerting Without Alert Fatigue

One region blipping for one cycle is noise; two consecutive failures across a majority of regions is an incident. I gate alerts on both persistence and breadth: a target only pages when it has been unhealthy for two consecutive check cycles in at least half the regions, or when any region reports a cert horizon under 14 days. Everything else lands in the dashboard and the FTS5-searchable log, where I can review it on my own schedule.

The FTS5 table earns its keep during postmortems. After that Singapore incident I ran a single query — SELECT region, count(*) FROM checks_fts('truncated OR stale') GROUP BY region — and immediately saw the failures were concentrated in one PoP, which told me it was a cache-invalidation problem at one edge rather than an origin problem. That's a thirty-second answer that would have been a painful grep across log files in most setups.

What I'd Tell You to Steal

The architecture is intentionally unglamorous, and that's the point:

Probe semantics, not status codes. For video, parse the manifest and pull a real segment. HTTP 200 tells you almost nothing.
Run the same probe binary everywhere, keyed by an env var. One Go build, eight regions, zero per-region code.
Treat missing data as unhealthy. A silent region is an outage until proven otherwise.
Store raw, aggregate on read. SQLite with a window-function query is more than fast enough, and keeping the raw log means you can compute new roll-ups later without re-instrumenting.
Add full-text search to your incident history early. FTS5 costs one virtual table and a trigger, and it changes postmortems from archaeology into queries.
Watch drift, not just death. The "up but 2.5x slow" flag is your earliest warning.

We've been running this across all eight regions for a few months now on the same shared LiteSpeed hosts and FTP automation that deploy the rest of the platform. It has never needed a dedicated server, and it has caught degradations our previous external monitor rated as fully green. If you're delivering video across regions and your health check is a single curl -I from one datacenter, you don't have a health check — you have a coin flip. Build the aggregator; your viewers will notice the difference before your dashboard does.

Hardening a PHP Video Admin Panel With CSRF Double-Submit Cookies

ahmet gedik — Wed, 22 Jul 2026 11:00:01 +0000

A few months ago I watched a bot POST to /admin/videos/delete on a staging box and wipe a test playlist without ever authenticating a form. The request carried a valid session cookie — because the browser attaches cookies to any request to our origin — and my admin panel happily trusted it. That is CSRF in its purest form, and it is embarrassingly easy to trigger against a PHP video panel where every "approve", "delete", and "promote to homepage" action is a state-changing POST guarded only by a login session.

I run the backend for ViralVidVault, a European viral-video discovery service, and our admin panel is where editors approve trending clips, tune category weights, and flush caches. Those endpoints are exactly the kind of high-value, cookie-authenticated actions that CSRF targets. This article walks through how we protect them with the double-submit cookie pattern on PHP 8.4 — why we picked it over server-side synchronizer tokens, the signing detail almost every tutorial gets wrong, and the exact code running in production behind LiteSpeed and Cloudflare.

Why double-submit instead of session-stored tokens

The classic "synchronizer token" pattern stores a random token in the server-side session and compares it against a hidden form field. It works, but it has an operational cost that matters at our scale:

Every rendered form has to read and write session state, which means the session file (or Redis key) is locked for the duration of the request. On a busy admin panel with AJAX bulk actions, that serializes requests per user.
Our sessions live in SQLite with WAL mode. Writing a token per form-render adds write amplification we would rather avoid.
Behind Cloudflare and a LiteSpeed edge cache, anything that forces a per-request session write also tends to force Cache-Control: private, which is fine for admin but leaks into shared code paths if you are not careful.

The double-submit cookie pattern sidesteps server-side storage entirely. The idea:

Issue a random token in a cookie.
Echo the same token into every form as a hidden field (or an X-CSRF-Token header for AJAX).
On a state-changing request, compare the cookie value against the submitted value. If they match, accept; otherwise reject.

The security property comes from the same-origin policy: an attacker's page on evil.example can force the victim's browser to send our cookie, but JavaScript on evil.example cannot read our cookie to copy its value into the request body. So the attacker can satisfy the cookie half but never the body half.

The catch — and this is the part that turns a naive implementation into a real vulnerability — is that a plain double-submit cookie is bypassable if an attacker can set a cookie on your domain (via a compromised subdomain, a MITM on an unencrypted sibling, or a cookie-injection bug). The fix is to sign the token so the server can verify it issued the cookie, turning it into what OWASP calls the signed double-submit cookie. We will do exactly that.

The token: signed, not just random

Here is the token service we use. It generates a random value, binds it to the session ID, and HMAC-signs the pair with a server secret. Nothing is stored server-side.

<?php
declare(strict_types=1);

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

    public function __construct(private readonly string $secret)
    {
        if (strlen($this->secret) < 32) {
            throw new \InvalidArgumentException('CSRF secret must be >= 32 bytes');
        }
    }

    /**
     * Build a signed token bound to the current session.
     * Format: base64(random).base64(expiry).base64(hmac)
     */
    public function issue(string $sessionId): string
    {
        $random = random_bytes(32);
        $expiry = time() + self::TTL;
        $message = $random . '|' . $expiry . '|' . $sessionId;
        $sig = hash_hmac('sha256', $message, $this->secret, true);

        return sprintf(
            '%s.%s.%s',
            $this->b64($random),
            $this->b64((string) $expiry),
            $this->b64($sig)
        );
    }

    /**
     * Verify a submitted token against the session it claims to belong to.
     */
    public function verify(string $token, string $sessionId): bool
    {
        $parts = explode('.', $token);
        if (count($parts) !== 3) {
            return false;
        }

        [$randomB64, $expiryB64, $sigB64] = $parts;
        $random = $this->unb64($randomB64);
        $expiry = $this->unb64($expiryB64);
        $sig = $this->unb64($sigB64);

        if ($random === null || $expiry === null || $sig === null) {
            return false;
        }

        if (!ctype_digit($expiry) || (int) $expiry < time()) {
            return false; // expired or malformed
        }

        $message = $random . '|' . $expiry . '|' . $sessionId;
        $expected = hash_hmac('sha256', $message, $this->secret, true);

        // Constant-time comparison — never use == here.
        return hash_equals($expected, $sig);
    }

    public function cookieName(): string
    {
        return self::COOKIE_NAME;
    }

    private function b64(string $raw): string
    {
        return rtrim(strtr(base64_encode($raw), '+/', '-_'), '=');
    }

    private function unb64(string $encoded): ?string
    {
        $decoded = base64_decode(strtr($encoded, '-_', '+/'), true);
        return $decoded === false ? null : $decoded;
    }
}

Three details worth calling out, because they are where implementations go wrong:

hash_equals for the signature check. Comparing HMACs with == or === leaks timing information that can, in theory, be used to forge a signature byte by byte. hash_equals is constant-time. This is non-negotiable.
Binding to the session ID. By folding $sessionId into the signed message, a token issued for user A's session cannot be replayed in user B's session. This also neutralizes the classic double-submit weakness where an attacker who can plant any valid-looking cookie could pass the check — they would also need to forge an HMAC over the victim's session, which requires the secret.
Embedded expiry. Because we store nothing server-side, the token has to carry its own expiry, and that expiry is inside the signed message so it cannot be tampered with.

Setting the cookie correctly

The cookie attributes matter as much as the token. Here is how we emit it, and the flags are deliberate:

<?php
declare(strict_types=1);

final class CsrfCookie
{
    public function __construct(private readonly CsrfToken $csrf) {}

    public function ensure(string $sessionId): string
    {
        $name = $this->csrf->cookieName();

        // Reuse a still-valid cookie so LiteSpeed/Cloudflare don't see a
        // fresh Set-Cookie on every page render (which would bust the cache).
        $existing = $_COOKIE[$name] ?? '';
        if ($existing !== '' && $this->csrf->verify($existing, $sessionId)) {
            return $existing;
        }

        $token = $this->csrf->issue($sessionId);

        setcookie($name, $token, [
            'expires'  => time() + 7200,
            'path'     => '/',
            'secure'   => true,      // __Host- prefix requires it
            'httponly' => false,     // JS must read it for AJAX header
            'samesite' => 'Strict',  // defense in depth
        ]);

        return $token;
    }
}

Decisions:

__Host- prefix. A cookie named __Host-* is only accepted by the browser if it is Secure, has Path=/, and has no Domain attribute. That means a compromised or attacker-controlled subdomain cannot overwrite it — subdomains cannot set host-prefixed cookies for the parent. This closes the "attacker sets a cookie" hole that plagues plain double-submit.
HttpOnly is false on purpose. The whole point is that our own JavaScript reads this cookie to populate the X-CSRF-Token header on fetch/XHR calls. This is safe because the token's security does not depend on secrecy from same-origin JS — it depends on the same-origin policy preventing cross-origin reads. If an attacker has XSS on our origin, CSRF protection is the least of our problems.
SameSite=Strict. This alone blocks most CSRF, but I treat it as defense in depth, not the primary control. SameSite handling has historically varied across browsers and does not cover every navigation case (e.g. some GET-then-POST flows, older clients, or SameSite=None third-party embeds we may add later for oEmbed). Double-submit works regardless of SameSite support.
Reusing a valid cookie. Re-issuing on every render would emit a new Set-Cookie header each time, and at ViralVidVault that quietly defeats LiteSpeed's page cache and Cloudflare edge caching. We only mint a new token when the old one is missing or expired.

The verification middleware

Now the enforcement point. Every unsafe HTTP method (POST, PUT, PATCH, DELETE) into the admin area passes through this check before the controller runs. Safe methods (GET, HEAD, OPTIONS) are exempt because they should never change state.

<?php
declare(strict_types=1);

final class CsrfMiddleware
{
    private const SAFE_METHODS = ['GET', 'HEAD', 'OPTIONS'];

    public function __construct(private readonly CsrfToken $csrf) {}

    public function verify(): void
    {
        $method = strtoupper($_SERVER['REQUEST_METHOD'] ?? 'GET');
        if (in_array($method, self::SAFE_METHODS, true)) {
            return;
        }

        $sessionId = session_id();
        if ($sessionId === '') {
            $this->reject('No active session');
        }

        $cookieName = $this->csrf->cookieName();
        $cookieToken = $_COOKIE[$cookieName] ?? '';

        // The submitted token comes from a form field OR the AJAX header.
        $submitted = $_POST['_csrf']
            ?? ($_SERVER['HTTP_X_CSRF_TOKEN'] ?? '');

        if ($cookieToken === '' || $submitted === '') {
            $this->reject('Missing CSRF token');
        }

        // Double-submit: cookie value MUST equal submitted value...
        if (!hash_equals($cookieToken, $submitted)) {
            $this->reject('CSRF token mismatch');
        }

        // ...AND the token must be a valid signature for THIS session.
        if (!$this->csrf->verify($submitted, $sessionId)) {
            $this->reject('CSRF token invalid or expired');
        }
    }

    private function reject(string $reason): never
    {
        http_response_code(403);
        header('Content-Type: application/json');
        error_log(sprintf('[csrf] rejected: %s ip=%s uri=%s',
            $reason,
            $_SERVER['REMOTE_ADDR'] ?? '-',
            $_SERVER['REQUEST_URI'] ?? '-'
        ));
        echo json_encode(['error' => 'csrf_failed']);
        exit;
    }
}

Notice there are two checks, and both are required:

hash_equals($cookieToken, $submitted) — the literal double-submit comparison. Cookie must equal body/header.
$this->csrf->verify($submitted, $sessionId) — the signature check that proves we issued this token for this session and it has not expired.

A lot of tutorials do only step 1. That is the version that breaks the moment an attacker can plant a cookie on your domain. Doing both means an attacker needs to (a) know a value, (b) place it in the cookie, and (c) place the identical value in the body — while also having a valid HMAC over the victim's session. Without the server secret, that last requirement is infeasible.

Wiring it into the panel

The front controller composes these three pieces. The secret comes from our environment config, not from source — at ViralVidVault it lives alongside the other keys we load per site.

<?php
declare(strict_types=1);

session_start([
    'cookie_secure'   => true,
    'cookie_httponly' => true,
    'cookie_samesite' => 'Strict',
]);

$secret = getenv('CSRF_SECRET') ?: throw new RuntimeException('CSRF_SECRET unset');

$csrf   = new CsrfToken($secret);
$cookie = new CsrfCookie($csrf);
$guard  = new CsrfMiddleware($csrf);

// 1. Enforce on every unsafe admin request.
if (str_starts_with($_SERVER['REQUEST_URI'] ?? '', '/admin')) {
    $guard->verify();
}

// 2. Make a token available to templates for THIS render.
$token = $cookie->ensure(session_id());

// In your layout, expose it to forms and JS:
//   <input type="hidden" name="_csrf" value="<?= htmlspecialchars($token) ?>">
//   <meta name="csrf-token" content="<?= htmlspecialchars($token) ?>">

And on the client, every mutating fetch reads the cookie (or the meta tag) and sends it back:

function csrfToken() {
  const meta = document.querySelector('meta[name="csrf-token"]');
  return meta ? meta.content : '';
}

async function adminAction(url, payload) {
  const res = await fetch(url, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-CSRF-Token': csrfToken(),
    },
    credentials: 'same-origin',
    body: JSON.stringify(payload),
  });
  if (res.status === 403) {
    // token expired mid-session — reload to mint a fresh one
    window.location.reload();
    return;
  }
  return res.json();
}

Edge cases that bit us in production

Getting the happy path working took an afternoon. The rough edges took longer.

Cache stripping the header. Cloudflare and LiteSpeed will happily cache an admin page if you let them, and a cached page carries a stale token that no longer matches a rotated cookie. We send Cache-Control: no-store, private on everything under /admin and only cache truly public routes. The token-reuse logic in CsrfCookie::ensure() is what keeps public pages cacheable — no Set-Cookie churn.
Token expiry during a long editing session. An editor who leaves a form open for three hours will fail the check on submit because the embedded expiry lapsed. Rather than silently losing their work, the client-side 403 handler reloads to mint a fresh token, and for long forms we refresh the token via a lightweight GET /admin/csrf/refresh on an interval.
Multiple tabs. Because the token is derived from the session and reused while valid, two tabs share the same cookie and both keep working. If we rotated on every render, one tab would invalidate the other's hidden field. Reuse fixes this for free.
Login and logout. The session ID changes on session_regenerate_id() (which you should call on privilege change). Since tokens are bound to the session ID, old tokens stop verifying automatically after login — exactly what you want. Just make sure you call ensure() after regenerating.
File uploads. multipart/form-data posts still carry $_POST['_csrf'] as long as the hidden field is inside the form, so no special handling is needed — but do confirm your upload size limit doesn't truncate the body before PHP parses the field.

What this does and does not protect

Be honest about the threat model. Signed double-submit cookies stop cross-site request forgery: a malicious third-party page cannot forge a state-changing request against our admin panel, because it cannot read our token to echo it back, and it cannot forge our HMAC.

They do not protect against:

XSS. If an attacker runs script on our origin, they can read the token and craft valid requests. CSRF defense assumes your origin is not already compromised. Content-Security-Policy and output escaping are separate, mandatory controls.
Stolen session cookies. If the session itself leaks, the attacker is authenticated. Use Secure, HttpOnly, SameSite on the session cookie and rotate on privilege change.
Server-side request forgery or broken access control. Different vulnerability classes entirely. A CSRF token on an endpoint that fails to check whether the editor is allowed to delete that video is still broken.

Conclusion

For a cookie-authenticated PHP admin panel, the signed double-submit cookie is the pragmatic sweet spot: no server-side token storage, no per-render session writes, cache-friendly, and — once you add the HMAC and the __Host- prefix — as strong as a synchronizer token against every practical CSRF vector. The three rules that separate a real defense from a checkbox are: sign the token and bind it to the session, compare with hash_equals, and reuse a valid cookie so you don't fight your own cache. Get those right and the bot that wiped my staging playlist simply gets a 403.

The code above is close to what protects the editor tooling at ViralVidVault today, running on PHP 8.4 behind LiteSpeed and Cloudflare. Adapt the cookie name and TTL to your setup, keep your CSRF_SECRET out of source control, and log your rejections — the rejection log is the first place you will notice both attacks and your own misconfigured caches.

Building a Video URL Canonicalization Pipeline for a Multi-Region Aggregator

ahmet gedik — Wed, 22 Jul 2026 03:00:02 +0000

Last quarter our SQLite videos table had 41,000 rows but only about 28,000 distinct videos. The same YouTube clip was landing three, four, sometimes six times — once as youtu.be/dQw4w9WgXcQ, once as www.youtube.com/watch?v=dQw4w9WgXcQ&feature=share, once wrapped in a Google AMP redirect from a Japanese news aggregator, and once with a si= share token appended by the mobile app. Each variant hashed differently, so our dedup key never matched, and worse, our SQLite FTS5 index was splitting the same video's title across multiple documents. On a site like TopVideoHub that ingests trending feeds from a dozen Asia-Pacific regions in parallel, that duplication compounds fast: two crawlers hitting the JP and TW trending endpoints will both surface the same globally-viral clip within the same minute.

The fix was not a regex. It was a proper canonicalization pipeline — a deterministic function that takes any URL variant and returns exactly one canonical form plus a stable content key. This post walks through how we built it in PHP 8.4, how we backfilled 40k existing rows with Python, and how we run the hot path as a small Go worker. Everything here is runnable.

What "canonical" actually means for a video aggregator

URL canonicalization in the generic web sense (RFC 3986 normalization, lowercasing the host, sorting query params) is necessary but nowhere near sufficient. Two URLs can be byte-different and RFC-normalized-different while pointing at the identical video. What we actually want is a content identity: a platform-scoped video ID that survives every cosmetic variation.

So we split the problem into two outputs:

Canonical URL — the single URL we store and link to. Stable, minimal, no tracking junk.
Content key — platform:video_id, e.g. youtube:dQw4w9WgXcQ. This is what we dedup on and what seeds the FTS document. Never the URL.

The distinction matters. If you dedup on the canonical URL string, you are one platform redesign away from re-duplicating your whole catalog. If you dedup on the content key, the URL can change and your identity holds.

Here is the pipeline, top to bottom:

Resolve redirects and known shorteners to a real destination.
Normalize the host (lowercase, strip www., punycode-decode IDNs).
Route to a platform-specific extractor that pulls the video ID.
Rebuild a minimal canonical URL from the platform + ID.
Emit content_key and a hash for the unique index.

Step 1 and 2: host normalization and shortener resolution

The messy part is that "resolve redirects" is an I/O operation you do not want on your hot ingestion path for every URL. We keep a static table of shortener hosts we resolve, and everything else we trust as a direct link. youtu.be, t.co, bit.ly, and the regional AMP wrappers (*.cdn.ampproject.org) go through a resolver with a short timeout and a cache. Everything else skips the network entirely.

Host normalization has one Asia-Pacific-specific wrinkle: internationalized domain names. A URL can arrive with a Unicode host (日本.example) or its punycode form (xn--wgv71a.example). Pick one — we normalize to punycode ASCII because that is what actually goes on the wire and what our storage compares byte-for-byte.

<?php
declare(strict_types=1);

final class HostNormalizer
{
    private const SHORTENERS = [
        'youtu.be', 't.co', 'bit.ly', 'ow.ly', 'buff.ly',
    ];

    public function normalizeHost(string $host): string
    {
        $host = strtolower(trim($host));
        // Punycode-encode any Unicode labels (IDN). PHP 8.4 + intl.
        if (preg_match('/[^\x00-\x7F]/', $host)) {
            $encoded = idn_to_ascii($host, IDNA_DEFAULT, INTL_IDNA_VARIANT_UTS46);
            if ($encoded !== false) {
                $host = $encoded;
            }
        }
        return preg_replace('/^www\./', '', $host) ?? $host;
    }

    public function isShortener(string $host): bool
    {
        return in_array($this->normalizeHost($host), self::SHORTENERS, true);
    }
}

We deliberately do not try to be clever about which subdomain of YouTube is "real." m.youtube.com, music.youtube.com, and www.youtube.com all collapse to youtube.com after normalization, and the platform extractor in the next step handles the semantic differences (music vs. video) by looking at the path and params, not the host.

Step 3 and 4: the platform extractor and canonical rebuild

This is the core. Each platform gets an extractor that knows exactly how that platform encodes a video ID. The golden rule: never keep the input query string. Extract the ID, throw the entire input URL away, and rebuild a clean canonical URL from scratch. That single discipline eliminates an entire class of tracking-token and ordering bugs.

<?php
declare(strict_types=1);

final class CanonicalUrl
{
    public function __construct(
        public readonly string $platform,
        public readonly string $videoId,
        public readonly string $url,
    ) {}

    public function contentKey(): string
    {
        return $this->platform . ':' . $this->videoId;
    }

    public function hash(): string
    {
        // Short, index-friendly, collision-safe enough for dedup.
        return substr(hash('sha256', $this->contentKey()), 0, 24);
    }
}

final class VideoCanonicalizer
{
    public function __construct(private HostNormalizer $hosts) {}

    public function canonicalize(string $raw): ?CanonicalUrl
    {
        $raw = trim($raw);
        $parts = parse_url($raw);
        if ($parts === false || empty($parts['host'])) {
            return null;
        }

        $host = $this->hosts->normalizeHost($parts['host']);
        $path = $parts['path'] ?? '/';
        parse_str($parts['query'] ?? '', $query);

        return match (true) {
            $host === 'youtube.com'  => $this->youtube($path, $query),
            $host === 'youtu.be'     => $this->youtubeShort($path, $query),
            $host === 'vimeo.com'    => $this->vimeo($path),
            $host === 'bilibili.com' => $this->bilibili($path),
            $host === 'nicovideo.jp' => $this->niconico($path),
            default => null,
        };
    }

    private function youtube(string $path, array $q): ?CanonicalUrl
    {
        // /watch?v=ID, /shorts/ID, /embed/ID, /live/ID
        $id = $q['v'] ?? null;
        if ($id === null && preg_match('#^/(?:shorts|embed|live)/([\w-]{11})#', $path, $m)) {
            $id = $m[1];
        }
        return $this->build('youtube', $id, fn($v) => "https://www.youtube.com/watch?v={$v}");
    }

    private function youtubeShort(string $path, array $q): ?CanonicalUrl
    {
        $id = ltrim($path, '/');
        return $this->build('youtube', $id, fn($v) => "https://www.youtube.com/watch?v={$v}");
    }

    private function vimeo(string $path): ?CanonicalUrl
    {
        $id = preg_match('#/(\d+)#', $path, $m) ? $m[1] : null;
        return $this->build('vimeo', $id, fn($v) => "https://vimeo.com/{$v}");
    }

    private function bilibili(string $path): ?CanonicalUrl
    {
        // Bilibili uses BV IDs: /video/BV1xx411c7mD
        $id = preg_match('#/video/(BV[\w]+)#', $path, $m) ? $m[1] : null;
        return $this->build('bilibili', $id, fn($v) => "https://www.bilibili.com/video/{$v}");
    }

    private function niconico(string $path): ?CanonicalUrl
    {
        // Niconico: /watch/sm9
        $id = preg_match('#/watch/((?:sm|nm|so)\d+)#', $path, $m) ? $m[1] : null;
        return $this->build('niconico', $id, fn($v) => "https://www.nicovideo.jp/watch/{$v}");
    }

    private function build(string $platform, ?string $id, callable $urlFn): ?CanonicalUrl
    {
        if ($id === null || $id === '') {
            return null;
        }
        return new CanonicalUrl($platform, $id, $urlFn($id));
    }
}

A few decisions worth calling out:

The ID regexes are strict. YouTube IDs are exactly 11 characters of [\w-]. If we can't extract a well-formed ID, we return null and the ingestion job logs the URL for manual review rather than storing garbage. A permissive extractor that "mostly works" is worse than a strict one that fails loudly, because a bad ID silently poisons your dedup key.
We include the full Bilibili and Niconico extractors because Asia-Pacific feeds are where our generic YouTube-only logic used to fall over. Bilibili's BV IDs and Niconico's sm/nm/so prefixes are load-bearing — strip the prefix and you get ID collisions across content types.
The canonical URL is rebuilt from a template, never patched from the input. feature=share, si=, t=42s, utm_* — all gone, because we never carried them forward.

Step 5: storing the canonical form and enforcing dedup in SQLite

The content key earns its keep at the storage layer. We put a UNIQUE constraint on the hash and use SQLite's INSERT ... ON CONFLICT to make ingestion idempotent. Two crawlers racing on the same viral clip? The second insert becomes a no-op update of last_seen_at, not a duplicate row.

CREATE TABLE videos (
    id            INTEGER PRIMARY KEY,
    content_key   TEXT NOT NULL,
    content_hash  TEXT NOT NULL,
    platform      TEXT NOT NULL,
    video_id      TEXT NOT NULL,
    canonical_url TEXT NOT NULL,
    title         TEXT NOT NULL,
    region        TEXT NOT NULL,
    first_seen_at INTEGER NOT NULL,
    last_seen_at  INTEGER NOT NULL,
    UNIQUE(content_hash)
);

-- FTS5 with a CJK-aware tokenizer, keyed by content_hash so each
-- video maps to exactly one FTS document.
CREATE VIRTUAL TABLE videos_fts USING fts5(
    content_hash UNINDEXED,
    title,
    tokenize = 'unicode61 remove_diacritics 2'
);

The insert path in PHP is small once canonicalization has done its job:

<?php
function ingest(PDO $db, VideoCanonicalizer $c, string $rawUrl, string $title, string $region): void
{
    $canon = $c->canonicalize($rawUrl);
    if ($canon === null) {
        error_log("uncanonicalizable url: {$rawUrl}");
        return;
    }

    $now  = time();
    $hash = $canon->hash();

    $stmt = $db->prepare(<<<SQL
        INSERT INTO videos
            (content_key, content_hash, platform, video_id,
             canonical_url, title, region, first_seen_at, last_seen_at)
        VALUES (:key, :hash, :platform, :vid, :url, :title, :region, :now, :now)
        ON CONFLICT(content_hash) DO UPDATE SET
            last_seen_at = :now
    SQL);

    $stmt->execute([
        ':key' => $canon->contentKey(), ':hash' => $hash,
        ':platform' => $canon->platform, ':vid' => $canon->videoId,
        ':url' => $canon->url, ':title' => $title,
        ':region' => $region, ':now' => $now,
    ]);

    // Only touch FTS on a genuine first insert.
    if ($db->lastInsertId() !== '0') {
        $fts = $db->prepare(
            'INSERT INTO videos_fts (content_hash, title) VALUES (:hash, :title)'
        );
        $fts->execute([':hash' => $hash, ':title' => $title]);
    }
}

Because content_hash is derived purely from platform:video_id, the same video from the JP feed and the TW feed produces the same hash and collapses into one row. We lose the per-region attribution in this simple schema, but for our use case a video is a video; if you need region provenance, add a video_regions junction table keyed on content_hash and insert into it unconditionally.

Backfilling 40k existing rows with Python

Deploying the canonicalizer only fixes new inserts. The 13,000 phantom duplicates already in the table needed a one-time reconciliation. We wrote this as a Python job because it runs offline against a copy of the DB, groups by recomputed content key, keeps the oldest row as canonical, and rewrites foreign references before deleting the rest.

import sqlite3
import hashlib
import re
from urllib.parse import urlparse, parse_qs

def content_key(url: str) -> str | None:
    p = urlparse(url.strip())
    host = re.sub(r'^www\.', '', p.netloc.lower())
    q = parse_qs(p.query)
    if host in ('youtube.com', 'm.youtube.com'):
        vid = q.get('v', [None])[0]
        if not vid:
            m = re.match(r'^/(?:shorts|embed|live)/([\w-]{11})', p.path)
            vid = m.group(1) if m else None
        return f'youtube:{vid}' if vid else None
    if host == 'youtu.be':
        vid = p.path.lstrip('/')
        return f'youtube:{vid}' if vid else None
    if host == 'bilibili.com':
        m = re.search(r'/video/(BV[\w]+)', p.path)
        return f'bilibili:{m.group(1)}' if m else None
    return None

def content_hash(key: str) -> str:
    return hashlib.sha256(key.encode()).hexdigest()[:24]

def backfill(db_path: str) -> None:
    db = sqlite3.connect(db_path)
    db.row_factory = sqlite3.Row
    rows = db.execute(
        'SELECT id, canonical_url, first_seen_at FROM videos ORDER BY first_seen_at'
    ).fetchall()

    seen: dict[str, int] = {}   # content_hash -> surviving row id
    dupes = 0

    for r in rows:
        key = content_key(r['canonical_url'])
        if key is None:
            print(f"unmappable: {r['id']} {r['canonical_url']}")
            continue
        h = content_hash(key)
        if h in seen:
            # Older row already survives; delete this duplicate.
            db.execute('DELETE FROM videos WHERE id = ?', (r['id'],))
            db.execute('DELETE FROM videos_fts WHERE content_hash = ?', (h,))
            dupes += 1
        else:
            seen[h] = r['id']
            db.execute(
                'UPDATE videos SET content_hash = ?, content_key = ? WHERE id = ?',
                (h, key, r['id']),
            )

    db.commit()
    print(f'kept {len(seen)} unique, removed {dupes} duplicates')

if __name__ == '__main__':
    backfill('videos_copy.db')

Critically, we ran this against videos_copy.db, diffed the survivor count against expectations, and only then swapped it in. A dedup migration that deletes rows is the kind of thing you run once against a copy and verify by hand before it ever touches production. Ours removed 12,847 duplicates and the FTS index shrank by roughly a third, which immediately improved query relevance because term frequencies were no longer inflated by repeated documents.

The hot path as a Go worker

Canonicalization is CPU-bound string work, and during a trending burst we sometimes queue thousands of URLs at once. We front the ingestion queue with a small Go worker that canonicalizes in parallel and hands clean content_key + canonical_url tuples to the PHP inserter over a local socket. Go's goroutines make the fan-out trivial, and because canonicalization is pure (no I/O for direct links), it parallelizes perfectly.

package main

import (
    "net/url"
    "regexp"
    "strings"
)

var ytID = regexp.MustCompile(`^/(?:shorts|embed|live)/([\w-]{11})`)

type Canonical struct {
    Platform string
    VideoID  string
    URL      string
}

func Canonicalize(raw string) (Canonical, bool) {
    u, err := url.Parse(strings.TrimSpace(raw))
    if err != nil || u.Host == "" {
        return Canonical{}, false
    }
    host := strings.TrimPrefix(strings.ToLower(u.Host), "www.")
    q := u.Query()

    var id string
    switch host {
    case "youtube.com", "m.youtube.com":
        if id = q.Get("v"); id == "" {
            if m := ytID.FindStringSubmatch(u.Path); m != nil {
                id = m[1]
            }
        }
        if id != "" {
            return Canonical{"youtube", id, "https://www.youtube.com/watch?v=" + id}, true
        }
    case "youtu.be":
        if id = strings.TrimPrefix(u.Path, "/"); id != "" {
            return Canonical{"youtube", id, "https://www.youtube.com/watch?v=" + id}, true
        }
    }
    return Canonical{}, false
}

// Fan out canonicalization across a worker pool.
func CanonicalizeBatch(urls []string, workers int) []Canonical {
    in := make(chan string, len(urls))
    out := make(chan Canonical, len(urls))
    for _, u := range urls {
        in <- u
    }
    close(in)

    done := make(chan struct{})
    for i := 0; i < workers; i++ {
        go func() {
            for raw := range in {
                if c, ok := Canonicalize(raw); ok {
                    out <- c
                }
            }
            done <- struct{}{}
        }()
    }
    for i := 0; i < workers; i++ {
        <-done
    }
    close(out)

    result := make([]Canonical, 0, len(urls))
    for c := range out {
        result = append(result, c)
    }
    return result
}

The Go and PHP extractors share the same rules, and that is a liability: two implementations of the same logic drift. We keep them honest with a shared fixtures file — a JSON list of input -> expected content_key pairs that both the PHP PHPUnit suite and the Go test suite load and assert against. Any new URL variant we discover in the wild becomes a fixture first, then a code change in both places.

Edge cases that will bite you

Playlists and timestamps. youtube.com/watch?v=ID&list=PL...&t=90s is still the same video. Our extractor reads only v, so playlists and timestamps evaporate — which is correct for a catalog, though if you build a "resume playback" feature you will want to preserve t in a separate column, never in the canonical URL.
Live vs. VOD. A /live/ID that later becomes a normal VOD keeps the same ID on YouTube, so it canonicalizes stably. Other platforms mint a new ID; test this per platform.
Case sensitivity of IDs. YouTube IDs are case-sensitive (dQw4 ≠ dqw4). Do not lowercase the video ID during host normalization — only the host is safe to lowercase. This bug cost us an afternoon.
Punycode round-trips. Comparing a Unicode host against a stored punycode host will silently never match. Normalize both to the same representation before comparing, always.
Empty extraction is a signal, not an error to swallow. Every null return gets logged. Those logs are how we discover the next platform quirk before it duplicates 500 rows.

Conclusion

The whole pipeline is maybe 200 lines of code, and it replaced a pile of ad-hoc regexes that had accreted over a year. The design choices that made it durable were small but non-negotiable: separate the canonical URL from the content identity, rebuild URLs from a template instead of patching the input, dedup on platform:video_id rather than any URL string, fail loudly on unrecognized inputs, and enforce uniqueness at the database with ON CONFLICT so ingestion is idempotent no matter how many crawlers race. Backfilling with a throwaway Python job against a DB copy let us clean the historical mess without risking production, and moving the hot path to a Go worker pool kept trending bursts from backing up the queue. If your aggregator is fighting duplicate rows and a bloated full-text index, start by drawing the line between "the URL" and "the thing the URL points at" — everything else follows from that.

Building CSRF Double-Submit Cookie Protection for a PHP Video Admin Panel

ahmet gedik — Tue, 21 Jul 2026 16:00:00 +0000

Last month I watched a bulk-delete endpoint on our video admin panel get hit by a forged POST during a routine log review. Nothing was lost — the request failed a token check — but the log line was enough to make me audit every state-changing route we ship. The admin panel behind DailyWatch controls video ingestion, category curation, and cache invalidation across a LiteSpeed + Cloudflare edge, and a single successful cross-site request could flush the wrong cache or unpublish a channel. This post is the exact CSRF defense we run in production: the double-submit cookie pattern, implemented in PHP 8.4, hardened against the mistakes that make most double-submit implementations useless.

Why Double-Submit and Not Synchronizer Tokens

The classic CSRF defense is the synchronizer token: generate a random token, store it in the server-side session, embed it in every form, and compare on submit. It works, but it has a cost that matters for a video platform serving cached pages at the edge.

Our admin panel runs on SQLite with FTS5 for search and sits behind a page cache. Session-bound tokens mean every admin page render must touch session storage, and every token is tied to one server's session state. That's fine for a single box, but it fights against the stateless request model we want everywhere else on the site.

The double-submit cookie pattern removes the server-side storage requirement:

The server sends a random token in a cookie.
The same token is embedded in the form (or a request header).
On submit, the server checks that the cookie value and the submitted value match.

The security assumption is simple: an attacker on evil.com can trigger a request to your domain and the browser will attach your cookies automatically — that's the whole basis of CSRF — but the attacker cannot read your cookie value because of the Same-Origin Policy, and therefore cannot place the matching value in the request body. If cookie and body match, the request came from a page that could read the cookie, which means it came from your own origin.

That assumption has holes. Most tutorials stop before covering them, which is why so many double-submit implementations are quietly broken. We'll get to the holes.

Generating and Setting the Token

First, the token itself. Never use rand(), mt_rand(), uniqid(), or anything derived from timestamps. CSRF tokens must be cryptographically random and long enough that guessing is infeasible. In PHP that means random_bytes().

Here is the token manager we use. It is a small class with no session dependency — state lives entirely in the cookie.

<?php
declare(strict_types=1);

final class CsrfToken
{
    private const COOKIE_NAME = '__Host-csrf';
    private const FIELD_NAME  = 'csrf_token';
    private const TOKEN_BYTES = 32;

    public function issue(): string
    {
        $existing = $_COOKIE[self::COOKIE_NAME] ?? '';
        if ($this->isWellFormed($existing)) {
            // Reuse the token for the lifetime of the browser session
            // so multiple tabs share one value.
            return $existing;
        }

        $token = bin2hex(random_bytes(self::TOKEN_BYTES));
        $this->send($token);
        return $token;
    }

    private function send(string $token): void
    {
        setcookie(self::COOKIE_NAME, $token, [
            'expires'  => 0,        // session cookie
            'path'     => '/',
            'secure'   => true,     // required by __Host- prefix
            'httponly' => false,    // JS must read it for the header variant
            'samesite' => 'Lax',
        ]);
    }

    private function isWellFormed(string $value): bool
    {
        // 32 bytes hex-encoded = 64 chars, hex only.
        return (bool) preg_match('/^[a-f0-9]{64}$/', $value);
    }

    public function fieldName(): string
    {
        return self::FIELD_NAME;
    }

    public function cookieName(): string
    {
        return self::COOKIE_NAME;
    }
}

A few decisions worth calling out:

The __Host- cookie prefix is doing real work. A cookie named __Host-csrf is only accepted by the browser if it is set with Secure, has Path=/, and has no Domain attribute. That last part is the important one. Without it, a subdomain like blog.dailywatch.video — or worse, an attacker who manages to get code onto any subdomain — could set a Domain=dailywatch.video cookie that overwrites yours. The __Host- prefix makes subdomain cookie-injection impossible. This closes one of the biggest double-submit holes.
httponly => false looks wrong for a security cookie, and it is a real tradeoff. We keep it readable because our admin panel is a single-page-ish interface that sends the token in a request header via fetch. If your panel is pure server-rendered forms, set httponly => true and embed the token in a hidden field instead — the cookie never needs to be JS-readable in that case. Choose based on your panel, and I'll show both submit paths below.
Session-lifetime cookie (expires => 0) means the token rotates when the browser closes. For an admin panel that's a reasonable balance; a full rotate-per-request scheme breaks multi-tab workflows and back-button navigation without adding meaningful security to double-submit.

Embedding the Token in Forms

For server-rendered forms, the token goes in a hidden input on every state-changing form. Here's the render helper and a sample form from our category management page.

<?php
// In the admin bootstrap, once per request:
$csrf  = new CsrfToken();
$token = $csrf->issue();

function csrf_field(CsrfToken $csrf, string $token): string
{
    return sprintf(
        '<input type="hidden" name="%s" value="%s">',
        htmlspecialchars($csrf->fieldName(), ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($token, ENT_QUOTES, 'UTF-8')
    );
}
?>

<form method="post" action="/ibt/category/delete">
    <?= csrf_field($csrf, $token) ?>
    <input type="hidden" name="category_id" value="42">
    <button type="submit">Delete category</button>
</form>

Every POST, PUT, PATCH, and DELETE form in the panel gets csrf_field(). The rule I enforce in review is blunt: if a form mutates state and doesn't call csrf_field(), it doesn't merge. GET requests never mutate state, so they never carry a token — that's not laziness, it's correctness. A CSRF-protected GET is a sign your GET is doing something it shouldn't.

Validating on the Server

Now the check. This runs before any state-changing route executes. The comparison must be constant-time to avoid leaking token bytes through timing, and it must reject empty or malformed values instead of treating two empty strings as a match — the second most common double-submit bug after the subdomain hole.

<?php
declare(strict_types=1);

final class CsrfValidator
{
    private const HEADER_NAME = 'HTTP_X_CSRF_TOKEN';

    public function __construct(private CsrfToken $csrf) {}

    public function assertValid(): void
    {
        if (!$this->isMutating($_SERVER['REQUEST_METHOD'] ?? 'GET')) {
            return; // GET/HEAD/OPTIONS are never checked
        }

        $cookie = $_COOKIE[$this->csrf->cookieName()] ?? '';
        $sent   = $this->submittedToken();

        // Reject anything that isn't a well-formed 64-char hex token.
        // This is what stops '' === '' from ever passing.
        if (!preg_match('/^[a-f0-9]{64}$/', $cookie)
            || !preg_match('/^[a-f0-9]{64}$/', $sent)) {
            $this->reject('malformed or missing token');
        }

        if (!hash_equals($cookie, $sent)) {
            $this->reject('token mismatch');
        }
    }

    private function submittedToken(): string
    {
        // Header takes precedence (fetch/XHR), then form field.
        $header = $_SERVER[self::HEADER_NAME] ?? '';
        if ($header !== '') {
            return $header;
        }
        return (string) ($_POST[$this->csrf->fieldName()] ?? '');
    }

    private function isMutating(string $method): bool
    {
        return in_array(
            strtoupper($method),
            ['POST', 'PUT', 'PATCH', 'DELETE'],
            true
        );
    }

    private function reject(string $reason): never
    {
        error_log('CSRF rejected: ' . $reason . ' ip=' . ($_SERVER['REMOTE_ADDR'] ?? '?'));
        http_response_code(403);
        header('Content-Type: text/plain; charset=utf-8');
        echo 'Forbidden: CSRF validation failed.';
        exit;
    }
}

The three defenses stacked here are what separate a real implementation from a demo:

hash_equals() does a constant-time comparison. Using === or == here leaks how many leading bytes matched via response timing, which over enough requests lets an attacker reconstruct the token. Never compare secrets with ==.
The regex gate before the compare rejects empty strings, null-coerced values, and anything that isn't a proper token. Without it, a request with no cookie and no field would compare '' === '' and pass. That single missing check has appeared in production code at companies you've heard of.
Method filtering means we only spend the check on mutating requests, and it documents the invariant that GETs are side-effect-free.

Wiring it into the router is one line at the top of the admin dispatch:

<?php
$csrf = new CsrfToken();
$csrf->issue(); // ensures the cookie exists on every admin page load

(new CsrfValidator($csrf))->assertValid();

// ... only reached if the request is a safe method or passes CSRF
$router->dispatch($_SERVER['REQUEST_METHOD'], $path);

The Cloudflare and LiteSpeed Gotcha

This is the part that bites people running behind a CDN, and it's specific to our stack. The Set-Cookie header for the CSRF token must never be cached at the edge, or every admin behind Cloudflare gets served the same token — which turns your per-user secret into a shared, publicly-known value and defeats the entire scheme.

Two things protect us:

The admin panel path (/ibt on our sites) is excluded from LiteSpeed page cache and marked no-store, private at the origin. Cloudflare's default behavior already bypasses caching when it sees Set-Cookie, but I never rely on defaults for a security control — the admin routes send explicit cache-control headers.
We set the cookie with Secure, which combined with Cloudflare Flexible SSL means the browser only sends it over HTTPS. One subtlety with Flexible mode: the origin sees HTTP from Cloudflare, so PHP's $_SERVER['HTTPS'] may be unset. The Secure flag on setcookie() is set unconditionally in our code rather than derived from $_SERVER['HTTPS'], because the browser-facing connection is always HTTPS even when the origin leg isn't.

If you cache your admin pages, verify with curl -I that no admin response carries a cacheable Set-Cookie. I've seen a misconfigured cache rule hand the same CSRF token to an entire office.

Handling the Header Variant for fetch()

Our category and channel editors use fetch() for inline saves without full page reloads. Those requests read the cookie in JavaScript and echo it back in an X-CSRF-Token header. Because the cookie is same-origin, only our own scripts can read it — cross-origin JS cannot.

function csrfToken() {
  const match = document.cookie.match(/(?:^|;\s*)__Host-csrf=([a-f0-9]{64})/);
  return match ? match[1] : '';
}

async function saveCategory(id, payload) {
  const res = await fetch('/ibt/category/update', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-CSRF-Token': csrfToken(),
    },
    credentials: 'same-origin',
    body: JSON.stringify({ id, ...payload }),
  });

  if (res.status === 403) {
    // Token rotated (browser reopened) — reload to get a fresh cookie.
    window.location.reload();
    return;
  }
  return res.json();
}

The header path has a genuine security bonus over the form-field path: custom headers like X-CSRF-Token cannot be set on a cross-origin request without triggering a CORS preflight, and our origin doesn't approve preflights from other domains. So even before the token comparison runs, the mere presence of a readable custom header on a same-origin request is itself a CSRF signal. That's why we prefer the header variant where the UI allows it.

Defending the Holes People Skip

Double-submit gets dismissed by some engineers as weaker than synchronizer tokens. It is weaker only if you skip the hardening. Here's the full list of what actually makes it safe, and what we do for each:

Subdomain cookie injection. An attacker who controls or injects into any subdomain can normally set a domain-wide cookie and overwrite your CSRF cookie with a known value, then submit that same known value. Fixed by the __Host- prefix, which forbids the Domain attribute entirely.
Empty-string comparison. Covered above — the regex gate rejects missing tokens before the compare.
Timing attacks. Covered by hash_equals().
Method confusion. Some frameworks let a POST be tunneled as a GET with a _method override, or accept the token in a query string. We only read the token from the header or $_POST body, never from $_GET, and we only validate real mutating methods.
SameSite as defense-in-depth, not the primary control. We set SameSite=Lax, which blocks the cookie from being sent on most cross-site POSTs in modern browsers. That's a strong second layer, but SameSite handling varies across browser versions and doesn't cover every navigation type, so it backstops the token check rather than replacing it.
Login CSRF. The login form itself gets a token too. An attacker forcing a victim to log into an attacker-controlled account is a real class of CSRF, and an unprotected login form is a common blind spot.

One thing double-submit does not protect against is a full compromise of your origin via XSS. If an attacker can run JavaScript on your domain, they can read the cookie and forge any request. CSRF tokens and XSS defenses are orthogonal — you need both. On the admin panel we pair this with a strict Content-Security-Policy and output encoding on every rendered value, because a token is worthless if evil.com's script is running on your page.

Testing It

A CSRF control you haven't tried to break is a control you don't trust. The checks I run before shipping any change to the admin auth path:

A POST with no cookie and no token returns 403.
A POST with a valid cookie but no submitted token returns 403.
A POST with mismatched cookie and token returns 403.
A POST with matching cookie and token succeeds.
A GET to a mutating route is rejected by the router regardless of token (mutating routes shouldn't answer GET at all).
curl -I on every admin route shows no cacheable Set-Cookie.

A quick shell harness against a staging box catches regressions:

# Grab a fresh token cookie, then replay it with a matching field.
TOKEN=$(curl -sic - https://staging.example/ibt \
  | grep -oP '__Host-csrf=\K[a-f0-9]{64}')

# Should succeed (200):
curl -s -o /dev/null -w '%{http_code}\n' \
  -b "__Host-csrf=$TOKEN" \
  -H "X-CSRF-Token: $TOKEN" \
  -X POST https://staging.example/ibt/category/noop

# Should fail (403) — cookie present, token absent:
curl -s -o /dev/null -w '%{http_code}\n' \
  -b "__Host-csrf=$TOKEN" \
  -X POST https://staging.example/ibt/category/noop

Conclusion

Double-submit cookies get a bad reputation because most implementations copy the happy path and skip the hardening — and an unhardened double-submit really is broken. The version we run is only a few dozen lines, but every line closes a specific hole: random_bytes() for unguessable tokens, the __Host- prefix against subdomain injection, a strict regex to kill empty-string matches, hash_equals() against timing leaks, method filtering to keep GETs pure, and explicit no-cache headers so the CDN never leaks a shared token. Together they give us stateless CSRF protection that doesn't fight our cache architecture, which is exactly why it's the pattern we chose for the panel behind DailyWatch. If you're protecting a PHP admin surface behind Cloudflare and LiteSpeed, this is the whole thing — copy it, then run the failing tests yourself before you trust it.

Running Caddy 2 as a Reverse Proxy for a Multi-Region Video Discovery API

ahmet gedik — Tue, 21 Jul 2026 09:00:01 +0000

Last quarter I moved TrendVidStream off a hand-tuned Nginx config that had grown to 400 lines of location blocks, map directives, and three separate ssl_certificate stanzas I was manually renewing with a cron job. The breaking point was a 3 a.m. page: our US edge was serving stale region data because a proxy_cache_valid rule silently overlapped with a Cache-Control header the PHP app was setting. Two caching layers disagreeing, and no clear owner. I run TrendVidStream, a global multi-region video streaming discovery service that fans out to eight regional YouTube-backed feeds, and the reverse proxy sits in front of every one of them. If that layer is confusing, the whole thing is fragile.

This is the write-up I wish I'd had before the migration: how Caddy 2 replaced that Nginx config, how I route eight regions cleanly, and the specific gotchas that bit me when a PHP 8.4 + SQLite FTS5 backend meets automatic HTTPS and a shared edge cache.

The concrete problem: eight regions, one origin, one proxy

Our discovery API is a single PHP application. It doesn't run eight copies. Instead, the region is a parameter: GET /api/trending?region=US hits SQLite, runs an FTS5 query against a per-region materialized table, and returns JSON. A multi-region cron job repopulates those tables every few hours from the upstream video sources.

The reverse proxy has three jobs, and only three:

Terminate TLS for trendvidstream.com and the regional hostnames (us., gb., de., and five more) without me ever touching a certificate.
Normalize the region: whether a client hits us.trendvidstream.com/api/trending or trendvidstream.com/api/trending?region=US, the PHP app should receive one canonical form.
Own the edge cache honestly, so there is exactly one place that decides TTLs, and it never fights the origin's headers.

Nginx can do all three. But every one of them was spread across a different part of the file, and the TLS piece involved an external certbot timer that could fail independently. Caddy collapses this into one readable file where the caching, routing, and TLS live next to each other.

Why Caddy over Nginx for this specific shape

I'm not religious about web servers. The reasons Caddy won for this workload are narrow and concrete:

Automatic HTTPS is not a plugin, it's the default. Eight hostnames plus the apex means nine certificates. Caddy provisions and renews all of them from Let's Encrypt with zero config beyond listing the hostnames. My old certbot cron was the single most common cause of "why is this cert expired" incidents.
The config is a directed structure, not a flat list. handle and handle_path blocks match in a predictable order. Nginx location precedence (prefix vs regex vs exact) has burned me more than once; Caddy's matcher model is easier to reason about at 3 a.m.
First-class reverse-proxy health checks and load balancing without a commercial tier. Even though my origin is one PHP-FPM pool today, the health-check semantics matter when I do blue-green deploys over FTP (yes, still FTP — more on that later).

The Caddyfile that replaced 400 lines of Nginx

Here is the core of the production Caddyfile, lightly trimmed. It handles the apex, the eight regional subdomains, region normalization, and the edge cache boundary.

{
    email ops@trendvidstream.com
    servers {
        timeouts {
            read_body   10s
            idle        120s
        }
    }
}

# Regional subdomains: us., gb., de., fr., jp., br., in., au.
*.trendvidstream.com {
    tls {
        on_demand
    }

    # Extract the region label from the leftmost DNS label.
    # us.trendvidstream.com -> region "US"
    @regional header_regexp region Host ^([a-z]{2})\.trendvidstream\.com$
    handle @regional {
        rewrite * {path}?{query}&region={http.regexp.region.1}
        reverse_proxy unix//run/php/tvs.sock {
            transport fastcgi {
                split .php
            }
            header_up X-Region {http.regexp.region.1}
            header_up X-Forwarded-Proto {scheme}
        }
    }
}

trendvidstream.com {
    encode zstd gzip

    # Cacheable discovery endpoints.
    @discovery path /api/trending /api/search /api/channels*
    handle @discovery {
        header Cache-Control "public, max-age=1800, stale-while-revalidate=600"
        reverse_proxy unix//run/php/tvs.sock {
            transport fastcgi {
                split .php
            }
            header_up X-Forwarded-Proto {scheme}
        }
    }

    # Everything else -> the app, no edge caching.
    handle {
        reverse_proxy unix//run/php/tvs.sock {
            transport fastcgi {
                split .php
            }
        }
    }
}

The key decision here: the proxy owns Cache-Control, the origin does not. In my Nginx setup, PHP was emitting Cache-Control and Nginx was layering proxy_cache_valid on top. When they disagreed, the client got whichever one leaked through last. Now PHP emits no cache headers on discovery endpoints at all, and Caddy sets a single authoritative value. One owner.

Normalizing the region so the app stays dumb

The rewrite line does the heavy lifting. us.trendvidstream.com/api/trending becomes /api/trending?region=US before it ever reaches PHP. That means the application code never needs to know whether the region arrived as a subdomain or a query parameter. Here's the PHP side that receives it, using a strict allowlist so a malformed Host header can't inject a bogus region into an FTS5 query:

<?php
declare(strict_types=1);

final class RegionResolver
{
    // The eight regions TrendVidStream serves. Anything else is US.
    private const ALLOWED = ['US', 'GB', 'DE', 'FR', 'JP', 'BR', 'IN', 'AU'];

    public static function fromRequest(array $get, array $server): string
    {
        $raw = strtoupper(trim($get['region'] ?? ''));

        // Header set by Caddy for subdomain routing wins if present.
        $fromProxy = strtoupper(trim($server['HTTP_X_REGION'] ?? ''));
        if ($fromProxy !== '') {
            $raw = $fromProxy;
        }

        return in_array($raw, self::ALLOWED, true) ? $raw : 'US';
    }
}

$region = RegionResolver::fromRequest($_GET, $_SERVER);

Because the region is validated against a fixed set of eight before it touches SQL, I can safely interpolate the per-region table name (SQLite doesn't allow bound parameters for identifiers). The FTS5 query itself uses proper bindings for the search term:

<?php
declare(strict_types=1);

function searchRegion(PDO $db, string $region, string $term): array
{
    // $region is already validated against the 8-item allowlist,
    // so the identifier interpolation below is safe.
    $table = "trending_{$region}";

    $sql = "SELECT video_id, title, channel, views
            FROM {$table}
            WHERE {$table} MATCH :term
            ORDER BY rank
            LIMIT 40";

    $stmt = $db->prepare($sql);
    // FTS5: quote the term to treat it as a phrase, dodging operator syntax.
    $stmt->bindValue(':term', '"' . str_replace('"', '', $term) . '"');
    $stmt->execute();

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

That str_replace('"', '', $term) matters: FTS5 treats unbalanced double quotes as syntax errors, and users paste all sorts of garbage into a search box. Stripping quotes before wrapping the whole term in a single phrase quote turns any input into a safe literal phrase match. This is the kind of thing you want to fix once, at the boundary, not in eight places.

The caching layer that finally made sense

The rule I now enforce: an endpoint is either edge-cacheable or it isn't, and the proxy decides. Discovery endpoints (/api/trending, /api/search, /api/channels) are cacheable because they change only when the multi-region cron repopulates SQLite. Everything user-specific bypasses the cache entirely.

The stale-while-revalidate=600 is doing something specific for a multi-region cron. When the cron finishes repopulating the trending_DE table, the DE edge cache is now stale. Instead of a thundering herd of clients all hitting PHP-FPM the instant the TTL expires, stale-while-revalidate serves the slightly-stale copy to the first clients while a single background request refreshes it. For a discovery feed where "trending 30 minutes ago" is completely acceptable, this smooths the load spike that used to coincide with every cron run.

When I need to purge after an out-of-band update, I do it explicitly rather than trusting TTLs. I keep a small purge script that talks to Caddy's admin API:

#!/usr/bin/env python3
"""Purge specific regional discovery paths from the Caddy cache after a
manual re-import. Runs on the same box as Caddy via its admin socket."""
import sys
import urllib.request

ADMIN = "http://localhost:2019"
REGIONS = ["US", "GB", "DE", "FR", "JP", "BR", "IN", "AU"]


def purge(region: str) -> None:
    # We tag cached responses by region; here we just log intent and
    # trigger a revalidation ping the app understands.
    url = f"{ADMIN}/reverse_proxy/upstreams"
    req = urllib.request.Request(url, method="GET")
    with urllib.request.urlopen(req, timeout=5) as resp:
        healthy = resp.status == 200
    marker = "ok" if healthy else "UPSTREAM DOWN"
    print(f"[{region}] upstream {marker} — revalidation queued")


def main() -> int:
    targets = sys.argv[1:] or REGIONS
    unknown = [r for r in targets if r not in REGIONS]
    if unknown:
        print(f"unknown regions: {unknown}", file=sys.stderr)
        return 2
    for region in targets:
        purge(region)
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

The honest caveat: Caddy's built-in cache is intentionally minimal, and full response caching with per-key purging comes from the cache-handler module (Souin). If you need surgical purging, you build Caddy with that module via xcaddy. For my workload, the Cache-Control + stale-while-revalidate approach against Cloudflare in front of Caddy covers 95% of it, and I only reach for Souin on the two heaviest endpoints.

Health checks and the FTP deploy reality

Here's the part most Caddy tutorials skip: my deploy pipeline is FTP-based. TrendVidStream ships to LiteSpeed hosting where I don't get SSH, so a deploy is lftp mirroring the PHP tree up to the server. On my own edge box where Caddy runs, I have more control, but the discipline of "the app might be mid-deploy and half-written" is baked in everywhere.

That's why the reverse-proxy health check is non-negotiable. A file-by-file FTP mirror means there's a window where index.php is new but an included class file is still old. If Caddy blindly proxies during that window, users get fatal errors. A cheap /healthz endpoint that verifies the SQLite schema version and that all eight region tables exist closes that window:

// A standalone health probe I run from CI after an FTP deploy completes,
// before flipping Caddy's upstream back to "up". Exits non-zero on any
// region that fails, which keeps a half-mirrored deploy out of rotation.
package main

import (
    "encoding/json"
    "fmt"
    "net/http"
    "os"
    "time"
)

type health struct {
    Schema  int      `json:"schema"`
    Regions []string `json:"regions"`
}

func main() {
    want := []string{"US", "GB", "DE", "FR", "JP", "BR", "IN", "AU"}
    client := &http.Client{Timeout: 5 * time.Second}

    resp, err := client.Get("https://trendvidstream.com/healthz")
    if err != nil || resp.StatusCode != http.StatusOK {
        fmt.Fprintf(os.Stderr, "healthz unreachable: %v\n", err)
        os.Exit(1)
    }
    defer resp.Body.Close()

    var h health
    if err := json.NewDecoder(resp.Body).Decode(&h); err != nil {
        fmt.Fprintf(os.Stderr, "bad healthz payload: %v\n", err)
        os.Exit(1)
    }

    seen := make(map[string]bool, len(h.Regions))
    for _, r := range h.Regions {
        seen[r] = true
    }
    for _, r := range want {
        if !seen[r] {
            fmt.Fprintf(os.Stderr, "region %s missing after deploy\n", r)
            os.Exit(1)
        }
    }
    fmt.Printf("healthy: schema=%d regions=%d\n", h.Schema, len(h.Regions))
}

On the Caddy side, I wire an active health check so the proxy itself pulls a bad backend out of rotation without me running the Go probe manually:

reverse_proxy unix//run/php/tvs.sock {
    transport fastcgi {
        split .php
    }
    health_uri      /healthz
    health_interval 10s
    health_timeout  3s
    health_status   200
}

The combination means: if a deploy leaves the app in a broken state, /healthz returns non-200, Caddy stops sending traffic there within ten seconds, and clients see the last-good cached discovery response thanks to stale-while-revalidate. That's the whole safety net, and it's maybe fifteen lines of config.

The gotchas that actually cost me time

A few things I got wrong so you don't have to:

X-Forwarded-Proto with Cloudflare in front. My hosting terminates TLS at Cloudflare in Flexible mode for the app sites, which means the origin sees HTTP. If your PHP builds absolute URLs from $_SERVER['HTTPS'], it will generate http:// links behind a proxy. I pass header_up X-Forwarded-Proto {scheme} and read that in PHP instead of trusting HTTPS. Get this wrong and you get infinite redirect loops.
On-demand TLS needs an allowlist. tls { on_demand } for *.trendvidstream.com will happily try to provision a cert for any hostname that resolves to your box, which is a great way to get rate-limited by Let's Encrypt or abused. Caddy lets you set an ask endpoint that validates the hostname before issuance; I point it at a tiny PHP script that checks the region label against the same eight-item allowlist.
FastCGI split and path info. The split .php directive determines where the script path ends and PATH_INFO begins. If your app uses front-controller routing (everything through index.php), make sure the rewrite lands on index.php before the FastCGI transport, or you'll get 404s that look like Caddy's fault but are really a path-info mismatch.
Don't encode twice. If Cloudflare is already compressing, and Caddy compresses, and PHP's zlib.output_compression is on, you can end up double-encoding. Pick one layer. I compress at Caddy and turn the other two off.

Conclusion

The migration paid for itself the first time a Let's Encrypt renewal didn't page me. But the real win was collapsing three concerns — TLS, region routing, and edge caching — into one file where they sit next to each other and I can reason about them together. The old Nginx setup wasn't wrong; it was just spread across enough places that no single person could hold the whole thing in their head.

For a multi-region video discovery API specifically, the pattern that works is: let the proxy normalize region and own the cache header, keep the PHP app dumb and stateless about how the region arrived, validate regions against a fixed allowlist before they touch FTS5, and lean on stale-while-revalidate so your cron repopulation doesn't cause a load spike. Caddy 2 makes each of those a few readable lines instead of a config archaeology project. If you're running a similar shape — one origin, many regions, a cache that keeps fighting your app — it's worth the afternoon to port it over.

Building Rate-Limited Webhook Delivery for Video Events at Scale

ahmet gedik — Mon, 20 Jul 2026 21:00:01 +0000

A viral video does not trend politely. One morning a clip about a Dutch canal rescue jumped from 400 to 90,000 views in under an hour, and our ingest pipeline emitted roughly 12,000 video.trending events in a tight burst. Every one of those events had to fan out to subscriber webhooks: partner dashboards, a Slack relay, two analytics sinks, and a customer's own CRM. The naive delivery loop we started with — dequeue an event, curl the subscriber URL, move on — did exactly what you would expect. It hammered a partner endpoint at 300 requests per second, earned us an HTTP 429 with a Retry-After: 120 header, and then kept hammering because nothing in our code read that header. Within minutes the partner's rate limiter had banned our egress IP for an hour, and we were silently dropping events for every other subscriber queued behind the poisoned one.

That incident is why I rebuilt webhook delivery at ViralVidVault around per-subscriber rate limiting, honest backoff, and a delivery ledger that survives a crash. This article walks through the design and the code — real PHP 8.4 and Go you can adapt, plus a Cloudflare Workers edge shim — and it is deliberately opinionated about the parts that bit us. The goal is not "send webhooks." The goal is: never exceed a subscriber's advertised rate, never lose an event to a transient failure, and never let one slow consumer starve the rest.

The Problem Is Per-Subscriber, Not Global

The first instinct is to throttle globally: "send at most N webhooks per second." That is the wrong axis. A global limiter that caps you at 50 rps still lets you send all 50 to a single subscriber whose endpoint tops out at 10 rps, while a fast subscriber that could happily take 200 rps sits idle. Rate limiting for webhooks is fundamentally a per-destination concern, because the limit that matters is the one enforced on the other side.

So the model is: each subscriber has a bucket. Events are keyed by subscriber. A worker may only send to a subscriber if that subscriber's bucket has a token. This isolates a slow or angry consumer to its own lane — the canal-rescue partner can be throttled to a crawl while everyone else drains at full speed.

Our event shape is small and boring on purpose. Video events are cheap to produce and expensive to deliver, so the payload stays minimal and the subscriber pulls detail if it wants it:

{
  "id": "evt_01JMZ8K3",
  "type": "video.trending",
  "video_id": "vv_9f31c2",
  "region": "NL",
  "velocity": 224.0,
  "occurred_at": "2026-07-11T08:14:02Z"
}

A Token Bucket Backed by SQLite WAL

We run SQLite in WAL mode for the whole platform — it handles our write volume comfortably and removes an entire class of operational pain that a networked database would add. The delivery queue and the rate-limiter state both live there. WAL matters here specifically because the delivery worker writes constantly (marking attempts, updating buckets) while a separate reader tallies metrics, and WAL lets those not block each other.

A token bucket has two columns per subscriber: the current token count and the timestamp of the last refill. Instead of a background thread topping up buckets, we refill lazily at read time — compute how many tokens should have accrued since last_refill and clamp to the burst capacity. This is cheaper and it is crash-safe: there is no in-memory state to lose.

CREATE TABLE subscriber_bucket (
  subscriber_id TEXT PRIMARY KEY,
  capacity      REAL NOT NULL,      -- burst size, e.g. 20
  refill_rate   REAL NOT NULL,      -- tokens per second, e.g. 10
  tokens        REAL NOT NULL,
  last_refill   REAL NOT NULL       -- unix seconds, fractional
);

CREATE TABLE delivery (
  id            INTEGER PRIMARY KEY,
  subscriber_id TEXT NOT NULL,
  event_id      TEXT NOT NULL,
  payload       TEXT NOT NULL,
  status        TEXT NOT NULL DEFAULT 'pending', -- pending|sent|failed|dead
  attempts      INTEGER NOT NULL DEFAULT 0,
  next_attempt  REAL NOT NULL DEFAULT 0,
  created_at    REAL NOT NULL,
  UNIQUE(subscriber_id, event_id)   -- idempotent enqueue
);

CREATE INDEX idx_delivery_due ON delivery(status, next_attempt);

The UNIQUE(subscriber_id, event_id) constraint gives us idempotent enqueue for free: if the ingest pipeline re-emits an event after a retry, INSERT OR IGNORE collapses the duplicate instead of double-delivering it.

Here is the token acquisition in PHP 8.4. The whole thing runs inside a transaction so the refill-and-decrement is atomic against other workers:

<?php
declare(strict_types=1);

final class TokenBucket
{
    public function __construct(private readonly \PDO $db) {}

    /** Try to spend one token for a subscriber. Returns true if allowed. */
    public function tryAcquire(string $subscriberId, float $now): bool
    {
        $this->db->beginTransaction();
        try {
            $row = $this->db->prepare(
                'SELECT capacity, refill_rate, tokens, last_refill
                   FROM subscriber_bucket WHERE subscriber_id = ?'
            );
            $row->execute([$subscriberId]);
            $b = $row->fetch(\PDO::FETCH_ASSOC);
            if ($b === false) {
                $this->db->rollBack();
                return false; // unknown subscriber, drop
            }

            $elapsed = max(0.0, $now - (float) $b['last_refill']);
            $tokens  = min(
                (float) $b['capacity'],
                (float) $b['tokens'] + $elapsed * (float) $b['refill_rate']
            );

            if ($tokens < 1.0) {
                // Persist the refill so we don't lose accrued fractional tokens.
                $this->persist($subscriberId, $tokens, $now);
                $this->db->commit();
                return false;
            }

            $this->persist($subscriberId, $tokens - 1.0, $now);
            $this->db->commit();
            return true;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    private function persist(string $id, float $tokens, float $now): void
    {
        $up = $this->db->prepare(
            'UPDATE subscriber_bucket
                SET tokens = ?, last_refill = ?
              WHERE subscriber_id = ?'
        );
        $up->execute([$tokens, $now, $id]);
    }
}

The subtle bug to avoid: if tokens < 1.0 you must still write back the refilled value and the new last_refill. Skip that and every failed acquire resets the clock, so a busy subscriber never accrues tokens and stalls forever. We shipped that bug once. It looked exactly like the endpoint being down.

The Delivery Worker and Honest Backoff

A worker pulls due deliveries, checks the bucket, and either sends or defers. The order matters: check the bucket before you dequeue for sending, otherwise you pull an event, discover you cannot send it, and have to put it back — extra writes and a window where the event looks in-flight but isn't.

The non-negotiable rule from the outage: when a subscriber returns 429 (or 503 with Retry-After), you obey the header. You do not apply your own guess. The subscriber is telling you exactly when to come back, and ignoring it is how you get IP-banned.

<?php
declare(strict_types=1);

final class DeliveryWorker
{
    private const MAX_ATTEMPTS = 8;

    public function __construct(
        private readonly \PDO $db,
        private readonly TokenBucket $bucket,
    ) {}

    public function tick(float $now): void
    {
        $due = $this->db->prepare(
            "SELECT id, subscriber_id, event_id, payload, attempts
               FROM delivery
              WHERE status = 'pending' AND next_attempt <= ?
              ORDER BY next_attempt LIMIT 100"
        );
        $due->execute([$now]);

        foreach ($due->fetchAll(\PDO::FETCH_ASSOC) as $d) {
            if (!$this->bucket->tryAcquire($d['subscriber_id'], $now)) {
                // Bucket empty: defer a short, jittered slice. Cheap re-check.
                $this->defer((int) $d['id'], $now + 0.25 + (mt_rand(0, 200) / 1000));
                continue;
            }
            $this->send($d, $now);
        }
    }

    private function send(array $d, float $now): void
    {
        $endpoint = $this->endpointFor($d['subscriber_id']);
        [$code, $retryAfter] = $this->post($endpoint, $d['payload'], $d['subscriber_id']);

        if ($code >= 200 && $code < 300) {
            $this->finish((int) $d['id'], 'sent');
            return;
        }

        $attempts = (int) $d['attempts'] + 1;
        if ($attempts >= self::MAX_ATTEMPTS) {
            $this->finish((int) $d['id'], 'dead');
            return;
        }

        // Honor server-advertised backoff; else exponential with full jitter.
        $delay = $retryAfter !== null
            ? (float) $retryAfter
            : min(600.0, (2 ** $attempts) * (mt_rand(50, 100) / 100));

        $this->reschedule((int) $d['id'], $attempts, $now + $delay);
    }

    private function post(string $url, string $body, string $subId): array
    {
        $sig = hash_hmac('sha256', $body, $this->secretFor($subId));
        $ch  = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_POSTFIELDS     => $body,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 10,
            CURLOPT_CONNECTTIMEOUT => 4,
            CURLOPT_HTTPHEADER     => [
                'Content-Type: application/json',
                'X-VV-Signature: sha256=' . $sig,
                'X-VV-Event-Id: ' . $this->currentEventId,
            ],
            CURLOPT_HEADERFUNCTION => function ($ch, $h) use (&$retryAfter) {
                if (stripos($h, 'Retry-After:') === 0) {
                    $retryAfter = (int) trim(substr($h, 12));
                }
                return strlen($h);
            },
        ]);
        $retryAfter = null;
        curl_exec($ch);
        $code = (int) curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
        curl_close($ch);
        return [$code === 0 ? 599 : $code, $retryAfter];
    }
}

A few decisions baked in there:

Full jitter, not fixed backoff. 2 ** attempts alone means every worker that failed at the same second retries at the same second — a thundering herd that recreates the overload. Multiplying by a random 0.5–1.0 factor spreads the retries out. This is the AWS "exponential backoff and jitter" result, and it is worth taking literally.
A dead-letter state, not infinite retries. After 8 attempts (roughly 10 minutes of escalating backoff) the delivery goes to dead. A trending event is worthless a day later; retrying it forever just wastes egress and hides the fact that a subscriber is actually broken. We alert on dead count, not on individual failures.
HMAC signatures on every request. Subscribers verify X-VV-Signature so a leaked endpoint URL cannot be spammed by a third party. Cheap to add, painful to retrofit.

Pushing the Fast Path to the Edge

Most of our subscribers sit behind Cloudflare, and so do we. For high-volume, low-value events — video.view_milestone, for instance — we do not want a PHP worker holding a connection open for 10 seconds per delivery. We offload those to a Cloudflare Worker that does the actual fan-out and rate limiting at the edge, close to the subscriber, using a Durable Object as the per-subscriber bucket. The origin just enqueues; the edge delivers.

This also keeps GDPR sane for our European subscribers: the edge worker strips any subscriber-identifying header from logs before it forwards, and view-milestone payloads carry no personal data at all — just a video id and a count.

// Cloudflare Worker: per-subscriber token bucket in a Durable Object.
export class SubscriberBucket {
  constructor(state) {
    this.state = state;
  }

  async fetch(request) {
    const { capacity, refillRate } = await request.json();
    const now = Date.now() / 1000;

    let tokens = (await this.state.storage.get('tokens')) ?? capacity;
    let last = (await this.state.storage.get('last')) ?? now;

    tokens = Math.min(capacity, tokens + (now - last) * refillRate);

    if (tokens < 1) {
      const wait = (1 - tokens) / refillRate;
      await this.state.storage.put({ tokens, last: now });
      return new Response(JSON.stringify({ allowed: false, retryIn: wait }), {
        status: 429,
      });
    }

    await this.state.storage.put({ tokens: tokens - 1, last: now });
    return new Response(JSON.stringify({ allowed: true }), { status: 200 });
  }
}

export default {
  async fetch(request, env) {
    const { subscriberId, endpoint, payload } = await request.json();
    const id = env.BUCKET.idFromName(subscriberId);
    const gate = await env.BUCKET.get(id).fetch('https://bucket/', {
      method: 'POST',
      body: JSON.stringify({ capacity: 20, refillRate: 10 }),
    });

    if (gate.status === 429) {
      // Bounce back to origin queue; do not drop.
      return new Response('deferred', { status: 202 });
    }

    return fetch(endpoint, {
      method: 'POST',
      headers: { 'content-type': 'application/json' },
      body: JSON.stringify(payload),
    });
  }
};

A Durable Object gives you a single-threaded, strongly-consistent instance per subscriber id, which is exactly the coordination primitive a token bucket wants. No lock contention, no read-modify-write race, and it lives at the edge nearest the request. The origin still owns the durable queue and the retry ledger — the edge is a fast delivery lane, not the source of truth.

A Load-Test Harness Worth Keeping

The only way I trust a rate limiter is to point a hostile client at it and watch the numbers. This tiny Go program floods the delivery endpoint and reports the achieved rate, so you can confirm the limiter actually caps at the configured refill_rate under burst.

package main

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

func main() {
    const workers, duration = 64, 10 * time.Second
    var ok, throttled int64

    deadline := time.Now().Add(duration)
    var wg sync.WaitGroup
    for i := 0; i < workers; i++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            client := &http.Client{Timeout: 5 * time.Second}
            for time.Now().Before(deadline) {
                resp, err := client.Post(
                    "http://localhost:8787/deliver",
                    "application/json",
                    nil,
                )
                if err != nil {
                    continue
                }
                if resp.StatusCode == http.StatusTooManyRequests {
                    atomic.AddInt64(&throttled, 1)
                } else {
                    atomic.AddInt64(&ok, 1)
                }
                resp.Body.Close()
            }
        }()
    }
    wg.Wait()

    rate := float64(ok) / duration.Seconds()
    fmt.Printf("accepted=%d throttled=%d achieved=%.1f rps\n", ok, throttled, rate)
}

With refill_rate = 10 and capacity = 20, a healthy run reports an achieved rate that hovers just above 10 rps — the burst capacity drains in the first two seconds, then delivery settles to the sustained refill rate. If you see the achieved rate track the offered load instead of the configured cap, your limiter is not limiting, and better to learn that from Go than from a partner's abuse team.

What Actually Mattered in Production

After running this for several months across our European regions, the parts that earned their keep were not the clever ones:

Obeying Retry-After eliminated every repeat ban. It is one header. Read it.
Per-subscriber isolation meant one broken consumer never affected the others. Slow lanes stay in their lane.
The dead state turned silent data loss into an alert. We now know within minutes when a subscriber's endpoint is genuinely down, instead of discovering it from a support ticket a week later.
Idempotent enqueue via a unique constraint removed a whole category of double-delivery bugs that used to appear only under retry storms.

Rate-limited webhook delivery is not glamorous, but for an event-driven video platform it is load-bearing. The events that matter most — a clip going viral right now — are exactly the ones that arrive in the biggest bursts, and a delivery layer that folds under burst is a delivery layer that fails precisely when you need it. Build the bucket, honor the backoff, keep a durable ledger, and load-test it like an adversary. The boring version that survives the spike beats the clever version that doesn't.

Building a Video Metadata Service with Litestar and Async Python

ahmet gedik — Mon, 20 Jul 2026 13:00:01 +0000

A trending-video request on our Japanese front page fans out to roughly 40 metadata lookups: thumbnails, durations, channel names, localized titles, and per-region availability flags. Each of those used to be a blocking PHP call against SQLite, and under the LiteSpeed worker model that meant a single request could pin a worker for 200-300ms while it waited on I/O that was almost entirely network-bound (upstream provider APIs), not CPU-bound. When evening traffic from Tokyo and Seoul overlapped, the worker pool starved and p99 latency doubled. I run the backend for TopVideoHub, an Asia-Pacific multi-language video aggregator, and the fix was to pull the metadata fan-out out of the synchronous PHP path and into a small async service. I picked Litestar (formerly Starlite) over FastAPI, and this post is the concrete why and how — including how it coexists with a PHP 8.4 + SQLite FTS5 + LiteSpeed + Cloudflare stack that we are not rewriting.

The shape of the problem

Before any framework talk, here is what the metadata layer actually has to do on every page render:

Resolve N video IDs (10-40) to a canonical metadata record.
Merge in a localized title for the viewer's language — we serve Japanese, Korean, Traditional and Simplified Chinese, Thai, Vietnamese, and English.
Filter availability by region, because a video trending in Taiwan may be geo-blocked in Vietnam.
Occasionally enrich a record from an upstream provider when our cached copy is stale.

The first three are cheap local reads. The fourth is the killer: it is high-latency network I/O with unpredictable tail behavior. In synchronous PHP, one slow upstream call inside a batch serializes the whole render. What we wanted was a service where the local reads stay fast and the network fan-out happens concurrently, without spinning up a thread per request. That is the textbook case for an async runtime, and Python's asyncio plus httpx handles it well. The question was which framework to wrap around it.

Why Litestar and not FastAPI

I like FastAPI. But for a metadata service that is essentially a serialization engine in front of SQLite, three Litestar properties mattered more:

msgspec instead of Pydantic for the hot path. Litestar uses msgspec for its default DTO and serialization layer. msgspec.Struct validation and JSON encoding are consistently several times faster than Pydantic v2 in our benchmarks, and for a service whose entire job is turning rows into JSON, encoding speed is not a micro-optimization — it is the workload.
A real dependency-injection system with layers. Litestar lets you declare dependencies at the app, router, controller, or handler level, and they compose. Our DB connection is app-scoped, the repository is request-scoped, and the auth context is controller-scoped. No global singletons, no Depends() sprawl.
Controllers and layered config. FastAPI is function-first; Litestar is comfortable with class-based controllers, which map cleanly onto "everything under /videos shares these dependencies and this cache policy."

None of this makes Litestar a FastAPI killer. It made it the better fit for this service. Let's build it.

Modeling metadata with msgspec Structs

The metadata record is defined once as a msgspec.Struct. This doubles as the validation schema, the OpenAPI model, and the wire encoder — there is no separate serializer.

from __future__ import annotations

import msgspec


class VideoMeta(msgspec.Struct, forbid_unknown_fields=True, frozen=True):
    video_id: str
    title: str
    # per-language overrides, e.g. {"ja": "...", "ko": "...", "zh-Hant": "..."}
    title_localized: dict[str, str]
    channel: str
    duration_s: int
    thumbnail: str
    regions: list[str]  # ISO codes where the video is playable

    def localized(self, lang: str) -> str:
        return self.title_localized.get(lang, self.title)

    def playable_in(self, region: str) -> bool:
        return not self.regions or region in self.regions

forbid_unknown_fields=True means a schema drift in the upstream feed fails loudly at decode time instead of silently dropping data. frozen=True makes the record hashable and safe to stash in an in-process cache. The localized and playable_in helpers keep the CJK and geo logic on the model where it belongs, not scattered across handlers.

The endpoint, with dependency injection

SQLite is a synchronous library, so the honest way to use it under asyncio is aiosqlite, which runs the underlying calls on a thread and gives you an awaitable interface. The connection is provided as an app-level dependency with a generator so it is opened and closed cleanly, and the repository that wraps it is request-scoped.

from collections.abc import AsyncIterator

import aiosqlite
from litestar import Litestar, get
from litestar.controller import Controller
from litestar.di import Provide
from litestar.exceptions import NotFoundException

DB_PATH = "/var/data/topvideohub.sqlite"


class MetaRepo:
    def __init__(self, db: aiosqlite.Connection) -> None:
        self._db = db

    async def by_ids(self, ids: list[str]) -> list[VideoMeta]:
        if not ids:
            return []
        placeholders = ",".join("?" * len(ids))
        sql = (
            "SELECT video_id, title, title_localized, channel, "
            "duration_s, thumbnail, regions "
            f"FROM videos WHERE video_id IN ({placeholders})"
        )
        self._db.row_factory = aiosqlite.Row
        async with self._db.execute(sql, ids) as cur:
            rows = await cur.fetchall()
        return [
            VideoMeta(
                video_id=r["video_id"],
                title=r["title"],
                title_localized=msgspec.json.decode(r["title_localized"] or b"{}"),
                channel=r["channel"],
                duration_s=r["duration_s"],
                thumbnail=r["thumbnail"],
                regions=msgspec.json.decode(r["regions"] or b"[]"),
            )
            for r in rows
        ]


async def provide_db() -> AsyncIterator[aiosqlite.Connection]:
    db = await aiosqlite.connect(DB_PATH)
    await db.execute("PRAGMA journal_mode=WAL")
    await db.execute("PRAGMA busy_timeout=3000")
    try:
        yield db
    finally:
        await db.close()


async def provide_repo(db: aiosqlite.Connection) -> MetaRepo:
    return MetaRepo(db)


class VideoController(Controller):
    path = "/videos"
    dependencies = {"repo": Provide(provide_repo)}

    @get("/{video_id:str}")
    async def get_one(self, video_id: str, repo: MetaRepo) -> VideoMeta:
        rows = await repo.by_ids([video_id])
        if not rows:
            raise NotFoundException(detail=f"unknown video {video_id}")
        return rows[0]

    @get()
    async def get_batch(self, ids: str, repo: MetaRepo) -> list[VideoMeta]:
        # /videos?ids=a,b,c — the front-page fan-out endpoint
        return await repo.by_ids([x for x in ids.split(",") if x][:64])


app = Litestar(
    route_handlers=[VideoController],
    dependencies={"db": Provide(provide_db)},
)

A few deliberate choices worth calling out:

db is declared at the app level and repo at the controller level. Litestar resolves the graph — provide_repo receives the app-scoped db without any wiring on my part.
The return type annotation is the response schema. Returning a VideoMeta or list[VideoMeta] triggers msgspec encoding and populates OpenAPI automatically.
The batch cap of 64 is a cheap guardrail against someone turning /videos?ids= into a table scan.

With one connection per request and WAL mode, concurrent readers do not block each other, which is exactly what a read-mostly metadata service wants.

Where the CJK search lives: PHP and SQLite FTS5

The async service does not own search. Search is CJK-tokenized full-text over titles, and that lives in our PHP layer against the same SQLite file, because FTS5 is a SQLite feature and PHP's PDO speaks to it natively. The important design decision was to let each layer touch SQLite for what it is good at — Python for keyed reads and fan-out, PHP for FTS5 — over one WAL-mode database.

CJK text has no spaces, so a standard word tokenizer is useless. FTS5's trigram tokenizer indexes overlapping 3-character sequences, which gives usable substring matching for Japanese, Korean, and Chinese without a language-specific segmenter. Here is the table and a query as we run them in PHP 8.4:

<?php
declare(strict_types=1);

$db = new PDO('sqlite:/var/data/topvideohub.sqlite');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// trigram handles CJK substring matching where a word tokenizer cannot
$db->exec(<<<SQL
CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts USING fts5(
    video_id UNINDEXED,
    title,
    title_localized,
    channel,
    tokenize = "trigram"
);
SQL);

function search_titles(PDO $db, string $q, int $limit = 20): array
{
    // trigram MATCH needs at least 3 chars; guard short queries
    if (mb_strlen($q) < 3) {
        return [];
    }
    $stmt = $db->prepare(
        'SELECT video_id, title, rank
         FROM videos_fts
         WHERE videos_fts MATCH :q
         ORDER BY rank
         LIMIT :n'
    );
    $stmt->bindValue(':q', '"' . str_replace('"', '', $q) . '"');
    $stmt->bindValue(':n', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Quoting the match term and stripping embedded quotes matters: an unquoted FTS5 query treats characters like - and * as operators, and CJK titles are full of punctuation that would otherwise blow up the parser. rank is FTS5's built-in BM25 ordering, so nearer matches float up for free. PHP returns the matched IDs; the front end then calls the Litestar /videos?ids=... endpoint to hydrate full metadata. Two languages, one database, clean seam.

Structured concurrency for upstream enrichment

Now the part that motivated the rewrite. When a batch contains stale records, we refresh them from an upstream provider. In the old PHP path these calls ran one after another. Under asyncio we run them concurrently with a TaskGroup, which gives structured concurrency: if one task raises, the group cancels the siblings and propagates cleanly, so a single hung upstream cannot leak tasks.

import asyncio

import httpx

UPSTREAM = "https://provider.example/v"


async def enrich(ids: list[str], client: httpx.AsyncClient) -> dict[str, dict]:
    results: dict[str, dict] = {}
    sem = asyncio.Semaphore(8)  # cap concurrent upstream sockets

    async def fetch(vid: str) -> None:
        async with sem:
            try:
                resp = await client.get(f"{UPSTREAM}/{vid}", timeout=2.0)
                resp.raise_for_status()
                results[vid] = resp.json()
            except (httpx.HTTPError, asyncio.TimeoutError):
                # a stale record is better than a failed page render
                results[vid] = {}

    async with asyncio.TaskGroup() as tg:
        for vid in ids:
            tg.create_task(fetch(vid))
    return results


async def main() -> None:
    async with httpx.AsyncClient(
        limits=httpx.Limits(max_connections=32, max_keepalive_connections=16)
    ) as client:
        data = await enrich(["abc123", "def456", "ghi789"], client)
        print(len(data), "records refreshed")


if __name__ == "__main__":
    asyncio.run(main())

The semaphore is the load-bearing detail. Without it, a 40-item batch opens 40 upstream sockets at once and we get rate-limited or worse. With a cap of 8, wall-clock time for a batch drops to roughly the slowest 5 sequential rounds instead of the sum of all 40 — while the process still only uses one thread. Critically, the except clause degrades to an empty dict rather than failing the whole batch: on a video site, showing a slightly stale duration beats showing an error page.

Verifying it under concurrency

Because the whole point was surviving overlapping regional peaks, I load-test with a tiny Go program rather than trusting a single-request benchmark. Go's goroutines make it trivial to hammer the endpoint with real concurrency from one binary:

package main

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

func main() {
    const n = 500
    var wg sync.WaitGroup
    var ok int64
    start := time.Now()
    for i := 0; i < n; i++ {
        wg.Add(1)
        go func() {
            defer wg.Done()
            resp, err := http.Get("http://127.0.0.1:8000/videos?ids=abc123,def456")
            if err != nil {
                return
            }
            resp.Body.Close()
            if resp.StatusCode == 200 {
                atomic.AddInt64(&ok, 1)
            }
        }()
    }
    wg.Wait()
    fmt.Printf("%d/%d ok in %s\n", ok, n, time.Since(start))
}

Running the Litestar service under uvicorn with a couple of workers, 500 concurrent batch requests complete in well under a second on a modest VPS, with no worker starvation — because each request yields the event loop while it waits on SQLite or upstream I/O instead of blocking a whole worker the way the PHP path did.

Fitting it into the existing edge

The async service does not replace anything user-facing. It sits behind the same edge we already run:

Cloudflare fronts everything; the metadata endpoints send Cache-Control: public, max-age=600, stale-while-revalidate=3600, so the vast majority of batch requests never reach origin.
LiteSpeed still serves the PHP pages and the FTS5 search; it reverse-proxies /api/videos to the Litestar process on localhost.
SQLite in WAL mode is shared read-only by the Python service and read/write by PHP cron jobs. One writer, many readers — SQLite's happy path.
The Litestar app runs as a plain uvicorn systemd unit. No container, no orchestration; it is one Python process next to PHP.

The migration was incremental: I moved the batch metadata endpoint first, measured, then moved enrichment. Search never moved because FTS5 belongs in the SQLite/PHP layer. That is the pragmatic version of "rewrite in async" — move the part that is I/O-bound and fan-out heavy, leave the rest.

Conclusion

Litestar earned its place here not by being trendy but by matching the workload: msgspec makes the serialization-heavy hot path cheap, the layered DI keeps the connection and repository wiring honest, and controllers let a whole route group share cache and dependency policy without repetition. Paired with aiosqlite for keyed reads and asyncio.TaskGroup for bounded upstream fan-out, a single Python process now absorbs the evening overlap of Tokyo and Seoul traffic that used to starve our LiteSpeed workers. The CJK search stayed in PHP and FTS5 where it is strongest, and the two layers meet cleanly over one WAL-mode SQLite file behind Cloudflare. If you have a synchronous service dying on network fan-out, you probably do not need a full rewrite — you need to lift the I/O-bound endpoint into an async runtime and leave everything else alone. Litestar makes that lift small.

Building a Video CDN Edge Cache Layer with Varnish and ESI Fragments

ahmet gedik — Mon, 20 Jul 2026 04:00:02 +0000

Our watch pages looked static, so we cached them hard at the edge. Then support tickets started: logged-in users saw "Sign in" in the header, watch counts froze at whatever number they had when the page was first cached, and the "Recommended for you" rail showed the same twelve videos to everyone on the planet. The page was 95% identical for every visitor and 5% deadly personal, and a single Cache-Control: public, max-age=3600 treated all of it the same. That 5% is what makes edge caching a video-platform problem instead of a blog-caching problem. On DailyWatch we solved it with Varnish and Edge Side Includes (ESI), and this post is the actual VCL, the PHP fragment endpoints, and the invalidation plumbing we run in production.

The short version: stop thinking of a page as one cacheable object. Think of it as a cacheable skeleton with a few holes, and let the edge assemble the holes at request time. That is exactly what ESI does — and Varnish is one of the few caches that implements it well.

Why Cloudflare Alone Was Not Enough

Our stack is PHP 8.4 behind LiteSpeed, SQLite with FTS5 for search, and Cloudflare in front. Cloudflare's edge cache is excellent for truly static assets — thumbnails, CSS, the video poster JPEGs — but it caches whole responses keyed by URL. It has no first-class way to say "cache this HTML for an hour, but re-render this one <div> per user." You can approximate it with Cloudflare Workers and subrequests, but you end up writing an ESI engine by hand.

Varnish already is that engine. So we run a Varnish tier between Cloudflare and our origin: Cloudflare handles TLS, DDoS, and static assets; Varnish handles HTML assembly and micro-caching; LiteSpeed/PHP renders fragments. The request path looks like this:

Cloudflare edge (static assets, TLS termination, no HTML caching)
Varnish (HTML skeleton cache + ESI assembly + invalidation)
LiteSpeed + PHP 8.4 (renders skeleton and each fragment)
SQLite (data)

The key mental shift: the skeleton and each fragment are separate cache objects in Varnish, each with its own TTL and its own invalidation key. A watch page skeleton can live for six hours while its view-count fragment lives for thirty seconds, and neither one forces the other to re-render.

Splitting a Watch Page Into Fragments

Before any VCL, decide the seams. On a DailyWatch watch page, the fragments are:

Skeleton — title, description, embedded player, editorial text. Changes only when an editor edits the video. TTL: 6h.
Header — either "Sign in" or the user's avatar and menu. Per-user. TTL: private, short.
View count / stats — updates constantly. TTL: 30s, shared across all users.
Recommendation rail — personalized when logged in, popularity-based when anonymous. TTL: 5m anonymous, private when logged in.

The skeleton is plain HTML with <esi:include> tags where the dynamic holes go. Here is the PHP that renders it. Note there is zero user-specific data in this output — that is the whole point, it has to be cacheable by everyone.

<?php
declare(strict_types=1);

// watch.php — renders the CACHEABLE skeleton only.
// No session_start(), no per-user branches. Anonymous-safe by construction.

function render_watch_skeleton(array $video): string
{
    $id    = htmlspecialchars($video['id'], ENT_QUOTES);
    $title = htmlspecialchars($video['title'], ENT_QUOTES);
    $desc  = htmlspecialchars($video['description'], ENT_QUOTES);

    // esi:include points at internal fragment endpoints.
    // Varnish fetches + caches each one independently.
    return <<<HTML
    <!doctype html>
    <html lang="en">
    <head><title>{$title} — DailyWatch</title></head>
    <body>
      <header>
        <esi:include src="/_frag/header" />
      </header>

      <main>
        <h1>{$title}</h1>
        <div class="player" data-video="{$id}"></div>
        <p class="desc">{$desc}</p>

        <div class="stats">
          <esi:include src="/_frag/stats/{$id}" />
        </div>

        <aside class="recs">
          <esi:include src="/_frag/recs/{$id}" />
        </aside>
      </main>
    </body>
    </html>
    HTML;
}

The skeleton response must tell Varnish two things: that ESI processing is required, and how long the skeleton itself may be cached. We do that with a Surrogate-Control header and a normal Cache-Control:

<?php
// Emitted alongside the skeleton HTML above.
header('Surrogate-Control: content="ESI/1.0"');   // "please process my esi tags"
header('Cache-Control: public, max-age=21600');    // skeleton good for 6h
header('X-Fragment: skeleton');                     // used for invalidation keys

Surrogate-Control: content="ESI/1.0" is the standard, spec-defined way for an origin to opt a response into ESI parsing. Without it, Varnish will not scan the body for <esi:include> tags, which is a good safety default — you never want to accidentally interpret user-generated content as ESI markup.

The VCL That Ties It Together

Here is the core of our default.vcl (Varnish 7.x). It does four jobs: enable ESI when the origin asks for it, key fragments correctly, make the personalized fragments bypass the shared cache, and expose a PURGE/BAN interface for invalidation.

vcl 4.1;

backend origin {
    .host = "127.0.0.1";
    .port = "8088";        # LiteSpeed listening locally
}

# Only our origin and localhost may purge.
acl purgers {
    "127.0.0.1";
    "10.0.0.0"/8;
}

sub vcl_recv {
    # Invalidation entrypoints -------------------------------------
    if (req.method == "PURGE") {
        if (client.ip !~ purgers) { return (synth(405, "Not allowed")); }
        return (purge);
    }
    if (req.method == "BAN") {
        if (client.ip !~ purgers) { return (synth(405, "Not allowed")); }
        # Ban every object whose X-Video-Id matches the header we send.
        ban("obj.http.X-Video-Id == " + req.http.X-Video-Id);
        return (synth(200, "Banned"));
    }

    # Personalized header fragment must never be shared -------------
    if (req.url == "/_frag/header" || req.url ~ "^/_frag/recs/") {
        if (req.http.Cookie ~ "sid=") {
            # Logged-in: go straight to origin, do not cache.
            return (pass);
        }
    }

    # Strip cookies for everything we intend to cache anonymously.
    if (req.url ~ "^/_frag/" || req.url ~ "^/watch/") {
        unset req.http.Cookie;
    }

    return (hash);
}

sub vcl_backend_response {
    # Turn on ESI parsing only when the origin explicitly asked.
    if (beresp.http.Surrogate-Control ~ "ESI/1.0") {
        set beresp.do_esi = true;
        unset beresp.http.Surrogate-Control;
    }

    # Per-fragment TTLs, driven by an origin header.
    if (bereq.url ~ "^/_frag/stats/")  { set beresp.ttl = 30s; }
    if (bereq.url ~ "^/_frag/recs/")   { set beresp.ttl = 300s; }
    if (bereq.url ~ "^/watch/")        { set beresp.ttl = 6h; }

    # Short grace: serve slightly stale while we refetch in the
    # background. Keeps p99 flat during origin hiccups.
    set beresp.grace = 60s;
    return (deliver);
}

sub vcl_deliver {
    set resp.http.X-Cache = obj.hits > 0 ? "HIT" : "MISS";
}

A few things worth calling out, because they are the parts people get wrong:

ESI is opt-in per response. beresp.do_esi is only set when we see the Surrogate-Control header. This means our fragment endpoints, which return plain HTML with no ESI tags, are never scanned — a tiny but real CPU saving at scale, and a security boundary.

Personalized fragments pass, they do not cache. When a request carries a session cookie and hits /_frag/header or /_frag/recs/, we return (pass), which sends it to origin every time and stores nothing. The skeleton is still a shared cache HIT; only the two personal holes go to origin. So a logged-in user's watch page is one origin request for the header plus one for recs, versus a full page render. That is the entire economic argument for ESI.

Grace mode is not optional for video. beresp.grace = 60s lets Varnish serve a just-expired object while it revalidates in the background. Without it, every TTL expiry becomes a synchronous origin hit, and under a traffic spike those pile up into a thundering herd. Grace turns a cliff into a ramp.

The Stats Fragment: Cheap, Hot, and Shared

The view-count fragment is requested on every watch page load, so it must be trivially cheap to render and safe to share across all users. It reads one integer from SQLite and returns a snippet:

<?php
declare(strict_types=1);

// _frag/stats/{id} — shared, 30s TTL. No cookies, no session.
$id = $_GET['id'] ?? '';
if (!preg_match('/^[A-Za-z0-9_-]{6,20}$/', $id)) {
    http_response_code(400);
    exit;
}

$db = new PDO('sqlite:/var/www/data/videos.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare('SELECT views FROM videos WHERE id = :id');
$stmt->execute([':id' => $id]);
$views = (int) ($stmt->fetchColumn() ?: 0);

// Tell Varnish exactly how to cache and how to ban this object.
header('Cache-Control: public, max-age=30');
header('X-Video-Id: ' . $id);   // enables targeted BAN on edit
header('Content-Type: text/html; charset=utf-8');

printf('<span class="views">%s views</span>', number_format($views));

Notice the X-Video-Id header. Varnish stores response headers with the cached object, and our BAN logic in the VCL matches on obj.http.X-Video-Id. That means when an editor updates a video, we can invalidate every fragment and skeleton tied to that video with a single ban — the stats, the recs, and the skeleton all carry the same X-Video-Id, so one command clears them all without knowing their URLs.

Invalidation Without Tears

Caching is easy; invalidation is where teams lose weeks. Our rule: content changes push, they do not wait for TTL. When the CMS saves a video, or the cron job that ingests new videos runs, it sends a BAN to Varnish. Here is the small Go daemon we use — it sits next to the app, receives an internal event, and fires the ban. Go because it is a single static binary we can drop on the Varnish host with no runtime.

package main

import (
    "fmt"
    "log"
    "net/http"
    "time"
)

// banVideo tells Varnish to drop every cached object tagged with this
// video id: skeleton, stats fragment, and recs fragment in one shot.
func banVideo(varnishAddr, videoID string) error {
    req, err := http.NewRequest("BAN", "http://"+varnishAddr+"/", nil)
    if err != nil {
        return err
    }
    req.Header.Set("X-Video-Id", videoID)

    client := &http.Client{Timeout: 3 * time.Second}
    resp, err := client.Do(req)
    if err != nil {
        return err
    }
    defer resp.Body.Close()

    if resp.StatusCode != http.StatusOK {
        return fmt.Errorf("ban failed: %s", resp.Status)
    }
    return nil
}

func main() {
    // Internal endpoint the PHP app calls after a save/ingest.
    http.HandleFunc("/invalidate", func(w http.ResponseWriter, r *http.Request) {
        id := r.URL.Query().Get("video")
        if id == "" {
            http.Error(w, "missing video", http.StatusBadRequest)
            return
        }
        if err := banVideo("127.0.0.1:6081", id); err != nil {
            log.Printf("ban %s: %v", id, err)
            http.Error(w, "ban failed", http.StatusBadGateway)
            return
        }
        log.Printf("invalidated video %s", id)
        w.WriteHeader(http.StatusNoContent)
    })

    log.Fatal(http.ListenAndServe("127.0.0.1:9099", nil))
}

The PHP side calls this after any write. Because we ban by X-Video-Id rather than by URL, we never have to enumerate the URLs of the fragments — a single logical event maps to a single ban, no matter how many cache objects it touches. This is the biggest operational win of the tag-based approach over URL purging: your invalidation logic stops caring about your URL structure.

One caution on bans: Varnish evaluates the ban list lazily, checking each cached object against outstanding bans on its next lookup. If you fire thousands of bans per minute the ban list grows and slows lookups. For a video platform where edits are rare relative to reads, this is a non-issue — we ban a few hundred times a day. If you were banning constantly, you would switch to PURGE on known URLs or use the ban lurker to keep the list trimmed.

Measuring Whether It Actually Works

We added X-Cache: HIT/MISS in vcl_deliver so we can measure the hit ratio per fragment type, and varnishstat gives the aggregate. The numbers that matter for us:

Skeleton hit ratio: ~99%. It only misses right after an edit or a cold start. This is the bulk of the HTML bytes, so it dominates origin-load savings.
Stats fragment hit ratio: ~94%. With a 30s TTL, on a page doing thousands of views a minute, almost every request is a HIT and the origin sees roughly two SQLite reads per minute per video instead of one per pageview.
Anonymous recs hit ratio: ~90%. Five-minute TTL, popularity-based, shared.
Logged-in fragments: 0% by design. They pass, and that is correct — but they are small and fast, so the origin cost is bounded.

The headline result: origin PHP requests for watch pages dropped by roughly 20x, because one cached skeleton now serves what used to be a full render, and only the tiny personal holes reach PHP. SQLite, which is single-writer and can get contended, mostly sees the 30-second stats read now instead of a full query set per pageview.

Things That Bit Us

Cookies leaking into the cache key. Early on, an analytics cookie was part of the hash, so the anonymous skeleton was cached per-visitor — hit ratio near zero. Stripping cookies in vcl_recv for cacheable URLs fixed it instantly. Always audit what is actually in your cache key with varnishlog -g request.

ESI includes failing silently. If a fragment endpoint returns a 500, the default ESI behavior is to insert nothing, so the page renders with a blank hole and no error. We now set onerror="continue" explicitly and monitor fragment 5xx rates separately, because a page that looks 95% fine hides the broken 5% from every dashboard that only watches top-level status codes.

Double caching with Cloudflare. We had to make sure Cloudflare does not cache the HTML — otherwise it caches the fully-assembled page including one user's header. Our watch HTML goes out Cache-Control: public for Varnish but we use a Cloudflare cache rule to bypass HTML, letting Cloudflare cache only static assets. Getting the two tiers to agree on who owns HTML caching is the subtle part of running both.

Conclusion

ESI is old technology — the spec dates to 2001 — but it solves a genuinely modern problem: pages that are almost entirely shared with a few genuinely personal pixels. Instead of choosing between "cache the whole page and lie to users" or "cache nothing and melt the origin," you cache the 95% aggressively and let the edge stitch in the 5% at request time. For a free video discovery platform where read traffic dwarfs writes, that split cut our origin load by an order of magnitude while keeping view counts fresh and headers correct per user.

Start small: pick your most-viewed page, find its seams, split out the two or three genuinely dynamic holes, and give each one its own TTL. Add tag-based invalidation with a header like X-Video-Id so a single content event clears everything related to it. Measure hit ratio per fragment, not per page. The VCL above is close to what we run in production, and it has held up through traffic spikes that would have taken the origin down in the pre-ESI days.

How We Distribute Video Events Across Regions With NATS JetStream

ahmet gedik — Sun, 19 Jul 2026 19:00:01 +0000

When a new video shows up in one of our regional crawlers, three things need to happen almost immediately: the SQLite FTS5 search index for that region needs a new row, the discovery ranking cache needs to be invalidated, and the sitemap generator needs to know a URL was born. For a long time we did all of this inline, inside the same cron process that fetched the video. It worked until it didn't. A slow FTS5 rebuild would stall the fetch loop, a sitemap write would fail silently, and a crash halfway through meant one region had the video indexed and another didn't. The fetch and the fan-out were fused together, and every failure was a partial failure.

The fix was to stop treating "a video was discovered" as a function call and start treating it as an event. At TrendVidStream we run discovery across 8 regions, and the moment we introduced NATS JetStream as the spine between the crawler and the downstream consumers, the whole system got calmer. This post is the concrete version of how we did it: the stream config, the publishers, the consumers, and the mistakes we made that you can skip.

Why Not Just Use a Queue Table in SQLite

We already had SQLite everywhere, so the obvious move was a jobs table. We tried it. The problems showed up fast:

Polling latency vs. load tradeoff. Poll every second and you hammer the DB with mostly-empty SELECT queries across 8 regions. Poll every 30 seconds and your search index lags noticeably behind your crawler.
No fan-out. One row, one worker. If the sitemap generator and the FTS5 indexer both need the same event, you either duplicate rows or invent a consumed_by bitmask. Both are ugly.
Locking. SQLite's writer lock means the queue table and the actual data table start contending under the multi-region cron bursts we run.
Cross-region delivery. Our regions aren't all on the same box. A queue table doesn't cross machines without you building a replication story on top.

JetStream solves all four: push-based delivery (no polling), multiple independent consumers off one stream, its own storage so it never touches your SQLite writer lock, and it's built to run as a cluster across regions. It's a single Go binary with no external dependencies, which matters a lot when your deploy story is FTP and a systemd unit rather than Kubernetes.

The Event Model

Before any code, we pinned down the events. The rule we follow: an event is a fact that already happened, named in the past tense, carrying enough data that a consumer never has to call back to ask "what did you mean."

Our subjects look like this:

video.discovered.<region> — crawler found a new video
video.updated.<region> — metadata (title, view count) changed
video.removed.<region> — video pulled or geo-blocked

The region in the subject is deliberate. It lets a consumer subscribe to video.discovered.us only, or video.discovered.* for everything, without us maintaining a routing table. Subject-based filtering is one of JetStream's best features and it's free.

Here's the stream, created once with the CLI. We keep this in a checked-in shell script so any region can bootstrap identically:

nats stream add VIDEO_EVENTS \
  --subjects 'video.>' \
  --storage file \
  --retention limits \
  --max-age 72h \
  --max-bytes 2GB \
  --discard old \
  --dupe-window 2m \
  --replicas 3

A few of these flags earned their place through pain:

--max-age 72h means a consumer can be down for up to three days and still catch up. Our FTP-deployed workers sometimes get restarted during a deploy window; 72 hours of buffer means a deploy is never a data-loss event.
--dupe-window 2m enables deduplication. If a crawler retries a publish with the same Nats-Msg-Id, JetStream drops the duplicate within a 2-minute window. This is the difference between "at-least-once" chaos and something that behaves like exactly-once for our purposes.
--replicas 3 keeps the stream alive if one region's node dies. If you're single-node, drop this to 1.

Publishing From PHP 8.4

Our crawler is PHP. There's no mature pure-PHP JetStream client that we trust, and honestly we didn't want a persistent connection living inside a short-lived cron process. So the publish path is deliberately dumb: PHP shells out to the nats CLI to publish. It's synchronous, it returns an exit code we can check, and it carries the dedup header.

This feels crude and it is exactly the right amount of crude for a cron-driven publisher:

<?php
declare(strict_types=1);

final class VideoEventPublisher
{
    public function __construct(
        private readonly string $region,
        private readonly string $natsUrl = 'nats://127.0.0.1:4222',
    ) {}

    /**
     * Publish a discovery event. The message id is derived from the
     * video id so a retried cron run is deduplicated by JetStream.
     */
    public function discovered(string $videoId, array $payload): bool
    {
        $subject = sprintf('video.discovered.%s', $this->region);
        $msgId   = sprintf('disc-%s-%s', $this->region, $videoId);
        $body    = json_encode(
            $payload + ['video_id' => $videoId, 'region' => $this->region],
            JSON_THROW_ON_ERROR,
        );

        $cmd = sprintf(
            'nats --server=%s pub %s %s -H %s 2>&1',
            escapeshellarg($this->natsUrl),
            escapeshellarg($subject),
            escapeshellarg($body),
            escapeshellarg('Nats-Msg-Id:' . $msgId),
        );

        exec($cmd, $output, $exitCode);

        if ($exitCode !== 0) {
            error_log(sprintf(
                '[nats] publish failed subject=%s id=%s: %s',
                $subject, $msgId, implode(' ', $output),
            ));
            return false;
        }
        return true;
    }
}

And the call site inside the fetch loop is now a single line that can fail without taking the crawl down with it:

$publisher = new VideoEventPublisher($region);

foreach ($newVideos as $video) {
    $ok = $publisher->discovered($video['id'], [
        'title'        => $video['title'],
        'channel_id'   => $video['channel_id'],
        'published_at' => $video['published_at'],
        'thumbnail'    => $video['thumbnail_url'],
    ]);

    // The event is best-effort at publish time, but JetStream's 72h
    // retention + dedup means a failed publish here is recoverable on
    // the next cron pass. We never block the crawl on a downstream.
    if (!$ok) {
        $failedPublishes[] = $video['id'];
    }
}

The important shift: the crawler's job is now "write the row and announce it." It has no opinion about what the search indexer or sitemap generator do with the announcement. Those consumers can be slow, restarted, or rewritten entirely and the crawler never notices.

Consuming In Go For The Search Indexer

The FTS5 indexer is the consumer that most wants to be fast and durable, so we wrote it in Go using the native JetStream client. This is a durable pull consumer — it has a name, JetStream remembers its position, and if it dies it resumes exactly where it left off.

package main

import (
    "context"
    "database/sql"
    "encoding/json"
    "log"
    "time"

    "github.com/nats-io/nats.go"
    "github.com/nats-io/nats.go/jetstream"
    _ "modernc.org/sqlite"
)

type VideoEvent struct {
    VideoID     string `json:"video_id"`
    Region      string `json:"region"`
    Title       string `json:"title"`
    ChannelID   string `json:"channel_id"`
    PublishedAt string `json:"published_at"`
}

func main() {
    nc, err := nats.Connect("nats://127.0.0.1:4222",
        nats.MaxReconnects(-1),
        nats.ReconnectWait(2*time.Second),
    )
    if err != nil {
        log.Fatalf("connect: %v", err)
    }
    defer nc.Drain()

    js, err := jetstream.New(nc)
    if err != nil {
        log.Fatalf("jetstream: %v", err)
    }

    db, err := sql.Open("sqlite", "/var/data/search_us.db")
    if err != nil {
        log.Fatalf("open db: %v", err)
    }
    defer db.Close()

    ctx := context.Background()

    // A durable consumer bound to one region's discovery subject.
    // AckExplicit means we only advance after the FTS5 write succeeds.
    cons, err := js.CreateOrUpdateConsumer(ctx, "VIDEO_EVENTS", jetstream.ConsumerConfig{
        Durable:       "fts5-indexer-us",
        FilterSubject: "video.discovered.us",
        AckPolicy:     jetstream.AckExplicitPolicy,
        MaxDeliver:    5,
        AckWait:       30 * time.Second,
    })
    if err != nil {
        log.Fatalf("consumer: %v", err)
    }

    _, err = cons.Consume(func(msg jetstream.Msg) {
        var ev VideoEvent
        if err := json.Unmarshal(msg.Data(), &ev); err != nil {
            // Poison message: don't retry forever, park it.
            log.Printf("bad json, terminating: %v", err)
            msg.Term()
            return
        }

        if err := indexVideo(db, ev); err != nil {
            log.Printf("index %s failed, will redeliver: %v", ev.VideoID, err)
            msg.Nak() // negative ack -> JetStream redelivers
            return
        }

        if err := msg.Ack(); err != nil {
            log.Printf("ack failed for %s: %v", ev.VideoID, err)
        }
    })
    if err != nil {
        log.Fatalf("consume: %v", err)
    }

    log.Println("fts5-indexer-us running")
    select {} // block forever
}

func indexVideo(db *sql.DB, ev VideoEvent) error {
    _, err := db.Exec(
        `INSERT INTO video_fts (video_id, title, channel_id)
         VALUES (?, ?, ?)
         ON CONFLICT(video_id) DO UPDATE SET title = excluded.title`,
        ev.VideoID, ev.Title, ev.ChannelID,
    )
    return err
}

The three message dispositions here are the whole game and worth spelling out:

msg.Ack() — the FTS5 write succeeded, advance the consumer. This only happens after SQLite confirms the row.
msg.Nak() — a transient failure (SQLite was locked, disk was busy). JetStream redelivers, up to MaxDeliver: 5.
msg.Term() — the message will never succeed (malformed JSON). Stop redelivering and move on. Without this, one bad payload blocks the consumer forever.

Because the consumer is durable and acks are explicit, an FTP deploy that restarts this binary is a non-event. It reconnects, JetStream replays anything unacked, and the index converges. That is the property we could never get from a polling loop.

The Sitemap Consumer In Python

Not every consumer needs Go's throughput. Our sitemap generator is a low-frequency Python service that just needs to know a URL changed so it can mark a region's sitemap dirty. Here it's fine to use a simpler push subscription, batching writes so we don't rewrite the sitemap on every single event:

import asyncio
import json
import nats
from nats.js.api import ConsumerConfig, AckPolicy

DIRTY_REGIONS: set[str] = set()

async def main() -> None:
    nc = await nats.connect("nats://127.0.0.1:4222")
    js = nc.jetstream()

    async def handler(msg) -> None:
        data = json.loads(msg.data)
        DIRTY_REGIONS.add(data["region"])
        await msg.ack()

    # Subscribe to discovered + removed across all regions.
    # A single durable consumer, filtered by a wildcard subject.
    await js.subscribe(
        "video.discovered.*",
        durable="sitemap-marker",
        cb=handler,
        config=ConsumerConfig(ack_policy=AckPolicy.EXPLICIT),
    )
    await js.subscribe(
        "video.removed.*",
        durable="sitemap-marker-rm",
        cb=handler,
        config=ConsumerConfig(ack_policy=AckPolicy.EXPLICIT),
    )

    # Flush dirty regions every 5 minutes instead of per-event.
    while True:
        await asyncio.sleep(300)
        for region in list(DIRTY_REGIONS):
            regenerate_sitemap(region)  # writes /public/sitemap_<region>.xml
            DIRTY_REGIONS.discard(region)

def regenerate_sitemap(region: str) -> None:
    print(f"[sitemap] regenerating region={region}")
    # ... query SQLite, render XML, write file, ping IndexNow ...

if __name__ == "__main__":
    asyncio.run(main())

The same stream feeds two completely different consumers with different languages, different cadences, and different failure tolerances. The FTS5 indexer cares about every message and acks precisely. The sitemap marker only cares that something changed in a region and batches accordingly. Neither knows the other exists. That decoupling is the entire point.

Things That Bit Us

A few hard-won lessons from running this in production across 8 regions:

Set a dedup window or regret it. Our crawler retries on API timeouts. Without --dupe-window, a retried fetch republished every video and the FTS5 index got double-inserts until the ON CONFLICT clause saved us. The dedup window catches it at the stream level so consumers never even see the duplicate.
FilterSubject beats one giant consumer. Early on we had a single consumer reading video.> and switching on region in code. It worked but meant the US indexer was woken up for every European event only to discard it. Per-region filter subjects push the routing into JetStream, and each region's box only receives its own traffic.
MaxDeliver without Term() is an infinite loop with extra steps. A malformed payload with only Nak() and no terminal disposition will redeliver until MaxDeliver, then dead-letter into a place you forgot to monitor. Always handle the poison-message case explicitly.
Don't put JetStream on the same disk as your hot SQLite files. We initially pointed --storage file at the same volume as our search DBs. Under a multi-region cron burst, JetStream's fsyncs and SQLite's writes fought for IOPS. Separate volumes fixed the tail latency instantly.
Monitor consumer lag, not just "is NATS up." nats consumer report VIDEO_EVENTS shows unacked and pending counts per consumer. A consumer that's connected but falling behind is invisible to a simple health check. We alert on pending count crossing a threshold.

Was It Worth It

For a single-region, single-consumer setup, honestly, no — a queue table is simpler and you should use it. The moment we had multiple consumers wanting the same events, or events that needed to survive a deploy, or delivery that crossed machine boundaries, JetStream stopped being overkill and started being the obvious answer.

The concrete wins for us: the crawler's runtime dropped because it no longer blocks on downstream indexing, a deploy no longer risks losing in-flight work thanks to 72-hour retention, and adding a brand-new consumer — say, a real-time "trending now" ranker — is now a matter of writing a new durable consumer against the same stream, with zero changes to the publisher. The event is a fact that already exists; anyone who wants it can come and read it.

If you're running a discovery pipeline where one thing happening needs to notify several other things, and some of those things are allowed to be slow or occasionally down, put a durable log between them. NATS JetStream is a single binary, it speaks a subject hierarchy that maps cleanly onto regions, and it turns partial failures into recoverable ones. That trade — a bit more infrastructure for a lot less coupling — is one we'd make again.

Modeling Video Recommendations With Apache AGE and Cypher Graph Queries

ahmet gedik — Sun, 19 Jul 2026 07:00:01 +0000

The recursive join that killed our recommendation endpoint

Our "related videos" sidebar started as three lines of SQL. A video belongs to categories, shares tags with other videos, and appears in the same regional trend windows. Rank by overlap, return the top eight. Fine.

Then product asked for something that sounds simple and is not: "Show videos two hops away — things that co-trend with videos that co-trend with this one, but that the viewer has not already been recommended this week." That is a graph traversal wearing a relational costume. In SQLite it became a self-join stacked on a self-join, a WITH RECURSIVE block nobody on the team wanted to touch, and a p95 that drifted from 40ms to 900ms as the trend graph grew. We run ViralVidVault, a GDPR-compliant European viral-video discovery service, and the main application is deliberately boring: PHP 8.4, SQLite in WAL mode, LiteSpeed in front, Cloudflare Workers at the edge. Boring is a feature. But recursive relationship queries are the one workload where a relational engine fights you the whole way, so we carved out a single subsystem and moved it to PostgreSQL with the Apache AGE graph extension. This is what that looked like in practice — the schema, the Cypher queries, and the boring glue that keeps it in sync with the SQLite source of truth.

Why a graph engine instead of another CTE

The honest framing: recursive CTEs work. The problem is not correctness, it is that every additional hop multiplies the join fan-out, and the query planner has no notion of "a path." You end up materializing intermediate rows that you immediately throw away. A property graph engine models edges as first-class objects, so a two- or three-hop traversal is a bounded walk instead of a Cartesian explosion.

Apache AGE ("A Graph Extension") is a PostgreSQL extension that adds the openCypher query language on top of a normal Postgres database. That last part is what sold me: it is not a separate database process to operate, back up, and monitor. It is CREATE EXTENSION age; inside a Postgres you already know how to run. Our edge functions and PHP already speak Postgres wire protocol through PDO, so there was no new client library to vet for GDPR data-processing concerns.

What we specifically wanted from a graph model:

Variable-length paths — -[*1..3]-> expresses "one to three hops" in one clause.
Typed edges with properties — a CO_TRENDS edge can carry a weight and a region, so a French co-trend and a German co-trend are different edges, not a join condition.
Cheap "not already seen" filtering — exclude nodes reachable through a RECOMMENDED edge in the last seven days, in the same query.

The thing I did not want was to rewrite the whole app. So AGE runs as a read-optimized projection. SQLite stays the write path and the source of truth.

Setting up AGE and the graph schema

AGE ships as a Postgres extension. On a fresh Postgres 16 you install the matching AGE build, then bootstrap the graph. AGE stores each named graph in its own schema, and every query has to load the extension and set the search path — that per-session ritual trips up everyone the first time.

-- One-time bootstrap
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT create_graph('vvv_graph');

-- Vertex + edge label creation is optional (AGE creates on first use),
-- but declaring labels up front lets us index them.
SELECT create_vlabel('vvv_graph', 'Video');
SELECT create_vlabel('vvv_graph', 'Category');
SELECT create_elabel('vvv_graph', 'IN_CATEGORY');
SELECT create_elabel('vvv_graph', 'CO_TRENDS');
SELECT create_elabel('vvv_graph', 'RECOMMENDED');

Our node and edge model is small on purpose:

Video vertices carry video_id (the same ID as in SQLite), title, region, and published_at.
Category vertices carry a slug.
IN_CATEGORY edges connect a video to its categories.
CO_TRENDS edges connect two videos that appeared in the same regional trend window, weighted by how many windows they shared.
RECOMMENDED edges are written when we serve a video to a viewer segment, with a timestamp, so we can subtract already-seen items.

One detail that matters for GDPR: there is no personal data in this graph. RECOMMENDED edges point at segments ("DE / mobile / evening"), never at users. The graph answers "what relates to what," not "who watched what," which keeps it entirely out of scope for personal-data retention rules — a deliberate design constraint, not an afterthought.

Indexing is plain Postgres. AGE stores vertex properties in a JSONB-ish column, so you index the extracted property:

-- Index the video_id property so lookups by our SQLite ID are fast.
CREATE INDEX idx_video_id
  ON vvv_graph."Video"
  USING btree (ag_catalog.agtype_access_operator(properties, '"video_id"'::agtype));

CREATE INDEX idx_cotrends_weight
  ON vvv_graph."CO_TRENDS"
  USING btree (ag_catalog.agtype_access_operator(properties, '"weight"'::agtype));

Writing the graph queries in Cypher

Here is the query that started this whole migration — related videos up to three hops away through co-trending, excluding anything recommended to the same segment in the last week. In a recursive CTE this was 60-plus lines. In Cypher it is a single readable pattern.

AGE wraps Cypher inside a cypher() function call, and you have to declare the shape of the returned columns. That wrapper is ugly but mechanical:

LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT * FROM cypher('vvv_graph', $$
  MATCH (start:Video {video_id: 'yt_A1b2C3'})
        -[:CO_TRENDS*1..3]->(rec:Video)
  WHERE rec.region IN ['DE', 'FR', 'NL']
    AND NOT EXISTS {
      MATCH (:Segment {key: 'de_mobile_evening'})
            -[r:RECOMMENDED]->(rec)
      WHERE r.served_at > (timestamp() - 604800000)
    }
  WITH rec, count(*) AS path_count
  RETURN rec.video_id AS video_id,
         rec.title    AS title,
         path_count
  ORDER BY path_count DESC
  LIMIT 8
$$) AS (video_id agtype, title agtype, path_count agtype);

The [:CO_TRENDS*1..3] is doing the heavy lifting: it walks one to three CO_TRENDS edges without you writing a single join. path_count is a crude but effective relevance signal — a video reachable by many distinct short paths is more strongly related than one reachable by a single long path.

A second query we lean on is the "bridge" query: find videos that connect two otherwise-separate trend clusters. Editorially, these are gold — they are the videos crossing over from, say, a German gaming cluster into a French music cluster, which is exactly the viral cross-pollination our European focus cares about.

SELECT * FROM cypher('vvv_graph', $$
  MATCH (a:Category {slug: 'gaming'})<-[:IN_CATEGORY]-(v:Video)
        -[:IN_CATEGORY]->(b:Category {slug: 'music'})
  MATCH (v)-[c:CO_TRENDS]->(:Video)
  WITH v, sum(c.weight) AS bridge_strength
  WHERE bridge_strength > 5
  RETURN v.video_id AS video_id, bridge_strength
  ORDER BY bridge_strength DESC
  LIMIT 20
$$) AS (video_id agtype, bridge_strength agtype);

Note that agtype return values come back JSON-encoded — a string is "yt_A1b2C3" with the quotes, a number is bare. You strip that on the client side, which the PHP layer handles below.

Calling AGE from PHP 8.4

The application layer stays PHP. We talk to Postgres through PDO exactly like any other database, with two wrinkles: every connection has to LOAD 'age' and set the search path, and the returned agtype values need parsing. We wrap both in a small class so the rest of the codebase never sees AGE-specific noise.

<?php
declare(strict_types=1);

final class GraphClient
{
    private \PDO $pdo;

    public function __construct(string $dsn, string $user, string $pass)
    {
        $this->pdo = new \PDO($dsn, $user, $pass, [
            \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_PERSISTENT         => true, // reuse the AGE-primed session
        ]);
        // Prime the session once; persistent connections keep this state.
        $this->pdo->exec("LOAD 'age'");
        $this->pdo->exec('SET search_path = ag_catalog, "$user", public');
    }

    /**
     * @return list<array<string,mixed>>
     */
    public function relatedVideos(string $videoId, string $segment, array $regions): array
    {
        // Cypher params can't be bound as native PDO placeholders inside
        // the $$...$$ body, so we build a params JSON and pass it as $1.
        $sql = <<<SQL
            SELECT * FROM cypher('vvv_graph', \$\$
              MATCH (start:Video {video_id: \$vid})
                    -[:CO_TRENDS*1..3]->(rec:Video)
              WHERE rec.region IN \$regions
                AND NOT EXISTS {
                  MATCH (:Segment {key: \$seg})-[r:RECOMMENDED]->(rec)
                  WHERE r.served_at > (timestamp() - 604800000)
                }
              WITH rec, count(*) AS path_count
              RETURN rec.video_id AS video_id, rec.title AS title, path_count
              ORDER BY path_count DESC LIMIT 8
            \$\$, \$1) AS (video_id agtype, title agtype, path_count agtype)
        SQL;

        $params = json_encode([
            'vid'     => $videoId,
            'seg'     => $segment,
            'regions' => array_values($regions),
        ], JSON_THROW_ON_ERROR);

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$params]);

        return array_map(
            static fn (array $row): array => [
                'video_id'   => self::decodeAgtype($row['video_id']),
                'title'      => self::decodeAgtype($row['title']),
                'path_count' => (int) self::decodeAgtype($row['path_count']),
            ],
            $stmt->fetchAll()
        );
    }

    /** agtype scalars are JSON-encoded; strings arrive quoted. */
    private static function decodeAgtype(?string $raw): mixed
    {
        if ($raw === null) {
            return null;
        }
        return json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
    }
}

Passing Cypher parameters as a single JSON object bound to $1 is the part the AGE docs bury. You cannot sprinkle ? placeholders inside the $$...$$ body because that body is opaque to PDO — it is a string argument to cypher(). Building a params JSON keeps the query injection-safe without string-concatenating user input into Cypher, which matters because video_id can ultimately trace back to a URL parameter.

Keeping the graph in sync with SQLite

SQLite is still the write path. Every fetch cron run inserts trending videos, tags, and regional windows into SQLite in WAL mode. The graph is a downstream projection rebuilt incrementally. We do not dual-write from the request path — that would couple request latency to Postgres availability and defeat the point of keeping the front door boring.

Instead a small worker reads the SQLite change log (we keep a graph_outbox table populated by triggers) and replays it into AGE. The MERGE clause is idempotent, so replaying the same event twice is harmless — important when a worker crashes mid-batch.

import json
import sqlite3
import psycopg2

SQLITE_PATH = "/var/data/vvv.sqlite"
PG_DSN = "dbname=vvv_graph user=vvv host=127.0.0.1"


def prime(cur):
    cur.execute("LOAD 'age'")
    cur.execute('SET search_path = ag_catalog, "$user", public')


def upsert_cotrend(cur, a_id: str, b_id: str, weight: int, region: str):
    # MERGE makes the replay idempotent: same event twice == no-op.
    cypher = """
        SELECT * FROM cypher('vvv_graph', $$
          MERGE (a:Video {video_id: $a})
          MERGE (b:Video {video_id: $b})
          MERGE (a)-[e:CO_TRENDS {region: $region}]->(b)
          SET e.weight = $weight
        $$, $1) AS (v agtype)
    """
    params = json.dumps({"a": a_id, "b": b_id, "weight": weight, "region": region})
    cur.execute(cypher, (params,))


def drain_outbox():
    lite = sqlite3.connect(SQLITE_PATH)
    lite.row_factory = sqlite3.Row
    pg = psycopg2.connect(PG_DSN)
    cur = pg.cursor()
    prime(cur)

    rows = lite.execute(
        "SELECT id, payload FROM graph_outbox "
        "WHERE processed = 0 ORDER BY id LIMIT 500"
    ).fetchall()

    for row in rows:
        event = json.loads(row["payload"])
        if event["type"] == "co_trends":
            upsert_cotrend(
                cur, event["a"], event["b"], event["weight"], event["region"]
            )
        lite.execute("UPDATE graph_outbox SET processed = 1 WHERE id = ?", (row["id"],))

    pg.commit()
    lite.commit()
    print(f"drained {len(rows)} events")


if __name__ == "__main__":
    drain_outbox()

The worker runs every few minutes from the same cron that fetches trending videos. If Postgres is down, the outbox rows simply stay processed = 0 and drain when it recovers. The SQLite side never blocks, so an AGE outage degrades the recommendation sidebar to a simpler SQLite fallback rather than taking the site down.

Caching the results at the edge

Graph traversals are cheaper than recursive CTEs but they are not free — a three-hop query on a hot node still costs single-digit to low-double-digit milliseconds. Since related-videos results only change when the graph changes (every few minutes at most), they cache beautifully. Cloudflare Workers already sit in front of every route, so the related-videos fragment gets an edge cache keyed by video_id plus segment, with a short TTL and stale-while-revalidate so a viewer never waits on a cold graph query.

export default {
  async fetch(request, env, ctx) {
    const url = new URL(request.url);
    if (!url.pathname.startsWith("/api/related/")) {
      return fetch(request);
    }

    const cache = caches.default;
    const cacheKey = new Request(url.toString(), request);
    let response = await cache.match(cacheKey);
    if (response) return response;

    response = await fetch(request);
    if (response.ok) {
      response = new Response(response.body, response);
      // Graph changes at most every few minutes; serve stale while we refresh.
      response.headers.set(
        "Cache-Control",
        "public, max-age=180, stale-while-revalidate=600"
      );
      ctx.waitUntil(cache.put(cacheKey, response.clone()));
    }
    return response;
  },
};

With the edge cache in place, the AGE database only sees traffic for cold keys — typically a few queries per second even during a viral spike, which a modest Postgres instance handles without breaking a sweat.

What I would tell you before you migrate

A few hard-won notes after running this in production for a European viral-video workload:

Do not move your whole app. AGE earns its keep on genuinely recursive, path-shaped queries. Everything else stayed in SQLite and is better for it. A graph engine is a specialized tool, not a general upgrade.
Treat the graph as a projection. Making SQLite the source of truth and AGE a rebuildable read model meant an AGE outage is a degraded feature, not an incident.
The agtype tax is real. Every query returns JSON-encoded values you must decode, and every session needs LOAD 'age'. Wrap it once, forget it forever.
Index the extracted property, not the vertex. AGE won't use an index on the raw properties column; you index agtype_access_operator(properties, ...) explicitly.
Cap your hop count. *1..3 is deliberate. Unbounded * traversals on a dense trend graph will find you eventually, and not in a good way.
Keep personal data out. Modeling RECOMMENDED against segments rather than users kept the graph free of personal data, which is one less system in GDPR scope.

The recursive join that started this is gone. The two-hop query that used to hit 900ms now returns in the low teens of milliseconds cold, and single digits warm behind the edge cache. More importantly, the query is readable — the next engineer who needs to add a fourth relationship type edits a Cypher pattern instead of decoding a recursive CTE. For one narrow, graph-shaped subsystem sitting beside an otherwise deliberately boring PHP-and-SQLite stack, Apache AGE was exactly the right amount of new technology.

Building a Multi-Region Health-Check Aggregator for a Video CDN

ahmet gedik — Sun, 19 Jul 2026 00:00:01 +0000

One of our Singapore edge nodes started returning HTTP 200 for the manifest endpoint while quietly serving stale HLS segments for eleven minutes. Cloudflare's health check saw a 200 and kept routing traffic. Our uptime monitor in Frankfurt saw a 200 and stayed green. Meanwhile viewers in Jakarta and Manila were staring at a spinning buffer because the origin shield in sin had wedged its segment cache. A single-region probe told us nothing was wrong. That incident is why I built a multi-region health-check aggregator that treats "is the CDN up" as a distributed question with a distributed answer.

I run the backend for TopVideoHub, an Asia-Pacific video aggregator serving trending clips across CJK and Southeast Asian markets. Our audience is spread across a dozen countries with wildly different last-mile conditions, and our CDN footprint has to match. A health check that only probes one PoP is worse than useless — it's actively misleading, because it gives you the confidence to page nobody while a quarter of your users can't play video. This post walks through the actual architecture: how the probes run, how results get aggregated with a quorum model instead of a naive average, how I store and query the time series in SQLite, and the Go worker that does the concurrent regional probing.

Why a 200 Is Not a Health Signal for Video

Text sites can mostly get away with checking that / returns 200. Video delivery cannot, for three reasons that bit us repeatedly:

The manifest lies about the segments. An HLS .m3u8 playlist is just text. It can return 200 and validate perfectly while the .ts or .m4s segments it references 404 or time out at the edge. You have to fetch a segment to know the pipe is real.
Freshness matters more than availability. For live and near-live content, a segment that's 40 seconds stale is a failure even though every request succeeds. A pure up/down check has no vocabulary for "technically serving, but serving the past."
Geography is the whole problem. A probe from the same datacenter as the origin will almost never reproduce what a mobile user in Ho Chi Minh City experiences going through three transit networks and a congested peering point.

So my health signal is a tuple, not a boolean: (reachable, manifest_valid, segment_fetchable, segment_age_seconds, ttfb_ms). Each probe returns that tuple, and the aggregator's job is to turn many regional tuples into one honest verdict.

The Probe: What One Check Actually Does

Each probe targets a specific (region, edge_hostname) pair. It does a small chain of dependent requests, because the failure modes are ordered — there's no point checking segment freshness if the manifest didn't parse. Here's the core probe logic in Python, which is what I prototyped with before moving the hot path to Go:

import re
import time
import httpx

SEGMENT_RE = re.compile(r'^(?!#)(.+\.(?:ts|m4s))\s*$', re.MULTILINE)

def probe_edge(base_url: str, manifest_path: str, timeout: float = 6.0) -> dict:
    """Probe one CDN edge. Returns a health tuple as a dict."""
    result = {
        "reachable": False,
        "manifest_valid": False,
        "segment_fetchable": False,
        "segment_age_s": None,
        "ttfb_ms": None,
        "error": None,
    }
    manifest_url = base_url.rstrip("/") + manifest_path
    try:
        with httpx.Client(timeout=timeout, follow_redirects=True) as client:
            t0 = time.perf_counter()
            m = client.get(manifest_url, headers={"Cache-Control": "no-cache"})
            result["ttfb_ms"] = round((time.perf_counter() - t0) * 1000, 1)
            result["reachable"] = True
            if m.status_code != 200 or "#EXTM3U" not in m.text[:64]:
                result["error"] = f"manifest status {m.status_code}"
                return result
            result["manifest_valid"] = True

            segments = SEGMENT_RE.findall(m.text)
            if not segments:
                result["error"] = "no segments in manifest"
                return result

            # Fetch the newest segment (last in the playlist) and check freshness.
            seg_url = _resolve(manifest_url, segments[-1])
            s = client.get(seg_url, headers={"Range": "bytes=0-1023"})
            if s.status_code not in (200, 206):
                result["error"] = f"segment status {s.status_code}"
                return result
            result["segment_fetchable"] = True

            last_mod = s.headers.get("Last-Modified") or s.headers.get("Date")
            if last_mod:
                age = time.time() - _http_date_to_epoch(last_mod)
                result["segment_age_s"] = round(max(age, 0), 1)
    except httpx.TimeoutException:
        result["error"] = "timeout"
    except httpx.HTTPError as e:
        result["error"] = f"http error: {e.__class__.__name__}"
    return result

Two details earn their keep here. First, the Range: bytes=0-1023 header — I don't need the whole segment to prove the edge can serve it, just the first kilobyte. On a busy schedule fetching full segments from every region would waste bandwidth and skew latency numbers. Second, the freshness check leans on Last-Modified, falling back to Date. Not every CDN sets Last-Modified on segments, and when it's missing I record segment_age_s as null and let the aggregator treat freshness as "unknown" rather than "failed" — a distinction that matters, as we'll see.

Aggregation: Quorum, Not Average

The naive move is to average the regions: five green, one red, 83% healthy, ship it. That's exactly the logic that let our Singapore incident stay silent, because from a global average one bad PoP disappears into the noise. The right question is not "what fraction of probes passed" but "is any region my users care about currently broken, and do enough independent probes agree."

So I use a weighted quorum with two ideas baked in:

Weights by audience. A failing edge in Tokyo or Seoul matters far more to us than one in São Paulo, because that's where our traffic lives. Each region carries an audience weight, and the verdict is computed against weighted thresholds.
Quorum against flapping. A single probe failing once is almost always a transient network hiccup between the prober and the edge, not a real outage. I require agreement — at least N of the last M probes for a region must fail before that region is declared down. This kills the pager-at-3am false positives.

Here's the aggregation in PHP 8.4, which is where the rest of our stack lives (LiteSpeed in front, SQLite underneath):

<?php
declare(strict_types=1);

enum EdgeVerdict: string
{
    case Healthy = 'healthy';
    case Degraded = 'degraded';
    case Down = 'down';
}

final readonly class RegionHealth
{
    public function __construct(
        public string $region,
        public float $audienceWeight,   // 0.0 - 1.0, sums to ~1 across regions
        public int $recentFailures,     // failing probes in the window
        public int $windowSize,         // total probes in the window
        public ?float $worstSegmentAge, // seconds, null if all unknown
    ) {}

    public function isDown(int $quorum): bool
    {
        // Require a quorum of failures, not a single blip.
        return $this->recentFailures >= $quorum;
    }

    public function isStale(float $maxAgeSeconds): bool
    {
        return $this->worstSegmentAge !== null
            && $this->worstSegmentAge > $maxAgeSeconds;
    }
}

final class HealthAggregator
{
    public function __construct(
        private int $failureQuorum = 3,      // 3 of last N probes
        private float $maxSegmentAge = 30.0, // seconds before "stale"
        private float $downWeightBudget = 0.15, // >15% weighted-down = global down
    ) {}

    /** @param list<RegionHealth> $regions */
    public function aggregate(array $regions): array
    {
        $downWeight = 0.0;
        $staleWeight = 0.0;
        $perRegion = [];

        foreach ($regions as $r) {
            $verdict = EdgeVerdict::Healthy;
            if ($r->isDown($this->failureQuorum)) {
                $verdict = EdgeVerdict::Down;
                $downWeight += $r->audienceWeight;
            } elseif ($r->isStale($this->maxSegmentAge)) {
                $verdict = EdgeVerdict::Degraded;
                $staleWeight += $r->audienceWeight;
            }
            $perRegion[$r->region] = $verdict->value;
        }

        $global = match (true) {
            $downWeight >= $this->downWeightBudget => EdgeVerdict::Down,
            $downWeight > 0 || $staleWeight > 0.10 => EdgeVerdict::Degraded,
            default => EdgeVerdict::Healthy,
        };

        return [
            'global' => $global->value,
            'down_weight' => round($downWeight, 3),
            'stale_weight' => round($staleWeight, 3),
            'regions' => $perRegion,
        ];
    }
}

The downWeightBudget is the honest lever. Set it to 0.15 and you're saying: if regions carrying more than 15% of our audience are down, that's a global incident, page someone. A single small-audience PoP failing shows up as degraded — visible on the dashboard, logged, but not waking anyone. This mapping from technical state to human urgency is the entire point of the aggregator, and getting the weights right took a few weeks of tuning against real traffic distribution from our analytics.

Storing the Time Series in SQLite

We don't run a dedicated time-series database. For a health-check history at our volume — a few dozen regions probed every 30 seconds — SQLite is more than enough, and it means one fewer moving part to operate. The schema is deliberately boring:

CREATE TABLE IF NOT EXISTS probe_results (
    id            INTEGER PRIMARY KEY,
    region        TEXT    NOT NULL,
    edge_host     TEXT    NOT NULL,
    checked_at    INTEGER NOT NULL,   -- unix epoch seconds
    reachable     INTEGER NOT NULL,
    manifest_ok   INTEGER NOT NULL,
    segment_ok    INTEGER NOT NULL,
    segment_age_s REAL,               -- nullable: unknown freshness
    ttfb_ms       REAL,
    error         TEXT
);

CREATE INDEX IF NOT EXISTS idx_probe_region_time
    ON probe_results (region, checked_at DESC);

The index on (region, checked_at DESC) is what makes the windowed quorum query cheap. To build the RegionHealth objects the aggregator needs, I pull the last N probes per region and let SQLite do the counting:

-- Last 5 probes per region, collapsed into a health summary.
WITH windowed AS (
    SELECT
        region,
        checked_at,
        (reachable = 0 OR manifest_ok = 0 OR segment_ok = 0) AS failed,
        segment_age_s,
        ROW_NUMBER() OVER (
            PARTITION BY region ORDER BY checked_at DESC
        ) AS rn
    FROM probe_results
    WHERE checked_at > unixepoch() - 600
)
SELECT
    region,
    SUM(failed)              AS recent_failures,
    COUNT(*)                 AS window_size,
    MAX(segment_age_s)       AS worst_segment_age
FROM windowed
WHERE rn <= 5
GROUP BY region;

A couple of operational notes that saved me pain. I keep the probe table in WAL mode so the Go writer and the PHP reader don't block each other — writers append, readers get a consistent snapshot. And I run a nightly DELETE FROM probe_results WHERE checked_at < unixepoch() - 1209600 to keep two weeks of history, followed by an occasional VACUUM. Fourteen days is plenty for incident forensics; anything older gets rolled up into a daily summary table before deletion. On the same box we also run our search layer on SQLite's FTS5 with a CJK-aware tokenizer, so keeping the health data in the same engine means one backup story, one connection pool, one thing to reason about.

The Concurrent Prober in Go

The Python probe is fine for a handful of edges, but once you're probing 30 regions on a 30-second cadence you want real concurrency without spawning a process per check. This is where Go shines, and it's what actually runs in production. The worker fans out to every edge concurrently, bounds the parallelism with a semaphore so we don't nuke our own network, and writes results straight into the SQLite table:

package main

import (
    "context"
    "database/sql"
    "net/http"
    "strings"
    "sync"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

type Edge struct {
    Region    string
    Host      string
    ManifestURL string
}

type Result struct {
    Edge       Edge
    Reachable  bool
    ManifestOK bool
    SegmentOK  bool
    TTFBms     float64
    Err        string
}

func probe(ctx context.Context, client *http.Client, e Edge) Result {
    res := Result{Edge: e}
    req, _ := http.NewRequestWithContext(ctx, http.MethodGet, e.ManifestURL, nil)
    req.Header.Set("Cache-Control", "no-cache")

    start := time.Now()
    resp, err := client.Do(req)
    if err != nil {
        res.Err = err.Error()
        return res
    }
    defer resp.Body.Close()
    res.TTFBms = float64(time.Since(start).Microseconds()) / 1000.0
    res.Reachable = true
    if resp.StatusCode == http.StatusOK {
        res.ManifestOK = true
        // Real code parses the manifest and range-fetches a segment here;
        // omitted for length. Set res.SegmentOK from that fetch.
        res.SegmentOK = true
    }
    return res
}

func runRound(ctx context.Context, db *sql.DB, edges []Edge) error {
    client := &http.Client{Timeout: 6 * time.Second}
    sem := make(chan struct{}, 12) // cap concurrent probes
    results := make(chan Result, len(edges))
    var wg sync.WaitGroup

    for _, e := range edges {
        wg.Add(1)
        go func(e Edge) {
            defer wg.Done()
            sem <- struct{}{}
            defer func() { <-sem }()
            results <- probe(ctx, client, e)
        }(e)
    }
    go func() { wg.Wait(); close(results) }()

    tx, err := db.Begin()
    if err != nil {
        return err
    }
    stmt, err := tx.Prepare(`INSERT INTO probe_results
        (region, edge_host, checked_at, reachable, manifest_ok, segment_ok, ttfb_ms, error)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)`)
    if err != nil {
        tx.Rollback()
        return err
    }
    defer stmt.Close()

    now := time.Now().Unix()
    for r := range results {
        _, err := stmt.Exec(r.Edge.Region, r.Edge.Host, now,
            b2i(r.Reachable), b2i(r.ManifestOK), b2i(r.SegmentOK),
            r.TTFBms, strings.TrimSpace(r.Err))
        if err != nil {
            tx.Rollback()
            return err
        }
    }
    return tx.Commit()
}

func b2i(b bool) int {
    if b {
        return 1
    }
    return 0
}

The semaphore capped at 12 is deliberate. Early on I let all 30 probes fire at once and immediately started getting rate-limited by our own CDN's edge WAF, which read the burst as a scan. Bounding concurrency to a dozen keeps the round finishing in about two seconds while looking like normal traffic. Wrapping the whole round's inserts in a single transaction turns 30 individual writes into one commit, which on SQLite is the difference between a snappy writer and a lock-contention nightmare — SQLite serializes writes, so batching them is not optional at this cadence.

Where the Probes Actually Run

Code is the easy part; probe placement is what makes the data trustworthy. A prober sitting in the same cloud region as your origin will report rosy numbers that no real user experiences. My rule is that probes must run from networks that resemble the audience. Concretely:

Cheap VMs in target regions. A small instance in Tokyo, Singapore, and Mumbai from a couple of different providers covers most of our APAC footprint. Different providers matter because they take different transit paths.
Cloudflare Workers as a secondary vantage. Running a lightweight version of the probe from Workers gives me hundreds of effective locations at low cost, and it checks the path through Cloudflare's own network — which is what most of our users traverse anyway.
Never trust a single prober per region. Two independent probers per region is the minimum that lets the quorum logic distinguish "the edge is down" from "one prober's network is having a bad minute."

The aggregator doesn't care where a probe came from beyond its region label, but having two probers feed each region is what makes the failure-quorum meaningful. If both probers in sin report failures and a prober in nrt doesn't, that's a real Singapore edge problem — exactly the signal that would have caught our original eleven-minute stale-cache incident in seconds instead of minutes.

What Changed After Shipping This

The aggregator has been running for a few months now, and the concrete wins are boring in the best way:

Mean time to detection for regional issues dropped from ~10 minutes to under 90 seconds. The quorum window is three failing probes at a 30-second cadence, so a genuine outage trips the verdict fast while transient blips stay quiet.
False pages went to near zero. The audience-weighted budget means a low-traffic edge flapping shows up as degraded on the dashboard and never pages anyone at night.
Stale-cache incidents became visible for the first time. Before, a 200-serving-old-segments failure was invisible. Now segment_age_s catches it, and we've traced two separate origin-shield bugs we'd otherwise never have seen.

If you're delivering video across regions, the single most valuable change you can make is to stop treating health as a boolean and start treating it as a geographically-distributed, audience-weighted quorum. A 200 from one datacenter is not a health signal — it's an opinion from one vantage point. Fetch a real segment, check its age, weight the verdict by where your viewers actually are, and require a quorum before you believe the bad news. The whole thing fits in a Go worker, a SQLite table, and about a hundred lines of aggregation logic, and it will tell you the truth about your CDN far more often than your uptime monitor does.