Docs/Skills and code/src/
src/stages/01-discover.js
import fs from "node:fs";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { searchVideos } from "../youtube.js";
import { recordVideoSnapshot } from "../observations.js";
const db = getDb();
const configuredSeeds = JSON.parse(fs.readFileSync(config.seedPath, "utf8"));
const seeds = configuredSeeds.length ? configuredSeeds : config.ecosystem.includeTerms;
const insertSearch = db.prepare(`
INSERT INTO searches (query, searched_at) VALUES (?, ?)
ON CONFLICT(query) DO UPDATE SET searched_at = excluded.searched_at
`);
const getSearch = db.prepare(`SELECT id FROM searches WHERE query = ?`);
const insertChannel = db.prepare(`
INSERT INTO channels (channel_url, name, discovered_at) VALUES (?, ?, ?)
ON CONFLICT(channel_url) DO UPDATE SET name = COALESCE(NULLIF(excluded.name, ''), channels.name)
`);
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 insertVideoSearch = db.prepare(`
INSERT OR IGNORE INTO video_searches (video_id, search_id, position) VALUES (?, ?, ?)
`);
const saveResults = db.transaction((searchId, videos) => {
const observedAt = new Date().toISOString();
videos.forEach((video, index) => {
if (video.channel_url && video.channel_name) insertChannel.run(video.channel_url, video.channel_name, observedAt);
const record = {
...video,
channel_url: video.channel_url ?? null,
channel_name: video.channel_name ?? null,
discovered_at: observedAt,
metadata_observed_at: observedAt,
metadata_source: video.metadata_source ?? "ytsearch.js"
};
insertVideo.run(record);
insertVideoSearch.run(video.video_id, searchId, index + 1);
recordVideoSnapshot(record, "ytsearch.js", observedAt);
});
});
for (const query of seeds) {
console.log(`\n[discover] Searching: ${query}`);
insertSearch.run(query, new Date().toISOString());
const search = getSearch.get(query);
if (!search) throw new Error(`Could not create search record for "${query}"`);
let videos;
try {
videos = await searchVideos(query, config.youtube.resultsPerSearch);
} catch (error) {
console.error(`[discover] Search failed: ${query}`, error);
continue;
}
console.log(`[discover] Found ${videos.length} normalized videos.`);
saveResults(search.id, videos);
console.log(`[discover] Saved ${videos.length} videos and metric snapshots.`);
}
console.log("\n[discover] Database totals:", {
searches: db.prepare("SELECT COUNT(*) AS count FROM searches").get().count,
channels: db.prepare("SELECT COUNT(*) AS count FROM channels").get().count,
videos: db.prepare("SELECT COUNT(*) AS count FROM videos").get().count,
relationships: db.prepare("SELECT COUNT(*) AS count FROM video_searches").get().count,
videoMetricSnapshots: db.prepare("SELECT COUNT(*) AS count FROM video_metric_snapshots").get().count
});
console.log("[discover] Complete.");
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.