`migrations/20260601_prompt_match_columns.sql`

108 lines
-- 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;