← back to Wallco Ai

sql migration: add all_designs.inspired_by_sku + inspired_by_vendor

be80861fb1d4c06d4bfd07d26e309a4cc6933f97 · 2026-05-19 23:49:51 -0700 · Steve Abrams

Idempotent migration adds two ADMIN-ONLY columns so the new
inspiration_generator.js pipeline can record which archival vendor
product each AI-generated wallco design was inspired by.

inspired_by_vendor never surfaces on public /design/:id pages — vendor
identity stays in DB + /admin/inspirations only.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

Files touched

• A sql/2026-05-20-add-inspired-by.sql

Diff

commit be80861fb1d4c06d4bfd07d26e309a4cc6933f97
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue May 19 23:49:51 2026 -0700

    sql migration: add all_designs.inspired_by_sku + inspired_by_vendor
    
    Idempotent migration adds two ADMIN-ONLY columns so the new
    inspiration_generator.js pipeline can record which archival vendor
    product each AI-generated wallco design was inspired by.
    
    inspired_by_vendor never surfaces on public /design/:id pages — vendor
    identity stays in DB + /admin/inspirations only.
    
    Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
 sql/2026-05-20-add-inspired-by.sql | 61 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 61 insertions(+)

diff --git a/sql/2026-05-20-add-inspired-by.sql b/sql/2026-05-20-add-inspired-by.sql
new file mode 100644
index 0000000..2a2ef9e
--- /dev/null
+++ b/sql/2026-05-20-add-inspired-by.sql
@@ -0,0 +1,61 @@
+-- 2026-05-20 — Inspiration pipeline (inspired-by-real-vendor seeds)
+--
+-- Adds two ADMIN-ONLY metadata columns to all_designs so the new
+-- scripts/inspiration_generator.js can record which archival vendor product
+-- each generated wallco design was inspired by.
+--
+-- HARD RULES:
+--   1. inspired_by_vendor is ADMIN-ONLY — never expose on public surfaces.
+--   2. inspired_by_sku is the vendor's SKU (e.g. Shopify SKU, manufacturer
+--      mfr_sku). Stored verbatim from shopify_products.sku / mfr_sku.
+--   3. Idempotent — re-applying this file is a no-op.
+
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM pg_attribute
+    WHERE attrelid = 'all_designs'::regclass
+      AND attname  = 'inspired_by_sku'
+      AND NOT attisdropped
+  ) THEN
+    ALTER TABLE all_designs ADD COLUMN inspired_by_sku TEXT;
+    RAISE NOTICE 'added all_designs.inspired_by_sku';
+  ELSE
+    RAISE NOTICE 'all_designs.inspired_by_sku already exists — skipping';
+  END IF;
+
+  IF NOT EXISTS (
+    SELECT 1 FROM pg_attribute
+    WHERE attrelid = 'all_designs'::regclass
+      AND attname  = 'inspired_by_vendor'
+      AND NOT attisdropped
+  ) THEN
+    ALTER TABLE all_designs ADD COLUMN inspired_by_vendor TEXT;
+    RAISE NOTICE 'added all_designs.inspired_by_vendor';
+  ELSE
+    RAISE NOTICE 'all_designs.inspired_by_vendor already exists — skipping';
+  END IF;
+END$$;
+
+-- Lookup index — find every wallco design inspired by a given vendor SKU.
+CREATE INDEX IF NOT EXISTS idx_all_designs_inspired_by_sku
+  ON all_designs(inspired_by_sku)
+  WHERE inspired_by_sku IS NOT NULL;
+
+-- Recreate the spoon_all_designs view so the new columns are visible
+-- through the canonical alias used by server.js + admin.js + scripts.
+-- (View must be re-built when its base table gets new columns.)
+DO $$
+DECLARE
+  view_exists BOOLEAN;
+BEGIN
+  SELECT EXISTS(
+    SELECT 1 FROM information_schema.views WHERE table_name = 'spoon_all_designs'
+  ) INTO view_exists;
+  IF view_exists THEN
+    DROP VIEW spoon_all_designs;
+    RAISE NOTICE 'dropped existing spoon_all_designs view';
+  END IF;
+  EXECUTE 'CREATE VIEW spoon_all_designs AS SELECT * FROM all_designs';
+  RAISE NOTICE 'recreated spoon_all_designs view including inspired_by_* columns';
+END$$;

← 6e30ae3 add /library surface data — materialize 57,629 real designer  ·  back to Wallco Ai  ·  wallco: cactus collection generator + publisher (10 Omni Tuc f90b1a4 →