← back to Designer Wallcoverings
track DW-Agents/scripts/reverse-sync.js (orphan relink dry-run, env-based DB url)
a45a0194c8d67342989504ee69dffda58ad4fb8b · 2026-06-01 10:24:11 -0700 · Steve Abrams
Files touched
A DW-Agents/scripts/reverse-sync.js
Diff
commit a45a0194c8d67342989504ee69dffda58ad4fb8b
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Jun 1 10:24:11 2026 -0700
track DW-Agents/scripts/reverse-sync.js (orphan relink dry-run, env-based DB url)
---
DW-Agents/scripts/reverse-sync.js | 119 ++++++++++++++++++++++++++++++++++++++
1 file changed, 119 insertions(+)
diff --git a/DW-Agents/scripts/reverse-sync.js b/DW-Agents/scripts/reverse-sync.js
new file mode 100644
index 00000000..177339b1
--- /dev/null
+++ b/DW-Agents/scripts/reverse-sync.js
@@ -0,0 +1,119 @@
+#!/usr/bin/env node
+/**
+ * reverse-sync.js — resolve vendor_catalog orphans by re-linking them to an
+ * existing Shopify product, so the enrichment pipeline can pick them up again.
+ *
+ * An "orphan" = vendor_catalog row with shopify_product_id IS NULL. Many orphans
+ * are simply NOT on Shopify (sync_status='new' never pushed, or 'never_push'
+ * intentionally excluded) — those are NOT reverse-syncable. This script only
+ * relinks orphans whose mfr_sku / dw_sku matches EXACTLY ONE existing
+ * shopify_products row (safe 1:1); ambiguous (>1) and unmatched are reported,
+ * never written.
+ *
+ * DRY RUN BY DEFAULT — measures resolvable count, writes nothing.
+ * node reverse-sync.js # dry-run report only
+ * node reverse-sync.js --apply # actually UPDATE vendor_catalog (gated)
+ *
+ * Companion to drift-audit.js (read-only auditor). Same DB.
+ */
+const { Pool } = require('pg');
+// Connection string comes from the environment — never hardcode the DB password.
+// On Kamatera: DW_PG_URL=postgresql://dw_admin:****@127.0.0.1:5432/dw_unified node reverse-sync.js
+const PG_URL = process.env.DW_PG_URL || process.env.DATABASE_URL;
+if (!PG_URL) { console.error('Set DW_PG_URL (postgresql://user:pass@host:5432/dw_unified) before running.'); process.exit(1); }
+const pool = new Pool({ connectionString: PG_URL });
+
+const APPLY = process.argv.includes('--apply');
+// statuses that should NEVER be auto-linked (intentionally off-Shopify / handled)
+const EXCLUDE = `('never_push','excluded','archived_duplicate','transferred')`;
+
+async function q(sql, params) { const { rows } = await pool.query(sql, params); return rows; }
+
+async function main() {
+ console.log(`\n━━━ reverse-sync ${APPLY ? '[APPLY]' : '[DRY RUN]'} — ${new Date().toISOString()} ━━━\n`);
+
+ const [base] = await q(`
+ SELECT
+ count(*) AS orphans_all,
+ count(*) FILTER (WHERE sync_status IN ${EXCLUDE}) AS excluded_status,
+ count(*) FILTER (WHERE sync_status NOT IN ${EXCLUDE}) AS candidate
+ FROM vendor_catalog WHERE shopify_product_id IS NULL`);
+ console.log(`orphans (shopify_product_id NULL): ${(+base.orphans_all).toLocaleString()}`);
+ console.log(` excluded by status ${EXCLUDE.replace(/[()']/g,'')}: ${(+base.excluded_status).toLocaleString()}`);
+ console.log(` linkable candidates: ${(+base.candidate).toLocaleString()}\n`);
+
+ // --- match by mfr_sku (exact, non-empty), count distinct shopify products per orphan ---
+ const mfr = await q(`
+ SELECT
+ count(*) FILTER (WHERE n = 1) AS exact1,
+ count(*) FILTER (WHERE n > 1) AS ambiguous,
+ count(*) FILTER (WHERE n = 0) AS none
+ FROM (
+ SELECT o.id, count(DISTINCT sp.shopify_id) AS n
+ FROM vendor_catalog o
+ LEFT JOIN shopify_products sp ON sp.mfr_sku = o.mfr_sku
+ WHERE o.shopify_product_id IS NULL
+ AND o.sync_status NOT IN ${EXCLUDE}
+ AND o.mfr_sku IS NOT NULL AND o.mfr_sku <> ''
+ GROUP BY o.id
+ ) t`);
+ console.log('── match by mfr_sku ──');
+ console.log(` 1:1 resolvable: ${(+mfr[0].exact1).toLocaleString()}`);
+ console.log(` ambiguous (>1): ${(+mfr[0].ambiguous).toLocaleString()}`);
+ console.log(` no match: ${(+mfr[0].none).toLocaleString()}\n`);
+
+ // --- dw_sku fallback for orphans with NO mfr_sku 1:1 match ---
+ const dw = await q(`
+ SELECT
+ count(*) FILTER (WHERE n = 1) AS exact1,
+ count(*) FILTER (WHERE n > 1) AS ambiguous
+ FROM (
+ SELECT o.id, count(DISTINCT sp.shopify_id) AS n
+ FROM vendor_catalog o
+ LEFT JOIN shopify_products sp ON sp.dw_sku = o.dw_sku
+ WHERE o.shopify_product_id IS NULL
+ AND o.sync_status NOT IN ${EXCLUDE}
+ AND o.dw_sku IS NOT NULL AND o.dw_sku <> ''
+ GROUP BY o.id
+ ) t`);
+ console.log('── match by dw_sku ──');
+ console.log(` 1:1 resolvable: ${(+dw[0].exact1).toLocaleString()}`);
+ console.log(` ambiguous (>1): ${(+dw[0].ambiguous).toLocaleString()}\n`);
+
+ // --- per-vendor resolvable (1:1 by mfr_sku), top 15 ---
+ const perVendor = await q(`
+ SELECT o.vendor_code, count(*) AS resolvable
+ FROM vendor_catalog o
+ JOIN LATERAL (
+ SELECT count(DISTINCT sp.shopify_id) AS n, max(sp.shopify_id) AS sid
+ FROM shopify_products sp WHERE sp.mfr_sku = o.mfr_sku
+ ) m ON m.n = 1
+ WHERE o.shopify_product_id IS NULL
+ AND o.sync_status NOT IN ${EXCLUDE}
+ AND o.mfr_sku IS NOT NULL AND o.mfr_sku <> ''
+ GROUP BY o.vendor_code ORDER BY resolvable DESC LIMIT 15`);
+ console.log('── top vendors with 1:1-resolvable orphans (by mfr_sku) ──');
+ perVendor.forEach(r => console.log(` ${String(r.vendor_code||'(none)').padEnd(16)} ${String(r.resolvable).padStart(7)}`));
+
+ // --- 10 sample 1:1 matches to eyeball ---
+ const sample = await q(`
+ SELECT o.id AS vc_id, o.vendor_code, o.mfr_sku, o.pattern_name, m.sid AS shopify_id, m.title
+ FROM vendor_catalog o
+ JOIN LATERAL (
+ SELECT count(DISTINCT sp.shopify_id) AS n, max(sp.shopify_id) AS sid, max(sp.title) AS title
+ FROM shopify_products sp WHERE sp.mfr_sku = o.mfr_sku
+ ) m ON m.n = 1
+ WHERE o.shopify_product_id IS NULL AND o.sync_status NOT IN ${EXCLUDE}
+ AND o.mfr_sku IS NOT NULL AND o.mfr_sku <> ''
+ LIMIT 10`);
+ console.log('\n── sample 1:1 matches (vc_id vendor mfr_sku -> shopify_id title) ──');
+ sample.forEach(r => console.log(` ${r.vc_id} ${String(r.vendor_code||'').padEnd(10)} ${String(r.mfr_sku).padEnd(16)} -> ${r.shopify_id} ${String(r.title||'').slice(0,40)}`));
+
+ if (APPLY) {
+ console.log('\n⛔ --apply path intentionally not implemented yet. Review the dry-run, then enable a batched, backed-up UPDATE.');
+ } else {
+ console.log('\n[DRY RUN] No rows written. Re-run with --apply only after the 1:1 counts + sample look right.');
+ }
+ await pool.end();
+}
+main().catch(e => { console.error(e.message); process.exit(1); });
← 20cf51fa fix: remove backup source leak from public dir, fix duplicat
·
back to Designer Wallcoverings
·
Grasscloth sites: unique clean image heroes (fleet hero dedu 5c41f8cc →