migration: add user_prompt_match/verdict/rated_at to all_designs + spoon view (applied local)

25e596a08012be9237b3022df3bcd569742009c9 · 2026-06-01 12:41:48 -0700 · Steve Abrams
Files touched

A migrations/20260601_prompt_match_columns.sql
Diff

commit 25e596a08012be9237b3022df3bcd569742009c9
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jun 1 12:41:48 2026 -0700

    migration: add user_prompt_match/verdict/rated_at to all_designs + spoon view (applied local)
---
 migrations/20260601_prompt_match_columns.sql | 107 +++++++++++++++++++++++++++
 1 file changed, 107 insertions(+)

diff --git a/migrations/20260601_prompt_match_columns.sql b/migrations/20260601_prompt_match_columns.sql
new file mode 100644
index 0000000..487bde0
--- /dev/null
+++ b/migrations/20260601_prompt_match_columns.sql
@@ -0,0 +1,107 @@
+-- 20260601_prompt_match_columns.sql
+--
+-- Promote the prompt-vs-image A/B rating signal from the append-only JSONL
+-- ledger (data/prompt-match-ratings.jsonl) to first-class PG columns, so the
+-- rating becomes the source of truth and the generator learning loop
+-- (scripts/learn_from_votes.js) can read it.
+--
+-- NOTE on topology: `spoon_all_designs` is a VIEW over base table `all_designs`.
+-- The existing user_* rating columns physically live on all_designs and are
+-- exposed via the view's explicit column list. So we must (1) ALTER the BASE
+-- table additively, then (2) CREATE OR REPLACE the view appending the 3 new
+-- columns at the END of its select list (the only thing CREATE OR REPLACE VIEW
+-- permits without a drop). The view is updatable, so the API's
+-- `UPDATE spoon_all_designs SET user_prompt_*` writes through to all_designs.
+--
+-- ADDITIVE ONLY. No drops, no renames of existing columns. ALTER ... IF NOT
+-- EXISTS makes the table change idempotent/re-runnable. CREATE OR REPLACE VIEW
+-- only appends columns (existing column order/names unchanged), so it is safe
+-- on local dw_unified and prod dw_unified. Mirrors existing user_stars (smallint),
+-- user_color_good (boolean), user_rated_at (timestamptz).
+--
+-- Columns added (on all_designs, surfaced via spoon_all_designs):
+--   user_prompt_match    smallint     -- 1..5 "does the image match the prompt"
+--   user_prompt_verdict  text         -- 'approve' | 'reject' | NULL
+--   user_prompt_rated_at timestamptz  -- when the rating was last set
+--
+-- Does NOT touch is_published, the settlement gate, or designs.json sync.
+
+BEGIN;
+
+ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS user_prompt_match    smallint;
+ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS user_prompt_verdict  text;
+ALTER TABLE all_designs ADD COLUMN IF NOT EXISTS user_prompt_rated_at timestamptz;
+
+-- Re-expose through the view (append-only; existing columns unchanged).
+CREATE OR REPLACE VIEW spoon_all_designs AS
+SELECT all_designs.id,
+    all_designs.brand,
+    all_designs.kind,
+    all_designs.width_in,
+    all_designs.height_in,
+    all_designs.panels,
+    all_designs.generator,
+    all_designs.prompt,
+    all_designs.negative_prompt,
+    all_designs.seed,
+    all_designs.steps,
+    all_designs.cfg_scale,
+    all_designs.pd_source_ids,
+    all_designs.image_url,
+    all_designs.local_path,
+    all_designs.dominant_hex,
+    all_designs.palette,
+    all_designs.motifs,
+    all_designs.tags,
+    all_designs.category,
+    all_designs.is_published,
+    all_designs.notes,
+    all_designs.created_at,
+    all_designs.product_line,
+    all_designs.parent_design_id,
+    all_designs.chat_session_id,
+    all_designs.request_text,
+    all_designs.verticals,
+    all_designs.source_shopify_id,
+    all_designs.source_dw_sku,
+    all_designs.source_url,
+    all_designs.gd_score,
+    all_designs.gd_critique,
+    all_designs.id_score,
+    all_designs.id_critique,
+    all_designs.combined_score,
+    all_designs.rating_summary,
+    all_designs.rated_at,
+    all_designs.user_score_avg,
+    all_designs.user_vote_count,
+    all_designs.poll_wins,
+    all_designs.poll_losses,
+    all_designs.elo,
+    all_designs.room_mockups,
+    all_designs.user_removed,
+    all_designs.user_color_good,
+    all_designs.user_style_good,
+    all_designs.user_scale_good,
+    all_designs.user_stars,
+    all_designs.user_rated_at,
+    all_designs.ai_title,
+    all_designs.ai_pattern,
+    all_designs.ai_color_name,
+    all_designs.dig_number,
+    all_designs.ai_reviewed_at,
+    all_designs.ai_review_raw,
+    all_designs.spoonflower_design_id,
+    all_designs.spoonflower_url,
+    all_designs.spoonflower_uploaded_at,
+    all_designs.shopify_product_id,
+    all_designs.shopify_product_url,
+    all_designs.shopify_uploaded_at,
+    all_designs.settlement_verdict,
+    all_designs.inspired_by_sku,
+    all_designs.inspired_by_vendor,
+    all_designs.user_prompt_match,
+    all_designs.user_prompt_verdict,
+    all_designs.user_prompt_rated_at
+   FROM all_designs;
+
+COMMIT;
← 89e036b batch-regen: 2-design concurrency pool (DTD-C), deterministi · back to Wallco Ai · scope dw_auth admin cookie to .wallco.ai so admin login carr f1f5191 →