Docs/Skills and code/src/
src/stages/03-profile-channels.js
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { ensureAnalysisSchema } from "../analysis-schema.js";
import { ensureResearchSchema } from "../analysis/research-schema.js";
import { callLLM, getLLMConfig, parseJsonObject } from "../llm.js";
import { upsertConcept, attachConceptToChannel } from "../concept-store.js";
import { upsertPattern, attachPatternToChannel } from "../pattern-store.js";
const db = getDb();
ensureAnalysisSchema();
ensureResearchSchema();
const force = process.argv.includes("--force");
const limitArg = process.argv.find(value => value.startsWith("--limit="));
const limit = limitArg ? Number(limitArg.split("=")[1]) : null;
const SOURCE = "channel_observation_v3";
const VIDEO_SOURCE = "channel_sample_title_signal_v2";
const videoSignalsEnabled = !process.argv.includes("--no-video-signals");
const channelUrlArg = process.argv.find(value => value.startsWith("--channel-url="));
const requestedChannelUrl = channelUrlArg ? channelUrlArg.slice("--channel-url=".length) : null;
const channels = db.prepare(`
SELECT
c.channel_url,
c.name,
COALESCE(m.description, c.description) AS description,
COALESCE(m.subscriber_count, c.subscriber_count) AS subscriber_count,
COALESCE(m.subscriber_count_text, c.subscriber_count_text) AS subscriber_count_text,
COALESCE(m.video_count, c.video_count) AS video_count,
COALESCE(m.video_count_text, c.video_count_text) AS video_count_text,
COALESCE(m.total_view_count, c.total_view_count) AS total_view_count,
COALESCE(m.total_view_count_text, c.total_view_count_text) AS total_view_count_text,
COALESCE(m.joined_date, c.joined_date) AS joined_date,
COALESCE(m.country, c.country) AS country,
COALESCE(m.keywords_json, c.keywords_json) AS keywords_json,
q.classification,
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,
(SELECT COUNT(*) FROM channel_videos cv WHERE cv.channel_url=c.channel_url AND cv.source='popular') AS popular_sample_count,
(SELECT COUNT(*) FROM channel_videos cv WHERE cv.channel_url=c.channel_url AND cv.source='newest') AS newest_sample_count,
(SELECT COUNT(*) FROM channel_videos cv WHERE cv.channel_url=c.channel_url AND cv.source='unsorted') AS unsorted_sample_count
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
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
WHERE (@channelUrl IS NULL OR c.channel_url = @channelUrl)
ORDER BY
CASE WHEN priority.priority_score IS NULL THEN 1 ELSE 0 END,
priority.priority_score DESC,
CASE WHEN (SELECT COUNT(*) FROM channel_videos cv WHERE cv.channel_url=c.channel_url AND cv.source='popular') > 0
AND (SELECT COUNT(*) FROM channel_videos cv WHERE cv.channel_url=c.channel_url AND cv.source='newest') > 0 THEN 0 ELSE 1 END,
c.discovered_at,
c.name COLLATE NOCASE
`).all({ channelUrl: requestedChannelUrl });
channels.splice(0, channels.length, ...filterQualifiedRows(db, channels, "channel", { label: "03-profile-channels.js" }));
const saveProfile = db.prepare(`
INSERT INTO channel_profiles (
channel_url, ecosystem, profile_json, method, model, generated_at
) VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(channel_url) DO UPDATE SET
ecosystem = excluded.ecosystem,
profile_json = excluded.profile_json,
method = excluded.method,
model = excluded.model,
generated_at = excluded.generated_at
`);
function titlesFor(channelUrl, source) {
return db.prepare(`
SELECT v.video_id, v.title, v.view_count, v.published_at
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
LIMIT 20
`).all(channelUrl, source).map(video => ({ ...video, source }));
}
function asArray(value) {
return Array.isArray(value) ? value : [];
}
function cleanProposal(value) {
return asArray(value)
.map(item => {
if (typeof item === "string") {
return { name: item.trim(), evidence: [], confidence: null };
}
if (!item || typeof item !== "object") return null;
const name = String(item.name ?? "").trim();
if (!name) return null;
return {
name,
evidence: [...new Set(asArray(item.evidence)
.map(x => String(x).trim())
.filter(Boolean))],
confidence: Number.isFinite(Number(item.confidence))
? Math.max(0, Math.min(1, Number(item.confidence)))
: null
};
})
.filter(Boolean);
}
function buildPrompt(channel, popular, newest, unsorted = []) {
return `Analyze this YouTube channel as an ecosystem research object.
The goal is to extract OBSERVATIONS that can later become a controlled vocabulary.
Do not pretend that the ontology is already solved. Preserve specific names and
editorial distinctions rather than merging similar things.
Return ONLY valid JSON:
{
"schema_version": "channel_observation_v3",
"channel_summary": "one cautious sentence",
"vocabulary_proposals": {
"entities": [
{"name": "...", "entity_type": "person|organization|place|product|concept|event|work|other", "evidence": ["description|keywords|popular_titles|newest_titles|unsorted_titles"], "confidence": 0.0}
],
"themes": [
{"name": "...", "evidence": ["description|keywords|popular_titles|newest_titles|unsorted_titles"], "confidence": 0.0}
],
"angles": [
{"name": "...", "evidence": ["popular_titles|newest_titles|unsorted_titles"], "confidence": 0.0}
],
"formats": [
{"name": "...", "evidence": ["popular_titles|newest_titles|unsorted_titles"], "confidence": 0.0}
],
"hooks": [
{"name": "...", "evidence": ["popular_titles|newest_titles|unsorted_titles"], "confidence": 0.0}
],
"promises": [
{"name": "...", "evidence": ["popular_titles|newest_titles|unsorted_titles"], "confidence": 0.0}
],
"packaging": [
{"name": "...", "evidence": ["popular_titles|newest_titles|unsorted_titles"], "confidence": 0.0}
]
},
"limitations": ["important uncertainty or evidence limitation"],
"video_signals": [
{"video_id":"exact supplied id","source":"popular|newest|unsorted","entities":[{"name":"identifiable named thing","type":"person|organization|place|product|concept|event|work|other","confidence":0.0}],"themes":["broad topic"],"angle":"reusable framing 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 or empty","promise_type":"explanation|answer|history|evaluation|instruction|discovery|entertainment|other or empty","promise_text":"short viewer promise or empty","packaging_pattern":"reusable title construction or empty","confidence":0.0}
],
"confidence": 0.0
}
Rules:
- Entities are identifiable things: people, organizations, places, products, concepts, events, and other identifiable named things.
- Themes are broader recurring subject areas, not ordinary title words.
- Angles are recurring editorial ways of approaching a subject. Keep them as reusable ideas, not full video summaries.
- Formats describe content/story forms (for example biography, investigation, ranking, interview).
- Hook mechanisms are reusable categories (contradiction, mystery, question, revelation); hook_text is literal wording.
- promise_type is a reusable category; promise_text is the specific promise suggested by the title.
- Packaging describes reusable title constructions, not exact titles.
- Entity means an identifiable named thing. Do not extract amounts, durations, generic groups, or channel names as entities.
- Prefer fewer high-confidence entities over broad guesses. Empty values are correct when evidence is weak.
- Only report something as recurring when the supplied material gives reasonable support.
- Do not infer facts about the channel that are not supported by the supplied material.
- Evidence labels must be chosen ONLY from the allowed labels above.
- Keep each proposal list to at most 12 items.
- Do not collapse two similar phrases merely because they sound related. Canonicalization is a later stage.
- Confidence is confidence in the extraction, not truth probability.
- For every supplied video, return at most one lightweight video_signal. Only classify what its TITLE supports. Do not infer actual video content.
- Use the exact video_id and source provided below. Do not invent IDs. If a title is ambiguous, leave uncertain fields empty and lower confidence.
- Per video: at most 3 entities, 2 themes; all non-literal categories are short reusable labels, not paragraphs.
- If no video signals were requested, return an empty video_signals array.
CHANNEL
Name: ${channel.name}
Description: ${channel.description ?? ""}
Keywords: ${channel.keywords_json ?? "[]"}
Classification: ${channel.classification ?? "unknown"}
Subscriber count: ${channel.subscriber_count_text ?? channel.subscriber_count ?? "unknown"}
Total channel views: ${channel.total_view_count_text ?? channel.total_view_count ?? "unknown"}
Video count: ${channel.video_count_text ?? channel.video_count ?? "unknown"}
Joined: ${channel.joined_date ?? "unknown"}
Country: ${channel.country ?? "unknown"}
POPULAR TITLES
${popular.map((v, i) => `${i + 1}. [video_id=${v.video_id}; source=popular] ${v.title}`).join("\n") || "(none)"}
NEWEST TITLES
${newest.map((v, i) => `${i + 1}. [video_id=${v.video_id}; source=newest] ${v.title}`).join("\n") || "(none)"}
UNSORTED FEED TITLES (sort was unavailable; do not infer popularity or recency)
${unsorted.map((v, i) => `${i + 1}. [video_id=${v.video_id}; source=unsorted] ${v.title}`).join("\n") || "(none)"}
VIDEO SIGNALS REQUESTED: ${videoSignalsEnabled ? "yes" : "no"}`;
}
function saveExtracted(channel, observation, model) {
const now = new Date().toISOString();
const vocabulary = observation.vocabulary_proposals ?? {};
db.transaction(() => {
if (force) {
db.prepare(`
DELETE FROM channel_concepts
WHERE channel_url = ? AND source IN ('channel_profile_llm', 'channel_observation_v2', ?)
`).run(channel.channel_url, SOURCE);
db.prepare(`
DELETE FROM channel_patterns
WHERE channel_url = ? AND source IN ('channel_profile_llm', 'channel_observation_v2', ?)
`).run(channel.channel_url, SOURCE);
}
saveProfile.run(
channel.channel_url,
config.ecosystem.name,
JSON.stringify(observation),
"llm_observation",
model,
now
);
for (const item of cleanProposal(vocabulary.entities)) {
const concept = upsertConcept({
ecosystem: config.ecosystem.name,
name: item.name,
type: "entity",
source: SOURCE
});
if (concept) {
attachConceptToChannel({
channelUrl: channel.channel_url,
conceptId: concept.id,
source: SOURCE,
confidence: item.confidence
});
}
}
for (const item of cleanProposal(vocabulary.themes)) {
const concept = upsertConcept({
ecosystem: config.ecosystem.name,
name: item.name,
type: "theme",
source: SOURCE
});
if (concept) {
attachConceptToChannel({
channelUrl: channel.channel_url,
conceptId: concept.id,
source: SOURCE,
confidence: item.confidence
});
}
}
for (const item of cleanProposal(vocabulary.angles)) {
const concept = upsertConcept({
ecosystem: config.ecosystem.name,
name: item.name,
type: "angle",
source: SOURCE
});
if (concept) {
attachConceptToChannel({
channelUrl: channel.channel_url,
conceptId: concept.id,
source: SOURCE,
confidence: item.confidence
});
}
}
for (const [type, key] of [
["format", "formats"],
["hook", "hooks"],
["promise", "promises"],
["packaging", "packaging"]
]) {
for (const item of cleanProposal(vocabulary[key])) {
const pattern = upsertPattern({
ecosystem: config.ecosystem.name,
type,
name: item.name
});
if (pattern) {
attachPatternToChannel({
channelUrl: channel.channel_url,
patternId: pattern.id,
source: SOURCE,
confidence: item.confidence
});
}
}
}
const suppliedVideos = [...popular, ...newest, ...unsorted];
const suppliedById = new Map(suppliedVideos.map(video => [video.video_id, video]));
const saveVideoObservation = 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
`);
const videoSignals = videoSignalsEnabled && Array.isArray(observation.video_signals)
? observation.video_signals : [];
for (const signal of videoSignals) {
const supplied = suppliedById.get(String(signal?.video_id ?? ""));
if (!supplied) continue;
const clean = {
schema_version: "video_title_signal_v2",
evidence_level: "title_only",
video_id: supplied.video_id,
title: supplied.title,
source: supplied.source,
entities: asArray(signal.entities).map(value => {
if (typeof value === "string") return { name: value.trim(), type: "other", confidence: null };
if (!value || typeof value !== "object") return null;
const name = String(value.name ?? "").trim();
if (!name || /^\$?\s*\d[\d,.]*(?:\s*[kmb])?(?:\s*(?:trillion|billion|million))?$/i.test(name)) return null;
if (/^(rich people|wealthy people|people|everyone|history|science|education|official channel|documentary|explained)$/i.test(name)) return null;
return { name, type: String(value.type ?? value.entity_type ?? "other").trim(), confidence: Number.isFinite(Number(value.confidence)) ? Math.max(0, Math.min(1, Number(value.confidence))) : null };
}).filter(Boolean).slice(0, 3),
themes: asArray(signal.themes).map(value => String(value ?? "").trim()).filter(Boolean).slice(0, 2),
angle: String(signal.angle ?? "").trim(),
format_family: String(signal.format_family ?? signal.format ?? "").trim(),
hook_mechanism: String(signal.hook_mechanism ?? "").trim(),
hook_text: String(signal.hook_text ?? "").trim(),
promise_type: String(signal.promise_type ?? "").trim(),
promise_text: String(signal.promise_text ?? signal.promise ?? "").trim(),
packaging_pattern: String(signal.packaging_pattern ?? "").trim(),
confidence: Number.isFinite(Number(signal.confidence))
? Math.max(0, Math.min(1, Number(signal.confidence))) : null
};
saveVideoObservation.run(
supplied.video_id, channel.channel_url, config.ecosystem.name,
VIDEO_SOURCE, JSON.stringify(clean), clean.confidence, now
);
}
})();
}
let processed = 0;
let skipped = 0;
let failed = 0;
for (const [index, channel] of channels.entries()) {
if (limit !== null && processed >= limit) break;
const existing = db.prepare(`
SELECT 1 FROM channel_profiles
WHERE channel_url = ? AND ecosystem = ?
`).get(channel.channel_url, config.ecosystem.name);
if (existing && !force) {
skipped++;
continue;
}
console.log(`\n=== Channel observation ${index + 1}/${channels.length} ===`);
console.log(`Channel: ${channel.name}`);
const popular = titlesFor(channel.channel_url, "popular");
const newest = titlesFor(channel.channel_url, "newest");
const unsorted = popular.length || newest.length ? [] : titlesFor(channel.channel_url, "unsorted");
console.log("Evidence supplied:", {
description: Boolean(channel.description),
keywords: Boolean(channel.keywords_json),
subscriberCount: channel.subscriber_count,
totalChannelViews: channel.total_view_count,
videoCount: channel.video_count,
popularTitles: popular.length,
newestTitles: newest.length,
unsortedTitles: unsorted.length
});
if (!popular.length && !newest.length && !unsorted.length) {
console.log("Skipped: no channel video titles available.");
skipped++;
continue;
}
try {
const llm = await callLLM({
system: "You are a careful research data extractor. Return only the requested JSON. Do not hallucinate or silently canonicalize.",
user: buildPrompt(channel, popular, newest, unsorted),
temperature: 0.1,
maxTokens: videoSignalsEnabled ? 5200 : 2200
});
const observation = parseJsonObject(llm.content);
if (observation.schema_version !== "channel_observation_v3") {
throw new Error(`Unexpected schema_version: ${observation.schema_version ?? "missing"}`);
}
saveExtracted(channel, observation, llm.model);
const v = observation.vocabulary_proposals ?? {};
console.log("Observation saved:", {
entities: cleanProposal(v.entities).length,
themes: cleanProposal(v.themes).length,
angles: cleanProposal(v.angles).length,
formats: cleanProposal(v.formats).length,
hooks: cleanProposal(v.hooks).length,
promises: cleanProposal(v.promises).length,
packaging: cleanProposal(v.packaging).length,
videoSignals: videoSignalsEnabled ? (Array.isArray(observation.video_signals) ? observation.video_signals.length : 0) : 0
});
processed++;
} catch (error) {
failed++;
console.error("Channel observation failed:", error instanceof Error ? error.message : error);
}
}
console.log("\n=== Channel observation summary ===");
console.log({ processed, skipped, failed });
try {
console.log("LLM:", getLLMConfig().model);
} catch {
console.log("LLM configuration could not be displayed.");
}
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.