← back to Designer Wallcoverings

Carl Robinson: harden Shopify SKU update — key on product-id+variant-role, live duplicate-guard, idempotent (prevents GRS/MIC number dupes)

2faa01a52b15498edfb34a5315a73418e0b1879f · 2026-07-06 12:27:18 -0700 · steve@designerwallcoverings.com

Files touched

• M scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs

Diff

commit 2faa01a52b15498edfb34a5315a73418e0b1879f
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Mon Jul 6 12:27:18 2026 -0700

    Carl Robinson: harden Shopify SKU update — key on product-id+variant-role, live duplicate-guard, idempotent (prevents GRS/MIC number dupes)
---
 .../update-shopify-skus-carl-robinson.cjs          | 64 ++++++++++++++--------
 1 file changed, 40 insertions(+), 24 deletions(-)

diff --git a/scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs b/scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs
index b0809821..d939aa70 100644
--- a/scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs
+++ b/scripts/wallquest-refresh/update-shopify-skus-carl-robinson.cjs
@@ -1,7 +1,9 @@
-// Update the 67 live Carl Robinson Shopify products' variant SKUs DWCR-*  →  material-based
-// (DWGRS-/DWPWV-/DWCORK-/DWMIC-…), and re-key dw_sku_registry. Beach-city titles unchanged.
-// Reads /tmp/cr-sku-remap.json (old,new,shopify_product_id). Env: SHOPIFY_ADMIN_TOKEN, DATABASE_URL.
-const fs = require('fs');
+// Set the 67 Carl Robinson products' variant SKUs to the final material-series scheme
+// (GRS-100020…, NET-100042…, MIC-100065). HARDENED against concurrency + duplicates:
+//  • keys off shopify_product_id + variant role (Standard/Sample), NOT a stale "old" SKU
+//  • DUPLICATE GUARD: before setting a SKU, checks no OTHER product already holds it → skip+flag
+//  • idempotent: if a variant already has the target SKU, it's left alone
+// Env: SHOPIFY_ADMIN_TOKEN, DATABASE_URL.
 const https = require('https');
 const { Client } = require('pg');
 
@@ -10,7 +12,6 @@ const API = '2024-10';
 const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
 if (!TOKEN) { console.error('no SHOPIFY_ADMIN_TOKEN'); process.exit(1); }
 const CONN = process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified';
-const map = JSON.parse(fs.readFileSync('/tmp/cr-sku-remap.json', 'utf8')).filter(m => m.shopify_product_id);
 const sleep = ms => new Promise(r => setTimeout(r, ms));
 
 function rest(method, path, body) {
@@ -23,32 +24,47 @@ function rest(method, path, body) {
     if (data) req.write(data); req.end();
   });
 }
+function gql(query) {
+  return new Promise((resolve, reject) => {
+    const body = JSON.stringify({ query });
+    const req = https.request({ hostname: STORE, path: `/admin/api/${API}/graphql.json`, method: 'POST',
+      headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(body) }, timeout: 40000 },
+      res => { let d = ''; res.on('data', c => d += c); res.on('end', () => { try { resolve(JSON.parse(d)); } catch (e) { reject(new Error('bad json')); } }); });
+    req.on('error', reject); req.on('timeout', () => { req.destroy(); reject(new Error('timeout')); });
+    req.write(body); req.end();
+  });
+}
+// returns true if some variant with this sku exists on a DIFFERENT product
+async function skuTakenElsewhere(sku, myPid) {
+  const q = `{ productVariants(first:5, query:"sku:'${sku.replace(/'/g, "")}'"){edges{node{id sku product{id legacyResourceId}}}} }`;
+  const r = await gql(q);
+  const edges = r.data?.productVariants?.edges || [];
+  return edges.some(e => String(e.node.product.legacyResourceId) !== String(myPid));
+}
 
 (async () => {
   const db = new Client({ connectionString: CONN }); await db.connect();
-  let ok = 0, err = 0;
-  for (const m of map) {
+  const { rows } = await db.query("SELECT c.mfr_sku, c.dw_sku, r.shopify_product_id FROM carl_robinson_catalog c JOIN dw_sku_registry r ON r.mfr_sku=c.mfr_sku WHERE r.shopify_product_id IS NOT NULL ORDER BY c.dw_sku");
+  console.log(`checking + updating ${rows.length} products (duplicate-guarded)…`);
+  let set = 0, already = 0, skipped = 0, err = 0;
+  for (const p of rows) {
     try {
-      const g = await rest('GET', `/products/${m.shopify_product_id}.json?fields=id,variants`);
+      const g = await rest('GET', `/products/${p.shopify_product_id}.json?fields=id,variants`);
       const variants = g.j?.product?.variants || [];
-      let changed = 0;
       for (const v of variants) {
-        let nsku = null;
-        if (v.sku === m.old) nsku = m.new;
-        else if (v.sku === `${m.old}-Sample`) nsku = `${m.new}-Sample`;
-        if (nsku && nsku !== v.sku) {
-          const u = await rest('PUT', `/variants/${v.id}.json`, { variant: { id: v.id, sku: nsku } });
-          if (u.status >= 200 && u.status < 300) changed++;
-          else console.log(`  ✗ variant ${v.id} ${v.sku}→${nsku} HTTP ${u.status}`);
-          await sleep(550);
-        }
+        const isSample = /sample/i.test(v.title || v.option1 || '') || /-sample$/i.test(v.sku || '');
+        const target = isSample ? `${p.dw_sku}-Sample` : p.dw_sku;
+        if (v.sku === target) { already++; continue; }
+        if (await skuTakenElsewhere(target, p.shopify_product_id)) { skipped++; console.log(`⚠ DUP GUARD: ${target} already on another product — skipped ${p.mfr_sku}`); await sleep(400); continue; }
+        const u = await rest('PUT', `/variants/${v.id}.json`, { variant: { id: v.id, sku: target } });
+        if (u.status >= 200 && u.status < 300) { set++; console.log(`✓ ${p.mfr_sku} ${isSample ? 'Sample' : 'Standard'} → ${target}`); }
+        else { err++; console.log(`✗ ${p.mfr_sku} → ${target} HTTP ${u.status}`); }
+        await sleep(500);
       }
-      // re-key registry old → new
-      await db.query('UPDATE dw_sku_registry SET dw_sku=$1, updated_at=now() WHERE dw_sku=$2', [m.new, m.old]);
-      ok++; console.log(`✓ ${m.old} → ${m.new} (${changed} variant sku${changed===1?'':'s'}) [${ok}/${map.length}]`);
-    } catch (e) { err++; console.log(`✗ ${m.old} ${String(e).slice(0, 90)}`); }
-    await sleep(300);
+      await db.query("UPDATE dw_sku_registry SET dw_sku=$1, updated_at=now() WHERE mfr_sku=$2", [p.dw_sku, p.mfr_sku]);
+    } catch (e) { err++; console.log(`✗ ${p.mfr_sku} ${String(e).slice(0, 90)}`); }
+    await sleep(200);
   }
-  console.log(`\nSKU UPDATE DONE: products=${ok} errors=${err} (variant SKUs → material-based; registry re-keyed)`);
+  console.log(`\nSKU UPDATE DONE: set=${set} already-correct=${already} dup-skipped=${skipped} errors=${err}`);
   await db.end();
 })().catch(e => { console.error('FATAL', e.message); process.exit(1); });

← 366e5801 Carl Robinson: unique running 6-digit SKU numbers across boo  ·  back to Designer Wallcoverings  ·  Carl Robinson: fix publish-channels to anchor on carl_robins 2347c1e6 →