`src/stages/22-audit-channel-metrics.js`

Code191 lines12.3 KBsrc/
import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import fs from "node:fs";
import path from "node:path";
import crypto from "node:crypto";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { calculateChannelMetrics, csvCell } from "../analysis/channel-metric-features.js";

const db = getDb();
const root = process.cwd();
const generatedAt = new Date().toISOString();
const runId = `${generatedAt}#${crypto.randomUUID().slice(0, 8)}`;
const stamp = generatedAt.replaceAll(":", "-").replaceAll(".", "-");
const reportDir = path.join(root, "reports");
fs.mkdirSync(reportDir, { recursive: true });

console.log("[channel-metrics] Starting read-and-report audit", { generatedAt, runId, database: path.resolve(config.dbPath) });

db.exec(`
  CREATE TABLE IF NOT EXISTS channel_metrics_audit_runs (
    run_id TEXT PRIMARY KEY,
    generated_at TEXT NOT NULL,
    metrics_version TEXT NOT NULL,
    channel_count INTEGER NOT NULL,
    linked_video_count INTEGER NOT NULL,
    config_json TEXT NOT NULL
  );
  CREATE TABLE IF NOT EXISTS channel_metric_features (
    run_id TEXT NOT NULL,
    channel_url TEXT NOT NULL,
    channel_name TEXT,
    ecosystem_classification TEXT,
    subscriber_count INTEGER,
    subscriber_count_precision TEXT,
    subscriber_band TEXT,
    declared_video_count INTEGER,
    declared_video_count_precision TEXT,
    catalogue_band TEXT,
    total_view_count INTEGER,
    total_view_count_precision TEXT,
    joined_date TEXT,
    channel_age_days REAL,
    channel_age_band TEXT,
    subscribers_per_upload REAL,
    lifetime_views_per_upload REAL,
    lifetime_views_per_subscriber REAL,
    declared_uploads_per_year_since_join REAL,
    linked_video_count INTEGER,
    corpus_coverage_ratio REAL,
    evidence_band TEXT,
    video_count_with_views INTEGER,
    video_median_views REAL,
    video_p90_views REAL,
    sampled_view_total REAL,
    top_decile_view_share REAL,
    exact_dated_video_count_for_age_adjusted_views INTEGER,
    median_exact_dated_views_per_day REAL,
    metadata_completeness_percent REAL,
    warnings_json TEXT NOT NULL,
    metrics_json TEXT NOT NULL,
    computed_at TEXT NOT NULL,
    PRIMARY KEY (run_id, channel_url)
  );
  CREATE INDEX IF NOT EXISTS idx_channel_metric_features_subs_upload
    ON channel_metric_features(run_id, subscribers_per_upload DESC);
  CREATE INDEX IF NOT EXISTS idx_channel_metric_features_views_upload
    ON channel_metric_features(run_id, lifetime_views_per_upload DESC);
  CREATE INDEX IF NOT EXISTS idx_channel_metric_features_evidence
    ON channel_metric_features(run_id, evidence_band, linked_video_count DESC);
`);

const channels = db.prepare(`
  SELECT c.*, COALESCE(q.classification, 'unknown') AS ecosystem_classification
  FROM channels c
  LEFT JOIN channel_qualifications q ON q.channel_url = c.channel_url
  ORDER BY c.name COLLATE NOCASE, c.channel_url
`).all();
channels.splice(0, channels.length, ...filterQualifiedRows(db, channels, "channel", { label: "22-audit-channel-metrics.js" }));
const linkedRows = db.prepare(`
  SELECT channel_url, video_id FROM channel_videos
  UNION
  SELECT channel_url, video_id FROM videos WHERE channel_url IS NOT NULL
`).all();
const videos = db.prepare(`SELECT video_id, title, channel_url, view_count, published_at, published_at_normalized, published_at_precision, published_at_text FROM videos`).all();
videos.splice(0, videos.length, ...filterQualifiedRows(db, videos, "video", { label: "22-audit-channel-metrics.js" }));
const videoById = new Map(videos.map(row => [row.video_id, row]));
const idsByChannel = new Map();
for (const row of linkedRows) {
  if (!idsByChannel.has(row.channel_url)) idsByChannel.set(row.channel_url, new Set());
  idsByChannel.get(row.channel_url).add(row.video_id);
}

const features = channels.map(channel => {
  const ids = [...(idsByChannel.get(channel.channel_url) ?? [])];
  const rows = ids.map(id => videoById.get(id)).filter(Boolean);
  return calculateChannelMetrics(channel, rows, { nowMs: Date.parse(generatedAt) });
});
const insertRun = db.prepare(`INSERT INTO channel_metrics_audit_runs (run_id, generated_at, metrics_version, channel_count, linked_video_count, config_json) VALUES (?, ?, ?, ?, ?, ?)`);
const insertFeature = db.prepare(`INSERT INTO channel_metric_features (
  run_id, channel_url, channel_name, ecosystem_classification, subscriber_count, subscriber_count_precision, subscriber_band,
  declared_video_count, declared_video_count_precision, catalogue_band, total_view_count, total_view_count_precision,
  joined_date, channel_age_days, channel_age_band, subscribers_per_upload, lifetime_views_per_upload,
  lifetime_views_per_subscriber, declared_uploads_per_year_since_join, linked_video_count, corpus_coverage_ratio,
  evidence_band, video_count_with_views, video_median_views, video_p90_views, sampled_view_total, top_decile_view_share,
  exact_dated_video_count_for_age_adjusted_views, median_exact_dated_views_per_day, metadata_completeness_percent,
  warnings_json, metrics_json, computed_at
) VALUES (
  @run_id, @channel_url, @channel_name, @ecosystem_classification, @subscriber_count, @subscriber_count_precision, @subscriber_band,
  @declared_video_count, @declared_video_count_precision, @catalogue_band, @total_view_count, @total_view_count_precision,
  @joined_date, @channel_age_days, @channel_age_band, @subscribers_per_upload, @lifetime_views_per_upload,
  @lifetime_views_per_subscriber, @declared_uploads_per_year_since_join, @linked_video_count, @corpus_coverage_ratio,
  @evidence_band, @video_count_with_views, @video_median_views, @video_p90_views, @sampled_view_total, @top_decile_view_share,
  @exact_dated_video_count_for_age_adjusted_views, @median_exact_dated_views_per_day, @metadata_completeness_percent,
  @warnings_json, @metrics_json, @computed_at
)`);
const tx = db.transaction(() => {
  insertRun.run(runId, generatedAt, "channel-metrics-v1", features.length, linkedRows.length, JSON.stringify({ source: "channels + channel_videos + videos", scoring_integration: false }));
  for (const feature of features) {
    insertFeature.run({
      run_id: runId,
      channel_url: feature.channel_url,
      channel_name: feature.channel_name,
      ecosystem_classification: feature.ecosystem_classification,
      subscriber_count: feature.subscriber_count,
      subscriber_count_precision: feature.subscriber_count_precision,
      subscriber_band: feature.subscriber_band,
      declared_video_count: feature.declared_video_count,
      declared_video_count_precision: feature.declared_video_count_precision,
      catalogue_band: feature.catalogue_band,
      total_view_count: feature.total_view_count,
      total_view_count_precision: feature.total_view_count_precision,
      joined_date: feature.joined_date,
      channel_age_days: feature.channel_age_days,
      channel_age_band: feature.channel_age_band,
      subscribers_per_upload: feature.subscribers_per_upload,
      lifetime_views_per_upload: feature.lifetime_views_per_upload,
      lifetime_views_per_subscriber: feature.lifetime_views_per_subscriber,
      declared_uploads_per_year_since_join: feature.declared_uploads_per_year_since_join,
      linked_video_count: feature.linked_video_count,
      corpus_coverage_ratio: feature.corpus_coverage_ratio,
      evidence_band: feature.evidence_band,
      video_count_with_views: feature.video_count_with_views,
      video_median_views: feature.video_median_views,
      video_p90_views: feature.video_p90_views,
      sampled_view_total: feature.sampled_view_total,
      top_decile_view_share: feature.top_decile_view_share,
      exact_dated_video_count_for_age_adjusted_views: feature.exact_dated_video_count_for_age_adjusted_views,
      median_exact_dated_views_per_day: feature.median_exact_dated_views_per_day,
      metadata_completeness_percent: feature.metadata_completeness_percent,
      warnings_json: JSON.stringify(feature.warnings),
      metrics_json: JSON.stringify(feature),
      computed_at: generatedAt
    });
  }
});
tx();

const sortRows = key => [...features].filter(row => row[key] != null).sort((a, b) => b[key] - a[key]);
const top = {
  subscribers_per_upload: sortRows("subscribers_per_upload").slice(0, 25),
  lifetime_views_per_upload: sortRows("lifetime_views_per_upload").slice(0, 25),
  median_exact_dated_views_per_day: sortRows("median_exact_dated_views_per_day").slice(0, 25),
  video_median_views: sortRows("video_median_views").slice(0, 25),
  top_decile_view_share: [...features].filter(row => row.top_decile_view_share != null).sort((a, b) => b.top_decile_view_share - a.top_decile_view_share).slice(0, 25)
};
const summary = {
  generated_at: generatedAt,
  run_id: runId,
  metrics_version: "channel-metrics-v1",
  channel_count: features.length,
  linked_video_relationships: linkedRows.length,
  channels_with_subscriber_per_upload: features.filter(row => row.subscribers_per_upload != null).length,
  channels_with_lifetime_views_per_upload: features.filter(row => row.lifetime_views_per_upload != null).length,
  channels_with_age_adjusted_sample: features.filter(row => row.exact_dated_video_count_for_age_adjusted_views >= 5).length,
  evidence_bands: Object.fromEntries(["under_5", "5_9", "10_19", "20_plus", "unknown"].map(band => [band, features.filter(row => row.evidence_band === band).length])),
  caution: "These are descriptive features, not quality scores. Channel totals are self-reported/displayed metrics; video performance summaries cover only videos linked in this database. Subscriber counts marked abbreviated are approximate. No queue score is changed by this audit."
};
const jsonPath = path.join(reportDir, `channel-metrics-audit-${stamp}.json`);
const csvPath = path.join(reportDir, `channel-metrics-audit-${stamp}.csv`);
const mdPath = path.join(reportDir, `channel-metrics-audit-${stamp}.md`);
fs.writeFileSync(jsonPath, JSON.stringify({ summary, features, top }, null, 2));
const columns = ["channel_name", "channel_url", "ecosystem_classification", "subscriber_count", "subscriber_count_precision", "subscriber_band", "declared_video_count", "declared_video_count_precision", "catalogue_band", "total_view_count", "channel_age_days", "channel_age_band", "subscribers_per_upload", "lifetime_views_per_upload", "lifetime_views_per_subscriber", "declared_uploads_per_year_since_join", "linked_video_count", "corpus_coverage_percent", "evidence_band", "video_count_with_views", "video_median_views", "video_p90_views", "top_decile_view_share", "exact_dated_video_count_for_age_adjusted_views", "median_exact_dated_views_per_day", "metadata_completeness_percent", "warnings"];
const csv = [columns.join(","), ...features.map(row => columns.map(key => csvCell(key === "corpus_coverage_percent" ? (row.corpus_coverage_percent ?? null) : row[key])).join(","))].join("\n");
fs.writeFileSync(csvPath, csv + "\n");
const table = rows => ["| Channel | Subscribers | Uploads | Subs/upload | Views/upload | Linked videos | Median sampled views | Warnings |", "|---|---:|---:|---:|---:|---:|---:|---|"]
  .concat(rows.map(row => `| ${String(row.channel_name ?? "").replaceAll("|", "\\|")} | ${row.subscriber_count ?? "—"} | ${row.declared_video_count ?? "—"} | ${row.subscribers_per_upload ?? "—"} | ${row.lifetime_views_per_upload ?? "—"} | ${row.linked_video_count} | ${row.video_median_views ?? "—"} | ${row.warnings.join("; ") || "—"} |`)).join("\n");
const md = `# Channel metrics audit\n\n- Generated: ${generatedAt}\n- Run ID: ${runId}\n- Channels: ${features.length}\n- Linked video relationships: ${linkedRows.length}\n- Metrics version: channel-metrics-v1\n\n## Evidence depth\n\n${Object.entries(summary.evidence_bands).map(([key, value]) => `- ${key}: ${value}`).join("\n")}\n\n## Highest subscribers per upload\n\n${table(top.subscribers_per_upload.slice(0, 20))}\n\n## Highest lifetime views per upload\n\n${table(top.lifetime_views_per_upload.slice(0, 20))}\n\n## Highest median views per day (exact-date videos only)\n\n${table(top.median_exact_dated_views_per_day.slice(0, 20))}\n\n## Important interpretation limits\n\n${summary.caution}\n\nThe subscriber-per-upload and lifetime-views-per-upload ratios are descriptive signals, not standalone success scores. Compare channels within subscriber, catalogue, age, format, and relevance cohorts before integrating these features into queue rankings.\n`;
fs.writeFileSync(mdPath, md);
console.log("[channel-metrics] Completed", { summary, reports: { jsonPath, csvPath, mdPath }, databaseTables: ["channel_metrics_audit_runs", "channel_metric_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.