devLap

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

php dev.to

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

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

What "canonical" actually means for a video aggregator

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

So we split the problem into two outputs:

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

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

Here is the pipeline, top to bottom:

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

Step 1 and 2: host normalization and shortener resolution

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

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

<?php
declare(strict_types=1);

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

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

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

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

Step 3 and 4: the platform extractor and canonical rebuild

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

<?php
declare(strict_types=1);

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

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

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

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

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

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

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

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

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

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

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

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

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

A few decisions worth calling out:

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

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

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

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

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

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

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

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

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

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

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

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

Backfilling 40k existing rows with Python

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

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

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

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

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

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

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

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

if __name__ == '__main__':
    backfill('videos_copy.db')
Enter fullscreen mode Exit fullscreen mode

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

The hot path as a Go worker

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

package main

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

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

type Canonical struct {
    Platform string
    VideoID  string
    URL      string
}

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

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

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

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

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

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

Edge cases that will bite you

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

Conclusion

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

Source: dev.to

arrow_back Back to Tutorials