Docs/Skills and code/src/

src/stages/08-normalize-temporal-data.js

Code63 lines3.3 KBsrc/

import { getDb } from "../db.js";
import { ensureResearchSchema } from "../analysis/research-schema.js";
import { normalizePublishedAt } from "../analysis/temporal.js";

const db = getDb();
ensureResearchSchema();
const force = process.argv.includes("--force");
const limitArg = process.argv.find(value => value.startsWith("--limit="));
const limit = limitArg ? Math.max(1, Number(limitArg.split("=")[1]) || 1) : null;
const rows = db.prepare(`
  SELECT video_id, published_at, published_at_raw, published_at_text, discovered_at,
         published_at_normalized, published_at_precision, published_at_normalization_method
  FROM videos
  WHERE COALESCE(NULLIF(published_at_raw,''), NULLIF(published_at,''), NULLIF(published_at_text,'')) IS NOT NULL
  ORDER BY discovered_at, video_id
`).all();

const update = db.prepare(`
  UPDATE videos SET
    published_at_raw = COALESCE(NULLIF(published_at_raw,''), NULLIF(published_at,''), NULLIF(published_at_text,'')),
    published_at_normalized = ?,
    published_at_precision = ?,
    published_at_normalization_method = ?,
    published_at_normalized_at = ?
  WHERE video_id = ?
`);
const now = new Date().toISOString();
let processed = 0, skipped = 0, exact = 0, estimated = 0, unknown = 0;
const examples = [];
const tx = db.transaction(() => {
  for (const row of rows) {
    if (limit !== null && processed >= limit) break;
    const candidates = [row.published_at_raw, row.published_at, row.published_at_text].filter(value => value != null && String(value).trim() !== "");
    const exactCandidate = candidates.find(value => normalizePublishedAt(value, row.discovered_at).precision.startsWith("exact_"));
    const raw = exactCandidate ?? candidates[0] ?? null;
    const result = normalizePublishedAt(raw, row.discovered_at);
    const unchanged = row.published_at_normalized === result.normalized &&
      row.published_at_precision === result.precision &&
      row.published_at_normalization_method === result.method;
    if (!force && unchanged) { skipped++; continue; }
    update.run(result.normalized, result.precision, result.method, now, row.video_id);
    processed++;
    if (result.precision.startsWith("exact_")) exact++;
    else if (result.precision.startsWith("estimated_")) estimated++;
    else unknown++;
    if (examples.length < 12) examples.push({ videoId: row.video_id, raw, anchor: row.discovered_at, normalized: result.normalized, precision: result.precision, method: result.method });
  }
});
tx();

const coverage = db.prepare(`SELECT COUNT(*) AS total,
  SUM(CASE WHEN published_at_raw IS NOT NULL AND trim(published_at_raw)<>'' THEN 1 ELSE 0 END) AS raw_present,
  SUM(CASE WHEN published_at_normalized IS NOT NULL THEN 1 ELSE 0 END) AS normalized,
  SUM(CASE WHEN published_at_precision LIKE 'exact_%' THEN 1 ELSE 0 END) AS exact_dates,
  SUM(CASE WHEN published_at_precision LIKE 'estimated_%' THEN 1 ELSE 0 END) AS estimated_dates,
  SUM(CASE WHEN published_at_precision='unknown' THEN 1 ELSE 0 END) AS unknown_dates
  FROM videos`).get();
console.log("[temporal] Normalization complete", {
  totalRowsWithSource: rows.length, processed, skipped, exact, estimated, unknown,
  coverage, examples,
  note: "Exact dates from display text such as 'Premiered Sep 19, 2026' are parsed as exact calendar days. Relative dates remain estimates anchored to videos.discovered_at; original display text is preserved."
});

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.