`README.md`

44 lines
# 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.