Docs/Skills and code/src/
src/stages/06-backfill-video-signals.js
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { ensureResearchSchema } from "../analysis/research-schema.js";
import { callLLM, getLLMConfig, parseJsonObject } from "../llm.js";
const db = getDb();
ensureResearchSchema();
const limitArg = process.argv.find(value => value.startsWith("--limit="));
const limit = limitArg ? Math.max(1, Number(limitArg.split("=")[1])) : 5;
const channelArg = process.argv.find(value => value.startsWith("--channel-url="));
const onlyChannel = channelArg ? channelArg.slice("--channel-url=".length) : null;
const force = process.argv.includes("--force");
const SOURCE = "channel_sample_title_signal_v2";
const channels = db.prepare(`
SELECT c.channel_url, c.name,
priority.priority_score AS automatic_priority_score,
priority.specialist_score AS automatic_specialist_score,
priority.breakthrough_score AS automatic_breakthrough_score,
priority.novelty_score AS automatic_novelty_score,
priority.evidence_quality_score AS priority_evidence_quality,
COUNT(DISTINCT cv.video_id) AS sampled_videos,
SUM(CASE WHEN cv.source='popular' THEN 1 ELSE 0 END) AS popular_count,
SUM(CASE WHEN cv.source='newest' THEN 1 ELSE 0 END) AS newest_count
FROM channels c
LEFT JOIN (SELECT run_id FROM channel_priority_runs ORDER BY generated_at DESC LIMIT 1) latest_run ON 1=1
LEFT JOIN channel_priority_scores priority
ON priority.run_id=latest_run.run_id AND priority.channel_url=c.channel_url
LEFT JOIN channel_videos cv ON cv.channel_url=c.channel_url
WHERE (@channelUrl IS NULL OR c.channel_url=@channelUrl)
GROUP BY c.channel_url
HAVING COUNT(DISTINCT cv.video_id) > 0
AND (@force=1 OR EXISTS (
SELECT 1 FROM channel_videos cv2 JOIN videos v2 ON v2.video_id=cv2.video_id
WHERE cv2.channel_url=c.channel_url
AND NOT EXISTS (SELECT 1 FROM video_observations vo WHERE vo.video_id=v2.video_id AND vo.source=@source)
))
ORDER BY
CASE WHEN priority.priority_score IS NULL THEN 1 ELSE 0 END,
priority.priority_score DESC,
CASE WHEN SUM(CASE WHEN cv.source='popular' THEN 1 ELSE 0 END)>0
AND SUM(CASE WHEN cv.source='newest' THEN 1 ELSE 0 END)>0 THEN 0 ELSE 1 END,
c.name COLLATE NOCASE
`).all({ channelUrl: onlyChannel, force: force ? 1 : 0, source: SOURCE }).slice(0, limit);
channels.splice(0, channels.length, ...filterQualifiedRows(db, channels, "channel", { label: "06-backfill-video-signals.js" }));
const save = db.prepare(`
INSERT INTO video_observations (
video_id, channel_url, ecosystem, source, evidence_level,
observation_json, confidence, created_at
) VALUES (?, ?, ?, ?, 'title_only', ?, ?, ?)
ON CONFLICT(video_id, source) DO UPDATE SET
channel_url=excluded.channel_url, ecosystem=excluded.ecosystem,
evidence_level=excluded.evidence_level, observation_json=excluded.observation_json,
confidence=excluded.confidence, created_at=excluded.created_at
`);
function listTitles(channelUrl, source) {
return db.prepare(`
SELECT v.video_id, v.title, v.view_count, v.published_at, cv.source AS sample_source, cv.position
FROM channel_videos cv JOIN videos v ON v.video_id=cv.video_id
WHERE cv.channel_url=? AND cv.source=?
ORDER BY cv.position, v.discovered_at LIMIT 20
`).all(channelUrl, source);
}
function clampConfidence(value) {
if (value == null || value === "") return null;
const number = Number(value);
return Number.isFinite(number) ? Math.max(0, Math.min(1, number)) : null;
}
function cleanText(value, maxLength = 120) {
return String(value ?? "").trim().replace(/\s+/g, " ").slice(0, maxLength);
}
function cleanArray(value, max, maxLength = 100) {
if (!Array.isArray(value)) return [];
const seen = new Set();
const result = [];
for (const item of value) {
const label = cleanText(item && typeof item === "object" ? item.name : item, maxLength);
const key = label.toLocaleLowerCase();
if (!label || seen.has(key)) continue;
seen.add(key);
result.push(label);
if (result.length >= max) break;
}
return result;
}
function cleanEntities(value) {
if (!Array.isArray(value)) return [];
const seen = new Set();
const result = [];
for (const item of value) {
const name = cleanText(item && typeof item === "object" ? item.name : item, 100);
const type = cleanText(item?.type ?? item?.entity_type ?? "other", 30).toLowerCase();
if (!name || /^\$?\s*\d[\d,.]*(?:\s*[kmb])?(?:\s*(?:trillion|billion|million))?$/i.test(name)) continue;
if (/^\d+\s*(?:s|sec|seconds?|mins?|minutes?|hours?|days?)$/i.test(name)) continue;
if (/^(rich people|wealthy people|people|everyone|history|science|education|official channel|documentary|explained)$/i.test(name)) continue;
const key = name.toLocaleLowerCase();
if (seen.has(key)) continue;
seen.add(key);
result.push({ name, type, confidence: clampConfidence(item?.confidence) });
if (result.length >= 3) break;
}
return result;
}
let succeeded = 0;
let failed = 0;
let savedTotal = 0;
for (const [index, channel] of channels.entries()) {
const popular = listTitles(channel.channel_url, "popular");
const newest = listTitles(channel.channel_url, "newest");
const unsorted = popular.length || newest.length ? [] : listTitles(channel.channel_url, "unsorted");
const byId = new Map();
for (const video of [...popular, ...newest, ...unsorted]) {
if (!byId.has(video.video_id)) byId.set(video.video_id, { ...video, sample_sources: [] });
const item = byId.get(video.video_id);
if (!item.sample_sources.includes(video.sample_source)) item.sample_sources.push(video.sample_source);
}
const supplied = [...byId.values()];
if (!supplied.length) {
console.log(`[video-signals-v2] Skip ${channel.name}: no popular/newest/unsorted titles.`);
continue;
}
console.log(`\n[video-signals-v2] ${index + 1}/${channels.length}: ${channel.name}`, {
automaticPriority: channel.automatic_priority_score,
specialist: channel.automatic_specialist_score,
breakthrough: channel.automatic_breakthrough_score,
novelty: channel.automatic_novelty_score,
evidenceQuality: channel.priority_evidence_quality,
videos: supplied.length,
popular: popular.length, newest: newest.length, unsortedFallback: unsorted.length
});
const prompt = `You extract compact, typed observations from YouTube TITLES for a research corpus.
Return ONLY valid JSON with schema_version "video_title_signals_v2":
{"schema_version":"video_title_signals_v2","signals":[{
"video_id":"exact supplied ID",
"entities":[{"name":"identifiable named person, organization, place, product, concept, or event","type":"person|organization|place|product|concept|event|work|other","confidence":0.0}],
"themes":["broad topic, not an entity name"],
"angle":"reusable editorial framing, short phrase or empty",
"format_family":"biography|investigation|explainer|ranking|essay|documentary|interview|tutorial|review|other, or empty",
"hook_mechanism":"question|mystery|contradiction|revelation|stakes|surprise|comparison|challenge|other, or empty",
"hook_text":"short literal wording only when clearly present in title, otherwise empty",
"promise_type":"explanation|answer|history|evaluation|instruction|discovery|entertainment|other, or empty",
"promise_text":"short specific viewer promise inferred from title, or empty",
"packaging_pattern":"reusable title construction, not the whole title, or empty",
"confidence":0.0
}]}
Rules:
- Return one object for each supplied video ID, never invent IDs.
- Title-only evidence. Do not claim you know the video's actual content or quality.
- Entity means a named, identifiable thing. Do NOT extract dollar amounts, durations, generic groups ("rich people"), channel names, or generic words as entities.
- Do not treat a theme or editorial angle as an entity. Prefer fewer high-confidence entities over broad guesses.
- Format, hook_mechanism and promise_type must be reusable category labels, not literal title fragments.
- hook_text and promise_text are the literal/specific expressions; do not put the whole title into them.
- Keep themes to at most 2, entities to at most 3. Other fields must be short and reusable.
- Empty strings/arrays are correct when evidence is weak. No canonicalization or alias merging.
- confidence describes extraction confidence, not whether the title's claims are true.
VIDEOS:
${supplied.map(v => JSON.stringify({ video_id: v.video_id, sources: v.sample_sources, title: v.title })).join("\n")}`;
try {
const result = await callLLM({
system: "Careful structured research extractor. Obey the schema and avoid hallucinations. Return JSON only.",
user: prompt, temperature: 0.05, maxTokens: 6500
});
const parsed = parseJsonObject(result.content);
if (parsed.schema_version !== "video_title_signals_v2" || !Array.isArray(parsed.signals)) {
throw new Error("Expected video_title_signals_v2 with a signals array.");
}
const suppliedById = new Map(supplied.map(video => [video.video_id, video]));
let saved = 0;
const tx = db.transaction(() => {
for (const signal of parsed.signals) {
const video = suppliedById.get(String(signal?.video_id ?? ""));
if (!video) {
console.warn(`[video-signals-v2] Ignored unknown video ID from model: ${signal?.video_id}`);
continue;
}
const observation = {
schema_version: "video_title_signal_v2", evidence_level: "title_only",
video_id: video.video_id, title: video.title, sources: video.sample_sources,
entities: cleanEntities(signal.entities), themes: cleanArray(signal.themes, 2),
angle: cleanText(signal.angle), format_family: cleanText(signal.format_family),
hook_mechanism: cleanText(signal.hook_mechanism), hook_text: cleanText(signal.hook_text),
promise_type: cleanText(signal.promise_type), promise_text: cleanText(signal.promise_text),
packaging_pattern: cleanText(signal.packaging_pattern),
confidence: clampConfidence(signal.confidence), extraction_model: result.model
};
save.run(video.video_id, channel.channel_url, config.ecosystem.name, SOURCE,
JSON.stringify(observation), observation.confidence, new Date().toISOString());
saved++;
}
});
tx();
savedTotal += saved;
succeeded++;
console.log(`[video-signals-v2] Saved ${saved}/${supplied.length} observations using ${result.model}.`);
if (saved < supplied.length) console.warn(`[video-signals-v2] Missing ${supplied.length - saved} observations; inspect the model response before scaling.`);
} catch (error) {
failed++;
console.error(`[video-signals-v2] Failed channel=${channel.name} url=${channel.channel_url}:`, error instanceof Error ? error.message : error);
}
}
console.log("\n[video-signals-v2] Summary", {
selectedChannels: channels.length, succeeded, failed, observationsSaved: savedTotal,
source: SOURCE, llmModel: getLLMConfig().model,
note: "Selection uses the latest automatic channel-priority run when available, then sample completeness. Human annotations are not used to select channels. Existing v1 observations are preserved."
});
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.