backfill-canonical-colors.js — push canonical color_1/2/3 metafields for all 54,884 active products from DB ai_colors (designer names mapped from hex, handles pct/percentage formats). $0, no image processing. Makes search-by-color comprehensive (was ~9% metafield coverage).

0e985f9747f738828e28abe12a9043195fd6e22c · 2026-06-13 22:52:35 -0700 · Steve Abrams
Files touched

A DW-Programming/backfill-canonical-colors.js
Diff

commit 0e985f9747f738828e28abe12a9043195fd6e22c
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat Jun 13 22:52:35 2026 -0700

    backfill-canonical-colors.js — push canonical color_1/2/3 metafields for all 54,884 active products from DB ai_colors (designer names mapped from hex, handles pct/percentage formats). $0, no image processing. Makes search-by-color comprehensive (was ~9% metafield coverage).
---
 DW-Programming/backfill-canonical-colors.js | 91 +++++++++++++++++++++++++++++
 1 file changed, 91 insertions(+)

diff --git a/DW-Programming/backfill-canonical-colors.js b/DW-Programming/backfill-canonical-colors.js
new file mode 100644
index 00000000..999269aa
--- /dev/null
+++ b/DW-Programming/backfill-canonical-colors.js
@@ -0,0 +1,91 @@
+#!/usr/bin/env node
+/**
+ * Backfill canonical search-by-color metafields for ACTIVE products from the color
+ * data ALREADY in the DB (vendor ai_colors) — no image processing, just pushing.
+ * Reads color_backfill_todo (numeric_id, ai_colors), parses the stored breakdown,
+ * writes custom.color_{1,2,3}_hex/_percentage/_name (designer name mapped from hex)
+ * + custom.color_hex + custom.color_details via Admin GraphQL. Resumable. $0.
+ * Usage: node backfill-canonical-colors.js [--limit N] [--conc 8] [--dry-run]
+ */
+const https = require('https');
+const { Pool } = require('pg');
+
+const SHOPIFY_DOMAIN = 'designer-laboratory-sandbox.myshopify.com';
+const API_VERSION = '2024-01';
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+const DB_URL = process.env.TODO_DB_URL || 'postgres://stevestudio2@/dw_unified?host=/tmp';
+const args = process.argv.slice(2);
+const DRY = args.includes('--dry-run');
+const LIMIT = parseInt(args.find((_, i, a) => a[i-1] === '--limit') || '0', 10) || 0;
+const CONC = parseInt(args.find((_, i, a) => a[i-1] === '--conc') || '8', 10) || 8;
+const pool = new Pool({ connectionString: DB_URL });
+
+// Designer palette (name -> hex) — same set as fetch-colors-designer.js / local-palette.py.
+const DESIGNER = {White:'#FFFFFF','Off White':'#F5F3EE',Ivory:'#FFFAF0',Eggshell:'#F0EADA',Alabaster:'#EDEAE0',Cream:'#F5EEDC',Vanilla:'#F3E5C2',Bone:'#E3DAC9',Linen:'#EBE4D7',Pearl:'#EEECE6',Ecru:'#CDC2A8',Oatmeal:'#DCD2BE',Beige:'#E0D2B4',Sand:'#D6C49E',Wheat:'#D7C8A0',Khaki:'#BDB76B',Greige:'#BEB4A5',Mushroom:'#B4A89B',Taupe:'#A59687',Putty:'#BEB29B',Stone:'#BEB9AF',Camel:'#C19A6B',Tan:'#D2B48C',Fawn:'#C8B496','Dove Gray':'#BEBCB9','Light Gray':'#CDCDCD',Gray:'#828282',Silver:'#C0C0C4',Ash:'#B2B2AF',Smoke:'#87878A',Pewter:'#949496',Steel:'#8C96A0',Slate:'#6C7680',Graphite:'#4A4A50',Charcoal:'#3A3A3F',Gunmetal:'#50555F',Black:'#181818',Onyx:'#26262A',Ebony:'#2D2826',Jet:'#1E1E1E',Ink:'#1C1E28',Brown:'#6E4B32',Chocolate:'#4A3223',Espresso:'#372A22',Coffee:'#5A4637',Mocha:'#785F4B',Walnut:'#5F412D',Chestnut:'#825037',Caramel:'#AF7D4B',Cognac:'#965A37',Tobacco:'#785537',Umber:'#705037',Sienna:'#A05A3C',Bronze:'#8C6E46',Copper:'#B26E4B',Navy:'#232955',Midnight:'#1E233C',Indigo:'#37326E',Cobalt:'#2846A0','Royal Blue':'#324BB4',Sapphire:'#283C8C',Blue:'#375AAA',Denim:'#506E96',Cornflower:'#82A0DC',Cerulean:'#3C82BE','Sky Blue':'#8CB4E1','Powder Blue':'#BCD0E4','Slate Blue':'#5F6E8C',Teal:'#287882',Peacock:'#286E78',Turquoise:'#5ABEBE',Aqua:'#87C8C8',Green:'#468246',Emerald:'#288C5A','Forest Green':'#2D5837','Hunter Green':'#32553C',Juniper:'#4B6E5A',Olive:'#6E733C',Moss:'#788250',Fern:'#648C5A',Sage:'#9EA88E',Celadon:'#AFC3A5',Seafoam:'#A0CDB4',Mint:'#B2D8BC',Pistachio:'#ACC88C',Chartreuse:'#B4C85A',Yellow:'#F0DC46',Lemon:'#F5E678',Butter:'#F5E49B',Champagne:'#EBDCB9',Gold:'#D4AF37',Brass:'#B4965A',Honey:'#DCAA50',Amber:'#D29637',Mustard:'#C8A028',Ochre:'#BE913C',Saffron:'#D7A02A',Marigold:'#E6AA32',Orange:'#E68C32',Tangerine:'#EB963C',Pumpkin:'#D77832','Burnt Orange':'#BE642D',Terracotta:'#C86E4B',Clay:'#B67055',Rust:'#A85537',Paprika:'#B45037',Apricot:'#F0B68C',Peach:'#F5BEA0',Red:'#BE2D2D',Scarlet:'#C8322D',Crimson:'#B42837',Cherry:'#B42D3C',Brick:'#A04837',Ruby:'#9B2841',Garnet:'#7A2837',Wine:'#73233C',Burgundy:'#782837',Maroon:'#6E2832',Oxblood:'#64262A',Pink:'#EB96AF',Blush:'#F0CACA',Petal:'#F5D2D7',Rose:'#D2788C','Dusty Rose':'#C6969B',Salmon:'#EB9682',Coral:'#F0806E',Mauve:'#B48CA0',Peony:'#E182A0',Fuchsia:'#C83C8C',Magenta:'#BE3C82',Purple:'#784696',Violet:'#825AB4',Amethyst:'#8C64AA',Orchid:'#BE78BE',Lavender:'#C0ACD8',Lilac:'#C8B0D4',Wisteria:'#AC98CA',Heather:'#A58EAC',Plum:'#6E3C5F',Aubergine:'#482A48',Eggplant:'#4C2E4C'};
+const D_RGB = Object.entries(DESIGNER).map(([name,hex]) => ({ name, rgb: [parseInt(hex.slice(1,3),16),parseInt(hex.slice(3,5),16),parseInt(hex.slice(5,7),16)] }));
+function designerName(hex){
+  const h=(hex||'').replace('#',''); if(h.length<6) return null;
+  const rgb=[parseInt(h.slice(0,2),16),parseInt(h.slice(2,4),16),parseInt(h.slice(4,6),16)];
+  if(rgb.some(isNaN)) return null;
+  let best=null,bd=1e18; for(const d of D_RGB){const dd=(rgb[0]-d.rgb[0])**2+(rgb[1]-d.rgb[1])**2+(rgb[2]-d.rgb[2])**2; if(dd<bd){bd=dd;best=d.name;}} return best;
+}
+function normHex(x){ if(!x) return null; let h=String(x).trim(); if(!h.startsWith('#')) h='#'+h; return /^#[0-9A-Fa-f]{6}$/.test(h) ? h.toUpperCase() : null; }
+function parseColors(ac){
+  let arr; try{ arr=typeof ac==='string'?JSON.parse(ac):ac; }catch{ return []; }
+  if(!Array.isArray(arr)) return [];
+  return arr.map(c=>{
+    const hex=normHex(c.hex||c.color||c.value); if(!hex) return null;
+    const pct=parseFloat(c.percentage!=null?c.percentage:(c.pct!=null?c.pct:(c.percent!=null?c.percent:0)))||0;
+    return { name: designerName(hex)||c.name||'', hex, percentage: pct };
+  }).filter(Boolean).sort((a,b)=>b.percentage-a.percentage);
+}
+function shopifyGraphQL(query, variables){
+  return new Promise((resolve,reject)=>{
+    const body=JSON.stringify({query,variables});
+    const req=https.request({hostname:SHOPIFY_DOMAIN,path:`/admin/api/${API_VERSION}/graphql.json`,method:'POST',
+      headers:{'Content-Type':'application/json','X-Shopify-Access-Token':TOKEN,'Content-Length':Buffer.byteLength(body)},timeout:30000},res=>{
+      let d='';res.on('data',c=>d+=c);res.on('end',()=>{try{resolve(JSON.parse(d));}catch(e){reject(e);}});
+    });
+    req.on('error',reject);req.on('timeout',()=>{req.destroy();reject(new Error('gql timeout'));});req.write(body);req.end();
+  });
+}
+async function write(numericId, colors){
+  const owner=`gid://shopify/Product/${numericId}`;
+  const mf=[
+    {ownerId:owner,namespace:'custom',key:'color_hex',type:'single_line_text_field',value:colors[0].hex},
+    {ownerId:owner,namespace:'custom',key:'color_details',type:'json',value:JSON.stringify(colors)},
+  ];
+  for(let i=0;i<3;i++){ const c=colors[i]; if(!c) break;
+    mf.push({ownerId:owner,namespace:'custom',key:`color_${i+1}_hex`,type:'single_line_text_field',value:c.hex});
+    mf.push({ownerId:owner,namespace:'custom',key:`color_${i+1}_percentage`,type:'number_decimal',value:String(c.percentage)});
+    mf.push({ownerId:owner,namespace:'custom',key:`color_${i+1}_name`,type:'single_line_text_field',value:c.name});
+  }
+  const r=await shopifyGraphQL(`mutation($mf:[MetafieldsSetInput!]!){ metafieldsSet(metafields:$mf){ userErrors{message} } }`,{mf});
+  const ue=r?.data?.metafieldsSet?.userErrors; if(ue&&ue.length) throw new Error(ue[0].message);
+  if(r?.errors) throw new Error(JSON.stringify(r.errors).slice(0,120));
+}
+(async()=>{
+  if(!TOKEN){console.error('Missing SHOPIFY_ADMIN_TOKEN');process.exit(1);}
+  const {rows}=await pool.query(`SELECT numeric_id, ai_colors FROM color_backfill_todo WHERE done_at IS NULL ${LIMIT?'LIMIT '+LIMIT:''}`);
+  console.log(`Backfill — ${DRY?'DRY':'LIVE'} — ${rows.length} products, conc ${CONC}`);
+  let done=0,failed=0,idx=0; const queue=[...rows];
+  async function worker(){
+    while(queue.length){
+      const p=queue.shift(); const n=++idx;
+      const colors=parseColors(p.ai_colors);
+      if(!colors.length){ failed++; await pool.query(`UPDATE color_backfill_todo SET err='no-colors' WHERE numeric_id=$1`,[p.numeric_id]); continue; }
+      try{
+        if(!DRY) await write(p.numeric_id, colors);
+        await pool.query(`UPDATE color_backfill_todo SET done_at=now(), err=NULL WHERE numeric_id=$1`,[p.numeric_id]); done++;
+        if(n<=8||n%500===0) console.log(`  [${n}] OK ${p.numeric_id} ${colors[0].hex} ${colors[0].name} (${colors.length})`);
+      }catch(e){
+        failed++; await pool.query(`UPDATE color_backfill_todo SET err=$2 WHERE numeric_id=$1`,[p.numeric_id,String(e.message).slice(0,80)]);
+        if(String(e.message).match(/Throttled|429/)) await new Promise(r=>setTimeout(r,3000));
+        if(n%200===0) console.log(`  [${n}] ERR ${p.numeric_id}: ${e.message}`);
+      }
+    }
+  }
+  await Promise.all(Array.from({length:CONC},worker));
+  console.log(`\nBackfill done: ${done} written, ${failed} failed.`);
+  await pool.end();
+})().catch(e=>{console.error(e);process.exit(1);});
← 29127900 enricher: also write CANONICAL search-by-color schema (custo · back to Designer Wallcoverings · sweep-to-100.js — drives search-by-color coverage to 100%: s a96bd4e7 →