Docs/Skills and code/src/
src/analysis-schema.js
import { getDb } from "./db.js";
const db = getDb();
/*
* Analysis tables deliberately live outside the original acquisition schema.
* They can be rebuilt or replaced without changing raw YouTube observations.
*/
export function ensureAnalysisSchema() {
db.exec(`
CREATE TABLE IF NOT EXISTS discovery_candidates (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ecosystem TEXT NOT NULL,
concept_id INTEGER,
query TEXT NOT NULL,
source_type TEXT NOT NULL,
source_ref TEXT,
evidence_json TEXT,
priority REAL NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'candidate',
created_at TEXT NOT NULL,
searched_at TEXT,
UNIQUE(ecosystem, query),
FOREIGN KEY(concept_id) REFERENCES concepts(id)
);
CREATE TABLE IF NOT EXISTS search_origins (
search_id INTEGER PRIMARY KEY,
ecosystem TEXT NOT NULL,
concept_id INTEGER,
source_type TEXT NOT NULL,
source_ref TEXT,
created_at TEXT NOT NULL,
FOREIGN KEY(search_id) REFERENCES searches(id),
FOREIGN KEY(concept_id) REFERENCES concepts(id)
);
CREATE TABLE IF NOT EXISTS channel_metadata (
channel_url TEXT PRIMARY KEY,
fetched_at TEXT NOT NULL,
channel_id TEXT,
name TEXT,
description TEXT,
subscriber_count INTEGER,
subscriber_count_text TEXT,
subscriber_count_precision TEXT,
video_count INTEGER,
video_count_text TEXT,
video_count_precision TEXT,
total_view_count INTEGER,
total_view_count_text TEXT,
total_view_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_source TEXT,
metadata_raw_json TEXT,
keywords_json TEXT,
thumbnail_url TEXT,
is_verified INTEGER,
is_artist INTEGER,
fetch_error TEXT,
FOREIGN KEY(channel_url) REFERENCES channels(channel_url)
);
CREATE TABLE IF NOT EXISTS channel_profiles (
channel_url TEXT PRIMARY KEY,
ecosystem TEXT NOT NULL,
profile_json TEXT NOT NULL,
method TEXT NOT NULL,
model TEXT,
generated_at TEXT NOT NULL,
FOREIGN KEY(channel_url) REFERENCES channels(channel_url)
);
CREATE TABLE IF NOT EXISTS video_profiles (
video_id TEXT PRIMARY KEY,
ecosystem TEXT NOT NULL,
profile_json TEXT NOT NULL,
method TEXT NOT NULL,
model TEXT,
generated_at TEXT NOT NULL,
FOREIGN KEY(video_id) REFERENCES videos(video_id)
);
CREATE TABLE IF NOT EXISTS concepts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ecosystem TEXT NOT NULL,
canonical_name TEXT NOT NULL,
normalized_name TEXT NOT NULL,
concept_type TEXT NOT NULL,
description TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
UNIQUE(ecosystem, normalized_name, concept_type)
);
CREATE TABLE IF NOT EXISTS concept_aliases (
concept_id INTEGER NOT NULL,
alias TEXT NOT NULL,
normalized_alias TEXT NOT NULL,
source TEXT,
created_at TEXT NOT NULL,
PRIMARY KEY(concept_id, normalized_alias),
FOREIGN KEY(concept_id) REFERENCES concepts(id)
);
CREATE TABLE IF NOT EXISTS channel_concepts (
channel_url TEXT NOT NULL,
concept_id INTEGER NOT NULL,
source TEXT NOT NULL,
confidence REAL,
created_at TEXT NOT NULL,
PRIMARY KEY(channel_url, concept_id, source),
FOREIGN KEY(channel_url) REFERENCES channels(channel_url),
FOREIGN KEY(concept_id) REFERENCES concepts(id)
);
CREATE TABLE IF NOT EXISTS video_concepts (
video_id TEXT NOT NULL,
concept_id INTEGER NOT NULL,
source TEXT NOT NULL,
confidence REAL,
created_at TEXT NOT NULL,
PRIMARY KEY(video_id, concept_id, source),
FOREIGN KEY(video_id) REFERENCES videos(video_id),
FOREIGN KEY(concept_id) REFERENCES concepts(id)
);
CREATE TABLE IF NOT EXISTS patterns (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ecosystem TEXT NOT NULL,
pattern_type TEXT NOT NULL,
canonical_name TEXT NOT NULL,
normalized_name TEXT NOT NULL,
description TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
UNIQUE(ecosystem, pattern_type, normalized_name)
);
CREATE TABLE IF NOT EXISTS video_patterns (
video_id TEXT NOT NULL,
pattern_id INTEGER NOT NULL,
source TEXT NOT NULL,
confidence REAL,
created_at TEXT NOT NULL,
PRIMARY KEY(video_id, pattern_id, source),
FOREIGN KEY(video_id) REFERENCES videos(video_id),
FOREIGN KEY(pattern_id) REFERENCES patterns(id)
);
CREATE TABLE IF NOT EXISTS channel_patterns (
channel_url TEXT NOT NULL,
pattern_id INTEGER NOT NULL,
source TEXT NOT NULL,
confidence REAL,
created_at TEXT NOT NULL,
PRIMARY KEY(channel_url, pattern_id, source),
FOREIGN KEY(channel_url) REFERENCES channels(channel_url),
FOREIGN KEY(pattern_id) REFERENCES patterns(id)
);
CREATE INDEX IF NOT EXISTS idx_concepts_ecosystem
ON concepts(ecosystem);
CREATE INDEX IF NOT EXISTS idx_patterns_ecosystem
ON patterns(ecosystem);
CREATE INDEX IF NOT EXISTS idx_channel_concepts_concept
ON channel_concepts(concept_id);
CREATE INDEX IF NOT EXISTS idx_video_concepts_concept
ON video_concepts(concept_id);
CREATE INDEX IF NOT EXISTS idx_video_patterns_pattern
ON video_patterns(pattern_id);
CREATE INDEX IF NOT EXISTS idx_channel_patterns_pattern
ON channel_patterns(pattern_id);
`);
const addColumn = (table, column, definition) => {
const columns = db.prepare(`PRAGMA table_info(${table})`).all();
if (!columns.some(item => item.name === column)) {
db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
}
};
for (const [column, definition] of [
["subscriber_count_precision", "TEXT"], ["video_count_precision", "TEXT"],
["total_view_count", "INTEGER"], ["total_view_count_text", "TEXT"], ["total_view_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_source", "TEXT"], ["metadata_raw_json", "TEXT"]
]) addColumn("channel_metadata", column, definition);
// Legacy Boolean coercion erased the distinction between unknown and false.
db.exec(`UPDATE channel_metadata SET is_verified=NULL WHERE is_verified=0 AND metadata_source IS NULL`);
db.exec(`UPDATE channel_metadata SET is_artist=NULL WHERE is_artist=0 AND metadata_source IS NULL`);
}
export { 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.