← back to All Designerwallcoverings
auto-save: 2026-07-08T10:35:15 (1 files) — scripts/fm-wallpaper-sync.mjs
ecdf294a3dce193c20b8d01d7dc416b4562198bd · 2026-07-08 10:35:16 -0700 · Steve Abrams
Files touched
A scripts/fm-wallpaper-sync.mjs
Diff
commit ecdf294a3dce193c20b8d01d7dc416b4562198bd
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 8 10:35:16 2026 -0700
auto-save: 2026-07-08T10:35:15 (1 files) — scripts/fm-wallpaper-sync.mjs
---
scripts/fm-wallpaper-sync.mjs | 231 ++++++++++++++++++++++++++++++++++++++++++
1 file changed, 231 insertions(+)
diff --git a/scripts/fm-wallpaper-sync.mjs b/scripts/fm-wallpaper-sync.mjs
new file mode 100644
index 0000000..a12c60f
--- /dev/null
+++ b/scripts/fm-wallpaper-sync.mjs
@@ -0,0 +1,231 @@
+#!/usr/bin/env node
+// FileMaker WALLPAPER → dw_unified.filemaker_wallpaper mirror.
+// ─────────────────────────────────────────────────────────────────────────────
+// WHY: all.designerwallcoverings.com / substitutefinder / dw-photo-capture all read
+// the /api/catalog-full feed, which is Shopify-driven. Steve's rule: FileMaker Pro's
+// WALLPAPER file is the SOURCE OF TRUTH — the catalog should show what FMPro says is
+// live (no `Date Discontinued`), display what FMPro says the mfr number is, and be
+// the source for a FileMaker→Shopify spec push. FileMaker's Data API is slow + rate-
+// limited over ~160k master rows, so we mirror it into Postgres once (nightly) and the
+// feed LEFT-JOINs the mirror by dw_sku. This script owns that mirror.
+//
+// READ LAYOUT: "*List Wallpapers - Shopify Detail Database" — the FileMaker layout
+// purpose-built to stage every field for Shopify (Shopify Handle, comboskuwithdash,
+// Mfr Pattern, mfr pattern number, Width, and the whole `… for Shopify MetaData`
+// family). It reads cleanly via the Data API PROVIDED we obey the clean-query rule.
+//
+// CLEAN-QUERY RULE (hard gotcha, verified): a `Date Discontinued` clause in the find
+// query silently breaks the Data API — foundCount>0 but zero rows returned. So we
+// query on `Record Type` + `comboskuwithdash` (both safe) and evaluate Date
+// Discontinued from the returned fieldData in code.
+//
+// COST: FileMaker Cloud Data API = already-paid, $0/call. Postgres = $0 (local).
+// SAFETY: writes ONLY to the new `filemaker_wallpaper` table. Touches nothing else.
+//
+// Usage:
+// node scripts/fm-wallpaper-sync.mjs # full sync (all Master rows)
+// FM_LIMIT=1000 node scripts/fm-wallpaper-sync.mjs # cap total rows (smoke test)
+// FM_BATCH=300 node scripts/fm-wallpaper-sync.mjs # page size (default 500)
+
+import { readFileSync } from 'node:fs';
+import { join } from 'node:path';
+import { pathToFileURL } from 'node:url';
+import pg from 'pg';
+
+const FM_MCP_DIR = process.env.FM_MCP_DIR || '/Users/macstudio3/Projects/filemaker-mcp';
+const FM_DATABASE = 'WALLPAPER';
+const FM_LAYOUT = process.env.FM_LAYOUT || '*List Wallpapers - Shopify Detail Database';
+const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
+const BATCH = Math.max(50, +(process.env.FM_BATCH || 500) || 500);
+const CAP = process.env.FM_LIMIT ? Math.max(1, +process.env.FM_LIMIT) : Infinity;
+
+// ── FileMaker field names on the Shopify Detail layout (verified via field metadata) ──
+const F = {
+ dwSku: 'comboskuwithdash', // "DWT-55001" — the dw_sku join key
+ comboSku: 'combo sku', // "DWT55001"
+ handle: 'Shopify Handle',
+ mfrPattern: 'Mfr Pattern', // primary, usually-populated mfr number ("T35100")
+ mfrPatternNumber: 'mfr pattern number', // secondary/alt mfr number (often blank)
+ name: 'Name of Pattern', // "SAROKA WALLPAPER - Pearl"
+ color: 'Color of Pattern',
+ title: 'Title',
+ series: 'Series',
+ vid: 'vid', // vendor id ("thib", "WQ", …)
+ recordType: 'Record Type', // "Master" == a real product record
+ dateDiscontinued: 'Date Discontinued',
+ bodyHtml: 'Descripton for Shopify Body',
+ tags: 'Tags',
+ dwRetail: 'Dw Retail Price', netPrice: 'Net Price', variantPrice: 'Variant Price', mfrMap: 'MFR MAP PRICE',
+};
+// Everything that counts as a "spec" for display + the FileMaker→Shopify push, stored
+// as JSONB (non-empty only). Key = our snake_case; value = the FileMaker field name.
+const SPEC_FIELDS = {
+ width: 'Width', repeat: 'Repeat', content: 'Content',
+ sold_per: 'Sold Per', minimum: 'Minimum', collection: 'Collection',
+ durability: 'Durability for Shopify MetaData', lead_time: 'Lead Time for Shopify MetaData',
+ length: 'Length for Shopify Meta Data', packaged: 'Packaged for Shopify Meta Data',
+ qty_order_unit: 'Qty Order Unit for Shopify MetaData', qty_min_unit: 'Qty Minimum Unit for Shopify MetaData',
+ unit_of_measure: 'Unit of Measure for Shopify Meta Data', style: 'Style for Shopify MetaData',
+ type: 'Type for Shopify MetaData', brand: 'Brand for Shopify MetaData', color: 'Color for Shopify MetaData',
+ product_category: 'Product Category for Shopify Sample Data', variant_grams: 'Variant Grams',
+ keyword_tags: 'KEYWORD TAGS', collection_tag: 'COLLECTION TAG', mfr_name: 'Mfr Name of Pattern',
+};
+
+const s = (f, k) => { const v = f[k]; return v == null ? '' : String(v).trim(); };
+const numOrNull = (f, k) => {
+ const raw = s(f, k); if (!raw) return null;
+ const v = parseFloat(raw.replace(/[^0-9.\-]/g, '')); return Number.isFinite(v) ? v : null;
+};
+// mfr numbers carry human notes ("SBG3207 -- MUST USE QUOTE #G45"). Keep the raw for
+// display + push-safety, and a normalized token for mismatch comparison.
+const cleanMfr = (raw) => String(raw || '').split(/--|·|;|\bMUST\b|\bQUOTE\b/i)[0]
+ .replace(/[^A-Za-z0-9./-]+/g, '').toUpperCase();
+
+// Load filemaker-mcp/.env (host + Cognito creds) without clobbering our own env.
+function loadFmEnv() {
+ const raw = readFileSync(join(FM_MCP_DIR, '.env'), 'utf8');
+ for (const line of raw.split('\n')) {
+ const m = line.match(/^([A-Za-z_][A-Za-z0-9_]*)=(.*)$/); if (!m) continue;
+ let v = m[2];
+ if ((v.startsWith('"') && v.endsWith('"')) || (v.startsWith("'") && v.endsWith("'"))) v = v.slice(1, -1);
+ if (process.env[m[1]] === undefined) process.env[m[1]] = v;
+ }
+}
+
+const DDL = `
+CREATE TABLE IF NOT EXISTS filemaker_wallpaper (
+ dw_sku text PRIMARY KEY,
+ combo_sku text,
+ shopify_handle text,
+ mfr_pattern text,
+ mfr_pattern_number text,
+ mfr_number text, -- resolved: Mfr Pattern || mfr pattern number
+ mfr_number_clean text, -- normalized token for mismatch comparison
+ name_of_pattern text,
+ color_of_pattern text,
+ title text,
+ series text,
+ vid text,
+ record_type text,
+ date_discontinued text,
+ is_discontinued boolean,
+ width text,
+ body_html text,
+ tags text,
+ dw_retail_price numeric,
+ net_price numeric,
+ variant_price numeric,
+ mfr_map_price numeric,
+ specs jsonb,
+ fm_record_id text,
+ synced_at timestamptz DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS filemaker_wallpaper_disco_idx ON filemaker_wallpaper (is_discontinued);
+CREATE INDEX IF NOT EXISTS filemaker_wallpaper_mfrclean_idx ON filemaker_wallpaper (mfr_number_clean);
+`;
+
+const UPSERT = `
+INSERT INTO filemaker_wallpaper (
+ dw_sku, combo_sku, shopify_handle, mfr_pattern, mfr_pattern_number, mfr_number, mfr_number_clean,
+ name_of_pattern, color_of_pattern, title, series, vid, record_type,
+ date_discontinued, is_discontinued, width, body_html, tags,
+ dw_retail_price, net_price, variant_price, mfr_map_price, specs, fm_record_id, synced_at
+) VALUES (
+ $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24, now()
+)
+ON CONFLICT (dw_sku) DO UPDATE SET
+ combo_sku=EXCLUDED.combo_sku, shopify_handle=EXCLUDED.shopify_handle,
+ mfr_pattern=EXCLUDED.mfr_pattern, mfr_pattern_number=EXCLUDED.mfr_pattern_number,
+ mfr_number=EXCLUDED.mfr_number, mfr_number_clean=EXCLUDED.mfr_number_clean,
+ name_of_pattern=EXCLUDED.name_of_pattern, color_of_pattern=EXCLUDED.color_of_pattern,
+ title=EXCLUDED.title, series=EXCLUDED.series, vid=EXCLUDED.vid, record_type=EXCLUDED.record_type,
+ date_discontinued=EXCLUDED.date_discontinued, is_discontinued=EXCLUDED.is_discontinued,
+ width=EXCLUDED.width, body_html=EXCLUDED.body_html, tags=EXCLUDED.tags,
+ dw_retail_price=EXCLUDED.dw_retail_price, net_price=EXCLUDED.net_price,
+ variant_price=EXCLUDED.variant_price, mfr_map_price=EXCLUDED.mfr_map_price,
+ specs=EXCLUDED.specs, fm_record_id=EXCLUDED.fm_record_id, synced_at=now()
+`;
+
+function rowFromRecord(rec) {
+ const f = rec.fieldData || {};
+ const dwSku = s(f, F.dwSku);
+ if (!dwSku) return null; // skip blank / phantom rows
+ const mfrPattern = s(f, F.mfrPattern);
+ const mfrPatternNumber = s(f, F.mfrPatternNumber);
+ const mfrNumber = mfrPattern || mfrPatternNumber || '';
+ const disco = s(f, F.dateDiscontinued);
+ const specs = {};
+ for (const [key, fmField] of Object.entries(SPEC_FIELDS)) { const v = s(f, fmField); if (v) specs[key] = v; }
+ return [
+ dwSku, s(f, F.comboSku), s(f, F.handle),
+ mfrPattern, mfrPatternNumber, mfrNumber, cleanMfr(mfrNumber),
+ s(f, F.name), s(f, F.color), s(f, F.title), s(f, F.series), s(f, F.vid), s(f, F.recordType),
+ disco, !!disco, s(f, F.width), s(f, F.bodyHtml), s(f, F.tags),
+ numOrNull(f, F.dwRetail), numOrNull(f, F.netPrice), numOrNull(f, F.variantPrice), numOrNull(f, F.mfrMap),
+ JSON.stringify(specs), rec.recordId,
+ ];
+}
+
+async function main() {
+ const t0 = Date.now();
+ loadFmEnv();
+ const fm = await import(pathToFileURL(join(FM_MCP_DIR, 'src', 'fm-client.js')).href);
+ const pool = new pg.Pool({ connectionString: DSN, max: 3 });
+ await pool.query(DDL);
+
+ // Clean-query rule: Record Type=Master + comboskuwithdash present. NEVER Date Discontinued.
+ const query = [{ [F.recordType]: 'Master', [F.dwSku]: '*' }];
+ let offset = 1, total = 0, upserted = 0, skipped = 0, disco = 0, found = null;
+ console.log(`FM sync: layout="${FM_LAYOUT}", batch=${BATCH}, cap=${CAP === Infinity ? 'all' : CAP}`);
+
+ for (;;) {
+ const want = Math.min(BATCH, CAP - total);
+ if (want <= 0) break;
+ let res;
+ try {
+ res = await fm.findRecords(FM_DATABASE, FM_LAYOUT, query, { limit: want, offset });
+ } catch (e) {
+ if (String(e.fmCode) === '401') break; // "no more records"
+ throw e;
+ }
+ const recs = res.records || [];
+ if (found == null) found = res.dataInfo?.foundCount ?? null;
+ if (!recs.length) break;
+
+ const values = [];
+ for (const rec of recs) {
+ const row = rowFromRecord(rec);
+ if (!row) { skipped++; continue; }
+ if (row[14]) disco++; // is_discontinued
+ values.push(row);
+ }
+ // upsert this batch inside one transaction
+ if (values.length) {
+ const client = await pool.connect();
+ try {
+ await client.query('BEGIN');
+ for (const v of values) await client.query(UPSERT, v);
+ await client.query('COMMIT');
+ upserted += values.length;
+ } catch (e) { await client.query('ROLLBACK'); throw e; }
+ finally { client.release(); }
+ }
+ total += recs.length;
+ offset += recs.length;
+ if (total % 5000 < BATCH) {
+ console.log(` …${total.toLocaleString()}${found ? '/' + found.toLocaleString() : ''} read · ${upserted.toLocaleString()} upserted · ${disco.toLocaleString()} disco · ${skipped} skipped`);
+ }
+ if (recs.length < want) break; // last page
+ }
+
+ const { rows: [c] } = await pool.query(
+ `SELECT count(*) total, count(*) FILTER (WHERE is_discontinued) disco,
+ count(*) FILTER (WHERE NOT is_discontinued AND mfr_number<>'') live_with_mfr
+ FROM filemaker_wallpaper`);
+ console.log(`\nFM sync DONE in ${((Date.now() - t0) / 1000).toFixed(1)}s`);
+ console.log(` read ${total.toLocaleString()} · upserted ${upserted.toLocaleString()} · skipped ${skipped}`);
+ console.log(` table now: ${(+c.total).toLocaleString()} rows · ${(+c.disco).toLocaleString()} discontinued · ${(+c.live_with_mfr).toLocaleString()} live-with-mfr`);
+ await pool.end();
+}
+
+main().catch((e) => { console.error('FM sync FAILED:', e.message); process.exit(1); });
← 949b520 Stop tracking data/backfills/ ops artifacts; gitignore the d
·
back to All Designerwallcoverings
·
Add /api/discontinued + red successor banner: dead/archived 55b78db →