Docs/Skills and code/src/
src/analysis/research-schema.js
import { getDb } from "../db.js";
function addColumn(db, table, column, definition) {
const exists = db.prepare(`PRAGMA table_info(${table})`).all()
.some(item => item.name === column);
if (!exists) db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
}
/** Additive research schema. Existing observations and source values are preserved. */
export function ensureResearchSchema() {
const db = getDb();
db.exec(`
CREATE TABLE IF NOT EXISTS editorial_annotations (
object_type TEXT NOT NULL CHECK (object_type IN ('channel','video')),
object_id TEXT NOT NULL,
important INTEGER NOT NULL DEFAULT 0 CHECK (important IN (0,1)),
editorial_status TEXT NOT NULL DEFAULT 'unknown'
CHECK (editorial_status IN ('unknown','core','adjacent','outside')),
reasons_json TEXT NOT NULL DEFAULT '[]',
note TEXT,
source TEXT NOT NULL DEFAULT 'human',
updated_at TEXT NOT NULL,
PRIMARY KEY(object_type, object_id)
);
CREATE TABLE IF NOT EXISTS video_observations (
video_id TEXT NOT NULL,
channel_url TEXT,
ecosystem TEXT NOT NULL,
source TEXT NOT NULL,
evidence_level TEXT NOT NULL DEFAULT 'title_only',
observation_json TEXT NOT NULL,
confidence REAL,
created_at TEXT NOT NULL,
PRIMARY KEY(video_id, source),
FOREIGN KEY(video_id) REFERENCES videos(video_id),
FOREIGN KEY(channel_url) REFERENCES channels(channel_url)
);
CREATE TABLE IF NOT EXISTS research_findings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ecosystem TEXT NOT NULL,
finding_type TEXT NOT NULL,
claim TEXT NOT NULL,
evidence_json TEXT NOT NULL,
confidence REAL,
status TEXT NOT NULL DEFAULT 'candidate',
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS vocabulary_terms (
term_id INTEGER PRIMARY KEY AUTOINCREMENT,
ecosystem TEXT NOT NULL,
term_type TEXT NOT NULL,
label TEXT NOT NULL,
normalized_label TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'candidate'
CHECK (status IN ('candidate','canonical','alias','rejected')),
canonical_term_id INTEGER,
first_seen_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
FOREIGN KEY(canonical_term_id) REFERENCES vocabulary_terms(term_id),
UNIQUE(ecosystem, term_type, normalized_label)
);
CREATE TABLE IF NOT EXISTS vocabulary_evidence (
evidence_id INTEGER PRIMARY KEY AUTOINCREMENT,
term_id INTEGER NOT NULL,
object_type TEXT NOT NULL,
object_id TEXT NOT NULL,
source TEXT NOT NULL,
raw_label TEXT NOT NULL,
evidence_json TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL,
FOREIGN KEY(term_id) REFERENCES vocabulary_terms(term_id),
UNIQUE(term_id, object_type, object_id, source, raw_label)
);
CREATE TABLE IF NOT EXISTS vocabulary_embeddings (
term_id INTEGER NOT NULL,
model TEXT NOT NULL,
dimensions INTEGER NOT NULL,
vector_json TEXT NOT NULL,
created_at TEXT NOT NULL,
PRIMARY KEY(term_id, model),
FOREIGN KEY(term_id) REFERENCES vocabulary_terms(term_id)
);
CREATE TABLE IF NOT EXISTS vocabulary_relations (
relation_id INTEGER PRIMARY KEY AUTOINCREMENT,
term_a_id INTEGER NOT NULL,
term_b_id INTEGER NOT NULL,
relation_type TEXT NOT NULL,
similarity REAL NOT NULL,
status TEXT NOT NULL DEFAULT 'proposed'
CHECK (status IN ('proposed','accepted','rejected')),
method TEXT NOT NULL,
evidence_json TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
CHECK (term_a_id <> term_b_id),
UNIQUE(term_a_id, term_b_id, relation_type, method),
FOREIGN KEY(term_a_id) REFERENCES vocabulary_terms(term_id),
FOREIGN KEY(term_b_id) REFERENCES vocabulary_terms(term_id)
);
CREATE TABLE IF NOT EXISTS video_feature_values (
video_id TEXT NOT NULL,
term_id INTEGER NOT NULL,
evidence_count INTEGER NOT NULL DEFAULT 1,
confidence REAL,
evidence_json TEXT NOT NULL DEFAULT '{}',
generated_at TEXT NOT NULL,
PRIMARY KEY(video_id, term_id),
FOREIGN KEY(video_id) REFERENCES videos(video_id),
FOREIGN KEY(term_id) REFERENCES vocabulary_terms(term_id)
);
CREATE TABLE IF NOT EXISTS video_embeddings (
video_id TEXT NOT NULL,
model TEXT NOT NULL,
dimensions INTEGER NOT NULL,
vector_json TEXT NOT NULL,
title_hash TEXT NOT NULL,
input_version TEXT NOT NULL DEFAULT 'title-prefix-v1',
created_at TEXT NOT NULL,
PRIMARY KEY(video_id, model),
FOREIGN KEY(video_id) REFERENCES videos(video_id)
);
CREATE TABLE IF NOT EXISTS channel_priority_runs (
run_id TEXT PRIMARY KEY,
embedding_model TEXT NOT NULL,
scoring_version TEXT NOT NULL,
generated_at TEXT NOT NULL,
configuration_json TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS video_niche_clusters (
run_id TEXT NOT NULL,
cluster_no INTEGER NOT NULL,
representative_video_id TEXT NOT NULL,
representative_title TEXT NOT NULL,
member_count INTEGER NOT NULL,
channel_count INTEGER NOT NULL DEFAULT 0,
mean_similarity REAL,
centroid_json TEXT NOT NULL,
diagnostics_json TEXT NOT NULL DEFAULT '{}',
PRIMARY KEY(run_id, cluster_no),
FOREIGN KEY(run_id) REFERENCES channel_priority_runs(run_id)
);
CREATE TABLE IF NOT EXISTS video_niche_memberships (
run_id TEXT NOT NULL,
video_id TEXT NOT NULL,
cluster_no INTEGER NOT NULL,
similarity_to_centroid REAL NOT NULL,
PRIMARY KEY(run_id, video_id),
FOREIGN KEY(run_id, cluster_no) REFERENCES video_niche_clusters(run_id, cluster_no),
FOREIGN KEY(video_id) REFERENCES videos(video_id)
);
CREATE TABLE IF NOT EXISTS channel_priority_scores (
run_id TEXT NOT NULL,
channel_url TEXT NOT NULL,
specialist_score REAL,
breakthrough_score REAL,
novelty_score REAL,
exploration_score REAL NOT NULL,
evidence_quality_score REAL NOT NULL,
priority_score REAL,
confidence REAL NOT NULL,
embedded_video_count INTEGER NOT NULL DEFAULT 0,
sampled_video_count INTEGER NOT NULL DEFAULT 0,
performance_video_count INTEGER NOT NULL DEFAULT 0,
age_adjusted_performance_count INTEGER NOT NULL DEFAULT 0,
dominant_cluster_no INTEGER,
dominant_cluster_share REAL,
mean_title_coherence REAL,
rationale_json TEXT NOT NULL,
generated_at TEXT NOT NULL,
PRIMARY KEY(run_id, channel_url),
FOREIGN KEY(run_id) REFERENCES channel_priority_runs(run_id),
FOREIGN KEY(channel_url) REFERENCES channels(channel_url)
);
CREATE INDEX IF NOT EXISTS idx_editorial_annotations_status
ON editorial_annotations(object_type, editorial_status, important);
CREATE INDEX IF NOT EXISTS idx_video_observations_channel
ON video_observations(channel_url);
CREATE INDEX IF NOT EXISTS idx_research_findings_status
ON research_findings(status, finding_type);
CREATE INDEX IF NOT EXISTS idx_vocabulary_terms_status
ON vocabulary_terms(ecosystem, term_type, status);
CREATE INDEX IF NOT EXISTS idx_vocabulary_evidence_object
ON vocabulary_evidence(object_type, object_id);
CREATE INDEX IF NOT EXISTS idx_vocabulary_relations_review
ON vocabulary_relations(status, similarity DESC);
CREATE INDEX IF NOT EXISTS idx_video_feature_values_term
ON video_feature_values(term_id, video_id);
CREATE INDEX IF NOT EXISTS idx_video_embeddings_model
ON video_embeddings(model, video_id);
CREATE INDEX IF NOT EXISTS idx_priority_scores_rank
ON channel_priority_scores(run_id, priority_score DESC);
CREATE INDEX IF NOT EXISTS idx_niche_memberships_cluster
ON video_niche_memberships(run_id, cluster_no);
`);
// Preserve the source string. The old published_at column remains untouched.
addColumn(db, "videos", "published_at_raw", "TEXT");
addColumn(db, "videos", "published_at_normalized", "TEXT");
addColumn(db, "videos", "published_at_precision", "TEXT");
addColumn(db, "videos", "published_at_normalization_method", "TEXT");
addColumn(db, "videos", "published_at_normalized_at", "TEXT");
db.prepare(`
UPDATE videos
SET published_at_raw = published_at
WHERE published_at_raw IS NULL AND published_at IS NOT NULL
`).run();
}
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.