DEV Community: ahmet gedik

Running NextDNS on Your Router for Ad-Free Video Discovery at DNS Layer

ahmet gedik — Fri, 03 Jul 2026 04:00:02 +0000

Last month a reader emailed me a screenshot of ViralVidVault loaded on his smart TV's built-in browser. The page worked, but he had counted nine third-party ad and tracker domains his TV was still resolving in the background — none of which came from us. We run a deliberately lean stack and ship no display ads, yet his network was lighting up like a Christmas tree because every other tab, app, and embedded SDK on that device was phoning home. He asked the obvious question: "Can I just block this stuff once, for the whole house, instead of installing an extension on every screen?"

The answer is yes, and the cleanest place to do it is the router, at the DNS layer, with NextDNS. This post is the engineering write-up I sent him, expanded. I run the backend for ViralVidVault, a GDPR-compliant European viral-video discovery service, so I care about two things that usually pull in opposite directions: blocking the surveillance-grade ad tech that pollutes video browsing, and not breaking the legitimate video CDNs and thumbnail hosts that make discovery feel instant. DNS-level blocking done badly does both. Done well, it's the highest-leverage privacy change you can make for an entire network.

Why the router, and why DNS specifically

Browser extensions like uBlock Origin are excellent, but they have a coverage problem. They protect exactly one browser on exactly one device. The moment you open a video in a native app, a smart TV, a console, an IoT picture frame, or a guest's phone, you're unprotected. There is no extension surface on a Roku.

DNS filtering works one layer down. Before any of those devices can connect to doubleclick.net or some fingerprinting endpoint, it has to resolve the hostname to an IP. If your router hands DNS to a filtering resolver, the resolution simply fails (returns 0.0.0.0 or NXDOMAIN) and the connection never opens. One configuration point, every device covered, including the ones you can't install software on.

The tradeoffs are real and worth stating up front:

DNS blocking is domain-granular, not URL-granular. You can block ads.example.com but not example.com/ads. For first-party ads served from the same domain as content, DNS can't help — that's where a browser extension still earns its place.
It can't see inside encrypted traffic. It only sees the hostname being looked up. That's enough for blocking but not for cosmetic filtering (hiding empty ad boxes).
Misconfigured blocklists break things. Over-aggressive lists kill video thumbnails, CDN shards, and login flows. The whole back half of this article is about avoiding that.

NextDNS specifically is attractive because it's a hosted resolver with a real API, per-profile configuration, query logging you can export, and support for encrypted transport (DoH/DoT/DoQ). You get the control of a self-hosted Pi-hole without running the Pi.

The DNS resolution path you're actually changing

Before touching anything, it helps to see what a lookup looks like and where the block lands. Here's a tiny Go program that resolves a hostname against a specific resolver so you can compare your default resolver against NextDNS and confirm blocking is live.

package main

import (
    "context"
    "fmt"
    "net"
    "os"
    "time"
)

// resolveVia queries a specific DNS server for a hostname's A records.
func resolveVia(server, host string) ([]string, error) {
    r := &net.Resolver{
        PreferGo: true,
        Dial: func(ctx context.Context, network, _ string) (net.Conn, error) {
            d := net.Dialer{Timeout: 3 * time.Second}
            return d.DialContext(ctx, network, server+":53")
        },
    }
    ctx, cancel := context.WithTimeout(context.Background(), 4*time.Second)
    defer cancel()
    return r.LookupHost(ctx, host)
}

func main() {
    host := "doubleclick.net"
    if len(os.Args) > 1 {
        host = os.Args[1]
    }
    // Compare a normal resolver against NextDNS (anycast IPs).
    for _, srv := range []string{"1.1.1.1", "45.90.28.0"} {
        ips, err := resolveVia(srv, host)
        if err != nil {
            fmt.Printf("%-15s %s -> ERROR/blocked: %v\n", srv, host, err)
            continue
        }
        fmt.Printf("%-15s %s -> %v\n", srv, host, ips)
    }
}

Run it against a known ad domain and against i.ytimg.com (YouTube thumbnails). A correctly tuned profile returns 0.0.0.0 or an error for the first and real IPs for the second. If your thumbnails resolve to nothing, your blocklist is too aggressive and you've found the problem before users do.

Setting up the NextDNS profile

Create a profile at my.nextdns.io. You'll get a config ID (six hex characters) and a set of endpoints. The configuration that matters for video discovery breaks into three knobs.

Blocklists. Start conservative. I run exactly three:

NextDNS Ads & Trackers — the built-in list, well maintained, low false-positive rate.
OISD Small — a curated list that deliberately avoids breaking common services. The "Small" variant is the right call; the full variant blocks things you'll miss.
AdGuard DNS filter — good coverage of in-app and mobile ad SDKs that desktop-focused lists miss.

Resist the urge to enable a dozen lists. Blocklist overlap gives you diminishing returns and rising false positives. Two or three good lists beat ten aggressive ones every time.

Native tracking protection. NextDNS has toggles for blocking telemetry from Samsung, LG, Roku, Apple, Windows, and others. For a TV-heavy household this is the single most effective switch — smart TVs are among the most aggressive trackers in the home, and these are the devices you cannot put an extension on.

Denylist / Allowlist. This is your manual override and it's where you keep video discovery working. Pre-seed the allowlist with the hosts your video sources need before anyone complains.

Keeping video CDNs in the allowlist

This is the part most write-ups skip, and it's the part that determines whether your family curses your name. Video discovery depends on a sprawl of thumbnail hosts, manifest servers, and segment CDNs. Block one by accident and a whole category of content silently goes blank.

Here's the allowlist I maintain, and the reasoning:

i.ytimg.com, i9.ytimg.com — YouTube thumbnail shards. Blocking these blanks every grid.
*.googlevideo.com — the actual video segment CDN. Non-negotiable.
*.cdn.vine.co, video.twimg.com — short-form video hosts referenced by trend feeds.
*.cdninstagram.com, scontent.cdninstagram.com — Instagram media; some trackers share parent domains, so test carefully.
Your own asset domains. For us that's the Cloudflare-fronted CDN serving ViralVidVault thumbnails.

The failure mode is subtle: a blocklist blocks metrics.somecdn.net, but the same provider also serves media.somecdn.net from a wildcard rule, and now your video is gone. Allowlist entries in NextDNS take precedence over blocklists, so an explicit allow is your safety valve.

You can script the comparison instead of eyeballing the query log. This Python snippet pulls your NextDNS logs via the API and flags any blocked domain that looks like a video CDN — the early-warning system for over-blocking.

import os
import sys
import requests

# NextDNS API: https://api.nextdns.io
API_KEY = os.environ["NEXTDNS_API_KEY"]
PROFILE = os.environ["NEXTDNS_PROFILE"]

# Substrings that indicate a domain we almost certainly want resolving.
VIDEO_HINTS = (
    "ytimg", "googlevideo", "cdninstagram", "twimg",
    "vimeocdn", "video", "media", "thumb", "img", "cdn",
)

def fetch_blocked(limit=1000):
    url = f"https://api.nextdns.io/profiles/{PROFILE}/logs"
    headers = {"X-Api-Key": API_KEY}
    params = {"limit": limit, "status": "blocked"}
    resp = requests.get(url, headers=headers, params=params, timeout=15)
    resp.raise_for_status()
    return resp.json().get("data", [])

def main():
    suspicious = {}
    for entry in fetch_blocked():
        domain = entry.get("domain", "")
        if any(h in domain for h in VIDEO_HINTS):
            suspicious[domain] = suspicious.get(domain, 0) + 1
    if not suspicious:
        print("No video-like domains blocked. Clean.")
        return
    print("Review these — possible false positives:")
    for domain, count in sorted(suspicious.items(), key=lambda x: -x[1]):
        print(f"  {count:5d}  {domain}")

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

Run it on a cron once a day. Anything it surfaces is a candidate for the allowlist. I run the equivalent of this against our own analytics pipeline: when our European users report blank thumbnails, the first thing I check is whether a popular DNS blocklist started catching one of our CDN shards.

Configuring the router

There are three ways to point your router at NextDNS, in increasing order of robustness.

1. Plain DNS (DHCP). In your router's DHCP/DNS settings, replace the upstream resolvers with the two NextDNS anycast IPs shown in your profile (they're linked to your config ID). Easiest, works on every router, but unencrypted and your ISP can see (and some hijack) port 53.

2. DNS-over-TLS / DNS-over-HTTPS. If your router runs OpenWrt, OPNsense, or has native DoH/DoT (many do now), use the encrypted endpoint. This encrypts lookups and is the configuration I recommend. On OpenWrt this is a few lines of dnsmasq plus https-dns-proxy.

3. Linked IP for plain-DNS profiles. NextDNS ties your profile to your home's public IP so the anycast IPs apply your specific rules. Because home IPs change, NextDNS gives you an updater endpoint. Don't install yet another daemon — your router can hit it on a schedule. Here's a minimal PHP updater you can run from cron on any always-on box (a NAS, a Pi, or in my case a small LiteSpeed/PHP 8.4 box that already runs other household jobs).

<?php
declare(strict_types=1);

// Keeps the NextDNS "linked IP" pointed at your current public address.
// Run from cron, e.g. every 15 minutes:  */15 * * * *  php /opt/nextdns/update.php

const UPDATE_URL = 'https://link-ip.nextdns.io/abc123/your-secret-token';
const STATE_FILE = '/var/lib/nextdns/last_ip.txt';

function currentPublicIp(): ?string
{
    $ctx = stream_context_create(['http' => ['timeout' => 5]]);
    $ip  = @file_get_contents('https://api.ipify.org', false, $ctx);
    $ip  = is_string($ip) ? trim($ip) : '';
    return filter_var($ip, FILTER_VALIDATE_IP) ? $ip : null;
}

function lastKnownIp(): ?string
{
    if (!is_file(STATE_FILE)) {
        return null;
    }
    $v = trim((string) file_get_contents(STATE_FILE));
    return $v !== '' ? $v : null;
}

$ip = currentPublicIp();
if ($ip === null) {
    fwrite(STDERR, "could not determine public IP\n");
    exit(1);
}

if ($ip === lastKnownIp()) {
    // No change — don't hammer the endpoint.
    exit(0);
}

$resp = @file_get_contents(UPDATE_URL, false, stream_context_create([
    'http' => ['timeout' => 8, 'ignore_errors' => true],
]));

if ($resp === false) {
    fwrite(STDERR, "update request failed\n");
    exit(1);
}

@mkdir(dirname(STATE_FILE), 0750, true);
file_put_contents(STATE_FILE, $ip, LOCK_EX);
fwrite(STDOUT, "linked IP updated to {$ip}\n");

The state-file check matters: without it you'd POST to the link endpoint every cron tick whether the IP changed or not. The same instinct that makes me cache aggressively in production — only do work when state actually changed — applies to a 30-line cron script. I store the last IP in a flat file here, the same way ViralVidVault keeps small bits of state in SQLite WAL mode instead of reaching for a heavier store. Right tool, right scale.

Verifying it works end to end

Claiming "it's blocking ads now" without checking is how you end up with a broken network and a false sense of security. Verify in three places.

At the resolver. Re-run the Go probe from earlier. Ad domains should fail; video CDNs should resolve.

On a device. Visit a deliberately ad-heavy page and watch it come back clean. Then — critically — open your actual video discovery flow and confirm thumbnails, autoplay previews, and playback all work. If anything is blank, check the query log immediately.

In the logs. The NextDNS dashboard shows blocked vs. allowed in real time. The number that matters isn't "percentage blocked" — a high block rate can mean you're breaking things. The number that matters is zero blocked video CDN domains combined with a healthy block count on tracker domains.

A quick verification checklist I hand to non-technical users:

Ad-heavy news site loads with visibly fewer ads — good.
YouTube/short-form thumbnails all render — good.
Video playback starts without buffering loops — good.
Smart TV home screen still works — good (native tracking blocking shouldn't break the UI).
Login flows on banking/shopping sites work — good (a common over-block casualty).

If all five pass, you're done. If any fail, it's almost always one allowlist entry away from fixed.

What this does and doesn't buy you for privacy

Being precise here matters, especially if you, like me, work under GDPR and have to think about data flows carefully.

DNS filtering at the router stops a large fraction of tracker connections from ever opening. That's genuine: a tracker that can't resolve its endpoint collects nothing. It also collapses a whole class of cross-device tracking, because the TV, phone, and laptop all share one filtered resolver.

What it does not do:

It doesn't stop first-party analytics served from the same domain as the site you're visiting. Our own analytics at ViralVidVault are first-party and privacy-preserving by design, and DNS blocking wouldn't touch them — which is the correct outcome, because they don't track you across the web.
It doesn't encrypt the content of your traffic; TLS already does that. It encrypts your lookups if you use DoH/DoT.
It doesn't make you anonymous. Your ISP still sees the IPs you connect to.

Think of it as removing the surveillance scaffolding around video browsing, not as a cloak. For a household, that's a huge, durable win for one afternoon of setup.

Conclusion

The reader who emailed me set this up over a weekend: a NextDNS profile with three blocklists, native TV tracking protection on, a short video-CDN allowlist, DoT on his OpenWrt router, and the PHP cron keeping his linked IP fresh. His follow-up screenshot showed those nine background tracker domains down to zero, with every thumbnail intact.

The engineering lesson generalizes beyond DNS. Block at the lowest layer that gives you full coverage (the router), keep your blocklists conservative and your allowlist explicit, and never trust a change you haven't verified on a real device. The same discipline I apply to caching and edge config for European video discovery — do the work once, at the right layer, and prove it works — is exactly what makes a home network both ad-free and unbroken. Set it up, run the verification checklist, and let it quietly do its job on every screen in the house.

Coordinating Video Region Routers With etcd Distributed Configuration

ahmet gedik — Thu, 02 Jul 2026 17:00:01 +0000

The 2 AM failover we couldn't fix without a deploy

Last March our Singapore origin started serving stale trending lists to the Japanese and Korean edges. The fix was trivial — reroute jp and kr traffic to our Tokyo pull node and drop the weight on Singapore to zero. The problem was how. Our region routing table lived in a PHP config array baked into the deploy artifact. Changing one weight meant a git commit, a CI run, an FTP push to four LiteSpeed nodes, and a Cloudflare cache purge. Twenty minutes of latency on a routing decision that should take two seconds.

At TopVideoHub we aggregate trending video across thirteen Asia-Pacific markets, each with its own language edge — Japanese, Korean, Traditional and Simplified Chinese, Thai, Vietnamese, and more. A "region router" decides which origin node pulls trending data for a given market, how heavily to weight each candidate origin, and which fallback to use when an origin degrades. That decision changes constantly: an origin gets rate-limited, a CJK tokenizer index rebuild slows a node, a holiday traffic spike shifts the optimal topology. Configuration that can only change at deploy time is configuration that's always slightly wrong.

This is the story of moving that routing table into etcd, and the concrete patterns — watches, leases, transactions, and a polyglot read path — that made it safe to change live.

Why etcd and not a database flag table

The obvious alternative was a routing_config table in SQLite or a shared MySQL instance. We already run SQLite with an FTS5 CJK tokenizer for search, so adding a config table costs nothing. But config-in-a-database has two failure modes that bit us before:

Polling latency and load. Every router node has to poll the table on an interval. Poll too often and you hammer the DB; poll too rarely and a routing change takes a minute to propagate. There's no clean push.
No coordination primitives. When two operators (or two automated controllers) write at once, last-write-wins silently clobbers one change. There's no atomic compare-and-swap across the cluster, no lease that expires when a node dies.

etcd is built for exactly this. It gives us:

Watches — a long-lived stream that pushes the new value the instant a key changes, so propagation is sub-second instead of poll-interval-bound.
Leases — a TTL attached to a key, so an origin node that goes silent automatically drops out of the routing table when its lease expires.
Transactions — atomic compare-and-swap on a key revision, so concurrent writers can't clobber each other.
A linearizable, Raft-backed store — every node reads a consistent view; there is no split-brain on the config itself.

We were not trying to replace our data plane. etcd holds control-plane data: a few kilobytes of routing weights and origin health that change often and must be correct everywhere at once. The video payloads stay in SQLite and on the origins.

Modeling the region routing config

We namespace everything under /topvideohub/routing/. The layout is deliberately flat so a single prefix watch covers the whole config:

/topvideohub/routing/edge/<market> — the routing policy for one market edge, a JSON document: ordered candidate origins with weights and a fallback.
/topvideohub/routing/origin/<id>/health — a lease-bound key each origin node writes to prove it is alive.
/topvideohub/routing/version — a monotonic integer bumped on every coherent config change, used as a sanity check by readers.

An edge document looks like this:

{
  "market": "jp",
  "language": "ja",
  "candidates": [
    {"origin": "tokyo-1", "weight": 70},
    {"origin": "singapore-1", "weight": 30}
  ],
  "fallback": "singapore-1",
  "min_healthy_weight": 50
}

The min_healthy_weight field is a guard: if the live, healthy candidates don't sum to at least that weight, the router refuses to trust the document and uses the fallback. That single field has saved us from routing all of Japan to a node that had silently lost its lease.

Seeding and validating config with Python

We never write raw JSON into etcd by hand. Every change goes through a small Python tool that validates the document against a schema before the write and uses an atomic transaction so a concurrent edit can't be lost. We use the etcd3 client.

import json
import sys
import etcd3

SCHEMA_KEYS = {"market", "language", "candidates", "fallback", "min_healthy_weight"}
VALID_ORIGINS = {"tokyo-1", "tokyo-2", "singapore-1", "seoul-1", "taipei-1"}


def validate(doc: dict) -> None:
    missing = SCHEMA_KEYS - doc.keys()
    if missing:
        raise ValueError(f"missing keys: {missing}")
    if not doc["candidates"]:
        raise ValueError("candidates must be non-empty")
    total = 0
    for c in doc["candidates"]:
        if c["origin"] not in VALID_ORIGINS:
            raise ValueError(f"unknown origin {c['origin']}")
        if not 0 <= c["weight"] <= 100:
            raise ValueError(f"weight out of range for {c['origin']}")
        total += c["weight"]
    if total != 100:
        raise ValueError(f"weights must sum to 100, got {total}")
    if doc["fallback"] not in VALID_ORIGINS:
        raise ValueError("fallback is not a valid origin")


def put_edge(client: etcd3.Etcd3Client, market: str, doc: dict) -> None:
    validate(doc)
    key = f"/topvideohub/routing/edge/{market}"
    payload = json.dumps(doc, ensure_ascii=False, separators=(",", ":"))

    # Atomic compare-and-swap on the current value so a concurrent
    # operator edit between our read and write is not silently lost.
    current, meta = client.get(key)
    success, _ = client.transaction(
        compare=[
            client.transactions.value(key) == (current or b"")
        ],
        success=[client.transactions.put(key, payload)],
        failure=[],
    )
    if not success:
        raise RuntimeError("key changed under us; re-read and retry")

    client.put("/topvideohub/routing/version", str(int(meta_rev(client)) + 1))
    print(f"wrote {key}")


def meta_rev(client: etcd3.Etcd3Client) -> str:
    v, _ = client.get("/topvideohub/routing/version")
    return (v or b"0").decode()


if __name__ == "__main__":
    doc = json.load(open(sys.argv[1], encoding="utf-8"))
    cli = etcd3.client(host="10.0.0.10", port=2379)
    put_edge(cli, doc["market"], doc)

Two things matter here. First, validation runs before the write, so a typo'd origin or weights that don't sum to 100 never reach the cluster. Second, the transaction with a value comparison is an atomic compare-and-swap — if another operator wrote between our read and our write, the transaction fails and we retry rather than clobber. ensure_ascii=False keeps the CJK language tags readable in etcdctl get output, which matters when you're debugging at 2 AM.

A Go region router that watches for changes

The router itself is a small Go service that runs next to each LiteSpeed node. It loads the full routing prefix once on startup, then opens a watch and applies deltas in-memory. Reads of the routing table are served from an atomic snapshot, so request-path lookups never touch etcd directly and never block on the network.

package router

import (
    "context"
    "encoding/json"
    "log"
    "strings"
    "sync/atomic"

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

const prefix = "/topvideohub/routing/edge/"

type Candidate struct {
    Origin string `json:"origin"`
    Weight int    `json:"weight"`
}

type Edge struct {
    Market           string      `json:"market"`
    Candidates       []Candidate `json:"candidates"`
    Fallback         string      `json:"fallback"`
    MinHealthyWeight int         `json:"min_healthy_weight"`
}

type Router struct {
    table atomic.Pointer[map[string]Edge]
}

func New(ctx context.Context, cli *clientv3.Client) (*Router, error) {
    r := &Router{}
    m := map[string]Edge{}

    // 1. Load the current state and capture the revision to watch from.
    resp, err := cli.Get(ctx, prefix, clientv3.WithPrefix())
    if err != nil {
        return nil, err
    }
    for _, kv := range resp.Kvs {
        r.applyInto(m, string(kv.Key), kv.Value)
    }
    r.table.Store(&m)

    // 2. Watch from resp.Header.Revision+1 so we miss nothing in the gap.
    go r.watch(ctx, cli, resp.Header.Revision+1)
    return r, nil
}

func (r *Router) watch(ctx context.Context, cli *clientv3.Client, rev int64) {
    wch := cli.Watch(ctx, prefix, clientv3.WithPrefix(), clientv3.WithRev(rev))
    for wr := range wch {
        if wr.Err() != nil {
            log.Printf("watch error, will reconnect: %v", wr.Err())
            return
        }
        // Copy-on-write: clone the current table, mutate, swap atomically.
        old := *r.table.Load()
        next := make(map[string]Edge, len(old))
        for k, v := range old {
            next[k] = v
        }
        for _, ev := range wr.Events {
            key := string(ev.Kv.Key)
            if ev.Type == clientv3.EventTypeDelete {
                delete(next, strings.TrimPrefix(key, prefix))
                continue
            }
            r.applyInto(next, key, ev.Kv.Value)
        }
        r.table.Store(&next)
    }
}

func (r *Router) applyInto(m map[string]Edge, key string, val []byte) {
    var e Edge
    if err := json.Unmarshal(val, &e); err != nil {
        log.Printf("skip bad edge doc %s: %v", key, err)
        return
    }
    m[strings.TrimPrefix(key, prefix)] = e
}

// Resolve picks an origin for a market given the set of healthy origins.
func (r *Router) Resolve(market string, healthy map[string]bool) string {
    m := *r.table.Load()
    e, ok := m[market]
    if !ok {
        return ""
    }
    healthyWeight := 0
    for _, c := range e.Candidates {
        if healthy[c.Origin] {
            healthyWeight += c.Weight
        }
    }
    if healthyWeight < e.MinHealthyWeight {
        return e.Fallback
    }
    // Highest-weight healthy candidate wins; deterministic and cheap.
    best, bestW := e.Fallback, -1
    for _, c := range e.Candidates {
        if healthy[c.Origin] && c.Weight > bestW {
            best, bestW = c.Origin, c.Weight
        }
    }
    return best
}

The important details:

Load-then-watch from the captured revision. We watch from resp.Header.Revision+1, the revision returned by the initial Get. This closes the race window where a change between the load and the watch would otherwise be missed.
Copy-on-write with atomic.Pointer. The request path calls Resolve, which reads an immutable snapshot via a single atomic load — no mutex contention on hot reads. The watch goroutine builds a new map and swaps the pointer.
Bad documents are skipped, not fatal. A malformed JSON value logs and is ignored rather than crashing the router. Combined with the Python-side validation, a bad write essentially can't happen, but defense in depth is cheap.
The min_healthy_weight guard falls back the moment live capacity drops below threshold.

Origin health with leases

Health is the other half. Each origin node holds an etcd lease and writes its health key under that lease. If the node dies, the lease expires and the key vanishes — no separate liveness check, no stale "healthy" flag. The router subscribes to the health prefix the same way it watches edges and feeds the live set into the healthy map passed to Resolve.

func keepAlive(ctx context.Context, cli *clientv3.Client, originID string) error {
    // 10s TTL: if the node stops renewing, it drops out within ~10s.
    lease, err := cli.Grant(ctx, 10)
    if err != nil {
        return err
    }
    key := "/topvideohub/routing/origin/" + originID + "/health"
    if _, err := cli.Put(ctx, key, "ok", clientv3.WithLease(lease.ID)); err != nil {
        return err
    }
    ch, err := cli.KeepAlive(ctx, lease.ID)
    if err != nil {
        return err
    }
    for range ch { // drain keepalive acks until ctx is cancelled
    }
    return ctx.Err()
}

With a 10-second TTL, an origin that crashes or is partitioned drops out of every router's healthy set within ten seconds, cluster-wide, with zero manual intervention. That is the property a database flag table simply cannot give you cheaply.

Reading the same config from PHP 8.4

Not everything is Go. Our admin panel and a handful of cron jobs run on PHP 8.4 under LiteSpeed, and they sometimes need to read the effective routing config — for example, to show operators which origin a market is currently pinned to. We do not run a watch from PHP; that's the router's job. PHP reads on demand through etcd's gRPC-gateway HTTP/JSON API, which is enabled by default on port 2379. Keys and values are base64-encoded on the wire.

<?php
declare(strict_types=1);

final class EtcdReader
{
    public function __construct(
        private readonly string $endpoint = 'http://10.0.0.10:2379',
    ) {}

    /** Range-read a prefix and return decoded key => value pairs. */
    public function getPrefix(string $prefix): array
    {
        // rangeEnd is the prefix with its last byte incremented — etcd's
        // convention for "all keys under this prefix".
        $rangeEnd = substr($prefix, 0, -1)
            . chr(ord(substr($prefix, -1)) + 1);

        $body = json_encode([
            'key'       => base64_encode($prefix),
            'range_end' => base64_encode($rangeEnd),
        ], JSON_THROW_ON_ERROR);

        $ch = curl_init($this->endpoint . '/v3/kv/range');
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_POSTFIELDS     => $body,
            CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 2,
        ]);
        $raw = curl_exec($ch);
        if ($raw === false) {
            throw new RuntimeException('etcd unreachable: ' . curl_error($ch));
        }
        curl_close($ch);

        $resp = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
        $out  = [];
        foreach ($resp['kvs'] ?? [] as $kv) {
            $key = base64_decode($kv['key']);
            $out[$key] = base64_decode($kv['value']);
        }
        return $out;
    }

    /** Effective origin for a market, mirroring the Go resolver. */
    public function resolveMarket(string $market, array $healthy): ?string
    {
        $pairs = $this->getPrefix('/topvideohub/routing/edge/');
        $key   = "/topvideohub/routing/edge/{$market}";
        if (!isset($pairs[$key])) {
            return null;
        }
        $edge = json_decode($pairs[$key], true, 512, JSON_THROW_ON_ERROR);

        $healthyWeight = 0;
        foreach ($edge['candidates'] as $c) {
            if ($healthy[$c['origin']] ?? false) {
                $healthyWeight += $c['weight'];
            }
        }
        if ($healthyWeight < $edge['min_healthy_weight']) {
            return $edge['fallback'];
        }

        $best = $edge['fallback'];
        $bestW = -1;
        foreach ($edge['candidates'] as $c) {
            if (($healthy[$c['origin']] ?? false) && $c['weight'] > $bestW) {
                $best = $c['origin'];
                $bestW = $c['weight'];
            }
        }
        return $best;
    }
}

A few PHP-specific notes from production:

Keep the curl timeout short (2s) and cache the result. A LiteSpeed worker should never block a page render on etcd. We cache the decoded prefix in APCu for a few seconds; the admin panel doesn't need sub-second freshness, and the router — which does — uses watches instead.
The resolveMarket logic is intentionally a line-for-line mirror of the Go Resolve. When two languages implement the same decision, drift is the enemy. We have a contract test that feeds both implementations the same fixtures and asserts identical output.
Always base64_decode both key and value. This trips up everyone the first time they hit the etcd HTTP gateway.

Guarding against split-brain and bad writes

etcd's Raft consensus means the store never splits its brain — a minority partition can't accept writes. But your application still can, so we layered on a few rules:

All writes go through the validating Python tool or an equivalent server endpoint. No etcdctl put of raw JSON in production. Validation before write is non-negotiable.
Compare-and-swap on every edge write, so concurrent operator edits fail loudly instead of clobbering.
Readers tolerate missing or malformed keys and fall back rather than crash. The router skips bad docs; PHP returns null and the caller shows "unknown."
Run an odd-sized etcd cluster (we run three nodes across two availability zones) so quorum is well-defined. A two-node cluster has no fault tolerance for writes — lose one and you lose quorum.
Watch reconnection is mandatory. Our watch goroutine returns on error; a supervising loop re-creates the client and reloads from the current revision. A router that silently stops receiving updates is worse than one that crashes.

We also keep a Cloudflare and LiteSpeed cache consideration in mind: the routing decision happens behind the cache, at origin-pull time, not per-request. Changing a routing weight does not require a cache purge, because cached pages are language-edge content, not routing state. Decoupling those two was part of why this migration was worth doing.

What we learned

Moving region routing from a baked-in PHP array to etcd changed our operational tempo more than any single performance optimization we've shipped. A routing change that used to be a twenty-minute deploy is now a two-second validated write that propagates to every node before you've switched terminal tabs.

The principles that generalized beyond our specific stack:

Put control-plane data in etcd, keep the data plane where it is. etcd is for kilobytes of fast-changing, must-be-consistent config — not for video payloads or search indexes. SQLite with the CJK tokenizer still owns the data plane.
Watches beat polling for anything that must propagate fast across many nodes, and leases give you liveness for free.
Validate before you write, and tolerate bad reads. Defense on both ends makes live config changes boring, which is exactly what you want at 2 AM.
When two languages read the same config, test that they agree. A contract test against shared fixtures kept our Go and PHP resolvers from drifting.

If you're running a multi-region service and your routing topology still lives in a deploy artifact, the next incident will remind you why it shouldn't. etcd is a small, sharp tool for exactly this problem.

Multi-Tenant Video Platform Schema Design in PostgreSQL With RLS

ahmet gedik — Thu, 02 Jul 2026 09:00:00 +0000

When DailyWatch began indexing trending videos for partner publishers, each partner was just a partner_id column bolted onto a handful of tables. It held together until a cache key collision served one partner's "trending" rail to another. Nothing catastrophic reached end users, but it was the moment I understood that "add a column and filter on it" is not a multi-tenancy strategy — it is a missing WHERE clause waiting to happen. We run a deliberately lean stack at DailyWatch (PHP 8.4, SQLite FTS5 for discovery, LiteSpeed, Cloudflare at the edge), but tenant metadata, billing, and per-publisher configuration live in PostgreSQL, and that is where isolation has to be enforced by the engine rather than trusted to application code.

This is the schema I wish I had designed on day one: shared tables, hard isolation through Row-Level Security, tenant-aware partitioning for the high-volume event stream, and the operational glue — connection pooling, migrations, cache keys — that makes it survive contact with production.

Three ways to isolate tenants, and why I picked one

There are really three options, and the trade-offs are concrete:

Database per tenant. Strongest isolation and trivial to reason about, but painful past a few dozen tenants. Connection counts explode, every migration fans out, and cross-tenant analytics become a federation problem.
Schema per tenant. One database, one search_path switch per request. Better connection reuse than separate databases, but pg_dump slows to a crawl with thousands of schemas and the catalog bloats.
Shared schema, tenant_id on every row, enforced by Row-Level Security. One set of tables, one migration, isolation enforced by the database instead of by every developer remembering a filter.

For a video discovery platform onboarding publishers continuously, the shared-schema model wins. The catch everyone warns you about — "one forgotten WHERE tenant_id = ? leaks data" — is exactly the failure mode RLS eliminates. The policy lives in the database; the application physically cannot read another tenant's rows, even when a query forgets the filter.

The core schema

Start with the tenant table and make tenant_id a first-class part of every dependent table's primary key. Compound keys (tenant_id, id) keep indexes naturally clustered per tenant and make later table partitioning straightforward. Compound foreign keys are the quiet hero here: a video can only reference a channel belonging to the same tenant, because the FK includes tenant_id on both sides.

CREATE TABLE tenant (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug        text NOT NULL UNIQUE,
    name        text NOT NULL,
    plan        text NOT NULL DEFAULT 'free',
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE channel (
    tenant_id   bigint NOT NULL REFERENCES tenant(id),
    id          bigint GENERATED ALWAYS AS IDENTITY,
    external_id text NOT NULL,          -- e.g. a YouTube channel id
    title       text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id),
    UNIQUE (tenant_id, external_id)
);

CREATE TABLE video (
    tenant_id    bigint NOT NULL REFERENCES tenant(id),
    id           bigint GENERATED ALWAYS AS IDENTITY,
    channel_id   bigint NOT NULL,
    external_id  text NOT NULL,
    title        text NOT NULL,
    duration_s   int  NOT NULL DEFAULT 0,
    published_at timestamptz,
    region       text NOT NULL DEFAULT 'US',
    created_at   timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id),
    UNIQUE (tenant_id, external_id),
    -- This is the line that makes cross-tenant references impossible:
    FOREIGN KEY (tenant_id, channel_id) REFERENCES channel(tenant_id, id)
);

CREATE INDEX video_trending_idx
    ON video (tenant_id, region, published_at DESC);

The video_trending_idx index leads with tenant_id on purpose. Every read path is already tenant-scoped, so the planner can use the index for the most common query — "latest videos for this tenant in this region" — without scanning across tenants.

Locking it down with Row-Level Security

RLS is where the design stops being a convention and becomes a guarantee. Enable it, then force it so even the table owner is subject to the policy, and run your application as a dedicated non-superuser role (superusers and BYPASSRLS roles skip policies entirely).

ALTER TABLE channel ENABLE ROW LEVEL SECURITY;
ALTER TABLE channel FORCE  ROW LEVEL SECURITY;
ALTER TABLE video   ENABLE ROW LEVEL SECURITY;
ALTER TABLE video   FORCE  ROW LEVEL SECURITY;

-- current_setting WITHOUT the 'missing_ok' flag throws if app.tenant_id
-- is unset. That is intentional: no tenant context means no rows, never
-- all rows. Fail closed.
CREATE POLICY tenant_isolation ON channel
    USING      (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

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

The USING clause filters what a query can see; the WITH CHECK clause filters what it can write. Without WITH CHECK, a tenant could INSERT a row stamped with someone else's tenant_id. With both, the row a tenant inserts must belong to that tenant, and the rows it selects or updates are silently constrained to its own. The application never writes WHERE tenant_id = ? again — and crucially, cannot opt out of it.

The one thing the application must do is set app.tenant_id correctly for every request. Get that wrong and you get zero rows, not someone else's rows. That asymmetry is the whole point.

Setting tenant context from PHP

Here is the request-scoping helper in PHP 8.4. The non-negotiable detail is SET LOCAL semantics — via set_config(name, value, is_local => true) — so the setting is bound to the transaction and dies on COMMIT/ROLLBACK. On a pooled connection, a plain SET would leak one tenant's context into the next request that reuses the socket.

<?php
declare(strict_types=1);

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

    /**
     * Run a unit of work scoped to a single tenant. The GUC is set with
     * is_local = true, so it is tied to this transaction and can never
     * outlive the request on a pooled connection.
     */
    public function scoped(int $tenantId, callable $work): mixed
    {
        $this->pdo->beginTransaction();
        try {
            // set_config is parameterisable; the bare SET command is not,
            // so this avoids string-concatenating the tenant id into SQL.
            $stmt = $this->pdo->prepare(
                "SELECT set_config('app.tenant_id', ?, true)"
            );
            $stmt->execute([(string) $tenantId]);

            $result = $work($this->pdo);

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

// Usage in a controller. Note: no tenant_id in the query — RLS adds it.
$videos = $tenant->scoped($currentTenantId, static function (PDO $db): array {
    $stmt = $db->prepare(
        'SELECT id, title FROM video
          WHERE region = :region
          ORDER BY published_at DESC
          LIMIT 40'
    );
    $stmt->execute([':region' => 'US']);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
});

Notice the query has no tenant_id predicate at all. A new engineer can write the most naive query imaginable and it is still isolated, because the policy is appended by the planner. That is the property you are buying: correctness that does not depend on discipline.

The connection pooling trap

This is the bug that bites everyone who adopts a transaction pooler (PgBouncer in transaction mode, or a language-level pool). In that mode a physical connection is handed to a different client after every transaction. If you ever run SET app.tenant_id = 5 without LOCAL, the GUC sticks to the socket and the next checkout inherits tenant 5. The fix is two-fold: always use the local/transaction-scoped form, and reset connections on return to the pool as a belt-and-suspenders measure.

import psycopg_pool


def _reset(conn) -> None:
    # Belt-and-suspenders: even a leaked GUC cannot survive a checkout.
    conn.execute("RESET ALL")
    conn.commit()


pool = psycopg_pool.ConnectionPool(
    "postgresql://app@db/dailywatch",
    min_size=4,
    max_size=20,
    reset=_reset,
)


def trending(tenant_id: int, region: str = "US") -> list[tuple]:
    with pool.connection() as conn:
        with conn.transaction():            # explicit txn => SET LOCAL scope
            conn.execute(
                "SELECT set_config('app.tenant_id', %s, true)",
                (str(tenant_id),),
            )
            cur = conn.execute(
                """
                SELECT id, title
                  FROM video
                 WHERE region = %s
                 ORDER BY published_at DESC
                 LIMIT 40
                """,
                (region,),
            )
            return cur.fetchall()

The reset callback runs RESET ALL before a connection re-enters the pool. Combined with set_config(..., true) inside an explicit transaction, you now have two independent guarantees that tenant context cannot leak. When the failure mode is "customer A sees customer B's data," two guarantees are not paranoia.

Partitioning the event firehose

Metadata tables stay small. The table that grows without bound is the watch/impression event stream — millions of rows a day across all tenants. Range-partition it by time so retention is a metadata operation (drop last month's partition instead of running a DELETE that bloats the heap), and keep RLS on the parent so every partition inherits tenant isolation automatically.

CREATE TABLE watch_event (
    tenant_id   bigint      NOT NULL,
    video_id    bigint      NOT NULL,
    occurred_at timestamptz NOT NULL DEFAULT now(),
    country     text,
    PRIMARY KEY (tenant_id, video_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE watch_event_2026_06 PARTITION OF watch_event
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE watch_event_2026_07 PARTITION OF watch_event
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- RLS on the parent applies to every partition.
ALTER TABLE watch_event ENABLE ROW LEVEL SECURITY;
ALTER TABLE watch_event FORCE  ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON watch_event
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

For a handful of very large "whale" tenants you can go a step further and LIST-partition the top level by tenant_id, then sub-partition each by time. That isolates a noisy tenant's data to its own physical files and lets you set per-tenant autovacuum thresholds. Start with time-range partitioning; reach for hybrid partitioning only when one tenant's volume distorts the others.

Migrations run once, not per tenant

This is the operational payoff that schema-per-tenant gives up. With a shared schema there is a single physical copy of each table, so a migration applies to every tenant the moment it commits — no fan-out, no half-migrated tenants, no drift. Here is a minimal, idempotent runner in Go that applies a pending file inside one transaction and records it.

package main

import (
    "context"
    "log"
    "os"

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

func main() {
    const version = "0007_add_watch_event_partitions"
    ctx := context.Background()

    conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatalf("connect: %v", err)
    }
    defer conn.Close(ctx)

    tx, err := conn.Begin(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer tx.Rollback(ctx) // no-op after a successful Commit

    var applied bool
    if err := tx.QueryRow(ctx,
        `SELECT EXISTS (SELECT 1 FROM schema_migrations WHERE version = $1)`,
        version,
    ).Scan(&applied); err != nil {
        log.Fatal(err)
    }
    if applied {
        log.Println("already applied; nothing to do")
        return
    }

    sql, err := os.ReadFile("migrations/" + version + ".sql")
    if err != nil {
        log.Fatal(err)
    }
    if _, err := tx.Exec(ctx, string(sql)); err != nil {
        log.Fatalf("migration failed, rolling back: %v", err)
    }
    if _, err := tx.Exec(ctx,
        `INSERT INTO schema_migrations (version) VALUES ($1)`, version,
    ); err != nil {
        log.Fatal(err)
    }
    if err := tx.Commit(ctx); err != nil {
        log.Fatal(err)
    }
    log.Println("migration applied to all tenants at once")
}

Running the schema change and the bookkeeping INSERT in the same transaction means either both land or neither does — you can never end up with a migration applied but unrecorded, which is the classic cause of a re-run double-applying DDL.

Search lives outside Postgres — but still per tenant

DailyWatch serves discovery from SQLite FTS5 indexes pushed to the edge behind LiteSpeed and Cloudflare, not from Postgres full-text search, because a read-only FTS5 file is dramatically cheaper to replicate and query for "type-ahead" workloads. Postgres stays the source of truth; a periodic job exports each tenant's videos into its own FTS5 file (search/{tenant_slug}.db). The tenant boundary that RLS enforces in Postgres is preserved downstream simply by never mixing two tenants into one index file. The lesson generalises: whatever you build on top of the database — search, cache, exports — must carry tenant_id all the way through, or you reopen the hole RLS just closed.

Cache keys must carry the tenant

That original incident was a cache bug, not a database bug. The page cache key was trending:{region} with no tenant in it, so the first publisher to warm the cache for US served their rail to everyone in that region. The rule is blunt and absolute: every cache key, at every layer, includes the tenant.

Page cache (LiteSpeed / PHP file cache): tenant:{id}:page:{route}:{region}
Cloudflare Cache-Key / Vary: include a tenant-derived header or hostname so the edge never crosses tenants
Application data cache: prefix every key with tenant:{id}:

If the tenant is part of the key, a collision is impossible by construction — the same defensive instinct as putting tenant_id in the primary key.

What I would tell my past self

Put tenant_id in the primary key, not just as a filtered column. Compound keys make isolation, indexing, and partitioning all fall out naturally.
Enforce isolation with FORCE RLS and a non-superuser app role. A USING plus WITH CHECK policy closes both read and write leaks.
Set tenant context with SET LOCAL / set_config(..., true) inside a transaction, and reset pooled connections. The pooling leak is the one that gets you in production.
Fail closed: no tenant context should yield zero rows, never all rows.
Carry tenant_id into every downstream system — search indexes, cache keys, exports. The database boundary is only as strong as its weakest consumer.

The shared-schema-plus-RLS design is not the flashiest answer, but it is the one that lets a small team onboard publishers indefinitely without the per-tenant operational tax, while making the worst-case bug — one tenant seeing another's data — a thing the database refuses to let happen rather than a thing your code has to remember to prevent.

Adopting PostgreSQL UUID v7 Primary Keys for Video Metadata at Scale

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

Last quarter our discovery catalog crossed 40 million video-metadata rows spread across eight regions, and our BIGSERIAL primary keys finally became the bottleneck we could no longer paper over. Each region runs its own ingest cron on its own cadence, and merging regional shards meant remapping integer IDs by hand. Worse, our public watch URLs exposed sequential integers, so anyone could fetch two URLs a day apart, subtract, and estimate our daily ingest volume. We run TrendVidStream, a global multi-region streaming-discovery service, and the change that finally stuck was moving primary keys to PostgreSQL UUID v7. This is the concrete story of why we did it and exactly how, including a backfill of 40 million live rows without a maintenance window.

The problem with BIGSERIAL at multi-region scale

We operate eight regional ingest pipelines. Each is a cron job that pulls fresh streaming metadata, normalizes it, and writes it into Postgres. With a single monotonic BIGSERIAL sequence, growth exposed four distinct failures:

Shard merges required ID remapping. When the same video surfaced in two regions, reconciling rows meant translating one region's integer ID space into another. Every foreign key into comments, watch history, and recommendation edges had to be rewritten inside the same transaction.
Sequential IDs leaked business data. A watch URL like /watch/4821993 is a gift to competitors. Catalog velocity is a trade secret for a discovery product, and a plain incrementing counter hands it out for free.
The right-most index page became a write hotspot. Every region appended to the same increasing sequence, so under concurrent ingest the last B-tree leaf page turned into a lock-contention point.
ID generation needed a database round trip. You cannot know a row's ID until after the INSERT returns. That makes client-side reference graphs and large batch inserts awkward, especially in our PHP 8.4 ingest layer where we want to build the whole object graph in memory first.

What we actually needed was globally unique identifiers that any process — PHP, a Go worker, a Python backfill script — could mint locally, with no coordination, while still keeping the locality benefits that made BIGSERIAL fast in the first place.

Why random UUID v4 was the wrong fix

The obvious reaction is "just use uuid with gen_random_uuid()." We tried it in a staging shard and the numbers were ugly. Random UUID v4 keys are uniformly distributed, which means consecutive inserts land in random positions across the primary-key B-tree. The consequences at our row counts:

Index fragmentation. Inserts touch random leaf pages, so the index never stays densely packed. Our v4 staging index was roughly 30% larger on disk than the equivalent BIGSERIAL index for the same row count.
Cache thrashing. Because writes scatter, the pages you need are rarely the pages already in shared_buffers. Buffer hit ratio dropped and read latency on the ingest path climbed.
WAL amplification. Random insert positions trigger more full-page writes, which inflates WAL volume and slows down streaming replication to our read replicas.

UUID v4 solved uniqueness and the leakage problem but traded away the locality that made our hot insert path cheap. UUID v7 is the version that keeps both.

What UUID v7 actually is

UUID v7, standardized in RFC 9562, is a 128-bit identifier whose leading 48 bits are a Unix timestamp in milliseconds, followed by version and variant bits and then random data. The layout matters:

Bits 0-47: Unix epoch milliseconds, big-endian.
Bits 48-51: version (0111 = 7).
Bits 52-63: 12 bits of randomness (rand_a).
Bits 64-65: variant (10).
Bits 66-127: 62 bits of randomness (rand_b).

Because the timestamp is the most significant part, IDs minted later sort after IDs minted earlier. New rows therefore append near the right edge of the B-tree, exactly like BIGSERIAL, so you keep insert locality, dense index pages, and cheap range scans. At the same time the 74 bits of randomness give you collision-resistant global uniqueness without any central sequence. That is the whole pitch: time-ordered like an integer, decentralized like a UUID.

Generating UUID v7 inside PostgreSQL

PostgreSQL 18 ships a native uuidv7() function, which is what we use on our primary cluster. The table definition is refreshingly boring:

-- PostgreSQL 18+: native generator
CREATE TABLE videos (
    uuid       uuid PRIMARY KEY DEFAULT uuidv7(),
    region     text        NOT NULL,
    title      text        NOT NULL,
    duration_s int         NOT NULL,
    published  timestamptz NOT NULL DEFAULT now()
);

If you are still on Postgres 14-17, you do not need an extension. This pure-SQL function reuses gen_random_uuid() for the random bytes, overlays the millisecond timestamp into the first six bytes, and flips the version bits:

-- PostgreSQL 14-17: no extension required
CREATE OR REPLACE FUNCTION uuid_generate_v7()
RETURNS uuid
AS $$
BEGIN
  RETURN encode(
    set_bit(
      set_bit(
        overlay(
          uuid_send(gen_random_uuid())
          PLACING substring(
            int8send(floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint)
            FROM 3
          )
          FROM 1 FOR 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex'
  )::uuid;
END
$$ LANGUAGE plpgsql VOLATILE;

The int8send call serializes the 64-bit millisecond timestamp; we take its trailing six bytes (FROM 3) because the top two bytes are always zero for current dates, and overlay drops them into the front of the UUID. The two set_bit calls turn the version nibble into 0111. Wrap either form in DEFAULT and your existing INSERT statements need no changes at all.

Generating UUID v7 in the application layer with PHP 8.4

The real win is generating IDs before the row ever reaches the database. Our ingest layer builds an entire object graph — video, channel reference, region tags — in memory, then bulk-inserts it. We mint the IDs in PHP 8.4 so foreign keys are wired up client-side. You can pull in symfony/uid and call Uuid::v7(), but the dependency-free version is short enough that we inlined it:

<?php
declare(strict_types=1);

function uuidv7(): string
{
    // 48-bit millisecond timestamp
    $unixTsMs = (int) (microtime(true) * 1000);
    $tsHex    = str_pad(dechex($unixTsMs), 12, '0', STR_PAD_LEFT);

    // 80 random bits; we overwrite version + variant nibbles below
    $rand = random_bytes(10);
    $rand[0] = chr((ord($rand[0]) & 0x0f) | 0x70); // version 7
    $rand[2] = chr((ord($rand[2]) & 0x3f) | 0x80); // variant 10xx

    $hex = $tsHex . bin2hex($rand);

    return sprintf(
        '%s-%s-%s-%s-%s',
        substr($hex, 0, 8),
        substr($hex, 8, 4),
        substr($hex, 12, 4),
        substr($hex, 16, 4),
        substr($hex, 20, 12)
    );
}

// Build the graph in memory, then one round trip:
$videoId = uuidv7();
$pdo->prepare(
    'INSERT INTO videos (uuid, region, title, duration_s) VALUES (?, ?, ?, ?)'
)->execute([$videoId, 'eu-west', 'Highlight Reel', 212]);

Generating IDs in PHP also lets our SQLite FTS5 search index — which we keep as a per-region read-side cache for instant autocomplete — reference the exact same UUID the moment a row is created, instead of waiting on a RETURNING clause and a second write. The canonical metadata lives in Postgres; the FTS5 mirror just copies the already-known key.

Generating UUID v7 in Go for the ingest workers

Our highest-throughput ingest workers are written in Go, where minting IDs locally removes a round trip from the hot loop entirely. The implementation maps directly onto the RFC layout:

package main

import (
    "crypto/rand"
    "fmt"
    "time"
)

// UUIDv7 returns a time-ordered RFC 9562 UUID v7 string.
func UUIDv7() (string, error) {
    var u [16]byte

    ts := uint64(time.Now().UnixMilli())
    u[0] = byte(ts >> 40)
    u[1] = byte(ts >> 32)
    u[2] = byte(ts >> 24)
    u[3] = byte(ts >> 16)
    u[4] = byte(ts >> 8)
    u[5] = byte(ts)

    if _, err := rand.Read(u[6:]); err != nil {
        return "", err
    }
    u[6] = (u[6] & 0x0f) | 0x70 // version 7
    u[8] = (u[8] & 0x3f) | 0x80 // variant 10xx

    return fmt.Sprintf("%x-%x-%x-%x-%x",
        u[0:4], u[4:6], u[6:8], u[8:10], u[10:16]), nil
}

func main() {
    id, err := UUIDv7()
    if err != nil {
        panic(err)
    }
    fmt.Println(id) // e.g. 0195a3f1-7c2a-7b3e-9d41-3f2a8c1e6b00
}

Because every worker stamps the leading 48 bits from its own clock, IDs minted across eight regions still sort into a single coherent global timeline as long as the boxes are within a second or two of NTP — which they are. No sequence, no coordination, no contention on a shared counter.

Backfilling 40 million live rows without downtime

The hard part was migrating the existing catalog. We could not lock 40 million rows or take the catalog offline. The plan was incremental: add a nullable uuid column, backfill it in small batches from a Python cron job, then promote it to the primary key in a final swap.

The critical property of UUID v7 made this safe: for historical rows we set the timestamp portion from each row's real published time, so the backfilled UUIDs sort in the same order as the original sequence. New rows mint a UUID from the current clock. Both interleave cleanly.

import os
import time
import uuid
import psycopg2
from psycopg2.extras import execute_values

BATCH = 5000

def uuid7(unix_ms: int) -> uuid.UUID:
    rand_b = os.urandom(10)
    raw = bytearray(unix_ms.to_bytes(6, "big") + rand_b)
    raw[6] = (raw[6] & 0x0F) | 0x70  # version 7
    raw[8] = (raw[8] & 0x3F) | 0x80  # variant 10xx
    return uuid.UUID(bytes=bytes(raw))

def backfill(dsn: str) -> None:
    conn = psycopg2.connect(dsn)
    conn.autocommit = False
    with conn.cursor() as cur:
        while True:
            cur.execute(
                "SELECT id, extract(epoch FROM published) * 1000 "
                "FROM videos WHERE uuid IS NULL ORDER BY id LIMIT %s",
                (BATCH,),
            )
            rows = cur.fetchall()
            if not rows:
                break
            updates = [(str(uuid7(int(ms))), pk) for pk, ms in rows]
            execute_values(
                cur,
                "UPDATE videos SET uuid = data.u::uuid "
                "FROM (VALUES %s) AS data(u, id) "
                "WHERE videos.id = data.id",
                updates,
            )
            conn.commit()
            print(f"backfilled {len(rows)} rows")
            time.sleep(0.2)  # keep replication lag low
    conn.close()

if __name__ == "__main__":
    backfill(os.environ["DATABASE_URL"])

The time.sleep(0.2) between commits is deliberate: it keeps replica lag bounded so our read-side search caches never fall behind during the multi-hour backfill. We ran this as an ordinary cron entry, the same way every other job in our FTP-deployed stack ships — no orchestration framework, just a script and a crontab line. When the column was fully populated we added the unique index CONCURRENTLY, then ran one short transaction to swap the primary key over.

Index behavior and time-range partitioning

Once UUID v7 was the primary key, an unexpected bonus appeared: because the leading 48 bits encode time, a range of UUIDs maps directly to a range of time. That makes range partitioning on the UUID column behave almost exactly like partitioning on a timestamp.

CREATE TABLE videos (
    uuid       uuid NOT NULL DEFAULT uuidv7(),
    region     text NOT NULL,
    title      text NOT NULL,
    published  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (uuid)
) PARTITION BY RANGE (uuid);

-- The leading hex of a v7 UUID is the millisecond timestamp,
-- so these bounds carve the table into time windows.
CREATE TABLE videos_2026_q1 PARTITION OF videos
    FOR VALUES FROM ('01952000-0000-7000-8000-000000000000')
                 TO ('01964000-0000-7000-8000-000000000000');

CREATE TABLE videos_2026_q2 PARTITION OF videos
    FOR VALUES FROM ('01964000-0000-7000-8000-000000000000')
                 TO ('01975000-0000-7000-8000-000000000000');

What this bought us in practice:

Cheap retention. Dropping a stale quarter is a DROP TABLE on one partition, not a 40-million-row DELETE.
Pruning for free. Queries that filter on recent activity hit only the newest partitions, because recent UUIDs live in a known numeric range.
Dense indexes again. Inserts append to the active partition's right edge, restoring the BIGSERIAL locality we missed with v4.

What changed in production

After the cutover, the measurable differences across our eight-region deployment were:

Shard merges stopped requiring ID remapping — a row's identity is the same everywhere it appears, so reconciliation became a simple ON CONFLICT DO NOTHING.
Watch URLs stopped leaking catalog size, because the ID no longer encodes a guessable counter.
Insert-path contention dropped, since each region appends to its own right-most leaf pages instead of fighting over one shared sequence.
Index size stayed close to the old BIGSERIAL footprint, far below the random-v4 staging numbers, thanks to time-ordered inserts.

Conclusion

UUID v7 turned out to be the rare change that fixed a structural problem without introducing a new one. It gave us decentralized, globally unique, non-guessable identifiers while preserving the insert locality and small indexes that made integer keys fast. The migration path — nullable column, batched backfill from real timestamps, concurrent index build, atomic swap — let us move 40 million rows with zero downtime on the same plain cron-and-FTP tooling we use for everything else. If you are running a multi-region service still leaning on BIGSERIAL, and especially if your IDs are visible in public URLs, UUID v7 is worth the afternoon. Generate the keys in your application layer, lean on the time prefix for partitioning, and you keep almost everything you liked about sequential integers while shedding the parts that do not scale.

Building a Video Thumbnail Generator Service with Go and FFmpeg

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

Every viral video we ingest at ViralVidVault arrives without a usable preview image. YouTube and TikTok embeds give us one default poster frame, and it is almost always the worst possible choice: a black fade-in, a sponsor card, or a blurry first frame before the camera stabilizes. When you are running a European viral-video discovery feed where the thumbnail is the single biggest driver of click-through, shipping a bad frame is shipping a dead row in the grid.

We used to lean on the platform-provided poster. Click-through on the discovery grid sat around 3.1%. After we built an internal service that extracts several candidate frames per video, scores them, and serves a cropped WebP, that number moved to 5.4%. This article is the build log for that service: a small Go HTTP daemon that wraps FFmpeg, picks a good frame, and hands the result back to our PHP 8.4 / SQLite WAL stack for caching. No magic, just FFmpeg invoked carefully and a scoring heuristic that beats "grab the frame at 50%."

Why a Separate Go Service Instead of PHP

The rest of VViralVidVault runs on PHP 8.4 behind LiteSpeed, with SQLite in WAL mode as the primary store and Cloudflare Workers handling edge caching and GDPR-aware request shaping. PHP is great for the request/response cycle, but thumbnail generation is the wrong job for it:

It is CPU-bound and bursty. When a new trend breaks we might queue 400 videos in a few minutes. We do not want those FFmpeg processes competing with page renders inside the LiteSpeed worker pool.
It needs real concurrency. Go's goroutines and a bounded worker pool let us cap how many FFmpeg processes run at once, which matters because each one happily eats a full core.
Process supervision is cleaner. Shelling out to FFmpeg from PHP via proc_open works, but timeouts, zombie reaping, and stderr capture are fiddly. Go's os/exec with context.Context gives us cancellation for free.

So the architecture is: PHP enqueues a job (video URL or local path) over HTTP, the Go service downloads/reads the file, extracts candidate frames, scores them, encodes the winner to WebP, and returns the bytes plus metadata. PHP stores the result and lets Cloudflare cache the delivered image.

Extracting Candidate Frames with FFmpeg

The naive approach is -ss 00:00:05 -frames:v 1. That gives you one frame at a fixed offset, and for a 12-second clip that lands you in dead air half the time. Instead we extract N evenly spaced candidates across the duration, skipping the first and last 10% where intros and outros live.

Two FFmpeg details matter here. First, put -ss before -i for fast input seeking — it jumps via the index instead of decoding every frame up to the timestamp. Second, use -noaccurate_seek when you only need an approximate position; it is dramatically faster and a few frames of imprecision is irrelevant for a thumbnail.

Here is the core extraction in Go. We first probe duration with ffprobe, then fan out frame grabs:

package thumb

import (
    "context"
    "encoding/json"
    "fmt"
    "os"
    "os/exec"
    "path/filepath"
    "strconv"
    "time"
)

type probeResult struct {
    Format struct {
        Duration string `json:"duration"`
    } `json:"format"`
}

// probeDuration returns the clip length in seconds.
func probeDuration(ctx context.Context, path string) (float64, error) {
    cmd := exec.CommandContext(ctx, "ffprobe",
        "-v", "error",
        "-show_entries", "format=duration",
        "-of", "json", path)
    out, err := cmd.Output()
    if err != nil {
        return 0, fmt.Errorf("ffprobe: %w", err)
    }
    var pr probeResult
    if err := json.Unmarshal(out, &pr); err != nil {
        return 0, err
    }
    return strconv.ParseFloat(pr.Format.Duration, 64)
}

// extractFrame grabs a single JPEG at offset seconds into dir.
func extractFrame(ctx context.Context, src, dir string, offset float64, idx int) (string, error) {
    dst := filepath.Join(dir, fmt.Sprintf("cand_%02d.jpg", idx))
    cmd := exec.CommandContext(ctx, "ffmpeg",
        "-noaccurate_seek",
        "-ss", strconv.FormatFloat(offset, 'f', 3, 64),
        "-i", src,
        "-frames:v", "1",
        "-q:v", "3",
        "-y", dst)
    if err := cmd.Run(); err != nil {
        return "", fmt.Errorf("ffmpeg frame %d: %w", idx, err)
    }
    return dst, nil
}

// CandidateFrames extracts n evenly spaced frames, skipping head/tail.
func CandidateFrames(ctx context.Context, src, dir string, n int) ([]string, error) {
    dur, err := probeDuration(ctx, src)
    if err != nil {
        return nil, err
    }
    if dur <= 0 {
        return nil, fmt.Errorf("non-positive duration %.2f", dur)
    }
    start, end := dur*0.10, dur*0.90
    step := (end - start) / float64(n-1)

    frames := make([]string, 0, n)
    for i := 0; i < n; i++ {
        offset := start + step*float64(i)
        select {
        case <-ctx.Done():
            return frames, ctx.Err()
        default:
        }
        path, err := extractFrame(ctx, src, dir, offset, i)
        if err != nil {
            continue // skip a bad frame, keep the rest
        }
        frames = append(frames, path)
    }
    if len(frames) == 0 {
        return nil, fmt.Errorf("no frames extracted")
    }
    return frames, nil
}

A few production notes that cost us a day each to learn:

Always set a timeout via context.WithTimeout. A corrupt file can make FFmpeg hang forever. We cap each job at 30 seconds.
-q:v 3 for the intermediate JPEG keeps the candidates small but high enough quality to score reliably. We re-encode the winner to WebP later, so this is throwaway quality.
Skip, don't fail, on a single bad frame. Seeking near the end of a variable-frame-rate file occasionally returns nothing; you still want the other candidates.

Scoring Frames So We Don't Ship Black

Evenly spaced candidates solve "don't always grab 50%," but we still need to rank them. The two failure modes that kill click-through are dark frames (fades, night shots that read as black thumbnails) and flat frames (a static title card with no visual interest). We score each candidate on two cheap metrics:

Brightness — mean luminance. Penalize anything too dark or blown-out white.
Detail / contrast — standard deviation of luminance. A higher spread means more visual structure (faces, motion, scenery) versus a flat gradient.

We do not need ML for this. A weighted combination of luminance mean and variance, computed in pure Go over the decoded JPEG, beats the platform default reliably. Here is the scorer:

package thumb

import (
    "image"
    _ "image/jpeg"
    "math"
    "os"
)

type Score struct {
    Path       string
    Brightness float64 // 0..255 mean luma
    Detail     float64 // luma std-dev
    Total      float64
}

// scoreFrame computes brightness and detail for one candidate.
func scoreFrame(path string) (Score, error) {
    f, err := os.Open(path)
    if err != nil {
        return Score{}, err
    }
    defer f.Close()

    img, _, err := image.Decode(f)
    if err != nil {
        return Score{}, err
    }
    b := img.Bounds()

    // Sample on a grid; full-pixel scans are wasteful at thumbnail scale.
    const stride = 4
    var sum, sumSq, count float64
    for y := b.Min.Y; y < b.Max.Y; y += stride {
        for x := b.Min.X; x < b.Max.X; x += stride {
            r, g, bl, _ := img.At(x, y).RGBA()
            // ITU-R BT.601 luma, RGBA is 16-bit so shift to 8-bit.
            luma := 0.299*float64(r>>8) + 0.587*float64(g>>8) + 0.114*float64(bl>>8)
            sum += luma
            sumSq += luma * luma
            count++
        }
    }
    mean := sum / count
    variance := sumSq/count - mean*mean
    stdDev := math.Sqrt(math.Max(0, variance))

    // Brightness reward peaks around 120 (mid-tone) and falls off toward
    // pure black or pure white.
    brightScore := 1 - math.Abs(mean-120)/120
    if brightScore < 0 {
        brightScore = 0
    }
    // Detail reward, normalized; 70+ std-dev is plenty of structure.
    detailScore := math.Min(stdDev/70, 1)

    total := 0.45*brightScore + 0.55*detailScore
    return Score{Path: path, Brightness: mean, Detail: stdDev, Total: total}, nil
}

// BestFrame returns the highest-scoring candidate path.
func BestFrame(paths []string) (Score, error) {
    var best Score
    best.Total = -1
    for _, p := range paths {
        s, err := scoreFrame(p)
        if err != nil {
            continue
        }
        if s.Total > best.Total {
            best = s
        }
    }
    if best.Total < 0 {
        return Score{}, errImpossible
    }
    return best, nil
}

The grid sampling with stride = 4 matters at volume — scanning every pixel of even a 640px JPEG is wasteful when a quarter of the samples gives the same ranking. We weight detail slightly higher than brightness (0.55 vs 0.45) because in our A/B testing a slightly dark but information-rich frame outperformed a perfectly lit title card almost every time.

Encoding the Winner to WebP

Once we have the best candidate, we crop it to our grid aspect ratio (16:9) and re-encode to WebP. WebP cut our thumbnail bytes by roughly 30% versus JPEG at visually identical quality, which matters when Cloudflare is billing egress and European mobile users on metered data are a big chunk of our traffic.

We go back to FFmpeg for the crop-and-encode in one pass. The crop filter with min(iw,ih*16/9) math centers the crop regardless of source aspect ratio:

// EncodeWebP crops to 16:9, scales to width, and writes WebP at quality q.
func EncodeWebP(ctx context.Context, src, dst string, width, q int) error {
    vf := fmt.Sprintf(
        "crop='min(iw,ih*16/9)':'min(ih,iw*9/16)',scale=%d:-1",
        width)
    cmd := exec.CommandContext(ctx, "ffmpeg",
        "-i", src,
        "-vf", vf,
        "-c:v", "libwebp",
        "-quality", strconv.Itoa(q),
        "-frames:v", "1",
        "-y", dst)
    out, err := cmd.CombinedOutput()
    if err != nil {
        return fmt.Errorf("webp encode: %w: %s", err, out)
    }
    return nil
}

We serve -quality 80 for the grid and a separate -quality 90 larger render for the video detail page. The bounded worker pool that drives all of this is the standard Go pattern — a buffered channel as a semaphore so we never run more FFmpeg processes than we have cores to spare:

// Pool caps concurrent FFmpeg jobs at size.
type Pool struct{ sem chan struct{} }

func NewPool(size int) *Pool { return &Pool{sem: make(chan struct{}, size)} }

func (p *Pool) Run(ctx context.Context, job func() error) error {
    select {
    case p.sem <- struct{}{}:
    case <-ctx.Done():
        return ctx.Err()
    }
    defer func() { <-p.sem }()
    return job()
}

We size the pool at runtime.NumCPU() - 1 so the box stays responsive. On a 4-core VPS that means three concurrent extractions, which clears a 400-video burst in a couple of minutes.

Wiring It Into the PHP and Cloudflare Stack

The Go service exposes one endpoint, POST /thumb, taking a video reference and returning JSON metadata plus a stored file path. PHP calls it, records the result in SQLite, and lets Cloudflare do the heavy lifting on delivery. Because SQLite runs in WAL mode, the thumbnail-metadata writes from the ingest worker do not block the read traffic serving the discovery grid — readers and the single writer coexist without lock contention.

Here is the PHP 8.4 side that requests a thumbnail and caches the metadata:

<?php
declare(strict_types=1);

final class ThumbnailClient
{
    public function __construct(
        private string $serviceUrl,
        private \PDO $db,
    ) {}

    public function ensure(string $videoId, string $sourcePath): ?string
    {
        $stmt = $this->db->prepare(
            'SELECT webp_path FROM thumbnails WHERE video_id = ?'
        );
        $stmt->execute([$videoId]);
        if ($path = $stmt->fetchColumn()) {
            return $path; // already generated
        }

        $ch = curl_init($this->serviceUrl . '/thumb');
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 35,
            CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
            CURLOPT_POSTFIELDS     => json_encode([
                'video_id' => $videoId,
                'source'   => $sourcePath,
                'width'    => 640,
            ], JSON_THROW_ON_ERROR),
        ]);
        $raw = curl_exec($ch);
        if ($raw === false || curl_getinfo($ch, CURLINFO_HTTP_CODE) !== 200) {
            curl_close($ch);
            return null; // fall back to platform poster
        }
        curl_close($ch);

        $data = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
        $ins = $this->db->prepare(
            'INSERT INTO thumbnails (video_id, webp_path, brightness, detail)
             VALUES (?, ?, ?, ?)'
        );
        $ins->execute([
            $videoId,
            $data['webp_path'],
            $data['brightness'],
            $data['detail'],
        ]);
        return $data['webp_path'];
    }
}

The delivery path is deliberately dumb: the generated WebP files sit behind a Cloudflare Worker that sets a long Cache-Control and an immutable filename keyed by video_id plus a content hash. Once a thumbnail is generated it is effectively free to serve — the origin (LiteSpeed) sees the request only on the first miss per edge location. From a GDPR standpoint this is clean: thumbnails carry no user data, the Worker strips any analytics cookies on the image route, and nothing about a visitor is logged to serve a static frame.

A few operational guardrails we added after running this in production:

Idempotency by video_id. The PHP ensure() check means a re-ingested video does not regenerate, and the INSERT is guarded by a unique index so concurrent ingest workers cannot double-write.
Graceful fallback. If the Go service is down or times out, ensure() returns null and the grid falls back to the platform poster. A degraded thumbnail beats a 500.
Disk hygiene. The candidate JPEGs go into a per-job temp directory that is defer os.RemoveAll-ed in Go. Only the final WebP survives. We learned this after a forgotten temp dir filled a disk overnight.
Reprocess hook. Because the score is stored, we can later requery for thumbnails with low detail and regenerate them with a wider candidate set — useful when we tune the scoring weights.

What Moved the Numbers

The single biggest win was not the WebP savings or the concurrency — it was the scoring heuristic refusing to ship dark and flat frames. Before scoring, roughly one in six auto-generated thumbnails was effectively black on the grid. After scoring, that dropped to near zero, and the discovery-grid click-through climbed from 3.1% to 5.4% over three weeks. The WebP re-encode then trimmed about 30% off image bytes, which on a mobile-heavy European audience showed up as a measurable drop in Largest Contentful Paint on the discovery page.

If you are building something similar, the order of impact is clear: get the frame selection right first, then optimize bytes and concurrency. FFmpeg does the hard part; the value you add is in not blindly trusting the first frame it hands you. A 90-line Go scorer beat every off-the-shelf default we tried, and it runs in single-digit milliseconds per candidate. Start there, measure click-through, and only reach for anything heavier if the cheap heuristic stops paying its way.

Implementing the OAuth2 Device Authorization Flow for Smart TV Apps

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

A viewer opens our Android TV app, lands on a wall of trending videos pulled from Japan, Korea, and Taiwan, and taps Sign in to sync a watchlist between their phone and the living room. Then they hit the wall every TV developer eventually meets: there is no keyboard. There is a D-pad, an OK button, and maybe a voice remote that mishears half of what it's told. Asking someone to peck out an email and a strong password on a grid of on-screen letters is how you lose a login. At TopVideoHub we run this across a lot of low-end TV hardware in the Asia-Pacific market, and the math is unforgiving: every extra second on the sign-in screen measurably drops completion.

The OAuth2 Device Authorization Grant (RFC 8628) is the fix. The TV shows a short human-readable code, the user finishes login on the device already in their hand, and the TV quietly polls a backend until approval lands. This article walks through a real implementation on PHP 8.4 and SQLite, the parts the RFC glosses over, and the operational details that bite you behind LiteSpeed and Cloudflare.

Why the authorization code flow breaks on a TV

The normal browser-based authorization code flow assumes three things a TV does not reliably have: a capable web browser, a usable text-input method, and the ability to receive a redirect back to the application. A set-top box might ship a WebView, but bouncing the user out to an identity provider, having them type credentials with a remote, and catching the redirect URI in a native app is a fragile chain. Each link is a place to fail.

The device flow's core trick is decoupling the device that consumes the token from the device that grants it. The TV never sees a password. It asks the backend for a pair of codes, displays one to the human, and then does nothing but poll. The actual authentication happens in a real browser on a phone or laptop, where autofill, password managers, and even passkeys already work. The TV is reduced to a polling client, which is exactly what a constrained device is good at.

The device flow in four messages

Strip away the jargon and the whole protocol is four messages:

Device authorization request. The TV POSTs its client_id and requested scope to the device endpoint. The server returns a device_code (for the machine), a user_code (for the human), a verification_uri, a poll interval, and an expires_in.
Display. The TV renders the user_code and verification_uri on screen, ideally beside a QR code so a phone camera can jump straight to the page.
User approval. On their phone, the user opens the verification URI, logs in normally, confirms the code they see on the TV, and approves the requested scopes.
Polling. Meanwhile the TV repeatedly POSTs the device_code to the token endpoint. It gets authorization_pending until the human finishes, then a real access token.

Two codes, two endpoints, one polling loop. The subtlety is entirely in the edges: code entropy, replay protection, the slow_down back-off, and making sure no layer of your stack caches the token endpoint.

The data model

Everything hangs off a single device_auth table. We store the SHA-256 of the device_code rather than the code itself, because that value is a bearer secret — if your database leaks, you don't want raw, still-valid codes in it. The user_code stays in plaintext because it is short-lived, low-entropy by design, and has to be matched against what a human types.

CREATE TABLE device_auth (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    device_code  TEXT NOT NULL UNIQUE,   -- sha256 of the real code
    user_code    TEXT NOT NULL UNIQUE,   -- WDXR-7K2P shown on the TV
    client_id    TEXT NOT NULL,
    scope        TEXT NOT NULL DEFAULT '',
    status       TEXT NOT NULL DEFAULT 'pending', -- pending|approved|denied
    user_id      INTEGER,                -- set when a human approves
    interval_s   INTEGER NOT NULL DEFAULT 5,
    last_poll_at INTEGER NOT NULL DEFAULT 0,
    created_at   INTEGER NOT NULL,
    expires_at   INTEGER NOT NULL
);

CREATE INDEX idx_device_user_code ON device_auth(user_code);
CREATE INDEX idx_device_expires   ON device_auth(expires_at);

SQLite is more than enough here. These rows live for fifteen minutes and then get swept by a cron job. We already run SQLite as the primary store across the site, so the auth flow inherits the same backup and deployment story instead of dragging in a new dependency. The last_poll_at and interval_s columns exist purely to implement back-off, which I'll come back to.

Issuing a device code

The device endpoint generates both codes and persists the row. The user_code is the part real humans interact with, so its alphabet matters: drop 0/O, 1/I, and 5/S-style look-alikes, keep it uppercase, and group it with a dash so it reads cleanly from across a room.

<?php
declare(strict_types=1);

// POST /oauth/device/code   (client_id=...&scope=watchlist+profile)

function randomToken(int $bytes): string {
    return bin2hex(random_bytes($bytes));
}

function userCode(): string {
    // No ambiguous characters; grouped as WDXR-7K2P for easy reading.
    $chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
    $code  = '';
    for ($i = 0; $i < 8; $i++) {
        $code .= $chars[random_int(0, strlen($chars) - 1)];
    }
    return substr($code, 0, 4) . '-' . substr($code, 4, 4);
}

$clientId = $_POST['client_id'] ?? '';
$scope    = $_POST['scope'] ?? '';

header('Content-Type: application/json');

if ($clientId === '') {
    http_response_code(400);
    echo json_encode(['error' => 'invalid_request']);
    exit;
}

$deviceCode = randomToken(40);
$userCode   = userCode();
$now        = time();
$expiresIn  = 900;   // 15 minutes
$interval   = 5;     // baseline poll gap, seconds

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

$stmt = $db->prepare(
    'INSERT INTO device_auth
        (device_code, user_code, client_id, scope, status, interval_s, created_at, expires_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
);
$stmt->execute([
    hash('sha256', $deviceCode),   // store only the hash
    $userCode,
    $clientId,
    $scope,
    'pending',
    $interval,
    $now,
    $now + $expiresIn,
]);

echo json_encode([
    'device_code'               => $deviceCode,
    'user_code'                 => $userCode,
    'verification_uri'          => 'https://topvideohub.com/activate',
    'verification_uri_complete' => 'https://topvideohub.com/activate?code=' . $userCode,
    'expires_in'                => $expiresIn,
    'interval'                  => $interval,
], JSON_UNESCAPED_SLASHES);

A few decisions worth flagging. The user_code here has 32^8 ≈ 1.1 trillion possibilities, which is comfortable only if you rate-limit the verification endpoint — short codes are inherently guessable at scale, so brute-force protection lives on the approval side, not in the entropy. The verification_uri_complete field is a convenience that pre-fills the code from a QR scan, but it must never auto-approve; the human still has to confirm. And the fifteen-minute expires_in is a deliberate trade-off between giving a slow user time to find their phone and keeping abandoned codes from piling up.

The verification page

This is the human half and it deserves the same care as the machine half, even though it looks like an ordinary web page. The flow is: the user authenticates normally (the same session login the rest of the site uses), the page looks up the user_code they entered, and it shows exactly which scopes the TV is asking for. On approval, you set status = 'approved' and stamp the authenticated user_id onto the row; on rejection, status = 'denied'.

Two rules that are easy to get wrong here. First, never approve a code that has already expired — check expires_at server-side, not just in the UI. Second, throttle this endpoint hard. Because the user_code is short, an attacker who can submit codes quickly could try to hijack a pending session. We rate-limit by IP and by authenticated user, and we lock a user_code after a small number of failed match attempts. The code being typed by a logged-in human on a phone is the trust anchor of the whole flow; protect it accordingly.

Token polling and the slow_down dance

The token endpoint is where most implementations cut corners. It has to return precise RFC error codes, because the client's behavior branches on them: authorization_pending means keep waiting, slow_down means back off, access_denied means stop, and expired_token means start over. It also has to enforce the poll interval, because a naive TV client will hammer the endpoint, and the server is responsible for pushing back.

<?php
declare(strict_types=1);

// POST /oauth/token
// grant_type=urn:ietf:params:oauth:grant-type:device_code&device_code=...&client_id=...

header('Content-Type: application/json');
header('Cache-Control: no-store');   // never cache token responses

$grant      = $_POST['grant_type'] ?? '';
$deviceCode = $_POST['device_code'] ?? '';

if ($grant !== 'urn:ietf:params:oauth:grant-type:device_code' || $deviceCode === '') {
    http_response_code(400);
    echo json_encode(['error' => 'invalid_request']);
    exit;
}

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

$row = $db->prepare('SELECT * FROM device_auth WHERE device_code = ? LIMIT 1');
$row->execute([hash('sha256', $deviceCode)]);
$auth = $row->fetch(PDO::FETCH_ASSOC);
$now  = time();

if (!$auth || $now > (int) $auth['expires_at']) {
    http_response_code(400);
    echo json_encode(['error' => 'expired_token']);
    exit;
}

// Back-off: if the device polls faster than its interval, raise the interval
// and tell it to slow down.
$minGap   = (int) $auth['interval_s'];
$lastPoll = (int) $auth['last_poll_at'];
if ($lastPoll > 0 && ($now - $lastPoll) < $minGap) {
    $db->prepare('UPDATE device_auth SET interval_s = interval_s + 5 WHERE id = ?')
       ->execute([$auth['id']]);
    http_response_code(400);
    echo json_encode(['error' => 'slow_down']);
    exit;
}
$db->prepare('UPDATE device_auth SET last_poll_at = ? WHERE id = ?')
   ->execute([$now, $auth['id']]);

switch ($auth['status']) {
    case 'pending':
        http_response_code(400);
        echo json_encode(['error' => 'authorization_pending']);
        break;

    case 'denied':
        http_response_code(400);
        echo json_encode(['error' => 'access_denied']);
        break;

    case 'approved':
        $accessToken  = bin2hex(random_bytes(32));
        $refreshToken = bin2hex(random_bytes(32));
        $db->prepare(
            'INSERT INTO tokens (user_id, access_token, refresh_token, scope, expires_at)
             VALUES (?, ?, ?, ?, ?)'
        )->execute([
            $auth['user_id'],
            hash('sha256', $accessToken),
            hash('sha256', $refreshToken),
            $auth['scope'],
            $now + 3600,
        ]);
        // Burn the device_code so it can never be replayed.
        $db->prepare('DELETE FROM device_auth WHERE id = ?')->execute([$auth['id']]);

        echo json_encode([
            'access_token'  => $accessToken,
            'refresh_token' => $refreshToken,
            'token_type'    => 'Bearer',
            'expires_in'    => 3600,
            'scope'         => $auth['scope'],
        ]);
        break;
}

Note the three defensive moves: token hashes go into the database (the raw values are returned exactly once), the device_code row is deleted the instant a token is issued so a replayed poll can't mint a second token, and the slow_down branch actually raises the stored interval rather than just complaining. A polite back-off that the server doesn't enforce is not a back-off.

The TV client

The device side is simple by design. Request codes, render them, sleep, poll, and honor whatever the server tells you. Here it is in Go, which is a common choice for Tizen, webOS, and Android TV sidecar services:

package main

import (
    "encoding/json"
    "net/http"
    "net/url"
    "strings"
    "time"
)

type deviceResp struct {
    DeviceCode      string `json:"device_code"`
    UserCode        string `json:"user_code"`
    VerificationURI string `json:"verification_uri"`
    ExpiresIn       int    `json:"expires_in"`
    Interval        int    `json:"interval"`
}

type tokenResp struct {
    AccessToken string `json:"access_token"`
    Error       string `json:"error"`
}

const base = "https://topvideohub.com/oauth"

func main() {
    dr := requestCode()
    // Render dr.UserCode + dr.VerificationURI on screen (big font + QR code).
    println("Go to", dr.VerificationURI, "and enter", dr.UserCode)

    interval := time.Duration(dr.Interval) * time.Second
    deadline := time.Now().Add(time.Duration(dr.ExpiresIn) * time.Second)

    for time.Now().Before(deadline) {
        time.Sleep(interval)
        tok, status := poll(dr.DeviceCode)
        switch {
        case status == "authorization_pending":
            continue
        case status == "slow_down":
            interval += 5 * time.Second // honour the server back-off
        case tok.AccessToken != "":
            println("signed in")
            return
        default:
            println("login failed:", status)
            return
        }
    }
    println("device code expired, restart sign-in")
}

func requestCode() deviceResp {
    form := url.Values{"client_id": {"tv-app"}, "scope": {"watchlist profile"}}
    resp, _ := http.PostForm(base+"/device/code", form)
    defer resp.Body.Close()
    var dr deviceResp
    json.NewDecoder(resp.Body).Decode(&dr)
    return dr
}

func poll(deviceCode string) (tokenResp, string) {
    form := url.Values{
        "grant_type":  {"urn:ietf:params:oauth:grant-type:device_code"},
        "device_code": {deviceCode},
        "client_id":   {"tv-app"},
    }
    resp, _ := http.Post(base+"/token", "application/x-www-form-urlencoded",
        strings.NewReader(form.Encode()))
    defer resp.Body.Close()
    var tr tokenResp
    json.NewDecoder(resp.Body).Decode(&tr)
    if tr.Error != "" {
        return tr, tr.Error
    }
    return tr, "ok"
}

The loop respects three signals from the server: the initial interval, the slow_down bump, and the overall expires_in deadline. A well-behaved client never decides on its own how often to poll — that decision belongs to the backend, which is the only side that knows how loaded it is.

Operational details behind Cloudflare and LiteSpeed

This is where a textbook-correct implementation still fails in production. Polling generates a steady stream of identical-looking requests, and your caching layers will happily cache the wrong thing.

Never cache the token endpoint. It returns Cache-Control: no-store, but defense in depth matters. On Cloudflare we add a cache rule to bypass /oauth/* entirely. Because the site runs Cloudflare in Flexible mode, a stale cached authorization_pending would leave a TV polling forever even after approval — so this rule is not optional.
Exclude /oauth from the LiteSpeed page cache. The same logic applies to the origin-side cache. We add /oauth to the cache-exclude list so LiteSpeed never serves a stored response for a dynamic token check.
Rate-limit by client and IP. Beyond the per-row slow_down, put a coarse rate limit on both endpoints. The device endpoint can be abused to spam code creation; the verification endpoint must be throttled to defeat user_code guessing.
Sweep expired rows. A small cron DELETE FROM device_auth WHERE expires_at < strftime('%s','now') keeps the table tiny and removes abandoned codes promptly.

Security details that matter

The device flow's convenience comes from sending a short code over a side channel, and that is also its main attack surface. Treat the user_code as the weak link: keep it short-lived, rate-limit and lock it after failed attempts, and require an authenticated, explicit human approval — never auto-grant from a pre-filled link. Store the device_code and issued tokens as hashes, make the device_code strictly single-use by deleting it on success, and bind the granted token to the exact scopes the user consented to. Run every leg over TLS, and show the user the real scope list on the approval page so consent means something. Get those right and you have a sign-in that takes a viewer about ten seconds with a phone they're already holding.

Conclusion

The device authorization grant is one of those protocols that looks trivial in the RFC and turns into a checklist of small, sharp edges in production: code entropy, hashed secrets, single-use enforcement, server-driven back-off, and cache layers that must be told to keep their hands off. None of it is hard individually. The win is real, though — on constrained Asia-Pacific TV hardware, moving authentication off the remote and onto the phone is the difference between a login people complete and one they abandon. Build the four messages carefully, enforce slow_down on the server, keep Cloudflare and LiteSpeed away from the token endpoint, and the rest is just polling.

Driving Video Region Router Config With etcd Watches and Leases

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

We route viewers to the nearest video edge based on a region map: a table that says "traffic from eu-west goes to edge pool 3, traffic from ap-south goes to edge pool 7, and if pool 7 is degraded, fail over to pool 2." For a long time that map lived in a PHP config file that we deployed with the rest of the app. The day a CDN partner had a regional outage in Singapore, we learned exactly how bad that design was: changing one routing rule meant a full deploy across every node, and during the ~90 seconds it took to roll out, half our Southeast Asia traffic kept hammering a dead pool. At DailyWatch that is the difference between a video starting in 400ms and a spinner that makes someone close the tab.

The fix was to pull the region map out of the deploy artifact entirely and put it in etcd, where it can be changed atomically, watched in real time, and survive a node restart without anyone touching a config file. This post walks through how we model video region routing in etcd, how we propagate changes to a fleet of routers in under a second, and the specific traps (lease expiry, watch reconnection, thundering-herd reloads) that bit us along the way.

Why etcd and not just a database row

We already run SQLite with FTS5 for our video catalog search, and the obvious lazy move is to add a region_routes table and poll it. We tried that first. Polling a row every few seconds gives you latency you can measure in seconds and a load pattern that scales linearly with your node count. etcd buys you three things a polled row does not:

Watches. Instead of asking "did anything change?" on a timer, every router holds an open watch and gets pushed the new value the instant it is committed. Propagation is a network round-trip, not a poll interval.
Leases. A key can be tied to a lease with a TTL. If the process that wrote it dies and stops renewing, the key disappears automatically. This is perfect for "this edge pool is healthy" markers that should vanish when the health-checker crashes.
Atomic multi-key transactions. You can swap an entire region map in one transaction so a router never observes a half-updated state where eu-west points at a pool that the same update was about to delete.

etcd is a Raft-backed consistent store. It is not a cache and not a high-throughput data store, so we keep it for control-plane data only: routing rules, pool health, feature flags. The video metadata stays in SQLite, served from disk behind LiteSpeed, and the hot path never touches etcd directly. That separation matters and I'll come back to it.

Modeling the region map as keys

etcd is a flat, sorted key-value space, so the design work is in the key layout. We namespace everything under a versioned prefix so we can run a v1 and v2 schema side by side during a migration:

/dw/v1/routes/eu-west      -> {"pool":"edge-3","fallback":"edge-2","weight":100}
/dw/v1/routes/ap-south     -> {"pool":"edge-7","fallback":"edge-2","weight":100}
/dw/v1/routes/us-east      -> {"pool":"edge-1","fallback":"edge-5","weight":100}
/dw/v1/pools/edge-3/health -> "ok"   (lease-bound, 10s TTL)
/dw/v1/pools/edge-7/health -> "ok"   (lease-bound, 10s TTL)

The routes/ subtree is the intent: where operators want traffic to go. The pools/.../health subtree is reality: which pools are actually answering. A router computes the effective target by combining the two — use pool if its health key exists, otherwise drop to fallback. Because the health keys are lease-bound, a crashed pool's health marker evaporates within the TTL window without any external system having to delete it.

The prefix structure also makes watches cheap. A router watches the single prefix /dw/v1/ and receives every route and health change in one stream. That is one open watch per router regardless of how many regions or pools you have.

Writing config atomically from the control plane

Operators change routing through a small control tool, not by editing etcd keys by hand. The important property is atomicity: when we shift a region to a new pool, the read side must never catch a torn write. etcd transactions (Txn) give us compare-and-swap and multi-op-in-one-commit. Here is the Python writer we use, built on the official etcd3 client:

import json
import etcd3

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

def shift_region(region: str, new_pool: str, fallback: str, weight: int = 100):
    """Atomically repoint a region to a new edge pool."""
    key = f"/dw/v1/routes/{region}"
    value = json.dumps({
        "pool": new_pool,
        "fallback": fallback,
        "weight": weight,
    })

    # Read the current revision so we can detect a concurrent writer.
    current, meta = client.get(key)
    if current is None:
        # Key does not exist yet: only create if still absent.
        success, _ = client.transaction(
            compare=[client.transactions.version(key) == 0],
            success=[client.transactions.put(key, value)],
            failure=[],
        )
    else:
        # Key exists: only write if nobody changed it since we read.
        success, _ = client.transaction(
            compare=[client.transactions.mod(key) == meta.mod_revision],
            success=[client.transactions.put(key, value)],
            failure=[],
        )

    if not success:
        raise RuntimeError(f"concurrent update to {region}, retry")
    print(f"shifted {region} -> {new_pool} (fallback {fallback})")

if __name__ == "__main__":
    # Singapore CDN is down: move ap-south off edge-7 onto edge-2.
    shift_region("ap-south", new_pool="edge-2", fallback="edge-1")

The compare-and-swap on mod_revision is what makes concurrent operators safe. If two engineers both try to repoint ap-south at the same time, one transaction succeeds and the other fails the compare and raises, instead of silently clobbering. For a full map swap you would put several keys into a single success=[...] list so they all land in one Raft commit.

Health markers with leases

The health-checker is a separate process running next to each edge pool. It writes a lease-bound health key and keeps the lease alive. If the checker dies, the lease is not renewed, etcd expires it, and every watching router learns the pool is gone — all without a central coordinator deciding the pool is dead. This is the piece that turned a manual incident response into a self-healing one.

import time
import etcd3

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

def run_health_marker(pool: str, ttl: int = 10):
    lease = client.lease(ttl)
    key = f"/dw/v1/pools/{pool}/health"
    client.put(key, "ok", lease=lease)
    print(f"registered {pool} with {ttl}s lease")

    while True:
        if pool_is_serving(pool):
            # Renew before expiry; refresh at one third of the TTL.
            lease.refresh()
        else:
            # Stop refreshing; key expires within ttl and routers fail over.
            print(f"{pool} unhealthy, letting lease lapse")
            return
        time.sleep(ttl / 3)

def pool_is_serving(pool: str) -> bool:
    # Real check: HTTP probe a known manifest on the edge, verify 200 + body.
    # Returning True here for illustration.
    return True

if __name__ == "__main__":
    run_health_marker("edge-7")

The critical detail is refreshing at one third of the TTL, not at the TTL boundary. A single dropped renewal packet near the deadline would otherwise expire a perfectly healthy pool and cause a spurious failover. With a 10-second TTL and a ~3.3-second refresh interval, you tolerate two consecutive missed renewals before anything fails over. Tune the TTL to your tolerance: shorter means faster detection of real failures but less slack for network blips.

Consuming the map on the router

Our routers are written in Go because the watch loop wants real concurrency and the official go.etcd.io/etcd/client/v3 library is the reference implementation of the protocol. Each router loads the full prefix once at startup, then holds a watch from the revision it loaded at, so it never misses an update between the initial read and the start of the watch.

package main

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

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

type Route struct {
    Pool     string `json:"pool"`
    Fallback string `json:"fallback"`
    Weight   int    `json:"weight"`
}

type RouteTable struct {
    mu     sync.RWMutex
    routes map[string]Route
    health map[string]bool
}

func (t *RouteTable) Target(region string) string {
    t.mu.RLock()
    defer t.mu.RUnlock()
    r, ok := t.routes[region]
    if !ok {
        return ""
    }
    if t.health[r.Pool] {
        return r.Pool
    }
    return r.Fallback // primary unhealthy, use fallback
}

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

    table := &RouteTable{routes: map[string]Route{}, health: map[string]bool{}}

    // 1. Initial snapshot of the whole prefix.
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    resp, err := cli.Get(ctx, "/dw/v1/", clientv3.WithPrefix())
    cancel()
    if err != nil {
        log.Fatal(err)
    }
    for _, kv := range resp.Kvs {
        table.apply(string(kv.Key), kv.Value, false)
    }
    startRev := resp.Header.Revision + 1

    // 2. Watch from the revision right after the snapshot. No gap, no miss.
    watchChan := cli.Watch(context.Background(), "/dw/v1/",
        clientv3.WithPrefix(), clientv3.WithRev(startRev))

    log.Printf("router ready, watching from rev %d", startRev)
    for wr := range watchChan {
        if wr.Err() != nil {
            log.Printf("watch error, will rewatch: %v", wr.Err())
            continue
        }
        for _, ev := range wr.Events {
            deleted := ev.Type == clientv3.EventTypeDelete
            table.apply(string(ev.Kv.Key), ev.Kv.Value, deleted)
        }
    }
}

func (t *RouteTable) apply(key string, val []byte, deleted bool) {
    t.mu.Lock()
    defer t.mu.Unlock()
    switch {
    case len(key) > len("/dw/v1/routes/") && key[:14] == "/dw/v1/routes/":
        region := key[len("/dw/v1/routes/"):]
        if deleted {
            delete(t.routes, region)
            return
        }
        var r Route
        if err := json.Unmarshal(val, &r); err == nil {
            t.routes[region] = r
        }
    case len(key) > len("/dw/v1/pools/") && key[:13] == "/dw/v1/pools/":
        // .../pools/edge-7/health
        pool := key[len("/dw/v1/pools/") : len(key)-len("/health")]
        t.health[pool] = !deleted // delete (lease expiry) => unhealthy
    }
}

The snapshot-then-watch-from-revision pattern is the single most important thing to get right. If you take the snapshot and then start a watch "from now," any write that lands in between is lost forever and your router silently runs stale config. Reading resp.Header.Revision and watching from +1 closes that gap because etcd revisions are a global monotonic counter.

Note also that the in-memory table is guarded by an RWMutex. The hot request path calls Target() with a read lock, which lets thousands of concurrent lookups proceed in parallel; only the watch goroutine takes the write lock, and only when something actually changes. The routing decision itself never makes a network call to etcd — it reads local memory.

Bridging etcd into the PHP application layer

Most of our request handling is PHP 8.4 behind LiteSpeed, and PHP's process-per-request model is a poor fit for holding a long-lived etcd watch. We do not connect PHP to etcd directly. Instead the Go router writes a flat snapshot of the effective route table to a local file whenever it changes, and PHP reads that file through opcache-friendly require. This keeps the hot path zero-network and lets LiteSpeed's per-worker opcache hold the parsed result.

<?php
declare(strict_types=1);

final class RegionRouter
{
    private const SNAPSHOT = '/dev/shm/dw_routes.php';

    /** @var array<string, array{pool:string, fallback:string}> */
    private array $table;

    public function __construct()
    {
        // The Go watcher writes this file atomically (write temp, rename).
        // On /dev/shm it is a RAM read; opcache caches the parsed array.
        $this->table = is_file(self::SNAPSHOT)
            ? (require self::SNAPSHOT)
            : [];
    }

    public function edgeFor(string $region): string
    {
        $route = $this->table[$region] ?? $this->table['default'] ?? null;
        if ($route === null) {
            return 'edge-1'; // hard-coded safety net
        }
        return $route['pool'];
    }
}

// Usage inside a request handler:
$region = $_SERVER['HTTP_CF_IPCOUNTRY'] ?? 'US'; // Cloudflare geo header
$router = new RegionRouter();
$edge   = $router->edgeFor(strtolower($region));
header('X-DW-Edge: ' . $edge);

The Go side writes that file with a temp-write-then-rename so PHP never reads a half-written snapshot — rename is atomic on the same filesystem. Putting the file on /dev/shm means the read is from RAM, and because the content is a return [...] PHP array, opcache holds the compiled form between requests. We feed the region from Cloudflare's CF-IPCOUNTRY header, which it injects at the edge, so we get geo classification for free without a GeoIP database in the app. The result is that a routing decision in PHP is an array lookup against an opcache-resident structure, while the actual source of truth still lives in etcd and updates within a second of any change.

The traps that bit us

A few hard-won lessons, in case you build something similar:

Watch channels close. etcd will close your watch on a compaction or a leader change. The Go example logs and continues, but a production router must recreate the watch from its last seen revision, and handle ErrCompacted by taking a fresh snapshot. Treat the watch as something that will break, not something that might.
Compaction eats history. etcd compacts old revisions to reclaim space. If your router was disconnected longer than your compaction window, watching from the old revision fails. Always have the resnapshot fallback.
Leases need a connection. A lease is renewed over a client connection. If the health-checker's connection to etcd drops, the lease lapses even if the pool is fine. We mitigate by running etcd as a 3- or 5-node cluster and pointing clients at all members, so a single member failure does not orphan leases.
Don't put the hot path in etcd. etcd is a control plane. Tens of thousands of route lookups per second belong in process memory, refreshed by a watch. We learned to keep etcd for the thing that changes rarely and is read by watch, never by per-request Get.
Version your key prefix. /dw/v1/ cost nothing to add up front and saved us during a schema change, because v1 and v2 readers coexisted while we migrated writers.

Conclusion

Moving the video region map from a deployed config file into etcd changed how we handle CDN incidents. What used to be a code change and a fleet-wide deploy is now a single atomic transaction that propagates to every router in under a second, with health markers that self-expire when a pool dies and fail traffic over without a human in the loop. The architecture that made it work is boring on purpose: etcd holds only the small, slowly-changing control data; watches push changes instead of polls pulling them; leases encode liveness; and the hot request path — whether Go or PHP behind LiteSpeed — always reads from local memory, never from etcd directly. If you run any kind of geo-aware routing and you are still shipping that map in your deploy artifact, the watch-plus-lease pattern is worth the afternoon it takes to wire up.

Implementing JWT Refresh Token Rotation for Video API Clients in PHP 8.4

ahmet gedik — Tue, 30 Jun 2026 13:00:01 +0000

Three months ago our anomaly dashboard flagged something ugly: a single refresh token issued to our Android client was being redeemed from two different autonomous systems within the same hour — one request originating in Frankfurt, the other in São Paulo. The token was valid, the signature checked out, and under our old static-refresh-token scheme there was nothing in the protocol itself that let us tell the legitimate device from the attacker. We run TrendVidStream, a global multi-region video streaming discovery platform, and our API serves mobile clients, partner integrations, and our own fleet of cron workers pulling trending-video data across eight regions. Every one of those clients authenticates with JWTs. That incident is why we moved to refresh-token rotation with reuse detection, and this post is the full implementation: the storage schema, the PHP 8.4 server code, the client-side retry logic, and the operational sharp edges that the RFC drafts do not warn you about.

Why a Static Refresh Token Is a 30-Day Skeleton Key

The standard two-token setup looks reasonable on paper. Access tokens are short-lived JWTs (ours live 10 minutes), validated statelessly by signature, so the hot path never touches the database. Refresh tokens are long-lived opaque strings (ours lived 30 days) that the client exchanges for a new access token when the old one expires.

The problem is what happens when a refresh token leaks. With a static token:

The stolen credential works for its entire remaining lifetime — up to 30 days of full API access.
The attacker and the legitimate client both keep using the same token, and from the server's perspective the traffic is indistinguishable.
Revocation only happens if the user notices something and logs out everywhere, which for a machine-to-machine video API client basically means never.
Leaks are more common than people admit: tokens end up in crash reports, in debug logs shipped to third-party aggregators, in backup snapshots of mobile devices, and in partner integrations that store them in plaintext config files.

Rotation changes the economics completely. Every time a refresh token is used, the server invalidates it and issues a brand-new one. Each token is single-use. If a token is ever presented twice, one of the two presenters is not the legitimate client — and the server now has a concrete, protocol-level signal of theft. That signal is the entire point. Rotation is not primarily about shrinking the exposure window; it is about turning a silent compromise into a loud, detectable event.

Token Families and the Rotation Chain

The mechanism that makes reuse detection work is the token family. When a client first authenticates with credentials, the server creates a family — just a random ID — and issues refresh token R1 inside it. When the client redeems R1, the server marks R1 as rotated and issues R2 in the same family. R2 begets R3, and so on. The family is a linked chain of single-use tokens, and at any moment exactly one token in the family should be active.

Now consider theft. An attacker steals R2 while the legitimate client still holds it. Two cases:

The attacker redeems R2 first. They get R3. The legitimate client later presents R2, the server sees a rotated token being reused, and revokes the entire family — including the attacker's R3. The attacker is cut off and the real user re-authenticates.
The legitimate client redeems R2 first and gets R3. The attacker later presents the stale R2, the server detects reuse, and again the whole family dies.

In both orderings the attacker loses access within one rotation cycle, and the server logs a reuse event with the family ID, client ID, and region attached. Compare that with the static scheme, where the attacker quietly coexists with the real client for a month.

The critical implementation detail is that reuse must revoke the whole family, not just the presented token. If you only reject the stale token, the attacker who rotated first keeps their fresh chain and you have detected the theft without actually stopping it.

The Storage Schema in SQLite

Our entire platform runs on SQLite — FTS5 powers the video search index, WAL mode handles concurrent readers under LiteSpeed, and we deploy to shared hosting over FTP, which means there is no Redis, no Postgres, and no daemon we can install. The auth store lives in the same constraints, and honestly SQLite is a great fit here: refresh grants are low-frequency writes (one per client per access-token lifetime), and WAL gives us the transactional guarantees rotation needs.

Two non-negotiable rules before the schema. First, refresh tokens are opaque random strings, not JWTs — there is nothing for the client to introspect, and an unsigned 256-bit random value cannot be forged offline. Second, the database stores only a SHA-256 hash of the token. If the database file ever leaks (and on FTP-deployed shared hosting you should assume it can), the hashes are useless to an attacker because the preimage space is 256 random bits, not a human password — no bcrypt needed, a single fast hash is correct here.

PRAGMA journal_mode = WAL;

CREATE TABLE refresh_tokens (
    id          INTEGER PRIMARY KEY,
    token_hash  TEXT    NOT NULL UNIQUE,  -- sha256 hex of the opaque token
    family_id   TEXT    NOT NULL,         -- random id shared by the whole chain
    client_id   TEXT    NOT NULL,         -- mobile app, partner, region worker
    user_id     INTEGER NOT NULL,
    status      TEXT    NOT NULL DEFAULT 'active',  -- active | rotated | revoked
    replaced_by INTEGER,                  -- id of the child token, if rotated
    issued_at   INTEGER NOT NULL,
    expires_at  INTEGER NOT NULL,
    rotated_at  INTEGER,
    region      TEXT,                     -- which regional context issued it
    FOREIGN KEY (replaced_by) REFERENCES refresh_tokens(id)
);

CREATE INDEX idx_rt_family ON refresh_tokens(family_id);
CREATE INDEX idx_rt_expiry ON refresh_tokens(expires_at);

The region column earns its keep in incident response. When a reuse event fires, knowing that a family bootstrapped by our US trending worker is suddenly being redeemed by something claiming to be the mobile client tells you immediately what kind of leak you are looking at.

Issuing the Token Pair in PHP 8.4

Issuance happens in two places: initial login (new family) and rotation (existing family). Same code path, the only difference is whether a family ID is passed in. We use firebase/php-jwt for the access-token signing and PHP 8.3+ typed class constants to keep the TTLs honest.

final class TokenIssuer
{
    private const int ACCESS_TTL  = 600;        // 10 minutes
    private const int REFRESH_TTL = 2_592_000;  // 30 days

    public function __construct(
        private readonly PDO $db,
        private readonly string $signingKey,
        private readonly string $keyId,
    ) {}

    public function issuePair(
        int $userId,
        string $clientId,
        string $region,
        ?string $familyId = null,
    ): array {
        $familyId   ??= bin2hex(random_bytes(16));
        $refreshToken = bin2hex(random_bytes(32)); // opaque, never a JWT
        $now          = time();

        $stmt = $this->db->prepare(
            'INSERT INTO refresh_tokens
               (token_hash, family_id, client_id, user_id, issued_at, expires_at, region)
             VALUES (:hash, :family, :client, :user, :now, :exp, :region)'
        );
        $stmt->execute([
            'hash'   => hash('sha256', $refreshToken),
            'family' => $familyId,
            'client' => $clientId,
            'user'   => $userId,
            'now'    => $now,
            'exp'    => $now + self::REFRESH_TTL,
            'region' => $region,
        ]);

        $payload = [
            'iss' => 'api.trendvidstream.com',
            'sub' => (string) $userId,
            'cid' => $clientId,
            'iat' => $now,
            'exp' => $now + self::ACCESS_TTL,
        ];

        return [
            'access_token'  => JWT::encode($payload, $this->signingKey, 'HS256', $this->keyId),
            'refresh_token' => $refreshToken,
            'expires_in'    => self::ACCESS_TTL,
        ];
    }
}

Note the fourth argument to JWT::encode — the kid header. Put it in from day one. When you eventually rotate the signing key itself (and you will), the kid lets your verifier pick the right key from a keyring instead of forcing a flag-day cutover where every outstanding access token dies at once. We keep the current key plus one predecessor in the keyring and retire the old one after ACCESS_TTL has fully elapsed.

The Rotation Endpoint with Reuse Detection

This is the heart of the system, and it is where most implementations get subtly wrong in one of two ways: they forget family revocation, or they ignore concurrency. The whole exchange must be transactional, and the rotated-status update must be a compare-and-swap so that two parallel requests presenting the same token cannot both win.

final class RefreshEndpoint
{
    private const int GRACE_SECONDS = 30;

    public function __construct(
        private readonly PDO $db,
        private readonly TokenIssuer $issuer,
    ) {}

    public function rotate(string $presented, string $region): array
    {
        $hash = hash('sha256', $presented);
        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare(
                'SELECT * FROM refresh_tokens WHERE token_hash = :hash'
            );
            $stmt->execute(['hash' => $hash]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $now = time();

            if ($row === false) {
                throw new AuthException('unknown_token', 401);
            }
            if ($row['status'] === 'revoked' || (int) $row['expires_at'] < $now) {
                throw new AuthException('expired_or_revoked', 401);
            }

            if ($row['status'] === 'rotated') {
                $age = $now - (int) $row['rotated_at'];
                if ($age <= self::GRACE_SECONDS) {
                    // A retry of the same request over a flaky mobile
                    // network, not an attack. Tell the client to re-read
                    // its token store and try again.
                    throw new AuthException('retry_with_new_token', 409);
                }
                // Reuse outside the grace window: assume theft.
                $this->revokeFamily($row['family_id']);
                $this->db->commit();
                $this->alertReuse($row);
                throw new AuthException('token_reuse_detected', 401);
            }

            // Happy path: compare-and-swap the status, then issue a child.
            $upd = $this->db->prepare(
                'UPDATE refresh_tokens
                    SET status = :rot, rotated_at = :now
                  WHERE id = :id AND status = :act'
            );
            $upd->execute([
                'rot' => 'rotated', 'now' => $now,
                'id'  => $row['id'], 'act' => 'active',
            ]);
            if ($upd->rowCount() !== 1) {
                // Lost a race with a parallel refresh in this same window.
                throw new AuthException('retry_with_new_token', 409);
            }

            $pair = $this->issuer->issuePair(
                (int) $row['user_id'],
                $row['client_id'],
                $region,
                $row['family_id'],
            );
            $this->db->commit();
            return $pair;
        } catch (Throwable $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            throw $e;
        }
    }

    private function revokeFamily(string $familyId): void
    {
        $stmt = $this->db->prepare(
            'UPDATE refresh_tokens SET status = :st WHERE family_id = :fam'
        );
        $stmt->execute(['st' => 'revoked', 'fam' => $familyId]);
    }

    private function alertReuse(array $row): void
    {
        error_log(sprintf(
            'TOKEN_REUSE family=%s client=%s user=%d region=%s',
            $row['family_id'],
            $row['client_id'],
            (int) $row['user_id'],
            $row['region'] ?? '-',
        ));
    }
}

A few decisions in there deserve justification.

The grace window is the concession you make to the real world. Strict single-use rotation assumes the client always receives the response containing its new token. On mobile networks that assumption fails constantly: the server commits the rotation, the response dies in transit, the client retries with the token it still has — which is now marked rotated. Without a grace window, every dropped response nukes a legitimate family and forces a full re-login. With a 30-second window, a quick retry gets a 409 telling it to reload its token store (where a parallel request may have already saved the new pair) rather than a family-killing 401. Thirty seconds is long enough to absorb any sane retry policy and short enough that an attacker replaying a day-old stolen token still trips the alarm. Some implementations return the cached child token inside the grace window instead of a 409; we chose the 409 because it keeps the endpoint stateless about response bodies and pushes the recovery into the client, where the single-flight lock (below) handles it cleanly.

The compare-and-swap (WHERE id = :id AND status = :act plus the rowCount() check) matters even under SQLite's writer lock. Two requests can both read the row as active before either writes; the CAS guarantees only one of them transitions it and issues the child, while the loser gets a retryable 409 instead of silently creating a second branch in the family.

And the reuse path commits before throwing. The family revocation must persist even though the request itself fails — rolling it back with the exception would undo the one thing the detection exists to do.

The Client Side Needs to Cooperate

Rotation breaks lazy clients. Any client that fires concurrent requests and refreshes on-demand from multiple threads will trample its own chain. Our region workers — the same Python fleet that pulls trending feeds for eight regions on staggered cron schedules — all share one rule: refreshes are single-flight, and the token pair is persisted atomically before the new access token is used.

import threading
import time
import requests

class TokenManager:
    def __init__(self, base_url, client_id, store):
        self.base_url = base_url
        self.client_id = client_id
        self.store = store              # persists the pair atomically to disk
        self._lock = threading.Lock()

    def access_token(self):
        tok = self.store.load()
        if tok['access_expires_at'] - time.time() > 30:
            return tok['access_token']
        return self._refresh()

    def _refresh(self):
        with self._lock:                # single-flight: one refresh at a time
            tok = self.store.load()     # re-check after acquiring the lock
            if tok['access_expires_at'] - time.time() > 30:
                return tok['access_token']

            for attempt in range(3):
                resp = requests.post(
                    f'{self.base_url}/oauth/token',
                    json={
                        'grant_type': 'refresh_token',
                        'refresh_token': tok['refresh_token'],
                        'client_id': self.client_id,
                    },
                    timeout=10,
                )
                if resp.status_code == 200:
                    body = resp.json()
                    self.store.save({
                        'access_token': body['access_token'],
                        'refresh_token': body['refresh_token'],
                        'access_expires_at': time.time() + body['expires_in'],
                    })
                    return body['access_token']
                if resp.status_code == 409:
                    # Server saw a parallel or retried rotation; another
                    # writer may have saved the fresh pair already.
                    time.sleep(1 + attempt)
                    tok = self.store.load()
                    continue
                if resp.status_code == 401:
                    raise ReauthRequired(resp.json().get('error'))
            raise RefreshFailed('gave up after 3 attempts')

The details that matter, in rough order of how much pain they saved us:

Persist before use. The new refresh token must hit durable storage before the old one is forgotten. If the process crashes between receiving the pair and saving it, the client is locked out of its own family.
Refresh proactively, not reactively. The 30-second buffer before expiry means in-flight requests almost never race an expired token.
Treat 409 as 'reload and retry', 401 as 'stop and re-authenticate'. Conflating them either spams your login endpoint or retries forever against a revoked family.
One refresher per token store. Two cron processes sharing one credential file will fork the chain no matter how polite the server is. Each of our region workers gets its own client_id and its own family.

Operational Cleanup and the Cron Angle

Rotation multiplies your row count: every refresh leaves a tombstone. At a 10-minute access TTL, one busy client writes ~144 rows a day. Left alone, the table becomes a slowly growing log of every session your API ever had. We prune from the same staggered cron jobs that refresh the regional video feeds — they already run every few hours per region, so the auth maintenance rides along for free.

// cron/prune_tokens.php — rides along with the multi-region fetch cron.
$db = new PDO('sqlite:' . __DIR__ . '/../data/auth.db');
$db->exec('PRAGMA journal_mode = WAL');

// Keep revoked/rotated rows 90 days past expiry for forensics,
// then let them go.
$cutoff = time() - 90 * 86400;
$db->exec('DELETE FROM refresh_tokens WHERE expires_at < ' . $cutoff);
$db->exec('PRAGMA optimize');

Why keep tombstones 90 days? Because the rotated chain is your audit trail. When a reuse event fires, walking the family through replaced_by tells you exactly when the chain forked, which region issued each link, and how long the attacker held the token before redeeming it. Deleting rotated rows eagerly throws away the only forensic record the protocol gives you.

One deployment note, since our stack is unusual: we ship code to all production hosts over FTP with lftp mirror scripts, which means secrets management has to be deliberate. The JWT signing key and the SQLite auth database are both excluded from the mirror set — the key lives in a per-site env file that is provisioned once by hand, and the database is born on the host. If your deploy pipeline is rsync, FTP, or anything else that mirrors a directory tree, audit the exclusion list before you ship an auth system; the failure mode is overwriting production token state with a stale local copy, which revokes every client at once and looks exactly like an outage.

Finally, monitor the reuse events as a product signal, not just a security one. After we shipped rotation, our first three TOKEN_REUSE alerts were not attackers — they were a partner integration running two replicas against one credential. The protocol caught a misconfiguration that would otherwise have surfaced as intermittent, unexplainable 401s in their logs.

Conclusion

Refresh-token rotation is one of the rare security upgrades where the hard part is not cryptography but state and concurrency: a single-use chain with family revocation, a compare-and-swap on the status transition, a grace window for retried requests, and clients disciplined enough to single-flight their refreshes and persist before forgetting. None of it needs heavyweight infrastructure — the implementation above runs in production on shared hosting with SQLite in WAL mode behind a video discovery API serving eight regions. The payoff showed up in week one: stolen-token replay went from invisible to a logged, family-revoking, alert-firing event. If your API still hands out 30-day static refresh tokens, the schema and two PHP classes above are genuinely an afternoon of work — and the first reuse alert they fire will justify it.

Building a Video URL Canonicalization Pipeline for Deduplicated Ingest

ahmet gedik — Tue, 30 Jun 2026 01:00:02 +0000

The duplicate that wasn't a duplicate

Last quarter our ingest counters at ViralVidVault told a confusing story: the videos table grew by roughly 38% week-over-week, but unique watch sessions barely moved. When I pulled a sample, the cause was embarrassingly mundane. The same YouTube clip had landed in our database eleven times. Once as https://www.youtube.com/watch?v=dQw4w9WgXcQ, once as https://youtu.be/dQw4w9WgXcQ, once with ?feature=share, once with a UTM-stuffed referral query string from a partner feed, once as youtube.com/shorts/dQw4w9WgXcQ, once through m.youtube.com, and several more with trailing slashes, fragment timestamps, and mixed-case hosts.

Every one of those rows pointed at the same underlying asset. To a trend-tracking product that ranks videos by velocity, eleven phantom rows is not a cosmetic bug. It splits engagement signals across duplicates, poisons the "trending" ranking, wastes thumbnail-fetch quota, and inflates storage. For a GDPR-first European product where we keep ingest lean on purpose, it also meant we were storing more URLs than we needed to justify.

The fix is a canonicalization pipeline: a deterministic function that maps any incoming video URL to one stable identity, applied at the edge and again at write time, backed by a database constraint that makes duplicates structurally impossible. This post walks through how we built ours on PHP 8.4, SQLite in WAL mode, LiteSpeed, and a Cloudflare Worker at the edge.

What "canonical" actually means for a video URL

Canonicalization is not the same as URL normalization. Normalization (lowercasing the host, resolving . and .. path segments, percent-encoding consistently) is the generic, RFC 3986-flavored part. Canonicalization is the domain-specific part: knowing that for YouTube the only thing that identifies the asset is the 11-character video ID, and that everything else in the URL is noise we are free to discard.

So our pipeline has two layers:

Structural normalization that applies to every URL regardless of platform: lowercase scheme and host, strip the default port, drop the fragment, sort and filter query parameters, remove tracking junk.
Platform extraction that recognizes a known provider (YouTube, Vimeo, Dailymotion, TikTok, and a few European players like Rutube-style hosts) and reduces the URL to a provider:id tuple plus a single rebuilt canonical URL.

The output of the pipeline is two things we store on every row: a short opaque canonical_key (used as a unique index) and a clean canonical_url (used for display, embeds, and outbound links). The key is what prevents duplicates; the URL is what humans and crawlers see.

A few rules we settled on after getting burned:

The canonical key must be stable across reorderings. ?a=1&b=2 and ?b=2&a=1 are the same resource.
It must be case-correct, not case-blind. Hosts are case-insensitive, but YouTube video IDs are case-sensitive. dQw4w9WgXcQ is not dqw4w9wgxcq. Lowercasing the whole URL is a classic way to silently corrupt your identity layer.
It must fail safe. An unrecognized provider should still produce a deterministic key from the normalized URL, never throw, never collapse two genuinely different videos into one.

The structural normalizer in PHP 8.4

Here is the base normalizer. It does the provider-agnostic work and returns a structured value object. I am leaning on PHP 8.4 features: readonly properties, constructor promotion, and a typed enum for the provider.

<?php
declare(strict_types=1);

enum VideoProvider: string
{
    case YouTube     = 'youtube';
    case Vimeo       = 'vimeo';
    case Dailymotion = 'dailymotion';
    case TikTok      = 'tiktok';
    case Unknown     = 'unknown';
}

final readonly class CanonicalVideo
{
    public function __construct(
        public VideoProvider $provider,
        public string $videoId,
        public string $canonicalUrl,
        public string $canonicalKey,
    ) {}
}

final class UrlNormalizer
{
    /** Query params we always strip before hashing. */
    private const TRACKING_PREFIXES = ['utm_', 'fbclid', 'gclid', 'mc_', '_hs'];
    private const TRACKING_EXACT = [
        'feature', 'app', 'si', 'pp', 'ref', 'ref_src',
        'source', 'spm', 'igshid', 'context',
    ];

    /** Normalize host + path + query. Does NOT touch provider semantics. */
    public function normalize(string $raw): array
    {
        $raw = trim($raw);
        $parts = parse_url($raw);
        if ($parts === false || !isset($parts['host'])) {
            throw new InvalidArgumentException("unparseable url: {$raw}");
        }

        $scheme = strtolower($parts['scheme'] ?? 'https');
        $host   = strtolower($parts['host']);
        $host   = preg_replace('/^(www|m|mobile)\./', '', $host);

        // Drop default ports.
        $port = $parts['port'] ?? null;
        if (($scheme === 'https' && $port === 443) || ($scheme === 'http' && $port === 80)) {
            $port = null;
        }

        $path = $parts['path'] ?? '/';
        $path = rtrim($path, '/') ?: '/';

        $query = $this->cleanQuery($parts['query'] ?? '');

        return [
            'scheme' => $scheme,
            'host'   => $host,
            'port'   => $port,
            'path'   => $path,
            'query'  => $query, // associative, already filtered + sorted
        ];
    }

    private function cleanQuery(string $query): array
    {
        if ($query === '') {
            return [];
        }
        parse_str($query, $params);
        $kept = [];
        foreach ($params as $key => $value) {
            $lower = strtolower((string) $key);
            if (in_array($lower, self::TRACKING_EXACT, true)) {
                continue;
            }
            foreach (self::TRACKING_PREFIXES as $prefix) {
                if (str_starts_with($lower, $prefix)) {
                    continue 2;
                }
            }
            $kept[$key] = $value;
        }
        ksort($kept); // stable ordering -> stable hash
        return $kept;
    }
}

The important details are the small ones. We strip www, m, and mobile host prefixes because they are routing artifacts, not identity. We drop default ports so :443 and the absence of a port hash identically. We rtrim trailing slashes so /watch and /watch/ agree. And we ksort the surviving query parameters so the hash is independent of parameter order. Tracking parameters get dropped wholesale; that single step killed about 60% of our duplicates because partner feeds love appending utm_*.

Platform extraction: reducing to provider plus ID

The normalizer gives us a clean URL skeleton. The extractor turns it into identity. This is the part most people get wrong by trying to write one mega-regex. Instead, dispatch on host, then apply the smallest rule that matches.

<?php
declare(strict_types=1);

final class VideoCanonicalizer
{
    public function __construct(private UrlNormalizer $normalizer) {}

    public function canonicalize(string $raw): CanonicalVideo
    {
        $n = $this->normalizer->normalize($raw);
        [$provider, $id] = $this->extract($n['host'], $n['path'], $n['query']);

        if ($provider !== VideoProvider::Unknown && $id !== '') {
            $url = $this->rebuild($provider, $id);
            $key = $provider->value . ':' . $id;
            return new CanonicalVideo($provider, $id, $url, $key);
        }

        // Fail-safe path: deterministic key from the normalized skeleton.
        $url = $this->rebuildGeneric($n);
        $key = 'url:' . substr(hash('sha256', $url), 0, 24);
        return new CanonicalVideo(VideoProvider::Unknown, '', $url, $key);
    }

    /** @return array{0: VideoProvider, 1: string} */
    private function extract(string $host, string $path, array $query): array
    {
        return match (true) {
            str_ends_with($host, 'youtube.com') => $this->youtube($path, $query),
            $host === 'youtu.be'                => [VideoProvider::YouTube, ltrim($path, '/')],
            str_ends_with($host, 'vimeo.com')   => [VideoProvider::Vimeo, $this->lastSegment($path)],
            str_ends_with($host, 'dailymotion.com') => [VideoProvider::Dailymotion, $this->dmId($path)],
            $host === 'dai.ly'                  => [VideoProvider::Dailymotion, ltrim($path, '/')],
            str_ends_with($host, 'tiktok.com')  => [VideoProvider::TikTok, $this->tiktokId($path)],
            default                             => [VideoProvider::Unknown, ''],
        };
    }

    private function youtube(string $path, array $query): array
    {
        // /watch?v=ID, /shorts/ID, /embed/ID, /live/ID
        if ($path === '/watch' && isset($query['v'])) {
            return [VideoProvider::YouTube, (string) $query['v']];
        }
        if (preg_match('#^/(shorts|embed|live|v)/([A-Za-z0-9_-]{11})#', $path, $m)) {
            return [VideoProvider::YouTube, $m[2]];
        }
        return [VideoProvider::Unknown, ''];
    }

    private function dmId(string $path): string
    {
        // /video/x8abcde or /video/x8abcde_title-slug
        if (preg_match('#/video/([a-z0-9]+)#i', $path, $m)) {
            return $m[1];
        }
        return '';
    }

    private function tiktokId(string $path): string
    {
        return preg_match('#/video/(\d+)#', $path, $m) ? $m[1] : '';
    }

    private function lastSegment(string $path): string
    {
        $seg = substr(strrchr($path, '/') ?: '', 1);
        return ctype_digit($seg) ? $seg : '';
    }

    private function rebuild(VideoProvider $p, string $id): string
    {
        return match ($p) {
            VideoProvider::YouTube     => "https://www.youtube.com/watch?v={$id}",
            VideoProvider::Vimeo       => "https://vimeo.com/{$id}",
            VideoProvider::Dailymotion => "https://www.dailymotion.com/video/{$id}",
            VideoProvider::TikTok      => "https://www.tiktok.com/video/{$id}",
            VideoProvider::Unknown     => '',
        };
    }

    private function rebuildGeneric(array $n): string
    {
        $url = $n['scheme'] . '://' . $n['host'];
        if ($n['port'] !== null) {
            $url .= ':' . $n['port'];
        }
        $url .= $n['path'];
        if ($n['query'] !== []) {
            $url .= '?' . http_build_query($n['query']);
        }
        return $url;
    }
}

Notice we rebuild a fresh canonical URL from the ID rather than reusing the input. This matters: an attacker (or a sloppy partner) cannot smuggle an open-redirect or a malicious query string into our stored URL, because we throw the input away once we have the ID. The stored URL is always one we constructed from a hardcoded template. For the unknown-provider case we hash the normalized skeleton into a 24-character key, which is collision-resistant enough for an ingest table and short enough to index cheaply.

Making duplicates impossible at the storage layer

Application-level dedup is necessary but not sufficient. Two ingest workers can canonicalize the same URL concurrently, both check "does this key exist," both see no, and both insert. The only reliable guard is a database constraint. With SQLite in WAL mode this is cheap and the concurrency story is good: WAL lets readers continue while a writer commits, which suits our bursty fetch-then-write ingest pattern.

CREATE TABLE IF NOT EXISTS videos (
    id            INTEGER PRIMARY KEY,
    canonical_key TEXT NOT NULL,
    canonical_url TEXT NOT NULL,
    provider      TEXT NOT NULL,
    video_id      TEXT NOT NULL DEFAULT '',
    title         TEXT,
    first_seen    INTEGER NOT NULL,
    last_seen     INTEGER NOT NULL,
    view_velocity REAL NOT NULL DEFAULT 0
);

-- The structural guard: one row per canonical identity.
CREATE UNIQUE INDEX IF NOT EXISTS idx_videos_canonical
    ON videos(canonical_key);

The write path then becomes an idempotent upsert. We never "check then insert"; we let the unique index arbitrate and use ON CONFLICT to merge signal into the existing row instead of failing:

INSERT INTO videos (canonical_key, canonical_url, provider, video_id, title, first_seen, last_seen)
VALUES (:key, :url, :provider, :vid, :title, :now, :now)
ON CONFLICT(canonical_key) DO UPDATE SET
    last_seen     = excluded.last_seen,
    view_velocity = view_velocity + 1,
    title         = COALESCE(videos.title, excluded.title);

This is the whole game. Even if ten workers race on the same trending clip, the database collapses them into one row and accumulates velocity correctly. The first writer sets first_seen; every subsequent sighting bumps last_seen and velocity. We get accurate trend tracking for free, and the storage footprint stays honest.

Pushing canonicalization to the edge

Most of our duplicate URLs arrive from share links and partner referrals that hit the site directly. Cleaning them at write time works, but we also normalize at the edge in a Cloudflare Worker so that cache keys collapse too. If youtu.be/X and youtube.com/watch?v=X produce different cache keys, our LiteSpeed origin gets hit twice for the same logical page. A tiny edge normalizer fixes the cache-hit ratio before a request ever reaches PHP.

The edge logic is intentionally a thin subset of the full pipeline; it only needs to agree on identity, not rebuild display URLs. We keep the authoritative version in Go because we also run a batch backfill tool that re-canonicalizes historical rows, and sharing a single tested implementation between the backfiller and an edge sidecar avoids drift.

package canon

import (
    "crypto/sha256"
    "encoding/hex"
    "net/url"
    "regexp"
    "strings"
)

var ytShort = regexp.MustCompile(`^/(shorts|embed|live|v)/([A-Za-z0-9_-]{11})`)

// Key returns a stable canonical key for a video URL, or a hashed
// fallback for unknown providers. It never panics on bad input.
func Key(raw string) string {
    u, err := url.Parse(strings.TrimSpace(raw))
    if err != nil || u.Host == "" {
        return hashKey(strings.ToLower(raw))
    }
    host := strings.ToLower(u.Host)
    host = strings.TrimPrefix(host, "www.")
    host = strings.TrimPrefix(host, "m.")

    switch {
    case strings.HasSuffix(host, "youtube.com"):
        if u.Path == "/watch" {
            if v := u.Query().Get("v"); v != "" {
                return "youtube:" + v
            }
        }
        if m := ytShort.FindStringSubmatch(u.Path); m != nil {
            return "youtube:" + m[2]
        }
    case host == "youtu.be":
        if id := strings.Trim(u.Path, "/"); id != "" {
            return "youtube:" + id
        }
    }
    // Fallback: scheme-stripped, host-normalized, path only.
    return hashKey(host + strings.TrimRight(u.Path, "/"))
}

func hashKey(s string) string {
    sum := sha256.Sum256([]byte(s))
    return "url:" + hex.EncodeToString(sum[:])[:24]
}

The Go and PHP implementations share a contract enforced by a shared fixtures file: a JSON list of input -> expected_key pairs that both test suites load. If the edge and the origin ever disagree on a key, the build fails. That fixture file has saved us twice when someone "improved" a regex on one side only.

Verifying canonicalization with a property test

The scary failure mode is not a missed duplicate; it is a false merge, where two genuinely different videos collapse into one key. Unit tests with hand-picked URLs catch the obvious cases, but I sleep better with a property-based check. The invariant: distinct video IDs must always produce distinct keys, and the same ID under any URL shape must always produce the same key. Here is the batch validator we run nightly against a sample of the live table, in Python against the SQLite file directly.

import sqlite3
import itertools

def load_pairs(db_path: str):
    con = sqlite3.connect(db_path)
    con.row_factory = sqlite3.Row
    rows = con.execute(
        "SELECT canonical_key, provider, video_id, canonical_url FROM videos"
    ).fetchall()
    con.close()
    return rows

def check_invariants(rows) -> list[str]:
    errors = []
    # Invariant 1: one key never maps to two different (provider, video_id).
    by_key: dict[str, set] = {}
    for r in rows:
        ident = (r["provider"], r["video_id"])
        by_key.setdefault(r["canonical_key"], set()).add(ident)
    for key, idents in by_key.items():
        # ignore unknown-provider rows, whose id is empty by design
        real = {i for i in idents if i[1] != ""}
        if len(real) > 1:
            errors.append(f"FALSE MERGE on {key}: {sorted(real)}")

    # Invariant 2: a known (provider, video_id) maps to exactly one key.
    by_ident: dict[tuple, set] = {}
    for r in rows:
        if r["video_id"] == "":
            continue
        ident = (r["provider"], r["video_id"])
        by_ident.setdefault(ident, set()).add(r["canonical_key"])
    for ident, keys in by_ident.items():
        if len(keys) > 1:
            errors.append(f"SPLIT IDENTITY for {ident}: {sorted(keys)}")
    return errors

if __name__ == "__main__":
    problems = check_invariants(load_pairs("data/videos.db"))
    if problems:
        for p in problems:
            print(p)
        raise SystemExit(1)
    print("canonicalization invariants hold")

Invariant 1 catches false merges; invariant 2 catches the opposite, an identity that somehow ended up under two keys (which would happen if you forgot to backfill after changing a rule). Running both nightly turns a silent data-corruption bug into a loud CI failure.

A few hard-won lessons

Never lowercase the whole URL. Host is case-insensitive; the video ID and path often are not. Lowercase the host only.
Rebuild, do not reuse. Construct the stored canonical URL from a template plus the extracted ID. It sanitizes input and gives you one display format forever.
Strip tracking before hashing. A single utm_* filter eliminated the majority of our duplicates. Maintain the strip-list as data, not code.
Let the database arbitrate races. A unique index plus ON CONFLICT DO UPDATE is more correct than any application-level check-then-insert, and under SQLite WAL it is fast.
Share one implementation across tiers. Edge (Go/Worker) and origin (PHP) must agree byte-for-byte on keys. Enforce it with a shared fixtures file in both test suites.
Test for false merges explicitly. The dangerous bug is collapsing two different videos, not missing one duplicate. A property check is worth more than fifty hand-written cases.

The pipeline took about two days to build and a week to roll out behind a backfill. Our videos table shrank by roughly a third overnight, the trending ranking stabilized because engagement stopped fragmenting across duplicates, and our Cloudflare cache-hit ratio climbed several points because share-link variants now collapse to one cache key. For a lean, GDPR-conscious product, that is the kind of change that pays for itself in storage, quota, and signal quality all at once. Canonicalization is unglamorous plumbing, but getting your identity layer right is the difference between a trend tracker that measures reality and one that measures its own duplicates.

Hot Reloading Go Video Services with Air Without Losing State

ahmet gedik — Mon, 29 Jun 2026 16:00:01 +0000

The 40-Second Feedback Loop That Was Killing My Productivity

Last quarter I rewrote the trending-video ingestion service at TopVideoHub from a PHP cron worker into a long-running Go service. The reason was simple: we aggregate trending video metadata across roughly a dozen Asia-Pacific regions, and each region has its own polling cadence, its own language tokenizer quirks, and its own rate-limit budget. A request-scoped PHP process spinning up per cron tick could not hold the in-memory region schedulers, the warm HTTP connection pools to upstream providers, or the prepared statement cache against our SQLite store. Go could.

The trouble showed up on day two. Every time I touched a handler, a region poller, or a CJK normalization helper, I had to Ctrl-C the running binary, run go build ./cmd/ingest, wait for the compile, restart it, and then wait again while the service re-warmed its connection pools and re-read the region config from disk. On my machine that round trip was somewhere between 30 and 45 seconds. When you are debugging why Japanese trending titles are getting tokenized as one giant token, you do that loop forty or fifty times an hour. The math is brutal: I was spending more time waiting for restarts than reading code.

This is the exact problem Air solves. It watches your source tree, rebuilds the binary on change, and restarts the process for you. But the naive setup most tutorials show you will actively hurt a video service: it rebuilds on irrelevant file changes, it kills in-flight upstream fetches mid-write, and it does nothing to preserve the slow-to-rebuild state that made you choose a long-running service in the first place. This article is how I configured Air for a real ingestion service, what broke, and the patterns that made the inner loop feel instant.

What Air Actually Does, and Why a Video Service Stresses It

Air is a file watcher plus a build-and-restart supervisor. The loop is: detect a write under a watched path, debounce, run your build command, and if the build succeeds, kill the old process and exec the new one. Conceptually trivial. The friction comes from the assumptions.

A typical web tutorial assumes your service is stateless and starts in milliseconds. A video aggregation service violates both assumptions:

Startup is expensive. We open a SQLite database with FTS5 enabled, run a quick integrity check, warm a pool of HTTP clients with custom transports, and load region schedules. Cold start is two to three seconds before the first poll fires.
There is real I/O in flight. When Air kills the process, there may be a half-completed batch insert of trending videos for the JP region. Kill it at the wrong moment and you get a partial write or a locked database file.
Generated and data files live in the tree. Our repo carries a data/ directory with the dev SQLite file, a tmp/ build output dir, and generated tokenizer dictionaries. If Air watches those, the service rebuilds itself in an infinite loop the moment it writes its own database.

So the goal is not "turn on Air." The goal is to watch exactly the Go source that matters, rebuild fast, and shut down cleanly enough that a restart never corrupts the dev database.

A Minimal but Honest Air Configuration

Air reads a .air.toml from the project root. Here is the one I run, trimmed of comments so you can see the shape, then I will walk through the decisions.

root = "."
tmp_dir = "tmp"

[build]
  # Build only the ingest entrypoint, output to tmp/
  cmd = "go build -o ./tmp/ingest ./cmd/ingest"
  bin = "./tmp/ingest"
  full_bin = "APP_ENV=dev ./tmp/ingest --config=config/dev.yaml"

  # Only these extensions trigger a rebuild
  include_ext = ["go", "yaml", "sql"]

  # Never watch these — they cause rebuild loops or noise
  exclude_dir = ["tmp", "data", "vendor", "testdata", "node_modules", ".git"]
  exclude_regex = ["_test\\.go", "_gen\\.go"]

  # Debounce: wait 200ms after the last write before building
  delay = 200

  # Give the old process time to shut down gracefully
  kill_delay = "5s"
  send_interrupt = true
  stop_on_error = true

[log]
  time = true

[misc]
  clean_on_exit = true

The lines that matter most for a video service are the last few in [build].

include_ext is an allowlist, not the default catch-all. I include yaml because our region schedules and rate-limit budgets live in config, and I genuinely want a restart when I tweak a polling interval. I include sql because we keep migration files and FTS5 setup statements as .sql and embed them — changing one should rebuild. Everything else is excluded by omission.

exclude_dir lists data and tmp explicitly. This is the single most important line. The service writes its dev SQLite file into data/, and Air writes the compiled binary into tmp/. If either is watched, you get a rebuild loop: the service runs, writes the DB, Air sees the write, rebuilds, restarts, which writes the DB again. I have watched a junior engineer lose an afternoon to exactly this before realizing the watcher was chasing its own tail.

exclude_regex drops _test.go and our generated _gen.go files. I do not want a restart of the running service every time I save a test file — tests run in a separate go test watcher. And the generated tokenizer files change as a side effect of builds, so watching them re-triggers builds.

send_interrupt = true plus kill_delay = "5s" is the graceful-shutdown contract. By default Air sends SIGKILL, which is a hard stop with no cleanup. With send_interrupt, Air sends SIGINT (and on supported platforms a terminate signal), waits up to kill_delay, and only then escalates. That five-second window is what lets an in-flight JP batch insert finish before the process dies.

Making the Service Honor the Interrupt

Air sending SIGINT does nothing useful unless your service listens for it. This is where most setups quietly corrupt their dev database without anyone noticing, because the corruption is intermittent. Here is the shutdown path in our ingest service's main.

package main

import (
    "context"
    "log/slog"
    "os"
    "os/signal"
    "syscall"
    "time"
)

func main() {
    cfg := mustLoadConfig()
    app, err := NewApp(cfg) // opens SQLite, warms pools, loads region schedules
    if err != nil {
        slog.Error("startup failed", "err", err)
        os.Exit(1)
    }

    // Root context cancelled on SIGINT/SIGTERM — the signal Air sends.
    ctx, stop := signal.NotifyContext(context.Background(),
        syscall.SIGINT, syscall.SIGTERM)
    defer stop()

    // Start region pollers; they select on ctx.Done().
    app.Run(ctx)

    <-ctx.Done()
    slog.Info("interrupt received, draining")

    // Bounded drain: let in-flight batch inserts finish, then close cleanly.
    drainCtx, cancel := context.WithTimeout(context.Background(), 4*time.Second)
    defer cancel()

    if err := app.Shutdown(drainCtx); err != nil {
        slog.Error("unclean shutdown", "err", err)
        os.Exit(1)
    }
    slog.Info("drained, exiting")
}

The key relationship is between Air's kill_delay = "5s" and the 4*time.Second drain timeout. The drain must finish comfortably inside the kill delay, otherwise Air escalates to SIGKILL and you are back to hard kills. I keep a one-second safety margin.

The Shutdown method does the boring but essential work: it stops accepting new poll ticks, waits for any goroutine currently writing a batch to release its transaction, runs a PRAGMA wal_checkpoint(TRUNCATE) so the WAL file does not grow unbounded across dozens of dev restarts, and closes the database handle. Closing the SQLite handle cleanly is what prevents the database is locked errors that otherwise appear randomly when Air restarts mid-write.

A subtle point: because we use SQLite in WAL mode for the dev database, an unclean kill leaves the -wal and -shm sidecar files behind. SQLite recovers from them on next open, but if a restart happens during checkpoint you can hit a brief lock. The graceful drain plus checkpoint-on-shutdown removes that class of flakiness entirely. This mirrors the durability discipline we run in production, where the same store backs our FTS5 search index — the dev loop should not be sloppier than prod just because it is dev.

Cutting the Rebuild Time, Not Just Automating It

Air restarting for you is only half the win. If each rebuild still takes 30 seconds because you compile the whole monorepo, you have automated the slowness, not removed it. Three changes took our rebuild from ~12 seconds to under 2.

First, build a single binary, not the world. The cmd = "go build -o ./tmp/ingest ./cmd/ingest" line targets one entrypoint. Earlier I had go build ./... out of laziness, which recompiled every command in the repo on every save. Targeting ./cmd/ingest only rebuilds that package and its changed dependencies.

Second, lean on Go's build cache and skip work that does not help the inner loop. I disable inlining and optimizations during dev builds, which speeds compilation and improves debugger stepping:

[build]
  cmd = "go build -gcflags='all=-N -l' -o ./tmp/ingest ./cmd/ingest"

The -N -l flags turn off optimization and inlining. You would never ship this, but for a dev rebuild it shaves time and makes Delve breakpoints land where you expect. The first build after a go clean -cache is still slow; every incremental one after that is fast because Go caches compiled packages.

Third — and this is the one people miss — keep your slow startup work out of the rebuild path by separating it from the thing that changes often. Our CJK tokenizer dictionary loads from disk at startup and takes about 800ms to parse. I do not want to pay that on every restart while iterating on a handler. So I load it once into a memory-mapped file that survives across restarts when possible, and where that is not possible, I gate it behind a fast-path check. The principle generalizes: anything expensive and unchanging should not be recomputed on every Air restart.

Here is a compact example of that gating pattern — a lazy, restart-aware loader for the region dictionary:

// dictCache loads the CJK tokenizer dictionary at most once per binary
// lifetime, and logs how long startup actually cost so I can see
// whether a given restart paid the full price or hit a warm path.
type dictCache struct {
    once sync.Once
    dict *Dictionary
    err  error
}

func (c *dictCache) Load(path string) (*Dictionary, error) {
    c.once.Do(func() {
        start := time.Now()
        c.dict, c.err = parseDictionary(path) // ~800ms cold
        slog.Info("dictionary loaded",
            "path", path,
            "took", time.Since(start).Round(time.Millisecond))
    })
    return c.dict, c.err
}

The logging matters more than it looks. When I can see took=812ms versus took=3ms in the restart logs, I immediately know whether my config change accidentally invalidated the warm path. Observability of the inner loop is how you keep the inner loop fast over months, not just on the day you set it up.

Watching More Than Go — Templates, Config, and Migrations

A video service is rarely pure Go. Ours renders a handful of admin HTML templates, reads YAML region config, and runs SQL migrations. I want a restart on the right subset of these without drowning in false triggers.

The include_ext = ["go", "yaml", "sql"] line handles config and migrations. For templates, I made a deliberate choice not to restart the whole service. Templates are reparsed lazily on each admin request in dev mode via a build tag, so editing a template shows up on the next page refresh with no rebuild at all. Mixing two strategies — full restart for code, hot reparse for templates — is faster than forcing everything through Air.

The decision rule I use for any file type is a short checklist:

Does changing it require recompilation? Go source and embedded SQL do. Templates loaded at runtime do not.
Does a change need fresh in-memory state? Region schedules and rate budgets do, so YAML triggers a restart. A CSS tweak does not.
Does the file get written by the service itself? The dev SQLite file and the WAL sidecars do, so they are excluded — watching them is the rebuild-loop trap.

Run every file type through those three questions and your .air.toml writes itself. The mistake is treating Air as all-or-nothing. It is a tool for the subset of changes that genuinely need a recompile-and-restart, and the more precisely you scope it, the faster and quieter it is.

Running Air Alongside the Rest of the Stack

The ingest service does not run alone. In dev I also run the PHP frontend (the same LiteSpeed-backed stack that serves the public site), a local Cloudflare-equivalent proxy for testing cache headers, and a go test watcher. Trying to cram all of that into Air is a mistake — Air supervises one process. I use a small process manager and let Air own only the Go ingest service.

The one cross-cutting concern is ports and locks. When Air restarts the ingest service, the new process must be able to re-bind the local admin port and re-open the SQLite file immediately. The graceful shutdown above is what guarantees the old process has released both before the new one starts. Without it, you get address already in use or database is locked on roughly one restart in ten — frequent enough to be maddening, rare enough that you blame your code instead of the watcher.

One more practical tip: pipe Air's output through with timestamps ([log] time = true) and keep the terminal visible. The restart cadence becomes a diagnostic. If I save a handler and see a restart take eight seconds instead of two, something pulled a heavy dependency into the rebuild path, and I want to know immediately, not three weeks later when the inner loop has silently degraded.

What This Bought Us

The honest accounting: setting up Air properly took an afternoon, most of it spent on the graceful-shutdown path and chasing one rebuild loop caused by watching data/. In exchange, the edit-to-running-service loop went from 30-45 seconds to about 2 seconds, and the intermittent database is locked errors that used to punctuate a debugging session disappeared because restarts now drain cleanly.

The lessons that generalize beyond our stack:

Air automates the loop; you still have to make the loop fast. Build one binary, use the build cache, and keep expensive startup work off the rebuild path.
Graceful shutdown is not optional for a stateful service. Match your drain timeout to Air's kill_delay, handle SIGINT, and checkpoint your database on the way out.
Scope the watcher precisely. Allowlist extensions, exclude self-written data and build directories, and run each file type through the recompile/state/self-written checklist.
Instrument the inner loop. Log startup cost per restart so degradation is visible the day it happens.

A long-running Go service was the right call for region-aware video ingestion, but only because the development loop stayed tight. Air, configured with the same care you would give a production process, is what keeps a stateful service pleasant to work on day after day.

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

ahmet gedik — Mon, 29 Jun 2026 04:00:02 +0000

Our video metadata API was fast in Frankfurt and miserable everywhere else. A user in São Paulo asking for the trending feed was waiting 380–450ms just for the round trip to a single origin, before SQLite even touched the FTS5 index. We run DailyWatch, a free video discovery platform with an English-speaking audience spread across the Americas, Europe, and South/East Asia, and a single-origin reverse proxy was the bottleneck nobody wanted to own. This is the write-up of how we replaced an aging nginx config with Caddy 2 as a region-aware reverse proxy edge, what actually moved the latency numbers, and the code that holds it together.

I'll be specific about the stack because the choices only make sense in context: PHP 8.4 application servers behind LiteSpeed, SQLite with FTS5 for the search index, and Cloudflare sitting in front of everything. Caddy 2 slots in between Cloudflare and the origins, and that placement is the whole point.

The latency problem that started this

The API itself was fine. A /v1/feed?region=BR request resolved in 12–18ms on the box. The pain was geographic. We had origins in three places — eu-fra, us-iad, and ap-sin — but the proxy in front of them was dumb. Requests landed wherever Cloudflare's nearest data center routed them, and then got proxied to whichever origin the config happened to list first. Half our Asian traffic was crossing two oceans to hit Frankfurt.

The symptoms were concrete:

p95 latency for non-EU regions sat above 350ms even on cache misses that should have cost 20ms.
When eu-fra had a deploy hiccup, nginx kept routing to it for ~30 seconds because passive health checks only fail open after enough errors accumulate.
Compression and cache-control headers were inconsistent because three different origin configs each set them slightly differently.

We wanted one component that owned TLS termination toward the origins, health-checked failover, region-aware upstream selection, and a single source of truth for caching headers. Caddy 2 does all of that with a config file you can actually read six months later.

Why Caddy 2 over nginx for this job

I'm not religious about proxies. nginx is excellent. But for a multi-region API edge, Caddy 2 had three properties that mattered more than raw throughput (both are far faster than our origins anyway):

Active health checks are first-class. nginx open-source only does passive checks unless you pay for nginx Plus. Caddy does active HTTP probing out of the box, so a sick origin is pulled from rotation before a user request fails on it.
The reverse_proxy directive understands upstream policies natively. Round-robin, least-connections, first-available, IP-hash, and header-based selection are config flags, not Lua modules.
Structured JSON config under the hood with a human Caddyfile on top. We generate part of our config from a script (more on that below), and Caddy's admin API lets us push upstream changes without a full reload.

The one thing to know going in: Caddy terminates TLS automatically, which is wonderful for public endpoints but something you must explicitly control when you sit behind Cloudflare. We do, and I'll show the relevant block.

A region-aware Caddyfile

Here is the core of the edge config. Cloudflare passes the visitor's country in CF-IPCountry, and we map that header to an upstream group. Caddy's @named matchers make this clean — each region matcher selects a different reverse_proxy with its own ordered upstream list, so failover stays inside the geographically closest set first.

{
    # We're behind Cloudflare (Flexible/Full). Don't auto-issue certs on the
    # internal hostname; Cloudflare handles the public TLS.
    auto_https off
    servers {
        trusted_proxies static private_ranges
    }
}

api.dailywatch.video:80 {
    # Trust Cloudflare's forwarded client IP.
    request_header +X-Real-IP {http.request.header.CF-Connecting-IP}

    # Region matchers driven by Cloudflare's geo header.
    @asia header CF-IPCountry SG TW HK JP KR VN TH ID MY PH IN
    @americas header CF-IPCountry US CA BR MX AR CL CO PE
    # Everything else (incl. EU) falls through to the default handler.

    handle @asia {
        reverse_proxy ap-sin.internal:8080 eu-fra.internal:8080 {
            lb_policy first
            health_uri /healthz
            health_interval 3s
            health_timeout 2s
            health_status 200
            header_up X-Edge-Region asia
        }
    }

    handle @americas {
        reverse_proxy us-iad.internal:8080 eu-fra.internal:8080 {
            lb_policy first
            health_uri /healthz
            health_interval 3s
            header_up X-Edge-Region americas
        }
    }

    handle {
        reverse_proxy eu-fra.internal:8080 us-iad.internal:8080 {
            lb_policy first
            health_uri /healthz
            health_interval 3s
            header_up X-Edge-Region eu
        }
    }

    encode zstd gzip
    header {
        Cache-Control "public, max-age=60, stale-while-revalidate=600"
        Vary "Accept-Encoding, CF-IPCountry"
        -Server
    }
}

The key decision is lb_policy first. Combined with active health checks, first means "always use the closest origin, fall to the next only when the closest is unhealthy." Round-robin would have spread Asian traffic back to Frankfurt, which is exactly the problem we were solving. first gives us geographic affinity and automatic failover in one policy.

Note Vary: CF-IPCountry. Without it, a cached response from one region could be served to another. We add the country to the Vary key so Cloudflare and any downstream cache key correctly per region.

Health-checked upstreams and failover

The health_uri /healthz block is doing the heavy lifting. Every three seconds Caddy probes each origin. The origin's health endpoint should report more than "the process is up" — it should report whether this region's data plane is actually serviceable. On our PHP origins the health check verifies the SQLite handle and that the FTS5 index responds, because a locked database is the failure mode that actually hurts us.

<?php
// healthz.php — origin health endpoint Caddy probes every 3s.
// Returns 200 only when SQLite + FTS5 are genuinely serviceable.
declare(strict_types=1);

header('Content-Type: application/json');

$dbPath = '/var/data/video.db';
$started = hrtime(true);

try {
    $pdo = new PDO('sqlite:' . $dbPath, null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_TIMEOUT => 1, // fail fast if the DB is locked
    ]);

    // Touch the FTS5 index — a cheap query that fails if the index is corrupt.
    $row = $pdo->query(
        "SELECT count(*) AS n FROM videos_fts WHERE videos_fts MATCH 'a*' LIMIT 1"
    )->fetch(PDO::FETCH_ASSOC);

    $elapsedMs = (hrtime(true) - $started) / 1e6;

    // If the probe itself is slow, report degraded so Caddy routes elsewhere.
    if ($elapsedMs > 250) {
        http_response_code(503);
        echo json_encode(['status' => 'degraded', 'probe_ms' => $elapsedMs]);
        return;
    }

    http_response_code(200);
    echo json_encode([
        'status'    => 'ok',
        'region'    => getenv('EDGE_REGION') ?: 'unknown',
        'fts_rows'  => (int) $row['n'],
        'probe_ms'  => round($elapsedMs, 2),
    ]);
} catch (Throwable $e) {
    http_response_code(503);
    echo json_encode(['status' => 'down', 'error' => $e->getMessage()]);
}

Returning 503 when the probe takes longer than 250ms is deliberate. A SQLite database under write contention doesn't error — it gets slow. A binary up/down check would keep a degraded origin in rotation. By treating slowness as unhealthy, Caddy pulls the box before users feel it, and lb_policy first quietly shifts that region's traffic to the secondary origin until the probe recovers.

Picking the closest origin in PHP

The Caddyfile handles the common case statically, but country-to-region mapping is data, not logic, and we didn't want to redeploy the proxy every time we adjusted a mapping. We generate the matcher lists from a single PHP source of truth that the rest of the application already uses for region selection. This keeps the proxy's geography and the app's geography from drifting apart.

<?php
// region_map.php — single source of truth for country -> origin region.
// Used by the app AND to generate Caddy matchers, so they never drift.
declare(strict_types=1);

final class RegionMap
{
    /** @var array<string, list<string>> region => ordered origins */
    private const ORIGINS = [
        'asia'     => ['ap-sin.internal:8080', 'eu-fra.internal:8080'],
        'americas' => ['us-iad.internal:8080', 'eu-fra.internal:8080'],
        'eu'       => ['eu-fra.internal:8080', 'us-iad.internal:8080'],
    ];

    /** @var array<string, string> ISO country => region */
    private const COUNTRY = [
        'SG' => 'asia', 'TW' => 'asia', 'HK' => 'asia', 'JP' => 'asia',
        'KR' => 'asia', 'IN' => 'asia', 'VN' => 'asia', 'TH' => 'asia',
        'US' => 'americas', 'CA' => 'americas', 'BR' => 'americas',
        'MX' => 'americas', 'AR' => 'americas', 'CL' => 'americas',
    ];

    public static function regionFor(string $country): string
    {
        return self::COUNTRY[strtoupper($country)] ?? 'eu';
    }

    /** Ordered upstream list for a given request region. */
    public static function originsFor(string $region): array
    {
        return self::ORIGINS[$region] ?? self::ORIGINS['eu'];
    }

    /** Emit Caddy `handle` blocks from this map — run in CI. */
    public static function toCaddyMatchers(): string
    {
        $byRegion = [];
        foreach (self::COUNTRY as $cc => $region) {
            $byRegion[$region][] = $cc;
        }
        $out = '';
        foreach ($byRegion as $region => $countries) {
            $out .= "@{$region} header CF-IPCountry " . implode(' ', $countries) . "\n";
        }
        return $out;
    }
}

// Generate matchers for the Caddyfile build step:
if (PHP_SAPI === 'cli') {
    echo RegionMap::toCaddyMatchers();
}

Now php region_map.php emits exactly the @asia/@americas matcher lines, and our CI assembles the final Caddyfile from a template plus this output. The PHP app calls RegionMap::regionFor() for its own internal routing decisions. One file, two consumers, zero drift between what the app believes about geography and what the proxy enforces.

A latency probe that adjusts the routing

Static country maps are a good baseline, but real network conditions shift. A transit provider has a bad day and suddenly ap-sin is slower from Mumbai than eu-fra. We run a small Python probe on each edge that measures real latency to every origin and writes the results where our config build can read them. It doesn't reconfigure Caddy live — it produces evidence we use to re-order the static lists, which is safer than fully dynamic routing that can flap.

#!/usr/bin/env python3
"""latency_probe.py — measure edge->origin latency, emit JSON for the
config build. Run from cron on each Caddy host."""
import json
import statistics
import time
import urllib.request

ORIGINS = {
    "ap-sin": "http://ap-sin.internal:8080/healthz",
    "us-iad": "http://us-iad.internal:8080/healthz",
    "eu-fra": "http://eu-fra.internal:8080/healthz",
}
SAMPLES = 7


def probe(url: str) -> float | None:
    timings = []
    for _ in range(SAMPLES):
        start = time.perf_counter()
        try:
            with urllib.request.urlopen(url, timeout=2) as resp:
                resp.read()
            timings.append((time.perf_counter() - start) * 1000.0)
        except Exception:
            continue
        time.sleep(0.1)
    if not timings:
        return None
    # Drop the slowest sample to reduce noise, then take the median.
    timings.sort()
    return round(statistics.median(timings[:-1] or timings), 2)


def main() -> None:
    results = {name: probe(url) for name, url in ORIGINS.items()}
    ranked = sorted(
        (r for r in results.items() if r[1] is not None),
        key=lambda kv: kv[1],
    )
    report = {
        "measured_at": int(time.time()),
        "latency_ms": results,
        "preferred_order": [name for name, _ in ranked],
    }
    print(json.dumps(report, indent=2))


if __name__ == "__main__":
    main()

The preferred_order field is what we care about. If a probe on the Singapore edge consistently reports eu-fra faster than ap-sin for a few hours, an alert fires and we know transit is degraded before users complain. We deliberately keep a human (or at least a slow CI cadence) in the loop here — fully automatic reordering on a 7-sample probe is how you build a system that oscillates between origins every cron tick.

Coordinating with Cloudflare and LiteSpeed

Three caching layers means three chances to cache the wrong thing. Our rules:

Cloudflare caches at the public edge, keyed including CF-IPCountry via our Vary header. Short max-age=60 with stale-while-revalidate=600 keeps the feed fresh without hammering origins.
Caddy does not cache by default, and we keep it that way. It's a router and TLS/compression boundary, not a cache. Adding a cache module here would just create a fourth layer to invalidate.
LiteSpeed on the origin serves the PHP page cache. Caddy forwards Cache-Control from the origin untouched for documents, and only overrides it for the API feed endpoints where we want a consistent policy.

The encode zstd gzip directive is worth calling out. We let Caddy own compression at the proxy edge rather than each origin, so the negotiation logic lives in one place. Cloudflare re-compresses for the client, but compressing origin→edge traffic still cuts our internal bandwidth on JSON payloads by roughly 70%.

One gotcha behind Cloudflare Flexible mode: auto_https off is mandatory. If you leave Caddy's automatic HTTPS on while listening on an internal hostname that has no public DNS, it will spin trying to solve ACME challenges. Turn it off, let Cloudflare terminate public TLS, and trust the forwarded protocol header.

A Go aggregator for fleet-wide health

The Python probe runs per-edge. To see the whole fleet at once we run a tiny Go service that fans out to every origin's /healthz concurrently and exposes a single rollup. Go's goroutines make the concurrent fan-out trivial and the binary deploys with no runtime dependencies, which matters on minimal proxy hosts.

package main

import (
    "encoding/json"
    "net/http"
    "sync"
    "time"
)

var origins = map[string]string{
    "ap-sin": "http://ap-sin.internal:8080/healthz",
    "us-iad": "http://us-iad.internal:8080/healthz",
    "eu-fra": "http://eu-fra.internal:8080/healthz",
}

type result struct {
    Healthy bool    `json:"healthy"`
    Millis  float64 `json:"millis"`
}

func check(url string) result {
    client := http.Client{Timeout: 2 * time.Second}
    start := time.Now()
    resp, err := client.Get(url)
    if err != nil {
        return result{Healthy: false}
    }
    defer resp.Body.Close()
    return result{
        Healthy: resp.StatusCode == http.StatusOK,
        Millis:  float64(time.Since(start).Microseconds()) / 1000.0,
    }
}

func rollup(w http.ResponseWriter, _ *http.Request) {
    var mu sync.Mutex
    var wg sync.WaitGroup
    out := make(map[string]result, len(origins))

    for name, url := range origins {
        wg.Add(1)
        go func(name, url string) {
            defer wg.Done()
            r := check(url)
            mu.Lock()
            out[name] = r
            mu.Unlock()
        }(name, url)
    }
    wg.Wait()

    w.Header().Set("Content-Type", "application/json")
    _ = json.NewEncoder(w).Encode(out)
}

func main() {
    http.HandleFunc("/fleet", rollup)
    _ = http.ListenAndServe(":9090", nil)
}

We scrape /fleet into our dashboard and our alerting. When eu-fra flips unhealthy, the rollup shows it instantly across every edge, and because Caddy's own active checks already pulled it from rotation, the alert is informational rather than a fire drill. The two systems agree, which is the whole goal: the proxy acts on health autonomously, and the Go aggregator tells humans what the proxy already decided.

What we measured

Numbers after two weeks in production, comparing the old single-list nginx config to region-aware Caddy 2:

p95 latency for Asian traffic dropped from 358ms to 96ms on cache misses — almost entirely the elimination of cross-ocean origin hops.
p95 for the Americas went from 290ms to 88ms.
EU traffic stayed flat (it was already hitting Frankfurt), which is exactly what we wanted — no regression for the region that was fine.
Failover time when an origin went unhealthy dropped from ~30s of intermittent errors to a single missed 3s health interval, with zero user-facing 5xx during the last two deploys.
Origin→edge bandwidth fell ~70% on JSON endpoints thanks to centralizing zstd/gzip at the proxy.

The config is also smaller and more honest than what it replaced. One Caddyfile, generated partly from the same PHP region map the application uses, with active health checks that understand what "healthy" means for a SQLite-backed origin.

Conclusion

Caddy 2 turned out to be the right tool not because it's faster than nginx in microbenchmarks — at our scale neither proxy is the bottleneck — but because region-aware routing, active health checks, and centralized compression are all config, not custom modules. The combination of lb_policy first with active probing gives you geographic affinity and automatic failover in two lines, and keeping the country-to-region map in one PHP file that feeds both the app and the proxy build is what stops the two from drifting apart over time.

If you're running a multi-region API and your reverse proxy still treats all origins as an undifferentiated pool, that's almost certainly where your tail latency is hiding. Map your regions, health-check them honestly, and let the closest healthy origin win. That single change did more for our worldwide p95 than any amount of query tuning on the origins themselves.

Ad-Hoc Video Analytics with DuckDB on Parquet Exports from Production SQLite

ahmet gedik — Sun, 28 Jun 2026 18:00:01 +0000

Every couple of hours, a cron job on a cheap LiteSpeed shared host wakes up, hits the YouTube Data API for eight regions — US, GB, DE, FR, IN, BR, AU, CA — and writes trending-video metadata into a single SQLite database. That is the entire ingestion pipeline behind TrendVidStream, a multi-region video streaming discovery site I run. PHP 8.4 renders the pages, SQLite FTS5 powers search, and deploys happen over plain FTP because that is what the hosting gives you. It works, it is boring, and it costs almost nothing.

What this setup does not handle gracefully is me, at 11pm, asking questions like: "Which videos trended in four or more regions this week, how fast did they climb, and is the music category quietly eating everything else?" Those are analytical questions — full scans, window functions, cross-snapshot joins. Running them against the production SQLite file means either hammering a shared-host CPU that is also serving traffic, or copying a multi-hundred-megabyte .db file to my laptop every time I get curious.

The fix that stuck: a nightly export of the hot tables to hive-partitioned Parquet, pulled down over the same FTP channel the deploys already use, and queried locally with DuckDB. The whole thing is about 120 lines of PHP and SQL, and it turned "I wonder if..." questions from a 20-minute chore into a 4-second query.

Why not just query SQLite directly

SQLite is genuinely great at what the site needs: point lookups by video ID, FTS5 full-text search, small ordered scans for category pages. It is a row store with a single-writer model, and the access patterns of a discovery site fit that perfectly.

Analytical queries are a different animal:

They scan everything. "View-count delta per video per region over 14 days" touches every row in the window. A row store reads entire rows even when you need three columns out of eighteen.
They run on the wrong machine. Shared hosting gives you a slice of a CPU and a 180-second execution cap. A 40-second aggregate query is both slow and rude to your own visitors.
They want history. The production table is a current state table — each fetch upserts the latest stats. The moment you ask "how fast is this climbing," you need snapshots, and bolting a history table onto the production schema bloats the file that FTS5 and the page cache live in.
They are exploratory. Half my queries are dead ends. Dead ends belong on a laptop, not on a box serving requests.

DuckDB is the obvious counterpart: an in-process columnar engine that reads Parquet natively, does proper window functions and grouping sets, and installs with pip install duckdb or a single static binary. No server, no cluster, no infrastructure to babysit — which matters when your entire ops story is cron plus FTP.

First contact: pointing DuckDB at the SQLite file

Before building any export pipeline, it is worth knowing that DuckDB can read SQLite databases directly through its sqlite extension. For one-off exploration against a copied-down .db file, this is all you need:

INSTALL sqlite;
LOAD sqlite;
ATTACH 'app.db' AS prod (TYPE sqlite, READ_ONLY);

SELECT region, count(*) AS videos, max(fetched_at) AS last_fetch
FROM prod.videos
GROUP BY region
ORDER BY videos DESC;

This works, and it is how I sanity-check the export pipeline itself. But as a daily workflow it has two problems. First, you are still shipping the whole database file around — mine is dominated by FTS5 index pages and description text that analytics never touches. Second, SQLite's type affinity leaks through: my timestamps are stored as TEXT (2026-06-12 14:20:01), integers that were ever written as strings stay strings, and every query ends up wrapped in casts.

Parquet snapshots solve both. You project only the analytical columns, cast once at export time, and get real types plus columnar compression. My daily snapshot of ~40,000 rows across eight regions lands at about 2.5 MB zstd-compressed — small enough that pulling it over FTP is a non-event.

The export cron, in PHP

The site already has a cron entry point that loops over regions, so the export hangs off the end of that. PHP does not have a native Parquet writer worth using, and I did not want one — the trick is to ship the DuckDB CLI binary (a single ~40 MB static executable) alongside the app and let PHP orchestrate it. The CLI reads the SQLite file through the same extension shown above and writes partitioned Parquet in one COPY statement:

<?php
declare(strict_types=1);

// cron/export_parquet.php — runs nightly after the last fetch cycle

const DB_PATH   = __DIR__ . '/../data/app.db';
const EXPORT_DIR = __DIR__ . '/../data/exports';
const DUCKDB_BIN = __DIR__ . '/../bin/duckdb';

$snapshotDate = new DateTimeImmutable('today')->format('Y-m-d'); // PHP 8.4: new in expression

$sql = <<<SQL
INSTALL sqlite; LOAD sqlite;
ATTACH '%s' AS prod (TYPE sqlite, READ_ONLY);
COPY (
    SELECT
        video_id,
        title,
        channel_id,
        channel_title,
        category_id,
        region,
        CAST(view_count AS BIGINT)  AS view_count,
        CAST(like_count AS BIGINT)  AS like_count,
        strptime(published_at, '%%Y-%%m-%%d %%H:%%M:%%S') AS published_at,
        strptime(fetched_at,   '%%Y-%%m-%%d %%H:%%M:%%S') AS fetched_at,
        DATE '%s' AS snapshot_date
    FROM prod.videos
    WHERE fetched_at >= strftime(now() - INTERVAL 1 DAY, '%%Y-%%m-%%d %%H:%%M:%%S')
) TO '%s' (
    FORMAT parquet,
    COMPRESSION zstd,
    PARTITION_BY (snapshot_date, region),
    OVERWRITE_OR_IGNORE true
);
SQL;

$query = sprintf($sql, DB_PATH, $snapshotDate, EXPORT_DIR);

$proc = proc_open(
    [DUCKDB_BIN, ':memory:'],
    [0 => ['pipe', 'r'], 1 => ['pipe', 'w'], 2 => ['pipe', 'w']],
    $pipes
);

fwrite($pipes[0], $query);
fclose($pipes[0]);
$stderr = stream_get_contents($pipes[2]);
$exit = proc_close($proc);

if ($exit !== 0) {
    error_log('[export_parquet] FAILED: ' . trim($stderr));
    exit(1);
}

echo 'Exported snapshot ' . $snapshotDate . PHP_EOL;

A few deliberate choices in there:

READ_ONLY on the attach. DuckDB takes a read snapshot and never touches the WAL in write mode, so the running site is unaffected even if a fetch cycle overlaps.
All casting happens here. strptime turns SQLite's text timestamps into real TIMESTAMP columns once, at export time. Every downstream query gets clean types for free.
PARTITION_BY (snapshot_date, region) writes a hive layout: exports/snapshot_date=2026-06-12/region=DE/data_0.parquet. That layout is the whole reason later queries are fast — DuckDB prunes partitions from the file path before reading a single byte.
The WHERE clause makes the export incremental. Only rows touched in the last day are snapshotted, so the export takes about two seconds on shared hosting instead of rewriting history every night.

Getting the files off the server

Deploys for this site go out over FTP with lftp, so the analytics pull reuses the exact same mechanism in reverse. One line in a local cron:

lftp -e 'mirror --only-newer /data/exports ./exports; quit' -u $FTP_USER,$FTP_PASS $FTP_HOST

--only-newer means each morning I transfer one day's partitions — eight small Parquet files — not the whole archive. The hive directory structure survives the mirror untouched, which matters because DuckDB derives the snapshot_date and region columns from those directory names. After a few months the local exports/ tree holds the full history that the production database deliberately does not.

There is a nice asymmetry here: the production box only ever keeps the latest day or two of exports (a cleanup step deletes older partition directories), so the shared host stays lean while the laptop accumulates the long tail.

The queries that pay rent

With the data local, everything happens through read_parquet with hive_partitioning enabled. These three queries are the ones I actually run weekly, not demo material.

-- 1. Cross-region overlap: videos trending in 3+ regions on one day.
--    This is the core editorial signal for a multi-region discovery site.
SELECT
    video_id,
    any_value(title)            AS title,
    count(DISTINCT region)      AS region_count,
    list(DISTINCT region)       AS regions,
    max(view_count)             AS peak_views
FROM read_parquet('exports/**/*.parquet', hive_partitioning = true)
WHERE snapshot_date = DATE '2026-06-12'
GROUP BY video_id
HAVING count(DISTINCT region) >= 3
ORDER BY region_count DESC, peak_views DESC
LIMIT 25;

-- 2. Velocity: 24h view-count delta per video per region.
WITH daily AS (
    SELECT video_id, region, snapshot_date,
           max(view_count) AS views
    FROM read_parquet('exports/**/*.parquet', hive_partitioning = true)
    WHERE snapshot_date >= current_date - INTERVAL 7 DAY
    GROUP BY ALL
)
SELECT video_id, region, snapshot_date,
       views - lag(views) OVER w AS views_24h,
       round(100.0 * (views - lag(views) OVER w) / nullif(lag(views) OVER w, 0), 1) AS pct_24h
FROM daily
WINDOW w AS (PARTITION BY video_id, region ORDER BY snapshot_date)
QUALIFY views_24h IS NOT NULL
ORDER BY views_24h DESC
LIMIT 50;

-- 3. Category drift: week-over-week share of trending slots per category.
SELECT category_id,
       date_trunc('week', snapshot_date) AS week,
       count(*) AS slots,
       round(100.0 * count(*) / sum(count(*)) OVER (PARTITION BY date_trunc('week', snapshot_date)), 1) AS share_pct
FROM read_parquet('exports/**/*.parquet', hive_partitioning = true)
GROUP BY ALL
ORDER BY week DESC, share_pct DESC;

Things worth noticing if you are coming from SQLite:

QUALIFY filters on window-function results without an extra subquery layer. Query 2 in SQLite is a three-level nesting exercise; here it reads top to bottom.
GROUP BY ALL groups by every non-aggregated column. Small thing, huge quality-of-life for exploratory work where you keep adding columns.
list(DISTINCT region) returns an actual array. Seeing [US, GB, DE, IN] inline beats a second lookup query.
Partition pruning is visible. Query 1 filters on snapshot_date, which is a directory name, so DuckDB opens only that day's eight files. EXPLAIN confirms it. On a year of history, the query cost stays flat.

On my laptop, all three queries over ~90 days of snapshots (about 3.5 million rows) complete in well under a second. The same overlap query against the production SQLite schema — before I gave up on running it there — took around 30 seconds and pinned the CPU.

Automating the weekly digest in Python

Once the same four or five queries proved useful repeatedly, they graduated into a small Python script that runs after the morning FTP pull and writes a markdown digest. DuckDB's Python API makes this almost embarrassingly short:

#!/usr/bin/env python3
"""weekly_digest.py — cross-region trending summary from Parquet exports."""
import duckdb
from datetime import date, timedelta
from pathlib import Path

GLOB = 'exports/**/*.parquet'
since = date.today() - timedelta(days=7)

con = duckdb.connect()  # in-memory; the Parquet files ARE the database

overlap = con.execute("""
    SELECT video_id, any_value(title) AS title,
           count(DISTINCT region) AS regions,
           max(view_count) AS peak_views
    FROM read_parquet($glob, hive_partitioning = true)
    WHERE snapshot_date >= $since
    GROUP BY video_id
    HAVING count(DISTINCT region) >= 3
    ORDER BY regions DESC, peak_views DESC
    LIMIT 15
""", {'glob': GLOB, 'since': since}).fetchall()

by_region = con.execute("""
    SELECT region, count(DISTINCT video_id) AS uniques,
           round(avg(view_count)) AS avg_views
    FROM read_parquet($glob, hive_partitioning = true)
    WHERE snapshot_date >= $since
    GROUP BY region ORDER BY uniques DESC
""", {'glob': GLOB, 'since': since}).fetchall()

lines = [f'# Trending digest — week of {since}', '', '## Multi-region hits', '']
for vid, title, regions, views in overlap:
    lines.append(f'- **{title}** — {regions} regions, peak {views:,} views (`{vid}`)')

lines += ['', '## Region health', '']
for region, uniques, avg_views in by_region:
    lines.append(f'- {region}: {uniques} unique trending videos, avg {int(avg_views):,} views')

Path(f'digests/{since}.md').write_text('\n'.join(lines) + '\n')
print(f'wrote digests/{since}.md ({len(overlap)} multi-region hits)')

Note the parameterized queries — $glob and $since — which keep the SQL copy-pasteable between the CLI and the script. The digest tells me in thirty seconds whether a region's API fetch silently degraded (uniques drop), whether a category is surging, and what the genuinely global videos were this week. The third one feeds directly back into the product.

Closing the loop back to production

The analytics layer would be a vanity project if nothing flowed back to the site. The piece that does: the multi-region overlap query gets serialized to a small JSON file and pushed to each host over the existing FTP deploy channel. On the PHP side, rendering it is deliberately dumb — no DuckDB, no SQLite involvement, just a cached file read:

<?php
declare(strict_types=1);

final readonly class GlobalTrending
{
    public function __construct(private string $path) {}

    /** @return list<array{video_id: string, title: string, regions: int}> */
    public function top(int $limit = 10): array
    {
        if (!is_file($this->path) || filemtime($this->path) < time() - 86400 * 2) {
            return []; // stale or missing → widget simply doesn't render
        }
        $rows = json_decode(file_get_contents($this->path), true, flags: JSON_THROW_ON_ERROR);
        return array_slice($rows, 0, $limit);
    }
}

The staleness check is the important line. If my laptop is closed for a week and no fresh JSON arrives, the "trending worldwide" widget disappears instead of showing fossilized data. The production site never depends on the analytics pipeline being alive — it only gets enhanced by it. For a one-person operation, that failure mode is the difference between a hobby-friendly system and a pager.

Gotchas I hit so you don't have to

SQLite type affinity will bite exactly once. A handful of my early view_count values were written as text by an old fetcher version. DuckDB's sqlite extension surfaced them as VARCHAR and the first export died on the cast. Fix it at export time with TRY_CAST and a logged count of nulls, not by mutating production data mid-flight.
Normalize timezones before comparing regions. Eight regions means fetch timestamps that look misaligned if any layer applied a local offset. I store everything UTC in SQLite and declared the export's strptime results as UTC; do this on day one, because retrofitting timezone hygiene across months of Parquet partitions is miserable.
OVERWRITE_OR_IGNORE matters for reruns. Without it, re-running the export after a partial failure either errors or duplicates rows inside a partition. With it, the day's partition is rewritten atomically and the cron is safely idempotent.
Don't glob more than you need. read_parquet('exports/**/*.parquet') is fine with a snapshot_date filter (pruning saves you), but an unfiltered SELECT count(*) over a year of partitions opens every file. Listing thousands of small files has fixed overhead even when each one is tiny — keep daily partitions per region as single files, resist the urge to export hourly.
Pin the DuckDB version in both places. The CLI binary on the host writes the Parquet; the Python package on the laptop reads it. Parquet itself is stable, but I keep both on the same release anyway so behavior differences in strptime and casting never become a debugging session.
Dedup within snapshots, not across them. The same video_id legitimately appears in multiple regions and on multiple days — that's the signal, not noise. Every aggregate needs to be explicit about which axis it collapses, hence the count(DISTINCT region) and max(view_count) patterns above rather than naive counts.

Conclusion

The pattern here is small and reusable: keep the transactional store (SQLite) doing exactly what it is good at, snapshot the analytical slice to hive-partitioned Parquet on a schedule, move files with whatever boring transport you already trust — FTP, in my case — and let DuckDB do the heavy reading on hardware you don't share with your visitors. No warehouse, no streaming infra, no monthly bill. For a multi-region discovery site fed by cron jobs, it answers real product questions in seconds, and the production box never even notices the questions were asked.

If you are running anything on SQLite and finding yourself writing painful analytical queries against it, try the ATTACH ... (TYPE sqlite) one-liner first. Five minutes with that is usually all it takes to be convinced.