Docs/Skills and code/src/
src/stages/30-enrichment-coverage-report.js
import fs from "node:fs";
import path from "node:path";
import { getDb } from "../db.js";
const db = getDb();
const root = process.cwd();
const outDir = path.join(root, "reports", "enrichment");
fs.mkdirSync(outDir, { recursive: true });
const exists = name => Boolean(db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(name));
const tableRows = name => exists(name) ? db.prepare(`SELECT * FROM "${name}"`).all() : [];
const tableColumns = name => exists(name) ? db.prepare(`PRAGMA table_info("${name}")`).all().map(row => row.name) : [];
const channels = tableRows("channels");
const metadata = new Map(tableRows("channel_metadata").map(row => [row.channel_url, row]));
const profiles = new Map(tableRows("channel_profiles").map(row => [row.channel_url, row]));
const qualifications = new Map(tableRows("channel_qualifications").map(row => [row.channel_url, row]));
const llmQualifications = tableRows("channel_qualification_llm_results").filter(row => row.processing_status === "succeeded");
const latestLlm = new Map();
for (const row of llmQualifications.sort((a, b) => String(b.created_at ?? "").localeCompare(String(a.created_at ?? "")))) {
if (!latestLlm.has(row.channel_url)) latestLlm.set(row.channel_url, row);
}
const videos = new Map(tableRows("videos").map(row => [row.video_id, row]));
const videosByChannel = new Map();
function addVideo(channelUrl, videoId) {
if (!channelUrl || !videoId) return;
if (!videosByChannel.has(channelUrl)) videosByChannel.set(channelUrl, new Set());
videosByChannel.get(channelUrl).add(videoId);
}
for (const video of videos.values()) addVideo(video.channel_url, video.video_id);
for (const link of tableRows("channel_videos")) addVideo(link.channel_url, link.video_id);
const isPresent = value => value !== null && value !== undefined && String(value).trim() !== "";
const effective = (channel, meta, field) => isPresent(meta?.[field]) ? meta[field] : channel[field];
const csvCell = value => {
const text = value == null ? "" : Array.isArray(value) ? value.join("; ") : String(value);
return /[",\r\n]/.test(text) ? `"${text.replaceAll('"', '""')}"` : text;
};
const writeCsv = (file, rows, fields) => fs.writeFileSync(file, [fields.map(csvCell).join(","), ...rows.map(row => fields.map(field => csvCell(row[field])).join(","))].join("\n") + "\n", "utf8");
const metadataFields = [
["channel_id", "Channel identity", "npm run channel-metadata", "high"],
["description", "Channel description", "npm run channel-metadata", "high"],
["subscriber_count", "Audience size", "npm run channel-metadata", "high"],
["video_count", "Declared catalogue size", "npm run channel-metadata", "high"],
["total_view_count", "Lifetime channel views", "npm run channel-metadata", "high"],
["joined_date", "Channel age", "npm run channel-metadata", "high"],
["country", "Channel country", "npm run channel-metadata", "low"],
["canonical_url", "Canonical channel URL", "npm run channel-metadata", "medium"],
["handle", "Channel handle", "npm run channel-metadata", "medium"],
["keywords_json", "Channel keywords", "npm run channel-metadata", "medium"],
["thumbnail_url", "Channel thumbnail", "npm run channel-metadata", "low"],
["banner_url", "Channel banner", "npm run channel-metadata", "low"],
["custom_links_json", "Channel external links", "npm run channel-metadata", "low"],
["metadata_source", "Metadata provenance", "npm run channel-metadata", "medium"]
];
const gapRows = [];
const backlog = [];
const channelVideoStats = new Map();
for (const channel of channels) {
const url = channel.channel_url;
const meta = metadata.get(url);
const missing = [];
for (const [field, label, command, priority] of metadataFields) {
if (!isPresent(effective(channel, meta, field))) missing.push(field);
}
const videoIds = [...(videosByChannel.get(url) ?? [])];
const linkedVideos = videoIds.map(id => videos.get(id)).filter(Boolean);
const views = linkedVideos.filter(video => isPresent(video.view_count));
const dates = linkedVideos.filter(video => isPresent(video.published_at_normalized) || isPresent(video.published_at));
const durations = linkedVideos.filter(video => isPresent(video.duration_seconds));
channelVideoStats.set(url, { linked: linkedVideos.length, views: views.length, dates: dates.length, durations: durations.length });
const profileRow = profiles.get(url);
let profile = {};
try { profile = JSON.parse(profileRow?.profile_json ?? "{}"); } catch { profile = {}; }
const vocab = profile.vocabulary_proposals ?? {};
const profileHasSignal = Boolean(String(profile.channel_summary ?? "").trim()) || ["themes", "entities", "angles", "formats", "hooks", "promises", "packaging"].some(key => Array.isArray(vocab[key]) && vocab[key].length > 0);
const qualification = latestLlm.get(url) ?? qualifications.get(url);
const issues = [...missing];
if (!linkedVideos.length) issues.push("linked_video_sample");
if (linkedVideos.length && views.length < linkedVideos.length) issues.push("video_view_counts");
if (linkedVideos.length && dates.length < linkedVideos.length) issues.push("video_publication_dates");
if (linkedVideos.length && durations.length < linkedVideos.length) issues.push("video_durations");
if (!profileRow || !profileHasSignal) issues.push("editorial_profile_research_only");
if (!qualification) issues.push("qualification");
if (issues.length) backlog.push({
channel_name: channel.name, channel_url: url, discovered_at: channel.discovered_at ?? "",
missing_metadata_fields: missing.join("; "), linked_videos: linkedVideos.length,
videos_with_views: views.length, videos_with_publication_dates: dates.length,
videos_with_durations: durations.length,
profile_status: !profileRow ? "missing" : profileHasSignal ? "populated" : "empty_or_unusable",
profile_affects_queue_ratings: "no",
qualification_status: !qualification ? "missing" : (qualification.processing_status === "succeeded" ? "llm_succeeded_unchecked_for_freshness" : "canonical_row_present"),
last_metadata_fetch: meta?.fetched_at ?? "", metadata_fetch_error: meta?.fetch_error ?? "",
recommended_actions: [missing.length ? "channel-metadata" : null, linkedVideos.length < 3 ? `npm run catalogue:mine -- --channel-url="${url}" --limit-per-channel=300` : null, linkedVideos.length && views.length < linkedVideos.length ? "video-metadata" : null, linkedVideos.length && dates.length < linkedVideos.length ? "video-metadata; verify date normalization" : null, !qualification ? "qualification:llm" : null, !profileHasSignal ? "profile:channels (research only; does not affect queues)" : null].filter(Boolean).join("; ")
});
}
for (const [field, label, command, priority] of metadataFields) {
const populated = channels.filter(channel => isPresent(effective(channel, metadata.get(channel.channel_url), field))).length;
const missingCount = channels.length - populated;
gapRows.push({ category: "channel_metadata", field, label, total_channels: channels.length, populated, missing: missingCount, coverage_percent: channels.length ? Number((populated / channels.length * 100).toFixed(1)) : 0, priority, failure_count: channels.filter(channel => !isPresent(effective(channel, metadata.get(channel.channel_url), field)) && isPresent(metadata.get(channel.channel_url)?.fetch_error)).length, recommended_action: command, command: missingCount ? command : "none needed" });
}
const allVideoRows = [...videos.values()];
const videoTotals = {
linked: [...channelVideoStats.values()].reduce((sum, row) => sum + row.linked, 0),
views: allVideoRows.filter(video => isPresent(video.view_count)).length,
dates: allVideoRows.filter(video => isPresent(video.published_at_normalized) || isPresent(video.published_at)).length,
durations: allVideoRows.filter(video => isPresent(video.duration_seconds)).length
};
for (const [field, label, presentCount, command, priority] of [
["linked_video_sample", "Channels with at least one linked video", [...channelVideoStats.values()].filter(row => row.linked > 0).length, "npm run catalogue:mine", "high"],
["video_view_counts", "Linked videos with view counts", videoTotals.views, "npm run video-metadata", "high"],
["video_publication_dates", "Linked videos with publication dates", videoTotals.dates, "npm run video-metadata; npm run dates:repair", "high"],
["video_durations", "Linked videos with duration seconds", videoTotals.durations, "npm run video-metadata", "medium"]
]) {
const total = field === "linked_video_sample" ? channels.length : videos.size;
gapRows.push({ category: "video_evidence", field, label, total_channels: field === "linked_video_sample" ? channels.length : "", total_videos: field === "linked_video_sample" ? "" : total, populated: presentCount, missing: Math.max(0, total - presentCount), coverage_percent: total ? Number((presentCount / total * 100).toFixed(1)) : 0, priority, failure_count: "", recommended_action: command, command: presentCount < total ? command : "none needed" });
}
const profilePopulated = channels.filter(channel => {
const row = profiles.get(channel.channel_url); if (!row) return false;
try { const p = JSON.parse(row.profile_json ?? "{}"); const v = p.vocabulary_proposals ?? {}; return Boolean(String(p.channel_summary ?? "").trim()) || ["themes", "entities", "angles", "formats", "hooks", "promises", "packaging"].some(key => Array.isArray(v[key]) && v[key].length); } catch { return false; }
}).length;
const qualified = channels.filter(channel => latestLlm.has(channel.channel_url) || qualifications.has(channel.channel_url)).length;
gapRows.push({ category: "research_only", field: "channel_profiles", label: "Channels with usable channel profiles (excluded from queue scoring)", total_channels: channels.length, populated: profilePopulated, missing: channels.length - profilePopulated, coverage_percent: channels.length ? Number((profilePopulated / channels.length * 100).toFixed(1)) : 0, priority: "optional", failure_count: "", recommended_action: "npm run profile:channels", command: "optional research-only enrichment" });
gapRows.push({ category: "qualification", field: "channel_qualification", label: "Channels with any stored qualification", total_channels: channels.length, populated: qualified, missing: channels.length - qualified, coverage_percent: channels.length ? Number((qualified / channels.length * 100).toFixed(1)) : 0, priority: "high", failure_count: "", recommended_action: "npm run qualification:llm", command: qualified < channels.length ? "npm run qualification:llm" : "inspect freshness / input fingerprints" });
const now = new Date().toISOString();
const summary = {
generated_at: now, channels: channels.length, videos: videos.size,
metadata_fields_missing_any: gapRows.filter(row => row.category === "channel_metadata" && Number(row.missing) > 0).length,
channels_with_metadata_gaps: backlog.filter(row => row.missing_metadata_fields).length,
channels_with_no_linked_videos: [...channelVideoStats.values()].filter(row => row.linked === 0).length,
channels_with_missing_qualification: channels.length - qualified,
usable_channel_profiles: profilePopulated,
note: "Read-only report. Channel profiles/vocabulary are listed for research coverage only and are excluded from all queue scores in channel-queues-v5-raw-metadata-only. Missing fields are not proof the source exposes them."
};
const md = [
"# Enrichment coverage report", "", `Generated: ${now}`, "",
"## Summary", "",
`- Channels: **${summary.channels}**`, `- Videos: **${summary.videos}**`,
`- Channels with channel-metadata gaps: **${summary.channels_with_metadata_gaps}**`,
`- Channels with no linked videos: **${summary.channels_with_no_linked_videos}**`,
`- Channels with no stored qualification: **${summary.channels_with_missing_qualification}**`,
`- Channels with usable channel profiles: **${summary.usable_channel_profiles}** (research only; does not affect queue ratings)`, "",
"## Channel metadata coverage", "",
"| Field | Populated | Missing | Coverage | Priority | Suggested action |", "|---|---:|---:|---:|---|---|",
...gapRows.filter(row => row.category === "channel_metadata").map(row => `| ${row.field} | ${row.populated} | ${row.missing} | ${row.coverage_percent}% | ${row.priority} | \`${row.recommended_action}\` |`), "",
"## Other evidence coverage", "",
"| Field | Populated | Missing | Coverage | Suggested action |", "|---|---:|---:|---:|---|",
...gapRows.filter(row => row.category !== "channel_metadata").map(row => `| ${row.field} | ${row.populated} | ${row.missing} | ${row.coverage_percent}% | \`${row.recommended_action}\` |`), "",
"## Recommended run order", "",
"1. Run `npm run enrichment:report` again after each enrichment pass to measure the change.",
"2. Run `npm run channel-metadata` to fetch missing channel fields; use `npm run channel-metadata -- --force` only if you intentionally want to refresh all channels.",
"3. Address video view/date/duration gaps with `npm run video-metadata`, then `npm run dates:repair` where appropriate.",
"4. Inspect channels with no/very few linked videos and run `npm run catalogue:mine` selectively where useful.",
"5. Run `npm run qualification:llm` for missing qualifications; queue scoring checks the input fingerprint and will not trust stale LLM assessments.",
"6. Run `npm run queues:rate -- --apply` after desired metadata/qualification changes to persist fresh queue ratings.",
"7. `npm run profile:channels` remains available for the separate research/ontology workflow, but its output is not used by queue ratings.", "",
"## Interpretation notes", "",
"- `missing` means no usable value was found in either the canonical channel row or channel-metadata row.",
"- A recorded fetch error is counted separately in the CSV. A missing value without an error is not automatically classified as source-unavailable.",
"- Video coverage counts are based on unique video rows and channel links; publication dates are reported as present if either normalized or raw date exists.",
"- Qualification coverage counts any stored LLM success or canonical qualification row; it does not replace the queue stage's freshness/fingerprint check.",
"- Channel-profile coverage is informational only and does not enter any queue rating."
].join("\n");
fs.writeFileSync(path.join(outDir, "coverage.md"), md, "utf8");
writeCsv(path.join(outDir, "field-gaps.csv"), gapRows, ["category", "field", "label", "total_channels", "total_videos", "populated", "missing", "coverage_percent", "priority", "failure_count", "recommended_action", "command"]);
writeCsv(path.join(outDir, "channel-backlog.csv"), backlog, ["channel_name", "channel_url", "discovered_at", "missing_metadata_fields", "linked_videos", "videos_with_views", "videos_with_publication_dates", "videos_with_durations", "profile_status", "profile_affects_queue_ratings", "qualification_status", "last_metadata_fetch", "metadata_fetch_error", "recommended_actions"]);
fs.writeFileSync(path.join(outDir, "coverage-summary.json"), JSON.stringify(summary, null, 2) + "\n", "utf8");
console.log("[enrichment-report] Read-only coverage report generated.");
console.log(JSON.stringify({ ...summary, output: ["reports/enrichment/coverage.md", "reports/enrichment/field-gaps.csv", "reports/enrichment/channel-backlog.csv", "reports/enrichment/coverage-summary.json"] }, null, 2));
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.