← back to All Designerwallcoverings

FileMaker WALLPAPER as source of truth across all./substitute/photo

8ed57a08c5f8d96034738c3fec508d2cb82d3b89 · 2026-07-08 11:03:12 -0700 · Steve

- scripts/fm-wallpaper-sync.mjs: mirror FileMaker WALLPAPER (Shopify Detail layout,
  clean-query rule, Record Type=Master) into dw_unified.filemaker_wallpaper
- server.js: LEFT-JOIN the mirror by dw_sku -> fm_mfr_number, fm_discontinued,
  fm_width, fm_specs, mfr_mismatch; hide FMPro-discontinued by default (union);
  FM stats on /healthz + /api/facets; fields flow to /api/catalog-full (substitute+photo)
- public/index.html: show both mfr numbers with mismatch flag, FM specs on cards,
  FM header chip + Mismatches/Show-FM-disco toggles + bulk Preview-sync modal,
  per-card Update Shopify button (dry-run)
- scripts/shopify-push.mjs: FileMaker->Shopify diff engine (name/mfr/width/specs);
  writes only non-empty changed fields, never downgrades richer Shopify data, never
  pushes a name into the mfr field; commit gated behind SHOPIFY_PUSH_ENABLED

Files touched

• A scripts/shopify-push.mjs

Diff

commit 8ed57a08c5f8d96034738c3fec508d2cb82d3b89
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Jul 8 11:03:12 2026 -0700

    FileMaker WALLPAPER as source of truth across all./substitute/photo
    
    - scripts/fm-wallpaper-sync.mjs: mirror FileMaker WALLPAPER (Shopify Detail layout,
      clean-query rule, Record Type=Master) into dw_unified.filemaker_wallpaper
    - server.js: LEFT-JOIN the mirror by dw_sku -> fm_mfr_number, fm_discontinued,
      fm_width, fm_specs, mfr_mismatch; hide FMPro-discontinued by default (union);
      FM stats on /healthz + /api/facets; fields flow to /api/catalog-full (substitute+photo)
    - public/index.html: show both mfr numbers with mismatch flag, FM specs on cards,
      FM header chip + Mismatches/Show-FM-disco toggles + bulk Preview-sync modal,
      per-card Update Shopify button (dry-run)
    - scripts/shopify-push.mjs: FileMaker->Shopify diff engine (name/mfr/width/specs);
      writes only non-empty changed fields, never downgrades richer Shopify data, never
      pushes a name into the mfr field; commit gated behind SHOPIFY_PUSH_ENABLED
---
 scripts/shopify-push.mjs | 181 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 181 insertions(+)

diff --git a/scripts/shopify-push.mjs b/scripts/shopify-push.mjs
new file mode 100644
index 0000000..4f0487e
--- /dev/null
+++ b/scripts/shopify-push.mjs
@@ -0,0 +1,181 @@
+#!/usr/bin/env node
+// FileMaker → Shopify spec push worker (per-SKU).  DRY-RUN by default.
+// ─────────────────────────────────────────────────────────────────────────────
+// Steve: "Create button to update shopify with name, mfr, width, all specs" — the
+// data flowing FROM FileMaker's WALLPAPER master INTO the live Shopify product. This
+// worker owns one SKU: read the FileMaker mirror row + the live Shopify product, compute
+// the diff, and (only when explicitly gated) write the non-empty, changed fields.
+//
+// Spawned by all-designerwallcoverings/server.js (the /api/shopify-push endpoint), same
+// pattern as scripts/live-scrape.js. Emits ONE JSON object on stdout.
+//
+// HARD SAFETY RAILS:
+//   • NEVER writes an empty FileMaker value (a blank FM cell must not blank live Shopify data).
+//   • NEVER touches custom.material (the double-encoding-bug field) or any color_* / color_details
+//     metafield (owned by the local PIL color-enrichment pipeline).
+//   • Only writes metafield keys that ALREADY EXIST on the product, except the core mfr + width
+//     keys which are allowed to be created — so the push can't sprawl new metafields.
+//   • --commit is a NO-OP unless SHOPIFY_PUSH_ENABLED=1 is ALSO set in the environment. The
+//     dry-run diff is always free and side-effect-free. Live writes stay Steve-gated.
+//
+// Usage:
+//   node scripts/shopify-push.mjs <dw_sku>            # dry-run: prints the diff, writes nothing
+//   node scripts/shopify-push.mjs <dw_sku> --commit   # write (requires SHOPIFY_PUSH_ENABLED=1)
+
+import pg from 'pg';
+import { gql } from '/Users/macstudio3/Projects/designerwallcoverings/scripts/lib/shopify.mjs';
+
+const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
+const dwSkuArg = process.argv[2];
+const COMMIT = process.argv.includes('--commit');
+const PUSH_ENABLED = process.env.SHOPIFY_PUSH_ENABLED === '1';
+const out = (o) => { process.stdout.write(JSON.stringify(o)); process.exit(o.ok === false ? 1 : 0); };
+
+if (!dwSkuArg) out({ ok: false, reason: 'dw_sku required' });
+
+// ── FileMaker-field → Shopify-metafield target map ───────────────────────────────
+// Each FM value fans out to the DW metafield keys that mirror it (discovered from a real
+// product). `create:true` = allowed to be created if absent (the core identity fields);
+// everything else is written ONLY if the key already exists on the product (no sprawl).
+// Deliberately EXCLUDED: custom.material (double-encode bug), custom.color* / color_details
+// (local color pipeline owns those).
+const T = (namespace, key, create = false) => ({ namespace, key, create });
+const FIELD_TARGETS = {
+  // key = FileMaker mirror field OR specs.<key>;  value = metafield targets
+  mfr_number: [T('custom', 'manufacturer_sku', true), T('dwc', 'manufacturer_sku', true)],
+  width:      [T('specifications', 'width', true), T('global', 'width', true), T('dwc', 'width'), T('custom', 'width')],
+  content:    [T('global', 'Contents'), T('dwc', 'contents'), T('specifications', 'material'), T('global', 'material')],
+  collection: [T('global', 'Collection'), T('custom', 'collection_name')],
+  length:     [T('global', 'length'), T('custom', 'length'), T('specifications', 'roll_length')],
+  brand:      [T('global', 'Brand'), T('custom', 'brand')],
+  type:       [T('global', 'type')],
+  style:      [T('specs', 'style')],
+  sold_per:   [T('global', 'sold_by')],
+  product_category: [T('specs', 'pattern')],
+};
+
+async function main() {
+  const pool = new pg.Pool({ connectionString: DSN, max: 2 });
+  // FileMaker's comboskuwithdash is bare (no -SAMPLE); a card may pass the Shopify variant sku
+  // WITH the suffix. Normalize once so BOTH lookups key on the same bare dw_sku.
+  const dwBare = dwSkuArg.replace(/-sample$/i, '');
+  // 1. FileMaker source of truth
+  const { rows: fmRows } = await pool.query(
+    `SELECT * FROM filemaker_wallpaper WHERE upper(dw_sku)=upper($1) LIMIT 1`, [dwBare]);
+  const fm = fmRows[0];
+  if (!fm) { await pool.end(); return out({ ok: false, dw_sku: dwSkuArg, reason: 'not in FileMaker WALLPAPER file' }); }
+  if (fm.is_discontinued) { await pool.end(); return out({ ok: false, dw_sku: dwSkuArg, reason: 'FileMaker marks this DISCONTINUED — refusing to push' }); }
+
+  // 2. the live Shopify product (GID via the mirror). The SKU may live in dw_sku, variant_sku,
+  // OR sku (dw_sku is often null on the sample-variant rows) — match any, -SAMPLE-stripped.
+  const { rows: spRows } = await pool.query(
+    `SELECT shopify_id, handle, title, status FROM shopify_products
+      WHERE upper($1) IN (
+        upper(regexp_replace(coalesce(dw_sku,''),'-SAMPLE$','','i')),
+        upper(regexp_replace(coalesce(variant_sku,''),'-SAMPLE$','','i')),
+        upper(regexp_replace(coalesce(sku,''),'-SAMPLE$','','i')) )
+        AND shopify_id IS NOT NULL
+      ORDER BY (upper(status)='ACTIVE') DESC LIMIT 1`, [dwBare]);
+  await pool.end();
+  const sp = spRows[0];
+  if (!sp) return out({ ok: false, dw_sku: dwSkuArg, reason: 'no Shopify product for this SKU (FileMaker-only — would need CREATE, not update)', fm_only: true });
+  const gid = /^gid:/.test(sp.shopify_id) ? sp.shopify_id : `gid://shopify/Product/${String(sp.shopify_id).match(/\d+$/)?.[0]}`;
+
+  // 3. read the product's current title + existing metafields
+  const pd = await gql(`{ product(id:${JSON.stringify(gid)}) { id title status
+     metafields(first:80){ nodes { namespace key type value } } } }`);
+  if (pd?.__err || !pd?.product) return out({ ok: false, dw_sku: dwSkuArg, reason: 'Shopify product read failed', detail: pd?.__err });
+  const prod = pd.product;
+  const have = new Map();                                 // "ns.key" → {type,value}
+  for (const m of prod.metafields.nodes) have.set(`${m.namespace}.${m.key}`, { type: m.type, value: m.value });
+
+  // 4. build the FileMaker source values (non-empty only)
+  const specs = fm.specs || {};
+  const srcVal = (fieldKey) => {
+    if (fieldKey === 'mfr_number') return (fm.mfr_number || '').trim();
+    if (fieldKey === 'width') return (fm.width || specs.width || '').trim();
+    return (specs[fieldKey] || '').trim();
+  };
+
+  // Don't-downgrade guard: if Shopify's current value already CONTAINS the FileMaker value
+  // (normalized to alphanumerics), Shopify is a richer superset ("36\" (91CM)" ⊇ "36\"",
+  // "Linen Gesso - Ivory … | Schumacher" ⊇ "Linen Gesso") — pushing would strip detail, so we
+  // record it as an info-only skip rather than a proposed change. FileMaker is authoritative
+  // for the mfr NUMBER (identity), not for how richly a spec/name is merchandised.
+  const norm = (s) => String(s || '').toLowerCase().replace(/[^a-z0-9]/g, '');
+  const shopifyRicher = (before, val) => { const b = norm(before), v = norm(val); return !!b && !!v && b.includes(v) && b !== v; };
+
+  // 5. diff
+  const changes = [];                                     // proposed metafield changes
+  const skipped = [];                                     // info-only: differs but Shopify is richer
+  const metafieldsToSet = [];
+  for (const [fieldKey, targets] of Object.entries(FIELD_TARGETS)) {
+    const val = srcVal(fieldKey);
+    if (!val) continue;                                   // RULE: never push an empty FM value
+    // Guard: an mfr NUMBER never contains whitespace. When FileMaker's mfr value looks like a
+    // name (e.g. "Cabochon Verdigris") but Shopify already has a compact code, don't propose
+    // overwriting — surface it for human verification instead. FMPro is authoritative for the
+    // mfr, but only when it actually holds a number.
+    if (fieldKey === 'mfr_number' && /\s/.test(val)) { skipped.push({ field: fieldKey, to: val, reason: 'FileMaker mfr looks like a name, not a number — verify in FileMaker' }); break; }
+    for (const t of targets) {
+      const id = `${t.namespace}.${t.key}`;
+      const cur = have.get(id);
+      if (!cur && !t.create) continue;                    // no sprawl: skip keys not already present
+      const before = cur ? (cur.value || '') : null;
+      if (String(before ?? '') === val) continue;         // already matches
+      if (shopifyRicher(before, val)) { skipped.push({ field: fieldKey, metafield: id, from: before, to: val, reason: 'shopify value is richer' }); continue; }
+      changes.push({ field: fieldKey, metafield: id, from: before, to: val });
+      metafieldsToSet.push({ ownerId: gid, namespace: t.namespace, key: t.key,
+        type: cur?.type || 'single_line_text_field', value: val });
+    }
+  }
+  // title (name) — only when FileMaker has a name, it differs, AND Shopify isn't already richer
+  const fmName = (fm.name_of_pattern || fm.title || '').trim();
+  let titleChange = null;
+  if (fmName && fmName !== (prod.title || '').trim()) {
+    if (shopifyRicher(prod.title, fmName)) skipped.push({ field: 'title', from: prod.title, to: fmName, reason: 'shopify title is richer' });
+    else titleChange = { from: prod.title, to: fmName };
+  }
+
+  const plan = {
+    ok: true, dw_sku: fm.dw_sku, product_id: gid, handle: sp.handle,
+    shopify_title: prod.title, fm_name: fmName || null,
+    fm_mfr_number: fm.mfr_number || null,
+    title_change: titleChange,
+    metafield_changes: changes,
+    skipped_richer: skipped,
+    change_count: changes.length + (titleChange ? 1 : 0),
+    committed: false,
+  };
+
+  if (!COMMIT || plan.change_count === 0) {
+    plan.note = plan.change_count === 0 ? 'No changes — Shopify already matches FileMaker.'
+      : 'DRY RUN — call with --commit (and SHOPIFY_PUSH_ENABLED=1) to write.';
+    return out(plan);
+  }
+
+  // ── COMMIT (gated) ──────────────────────────────────────────────────────────────
+  if (!PUSH_ENABLED) {
+    plan.note = 'COMMIT BLOCKED: SHOPIFY_PUSH_ENABLED is not set. This is the Steve-gated live-write switch.';
+    plan.blocked = true;
+    return out(plan);
+  }
+  const errors = [];
+  if (titleChange) {
+    const r = await gql(`mutation($input:ProductInput!){ productUpdate(input:$input){ userErrors{ field message } } }`,
+      { input: { id: gid, title: fmName } });
+    if (r?.__err || r?.productUpdate?.userErrors?.length) errors.push({ title: r?.productUpdate?.userErrors || r?.__err });
+  }
+  for (let i = 0; i < metafieldsToSet.length; i += 25) {
+    const batch = metafieldsToSet.slice(i, i + 25);
+    const r = await gql(`mutation($m:[MetafieldsSetInput!]!){ metafieldsSet(metafields:$m){ userErrors{ field message } } }`,
+      { m: batch });
+    if (r?.__err || r?.metafieldsSet?.userErrors?.length) errors.push({ metafields: r?.metafieldsSet?.userErrors || r?.__err });
+  }
+  plan.committed = errors.length === 0;
+  plan.errors = errors;
+  plan.note = errors.length ? 'Committed with errors — see errors[].' : 'Committed to live Shopify.';
+  return out(plan);
+}
+
+main().catch((e) => out({ ok: false, dw_sku: dwSkuArg, reason: 'push worker error: ' + e.message }));

← 55b78db Add /api/discontinued + red successor banner: dead/archived  ·  back to All Designerwallcoverings  ·  chore: v1.1.0 → v1.2.0 (session close — feed + discontinued- f088c73 →