← back to Filemaker Mcp
Add read-only mfr review-queue generator (TK-10906 deliverable 2)
66e87e3708ab4ad0911a9b9f0e52c68af0074845 · 2026-08-27 07:47:03 -0700 · Steve Abrams
scripts/mfr-review-queue.mjs: for a vendor list, emits data/mfr-review-queue.jsonl of the
broken DW#==mfr rows with a best-guess real mfr + source + confidence for staff to confirm
before any (Steve-gated) repair. Sources in order: dw_sku alpha-prefix (Tier-2, high),
READ-ONLY FileMaker Mfr# note (medium), else none (needs vendor re-scrape). ZERO writes to
dw_unified / FileMaker / Shopify. Default Tier-3 set excludes Schumacher + pure-numeric-legit.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A scripts/mfr-review-queue.mjs
Diff
commit 66e87e3708ab4ad0911a9b9f0e52c68af0074845
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 27 07:47:03 2026 -0700
Add read-only mfr review-queue generator (TK-10906 deliverable 2)
scripts/mfr-review-queue.mjs: for a vendor list, emits data/mfr-review-queue.jsonl of the
broken DW#==mfr rows with a best-guess real mfr + source + confidence for staff to confirm
before any (Steve-gated) repair. Sources in order: dw_sku alpha-prefix (Tier-2, high),
READ-ONLY FileMaker Mfr# note (medium), else none (needs vendor re-scrape). ZERO writes to
dw_unified / FileMaker / Shopify. Default Tier-3 set excludes Schumacher + pure-numeric-legit.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
scripts/mfr-review-queue.mjs | 162 +++++++++++++++++++++++++++++++++++++++++++
1 file changed, 162 insertions(+)
diff --git a/scripts/mfr-review-queue.mjs b/scripts/mfr-review-queue.mjs
new file mode 100644
index 0000000..5416e7b
--- /dev/null
+++ b/scripts/mfr-review-queue.mjs
@@ -0,0 +1,162 @@
+// TK-10906 — READ-ONLY mfr review-queue generator.
+//
+// For a given vendor list, emit a per-item review queue of the BROKEN (DW#==mfr) rows with a
+// BEST-GUESS real mfr + its source + a confidence, for STAFF to confirm before any write.
+// The actual mfr repair is Steve-gated and runs SEPARATELY from this queue after he confirms
+// the vendor set. This tool makes ZERO writes to dw_unified, FileMaker, or Shopify — it only
+// SELECTs from the local dw_unified mirror and (optionally) does READ-ONLY FileMaker finds.
+//
+// node scripts/mfr-review-queue.mjs # default Tier-3 vendor set, ~50 rows
+// node scripts/mfr-review-queue.mjs --limit 50 # cap total rows
+// node scripts/mfr-review-queue.mjs --no-fm # skip the FileMaker-note lookups (pure PG)
+// node scripts/mfr-review-queue.mjs --vendors "Glitter Walls,Wolf Gordon"
+//
+// Output: data/mfr-review-queue.jsonl (one JSON object per broken SKU)
+// { dw_sku, bad_mfr, vendor, pattern, best_guess_real_mfr, source, confidence }
+// source ∈ { dwsku-alpha-prefix, filemaker-note, vendor-feed, none }
+import { execFileSync } from 'node:child_process';
+import { writeFileSync, mkdirSync, readFileSync, existsSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+
+const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
+const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
+const OUT = join(ROOT, 'data', 'mfr-review-queue.jsonl');
+
+// ---- args -------------------------------------------------------------------------------
+const args = process.argv.slice(2);
+const flag = (n) => args.includes(n);
+const opt = (n, d) => { const i = args.indexOf(n); return i >= 0 && args[i + 1] ? args[i + 1] : d; };
+const LIMIT = parseInt(opt('--limit', '50'), 10);
+const USE_FM = !flag('--no-fm');
+
+// DEFAULT Tier-3 vendor set — EXCLUDING the Schumacher family (DW# IS their real mfr) and the
+// pure-numeric-legit vendors. These are confirmed to carry the DW#==mfr signature.
+const DEFAULT_VENDORS = [
+ 'Glitter Walls', 'Hollywood Wallcoverings', 'LA Walls', 'DW Bespoke Studio',
+ 'Jeffrey Stevens', 'Graham & Brown', 'Ralph Lauren', 'Anna French',
+ 'Wolf Gordon', 'Missoni', 'IKSEL', 'Versace',
+];
+const VENDORS = opt('--vendors', '') ? opt('--vendors', '').split(',').map((v) => v.trim()).filter(Boolean) : DEFAULT_VENDORS;
+
+// ---- READ-ONLY psql helpers -------------------------------------------------------------
+const sqlEsc = (v) => String(v ?? '').replace(/'/g, "''");
+function sqlRows(q) {
+ // -F $'\t' tab-separated, -A unaligned, -t tuples-only. Purely a SELECT — read-only.
+ try {
+ const out = execFileSync(PSQL, ['dw_unified', '-tAF', '\t', '-c', q], { encoding: 'utf8' });
+ return out.split('\n').filter(Boolean).map((l) => l.split('\t'));
+ } catch (e) { console.error('psql read failed:', e.message); return []; }
+}
+
+// ---- guard helpers (same semantics as lib/wallpaper.js — pure, no writes) ----------------
+const dwTail = (dwSku) => (String(dwSku || '').match(/(\d+)(?:[-_ ]?sample)?$/i) || [])[1] || '';
+function recoverAlphaMfr(dwSku, badMfr) {
+ const bad = String(badMfr || '').trim();
+ if (!bad || !/^\d+$/.test(bad)) return '';
+ const seg = String(dwSku || '').replace(/[-_ ]?sample$/i, '').split(/[-_ ]/).pop() || '';
+ const m = seg.match(/^([A-Za-z]+)(\d+)$/);
+ if (m && m[2] === bad) return (m[1] + m[2]).toUpperCase();
+ return '';
+}
+
+// ---- optional READ-ONLY FileMaker note lookup -------------------------------------------
+// Some real codes live only on the FileMaker WALLPAPER master (the "Mfr #<code>" note /
+// Mfr Pattern field) and never made it back into dw_unified. We do a READ-ONLY find for the
+// master and read its Mfr Pattern / MetDataSearchWord — but ONLY report it as a guess when
+// it differs from the DW# placeholder. NEVER writes.
+let fm = null;
+if (USE_FM) {
+ try {
+ // load .env for FM creds (same pattern as the repo's other scripts)
+ if (existsSync(join(ROOT, '.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].replace(/^['"]|['"]$/g, '');
+ }
+ }
+ fm = await import('../src/fm-client.js');
+ await fm.ping();
+ } catch (e) {
+ console.error(`FileMaker unavailable (${e.message.split('\n')[0]}) — continuing with PG-only sources.`);
+ fm = null;
+ }
+}
+
+const FULL = '*List Wallpapers - Full View';
+const normSku = (s) => String(s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
+async function fmNoteMfr(dwSku) {
+ if (!fm) return '';
+ // Split dw_sku into Series + pattern-segment (best-effort) and find the master READ-ONLY.
+ const clean = String(dwSku).replace(/[-_ ]?sample$/i, '');
+ const parts = clean.split(/[-_ ]/);
+ const series = parts.length > 1 ? parts[0] : '';
+ const pattern = parts.length > 1 ? parts.slice(1).join('-') : clean;
+ const q = series ? [{ Series: '==' + series, 'JS Pattern': '==' + pattern }] : [{ 'JS Pattern': '==' + pattern }];
+ try {
+ const r = await fm.findRecords('WALLPAPER', FULL, q, { limit: 3 });
+ for (const rec of (r.records || [])) {
+ const fd = rec.fieldData || {};
+ // confirm same SKU
+ if (normSku(String(fd.Series || '') + String(fd['JS Pattern'] || '')) !== normSku(clean)) continue;
+ const real = String(fd['Mfr Pattern'] || fd['Detail 1 Mfr Number'] || fd.MetDataSearchWord || '').trim();
+ // Only a guess if it's a REAL code that differs from the DW# numeric tail.
+ if (real && real !== dwTail(dwSku)) return real;
+ }
+ } catch { /* no-match / auth blip -> no guess */ }
+ return '';
+}
+
+// ---- gather the broken rows per vendor --------------------------------------------------
+// Broken (DW#==mfr) = mfr_sku equals the DW SKU's numeric tail. Read-only SELECT.
+mkdirSync(join(ROOT, 'data'), { recursive: true });
+const rows = [];
+const perVendor = {};
+// Distribute the row budget across vendors so the sample spans the whole set (round up),
+// rather than exhausting the cap on the first vendor.
+const perVendorCap = Math.max(1, Math.ceil(LIMIT / VENDORS.length));
+for (const vendor of VENDORS) {
+ if (rows.length >= LIMIT) break;
+ const cap = Math.min(perVendorCap, LIMIT - rows.length);
+ const q = `SELECT dw_sku, mfr_sku, vendor, COALESCE(pattern_name,'') FROM shopify_products
+ WHERE vendor ILIKE '%${sqlEsc(vendor)}%'
+ AND mfr_sku IS NOT NULL AND mfr_sku <> ''
+ AND mfr_sku = (regexp_match(dw_sku, '([0-9]+)(-[Ss]ample)?$'))[1]
+ ORDER BY dw_sku
+ LIMIT ${cap}`;
+ const found = sqlRows(q);
+ perVendor[vendor] = { total: found.length, alpha: 0, fmnote: 0, none: 0 };
+ for (const [dw_sku, bad_mfr, vend, pattern] of found) {
+ if (rows.length >= LIMIT) break;
+ // Source 1 (Tier-2, HIGH conf): dw_sku alpha-prefix recovery.
+ let best = recoverAlphaMfr(dw_sku, bad_mfr), source = best ? 'dwsku-alpha-prefix' : '', confidence = best ? 'high' : '';
+ // Source 2 (MEDIUM conf): READ-ONLY FileMaker Mfr# note on the master.
+ if (!best) {
+ const note = await fmNoteMfr(dw_sku);
+ if (note) { best = note; source = 'filemaker-note'; confidence = 'medium'; }
+ }
+ // else: none — needs a vendor re-scrape.
+ if (!best) { source = 'none'; confidence = 'none'; }
+ perVendor[vend || vendor] = perVendor[vendor];
+ perVendor[vendor][source === 'dwsku-alpha-prefix' ? 'alpha' : source === 'filemaker-note' ? 'fmnote' : 'none']++;
+ rows.push({ dw_sku, bad_mfr, vendor: vend || vendor, pattern, best_guess_real_mfr: best || null, source, confidence });
+ }
+}
+
+// ---- write the queue (the ONLY write this tool makes — to its own data file) -------------
+writeFileSync(OUT, rows.map((r) => JSON.stringify(r)).join('\n') + (rows.length ? '\n' : ''), 'utf8');
+
+// ---- report -----------------------------------------------------------------------------
+console.log(`mfr-review-queue: ${rows.length} broken rows across ${VENDORS.length} vendors -> ${OUT}`);
+console.log(`FileMaker note lookups: ${fm ? 'ON' : 'OFF (PG-only)'}\n`);
+console.log('per-vendor quality:');
+for (const v of VENDORS) {
+ const s = perVendor[v]; if (!s) continue;
+ console.log(` ${v.padEnd(24)} rows=${String(s.total).padStart(3)} alpha=${s.alpha} fm-note=${s.fmnote} none=${s.none}`);
+}
+const bySource = rows.reduce((a, r) => { a[r.source] = (a[r.source] || 0) + 1; return a; }, {});
+console.log('\nsample (first 12):');
+for (const r of rows.slice(0, 12)) {
+ console.log(` ${r.dw_sku.padEnd(18)} bad=${String(r.bad_mfr).padEnd(8)} -> ${String(r.best_guess_real_mfr || '(none)').padEnd(12)} [${r.source}/${r.confidence}] "${r.pattern.slice(0, 30)}"`);
+}
+console.log('\nby source:', JSON.stringify(bySource));
+console.log('\nZERO writes to dw_unified / FileMaker / Shopify. Confirm the vendor set + guesses, then run the (Steve-gated) repair separately.');
← e1069c1 Otto importer: guard against DW#==mfr placeholders; read rea
·
back to Filemaker Mcp
·
Guard fix: Schumacher family (DWSW/SCH) is legit-numeric — n b1dedc0 →