cadence-import: pre-push dedup guard — query Shopify by base-SKU before create; if a live (non-archived) product exists, backfill link + skip create instead of duplicating

79f2fa5943dba37f5695b31ad8b8f057244f0c5a · 2026-06-12 08:59:18 -0700 · SteveStudio2
Files touched

M shopify/scripts/cadence/cadence-import.js
Diff

commit 79f2fa5943dba37f5695b31ad8b8f057244f0c5a
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date:   Fri Jun 12 08:59:18 2026 -0700

    cadence-import: pre-push dedup guard — query Shopify by base-SKU before create; if a live (non-archived) product exists, backfill link + skip create instead of duplicating
---
 shopify/scripts/cadence/cadence-import.js | 40 ++++++++++++++++++++++++++++---
 1 file changed, 37 insertions(+), 3 deletions(-)

diff --git a/shopify/scripts/cadence/cadence-import.js b/shopify/scripts/cadence/cadence-import.js
index a5f773f6..17f59446 100644
--- a/shopify/scripts/cadence/cadence-import.js
+++ b/shopify/scripts/cadence/cadence-import.js
@@ -211,7 +211,40 @@ function buildInput(vendor, cfg, row, retail) {
 
 const PRODUCTSET = `mutation push($input: ProductSetInput!){productSet(synchronous:true,input:$input){product{id handle status} userErrors{field message}}}`;
 
+// ---- PRE-PUSH DEDUP GUARD (Steve 2026-06-12, DTD-picked structural fix) ----
+// The net-new gate selects rows where shopify_product_id='' and blindly CREATEs. If that link
+// is empty due to drift (a Mac2-created product whose id never reached this DB, a late sync, a
+// re-import) but the product ALREADY exists on Shopify, a blind create makes a LIVE duplicate.
+// Before every create we ask Shopify itself whether a NON-archived product already carries this
+// exact variant sku. If so: backfill the catalog link and SKIP the create (idempotent, never
+// clobbers the existing listing). Only archived-only matches fall through to a fresh create
+// (the sku has no live listing). Makes link drift permanently harmless for every vendor.
+async function findExistingProduct(dwSku) {
+  const q = `query($q:String){products(first:15,query:$q){edges{node{id status variants(first:25){edges{node{sku price}}}}}}}`;
+  const r = await gqlRetry(q, { q: `sku:${dwSku}` });
+  const edges = r.json?.data?.products?.edges || [];
+  // base-sku match: strip -Roll/-Sample so the bare dw_sku matches whatever suffix convention the
+  // existing product used (DWAE-500010 ≡ DWAE-500010-Roll). Avoids the "sku:" prefix search's loose
+  // false positives (e.g. DWKK-1000 matching DWKK-100099) while catching cross-tool dups.
+  const baseSku = s => String(s || '').toUpperCase().replace(/-(SAMPLE|ROLL)$/i, '').trim();
+  const key = baseSku(dwSku);
+  const matches = edges.map(e => e.node).filter(n => (n.variants?.edges || []).some(v => baseSku(v.node.sku) === key));
+  if (!matches.length) return null;
+  const live = matches.find(n => n.status === 'ACTIVE') || matches.find(n => n.status === 'DRAFT');
+  if (live) return { id: live.id, status: live.status, statuses: matches.map(m => m.status) };
+  return { id: matches[0].id, status: 'ARCHIVED', archivedOnly: true, statuses: matches.map(m => m.status) };
+}
+
 async function createProduct(table, row, payload) {
+  // DEDUP GUARD — never create a second copy of a sku that already has a live listing.
+  const existing = await findExistingProduct(row.dw_sku);
+  if (existing && !existing.archivedOnly) {
+    const num = String(existing.id).replace(/.*\//, '');
+    // log our computed price to the DB (the hard rule) but DO NOT overwrite the live Shopify product.
+    logPriceToDb(table, row.dw_sku, payload.retail);
+    psql(`UPDATE ${table} SET shopify_product_id='${num}' WHERE dw_sku='${row.dw_sku.replace(/'/g,"''")}' AND coalesce(shopify_product_id::text,'')='';`);
+    return { ok: true, pid: existing.id, action: 'linked-existing', status: existing.status };
+  }
   // dw_unified FIRST
   logPriceToDb(table, row.dw_sku, payload.retail);
   const r = await gqlRetry(PRODUCTSET, { input: payload.input });
@@ -248,7 +281,7 @@ async function createProduct(table, row, payload) {
   for (let i=0;i<Math.min(VENDORS_PER_SLOT, ready.length);i++) picked.push(ready[(start+i)%ready.length]);
   console.log(`\n=== ${COMMIT?'LIVE COMMIT':'DRY-RUN'} — slot ${SLOT}: ${picked.length} vendor(s) × up to ${SKUS_PER_VENDOR} SKUs ===`);
 
-  const plan = []; let created=0, failed=0;
+  const plan = []; let created=0, failed=0, linked=0;
   for (const r of picked) {
     if (COMMIT) ensureOurPriceCol(r.cfg.table);
     const skus = selectSkus(r.cfg, SKUS_PER_VENDOR);
@@ -260,7 +293,8 @@ async function createProduct(table, row, payload) {
       plan.push({ vendor:r.vendor, dw_sku:row.dw_sku, cost:row.cost, retail, sample:SAMPLE_PRICE, title:payload.title });
       if (!COMMIT) { console.log(`  · ${row.dw_sku}  cost $${row.cost.toFixed(2)} → roll $${retail}  sample $${SAMPLE_PRICE}  | ${payload.title.slice(0,52)}`); continue; }
       const res = await createProduct(r.cfg.table, row, payload);
-      if (res.ok) { created++; if(created%10===0) process.stdout.write(`\r  created ${created}…`); }
+      if (res.ok && res.action === 'linked-existing') { linked++; console.log(`  ↪ ${row.dw_sku} already on Shopify (${res.status}) → backfilled link ${String(res.pid).replace(/.*\//,'')}, skipped create (dedup guard)`); }
+      else if (res.ok) { created++; if(created%10===0) process.stdout.write(`\r  created ${created}…`); }
       else { failed++; console.log(`  ✗ ${row.dw_sku} ${JSON.stringify(res.err).slice(0,140)}`); }
       if (res.stop) { console.log(`\n⛔ DAILY_VARIANT_LIMIT reached — stopping (resets in ~24h). created ${created} this run.`); break; }
     }
@@ -270,7 +304,7 @@ async function createProduct(table, row, payload) {
   saveCursor({ idx: (start + picked.length) % ready.length, lastSlot: SLOT, lastRun: TODAY });
   const planFile = path.join(DATADIR, `cadence-plan-${SLOT}-${TODAY}.json`);
   fs.mkdirSync(DATADIR,{recursive:true}); fs.writeFileSync(planFile, JSON.stringify(plan,null,1));
-  console.log(`\n\n${COMMIT?`CREATED ${created}, failed ${failed}`:`DRY-RUN planned ${plan.length} products`} → ${planFile}`);
+  console.log(`\n\n${COMMIT?`CREATED ${created}, linked-existing ${linked} (dedup guard), failed ${failed}`:`DRY-RUN planned ${plan.length} products`} → ${planFile}`);
   if (!COMMIT) console.log('Re-run with --commit to create these on Shopify (DRAFT status, dw_unified logged first).');
 
   // refresh the CNCP "Price Coverage" goal panel now that new cost may have landed.
← 6f53fddf Add dedup-kravet-active.js: dry-run-default tool to archive · back to Designer Wallcoverings · worker-guard: pre-push dedup guard module + 9-test harness + 6cc85e55 →