← back to Fm Wallpaper Sync
Nightly FileMaker WALLPAPER -> postgres sync (fm_wallpaper_live + fill-merge fmpro/master_fmpro), 1am launchd
73f513127bc3dca58f71a750aad7c001c0fada1f · 2026-08-12 09:17:47 -0700 · steve
Files touched
A .gitignoreA README.mdA build.sqlA build_dryrun.sqlA run.shA sync.mjs
Diff
commit 73f513127bc3dca58f71a750aad7c001c0fada1f
Author: steve <steve@designerwallcoverings.com>
Date: Wed Aug 12 09:17:47 2026 -0700
Nightly FileMaker WALLPAPER -> postgres sync (fm_wallpaper_live + fill-merge fmpro/master_fmpro), 1am launchd
---
.gitignore | 7 ++++++
README.md | 43 ++++++++++++++++++++++++++++++++
build.sql | 76 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
build_dryrun.sql | 52 ++++++++++++++++++++++++++++++++++++++
run.sh | 52 ++++++++++++++++++++++++++++++++++++++
sync.mjs | 55 ++++++++++++++++++++++++++++++++++++++++
6 files changed, 285 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..d80d2c5
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,7 @@
+node_modules/
+*.log
+logs/
+.env*
+/tmp/
+.DS_Store
+latest.json
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..3062715
--- /dev/null
+++ b/README.md
@@ -0,0 +1,43 @@
+# fm-wallpaper-sync
+
+Nightly (1:00 AM, launchd `com.steve.fm-wallpaper-sync`) refresh of the FileMaker
+WALLPAPER data into postgres `dw_unified`, so the "Shopify-active vs FileMaker"
+audit is always current. Born from TK-10483 — the old `fmpro`/`master_fmpro`
+tables were a stale March-2026 snapshot (153k rows / 302k live).
+
+## What it does
+1. **Pull** every live FM WALLPAPER record (~302k) via the Data API, reusing the
+ `filemaker-mcp` Claris-ID client. Creds read live from `~/.claude.json` (no dup).
+ Read-only against FileMaker. → `/tmp/fm_wallpaper_full.csv`
+2. **Rebuild** `fm_wallpaper_live` — the canonical CURRENT mirror, one row per
+ normalized combo sku (`^[A-Z]+[0-9]+`, most-populated record wins). Atomic swap.
+3. **Fill-merge** into `fmpro` + `master_fmpro`:
+ - UPDATE existing rows — only *fills* empty `mfr_sku`/`sku_prefix`/`image_filename`
+ (`COALESCE(NULLIF(existing,''), fm)`); never overwrites a populated value.
+ - INSERT combo skus not yet present.
+ - **Never touches** the vendor-join columns `phone`/`email_1`/`vendor_code`/
+ `account_num` — those come from a vendor join FM can't supply. Proven preserved
+ in `build_dryrun.sql` (PRE==POST populated counts).
+4. **Regenerate** `~/Desktop/fm-wallpaper-audit/MISSING_from_FM_wallpaper_ALL.csv`.
+5. **Heartbeat** → `latest.json`.
+
+Safety floor: aborts the whole transaction if the FM pull returns < 250k rows
+(guards against a truncated pull wiping/expanding the live tables).
+
+## Files
+- `sync.mjs` — FM Data API pager → CSV
+- `build.sql` — staging → `fm_wallpaper_live` + fill-merge (`__CSV__` seded in by run.sh)
+- `build_dryrun.sql` — same merge, snapshots vendor-join cols, ROLLBACKs (validation)
+- `run.sh` — orchestrates 1–5, logs to `logs/`
+- Reruns are idempotent (fill-only + INSERT-if-absent).
+
+## Manual run
+```
+./run.sh # full nightly job
+tail -f logs/sync-$(date +%Y%m%d).log
+```
+
+## Consumers of fmpro/master_fmpro (why the merge is fill-only)
+21 files incl. live internal viewers (astek/quadrille/schumacher/momentum/
+muralsource/artmura landings, mfr-review-viewer, ads-dashboard). The fill-only
+rule keeps them working while un-staling the data.
diff --git a/build.sql b/build.sql
new file mode 100644
index 0000000..53fa453
--- /dev/null
+++ b/build.sql
@@ -0,0 +1,76 @@
+\set ON_ERROR_STOP on
+BEGIN;
+
+-- staging: column ORDER must match sync.mjs CSV column order (position-matched by \copy)
+CREATE TEMP TABLE fm_wallpaper_stage (
+ combo_sku text, series text, js_pattern text, mfr_pattern text, supplier text,
+ jpg_name text, width text, border text, rpt text, five_ten text, account text,
+ line text, minimum text, date_line text, page text, retail text, cost text, net text,
+ record_id text
+) ON COMMIT DROP;
+\copy fm_wallpaper_stage FROM '__CSV__' WITH (FORMAT csv, HEADER true)
+
+-- guard: refuse to proceed on a truncated/empty pull (protects the live tables)
+DO $$
+DECLARE n int; BEGIN
+ SELECT count(*) INTO n FROM fm_wallpaper_stage;
+ IF n < 250000 THEN RAISE EXCEPTION 'stage row count % below safety floor 250000 - aborting', n; END IF;
+END $$;
+
+-- ===== canonical current mirror: one row per normalized combo sku, most-populated wins =====
+DROP TABLE IF EXISTS fm_wallpaper_live_new;
+CREATE TABLE fm_wallpaper_live_new AS
+SELECT DISTINCT ON (norm)
+ norm AS combo_sku,
+ nullif(upper(series),'') AS series,
+ nullif(js_pattern,'') AS js_pattern,
+ nullif(split_part(mfr_pattern,' -- ',1),'') AS mfr_pattern,
+ nullif(supplier,'') AS supplier,
+ nullif(jpg_name,'') AS jpg_name,
+ nullif(width,'') AS fm_width, nullif(border,'') AS fm_border, nullif(rpt,'') AS fm_repeat,
+ nullif(five_ten,'') AS five_ten, nullif(account,'') AS fm_account, nullif(line,'') AS fm_line,
+ nullif(minimum,'') AS fm_minimum, nullif(date_line,'') AS date_line,
+ nullif(retail,'') AS retail, nullif(cost,'') AS cost, nullif(net,'') AS net,
+ record_id, now() AS refreshed_at
+FROM (SELECT *, upper(regexp_replace(combo_sku,'[^A-Za-z0-9]','','g')) AS norm FROM fm_wallpaper_stage) s
+WHERE norm ~ '^[A-Z]+[0-9]+'
+ORDER BY norm,
+ (nullif(mfr_pattern,'') IS NOT NULL)::int DESC,
+ (nullif(supplier,'') IS NOT NULL)::int DESC,
+ (nullif(jpg_name,'') IS NOT NULL)::int DESC;
+
+DROP TABLE IF EXISTS fm_wallpaper_live;
+ALTER TABLE fm_wallpaper_live_new RENAME TO fm_wallpaper_live;
+CREATE INDEX fm_wallpaper_live_combo_idx ON fm_wallpaper_live(combo_sku);
+
+-- ===== safe fill-only merge into fmpro (NEVER nulls vendor-join cols phone/email/vendor_code/account) =====
+UPDATE fmpro f SET
+ mfr_sku = COALESCE(NULLIF(f.mfr_sku,''), l.mfr_pattern),
+ sku_prefix = COALESCE(NULLIF(f.sku_prefix,''), l.series),
+ image_filename = COALESCE(NULLIF(f.image_filename,''), l.jpg_name)
+FROM fm_wallpaper_live l WHERE upper(f.sku) = l.combo_sku;
+
+INSERT INTO fmpro (id, sku, mfr_sku, sku_prefix, image_filename, vendor_name, added_date)
+SELECT (SELECT COALESCE(max(id),0) FROM fmpro) + row_number() OVER (),
+ l.combo_sku, l.mfr_pattern, l.series, l.jpg_name, l.supplier, CURRENT_DATE
+FROM fm_wallpaper_live l
+WHERE NOT EXISTS (SELECT 1 FROM fmpro f WHERE upper(f.sku) = l.combo_sku);
+
+-- ===== same into master_fmpro (id is serial+PK, omit it) =====
+UPDATE master_fmpro f SET
+ mfr_sku = COALESCE(NULLIF(f.mfr_sku,''), l.mfr_pattern),
+ sku_prefix = COALESCE(NULLIF(f.sku_prefix,''), l.series),
+ image_filename = COALESCE(NULLIF(f.image_filename,''), l.jpg_name)
+FROM fm_wallpaper_live l WHERE upper(f.sku) = l.combo_sku;
+
+INSERT INTO master_fmpro (sku, mfr_sku, sku_prefix, image_filename, vendor_name, added_date)
+SELECT l.combo_sku, l.mfr_pattern, l.series, l.jpg_name, l.supplier, CURRENT_DATE
+FROM fm_wallpaper_live l
+WHERE NOT EXISTS (SELECT 1 FROM master_fmpro f WHERE upper(f.sku) = l.combo_sku);
+
+SELECT
+ (SELECT count(*) FROM fm_wallpaper_live) AS live_distinct_combosku,
+ (SELECT count(*) FROM fmpro) AS fmpro_rows,
+ (SELECT count(*) FROM master_fmpro) AS master_fmpro_rows;
+
+COMMIT;
diff --git a/build_dryrun.sql b/build_dryrun.sql
new file mode 100644
index 0000000..897a6c1
--- /dev/null
+++ b/build_dryrun.sql
@@ -0,0 +1,52 @@
+\set ON_ERROR_STOP on
+BEGIN;
+CREATE TEMP TABLE fm_wallpaper_stage (
+ combo_sku text, series text, js_pattern text, mfr_pattern text, supplier text,
+ jpg_name text, width text, border text, rpt text, five_ten text, account text,
+ line text, minimum text, date_line text, page text, retail text, cost text, net text,
+ record_id text
+) ON COMMIT DROP;
+\copy fm_wallpaper_stage FROM '__CSV__' WITH (FORMAT csv, HEADER true)
+DO $$ DECLARE n int; BEGIN SELECT count(*) INTO n FROM fm_wallpaper_stage;
+ IF n < 250000 THEN RAISE EXCEPTION 'stage row count % below floor', n; END IF; END $$;
+
+CREATE TEMP TABLE _pre AS SELECT
+ (SELECT count(*) FROM fmpro) rows,
+ (SELECT count(*) FILTER (WHERE nullif(phone,'') IS NOT NULL) FROM fmpro) phone,
+ (SELECT count(*) FILTER (WHERE nullif(email_1,'') IS NOT NULL) FROM fmpro) email,
+ (SELECT count(*) FILTER (WHERE nullif(vendor_code,'') IS NOT NULL) FROM fmpro) vcode,
+ (SELECT count(*) FILTER (WHERE nullif(account_num,'') IS NOT NULL) FROM fmpro) acct;
+
+DROP TABLE IF EXISTS fm_wallpaper_live_new;
+CREATE TABLE fm_wallpaper_live_new AS
+SELECT DISTINCT ON (norm) norm AS combo_sku,
+ nullif(upper(series),'') series, nullif(js_pattern,'') js_pattern,
+ nullif(split_part(mfr_pattern,' -- ',1),'') mfr_pattern, nullif(supplier,'') supplier,
+ nullif(jpg_name,'') jpg_name, record_id, now() refreshed_at
+FROM (SELECT *, upper(regexp_replace(combo_sku,'[^A-Za-z0-9]','','g')) norm FROM fm_wallpaper_stage) s
+WHERE norm ~ '^[A-Z]+[0-9]+'
+ORDER BY norm, (nullif(mfr_pattern,'') IS NOT NULL)::int DESC, (nullif(supplier,'') IS NOT NULL)::int DESC;
+DROP TABLE IF EXISTS fm_wallpaper_live;
+ALTER TABLE fm_wallpaper_live_new RENAME TO fm_wallpaper_live;
+
+UPDATE fmpro f SET mfr_sku=COALESCE(NULLIF(f.mfr_sku,''),l.mfr_pattern),
+ sku_prefix=COALESCE(NULLIF(f.sku_prefix,''),l.series),
+ image_filename=COALESCE(NULLIF(f.image_filename,''),l.jpg_name)
+FROM fm_wallpaper_live l WHERE upper(f.sku)=l.combo_sku;
+INSERT INTO fmpro (id, sku, mfr_sku, sku_prefix, image_filename, vendor_name, added_date)
+SELECT (SELECT COALESCE(max(id),0) FROM fmpro)+row_number() OVER (),
+ l.combo_sku,l.mfr_pattern,l.series,l.jpg_name,l.supplier,CURRENT_DATE
+FROM fm_wallpaper_live l WHERE NOT EXISTS (SELECT 1 FROM fmpro f WHERE upper(f.sku)=l.combo_sku);
+
+\echo '=== PRE (fmpro) ==='
+SELECT * FROM _pre;
+\echo '=== POST (fmpro) — phone/email/vcode/acct must NOT drop ==='
+SELECT (SELECT count(*) FROM fmpro) rows,
+ count(*) FILTER (WHERE nullif(phone,'') IS NOT NULL) phone,
+ count(*) FILTER (WHERE nullif(email_1,'') IS NOT NULL) email,
+ count(*) FILTER (WHERE nullif(vendor_code,'') IS NOT NULL) vcode,
+ count(*) FILTER (WHERE nullif(account_num,'') IS NOT NULL) acct FROM fmpro;
+\echo '=== deltas ==='
+SELECT (SELECT count(*) FROM fmpro)-(SELECT rows FROM _pre) AS fmpro_new_rows,
+ (SELECT count(*) FROM fm_wallpaper_live) AS live_distinct;
+ROLLBACK;
diff --git a/run.sh b/run.sh
new file mode 100755
index 0000000..9ae447a
--- /dev/null
+++ b/run.sh
@@ -0,0 +1,52 @@
+#!/bin/zsh
+# Nightly FileMaker WALLPAPER -> postgres refresh (1am via launchd).
+# 1) pull live FM 2) rebuild fm_wallpaper_live + fill-merge fmpro/master_fmpro
+# 3) regenerate the missing-from-FM audit CSV 4) write heartbeat.
+set -uo pipefail
+DIR="$HOME/Projects/fm-wallpaper-sync"
+CSV="/tmp/fm_wallpaper_full.csv"
+AUDIT_DIR="$HOME/Desktop/fm-wallpaper-audit"
+NODE="/opt/homebrew/bin/node"
+PSQL="/opt/homebrew/opt/postgresql@16/bin/psql"; [ -x "$PSQL" ] || PSQL="$(command -v psql)"
+PG=(-h 127.0.0.1 -U dw_admin -d dw_unified)
+TS="$(date +%Y%m%d)"; LOG="$DIR/logs/sync-$TS.log"
+mkdir -p "$DIR/logs" "$AUDIT_DIR"
+exec >>"$LOG" 2>&1
+echo "===== $(date) START ====="
+fail(){ echo "FAILED: $1"; print -r -- "{\"ok\":false,\"stage\":\"$1\",\"at\":\"$(date -u +%FT%TZ)\"}" > "$DIR/latest.json"; exit 1; }
+
+echo "-- pull"
+$NODE "$DIR/sync.mjs" "$CSV" || fail pull
+[ -s "$CSV" ] || fail pull_empty
+
+echo "-- build+merge"
+sed "s#__CSV__#$CSV#g" "$DIR/build.sql" | $PSQL "${PG[@]}" -f - || fail build
+
+echo "-- regenerate audit CSV"
+$PSQL "${PG[@]}" -c "\COPY (
+ WITH act AS (
+ SELECT dw_sku, substring(dw_sku from '^[A-Z]+') fm_series, substring(dw_sku from '[0-9]+\$') fm_js_pattern,
+ mfr_sku fm_mfr_pattern, vendor, title, product_type, raw_sku, price, cost, handle, shopify_id,
+ created_at_shopify, image_url
+ FROM (
+ SELECT substring(upper(regexp_replace(coalesce(variant_sku,sku),'[^A-Za-z0-9]','','g')) from '^[A-Z]+[0-9]+') dw_sku,
+ coalesce(variant_sku,sku) raw_sku, coalesce(nullif(mfr_sku,''),'') mfr_sku,
+ coalesce(vendor,supplier_name,'') vendor, coalesce(nullif(title,''),pattern_name,'') title,
+ coalesce(product_type,'') product_type, price, cost, handle, shopify_id, created_at_shopify, image_url,
+ row_number() OVER (PARTITION BY substring(upper(regexp_replace(coalesce(variant_sku,sku),'[^A-Za-z0-9]','','g')) from '^[A-Z]+[0-9]+')
+ ORDER BY created_at_shopify DESC NULLS LAST) rn
+ FROM shopify_products WHERE status='ACTIVE' AND coalesce(variant_sku,sku) IS NOT NULL
+ ) x WHERE rn=1 AND dw_sku IS NOT NULL AND dw_sku<>''
+ )
+ SELECT a.dw_sku, a.fm_series, a.fm_js_pattern, a.fm_mfr_pattern, a.vendor, a.title, a.product_type,
+ a.raw_sku, a.price, a.cost, a.handle, a.shopify_id, a.created_at_shopify, a.image_url
+ FROM act a WHERE NOT EXISTS (SELECT 1 FROM fm_wallpaper_live f WHERE f.combo_sku=a.dw_sku)
+ ORDER BY a.vendor, a.dw_sku
+) TO '$AUDIT_DIR/MISSING_from_FM_wallpaper_ALL.csv' WITH (FORMAT csv, HEADER true)" || fail audit_export
+
+LIVE=$($PSQL "${PG[@]}" -tAc "SELECT count(*) FROM fm_wallpaper_live")
+MISS=$(wc -l < "$AUDIT_DIR/MISSING_from_FM_wallpaper_ALL.csv" | tr -d ' '); MISS=$((MISS-1))
+FMPRO=$($PSQL "${PG[@]}" -tAc "SELECT count(*) FROM fmpro")
+print -r -- "{\"ok\":true,\"at\":\"$(date -u +%FT%TZ)\",\"live_distinct_combosku\":$LIVE,\"fmpro_rows\":$FMPRO,\"missing_from_fm\":$MISS}" > "$DIR/latest.json"
+echo "-- done live=$LIVE fmpro=$FMPRO missing=$MISS"
+echo "===== $(date) OK ====="
diff --git a/sync.mjs b/sync.mjs
new file mode 100755
index 0000000..c2e0f2c
--- /dev/null
+++ b/sync.mjs
@@ -0,0 +1,55 @@
+#!/usr/bin/env node
+// Nightly: pull ALL live FileMaker WALLPAPER records → /tmp/fm_wallpaper_full.csv
+// Reuses the filemaker-mcp Claris-ID client. Read-only against FileMaker.
+// Creds are read live from ~/.claude.json (no secret duplication).
+import fs from 'fs';
+import os from 'os';
+import path from 'path';
+
+const cfg = JSON.parse(fs.readFileSync(path.join(os.homedir(), '.claude.json'), 'utf8'));
+const env = cfg.mcpServers?.filemaker?.env || {};
+for (const [k, v] of Object.entries(env)) if (v != null) process.env[k] = String(v);
+
+const fm = await import('/Users/macstudio3/Projects/filemaker-mcp/src/fm-client.js');
+
+const DB = 'WALLPAPER', LAYOUT = 'Basic List of Fields', PAGE = 1000;
+const OUT = process.argv[2] || '/tmp/fm_wallpaper_full.csv';
+
+// FM field name -> CSV column
+const F = [
+ ['combo sku','combo_sku'], ['Series','series'], ['JS Pattern','js_pattern'],
+ ['Mfr Pattern','mfr_pattern'], ['Supplier','supplier'], ['JPG Name','jpg_name'],
+ ['Width','width'], ['Border','border'], ['Repeat','repeat'], ['Five Ten Code','five_ten'],
+ ['Account #','account'], ['Line','line'], ['Minimum','minimum'],
+ ['Date Line Put Up:','date_line'], ['Page','page'],
+ ['Retail Price','retail'], ['Cost','cost'], ['Net Price','net'],
+];
+const HEADER = [...F.map(x => x[1]), 'record_id'];
+const q = s => '"' + String(s ?? '').replace(/"/g, '""') + '"';
+
+const out = fs.createWriteStream(OUT);
+out.write(HEADER.join(',') + '\n');
+
+let offset = 1, total = null, pages = 0, written = 0;
+const t0 = Date.now();
+while (true) {
+ let res, tries = 0;
+ while (true) {
+ try { res = await fm.listRecords(DB, LAYOUT, { limit: PAGE, offset }); break; }
+ catch (e) { if (++tries >= 4) throw e; await new Promise(r => setTimeout(r, 1500 * tries)); }
+ }
+ total = res?.dataInfo?.totalRecordCount ?? total;
+ const recs = res?.records || [];
+ if (!recs.length) break;
+ for (const r of recs) {
+ const fd = r.fieldData || {};
+ const row = F.map(([fmName]) => q(fd[fmName])).concat(q(r.recordId));
+ out.write(row.join(',') + '\n');
+ written++;
+ }
+ pages++; offset += PAGE;
+ if (pages % 25 === 0) console.error(` page ${pages} offset ${offset - 1}/${total} written=${written} ${((Date.now()-t0)/1000|0)}s`);
+ if (total && offset > total) break;
+}
+await new Promise(r => out.end(r));
+console.error(`PULL DONE pages=${pages} total=${total} written=${written} -> ${OUT} (${((Date.now()-t0)/1000|0)}s)`);
(oldest)
·
back to Fm Wallpaper Sync
·
auto-data-snapshot: 2026-08-12T09:25:42 (1 data files) — bui ebc0a69 →