The bug that started this project looked harmless in the logs: the same YouTube video appeared four times in our discovery index, each with a slightly different URL. One had ?feature=share, one had a &t=42s timestamp, one was the youtu.be short form, and one was a fully-qualified www.youtube.com/watch?v= link scraped from a regional trending feed. To our SQLite FTS5 index they were four distinct rows. To a human they were obviously the same video. Multiply that by 8 regions pulling overlapping trending data every few hours and you get an index that is 30% duplicate garbage, wasted crawl budget, and a search page that shows the same clip three times.
At TrendVidStream we ingest video metadata from dozens of sources across 8 regions, and the single highest-leverage fix we shipped last quarter was not a fancier ranking model — it was a boring, deterministic URL canonicalization pipeline. This post walks through how we built it: the parsing rules, the platform-specific quirks, the deterministic hashing that gives us idempotent upserts, and the multi-region cron plumbing that keeps it fast on a stack of PHP 8.4, SQLite, and FTP-based deploys. Everything here is runnable, not pseudocode.
Why Canonicalization Is Harder Than strtolower
The naive approach — lowercase the string, strip whitespace, done — fails immediately for video URLs because the identity of a video lives in a small number of meaningful parameters, and everything else is noise that varies by source. A canonical URL has to answer one question reliably: do these two strings point at the same piece of content?
The noise falls into predictable buckets:
-
Tracking params:
utm_source,utm_medium,feature,si,gclid,fbclid— never part of identity. -
Playback state:
t,start,time_continue,list,index— a timestamp or playlist position, not the video itself. -
Host aliases:
youtu.be/ID,m.youtube.com,youtube.com,www.youtube.comall resolve to the same watch page. -
Path form variance:
/watch?v=ID,/embed/ID,/shorts/ID,/v/IDare five doors into the same room. - Casing and encoding: hosts are case-insensitive, percent-encoding may or may not be applied, trailing slashes come and go.
The goal is to collapse all of that into one string per video, per platform, so that a hash of the canonical form becomes a stable primary key. The moment you have a stable key, deduplication stops being a fuzzy-matching problem and becomes a INSERT ... ON CONFLICT one-liner.
The Extraction Layer
The first job is to reduce any incoming URL to a (platform, video_id) tuple. This is platform-specific and it is where most of the real logic lives. We keep one extractor per platform behind a common interface. Here is the core of the PHP implementation we run in the ingest cron:
<?php
declare(strict_types=1);
final class VideoUrlCanonicalizer
{
// Params that never contribute to identity, stripped everywhere.
private const NOISE_PARAMS = [
'utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content',
'feature', 'si', 'gclid', 'fbclid', 'app', 'ab_channel',
't', 'start', 'time_continue', 'end', 'index', 'pp',
];
/**
* @return array{platform:string, id:string, canonical:string}|null
*/
public function canonicalize(string $raw): ?array
{
$raw = trim($raw);
if ($raw === '') {
return null;
}
// Force a scheme so parse_url populates host reliably.
if (!preg_match('#^https?://#i', $raw)) {
$raw = 'https://' . ltrim($raw, '/');
}
$parts = parse_url($raw);
if ($parts === false || empty($parts['host'])) {
return null;
}
$host = strtolower($parts['host']);
$host = preg_replace('#^(www|m|mobile)\.#', '', $host);
$path = $parts['path'] ?? '/';
parse_str($parts['query'] ?? '', $query);
return match (true) {
$this->isYouTube($host) => $this->youtube($host, $path, $query),
$host === 'vimeo.com' => $this->vimeo($path),
$this->isDailymotion($host) => $this->dailymotion($host, $path),
default => null,
};
}
private function isYouTube(string $host): bool
{
return in_array($host, ['youtube.com', 'youtu.be', 'youtube-nocookie.com'], true);
}
private function youtube(string $host, string $path, array $query): ?array
{
$id = null;
if ($host === 'youtu.be') {
// youtu.be/<id>
$id = trim($path, '/');
} elseif (isset($query['v'])) {
// /watch?v=<id>
$id = $query['v'];
} elseif (preg_match('#^/(embed|shorts|v|live)/([^/?]+)#', $path, $m)) {
// /embed/<id>, /shorts/<id>, /v/<id>, /live/<id>
$id = $m[2];
}
if ($id === null || !preg_match('#^[A-Za-z0-9_-]{11}$#', $id)) {
return null; // reject anything that is not a real 11-char video id
}
return [
'platform' => 'youtube',
'id' => $id,
'canonical' => "https://www.youtube.com/watch?v={$id}",
];
}
private function vimeo(string $path): ?array
{
if (!preg_match('#/(\d+)#', $path, $m)) {
return null;
}
return [
'platform' => 'vimeo',
'id' => $m[1],
'canonical' => "https://vimeo.com/{$m[1]}",
];
}
private function isDailymotion(string $host): bool
{
return $host === 'dailymotion.com' || $host === 'dai.ly';
}
private function dailymotion(string $host, string $path): ?array
{
$id = $host === 'dai.ly'
? trim($path, '/')
: (preg_match('#/video/([a-z0-9]+)#i', $path, $m) ? $m[1] : null);
if ($id === null || $id === '') {
return null;
}
return [
'platform' => 'dailymotion',
'id' => $id,
'canonical' => "https://www.dailymotion.com/video/{$id}",
];
}
}
A few decisions worth calling out:
-
We validate the ID shape. YouTube IDs are exactly 11 characters from
[A-Za-z0-9_-]. If extraction produces anything else, we returnnulland drop the record rather than index garbage. Rejecting is a feature — a malformed URL that sneaks in becomes a permanent phantom row. - The canonical form is regenerated, not edited. We never try to "clean" the input string by removing params. We throw the input away and rebuild the URL from the extracted ID. That guarantees byte-identical output for byte-different inputs, which is the whole point.
-
youtube-nocookie.comandm.youtube.comcollapse into the same canonical host. Host normalization happens before dispatch, so mobile and embed hosts never fork the logic.
The NOISE_PARAMS constant is effectively documentation at this point — because we rebuild from the ID, noise params are dropped implicitly. We keep the list because some platforms (Dailymotion playlists, for example) do carry an occasionally-meaningful param, and having the allow/deny lists explicit makes the next engineer's life easier.
Deterministic Keys via Hashing
Once you have (platform, id), the canonical URL is derived, and the key is a hash of the canonical URL. We use a truncated SHA-256 rendered as hex, stored as the primary key. Deterministic in, deterministic out — the same video from any of the 8 regions produces the same key.
public function key(array $canon): string
{
// Namespaced so two platforms can never collide on a shared numeric id.
$material = $canon['platform'] . ':' . $canon['id'];
return substr(hash('sha256', $material), 0, 20);
}
Why hash at all instead of using platform:id directly as the key? Two reasons. First, fixed-width keys make the SQLite index dense and predictable regardless of how long a platform's native IDs grow. Second, it decouples the storage key from the source IDs, so if a platform changes its ID format we can re-derive without a schema migration touching foreign keys. The namespacing (platform:id) before hashing is non-negotiable — Vimeo and an internal numeric source could otherwise collide on 123456.
Idempotent Upserts Into SQLite
The payoff is the write path. Because the key is stable, ingestion is an idempotent upsert. Running the same region's feed twice changes nothing; running two overlapping regions merges cleanly. Here is the schema and the upsert we run in the cron:
<?php
$db = new PDO('sqlite:' . __DIR__ . '/data/videos.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec(<<<SQL
CREATE TABLE IF NOT EXISTS videos (
key TEXT PRIMARY KEY,
platform TEXT NOT NULL,
video_id TEXT NOT NULL,
canonical TEXT NOT NULL,
title TEXT NOT NULL DEFAULT '',
regions TEXT NOT NULL DEFAULT '', -- comma list of regions that saw it
first_seen INTEGER NOT NULL,
last_seen INTEGER NOT NULL
);
SQL);
// FTS5 mirror for discovery search.
$db->exec(<<<SQL
CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts
USING fts5(key UNINDEXED, title, content='videos', content_rowid='rowid');
SQL);
function upsertVideo(PDO $db, array $canon, string $key, string $title, string $region, int $now): void
{
$stmt = $db->prepare(<<<SQL
INSERT INTO videos (key, platform, video_id, canonical, title, regions, first_seen, last_seen)
VALUES (:key, :platform, :vid, :canonical, :title, :region, :now, :now)
ON CONFLICT(key) DO UPDATE SET
last_seen = :now,
title = CASE WHEN excluded.title != '' THEN excluded.title ELSE videos.title END,
regions = CASE
WHEN instr(',' || videos.regions || ',', ',' || :region || ',') > 0
THEN videos.regions
ELSE videos.regions || ',' || :region
END
SQL);
$stmt->execute([
':key' => $key, ':platform' => $canon['platform'], ':vid' => $canon['id'],
':canonical' => $canon['canonical'], ':title' => $title,
':region' => $region, ':now' => $now,
]);
}
The regions column deserves a note. Instead of a join table, we keep a comma-delimited membership set and use SQLite's instr to test membership before appending — a video first seen in the US feed and later in the DE feed accumulates US,DE without duplicating rows. For a discovery index where "which regions is this trending in" is a display concern rather than a query-heavy dimension, this denormalization keeps the write path to a single statement. If you needed to query by region at scale you would normalize it, but measure first: on our volumes the comma list wins on simplicity and write speed.
The title merge logic keeps the first non-empty title we saw and only overwrites with another non-empty value — regional feeds sometimes return blank titles, and we never want a good title clobbered by an empty one.
Wiring It Into the Multi-Region Cron
The pipeline runs inside our per-region fetch cron. Each region has its own schedule (staggered so we never hit a source's rate limit with 8 simultaneous requests), and each invocation streams its trending feed through the canonicalizer before touching the database. Because the write is idempotent, region overlap is free — we do not coordinate between regions at all, which is exactly what you want when jobs run on independent schedules.
Here is the shape of the per-region driver:
<?php
$regions = explode(',', getenv('FETCH_REGIONS') ?: 'US,GB,DE,FR,IN,BR,AU,CA');
$canon = new VideoUrlCanonicalizer();
$now = time();
foreach ($regions as $region) {
$region = trim($region);
$feed = fetchTrendingFeed($region); // returns [['url' => ..., 'title' => ...], ...]
$db->beginTransaction();
$inserted = $skipped = 0;
foreach ($feed as $item) {
$result = $canon->canonicalize($item['url']);
if ($result === null) {
$skipped++;
continue; // reject: unparseable or bad id shape
}
$key = $canon->key($result);
upsertVideo($db, $result, $key, $item['title'] ?? '', $region, $now);
$inserted++;
}
$db->commit();
fprintf(STDERR, "[%s] region=%s upserted=%d skipped=%d\n",
date('c', $now), $region, $inserted, $skipped);
}
// Keep FTS in sync and reclaim space after a bulk run.
$db->exec("INSERT INTO videos_fts(videos_fts) VALUES('rebuild')");
$db->exec('PRAGMA optimize');
Wrapping each region's batch in a single transaction is the difference between a fetch job that takes 40 seconds and one that takes 4 minutes — SQLite fsyncs once per commit, so per-row commits will destroy you. The skipped counter going to stderr is our canary: if a source changes its URL format overnight, the skip rate spikes and we see it in the cron logs before it poisons the index.
Portability: The Same Logic in Go
Because the canonicalization rules are pure functions with no I/O, they are trivially portable. When we needed the same normalization inside a small Go service that pre-validates submitted URLs at the edge, we reimplemented the core in about 30 lines and validated it against the PHP version with a shared fixture file of input/output pairs. Keeping the rules pure is what makes this cheap:
package canon
import (
"crypto/sha256"
"encoding/hex"
"fmt"
"net/url"
"regexp"
"strings"
)
var ytID = regexp.MustCompile(`^[A-Za-z0-9_-]{11}$`)
var hostPrefix = regexp.MustCompile(`^(www|m|mobile)\.`)
type Result struct {
Platform string
ID string
Canonical string
}
func Canonicalize(raw string) (Result, bool) {
raw = strings.TrimSpace(raw)
if !strings.HasPrefix(raw, "http") {
raw = "https://" + strings.TrimLeft(raw, "/")
}
u, err := url.Parse(raw)
if err != nil || u.Host == "" {
return Result{}, false
}
host := hostPrefix.ReplaceAllString(strings.ToLower(u.Host), "")
var id string
switch host {
case "youtu.be":
id = strings.Trim(u.Path, "/")
case "youtube.com", "youtube-nocookie.com":
if v := u.Query().Get("v"); v != "" {
id = v
} else if p := strings.SplitN(strings.Trim(u.Path, "/"), "/", 2); len(p) == 2 &&
(p[0] == "embed" || p[0] == "shorts" || p[0] == "v" || p[0] == "live") {
id = p[1]
}
default:
return Result{}, false
}
if !ytID.MatchString(id) {
return Result{}, false
}
return Result{
Platform: "youtube",
ID: id,
Canonical: fmt.Sprintf("https://www.youtube.com/watch?v=%s", id),
}, true
}
func Key(r Result) string {
sum := sha256.Sum256([]byte(r.Platform + ":" + r.ID))
return hex.EncodeToString(sum[:])[:20]
}
The critical property: Key(Canonicalize(x)) in Go produces the exact same 20-char string as key(canonicalize(x)) in PHP, because both hash platform:id with SHA-256 and truncate identically. That lets the edge service reject a duplicate submission before it ever reaches the ingest cron, using nothing but a key lookup. Cross-language determinism is only possible because we agreed on the canonical string and the hash, and wrote both to a shared fixture file that both test suites read.
Testing Against a Fixture Table
The entire pipeline is validated by one flat fixture file — a list of input → expected_canonical pairs. Every real-world weird URL we have ever seen goes in there the moment we see it, which turns production surprises into permanent regression tests. A trimmed version:
# canon_fixtures.py — shared truth table, read by both PHP and Go test suites
FIXTURES = [
("https://youtu.be/dQw4w9WgXcQ?si=abc123", "https://www.youtube.com/watch?v=dQw4w9WgXcQ"),
("https://m.youtube.com/watch?v=dQw4w9WgXcQ&t=42s", "https://www.youtube.com/watch?v=dQw4w9WgXcQ"),
("https://www.youtube.com/shorts/dQw4w9WgXcQ", "https://www.youtube.com/watch?v=dQw4w9WgXcQ"),
("https://www.youtube.com/embed/dQw4w9WgXcQ?rel=0", "https://www.youtube.com/watch?v=dQw4w9WgXcQ"),
("youtube.com/watch?v=dQw4w9WgXcQ&feature=share", "https://www.youtube.com/watch?v=dQw4w9WgXcQ"),
("https://vimeo.com/76979871?foo=bar", "https://vimeo.com/76979871"),
("https://dai.ly/x8abcde", "https://www.dailymotion.com/video/x8abcde"),
("https://youtube.com/watch?v=short", None), # bad id -> rejected
]
if __name__ == "__main__":
# A tiny reference implementation to keep the truth table honest.
import re, urllib.parse as up
def canon(raw):
raw = raw.strip()
if not raw.startswith("http"):
raw = "https://" + raw.lstrip("/")
u = up.urlparse(raw)
host = re.sub(r"^(www|m|mobile)\.", "", u.netloc.lower())
if host in ("youtube.com", "youtu.be", "youtube-nocookie.com"):
if host == "youtu.be":
vid = u.path.strip("/")
elif (q := up.parse_qs(u.query).get("v")):
vid = q[0]
else:
m = re.match(r"/(embed|shorts|v|live)/([^/?]+)", u.path)
vid = m.group(2) if m else ""
return f"https://www.youtube.com/watch?v={vid}" if re.fullmatch(r"[A-Za-z0-9_-]{11}", vid) else None
return None # other platforms omitted for brevity
for src, expected in FIXTURES:
got = canon(src)
status = "ok" if got == expected else "FAIL"
print(f"[{status}] {src} -> {got!r}")
When a new source shows us a URL shape we do not handle, the fix is a one-line fixture addition plus whatever extractor branch it demands — and the deploy is our normal FTP push of the updated cron scripts, no database migration required because the canonical form and key are unchanged for everything already indexed.
What This Bought Us
The numbers after shipping the pipeline were unambiguous: index size dropped by roughly 30% as duplicates collapsed, search results stopped showing the same clip multiple times, and crawl budget stopped being wasted re-fetching metadata for URLs we already had under a different string. The idempotent upsert also made our multi-region cron simpler — we deleted a whole layer of cross-region deduplication logic that existed only to paper over the missing canonical key.
The lessons that generalize beyond video URLs:
- Rebuild, don't clean. Deriving the canonical form from extracted identity, rather than editing the input string, is what guarantees byte-identical output.
- Reject aggressively. Validating the ID shape and dropping anything that fails keeps phantom rows out of the index permanently.
- Hash a namespaced identity for your key, and idempotent upserts fall out for free.
- Keep the rules pure, and porting the exact same behavior to another language becomes a fixture-file exercise instead of a research project.
Canonicalization is unglamorous plumbing, but it is the kind of plumbing that quietly makes every downstream system — search, ranking, crawl scheduling — correct by construction instead of correct by luck. If your discovery index is fighting duplicates, this is the cheapest high-leverage fix available.
Top comments (0)