Docs/Skills and code/src/

src/stages/11-build-feature-matrix.js

Code107 lines4.5 KBsrc/

import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { ensureResearchSchema } from "../analysis/research-schema.js";

const db = getDb();
ensureResearchSchema();
const ecosystem = config.ecosystem.name;
const includeCandidates = process.argv.includes("--include-candidates");
const generatedAt = new Date().toISOString();

const evidenceRows = db.prepare(`
  SELECT e.evidence_id, e.object_id AS video_id, e.source, e.raw_label, e.evidence_json,
    t.term_id, t.term_type, t.label, t.status, t.canonical_term_id
  FROM vocabulary_evidence e
  JOIN vocabulary_terms t ON t.term_id=e.term_id
  JOIN videos v ON v.video_id=e.object_id
  WHERE e.object_type='video' AND t.ecosystem=?
  ORDER BY e.object_id, t.term_id
`).all(ecosystem);
evidenceRows.splice(0, evidenceRows.length, ...filterQualifiedRows(db, evidenceRows, "video", { label: "11-build-feature-matrix.js" }));

function resolveFeature(row) {
  if (row.status === "canonical") return row.term_id;
  if (row.status === "alias" && row.canonical_term_id) {
    const target = db.prepare(`SELECT status, term_type FROM vocabulary_terms WHERE term_id=?`).get(row.canonical_term_id);
    if (target?.status === "canonical" && target.term_type === row.term_type) return row.canonical_term_id;
  }
  if (includeCandidates && row.status === "candidate") return row.term_id;
  return null;
}

const aggregates = new Map();
for (const row of evidenceRows) {
  const featureId = resolveFeature(row);
  if (!featureId) continue;
  const key = `${row.video_id}:${featureId}`;
  if (!aggregates.has(key)) aggregates.set(key, {
    videoId: row.video_id, termId: featureId, count: 0, labels: new Set(), sources: new Set(), evidenceIds: []
  });
  const item = aggregates.get(key);
  item.count++;
  item.labels.add(row.raw_label);
  item.sources.add(row.source);
  if (item.evidenceIds.length < 10) item.evidenceIds.push(row.evidence_id);
}

const deleteRows = db.prepare(`
  DELETE FROM video_feature_values
  WHERE term_id IN (SELECT term_id FROM vocabulary_terms WHERE ecosystem=?)
`).run(ecosystem);
const insert = db.prepare(`
  INSERT INTO video_feature_values (
    video_id, term_id, evidence_count, confidence, evidence_json, generated_at
  ) VALUES (?, ?, ?, NULL, ?, ?)
`);
const tx = db.transaction(() => {
  for (const item of aggregates.values()) {
    insert.run(item.videoId, item.termId, item.count, JSON.stringify({
      rawLabels: [...item.labels], sources: [...item.sources], evidenceIds: item.evidenceIds,
      matrixMode: includeCandidates ? "candidate_preview" : "approved_canonical_only"
    }), generatedAt);
  }
});
tx();

const summary = db.prepare(`
  SELECT COUNT(DISTINCT video_id) AS videos_with_features,
    COUNT(*) AS video_feature_pairs,
    COUNT(DISTINCT vf.term_id) AS distinct_features
  FROM video_feature_values vf
  JOIN vocabulary_terms t ON t.term_id=vf.term_id
  WHERE t.ecosystem=?
`).get(ecosystem);
const featureFrequency = db.prepare(`
  SELECT t.term_id, t.term_type, t.label, COUNT(DISTINCT vf.video_id) AS video_count
  FROM video_feature_values vf JOIN vocabulary_terms t ON t.term_id=vf.term_id
  WHERE t.ecosystem=?
  GROUP BY t.term_id ORDER BY video_count DESC, t.term_type, t.label LIMIT 100
`).all(ecosystem);

const here = path.dirname(fileURLToPath(import.meta.url));
const reportDir = path.resolve(here, "../../reports");
fs.mkdirSync(reportDir, { recursive: true });
const stamp = generatedAt.replaceAll(":", "-").replaceAll(".", "-");
const reportPath = path.join(reportDir, `feature-matrix-${stamp}.json`);
fs.writeFileSync(reportPath, JSON.stringify({
  generatedAt, ecosystem, mode: includeCandidates ? "candidate_preview" : "approved_canonical_only",
  summary, featureFrequency,
  caveat: "Sparse interpretable feature matrix. This is not PCA; dimensionality reduction should follow canonicalization and coverage audits."
}, null, 2));

console.log("[feature-matrix] Rebuilt derived feature values", {
  mode: includeCandidates ? "candidate_preview" : "approved_canonical_only",
  previousRowsDeleted: deleteRows.changes,
  evidenceRowsExamined: evidenceRows.length,
  videoFeaturePairsWritten: aggregates.size,
  summary, reportPath,
  note: includeCandidates
    ? "Includes unresolved terms for diagnosis; do not use this preview as final analytical features."
    : "Only approved canonical terms and their aliases contribute features."
});

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.