← back to Filemaker Mcp
wallpaper: prevent duplicate masters via local Postgres idempotency ledger (FM Cloud index-lag guard) + test
7d38b740ec1ea5faaa9a3f7707d74e026566b7cf · 2026-08-12 09:22:27 -0700 · Steve Abrams
Files touched
M lib/wallpaper.jsA scripts/test-dup-ledger.mjs
Diff
commit 7d38b740ec1ea5faaa9a3f7707d74e026566b7cf
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 12 09:22:27 2026 -0700
wallpaper: prevent duplicate masters via local Postgres idempotency ledger (FM Cloud index-lag guard) + test
---
lib/wallpaper.js | 83 ++++++++++++++++++++++++++++++++++++++++++---
scripts/test-dup-ledger.mjs | 34 +++++++++++++++++++
2 files changed, 113 insertions(+), 4 deletions(-)
diff --git a/lib/wallpaper.js b/lib/wallpaper.js
index b74b924..f494b5a 100644
--- a/lib/wallpaper.js
+++ b/lib/wallpaper.js
@@ -17,6 +17,64 @@ function sql(q) {
try { return JSON.parse(execFileSync(PSQL, ['dw_unified', '-tAc', q], { encoding: 'utf8' }).trim() || 'null'); }
catch { return null; }
}
+// Escape a value for a single-quoted SQL literal.
+const sqlEsc = (v) => String(v ?? '').replace(/'/g, "''");
+// Run a non-SELECT statement (DDL / INSERT / UPDATE / DELETE). Returns trimmed stdout
+// (e.g. a RETURNING value) or null on error. Kept separate from sql() which JSON-parses.
+function sqlExec(q) {
+ // -q (quiet) is REQUIRED: without it psql writes the command-status tag ("INSERT 0 0")
+ // to stdout, which a no-op ON CONFLICT would return as a non-empty string and be
+ // misread as "claim won" — the RETURNING contract only holds when the tag is suppressed.
+ try { return execFileSync(PSQL, ['dw_unified', '-qtAc', q], { encoding: 'utf8' }).trim(); }
+ catch { return null; }
+}
+
+// ---- LOCAL IDEMPOTENCY LEDGER (duplicate-master prevention) -----------------------------
+// FileMaker Cloud does NOT index a freshly-created record for finds for a short-but-nonzero
+// window, so a re-run (or a concurrent run) whose existence check (findExistingMaster) runs
+// inside that window misses the just-created master and mints a SECOND one — the verified
+// duplicate pattern (every dup pair shares an identical Series|JS-Pattern and both records
+// are fully populated => a genuine double-create, not a partial/split bug). A Postgres row
+// is IMMEDIATELY consistent where FileMaker's index lags, so we record every master WE
+// create keyed on the normalized SKU and consult it BEFORE creating. The UNIQUE PK also
+// serializes two concurrent creators (only one wins the claim; the loser skips the create).
+let _ledgerReady = false;
+function ledgerInit() {
+ if (_ledgerReady) return;
+ sqlExec(`CREATE TABLE IF NOT EXISTS wallpaper_master_ledger (
+ norm_key text PRIMARY KEY, series text, js_pattern text, fm_record_id text,
+ mfr text, vid text, created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now())`);
+ _ledgerReady = true;
+}
+// The FileMaker recordId we've already recorded for this normalized key (only once the
+// create actually succeeded), or '' if none.
+function ledgerLookup(normKey) {
+ ledgerInit();
+ const row = sql(`SELECT json_build_object('id', fm_record_id) FROM wallpaper_master_ledger WHERE norm_key='${sqlEsc(normKey)}' AND fm_record_id IS NOT NULL`);
+ return (row && row.id) ? String(row.id) : '';
+}
+// Atomically CLAIM the key before creating in FileMaker. TRUE => we own the claim (proceed
+// to create); FALSE => a live peer already holds it (skip the create, no duplicate). A
+// prior claim that never recorded an fm_record_id and is >5 min old is treated as abandoned
+// (a create that died) and may be reclaimed.
+function ledgerClaim(normKey, series, jsPattern) {
+ ledgerInit();
+ const out = sqlExec(`INSERT INTO wallpaper_master_ledger(norm_key,series,js_pattern)
+ VALUES('${sqlEsc(normKey)}','${sqlEsc(series)}','${sqlEsc(jsPattern)}')
+ ON CONFLICT(norm_key) DO UPDATE SET series=EXCLUDED.series, js_pattern=EXCLUDED.js_pattern, updated_at=now()
+ WHERE wallpaper_master_ledger.fm_record_id IS NULL AND wallpaper_master_ledger.updated_at < now() - interval '5 minutes'
+ RETURNING norm_key`);
+ return !!(out && out.length);
+}
+// Record the FileMaker recordId (+ mfr/vid) once the master is actually created.
+function ledgerRecord(normKey, id, mfr, vid) {
+ ledgerInit();
+ sqlExec(`UPDATE wallpaper_master_ledger SET fm_record_id='${sqlEsc(id)}', mfr='${sqlEsc(mfr)}', vid='${sqlEsc(vid)}', updated_at=now() WHERE norm_key='${sqlEsc(normKey)}'`);
+}
+// Release a claim whose create FAILED, so a later run can retry instead of dead-locking.
+function ledgerRelease(normKey) {
+ sqlExec(`DELETE FROM wallpaper_master_ledger WHERE norm_key='${sqlEsc(normKey)}' AND fm_record_id IS NULL`);
+}
const firstNum = (s) => { const m = String(s || '').match(/[\d.]+/); return m ? m[0] : ''; };
const widthClean = (s) => { const n = firstNum(s); return n ? `${n}"` : ''; };
@@ -441,9 +499,25 @@ export async function ensureWallpaper(combo) {
if (!String(p.prefix || '').trim()) {
return { ok: false, flagged: combo, reason: 'blank-series — refusing to create a master with an empty Series' };
}
- const res = await fm.createRecord('WALLPAPER', ENTRY, { 'Mfr Pattern': s.mfr, 'JS Pattern': p.num, Series: p.prefix, Supplier: s.supplier || '', Width: width, Repeat: repeat || '' }, { dryRun: false }).catch((e) => ({ err: e.fmCode }));
- if (res.err) return { ok: false, flagged: combo, reason: `create failed ${res.err}` };
- id = res.recordId;
+ // IDEMPOTENCY LEDGER GUARD — FileMaker's find missed any master, but that can be an
+ // index-lag false negative on a record WE just created. Consult the immediately-
+ // consistent Postgres ledger before minting a duplicate.
+ const ledId = ledgerLookup(p.key);
+ if (ledId) {
+ id = ledId; // already created (FM just hasn't indexed it yet) — reuse, don't duplicate
+ } else if (!ledgerClaim(p.key, p.prefix, p.num)) {
+ // A live peer run holds the claim mid-create; bail WITHOUT minting a twin. A later
+ // pass resolves it (by then the peer has recorded its recordId or FM has indexed it).
+ const peer = ledgerLookup(p.key);
+ if (peer) { id = peer; }
+ else return { ok: false, flagged: combo, reason: 'create in-flight by a peer run — no duplicate minted, retry shortly' };
+ }
+ if (!id) {
+ const res = await fm.createRecord('WALLPAPER', ENTRY, { 'Mfr Pattern': s.mfr, 'JS Pattern': p.num, Series: p.prefix, Supplier: s.supplier || '', Width: width, Repeat: repeat || '' }, { dryRun: false }).catch((e) => ({ err: e.fmCode }));
+ if (res.err) { ledgerRelease(p.key); return { ok: false, flagged: combo, reason: `create failed ${res.err}` }; }
+ id = res.recordId;
+ ledgerRecord(p.key, id, s.mfr, vid); // remember it so a re-run inside FM's index-lag window can't duplicate
+ }
} else {
for (const [k, v] of [['Mfr Pattern', s.mfr], ['Supplier', s.supplier || ''], ['Width', width], ['Repeat', repeat]]) { if (v) { try { await fm.updateRecord('WALLPAPER', ENTRY, id, { [k]: v }, { dryRun: false }); } catch {} } }
}
@@ -459,4 +533,5 @@ export async function ensureWallpaper(combo) {
// Internal helpers exported for the TK-10083 live-proof dry-run so it exercises the ACTUAL
// committed logic (not a re-implementation). findExistingMaster is READ-ONLY (only fm.find).
-export const _internals = { parseCombo, normalizeSku, findExistingMaster, canonicalDashFor, splitCandidates, sourceFor, mfrByNumber };
+export const _internals = { parseCombo, normalizeSku, findExistingMaster, canonicalDashFor, splitCandidates, sourceFor, mfrByNumber,
+ ledgerInit, ledgerLookup, ledgerClaim, ledgerRecord, ledgerRelease };
diff --git a/scripts/test-dup-ledger.mjs b/scripts/test-dup-ledger.mjs
new file mode 100644
index 0000000..8917cbf
--- /dev/null
+++ b/scripts/test-dup-ledger.mjs
@@ -0,0 +1,34 @@
+// Idempotency-ledger proof for the duplicate-master prevention (DTD verdict C, 2026-08-12).
+// Exercises the ACTUAL committed ledger functions against Postgres — NO FileMaker writes —
+// to prove: a re-run inside FM's index-lag window reuses the recorded record instead of
+// minting a twin, a concurrent second creator is blocked, and a failed create is retryable.
+// node scripts/test-dup-ledger.mjs
+import { readFileSync } from 'node:fs';
+for (const l of readFileSync('.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,''); }
+import { execFileSync } from 'node:child_process';
+const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
+const { _internals } = await import('../lib/wallpaper.js');
+const { ledgerInit, ledgerLookup, ledgerClaim, ledgerRecord, ledgerRelease } = _internals;
+const K='ZZTESTPREVENT001', K2='ZZTESTPREVENT002';
+let pass=0, fail=0; const ok=(c,m)=>{ if(c){pass++;console.log(' ✓',m);} else {fail++;console.log(' ✗ FAIL',m);} };
+const clean=()=>{ try{ execFileSync(PSQL,['dw_unified','-tAc',`DELETE FROM wallpaper_master_ledger WHERE norm_key IN ('${K}','${K2}')`]); }catch{} };
+ledgerInit(); clean();
+console.log('[1] fresh key: nothing recorded');
+ok(ledgerLookup(K)==='', 'ledgerLookup empty before any create');
+console.log('[2] first creator wins the claim, a concurrent 2nd is blocked (no twin)');
+ok(ledgerClaim(K,'ZZ','TESTPREVENT001')===true, 'first ledgerClaim wins');
+ok(ledgerClaim(K,'ZZ','TESTPREVENT001')===false, 'immediate 2nd ledgerClaim BLOCKED (fresh, not stale) -> no duplicate create');
+ok(ledgerLookup(K)==='', 'lookup still empty (create not finished -> no id yet)');
+console.log('[3] create finished: record id -> a re-run REUSES it instead of minting a twin');
+ledgerRecord(K,'999999','TESTMFR','TESTVID');
+ok(ledgerLookup(K)==='999999', 'ledgerLookup returns recorded fm_record_id (re-run skips create)');
+ok(ledgerClaim(K,'ZZ','TESTPREVENT001')===false, 'claim still blocked after record (recorded key is not reclaimable)');
+console.log('[4] failed create is retryable: release the un-recorded claim');
+ok(ledgerClaim(K2,'ZZ','TESTPREVENT002')===true, 'claim K2');
+ledgerRelease(K2);
+ok(ledgerLookup(K2)==='', 'K2 released');
+ok(ledgerClaim(K2,'ZZ','TESTPREVENT002')===true, 'K2 reclaimable after release (failed create can retry)');
+ok(ledgerRelease(K) || ledgerLookup(K)==='999999', 'ledgerRelease is a NO-OP on a recorded key (never drops a real master)');
+clean();
+console.log(`\n=== ${fail? 'FAIL':'ALL PASS'} (${pass} passed, ${fail} failed) ===`);
+process.exit(fail?1:0);
← dcd8c12 add dedup-dwpp-32837 script + 539333/539334 restore archives
·
back to Filemaker Mcp
·
chore: lint (env-path, dedup keep-guard, test readability), a2688ce →