`scripts/backfill_prompt_match.js`

56 lines
#!/usr/bin/env node
/**
 * Backfill prompt-match ratings from the append-only JSONL ledger
 * (data/prompt-match-ratings.jsonl) into the PG columns promoted in
 * migrations/20260601_prompt_match_columns.sql.
 *
 * Last-write-wins per id (ledger is chronological). Idempotent: re-running just
 * re-applies the same final state. ADDITIVE only — never touches is_published.
 *
 * Usage: node scripts/backfill_prompt_match.js
 * Prints a JSON summary { ledger_lines, distinct_ids, applied, skipped }.
 */
'use strict';
const fs = require('fs');
const path = require('path');
const { psqlExecLocal, pgEsc } = require('../lib/db');

const LEDGER = path.join(__dirname, '..', 'data', 'prompt-match-ratings.jsonl');

if (!fs.existsSync(LEDGER)) {
  console.log(JSON.stringify({ ledger_lines: 0, distinct_ids: 0, applied: 0, skipped: 0, note: 'no ledger file' }));
  process.exit(0);
}

const lines = fs.readFileSync(LEDGER, 'utf8').split('\n').filter(Boolean);
const latest = new Map(); // id -> { match, verdict, ts }
for (const ln of lines) {
  try {
    const r = JSON.parse(ln);
    if (Number.isInteger(r.id)) latest.set(r.id, { match: r.match ?? null, verdict: r.verdict ?? null, ts: r.ts || null });
  } catch {}
}

let applied = 0, skipped = 0;
for (const [id, r] of latest) {
  const match = Number.isInteger(r.match) && r.match >= 1 && r.match <= 5 ? r.match : null;
  const verdict = (r.verdict === 'approve' || r.verdict === 'reject') ? r.verdict : null;
  if (match === null && verdict === null) { skipped++; continue; }
  const ts = r.ts || new Date().toISOString();
  try {
    psqlExecLocal(
      'UPDATE spoon_all_designs SET ' +
        'user_prompt_match=' + pgEsc(match) + ', ' +
        'user_prompt_verdict=' + pgEsc(verdict) + ', ' +
        'user_prompt_rated_at=' + pgEsc(ts) + ' ' +
      'WHERE id=' + id + ';'
    );
    applied++;
  } catch (e) {
    skipped++;
    process.stderr.write(`id=${id} failed: ${e.message}\n`);
  }
}

console.log(JSON.stringify({ ledger_lines: lines.length, distinct_ids: latest.size, applied, skipped }));