← back to Filemaker Mcp
lib/wallpaper.js
670 lines
// Ensure a complete FileMaker WALLPAPER master record exists for a DW SKU, sourced
// from dw_unified, so an invoice line's lookup fields (width/name/vid/supplier/mfr#)
// resolve. Steve's rule (2026-07-10): Mfr Pattern MUST hold a real mfr number and
// vid MUST be the vendor's code; a SKU with no findable mfr number is FLAGGED, not
// created. See memory filemaker-wallpaper-record-for-invoice.
//
// The invoice's width/supplier/name resolve LIVE once this record exists; VID and
// Detail 1 Mfr Number are COPY-lookups, so the caller writes them onto the line.
import { execFileSync } from 'node:child_process';
import * as fm from '../src/fm-client.js';
const ENTRY = 'Add wallcovering';
const FULL = '*List Wallpapers - Full View';
const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
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}"` : ''; };
// Canonical SKU key: UPPERCASE + strip ALL non-alphanumerics. Collapses the three
// inconsistent forms of a SKU into one comparable key so a lookup can't miss (and can't
// mint a duplicate master) just because separators differ across the invoice/calc/raw
// forms. Digits are PRESERVED, so numeric-suffix SKUs stay distinct:
// OPARTDECOWAVES / OP-ARTDECO-WAVES / OP-ART-DECO-WAVES -> OPARTDECOWAVES
// SCH-12345 -> SCH12345 ; SCH-12346 -> SCH12346 (still distinct)
const normalizeSku = (s) => String(s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
// KNOWN DW series prefixes whose "split at the first separator" is trustworthy. Used by
// parseCombo to decide `confident`. These are canonical DW series codes (DW + 2 letters,
// plus the cork aliases and a few legacy). A leading token NOT in this set means the
// Series|remainder boundary is a GUESS (e.g. OP-ART where the real series is `OP-ART`,
// not `OP`) and we must fail closed rather than mint a master off the guess. The set is
// intentionally conservative: unknown-but-real series still resolve through the
// component-field OR-find (which enumerates every split) — this gate only governs whether
// a genuinely-new SKU may be CREATED.
const KNOWN_SERIES = new Set([
'CORK', 'DWGL', 'DWLG',
'DWC', 'DWSW', 'DWWC', 'DWLK', 'DWJS', 'DWBR', 'DWPH', 'DWPP', 'DWAT', 'DWAF', 'DWKK',
]);
// Authoritative Series -> vid (vendor code) overrides for PRIVATE-LABEL series whose real
// vendor is hidden behind a DW house brand in dw_unified (so vid can't be derived from the
// source row) AND whose existing FileMaker masters carry a WRONG vid that the modal-vote
// vidForSeries() would otherwise perpetuate. Checked BEFORE an existing master's own vid so
// re-running corrects a previously-mis-stamped master. DWPP ("Phillipe Romano" London Paper
// Weave / Prague Samarra etc.) is really MDC Wallcoverings — vid MDCKEN, NOT Wolf Gordon
// ("wol"), which is what the 4 mislabeled DWPP masters had. (Steve, 2026-08-12.)
const SERIES_VID = { DWPP: 'MDCKEN' };
// combo sku (DWKK134979, OP-ART-DECO-WAVES) -> {prefix, num, dashSku, key, confident}
// prefix = the leading DW series letters; num = the rest (pattern remainder, which may
// itself be all-alpha and hyphenated, e.g. "ART-DECO-WAVES"). key = the normalized
// canonical form used for duplicate-safe master matching.
//
// `confident` (TK-10083 iter 2): TRUE only when the Series|remainder split is genuinely
// DERIVABLE from the string alone — i.e. classic letters-then-digits (DWKK134979) or a
// KNOWN series prefix. It is FALSE for the all-alpha, no-separator invoice form
// (OPARTDECOWAVES) AND for an all-ALPHA hyphenated compound (OP-ART-DECO-WAVES): in a
// FileMaker master the Series is `OP-ART` and the JS Pattern is `DECO-WAVES`, a split
// that is NOT recoverable by string rules (both OP|ART-DECO-WAVES and OP-ART-DECO|WAVES
// are equally plausible). Minting a master off a guessed split is exactly what created
// the duplicates 538697/538698 (Series `DWC`, wrong split). When confident===false the
// caller MUST NOT create — it fails closed to review (see resolveWallpaperSource).
function parseCombo(combo) {
const raw = String(combo || '').trim();
if (!raw) return null;
let prefix, num, confident;
// 1) Classic letters-then-digits, no separator: DWKK134979 -> prefix DWKK, num 134979.
// The alpha/digit boundary is unambiguous, so this split IS confident.
let m = raw.match(/^([A-Za-z]+)(\d.*)$/);
if (m) { prefix = m[1]; num = m[2]; confident = true; }
// 2) Separator-delimited with a KNOWN series prefix before the first '-'/'_'/space,
// e.g. DWGL-830880 or CORK-12 or DWC-OP-ART... — the leading token is a real DW
// series code we recognize, so the split is confident.
else if ((m = raw.match(/^([A-Za-z]+)[-_ ](.+)$/)) && KNOWN_SERIES.has(m[1].toUpperCase())) {
prefix = m[1]; num = m[2]; confident = true;
}
// 3) Separator-delimited but the leading token is NOT a known series — we CANNOT trust
// the naive "split at first dash" (that is what mis-split OP-ART-DECO-WAVES into
// OP|ART-DECO-WAVES). Keep a best-guess prefix for the series-vid fallback ONLY, but
// mark it NOT confident so no create happens off this guess.
else if ((m = raw.match(/^([A-Za-z]+)[-_ ](.+)$/))) { prefix = m[1]; num = m[2]; confident = false; }
// 4) All-alpha, no separator (invoice form OPARTDECOWAVES): no derivable split at all.
else { prefix = ''; num = raw; confident = false; }
prefix = prefix.toUpperCase();
const dashSku = prefix ? `${prefix}-${num}` : num;
return { prefix, num, dashSku, raw: raw.toUpperCase(), key: normalizeSku(raw), confident };
}
// Series that are the SAME physical line under different DW prefixes (renamed over
// time — e.g. Greenland cork bounced Cork<->DWGL<->DWLG). `combo sku` = Series & JS
// Pattern is a FileMaker CALC, so a renamed master hides from a current-prefix "=="
// lookup and a duplicate master gets minted. Check these aliases before creating.
const SERIES_ALIASES = { CORK: ['DWGL', 'DWLG'], DWGL: ['CORK', 'DWLG'], DWLG: ['CORK', 'DWGL'] };
// FileMaker codes that mean "this query found nothing / doesn't apply" (try the next
// query) — as opposed to a REAL error (auth/timeout/lock) that means existence is
// UNVERIFIABLE and we must NOT create. Swallowing a real error as "not found" is what
// minted duplicates. 401=no match, 102=field not on layout, 105=layout, 106=table.
const FM_SKIP_CODES = new Set(['401', '102', '105', '106']);
// Enumerate every candidate {Series, JS Pattern} split of a dashed SKU form. The FM
// master stores the SKU pre-split across two REAL, findable fields (Series + JS Pattern),
// concatenated with NO separator into the `combo sku` calc. We cannot know the split from
// the string (OP-ART|DECO-WAVES vs OP|ART-DECO-WAVES), so we generate a split at EVERY
// separator boundary and let FileMaker's server-side component find pick the real one.
// "OP-ART-DECO-WAVES" -> {OP,ART-DECO-WAVES},{OP-ART,DECO-WAVES},{OP-ART-DECO,WAVES}
// The normalized re-check on returned records rejects any wrong split that happens to hit.
function splitCandidates(dashForm) {
const s = String(dashForm || '').trim();
const out = [];
const idxs = [];
for (let i = 0; i < s.length; i++) if (/[-_ ]/.test(s[i])) idxs.push(i);
for (const i of idxs) {
const series = s.slice(0, i);
const pattern = s.slice(i + 1);
if (series && pattern) out.push([series, pattern]);
}
return out;
}
// Read the REAL spec off a matched WALLPAPER master's fieldData (READ-ONLY). The master
// stores the authoritative human-entered mfr number in `Mfr Pattern` (its `Detail 1 Mfr
// Number` is a copy-lookup of the same). We return every field the caller might use to
// COMPLETE an invoice line from the master it already found — so an existing master is
// never re-flagged as "no mfr number" (TK-10906, deliverable 1a). Blank -> undefined.
function masterFields(fd) {
const s = (v) => { const t = String(v ?? '').trim(); return t || undefined; };
return {
mfr: s(fd['Mfr Pattern']) || s(fd['Detail 1 Mfr Number']),
vid: s(fd.vid),
name: s(fd['Name of Pattern']),
color: s(fd['Color of Pattern']),
width: s(fd.Width),
content: s(fd.Content),
repeat: s(fd.Repeat),
supplier: s(fd.Supplier),
};
}
// Alias- and error-aware existence check for a WALLPAPER master.
// Returns { id, vid, err, master }: id/vid of the matching master (vid = its OWN stored
// code), master = the real spec read off that record (see masterFields — used to complete
// a line WITHOUT re-flagging, TK-10906), or err = a real (non-skip) FileMaker code meaning
// existence could not be verified.
//
// SEPARATOR-AGNOSTIC MATCH (TK-10083, iter 2): the same SKU appears in THREE inconsistent
// forms — invoice OPARTDECOWAVES, FileMaker calc OP-ARTDECO-WAVES, raw OP-ART-DECO-WAVES.
// `combo sku` is a STORED CALC (= Series & JS Pattern, no separator); FileMaker returns
// 401 on a wildcard find against it and a "==" matches ONLY the byte-for-byte stored
// value — which we CANNOT reconstruct because the Series|JS-Pattern split is not derivable
// from the string (proven live: ==OPARTDECOWAVES and ==OP-ART-DECO-WAVES both 401; only
// the exact stored ==OP-ARTDECO-WAVES matches). Iter 1 relied on those calc "==" probes
// and therefore silently missed the master -> minted a duplicate.
//
// The robust, server-side-correct path is the COMPONENT FIELDS: Series and JS Pattern are
// real indexed text fields, findable with "==". We recover the canonical DASHED form from
// dw_unified (Postgres normalizes all three forms back to the stored dw_sku, e.g.
// OP-ART-DECO-WAVES), enumerate every dash-boundary split into {Series,JS Pattern}, issue
// ONE _find with the OR-array of candidate splits, and accept a returned record only when
// normalizeSku(Series + JS Pattern) === the target key. The OR-find matches whichever
// split is real (here OP-ART|DECO-WAVES -> 240939); wrong splits match nothing.
async function findExistingMaster(combo, p, dashCanonical) {
const prefixes = [p.prefix, ...(SERIES_ALIASES[p.prefix] || [])].filter(Boolean);
// Target normalized keys we accept a match against: the parsed key AND per-alias-prefix
// rewrites (a renamed series stores a different prefix but the same pattern remainder).
const targetKeys = new Set([p.key]);
for (const pre of prefixes) targetKeys.add(normalizeSku(pre + p.num));
// ---- Pass A: cheap exact calc "==" probes (fast path for classic letters-then-digits
// SKUs whose stored calc form IS reconstructable). These are a best-effort accelerator;
// Pass B is the correctness guarantee. Only probe the field that exists (`combo sku`).
const forms = new Set();
const add = (v) => { if (v) forms.add(v); };
add(combo); add(p.dashSku); add(p.raw);
if (dashCanonical) add(dashCanonical);
for (const pre of prefixes) { add(pre + p.num); add(pre + '-' + p.num); }
for (const v of forms) {
try {
const r = await fm.findRecords('WALLPAPER', FULL, { 'combo sku': '==' + v }, { limit: 5 });
for (const rec of (r.records || [])) {
const fd = rec.fieldData || {};
if (targetKeys.has(normalizeSku(fd['combo sku']))) {
return { id: rec.recordId, vid: (fd.vid || '').trim(), err: null, master: masterFields(fd) };
}
}
} catch (e) {
const code = String(e.fmCode || '');
if (FM_SKIP_CODES.has(code)) continue; // no-match / field not on layout -> next probe
return { id: null, vid: '', err: code || e.message }; // real error -> UNVERIFIABLE
}
}
// ---- Pass B (correctness): component-field {Series==X, JS Pattern==Y} OR-find over
// every dash-boundary split of the canonical dashed form (from Postgres) AND the raw
// incoming string. This is the path that resolves OP-ART-DECO-WAVES -> 240939.
const splitSources = [];
if (dashCanonical) splitSources.push(dashCanonical);
splitSources.push(p.raw, p.dashSku, combo);
const seen = new Set();
const orQuery = [];
for (const src of splitSources) {
for (const [series, pattern] of splitCandidates(src)) {
const k = series.toUpperCase() + ' ' + pattern.toUpperCase();
if (seen.has(k)) continue;
seen.add(k);
orQuery.push({ Series: '==' + series, 'JS Pattern': '==' + pattern });
}
}
if (orQuery.length) {
try {
const r = await fm.findRecords('WALLPAPER', FULL, orQuery, { limit: 25 });
for (const rec of (r.records || [])) {
const fd = rec.fieldData || {};
// Re-confirm via the SAME canonical key the calc would produce: Series & JS Pattern
// concatenated (no separator), normalized. Accept only a genuine same-SKU record.
const storedKey = normalizeSku(String(fd.Series || '') + String(fd['JS Pattern'] || ''));
// Also accept if the stored calc itself normalizes to a target (belt-and-suspenders).
const comboKey = normalizeSku(fd['combo sku']);
if (targetKeys.has(storedKey) || targetKeys.has(comboKey)) {
return { id: rec.recordId, vid: (fd.vid || '').trim(), err: null, master: masterFields(fd) };
}
}
} catch (e) {
const code = String(e.fmCode || '');
// 401 here means none of the candidate splits matched a master -> genuinely not found.
if (!FM_SKIP_CODES.has(code)) return { id: null, vid: '', err: code || e.message };
}
}
return { id: null, vid: '', err: null };
}
// shopify_products SELECT expression (metafields-first) — reused by both lookups.
// Metafields come in TWO shapes: nested {type,value} objects AND flat scalar strings
// (e.g. Designtex rows store "manufacturer_sku":"8405252" directly). Read both:
// ->'k'->>'value' catches nested, ->>'k' catches flat.
const SP_FIELDS = `json_build_object(
'mfr', COALESCE(NULLIF(mfr_sku,''),
metafields->'custom'->'manufacturer_sku'->>'value', metafields->'custom'->>'manufacturer_sku',
metafields->'global'->'mfr-pattern-number'->>'value', metafields->'global'->>'mfr-pattern-number',
metafields->'global'->'manufacturer_sku'->>'value', metafields->'global'->>'manufacturer_sku'),
'supplier', COALESCE(NULLIF(supplier_name,''),
metafields->'dwc'->'real_vendor'->>'value', metafields->'dwc'->>'real_vendor', vendor),
'pattern', COALESCE(NULLIF(pattern_name,''),
metafields->'custom'->'pattern_name'->>'value', metafields->'custom'->>'pattern_name',
metafields->'global'->'title'->>'value', metafields->'global'->>'pattern_name'),
'color', COALESCE(metafields->'custom'->'color'->>'value', metafields->'custom'->>'color',
metafields->'global'->'Color-of-Pattern'->>'value',
metafields->'dwc'->'color'->>'value', metafields->'dwc'->>'color'),
'width', COALESCE(metafields->'global'->'width'->>'value', metafields->'global'->>'width',
metafields->'dwc'->'width'->>'value', metafields->'dwc'->>'width',
metafields->'specs'->>'width'),
'content', COALESCE(metafields->'dwc'->'contents'->>'value', metafields->'dwc'->>'contents',
metafields->'custom'->'material'->>'value', metafields->'custom'->>'material',
metafields->'global'->'Content'->>'value', metafields->'global'->>'Content'),
'repeat', COALESCE(metafields->'specs'->'repeat'->>'value', metafields->'specs'->>'repeat',
metafields->'custom'->'pattern_repeat'->>'value', metafields->'custom'->>'pattern_repeat',
metafields->'custom'->'repeat'->>'value', metafields->'custom'->>'repeat',
metafields->'global'->'Repeat'->>'value', metafields->'global'->>'Repeat'),
'canonical', dw_sku)`;
// pull source fields for a dashed dw_sku from dw_unified (metafields-first).
// Tries (1) canonical dw_sku, (2) LEGACY sku/variant_sku match (e.g. DWLK-830880
// -> canonical DWSW-5003662) base-normalized, (3) connie_our_catalog fallback.
function sourceFor(dashSku, key) {
const esc = dashSku.replace(/'/g, "''");
let row = sql(`SELECT ${SP_FIELDS} FROM shopify_products
WHERE dw_sku ILIKE '${esc}' OR dw_sku ILIKE '${esc}-SAMPLE' OR dw_sku ILIKE '${esc}-Sample'
ORDER BY (dw_sku='${esc}') DESC LIMIT 1`);
if (row && row.mfr) return { ...row, src: 'shopify_products(dw_sku)' };
// Separator-agnostic fallback (TK-10083): when the incoming form's dashing doesn't
// match the stored dw_sku (e.g. invoice form OPARTDECOWAVES vs stored OP-ART-DECO-WAVES),
// match on the NORMALIZED key — strip every non-alphanumeric from dw_sku and compare.
if (key) {
const k = key.replace(/'/g, "''");
row = sql(`SELECT ${SP_FIELDS} FROM shopify_products
WHERE upper(regexp_replace(regexp_replace(dw_sku,'-(SAMPLE)$','','i'),'[^A-Za-z0-9]','','g')) = '${k}'
ORDER BY (upper(regexp_replace(dw_sku,'[^A-Za-z0-9]','','g'))='${k}') DESC LIMIT 1`);
if (row && row.mfr) return { ...row, src: 'shopify_products(dw_sku normalized)' };
}
// Match on the LEGACY sku/variant_sku (with a trailing -Sample stripped) and read the
// mfr metafields-first via SP_FIELDS. Many private-label lines (e.g. Phillipe Romano
// "London Paper Weave" DWPP-2052xx) leave the mfr_sku AND dw_sku COLUMNS blank and file
// the real mfr number in metafields.custom.manufacturer_sku — and only on the -Sample
// row. So we must NOT gate on `mfr_sku<>''` (that skipped this whole class and returned
// "no mfr number"); instead let SP_FIELDS' metafields COALESCE supply the mfr and let the
// outer `if (row && row.mfr)` guard reject a genuinely blank result. When the plain and
// -Sample rows both match, prefer whichever actually carries a manufacturer number so the
// data-bearing (usually -Sample) row wins over a blank sellable row.
row = sql(`SELECT ${SP_FIELDS} FROM shopify_products
WHERE upper(regexp_replace(COALESCE(NULLIF(sku,''),variant_sku),'-Sample$','','i')) = upper('${esc}')
ORDER BY (COALESCE(NULLIF(mfr_sku,''),
metafields->'custom'->'manufacturer_sku'->>'value', metafields->'custom'->>'manufacturer_sku',
metafields->'global'->'mfr-pattern-number'->>'value', metafields->'global'->>'mfr-pattern-number',
metafields->'global'->'manufacturer_sku'->>'value', metafields->'global'->>'manufacturer_sku') IS NOT NULL) DESC
LIMIT 1`);
if (row && row.mfr) return { ...row, src: 'shopify_products(legacy sku)' };
// (3) universal vendor_catalog (all scraped vendors, keyed on dw_sku) — the authoritative
// source for private-label lines absent from shopify_products (e.g. DWBR/Malibu→Brewster).
// Join dw_sku_registry for a clean supplier name (vendor_code is a slug like 'brewster_york').
row = sql(`SELECT json_build_object(
'mfr', NULLIF(vc.mfr_sku,''),
'supplier', COALESCE(NULLIF(reg.vendor_name,''), NULLIF(vc.original_vendor_name,'')),
'pattern', NULLIF(vc.pattern_name,''),
'color', COALESCE(NULLIF(vc.color_name,''), NULLIF(vc.color_primary,'')),
'width', COALESCE(NULLIF(vc.width,''), NULLIF(vc.width_inches::text,'')))
FROM vendor_catalog vc LEFT JOIN dw_sku_registry reg ON reg.dw_sku = vc.dw_sku
WHERE vc.dw_sku ILIKE '${esc}' AND NULLIF(vc.mfr_sku,'') IS NOT NULL LIMIT 1`);
if (row && row.mfr) return { ...row, src: 'vendor_catalog' };
// (4) dw_sku_registry — authoritative mfr + vendor_name backstop (no pattern/color/width)
row = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', NULLIF(vendor_name,''))
FROM dw_sku_registry WHERE dw_sku ILIKE '${esc}' AND NULLIF(mfr_sku,'') IS NOT NULL LIMIT 1`);
if (row && row.mfr) return { ...row, src: 'dw_sku_registry' };
const c = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', vendor, 'color', color_primary)
FROM connie_our_catalog WHERE dw_sku ILIKE '${esc}%' LIMIT 1`);
if (c && c.mfr) return { ...c, src: 'connie_our_catalog' };
// LAST RESORT — prefix-agnostic UNIQUE-number match. A private-label pattern is sold on
// Shopify under one series prefix (DWLA-436701) but its mfr number is filed in dw_unified
// under the real vendor's prefix (DWPR-436701) with the SAME numeric pattern tail. Match
// on the number; accept ONLY when it resolves to exactly ONE mfr across sources — an
// ambiguous number (46333 -> PSW1625RL AND DB46333) falls through to review, never a guess.
const byNum = mfrByNumber(dashSku);
if (byNum && byNum.mfr) return byNum;
return c || null;
}
// Prefix-agnostic mfr resolver: match on the trailing pattern NUMBER (>=3 digits) across
// shopify_products / vendor_catalog / dw_sku_registry, returning a source row ONLY when the
// number maps to exactly ONE distinct mfr. Zero matches (net-new) or >1 (ambiguous) -> null,
// so the caller flags it for review instead of writing a wrong/blank mfr. Read-only.
function mfrByNumber(dashSku) {
const num = (String(dashSku || '').match(/(\d{3,})\s*$/) || [])[1];
if (!num) return null;
const n = num.replace(/'/g, "''");
const spRe = `(^|[^0-9])${n}(-SAMPLE)?$`, vRe = `(^|[^0-9])${n}$`;
const mfrs = sql(`SELECT json_agg(DISTINCT mfr) FROM (
SELECT NULLIF(mfr_sku,'') mfr FROM shopify_products WHERE dw_sku ~* '${spRe}' AND NULLIF(mfr_sku,'') IS NOT NULL
UNION SELECT NULLIF(mfr_sku,'') FROM vendor_catalog WHERE dw_sku ~* '${vRe}' AND NULLIF(mfr_sku,'') IS NOT NULL
UNION SELECT NULLIF(mfr_sku,'') FROM dw_sku_registry WHERE dw_sku ~* '${vRe}' AND NULLIF(mfr_sku,'') IS NOT NULL
) t`);
if (!Array.isArray(mfrs) || mfrs.length !== 1) return null; // 0 = net-new; >1 = ambiguous
const e = String(mfrs[0]).replace(/'/g, "''");
const row = sql(`SELECT json_build_object(
'mfr', '${e}',
'supplier', COALESCE(
(SELECT COALESCE(NULLIF(reg.vendor_name,''), NULLIF(vc.original_vendor_name,'')) FROM vendor_catalog vc
LEFT JOIN dw_sku_registry reg ON reg.dw_sku = vc.dw_sku WHERE vc.mfr_sku='${e}' AND vc.dw_sku ~* '${vRe}' LIMIT 1),
(SELECT COALESCE(NULLIF(supplier_name,''), vendor) FROM shopify_products WHERE mfr_sku='${e}' AND dw_sku ~* '${spRe}' LIMIT 1)),
'pattern', COALESCE(
(SELECT NULLIF(pattern_name,'') FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1),
(SELECT NULLIF(pattern_name,'') FROM shopify_products WHERE mfr_sku='${e}' AND dw_sku ~* '${spRe}' LIMIT 1)),
'color', (SELECT COALESCE(NULLIF(color_name,''), NULLIF(color_primary,'')) FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1),
'width', (SELECT COALESCE(NULLIF(width,''), NULLIF(width_inches::text,'')) FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1),
'canonical', COALESCE(
(SELECT dw_sku FROM shopify_products WHERE mfr_sku='${e}' AND dw_sku ~* '${spRe}' LIMIT 1),
(SELECT dw_sku FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1)))`);
return (row && row.mfr) ? { ...row, src: 'number-match(unique)' } : null;
}
// LAST-RESORT vid guess for a genuinely-new SKU: the most common vid among existing
// WALLPAPER records of this Series. Only reached when the SKU has no master of its own
// to read a real vid from (see resolveWallpaperSource). Hardened: sample broadly,
// NEVER cache a blank, and NEVER cache/return a series result derived from a failed
// find — a real error must not freeze a wrong/blank vid for the whole run.
const vidCache = {};
async function vidForSeries(series) {
if (series in vidCache) return vidCache[series];
let recs = [];
try { recs = (await fm.findRecords('WALLPAPER', FULL, [{ Series: series, vid: '*' }], { limit: 200 })).records || []; }
catch (e) { if (String(e.fmCode) !== '401') return ''; } // real error -> don't cache, allow a later retry
const counts = {};
for (const rec of recs) { const v = (rec.fieldData.vid || '').trim(); if (v) counts[v] = (counts[v] || 0) + 1; }
const best = Object.entries(counts).sort((a, b) => b[1] - a[1])[0]?.[0] || '';
if (best) vidCache[series] = best; // cache only a real, non-blank result
return best;
}
// Recover the CANONICAL DASHED dw_sku for any of the three SKU forms by matching on the
// normalized key against Postgres (which stores the authoritative dashed form). Postgres
// can normalize (regexp_replace) where FileMaker's calc "==" cannot, so this is what turns
// invoice OPARTDECOWAVES / calc OP-ARTDECO-WAVES back into the stored OP-ART-DECO-WAVES —
// the dashing that seeds the component-field OR-find's split candidates. Read-only.
function canonicalDashFor(key) {
if (!key) return '';
const k = key.replace(/'/g, "''");
// NOTE: sql() JSON.parses its output, so SELECT a json object (not a bare text column).
const row = sql(`SELECT json_build_object('dw_sku', dw_sku) FROM shopify_products
WHERE upper(regexp_replace(regexp_replace(dw_sku,'-(SAMPLE)$','','i'),'[^A-Za-z0-9]','','g')) = '${k}'
ORDER BY (upper(regexp_replace(dw_sku,'[^A-Za-z0-9]','','g'))='${k}') DESC LIMIT 1`);
return (row && row.dw_sku) ? String(row.dw_sku) : '';
}
// ---- MFR-PLACEHOLDER GUARD (TK-10906) --------------------------------------------------
// The corruption signature is DW#==Mfr-SKU: a scraper's mfr-normalizer stripped the leading
// alpha prefix off the real mfr and kept only the digit tail, and that digit tail is ALSO
// the numeric tail of the DW SKU. So dw_unified's mfr_sku is a PLACEHOLDER — the DW number's
// own tail masquerading as a manufacturer code (e.g. HSW-51526 -> mfr "51526", but the real
// Astek code is "gz127"). Faithfully copying that placeholder onto an invoice/master is the
// bug. 20,215 shopify_products rows across 66 vendors carry it.
//
// dwTail(dwSku): the digit run at the end of the DW SKU's PATTERN segment (before -Sample).
const dwTail = (dwSku) => (String(dwSku || '').match(/(\d+)(?:[-_ ]?sample)?$/i) || [])[1] || '';
// LEGIT-NUMERIC allowlist (TK-10906 contrarian fix). For a FEW lines the DW#==mfr equality is
// NOT corruption — the DW SKU's numeric tail genuinely IS the vendor's real mfr. The
// Schumacher family (series DWSW-#######, ~9,053 rows) is the canonical case: Steve's hard
// rule is "DW# is Schumacher's real mfr — do NOT touch." For these, `mfr == DW#` is CORRECT
// and must pass through unflagged, so the placeholder test must NOT fire. Keyed on the DW
// series prefix (case-insensitive, prefix of the parsed Series). Add prefixes here only for
// lines confirmed to have a real numeric-only mfr equal to the DW tail.
// Schumacher files SKUs under BOTH prefixes (DWSW-####### and SCH-#####); both are the
// Schumacher family where DW# is the real mfr, and SCH- is exclusively Schumacher (verified).
const LEGIT_NUMERIC_MFR_PREFIXES = ['DWSW', 'SCH']; // Schumacher family — DW# IS the real mfr
function isLegitNumericLine(dwSku) {
const up = String(dwSku || '').toUpperCase();
// Match on a prefix boundary (SCH-###, SCH###) so an unrelated future prefix like "SCHX"
// isn't accidentally swept in — require the prefix to be followed by a non-letter or end.
return LEGIT_NUMERIC_MFR_PREFIXES.some((pre) => up === pre || up.startsWith(pre + '-') || new RegExp(`^${pre}\\d`).test(up));
}
// isPlaceholderMfr(mfr, dwSku): the candidate mfr is NOT a real mfr — it merely equals the
// DW SKU's numeric tail (the DW#==mfr corruption). Requires a non-empty digit tail so a
// genuinely numeric-only vendor code that happens to differ from the DW tail is untouched.
// GUARD: a line on the LEGIT_NUMERIC allowlist (Schumacher) is NEVER treated as a placeholder
// — for those, mfr==DW# is the real, correct code and must be written, not withheld.
function isPlaceholderMfr(mfr, dwSku) {
if (isLegitNumericLine(dwSku)) return false; // Schumacher family: DW# IS the real mfr
const t = dwTail(dwSku);
if (!t) return false;
return String(mfr || '').trim() === t;
}
// recoverAlphaMfr(dwSku, badMfr): the Wolf-Gordon class — the dw_sku PRESERVED the leading
// alpha prefix in its pattern segment (DWWG-AM10311) while the stored mfr dropped it to the
// digit tail ("10311"). The real mfr is the alpha+digit pattern segment (AM10311). Recover
// ONLY when unambiguous: the pattern segment must be exactly <ALPHA><the-bad-numeric-mfr>,
// i.e. the bad mfr is literally the digit tail of an alpha+digit segment. Returns the real
// mfr, or '' when not recoverable (e.g. DWWG-SM8011 already stores SM8011 — no recovery
// needed and dwTail there is 8011 which does NOT equal a differing stored mfr, so untouched).
function recoverAlphaMfr(dwSku, badMfr) {
const bad = String(badMfr || '').trim();
if (!bad || !/^\d+$/.test(bad)) return ''; // only recover a purely-numeric placeholder
// The pattern segment = the part after the LAST DW-prefix separator (DWWG-AM10311 -> AM10311),
// or the whole thing if there's no separator. Strip a trailing -Sample first.
const seg = String(dwSku || '').replace(/[-_ ]?sample$/i, '').split(/[-_ ]/).pop() || '';
// Unambiguous alpha+digit form whose digit tail is EXACTLY the bad mfr: <ALPHA><bad>.
const m = seg.match(/^([A-Za-z]+)(\d+)$/);
if (m && m[2] === bad) return (m[1] + m[2]).toUpperCase(); // AM + 10311 -> AM10311
return '';
}
// READ-ONLY resolve — what WOULD be written for a combo sku, with NO FileMaker writes.
// Returns { ok, mfr, vid, name, color, width, supplier, jpg, src } or { ok:false, reason }.
// Used by ensureWallpaper (below) and by dry-run/preview callers.
export async function resolveWallpaperSource(comboRaw) {
// A memo-sample order line arrives as the "-Sample" variant (e.g. DWPP-205219-Sample),
// but the WALLPAPER master is per PATTERN, not per sample-vs-sellable — the sample and
// the roll share one master. Strip a trailing -Sample so the sample line resolves to the
// same master (and so the number-tail / component-split logic sees the real SKU, not one
// ending in the word "Sample"). Consistent with the '-Sample$' stripping already done
// inside every sourceFor() query.
const combo = String(comboRaw || '').replace(/[-_ ]?sample$/i, '').trim();
const p = parseCombo(combo);
if (!p) return { ok: false, flagged: combo, reason: 'unparseable sku' };
const s = sourceFor(p.dashSku, p.key);
// Canonical dashed form (from Postgres) — used both to seed the component-field OR-find
// and, when the split is ambiguous, to gate whether a create is structurally allowed.
const dashCanonical = (s && s.canonical) ? String(s.canonical) : canonicalDashFor(p.key);
// vid is the SKU's OWN vendor code. Prefer the existing WALLPAPER master's stored vid
// (authoritative, per-SKU); fall back to the series-modal guess ONLY for a genuinely
// -new SKU. The component-field OR-find (Pass B) is what resolves the non-derivable
// Series|JS-Pattern split (e.g. OP-ART-DECO-WAVES -> master 240939) that the calc "=="
// could never hit — this is the fix that stops the duplicate mint.
const ex = await findExistingMaster(combo, p, dashCanonical);
// FAIL CLOSED (TK-10083 iter 2): if NO existing master was found AND the Series|remainder
// split is not confidently derivable, we must NOT proceed to a create — a create off a
// guessed split is exactly what minted 538697/538698. Route to review instead. (If a
// master WAS found, we're only completing/reading it, so ambiguity is moot.)
if (!ex.id && !ex.err && !p.confident) {
return { ok: false, flagged: combo, reason: 'ambiguous-split-needs-review', _p: p, _existing: ex,
note: `Series|JS-Pattern split for "${combo}" is not derivable from the string and no existing master matched; not creating.` };
}
// ---- MFR RESOLUTION (TK-10906). Resolve the real mfr from the best available source, in
// priority order, refusing to write a DW#==mfr placeholder:
//
// (a) EXISTING MASTER FIRST — if findExistingMaster matched a master that carries a real
// mfr (Mfr Pattern / Detail 1 Mfr Number), USE it and DO NOT re-flag just because
// dw_unified lacks/placeholders the mfr. ("Look up the SKU before creating a new one.")
// The master's mfr is only real if it is not itself the DW# placeholder.
// (b) dw_unified candidate — but treat a candidate that EQUALS the dw_sku's numeric tail
// (DW#==mfr) as NOT a real mfr and never write it.
// (c) ALPHA-PREFIX RECOVERY from the dw_sku (Wolf-Gordon: DWWG-AM10311 stored "10311" ->
// real AM10311). Only when unambiguous.
// (d) otherwise: no real mfr -> flag (money/invoice line still stands; only the mfr write
// is withheld). NEVER stamp the DW# placeholder.
//
// The dw_sku the placeholder-tests key on: the source's canonical dw_sku when known, else
// the parsed dashed form (the combo the line came in on).
const dwSku = (s && s.canonical) ? String(s.canonical) : (dashCanonical || p.dashSku);
const candidate = (s && s.mfr) ? String(s.mfr).trim() : '';
let mfr = '', mfrSrc = '';
// (a) existing-master real mfr wins — but reject a master that itself only holds the DW#.
const masterMfr = ex && ex.master && ex.master.mfr ? String(ex.master.mfr).trim() : '';
if (masterMfr && !isPlaceholderMfr(masterMfr, dwSku)) {
mfr = masterMfr; mfrSrc = 'filemaker-master';
}
// (b) dw_unified candidate — accept ONLY if it is not the DW#==mfr placeholder.
if (!mfr && candidate && !isPlaceholderMfr(candidate, dwSku)) {
mfr = candidate; mfrSrc = s.src;
}
// (c) alpha-prefix recovery from the dw_sku itself (recover AM10311 from "10311").
if (!mfr) {
const recovered = recoverAlphaMfr(dwSku, candidate || dwTail(dwSku));
// Only accept a recovery that actually differs from the placeholder tail.
if (recovered && !isPlaceholderMfr(recovered, dwSku)) { mfr = recovered; mfrSrc = 'dwsku-alpha-prefix'; }
}
// (d) no real mfr resolvable -> route to the flag/review path. Distinguish the two cases so
// the queue/notify layer can tell "we have a DW#==mfr placeholder we refused to write" from
// "dw_unified has nothing at all" — both withhold the mfr write, invoicing is unaffected.
if (!mfr) {
const placeholder = candidate && isPlaceholderMfr(candidate, dwSku);
return { ok: false, flagged: combo, _p: p, _existing: ex,
reason: placeholder
? `dw#==mfr placeholder ("${candidate}") — real mfr withheld, needs review (${s ? s.src : 'dw_unified'})`
: `no mfr number (${s ? s.src : 'not in dw_unified'})` };
}
// vid precedence: (1) an authoritative Series override (fixes private-label series whose
// existing masters are mis-stamped), then (2) the SKU's own existing-master vid, then
// (3) the series modal-vote guess for a genuinely-new series.
const vid = SERIES_VID[p.prefix] || ex.vid || await vidForSeries(p.prefix);
// Descriptive fields: prefer the master's own values (authoritative) then dw_unified.
const M = (ex && ex.master) || {};
const name = (s && s.pattern) || M.name || '';
const color = (s && s.color) || M.color || '';
const width = widthClean((s && s.width) || M.width || '');
const content = (s && s.content) || M.content || '';
const repeat = (s && s.repeat) || M.repeat || '';
const supplier = (s && s.supplier) || M.supplier || '';
const jpg = [name, color].filter(Boolean).join(' - ');
return { ok: true, mfr, vid, name, color, width, content, repeat, supplier, jpg, src: mfrSrc, _p: p, _existing: ex };
}
// Ensure/complete the WALLPAPER record for a combo sku (DW SKU, no dashes, no -Sample).
// Returns { ok, mfr, vid, name, color, width, supplier, src } or { ok:false, flagged, reason }.
export async function ensureWallpaper(combo) {
const r = await resolveWallpaperSource(combo);
if (!r.ok) return r;
const p = r._p, s = { mfr: r.mfr, supplier: r.supplier };
const vid = r.vid, name = r.name, color = r.color, width = r.width, jpg = r.jpg;
const content = r.content, repeat = r.repeat;
// Reuse the alias-/error-aware lookup already done in resolveWallpaperSource.
// NEVER create when existence could not be verified (auth blip / timeout / lock):
// treating a real error as "not found" is exactly what minted duplicate masters.
const ex = r._existing || { id: null, err: null };
if (!ex.id && ex.err) return { ok: false, flagged: combo, reason: `existence unverified (${ex.err}) — not creating (would risk a duplicate)` };
let id = ex.id;
if (!id) {
// STRUCTURAL FAIL-CLOSED GUARDS (TK-10083 iter 2). A create is only reached for a
// genuinely-new SKU. Refuse it unless BOTH hold:
// (1) the parse was CONFIDENT — the Series|remainder split is derivable, so we won't
// mint off a guessed split (the 538697/538698 failure mode); and
// (2) the Series to be written is non-blank — a Series:'' create is what a stray
// all-alpha SKU would produce and is never valid.
// Either failing routes to review; a blank-Series create is now IMPOSSIBLE.
if (!p.confident) {
return { ok: false, flagged: combo, reason: 'ambiguous-split-needs-review — not creating (split not derivable)' };
}
if (!String(p.prefix || '').trim()) {
return { ok: false, flagged: combo, reason: 'blank-series — refusing to create a master with an empty Series' };
}
// 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 {} } }
}
// Full-View-only descriptive fields: Name/Color/vid/JPG plus Content (the material/
// contents field) and MetDataSearchWord (the searchable "tags" field) — so the master
// (and the invoice's live lookups) carry the full spec and the real mfr number is
// findable by tag/search. (Steve: "put the real mfr number in the tags too", 2026-08-12.)
for (const [k, v] of [['Name of Pattern', name], ['Color of Pattern', color], ['vid', vid], ['JPG Name', jpg], ['Content', content], ['Repeat', repeat], ['MetDataSearchWord', s.mfr]]) {
if (v) { try { await fm.updateRecord('WALLPAPER', FULL, id, { [k]: v }, { dryRun: false }); } catch {} }
}
return { ok: true, mfr: s.mfr, vid, name, color, width, content, repeat, supplier: s.supplier || '', src: r.src };
}
// 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,
ledgerInit, ledgerLookup, ledgerClaim, ledgerRecord, ledgerRelease,
dwTail, isPlaceholderMfr, recoverAlphaMfr, masterFields, isLegitNumericLine };