Docs/Skills and code/src/
src/db.js
import fs from "node:fs";
import path from "node:path";
import Database from "better-sqlite3";
import { config } from "../config.js";
// Lets a child process (e.g. the viewer-triggered transcript sampler) target the same
// database the viewer is browsing, which may differ from config.dbPath.
const dbPath = process.env.ECOSYSTEM_DB_PATH ? path.resolve(process.env.ECOSYSTEM_DB_PATH) : config.dbPath;
fs.mkdirSync(
path.dirname(dbPath),
{
recursive: true
}
);
console.log(
`Database: ${dbPath}`
);
const db =
new Database(dbPath);
db.pragma("journal_mode = WAL");
db.exec(`
CREATE TABLE IF NOT EXISTS searches (
id INTEGER PRIMARY KEY AUTOINCREMENT,
query TEXT NOT NULL UNIQUE,
searched_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS channels (
channel_url TEXT PRIMARY KEY,
name TEXT NOT NULL,
discovered_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS videos (
video_id TEXT PRIMARY KEY,
title TEXT NOT NULL,
url TEXT NOT NULL,
channel_url TEXT,
channel_name TEXT,
thumbnail_url TEXT,
duration TEXT,
view_count INTEGER,
published_at TEXT,
discovered_at TEXT NOT NULL,
FOREIGN KEY(channel_url)
REFERENCES channels(channel_url)
);
CREATE TABLE IF NOT EXISTS video_metric_snapshots (
id INTEGER PRIMARY KEY AUTOINCREMENT,
video_id TEXT NOT NULL,
observed_at TEXT NOT NULL,
source TEXT NOT NULL,
view_count INTEGER,
view_count_text TEXT,
view_count_precision TEXT,
likes_count INTEGER,
published_at TEXT,
published_at_text TEXT,
published_at_precision TEXT,
duration_seconds INTEGER,
duration_precision TEXT,
category TEXT,
raw_metadata_json TEXT,
FOREIGN KEY(video_id) REFERENCES videos(video_id)
);
CREATE TABLE IF NOT EXISTS channel_metric_snapshots (
id INTEGER PRIMARY KEY AUTOINCREMENT,
channel_url TEXT NOT NULL,
observed_at TEXT NOT NULL,
source TEXT NOT NULL,
subscriber_count INTEGER,
subscriber_count_text TEXT,
subscriber_count_precision TEXT,
total_view_count INTEGER,
total_view_count_text TEXT,
total_view_count_precision TEXT,
video_count INTEGER,
video_count_text TEXT,
video_count_precision TEXT,
joined_date TEXT,
joined_date_text TEXT,
country TEXT,
raw_metadata_json TEXT,
FOREIGN KEY(channel_url) REFERENCES channels(channel_url)
);
CREATE INDEX IF NOT EXISTS idx_video_metric_snapshots_video_time
ON video_metric_snapshots(video_id, observed_at);
CREATE INDEX IF NOT EXISTS idx_channel_metric_snapshots_channel_time
ON channel_metric_snapshots(channel_url, observed_at);
CREATE TABLE IF NOT EXISTS video_searches (
video_id TEXT NOT NULL,
search_id INTEGER NOT NULL,
position INTEGER,
PRIMARY KEY(video_id, search_id),
FOREIGN KEY(video_id)
REFERENCES videos(video_id),
FOREIGN KEY(search_id)
REFERENCES searches(id)
);
CREATE TABLE IF NOT EXISTS channel_videos (
channel_url TEXT NOT NULL,
video_id TEXT NOT NULL,
source TEXT NOT NULL,
position INTEGER NOT NULL,
discovered_at TEXT NOT NULL,
PRIMARY KEY(
channel_url,
video_id,
source
),
FOREIGN KEY(channel_url)
REFERENCES channels(channel_url),
FOREIGN KEY(video_id)
REFERENCES videos(video_id)
);
CREATE TABLE IF NOT EXISTS channel_qualifications (
channel_url TEXT PRIMARY KEY,
ecosystem TEXT NOT NULL,
classification TEXT NOT NULL,
score REAL NOT NULL,
reason TEXT,
method TEXT NOT NULL,
reviewed_at TEXT,
updated_at TEXT NOT NULL,
FOREIGN KEY(channel_url)
REFERENCES channels(channel_url)
);
CREATE TABLE IF NOT EXISTS video_qualifications (
video_id TEXT PRIMARY KEY,
ecosystem TEXT NOT NULL,
classification TEXT NOT NULL,
score REAL NOT NULL,
reason TEXT,
method TEXT NOT NULL,
reviewed_at TEXT,
updated_at TEXT NOT NULL,
FOREIGN KEY(video_id)
REFERENCES videos(video_id)
);
`);
function addColumn(
table,
column,
definition
) {
const columns = db
.prepare(`PRAGMA table_info(${table})`)
.all();
const exists = columns.some(
item => item.name === column
);
if (!exists) {
db.exec(`
ALTER TABLE ${table}
ADD COLUMN ${column} ${definition}
`);
}
}
addColumn(
"channels",
"channel_id",
"TEXT"
);
addColumn(
"channels",
"description",
"TEXT"
);
addColumn(
"channels",
"subscriber_count",
"INTEGER"
);
addColumn(
"channels",
"subscriber_count_text",
"TEXT"
);
addColumn(
"channels",
"video_count",
"INTEGER"
);
addColumn(
"channels",
"video_count_text",
"TEXT"
);
addColumn(
"channels",
"keywords_json",
"TEXT"
);
addColumn(
"channels",
"thumbnail_url",
"TEXT"
);
addColumn(
"channels",
"is_verified",
"INTEGER"
);
addColumn(
"channels",
"is_artist",
"INTEGER"
);
addColumn(
"channels",
"enriched_at",
"TEXT"
);
addColumn(
"channels",
"enrichment_error",
"TEXT"
);
for (const [column, definition] of [
["subscriber_count_precision", "TEXT"],
["total_view_count", "INTEGER"],
["total_view_count_text", "TEXT"],
["total_view_count_precision", "TEXT"],
["video_count_precision", "TEXT"],
["joined_date", "TEXT"],
["joined_date_text", "TEXT"],
["country", "TEXT"],
["canonical_url", "TEXT"],
["handle", "TEXT"],
["banner_url", "TEXT"],
["custom_links_json", "TEXT"],
["metadata_observed_at", "TEXT"],
["metadata_source", "TEXT"],
["metadata_raw_json", "TEXT"]
]) addColumn("channels", column, definition);
// Earlier code coerced absent verification/artist flags to false. Without provenance,
// those legacy zeroes are not reliable negative evidence, so restore them to unknown.
db.exec(`UPDATE channels SET is_verified=NULL WHERE is_verified=0 AND metadata_source IS NULL`);
db.exec(`UPDATE channels SET is_artist=NULL WHERE is_artist=0 AND metadata_source IS NULL`);
for (const [column, definition] of [
["duration_seconds", "INTEGER"],
["duration_precision", "TEXT"],
["view_count_text", "TEXT"],
["view_count_precision", "TEXT"],
["published_at_text", "TEXT"],
["published_at_precision", "TEXT"],
["metadata_observed_at", "TEXT"],
["metadata_source", "TEXT"],
["raw_metadata_json", "TEXT"],
["likes_count", "INTEGER"],
["category", "TEXT"],
["description", "TEXT"],
["video_channel_id", "TEXT"]
]) addColumn("videos", column, definition);
// The previous adapter ignored the requested sort mode and stored the same feed
// as both Popular and Latest. Reclassify only identical ordered pairs as unsorted.
const getSourceIds = db.prepare(`
SELECT video_id FROM channel_videos WHERE channel_url=? AND source=? ORDER BY position, video_id
`);
const channelsWithSortLabels = db.prepare(`
SELECT DISTINCT channel_url FROM channel_videos WHERE source IN ('popular', 'newest')
`).all();
const reclassifyDuplicateFeed = db.transaction(channelUrl => {
db.prepare(`
INSERT OR IGNORE INTO channel_videos (channel_url, video_id, source, position, discovered_at)
SELECT channel_url, video_id, 'unsorted', position, discovered_at
FROM channel_videos WHERE channel_url=? AND source='popular'
`).run(channelUrl);
return db.prepare(`DELETE FROM channel_videos WHERE channel_url=? AND source IN ('popular','newest')`).run(channelUrl).changes;
});
let reclassifiedFeeds = 0;
for (const { channel_url: channelUrl } of channelsWithSortLabels) {
const popularIds = getSourceIds.all(channelUrl, "popular").map(row => row.video_id);
const newestIds = getSourceIds.all(channelUrl, "newest").map(row => row.video_id);
if (popularIds.length > 0 && popularIds.length === newestIds.length &&
popularIds.every((videoId, index) => videoId === newestIds[index])) {
reclassifiedFeeds += reclassifyDuplicateFeed(channelUrl);
console.warn(`[db-migration] Reclassified identical Popular/Latest feed as unsorted: ${channelUrl} (${popularIds.length} videos)`);
}
}
if (reclassifiedFeeds > 0) console.warn(`[db-migration] Removed ${reclassifiedFeeds} misleading Popular/Latest relationship rows.`);
export function getDb() {
return db;
}
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.