Docs/Skills and code/src/

src/stages/16-repair-date-normalization.js

Code88 lines4.5 KBsrc/

import { getDb } from "../db.js";
import { parseAbsoluteDate } from "../video-normalization.js";

const db = getDb();
const apply = process.argv.includes("--apply") || !process.argv.includes("--dry-run");
const runAt = new Date().toISOString();
if (process.argv.includes("--help")) {
  console.log("Date normalization repair: applies valid corrections by default. Use --dry-run to preview without writing; --apply explicitly applies corrections.");
  process.exit(0);
}

function parseDisplayDate(value, joined = false) {
  if (value == null) return { value: null, precision: "unknown" };
  const text = String(value).trim();
  return parseAbsoluteDate(joined ? text.replace(/^Joined\s+/i, "") : text);
}

function inspectRows({ table, idColumn, idField, dateColumn, textColumn, joined = false, precisionColumn = null }) {
  const rows = db.prepare(`SELECT ${idColumn} AS row_id, ${dateColumn} AS stored_date, ${textColumn} AS display_text${precisionColumn ? `, ${precisionColumn} AS precision` : ""} FROM ${table} WHERE ${textColumn} IS NOT NULL`).all();
  const candidates = [];
  for (const row of rows) {
    if (precisionColumn && row.precision !== "date") continue;
    const parsed = parseDisplayDate(row.display_text, joined);
    if (parsed.precision !== "date" || parsed.value === row.stored_date) continue;
    candidates.push({
      table, idField, id: row.row_id, stored: row.stored_date,
      display: row.display_text, corrected: parsed.value
    });
  }
  return candidates;
}

const plans = [
  { table: "videos", idColumn: "video_id", idField: "video_id", dateColumn: "published_at", textColumn: "published_at_text", precisionColumn: "published_at_precision" },
  { table: "video_metric_snapshots", idColumn: "id", idField: "id", dateColumn: "published_at", textColumn: "published_at_text", precisionColumn: "published_at_precision" },
  { table: "channels", idColumn: "channel_url", idField: "channel_url", dateColumn: "joined_date", textColumn: "joined_date_text", joined: true },
  { table: "channel_metadata", idColumn: "channel_url", idField: "channel_url", dateColumn: "joined_date", textColumn: "joined_date_text", joined: true },
  { table: "channel_metric_snapshots", idColumn: "id", idField: "id", dateColumn: "joined_date", textColumn: "joined_date_text", joined: true }
];

const tables = new Set(db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all().map(row => row.name));
const allCandidates = [];
for (const plan of plans) {
  if (!tables.has(plan.table)) {
    console.log(`[date-repair] Table absent; skipping: ${plan.table}`);
    continue;
  }
  const found = inspectRows(plan);
  allCandidates.push(...found);
  console.log(`[date-repair] ${plan.table}: ${found.length} date(s) need correction`);
}
console.log("[date-repair] Sample candidates:", allCandidates.slice(0, 20));
console.log("[date-repair] Summary:", {
  mode: apply ? "APPLY" : "DRY RUN",
  candidateCount: allCandidates.length,
  byTable: Object.fromEntries([...new Set(allCandidates.map(item => item.table))].map(table => [table, allCandidates.filter(item => item.table === table).length]))
});

if (!apply) {
  console.log("[date-repair] DRY RUN requested; no changes made. Rerun without --dry-run (or with --apply) to apply corrections.");
  process.exit(0);
}

const updateStatements = {
  videos: db.prepare(`UPDATE videos SET published_at=@corrected, published_at_normalized=@corrected,
    published_at_normalization_method='absolute_display_date_utc_v2', published_at_normalized_at=@runAt
    WHERE video_id=@id AND published_at_text=@display`),
  video_metric_snapshots: db.prepare(`UPDATE video_metric_snapshots SET published_at=@corrected
    WHERE id=@id AND published_at_text=@display`),
  channels: db.prepare(`UPDATE channels SET joined_date=@corrected WHERE channel_url=@id AND joined_date_text=@display`),
  channel_metadata: db.prepare(`UPDATE channel_metadata SET joined_date=@corrected WHERE channel_url=@id AND joined_date_text=@display`),
  channel_metric_snapshots: db.prepare(`UPDATE channel_metric_snapshots SET joined_date=@corrected WHERE id=@id AND joined_date_text=@display`)
};
let updated = 0;
db.transaction(() => {
  for (const item of allCandidates) {
    const result = updateStatements[item.table].run({
      id: item.id, corrected: item.corrected, display: item.display, runAt
    });
    updated += result.changes;
  }
})();
console.log(`[date-repair] Applied corrections: ${updated}`);
console.log("[date-repair] Display text and raw metadata were preserved; only normalized date fields were corrected.");

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.