Docs/Skills and code/src/
src/stages/33-sample-channel-transcripts.js
import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { spawnSync } from "node:child_process";
import { getDb } from "../db.js";
import { ensureResearchSchema } from "../analysis/research-schema.js";
import { normalizePublishedAt } from "../analysis/temporal.js";
import { fetchOriginalTranscript, formatTranscriptText } from "../transcript-fetch.js";
import { TRANSCRIPT_ROOT, SAMPLE_FILE_PATTERN, channelFolderName, rankAndBucket, seededRandom } from "../transcript-sampling.js";
/**
* Mines a channel's catalogue (stage 23), then saves transcripts of its 2 best, 2 median (random) and
* 2 worst performing videos into transcripts/<channel name>/, each with performance stats and metadata on top.
*
* npm run transcripts:sample -- --channel-url=https://www.youtube.com/@HinExpofficial
*
* Options: --skip-mine --limit-per-channel=300 --per-bucket=2 --seed=N --out-dir=PATH --delay-ms=1500
* Target a non-configured database with ECOSYSTEM_DB_PATH=data/<name>.db (the viewer button does this).
*/
const arg = name => process.argv.find(value => value.startsWith(`--${name}=`))?.slice(name.length + 3);
const channelArg = arg("channel-url")?.trim();
if (!channelArg) throw new Error("--channel-url=<channel url, handle or name> is required.");
const skipMine = process.argv.includes("--skip-mine");
const limitPerChannel = Math.min(300, Math.max(1, Number(arg("limit-per-channel") ?? 300) || 300));
const perBucket = Math.max(1, Number(arg("per-bucket") ?? 2) || 2);
const delayMs = Math.max(0, Number(arg("delay-ms") ?? 1500) || 0);
const seed = arg("seed") != null ? Number(arg("seed")) : Math.floor(Math.random() * 2 ** 32);
const outRoot = arg("out-dir") ? path.resolve(arg("out-dir")) : TRANSCRIPT_ROOT;
const MAX_ATTEMPTS_PER_BUCKET = 8;
const db = getDb();
const sleep = ms => new Promise(resolve => setTimeout(resolve, ms));
const log = (message, extra) => console.log(`[transcripts] ${message}`, ...(extra === undefined ? [] : [extra]));
const findChannel = () => db.prepare(`
SELECT c.channel_url, COALESCE(m.name, c.name) AS name, COALESCE(m.handle, c.handle) AS handle,
COALESCE(m.subscriber_count, c.subscriber_count) AS subscriber_count,
COALESCE(m.video_count, c.video_count) AS declared_videos,
COALESCE(m.total_view_count, c.total_view_count) AS total_views
FROM channels c LEFT JOIN channel_metadata m ON m.channel_url = c.channel_url
WHERE c.channel_url=@a OR c.canonical_url=@a OR c.handle=@a OR lower(c.name)=lower(@a) LIMIT 1`).get({ a: channelArg });
let channel = findChannel();
if (!channel && !/^https?:\/\//i.test(channelArg)) throw new Error(`Channel not found in this database: ${channelArg}`);
if (!skipMine) {
const stage23 = path.join(path.dirname(fileURLToPath(import.meta.url)), "23-mine-channel-catalogue.js");
log(`Step 1/2: mining catalogue (up to ${limitPerChannel} videos with rich metadata). This is the slow part.`);
const mine = spawnSync(process.execPath, [
stage23, `--channel-url=${channel?.channel_url ?? channelArg}`, `--limit-per-channel=${limitPerChannel}`, "--include-irrelevant-channels"
], { stdio: "inherit", env: process.env });
if (mine.error) throw mine.error;
if (mine.status !== 0) log(`Mining exited with status ${mine.status}; continuing with whatever was saved (some per-video metadata fetches may have failed).`);
channel = findChannel();
}
if (!channel) throw new Error(`Channel still not in the database after mining: ${channelArg}`);
// Freshly mined videos only have relative dates ("2w ago"); stage 08 normally converts them, so do it here for this channel.
ensureResearchSchema();
const undated = db.prepare(`
SELECT video_id, published_at, published_at_raw, published_at_text, discovered_at FROM videos
WHERE (channel_url=@u OR video_id IN (SELECT video_id FROM channel_videos WHERE channel_url=@u))
AND published_at_normalized IS NULL
AND COALESCE(NULLIF(published_at_raw,''), NULLIF(published_at,''), NULLIF(published_at_text,'')) IS NOT NULL`).all({ u: channel.channel_url });
const setDate = db.prepare("UPDATE videos SET published_at_raw=COALESCE(NULLIF(published_at_raw,''), ?), published_at_normalized=?, published_at_precision=?, published_at_normalization_method=?, published_at_normalized_at=? WHERE video_id=?");
db.transaction(() => {
for (const row of undated) {
const candidates = [row.published_at_raw, row.published_at, row.published_at_text].filter(x => x != null && String(x).trim() !== "");
const raw = candidates.find(x => normalizePublishedAt(x, row.discovered_at).precision.startsWith("exact_")) ?? candidates[0];
const result = normalizePublishedAt(raw, row.discovered_at);
setDate.run(raw, result.normalized, result.precision, result.method, new Date().toISOString(), row.video_id);
}
})();
if (undated.length) log(`Normalized publish dates for ${undated.length} newly mined videos.`);
const videos = db.prepare(`
SELECT video_id, title, url, view_count, view_count_precision, published_at_text, published_at_normalized, published_at_precision,
duration_seconds, likes_count, category, description
FROM videos WHERE channel_url=@u OR video_id IN (SELECT video_id FROM channel_videos WHERE channel_url=@u)`).all({ u: channel.channel_url });
log(`Step 2/2: ${channel.name} has ${videos.length} saved videos.`);
const { stats, ...buckets } = rankAndBucket(videos, { rng: seededRandom(seed) });
if (stats.eligible < 6) throw new Error(`Only ${stats.eligible} videos have both a view count and a publish date; need at least 6 to sample best/median/worst. Run without --skip-mine.`);
log("Ranking", { ...stats, seed });
const chosen = [];
const failures = [];
const used = new Set();
for (const bucket of ["best", "median", "worst"]) {
let got = 0, attempts = 0;
for (const video of buckets[bucket]) {
if (got >= perBucket || attempts >= MAX_ATTEMPTS_PER_BUCKET) break;
if (used.has(video.video_id)) continue;
attempts++;
try {
log(`${bucket} ${got + 1}/${perBucket}: ${video.title} (${video.video_id}, rank ${video.rank}/${video.rank_total})`);
const transcript = await fetchOriginalTranscript(video.video_id);
chosen.push({ bucket, index: ++got, video, transcript });
used.add(video.video_id);
} catch (error) {
failures.push({ bucket, video_id: video.video_id, title: video.title, error: error.message });
log(` no transcript, trying the next ${bucket} candidate: ${error.message}`);
}
if (delayMs) await sleep(delayMs);
}
if (got < perBucket) log(`WARNING: only ${got}/${perBucket} ${bucket} transcripts could be fetched.`);
}
if (!chosen.length) throw new Error("No transcripts could be fetched for any sampled video.");
const outDir = path.join(outRoot, channelFolderName(channel.name));
fs.mkdirSync(outDir, { recursive: true });
for (const file of fs.readdirSync(outDir)) if (SAMPLE_FILE_PATTERN.test(file)) fs.rmSync(path.join(outDir, file));
const n = (value, digits = 0) => value == null ? "n/a" : Number(value).toLocaleString("en-US", { maximumFractionDigits: digits });
const mins = seconds => seconds == null ? "n/a" : `${Math.floor(seconds / 60)}m ${String(seconds % 60).padStart(2, "0")}s`;
const written = [];
for (const { bucket, index, video: v, transcript: t } of chosen) {
const header = [
"=== VIDEO ===",
`Title: ${v.title}`,
`URL: ${v.url ?? `https://www.youtube.com/watch?v=${v.video_id}`}`,
`Video ID: ${v.video_id}`,
`Channel: ${channel.name} (${channel.handle ?? channel.channel_url})`,
`Published: ${v.published_at_text ?? "n/a"} (about ${v.published_at_normalized?.slice(0, 10) ?? "n/a"}, precision: ${v.published_at_precision ?? "unknown"}; age ${n(v.age_days)} days)`,
`Duration: ${mins(v.duration_seconds)}`,
`Category: ${v.category ?? "n/a"}`,
`Likes: ${n(v.likes_count)}`,
`Description: ${v.description ? v.description.replace(/\s+/g, " ").trim().slice(0, 1000) : "n/a (not captured)"}`,
"",
"=== PERFORMANCE ===",
`Sample bucket: ${bucket.toUpperCase()} #${index}`,
`Views: ${n(v.view_count)} (${v.view_count_precision ?? "precision unknown"})`,
`Views per day: ${n(v.views_per_day, 1)} (age floored at ${stats.age_floor_days} days)`,
`Rank by views/day: ${v.rank} of ${v.rank_total} videos (top ${v.percentile_from_top}% from the top; 0% = best)`,
`vs channel median views (${n(stats.median_views)}): ${n(v.vs_median_views, 2)}x`,
`vs channel median views/day (${n(stats.median_views_per_day, 1)}): ${n(v.vs_median_views_per_day, 2)}x`,
`Ranking pool: ${stats.eligible} videos with views and a publish date at least ${stats.min_age_days} days old${stats.used_fallback_pool ? " (too few mature videos, so younger ones were included)" : ""}`,
"",
"=== CHANNEL ===",
`Subscribers: ${n(v.subscriber_count ?? channel.subscriber_count)}`,
`Declared videos: ${n(channel.declared_videos)}, saved videos: ${videos.length}`,
`Mean views of ranked videos: ${n(stats.mean_views)}`,
"",
"=== TRANSCRIPT ===",
`Language: ${t.languageName} [${t.lang}], original language (detected via ${t.detection}; tracks on video: ${t.availableTracks.join(", ")})`,
`Fetched with: ${t.library}, ${t.segments.length} caption segments`,
"",
""
].join("\n");
const file = path.join(outDir, `${bucket}-${index}_${v.video_id}.txt`);
fs.writeFileSync(file, header + formatTranscriptText(t.segments) + "\n", "utf8");
written.push({ file: path.basename(file), bucket, video_id: v.video_id, title: v.title, views: v.view_count, rank: v.rank, language: t.lang });
}
fs.writeFileSync(path.join(outDir, "_index.json"), JSON.stringify({
generated_at: new Date().toISOString(), channel, seed, ranking: stats, files: written, failures
}, null, 2), "utf8");
log("Done", { outDir, files: written.length, failures: failures.length });
for (const w of written) console.log(` ${w.file} [${w.language}] ${w.views.toLocaleString("en-US")} views, rank ${w.rank}`);
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.