Docs/Skills and code/src/

src/stages/21-backfill-missing-qualifications.js

Code71 lines3.9 KBsrc/

import { config } from "../../config.js";
import { getDb } from "../db.js";
import { ensureAnalysisSchema } from "../analysis-schema.js";
import { classifyChannel, classifyVideos, saveQualification, saveVideoQualifications } from "../channel-qualification.js";

const db = getDb();
ensureAnalysisSchema();
const limitArg = process.argv.find(value => value.startsWith("--limit="));
const limit = limitArg ? Number(limitArg.slice("--limit=".length)) : Infinity;
if (!(limit > 0)) throw new Error("--limit must be a positive number when supplied.");

const before = {
  videos: db.prepare("SELECT COUNT(*) AS n FROM videos").get().n,
  videoQualifications: db.prepare("SELECT COUNT(*) AS n FROM video_qualifications").get().n,
  channels: db.prepare("SELECT COUNT(*) AS n FROM channels").get().n,
  channelQualifications: db.prepare("SELECT COUNT(*) AS n FROM channel_qualifications").get().n
};
console.log("[qualify-missing] Starting title-rule qualification backfill", { database: config.database?.path ?? "configured database", before, mode: "MISSING ROWS ONLY; existing qualifications preserved" });

const missingVideoRows = db.prepare(`
  SELECT v.video_id, v.title
  FROM videos v LEFT JOIN video_qualifications q ON q.video_id=v.video_id
  WHERE q.video_id IS NULL AND trim(COALESCE(v.title,'')) <> ''
  ORDER BY v.discovered_at, v.video_id
`).all();
const missingVideos = Number.isFinite(limit) ? missingVideoRows.slice(0, Math.floor(limit)) : missingVideoRows;
if (missingVideos.length) {
  const classified = classifyVideos(missingVideos, config.ecosystem);
  saveVideoQualifications(db, classified, config.ecosystem.name);
} else {
  console.log("[qualify-missing] No videos are missing qualification rows.");
}

const missingChannels = db.prepare(`
  SELECT c.channel_url, c.name,
    COALESCE(m.description, c.description) AS description,
    COALESCE(m.keywords_json, c.keywords_json) AS keywords_json
  FROM channels c
  LEFT JOIN channel_metadata m ON m.channel_url=c.channel_url
  LEFT JOIN channel_qualifications q ON q.channel_url=c.channel_url
  WHERE q.channel_url IS NULL
  ORDER BY c.name COLLATE NOCASE
`).all();
// Query each missing channel's available linked titles; no network fetches or assumed ordering.
for (const channel of missingChannels) {
  let keywords = [];
  try {
    const parsed = JSON.parse(channel.keywords_json ?? "[]");
    keywords = Array.isArray(parsed) ? parsed : [];
  } catch { keywords = []; }
  const linkedTitles = db.prepare(`
    SELECT DISTINCT v.title
    FROM videos v
    LEFT JOIN channel_videos cv ON cv.video_id=v.video_id AND cv.channel_url=?
    WHERE (cv.channel_url IS NOT NULL OR v.channel_url=?) AND trim(COALESCE(v.title,''))<>''
  `).all(channel.channel_url, channel.channel_url);
  const result = classifyChannel({ ...channel, keywords }, linkedTitles, [], config.ecosystem);
  saveQualification(db, channel.channel_url, config.ecosystem.name, result);
  console.log("[qualify-missing] Channel classified", { channel: channel.name, classification: result.classification, score: result.score, titleEvidence: linkedTitles.length });
}

const after = {
  videos: db.prepare("SELECT COUNT(*) AS n FROM videos").get().n,
  videoQualifications: db.prepare("SELECT COUNT(*) AS n FROM video_qualifications").get().n,
  unqualifiedVideos: db.prepare("SELECT COUNT(*) AS n FROM videos v LEFT JOIN video_qualifications q ON q.video_id=v.video_id WHERE q.video_id IS NULL").get().n,
  channels: db.prepare("SELECT COUNT(*) AS n FROM channels").get().n,
  channelQualifications: db.prepare("SELECT COUNT(*) AS n FROM channel_qualifications").get().n,
  unqualifiedChannels: db.prepare("SELECT COUNT(*) AS n FROM channels c LEFT JOIN channel_qualifications q ON q.channel_url=c.channel_url WHERE q.channel_url IS NULL").get().n
};
console.log("[qualify-missing] Complete", { selectedVideos: missingVideos.length, selectedChannels: missingChannels.length, before, after, note: "Rule-based title classification is a triage signal, not an editorially reviewed truth label." });

Shown as it is in the repository at build time (2026-11-03); the home directory appears as <home>. If this page and the file differ, the file is right. Back to the catalogue.