← back to Designer Wallcoverings
Add compute-price-coverage.js — truthful vendor cost-coverage into dw_unified
4646391119b846a8d63571aa4bd27af697c4962b · 2026-06-11 10:57:33 -0700 · SteveStudio2
Counts cost via vendors.js costExpr (never the stale registry column), filters the
shared vendor_catalog by vendor_code, upserts vendor_price_coverage + history snapshot.
Feeds the CNCP Price Coverage panel.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
A shopify/scripts/cadence/compute-price-coverage.js
Diff
commit 4646391119b846a8d63571aa4bd27af697c4962b
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Thu Jun 11 10:57:33 2026 -0700
Add compute-price-coverage.js — truthful vendor cost-coverage into dw_unified
Counts cost via vendors.js costExpr (never the stale registry column), filters the
shared vendor_catalog by vendor_code, upserts vendor_price_coverage + history snapshot.
Feeds the CNCP Price Coverage panel.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
shopify/scripts/cadence/compute-price-coverage.js | 212 ++++++++++++++++++++++
1 file changed, 212 insertions(+)
diff --git a/shopify/scripts/cadence/compute-price-coverage.js b/shopify/scripts/cadence/compute-price-coverage.js
new file mode 100644
index 00000000..96073702
--- /dev/null
+++ b/shopify/scripts/cadence/compute-price-coverage.js
@@ -0,0 +1,212 @@
+#!/usr/bin/env node
+/**
+ * compute-price-coverage.js — publish TRUTHFUL vendor price-coverage into dw_unified.
+ *
+ * WHY: On 2026-06-11 ~924 products shipped at the wrong $4.25 sample price because
+ * their vendors had NO real cost, so retail = cost/0.65/0.85 had nothing to run on.
+ * This effort tracks "how many vendors / SKUs now have correct cost". The CNCP
+ * "Price Coverage" goal panel reads the two tables this script maintains.
+ *
+ * SOURCE OF TRUTH: vendors.js costExpr map (the SAME map the cadence importer gates
+ * on). We NEVER trust vendor_registry.catalog_with_cost — that column is stale/manual
+ * (e.g. it claims York Contract = 147 costed when the real costExpr count is 31).
+ *
+ * For each IMPORTABLE vendor (in vendors.js, has a verified costExpr):
+ * total = COUNT(*) of its catalog table
+ * costed = COUNT(*) FILTER (WHERE (<costExpr>) > 0)
+ * For every OTHER active vendor with a catalog_table: cost cannot be trusted (no
+ * verified costExpr — "NEVER guess a cost column"), so total = row count, costed = 0,
+ * importable = false. This keeps the big zeros (Kravet/Newwall/Cowtan&Tout) honestly
+ * visible at 0% instead of hidden.
+ *
+ * NON-DESTRUCTIVE: only READS *_catalog + vendor_registry; only WRITES the two new
+ * tables vendor_price_coverage (current) + vendor_price_coverage_history (trend).
+ *
+ * Usage: node compute-price-coverage.js # recompute + upsert + history row
+ * node compute-price-coverage.js --quiet # only errors to stderr
+ */
+const { Pool } = require('pg');
+const VENDORS = require('./vendors.js');
+
+const QUIET = process.argv.includes('--quiet');
+const log = (...a) => { if (!QUIET) console.log(...a); };
+
+const pool = new Pool(
+ process.env.DATABASE_URL
+ ? { connectionString: process.env.DATABASE_URL }
+ : { host: '/tmp', database: 'dw_unified' } // peer auth as current user (same as `psql -d dw_unified`)
+);
+
+const GREEN_PCT = 95; // a vendor is "green" when importable AND >= this % costed
+
+const DDL = `
+CREATE TABLE IF NOT EXISTS vendor_price_coverage (
+ vendor_name TEXT PRIMARY KEY,
+ catalog_table TEXT,
+ discount_confirmed BOOLEAN,
+ is_active BOOLEAN,
+ importable BOOLEAN,
+ total_skus INTEGER NOT NULL DEFAULT 0,
+ costed_skus INTEGER NOT NULL DEFAULT 0,
+ pct_costed NUMERIC(5,2),
+ green BOOLEAN,
+ computed_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
+CREATE TABLE IF NOT EXISTS vendor_price_coverage_history (
+ snapshot_at TIMESTAMPTZ PRIMARY KEY DEFAULT now(),
+ total_skus INTEGER,
+ costed_skus INTEGER,
+ vendors_green INTEGER,
+ vendors_active INTEGER,
+ vendors_confirmed INTEGER
+);`;
+
+// safe identifier (table name) check — defends the dynamic SQL below
+const SAFE_IDENT = s => typeof s === 'string' && /^[a-z_][a-z0-9_]*$/i.test(s);
+
+async function tableExists(client, name) {
+ if (!SAFE_IDENT(name)) return false;
+ const r = await client.query('SELECT to_regclass($1) AS reg', ['public.' + name]);
+ return !!r.rows[0].reg;
+}
+
+// Some vendors share one big multi-vendor table (vendor_catalog, 200k rows across
+// many vendor_codes). When the table has a vendor_code column we MUST filter to the
+// vendor's own slice, or every shared-table vendor double-counts the whole table.
+const _vcCache = new Map();
+async function hasVendorCode(client, table) {
+ if (_vcCache.has(table)) return _vcCache.get(table);
+ const r = await client.query(
+ `SELECT 1 FROM information_schema.columns WHERE table_name=$1 AND column_name='vendor_code' LIMIT 1`,
+ [table]);
+ const has = r.rowCount > 0;
+ _vcCache.set(table, has);
+ return has;
+}
+
+// Count total + (optionally) costed rows for a vendor. costExpr is from vendors.js
+// (trusted, code-reviewed) — not user input. vendorCode filters the shared table.
+async function countCoverage(client, table, costExpr, vendorCode) {
+ const filtered = vendorCode && await hasVendorCode(client, table);
+ const where = filtered ? 'WHERE vendor_code = $1' : '';
+ const params = filtered ? [vendorCode] : [];
+ const costedSel = costExpr
+ ? `count(*) FILTER (WHERE (${costExpr}) > 0)::int`
+ : '0';
+ const sql = `SELECT count(*)::int AS total, ${costedSel} AS costed FROM ${table} ${where}`;
+ const r = await client.query(sql, params);
+ return { total: r.rows[0].total, costed: r.rows[0].costed };
+}
+
+async function upsert(client, row) {
+ const pct = row.total > 0 ? Math.round((row.costed / row.total) * 10000) / 100 : 0;
+ const green = !!row.importable && pct >= GREEN_PCT;
+ await client.query(
+ `INSERT INTO vendor_price_coverage
+ (vendor_name, catalog_table, discount_confirmed, is_active, importable,
+ total_skus, costed_skus, pct_costed, green, computed_at)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9, now())
+ ON CONFLICT (vendor_name) DO UPDATE SET
+ catalog_table=EXCLUDED.catalog_table, discount_confirmed=EXCLUDED.discount_confirmed,
+ is_active=EXCLUDED.is_active, importable=EXCLUDED.importable,
+ total_skus=EXCLUDED.total_skus, costed_skus=EXCLUDED.costed_skus,
+ pct_costed=EXCLUDED.pct_costed, green=EXCLUDED.green, computed_at=now()`,
+ [row.vendor_name, row.catalog_table, row.discount_confirmed, row.is_active,
+ row.importable, row.total, row.costed, pct, green]
+ );
+ return { ...row, pct, green };
+}
+
+(async () => {
+ const client = await pool.connect();
+ try {
+ await client.query(DDL);
+
+ // 1) registry snapshot — discount_confirmed / is_active / catalog_table / vendor_code per vendor
+ const reg = await client.query(
+ `SELECT vendor_name, vendor_code, catalog_table, discount_confirmed, is_active
+ FROM vendor_registry`);
+ const regByName = new Map();
+ for (const v of reg.rows) regByName.set(v.vendor_name, v);
+
+ const seen = new Set();
+ let nImportable = 0, nOther = 0, skipped = [];
+
+ // 2) IMPORTABLE vendors — truthful costExpr counts
+ for (const [vendorName, cfg] of Object.entries(VENDORS)) {
+ const r = regByName.get(vendorName) || {};
+ if (!(await tableExists(client, cfg.table))) {
+ skipped.push(`${vendorName} (missing table ${cfg.table})`);
+ continue;
+ }
+ let cov;
+ try {
+ cov = await countCoverage(client, cfg.table, cfg.costExpr, r.vendor_code);
+ } catch (e) {
+ skipped.push(`${vendorName} (costExpr error: ${e.message})`);
+ continue;
+ }
+ const out = await upsert(client, {
+ vendor_name: vendorName,
+ catalog_table: cfg.table,
+ discount_confirmed: r.discount_confirmed ?? null,
+ is_active: r.is_active ?? true,
+ importable: true,
+ total: cov.total, costed: cov.costed,
+ });
+ seen.add(vendorName);
+ nImportable++;
+ log(` [importable] ${vendorName.padEnd(20)} ${out.costed}/${out.total} (${out.pct}%)`);
+ }
+
+ // 3) every OTHER active vendor with a catalog_table — total only, costed=0
+ for (const v of reg.rows) {
+ if (seen.has(v.vendor_name)) continue;
+ if (!v.is_active) continue;
+ if (!v.catalog_table || !v.catalog_table.trim()) continue;
+ if (!(await tableExists(client, v.catalog_table))) continue;
+ let cov;
+ try {
+ // no verified costExpr for these → costed stays 0 ("NEVER guess a cost column")
+ cov = await countCoverage(client, v.catalog_table, null, v.vendor_code);
+ } catch (e) { continue; }
+ await upsert(client, {
+ vendor_name: v.vendor_name,
+ catalog_table: v.catalog_table,
+ discount_confirmed: v.discount_confirmed ?? null,
+ is_active: v.is_active,
+ importable: false,
+ total: cov.total, costed: 0,
+ });
+ seen.add(v.vendor_name);
+ nOther++;
+ }
+
+ // 4) aggregate trend snapshot
+ const agg = await client.query(
+ `SELECT COALESCE(SUM(total_skus),0)::int AS total_skus,
+ COALESCE(SUM(costed_skus),0)::int AS costed_skus,
+ COUNT(*) FILTER (WHERE green)::int AS vendors_green,
+ COUNT(*) FILTER (WHERE is_active)::int AS vendors_active,
+ COUNT(*) FILTER (WHERE discount_confirmed)::int AS vendors_confirmed
+ FROM vendor_price_coverage`);
+ const a = agg.rows[0];
+ await client.query(
+ `INSERT INTO vendor_price_coverage_history
+ (snapshot_at, total_skus, costed_skus, vendors_green, vendors_active, vendors_confirmed)
+ VALUES (now(), $1,$2,$3,$4,$5)`,
+ [a.total_skus, a.costed_skus, a.vendors_green, a.vendors_active, a.vendors_confirmed]);
+
+ const pct = a.total_skus > 0 ? ((a.costed_skus / a.total_skus) * 100).toFixed(1) : '0.0';
+ log(`\n✅ coverage recomputed: ${nImportable} importable + ${nOther} other vendors`);
+ log(` SKUs costed: ${a.costed_skus.toLocaleString()} / ${a.total_skus.toLocaleString()} (${pct}%)`);
+ log(` vendors green: ${a.vendors_green} · active: ${a.vendors_active} · confirmed: ${a.vendors_confirmed}`);
+ if (skipped.length) log(` skipped: ${skipped.join('; ')}`);
+ } catch (e) {
+ console.error('[compute-price-coverage] FATAL:', e.message);
+ process.exitCode = 1;
+ } finally {
+ client.release();
+ await pool.end();
+ }
+})();
← 2ec9fcae Recrawl: mark vendor-404 SKUs as discontinued (excluded) + S
·
back to Designer Wallcoverings
·
cadence-import: recompute price-coverage at end of run (feed bccac2fd →