Docs/Skills and code/src/
src/stages/23-mine-channel-catalogue.js
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import fs from "node:fs";
import path from "node:path";
import crypto from "node:crypto";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { getChannel, getChannelVideos, getVideoInfo } from "../youtube-channel.js";
const db = getDb();
const arg = name => process.argv.find(value => value.startsWith(`--${name}=`))?.slice(name.length + 3);
const positiveInt = (name, fallback, min = 1) => {
const raw = arg(name);
if (raw == null) return fallback;
const value = Number(raw);
if (!Number.isFinite(value) || value < min) throw new Error(`--${name} must be a number >= ${min}.`);
return Math.floor(value);
};
const requestedLimit = positiveInt("limit-per-channel", 300);
const limitPerChannel = Math.min(300, requestedLimit);
if (requestedLimit > 300) console.warn(`[catalogue-mine] Hard safety cap is 300 videos/channel; requested ${requestedLimit}, using 300.`);
const limitChannels = positiveInt("limit-channels", 1);
const offsetChannels = positiveInt("offset-channels", 0) - 1;
const channelUrlArg = arg("channel-url")?.trim() || null;
const metadataMode = arg("metadata-mode") ?? "missing";
if (!new Set(["missing", "all"]).has(metadataMode)) throw new Error("--metadata-mode must be missing or all.");
const retryFailed = process.argv.includes("--retry-failed");
const delayMs = Math.max(0, Number(arg("delay-ms") ?? config.stage1_5?.requestDelayMs ?? 250) || 0);
const generatedAt = new Date().toISOString();
const stamp = generatedAt.replaceAll(":", "-").replaceAll(".", "-");
const reportDir = path.join(process.cwd(), "reports");
fs.mkdirSync(reportDir, { recursive: true });
const sleep = ms => new Promise(resolve => setTimeout(resolve, ms));
const safeJson = value => { try { return JSON.stringify(value); } catch { return null; } };
const exists = table => Boolean(db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(table));
// Increase the existing feed-page limit for this run only, while retaining a hard page ceiling.
if (config.stage1_5) config.stage1_5.maxChannelPages = Math.min(40, Math.max(Number(config.stage1_5.maxChannelPages) || 5, Math.ceil(limitPerChannel / 10) + 2));
let selectedChannels = channelUrlArg
? db.prepare(`SELECT channel_url, name FROM channels WHERE channel_url=? OR canonical_url=? OR handle=? LIMIT 1`).all(channelUrlArg, channelUrlArg, channelUrlArg)
: db.prepare(`SELECT channel_url, name FROM channels ORDER BY discovered_at, channel_url LIMIT @limit OFFSET @offset`).all({ limit: limitChannels, offset: offsetChannels });
if (!selectedChannels.length && channelUrlArg) selectedChannels = [{ channel_url: channelUrlArg, name: channelUrlArg }];
selectedChannels.splice(0, selectedChannels.length, ...filterQualifiedRows(db, selectedChannels, "channel", { label: "23-mine-channel-catalogue.js" }));
if (!selectedChannels.length) throw new Error("No channels selected. Check --limit-channels and --offset-channels.");
if (!exists("channel_video_enrichment")) db.exec(`CREATE TABLE channel_video_enrichment (
channel_url TEXT NOT NULL,
video_id TEXT NOT NULL,
status TEXT NOT NULL,
attempts INTEGER NOT NULL DEFAULT 0,
fields_present_json TEXT,
last_attempt_at TEXT NOT NULL,
error TEXT,
source TEXT,
PRIMARY KEY(channel_url, video_id)
)`);
const upsertChannel = db.prepare(`UPDATE channels SET
name=COALESCE(NULLIF(@name,''), name), channel_id=COALESCE(@channel_id, channel_id),
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),
thumbnail_url=COALESCE(@thumbnail_url, thumbnail_url), banner_url=COALESCE(@banner_url, banner_url),
custom_links_json=COALESCE(@custom_links_json, custom_links_json), keywords_json=COALESCE(@keywords_json, keywords_json),
metadata_observed_at=@observed_at, metadata_source=@metadata_source, metadata_raw_json=COALESCE(@metadata_raw_json, metadata_raw_json),
enriched_at=@observed_at, enrichment_error=NULL
WHERE channel_url=@channel_url`);
const insertChannelIfMissing = db.prepare(`INSERT OR IGNORE INTO channels (channel_url, name, discovered_at) VALUES (?, ?, ?)`);
const upsertChannelMetadata = db.prepare(`INSERT INTO channel_metadata (
channel_url, fetched_at, channel_id, name, description, subscriber_count, subscriber_count_text, video_count, video_count_text,
keywords_json, thumbnail_url, is_verified, is_artist, fetch_error, subscriber_count_precision, 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, metadata_source, metadata_raw_json
) VALUES (
@channel_url, @observed_at, @channel_id, @name, @description, @subscriber_count, @subscriber_count_text, @video_count, @video_count_text,
@keywords_json, @thumbnail_url, @is_verified, @is_artist, NULL, @subscriber_count_precision, @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, @metadata_source, @metadata_raw_json
) 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),
video_count=COALESCE(excluded.video_count, channel_metadata.video_count), video_count_text=COALESCE(excluded.video_count_text, channel_metadata.video_count_text),
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), fetch_error=NULL,
subscriber_count_precision=COALESCE(excluded.subscriber_count_precision, channel_metadata.subscriber_count_precision),
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),
metadata_source=excluded.metadata_source, metadata_raw_json=COALESCE(excluded.metadata_raw_json, channel_metadata.metadata_raw_json)`);
const insertChannelSnapshot = db.prepare(`INSERT INTO channel_metric_snapshots (
channel_url, observed_at, source, subscriber_count, subscriber_count_text, subscriber_count_precision,
total_view_count, total_view_count_text, total_view_count_precision, video_count, video_count_text, video_count_precision,
joined_date, joined_date_text, country, raw_metadata_json
) VALUES (
@channel_url, @observed_at, @source, @subscriber_count, @subscriber_count_text, @subscriber_count_precision,
@total_view_count, @total_view_count_text, @total_view_count_precision, @video_count, @video_count_text, @video_count_precision,
@joined_date, @joined_date_text, @country, @raw_metadata_json
)`);
const insertVideo = db.prepare(`INSERT INTO videos (
video_id, title, url, channel_url, channel_name, thumbnail_url, duration, view_count, published_at, discovered_at,
published_at_raw, published_at_text, published_at_precision, duration_seconds, duration_precision,
view_count_text, view_count_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,
@published_at_raw, @published_at_text, @published_at_precision, @duration_seconds, @duration_precision,
@view_count_text, @view_count_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(videos.channel_url, excluded.channel_url), channel_name=COALESCE(excluded.channel_name, videos.channel_name),
thumbnail_url=COALESCE(excluded.thumbnail_url, videos.thumbnail_url), duration=COALESCE(excluded.duration, videos.duration),
duration_seconds=COALESCE(excluded.duration_seconds, videos.duration_seconds),
duration_precision=CASE WHEN excluded.duration_seconds IS NOT NULL THEN excluded.duration_precision ELSE videos.duration_precision END,
view_count=COALESCE(excluded.view_count, videos.view_count),
view_count_text=CASE WHEN excluded.view_count IS NOT NULL THEN COALESCE(excluded.view_count_text, videos.view_count_text) ELSE videos.view_count_text END,
view_count_precision=CASE WHEN excluded.view_count IS NOT NULL THEN COALESCE(excluded.view_count_precision, videos.view_count_precision) ELSE videos.view_count_precision END,
published_at=COALESCE(excluded.published_at, videos.published_at),
published_at_raw=COALESCE(excluded.published_at_raw, videos.published_at_raw),
published_at_text=CASE WHEN excluded.published_at IS NOT NULL THEN COALESCE(excluded.published_at_text, videos.published_at_text) ELSE videos.published_at_text END,
published_at_precision=CASE WHEN excluded.published_at IS NOT NULL THEN COALESCE(excluded.published_at_precision, videos.published_at_precision) ELSE videos.published_at_precision END,
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),
metadata_observed_at=COALESCE(excluded.metadata_observed_at, videos.metadata_observed_at),
metadata_source=CASE WHEN excluded.metadata_source LIKE 'youtubei_get_basic_info%' THEN excluded.metadata_source ELSE COALESCE(videos.metadata_source, excluded.metadata_source) END,
raw_metadata_json=COALESCE(excluded.raw_metadata_json, videos.raw_metadata_json)`);
const upsertLink = db.prepare(`INSERT INTO channel_videos (channel_url, video_id, source, position, discovered_at)
VALUES (@channel_url, @video_id, @source, @position, @discovered_at)
ON CONFLICT(channel_url, video_id, source) DO UPDATE SET position=excluded.position, discovered_at=excluded.discovered_at`);
const existingVideo = db.prepare(`SELECT video_id, description, likes_count, category, video_channel_id FROM videos WHERE video_id=?`);
const existingEnrichment = db.prepare(`SELECT status, attempts FROM channel_video_enrichment WHERE channel_url=? AND video_id=?`);
const saveEnrichment = db.prepare(`INSERT INTO channel_video_enrichment (channel_url, video_id, status, attempts, fields_present_json, last_attempt_at, error, source)
VALUES (@channel_url, @video_id, @status, @attempts, @fields_present_json, @last_attempt_at, @error, @source)
ON CONFLICT(channel_url, video_id) DO UPDATE SET status=excluded.status, attempts=excluded.attempts,
fields_present_json=excluded.fields_present_json, last_attempt_at=excluded.last_attempt_at, error=excluded.error, source=excluded.source`);
const insertVideoSnapshot = db.prepare(`INSERT INTO video_metric_snapshots (
video_id, observed_at, source, view_count, view_count_text, view_count_precision, likes_count,
published_at, published_at_text, published_at_precision, duration_seconds, duration_precision, category, raw_metadata_json
) VALUES (@video_id, @observed_at, @source, @view_count, @view_count_text, @view_count_precision, @likes_count,
@published_at, @published_at_text, @published_at_precision, @duration_seconds, @duration_precision, @category, @raw_metadata_json)`);
const report = { generated_at: generatedAt, limit_per_channel_requested: requestedLimit, limit_per_channel_applied: limitPerChannel, hard_cap: 300, metadata_mode: metadataMode, channels: [], errors: [] };
let channelSuccesses = 0, channelFailures = 0, feedVideoCount = 0, richFetches = 0, richSkips = 0, richFailures = 0, newVideos = 0;
console.log("[catalogue-mine] Plan", { channels: selectedChannels.length, limitPerChannel, metadataMode, retryFailed, delayMs, maxChannelPages: config.stage1_5?.maxChannelPages, safety: "hard cap 300 videos per channel; channel feed is not assumed sorted" });
for (let index = 0; index < selectedChannels.length; index++) {
const selected = selectedChannels[index];
const channelUrl = selected.channel_url;
console.log(`\n[catalogue-mine] Channel ${index + 1}/${selectedChannels.length}: ${selected.name ?? channelUrl}`);
console.log(`[catalogue-mine] URL: ${channelUrl}`);
try {
const channel = await getChannel(channelUrl);
const observedAt = new Date().toISOString();
const canonicalUrl = channel.canonical_url ?? channelUrl;
insertChannelIfMissing.run(channelUrl, channel.name ?? selected.name ?? channelUrl, observedAt);
const channelRow = {
...channel,
channel_url: channelUrl,
observed_at: observedAt,
keywords_json: safeJson(channel.keywords ?? []),
is_verified: channel.is_verified == null ? null : Number(Boolean(channel.is_verified)),
is_artist: channel.is_artist == null ? null : Number(Boolean(channel.is_artist))
};
const saveChannelTx = db.transaction(() => {
upsertChannel.run(channelRow);
upsertChannelMetadata.run(channelRow);
insertChannelSnapshot.run({ ...channelRow, source: "youtubei_channel_catalogue_mine", raw_metadata_json: channel.metadata_raw_json });
});
saveChannelTx();
const fetchedVideos = await getChannelVideos(channel, "", limitPerChannel);
const feedObservedAt = new Date().toISOString();
let addedVideoIds = 0;
const saveFeedTx = db.transaction(() => {
fetchedVideos.forEach((video, videoIndex) => {
const existed = Boolean(existingVideo.get(video.video_id));
const row = {
...video,
channel_url: channelUrl,
channel_name: video.channel_name ?? channel.name ?? selected.name,
discovered_at: feedObservedAt,
published_at_raw: video.published_at_text ?? video.published_at ?? null,
metadata_observed_at: feedObservedAt,
metadata_source: video.metadata_source ?? "youtubei_channel_feed"
};
insertVideo.run(row);
upsertLink.run({ channel_url: channelUrl, video_id: video.video_id, source: "catalogue_mine_unsorted_feed", position: videoIndex + 1, discovered_at: feedObservedAt });
if (!existed) addedVideoIds++;
});
});
saveFeedTx();
feedVideoCount += fetchedVideos.length;
newVideos += addedVideoIds;
console.log("[catalogue-mine] Feed saved", { received: fetchedVideos.length, newVideoIds: addedVideoIds, source: "catalogue_mine_unsorted_feed", sortApplied: fetchedVideos.sort_applied ?? false, sortError: fetchedVideos.sort_error ?? null, firstTitle: fetchedVideos[0]?.title ?? null, lastTitle: fetchedVideos.at(-1)?.title ?? null });
let fetchedRich = 0, skippedRich = 0, failedRich = 0, partialRich = 0;
for (let videoIndex = 0; videoIndex < fetchedVideos.length; videoIndex++) {
const feedVideo = fetchedVideos[videoIndex];
const prior = existingEnrichment.get(channelUrl, feedVideo.video_id);
const stored = existingVideo.get(feedVideo.video_id);
if (metadataMode === "missing" && stored?.description && stored?.likes_count != null && stored?.category && stored?.video_channel_id) {
if (!prior || prior.status !== "complete") saveEnrichment.run({ channel_url: channelUrl, video_id: feedVideo.video_id, status: "already_complete", attempts: prior?.attempts ?? 0, fields_present_json: safeJson(["description", "likes_count", "category", "video_channel_id"]), last_attempt_at: new Date().toISOString(), error: null, source: "existing_database" });
skippedRich++;
richSkips++;
continue;
}
if (metadataMode === "missing" && prior && ["complete", "partial", "already_complete"].includes(prior.status)) { skippedRich++; richSkips++; continue; }
if (!retryFailed && prior?.status === "failed" && metadataMode === "missing") { skippedRich++; richSkips++; continue; }
console.log(`[catalogue-mine] Rich video ${videoIndex + 1}/${fetchedVideos.length}: ${feedVideo.title} (${feedVideo.video_id})`);
try {
const info = await getVideoInfo(feedVideo.video_id);
const observed = new Date().toISOString();
const record = {
...info,
video_id: feedVideo.video_id,
url: info.url ?? feedVideo.url,
channel_url: channelUrl,
channel_name: info.channel_name ?? channel.name ?? selected.name,
discovered_at: feedObservedAt,
published_at_raw: info.published_at_text ?? info.published_at ?? null,
metadata_observed_at: observed,
metadata_source: info.metadata_source ?? "youtubei_get_basic_info"
};
const fieldsPresent = ["description", "likes_count", "category", "video_channel_id", "view_count", "duration_seconds", "published_at"].filter(key => record[key] !== null && record[key] !== undefined && record[key] !== "");
const status = fieldsPresent.length >= 5 && fieldsPresent.includes("view_count") && fieldsPresent.includes("duration_seconds") ? "complete" : "partial";
const attemptCount = (prior?.attempts ?? 0) + 1;
db.transaction(() => {
insertVideo.run(record);
insertVideoSnapshot.run({ ...record, observed_at: observed, source: record.metadata_source });
saveEnrichment.run({ channel_url: channelUrl, video_id: feedVideo.video_id, status, attempts: attemptCount, fields_present_json: safeJson(fieldsPresent), last_attempt_at: observed, error: null, source: record.metadata_source });
})();
fetchedRich++;
richFetches++;
if (status === "partial") partialRich++;
console.log("[catalogue-mine] Rich metadata saved", { videoId: feedVideo.video_id, status, fieldsPresent, views: info.view_count, likes: info.likes_count, durationSeconds: info.duration_seconds, published: info.published_at_text, descriptionChars: info.description?.length ?? 0 });
} catch (error) {
const message = error instanceof Error ? error.message : String(error);
const observed = new Date().toISOString();
saveEnrichment.run({ channel_url: channelUrl, video_id: feedVideo.video_id, status: "failed", attempts: (prior?.attempts ?? 0) + 1, fields_present_json: null, last_attempt_at: observed, error: message, source: "youtubei_get_basic_info" });
failedRich++;
richFailures++;
console.error("[catalogue-mine] Video metadata failed; continuing", { videoId: feedVideo.video_id, title: feedVideo.title, error: message });
}
if (delayMs > 0) await sleep(delayMs);
}
channelSuccesses++;
report.channels.push({ channel_url: channelUrl, channel_name: channel.name ?? selected.name, declared_video_count: channel.video_count, subscriber_count: channel.subscriber_count, feed_videos_received: fetchedVideos.length, new_video_ids: addedVideoIds, rich_metadata_fetched: fetchedRich, rich_metadata_skipped: skippedRich, rich_metadata_partial: partialRich, rich_metadata_failed: failedRich, feed_source: "catalogue_mine_unsorted_feed", sort_applied: fetchedVideos.sort_applied ?? false, sort_error: fetchedVideos.sort_error ?? null });
console.log("[catalogue-mine] Channel complete", report.channels.at(-1));
} catch (error) {
channelFailures++;
const message = error instanceof Error ? error.message : String(error);
report.errors.push({ channel_url: channelUrl, channel_name: selected.name, error: message });
console.error("[catalogue-mine] Channel failed; continuing", { channel: selected.name, channelUrl, error: message });
}
if (delayMs > 0) await sleep(delayMs);
}
report.summary = {
channels_requested: selectedChannels.length,
channels_succeeded: channelSuccesses,
channels_failed: channelFailures,
feed_videos_received: feedVideoCount,
new_video_ids: newVideos,
rich_metadata_fetched: richFetches,
rich_metadata_skipped: richSkips,
rich_metadata_failed: richFailures,
video_snapshot_rows: exists("video_metric_snapshots") ? db.prepare("SELECT COUNT(*) AS n FROM video_metric_snapshots").get().n : null,
channel_snapshot_rows: exists("channel_metric_snapshots") ? db.prepare("SELECT COUNT(*) AS n FROM channel_metric_snapshots").get().n : null,
hard_cap_per_channel: 300,
note: "The channel feed is labelled unsorted unless youtubei.js explicitly confirms a sort. Rich fields may legitimately be unavailable for some videos. Run normalize:temporal, videos:embed, priorities:build, queues:rate, and channel-metrics:audit after a substantial catalogue expansion."
};
const reportPath = path.join(reportDir, `channel-catalogue-mine-${stamp}.json`);
fs.writeFileSync(reportPath, JSON.stringify(report, null, 2));
console.log("\n[catalogue-mine] Final summary", { ...report.summary, reportPath, errors: report.errors.slice(0, 20) });
if (channelFailures || richFailures) process.exitCode = 1;
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.