Docs/Skills and code/src/
src/stages/01-5-enrich.js
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { getChannel, getChannelVideos } from "../youtube-channel.js";
import { ensureAnalysisSchema } from "../analysis-schema.js";
import { recordChannelSnapshot, recordVideoSnapshot } from "../observations.js";
import {
classifyChannel, classifyVideos, saveQualification, saveVideoQualifications
} from "../channel-qualification.js";
const db = getDb();
ensureAnalysisSchema();
const force = process.argv.includes("--force");
const channelArg = process.argv.find(value => value.startsWith("--channel-url="));
const onlyChannelUrl = channelArg ? channelArg.slice("--channel-url=".length) : null;
const channels = db.prepare(`
SELECT channel_url, name, enriched_at FROM channels
WHERE (@channelUrl IS NULL OR channel_url=@channelUrl)
ORDER BY discovered_at
`).all({ channelUrl: onlyChannelUrl });
const updateChannel = db.prepare(`
UPDATE channels SET
channel_id = COALESCE(@channel_id, channel_id),
name = COALESCE(@name, name),
description = COALESCE(@description, description),
subscriber_count = COALESCE(@subscriber_count, subscriber_count),
subscriber_count_text = COALESCE(@subscriber_count_text, subscriber_count_text),
subscriber_count_precision = COALESCE(@subscriber_count_precision, subscriber_count_precision),
video_count = COALESCE(@video_count, video_count),
video_count_text = COALESCE(@video_count_text, video_count_text),
video_count_precision = COALESCE(@video_count_precision, video_count_precision),
total_view_count = COALESCE(@total_view_count, total_view_count),
total_view_count_text = COALESCE(@total_view_count_text, total_view_count_text),
total_view_count_precision = COALESCE(@total_view_count_precision, total_view_count_precision),
joined_date = COALESCE(@joined_date, joined_date),
joined_date_text = COALESCE(@joined_date_text, joined_date_text),
country = COALESCE(@country, country),
canonical_url = COALESCE(@canonical_url, canonical_url),
handle = COALESCE(@handle, handle),
keywords_json = COALESCE(@keywords_json, keywords_json),
thumbnail_url = COALESCE(@thumbnail_url, thumbnail_url),
banner_url = COALESCE(@banner_url, banner_url),
custom_links_json = COALESCE(@custom_links_json, custom_links_json),
is_verified = COALESCE(@is_verified, is_verified),
is_artist = COALESCE(@is_artist, is_artist),
metadata_observed_at = COALESCE(@metadata_observed_at, metadata_observed_at),
metadata_source = COALESCE(@metadata_source, metadata_source),
metadata_raw_json = COALESCE(@metadata_raw_json, metadata_raw_json),
enriched_at = @enriched_at,
enrichment_error = NULL
WHERE channel_url = @channel_url
`);
const saveChannelMetadata = db.prepare(`
INSERT INTO channel_metadata (
channel_url, fetched_at, channel_id, name, description,
subscriber_count, subscriber_count_text, subscriber_count_precision, video_count, video_count_text, video_count_precision,
total_view_count, total_view_count_text, total_view_count_precision, joined_date, joined_date_text,
country, canonical_url, handle, banner_url, custom_links_json,
keywords_json, thumbnail_url, is_verified, is_artist,
metadata_source, metadata_raw_json, fetch_error
) VALUES (
@channel_url, @fetched_at, @channel_id, @name, @description,
@subscriber_count, @subscriber_count_text, @subscriber_count_precision, @video_count, @video_count_text, @video_count_precision,
@total_view_count, @total_view_count_text, @total_view_count_precision, @joined_date, @joined_date_text,
@country, @canonical_url, @handle, @banner_url, @custom_links_json,
@keywords_json, @thumbnail_url, @is_verified, @is_artist,
@metadata_source, @metadata_raw_json, NULL
) ON CONFLICT(channel_url) DO UPDATE SET
fetched_at=excluded.fetched_at,
channel_id=COALESCE(excluded.channel_id, channel_metadata.channel_id),
name=COALESCE(excluded.name, channel_metadata.name),
description=COALESCE(excluded.description, channel_metadata.description),
subscriber_count=COALESCE(excluded.subscriber_count, channel_metadata.subscriber_count),
subscriber_count_text=COALESCE(excluded.subscriber_count_text, channel_metadata.subscriber_count_text),
subscriber_count_precision=COALESCE(excluded.subscriber_count_precision, channel_metadata.subscriber_count_precision),
video_count=COALESCE(excluded.video_count, channel_metadata.video_count),
video_count_text=COALESCE(excluded.video_count_text, channel_metadata.video_count_text),
video_count_precision=COALESCE(excluded.video_count_precision, channel_metadata.video_count_precision),
total_view_count=COALESCE(excluded.total_view_count, channel_metadata.total_view_count),
total_view_count_text=COALESCE(excluded.total_view_count_text, channel_metadata.total_view_count_text),
total_view_count_precision=COALESCE(excluded.total_view_count_precision, channel_metadata.total_view_count_precision),
joined_date=COALESCE(excluded.joined_date, channel_metadata.joined_date),
joined_date_text=COALESCE(excluded.joined_date_text, channel_metadata.joined_date_text),
country=COALESCE(excluded.country, channel_metadata.country),
canonical_url=COALESCE(excluded.canonical_url, channel_metadata.canonical_url),
handle=COALESCE(excluded.handle, channel_metadata.handle),
banner_url=COALESCE(excluded.banner_url, channel_metadata.banner_url),
custom_links_json=COALESCE(excluded.custom_links_json, channel_metadata.custom_links_json),
keywords_json=COALESCE(excluded.keywords_json, channel_metadata.keywords_json),
thumbnail_url=COALESCE(excluded.thumbnail_url, channel_metadata.thumbnail_url),
is_verified=COALESCE(excluded.is_verified, channel_metadata.is_verified),
is_artist=COALESCE(excluded.is_artist, channel_metadata.is_artist),
metadata_source=COALESCE(excluded.metadata_source, channel_metadata.metadata_source),
metadata_raw_json=COALESCE(excluded.metadata_raw_json, channel_metadata.metadata_raw_json),
fetch_error=NULL
`);
const markError = db.prepare(`UPDATE channels SET enrichment_error = @error WHERE channel_url = @channel_url`);
const insertVideo = db.prepare(`
INSERT INTO videos (
video_id, title, url, channel_url, channel_name, thumbnail_url, duration,
view_count, published_at, discovered_at, duration_seconds, duration_precision, view_count_text,
view_count_precision, published_at_text, published_at_precision,
metadata_observed_at, metadata_source, raw_metadata_json, likes_count,
category, description, video_channel_id
) VALUES (
@video_id, @title, @url, @channel_url, @channel_name, @thumbnail_url, @duration,
@view_count, @published_at, @discovered_at, @duration_seconds, @duration_precision, @view_count_text,
@view_count_precision, @published_at_text, @published_at_precision,
@metadata_observed_at, @metadata_source, @raw_metadata_json, @likes_count,
@category, @description, @video_channel_id
) ON CONFLICT(video_id) DO UPDATE SET
title=COALESCE(NULLIF(excluded.title,''), videos.title),
url=COALESCE(excluded.url, videos.url),
channel_url=COALESCE(excluded.channel_url, videos.channel_url),
channel_name=COALESCE(excluded.channel_name, videos.channel_name),
thumbnail_url=COALESCE(excluded.thumbnail_url, videos.thumbnail_url),
duration=CASE
WHEN excluded.duration_precision IN ('rounded_display', 'display_only')
AND videos.duration_precision IN ('exact', 'displayed_seconds') THEN videos.duration
ELSE COALESCE(excluded.duration, videos.duration) END,
published_at=COALESCE(excluded.published_at, videos.published_at),
duration_seconds=CASE
WHEN excluded.duration_seconds IS NULL THEN videos.duration_seconds
WHEN excluded.duration_precision IN ('rounded_display', 'display_only')
AND videos.duration_precision IN ('exact', 'displayed_seconds') THEN videos.duration_seconds
ELSE excluded.duration_seconds END,
duration_precision=CASE
WHEN excluded.duration_seconds IS NULL THEN videos.duration_precision
WHEN excluded.duration_precision IN ('rounded_display', 'display_only')
AND videos.duration_precision IN ('exact', 'displayed_seconds') THEN videos.duration_precision
ELSE COALESCE(excluded.duration_precision, videos.duration_precision) END,
view_count=CASE
WHEN excluded.view_count IS NULL THEN videos.view_count
WHEN excluded.view_count_precision='abbreviated' AND videos.view_count_precision IN ('exact','exact_displayed') THEN videos.view_count
ELSE excluded.view_count END,
view_count_text=CASE
WHEN excluded.view_count IS NULL THEN videos.view_count_text
WHEN excluded.view_count_precision='abbreviated' AND videos.view_count_precision IN ('exact','exact_displayed') THEN videos.view_count_text
ELSE COALESCE(excluded.view_count_text, videos.view_count_text) END,
view_count_precision=CASE
WHEN excluded.view_count IS NULL THEN videos.view_count_precision
WHEN excluded.view_count_precision='abbreviated' AND videos.view_count_precision IN ('exact','exact_displayed') THEN videos.view_count_precision
ELSE COALESCE(excluded.view_count_precision, videos.view_count_precision) END,
published_at_text=CASE WHEN excluded.published_at IS NULL AND videos.published_at IS NOT NULL
THEN videos.published_at_text ELSE COALESCE(excluded.published_at_text, videos.published_at_text) END,
published_at_precision=CASE WHEN excluded.published_at IS NULL AND videos.published_at IS NOT NULL
THEN videos.published_at_precision ELSE COALESCE(excluded.published_at_precision, videos.published_at_precision) END,
metadata_observed_at=COALESCE(excluded.metadata_observed_at, videos.metadata_observed_at),
metadata_source=COALESCE(excluded.metadata_source, videos.metadata_source),
raw_metadata_json=COALESCE(excluded.raw_metadata_json, videos.raw_metadata_json),
likes_count=COALESCE(excluded.likes_count, videos.likes_count),
category=COALESCE(excluded.category, videos.category),
description=COALESCE(excluded.description, videos.description),
video_channel_id=COALESCE(excluded.video_channel_id, videos.video_channel_id)
`);
const insertChannelVideo = db.prepare(`
INSERT OR IGNORE INTO channel_videos (channel_url, video_id, source, position, discovered_at)
VALUES (?, ?, ?, ?, ?)
`);
function saveVideos(channelUrl, videos, source, timestamp) {
videos.forEach((video, index) => {
const record = {
...video, channel_url: channelUrl,
channel_name: video.channel_name ?? null,
discovered_at: timestamp,
metadata_observed_at: timestamp,
metadata_source: video.metadata_source ?? `youtubei_channel_feed:${source}`
};
insertVideo.run(record);
insertChannelVideo.run(channelUrl, video.video_id, source, index + 1, timestamp);
recordVideoSnapshot(record, `youtubei_channel_feed:${source}`, timestamp);
});
}
function channelRecord(channel, timestamp) {
return {
channel_url: channel.channel_url, channel_id: channel.channel_id, name: channel.name,
description: channel.description, subscriber_count: channel.subscriber_count,
subscriber_count_text: channel.subscriber_count_text, subscriber_count_precision: channel.subscriber_count_precision,
video_count: channel.video_count, video_count_text: channel.video_count_text,
video_count_precision: channel.video_count_precision, total_view_count: channel.total_view_count,
total_view_count_text: channel.total_view_count_text, total_view_count_precision: channel.total_view_count_precision,
joined_date: channel.joined_date,
joined_date_text: channel.joined_date_text, country: channel.country,
canonical_url: channel.canonical_url, handle: channel.handle,
keywords_json: JSON.stringify(channel.keywords ?? []), thumbnail_url: channel.thumbnail_url,
banner_url: channel.banner_url, custom_links_json: channel.custom_links_json,
is_verified: channel.is_verified == null ? null : Number(channel.is_verified),
is_artist: channel.is_artist == null ? null : Number(channel.is_artist),
metadata_observed_at: channel.metadata_observed_at, metadata_source: channel.metadata_source,
metadata_raw_json: channel.metadata_raw_json, enriched_at: timestamp
};
}
function sameIds(a, b) {
return a.length === b.length && a.every((video, index) => video.video_id === b[index]?.video_id);
}
let enriched = 0, skipped = 0, failed = 0;
for (const [index, item] of channels.entries()) {
console.log(`\n[enrich] ${index + 1}/${channels.length}: ${item.name}\n[enrich] URL: ${item.channel_url}`);
if (item.enriched_at && !force) { console.log("[enrich] Already enriched; skipping (use --force to refresh)."); skipped++; continue; }
try {
const channel = await getChannel(item.channel_url);
const popular = await getChannelVideos(channel, "Popular", config.stage1_5.popularVideos);
const newest = await getChannelVideos(channel, "Latest", config.stage1_5.newestVideos);
let lists = [];
let popularForClassification = popular;
let newestForClassification = newest;
if (popular.length > 0 && newest.length > 0 && sameIds(popular, newest)) {
console.warn("[enrich] Sort verification failed: Popular and Latest returned identical ordered IDs. Saving once as unsorted; not asserting either sort.");
lists = [{ videos: popular, source: "unsorted" }];
popularForClassification = [];
newestForClassification = [];
} else {
const popularSource = popular.sort_applied ? "popular" : "unsorted";
const newestSource = newest.sort_applied ? "newest" : "unsorted";
lists = [{ videos: popular, source: popularSource }, { videos: newest, source: newestSource }];
if (popularSource === "unsorted" && newestSource === "unsorted") {
const merged = [...popular, ...newest];
const unique = [...new Map(merged.map(video => [video.video_id, video])).values()];
lists = [{ videos: unique, source: "unsorted" }];
popularForClassification = [];
newestForClassification = [];
} else if (popularSource !== "popular") popularForClassification = [];
else if (newestSource !== "newest") newestForClassification = [];
}
const timestamp = new Date().toISOString();
const record = channelRecord(channel, timestamp);
db.transaction(() => {
updateChannel.run(record);
saveChannelMetadata.run({
channel_url: record.channel_url, fetched_at: timestamp, channel_id: record.channel_id,
name: record.name, description: record.description, subscriber_count: record.subscriber_count,
subscriber_count_text: record.subscriber_count_text, subscriber_count_precision: record.subscriber_count_precision,
video_count: record.video_count, video_count_text: record.video_count_text,
video_count_precision: record.video_count_precision, total_view_count: record.total_view_count,
total_view_count_text: record.total_view_count_text, total_view_count_precision: record.total_view_count_precision,
joined_date: record.joined_date,
joined_date_text: record.joined_date_text, country: record.country,
canonical_url: record.canonical_url, handle: record.handle, banner_url: record.banner_url,
custom_links_json: record.custom_links_json, keywords_json: record.keywords_json,
thumbnail_url: record.thumbnail_url, is_verified: record.is_verified, is_artist: record.is_artist,
metadata_source: record.metadata_source, metadata_raw_json: record.metadata_raw_json
});
recordChannelSnapshot(channel, "youtubei_channel_about", timestamp);
for (const list of lists) saveVideos(channel.channel_url, list.videos, list.source, timestamp);
})();
const qualification = classifyChannel(channel, popularForClassification, newestForClassification, config.ecosystem);
saveQualification(db, channel.channel_url, config.ecosystem.name, qualification);
console.log("[enrich] Saved channel metadata + observations:", {
channel: channel.name, subscribers: channel.subscriber_count_text,
totalViews: channel.total_view_count_text, videoCount: channel.video_count_text,
joined: channel.joined_date_text, country: channel.country,
avatar: Boolean(channel.thumbnail_url), banner: Boolean(channel.banner_url),
links: channel.custom_links?.length ?? 0,
feeds: lists.map(list => ({ source: list.source, count: list.videos.length })),
classification: qualification.classification
});
enriched++;
} catch (error) {
failed++;
const message = error instanceof Error ? error.message : String(error);
console.error(`[enrich] FAILED channel=${item.channel_url}: ${message}`, error);
markError.run({ channel_url: item.channel_url, error: message });
}
if (config.stage1_5.requestDelayMs > 0) await new Promise(resolve => setTimeout(resolve, config.stage1_5.requestDelayMs));
}
console.log("\n[enrich] Video qualification pass");
const videos = db.prepare("SELECT video_id, title FROM videos ORDER BY discovered_at").all();
const classifiedVideos = classifyVideos(videos, config.ecosystem);
saveVideoQualifications(db, classifiedVideos, config.ecosystem.name);
console.log("[enrich] Totals:", {
channels: db.prepare("SELECT COUNT(*) AS count FROM channels").get().count,
enrichedChannels: db.prepare("SELECT COUNT(*) AS count FROM channels WHERE enriched_at IS NOT NULL").get().count,
channelVideos: db.prepare("SELECT COUNT(*) AS count FROM channel_videos").get().count,
videos: db.prepare("SELECT COUNT(*) AS count FROM videos").get().count,
videoMetricSnapshots: db.prepare("SELECT COUNT(*) AS count FROM video_metric_snapshots").get().count,
channelMetricSnapshots: db.prepare("SELECT COUNT(*) AS count FROM channel_metric_snapshots").get().count,
channelQualifications: db.prepare("SELECT COUNT(*) AS count FROM channel_qualifications").get().count,
videoQualifications: db.prepare("SELECT COUNT(*) AS count FROM video_qualifications").get().count
});
console.log("[enrich] Run summary:", { enriched, skipped, failed });
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.