Two different crawlers on my aggregator once ingested the same YouTube clip four times in a single afternoon. One came in as youtube.com/watch?v=dQw4w9WgXcQ&feature=share, another as youtu.be/dQw4w9WgXcQ?t=42, a third as m.youtube.com/watch?v=dQw4w9WgXcQ&utm_source=trending, and the last as a youtube-nocookie.com embed URL. To my database they were four distinct rows, four distinct thumbnails, four distinct FTS5 documents, and four distinct chances to show a user the exact same video twice on one trending page. On TopVideoHub, where I aggregate trending video across Japan, Korea, Taiwan, Hong Kong, Thailand, Vietnam and Singapore, that duplication multiplies fast: the same viral clip surfaces through a dozen regional feeds, each decorating the URL with its own tracking junk. This is the story of the canonicalization pipeline I built to collapse all of those into one stable identity — and the surprisingly large number of edge cases that live between a raw URL string and a clean primary key.
Why a URL is a terrible primary key
The naive approach is to store the URL as-is and slap a UNIQUE index on it. This fails immediately because URLs carry three categories of noise that have nothing to do with which video is being referenced:
-
Tracking and session parameters:
utm_*,feature,si,pp,gclid,fbclid, and platform-specific referral tokens. -
Positional and playback state:
t=42,start=120,list=, autoplay flags, and quality hints. These describe how you're watching, not what you're watching. -
Host and scheme variance:
youtu.bevswww.youtube.comvsm.youtube.com,httpvshttps, trailing slashes, percent-encoding differences, and uppercase hosts.
The insight that makes canonicalization tractable is that almost every video platform has a stable, opaque video ID buried somewhere in the URL. YouTube has an 11-character ID, Vimeo has a numeric ID, Bilibili has a BV ID, Dailymotion has a short alphanumeric. If I can extract (platform, video_id), I have a canonical identity that survives every cosmetic mutation of the URL. The canonical URL is then something I reconstruct from that pair, not something I try to clean up in place.
So the pipeline has two jobs, in this order:
-
Identify: parse the raw URL down to
(platform, video_id). - Reconstruct: build one deterministic canonical URL from that pair.
Everything else — dedup, storage, the FTS5 index — hangs off step 1.
Extracting a stable identity per platform
I model each platform as an extractor: a host matcher plus a set of rules for finding the ID. In PHP 8.4 I lean on readonly classes and first-class callable syntax to keep these declarative and testable.
<?php
declare(strict_types=1);
final readonly class VideoIdentity
{
public function __construct(
public string $platform,
public string $videoId,
) {}
public function key(): string
{
return $this->platform . ':' . $this->videoId;
}
}
final class Canonicalizer
{
/** @var array<string, callable(array): ?VideoIdentity> */
private array $extractors;
public function __construct()
{
$this->extractors = [
'youtube' => $this->extractYouTube(...),
'vimeo' => $this->extractVimeo(...),
'bilibili' => $this->extractBilibili(...),
'dailymotion' => $this->extractDailymotion(...),
];
}
public function identify(string $rawUrl): ?VideoIdentity
{
$parts = parse_url($this->preNormalize($rawUrl));
if ($parts === false || !isset($parts['host'])) {
return null;
}
$parts['host'] = strtolower($parts['host']);
$parts['host'] = preg_replace('/^(www|m|mobile)\./', '', $parts['host']);
foreach ($this->extractors as $extract) {
if ($identity = $extract($parts)) {
return $identity;
}
}
return null;
}
private function preNormalize(string $url): string
{
$url = trim($url);
// Force a scheme so parse_url treats the host correctly.
if (!preg_match('#^https?://#i', $url)) {
$url = 'https://' . ltrim($url, '/');
}
return $url;
}
}
The host normalization step — lowercasing and stripping www./m./mobile. — is small but eliminates a whole class of duplicates before any platform logic runs. The individual extractors then each know exactly one thing:
<?php
trait PlatformExtractors
{
private function extractYouTube(array $p): ?VideoIdentity
{
$host = $p['host'];
if ($host === 'youtu.be') {
$id = ltrim($p['path'] ?? '', '/');
return $this->makeYouTube($id);
}
if (!in_array($host, ['youtube.com', 'youtube-nocookie.com'], true)) {
return null;
}
parse_str($p['query'] ?? '', $q);
if (isset($q['v'])) {
return $this->makeYouTube($q['v']);
}
// /embed/ID, /shorts/ID, /live/ID, /v/ID
if (preg_match('#^/(embed|shorts|live|v)/([^/?&]+)#', $p['path'] ?? '', $m)) {
return $this->makeYouTube($m[2]);
}
return null;
}
private function makeYouTube(string $id): ?VideoIdentity
{
// YouTube IDs are exactly 11 chars of [A-Za-z0-9_-].
return preg_match('/^[A-Za-z0-9_-]{11}$/', $id)
? new VideoIdentity('youtube', $id)
: null;
}
private function extractVimeo(array $p): ?VideoIdentity
{
if ($p['host'] !== 'vimeo.com') {
return null;
}
if (preg_match('#^/(?:video/)?(\d+)#', $p['path'] ?? '', $m)) {
return new VideoIdentity('vimeo', $m[1]);
}
return null;
}
private function extractBilibili(array $p): ?VideoIdentity
{
if (!str_ends_with($p['host'], 'bilibili.com')) {
return null;
}
if (preg_match('#/video/(BV[A-Za-z0-9]+)#', $p['path'] ?? '', $m)) {
return new VideoIdentity('bilibili', $m[1]);
}
return null;
}
private function extractDailymotion(array $p): ?VideoIdentity
{
$host = $p['host'];
if ($host === 'dai.ly') {
$id = ltrim($p['path'] ?? '', '/');
} elseif ($host === 'dailymotion.com'
&& preg_match('#^/video/([^/?_]+)#', $p['path'] ?? '', $m)) {
$id = $m[1];
} else {
return null;
}
return preg_match('/^[a-zA-Z0-9]+$/', $id)
? new VideoIdentity('dailymotion', $id)
: null;
}
}
Notice that every extractor validates the ID shape before trusting it. This matters more than it looks. A malformed feed once handed me youtube.com/watch?v=undefined, and without the {11} length check I'd have created a canonical row for a video that doesn't exist. Validation at the extraction boundary is the cheapest place to reject garbage.
Reconstructing the canonical URL
Once I have (platform, video_id), the canonical URL is pure template substitution. I never mutate the input URL — I throw it away and build a fresh one. This guarantees byte-for-byte identical output regardless of how mangled the input was.
<?php
final class CanonicalUrlBuilder
{
private const TEMPLATES = [
'youtube' => 'https://www.youtube.com/watch?v=%s',
'vimeo' => 'https://vimeo.com/%s',
'bilibili' => 'https://www.bilibili.com/video/%s',
'dailymotion' => 'https://www.dailymotion.com/video/%s',
];
public function build(VideoIdentity $id): string
{
$template = self::TEMPLATES[$id->platform]
?? throw new \InvalidArgumentException("Unknown platform: {$id->platform}");
return sprintf($template, rawurlencode($id->videoId));
}
/** Stable 64-bit dedup key, independent of URL form. */
public function fingerprint(VideoIdentity $id): string
{
return substr(hash('sha256', $id->key()), 0, 16);
}
}
The fingerprint() method is the actual primary dedup key. I hash the platform:video_id pair rather than the URL, so two rows collide only when they genuinely reference the same video. Truncating SHA-256 to 16 hex chars (64 bits) is plenty for a corpus in the low millions — the birthday-collision probability is negligible and it indexes tighter than a full 64-char digest in SQLite.
Wiring it into SQLite with FTS5
My storage layer is SQLite because it deploys as a single file behind LiteSpeed with zero external dependencies, and its FTS5 module gives me full-text search with a CJK-aware tokenizer — essential when the same trending page mixes Japanese, Korean and Traditional Chinese titles. The canonical fingerprint becomes a UNIQUE column, and the FTS index is populated from the canonical row only.
CREATE TABLE videos (
id INTEGER PRIMARY KEY,
fingerprint TEXT NOT NULL UNIQUE,
platform TEXT NOT NULL,
video_id TEXT NOT NULL,
canonical_url TEXT NOT NULL,
title TEXT NOT NULL,
region TEXT NOT NULL,
created_at INTEGER NOT NULL
);
-- CJK tokenizer: trigram handles languages without word boundaries.
CREATE VIRTUAL TABLE videos_fts USING fts5(
title,
content='videos',
content_rowid='id',
tokenize='trigram'
);
The insert path is an idempotent upsert. Because the fingerprint is deterministic, ingesting the same video from a seventh regional feed is a no-op on the identity row — I only append the new region to a mapping table so I still know the clip is trending in, say, both Vietnam and Thailand.
<?php
final class VideoRepository
{
public function __construct(
private \PDO $db,
private Canonicalizer $canon,
private CanonicalUrlBuilder $builder,
) {}
public function ingest(string $rawUrl, string $title, string $region, int $now): ?string
{
$identity = $this->canon->identify($rawUrl);
if ($identity === null) {
return null; // unsupported host or malformed URL — skip, don't store
}
$fingerprint = $this->builder->fingerprint($identity);
$canonicalUrl = $this->builder->build($identity);
$this->db->beginTransaction();
try {
$stmt = $this->db->prepare(
'INSERT INTO videos
(fingerprint, platform, video_id, canonical_url, title, region, created_at)
VALUES (:fp, :pf, :vid, :url, :title, :region, :now)
ON CONFLICT(fingerprint) DO NOTHING'
);
$stmt->execute([
':fp' => $fingerprint, ':pf' => $identity->platform,
':vid' => $identity->videoId, ':url' => $canonicalUrl,
':title' => $title, ':region' => $region, ':now' => $now,
]);
if ($this->db->lastInsertId() && $stmt->rowCount() > 0) {
$rowId = (int) $this->db->lastInsertId();
$this->db->prepare(
'INSERT INTO videos_fts(rowid, title) VALUES (:id, :title)'
)->execute([':id' => $rowId, ':title' => $title]);
}
$this->db->prepare(
'INSERT OR IGNORE INTO video_regions(fingerprint, region) VALUES (:fp, :region)'
)->execute([':fp' => $fingerprint, ':region' => $region]);
$this->db->commit();
return $fingerprint;
} catch (\Throwable $e) {
$this->db->rollBack();
throw $e;
}
}
}
The ON CONFLICT ... DO NOTHING clause is what makes the whole pipeline safe to run concurrently from multiple cron workers hitting different regional APIs. Two workers can race on the same viral clip; the loser's insert simply evaporates, and both still register their region. There's no application-level lock, no read-then-write window to lose — the uniqueness constraint on fingerprint is the coordination point, enforced by SQLite itself.
The redirect-resolution problem
Static extraction handles maybe 95% of real traffic, but some feeds hand you shortener URLs — bit.ly, t.co, platform-internal share links — that reveal nothing until you follow them. You cannot canonicalize what you cannot see. So the pipeline has a second, optional stage: resolve redirects, then re-run identification on the final URL.
I keep this stage in a separate worker because network I/O is slow and failure-prone, and I never want a hung shortener to stall the fast path. Here it is in Go, which is what I use for the resolver daemon since its http.Client gives me precise control over redirect depth and timeouts:
package main
import (
"errors"
"net/http"
"time"
)
// resolveFinalURL follows redirects up to a bounded depth and returns
// the final URL without downloading the body.
func resolveFinalURL(shortURL string, maxHops int) (string, error) {
hops := 0
client := &http.Client{
Timeout: 5 * time.Second,
CheckRedirect: func(req *http.Request, via []*http.Request) error {
hops++
if hops > maxHops {
return errors.New("too many redirects")
}
return nil
},
}
// HEAD avoids pulling the response body; most shorteners honor it.
req, err := http.NewRequest(http.MethodHead, shortURL, nil)
if err != nil {
return "", err
}
req.Header.Set("User-Agent", "TopVideoHub-Resolver/1.0")
resp, err := client.Do(req)
if err != nil {
return "", err
}
defer resp.Body.Close()
// resp.Request.URL is the URL after all redirects were followed.
return resp.Request.URL.String(), nil
}
The resolved URL flows straight back into the same Canonicalizer::identify(). Redirect resolution isn't a special case in the identity logic — it's just a preprocessing step that turns an opaque short link into something the static extractors already understand. This separation keeps the core canonicalizer pure, synchronous, and trivially unit-testable, while the messy network concerns live behind a queue.
Edge cases that will bite you
A few hard-won lessons from running this in production across seven markets:
-
Percent-encoding drift:
youtu.be/dQw4w9WgXcQandyoutu.be/dQw4w9WgXcQ%20are not the same string but reference the same video. Because I validate the extracted ID against a strict character class, the trailing%20fails the{11}check and I reject rather than duplicate — but you must decode consciously, not accidentally. -
Case-sensitive IDs, case-insensitive hosts: hosts are case-insensitive per RFC 3986, but video IDs are not.
dQw4andDQW4are different YouTube videos. Lowercase the host, never the ID. Getting this backwards silently merges distinct videos, which is far worse than a duplicate. -
Playlists masquerading as videos:
youtube.com/playlist?list=...has novparam. My extractor returnsnullfor these, and the repository skips them. Deciding not to store something is a valid canonicalization outcome. -
Region as metadata, not identity: the single biggest early mistake was folding region into the fingerprint. A clip trending in both Japan and Korea is one video shown in two markets, not two videos. Identity is
(platform, video_id); everything geographic is a separate many-to-many relationship. -
Idempotency under concurrency: rely on the database's uniqueness constraint, not an application
SELECTfollowed by anINSERT. The read-then-write pattern has a race window;ON CONFLICT DO NOTHINGdoes not.
Conclusion
A canonicalization pipeline is really an identity pipeline wearing a URL costume. The moment you stop trying to clean URLs and start extracting stable (platform, video_id) pairs, every downstream problem gets simpler: dedup becomes a hash comparison, the canonical URL becomes a template fill, storage becomes an idempotent upsert, and your FTS5 index stops carrying four copies of the same trending clip. The pipeline I run today is three small, boring, heavily-tested pieces — an identifier, a builder, and an idempotent repository — plus an optional out-of-band redirect resolver for the awkward 5%. Boring is exactly what you want in the layer that decides what counts as the same video. Push the messy network I/O to the edges, keep the core synchronous and pure, and let the database's uniqueness constraint be the single source of truth. Do that, and the same viral clip arriving from twelve regional feeds collapses cleanly into one row — which is the whole point.