Docs/Skills and code/src/
src/analysis/queue-table-store.js
What the file says about itself
Dedicated, auditable persistence for each of the five channel queues.
/** Dedicated, auditable persistence for each of the five channel queues. */
export const QUEUE_TABLES = Object.freeze({
learn_from: "channel_queue_learn_from",
breaking_out: "channel_queue_breaking_out",
durable_exemplars: "channel_queue_durable_exemplars",
unexplored_niches: "channel_queue_unexplored_niches",
reference_inspiration: "channel_queue_reference_inspiration"
});
const QUEUE_KEYS = Object.keys(QUEUE_TABLES);
const hasTable = (db, name) => Boolean(db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(name));
function ensureColumn(db, table, column, definition) {
const exists = db.prepare(`PRAGMA table_info(${table})`).all().some(row => row.name === column);
if (!exists) db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
}
const parseJson = (value, fallback) => {
if (value == null || value === "") return fallback;
try { return JSON.parse(value) ?? fallback; } catch { return fallback; }
};
export function ensureQueueTables(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS channel_queue_runs (
run_id TEXT PRIMARY KEY,
scoring_version TEXT NOT NULL,
generated_at TEXT NOT NULL,
source_priority_run_id TEXT,
config_json TEXT NOT NULL,
channel_count INTEGER NOT NULL,
queue_count INTEGER NOT NULL,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS channel_queue_scores (
run_id TEXT NOT NULL,
channel_url TEXT NOT NULL,
queue_key TEXT NOT NULL,
score REAL,
confidence REAL NOT NULL,
coverage REAL NOT NULL,
sample_size INTEGER NOT NULL,
status TEXT NOT NULL,
rank INTEGER,
reason_codes_json TEXT NOT NULL,
evidence_refs_json TEXT NOT NULL,
components_json TEXT NOT NULL,
rationale TEXT NOT NULL,
generated_at TEXT NOT NULL,
PRIMARY KEY(run_id, channel_url, queue_key),
FOREIGN KEY(run_id) REFERENCES channel_queue_runs(run_id),
FOREIGN KEY(channel_url) REFERENCES channels(channel_url)
);
CREATE INDEX IF NOT EXISTS idx_channel_queue_scores_rank ON channel_queue_scores(run_id, queue_key, rank);
CREATE INDEX IF NOT EXISTS idx_channel_queue_scores_key_score ON channel_queue_scores(queue_key, score DESC);
`);
for (const [queueKey, tableName] of Object.entries(QUEUE_TABLES)) {
db.exec(`
CREATE TABLE IF NOT EXISTS ${tableName} (
run_id TEXT NOT NULL,
channel_url TEXT NOT NULL,
channel_name TEXT NOT NULL,
final_channel_score REAL,
confidence REAL NOT NULL,
coverage REAL NOT NULL,
sample_size INTEGER NOT NULL,
status TEXT NOT NULL,
rank INTEGER,
eligibility TEXT NOT NULL DEFAULT 'qualification_pending',
qualification_classification TEXT NOT NULL DEFAULT 'unknown',
qualification_confidence REAL NOT NULL DEFAULT 0,
uncertainty_json TEXT NOT NULL DEFAULT '[]',
reason_codes_json TEXT NOT NULL,
evidence_refs_json TEXT NOT NULL,
components_json TEXT NOT NULL,
evidence_json TEXT NOT NULL,
rationale TEXT NOT NULL,
generated_at TEXT NOT NULL,
PRIMARY KEY(run_id, channel_url),
FOREIGN KEY(run_id) REFERENCES channel_queue_runs(run_id),
FOREIGN KEY(channel_url) REFERENCES channels(channel_url)
);
CREATE INDEX IF NOT EXISTS idx_${tableName}_rank ON ${tableName}(run_id, rank);
CREATE INDEX IF NOT EXISTS idx_${tableName}_score ON ${tableName}(final_channel_score DESC);
CREATE INDEX IF NOT EXISTS idx_${tableName}_channel ON ${tableName}(channel_url, generated_at DESC);
`);
// Additive migration for existing dedicated tables; historical rows remain intact.
ensureColumn(db, tableName, "eligibility", "TEXT NOT NULL DEFAULT 'qualification_pending'");
ensureColumn(db, tableName, "qualification_classification", "TEXT NOT NULL DEFAULT 'unknown'");
ensureColumn(db, tableName, "qualification_confidence", "REAL NOT NULL DEFAULT 0");
ensureColumn(db, tableName, "uncertainty_json", "TEXT NOT NULL DEFAULT '[]'");
db.prepare(`UPDATE ${tableName}
SET eligibility='legacy_unqualified', qualification_classification='unknown', qualification_confidence=0,
uncertainty_json='["legacy_row_no_llm_qualification_evidence"]'
WHERE eligibility='qualification_pending' AND evidence_json NOT LIKE '%"qualification"%'`).run();
}
}
export function buildEvidencePayload(channel, rating) {
const channelFacts = {
channel_url: channel.channel_url,
channel_name: channel.name ?? null,
handle: channel.handle ?? null,
channel_id: channel.channel_id ?? null,
description: channel.description ?? null,
subscriber_count: channel.subscriber_count ?? null,
subscriber_count_text: channel.subscriber_count_text ?? null,
subscriber_count_precision: channel.subscriber_count_precision ?? null,
video_count: channel.video_count ?? null,
video_count_precision: channel.video_count_precision ?? null,
video_count_text: channel.video_count_text ?? null,
total_view_count: channel.total_view_count ?? null,
total_view_count_text: channel.total_view_count_text ?? null,
total_view_count_precision: channel.total_view_count_precision ?? null,
joined_date: channel.joined_date ?? null,
country: channel.country ?? null,
metadata_source: channel.metadata_source ?? null,
ecosystem_classification: channel.ecosystem_classification ?? "unknown",
ecosystem_reason: channel.ecosystem_reason ?? null,
ecosystem_classification_source: channel.ecosystem_classification_source ?? "rule_based",
qualification_assessment: channel.qualification_assessment ?? null,
sampled_video_count: channel.sampled_video_count ?? (channel.videos ?? []).length
};
const queueSpecificInputs = {
learn_from: {
focus_specificity: rating.evidence?.qualification?.focus_specificity ?? null,
median_video_views: rating.evidence?.median_video_views ?? null,
repeatable_video_count: rating.evidence?.repeatable_video_count ?? null,
video_sample_size: rating.evidence?.video_sample_size ?? channel.sampled_video_count ?? (channel.videos ?? []).length,
metadata_coverage_percent: rating.evidence?.metadata_coverage_percent ?? null,
top_video_ids: channel.top_video_ids ?? []
},
breaking_out: rating.evidence ?? { method: "static_catalogue_growth_potential_v1", snapshot_dependent: false },
durable_exemplars: { channel_age_basis: channel.joined_date ?? null, lifetime_views_basis: channel.total_view_count ?? null, declared_catalogue_basis: channel.video_count ?? null },
unexplored_niches: {
topic_tags: rating.evidence?.topic_tags ?? [],
corpus_topic_rarity: rating.evidence?.corpus_topic_rarity ?? null,
focus_specificity: rating.evidence?.qualification?.focus_specificity ?? null,
video_sample_size: rating.evidence?.video_sample_size ?? null
},
reference_inspiration: { explicit_reference_status: rating.status === "explicit_reference", components: rating.components ?? [] }
}[rating.queue_key] ?? {};
return {
evidence_schema_version: "queue-evidence-v3-raw-metadata-only",
queue_key: rating.queue_key,
source_facts: channelFacts,
queue_specific_inputs: queueSpecificInputs,
final_score: rating.score ?? null,
confidence: rating.confidence ?? 0,
coverage: rating.coverage ?? 0,
sample_size: rating.sample_size ?? 0,
status: rating.status ?? "insufficient_evidence",
rank: rating.rank ?? null,
eligibility: rating.eligibility ?? "qualification_pending",
qualification_classification: rating.qualification_classification ?? rating.qualification?.classification ?? "unknown",
qualification_confidence: rating.qualification_confidence ?? (rating.qualification?.confidence ?? 0) * 100,
qualification: rating.qualification ?? rating.evidence?.qualification ?? null,
uncertainty: rating.uncertainty ?? [],
reason_codes: rating.reason_codes ?? [],
evidence_refs: rating.evidence_refs ?? [],
components: rating.components ?? [],
rationale: rating.rationale ?? ""
};
}
export function persistQueueRatings(db, {
runId, generatedAt, sourcePriorityRunId = null, channels, ratings,
config = {}, scoringVersion = "channel-queues-v2"
}) {
ensureQueueTables(db);
const channelByUrl = new Map(channels.map(channel => [channel.channel_url, channel]));
const insertRun = db.prepare(`INSERT INTO channel_queue_runs
(run_id, scoring_version, generated_at, source_priority_run_id, config_json, channel_count, queue_count, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`);
const legacyInsert = db.prepare(`INSERT INTO channel_queue_scores
(run_id, channel_url, queue_key, score, confidence, coverage, sample_size, status, rank, reason_codes_json, evidence_refs_json, components_json, rationale, generated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
const queueInserts = new Map(Object.entries(QUEUE_TABLES).map(([key, table]) => [key, db.prepare(`INSERT INTO ${table}
(run_id, channel_url, channel_name, final_channel_score, confidence, coverage, sample_size, status, rank,
eligibility, qualification_classification, qualification_confidence, uncertainty_json,
reason_codes_json, evidence_refs_json, components_json, evidence_json, rationale, generated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)]));
const tx = db.transaction(() => {
insertRun.run(runId, scoringVersion, generatedAt, sourcePriorityRunId, JSON.stringify(config), channels.length, QUEUE_KEYS.length, generatedAt);
let persisted = 0;
for (const row of ratings) {
const q = row.queue;
const key = q.queue_key;
const channel = channelByUrl.get(row.channel_url) ?? { channel_url: row.channel_url, name: row.channel_url };
const reasonJson = JSON.stringify(q.reason_codes ?? []);
const refsJson = JSON.stringify(q.evidence_refs ?? []);
const componentsJson = JSON.stringify(q.components ?? []);
const evidenceJson = JSON.stringify(buildEvidencePayload(channel, q));
const confidence = q.confidence ?? 0;
const coverage = q.coverage ?? 0;
const sampleSize = q.sample_size ?? 0;
const status = q.status ?? "insufficient_evidence";
legacyInsert.run(runId, row.channel_url, key, q.score ?? null, confidence, coverage, sampleSize, status, q.rank ?? null, reasonJson, refsJson, componentsJson, q.rationale ?? "", generatedAt);
const insert = queueInserts.get(key);
if (!insert) throw new Error(`Unknown queue key '${key}' encountered while persisting ratings`);
insert.run(runId, row.channel_url, channel.name ?? row.channel_url, q.score ?? null, confidence, coverage, sampleSize, status, q.rank ?? null,
q.eligibility ?? "qualification_pending", q.qualification_classification ?? q.qualification?.classification ?? "unknown",
q.qualification_confidence ?? (q.qualification?.confidence ?? 0) * 100, JSON.stringify(q.uncertainty ?? []),
reasonJson, refsJson, componentsJson, evidenceJson, q.rationale ?? "", generatedAt);
persisted += 1;
}
return persisted;
});
return tx();
}
export function migrateLegacyQueueScores(db) {
ensureQueueTables(db);
if (!hasTable(db, "channel_queue_scores")) return { sourceRows: 0, migratedRows: 0, byQueue: {} };
const rows = db.prepare(`SELECT s.*,
COALESCE(m.name, c.name, s.channel_url) AS channel_name
FROM channel_queue_scores s
LEFT JOIN channels c ON c.channel_url=s.channel_url
LEFT JOIN channel_metadata m ON m.channel_url=s.channel_url
ORDER BY s.generated_at, s.run_id, s.queue_key, s.channel_url`).all();
const inserts = new Map(Object.entries(QUEUE_TABLES).map(([key, table]) => [key, db.prepare(`INSERT OR IGNORE INTO ${table}
(run_id, channel_url, channel_name, final_channel_score, confidence, coverage, sample_size, status, rank,
eligibility, qualification_classification, qualification_confidence, uncertainty_json,
reason_codes_json, evidence_refs_json, components_json, evidence_json, rationale, generated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)]));
const byQueue = {};
let migratedRows = 0;
const tx = db.transaction(() => {
for (const row of rows) {
const insert = inserts.get(row.queue_key);
if (!insert) continue;
const payload = {
evidence_schema_version: "queue-evidence-v2-migrated",
eligibility: "legacy_unqualified", qualification_classification: "unknown", qualification_confidence: 0,
uncertainty: ["legacy_row_no_llm_qualification_evidence"],
migration_source: "channel_queue_scores",
queue_key: row.queue_key,
final_score: row.score ?? null,
confidence: row.confidence,
coverage: row.coverage,
sample_size: row.sample_size,
status: row.status,
rank: row.rank,
reason_codes: parseJson(row.reason_codes_json, []),
evidence_refs: parseJson(row.evidence_refs_json, []),
components: parseJson(row.components_json, []),
rationale: row.rationale ?? "",
note: "Legacy rows did not retain all raw inputs; this migration preserves every evidence field that existed in the legacy row. New runs store a richer evidence_json payload."
};
const result = insert.run(row.run_id, row.channel_url, row.channel_name ?? row.channel_url, row.score ?? null,
row.confidence ?? 0, row.coverage ?? 0, row.sample_size ?? 0, row.status ?? "unknown", row.rank ?? null,
"legacy_unqualified", "unknown", 0, JSON.stringify(["legacy_row_no_llm_qualification_evidence"]),
row.reason_codes_json ?? "[]", row.evidence_refs_json ?? "[]", row.components_json ?? "[]", JSON.stringify(payload), row.rationale ?? "", row.generated_at);
if (result.changes) {
migratedRows += 1;
byQueue[row.queue_key] = (byQueue[row.queue_key] ?? 0) + 1;
}
}
});
tx();
return { sourceRows: rows.length, migratedRows, byQueue };
}
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.