← back to All Designerwallcoverings
scripts/build-coverage.js
469 lines
#!/usr/bin/env node
// build-coverage.js — the LIVE-STOCK COVERAGE AUDIT generator for all.designerwallcoverings.com.
//
// Classifies EVERY display-vendor in the grid into a feasibility tier and writes the
// committed source-of-truth at data/live-stock-coverage.json. The server reads that file
// to drive each card's "Check Live" enable/dim (data-driven, not hardcoded per vendor),
// and the live worker reads the per-vendor `adapter` + `catalog_table` to route a scrape.
//
// KRAVET LIGHT-UP RECOVERY (prod): the DB_AUTHORITATIVE tier needs the app role `repl_user`
// to have SELECT on kravet_authoritative_pricing (owned by dw_admin). Without it, kapMatchCount
// catches the permission error → 0 resolvable → Kravet dims to Email-Vendor (graceful, honest).
// To light Kravet on prod after the grant lands, THREE steps in order (hot-reload alone is NOT
// enough — it only re-READS this file, it does not regenerate it):
// 1. GRANT: ssh root@45.61.58.125 "sudo -u postgres psql -d dw_unified -c 'GRANT SELECT ON kravet_authoritative_pricing TO repl_user'"
// 2. REGEN: ssh root@45.61.58.125 'cd /root/Projects/all-designerwallcoverings && node scripts/build-coverage.js'
// 3. WAIT: the server's mtime hot-reload picks up the new file within ≤10 min (or pm2 reload).
//
// Tiers (Steve's audit spec):
// PORTAL_CREDS — backing vendor has a login portal AND trade creds on file → live-capable (metered).
// PUBLIC_STOCK — backing storefront exposes stock with no login (Shopify .js / Woo Store API) → live-capable (metered headless).
// PORTAL_NO_CREDS — a portal exists but no creds → NOT live yet (needs creds); dims with that reason.
// QUOTE_ONLY — to-the-trade / quote-only line that publishes no stock anywhere → NOT feasible; always dims.
// NO_STOCK — no live stock source identified (private-label aggregate, one-off, internal) → dims.
//
// $0 (local) — pure Postgres reads, no paid API.
//
// node scripts/build-coverage.js # write data/live-stock-coverage.json
// node scripts/build-coverage.js --dry # print summary, don't write
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
try { require('dotenv').config({ path: path.join(__dirname, '..', '.env') }); } catch {}
const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
const OUT = path.join(__dirname, '..', 'data', 'live-stock-coverage.json');
const DRY = process.argv.includes('--dry');
// Mirror server.js's public-facing exclusion — banned upstream/private-label source names
// never surface on the public grid, so they are not display-vendors we classify.
const BANNED = /brewster|york|wallquest|wall\s*quest|newwall|new\s*wall\b|command\s*54|justin\s*david|nicolette\s*mayer|seabrook|chesapeake|nextwall|desima|carlsten/i;
const norm = (s) => String(s || '').trim().toLowerCase().replace(/[.\s]+$/, '');
// suffixes that distinguish a display-vendor variant from its registry parent
const SUFFIX = /\s+(fabrics?|wallpapers?|wallcoverings?|walls?|trim|pillows?|murals?|at dw|europe|usa|uk|to go|home|studios?)$/i;
const stripSuffix = (s) => { let p = String(s || ''); for (let i = 0; i < 3; i++) { const n = p.replace(SUFFIX, ''); if (n === p) break; p = n; } return p.trim(); };
// Platform is detected from the catalog's PRODUCT_URL pattern, NOT vendor_registry.website_tech_stack
// (that field is ~70% blank/wrong — hygge is tagged "unreachable" yet is a live public Shopify store).
// CANONICAL Shopify storefront product URL = host/products/<single-handle> (exactly one segment after
// /products/, so it exposes a public <url>.js stock feed). Thibaut's /products/wallcoverings/<code> has
// TWO segments → login-gated, correctly excluded. Our own storefront is excluded (circular).
const CANONICAL_SHOPIFY = /^https?:\/\/([^/]+)\/products\/[^/?#]+\/?(?:[?#].*)?$/i;
const isCanonicalShopify = (url) => { const m = CANONICAL_SHOPIFY.exec(String(url || '')); return !!m && !/designerwallcoverings\.com/i.test(m[1]); };
// WooCommerce single-product URL = host/product/<slug> (singular) → /wp-json/wc/store/products?slug=
const WOO_URL = /^https?:\/\/[^/]+\/product\/[^/?#]+/i;
// display-vendor labels that are WallQuest-backed but don't resolve through vendor_registry
// (the private-label names the grid shows; SKUs live in wallquest_catalog).
const WALLQUEST_ALIASES = new Set(['malibu wallpaper', 'ps removable wallpaper', 'malibu walls', 'malibu']);
// Known to-the-trade / quote-only houses that publish NO stock anywhere (memory + DW knowledge).
// A matched vendor here is QUOTE_ONLY regardless of a marketing site existing.
// Live-verification status from real metered scrapes (see the coverage report). Enabled vendors not
// listed here are wired but not yet proven with a live scrape (honest — the framework routes them).
const VERIFIED = {
'1838 Wallcoverings': 'live-verified 2026-07-07 (shopify-public → in_stock:true, $0.03)',
'Sancar': 'live-verified 2026-07-07 (shopify-public → in_stock:true, $0.03)',
'Malibu Wallpaper': 'live-verified 2026-07-07 (wallquest → OutOfStock, $0.10)',
'Romo': 'reaches live portal 2026-07-07 ($0.08) — stock-parse selectors pending tuning',
'Mind the Gap': 'live-verified 2026-07-07 (schema-public → in_stock:true, $0.03)',
'Graham & Brown': 'live-verified 2026-07-07 (schema-public → in_stock:true, $0.03)',
'Innovations USA': 'live-verified 2026-07-07 (schema-public → in_stock:true, $0.03)',
'Rebel Walls': 'live-verified 2026-07-07 (schema-public → in_stock:true, $0.03)',
'Designers Guild': 'live-verified 2026-07-07 (schema-public → in_stock:true via structured availability vote, $0.03)',
'Marburg': 'live-verified 2026-07-07 (schema-public → in_stock:true via WooCommerce stock class, $0.03)',
'Osborne & Little': 'live-verified 2026-07-07 (schema-public → in_stock:true via enabled add-to-cart; SKU remap by leading mfr code, $0.03)',
'Koroseal': 'live-verified 2026-07-07 (koroseal liveness — DWK-29052 Available via live 200 PDP / DWK-32619 Discontinued via 302→/NotFound; mfr-code URL reconstruction; price:null; $0.03 each)',
'Designtex': 'live-verified 2026-07-08 (schema-public → in_stock:true, $0.03; enabled after the Steve-approved dw_sku backfill of 417 active rows — base(variant_sku)=designtex_catalog.dw_sku — into the Kamatera-local dw_unified the aggregator loads; resolvability 417/417 = 100%)',
};
// PUBLIC-SCHEMA vendors — to-the-trade / Magento / custom storefronts whose ANONYMOUS product
// pages emit schema.org Offer availability (or visible Magento stock markup), so live stock is
// readable WITHOUT a trade login. Probed 2026-07-07 (free HTTP HEAD/GET, no session). These were
// mis-tiered as PORTAL_NO_CREDS purely because a login_url exists — but a login_url gates checkout,
// not the public availability signal. Routed to the login-free `schema-public` adapter; still
// subject to the resolvability gate (display SKUs must align with the catalog product_urls).
const SCHEMA_PUBLIC = new Set([
'mind the gap', 'osborne & little', 'osborne & little fabrics', 'designers guild',
'innovations', 'innovations usa', 'marburg', 'designtex', 'graham & brown',
'as creation', 'contrado', 'rebel walls',
]);
// schema-public vendors WIRED but kept DIM with an HONEST, SPECIFIC reason. Marburg (WooCommerce
// instock class) and Designers Guild (escaped-JSON itemAvailability) were tuned into the adapter's
// step-2.5 vote 2026-07-07 and are now live, so they're OFF this list. What remains is a vendor whose
// anonymous PDP genuinely publishes NO stock — kept dim rather than faking in_stock.
// AS Creation: products.as-creation.com is a pattern-CATALOG/where-to-buy reference, not a store —
// the schema.org Offer block is empty, there is no cart and no availability markup (sold via
// retailers). No public stock signal exists to read, so a live check would only ever return unknown.
const SCHEMA_PARSE_PENDING = new Map([
['as creation', 'AS Creation’s public site is a pattern catalog / where-to-buy reference (empty schema.org Offer, no cart, no stock markup) — it publishes no live stock; sold through retailers'],
// Designtex: RESOLVED 2026-07-08. It DOES publish login-free schema.org/Magento availability (the
// schema-public adapter reads it fine); the only blocker was that every grid Designtex product carried
// an EMPTY dw_sku/mfr_sku, so no SKU key existed to route a live check to the right PDP. The Steve-
// approved backfill wrote the authoritative dw_sku (= base(variant_sku) = designtex_catalog.dw_sku)
// into all 417 active rows in shopify_products, so it now clears the resolvability gate on the normal
// schema-public path. No longer PENDING — dropped from this map so it flips live_capable.
]);
// Portals that require a login AND publish only PRICE (a wholesale price sheet / SFTP feed), never
// live inventory — enabling a metered scrape would burn a session to read a price we can't surface.
// Honest dim: creds may exist, but the portal has no live-stock signal to read.
// (Kravet moved OUT of here 2026-07-15 — its price sheet is now the AUTHORITATIVE signal, served
// $0/instant by the kravet-db adapter via the KRAVET_FAMILY / DB_AUTHORITATIVE branch below.)
const NO_LIVE_STOCK_PORTAL = new Set([]);
// KRAVET FAMILY → DB_AUTHORITATIVE. Kravet emails DW an authoritative price-adjustment file
// (kravet-price-watch skill → dw_unified.kravet_authoritative_pricing: mfr_sku → new_whls/new_map),
// so every Kravet-umbrella line gets a $0, instant, session-free live check that returns the
// authoritative MAP price (standing rule: MAP = wholesale × 1.5). Keys are norm()'d display-vendor
// names as they appear in dw_unified. NOT donghia — 0 matches in kravet_authoritative_pricing;
// it stays QUOTE_ONLY. 'g p & j baker' is the alternate spacing dw_unified also carries.
const KRAVET_FAMILY = new Set([
'kravet', 'kravet couture', 'kravet design', 'lee jofa', 'lee jofa modern',
'brunschwig & fils', 'gp & j baker', 'g p & j baker', 'baker lifestyle',
'gaston y daniela', 'cole & son', 'threads',
]);
// Login-gated vendors whose trade creds live in the dw-price-stock credential vault (referenced IN
// PLACE at runtime by scripts/adapters/creds.js — NEVER copied here or into dw_unified) AND whose
// portal exposes LIVE STOCK via a wired login adapter. Enabling here only asserts "an adapter + vault
// creds exist"; the resolvability gate (≥5 & ≥20%) still governs. Placed BEFORE the public-Shopify
// branch in classify() because a vault-portal vendor's stock is trade-gated, not on the anon store.
// Expansion beyond Thibaut is Steve-gated — see docs/live-stock-creds-in-place.md.
const VAULT_PORTAL_ADAPTERS = new Map([
['thibaut', { adapter: 'thibaut', catalog_table: 'thibaut_catalog',
reason: 'Thibaut trade portal — creds referenced in place from the dw-price-stock vault (live stock behind trade login)' }],
]);
// LIVENESS adapters — login-free vendors whose ONLY honest "live" signal is product liveness
// (does the vendor PDP still resolve → Available, vs 301/404/redirect-to-category → Discontinued),
// NOT a stock count or a price. Koroseal (koroseal.com) is a commercial spec/sample catalog with no
// cart, no public price, and no inventory field — so it reports Available/Discontinued and NEVER a
// price. It resolves each grid SKU to its PDP by RECONSTRUCTING the URL from the mfr code (the
// koroseal.js collection-prefix + live thumb match), so its resolvability is governed by mfr-key
// presence (2351/2351 active carry a real mfr code), not by koroseal_catalog.product_url (which is
// sparse — only the digital-lab line). Routed BEFORE the login_url branch so it isn't mis-dimmed as
// PORTAL_NO_CREDS. Steve-approved 2026-07-07.
const LIVENESS_ADAPTERS = new Map([
['koroseal', { adapter: 'koroseal', catalog_table: 'koroseal_catalog', resolves_by: 'mfr',
reason: 'Available / Discontinued (product liveness — spec/sample line, no inventory count)' }],
]);
// WIRED-but-DIM trade/quote lines whose adapters are registered (scripts/adapters/) but which expose
// NO honest live-stock signal, so coverage keeps them dim with a specific, accurate reason rather than
// burning a metered session to read nothing. (Schumacher/Fabricut/Contrado authoring pass 2026-07-07.)
const WIRED_DIM = new Map([
['schumacher', { tier: 'QUOTE_ONLY', adapter: 'schumacher',
reason: 'quote-only trade line — portal exposes no live stock signal' }],
['fabricut', { tier: 'PORTAL_NO_CREDS', adapter: 'fabricut',
reason: 'price-oriented trade portal + catalog too small (needs ≥5 aligned SKUs)' }],
['fabricut stroheim', { tier: 'PORTAL_NO_CREDS', adapter: 'fabricut',
reason: 'price-oriented trade portal + catalog too small (needs ≥5 aligned SKUs)' }],
['contrado', { tier: 'NO_STOCK', adapter: 'contrado',
reason: 'made to order — no live stock signal' }],
]);
const QUOTE_ONLY = new Set([
'dedar', 'de gournay', 'de gournay murals', 'fromental', 'gracie', 'iksel', 'zuber',
'porter teleo', 'holland and sherry', 'holland & sherry', 'clarence house', 'fortuny',
'fortuny fabrics', 'pierre frey', 'manuel canovas', 'jim thompson', 'jim thompson fabrics',
'nobilis', 'elitis', 'maya romanoff', 'donghia', 'hermes', 'armani casa', 'armani/casa',
'dolce & gabbana', 'dolce and gabbana', 'versace', 'roberto cavalli', 'roberto cavalli wallpaper',
'christian lacroix europe', 'missoni home', 'missoni wallpaper', 'missoni fabric',
'kelly wearstler', 'martyn lawrence bullard', 'celerie kemble', 'sister parish',
'holly hunt', 'holly hunt walls', 'holly hunt in stock', 'quadrille', 'china seas',
'alan campbell', 'home couture', 'cloth and paper', 'charles burger', 'grasscloth',
]);
async function main() {
const pool = new Pool({ connectionString: DSN, max: 2 });
// 1. distinct display-vendors from the grid (same filter the server applies)
const { rows: vRows } = await pool.query(
`SELECT vendor, count(*)::int AS total,
count(*) FILTER (WHERE upper(coalesce(status,''))='ACTIVE')::int AS active
FROM shopify_products
WHERE coalesce(status,'') <> 'DELETED_FROM_SHOPIFY' AND vendor IS NOT NULL AND vendor <> ''
GROUP BY vendor`);
const displayVendors = vRows.filter((r) => !BANNED.test(r.vendor));
// 2. vendor_registry — the credential + portal + platform truth
const { rows: reg } = await pool.query(
`SELECT vendor_name, vendor_code, coalesce(private_label_name,'') AS private_label_name,
coalesce(catalog_table,'') AS catalog_table, coalesce(login_url,'') AS login_url,
(trade_password IS NOT NULL AND trade_password <> '') AS has_pass,
lower(coalesce(website_tech_stack,'')) AS stack,
coalesce(website_url,'') AS website_url,
display_prices
FROM vendor_registry`);
// 3. per catalog table: does it carry resolvable product_urls, and what PLATFORM are they?
// (sampled from the URLs themselves — the reliable signal, vs the broken tech_stack field.)
const { rows: urlTabs } = await pool.query(
`SELECT c.table_name FROM information_schema.columns c
WHERE c.table_schema='public' AND c.column_name='product_url'
AND (c.table_name ~ '_catalog$' OR c.table_name ~ '^catalog_')`);
const catUrlCount = new Map(), catShopify = new Map(), catWoo = new Map();
for (const { table_name } of urlTabs) {
try {
const { rows } = await pool.query(
`SELECT product_url FROM "${table_name}" WHERE product_url IS NOT NULL AND product_url <> '' LIMIT 40`);
catUrlCount.set(table_name, rows.length);
const shop = rows.filter((r) => isCanonicalShopify(r.product_url)).length;
const woo = rows.filter((r) => WOO_URL.test(r.product_url)).length;
// majority-of-sample vote so one odd URL doesn't flip the platform
catShopify.set(table_name, rows.length > 0 && shop >= Math.max(1, rows.length * 0.5));
catWoo.set(table_name, rows.length > 0 && woo >= Math.max(1, rows.length * 0.5));
} catch { /* skip unreadable */ }
}
const hasUrls = (tbl) => tbl && (catUrlCount.get(tbl) || 0) > 0;
// shared bucket table is NOT per-SKU resolvable for a single vendor's live URL
const dedicated = (tbl) => tbl && tbl !== 'vendor_catalog';
// registry lookup indexes
const byName = new Map(); // lower(vendor_name) → row
const byPrivateLabel = new Map(); // lower(private_label_name) → [rows]
for (const r of reg) {
byName.set(norm(r.vendor_name), r);
if (r.private_label_name) (byPrivateLabel.get(norm(r.private_label_name)) || byPrivateLabel.set(norm(r.private_label_name), []).get(norm(r.private_label_name))).push(r);
}
// resolve a display-vendor → best backing registry row (+ how it matched)
function resolve(vendor) {
const v = norm(vendor);
if (byName.has(v)) return { row: byName.get(v), method: 'exact' };
// display name IS a private-label bucket → pick the strongest backing (creds > shopify > first)
if (byPrivateLabel.has(v)) {
const rows = byPrivateLabel.get(v);
const pick = rows.find((r) => r.has_pass) || rows.find((r) => catShopify.get(r.catalog_table)) || rows[0];
return { row: pick, method: 'private-label' };
}
const s = norm(stripSuffix(vendor));
if (s !== v && byName.has(s)) return { row: byName.get(s), method: 'suffix-strip' };
return { row: null, method: 'unmatched' };
}
// adapter routing (only the reliable-contract adapters ENABLE; Woo is wired-but-dimmed)
function classify(vendor, backing, method) {
const v = norm(vendor);
// A private-label BUCKET (e.g. "Phillipe Romano" = Astek + Command54 + Justin David) aggregates
// many upstreams — a single backing catalog can't represent it, so a public-stock scrape would
// mostly mis-route and could associate the private label with a real vendor's site. Only 1:1
// matches (exact / suffix-strip) earn a public-stock adapter; buckets stay dim unless WallQuest-backed.
const oneToOne = method === 'exact' || method === 'suffix-strip';
// -1. KRAVET FAMILY → DB_AUTHORITATIVE (FIRST — precedes QUOTE_ONLY and every portal branch).
// The authoritative Kravet price file (daily email ingest) answers instantly at $0 with no
// Browserbase session; resolvability is gated on kapMatchCount (mfr_sku present in
// kravet_authoritative_pricing), not on a catalog product_url join.
if (KRAVET_FAMILY.has(v))
return { tier: 'DB_AUTHORITATIVE', live_capable: true, adapter: 'kravet-db',
reason: 'authoritative Kravet price file (daily email ingest — $0, instant)',
catalog_table: (backing && backing.catalog_table) || null, resolves_by: 'kap' };
// 0. WallQuest-backed private labels that don't resolve through vendor_registry — the grid
// shows the private-label name; SKUs live in wallquest_catalog (the adapter looks them up).
if (WALLQUEST_ALIASES.has(v))
return { tier: 'PORTAL_CREDS', live_capable: true, adapter: 'wallquest',
reason: 'WallQuest trade portal (creds on file)', catalog_table: 'wallquest_catalog' };
if (QUOTE_ONLY.has(v)) return { tier: 'QUOTE_ONLY', live_capable: false, adapter: null,
reason: 'vendor does not publish live stock (to-the-trade / quote-only)' };
// WIRED-but-DIM registered adapters with no honest live-stock signal (Schumacher/Fabricut/Contrado).
// Kept dim on purpose — the adapter exists and is registered, but coverage will not ENABLE a
// metered check because the vendor exposes nothing live to read. Precedes backing/portal branches.
if (WIRED_DIM.has(v)) {
const w = WIRED_DIM.get(v);
return { tier: w.tier, live_capable: false, adapter: w.adapter, reason: w.reason,
catalog_table: (backing && backing.catalog_table) || null };
}
// LIVENESS adapters (Koroseal) — login-free Available/Discontinued via URL reconstruction from the
// mfr code. Routed before the login_url branch so a spec-catalog login_url doesn't mis-dim it.
if (LIVENESS_ADAPTERS.has(v)) {
const m = LIVENESS_ADAPTERS.get(v);
return { tier: 'PUBLIC_STOCK', live_capable: true, adapter: m.adapter,
reason: m.reason, catalog_table: m.catalog_table || (backing && backing.catalog_table) || null,
resolves_by: m.resolves_by };
}
if (!backing) return { tier: 'NO_STOCK', live_capable: false, adapter: null,
reason: 'no live stock source identified for this line (private-label aggregate or unmapped)' };
const tbl = backing.catalog_table || '', pl = norm(backing.private_label_name);
const isWQ = /^(wallquest|malibu|brewster)_catalog$/.test(tbl) || pl === 'malibu wallpaper';
const isRomo = norm(backing.vendor_code) === 'romo' || norm(backing.vendor_name) === 'romo';
// 1. PORTAL+CREDS — wired portal adapters. WallQuest resolves via wallquest_catalog regardless of
// which sister row (brewster/malibu) mapped; Romo logs into trade.theromogroup.com.
if (isWQ)
return { tier: 'PORTAL_CREDS', live_capable: true, adapter: 'wallquest',
reason: 'WallQuest trade portal (creds on file)', catalog_table: 'wallquest_catalog' };
if (isRomo && hasUrls(tbl))
return { tier: 'PORTAL_CREDS', live_capable: true, adapter: 'romo',
reason: 'Romo trade portal (creds on file)', catalog_table: tbl || 'romo_catalog' };
// 1b. VAULT-PORTAL — trade creds in the dw-price-stock vault (referenced in place), portal
// exposes LIVE stock behind login → wired login adapter. Precedes the public-Shopify branch
// because a vault-portal vendor's stock is trade-gated, not on the anonymous storefront.
if (VAULT_PORTAL_ADAPTERS.has(v) && dedicated(tbl) && hasUrls(tbl)) {
const m = VAULT_PORTAL_ADAPTERS.get(v);
return { tier: 'PORTAL_CREDS', live_capable: true, adapter: m.adapter,
reason: m.reason, catalog_table: m.catalog_table || tbl };
}
// 2. PUBLIC_STOCK (Shopify) — canonical public .js feed, dedicated catalog, no login → ENABLE.
// Precedes the portal check: a login_url may exist (an account page) yet stock is public.
if (oneToOne && dedicated(tbl) && hasUrls(tbl) && catShopify.get(tbl))
return { tier: 'PUBLIC_STOCK', live_capable: true, adapter: 'shopify-public',
reason: 'public Shopify storefront stock (.js feed)', catalog_table: tbl };
// 2b. PUBLIC_STOCK (schema.org) — probed vendors whose anonymous PDP emits Offer availability.
// Precedes the portal branches so a login_url doesn't force these to dim as PORTAL_NO_CREDS.
if (SCHEMA_PUBLIC.has(v) && oneToOne && dedicated(tbl) && hasUrls(tbl)) {
if (SCHEMA_PARSE_PENDING.has(v))
return { tier: 'PUBLIC_STOCK', live_capable: false, adapter: 'schema-public',
reason: SCHEMA_PARSE_PENDING.get(v), catalog_table: tbl };
return { tier: 'PUBLIC_STOCK', live_capable: true, adapter: 'schema-public',
reason: 'public schema.org/Magento availability (no login required)', catalog_table: tbl };
}
// 2c. PORTAL, PRICE-ONLY — login publishes a price sheet, not live stock → honest dim.
if (NO_LIVE_STOCK_PORTAL.has(v))
return { tier: 'PORTAL_NO_CREDS', live_capable: false, adapter: null,
reason: 'vendor portal does not expose live stock (wholesale price sheet / SFTP only)', catalog_table: tbl };
if (backing.has_pass) // creds exist but no adapter wired for this specific portal yet
return { tier: 'PORTAL_CREDS', live_capable: false, adapter: null,
reason: 'backing portal has trade creds but no live adapter wired yet', catalog_table: tbl };
// 3. PORTAL+NO-CREDS — a login portal exists but no creds (Magento/B2B hide stock) → needs creds
if (backing.login_url)
return { tier: 'PORTAL_NO_CREDS', live_capable: false, adapter: null,
reason: 'vendor portal exists but no trade credentials on file — supply creds to enable', catalog_table: tbl };
// 4. PUBLIC_STOCK (Woo) — WooCommerce Store API; adapter wired but not per-site verified → dim
if (oneToOne && dedicated(tbl) && hasUrls(tbl) && catWoo.get(tbl))
return { tier: 'PUBLIC_STOCK', live_capable: false, adapter: 'woo-public',
reason: 'WooCommerce stock adapter ready — pending per-site verification', catalog_table: tbl };
// 5. explicit quote-only signal from registry
if (backing.display_prices === false)
return { tier: 'QUOTE_ONLY', live_capable: false, adapter: null,
reason: 'vendor does not publish live stock (to-the-trade / quote-only)' };
return { tier: 'NO_STOCK', live_capable: false, adapter: null,
reason: 'no live stock source identified for this line' };
}
// How many of a vendor's ACTIVE display SKUs actually resolve to a product_url in its backing
// catalog (matched by dw_sku OR mfr_sku). A wired adapter is USELESS — and would burn a metered
// session per click only to miss — if the grid's SKUs don't align with the catalog's SKUs.
async function resolvableCount(vendor, tbl) {
if (!tbl || !/^[a-z0-9_]+$/.test(tbl) || tbl === 'vendor_catalog') return { active: 0, resolvable: 0 };
try {
const { rows } = await pool.query(
// The third OR mirrors resolve.js: match on the leading <letters><digits>-<digits> code
// extracted from a COMPOUND display mfr_sku (Osborne & Little "W7813-02-Villa Como-Aqua" →
// "W7813-02"). It's a pure ADD (an extra OR), so it can only raise a vendor's resolvable
// count — never drop one already passing — and it's a no-op for already-clean SKUs.
`SELECT count(*) FILTER (WHERE upper(s.status)='ACTIVE')::int AS active,
count(*) FILTER (WHERE upper(s.status)='ACTIVE' AND EXISTS (
SELECT 1 FROM "${tbl}" c
WHERE (upper(c.dw_sku)=upper(s.dw_sku)
OR upper(coalesce(c.mfr_sku,''))=upper(coalesce(s.mfr_sku,''))
OR (substring(s.mfr_sku from '^[A-Za-z]+[0-9]+-[0-9]+') IS NOT NULL
AND upper(c.mfr_sku)=upper(substring(s.mfr_sku from '^[A-Za-z]+[0-9]+-[0-9]+'))))
AND c.product_url IS NOT NULL AND c.product_url <> ''))::int AS resolvable
FROM shopify_products s WHERE s.vendor = $1`, [vendor]);
return rows[0];
} catch { return { active: 0, resolvable: 0 }; }
}
// Resolvability gate for LIVENESS adapters that resolve via URL RECONSTRUCTION from the mfr code
// (Koroseal) rather than a catalog product_url join. Here "resolvable" = active grid rows carrying a
// usable mfr code (the reconstruction key). Koroseal's koroseal_catalog.product_url is sparse (only
// the digital-lab line), so the product_url gate would wrongly dim it; the honest measure is mfr-key
// presence — 2351/2351 active Koroseal SKUs have one.
async function mfrKeyCount(vendor) {
try {
const { rows } = await pool.query(
`SELECT count(*) FILTER (WHERE upper(status)='ACTIVE')::int AS active,
count(*) FILTER (WHERE upper(status)='ACTIVE' AND coalesce(mfr_sku,'') <> '')::int AS resolvable
FROM shopify_products WHERE vendor = $1`, [vendor]);
return rows[0];
} catch { return { active: 0, resolvable: 0 }; }
}
// Resolvability gate for the DB_AUTHORITATIVE tier (kravet-db): "resolvable" = the vendor's
// 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(${mfr})
OR upper(k.mfr_sku)=upper(${mfr})||'.0'
OR upper(k.mfr_sku)||'.0'=upper(${mfr})
-- dash→dot: Shopify "W3909-50" ↔ price-file "W3909.50.0" (wallcovering codes)
OR (position('-' in upper(${mfr}))>0
AND upper(k.mfr_sku)=replace(upper(${mfr}),'-','.')||'.0')))::int AS resolvable
FROM shopify_products s ${join} WHERE s.vendor = $1`, [vendor]);
return rows[0];
} catch { return { active: 0, resolvable: 0 }; }
}
const vendors = {};
const counts = { DB_AUTHORITATIVE: 0, PORTAL_CREDS: 0, PUBLIC_STOCK: 0, PORTAL_NO_CREDS: 0, QUOTE_ONLY: 0, NO_STOCK: 0, live_capable: 0 };
for (const dv of displayVendors) {
const { row: backing, method } = resolve(dv.vendor);
const c = classify(dv.vendor, backing, method);
const entry = {
tier: c.tier, live_capable: c.live_capable, adapter: c.adapter || null, reason: c.reason,
catalog_table: c.catalog_table || (backing && backing.catalog_table) || null,
backing: backing ? backing.vendor_name : null, match: method,
products: dv.total, active: dv.active,
};
// Resolvability gate: only ENABLE a wired vendor whose display SKUs actually resolve to a PDP.
// Reconstruction adapters (Koroseal) are gated on mfr-key presence; all others on catalog product_url join.
if (entry.live_capable && entry.adapter) {
const { active, resolvable } = c.resolves_by === 'kap'
? await kapMatchCount(dv.vendor)
: c.resolves_by === 'mfr'
? await mfrKeyCount(dv.vendor)
: await resolvableCount(dv.vendor, entry.catalog_table);
entry.resolvable_active = resolvable;
entry.resolvable_pct = active ? Math.round((resolvable / active) * 100) : 0;
if (!(resolvable >= 5 && entry.resolvable_pct >= 20)) {
entry.live_capable = false;
entry.reason = c.resolves_by === 'kap'
? `adapter wired (${entry.adapter}) but too few SKUs match the Kravet authoritative price file (${resolvable} of ${active} active)`
: c.resolves_by === 'mfr'
? `adapter wired (${entry.adapter}) but too few Koroseal SKUs carry an mfr code to reconstruct a PDP (${resolvable} of ${active} active)`
: `adapter wired (${entry.adapter}) but display SKUs don't align with the ${entry.catalog_table} catalog (${resolvable} of ${active} active resolvable) — needs SKU remap`;
}
}
if (entry.live_capable) entry.verified = VERIFIED[dv.vendor] || 'wired — not yet live-verified';
vendors[dv.vendor] = entry;
counts[c.tier] = (counts[c.tier] || 0) + 1;
if (entry.live_capable) counts.live_capable += 1;
}
const out = {
generated_at: new Date().toISOString(),
generator: 'scripts/build-coverage.js',
total_display_vendors: displayVendors.length,
counts,
// vendors keyed by the exact display-vendor string the grid uses (case-sensitive)
vendors,
};
await pool.end();
if (DRY) { console.log(JSON.stringify({ counts, total: out.total_display_vendors }, null, 2)); return; }
fs.writeFileSync(OUT, JSON.stringify(out, null, 2));
console.log(`coverage → ${OUT}`);
console.log(` ${out.total_display_vendors} display-vendors | live-capable ${counts.live_capable}`);
console.log(` DB_AUTHORITATIVE ${counts.DB_AUTHORITATIVE} · PORTAL_CREDS ${counts.PORTAL_CREDS} · PUBLIC_STOCK ${counts.PUBLIC_STOCK} · PORTAL_NO_CREDS ${counts.PORTAL_NO_CREDS} · QUOTE_ONLY ${counts.QUOTE_ONLY} · NO_STOCK ${counts.NO_STOCK}`);
}
main().catch((e) => { console.error('coverage build failed:', e.message); process.exit(1); });