← back to Dw Pairs Well
tools/pattern-id-backfill.js
347 lines
#!/usr/bin/env node
/**
* pattern-id-backfill.js — Option-A (Hybrid + image-confirm) pattern_id computer.
*
* Computes the final `custom.pattern_id` per ACTIVE product per the DTD-committed
* Option-A pipeline:
* 1. mfr_sku ROOT (series-aware) is the PRIMARY grouping key when available.
* The authoritative mfr_sku source is the Shopify metafield
* `custom.manufacturer_sku`, which is NOT in the local mirror — so this
* script accepts a mfr_sku MAP as an INPUT FILE (produced by the GATED
* Shopify Admin pull). Default = empty map → proves the T2 fallback path.
* 2. A color-invariant IMAGE-CONFIRM hash is the GUARD that splits over-grouped
* keys. In DRY-RUN we do NOT fetch 74k images; we FLAG which products/groups
* would need the image-confirm pass (non-texture multi-colorway members).
* 3. TEXTURE-AWARE EXCEPTION: products tagged with organic textures
* (grasscloth/linen/raffia/cork/…) TRUST the mfr_sku root and SKIP the image
* gate (the hash false-splits random fibers). These are flagged texture=true.
* 4. T2 normalized-title (strip vendor suffix + product-type noise + color words)
* is the FALLBACK key ONLY where no mfr_sku root exists.
*
* DRY-RUN ONLY. Reads the read-only mirror + an optional mfr map; WRITES a report
* (json + console). It NEVER calls the Shopify API and NEVER writes a metafield.
*
* Usage:
* node tools/pattern-id-backfill.js # empty map → pure T2 fallback
* node tools/pattern-id-backfill.js --mfr-map FILE.json # {key: mfrSku} input from gated pull
* node tools/pattern-id-backfill.js --use-mirror-mfr # read-only: use mirror.mfr_sku column as interim source
* node tools/pattern-id-backfill.js --apply # GATED NO-OP stub: prints + exits, writes nothing
*
* mfr-map FILE format (either keying works; dw_sku preferred, shopify_id accepted):
* { "DWWC12345": "TAK-AA04-01", "gid://shopify/Product/123": "FRL5226/01", ... }
*/
require('dotenv').config();
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const { COLORS } = require('../lib/classify');
const ARGV = process.argv.slice(2);
const APPLY = ARGV.includes('--apply');
const USE_MIRROR_MFR = ARGV.includes('--use-mirror-mfr');
const mfrMapIdx = ARGV.indexOf('--mfr-map');
const MFR_MAP_FILE = mfrMapIdx !== -1 ? ARGV[mfrMapIdx + 1] : null;
// ---- HARD GATE: --apply is a no-op stub. The 74k metafieldsSet WRITE is Steve-gated. ----
if (APPLY) {
console.log('────────────────────────────────────────────────────────');
console.log(' --apply is GATED — requires Steve sign-off.');
console.log(' Writing custom.pattern_id to ~74k Shopify products is a');
console.log(' bulk customer-facing catalog change + Kamatera-canonical');
console.log(' dw_unified write. This stub writes NOTHING and exits.');
console.log(' See the pending-approval memo for the gated command.');
console.log('────────────────────────────────────────────────────────');
process.exit(0);
}
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
// ---------- text normalization (shared shape with pattern-grouping-dryrun.js) ----------
const NOISE = [
'wallcovering','wallcoverings','wallpaper','wall covering','fabric','fabrics',
'durable vinyl','vinyl','grasscloth','print','commercial','residential',
'type ii','type iii','mural','wall mural','peel and stick','peel-and-stick',
'self adhesive','self-adhesive','by phillipe romano','phillipe romano'
];
const COLOR_WORDS = new Set([...COLORS,
'light','dark','deep','pale','soft','warm','cool','muted','bright','antique',
'metallic','natural','neutral','multi','multicolor','multicolour'
]);
// organic-fiber materials whose RANDOM fiber structure false-splits the image hash.
// DTD named grasscloth/linen/raffia/cork; close organic-fiber cousins included.
// (silk/suede/flocked deliberately EXCLUDED — those tile uniformly, hash is reliable.)
const TEXTURE_MATERIALS = [
'grasscloth','grass cloth','linen','raffia','cork','sisal','jute','abaca',
'hemp','seagrass','sea grass','paperweave','paper weave','bamboo','natural fiber'
];
function stripVendorSuffix(title) {
const i = (title || '').indexOf('|');
return (i === -1 ? (title || '') : title.slice(0, i)).trim();
}
function normTitle(title) {
let s = stripVendorSuffix(title).toLowerCase();
for (const n of NOISE) s = s.split(n).join(' ');
s = s.replace(/[^a-z0-9 ]+/g, ' ').replace(/\s+/g, ' ').trim();
return s;
}
function stripColors(normalized) {
const kept = normalized.split(' ').filter(w => w && !COLOR_WORDS.has(w));
return kept.join(' ').trim() || normalized;
}
// ---------- series-aware mfr_sku ROOT ----------
// Drop a TRAILING colorway suffix while preserving the pattern/series root:
// TAK-AA04-01 -> TAK-AA04 (drop final numeric token, keep alphanumeric series)
// FRL5226/01 -> FRL5226
// W7008-01 -> W7008
// AT9604 -> AT9604 (no colorway → unchanged)
// 5004561 -> 5004561 (single pure-numeric token → keep, never empty out)
// Junk roots: ONLY booleans / placeholders that are NEVER a real brand or pattern code.
// (Do NOT list real brands like Innovations or Sancar here — a brand name wrongly stored
// in the manufacturer_sku field is handled by the >60-member + image-confirm guard, which
// visually splits the over-merge instead of mislabeling a legit vendor. DTD verdict A;
// corrected 2026-06-26 after 'INNOVATIONS' was wrongly denied.)
const DENY_ROOTS = new Set([
'TRUE','FALSE','NULL','NONE','NA','N/A','UNKNOWN','TBD','TBA','TEST',
'SAMPLE','DEFAULT','PENDING','VARIOUS','ASSORTED','YES','NO'
]);
function mfrRoot(raw) {
if (!raw) return null;
let s = String(raw).trim().toUpperCase();
if (!s) return null;
// split on the common SKU separators
const tokens = s.split(/[-/_.\s]+/).filter(Boolean);
let root;
if (tokens.length >= 2) {
const last = tokens[tokens.length - 1];
// colorway suffix = 1-3 digits, optionally one trailing letter (01, 001, 01A)
if (/^\d{1,3}[A-Z]?$/.test(last)) {
root = tokens.slice(0, -1).join('-');
}
}
// no separable colorway → root is the whole thing (separators normalized to '-')
if (!root) root = tokens.join('-');
// junk-root guard: supplier names / booleans / placeholders → not a real pattern key
if (DENY_ROOTS.has(root)) return null;
return root;
}
function isTexture(tagsLower, titleLower) {
const hay = (tagsLower || '') + ' ' + (titleLower || '');
return TEXTURE_MATERIALS.some(m => hay.includes(m));
}
function vendorSlug(v) {
return (v || '∅').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '') || 'no-vendor';
}
// ---------- load mfr map (from gated Shopify pull) ----------
function loadMfrMap() {
if (!MFR_MAP_FILE) return {};
if (!fs.existsSync(MFR_MAP_FILE)) {
console.error(`mfr-map file not found: ${MFR_MAP_FILE} — proceeding with EMPTY map (pure fallback).`);
return {};
}
try {
const obj = JSON.parse(fs.readFileSync(MFR_MAP_FILE, 'utf8'));
// accept {key: mfr} or {key: {manufacturer_sku: mfr}}
const out = {};
for (const [k, v] of Object.entries(obj)) {
out[k] = typeof v === 'string' ? v : (v && (v.manufacturer_sku || v.mfr_sku || v.value)) || null;
}
return out;
} catch (e) {
console.error('Failed to parse mfr-map; using EMPTY map:', e.message);
return {};
}
}
(async () => {
const mfrMap = loadMfrMap();
const mfrMapSize = Object.keys(mfrMap).length;
console.log('Loading ACTIVE products from mirror (read-only)…');
const r = await pool.query(`
SELECT dw_sku, shopify_id, title, vendor, pattern_name, tags, image_url
${USE_MIRROR_MFR ? ', mfr_sku' : ''}
FROM shopify_products
WHERE status = 'ACTIVE' AND title IS NOT NULL AND handle IS NOT NULL
`);
const rows = r.rows;
console.log('Active products:', rows.length);
console.log('mfr-map entries:', mfrMapSize, USE_MIRROR_MFR ? '(+ mirror.mfr_sku column as interim source)' : '');
// STRUCTURAL JUNK-ROOT GUARD (replaces the brand denylist): a mfr_root that is a
// single PURE-ALPHA token (no digits) shared across MANY DISTINCT TITLES is a brand/
// label stuffed into the SKU field (SANCAR/INNOVATIONS), NOT a pattern code. Real
// pattern codes carry digits (5538, DY24, TAK-AA04); real colorways of one pattern
// share a base title. So: pure-alpha root + >=8 distinct base titles → demote to title.
const JUNK_TITLE_THRESHOLD = 8;
const rootTitles = new Map(); // root -> Set(distinct base titles)
for (const row of rows) {
let mfr = mfrMap[row.dw_sku] || mfrMap[row.shopify_id] || null;
if (!mfr && USE_MIRROR_MFR) mfr = row.mfr_sku || null;
const root = mfrRoot(mfr);
if (!root || /[0-9]/.test(root)) continue; // only pure-alpha roots are suspects
const base = stripColors(normTitle(row.title)) || (row.pattern_name || '').toLowerCase();
if (!rootTitles.has(root)) rootTitles.set(root, new Set());
rootTitles.get(root).add(base);
}
const junkRoots = new Set();
for (const [root, titles] of rootTitles) if (titles.size >= JUNK_TITLE_THRESHOLD) junkRoots.add(root);
if (junkRoots.size) console.log('structural junk-roots demoted to title:', [...junkRoots].join(', '));
const groups = new Map(); // pattern_id -> {pattern_id, vendor, source, key, members:[], texture, samples}
const perProduct = {}; // dw_sku -> {pid, sid} (emitted for the gated write)
const stats = {
via_mfr_root: 0, via_t2_fallback: 0, via_pattern_name_last_resort: 0,
texture_flagged: 0, no_image: 0
};
for (const row of rows) {
const titleLower = (row.title || '').toLowerCase();
const tagsLower = (row.tags || '').toLowerCase();
const vslug = vendorSlug(row.vendor);
const texture = isTexture(tagsLower, titleLower);
// resolve authoritative mfr sku: input map (preferred) → mirror column (if flag) → none
let mfr = mfrMap[row.dw_sku] || mfrMap[row.shopify_id] || null;
if (!mfr && USE_MIRROR_MFR) mfr = row.mfr_sku || null;
let source, key, patternId;
let root = mfrRoot(mfr);
if (root && junkRoots.has(root)) root = null; // structural guard: brand/label in SKU field → demote to title
if (root) {
source = 'mfr_root'; key = root;
patternId = `${vslug}::mfr::${root}`;
stats.via_mfr_root++;
} else {
// T2 fallback: normalized title minus color words
const t2 = stripColors(normTitle(row.title));
if (t2) {
source = 't2_title'; key = t2;
patternId = `${vslug}::t2::${t2}`;
stats.via_t2_fallback++;
} else {
// last resort: pattern_name (so a product never goes key-less)
const pn = (row.pattern_name || '').toLowerCase().trim();
source = 'pattern_name'; key = pn || 'ungrouped';
patternId = `${vslug}::pn::${key}`;
stats.via_pattern_name_last_resort++;
}
}
if (texture) stats.texture_flagged++;
if (!row.image_url) stats.no_image++;
perProduct[row.dw_sku] = { pid: patternId, sid: row.shopify_id };
let g = groups.get(patternId);
if (!g) {
g = { pattern_id: patternId, vendor: row.vendor, source, key, texture, members: 0, samples: [] };
groups.set(patternId, g);
}
g.members++;
// a group is texture if ANY member is texture-tagged (trust mfr root, relax image gate)
if (texture) g.texture = true;
if (g.samples.length < 4) g.samples.push({ dw_sku: row.dw_sku, title: row.title });
}
const arr = [...groups.values()];
fs.writeFileSync(path.join(__dirname, 'pattern-id-by-dwsku.json'), JSON.stringify(perProduct));
console.log('emitted per-product map →', Object.keys(perProduct).length, 'products → tools/pattern-id-by-dwsku.json');
const multi = arr.filter(g => g.members >= 2);
const productsInMulti = multi.reduce((a, g) => a + g.members, 0);
// Image-confirm bucketing — faithful to DTD exception #3, which relaxes the gate
// ONLY when we "TRUST the mfr_sku ROOT". So:
// skip_gate = texture AND key came from mfr_root → trust root, no confirm
// needs_vision = texture AND key came from T2/pattern_name → fiber-safe LOCAL
// Ollama-vision pass (dHash false-splits random fibers; a
// T2 texture over-merge like generic-title Coordonné is NOT
// safe to auto-trust)
// needs_dhash = non-texture multi-group → standard color-invariant dHash guard
// SAFETY: a >HUGE_CAP single-root group is almost certainly a junk/over-merged
// root (proven: mirror mfr_sku has "SANCAR"/"TRUE" pollution → 178/81-member
// false roots). Such groups ALWAYS get confirmed even if texture+mfr_root.
const HUGE_CAP = 60;
const skipGateGroups = multi.filter(g => g.texture && g.source === 'mfr_root' && g.members <= HUGE_CAP);
const needsVisionGroups = multi.filter(g => g.texture && !(g.source === 'mfr_root' && g.members <= HUGE_CAP));
const dhashGroups = multi.filter(g => !g.texture);
const imageConfirmGroups = dhashGroups; // legacy alias for the dHash bucket
const imageConfirmProducts = dhashGroups.reduce((a, g) => a + g.members, 0);
const visionProducts = needsVisionGroups.reduce((a, g) => a + g.members, 0);
const textureMultiGroups = multi.filter(g => g.texture);
arr.sort((a, b) => b.members - a.members);
const report = {
generated_at: new Date().toISOString(),
mode: 'DRY-RUN (read-only; no Shopify call, no metafield write)',
active_products: rows.length,
mfr_map_entries: mfrMapSize,
used_mirror_mfr_column: USE_MIRROR_MFR,
counts: {
distinct_pattern_ids: arr.length,
multi_colorway_groups: multi.length,
products_in_multi_groups: productsInMulti,
grid_rows_collapsed: productsInMulti - multi.length,
pct_catalog_collapsed: ((productsInMulti - multi.length) / rows.length * 100).toFixed(1) + '%',
avg_group_size: (rows.length / arr.length).toFixed(2),
huge_groups_gt60: arr.filter(g => g.members > 60).length,
},
key_source_breakdown: stats,
texture_aware: {
texture_flagged_products: stats.texture_flagged,
texture_multi_colorway_groups: textureMultiGroups.length,
skip_gate_groups_mfr_root: skipGateGroups.length,
note: 'texture exception relaxes the image gate ONLY for mfr_root-sourced groups (trust the root). texture T2 over-merges are routed to the local-vision bucket, NOT auto-trusted.',
},
image_confirm: {
dhash_groups: dhashGroups.length,
dhash_products: imageConfirmProducts,
local_vision_groups: needsVisionGroups.length,
local_vision_products: visionProducts,
note: 'dHash bucket = non-texture multi-groups (pattern-image-verify.py, $0 local). local_vision bucket = texture multi-groups w/o a trusted mfr root (Ollama qwen2.5vl, $0). NEITHER run in dry-run.',
},
top_groups: arr.slice(0, 25).map(g => ({
n: g.members, vendor: g.vendor, source: g.source, texture: g.texture,
pattern_id: g.pattern_id, eg: g.samples.map(s => s.title),
})),
huge_groups_for_review: arr.filter(g => g.members > 60).slice(0, 20).map(g => ({
n: g.members, vendor: g.vendor, source: g.source, texture: g.texture, pattern_id: g.pattern_id,
eg: g.samples.map(s => s.title),
})),
};
const file = path.join(__dirname, 'pattern-id-backfill-report.json');
fs.writeFileSync(file, JSON.stringify(report, null, 2));
// ---- console digest ----
console.log('\n==================== PATTERN-ID BACKFILL · DRY-RUN ====================');
console.log('active products :', rows.length);
console.log('distinct pattern_ids :', report.counts.distinct_pattern_ids);
console.log('multi-colorway groups :', report.counts.multi_colorway_groups);
console.log('grid rows collapsed :', report.counts.grid_rows_collapsed,
'(' + report.counts.pct_catalog_collapsed + ' of catalog)');
console.log('avg group size :', report.counts.avg_group_size);
console.log('huge groups (>60, inspect) :', report.counts.huge_groups_gt60);
console.log('--- key source ---');
console.log(' via mfr_root (primary) :', stats.via_mfr_root);
console.log(' via T2 title (fallback) :', stats.via_t2_fallback);
console.log(' via pattern_name (last) :', stats.via_pattern_name_last_resort);
console.log('--- texture-aware exception ---');
console.log(' texture-flagged products :', stats.texture_flagged);
console.log(' texture multi-groups :', textureMultiGroups.length);
console.log(' skip-gate (mfr_root only) :', skipGateGroups.length, 'groups');
console.log('--- image-confirm guard (NOT run in dry-run) ---');
console.log(' dHash groups (non-texture) :', dhashGroups.length, '·', imageConfirmProducts, 'products');
console.log(' local-vision (texture T2) :', needsVisionGroups.length, '·', visionProducts, 'products');
console.log(' products missing image :', stats.no_image);
console.log('--- top 5 largest groups ---');
report.top_groups.slice(0, 5).forEach(g =>
console.log(` ${g.n}× [${g.vendor}] ${g.source}${g.texture ? ' (texture)' : ''} ${g.pattern_id}`));
console.log('\nFull report →', file);
await pool.end();
})().catch(e => { console.error(e); process.exit(1); });