Docs/Skills and code/src/
src/stages/17-rate-channel-queues.js
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import { getDb } from "../db.js";
import { scoreChannelQueues } from "../analysis/channel-queues.js";
import { writeChannelQueueReports } from "../analysis/channel-queues-report.js";
import { persistQueueRatings } from "../analysis/queue-table-store.js";
import { buildQualificationInput } from "../analysis/channel-evidence.js";
import { config as appConfig } from "../../config.js";
const db = getDb();
const args = process.argv.slice(2);
const notapply = args.includes("--notapply");
const help = args.includes("--help");
const now = new Date();
const generatedAt = now.toISOString();
const runId = generatedAt.replaceAll(":", "-").replaceAll(".", "-");
const root = process.cwd();
if (help) {
console.log(`Channel queue rating (safe preview by default)\n\n npm run queues:rate # not preview; queue rows persisted\n npm run queues:rate -- --notapply # save run + 5 queue ratings/channel\n\nOutputs reports/channel-queues-<timestamp>.md and .json in either mode.`);
process.exit(0);
}
const tableExists = name => Boolean(db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(name));
const safeRows = (table, sql) => tableExists(table) ? db.prepare(sql).all() : [];
const parseJson = (value, fallback = {}) => { if (value == null || value === "") return fallback; try { return JSON.parse(value) ?? fallback; } catch { return fallback; } };
const num = value => value == null || value === "" || !Number.isFinite(Number(value)) ? null : Number(value);
const coalesce = (...values) => values.find(value => value !== null && value !== undefined && value !== "") ?? null;
const channelRows = db.prepare(`
SELECT c.*,
m.channel_id AS m_channel_id, m.name AS m_name, m.description AS m_description,
m.subscriber_count AS m_subscriber_count, m.subscriber_count_text AS m_subscriber_count_text,
m.video_count AS m_video_count, m.video_count_text AS m_video_count_text,
m.total_view_count AS m_total_view_count, m.total_view_count_text AS m_total_view_count_text,
m.joined_date AS m_joined_date, m.joined_date_text AS m_joined_date_text,
m.country AS m_country, m.canonical_url AS m_canonical_url, m.handle AS m_handle,
m.thumbnail_url AS m_thumbnail_url, m.metadata_source AS m_metadata_source,
m.custom_links_json AS m_custom_links_json,
m.keywords_json AS m_keywords_json,
m.subscriber_count_precision AS m_subscriber_count_precision,
m.video_count_precision AS m_video_count_precision,
m.total_view_count_precision AS m_total_view_count_precision,
cq.classification AS ecosystem_classification, cq.reason AS ecosystem_reason
FROM channels c
LEFT JOIN channel_metadata m ON m.channel_url=c.channel_url
LEFT JOIN channel_qualifications cq ON cq.channel_url=c.channel_url
ORDER BY c.name COLLATE NOCASE, c.channel_url
`).all();
channelRows.splice(0, channelRows.length, ...filterQualifiedRows(db, channelRows, "channel", { label: "17-rate-channel-queues.js" }));
const videoRows = db.prepare(`
SELECT v.video_id, v.title, v.url, v.channel_url, v.view_count, v.view_count_text,
v.published_at, v.published_at_normalized, v.published_at_precision,
v.published_at_text, v.metadata_observed_at, v.metadata_source, v.discovered_at, v.duration_seconds, v.duration_precision, v.category
FROM videos v
`).all();
videoRows.splice(0, videoRows.length, ...filterQualifiedRows(db, videoRows, "video", { label: "17-rate-channel-queues.js" }));
const videoById = new Map(videoRows.map(row => [row.video_id, {
...row,
// Queue scores use captured static video fields only. No snapshot fallback.
view_count: num(row.view_count),
view_count_text: row.view_count_text ?? null,
published_at: coalesce(row.published_at_normalized, row.published_at),
published_at_normalized: coalesce(row.published_at_normalized, row.published_at),
published_at_text: row.published_at_text ?? null,
published_at_precision: row.published_at_precision ?? null
}]));
const videoIdsByChannel = new Map();
for (const video of videoById.values()) {
if (video.channel_url) {
if (!videoIdsByChannel.has(video.channel_url)) videoIdsByChannel.set(video.channel_url, new Set());
videoIdsByChannel.get(video.channel_url).add(video.video_id);
}
}
for (const relation of safeRows("channel_videos", `SELECT channel_url, video_id FROM channel_videos`)) {
if (!videoIdsByChannel.has(relation.channel_url)) videoIdsByChannel.set(relation.channel_url, new Set());
videoIdsByChannel.get(relation.channel_url).add(relation.video_id);
}
const referenceSeeds = appConfig.ecosystem.referenceChannels ?? [];
const channels = channelRows.map(row => {
const url = row.channel_url;
const ids = [...(videoIdsByChannel.get(url) ?? [])];
return {
channel_url: url,
name: coalesce(row.m_name, row.name, url),
handle: coalesce(row.m_handle, row.handle),
channel_id: coalesce(row.m_channel_id, row.channel_id),
description: coalesce(row.m_description, row.description),
subscriber_count: coalesce(num(row.m_subscriber_count), num(row.subscriber_count)),
subscriber_count_text: coalesce(row.m_subscriber_count_text, row.subscriber_count_text),
subscriber_count_precision: coalesce(row.m_subscriber_count_precision, row.subscriber_count_precision),
video_count: coalesce(num(row.m_video_count), num(row.video_count)),
video_count_precision: coalesce(row.m_video_count_precision, row.video_count_precision),
video_count_text: coalesce(row.m_video_count_text, row.video_count_text),
total_view_count: coalesce(num(row.m_total_view_count), num(row.total_view_count)),
total_view_count_text: coalesce(row.m_total_view_count_text, row.total_view_count_text),
total_view_count_precision: coalesce(row.m_total_view_count_precision, row.total_view_count_precision),
joined_date: coalesce(row.m_joined_date, row.joined_date),
joined_date_text: coalesce(row.m_joined_date_text, row.joined_date_text),
country: coalesce(row.m_country, row.country),
canonical_url: coalesce(row.m_canonical_url, row.canonical_url),
thumbnail_url: coalesce(row.m_thumbnail_url, row.thumbnail_url),
custom_links_json: coalesce(row.m_custom_links_json, row.custom_links_json),
metadata_source: coalesce(row.m_metadata_source, row.metadata_source),
keywords_json: coalesce(row.m_keywords_json, row.keywords_json),
keywords: parseJson(coalesce(row.m_keywords_json, row.keywords_json), []),
is_verified: row.is_verified ?? null,
is_artist: row.is_artist ?? null,
ecosystem_classification: row.ecosystem_classification ?? "unknown",
ecosystem_reason: row.ecosystem_reason ?? null,
ecosystem_classification_source: "rule_based",
ecosystem_classification_confidence: 0.35,
sampled_video_count: ids.length,
videos: ids.map(id => videoById.get(id)).filter(Boolean),
top_video_ids: ids.slice(0, 3).map(id => `https://www.youtube.com/watch?v=${id}`),
};
});
// Prefer a successful LLM qualification only when its exact input fingerprint and prompt version match.
// Stale assessments are ignored instead of silently surviving changed channel/video evidence.
const llmTableExists = tableExists("channel_qualification_llm_results");
let llmAssessmentRows = [];
if (llmTableExists) {
llmAssessmentRows = db.prepare(`SELECT * FROM channel_qualification_llm_results
WHERE processing_status='succeeded' AND prompt_version=? ORDER BY created_at DESC`)
.all(appConfig.qualification.promptVersion);
}
const latestLlmByUrl = new Map();
for (const row of llmAssessmentRows) if (!latestLlmByUrl.has(row.channel_url)) latestLlmByUrl.set(row.channel_url, row);
let matchedLlmAssessments = 0;
for (const channel of channels) {
const stored = latestLlmByUrl.get(channel.channel_url);
if (!stored) continue;
const { inputHash } = buildQualificationInput(channel);
if (stored.input_hash !== inputHash) continue;
const assessment = {
classification: stored.classification,
relevance_score: stored.relevance_score,
confidence: stored.confidence,
focus_specificity: stored.focus_specificity,
channel_type: stored.channel_type,
focus_summary: stored.focus_summary,
rationale: stored.rationale,
supporting_evidence: parseJson(stored.supporting_evidence_json, []),
counter_evidence: parseJson(stored.counter_evidence_json, []),
topic_tags: parseJson(stored.topic_tags_json, []),
formats: parseJson(stored.formats_json, []),
uncertainty_flags: parseJson(stored.uncertainty_flags_json, []),
source: "llm",
prompt_version: stored.prompt_version,
model: stored.model,
run_id: stored.run_id,
input_hash: stored.input_hash
};
channel.qualification_assessment = assessment;
channel.ecosystem_classification = assessment.classification;
channel.ecosystem_reason = assessment.rationale;
channel.ecosystem_classification_source = "llm";
channel.ecosystem_classification_confidence = assessment.confidence;
channel.relevance_score = assessment.relevance_score;
matchedLlmAssessments += 1;
}
console.log("[channel-queues] Qualification evidence", {
ecosystem: appConfig.ecosystem.name,
promptVersion: appConfig.qualification.promptVersion,
channels: channels.length,
matchedFreshLlmAssessments: matchedLlmAssessments,
staleOrMissingLlmAssessments: channels.length - matchedLlmAssessments,
note: "Stale/missing LLM assessments fall back to low-confidence rule-based triage and remain qualification-pending unless the confidence threshold is met."
});
const result = scoreChannelQueues({ channels, referenceSeeds, now });
const sourcePriorityRunId = null;
const { report, queueReports, scoreRows, mdPath, jsonPath } = writeChannelQueueReports({
result, channels, generatedAt, runId, notapply, sourcePriorityRunId, root
});
if (!notapply) {
const persisted = persistQueueRatings(db, {
runId,
generatedAt,
sourcePriorityRunId: report.source_priority_run_id,
channels,
ratings: result.ratings,
scoringVersion: appConfig.queueScoring.version,
config: {
qualification_prompt_version: appConfig.qualification.promptVersion,
qualification_threshold: appConfig.qualification.minimumConfidenceToQualify,
breakout_method: "static_growth_potential_v2_evidence_led",
breakout_snapshot_dependent: false,
missingEvidence: "null_not_zero; missing component weights shrink observed mean toward neutral 50",
scoringScope: "raw channel metadata, qualification based on raw metadata and captured video fields only; excludes channel_profiles, vocabulary, concepts, patterns, embeddings and channel_priority_scores",
evidenceCoverage: "computed against all configured component weights; missing components remain visible as null",
supportedStatus: { minimumCoveragePercent: appConfig.queueScoring.minimumCoverageForSupported, minimumQualificationConfidence: appConfig.queueScoring.ranking.minimumConfidenceForSupported },
referenceEligibility: "inferred candidates require qualified core/adjacent classification; explicit seeds with conflicts/pending qualification remain visible but unranked",
ranking: "eligibility tiers before queue-specific scores; uncertainty and qualification evidence persisted",
persistence: "five dedicated queue tables plus legacy compatibility table",
evidence: "per-row evidence_json stores common qualification, source facts, queue inputs, observed component mean, available component weight, components including missing values, references, uncertainty, reason codes, rationale and final score"
}
});
console.log("[channel-queues] Persisted dedicated queue tables", {
runId, channels: channels.length, queues: result.queues.length, ratingRows: result.ratings.length,
perQueueTables: ["channel_queue_learn_from", "channel_queue_breaking_out", "channel_queue_durable_exemplars", "channel_queue_unexplored_niches", "channel_queue_reference_inspiration"],
rowsPersisted: persisted
});
} else {
console.log("[channel-queues] Preview only; no queue ratings were written to SQLite. Re-run with --apply to persist.");
}
console.log("[channel-queues] Qualification diagnostics:", result.diagnostics);
console.log("[channel-queues] Queue summary:", queueReports.map(q => ({ queue: q.key, rated: q.rated_count, unknown: q.unknown_count, top: q.rankings.slice(0, 5).map(row => ({ channel: row.channel_name, score: row.score, confidence: row.confidence })) })));
console.log("[channel-queues] Reports:", { markdown: mdPath, json: jsonPath });
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.