Docs/Skills and code/src/

src/series-analysis/store.js

Code71 lines3.3 KBsrc/

import fs from "node:fs";
import path from "node:path";
import Database from "better-sqlite3";

export const DEFAULT_DB_PATH = path.resolve(process.cwd(), "data", "series-analysis.db");

/** One database for all series; every table is keyed by series_id so runs for different series never mix. */
export function openStore(dbPath = process.env.SERIES_ANALYSIS_DB ?? DEFAULT_DB_PATH) {
  fs.mkdirSync(path.dirname(dbPath), { recursive: true });
  const db = new Database(dbPath);
  db.pragma("journal_mode = WAL");
  db.exec(`
    CREATE TABLE IF NOT EXISTS series_queries (
      series_id TEXT NOT NULL, query TEXT NOT NULL, tier TEXT NOT NULL, formula TEXT, anchor TEXT,
      origin TEXT NOT NULL DEFAULT 'template',
      status TEXT NOT NULL DEFAULT 'pending', result_count INTEGER, error TEXT, searched_at TEXT,
      PRIMARY KEY (series_id, query)
    );

    CREATE TABLE IF NOT EXISTS series_videos (
      series_id TEXT NOT NULL, video_id TEXT NOT NULL,
      title TEXT, url TEXT, channel_name TEXT, channel_url TEXT, channel_id TEXT,
      source TEXT NOT NULL,                       -- search | research-db
      first_seen_at TEXT NOT NULL,
      search_views INTEGER, search_published_text TEXT, search_duration_seconds INTEGER, thumbnail_url TEXT,
      info_status TEXT,                           -- NULL = not fetched, ok, error
      info_error TEXT, info_fetched_at TEXT,
      views INTEGER, likes INTEGER, published_at TEXT, published_precision TEXT, duration_seconds INTEGER,
      category TEXT, description TEXT,
      PRIMARY KEY (series_id, video_id)
    );

    CREATE TABLE IF NOT EXISTS series_hits (
      series_id TEXT NOT NULL, video_id TEXT NOT NULL, query TEXT NOT NULL, position INTEGER NOT NULL,
      PRIMARY KEY (series_id, video_id, query)
    );

    CREATE TABLE IF NOT EXISTS series_channels (
      series_id TEXT NOT NULL, channel_key TEXT NOT NULL,   -- channel_id when known, else channel_url
      channel_url TEXT, channel_id TEXT, name TEXT, handle TEXT, description TEXT, country TEXT,
      subscriber_count INTEGER, video_count INTEGER, total_view_count INTEGER, joined_date TEXT,
      is_verified INTEGER, keywords_json TEXT,
      baseline_n INTEGER, baseline_median_views REAL, baseline_p75_views REAL, baseline_note TEXT,
      status TEXT, error TEXT, fetched_at TEXT,
      PRIMARY KEY (series_id, channel_key)
    );

    CREATE TABLE IF NOT EXISTS series_llm_fit (
      series_id TEXT NOT NULL, video_id TEXT NOT NULL,
      fit REAL, subject TEXT, packaging TEXT, note TEXT, model TEXT, judged_at TEXT,
      PRIMARY KEY (series_id, video_id)
    );

    CREATE TABLE IF NOT EXISTS series_rankings (
      series_id TEXT NOT NULL, run_id TEXT NOT NULL, video_id TEXT NOT NULL,
      rank INTEGER, selected INTEGER NOT NULL DEFAULT 0, tier TEXT, learn_score REAL,
      fit REAL, performance REAL, learnability REAL, excluded TEXT, detail_json TEXT,
      PRIMARY KEY (series_id, run_id, video_id)
    );

    CREATE TABLE IF NOT EXISTS series_transcripts (
      series_id TEXT NOT NULL, video_id TEXT NOT NULL,
      status TEXT, error TEXT, language TEXT, segment_count INTEGER, file_path TEXT, fetched_at TEXT,
      PRIMARY KEY (series_id, video_id)
    );
  `);
  return db;
}

export const channelKeyOf = row => row.channel_id ?? row.channel_url ?? row.channel_name ?? 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.