gerbeur/api/db/migrations/0013_dump_url_canonical.ts

import type { DatabaseSync } from "node:sqlite";

// Adds `dumps.url_canonical` — the lookup key behind the "already dumped?"
// check on the create form — and backfills it for every existing URL dump.
//
// Purely local: it re-derives the key from the URL already stored on each row
// and makes no network calls.
//
// The helpers below are a deliberate frozen copy of `api/lib/canonical-url.ts`
// as it stood when this migration shipped, not an import of it. A migration
// runs exactly once per database, so importing the live version would mean two
// databases migrating at different times end up with keys computed under
// different rules. Improving the shared canonicalizer therefore calls for a new
// backfill migration rather than an edit here.
//
// Idempotent: the column is only added when missing and only rows whose key is
// still NULL are touched, so a fresh database built from schema.sql is a no-op.

const TRACKING_PARAMS = new Set([
  "fbclid",
  "gclid",
  "dclid",
  "msclkid",
  "twclid",
  "yclid",
  "mc_cid",
  "mc_eid",
  "igshid",
  "igsh",
  "si",
  "spm",
  "ref_src",
  "ref_url",
  "_ga",
  "_gl",
  "__twitter_impression",
]);

function isTrackingParam(key: string): boolean {
  const k = key.toLowerCase();
  return k.startsWith("utm_") || TRACKING_PARAMS.has(k);
}

const YOUTUBE_HOSTS = new Set([
  "youtube.com",
  "m.youtube.com",
  "music.youtube.com",
  "youtube-nocookie.com",
]);

function youtubeVideoId(
  host: string,
  pathname: string,
  params: URLSearchParams,
): string | null {
  if (host === "youtu.be") return pathname.split("/")[1] || null;
  if (!YOUTUBE_HOSTS.has(host)) return null;
  if (pathname === "/watch") return params.get("v");
  if (/^\/(embed|shorts|live)\//.test(pathname)) {
    return pathname.split("/")[2] || null;
  }
  return null;
}

function canonicalizeUrl(raw: string): string | null {
  let u: URL;
  try {
    u = new URL(raw);
  } catch {
    return null;
  }
  if (u.protocol !== "http:" && u.protocol !== "https:") return null;

  const host = u.hostname.toLowerCase().replace(/^www\./, "");
  if (!host) return null;

  const videoId = youtubeVideoId(host, u.pathname, u.searchParams);
  if (videoId) return `https://youtube.com/watch?v=${videoId}`;

  const listId = YOUTUBE_HOSTS.has(host) && u.pathname === "/playlist"
    ? u.searchParams.get("list")
    : null;
  if (listId) return `https://youtube.com/playlist?list=${listId}`;

  const path = u.pathname.replace(/\/+$/, "");

  const params = [...u.searchParams.entries()]
    .filter(([key]) => !isTrackingParam(key))
    .sort(([a, av], [b, bv]) => a.localeCompare(b) || av.localeCompare(bv));
  const query = new URLSearchParams(params).toString();

  const hash = /^#!?\//.test(u.hash) ? u.hash : "";

  return `https://${host}${path}${query ? `?${query}` : ""}${hash}`;
}

export function up(db: DatabaseSync): void {
  const columns = db.prepare(`PRAGMA table_info(dumps);`).all() as {
    name: string;
  }[];
  if (!columns.some((c) => c.name === "url_canonical")) {
    db.exec(`ALTER TABLE dumps ADD COLUMN url_canonical TEXT;`);
  }
  db.exec(
    `CREATE INDEX IF NOT EXISTS idx_dumps_url_canonical ON dumps(url_canonical);`,
  );

  const rows = db.prepare(
    `SELECT id, url FROM dumps WHERE url IS NOT NULL AND url_canonical IS NULL;`,
  ).all() as { id: string; url: string }[];

  const update = db.prepare(
    `UPDATE dumps SET url_canonical = ? WHERE id = ?;`,
  );
  for (const row of rows) {
    const canonical = canonicalizeUrl(row.url);
    if (canonical) update.run(canonical, row.id);
  }
}