Docs/Skills and code/src/
src/stages/02-channel-metadata.js
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { getChannel } from "../youtube-channel.js";
import { ensureAnalysisSchema } from "../analysis-schema.js";
import { recordChannelSnapshot } from "../observations.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, channel_id, description, subscriber_count,
video_count, total_view_count, joined_date, country, canonical_url,
keywords_json, thumbnail_url, banner_url
FROM channels
WHERE (@channelUrl IS NULL OR channel_url=@channelUrl)
ORDER BY discovered_at
`).all({ channelUrl: onlyChannelUrl });
channels.splice(0, channels.length, ...filterQualifiedRows(db, channels, "channel", { label: "02-channel-metadata.js" }));
const saveMetadata = 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, keywords_json, thumbnail_url, banner_url,
custom_links_json, 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, @keywords_json, @thumbnail_url, @banner_url,
@custom_links_json, @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),
keywords_json=COALESCE(excluded.keywords_json, channel_metadata.keywords_json),
thumbnail_url=COALESCE(excluded.thumbnail_url, channel_metadata.thumbnail_url),
banner_url=COALESCE(excluded.banner_url, channel_metadata.banner_url),
custom_links_json=COALESCE(excluded.custom_links_json, channel_metadata.custom_links_json),
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 updateChannels = 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)
WHERE channel_url=@channel_url
`);
const saveError = db.prepare(`
INSERT INTO channel_metadata (channel_url, fetched_at, name, fetch_error)
VALUES (?, ?, ?, ?)
ON CONFLICT(channel_url) DO UPDATE SET fetched_at=excluded.fetched_at, fetch_error=excluded.fetch_error
`);
function needsRefresh(row) {
return force || !row.channel_id || !row.description || row.subscriber_count == null ||
row.video_count == null || row.total_view_count == null || !row.joined_date ||
!row.country || !row.canonical_url || !row.keywords_json || !row.thumbnail_url || !row.banner_url;
}
function asRecord(channel, timestamp) {
return {
channel_url: channel.channel_url, fetched_at: timestamp, 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_source: channel.metadata_source, metadata_raw_json: channel.metadata_raw_json,
metadata_observed_at: channel.metadata_observed_at
};
}
let fetched = 0, skipped = 0, failed = 0;
for (const [index, row] of channels.entries()) {
console.log(`\n[metadata] ${index + 1}/${channels.length}: ${row.name}\n[metadata] URL: ${row.channel_url}`);
if (!needsRefresh(row)) { skipped++; console.log("[metadata] Existing metadata appears complete; skipping."); continue; }
try {
const channel = await getChannel(row.channel_url);
const timestamp = new Date().toISOString();
const record = asRecord(channel, timestamp);
db.transaction(() => {
const { metadata_observed_at, ...metadataRecord } = record;
saveMetadata.run(metadataRecord);
const { fetched_at, ...channelRecord } = metadataRecord;
updateChannels.run({ ...channelRecord, metadata_observed_at });
recordChannelSnapshot(channel, "youtubei_channel_about", timestamp);
})();
console.log("[metadata] Saved:", {
subscribers: record.subscriber_count_text, totalViews: record.total_view_count_text,
videos: record.video_count_text, joined: record.joined_date_text, country: record.country,
avatar: Boolean(record.thumbnail_url), banner: Boolean(record.banner_url), links: channel.custom_links?.length ?? 0
});
fetched++;
} catch (error) {
failed++;
const message = error instanceof Error ? error.message : String(error);
console.error(`[metadata] Fetch failed for ${row.channel_url}: ${message}`, error);
saveError.run(row.channel_url, new Date().toISOString(), row.name, message);
}
if (config.stage1_5.requestDelayMs > 0) await new Promise(resolve => setTimeout(resolve, config.stage1_5.requestDelayMs));
}
console.log("\n[metadata] Totals:", {
channels: db.prepare("SELECT COUNT(*) AS count FROM channels").get().count,
metadata: db.prepare("SELECT COUNT(*) AS count FROM channel_metadata").get().count,
withSubscribers: db.prepare("SELECT COUNT(*) AS count FROM channel_metadata WHERE subscriber_count IS NOT NULL").get().count,
withTotalViews: db.prepare("SELECT COUNT(*) AS count FROM channel_metadata WHERE total_view_count IS NOT NULL").get().count,
withJoinedDate: db.prepare("SELECT COUNT(*) AS count FROM channel_metadata WHERE joined_date IS NOT NULL").get().count,
withCountry: db.prepare("SELECT COUNT(*) AS count FROM channel_metadata WHERE country IS NOT NULL").get().count,
withAvatar: db.prepare("SELECT COUNT(*) AS count FROM channel_metadata WHERE thumbnail_url IS NOT NULL").get().count,
channelMetricSnapshots: db.prepare("SELECT COUNT(*) AS count FROM channel_metric_snapshots").get().count
});
console.log("[metadata] Run summary:", { fetched, 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.