auto-save: 2026-07-16T09:42:29 (1 files) — scripts/audit-clients.mjs

8e0d01f20440a18574fcc2231d1fe51c6abf7cdb · 2026-07-16 09:42:40 -0700 · Steve Abrams
Files touched

A scripts/audit-clients.mjs
Diff

commit 8e0d01f20440a18574fcc2231d1fe51c6abf7cdb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Jul 16 09:42:40 2026 -0700

    auto-save: 2026-07-16T09:42:29 (1 files) — scripts/audit-clients.mjs
---
 scripts/audit-clients.mjs | 83 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 83 insertions(+)

diff --git a/scripts/audit-clients.mjs b/scripts/audit-clients.mjs
new file mode 100644
index 0000000..dc44e97
--- /dev/null
+++ b/scripts/audit-clients.mjs
@@ -0,0 +1,83 @@
+#!/usr/bin/env node
+// READ-ONLY audit of the FileMaker Clients file. Detects (a) records whose
+// Company/Name fields are swapped (business name sitting in Name, a person in
+// Company — the signature of the fixed bug) and (b) duplicate clients (same
+// email, same phone, or same company). Writes NOTHING. $0 local.
+import { readFileSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+import * as fm from '../src/fm-client.js';
+
+const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
+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 LAYOUT = 'wALLPAPER oPTIONS';
+const norm = (s) => (s || '').toString().trim().toLowerCase().replace(/\s+/g, ' ');
+const last10 = (s) => (s || '').toString().replace(/\D/g, '').slice(-10);
+
+// business-name signal — word-boundary tokens that a PERSON name would not carry
+const BIZ = /\b(construction|inc|inc\.|llc|l\.l\.c|corp|corporation|company|co\.|design|designs|interiors|interior|studio|studios|group|electric|electrical|kitchen|kitchens|cabinet|cabinets|builders|building|homes|realty|architect|architects|architecture|associates|assoc|ltd|enterprises|contracting|contractors|remodel|remodeling|properties|property|development|developers|furnishings|decor|décor|flooring|floors|paint|painting|hospitality|hotel|hotels|restaurant|salon|spa|boutique|gallery|works|supply|services|solutions|systems|international|motors|ford|chevrolet|toyota|group|partners|lighting|millwork|woodworks|granite|stone|tile|drywall|plumbing|hvac|roofing|landscape|landscaping|nursery|florist|bakery|cafe|grill|realtors|residential|commercial|custom)\b/i;
+const looksBiz = (s) => BIZ.test(s || '');
+// a person-ish string: 1–4 alphabetic words, no business token, no digits
+const looksPerson = (s) => { const t = (s || '').trim(); return t && !/\d/.test(t) && !looksBiz(t) && t.split(/\s+/).length <= 4; };
+
+async function main() {
+  const PAGE = 500, CAP_PAGES = 200; // safety cap = 100k records
+  let offset = 1, page = 0, total = null;
+  const recs = [];
+  while (page < CAP_PAGES) {
+    const { records, dataInfo } = await fm.listRecords('Clients', LAYOUT, { limit: PAGE, offset });
+    if (total === null) total = dataInfo?.foundCount ?? dataInfo?.totalRecordCount ?? null;
+    if (!records.length) break;
+    for (const r of records) {
+      const f = r.fieldData || {};
+      recs.push({ id: r.recordId, acct: f['Account Number'], company: f['Company'] || '', name: f['Name'] || '', email: f['Email address'] || '', phone: f['Phone'] || '' });
+    }
+    process.stderr.write(`\r  scanned ${recs.length}${total ? '/' + total : ''} …`);
+    if (records.length < PAGE) break;
+    offset += PAGE; page++;
+  }
+  process.stderr.write('\n');
+  const hitCap = page >= CAP_PAGES;
+
+  // (a) swapped fields: business in Name, person in Company
+  const swapped = recs.filter((r) => r.company && r.name && looksBiz(r.name) && looksPerson(r.company));
+
+  // (b) duplicates by email / phone / company
+  const groupBy = (key) => {
+    const g = new Map();
+    for (const r of recs) { const k = key(r); if (!k) continue; (g.get(k) || g.set(k, []).get(k)).push(r); }
+    return [...g.values()].filter((v) => v.length > 1);
+  };
+  const dupEmail = groupBy((r) => norm(r.email));
+  const dupPhone = groupBy((r) => last10(r.phone));
+  const dupCompany = groupBy((r) => norm(r.company));
+
+  const uniqRecordsInDupes = new Set([...dupEmail, ...dupPhone, ...dupCompany].flat().map((r) => r.id)).size;
+
+  console.log('\n════════ FileMaker Clients audit (READ-ONLY) ════════');
+  console.log(`Records scanned      : ${recs.length}${hitCap ? '  ⚠️  HIT PAGE CAP — not all records scanned' : ''}`);
+  console.log(`\n── (a) Swapped Company/Name (business in Name, person in Company) ──`);
+  console.log(`Flagged: ${swapped.length}`);
+  for (const r of swapped.slice(0, 20)) console.log(`   acct ${r.acct||'?'} | Company="${r.company}"  Name="${r.name}"`);
+  if (swapped.length > 20) console.log(`   … and ${swapped.length - 20} more`);
+
+  console.log(`\n── (b) Duplicate clients ──`);
+  console.log(`Same EMAIL   : ${dupEmail.length} group(s)`);
+  for (const g of dupEmail.slice(0, 10)) console.log(`   "${g[0].email}" ×${g.length}  → accts ${g.map((r) => r.acct).join(', ')}`);
+  if (dupEmail.length > 10) console.log(`   … and ${dupEmail.length - 10} more group(s)`);
+  console.log(`Same PHONE   : ${dupPhone.length} group(s)`);
+  for (const g of dupPhone.slice(0, 10)) console.log(`   "${last10(g[0].phone)}" ×${g.length}  → accts ${g.map((r) => r.acct).join(', ')}  [${g.map((r)=>r.company||r.name).slice(0,3).join(' / ')}]`);
+  if (dupPhone.length > 10) console.log(`   … and ${dupPhone.length - 10} more group(s)`);
+  console.log(`Same COMPANY : ${dupCompany.length} group(s)`);
+  for (const g of dupCompany.slice(0, 10)) console.log(`   "${g[0].company}" ×${g.length}  → accts ${g.map((r) => r.acct).join(', ')}`);
+  if (dupCompany.length > 10) console.log(`   … and ${dupCompany.length - 10} more group(s)`);
+
+  console.log(`\n── Summary ──`);
+  console.log(`Swapped-field records : ${swapped.length}`);
+  console.log(`Records in a dup group: ${uniqRecordsInDupes}  (email ${dupEmail.length} / phone ${dupPhone.length} / company ${dupCompany.length} groups)`);
+  console.log('No records were modified.\n');
+}
+main().catch((e) => { console.error('AUDIT ERROR:', e.message); process.exit(1); });
← 96da496 Harden client dedup + add read-only single-order test · back to Filemaker Mcp · chore: lint, refactor, v0.2.3 (session close) 7210236 →