Add mirrorWriteThrough() helper: sync local mirror at Shopify write-time (closes metafield-not-read-back gap; force-add past shopify/lib gitignore like validate-before-activate.js)

95f1b1479638b706abea4cfed5ecd534c3592ba0 · 2026-07-12 19:18:58 -0700 · Steve

Files touched

A shopify/scripts/lib/mirror-write-through.js

Diff

commit 95f1b1479638b706abea4cfed5ecd534c3592ba0
Author: Steve <steve@designerwallcoverings.com>
Date:   Sun Jul 12 19:18:58 2026 -0700

    Add mirrorWriteThrough() helper: sync local mirror at Shopify write-time (closes metafield-not-read-back gap; force-add past shopify/lib gitignore like validate-before-activate.js)
---
 shopify/scripts/lib/mirror-write-through.js | 63 +++++++++++++++++++++++++++++
 1 file changed, 63 insertions(+)

diff --git a/shopify/scripts/lib/mirror-write-through.js b/shopify/scripts/lib/mirror-write-through.js
new file mode 100644
index 00000000..52188220
--- /dev/null
+++ b/shopify/scripts/lib/mirror-write-through.js
@@ -0,0 +1,63 @@
+/**
+ * mirror-write-through.js — keep the LOCAL dw_unified.shopify_products MIRROR in sync
+ * with Shopify AT WRITE TIME, so internal viewers (all.dw, substitutefinder, dashboards)
+ * never lag behind a change we just made.
+ *
+ * WHY THIS EXISTS (2026-07-12): we wrote 4,330 mfr numbers to Shopify metafields, but the
+ * mirror's mfr_sku column is a WRITE-SIDE field that no sync reads back from metafields —
+ * so the change was invisible to the viewers until a manual DB reconcile. The daily/hourly
+ * sync-shopify-products.js refreshes core fields + tags but NOT metafields. This helper closes
+ * that gap: any script that writes Shopify calls mirrorWriteThrough() in the same breath.
+ *
+ * The mirror is a READ CACHE (local, 127.0.0.1). Writing it to match a Shopify change we already
+ * made (the source of truth) is reversible cache-reconciliation, NOT a canonical write.
+ *
+ * Usage:
+ *   const { mirrorWriteThrough } = require('./lib/mirror-write-through');
+ *   await mirrorWriteThrough(pool, shopifyId, { mfr_sku: 'IW-003' });
+ *   await mirrorWriteThrough(pool, shopifyId, { tags: appendTag(cur, 'quotes') });
+ *   await mirrorWriteThrough(pool, shopifyId, { status: 'ACTIVE', price: 172.50 });
+ *
+ * shopifyId may be a numeric id or a full gid://shopify/Product/NNN — normalized to match
+ * however the mirror stores shopify_id (it uses the full gid). Updates ALL variant rows of the
+ * product (the mirror is one-row-per-variant, all sharing the product gid).
+ *
+ * Whitelisted columns only — never let a caller write arbitrary SQL identifiers.
+ */
+const ALLOWED = new Set([
+  'mfr_sku', 'tags', 'price', 'status', 'title', 'vendor', 'product_type',
+  'cost_price', 'retail_price', 'net_price', 'image_url', 'pattern_name',
+]);
+
+function normId(shopifyId) {
+  const s = String(shopifyId);
+  if (s.startsWith('gid://')) return s;
+  return `gid://shopify/Product/${s.replace(/\D/g, '')}`;
+}
+
+/**
+ * Apply a patch to every mirror row for a product. Returns the number of rows updated.
+ * No-ops (0 rows) are returned honestly, not swallowed — a 0 usually means the product
+ * isn't in the mirror yet (created since the last sync); caller can decide to enqueue a resync.
+ */
+async function mirrorWriteThrough(pool, shopifyId, patch) {
+  const cols = Object.keys(patch).filter((k) => ALLOWED.has(k));
+  if (!cols.length) throw new Error(`mirrorWriteThrough: no allowed columns in patch: ${JSON.stringify(Object.keys(patch))}`);
+  const gid = normId(shopifyId);
+  const sets = cols.map((c, i) => `${c} = $${i + 2}`).join(', ');
+  const vals = cols.map((c) => patch[c]);
+  const res = await pool.query(
+    `UPDATE shopify_products SET ${sets}, synced_at = now() WHERE shopify_id = $1`,
+    [gid, ...vals]
+  );
+  return res.rowCount;
+}
+
+/** Convenience for the common tag case: append a tag to a comma-separated tags string. */
+function appendTag(currentTags, tag) {
+  const list = (currentTags ? String(currentTags).split(',').map((t) => t.trim()).filter(Boolean) : []);
+  if (!list.some((t) => t.toLowerCase() === tag.toLowerCase())) list.push(tag);
+  return list.join(', ');
+}
+
+module.exports = { mirrorWriteThrough, appendTag, normId };

← 58428aa3 auto-save: 2026-07-12T18:49:42 (4 files) — pending-approval/ · back to Designer Wallcoverings · auto-save: 2026-07-12T19:49:54 (4 files) — pending-approval/ 0babc7b6 →