← back to All Designerwallcoverings
auto-save: 2026-07-15T16:38:37 (4 files) — scripts/adapters/kravet-db.js scripts/build-coverage.js server.js scripts/backfill-kravet-mfr.js
3658c7c66db7b9b4394d6a1d6f94c7657292e015 · 2026-07-15 16:38:38 -0700 · Steve Abrams
Files touched
M scripts/adapters/kravet-db.jsA scripts/backfill-kravet-mfr.jsM scripts/build-coverage.jsM server.js
Diff
commit 3658c7c66db7b9b4394d6a1d6f94c7657292e015
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 15 16:38:38 2026 -0700
auto-save: 2026-07-15T16:38:37 (4 files) — scripts/adapters/kravet-db.js scripts/build-coverage.js server.js scripts/backfill-kravet-mfr.js
---
scripts/adapters/kravet-db.js | 8 ++
scripts/backfill-kravet-mfr.js | 173 +++++++++++++++++++++++++++++++++++++++++
scripts/build-coverage.js | 19 ++++-
server.js | 6 ++
4 files changed, 202 insertions(+), 4 deletions(-)
diff --git a/scripts/adapters/kravet-db.js b/scripts/adapters/kravet-db.js
index decb1a9..79fbcca 100644
--- a/scripts/adapters/kravet-db.js
+++ b/scripts/adapters/kravet-db.js
@@ -43,6 +43,14 @@ async function run({ sku, pool, log }) {
for (const k of candidateKeys(rows[0].mfr_sku)) keys.add(k);
}
} catch { /* shopify_products bridge optional — raw-sku keys still tried */ }
+ // Curated backfill bridge — rows whose mirror mfr_sku is empty but were resolved by
+ // scripts/backfill-kravet-mfr.js (title / price-consensus / spec / local-model tiers).
+ try {
+ const { rows } = await pool.query(
+ `SELECT mfr_sku FROM kravet_mfr_backfill
+ WHERE upper(sku)=upper($1) OR upper(base_sku)=upper($1) LIMIT 1`, [sku]);
+ if (rows[0]) for (const k of candidateKeys(rows[0].mfr_sku)) keys.add(k);
+ } catch { /* backfill table optional */ }
if (!keys.size) return { available: false, session_opened: false, reason: 'sku required' };
// 2. Authoritative price row — exact upper() match on any candidate key.
diff --git a/scripts/backfill-kravet-mfr.js b/scripts/backfill-kravet-mfr.js
new file mode 100644
index 0000000..107a31d
--- /dev/null
+++ b/scripts/backfill-kravet-mfr.js
@@ -0,0 +1,173 @@
+#!/usr/bin/env node
+// Backfill mfr_sku for ACTIVE Kravet-family grid SKUs whose Shopify mirror row has no
+// mfr number, so the $0 DB_AUTHORITATIVE price check can resolve them. $0 end-to-end:
+// deterministic tiers first, then a LOCAL Ollama model for the residual — never a paid API.
+//
+// tier 1 title-exact title (pattern, color) hits exactly ONE kravet_catalog mfr code
+// tier 2 price-consensus many candidate codes but ALL carry the SAME MAP price — any
+// candidate answers the price button correctly
+// tier 3 spec-filter body_html specs (width / material) narrow candidates to one,
+// or to a price consensus
+// tier 4 ollama local model picks among ≤15 spec-filtered candidates using all
+// fields; accepted only when its pick carries a KAP price
+// else unresolved stays honest — the chip falls through to Email Vendor
+//
+// Writes to kravet_mfr_backfill (supplemental local table — the shopify_products mirror
+// re-syncs from live Shopify, so backfilled values would be lost there; consumers COALESCE
+// from this table instead). Idempotent: re-runs upsert by sku.
+//
+// Usage: node scripts/backfill-kravet-mfr.js [--dry] [--limit N]
+
+const { Pool } = require('pg');
+const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
+const OLLAMA_URLS = [
+ process.env.OLLAMA_URL || 'http://192.168.1.133:11434', // Mac1 qwen3:14b
+ 'http://127.0.0.1:11434', // local fallback (hermes3:8b)
+];
+const OLLAMA_MODELS = ['qwen3:14b', 'hermes3:8b'];
+const DRY = process.argv.includes('--dry');
+const LIMIT = (() => { const i = process.argv.indexOf('--limit'); return i > -1 ? +process.argv[i + 1] : 0; })();
+
+const pool = new Pool({ connectionString: DSN, max: 2 });
+
+async function pickOllama() {
+ for (const url of OLLAMA_URLS) {
+ for (const model of OLLAMA_MODELS) {
+ try {
+ const r = await fetch(`${url}/api/generate`, {
+ method: 'POST',
+ body: JSON.stringify({ model, prompt: 'hi /no_think', stream: false, options: { num_predict: 1 } }),
+ signal: AbortSignal.timeout(15000),
+ });
+ if (r.ok) return { url, model };
+ } catch { /* try next */ }
+ }
+ }
+ return null;
+}
+
+async function askOllama(ep, prompt) {
+ // think:false — qwen3 otherwise burns the whole num_predict budget in its thinking
+ // channel and returns an empty response (done_reason:length).
+ const r = await fetch(`${ep.url}/api/generate`, {
+ method: 'POST',
+ body: JSON.stringify({ model: ep.model, prompt, stream: false, think: false, options: { num_predict: 80, temperature: 0 } }),
+ signal: AbortSignal.timeout(90000),
+ });
+ if (!r.ok) throw new Error(`ollama ${r.status}`);
+ const d = await r.json();
+ return (d.response || d.thinking || '').trim();
+}
+
+const parseWidth = (body) => { const m = /Width:\s*([\d.]+)\s*Inch/i.exec(body || ''); return m ? parseFloat(m[1]) : null; };
+const parseMaterial = (body) => { const m = /Material:\s*([^\n<]+)/i.exec(body || ''); return m ? m[1].trim().toUpperCase() : null; };
+
+async function main() {
+ await pool.query(`CREATE TABLE IF NOT EXISTS kravet_mfr_backfill (
+ sku text PRIMARY KEY,
+ base_sku text NOT NULL,
+ mfr_sku text NOT NULL,
+ method text NOT NULL,
+ confidence text NOT NULL,
+ map_price numeric,
+ note text,
+ created_at timestamptz DEFAULT now()
+ )`);
+
+ // Gap rows + parsed title parts (mirror the server's display-vendor scope: Kravet family)
+ const { rows: gaps } = await pool.query(`
+ SELECT sku, title, body_html,
+ upper(trim(split_part(split_part(title,' Wallcoverings',1), ',', 1))) AS pat,
+ upper(trim(regexp_replace(split_part(split_part(title,' Wallcoverings',1), ',', 2),'^\\s+',''))) AS col
+ FROM shopify_products
+ WHERE vendor IN ('Kravet','Lee Jofa','Lee Jofa Modern','Brunschwig & Fils','Cole & Son',
+ 'GP & J Baker','Gaston Y Daniela','Threads','Baker Lifestyle',
+ 'Kravet Couture','Kravet Design')
+ AND status='ACTIVE' AND (mfr_sku IS NULL OR mfr_sku='')
+ ${LIMIT ? 'LIMIT ' + LIMIT : ''}`);
+ console.log(`gap rows: ${gaps.length}`);
+
+ // Candidate lookup: kravet_catalog by normalized pattern+color, joined to the price file
+ const candSql = `
+ SELECT DISTINCT upper(trim(kc.mfr_sku)) AS mfr, kc.width, kc.material, kc.composition,
+ coalesce(kap1.new_map, kap2.new_map) AS new_map
+ FROM kravet_catalog kc
+ LEFT JOIN kravet_authoritative_pricing kap1 ON upper(kap1.mfr_sku)=upper(trim(kc.mfr_sku))
+ LEFT JOIN kravet_authoritative_pricing kap2 ON upper(kap2.mfr_sku)=upper(trim(kc.mfr_sku))||'.0'
+ WHERE upper(trim(kc.pattern_name))=$1 AND upper(trim(kc.color_name))=$2
+ AND kc.mfr_sku IS NOT NULL AND kc.mfr_sku !~* '^NEEDS_MFR'`;
+
+ const ep = await pickOllama();
+ console.log(ep ? `ollama: ${ep.model} @ ${ep.url}` : 'ollama: UNAVAILABLE (tiers 1-3 only)');
+
+ const stats = { 'title-exact': 0, 'price-consensus': 0, 'spec-filter': 0, ollama: 0, unresolved: 0 };
+ const upsert = async (sku, mfr, method, confidence, map, note) => {
+ stats[method] = (stats[method] || 0) + 1;
+ if (DRY) return;
+ await pool.query(`INSERT INTO kravet_mfr_backfill (sku, base_sku, mfr_sku, method, confidence, map_price, note)
+ VALUES ($1,$2,$3,$4,$5,$6,$7)
+ ON CONFLICT (sku) DO UPDATE SET mfr_sku=EXCLUDED.mfr_sku, method=EXCLUDED.method,
+ confidence=EXCLUDED.confidence, map_price=EXCLUDED.map_price, note=EXCLUDED.note, created_at=now()`,
+ [sku, sku.replace(/-Sample$/i, ''), mfr, method, confidence, map, note || null]);
+ };
+
+ for (const g of gaps) {
+ let { rows: cands } = await pool.query(candSql, [g.pat, g.col]);
+ if (!cands.length) { stats.unresolved++; continue; }
+
+ const distinct = [...new Set(cands.map((c) => c.mfr))];
+ const priced = cands.filter((c) => c.new_map != null);
+ const prices = [...new Set(priced.map((c) => String(c.new_map)))];
+
+ if (distinct.length === 1) { await upsert(g.sku, distinct[0], 'title-exact', 'high', priced[0]?.new_map ?? null); continue; }
+ if (prices.length === 1 && priced.length === cands.length) {
+ await upsert(g.sku, priced.map((c) => c.mfr).sort()[0], 'price-consensus', 'high-for-price', priced[0].new_map,
+ `${distinct.length} colorway codes, all MAP $${priced[0].new_map}`); continue;
+ }
+
+ // tier 3 — narrow by the product's own specs
+ const w = parseWidth(g.body_html), mat = parseMaterial(g.body_html);
+ let f = cands;
+ // Each spec filter applies only when it leaves survivors — kravet_catalog width/material
+ // are sparse, and an over-eager filter would empty the set and starve the model tier.
+ if (w) { const t = f.filter((c) => { const cw = parseFloat(c.width); return !cw || Math.abs(cw - w) <= 1; }); if (t.length) f = t; }
+ if (mat) { const tok = mat.split(/[^A-Z]+/).filter((t) => t.length > 3); if (tok.length) { const t = f.filter((c) => { const hay = `${c.material || ''} ${c.composition || ''}`.toUpperCase(); return tok.some((x) => hay.includes(x)); }); if (t.length) f = t; } }
+ const fd = [...new Set(f.map((c) => c.mfr))];
+ const fp = f.filter((c) => c.new_map != null);
+ const fprices = [...new Set(fp.map((c) => String(c.new_map)))];
+ if (fd.length === 1) { await upsert(g.sku, fd[0], 'spec-filter', 'high', fp[0]?.new_map ?? null, `width=${w} mat=${mat}`); continue; }
+ if (fd.length > 1 && fprices.length === 1 && fp.length === f.length) {
+ await upsert(g.sku, fp.map((c) => c.mfr).sort()[0], 'price-consensus', 'high-for-price', fp[0].new_map,
+ `spec-filtered to ${fd.length} codes, all MAP $${fp[0].new_map}`); continue;
+ }
+
+ // tier 4 — local model pick. Only PRICED candidates can answer the price button, so the
+ // model chooses among the priced, spec-filtered set (unpriced siblings are dead ends here).
+ const pf = fp.filter((c, i, a) => a.findIndex((x) => x.mfr === c.mfr) === i);
+ if (pf.length === 1) { await upsert(g.sku, pf[0].mfr, 'spec-filter', 'medium', pf[0].new_map, 'single priced candidate after spec filter'); continue; }
+ if (ep && pf.length >= 2 && pf.length <= 25) {
+ const list = pf
+ .map((c) => `${c.mfr} | width:${c.width || '?'} | material:${(c.material || c.composition || '?').slice(0, 60)} | map:${c.new_map}`).join('\n');
+ const prompt = `Product: "${g.title}". Specs from its page: width ${w || '?'}", material ${mat || '?'}.
+Candidate Kravet mfr codes (code | width | material | MAP price):
+${list}
+Which single candidate code is this product? Reply with ONLY the code, or NONE if you cannot tell.`;
+ try {
+ const ans = (await askOllama(ep, prompt)).replace(/<think>[\s\S]*?<\/think>/g, '').trim().toUpperCase();
+ // NONE = the model honestly can't tell — leave unresolved. Otherwise accept only a
+ // reply that names one of the offered candidate codes (scan the whole reply, models
+ // sometimes wrap the code in a sentence).
+ const hit = !/^NONE\b/.test(ans) && pf.find((c) => ans.includes(c.mfr));
+ if (hit) { await upsert(g.sku, hit.mfr, 'ollama', 'model-pick', hit.new_map, `${ep.model} from ${pf.length} priced candidates`); continue; }
+ } catch (e) { /* fall through to unresolved */ }
+ }
+ stats.unresolved++;
+ }
+
+ console.log('RESULT', JSON.stringify(stats));
+ const { rows: [{ count }] } = await pool.query('SELECT count(*) FROM kravet_mfr_backfill');
+ console.log(`kravet_mfr_backfill rows: ${count}${DRY ? ' (dry run — no writes)' : ''}`);
+ await pool.end();
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
diff --git a/scripts/build-coverage.js b/scripts/build-coverage.js
index 370658a..73ca1a5 100644
--- a/scripts/build-coverage.js
+++ b/scripts/build-coverage.js
@@ -375,16 +375,27 @@ async function main() {
// ACTIVE grid SKUs whose mfr_sku EXISTS in kravet_authoritative_pricing (exact upper() match,
// ±trailing ".0" — mirroring the adapter's cheap format fallback). No product_url involved —
// the adapter answers from the price table itself.
+ // Rows with an empty mirror mfr_sku may still resolve through the curated backfill table
+ // (scripts/backfill-kravet-mfr.js) — COALESCE it in, but only when the table exists
+ // (prod may not have it yet; to_regclass keeps the whole count from erroring to 0/0).
+ let kapHasBackfill = null;
async function kapMatchCount(vendor) {
try {
+ if (kapHasBackfill === null) {
+ const { rows } = await pool.query(`SELECT to_regclass('kravet_mfr_backfill') IS NOT NULL AS ok`);
+ kapHasBackfill = rows[0].ok;
+ }
+ const join = kapHasBackfill
+ ? 'LEFT JOIN kravet_mfr_backfill b ON upper(b.sku)=upper(s.sku)' : '';
+ const mfr = kapHasBackfill ? "coalesce(nullif(s.mfr_sku,''), b.mfr_sku)" : 's.mfr_sku';
const { rows } = await pool.query(
`SELECT count(*) FILTER (WHERE upper(s.status)='ACTIVE')::int AS active,
count(*) FILTER (WHERE upper(s.status)='ACTIVE' AND EXISTS (
SELECT 1 FROM kravet_authoritative_pricing k
- WHERE upper(k.mfr_sku)=upper(s.mfr_sku)
- OR upper(k.mfr_sku)=upper(s.mfr_sku)||'.0'
- OR upper(k.mfr_sku)||'.0'=upper(s.mfr_sku)))::int AS resolvable
- FROM shopify_products s WHERE s.vendor = $1`, [vendor]);
+ WHERE upper(k.mfr_sku)=upper(${mfr})
+ OR upper(k.mfr_sku)=upper(${mfr})||'.0'
+ OR upper(k.mfr_sku)||'.0'=upper(${mfr})))::int AS resolvable
+ FROM shopify_products s ${join} WHERE s.vendor = $1`, [vendor]);
return rows[0];
} catch { return { active: 0, resolvable: 0 }; }
}
diff --git a/server.js b/server.js
index 9521c04..aeb419e 100644
--- a/server.js
+++ b/server.js
@@ -312,6 +312,12 @@ async function loadMfr() {
const { rows } = await pool.query(parts.join(' UNION ALL '));
for (const r of rows) { const k = String(r.dw_sku).toUpperCase(); if (!map.has(k)) map.set(k, r.mfr_sku); }
}
+ // Curated Kravet-family backfill (scripts/backfill-kravet-mfr.js — title/price-consensus/
+ // spec/local-model tiers) OVERRIDES the generic catalog map for its SKUs. Table optional.
+ try {
+ const { rows } = await pool.query('SELECT base_sku, mfr_sku FROM kravet_mfr_backfill');
+ for (const r of rows) map.set(String(r.base_sku).toUpperCase(), r.mfr_sku);
+ } catch { /* backfill table not present — fine */ }
MFR = map;
}
← c5afd0d contrarian fixes: price-only live result gets neutral dot +
·
back to All Designerwallcoverings
·
Backfill mfr_sku for 163 mfr-less Kravet-family SKUs at $0 ( 8a79e7b →