color-index: build from live shopify_products (active + online_store_published) — drop archived/unpublished handles so PDP color grid never links to 404s or dead images

61eee41b8c3285c1c4f59a7da39295dc6fb144db · 2026-07-15 16:30:51 -0700 · Steve Abrams
Files touched

M scripts/build-color-index.cjs
Diff

commit 61eee41b8c3285c1c4f59a7da39295dc6fb144db
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Jul 15 16:30:51 2026 -0700

    color-index: build from live shopify_products (active + online_store_published) — drop archived/unpublished handles so PDP color grid never links to 404s or dead images
---
 scripts/build-color-index.cjs | 50 ++++++++++++++++++++++++++++---------------
 1 file changed, 33 insertions(+), 17 deletions(-)

diff --git a/scripts/build-color-index.cjs b/scripts/build-color-index.cjs
index 1bc9bf5..76b4bcd 100644
--- a/scripts/build-color-index.cjs
+++ b/scripts/build-color-index.cjs
@@ -4,10 +4,15 @@
  * color-dot "index of many products within 10% tolerance" feature.
  *
  * Source of truth: dw_unified.product_colors (one dominant color per product,
- * with a PRECOMPUTED CIELAB lab_l/a/b) joined to the live storefront handle +
- * image. We emit ONE row per ACTIVE product with a real hex + LAB + handle +
- * image, so the /apps/color-index endpoint can do a pure perceptual ΔE filter
- * with no DB dependency at request time.
+ * with a PRECOMPUTED CIELAB lab_l/a/b) for the COLOR, JOINED to dw_unified.
+ * shopify_products for LIVE TRUTH (current status + current featured image).
+ * product_colors.status/image_url are populated by manual per-vendor extracts
+ * and go stale (an archived product can still read status='active' with a dead
+ * CDN image_url), which used to leak broken cards + 404 links into the PDP color
+ * grid. We therefore gate on shopify_products.status='active' (synced from live
+ * Shopify) and prefer its current image_url. We emit ONE row per active product
+ * with a real hex + LAB + handle + image, so the /apps/color-index endpoint can
+ * do a pure perceptual ΔE filter with no DB dependency at request time.
  *
  * Output: data/color-index.json  → { generated_at, count, items:[
  *   { h:"handle", t:"title", v:"vendor", x:"#rrggbb", l:LAB_L, a:LAB_A, b:LAB_B, i:"image_url" } ] }
@@ -32,20 +37,31 @@ const OUT = path.join(__dirname, '..', 'data', 'color-index.json');
 
 (async () => {
   const t0 = Date.now();
-  // status is case-mixed in this table ('ACTIVE' + 'active'); match case-insensitively.
-  // One row per handle: product_colors already carries one dominant color per product,
-  // but guard against dupes with DISTINCT ON (handle) keeping the highest-lightness…
-  // actually keep the first — any dominant color for the product is fine.
+  // Gate on LIVE truth: only handles that are active in shopify_products (synced
+  // from live Shopify) survive, so archived/deleted products can't leak a dead
+  // handle (404 link) or a stale image into the index. status is case-mixed
+  // ('ACTIVE' + 'active'); match case-insensitively. shopify_products is one row
+  // per variant, so DISTINCT ON (pc.handle) collapses to one row per product,
+  // keeping the most-recently-synced variant's featured image. The displayed
+  // image prefers the fresh shopify_products.image_url, falling back to the
+  // color-extract's image_url only if the mirror lacks one.
   const q = `
-    SELECT DISTINCT ON (handle)
-      handle, title, vendor, hex, lab_l, lab_a, lab_b, image_url
-    FROM product_colors
-    WHERE lower(status) = 'active'
-      AND lab_l IS NOT NULL AND lab_a IS NOT NULL AND lab_b IS NOT NULL
-      AND handle IS NOT NULL AND handle <> ''
-      AND image_url IS NOT NULL AND image_url <> ''
-      AND hex ~* '^#?[0-9a-f]{6}$'
-    ORDER BY handle, updated_at DESC NULLS LAST
+    SELECT DISTINCT ON (pc.handle)
+      pc.handle, pc.title, pc.vendor, pc.hex, pc.lab_l, pc.lab_a, pc.lab_b,
+      COALESCE(NULLIF(sp.image_url, ''), pc.image_url) AS image_url
+    FROM product_colors pc
+    JOIN shopify_products sp ON sp.handle = pc.handle
+    WHERE lower(sp.status) = 'active'
+      -- Must be PUBLISHED to the Online Store, not merely active in admin: an
+      -- active-but-unpublished product 404s on the storefront, so its /products/
+      -- <handle> link would be dead. online_store_published = live-visible.
+      AND sp.online_store_published = true
+      AND pc.lab_l IS NOT NULL AND pc.lab_a IS NOT NULL AND pc.lab_b IS NOT NULL
+      AND pc.handle IS NOT NULL AND pc.handle <> ''
+      AND COALESCE(NULLIF(sp.image_url, ''), pc.image_url) IS NOT NULL
+      AND COALESCE(NULLIF(sp.image_url, ''), pc.image_url) <> ''
+      AND pc.hex ~* '^#?[0-9a-f]{6}$'
+    ORDER BY pc.handle, sp.updated_at_shopify DESC NULLS LAST
   `;
   const { rows } = await pool.query(q);
   await pool.end();
← dd6e416 chore: session close snapshot (visual-search) · back to Dw Photo Capture · deps: add pg so build-color-index runs standalone (needed fo 91ce171 →