material classifier: derive custom.material_category from tags/title (45,284 active, 22 materials)

6d0d87cfcbe53f7ff04e8fed9ca5200a6abe8561 · 2026-07-16 12:02:58 -0700 · Steve
Files touched

A .gitignore
A classify.mjs
Diff

commit 6d0d87cfcbe53f7ff04e8fed9ca5200a6abe8561
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Jul 16 12:02:58 2026 -0700

    material classifier: derive custom.material_category from tags/title (45,284 active, 22 materials)
---
 .gitignore   |  7 +++++
 classify.mjs | 92 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 99 insertions(+)

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..3f324ba
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,7 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+data/*.tsv
+data/*.jsonl
diff --git a/classify.mjs b/classify.mjs
new file mode 100644
index 0000000..335ab8e
--- /dev/null
+++ b/classify.mjs
@@ -0,0 +1,92 @@
+#!/usr/bin/env node
+// Derive a normalized `custom.material` for every ACTIVE DW product from the
+// signals we already have (structured "Material:" tag → keyword scan over
+// tags+title). Priority order matters: decorative/specialty surfaces and
+// specific natural fibers rank ABOVE generic "Vinyl" (which is almost always
+// the substrate, not the look). Read-only: emits a distribution + a write-plan
+// JSONL (shopify_id → material). $0 (local, rules-based — no AI).
+import { execFileSync } from 'node:child_process';
+import { writeFileSync } from 'node:fs';
+
+// pull active products from the local mirror as TSV (id, type, title, tags, existing)
+const SQL = `
+copy (
+  select shopify_id,
+         translate(coalesce(product_type,''), E'\\t\\n\\r', '   '),
+         translate(coalesce(title,''),        E'\\t\\n\\r', '   '),
+         translate(coalesce(tags,''),         E'\\t\\n\\r', '   '),
+         translate(coalesce(metafields->'custom'->'material_category'->>'value',''), E'\\t\\n\\r', '   ')
+  from shopify_products
+  where lower(status)='active' and shopify_id is not null
+) to stdout`;   // default TEXT format: tab-delimited, empty string stays empty (no "" quoting)
+const raw = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tA', '-c', SQL], { maxBuffer: 1 << 30 }).toString();
+
+// canonical taxonomy, HIGHEST priority first. Each: [label, /regex/].
+// Vinyl is LAST — only if nothing else matched (it's the backing, not the face).
+const RULES = [
+  ['Glass Bead',  /glass[\s-]*bead|beaded|glass bead/],
+  ['Mica',        /\bmica\b/],
+  ['Cork',        /\bcork\b/],
+  ['Grasscloth',  /grass[\s-]*cloth/],
+  ['Sisal',       /\bsisal\b/],
+  ['Seagrass',    /sea[\s-]*grass/],
+  ['Jute',        /\bjute\b/],
+  ['Hemp',        /\bhemp\b/],
+  ['Raffia',      /\braffia\b/],
+  ['Paperweave',  /paper[\s-]*weave|arrowroot/],
+  ['Leather',     /\bleather\b/],
+  ['Suede',       /\bsuede\b|ultrasuede/],
+  ['Silk',        /\bsilk\b/],
+  ['Linen',       /\blinen\b/],
+  ['Wool',        /\bwool\b|\bfelt\b/],
+  ['Cotton',      /\bcotton\b/],
+  ['Flock',       /\bflock(ed)?\b/],
+  ['Velvet',      /\bvelvet\b/],
+  ['Mylar',       /\bmylar\b/],
+  ['Metallic',    /\bmetallic\b|\bfoil\b|gold leaf|silver leaf|\bgilt\b/],
+  ['Wood',        /\bwood\b|\bveneer\b|\bbamboo\b/],
+  ['Vinyl',       /\bvinyl\b/],
+];
+// explicit "Material: X" structured tag → canonical (highest confidence)
+const STRUCT = { linen:'Linen', cotton:'Cotton', silk:'Silk', velvet:'Velvet',
+  metallic:'Metallic', wool:'Wool', leather:'Leather', suede:'Suede', cork:'Cork',
+  grasscloth:'Grasscloth', vinyl:'Vinyl' };
+
+function classify(type, title, tags, existing) {
+  const ex = (existing || '').replace(/^"+|"+$/g, '').trim();   // strip any stray CSV quoting
+  if (ex) return { m: ex, src: 'existing' };
+  const hay = (title + ' ' + tags).toLowerCase();
+  // 1) structured Material: tag
+  const st = tags.match(/Material:\s*([A-Za-z][A-Za-z \-]*)/i);
+  if (st) { const k = st[1].trim().toLowerCase().split(/[\s-]/)[0]; if (STRUCT[k]) return { m: STRUCT[k], src: 'struct-tag' }; }
+  // 2) priority keyword scan
+  for (const [label, re] of RULES) if (re.test(hay)) return { m: label, src: 'keyword' };
+  return null; // unclassified
+}
+
+const dist = {}, bySrc = {}, plan = [], examples = {};
+let total = 0, classified = 0;
+for (const line of raw.split('\n')) {
+  if (!line) continue;
+  total++;
+  const c = line.split('\t');
+  const [sid, type, title, tags, existing] = [c[0]||'', c[1]||'', c[2]||'', c[3]||'', c[4]||''];
+  const r = classify(type, title, tags, existing);
+  if (!r) continue;
+  classified++;
+  dist[r.m] = (dist[r.m] || 0) + 1;
+  bySrc[r.src] = (bySrc[r.src] || 0) + 1;
+  (examples[r.m] = examples[r.m] || []).length < 3 && examples[r.m].push(`${title.slice(0,52)}  [${r.src}]`);
+  if (r.src !== 'existing') plan.push({ shopify_id: sid, material: r.m }); // material_category value to write
+}
+
+writeFileSync('data/material-plan.jsonl', plan.map(p => JSON.stringify(p)).join('\n') + '\n');
+console.log(`active total          : ${total}`);
+console.log(`classified            : ${classified} (${(classified/total*100).toFixed(1)}%)`);
+console.log(`unclassified (no write): ${total - classified}`);
+console.log(`to WRITE material_category: ${plan.length}  (already-set kept: ${bySrc.existing||0})`);
+console.log('by source             :', bySrc);
+console.log('\nmaterial_category distribution + sample titles:');
+for (const [k, v] of Object.entries(dist).sort((a, b) => b[1] - a[1])) {
+  console.log(`  ${k.padEnd(12)} ${String(v).padStart(6)}   e.g. ${(examples[k]||[]).slice(0,2).join('  |  ')}`);
+}
(oldest) · back to Dw Material Filter · material writer: metafieldsSet to custom.material_category ( 33c1a1f →