Docs/Skills and code/src/
src/stages/18-rating-evidence-audit.js
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import crypto from "node:crypto";
import { config } from "../../config.js";
import fs from "node:fs";
import path from "node:path";
import { getDb } from "../db.js";
import { embeddingConfig } from "../analysis/embeddings.js";
import { classifyDateWindow, parseCalendarDay, breakoutEligibility } from "../analysis/rating-evidence.js";
import { isRecencyEligible } from "../analysis/temporal.js";
const db = getDb();
const root = process.cwd();
const now = new Date();
const generatedAt = now.toISOString();
const stamp = generatedAt.replaceAll(":", "-").replaceAll(".", "-");
const model = embeddingConfig().model;
const limitArg = process.argv.find(arg => arg.startsWith("--limit="));
const reportLimit = limitArg ? Math.max(1, Number(limitArg.split("=")[1]) || 130) : 130;
const reportDir = path.join(root, "reports");
fs.mkdirSync(reportDir, { recursive: true });
const exists = name => Boolean(db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(name));
const num = value => value == null || value === "" || !Number.isFinite(Number(value)) ? null : Number(value);
const tableCount = table => exists(table) ? db.prepare(`SELECT COUNT(*) AS n FROM ${table}`).get().n : null;
const hash = value => crypto.createHash("sha256").update(String(value ?? "").trim()).digest("hex");
const pct = (count, total) => total ? Number((count / total * 100).toFixed(1)) : 0;
const fmt = value => value == null ? "—" : value;
const channels = db.prepare(`SELECT c.*,
m.channel_id AS m_channel_id, m.description AS m_description, m.subscriber_count AS m_subscriber_count,
m.video_count AS m_video_count, m.total_view_count AS m_total_view_count, m.joined_date AS m_joined_date,
m.country AS m_country, m.handle AS m_handle, m.metadata_source AS m_metadata_source
FROM channels c LEFT JOIN channel_metadata m ON m.channel_url=c.channel_url
ORDER BY c.name COLLATE NOCASE`).all();
channels.splice(0, channels.length, ...filterQualifiedRows(db, channels, "channel", { label: "18-rating-evidence-audit.js" }));
const videos = db.prepare(`SELECT v.video_id, v.title, v.channel_url, v.view_count, v.published_at,
v.published_at_normalized, v.published_at_precision, v.published_at_text, v.duration_seconds,
v.description, v.thumbnail_url, v.metadata_observed_at, v.metadata_source, v.discovered_at
FROM videos v`).all();
videos.splice(0, videos.length, ...filterQualifiedRows(db, videos, "video", { label: "18-rating-evidence-audit.js" }));
const videoById = new Map(videos.map(row => [row.video_id, row]));
const associations = db.prepare(`SELECT channel_url, video_id FROM channel_videos
UNION SELECT channel_url, video_id FROM videos WHERE channel_url IS NOT NULL`).all();
const videoIdsByChannel = new Map();
for (const row of associations) {
if (!videoIdsByChannel.has(row.channel_url)) videoIdsByChannel.set(row.channel_url, new Set());
videoIdsByChannel.get(row.channel_url).add(row.video_id);
}
const embeddingRows = exists("video_embeddings") ? db.prepare(`SELECT video_id, title_hash, input_version FROM video_embeddings WHERE model=?`).all(model) : [];
const embeddingIds = new Set(embeddingRows.filter(row => {
const video = videoById.get(row.video_id);
return video && row.title_hash === hash(video.title) && row.input_version === "title-prefix-v1";
}).map(row => row.video_id));
const snapshotCounts = new Map();
const latestSnapshot = new Map();
const snapshotRows = exists("video_metric_snapshots") ? db.prepare(`SELECT video_id, observed_at, view_count FROM video_metric_snapshots ORDER BY observed_at ASC, id ASC`).all() : [];
const snapshotSeries = new Map();
for (const row of snapshotRows) {
snapshotCounts.set(row.video_id, (snapshotCounts.get(row.video_id) ?? 0) + 1);
latestSnapshot.set(row.video_id, row.observed_at);
if (!snapshotSeries.has(row.video_id)) snapshotSeries.set(row.video_id, []);
snapshotSeries.get(row.video_id).push(row);
}
const channelSnapshotCounts = new Map();
if (exists("channel_metric_snapshots")) {
for (const row of db.prepare(`SELECT channel_url, COUNT(*) AS n, MAX(observed_at) AS latest FROM channel_metric_snapshots GROUP BY channel_url`).all()) {
channelSnapshotCounts.set(row.channel_url, row);
}
}
const latestPriorityRun = exists("channel_priority_runs") ? db.prepare(`SELECT run_id, generated_at FROM channel_priority_runs ORDER BY generated_at DESC LIMIT 1`).get() : null;
const priorityByChannel = new Map();
if (latestPriorityRun && exists("channel_priority_scores")) {
for (const row of db.prepare(`SELECT * FROM channel_priority_scores WHERE run_id=?`).all(latestPriorityRun.run_id)) priorityByChannel.set(row.channel_url, row);
}
const latestQueueRun = exists("channel_queue_runs") ? db.prepare(`SELECT run_id, generated_at FROM channel_queue_runs ORDER BY generated_at DESC LIMIT 1`).get() : null;
const queueByChannel = new Map();
if (latestQueueRun && exists("channel_queue_scores")) {
for (const row of db.prepare(`SELECT * FROM channel_queue_scores WHERE run_id=?`).all(latestQueueRun.run_id)) {
if (!queueByChannel.has(row.channel_url)) queueByChannel.set(row.channel_url, []);
queueByChannel.get(row.channel_url).push(row);
}
}
const channelAudit = channels.map(channel => {
const ids = [...(videoIdsByChannel.get(channel.channel_url) ?? [])];
const rows = ids.map(id => videoById.get(id)).filter(Boolean);
let recent = 0, baseline = 0, datedWithViews = 0, unknownDateWithViews = 0, impreciseDateWithViews = 0;
let recentRepeated = 0, baselineRepeated = 0, recentVelocityReady = 0, baselineVelocityReady = 0;
for (const video of rows) {
const date = parseCalendarDay(video.published_at_normalized) ?? parseCalendarDay(video.published_at);
const hasViews = video.view_count != null && Number.isFinite(Number(video.view_count));
if (!hasViews) continue;
if (!date) { unknownDateWithViews++; continue; }
const precision = String(video.published_at_precision ?? "").trim();
const precisionUsable = !precision || precision === "date" || isRecencyEligible(precision);
if (!precisionUsable) { impreciseDateWithViews++; continue; }
datedWithViews++;
const bucket = classifyDateWindow(date, now);
const count = snapshotCounts.get(video.video_id) ?? 0;
const snapshots = (snapshotSeries.get(video.video_id) ?? []).filter(row => row.view_count != null && Number.isFinite(Date.parse(row.observed_at)));
const spanHours = snapshots.length >= 2 ? (Date.parse(snapshots.at(-1).observed_at) - Date.parse(snapshots[0].observed_at)) / 3600000 : 0;
const velocityReady = snapshots.length >= 2 && spanHours >= 24 && Number(snapshots.at(-1).view_count) >= Number(snapshots[0].view_count);
if (bucket === "recent") { recent++; if (count >= 2) recentRepeated++; if (velocityReady) recentVelocityReady++; }
else if (bucket === "baseline") { baseline++; if (count >= 2) baselineRepeated++; if (velocityReady) baselineVelocityReady++; }
}
const embedded = ids.filter(id => embeddingIds.has(id)).length;
const metadataFields = [
channel.m_channel_id ?? channel.channel_id,
channel.m_description ?? channel.description,
channel.m_subscriber_count ?? channel.subscriber_count,
channel.m_video_count ?? channel.video_count,
channel.m_total_view_count ?? channel.total_view_count,
channel.m_joined_date ?? channel.joined_date,
channel.m_country ?? channel.country,
channel.m_handle ?? channel.handle,
channel.thumbnail_url
];
const missingVideoViews = rows.filter(v => v.view_count == null).length;
const missingVideoDates = rows.filter(v => !(parseCalendarDay(v.published_at_normalized) ?? parseCalendarDay(v.published_at))).length;
const missingDescriptions = rows.filter(v => !v.description).length;
const eligibility = breakoutEligibility(recent, baseline);
const priority = priorityByChannel.get(channel.channel_url) ?? {};
const queueRows = queueByChannel.get(channel.channel_url) ?? [];
const learn = queueRows.find(row => row.queue_key === "learn_from");
const breakout = queueRows.find(row => row.queue_key === "breaking_out");
return {
channel_url: channel.channel_url, channel_name: channel.name,
video_count_in_corpus: ids.length, embedded_current: embedded, embedding_gap: Math.max(0, ids.length - embedded),
video_count_with_views: rows.length - missingVideoViews, missing_view_counts: missingVideoViews,
dated_and_viewed: datedWithViews, unknown_date_but_viewed: unknownDateWithViews, imprecise_date_but_viewed: impreciseDateWithViews, missing_dates: missingVideoDates,
recent_viewed_videos: recent, baseline_viewed_videos: baseline,
breakout_eligible_now: eligibility.eligible, missing_recent_for_breakout: eligibility.missingRecent, missing_baseline_for_breakout: eligibility.missingBaseline,
recent_videos_with_repeated_snapshots: recentRepeated, baseline_videos_with_repeated_snapshots: baselineRepeated,
recent_videos_with_24h_velocity: recentVelocityReady, baseline_videos_with_24h_velocity: baselineVelocityReady,
videos_missing_descriptions: missingDescriptions, videos_missing_thumbnails: rows.filter(v => !v.thumbnail_url).length,
channel_metadata_fields_present: metadataFields.filter(v => v !== null && v !== undefined && v !== "").length,
channel_metadata_fields_total: metadataFields.length,
channel_snapshot_count: channelSnapshotCounts.get(channel.channel_url)?.n ?? 0,
channel_snapshot_latest: channelSnapshotCounts.get(channel.channel_url)?.latest ?? null,
specialist_score: num(priority.specialist_score), breakthrough_score: num(priority.breakthrough_score),
priority_embedded_count: num(priority.embedded_video_count), priority_performance_count: num(priority.performance_video_count),
learn_queue_status: learn?.status ?? "no_queue_run", learn_queue_score: num(learn?.score),
breakout_queue_status: breakout?.status ?? "no_queue_run", breakout_queue_score: num(breakout?.score),
priority_missing_signals: (() => { try { return JSON.parse(priority.rationale_json ?? "{}").missingSignals ?? []; } catch { return []; } })()
};
});
const fieldCoverage = [
["view_count", videos.filter(v => v.view_count != null).length],
["parseable published date", videos.filter(v => parseCalendarDay(v.published_at_normalized) ?? parseCalendarDay(v.published_at)).length],
["published_at_normalized", videos.filter(v => parseCalendarDay(v.published_at_normalized)).length],
["exact normalized dates", videos.filter(v => String(v.published_at_precision ?? "").startsWith("exact_")).length],
["estimated normalized dates", videos.filter(v => String(v.published_at_precision ?? "").startsWith("estimated_")).length],
["recency-eligible dates (exact/day/week precision)", videos.filter(v => { const p=String(v.published_at_precision ?? "").trim(); return p === "date" || !p || isRecencyEligible(p); }).length],
["duration_seconds", videos.filter(v => v.duration_seconds != null).length],
["description", videos.filter(v => v.description).length],
["thumbnail_url", videos.filter(v => v.thumbnail_url).length],
["current title embeddings", embeddingIds.size],
["at least one metric snapshot", [...snapshotCounts.values()].filter(n => n >= 1).length],
["two or more metric snapshots", [...snapshotCounts.values()].filter(n => n >= 2).length]
];
const queueSummary = [];
if (latestQueueRun && exists("channel_queue_scores")) {
for (const row of db.prepare(`SELECT queue_key, status, COUNT(*) AS n FROM channel_queue_scores WHERE run_id=? GROUP BY queue_key, status ORDER BY queue_key, status`).all(latestQueueRun.run_id)) queueSummary.push(row);
}
const summary = {
generated_at: generatedAt, database: path.resolve(config.dbPath), embedding_model: model,
channels: channels.length, videos: videos.length, channel_video_relationships: associations.length,
video_metric_snapshot_rows: snapshotRows.length,
videos_with_snapshots: [...snapshotCounts.values()].filter(n => n >= 1).length,
videos_with_repeated_snapshots: [...snapshotCounts.values()].filter(n => n >= 2).length,
latest_priority_run: latestPriorityRun ?? null, latest_queue_run: latestQueueRun ?? null,
channels_with_specialist_score: channelAudit.filter(c => c.specialist_score != null).length,
channels_with_breakthrough_score: channelAudit.filter(c => c.breakthrough_score != null).length,
channels_with_minimum_breakout_evidence: channelAudit.filter(c => c.breakout_eligible_now).length,
field_coverage: fieldCoverage.map(([field, count]) => ({ field, count, total: field === "current title embeddings" || field.includes("snapshot") ? videos.length : videos.length, percent: pct(count, videos.length) })),
queue_summary: queueSummary
};
const byEmbeddingGap = [...channelAudit].sort((a, b) => b.embedding_gap - a.embedding_gap || b.video_count_in_corpus - a.video_count_in_corpus).slice(0, reportLimit);
const byBreakoutGap = [...channelAudit].sort((a, b) => (b.missing_recent_for_breakout + b.missing_baseline_for_breakout) - (a.missing_recent_for_breakout + a.missing_baseline_for_breakout) || b.video_count_in_corpus - a.video_count_in_corpus).slice(0, reportLimit);
const report = { summary, channels: channelAudit, highest_embedding_gaps: byEmbeddingGap, highest_breakout_gaps: byBreakoutGap };
const jsonPath = path.join(reportDir, `rating-evidence-audit-${stamp}.json`);
const mdPath = path.join(reportDir, `rating-evidence-audit-${stamp}.md`);
fs.writeFileSync(jsonPath, JSON.stringify(report, null, 2));
const lines = [
"# Rating Evidence Audit", "", `Generated: ${generatedAt}`, `Embedding model: ${model}`, "",
"## Corpus coverage", "", `- Channels: ${channels.length}`, `- Videos: ${videos.length}`, `- Channel-video relationships: ${associations.length}`,
`- Videos with at least one metric snapshot: ${summary.videos_with_snapshots}`, `- Videos with repeated metric snapshots: ${summary.videos_with_repeated_snapshots}`,
`- Latest priority run: ${latestPriorityRun?.run_id ?? "none"}`, `- Latest queue run: ${latestQueueRun?.run_id ?? "none"}`, "",
"| Field | Present | Coverage |", "|---|---:|---:|",
...fieldCoverage.map(([field, count]) => `| ${field} | ${count}/${videos.length} | ${pct(count, videos.length)}% |`), "",
"## Queue blockers", "", `- Channels with specialist scores: ${summary.channels_with_specialist_score}/${channels.length}`,
`- Channels with breakthrough scores in the latest priority run: ${summary.channels_with_breakthrough_score}/${channels.length}`,
`- Channels currently meeting the queue's minimum recent/baseline evidence (excluding month/year precision estimates): ${summary.channels_with_minimum_breakout_evidence}/${channels.length}`, "",
"## Highest embedding gaps", "", "| Channel | Corpus videos | Embedded | Gap | Specialist | Learn queue |", "|---|---:|---:|---:|---:|---|",
...byEmbeddingGap.slice(0, 30).map(c => `| ${String(c.channel_name).replaceAll("|", "\\|")} | ${c.video_count_in_corpus} | ${c.embedded_current} | ${c.embedding_gap} | ${fmt(c.specialist_score)} | ${c.learn_queue_status} |`), "",
"## Breakout evidence gaps", "", "| Channel | Recent with views | Baseline with views | Need recent | Need baseline | Recent with >=24h velocity | Status |", "|---|---:|---:|---:|---:|---:|---|",
...byBreakoutGap.slice(0, 40).map(c => `| ${String(c.channel_name).replaceAll("|", "\\|")} | ${c.recent_viewed_videos} | ${c.baseline_viewed_videos} | ${c.missing_recent_for_breakout} | ${c.missing_baseline_for_breakout} | ${c.recent_videos_with_24h_velocity} | ${c.breakout_queue_status} |`), "",
"## Recommended order", "", "1. Backfill missing video fields, then run `npm run normalize:temporal`. Relative dates are estimates anchored to discovery time; exact dates remain preferable.",
"2. Run `npm run videos:embed -- --all` and wait for all batches to finish.",
"3. Rebuild channel priorities and reports before recalculating the five queues.",
"4. Snapshot a fixed per-channel cohort of recent videos repeatedly over multiple days. Only day/week-or-better publication precision is eligible for recent/baseline cohorts. The breakout scorer prefers measured recent velocity after at least three recent videos have >=24h snapshot spans; until then it uses the explicitly labelled lifetime views/day proxy.",
"5. Inspect the channel-level JSON for blockers before spending more fetches on already-complete channels.", ""
];
fs.writeFileSync(mdPath, lines.join("\n"));
console.log("[rating-audit] Summary:", summary);
console.log("[rating-audit] Reports:", { markdown: mdPath, json: jsonPath });
console.log("[rating-audit] Highest embedding gaps:", byEmbeddingGap.slice(0, 8).map(c => ({ channel: c.channel_name, videos: c.video_count_in_corpus, embedded: c.embedded_current, gap: c.embedding_gap })));
console.log("[rating-audit] Breakout gaps:", byBreakoutGap.slice(0, 8).map(c => ({ channel: c.channel_name, recent: c.recent_viewed_videos, baseline: c.baseline_viewed_videos, missingRecent: c.missing_recent_for_breakout, missingBaseline: c.missing_baseline_for_breakout })));
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.