← back to All Designerwallcoverings
scripts/backfill-kravet-mfr.js
174 lines
#!/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); });