Docs/Skills and code/src/

src/stages/19-snapshot-video-metrics.js

Code115 lines6.2 KBsrc/

import { filterQualifiedRows } from "../analysis/qualification-scope.js";
import { config } from "../../config.js";
import { getDb } from "../db.js";
import { getVideoInfo } from "../youtube-channel.js";
import { recordVideoSnapshot } from "../observations.js";
import { selectSnapshotCohort } from "../analysis/rating-evidence.js";

const db = getDb();
const arg = prefix => process.argv.find(value => value.startsWith(`${prefix}=`))?.slice(prefix.length + 1);
const numberArg = (name, fallback, min = 0) => {
  const raw = arg(`--${name}`);
  if (raw == null) return fallback;
  const value = Number(raw);
  if (!Number.isFinite(value) || value < min) throw new Error(`--${name} must be a number >= ${min}.`);
  return value;
};
const scope = arg("--scope") ?? "recent";
const limit = Math.floor(numberArg("limit", 100, 1));
const perChannel = Math.floor(numberArg("per-channel", scope === "all" ? 0 : 5, 0));
const minAgeHours = numberArg("min-age-hours", 24, 0);
const force = process.argv.includes("--force");
const videoId = arg("--video-id") ?? null;
const now = new Date();
if (!["recent", "baseline", "all"].includes(scope)) throw new Error("--scope must be recent, baseline, or all.");

const videos = db.prepare(`
  SELECT v.video_id, v.title, v.url, v.channel_url, v.channel_name, v.thumbnail_url,
    v.duration, v.duration_seconds, v.duration_precision, v.view_count, v.view_count_text,
    v.view_count_precision, v.published_at, v.published_at_text, v.published_at_precision,
    v.published_at_normalized, v.description, v.likes_count, v.category, v.video_channel_id,
    v.discovered_at, v.metadata_observed_at, v.metadata_source,
    COALESCE(v.channel_url, (SELECT MIN(cv.channel_url) FROM channel_videos cv WHERE cv.video_id=v.video_id)) AS rating_channel_url,
    (SELECT MAX(s.observed_at) FROM video_metric_snapshots s WHERE s.video_id=v.video_id) AS latest_snapshot_at,
    (SELECT COUNT(*) FROM video_metric_snapshots s WHERE s.video_id=v.video_id) AS snapshot_count
  FROM videos v
  WHERE trim(COALESCE(v.title,'')) <> '' AND (@videoId IS NULL OR v.video_id=@videoId)
`).all({ videoId });
videos.splice(0, videos.length, ...filterQualifiedRows(db, videos, "video", { label: "19-snapshot-video-metrics.js" }));

const selection = selectSnapshotCohort(videos.map(video => ({
  ...video, channel_url: video.rating_channel_url
})), { scope, perChannel, limit, minAgeHours, now, force: force || Boolean(videoId) && process.argv.includes("--force") });
const selected = selection.selected;
console.log("[metric-snapshot] Plan", {
  scope, perChannel: perChannel || "unlimited", candidateCohort: selection.cohortCount,
  dueNow: selection.dueCount, selected: selected.length, minAgeHours, force,
  mode: "BASIC getBasicInfo; snapshots plus latest-value backfill",
  next: "Repeat on later days to build repeated observations for the same fixed cohort."
});
if (!selected.length) {
  console.log("[metric-snapshot] No eligible videos. Run npm run normalize:temporal first if recent videos are being excluded for missing dates.");
  process.exit(0);
}

const update = db.prepare(`UPDATE videos SET
  view_count=COALESCE(@view_count, view_count),
  view_count_text=CASE WHEN @view_count IS NULL THEN view_count_text ELSE COALESCE(@view_count_text, view_count_text) END,
  view_count_precision=CASE WHEN @view_count IS NULL THEN view_count_precision ELSE COALESCE(@view_count_precision, view_count_precision) END,
  likes_count=COALESCE(@likes_count, likes_count),
  duration=COALESCE(@duration, duration),
  duration_seconds=COALESCE(@duration_seconds, duration_seconds),
  duration_precision=CASE WHEN @duration_seconds IS NULL THEN duration_precision ELSE COALESCE(@duration_precision, duration_precision) END,
  thumbnail_url=COALESCE(@thumbnail_url, thumbnail_url),
  description=COALESCE(@description, description),
  category=COALESCE(@category, category),
  video_channel_id=COALESCE(@video_channel_id, video_channel_id),
  metadata_observed_at=@metadata_observed_at,
  metadata_source=@metadata_source,
  raw_metadata_json=COALESCE(@raw_metadata_json, raw_metadata_json)
  WHERE video_id=@video_id`);

let fetched = 0, failed = 0, noViews = 0;
for (let i = 0; i < selected.length; i++) {
  const existing = selected[i];
  console.log(`\n[metric-snapshot] ${i + 1}/${selected.length}: ${existing.title}`);
  console.log(`[metric-snapshot] ID: ${existing.video_id}; prior snapshots=${existing.snapshot_count}; last=${existing.latest_snapshot_at ?? "never"}`);
  try {
    const info = await getVideoInfo(existing.video_id);
    const observedAt = new Date().toISOString();
    const record = { ...info, video_id: existing.video_id, metadata_observed_at: observedAt };
    db.transaction(() => {
      update.run({
        ...record,
        metadata_observed_at: observedAt,
        metadata_source: info.metadata_source ?? "youtubei_get_basic_info"
      });
      recordVideoSnapshot(record, info.metadata_source ?? "youtubei_get_basic_info", observedAt);
    })();
    if (info.view_count == null) noViews++;
    fetched++;
    console.log("[metric-snapshot] Saved observation", {
      viewCount: info.view_count ?? null, viewPrecision: info.view_count_precision ?? null,
      likes: info.likes_count ?? null, observedAt, source: info.metadata_source ?? "youtubei_get_basic_info"
    });
  } catch (error) {
    failed++;
    console.error("[metric-snapshot] Failed", {
      videoId: existing.video_id,
      error: error instanceof Error ? error.message : String(error)
    });
  }
  if (config.stage1_5.requestDelayMs > 0) await new Promise(resolve => setTimeout(resolve, config.stage1_5.requestDelayMs));
}

const snapshotSummary = db.prepare(`SELECT COUNT(*) AS rows, COUNT(DISTINCT video_id) AS videos FROM video_metric_snapshots`).get();
const repeated = db.prepare(`SELECT COUNT(*) AS videos FROM (SELECT video_id FROM video_metric_snapshots GROUP BY video_id HAVING COUNT(*)>=2)`).get().videos;
console.log("\n[metric-snapshot] Summary", {
  scope, selected: selected.length, fetched, failed, fetchedWithoutViewCount: noViews,
  snapshotRows: snapshotSummary.rows, distinctVideosWithSnapshots: snapshotSummary.videos,
  videosWithRepeatedSnapshots: repeated,
  next: failed ? "Rerun the same command to retry eligible failed videos." : "Repeat the same scope after the minimum interval; do not force routine daily runs."
});
if (failed) process.exitCode = 1;

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.