← back to Wallco Ai
db: add 003_schema_parity migration to bring prod in line with Mac2
628c81f666ebfeb516883608731bb49123fa0f9e · 2026-05-14 09:40:52 -0700 · SteveStudio2
Adds 18 columns to spoon_all_designs that exist on Mac2 dw_unified but
not on prod's. Discovered today during /designs surprise-me debug —
chat-driven INSERTs were silently failing because:
(a) prod's spoon_all_designs lacked dig_number (and 17 others)
(b) the BEFORE INSERT trigger fn_spoon_all_designs_assign_dig referenced
NEW.dig_number, so PG raised 'record "new" has no field' at trigger
compile time
(c) psql -q exited 0 with empty stdout when the trigger erred on stderr,
so chat.js parsed RETURNING id as NaN and returned {id: null}
All ALTER TABLE are IF NOT EXISTS; trigger DROP/CREATE wrapped so re-running
is safe. Also includes backfill UPDATE for dig_number on existing rows.
To apply on prod (pending Steve approval — YOLO mode disallows blind prod
writes):
scp db/migrations/003_schema_parity.sql root@45.61.58.125:/tmp/
ssh root@45.61.58.125 'sudo -n -u postgres psql dw_unified -f /tmp/003_schema_parity.sql'
Local Mac2 already has all these columns — applying is a no-op there.
Files touched
A db/migrations/003_schema_parity.sql
Diff
commit 628c81f666ebfeb516883608731bb49123fa0f9e
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Thu May 14 09:40:52 2026 -0700
db: add 003_schema_parity migration to bring prod in line with Mac2
Adds 18 columns to spoon_all_designs that exist on Mac2 dw_unified but
not on prod's. Discovered today during /designs surprise-me debug —
chat-driven INSERTs were silently failing because:
(a) prod's spoon_all_designs lacked dig_number (and 17 others)
(b) the BEFORE INSERT trigger fn_spoon_all_designs_assign_dig referenced
NEW.dig_number, so PG raised 'record "new" has no field' at trigger
compile time
(c) psql -q exited 0 with empty stdout when the trigger erred on stderr,
so chat.js parsed RETURNING id as NaN and returned {id: null}
All ALTER TABLE are IF NOT EXISTS; trigger DROP/CREATE wrapped so re-running
is safe. Also includes backfill UPDATE for dig_number on existing rows.
To apply on prod (pending Steve approval — YOLO mode disallows blind prod
writes):
scp db/migrations/003_schema_parity.sql root@45.61.58.125:/tmp/
ssh root@45.61.58.125 'sudo -n -u postgres psql dw_unified -f /tmp/003_schema_parity.sql'
Local Mac2 already has all these columns — applying is a no-op there.
---
db/migrations/003_schema_parity.sql | 61 +++++++++++++++++++++++++++++++++++++
1 file changed, 61 insertions(+)
diff --git a/db/migrations/003_schema_parity.sql b/db/migrations/003_schema_parity.sql
new file mode 100644
index 0000000..64ad2a3
--- /dev/null
+++ b/db/migrations/003_schema_parity.sql
@@ -0,0 +1,61 @@
+-- 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();
← a188292 feat(/designs surprise-me): defensive null-id handling + 'Tr
·
back to Wallco Ai
·
feat(/designs): kbd '/' hint inside search input — discovera 53149e4 →