← back to Dw Photo Capture
Photo capture: vendor-first new-SKU creation across Shopify + dw_unified + FileMaker
0a65a0338b4435908add35fb43816f428b9f0b47 · 2026-09-18 13:29:27 -0700 · steve
- Vendor-first XL dropdown sourced from canonical vendor_registry (/api/vendors-registry)
- Canonical DW# minting: next sequential in vendor's sku_prefix series (max-in-series+1,
floored at sku_range_start), collision-safe across shopify_products + staging + FM mirror;
authoritative shopify_products query failure -> PROVISIONAL sku + flag (never a colliding mint)
- Real FileMaker WALLPAPER master create (fm-client fmCreate) via *List Wallpapers - Full View
with confirmed field mapping; calc fields never written; unmapped specs kept in metafields
- createNewItem returns a full 3-system dry-run preview; commit still draft-only, never published
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01DCq3132eTmJESAKqJBM8FN
Files touched
M fm-client.jsM public/index.htmlM server.js
Diff
commit 0a65a0338b4435908add35fb43816f428b9f0b47
Author: steve <steve@designerwallcoverings.com>
Date: Fri Sep 18 13:29:27 2026 -0700
Photo capture: vendor-first new-SKU creation across Shopify + dw_unified + FileMaker
- Vendor-first XL dropdown sourced from canonical vendor_registry (/api/vendors-registry)
- Canonical DW# minting: next sequential in vendor's sku_prefix series (max-in-series+1,
floored at sku_range_start), collision-safe across shopify_products + staging + FM mirror;
authoritative shopify_products query failure -> PROVISIONAL sku + flag (never a colliding mint)
- Real FileMaker WALLPAPER master create (fm-client fmCreate) via *List Wallpapers - Full View
with confirmed field mapping; calc fields never written; unmapped specs kept in metafields
- createNewItem returns a full 3-system dry-run preview; commit still draft-only, never published
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01DCq3132eTmJESAKqJBM8FN
---
fm-client.js | 19 ++++-
public/index.html | 63 ++++++++++++++--
server.js | 218 ++++++++++++++++++++++++++++++++++++++++++++++++++----
3 files changed, 277 insertions(+), 23 deletions(-)
diff --git a/fm-client.js b/fm-client.js
index 3ae36fd..530a7db 100644
--- a/fm-client.js
+++ b/fm-client.js
@@ -105,4 +105,21 @@ async function fmSet(db, layout, recordId, fieldData) {
return { committed: true, recordId, wrote: fieldData };
}
-module.exports = { getIdToken, fmFind, fmGet, fmUpdate, fmSet };
+// Create ONE new record. dryRun (default TRUE) returns the fieldData that WOULD be written WITHOUT
+// committing, so it can never touch live FileMaker by accident (mirrors fmUpdate's safety default).
+// `dedupe` (optional) is a FileMaker _find query object (e.g. { Series:'DWAT', 'Mfr Pattern':'==TR2581' });
+// when given, an existing match short-circuits the create and returns { existed:true, recordId }.
+// IMPORTANT: create the record through a layout that EXPOSES every field you write — writing a field
+// that isn't on the layout silently no-ops (the Name/Color-of-Pattern gotcha). WALLPAPER masters must
+// use "*List Wallpapers - Full View", NOT "Add wallcovering".
+async function fmCreate(db, layout, fieldData, { dryRun = true, dedupe = null } = {}) {
+ if (dedupe) {
+ const r = await fmFind(db, layout, dedupe, { limit: 1 }).catch(() => ({ records: [] }));
+ if ((r.records || []).length) return { committed: false, existed: true, recordId: r.records[0].recordId, fieldData };
+ }
+ if (dryRun) return { committed: false, dryRun: true, db, layout, fieldData };
+ const resp = await fm(db, `/layouts/${encodeURIComponent(layout)}/records`, { method: 'POST', body: { fieldData } });
+ return { committed: true, recordId: resp && resp.recordId, modId: resp && resp.modId, fieldData };
+}
+
+module.exports = { getIdToken, fmFind, fmGet, fmUpdate, fmSet, fmCreate };
diff --git a/public/index.html b/public/index.html
index 4170ba5..236b432 100644
--- a/public/index.html
+++ b/public/index.html
@@ -231,6 +231,22 @@
.add-voice{display:block;width:100%;box-sizing:border-box;margin:0 0 12px;padding:11px;border:1px solid var(--line);border-radius:10px;background:#151310;color:#e7ddc7;font-size:14px;font-weight:600;cursor:pointer}
.add-voice:active{transform:scale(.99)}
.media-sub{font-size:12px;color:#b7ad98;margin-bottom:10px;min-height:0}
+ /* VENDOR-FIRST XL picker — big touch target + extra-large font for phone use */
+ .xl-vendor-wrap{margin:6px 0 14px;padding:12px;background:#0f0e0b;border:1px solid var(--line);border-radius:14px}
+ .xl-vendor-lbl{display:block;font-size:13px;letter-spacing:.04em;text-transform:uppercase;color:#c8ba97;margin-bottom:8px;font-weight:700}
+ select.xl-vendor{width:100%;box-sizing:border-box;background:#1b1812;border:2px solid #b8902f;border-radius:12px;
+ padding:16px 14px;color:var(--ink);font-size:24px;line-height:1.2;font-weight:600;min-height:64px;-webkit-appearance:none;appearance:none}
+ select.xl-vendor:focus{outline:none;box-shadow:0 0 0 3px rgba(184,144,47,.4)}
+ select.xl-vendor.need{border-color:#e0483a;box-shadow:0 0 0 3px rgba(224,72,58,.35)}
+ .xl-vendor-meta{margin-top:8px;font-size:13px;color:#9a917d;min-height:16px}
+ .xl-vendor-meta b{color:#d8cba6}
+ .pv-sys{margin-top:8px;padding:9px 11px;background:#0f0e0b;border:1px solid var(--line);border-radius:10px}
+ .pv-h{font-size:11px;text-transform:uppercase;letter-spacing:.04em;color:#c8ba97;font-weight:700;margin-bottom:4px}
+ .pv-sys small{color:#b7ad98;font-size:12.5px;line-height:1.5}
+ table.pv-fm{width:100%;margin-top:6px;border-collapse:collapse;font-size:12px}
+ table.pv-fm td{border-top:1px solid var(--line);padding:3px 4px;color:#cfc4aa;vertical-align:top}
+ table.pv-fm td:first-child{color:#8f866f;white-space:nowrap;width:44%}
+ .pv-flags{margin-top:8px;padding:8px 10px;background:#241a0c;border:1px solid #6b551f;border-radius:9px;color:#e8c987;font-size:12.5px;line-height:1.5}
.media-grid{display:grid;grid-template-columns:repeat(4,1fr);gap:7px;margin-bottom:10px}
.media-grid:empty{display:none}
.media-cell{position:relative;aspect-ratio:1/1;border-radius:10px;background:#000 center/cover no-repeat;overflow:hidden;border:1px solid var(--line)}
@@ -488,6 +504,12 @@
<button class="samp-x" id="addClose">✕</button>
<div class="samp-pat" id="addTitle" style="margin-bottom:4px">Add new item</div>
<div class="media-sub" id="addTarget"></div>
+ <!-- VENDOR FIRST: pick the vendor before anything else — it drives the DW#, Shopify vendor + FM vid -->
+ <div class="xl-vendor-wrap" id="xlVendorWrap">
+ <label class="xl-vendor-lbl">1 · Pick vendor</label>
+ <select id="addVendor" class="xl-vendor"><option value="">Loading vendors…</option></select>
+ <div class="xl-vendor-meta" id="xlVendorMeta"></div>
+ </div>
<div class="media-grid" id="addMedia"></div>
<div class="media-add">
<button class="media-btn" id="addPhotoBtn">📷 Photo <span id="cPhoto">0/10</span></button>
@@ -498,7 +520,6 @@
<button class="add-voice" id="addMeasure">📐 Measure real size (place a card by the goods)</button>
<button class="add-voice" id="addVoice">🎤 Voice-fill everything that's missing</button>
<div class="add-field"><label>Mfr # / SKU</label><div class="in-mic"><input id="addMfr" autocapitalize="characters" placeholder="e.g. TR2581"><button class="mic-btn" data-mic="addMfr" data-kind="sku" title="Speak the SKU / mfr number">🎤</button></div></div>
- <div class="add-field"><label>Vendor</label><div class="in-mic"><select id="addVendor"><option value="">Loading…</option></select><button class="mic-btn" data-mic="addVendor" data-kind="vendor" title="Speak the vendor name">🎤</button></div></div>
<div class="add-row"><div class="add-field"><label>VID</label><div class="in-mic"><input id="addVid" placeholder="THI"><button class="mic-btn" data-mic="addVid" data-kind="text" title="Speak the VID">🎤</button></div></div><div class="add-field"><label>Color</label><div class="in-mic"><input id="addColor"><button class="mic-btn" data-mic="addColor" data-kind="text" title="Speak the color">🎤</button></div></div></div>
<div class="add-row"><div class="add-field"><label>Pattern name</label><div class="in-mic"><input id="addName"><button class="mic-btn" data-mic="addName" data-kind="text" title="Speak the pattern name">🎤</button></div></div><div class="add-field"><label>Price</label><div class="in-mic"><input id="addPrice" type="number" inputmode="decimal"><button class="mic-btn" data-mic="addPrice" data-kind="num" title="Speak the price">🎤</button></div></div></div>
<div id="addSpecs"></div>
@@ -1733,7 +1754,23 @@ async function addMediaAdd(type,file){ if(!file)return; const c=mediaCounts();
} else { if(c.v>=MAX_VIDEOS){ toast('Max '+MAX_VIDEOS+' videos'); return; }
if(file.size>150*1024*1024){ toast('Video too big (>150MB)'); return; }
const dataUrl=await fileToDataUrl(file); _media.push({type:'video',dataUrl,name:file.name||'clip.mp4'}); renderMedia(); } }
-async function loadVendors(){ if(_vendorsLoaded) return; try{ const r=await(await fetch('/api/vendors')).json(); $('#addVendor').innerHTML='<option value="">— pick vendor —</option>'+(r.vendors||[]).map(v=>`<option value="${v.vendor}" data-vid="${v.vid||''}">${v.vendor}${v.vid?(' ('+v.vid+')'):''}</option>`).join(''); _vendorsLoaded=true; }catch(e){} }
+let _vreg=[];
+async function loadVendors(){ if(_vendorsLoaded) return; try{
+ // VENDOR-FIRST: the canonical vendor_registry drives DW# + Shopify vendor + FM vid.
+ const r=await(await fetch('/api/vendors-registry')).json(); _vreg=r.vendors||[];
+ $('#addVendor').innerHTML='<option value="">— pick vendor first —</option>'+_vreg.map(v=>
+ `<option value="${v.vendor}" data-vid="${v.vid||''}" data-prefix="${v.sku_prefix||''}" data-fmvid="${v.fm_vid||''}" data-start="${v.sku_range_start||0}">${v.vendor}${v.sku_prefix?(' — '+v.sku_prefix):''}</option>`).join('');
+ _vendorsLoaded=true;
+}catch(e){ /* keep the loading state visible on failure */ } }
+// When a vendor is chosen, auto-fill VID from the registry + show the canonical minting series.
+function onVendorPick(){ const sel=$('#addVendor'); const o=sel.options[sel.selectedIndex]; sel.classList.remove('need');
+ if(!o||!o.value){ $('#xlVendorMeta').innerHTML=''; return; }
+ const fmvid=o.getAttribute('data-fmvid')||'', vid=o.getAttribute('data-vid')||'', pfx=o.getAttribute('data-prefix')||'';
+ if(fmvid) $('#addVid').value=fmvid; else if(vid && !$('#addVid').value) $('#addVid').value='';
+ $('#xlVendorMeta').innerHTML = pfx
+ ? `Series <b>${pfx.replace(/-+$/,'')}</b> · next DW# minted on Preview${fmvid?(' · FM vid <b>'+fmvid+'</b>'):' · <b>FM vid unresolved</b>'}`
+ : `⚠ no SKU prefix in registry — will use a <b>PROVISIONAL</b> SKU (flag before go-live)`;
+}
function openAddModal(pref,opts){ opts=opts||{}; _addMode=opts.mode||'add'; _addSession++; addResetMedia();
['addMfr','addVid','addName','addColor','addPrice'].forEach(id=>$('#'+id).value=''); $('#addNote').textContent=''; $('#addResult').innerHTML='';
$('#addTitle').textContent = _addMode==='update' ? 'Update SKU on Shopify' : 'Add new item';
@@ -1741,7 +1778,8 @@ function openAddModal(pref,opts){ opts=opts||{}; _addMode=opts.mode||'add'; _add
$('#addPreview').textContent = _addMode==='update' ? '⬆ Push media to SKU (LIVE)' : '👁 Preview';
if(pref){ if(pref.mfr)$('#addMfr').value=pref.mfr; if(pref.color)$('#addColor').value=pref.color; if(pref.name)$('#addName').value=pref.name; }
$('#addModal').hidden=false; document.body.style.overflow='hidden';
- loadVendors().then(()=>{ if(pref&&pref.vendor) $('#addVendor').value=pref.vendor; });
+ $('#xlVendorMeta').innerHTML=''; $('#addVendor').classList.toggle('need', _addMode!=='update');
+ loadVendors().then(()=>{ if(pref&&pref.vendor){ $('#addVendor').value=pref.vendor; } onVendorPick(); });
if(opts.camera) setTimeout(()=>$('#addPhotoInput').click(),250); // onload → go straight to camera
}
function addPhotosList(){ return _media.filter(m=>m.type==='photo').map(m=>m.dataUrl); }
@@ -1765,13 +1803,24 @@ async function pollVideoStatus(product_id, baseMsg, expected){ const started=Dat
$('#addNote').innerHTML=`${baseMsg} · 🎥 still transcoding — check Shopify shortly.`;
}
async function addPreview(){ if(_addMode==='update') return addUpdate();
- const p=addPayload(false); if(!p.mfr||!p.vendor){ $('#addNote').textContent='Mfr# and Vendor are required.'; return; }
+ const p=addPayload(false);
+ if(!p.vendor){ $('#addVendor').classList.add('need'); $('#addNote').textContent='Pick a vendor first.'; return; }
+ if(!p.mfr){ $('#addNote').textContent='Mfr# is required.'; return; }
$('#addNote').textContent='Previewing…'; $('#addResult').innerHTML='';
const r=await(await fetch('/api/create-item',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify(p)})).json();
if(r.duplicate){ $('#addNote').innerHTML='⚠ '+r.err+' — tap <b>⌂ → Update SKU</b> to add media to it live.'; return; }
if(!r.ok){ $('#addNote').textContent='✗ '+(r.err||'failed'); return; }
- const pv=r.preview, c=mediaCounts(); $('#addNote').textContent='Preview — nothing created yet.';
- $('#addResult').innerHTML=`<div class="fb-hit"><div><b>${pv.title}</b><br><small>${[pv.vendor,pv.dw_sku,pv.mfr,pv.color,pv.price?('$'+pv.price):''].filter(Boolean).join(' · ')} · ${c.p} photo(s) · ${c.v} video(s) · <b>DRAFT</b></small></div></div><button class="samp-print" id="addCommit" style="margin-top:10px">✅ Create draft + upload media</button>`;
+ const pv=r.preview, c=mediaCounts(); $('#addNote').textContent='Preview — nothing created yet (all 3 systems).';
+ const fm=pv.filemaker||{}, esc=s=>String(s==null?'':s).replace(/</g,'<');
+ const fmRows=Object.entries(fm.fieldData||{}).map(([k,v])=>`<tr><td>${esc(k)}</td><td>${esc(v)}</td></tr>`).join('');
+ const flagsHtml=(pv.flags&&pv.flags.length)?`<div class="pv-flags">⚠ ${pv.flags.map(esc).join('<br>⚠ ')}</div>`:'';
+ $('#addResult').innerHTML=`
+ <div class="fb-hit"><div><b>${esc(pv.title)}</b><br><small><b>DW# ${esc(pv.dw_sku)}</b>${pv.provisional_sku?' <span style="color:#e0a53a">(PROVISIONAL)</span>':''} · ${c.p} photo(s) · ${c.v} video(s)</small></div></div>
+ ${flagsHtml}
+ <div class="pv-sys"><div class="pv-h">🛍 Shopify draft</div><small>${esc(pv.shopify.vendor)} · ${esc(pv.shopify.product_type)} · <b>DRAFT</b><br>Roll <b>${esc(pv.shopify.variants[0].sku)}</b> @ ${esc(pv.shopify.variants[0].price)} · Sample <b>${esc(pv.shopify.variants[1].sku)}</b> @ $4.25</small></div>
+ <div class="pv-sys"><div class="pv-h">🗄 dw_unified · new_items_staging</div><small>dw_sku <b>${esc(pv.staging.dw_sku)}</b> · vid ${esc(pv.staging.vid||'—')} · ${esc(pv.staging.pattern_name||'—')} / ${esc(pv.staging.color||'—')}</small></div>
+ <div class="pv-sys"><div class="pv-h">📇 FileMaker WALLPAPER master</div><small>${esc(fm.db||'')} · ${esc(fm.layout||'')} · combo sku auto → <b>${esc((fm.auto_calc&&fm.auto_calc['combo sku'])||'')}</b></small><table class="pv-fm">${fmRows}</table></div>
+ <button class="samp-print" id="addCommit" style="margin-top:12px">✅ Create draft + staging + FM master</button>`;
$('#addCommit').addEventListener('click',addCommit);
}
async function addCommit(){ const b=$('#addCommit'); b.disabled=true; $('#addNote').textContent='Creating draft…';
@@ -1851,7 +1900,7 @@ async function addUpdate(){ if(!_updatePid) await addResolveUpdate();
}
$('#addBtn').addEventListener('click',()=>openAddModal());
$('#addClose').addEventListener('click',()=>{ $('#addModal').hidden=true; document.body.style.overflow=''; });
-$('#addVendor').addEventListener('change',e=>{ const o=e.target.selectedOptions[0]; if(o&&o.dataset.vid&&!$('#addVid').value) $('#addVid').value=o.dataset.vid; });
+$('#addVendor').addEventListener('change',onVendorPick);
$('#addPhotoBtn').addEventListener('click',()=>$('#addPhotoInput').click());
$('#addVideoBtn').addEventListener('click',()=>$('#addVideoInput').click());
$('#addPhotoInput').addEventListener('change',e=>{ const files=[...e.target.files]; e.target.value=''; files.forEach(f=>addMediaAdd('photo',f)); });
diff --git a/server.js b/server.js
index e02adc2..2c7af64 100644
--- a/server.js
+++ b/server.js
@@ -21,6 +21,11 @@ let FM = null;
try { FM = require('./fm-client.js'); } catch (e) { console.log('FileMaker client unavailable:', e.message); }
const FM_DB = process.env.FM_DB || 'WALLPAPER';
const FM_LAYOUT = process.env.FM_LAYOUT || 'Sample Requests via email';
+// WALLPAPER master CREATE layout. MUST expose Name/Color of Pattern (the "Add wallcovering" layout
+// does NOT — writing through it silently drops those fields). Confirmed live 2026-09-18 via
+// fm.fieldMetadata: this view exposes Series, JS Pattern, Mfr Pattern, Name/Color of Pattern, Width,
+// Content, Repeat, Sold Per, Retail Price, vid, Record Type, Internal Description.
+const FM_WP_CREATE_LAYOUT = process.env.FM_WP_CREATE_LAYOUT || '*List Wallpapers - Full View';
const FM_PRINT_FLAG = process.env.FM_PRINT_FLAG_FIELD || ''; // Steve designates a flag field the Mac poller reads
const FM_ENABLED = () => !!(FM && process.env.FM_CLARIS_EMAIL && process.env.FM_CLARIS_PASSWORD && process.env.FM_CLOUD_HOST);
// Today's date as MM/DD/YYYY in the business timezone (Kamatera runs UTC; stamp must be Pacific
@@ -1662,6 +1667,14 @@ const appHandler = (req, res) => {
return;
}
+ // VENDOR-FIRST dropdown source — the canonical vendor_registry (drives the DW# mint + Shopify
+ // vendor + FileMaker vid). This is what the XL "pick vendor first" menu loads.
+ if (u.pathname === '/api/vendors-registry' && req.method === 'GET') {
+ getVendorsRegistry().then(list => send(res, 200, { ok: true, count: list.length, vendors: list }))
+ .catch(e => send(res, 200, { ok: false, err: e.message, vendors: [] }));
+ return;
+ }
+
// Add a NEW item → Shopify DRAFT + dw_unified staging. dryRun (default) PREVIEWS; commit:true writes.
// Never auto-published (going live is a separate gated step); dedups on mfr#.
if (u.pathname === '/api/create-item' && req.method === 'POST') {
@@ -2391,33 +2404,206 @@ function getVendors() {
});
}
-// Create a NEW item (arbitrary vendor) as a Shopify DRAFT + a dw_unified staging row. Draft-only,
-// never auto-published (going live is a separate gated step). dryRun (default) returns a preview.
+// ── tiny psql helpers (this app shells to psql; zero pg driver deps) ──────────
+function pgRows(sql) {
+ return new Promise(resolve => {
+ execFile(PSQL, ['-d', DW_DB, '-tAF', '\t', '-c', sql], { timeout: 9000, maxBuffer: 8 * 1024 * 1024 }, (err, out) => {
+ if (err) return resolve([]);
+ resolve((out || '').split('\n').filter(Boolean).map(l => l.split('\t')));
+ });
+ });
+}
+// Like pgRows but distinguishes ERROR (returns {ok:false}) from an empty result set (returns {ok:true,rows:[]}).
+// The mint MUST know the difference: a FAILED "what's already used" query is unsafe to treat as "nothing used".
+function pgQuery(sql) {
+ return new Promise(resolve => {
+ execFile(PSQL, ['-d', DW_DB, '-v', 'ON_ERROR_STOP=1', '-tAF', '\t', '-c', sql], { timeout: 9000, maxBuffer: 8 * 1024 * 1024 }, (err, out) => {
+ if (err) return resolve({ ok: false, rows: [] });
+ resolve({ ok: true, rows: (out || '').split('\n').filter(Boolean).map(l => l.split('\t')) });
+ });
+ });
+}
+
+// ── VENDOR-FIRST dropdown source: the canonical vendor_registry ──────────────
+// Returns one row per real, active vendor with its canonical DW# minting inputs
+// (sku_prefix + sku_range_start) and — best-effort — the short FileMaker `vid`
+// (KRA/WQ/PJ…) inferred from the dominant vid on that prefix's existing FM masters.
+let _vregCache = { at: 0, list: [] };
+async function getVendorsRegistry() {
+ if (Date.now() - _vregCache.at < 10 * 60 * 1000 && _vregCache.list.length) return _vregCache.list;
+ // vendor_registry drives the DW#: vendor_code (vid for staging), sku_prefix, sku_range_start.
+ const vrows = await pgRows(
+ `select coalesce(nullif(private_label_name,''), vendor_name) disp, vendor_name, vendor_code,
+ coalesce(sku_prefix,''), coalesce(sku_range_start,0)::text, coalesce(private_label_name,'')
+ from vendor_registry
+ where coalesce(is_active,true) and coalesce(vendor_name,'')<>''
+ and coalesce(skip_shopify,false)=false
+ order by disp`);
+ // dominant short FM vid per series (prefix without the trailing dash), from the FM mirror.
+ const fmrows = await pgRows(
+ `select series, vid, count(*) n from filemaker_wallpaper
+ where coalesce(series,'')<>'' and coalesce(vid,'')<>''
+ group by series, vid`);
+ const fmVidBySeries = {};
+ for (const [series, vid, n] of fmrows) {
+ const s = series.toUpperCase(); const c = +n || 0;
+ if (!fmVidBySeries[s] || c > fmVidBySeries[s].n) fmVidBySeries[s] = { vid, n: c };
+ }
+ const list = vrows.map(([disp, vendor_name, vendor_code, sku_prefix, range, plabel]) => {
+ const series = (sku_prefix || '').replace(/-+$/, '').toUpperCase();
+ return {
+ vendor: disp, // customer-facing name (private-label name wins) — Shopify vendor
+ real_vendor: vendor_name, // internal only (never shown / never customer-facing)
+ vid: vendor_code, // dw_unified staging vid
+ sku_prefix: sku_prefix || null,
+ sku_range_start: +range || 0,
+ fm_vid: (fmVidBySeries[series] && fmVidBySeries[series].vid) || null, // short FileMaker vid, best-effort
+ private_label: !!plabel,
+ };
+ }).filter(v => v.vendor);
+ _vregCache = { at: Date.now(), list };
+ return list;
+}
+
+// ── CANONICAL DW# minting (matches the vendor onboarders) ────────────────────
+// Scheme (verified against sanderson-onboard/build-payloads.mjs + create-grasscloth-masters):
+// dw_sku = <sku_prefix><N>, where N starts at max(sku_range_start, maxUsedInSeries+1) and skips
+// any number already used in that prefix's series. Series (FM) = prefix without the dash; the
+// numeric N is the FM "JS Pattern"; FileMaker auto-calcs `combo sku` = <Series>-<N> = the dw_sku.
+// USED-NUMBER SOURCES (union, so a mint never collides): the live store mirror (shopify_products),
+// this app's own new_items_staging (so back-to-back scans don't collide pre-sync), and the FM mirror
+// (filemaker_wallpaper). If the vendor has no sku_prefix we CANNOT mint safely → return a clearly
+// marked PROVISIONAL sku and flag it (never fabricate a colliding canonical DW#).
+async function mintDwSku(vreg) {
+ const prefix = (vreg.sku_prefix || '').trim();
+ if (!prefix) {
+ const provisional = 'PROV-' + (vreg.vid || 'VENDOR').toUpperCase().replace(/[^A-Z0-9]/g, '').slice(0, 8) + '-' + Date.now().toString(36).toUpperCase();
+ return { dw_sku: provisional, series: null, js_pattern: null, provisional: true,
+ flag: `no sku_prefix in vendor_registry for "${vreg.real_vendor || vreg.vendor}" — assign one (registry.sku_prefix + sku_range_start) before go-live; used PROVISIONAL sku` };
+ }
+ const pfxNoDash = prefix.replace(/-+$/, '');
+ const esc = prefix.replace(/'/g, "''");
+ const escN = pfxNoDash.replace(/'/g, "''");
+ // Pull the numeric tails already used under this prefix — each source queried SEPARATELY and
+ // GUARDED, so a missing/lazily-created table (e.g. new_items_staging) can never silently zero out
+ // the AUTHORITATIVE shopify_products count and cause a colliding mint. shopify_products is the
+ // authoritative live-store mirror and MUST answer; if it errors we refuse to mint (flag), never guess.
+ const used = new Set();
+ const shop = await pgQuery(`select regexp_replace(dw_sku,'^${esc}','')::text from shopify_products where dw_sku ~ '^${esc}[0-9]+$'`);
+ if (!shop.ok) {
+ const provisional = 'PROV-' + pfxNoDash + '-' + Date.now().toString(36).toUpperCase();
+ return { dw_sku: provisional, series: null, js_pattern: null, provisional: true,
+ flag: `could not read shopify_products to verify the ${prefix} series is collision-free — used PROVISIONAL sku (do NOT go live until the real max-in-series is confirmed)` };
+ }
+ for (const r of shop.rows) { const n = parseInt(r[0], 10); if (Number.isFinite(n)) used.add(n); }
+ // best-effort secondary sources: FM mirror (always present) + staging (guarded — may not exist yet)
+ const fmq = await pgQuery(`select regexp_replace(coalesce(nullif(combo_sku,''),dw_sku),'^${escN}-','')::text from filemaker_wallpaper where coalesce(nullif(combo_sku,''),dw_sku) ~ '^${escN}-[0-9]+$'`);
+ if (fmq.ok) for (const r of fmq.rows) { const n = parseInt(r[0], 10); if (Number.isFinite(n)) used.add(n); }
+ const stg = await pgQuery(`select regexp_replace(dw_sku,'^${esc}','')::text from new_items_staging where to_regclass('public.new_items_staging') is not null and dw_sku ~ '^${esc}[0-9]+$'`);
+ if (stg.ok) for (const r of stg.rows) { const n = parseInt(r[0], 10); if (Number.isFinite(n)) used.add(n); }
+ const maxUsed = used.size ? Math.max(...used) : 0;
+ let n = Math.max(vreg.sku_range_start || 0, maxUsed + 1, 1);
+ while (used.has(n)) n++;
+ return { dw_sku: `${prefix}${n}`, series: pfxNoDash, js_pattern: String(n), provisional: false, flag: null };
+}
+
+// ── FileMaker WALLPAPER master fieldData builder ─────────────────────────────
+// Maps captured fields onto the REAL WALLPAPER field names (confirmed live 2026-09-18 via
+// fm.fieldMetadata on "*List Wallpapers - Full View"). CALC fields (combo sku, comboskuwithdash,
+// seriesdashnumber, Dw Retail Price) are auto-derived by FileMaker — NEVER written here.
+// Fields NOT on this create layout (Collection, Match, Price Code, roll length) are intentionally
+// carried in dw_unified.specs + Shopify metafields instead, and surfaced in `unmapped` so it's explicit.
+function buildFmMaster(p, mint, vreg) {
+ const fd = { 'Record Type': 'Master' };
+ if (mint.series) fd['Series'] = mint.series; // e.g. DWAT (combo sku auto = DWAT-<JS>)
+ if (mint.js_pattern) fd['JS Pattern'] = mint.js_pattern; // the numeric DW#
+ const mfr = String(p.mfr || '').trim();
+ if (mfr) { fd['Mfr Pattern'] = mfr; fd['mfr pattern number'] = mfr; }
+ const name = String(p.name || '').trim();
+ if (name) { fd['Name of Pattern'] = name; fd['Internal Description'] = name; }
+ const color = String(p.color || '').trim();
+ if (color) fd['Color of Pattern'] = color;
+ if (p.width) fd['Width'] = /["]/.test(String(p.width)) ? String(p.width) : (String(p.width).match(/[\d.]+/) ? String(p.width).match(/[\d.]+/)[0] + '"' : String(p.width));
+ if (p.substrate) fd['Content'] = String(p.substrate).trim();
+ if (p.repeat) fd['Repeat'] = String(p.repeat).trim();
+ if (p.how_sold) fd['Sold Per'] = String(p.how_sold).trim();
+ if (p.price && +p.price > 0) fd['Retail Price'] = String(p.price);
+ const fmVid = (vreg && vreg.fm_vid) || null;
+ if (fmVid) fd['vid'] = fmVid;
+ // spec fields with no home on this layout — kept in Shopify metafields + staging specs, reported honestly
+ const unmapped = {};
+ if (p.collection) unmapped.collection = p.collection; // "Collection" not on create layout
+ if (p.roll_length) unmapped.roll_length = p.roll_length; // no roll-length field on create layout
+ if (p.pattern_match) unmapped.pattern_match = p.pattern_match; // no Match field on create layout
+ if (p.price_code) unmapped.price_code = p.price_code; // no Price Code field on create layout
+ if (p.material) unmapped.material = p.material;
+ return { fieldData: fd, unmapped, vid_flag: fmVid ? null : `no FileMaker vid resolved for series ${mint.series || '?'} — set WALLPAPER.vid manually or add a vid mapping` };
+}
+
+// Create a NEW item (arbitrary vendor) as a Shopify DRAFT + a dw_unified staging row + a REAL
+// FileMaker WALLPAPER master. Draft-only, never auto-published (going live is a separate gated step).
+// dryRun (default) returns a full 3-system preview and writes nothing.
async function createNewItem(p, b64, dryRun) {
const mfr = String(p.mfr || '').trim(); const vendor = String(p.vendor || '').trim();
- if (!mfr || !vendor) return { ok: false, err: 'mfr + vendor required' };
+ if (!mfr || !vendor) return { ok: false, err: 'mfr + vendor required — pick a vendor first' };
+ // Resolve the chosen vendor from the canonical registry (drives DW# + Shopify vendor + FM vid).
+ const registry = await getVendorsRegistry().catch(() => []);
+ let vreg = registry.find(v => (v.vid && p.vid && v.vid === p.vid))
+ || registry.find(v => v.vendor === vendor)
+ || registry.find(v => (v.real_vendor || '') === vendor);
+ if (!vreg) {
+ // vendor typed/spoken but not in the registry — still allow, but flag (no canonical prefix known)
+ vreg = { vendor, real_vendor: vendor, vid: p.vid || '', sku_prefix: null, sku_range_start: 0, fm_vid: null, private_label: false };
+ }
// dedup: refuse if the mfr# already exists in the catalog (attach instead, don't duplicate)
const dup = CATALOG.find(x => x.mfr && nmfr(x.mfr) === nmfr(mfr));
- const dwsku = String(p.dw_sku || '').trim() || (String(p.vid || vendor).slice(0, 4).toUpperCase().replace(/[^A-Z0-9]/g, '') + mfr.replace(/[^A-Za-z0-9]/g, ''));
+ // CANONICAL DW# minting — next sequential in the vendor's series (never a naive prefix+mfr concat).
+ // Honor an explicit dw_sku override only if the client passed one.
+ const mint = String(p.dw_sku || '').trim()
+ ? { dw_sku: String(p.dw_sku).trim(), series: (String(p.dw_sku).split('-')[0] || null), js_pattern: (String(p.dw_sku).split('-')[1] || null), provisional: false, flag: null }
+ : await mintDwSku(vreg);
+ const dwsku = mint.dw_sku;
const name = String(p.name || '').trim(); const color = String(p.color || '').trim();
const price = p.price && +p.price > 0 ? String(p.price) : null;
- const title = [name || mfr, color, '|', vendor].filter(Boolean).join(' ').replace(' | ', ' | ');
- const preview = { title, dw_sku: dwsku, mfr, vendor, vid: p.vid || null, color, price, status: 'draft', photos: Array.isArray(p._photos64) ? p._photos64.length : (b64 ? 1 : 0), duplicate_of: dup ? (dup.dw_sku || dup.product_id) : null };
+ // Title format: Pattern Name Real Color Name | Brand Name (never "Unknown"; fall back mfr → color)
+ const lead = name || mfr || color;
+ const title = [lead, color && color !== lead ? color : '', '|', vreg.vendor].filter(Boolean).join(' ');
+ const material = (p.material || '').trim() || 'Wallcovering';
+ const fm = buildFmMaster(p, mint, vreg);
+ const specsObj = { material, collection: p.collection || '', width: p.width || '', roll_length: p.roll_length || '',
+ repeat: p.repeat || '', pattern_match: p.pattern_match || '', substrate: p.substrate || '',
+ how_sold: p.how_sold || '', price_code: p.price_code || '' };
+ const flags = [mint.flag, fm.vid_flag, dup ? `mfr# ${mfr} already in catalog as ${dup.dw_sku || dup.product_id}` : null].filter(Boolean);
+ // FULL 3-system preview — exactly what Shopify / dw_unified / FileMaker WOULD receive.
+ const preview = {
+ title, dw_sku: dwsku, mfr, vendor: vreg.vendor, vid: vreg.vid || null, color, price, status: 'draft',
+ photos: Array.isArray(p._photos64) ? p._photos64.length : (b64 ? 1 : 0),
+ provisional_sku: !!mint.provisional, duplicate_of: dup ? (dup.dw_sku || dup.product_id) : null,
+ shopify: { vendor: vreg.vendor, product_type: material, status: 'draft',
+ variants: [{ option1: 'Roll', sku: dwsku, price: price || '(quote — draft, no price yet)' },
+ { option1: 'Sample', sku: dwsku + '-Sample', price: '4.25' }] },
+ staging: { table: 'new_items_staging', dw_sku: dwsku, mfr_sku: mfr, vendor: vreg.vendor, vid: vreg.vid || '', pattern_name: name, color, price, specs: specsObj },
+ filemaker: { db: FM_DB, layout: FM_WP_CREATE_LAYOUT, fieldData: fm.fieldData,
+ auto_calc: { 'combo sku': mint.series && mint.js_pattern ? `${mint.series}-${mint.js_pattern}` : '(needs Series + JS Pattern)' },
+ unmapped_kept_in_metafields: fm.unmapped },
+ flags,
+ };
if (dup) return { ok: false, duplicate: true, preview, err: `mfr# ${mfr} already exists (${dup.dw_sku || dup.product_id}) — add a photo to it instead` };
if (dryRun) return { ok: true, dryRun: true, preview };
+ // ── COMMIT: PostgreSQL-staging + Shopify draft + FileMaker master (draft-only, never published) ──
try {
const mf = (ns, key, val) => val ? { namespace: ns, key, value: String(val), type: 'single_line_text_field' } : null;
const variants = [{ option1: 'Roll', sku: dwsku, inventory_management: 'shopify', inventory_quantity: 0 },
{ option1: 'Sample', sku: dwsku + '-Sample', price: '4.25', inventory_management: 'shopify', inventory_quantity: 0 }];
if (price) variants[0].price = price;
- const material = (p.material || '').trim() || 'Wallcovering';
const payload = { product: {
- title, vendor, product_type: material, status: 'draft', tags: ['new-from-scan', 'display_variant', color ? ('color:' + color) : ''].filter(Boolean).join(', '),
+ title, vendor: vreg.vendor, product_type: material, status: 'draft',
+ tags: ['new-from-scan', 'display_variant', mint.provisional ? 'Provisional-SKU' : '', color ? ('color:' + color) : ''].filter(Boolean).join(', '),
options: [{ name: 'Size' }], variants,
images: (Array.isArray(p._photos64) && p._photos64.length ? p._photos64 : (b64 ? [b64] : []))
.map((att, i) => ({ attachment: att, filename: `${dwsku}${i ? '-' + i : ''}.jpg`, position: i + 1 })),
metafields: [mf('custom', 'manufacturer_sku', mfr), mf('dwc', 'manufacturer_sku', mfr),
- mf('custom', 'pattern_name', name), mf('custom', 'color', color), mf('global', 'Brand', vendor),
+ mf('custom', 'pattern_name', name), mf('custom', 'color', color), mf('global', 'Brand', vreg.vendor),
mf('global', 'Collection', p.collection), mf('global', 'width', p.width), mf('global', 'length', p.roll_length),
mf('global', 'Pattern-Repeat', p.repeat), mf('global', 'Match', p.pattern_match), mf('global', 'Content', p.substrate),
mf('dwc', 'sold_by', p.how_sold), mf('custom', 'price_code', p.price_code), mf('custom', 'material', material)].filter(Boolean)
@@ -2426,19 +2612,21 @@ async function createNewItem(p, b64, dryRun) {
if (cr.status < 200 || cr.status >= 300) return { ok: false, err: `Shopify create HTTP ${cr.status}: ${(cr.raw || '').slice(0, 160)}` };
const pid = cr.body && cr.body.product && cr.body.product.id;
// stage into dw_unified (additive table — does NOT touch canonical catalog rows).
- // `specs` jsonb keeps the FULL captured spec set — every field off any spec sheet/sample, nothing dropped.
- const specsObj = { material, collection: p.collection || '', width: p.width || '', roll_length: p.roll_length || '',
- repeat: p.repeat || '', pattern_match: p.pattern_match || '', substrate: p.substrate || '',
- how_sold: p.how_sold || '', price_code: p.price_code || '' };
// $$-dollar-quoting: a stray "$$" in ANY value would break the quote → strip it from EVERY
// interpolated string (not just specs) so a vendor/mfr like "Foo $$ Bar" can't malform the SQL.
const esc = s => String(s == null ? '' : s).replace(/\$\$/g, '$');
const specsJson = esc(JSON.stringify(specsObj));
const stageSQL = `insert into new_items_staging (dw_sku, mfr_sku, vendor, vid, pattern_name, color, price, specs, shopify_product_id, created_via)
- values ($$${esc(dwsku)}$$,$$${esc(mfr)}$$,$$${esc(vendor)}$$,$$${esc(p.vid || '')}$$,$$${esc(name)}$$,$$${esc(color)}$$,${price || 'NULL'},$$${specsJson}$$::jsonb,${pid || 'NULL'},$$scan$$) on conflict do nothing`;
+ values ($$${esc(dwsku)}$$,$$${esc(mfr)}$$,$$${esc(vreg.vendor)}$$,$$${esc(vreg.vid || '')}$$,$$${esc(name)}$$,$$${esc(color)}$$,${price || 'NULL'},$$${specsJson}$$::jsonb,${pid || 'NULL'},$$scan$$) on conflict do nothing`;
execFile(PSQL, ['-d', DW_DB, '-c', 'create table if not exists new_items_staging (id bigserial primary key, dw_sku text, mfr_sku text, vendor text, vid text, pattern_name text, color text, price numeric, shopify_product_id bigint, created_via text, created_at timestamptz default now()); alter table new_items_staging add column if not exists specs jsonb; ' + stageSQL], { timeout: 8000 }, () => {});
+ // FileMaker WALLPAPER master — real create (dedupe on Series + Mfr Pattern so a re-run never dupes).
+ let fmResult = { committed: false, skipped: 'FileMaker disabled or not configured' };
+ if (FM_ENABLED() && FM.fmCreate) {
+ const dedupe = (mint.series && mfr) ? { 'Series': mint.series, 'Mfr Pattern': '==' + mfr } : null;
+ fmResult = await FM.fmCreate(FM_DB, FM_WP_CREATE_LAYOUT, fm.fieldData, { dryRun: false, dedupe }).catch(e => ({ committed: false, err: e.message }));
+ }
if (pid) CATALOG.push({ product_id: pid, title, status: 'DRAFT', dw_sku: dwsku, mfr, price, image: null, done: false });
- return { ok: true, product_id: pid, dw_sku: dwsku, title, status: 'draft', preview };
+ return { ok: true, product_id: pid, dw_sku: dwsku, title, status: 'draft', filemaker: fmResult, flags, preview };
} catch (e) { return { ok: false, err: e.message }; }
}
← 70ed167 batch mode: OCR falls back to manual after a timeout (no mor
·
back to Dw Photo Capture
·
Photo capture: vendor picker → sticky overlay bar on the liv 0c8c48f →