← back to Fm Wallpaper Sync
Add new-SKU approval gate: fmpro_wallpaper_review (canary batches) + local approval viewer :9796; nightly job queues new SKUs as pending
620fba68b20031480b78d84704ce541d3c838e28 · 2026-08-12 09:27:33 -0700 · steve
Files touched
M build.sqlA viewer/index.htmlA viewer/server.js
Diff
commit 620fba68b20031480b78d84704ce541d3c838e28
Author: steve <steve@designerwallcoverings.com>
Date: Wed Aug 12 09:27:33 2026 -0700
Add new-SKU approval gate: fmpro_wallpaper_review (canary batches) + local approval viewer :9796; nightly job queues new SKUs as pending
---
build.sql | 9 +++++
viewer/index.html | 99 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
viewer/server.js | 86 +++++++++++++++++++++++++++++++++++++++++++++++
3 files changed, 194 insertions(+)
diff --git a/build.sql b/build.sql
index 329777f..5548b0b 100644
--- a/build.sql
+++ b/build.sql
@@ -77,6 +77,15 @@ WHERE added_date::date = CURRENT_DATE AND nullif(sku,'') IS NOT NULL
ORDER BY sku, (nullif(vendor_name,'') IS NOT NULL) DESC
ON CONFLICT (sku) DO NOTHING;
+-- backfill thumbnails/titles for still-pending review rows from the live Shopify catalog
+UPDATE fmpro_wallpaper_review r SET image_url=s.image_url, title=s.title
+FROM (
+ SELECT substring(upper(regexp_replace(coalesce(variant_sku,sku),'[^A-Za-z0-9]','','g')) from '^[A-Z]+[0-9]+') core,
+ max(image_url) image_url, min(title) title
+ FROM shopify_products WHERE status='ACTIVE' AND coalesce(variant_sku,sku) IS NOT NULL GROUP BY 1
+) s
+WHERE r.review_status='pending' AND r.image_url IS NULL AND upper(r.sku)=s.core;
+
SELECT
(SELECT count(*) FROM fm_wallpaper_live) AS live_distinct_combosku,
(SELECT count(*) FROM fmpro) AS fmpro_rows,
diff --git a/viewer/index.html b/viewer/index.html
new file mode 100644
index 0000000..2888a36
--- /dev/null
+++ b/viewer/index.html
@@ -0,0 +1,99 @@
+<!doctype html><html><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
+<title>WALLPAPER fmpro — new-SKU approvals</title>
+<style>
+:root{--cols:5}
+*{box-sizing:border-box}
+body{margin:0;font:14px/1.4 -apple-system,Segoe UI,Roboto,sans-serif;background:#f4f5f6;color:#1c2529}
+header{position:sticky;top:0;z-index:5;background:#1F4E5F;color:#fff;padding:10px 16px;display:flex;gap:14px;align-items:center;flex-wrap:wrap}
+header h1{font-size:16px;margin:0;font-weight:700;letter-spacing:.3px}
+.pill{background:#2c6a80;border-radius:20px;padding:3px 10px;font-size:12px}
+.bar{padding:10px 16px;background:#fff;border-bottom:1px solid #dfe3e6;display:flex;gap:10px;align-items:center;flex-wrap:wrap;position:sticky;top:44px;z-index:4}
+select,input[type=search]{padding:6px 9px;border:1px solid #cbd2d6;border-radius:6px;font-size:13px;background:#fff}
+input[type=range]{width:120px}
+button{border:1px solid #cbd2d6;background:#fff;border-radius:6px;padding:6px 11px;font-size:13px;cursor:pointer}
+button:hover{background:#f0f2f3}
+.approve{background:#137a41;color:#fff;border-color:#137a41}.approve:hover{background:#0f6435}
+.reject{background:#a12626;color:#fff;border-color:#a12626}.reject:hover{background:#8a1f1f}
+.spacer{flex:1}
+#grid{display:grid;grid-template-columns:repeat(var(--cols),1fr);gap:10px;padding:14px 16px}
+.card{background:#fff;border:1px solid #dfe3e6;border-radius:9px;overflow:hidden;display:flex;flex-direction:column;position:relative}
+.card.sel{outline:2px solid #1F4E5F}
+.thumb{aspect-ratio:1;background:#eceef0 center/cover no-repeat;display:flex;align-items:center;justify-content:center;color:#9aa4a9;font-size:11px}
+.cb{position:absolute;top:6px;left:6px;width:17px;height:17px;cursor:pointer}
+.st{position:absolute;top:6px;right:6px;font-size:10px;font-weight:700;padding:2px 7px;border-radius:10px;text-transform:uppercase}
+.st.pending{background:#fde68a;color:#733}.st.approved{background:#137a41;color:#fff}.st.rejected{background:#a12626;color:#fff}
+.body{padding:8px 10px;display:flex;flex-direction:column;gap:3px;flex:1}
+.sku{font-weight:700;font-size:14px}
+.meta{font-size:11.5px;color:#5a666c;word-break:break-word}
+.when{font-size:10.5px;color:#8894;margin-top:2px}
+.acts{display:flex;gap:6px;padding:8px 10px;border-top:1px solid #eef1f2}
+.acts button{flex:1;padding:5px}
+#more{margin:6px 16px 40px;padding:10px 20px}
+.dim{opacity:.55}
+</style></head><body>
+<header>
+ <h1>WALLPAPER fmpro · new-SKU approvals</h1>
+ <span class="pill" id="sumPending">pending —</span>
+ <span class="pill" id="sumApproved">approved —</span>
+ <span class="pill" id="sumRejected">rejected —</span>
+ <span class="spacer"></span>
+ <span class="pill">canary batches: <span id="batches">—</span></span>
+</header>
+<div class="bar">
+ <select id="status"><option value="pending">Pending</option><option value="approved">Approved</option><option value="rejected">Rejected</option></select>
+ <select id="vendor"><option value="">All vendors</option></select>
+ <input type="search" id="q" placeholder="search sku / mfr / title">
+ <select id="sort"><option value="sku">SKU A→Z</option><option value="vendor">Vendor</option><option value="newest">Newest</option><option value="img">Has image</option></select>
+ <label>density <input type="range" id="dens" min="3" max="9" value="5"></label>
+ <span class="spacer"></span>
+ <span id="selinfo" class="meta"></span>
+ <button class="approve" id="apSel">Approve selected</button>
+ <button class="reject" id="rjSel">Reject selected</button>
+ <button class="approve" id="apAll" title="approve everything matching the current filter">Approve ALL in filter</button>
+ <button class="reject" id="rjAll">Reject ALL in filter</button>
+</div>
+<div id="grid"></div>
+<button id="more">Load more</button>
+<script>
+const $=s=>document.querySelector(s), grid=$('#grid');
+let offset=0,total=0,sel=new Set(),loading=false;
+const st=()=>({status:$('#status').value,vendor:$('#vendor').value,q:$('#q').value.trim(),sort:$('#sort').value});
+const LS=(k,v)=>v===undefined?localStorage.getItem('fmr_'+k):localStorage.setItem('fmr_'+k,v);
+if(LS('dens')){$('#dens').value=LS('dens')}; if(LS('sort')){$('#sort').value=LS('sort')}
+document.documentElement.style.setProperty('--cols',$('#dens').value);
+$('#dens').oninput=e=>{document.documentElement.style.setProperty('--cols',e.target.value);LS('dens',e.target.value)};
+
+async function summary(){const s=await (await fetch('/api/summary')).json();
+ const c=Object.fromEntries(s.counts.map(r=>[r.status,r.n]));
+ $('#sumPending').textContent='pending '+(c.pending||0);$('#sumApproved').textContent='approved '+(c.approved||0);$('#sumRejected').textContent='rejected '+(c.rejected||0);
+ $('#batches').textContent=s.batches.map(b=>b.batch+' ('+b.pending+'▸)').join(' ');
+ const v=$('#vendor');const cur=v.value;v.innerHTML='<option value="">All vendors ('+(c.pending||0)+')</option>'+s.vendors.map(x=>`<option value="${x.vendor.replace(/"/g,'')}">${x.vendor} (${x.n})</option>`).join('');v.value=cur;}
+function card(r){const img=r.image_url?`style="background-image:url('${r.image_url.replace(/'/g,'')}')"`:'';
+ return `<div class="card ${sel.has(r.sku)?'sel':''}" data-sku="${r.sku}">
+ <input type="checkbox" class="cb" ${sel.has(r.sku)?'checked':''}>
+ <span class="st ${r.review_status}">${r.review_status}</span>
+ <div class="thumb" ${img}>${r.image_url?'':'no image'}</div>
+ <div class="body"><div class="sku">${r.sku}</div>
+ <div class="meta">${r.vendor_name||'<span class=dim>(no vendor)</span>'}</div>
+ <div class="meta">mfr: ${r.mfr_sku||'—'} · ${r.sku_prefix||''}</div>
+ <div class="meta">${r.title||r.image_filename||''}</div>
+ <div class="when" title="${r.batch}">🕓 ${r.seen}</div></div>
+ <div class="acts"><button class="approve" data-a="approved">Approve</button><button class="reject" data-a="rejected">Reject</button></div></div>`;}
+async function load(reset){if(loading)return;loading=true;if(reset){offset=0;grid.innerHTML='';sel.clear();syncSel();}
+ const p=new URLSearchParams({...st(),limit:60,offset});const d=await(await fetch('/api/list?'+p)).json();
+ total=d.total;grid.insertAdjacentHTML('beforeend',d.rows.map(card).join(''));offset+=d.rows.length;
+ $('#more').style.display=offset<total?'block':'none';$('#more').textContent=`Load more (${offset}/${total})`;loading=false;}
+function syncSel(){$('#selinfo').textContent=sel.size?sel.size+' selected':'';}
+grid.addEventListener('click',async e=>{const card=e.target.closest('.card');if(!card)return;const sku=card.dataset.sku;
+ if(e.target.classList.contains('cb')){e.target.checked?sel.add(sku):sel.delete(sku);card.classList.toggle('sel',e.target.checked);syncSel();return;}
+ const a=e.target.dataset.a;if(a){await decide({scope:'selected',skus:[sku],decision:a});card.querySelector('.st').className='st '+a;card.querySelector('.st').textContent=a;card.classList.add('dim');summary();}});
+async function decide(payload){const r=await(await fetch('/api/decide',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify(payload)})).json();return r;}
+async function bulkSel(dec){if(!sel.size)return alert('nothing selected');const r=await decide({scope:'selected',skus:[...sel],decision:dec});await load(true);summary();alert(`${dec}: ${r.updated}`);}
+async function bulkFilter(dec){const s=st();if(!confirm(`${dec} ALL ${s.status} matching this filter? This can be many thousands.`))return;const r=await decide({scope:'filter',decision:dec,...s});await load(true);summary();alert(`${dec}: ${r.updated}`);}
+$('#apSel').onclick=()=>bulkSel('approved');$('#rjSel').onclick=()=>bulkSel('rejected');
+$('#apAll').onclick=()=>bulkFilter('approved');$('#rjAll').onclick=()=>bulkFilter('rejected');
+['status','vendor','sort'].forEach(id=>$('#'+id).onchange=()=>{if(id=='sort')LS('sort',$('#sort').value);load(true)});
+let t;$('#q').oninput=()=>{clearTimeout(t);t=setTimeout(()=>load(true),300)};
+$('#more').onclick=()=>load(false);
+summary();load(true);
+</script></body></html>
diff --git a/viewer/server.js b/viewer/server.js
new file mode 100644
index 0000000..97e4c68
--- /dev/null
+++ b/viewer/server.js
@@ -0,0 +1,86 @@
+#!/usr/bin/env node
+// Local approval viewer for new WALLPAPER fmpro SKUs (fmpro_wallpaper_review).
+// Basic-auth admin/DW2024!. Zero deps — queries via psql. Read/write dw_unified.
+const http = require('http');
+const { execFile } = require('child_process');
+const fs = require('fs');
+const path = require('path');
+
+const PORT = 9796, USER = 'admin', PASS = 'DW2024!';
+const PG = ['-h', '127.0.0.1', '-U', 'dw_admin', '-d', 'dw_unified', '-tAqX'];
+const HTML = fs.readFileSync(path.join(__dirname, 'index.html'), 'utf8');
+
+const q1 = s => "'" + String(s).replace(/'/g, "''") + "'"; // sql string literal
+const clean = s => String(s || '').replace(/[^\x20-\x7E]/g, '').slice(0, 120);
+const skuOk = s => /^[A-Za-z0-9_.\- ]{1,60}$/.test(s);
+
+function sql(query) {
+ return new Promise((res, rej) => {
+ execFile('psql', [...PG, '-c', query], { maxBuffer: 64 * 1024 * 1024 }, (e, out) => e ? rej(e) : res(out));
+ });
+}
+const jsonQuery = async (inner) =>
+ JSON.parse((await sql(`SELECT coalesce(json_agg(t),'[]') FROM (${inner}) t`)).trim() || '[]');
+
+function whereClause({ status, vendor, q }) {
+ const w = [];
+ w.push(`review_status = ${q1((status && ['pending','approved','rejected'].includes(status)) ? status : 'pending')}`);
+ if (vendor) w.push(vendor === '(none)' ? `nullif(vendor_name,'') IS NULL` : `vendor_name = ${q1(clean(vendor))}`);
+ if (q) { const t = q1('%' + clean(q).toLowerCase() + '%'); w.push(`(lower(sku) LIKE ${t} OR lower(coalesce(mfr_sku,'')) LIKE ${t} OR lower(coalesce(title,'')) LIKE ${t})`); }
+ return w.join(' AND ');
+}
+
+const send = (r, code, body, type = 'application/json') => { r.writeHead(code, { 'Content-Type': type }); r.end(body); };
+
+const server = http.createServer(async (req, res) => {
+ const auth = (req.headers.authorization || '').split(' ')[1] || '';
+ const [u, p] = Buffer.from(auth, 'base64').toString().split(':');
+ if (u !== USER || p !== PASS) { res.writeHead(401, { 'WWW-Authenticate': 'Basic realm="fmpro-review"' }); return res.end('auth'); }
+ const url = new URL(req.url, 'http://x');
+ try {
+ if (url.pathname === '/') return send(res, 200, HTML, 'text/html');
+
+ if (url.pathname === '/api/summary') {
+ const counts = await jsonQuery(`SELECT review_status status, count(*)::int n FROM fmpro_wallpaper_review GROUP BY 1`);
+ const vendors = await jsonQuery(`SELECT coalesce(nullif(vendor_name,''),'(none)') vendor, count(*)::int n
+ FROM fmpro_wallpaper_review WHERE review_status='pending' GROUP BY 1 ORDER BY 2 DESC`);
+ const batches = await jsonQuery(`SELECT batch_date::text batch, count(*)::int n, count(*) FILTER (WHERE review_status='pending')::int pending
+ FROM fmpro_wallpaper_review GROUP BY 1 ORDER BY 1 DESC`);
+ return send(res, 200, JSON.stringify({ counts, vendors, batches }));
+ }
+
+ if (url.pathname === '/api/list') {
+ const g = Object.fromEntries(url.searchParams);
+ const limit = Math.min(500, Math.max(1, parseInt(g.limit) || 60));
+ const offset = Math.max(0, parseInt(g.offset) || 0);
+ const sort = ({ sku: 'sku', vendor: 'vendor_name NULLS LAST, sku', newest: 'first_seen DESC', img: '(image_url IS NOT NULL) DESC, sku' })[g.sort] || 'sku';
+ const where = whereClause(g);
+ const total = (await jsonQuery(`SELECT count(*)::int n FROM fmpro_wallpaper_review WHERE ${where}`))[0]?.n || 0;
+ const rows = await jsonQuery(`SELECT sku, review_status, vendor_name, mfr_sku, sku_prefix, image_filename,
+ image_url, title, to_char(first_seen,'Mon DD, YYYY, HH12:MI AM') seen, batch_date::text batch
+ FROM fmpro_wallpaper_review WHERE ${where} ORDER BY ${sort} LIMIT ${limit} OFFSET ${offset}`);
+ return send(res, 200, JSON.stringify({ total, rows, limit, offset }));
+ }
+
+ if (url.pathname === '/api/decide' && req.method === 'POST') {
+ let body = ''; for await (const c of req) body += c;
+ const b = JSON.parse(body || '{}');
+ const decision = b.decision === 'approved' ? 'approved' : b.decision === 'rejected' ? 'rejected' : b.decision === 'pending' ? 'pending' : null;
+ if (!decision) return send(res, 400, JSON.stringify({ error: 'bad decision' }));
+ let where;
+ if (b.scope === 'selected' && Array.isArray(b.skus) && b.skus.length) {
+ const list = b.skus.filter(skuOk).map(q1).join(',');
+ if (!list) return send(res, 400, JSON.stringify({ error: 'no valid skus' }));
+ where = `sku IN (${list})`;
+ } else if (b.scope === 'filter') {
+ where = whereClause(b); // acts on everything matching the current filter (status defaults to pending)
+ } else return send(res, 400, JSON.stringify({ error: 'bad scope' }));
+ const out = await sql(`WITH u AS (UPDATE fmpro_wallpaper_review
+ SET review_status=${q1(decision)}, decided_at=now(), decided_by='local-viewer'
+ WHERE ${where} RETURNING 1) SELECT count(*)::int FROM u`);
+ return send(res, 200, JSON.stringify({ updated: parseInt(out.trim()) || 0, decision }));
+ }
+ return send(res, 404, JSON.stringify({ error: 'not found' }));
+ } catch (e) { return send(res, 500, JSON.stringify({ error: String(e.message || e) })); }
+});
+server.listen(PORT, '127.0.0.1', () => console.log(`fmpro-review viewer on http://127.0.0.1:${PORT} (admin/DW2024!)`));
← ebc0a69 auto-data-snapshot: 2026-08-12T09:25:42 (1 data files) — bui
·
back to Fm Wallpaper Sync
·
(newest)