← back to Dw Photo Capture
Add discontinued-SKU resolver → red card → live successor link (local only)
ba63786d871693df4eb6ca7697fc958594ec7024 · 2026-07-08 11:00:29 -0700 · Steve Abrams
/api/discontinued?q=<sku> resolves discontinued/deleted SKUs dash/punct-insensitively
(CHC-216830 == CHC216830 == CHC-216830-SAMPLE) and links the live successor via the
COPY-OF mfr hint + sibling harvest + active title match, ported from substitutefinder
find.py. Scanner lookup falls back to it on 0 active hits and shows a red successor card.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M public/index.htmlM server.js
Diff
commit ba63786d871693df4eb6ca7697fc958594ec7024
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 8 11:00:29 2026 -0700
Add discontinued-SKU resolver → red card → live successor link (local only)
/api/discontinued?q=<sku> resolves discontinued/deleted SKUs dash/punct-insensitively
(CHC-216830 == CHC216830 == CHC-216830-SAMPLE) and links the live successor via the
COPY-OF mfr hint + sibling harvest + active title match, ported from substitutefinder
find.py. Scanner lookup falls back to it on 0 active hits and shows a red successor card.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/index.html | 48 ++++++++++++++++++++-
server.js | 122 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 168 insertions(+), 2 deletions(-)
diff --git a/public/index.html b/public/index.html
index cfb838c..bfeab04 100644
--- a/public/index.html
+++ b/public/index.html
@@ -51,6 +51,18 @@
.fav-btn.on{color:var(--gold)}
.fav-btn:active{transform:scale(.88)}
.b-need{background:#3a2a14;color:#e7c878} .b-done{background:var(--green);color:#fff} .b-err{background:var(--red);color:#fff}
+ /* Discontinued SKU → red card with live successor link */
+ .disco-card{grid-column:1/-1;background:#2a1512;border:1px solid #c0392b;border-left:5px solid #c0392b;border-radius:16px;padding:16px 18px;display:flex;flex-direction:column;gap:10px}
+ .disco-card .dhead{font:800 16px/1.3 "SF Pro Display",sans-serif;color:#e8776a;display:flex;align-items:center;gap:8px}
+ .disco-card .dsku{font:600 12px/1 ui-monospace,Menlo,monospace;color:#e8a79e;letter-spacing:.03em}
+ .disco-card .dstatus{font-size:10.5px;font-weight:700;letter-spacing:.05em;color:#fff;background:#c0392b;border-radius:99px;padding:2px 9px;text-transform:uppercase}
+ .disco-succ{background:#16140f;border:1px solid #3a5a44;border-radius:12px;padding:12px 14px;display:flex;flex-direction:column;gap:6px}
+ .disco-succ .slabel{font-size:11px;letter-spacing:.06em;text-transform:uppercase;color:#7fbf9a;font-weight:700}
+ .disco-succ .stitle{font:600 15px/1.3 "SF Pro Display",sans-serif;color:var(--ink)}
+ .disco-succ .ssku{font:600 12px/1 ui-monospace,Menlo,monospace;color:var(--green)}
+ .disco-succ a{color:var(--green);text-decoration:none;font-weight:700}
+ .disco-succ .go{align-self:flex-start;margin-top:4px;background:var(--green);color:#fff;border:none;border-radius:10px;padding:8px 15px;font-size:13.5px;font-weight:700;cursor:pointer;text-decoration:none;display:inline-block}
+ .disco-nosucc{font-size:13px;color:var(--muted)}
.b-live{background:#1f7a48;color:#fff;box-shadow:0 0 0 2px rgba(63,160,106,.4)}
.b-new{background:#2a2350;color:#cbb8ff;box-shadow:0 0 0 1px #5a4aa0}
.body{padding:11px 12px 13px;display:flex;flex-direction:column;gap:6px;flex:1}
@@ -493,15 +505,22 @@ async function load(){
ITEMS=d.items; render();
}
function priced(x){return x.price && parseFloat(x.price)>4.25;}
-let LOOKUP=[], LOOKUP_INDEXED=0, _lkT=0;
+let LOOKUP=[], LOOKUP_INDEXED=0, _lkT=0, DISCO=null;
async function doLookup(){
const q=($('#q').value||'').trim();
+ DISCO=null;
if(!q){ LOOKUP=[]; render(); return; }
try{ const r=await fetch('/api/lookup?q='+encodeURIComponent(q)); const d=await r.json();
LOOKUP=(d.items||[]).map(it=>({...it, keep_images: !it.needs_create && !!it.image})); // preserve existing room/lifestyle shots on live SKUs
LOOKUP_INDEXED=d.indexed||0;
- // not in the TWIL index? auto-escalate to a store-wide search (mfr# + DW SKU across every vendor)
+ // No active TWIL hit? Before escalating to a store-wide search, check whether this is a
+ // DISCONTINUED / deleted SKU — if so, show the RED card pointing at its live successor.
if(!LOOKUP.length && q){
+ try{
+ const dr=await(await fetch('/api/discontinued?q='+encodeURIComponent(q))).json();
+ if(dr && dr.found && dr.discontinued){ DISCO=dr; render(); return; }
+ }catch(e){}
+ // otherwise auto-escalate to a store-wide search (mfr# + DW SKU across every vendor)
toast('Not in TWIL — searching all Shopify…');
document.querySelectorAll('.chip').forEach(z=>z.classList.remove('on')); const sc=document.querySelector('.chip[data-f="shop"]'); if(sc)sc.classList.add('on');
filter='shop'; setLS('filter','shop'); $('#q').placeholder='🌐 Any Shopify SKU, name, vendor, or collection…';
@@ -510,6 +529,23 @@ async function doLookup(){
render();
}catch(e){ toast('Lookup failed'); }
}
+function discoCardHtml(d){
+ const succ = d.successor;
+ const succUrl = succ && succ.handle ? ('https://www.designerwallcoverings.com/products/'+succ.handle) : '';
+ const succHtml = succ
+ ? `<div class="disco-succ">
+ <span class="slabel">Now available as →</span>
+ <span class="stitle">${(succ.title||'').replace(/</g,'<')}</span>
+ <span class="ssku">${(succ.sku||'').replace(/</g,'<')}</span>
+ ${succUrl?`<a class="go" href="${succUrl}" target="_blank" rel="noopener">View live product →</a>`:''}
+ </div>`
+ : `<div class="disco-nosucc">No live successor found — search the store for a comparable pattern.</div>`;
+ return `<div class="disco-card">
+ <div class="dhead">⛔ Discontinued <span class="dstatus">${(d.status||'').replace(/</g,'<')}</span></div>
+ <div>${(d.title||'').replace(/</g,'<')} <span class="dsku">${(d.sku||'').replace(/</g,'<')}</span></div>
+ ${succHtml}
+ </div>`;
+}
// The not-yet-in-Shopify sheet items — photograph one to create + go live.
let NEWITEMS=[];
async function loadNew(){
@@ -658,6 +694,14 @@ function render(){
grid.innerHTML='<div class="empty">📸 Pick a filter, 🔎 search a SKU/collection, or tap 🔢 Scan to begin.'+(total?('<br><br>'+ (total-done) +' still need a photo.'):'')+'</div>';
return;
}
+ // A resolved discontinued/deleted SKU → red successor card (takes over the grid for the 'any' lookup filter).
+ if(DISCO && (filter==='any'||filter==='shop')){
+ $('#prog').style.width='0%';
+ $('#count').textContent='Discontinued SKU';
+ $('#remain').textContent='→ see live successor';
+ grid.innerHTML=discoCardHtml(DISCO);
+ return;
+ }
const q=($('#q').value||'').toLowerCase(), sort=$('#sort').value;
const src = filter==='any' ? LOOKUP : filter==='shop' ? SHOPSEARCH : filter==='new' ? NEWITEMS
: filter==='twil' ? TWILITEMS : filter==='fav' ? FAVORITES : filter==='recent' ? RECENTS
diff --git a/server.js b/server.js
index aa9f9d1..fcf0235 100644
--- a/server.js
+++ b/server.js
@@ -1133,6 +1133,19 @@ const appHandler = (req, res) => {
return send(res, 200, { total: matches.length, items: matches, indexed: INDEX.length });
}
+ // Discontinued/deleted SKU resolver → { found, sku, title, status, discontinued, successor }.
+ // Dash/punct-insensitive; catches base-of-SAMPLE (CHC-216830 == CHC216830 == CHC-216830-SAMPLE).
+ // The scanner UI falls back to this when the main lookup returns 0 active hits, and renders a RED
+ // card pointing at the live SUCCESSOR (the product the user should photograph/reorder). $0 local psql.
+ if (u.pathname === '/api/discontinued' && req.method === 'GET') {
+ const q = (u.searchParams.get('q') || u.searchParams.get('sku') || '').trim();
+ if (!q) return send(res, 200, { found: false });
+ resolveDiscontinued(q)
+ .then(r => send(res, 200, r || { found: false, sku: q }))
+ .catch(e => send(res, 200, { found: false, err: e.message }));
+ return;
+ }
+
// All sheet GRS items that don't exist in Shopify yet — the "create on photo" worklist.
if (u.pathname === '/api/new' && req.method === 'GET') {
const items = INDEX.filter(x => x.needs_create);
@@ -2164,6 +2177,115 @@ function identifyUnified(code) {
});
}
+// ── Discontinued-SKU resolver + live successor ───────────────────────────────
+// Ported from ~/.claude/skills/substitutefinder/find.py (resolve_successor + target query).
+// A typed SKU like CHC-216830 (or CHC216830, or CHC-216830-SAMPLE) resolves — dash/punct-
+// insensitively, and by de-dashed PREFIX so the -SAMPLE variant is caught — to a stored row
+// (e.g. CHC-216830-SAMPLE / DELETED_FROM_SHOPIFY). If that row is NOT active/draft it's flagged
+// discontinued and linked to its live SUCCESSOR: the nearest ACTIVE product whose name matches
+// the dead item's real pattern — resolved via a COPY-OF-<pattern> mfr hint (often only on a
+// SIBLING sharing the dead title) then the dead base title itself. $0 local psql.
+const _pgLit = s => "'" + String(s == null ? '' : s).replace(/'/g, "''") + "'";
+function _pgq(sql) {
+ return new Promise(resolve => {
+ execFile(PSQL, ['-d', DW_DB, '--csv', '-v', 'ON_ERROR_STOP=1', '-c', sql],
+ { timeout: 8000, maxBuffer: 4 * 1024 * 1024 }, (err, out) => {
+ if (err) return resolve([]);
+ const lines = String(out || '').split('\n').filter(Boolean);
+ if (lines.length < 1) return resolve([]);
+ // minimal CSV parse (handles quoted fields with embedded commas/quotes)
+ const parse = line => {
+ const f = []; let cur = '', q = false;
+ for (let i = 0; i < line.length; i++) {
+ const ch = line[i];
+ if (q) { if (ch === '"') { if (line[i + 1] === '"') { cur += '"'; i++; } else q = false; } else cur += ch; }
+ else if (ch === '"') q = true;
+ else if (ch === ',') { f.push(cur); cur = ''; }
+ else cur += ch;
+ }
+ f.push(cur); return f;
+ };
+ const hdr = parse(lines[0]);
+ resolve(lines.slice(1).map(l => { const c = parse(l); const o = {}; hdr.forEach((h, i) => o[h] = c[i] == null ? '' : c[i]); return o; }));
+ });
+ });
+}
+const _cleanTitle = s => String(s || '').replace(/�/g, '').replace(/ +/g, ' ').trim();
+function _succName(s) {
+ s = String(s || '').replace(/copy[\s_-]*of[\s_-]*/ig, ''); // drop COPY-OF-
+ s = s.replace(/[\s_-]+\d+$/, ''); // drop trailing -1..-8
+ s = s.replace(/[_-]+/g, ' ').trim(); // dashes/underscores -> spaces
+ return s.replace(/\s*(wall\s?paper|wall\s?covering)\s*$/i, '').trim(); // drop generic suffix
+}
+async function resolveSuccessor(mfr, title) {
+ const names = [_succName(mfr)];
+ const baseTitle = String(title || '').replace(/\s*\|.*$/, '').trim();
+ if (baseTitle.length >= 4) {
+ // harvest COPY-OF-<x> hints from any row sharing this dead title (often a sibling carries it)
+ const sibs = await _pgq(`select coalesce(mfr_sku,'') mfr from shopify_products
+ where title ilike '%'||${_pgLit(baseTitle)}||'%' and mfr_sku ~* 'copy' limit 10;`);
+ for (const r of sibs) names.push(_succName(r.mfr));
+ names.push(baseTitle);
+ }
+ const seen = new Set(), ordered = [];
+ for (let n of names) { n = (n || '').trim(); if (n.length >= 4 && !seen.has(n.toLowerCase())) { seen.add(n.toLowerCase()); ordered.push(n); } }
+ for (const name of ordered) {
+ const lit = _pgLit(name);
+ const rows = await _pgq(`select dw_sku, coalesce(title,'') title, coalesce(handle,'') handle
+ from shopify_products
+ where lower(status) in ('active','draft') and image_url is not null
+ and dw_sku is not null and dw_sku <> '' and title ilike '%'||${lit}||'%'
+ order by (title !~* 'memo|sample') desc,
+ (lower(title) like lower(${lit})||'%') desc, dw_sku
+ limit 1;`);
+ if (rows.length) { const r = rows[0]; return { sku: r.dw_sku, title: _cleanTitle(r.title), handle: r.handle }; }
+ }
+ return null;
+}
+// Resolve a (usually discontinued/deleted) SKU + its live successor. Returns null if nothing matches.
+async function resolveDiscontinued(query) {
+ const q = String(query || '').trim();
+ if (!q) return null;
+ const qbase = q.replace(/-sample$/i, ''); // CHC-216830-SAMPLE typed → base CHC-216830
+ const lit = _pgLit(qbase);
+ // dash/punct-insensitive base match + de-dashed PREFIX (catches the -SAMPLE suffix); include
+ // archived/deleted. Prefer exact dw_sku, then a live one if the base has both, then archived.
+ const tgt = await _pgq(`select dw_sku, coalesce(title,'') title, coalesce(status,'') status,
+ coalesce(handle,'') handle, coalesce(mfr_sku,'') mfr
+ from shopify_products
+ where regexp_replace(lower(dw_sku),'[^a-z0-9]','','g') like regexp_replace(lower(${lit}),'[^a-z0-9]','','g')||'%'
+ or dw_sku ilike ${lit} or sku ilike ${lit}
+ or handle ilike ('%'||${lit}||'%') or mfr_sku ilike ${lit}
+ order by (dw_sku ilike ${lit}) desc,
+ (lower(status) in ('active','draft')) desc,
+ (regexp_replace(lower(dw_sku),'[^a-z0-9]','','g') = regexp_replace(lower(${lit}),'[^a-z0-9]','','g')) desc,
+ (lower(status)='archived') desc
+ limit 1;`);
+ if (!tgt.length) return null;
+ const t = tgt[0];
+ const status = (t.status || '').trim();
+ const discontinued = !['active', 'draft'].includes(status.toLowerCase());
+ let successor = null;
+ if (discontinued) {
+ successor = await resolveSuccessor(t.mfr, t.title);
+ if (!successor) {
+ // last-resort: nearest active product whose title shares the dead base title
+ const base = String(t.title || '').replace(/\s*\|.*$/, '').trim();
+ if (base.length >= 4) {
+ const rows = await _pgq(`select dw_sku, coalesce(title,'') title, coalesce(handle,'') handle
+ from shopify_products where lower(status) in ('active','draft') and image_url is not null
+ and dw_sku is not null and dw_sku <> '' and title ilike '%'||${_pgLit(base)}||'%'
+ order by (title !~* 'memo|sample') desc, dw_sku limit 1;`);
+ if (rows.length) successor = { sku: rows[0].dw_sku, title: _cleanTitle(rows[0].title), handle: rows[0].handle };
+ }
+ }
+ }
+ return {
+ found: true, sku: t.dw_sku, title: _cleanTitle(t.title),
+ status: status.toUpperCase(), discontinued, successor
+ };
+}
+
// Query the CLIP visual-search service (front/pattern photo → visually-nearest catalog products).
function visualSearch(b64, k) {
return new Promise(resolve => {
← 140c994 gitignore the runtime all-dw feed cache (data/all-catalog.js
·
back to Dw Photo Capture
·
auto-save: 2026-07-08T11:05:21 (1 files) — data/build.json 5f86fc5 →