`src/analysis/channel-qualification-store.js`

Code119 lines7.0 KBsrc/
import { config } from "../../config.js";

export function ensureChannelQualificationLlmSchema(db) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS channel_qualification_llm_runs (
      run_id TEXT PRIMARY KEY,
      ecosystem_name TEXT NOT NULL,
      prompt_version TEXT NOT NULL,
      model TEXT NOT NULL,
      started_at TEXT NOT NULL,
      finished_at TEXT,
      input_channel_count INTEGER NOT NULL DEFAULT 0,
      requested_channel_count INTEGER NOT NULL DEFAULT 0,
      success_count INTEGER NOT NULL DEFAULT 0,
      failure_count INTEGER NOT NULL DEFAULT 0,
      batch_size INTEGER NOT NULL,
      config_json TEXT NOT NULL,
      status TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS channel_qualification_llm_results (
      run_id TEXT NOT NULL,
      channel_url TEXT NOT NULL,
      input_hash TEXT NOT NULL,
      prompt_version TEXT NOT NULL,
      model TEXT NOT NULL,
      processing_status TEXT NOT NULL,
      classification TEXT,
      relevance_score REAL,
      confidence REAL,
      focus_specificity REAL,
      channel_type TEXT,
      focus_summary TEXT,
      rationale TEXT,
      supporting_evidence_json TEXT NOT NULL DEFAULT '[]',
      counter_evidence_json TEXT NOT NULL DEFAULT '[]',
      topic_tags_json TEXT NOT NULL DEFAULT '[]',
      formats_json TEXT NOT NULL DEFAULT '[]',
      uncertainty_flags_json TEXT NOT NULL DEFAULT '[]',
      raw_response_json TEXT,
      error_text TEXT,
      created_at TEXT NOT NULL,
      PRIMARY KEY (run_id, channel_url),
      FOREIGN KEY (run_id) REFERENCES channel_qualification_llm_runs(run_id),
      FOREIGN KEY (channel_url) REFERENCES channels(channel_url)
    );
    CREATE INDEX IF NOT EXISTS idx_channel_qual_llm_latest ON channel_qualification_llm_results(channel_url, prompt_version, created_at DESC);
    CREATE INDEX IF NOT EXISTS idx_channel_qual_llm_status ON channel_qualification_llm_results(run_id, processing_status);
  `);
}

export function normalizeLlmClassification(value) {
  const label = String(value ?? "").trim().toLowerCase().replace(/[ -]+/g, "_");
  if (label === "core") return "core";
  if (["adjacent", "adjacent_relevant", "related"].includes(label)) return "adjacent";
  if (["irrelevant", "not_relevant", "out_of_scope", "unrelated"].includes(label)) return "irrelevant";
  if (["unknown", "uncertain", "insufficient_evidence", "unclear"].includes(label)) return "unknown";
  return null;
}

export function normalizeLlmResult(raw, expectedUrl) {
  const channelUrl = String(raw?.channel_url ?? raw?.channelUrl ?? "").trim();
  if (channelUrl !== expectedUrl) throw new Error(`LLM result channel_url mismatch; expected ${expectedUrl}, got ${channelUrl || "missing"}`);
  const classification = normalizeLlmClassification(raw?.classification);
  if (!classification) throw new Error(`Invalid classification '${raw?.classification ?? "missing"}' for ${expectedUrl}`);
  const asPercent = value => {
    const n = Number(value);
    return Number.isFinite(n) ? Math.max(0, Math.min(100, n)) : null;
  };
  const asConfidencePercent = value => {
    const n = Number(value);
    if (!Number.isFinite(n)) return null;
    return Math.max(0, Math.min(100, n <= 1 ? n * 100 : n));
  };
  const asList = value => Array.isArray(value) ? value.map(item => typeof item === "string" ? item.trim() : String(item?.name ?? item?.value ?? "").trim()).filter(Boolean).slice(0, 12) : [];
  const confidence = asConfidencePercent(raw?.confidence);
  const relevanceScore = asPercent(raw?.relevance_score ?? raw?.relevanceScore);
  const focusSpecificity = asPercent(raw?.focus_specificity ?? raw?.focusSpecificity);
  if (confidence == null) throw new Error(`Missing or invalid confidence for ${expectedUrl}`);
  return {
    channel_url: expectedUrl, classification,
    relevance_score: relevanceScore,
    confidence: confidence / 100,
    focus_specificity: focusSpecificity,
    channel_type: String(raw?.channel_type ?? raw?.channelType ?? "unknown").slice(0, 100),
    focus_summary: String(raw?.focus_summary ?? raw?.focusSummary ?? "").slice(0, 500),
    rationale: String(raw?.rationale ?? "").slice(0, 1200),
    supporting_evidence: asList(raw?.supporting_evidence ?? raw?.supportingEvidence),
    counter_evidence: asList(raw?.counter_evidence ?? raw?.counterEvidence),
    topic_tags: asList(raw?.topic_tags ?? raw?.topicTags).map(s => s.toLowerCase()).slice(0, 12),
    formats: asList(raw?.formats ?? raw?.editorial_formats ?? raw?.editorialFormats).slice(0, 10),
    uncertainty_flags: asList(raw?.uncertainty_flags ?? raw?.uncertaintyFlags).slice(0, 10)
  };
}

export function qualificationPrompt() {
  const q = config.qualification.classification;
  return `You are classifying channels for the ecosystem named "${config.ecosystem.name}".
Target ecosystem description: ${config.ecosystem.description}
Configured inclusion terms (discovery hints, not sufficient evidence by themselves): ${JSON.stringify(config.ecosystem.includeTerms)}
Configured exclusion terms (possible false-positive areas, not automatic exclusion rules): ${JSON.stringify(config.ecosystem.excludeTerms)}
Use ONLY the supplied channel metadata and representative video titles. Do not browse or invent facts.

Classification definitions and decision criteria:
CORE: ${q.core.definition}\nInclude when: ${q.core.includeWhen.map(x => `- ${x}`).join("\n")}\nDo not infer from: ${q.core.doNotInferFrom.map(x => `- ${x}`).join("\n")}

ADJACENT: ${q.adjacent.definition}\nInclude when: ${q.adjacent.includeWhen.map(x => `- ${x}`).join("\n")}\nDo not infer from: ${q.adjacent.doNotInferFrom.map(x => `- ${x}`).join("\n")}

UNKNOWN: ${q.unknown.definition}\nUse when: ${q.unknown.includeWhen.map(x => `- ${x}`).join("\n")}

IRRELEVANT: ${q.irrelevant.definition}\nUse when: ${q.irrelevant.includeWhen.map(x => `- ${x}`).join("\n")}

Important: channel names and keyword matches are weak evidence on their own. Prefer recurring video subjects plus the channel description. Organizations, publishers, creators, educators, and archives can be core or adjacent depending on their actual content. A channel with sparse or biased titles should usually be unknown, not confidently irrelevant. Confidence means confidence in the classification, not how popular the channel is.

Return valid JSON only, no markdown. Shape:
{"results":[{"channel_url":"exact supplied URL","classification":"core|adjacent|unknown|irrelevant","confidence":0.0,"relevance_score":0,"focus_specificity":0,"channel_type":"independent_creator|organization|media_publisher|archive_or_rights_holder|educator|subject_expert|commercial_brand|other|unknown","focus_summary":"short grounded summary","rationale":"why this label fits the criteria","supporting_evidence":["specific supplied evidence"],"counter_evidence":["contradictions or none"],"topic_tags":["canonical short topics"],"formats":["observable or strongly implied formats"],"uncertainty_flags":["specific uncertainty or none"]}]}

Score confidence from 0 to 1; relevance_score and focus_specificity from 0 to 100. Be conservative. Return exactly one result for every supplied channel URL, and do not change URLs.`;
}
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.