← back to Filemaker Mcp
shopify->filemaker client sync: info@ trigger, 3-way dedup (email/phone/name), auto-create; created acct 1061931 live; launchd staged (not loaded)
cecc780ff3c20d7b22c43f4b2022b0670601231b · 2026-06-29 11:07:41 -0700 · Steve
Files touched
M .gitignoreA bin/shopify-sync.jsA lib/sync-core.js
Diff
commit cecc780ff3c20d7b22c43f4b2022b0670601231b
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Jun 29 11:07:41 2026 -0700
shopify->filemaker client sync: info@ trigger, 3-way dedup (email/phone/name), auto-create; created acct 1061931 live; launchd staged (not loaded)
---
.gitignore | 1 +
bin/shopify-sync.js | 94 +++++++++++++++++++++++++++++++++++++++++++++++++++++
lib/sync-core.js | 81 +++++++++++++++++++++++++++++++++++++++++++++
3 files changed, 176 insertions(+)
diff --git a/.gitignore b/.gitignore
index b07f084..f3ad563 100644
--- a/.gitignore
+++ b/.gitignore
@@ -7,3 +7,4 @@ tmp/
.DS_Store
dist/
build/
+data/
diff --git a/bin/shopify-sync.js b/bin/shopify-sync.js
new file mode 100644
index 0000000..868566a
--- /dev/null
+++ b/bin/shopify-sync.js
@@ -0,0 +1,94 @@
+#!/usr/bin/env node
+// Standing watcher: poll info@ (via George) for new Designer Wallcoverings
+// Shopify order emails, enrich each from the Shopify API, and add genuinely-new
+// customers to the FileMaker Clients file (deduped by email/phone/name).
+// Auto-commits and reports. Run on a schedule (launchd).
+import { readFileSync, writeFileSync, existsSync, mkdirSync, appendFileSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+import { syncOrder } from '../lib/sync-core.js';
+
+const __dir = dirname(fileURLToPath(import.meta.url));
+const ROOT = join(__dir, '..');
+// load .env
+for (const l of readFileSync(join(ROOT, '.env'), 'utf8').split('\n')) {
+ const m = l.match(/^([A-Z0-9_]+)=(.*)$/); if (m && !process.env[m[1]]) process.env[m[1]] = m[2];
+}
+
+const DATA = join(ROOT, 'data');
+if (!existsSync(DATA)) mkdirSync(DATA, { recursive: true });
+const STATE_F = join(DATA, 'sync-state.json');
+const LEDGER = join(DATA, 'sync-ledger.jsonl');
+
+const GEORGE = 'http://127.0.0.1:9850';
+const GEORGE_AUTH = 'Basic ' + Buffer.from('admin:').toString('base64');
+const DW_SENDER = 'store+1541177456@t.shopifyemail.com'; // the DW customer-order sender (excludes vendor confirmations)
+const STORE = process.env.FM_SHOPIFY_STORE || process.env.SHOPIFY_STORE;
+const SHOP_TOKEN = process.env.SHOPIFY_ORDERS_TOKEN;
+
+const log = (...a) => console.log(new Date().toISOString(), ...a);
+
+function loadState() { try { return JSON.parse(readFileSync(STATE_F, 'utf8')); } catch { return { maxProcessed: 0, processed: [] }; } }
+function saveState(s) { writeFileSync(STATE_F, JSON.stringify(s, null, 2)); }
+
+// 1) pull recent DW order emails from info@ via George; parse "Order #NNNNN placed by NAME"
+async function newOrderNumbers(state) {
+ const q = encodeURIComponent(`from:${DW_SENDER} subject:"placed by" newer_than:14d`);
+ const r = await fetch(`${GEORGE}/api/search?account=info&q=${q}&maxResults=40`, { headers: { Authorization: GEORGE_AUTH } });
+ if (!r.ok) throw new Error('George search failed: ' + r.status);
+ const msgs = (await r.json()).messages || [];
+ const nums = new Set();
+ for (const m of msgs) {
+ const mm = (m.subject || '').match(/Order\s+#(\d+)\s+placed by/i);
+ if (mm) { const n = Number(mm[1]); if (n > (state.maxProcessed || 0) && !state.processed?.includes(n)) nums.add(n); }
+ }
+ return [...nums].sort((a, b) => a - b);
+}
+
+// 2) fetch the full order (address/phone/email) from Shopify by order number
+async function fetchOrder(num) {
+ const url = `https://${STORE}/admin/api/2024-10/orders.json?status=any&name=${encodeURIComponent('#' + num)}`;
+ const r = await fetch(url, { headers: { 'X-Shopify-Access-Token': SHOP_TOKEN } });
+ if (!r.ok) throw new Error(`Shopify fetch ${num} failed: ${r.status}`);
+ const orders = (await r.json()).orders || [];
+ return orders.find((o) => o.order_number === num) || orders[0] || null;
+}
+
+// optional: report a created client back to steve-office via George (internal send, allowed)
+async function reportCreated(results) {
+ const created = results.filter((r) => r.action === 'created');
+ if (!created.length) return;
+ const rows = created.map((c) => `Order #${c.orderNumber} → new client acct ${c.accountNumber} — ${c.company} (${c.email})`).join('<br>');
+ await fetch(`${GEORGE}/api/send`, {
+ method: 'POST', headers: { Authorization: GEORGE_AUTH, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ account: 'steve-office', to: 'steve@designerwallcoverings.com',
+ subject: `FileMaker: ${created.length} new client(s) added from Shopify orders`,
+ body: `<p>Added to the FileMaker Clients file (auto-synced from Shopify orders):</p><p>${rows}</p>` }),
+ }).catch((e) => log('report email failed:', e.message));
+}
+
+async function main() {
+ if (!STORE || !SHOP_TOKEN) throw new Error('SHOPIFY_STORE / SHOPIFY_ORDERS_TOKEN not set in .env');
+ const state = loadState();
+ const nums = await newOrderNumbers(state);
+ if (!nums.length) { log('no new orders.'); return; }
+ log('new order numbers:', nums.join(', '));
+
+ const results = [];
+ for (const num of nums) {
+ try {
+ const order = await fetchOrder(num);
+ if (!order) { log(`order ${num} not found in Shopify, skipping`); continue; }
+ const res = await syncOrder(order, { commit: true });
+ results.push(res);
+ log(`#${num}: ${res.action}${res.action === 'created' ? ' acct ' + res.accountNumber + ' (' + res.company + ')' : res.action === 'skipped' ? ' (' + res.reason + (res.matchedOn ? ' on ' + res.matchedOn.join('/') : '') + ')' : ''}`);
+ appendFileSync(LEDGER, JSON.stringify({ ts: new Date().toISOString(), ...res }) + '\n');
+ state.processed = [...new Set([...(state.processed || []), num])].slice(-500);
+ state.maxProcessed = Math.max(state.maxProcessed || 0, num);
+ saveState(state);
+ } catch (e) { log(`#${num} ERROR:`, e.message); }
+ }
+ await reportCreated(results);
+}
+
+main().catch((e) => { log('FATAL', e.message); process.exit(1); });
diff --git a/lib/sync-core.js b/lib/sync-core.js
new file mode 100644
index 0000000..a8b6fca
--- /dev/null
+++ b/lib/sync-core.js
@@ -0,0 +1,81 @@
+// Shopify order -> FileMaker Clients sync core.
+// Maps a Shopify order's customer to a FileMaker Clients record, but only
+// creates one when NO similar client already exists (deduped by email, phone,
+// OR name — per Steve's rule "always check for similar or same name based on
+// name, email, or phone number").
+import * as fm from '../src/fm-client.js';
+
+const LAYOUT = 'wALLPAPER oPTIONS'; // Clients layout exposing the client header fields
+const norm = (s) => (s || '').toString().trim().toLowerCase().replace(/\s+/g, ' ');
+const digits = (s) => (s || '').toString().replace(/\D/g, '');
+const last10 = (s) => digits(s).slice(-10);
+
+export function mapOrder(o) {
+ const c = o.customer || {};
+ const s = o.shipping_address || o.billing_address || {};
+ const fullName = ((c.first_name || '') + ' ' + (c.last_name || '')).trim();
+ return {
+ orderNumber: o.order_number,
+ orderId: o.id,
+ fields: {
+ 'Company': fullName || s.company || '',
+ 'Name': s.company || '',
+ 'Address': s.address1 || '',
+ 'City': s.city || '',
+ 'State': s.province_code || '',
+ 'Zip': s.zip || '',
+ 'Phone': s.phone || o.phone || c.phone || '',
+ 'Email address': o.email || c.email || '',
+ },
+ };
+}
+
+// Returns an array of matching existing clients (empty = safe to create).
+export async function findDuplicates({ fields }) {
+ const email = norm(fields['Email address']);
+ const phone10 = last10(fields['Phone']);
+ const name = norm(fields['Company']);
+ const queries = [];
+ if (email) queries.push({ 'Email address': '==' + fields['Email address'] });
+ if (phone10) queries.push({ 'Phone': '*' + phone10 + '*' }); // contains last-10 digits
+ if (name) queries.push({ 'Company': '==' + fields['Company'] }); // exact display name
+ if (name) queries.push({ 'Name': '==' + fields['Company'] });
+
+ const hits = new Map(); // recordId -> {acct, company, why[]}
+ for (const q of queries) {
+ let recs = [];
+ try { recs = (await fm.findRecords('Clients', LAYOUT, q, { limit: 10 })).records; }
+ catch (e) { if (e.fmCode === '401') continue; /* 401 = no records found */ else continue; }
+ const why = Object.keys(q)[0];
+ for (const r of recs) {
+ // confirm phone match by normalized last-10 to avoid wildcard false positives
+ if (why === 'Phone' && last10(r.fieldData['Phone']) !== phone10) continue;
+ const e = hits.get(r.recordId) || { recordId: r.recordId, acct: r.fieldData['Account Number'], company: r.fieldData['Company'], why: [] };
+ if (!e.why.includes(why)) e.why.push(why);
+ hits.set(r.recordId, e);
+ }
+ }
+ return [...hits.values()];
+}
+
+// Process one order: skip if duplicate, else create. Returns a result record.
+export async function syncOrder(order, { commit = true } = {}) {
+ const mapped = mapOrder(order);
+ if (!mapped.fields['Email address'] && !mapped.fields['Phone'] && !mapped.fields['Company']) {
+ return { orderNumber: mapped.orderNumber, action: 'skipped', reason: 'no identifying fields' };
+ }
+ const dupes = await findDuplicates(mapped);
+ if (dupes.length) {
+ return { orderNumber: mapped.orderNumber, action: 'skipped', reason: 'duplicate',
+ matchedOn: [...new Set(dupes.flatMap((d) => d.why))], existing: dupes.map((d) => ({ acct: d.acct, company: d.company })) };
+ }
+ if (!commit) {
+ return { orderNumber: mapped.orderNumber, action: 'would-create', fields: mapped.fields };
+ }
+ const res = await fm.createRecord('Clients', LAYOUT, mapped.fields, { dryRun: false });
+ // read back the auto-assigned account number
+ let acct = null;
+ try { acct = (await fm.getRecord('Clients', LAYOUT, res.recordId))?.fieldData?.['Account Number']; } catch {}
+ return { orderNumber: mapped.orderNumber, action: 'created', recordId: res.recordId, accountNumber: acct,
+ company: mapped.fields['Company'], email: mapped.fields['Email address'] };
+}
← 6470923 live: connected to FileMaker Cloud — Clients + WALLPAPER rea
·
back to Filemaker Mcp
·
auto-save: 2026-06-29T12:19:02 (5 files) — bin/shopify-sync. 8f367cd →