wulf-pulse/scripts/backfill-fingerprints.ts

/**
 * backfill-fingerprints.ts
 *
 * Generates aggregate_fingerprint for analyzer_analyses rows that are missing
 * one. Reads triage_response + sonnet_response (+ optional opus_response) from
 * the legacy model_traces JSONB column on each analysis, runs Stage 6, and
 * writes the result back via updateAnalysisFingerprint.
 *
 * Idempotent — analyses with a fingerprint already in place are skipped at
 * the SQL filter level, so re-running is safe.
 *
 * Usage:
 *   npx tsx scripts/backfill-fingerprints.ts                 # process all
 *   npx tsx scripts/backfill-fingerprints.ts --limit=50      # cap work
 *   npx tsx scripts/backfill-fingerprints.ts --dry-run       # show what would run
 *
 * Spec: docs/ticket-analyzer-phase2-spec.md → Section A.3
 */

import { config } from 'dotenv';
import { resolve } from 'path';

config({ path: resolve(__dirname, '../.env.local') });
config({ path: resolve(__dirname, '../.env') });

// When run from the host (not inside the docker network), POSTGRES_HOST is
// 'postgres' which won't resolve. Fall back to localhost.
if (process.env.POSTGRES_HOST === 'postgres') {
  process.env.POSTGRES_HOST = 'localhost';
}

import postgresClient from '../lib/services/postgres-client';
import { runFingerprintStage } from '../lib/services/analyzer/stages/stage6-fingerprint';
import { updateAnalysisFingerprint } from '../lib/services/analyzer/persistence';
import {
  DeepAnalysisResponse,
  OpusResponse,
  TriageResponse,
} from '../lib/types/analyzer';

interface BackfillRow {
  id: string;
  ticket_number: string;
  analysis_version: number;
  model_traces: Record<string, unknown> | null;
}

const BATCH_SIZE = 10;

function parseArgs() {
  const args = process.argv.slice(2);
  const dryRun = args.includes('--dry-run');
  const limitArg = args.find((a) => a.startsWith('--limit='));
  const limit = limitArg
    ? Math.max(0, Number(limitArg.split('=')[1]))
    : Number.POSITIVE_INFINITY;
  return { dryRun, limit };
}

async function main() {
  const { dryRun, limit } = parseArgs();
  console.log(
    `[backfill-fingerprints] starting (dryRun=${dryRun}, limit=${
      Number.isFinite(limit) ? limit : 'unlimited'
    })`
  );

  let processed = 0;
  let succeeded = 0;
  let skipped = 0;
  let failed = 0;
  let totalCost = 0;

  // Loop in batches until we run out of work or hit the limit.
  while (processed < limit) {
    const remaining = Math.min(BATCH_SIZE, limit - processed);
    const res = await postgresClient.query<BackfillRow>(
      `SELECT id::text AS id, ticket_number, analysis_version, model_traces
         FROM analyzer_analyses
        WHERE aggregate_fingerprint IS NULL
          AND status = 'complete'
        ORDER BY triggered_at ASC
        LIMIT $1`,
      [remaining]
    );

    if (res.rowCount === 0) break;

    for (const row of res.rows) {
      processed++;
      const tag = `${row.ticket_number} v${row.analysis_version}`;

      const traces = row.model_traces ?? {};
      const tRaw = (traces as Record<string, unknown>).triage_response;
      const sRaw = (traces as Record<string, unknown>).sonnet_response;
      const oRaw = (traces as Record<string, unknown>).opus_response;

      if (!tRaw || !sRaw) {
        console.log(`[skip] ${tag} — model_traces missing triage/sonnet`);
        skipped++;
        continue;
      }

      let triage, sonnet, opus;
      try {
        triage = TriageResponse.parse(tRaw);
        sonnet = DeepAnalysisResponse.parse(sRaw);
        opus = oRaw ? OpusResponse.parse(oRaw) : null;
      } catch (err) {
        console.log(
          `[skip] ${tag} — model_traces shape unrecognized: ${
            err instanceof Error ? err.message : String(err)
          }`
        );
        skipped++;
        continue;
      }

      if (dryRun) {
        console.log(`[dry] would fingerprint ${tag}`);
        continue;
      }

      try {
        const fp = await runFingerprintStage({ triage, sonnet, opus });
        await updateAnalysisFingerprint(row.id, fp.data);
        totalCost += fp.estimated_cost_usd;
        succeeded++;
        console.log(
          `[ok] ${tag} (cost $${fp.estimated_cost_usd.toFixed(4)}, total $${totalCost.toFixed(4)})`
        );
      } catch (err) {
        failed++;
        console.error(
          `[err] ${tag}: ${err instanceof Error ? err.message : String(err)}`
        );
      }
    }
  }

  console.log(
    `[backfill-fingerprints] done. processed=${processed} succeeded=${succeeded} skipped=${skipped} failed=${failed} totalCost=$${totalCost.toFixed(4)}`
  );
  process.exit(failed > 0 && succeeded === 0 ? 1 : 0);
}

main().catch((err) => {
  console.error('[backfill-fingerprints] unhandled error:', err);
  process.exit(1);
});