`db/migrations/003_schema_parity.sql`

62 lines
-- 003_schema_parity.sql — bring prod's spoon_all_designs in line with Mac2.
--
-- Discovered 2026-05-14 during /designs surprise-me debug: prod was missing
-- 18 columns vs the local Mac2 dw_unified, causing chat-driven INSERTs to
-- silently fail (RETURNING id came back empty because the BEFORE INSERT
-- trigger fn_spoon_all_designs_assign_dig referenced a column that didn't
-- exist on prod, and `psql -q` returned exit-0 with empty stdout, so the
-- chat layer surfaced {id: null} to the client).
--
-- All ALTER TABLE ... ADD COLUMN are IF NOT EXISTS — safe on Mac2 (no-op)
-- and on any future prod replay.
--
-- After applying, retest with:
--   curl -X POST https://wallco.ai/api/chat/catalog \
--     -H "Content-Type: application/json" \
--     -d '{"message":"give me a navy and gold wallpaper with celestial motif"}'
-- Expected: 200, design.id is a real bigint, /design/<id> opens.

ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS dig_number              TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS ai_title                TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS ai_pattern              TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS ai_color_name           TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS ai_reviewed_at          TIMESTAMPTZ;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS ai_review_raw           JSONB;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS room_mockups            JSONB;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS spoonflower_design_id   TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS spoonflower_url         TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS spoonflower_uploaded_at TIMESTAMPTZ;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS shopify_product_id      TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS shopify_product_url     TEXT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS shopify_uploaded_at     TIMESTAMPTZ;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS user_color_good         BOOLEAN;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS user_style_good         BOOLEAN;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS user_scale_good         BOOLEAN;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS user_stars              SMALLINT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS user_rated_at           TIMESTAMPTZ;

-- Backfill dig_number for any pre-migration row that didn't have one yet.
-- Matches the namespace used by /api/design/:id/ai-designer and the trigger.
UPDATE spoon_all_designs
   SET dig_number = 'DIG_AI_' || lpad(id::text, 6, '0')
 WHERE dig_number IS NULL OR dig_number = '';

-- Verify the trigger function is fresh (recreating is safe — DROP IF EXISTS
-- inside the migration handles already-applied case). The trigger references
-- NEW.dig_number, so it can't compile until the column exists above.
CREATE OR REPLACE FUNCTION fn_spoon_all_designs_assign_dig()
RETURNS trigger AS $$
BEGIN
  IF NEW.dig_number IS NULL OR NEW.dig_number = '' THEN
    NEW.dig_number := 'DIG_AI_' || lpad(NEW.id::text, 6, '0');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_spoon_all_designs_assign_dig ON spoon_all_designs;
CREATE TRIGGER trg_spoon_all_designs_assign_dig
  BEFORE INSERT ON spoon_all_designs
  FOR EACH ROW
  EXECUTE FUNCTION fn_spoon_all_designs_assign_dig();