← back to Designer Wallcoverings
color enrichment: nightly new-SKU auto-run ($0 local palette → color_details + color_N metafields) + product_colors sync bridge for shop-by-color + launchd plist (install gated)
134f9a711ea067edb67311bafd392ec534b7b786 · 2026-07-15 17:02:08 -0700 · Steve Abrams
Files touched
A DW-Programming/color-enrich-newskus.shA DW-Programming/launchd/com.steve.color-enrich-newskus.plistA DW-Programming/sync-enrich-to-product-colors.js
Diff
commit 134f9a711ea067edb67311bafd392ec534b7b786
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 15 17:02:08 2026 -0700
color enrichment: nightly new-SKU auto-run ($0 local palette → color_details + color_N metafields) + product_colors sync bridge for shop-by-color + launchd plist (install gated)
---
DW-Programming/color-enrich-newskus.sh | 38 ++++++++
.../launchd/com.steve.color-enrich-newskus.plist | 32 +++++++
DW-Programming/sync-enrich-to-product-colors.js | 103 +++++++++++++++++++++
3 files changed, 173 insertions(+)
diff --git a/DW-Programming/color-enrich-newskus.sh b/DW-Programming/color-enrich-newskus.sh
new file mode 100755
index 00000000..c1cd9624
--- /dev/null
+++ b/DW-Programming/color-enrich-newskus.sh
@@ -0,0 +1,38 @@
+#!/usr/bin/env bash
+# color-enrich-newskus.sh — nightly self-maintaining color enrichment.
+#
+# Ensures EVERY new SKU gets hex# + full color palette (names + percentages):
+# 1. Enqueue any active+published SKU never seen in color_enrich_todo.
+# 2. Run the LOCAL ($0, no model) palette enricher, which writes the Shopify
+# metafields custom.color_details (json [{name,hex,percentage}]) +
+# custom.color_{1,2,3}_hex / _percentage / _name that the PDP "Colors In
+# This Pattern" widget reads.
+#
+# $0 (local median-cut). Resumable. Safe to run nightly.
+set -euo pipefail
+cd "$(dirname "$0")"
+export PATH="/opt/homebrew/bin:/usr/local/bin:/usr/bin:/bin"
+export NODE_PATH="$HOME/Projects/all-designerwallcoverings/node_modules"
+export SHOPIFY_ADMIN_TOKEN="$(grep -E '^SHOPIFY_ADMIN_TOKEN=' "$HOME/Projects/secrets-manager/.env" | head -1 | cut -d= -f2- | tr -d '"'\'' \r')"
+
+echo "[$(date '+%F %T')] enqueue new never-enriched active+published SKUs…"
+psql -h /tmp -d dw_unified -c "
+INSERT INTO color_enrich_todo (numeric_id, image_url, title)
+SELECT numeric_id, image_url, title FROM (
+ SELECT DISTINCT ON (substring(sp.shopify_id from '[0-9]+\$'))
+ substring(sp.shopify_id from '[0-9]+\$') AS numeric_id, sp.image_url, sp.title
+ FROM shopify_products sp
+ WHERE lower(sp.status)='active' AND sp.online_store_published AND sp.image_url LIKE 'http%'
+ ORDER BY substring(sp.shopify_id from '[0-9]+\$'), sp.updated_at_shopify DESC NULLS LAST
+) t
+WHERE t.numeric_id IS NOT NULL
+ AND t.numeric_id NOT IN (SELECT numeric_id FROM color_enrich_todo WHERE numeric_id IS NOT NULL);
+"
+
+echo "[$(date '+%F %T')] running local palette enricher on all pending…"
+node enrich-active-colors.js --conc 5
+
+echo "[$(date '+%F %T')] syncing new colors into product_colors (shop-by-color source)…"
+node sync-enrich-to-product-colors.js || echo " (sync step not present yet — skipping)"
+
+echo "[$(date '+%F %T')] new-sku color enrich complete."
diff --git a/DW-Programming/launchd/com.steve.color-enrich-newskus.plist b/DW-Programming/launchd/com.steve.color-enrich-newskus.plist
new file mode 100644
index 00000000..958ff00b
--- /dev/null
+++ b/DW-Programming/launchd/com.steve.color-enrich-newskus.plist
@@ -0,0 +1,32 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!-- Nightly self-maintaining color enrichment for NEW SKUs (04:15 local).
+ DRAFT — install with:
+ cp .../DW-Programming/launchd/com.steve.color-enrich-newskus.plist ~/Library/LaunchAgents/
+ launchctl bootstrap gui/$(id -u) ~/Library/LaunchAgents/com.steve.color-enrich-newskus.plist
+ Enqueues any never-enriched active+published SKU and runs the $0 local
+ palette enricher → custom.color_details + color_N metafields (hex + %). -->
+<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
+<plist version="1.0">
+<dict>
+ <key>Label</key>
+ <string>com.steve.color-enrich-newskus</string>
+ <key>ProgramArguments</key>
+ <array>
+ <string>/bin/bash</string>
+ <string>/Users/macstudio3/Projects/Designer-Wallcoverings/DW-Programming/color-enrich-newskus.sh</string>
+ </array>
+ <key>WorkingDirectory</key>
+ <string>/Users/macstudio3/Projects/Designer-Wallcoverings/DW-Programming</string>
+ <key>StartCalendarInterval</key>
+ <dict>
+ <key>Hour</key><integer>4</integer>
+ <key>Minute</key><integer>15</integer>
+ </dict>
+ <key>RunAtLoad</key>
+ <false/>
+ <key>StandardOutPath</key>
+ <string>/Users/macstudio3/Projects/Designer-Wallcoverings/DW-Programming/logs/color-enrich-newskus.log</string>
+ <key>StandardErrorPath</key>
+ <string>/Users/macstudio3/Projects/Designer-Wallcoverings/DW-Programming/logs/color-enrich-newskus.log</string>
+</dict>
+</plist>
diff --git a/DW-Programming/sync-enrich-to-product-colors.js b/DW-Programming/sync-enrich-to-product-colors.js
new file mode 100755
index 00000000..4ed1e775
--- /dev/null
+++ b/DW-Programming/sync-enrich-to-product-colors.js
@@ -0,0 +1,103 @@
+#!/usr/bin/env node
+/**
+ * sync-enrich-to-product-colors.js
+ *
+ * Bridges the color enrichment (color_enrich_todo, populated by
+ * enrich-active-colors.js via the local $0 palette) into product_colors — the
+ * table the shop-by-color index (build-color-index.cjs → /apps/color-index)
+ * reads. enrich-active-colors writes the Shopify metafields (PDP palette) but
+ * NOT product_colors, so without this bridge the newly-colored SKUs never show
+ * up in shop-by-color.
+ *
+ * For every done color_enrich_todo row with a hex, joined to its live
+ * shopify_products row (active + published), upsert one product_colors row with
+ * the dominant hex, precomputed CIELAB, and hex-derived color_tags.
+ *
+ * $0 (local PG only). Idempotent + resumable (ON CONFLICT upsert).
+ * node sync-enrich-to-product-colors.js [--limit N]
+ */
+const { Pool } = require('pg');
+const args = process.argv.slice(2);
+const LIMIT = parseInt(args.find((_, i, a) => a[i - 1] === '--limit') || '0', 10) || 0;
+const pool = new Pool({ connectionString: process.env.TODO_DB_URL || 'postgres://stevestudio2@/dw_unified?host=/tmp' });
+
+function hexToRgb(hex) {
+ hex = String(hex).replace('#', '');
+ return { r: parseInt(hex.slice(0, 2), 16), g: parseInt(hex.slice(2, 4), 16), b: parseInt(hex.slice(4, 6), 16) };
+}
+function rgbToLab(r, g, b) {
+ let rr = r / 255, gg = g / 255, bb = b / 255;
+ rr = rr > 0.04045 ? Math.pow((rr + 0.055) / 1.055, 2.4) : rr / 12.92;
+ gg = gg > 0.04045 ? Math.pow((gg + 0.055) / 1.055, 2.4) : gg / 12.92;
+ bb = bb > 0.04045 ? Math.pow((bb + 0.055) / 1.055, 2.4) : bb / 12.92;
+ let x = (rr * 0.4124564 + gg * 0.3575761 + bb * 0.1804375) / 0.95047;
+ let y = (rr * 0.2126729 + gg * 0.7151522 + bb * 0.0721750) / 1.00000;
+ let z = (rr * 0.0193339 + gg * 0.1191920 + bb * 0.9503041) / 1.08883;
+ x = x > 0.008856 ? Math.cbrt(x) : (7.787 * x) + 16 / 116;
+ y = y > 0.008856 ? Math.cbrt(y) : (7.787 * y) + 16 / 116;
+ z = z > 0.008856 ? Math.cbrt(z) : (7.787 * z) + 16 / 116;
+ return { l: (116 * y) - 16, a: 500 * (x - y), b: 200 * (y - z) };
+}
+// Coarse hue→family tags from a single hex — same buckets the vendor hex scripts use.
+function hexToColorTags(hex) {
+ const { r, g, b } = hexToRgb(hex);
+ const mx = Math.max(r, g, b) / 255, mn = Math.min(r, g, b) / 255, l = (mx + mn) / 2;
+ let h = 0, s = 0; const d = mx - mn;
+ if (d) {
+ s = l > 0.5 ? d / (2 - mx - mn) : d / (mx + mn);
+ const R = r / 255, G = g / 255, B = b / 255;
+ if (mx === R) h = ((G - B) / d + (G < B ? 6 : 0)); else if (mx === G) h = ((B - R) / d + 2); else h = ((R - G) / d + 4);
+ h *= 60;
+ }
+ s *= 100; const L = l * 100; const t = [];
+ if (s < 10) { if (L > 88) t.push('White', 'Ivory'); else if (L > 65) t.push('Light Gray', 'Silver'); else if (L > 40) t.push('Gray', 'Neutral Tone'); else if (L > 18) t.push('Charcoal', 'Dark Gray'); else t.push('Black', 'Ebony'); return t; }
+ if (h < 15 || h >= 345) t.push(L > 70 ? 'Pink' : L > 45 ? 'Red' : 'Burgundy', 'Warm Tone');
+ else if (h < 45) t.push(L > 65 ? 'Peach' : L > 40 ? 'Terracotta' : 'Brown', 'Warm Tone');
+ else if (h < 70) t.push(L > 72 ? 'Cream' : L > 50 ? 'Gold' : 'Bronze', 'Warm Tone');
+ else if (h < 90) t.push(L > 70 ? 'Yellow' : 'Olive', 'Warm Tone');
+ else if (h < 150) t.push(L > 60 ? 'Sage' : L > 38 ? 'Green' : 'Forest', 'Cool Tone');
+ else if (h < 200) t.push(L > 60 ? 'Seafoam' : L > 35 ? 'Teal' : 'Deep Teal', 'Cool Tone');
+ else if (h < 255) t.push(L > 68 ? 'Light Blue' : L > 42 ? 'Blue' : 'Navy', 'Cool Tone');
+ else if (h < 300) t.push(L > 65 ? 'Lavender' : L > 40 ? 'Violet' : 'Deep Purple', 'Cool Tone');
+ else t.push(L > 68 ? 'Pink' : L > 45 ? 'Magenta' : 'Plum', 'Warm Tone');
+ return t;
+}
+
+(async () => {
+ const { rows } = await pool.query(`
+ SELECT DISTINCT ON (cet.numeric_id)
+ cet.numeric_id, cet.color_hex, sp.handle, sp.vendor, sp.title, sp.product_type,
+ sp.image_url, sp.status
+ FROM color_enrich_todo cet
+ JOIN shopify_products sp ON substring(sp.shopify_id from '[0-9]+$') = cet.numeric_id
+ LEFT JOIN product_colors pc ON pc.shopify_product_id = cet.numeric_id
+ WHERE cet.done_at IS NOT NULL
+ AND cet.color_hex ~* '^#?[0-9a-f]{6}$'
+ AND lower(sp.status) = 'active' AND sp.online_store_published
+ AND pc.id IS NULL
+ ORDER BY cet.numeric_id, sp.updated_at_shopify DESC NULLS LAST
+ ${LIMIT ? 'LIMIT ' + LIMIT : ''}
+ `);
+ console.log(`sync: ${rows.length} newly-enriched products → product_colors`);
+ let ok = 0;
+ for (let i = 0; i < rows.length; i += 500) {
+ const batch = rows.slice(i, i + 500);
+ for (const r of batch) {
+ const hex = r.color_hex.startsWith('#') ? r.color_hex : '#' + r.color_hex;
+ const { r: rr, g, b } = hexToRgb(hex);
+ const lab = rgbToLab(rr, g, b);
+ const tags = hexToColorTags(hex);
+ await pool.query(`
+ INSERT INTO product_colors (shopify_product_id, vendor, hex, lab_l, lab_a, lab_b, color_tags, source, title, handle, product_type, image_url, status)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,'local-palette',$8,$9,$10,$11,'active')
+ ON CONFLICT (shopify_product_id) DO UPDATE SET
+ hex=EXCLUDED.hex, lab_l=EXCLUDED.lab_l, lab_a=EXCLUDED.lab_a, lab_b=EXCLUDED.lab_b,
+ color_tags=EXCLUDED.color_tags, image_url=EXCLUDED.image_url, status='active', updated_at=NOW()
+ `, [r.numeric_id, r.vendor, hex.toLowerCase(), lab.l, lab.a, lab.b, tags, r.title, r.handle, r.product_type || 'Wallcovering', r.image_url]);
+ ok++;
+ }
+ console.log(` ${Math.min(i + 500, rows.length)}/${rows.length}`);
+ }
+ console.log(`sync done: ${ok} upserted into product_colors`);
+ await pool.end();
+})().catch(e => { console.error('sync FAILED:', e.message); pool.end(); process.exit(1); });
← d2557f10 auto-save: 2026-07-15T16:38:37 (4 files) — pending-approval/
·
back to Designer Wallcoverings
·
auto-save: 2026-07-15T17:08:45 (5 files) — pending-approval/ a6ee3510 →