Docs/Skills and code/src/

src/analysis/within-channel-performance-store.js

Code174 lines10.9 KBsrc/

import { ensureWithinChannelPerformanceSchema, WITHIN_CHANNEL_SCHEMA_VERSION } from "./within-channel-performance-schema.js";
import { analyzeWithinChannelCorpus } from "./within-channel-performance.js";

const tableExists = (db, name) => Boolean(db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(name));
const numberOrNull = value => value !== null && value !== undefined && value !== "" && Number.isFinite(Number(value)) ? Number(value) : null;
const timeValue = value => { const time = Date.parse(value ?? ""); return Number.isFinite(time) ? time : null; };
const precisionRank = value => {
  const text = String(value ?? "").toLowerCase();
  if (text === "exact_displayed") return 2;
  if (text === "exact" || text.startsWith("exact_")) return 3;
  if (text === "abbreviated") return 1;
  return 0;
};

export function loadWithinChannelInput(db) {
  const channels = db.prepare(`
    SELECT c.*,
      COALESCE(m.name, c.name) AS resolved_name,
      COALESCE(m.description, c.description) AS resolved_description,
      COALESCE(m.subscriber_count, c.subscriber_count) AS resolved_subscriber_count,
      COALESCE(m.subscriber_count_precision, c.subscriber_count_precision) AS resolved_subscriber_count_precision,
      COALESCE(m.video_count, c.video_count) AS resolved_video_count,
      COALESCE(m.video_count_precision, c.video_count_precision) AS resolved_video_count_precision,
      COALESCE(m.total_view_count, c.total_view_count) AS resolved_total_view_count,
      COALESCE(m.joined_date, c.joined_date) AS resolved_joined_date
    FROM channels c LEFT JOIN channel_metadata m ON m.channel_url = c.channel_url
    ORDER BY COALESCE(m.name, c.name) COLLATE NOCASE, c.channel_url
  `).all();

  const relations = db.prepare(`
    SELECT channel_url, video_id FROM channel_videos WHERE channel_url IS NOT NULL AND video_id IS NOT NULL
    UNION
    SELECT channel_url, video_id FROM videos WHERE channel_url IS NOT NULL AND video_id IS NOT NULL
  `).all();
  const rawRelationCount = db.prepare("SELECT COUNT(*) AS n FROM channel_videos").get().n;
  const rawUniquePairCount = db.prepare("SELECT COUNT(*) AS n FROM (SELECT DISTINCT channel_url, video_id FROM channel_videos WHERE channel_url IS NOT NULL AND video_id IS NOT NULL)").get().n;
  const supplementalVideoPairs = db.prepare(`SELECT COUNT(*) AS n FROM videos v WHERE v.channel_url IS NOT NULL AND v.video_id IS NOT NULL
    AND NOT EXISTS (SELECT 1 FROM channel_videos cv WHERE cv.channel_url=v.channel_url AND cv.video_id=v.video_id)`).get().n;
  const videoRows = db.prepare(`SELECT * FROM videos`).all();
  const videoById = new Map(videoRows.map(row => [row.video_id, row]));

  const latestSnapshotByVideo = new Map();
  const latestNonNullViewSnapshotByVideo = new Map();
  if (tableExists(db, "video_metric_snapshots")) {
    const snapshots = db.prepare(`
      SELECT video_id, observed_at, view_count, view_count_text, view_count_precision,
        published_at, published_at_text, published_at_precision, duration_seconds, duration_precision, category
      FROM video_metric_snapshots
      ORDER BY observed_at ASC, id ASC
    `).all();
    for (const snapshot of snapshots) {
      const current = latestSnapshotByVideo.get(snapshot.video_id);
      const currentTime = timeValue(current?.observed_at);
      const candidateTime = timeValue(snapshot.observed_at);
      if (!current || (candidateTime ?? -Infinity) >= (currentTime ?? -Infinity)) latestSnapshotByVideo.set(snapshot.video_id, snapshot);
      if (numberOrNull(snapshot.view_count) != null) {
        const currentView = latestNonNullViewSnapshotByVideo.get(snapshot.video_id);
        const currentViewTime = timeValue(currentView?.observed_at);
        if (!currentView || (candidateTime ?? -Infinity) >= (currentViewTime ?? -Infinity)) latestNonNullViewSnapshotByVideo.set(snapshot.video_id, snapshot);
      }
    }
  }

  const relationPairs = new Map();
  for (const relation of relations) {
    if (!relationPairs.has(relation.channel_url)) relationPairs.set(relation.channel_url, new Set());
    relationPairs.get(relation.channel_url).add(relation.video_id);
  }
  const videosByChannel = new Map();
  for (const channel of channels) {
    const ids = relationPairs.get(channel.channel_url) ?? new Set();
    const rows = [];
    for (const videoId of ids) {
      const base = videoById.get(videoId) ?? { video_id: videoId };
      const latestSnapshot = latestSnapshotByVideo.get(videoId) ?? null;
      const snapshot = latestNonNullViewSnapshotByVideo.get(videoId) ?? latestSnapshot;
      const baseViews = numberOrNull(base.view_count);
      const snapshotViews = numberOrNull(snapshot?.view_count);
      const baseTime = timeValue(base.metadata_observed_at);
      const snapshotTime = timeValue(snapshot?.observed_at);
      let useSnapshot = false;
      if (snapshotViews != null && baseViews == null) useSnapshot = true;
      else if (snapshotViews != null && baseViews != null) {
        if (snapshotTime != null && (baseTime == null || snapshotTime > baseTime)) useSnapshot = true;
        else if (snapshotTime != null && baseTime != null && snapshotTime === baseTime && precisionRank(snapshot.view_count_precision) > precisionRank(base.view_count_precision)) useSnapshot = true;
      }
      let publishedAt = base.published_at_normalized || base.published_at || null;
      let publishedPrecision = base.published_at_precision ?? null;
      let publishedMethod = base.published_at_normalization_method ?? null;
      if (!(String(publishedPrecision ?? "").toLowerCase().startsWith("exact") || ["exact", "date"].includes(String(publishedPrecision ?? "").toLowerCase())) && snapshot?.published_at && precisionRank(snapshot.published_at_precision) >= 3) {
        publishedAt = snapshot.published_at;
        publishedPrecision = snapshot.published_at_precision;
        publishedMethod = "exact_snapshot_observation";
      }
      rows.push({
        video_id: videoId,
        title: base.title ?? null,
        url: base.url ?? null,
        view_count: useSnapshot ? snapshotViews : baseViews,
        view_count_precision: useSnapshot ? snapshot.view_count_precision : (base.view_count_precision ?? null),
        view_count_source: useSnapshot ? "latest_non_null_metric_snapshot" : (baseViews != null ? "videos_current_record" : null),
        view_observed_at: useSnapshot ? snapshot?.observed_at ?? null : (base.metadata_observed_at ?? null),
        published_at: publishedAt,
        published_at_normalized: publishedAt,
        published_at_precision: publishedPrecision,
        published_at_normalization_method: publishedMethod,
        discovered_at: base.discovered_at ?? null,
        duration_seconds: base.duration_seconds ?? latestSnapshot?.duration_seconds ?? null,
        duration_precision: base.duration_precision ?? latestSnapshot?.duration_precision ?? null,
        category: base.category ?? latestSnapshot?.category ?? null
      });
    }
    videosByChannel.set(channel.channel_url, rows);
  }
  return { channels, videosByChannel, rawRelationCount, rawUniquePairCount, supplementalVideoPairs, uniquePairCount: relations.length };
}

export function persistWithinChannelAnalysis(db, { runId, generatedAt, input, result, config, summary }) {
  ensureWithinChannelPerformanceSchema(db);
  const insertRun = db.prepare(`INSERT INTO within_channel_analysis_runs
    (run_id, generated_at, analysis_version, channels_analyzed, linked_unique_channel_video_pairs, eligible_video_records, reliable_dated_video_records, age_cohort_count, config_json, summary_json)
    VALUES (@run_id, @generated_at, @analysis_version, @channels_analyzed, @linked_unique_channel_video_pairs, @eligible_video_records, @reliable_dated_video_records, @age_cohort_count, @config_json, @summary_json)`);

  const profileColumns = Object.keys(result.profiles[0] ?? {}).filter(key => key !== "channel_url" && key !== "channel_name");
  const profileFields = ["run_id", "channel_url", "channel_name", ...profileColumns];
  const insertProfile = db.prepare(`INSERT INTO within_channel_channel_profiles (${profileFields.join(",")}) VALUES (${profileFields.map(field => `@${field}`).join(",")})`);

  const outlierColumns = Object.keys(result.outliers[0] ?? {}).filter(key => key !== "channel_url" && key !== "channel_name");
  const outlierFields = ["run_id", "channel_url", "channel_name", ...outlierColumns];
  const insertOutlier = db.prepare(`INSERT INTO within_channel_video_outliers (${outlierFields.join(",")}) VALUES (${outlierFields.map(field => `@${field}`).join(",")})`);

  const insertBenchmark = db.prepare(`INSERT INTO within_channel_age_cohort_benchmarks
    (run_id, age_cohort, eligible_video_count, median_views_per_day, p90_views_per_day, benchmark_status)
    VALUES (@run_id, @age_cohort, @eligible_video_count, @median_views_per_day, @p90_views_per_day, @benchmark_status)`);
  const insertQueueEvidence = db.prepare(`INSERT INTO within_channel_queue_evidence
    (run_id, channel_url, channel_name, queue_name, integration_status, score_effect, candidate_features_json, evidence_refs_json, interpretation)
    VALUES (@run_id, @channel_url, @channel_name, @queue_name, @integration_status, @score_effect, @candidate_features_json, @evidence_refs_json, @interpretation)`);

  const profileRows = result.profiles.map(row => ({ run_id: runId, ...row }));
  const outlierRows = result.outliers.map(row => ({ run_id: runId, ...row }));
  const benchmarkRows = result.cohortBenchmarks.map(row => ({ run_id: runId, ...row }));
  const queueRows = result.queueEvidence.map(row => ({ run_id: runId, ...row }));
  const eligible = result.profiles.reduce((sum, row) => sum + row.eligible_view_count, 0);
  const dated = result.profiles.reduce((sum, row) => sum + row.reliable_dated_view_count, 0);
  const run = {
    run_id: runId,
    generated_at: generatedAt,
    analysis_version: result.version,
    channels_analyzed: result.profiles.length,
    linked_unique_channel_video_pairs: input.uniquePairCount,
    eligible_video_records: eligible,
    reliable_dated_video_records: dated,
    age_cohort_count: result.cohortBenchmarks.length,
    config_json: JSON.stringify({ schemaVersion: WITHIN_CHANNEL_SCHEMA_VERSION, ...config }),
    summary_json: JSON.stringify(summary)
  };

  const transaction = db.transaction(() => {
    insertRun.run(run);
    for (const row of profileRows) insertProfile.run(row);
    for (const row of outlierRows) insertOutlier.run(row);
    for (const row of benchmarkRows) insertBenchmark.run(row);
    for (const row of queueRows) insertQueueEvidence.run(row);
  });
  transaction();
  return { run, profileRows: profileRows.length, outlierRows: outlierRows.length, benchmarkRows: benchmarkRows.length, queueRows: queueRows.length };
}

export function latestWithinChannelRun(db) {
  const exists = db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name='within_channel_analysis_runs'").get();
  if (!exists) return null;
  return db.prepare(`SELECT * FROM within_channel_analysis_runs ORDER BY generated_at DESC, run_id DESC LIMIT 1`).get() ?? null;
}

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.