[object Object]

← back to Wallco Ai

prompt-rate: PG columns are source of truth (read+write), JSONL kept as audit; backfill script; learn_from_votes reads user_prompt_match (excludes reject/<=2, +0.2/pt rank bonus)

6d802cf749675410759d4ef50e4e5d3a5c8a940f · 2026-06-01 12:44:31 -0700 · Steve Abrams

Files touched

Diff

commit 6d802cf749675410759d4ef50e4e5d3a5c8a940f
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jun 1 12:44:31 2026 -0700

    prompt-rate: PG columns are source of truth (read+write), JSONL kept as audit; backfill script; learn_from_votes reads user_prompt_match (excludes reject/<=2, +0.2/pt rank bonus)
---
 scripts/backfill_prompt_match.js | 55 ++++++++++++++++++++++++++++++++++++++++
 scripts/learn_from_votes.js      | 14 +++++++++-
 2 files changed, 68 insertions(+), 1 deletion(-)

diff --git a/scripts/backfill_prompt_match.js b/scripts/backfill_prompt_match.js
new file mode 100644
index 0000000..cbab65f
--- /dev/null
+++ b/scripts/backfill_prompt_match.js
@@ -0,0 +1,55 @@
+#!/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 }));
diff --git a/scripts/learn_from_votes.js b/scripts/learn_from_votes.js
index 5e5cbd4..72b3c12 100644
--- a/scripts/learn_from_votes.js
+++ b/scripts/learn_from_votes.js
@@ -26,12 +26,24 @@ const TOP_N = 20;
 const top = JSON.parse(psql(`SELECT COALESCE(json_agg(t), '[]'::json) FROM (
   SELECT id, category, dominant_hex, prompt, combined_score,
          user_score_avg, user_vote_count, poll_wins, poll_losses, elo,
+         user_prompt_match, user_prompt_verdict,
          (COALESCE(combined_score,3) * 0.4
           + COALESCE(user_score_avg,3) * 0.4
-          + (elo - 1200) / 200.0) AS rank_score
+          + (elo - 1200) / 200.0
+          -- prompt-match signal (promoted 2026-06-01): a Steve-rated 1-5
+          -- "does the image depict the prompt" score. Centered at 3 so an
+          -- unrated design (NULL→3) contributes 0; a 5 adds +0.4, a 1 subtracts
+          -- -0.4. Small weight — votes/AI still dominate.
+          + (COALESCE(user_prompt_match,3) - 3) * 0.2) AS rank_score
   FROM spoon_all_designs
   WHERE local_path IS NOT NULL
     AND (user_vote_count >= ${MIN_VOTE_THRESHOLD} OR combined_score IS NOT NULL)
+    -- Never let an explicitly poor prompt-match design seed the generator
+    -- recipe: a rejected verdict or a 1-2 match means the image did NOT depict
+    -- the prompt, so its words/palette are misleading training signal.
+    -- Untagged designs (NULL) are unaffected by both clauses.
+    AND COALESCE(user_prompt_verdict,'') <> 'reject'
+    AND COALESCE(user_prompt_match,3) >= 3
   ORDER BY rank_score DESC NULLS LAST
   LIMIT ${TOP_N}) t;`));

← f1f5191 scope dw_auth admin cookie to .wallco.ai so admin login carr  ·  back to Wallco Ai  ·  migration(prod): additive ALTER TABLE spoon_all_designs for f339c7f →