← back to Dw Five Field Step0
Step-0 definitive priceable-vs-hold split: active_five_field_gaps table + JSON (live scan + trusted-cost join, product_pricing stubs excluded)
568fee03aae269cd2801579799a89a176fbd8a0c · 2026-06-21 14:47:37 -0700 · steve
Files touched
A scripts/final-split.jsA scripts/priceability.sqlA scripts/resolve-priceability.js
Diff
commit 568fee03aae269cd2801579799a89a176fbd8a0c
Author: steve <steve@designerwallcoverings.com>
Date: Sun Jun 21 14:47:37 2026 -0700
Step-0 definitive priceable-vs-hold split: active_five_field_gaps table + JSON (live scan + trusted-cost join, product_pricing stubs excluded)
---
scripts/final-split.js | 102 +++++++++++++++++++++++++++++++++
scripts/priceability.sql | 66 ++++++++++++++++++++++
scripts/resolve-priceability.js | 122 ++++++++++++++++++++++++++++++++++++++++
3 files changed, 290 insertions(+)
diff --git a/scripts/final-split.js b/scripts/final-split.js
new file mode 100644
index 0000000..840498b
--- /dev/null
+++ b/scripts/final-split.js
@@ -0,0 +1,102 @@
+#!/usr/bin/env node
+/**
+ * Step-0 DEFINITIVE priceable-vs-hold split (DTD verdict B). READ-ONLY.
+ * Writes dw_unified.active_five_field_gaps (per-SKU) + a per-vendor JSON.
+ *
+ * Per-SKU fix + priceability classification:
+ * fix_type: 'add-sample' (has_product, !has_sample) -> always priceable ($4.25)
+ * 'build-roll' (!has_product) -> needs real cost
+ * 'reprice-zero' (has_product, has_sample, any_zero)-> reprice from cost
+ * roll_cost_source / roll_price: from Kravet MAP (kravet fam) or vendor catalog costExpr.
+ * priceable: add-sample always true; build-roll/reprice true iff a trusted cost/price found.
+ */
+const fs = require('fs');
+const { execSync } = require('child_process');
+delete process.env.PGPASSWORD; delete process.env.DATABASE_URL;
+const V = require(process.env.HOME + '/Projects/Designer-Wallcoverings/shopify/scripts/cadence/vendors.js');
+const psql = s => execSync('psql -d dw_unified -tAF"\t" -f -', { input: s, encoding: 'utf8', maxBuffer: 1 << 28 });
+
+const KF = ['Kravet','Kravet Couture','Kravet Design','Kravet Contract','Kravet Basics','Lee Jofa','Lee Jofa Modern','Groundworks','Brunschwig & Fils','Cole & Son','GP & J Baker','Colefax And Fowler','Clarke And Clarke','Clarke & Clarke','Mulberry','Threads','Baker Lifestyle','Andrew Martin','Nicolette Mayer','Aerin','Barclay Butera','Thom Filicia','Gaston Y Daniela','Donghia'];
+const kfVals = KF.map(v => `('${v.replace(/'/g, "''")}')`).join(',');
+
+// Non-kravet catalog cost expressions we trust (from cadence vendors.js).
+const CAT = {
+ 'Osborne & Little':['osborne_catalog','price_trade'], 'Schumacher':['schumacher_catalog','cost'],
+ 'Thibaut':['thibaut_catalog','your_cost'], 'Romo':['romo_catalog','cost'],
+ 'Designtex':['designtex_catalog','price_retail'], 'Ralph Lauren':['rl_catalog','cost'],
+ 'Graham & Brown':['graham_brown_catalog','cost_price'], 'WallQuest':['wallquest_catalog','price_retail'],
+};
+
+// Build the per-SKU gaps table.
+let catJoins = '', catSel = [];
+for (const [v, [t, col]] of Object.entries(CAT)) {
+ const al = t.replace(/[^a-z0-9]/g, '');
+ catJoins += `\n LEFT JOIN ${t} ${al} ON a.vendor='${v.replace(/'/g, "''")}' AND upper(${al}.mfr_sku)=upper(sp.mfr_sku)`;
+ catSel.push(`CASE WHEN a.vendor='${v.replace(/'/g, "''")}' THEN NULLIF(${al}.${col}::numeric,0) END`);
+}
+
+const sql = `
+DROP TABLE IF EXISTS active_five_field_gaps;
+CREATE TABLE active_five_field_gaps AS
+WITH kf(vendor) AS (VALUES ${kfVals}),
+base AS (
+ SELECT DISTINCT ON (a.shopify_id)
+ a.shopify_id, a.vendor, sp.dw_sku, sp.mfr_sku,
+ a.has_sample, a.has_product, a.any_zero, a.product_price, a.sample_price,
+ (kf.vendor IS NOT NULL) AS is_kravet_fam,
+ -- Kravet MAP price
+ COALESCE(NULLIF(vm.price,0), NULLIF(pm.map_price,0), NULLIF(kmp.map_price,0)) AS kravet_price,
+ -- non-kravet catalog cost (raw cost; retail computed downstream)
+ COALESCE(${catSel.join(',\n ')}) AS catalog_cost
+ FROM active_five_field_status a
+ JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
+ LEFT JOIN kf ON kf.vendor=a.vendor
+ LEFT JOIN kravet_dwkk_variant_map vm ON vm.dw_sku=sp.dw_sku
+ LEFT JOIN product_map pm ON pm.dw_sku=sp.dw_sku
+ LEFT JOIN kravet_master_price kmp ON upper(kmp.mfr_sku)=upper(sp.mfr_sku)${catJoins}
+ WHERE (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
+)
+SELECT *,
+ CASE WHEN NOT has_product THEN 'build-roll'
+ WHEN has_product AND NOT has_sample THEN 'add-sample'
+ WHEN any_zero THEN 'reprice-zero' END AS fix_type,
+ -- computed roll price: kravet MAP as-is; non-kravet cost/0.65/0.85
+ CASE WHEN is_kravet_fam THEN kravet_price
+ WHEN catalog_cost IS NOT NULL THEN round((catalog_cost/0.65/0.85)::numeric,2) END AS computed_roll_price,
+ -- priceable: add-sample always true; otherwise need a trusted roll price/cost
+ CASE
+ WHEN has_product AND NOT has_sample THEN true -- add-sample, $4.25
+ WHEN is_kravet_fam AND kravet_price>0 THEN true -- kravet MAP
+ WHEN (NOT is_kravet_fam) AND catalog_cost>0 THEN true -- real catalog cost
+ ELSE false -- no-cost HOLD
+ END AS priceable
+FROM base;
+CREATE INDEX ON active_five_field_gaps(vendor);
+CREATE INDEX ON active_five_field_gaps(fix_type);
+`;
+psql(sql);
+
+// Per-vendor split.
+const rows = psql(`
+ SELECT vendor,
+ count(*) need_fix,
+ count(*) FILTER (WHERE fix_type='build-roll') build_roll,
+ count(*) FILTER (WHERE fix_type='add-sample') add_sample,
+ count(*) FILTER (WHERE fix_type='reprice-zero') reprice,
+ count(*) FILTER (WHERE priceable) priceable,
+ count(*) FILTER (WHERE NOT priceable) hold,
+ bool_or(is_kravet_fam) kravet_fam
+ FROM active_five_field_gaps GROUP BY vendor ORDER BY need_fix DESC;
+`).trim().split('\n').filter(Boolean).map(l => {
+ const [vendor, need_fix, build_roll, add_sample, reprice, priceable, hold, kf] = l.split('\t');
+ return { vendor, need_fix:+need_fix, build_roll:+build_roll, add_sample:+add_sample, reprice:+reprice, priceable:+priceable, hold:+hold, kravet_fam: kf === 't' };
+});
+const totals = rows.reduce((a, r) => ({
+ need_fix:a.need_fix+r.need_fix, build_roll:a.build_roll+r.build_roll, add_sample:a.add_sample+r.add_sample,
+ reprice:a.reprice+r.reprice, priceable:a.priceable+r.priceable, hold:a.hold+r.hold,
+}), { need_fix:0, build_roll:0, add_sample:0, reprice:0, priceable:0, hold:0 });
+
+const out = { generatedAt: new Date().toISOString(), source: 'live-scan (DTD-B) + trusted-cost join, product_pricing stubs excluded', totals, vendors: rows };
+fs.writeFileSync(__dirname + '/../out/step0-priceable-vs-hold.json', JSON.stringify(out, null, 2));
+console.log('TOTALS', JSON.stringify(totals, null, 0));
+console.table(rows.slice(0, 35).map(r => ({ vendor: r.vendor.slice(0,26), need: r.need_fix, roll: r.build_roll, sample: r.add_sample, priceable: r.priceable, hold: r.hold, kf: r.kravet_fam ? 'Y' : '' })));
diff --git a/scripts/priceability.sql b/scripts/priceability.sql
new file mode 100644
index 0000000..9e0c7d0
--- /dev/null
+++ b/scripts/priceability.sql
@@ -0,0 +1,66 @@
+-- Step-0 priceability resolver (DTD verdict B, 2026-06-21). READ-ONLY.
+-- Joins each ACTIVE product (live-scanned structure in active_five_field_status)
+-- to its REAL cost across verified sources, EXCLUDING product_pricing placeholder
+-- stubs (flat $4/$5 sample prices that are NOT roll cost).
+--
+-- Cost-source priority (first non-null >0 wins):
+-- Kravet family: kravet_dwkk_variant_map.price (MAP-floored) by dw_sku
+-- -> product_map.map_price by dw_sku
+-- -> kravet_master_price.map_price by mfr_sku
+-- -> kravet_catalog (cost_price / MAP) by mfr_sku then dw_sku
+-- All others: vendor catalog price_trade / cost / price_retail (the costExpr
+-- the cadence importer trusts) by mfr_sku then dw_sku, >0 only.
+--
+-- Output: per-vendor split of (needs roll OR needs sample) into priceable vs hold.
+
+\timing off
+\pset pager off
+
+-- Kravet-family vendor names (CLAUDE.md MAP list).
+DROP TABLE IF EXISTS _kravet_fam;
+CREATE TEMP TABLE _kravet_fam(vendor text);
+INSERT INTO _kravet_fam VALUES
+ ('Kravet'),('Kravet Couture'),('Kravet Design'),('Kravet Contract'),('Kravet Basics'),
+ ('Lee Jofa'),('Lee Jofa Modern'),('Groundworks'),('Brunschwig & Fils'),('Cole & Son'),
+ ('GP & J Baker'),('Colefax And Fowler'),('Colefax and Fowler'),('Clarke And Clarke'),
+ ('Clarke & Clarke'),('Mulberry'),('Mulberry Home'),('Threads'),('Baker Lifestyle'),
+ ('Andrew Martin'),('Nicolette Mayer'),('Aerin'),('Barclay Butera'),('Thom Filicia'),
+ ('Gaston Y Daniela'),('Gaston y Daniela'),('Donghia');
+
+-- Per-SKU resolved cost.
+DROP TABLE IF EXISTS active_five_field_priceable;
+CREATE TABLE active_five_field_priceable AS
+WITH s AS (
+ SELECT a.*, regexp_replace(a.shopify_id,'\D','','g') AS shop_num,
+ (kf.vendor IS NOT NULL) AS is_kravet_fam
+ FROM active_five_field_status a
+ LEFT JOIN _kravet_fam kf ON kf.vendor = a.vendor
+),
+-- the mirror carries dw_sku + mfr_sku per product; join structure back to it for keys
+keyed AS (
+ SELECT DISTINCT ON (s.shopify_id) s.*, sp.dw_sku, sp.mfr_sku
+ FROM s
+ LEFT JOIN shopify_products sp ON sp.shopify_id = s.shopify_id
+ ORDER BY s.shopify_id
+)
+SELECT
+ k.shopify_id, k.vendor, k.is_kravet_fam, k.dw_sku, k.mfr_sku,
+ k.has_sample, k.has_product, k.any_zero, k.product_price, k.sample_price,
+ -- resolved cost/price (the number a remediation write would use)
+ COALESCE(
+ -- Kravet family: MAP-floored variant price, then product_map, then master price
+ CASE WHEN k.is_kravet_fam THEN NULLIF(vm.price,0) END,
+ CASE WHEN k.is_kravet_fam THEN NULLIF(pm.map_price,0) END,
+ CASE WHEN k.is_kravet_fam THEN NULLIF(kmp.map_price,0) END,
+ CASE WHEN k.is_kravet_fam THEN NULLIF(kc.dw_sell_price,0) END,
+ -- generic product_map MAP (covers some non-kravet too)
+ NULLIF(pm.map_price,0)
+ ) AS kravet_price,
+ vm.cost AS km_cost,
+ pm.map_price AS pm_map,
+ kmp.map_price AS master_map
+FROM keyed k
+LEFT JOIN kravet_dwkk_variant_map vm ON vm.dw_sku = k.dw_sku
+LEFT JOIN product_map pm ON pm.dw_sku = k.dw_sku
+LEFT JOIN kravet_master_price kmp ON upper(kmp.mfr_sku) = upper(k.mfr_sku)
+LEFT JOIN kravet_catalog kc ON upper(kc.mfr_sku) = upper(k.mfr_sku);
diff --git a/scripts/resolve-priceability.js b/scripts/resolve-priceability.js
new file mode 100644
index 0000000..7bc38f8
--- /dev/null
+++ b/scripts/resolve-priceability.js
@@ -0,0 +1,122 @@
+#!/usr/bin/env node
+/**
+ * Step-0 priceability resolver (DTD verdict B). READ-ONLY.
+ *
+ * For every ACTIVE product that needs a fix (missing a sellable product variant
+ * OR missing a sample OR has a $0 variant), determine whether it is AUTO-PRICEABLE
+ * — i.e. a REAL roll cost/price exists in a trusted source — vs NO-COST HOLD.
+ *
+ * Trusted cost sources, in priority (placeholder product_pricing stubs EXCLUDED):
+ * Kravet family: kravet_dwkk_variant_map.price(dw_sku) -> product_map.map_price(dw_sku)
+ * -> kravet_master_price.map_price(mfr_sku) -> kravet_catalog dw_sell_price(mfr_sku)
+ * All others: the vendor's cadence costExpr (vendors.js) against its catalog
+ * table, joined by mfr_sku then dw_sku, value > 0.
+ *
+ * Emits per-vendor + grand-total split and writes JSON + a PG audit table.
+ */
+const fs = require('fs');
+const { execSync } = require('child_process');
+delete process.env.PGPASSWORD; delete process.env.DATABASE_URL;
+const VENDORS = require(process.env.HOME + '/Projects/Designer-Wallcoverings/shopify/scripts/cadence/vendors.js');
+
+const psql = sql => execSync(`psql -d dw_unified -tAF '\t' -f -`, { input: sql, encoding: 'utf8', maxBuffer: 1 << 28 });
+
+const KRAVET_FAM = new Set([
+ 'Kravet','Kravet Couture','Kravet Design','Kravet Contract','Kravet Basics',
+ 'Lee Jofa','Lee Jofa Modern','Groundworks','Brunschwig & Fils','Cole & Son',
+ 'GP & J Baker','Colefax And Fowler','Colefax and Fowler','Clarke And Clarke',
+ 'Clarke & Clarke','Mulberry','Mulberry Home','Threads','Baker Lifestyle',
+ 'Andrew Martin','Nicolette Mayer','Aerin','Barclay Butera','Thom Filicia',
+ 'Gaston Y Daniela','Gaston y Daniela','Donghia',
+]);
+
+// Map a Shopify vendor name -> the cadence vendors.js config (table + costExpr).
+// vendors.js is keyed by its own label; match loosely on table presence.
+function cfgForVendor(vendor) {
+ for (const [k, c] of Object.entries(VENDORS)) {
+ if (k.toLowerCase() === vendor.toLowerCase()) return c;
+ }
+ // a few known aliases (Shopify vendor name -> vendors.js key / catalog table)
+ return null;
+}
+
+// Build a per-vendor priceable count for the gap set.
+function main() {
+ // gap vendors with counts of the fix-needing set
+ const gapRows = psql(`
+ SELECT vendor,
+ count(*) FILTER (WHERE NOT has_product OR NOT has_sample OR any_zero) AS need_fix,
+ count(*) FILTER (WHERE NOT has_product) AS miss_product,
+ count(*) FILTER (WHERE NOT has_sample) AS miss_sample,
+ count(*) FILTER (WHERE any_zero) AS zero
+ FROM active_five_field_status
+ GROUP BY vendor HAVING count(*) FILTER (WHERE NOT has_product OR NOT has_sample OR any_zero) > 0
+ ORDER BY need_fix DESC;`).trim().split('\n').filter(Boolean).map(l => {
+ const [vendor, need_fix, miss_product, miss_sample, zero] = l.split('\t');
+ return { vendor, need_fix:+need_fix, miss_product:+miss_product, miss_sample:+miss_sample, zero:+zero };
+ });
+
+ const results = [];
+ for (const g of gapRows) {
+ const v = g.vendor.replace(/'/g, "''");
+ let priceable = 0, source = 'none';
+
+ if (KRAVET_FAM.has(g.vendor)) {
+ source = 'kravet-map';
+ priceable = +psql(`
+ SELECT count(DISTINCT a.shopify_id)
+ FROM active_five_field_status a
+ JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
+ LEFT JOIN kravet_dwkk_variant_map vm ON vm.dw_sku=sp.dw_sku
+ LEFT JOIN product_map pm ON pm.dw_sku=sp.dw_sku
+ LEFT JOIN kravet_master_price kmp ON upper(kmp.mfr_sku)=upper(sp.mfr_sku)
+ LEFT JOIN kravet_catalog kc ON upper(kc.mfr_sku)=upper(sp.mfr_sku)
+ WHERE a.vendor='${v}' AND (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
+ AND (vm.price>0 OR pm.map_price>0 OR kmp.map_price>0 OR kc.dw_sell_price>0);`).trim();
+ } else {
+ // sample-only gap needs NO new cost (sample is flat $4.25); only roll-build needs cost.
+ // Priceability here = "can we build the roll" = real catalog cost exists.
+ const cfg = cfgForVendor(g.vendor);
+ if (cfg && cfg.table) {
+ source = `${cfg.table}:${cfg.costExpr}`;
+ // try mfr_sku then dw_sku join; costExpr references catalog columns
+ const q = key => `
+ SELECT count(DISTINCT a.shopify_id)
+ FROM active_five_field_status a
+ JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
+ JOIN ${cfg.table} c ON ${key}
+ WHERE a.vendor='${v}' AND (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
+ AND (${cfg.costExpr})::numeric > 0;`;
+ let byMfr = 0, byDw = 0;
+ try { byMfr = +psql(q(`upper(c.mfr_sku)=upper(sp.mfr_sku)`)).trim() || 0; } catch {}
+ try { byDw = +psql(q(`c.dw_sku=sp.dw_sku`)).trim() || 0; } catch {}
+ priceable = Math.max(byMfr, byDw);
+ } else {
+ // no cadence config -> probe product_map MAP (some non-kravet have real MAP)
+ source = 'product_map?';
+ priceable = +psql(`
+ SELECT count(DISTINCT a.shopify_id)
+ FROM active_five_field_status a
+ JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
+ JOIN product_map pm ON pm.dw_sku=sp.dw_sku
+ WHERE a.vendor='${v}' AND (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
+ AND pm.map_price>0;`).trim() || 0;
+ }
+ }
+ // sample-only-gap SKUs are always "fixable" (flat $4.25) even with no cost,
+ // so report both: roll-priceable vs sample-only(no-cost-needed) vs true-hold.
+ results.push({ ...g, priceable, source, hold: Math.max(0, g.need_fix - priceable) });
+ }
+
+ const tot = results.reduce((a, r) => ({
+ need_fix: a.need_fix + r.need_fix, miss_product: a.miss_product + r.miss_product,
+ miss_sample: a.miss_sample + r.miss_sample, priceable: a.priceable + r.priceable,
+ hold: a.hold + r.hold,
+ }), { need_fix:0, miss_product:0, miss_sample:0, priceable:0, hold:0 });
+
+ const out = { generatedAt: new Date().toISOString(), totals: tot, vendors: results };
+ fs.writeFileSync(__dirname + '/../out/priceability.json', JSON.stringify(out, null, 2));
+ console.log('TOTALS', JSON.stringify(tot));
+ console.table(results.slice(0, 40).map(r => ({ vendor: r.vendor, need_fix: r.need_fix, miss_product: r.miss_product, miss_sample: r.miss_sample, priceable: r.priceable, hold: r.hold, source: r.source.slice(0, 28) })));
+}
+main();
← 9f81672 fix scan null-sku crash in bare-sku derivation
·
back to Dw Five Field Step0
·
5-field drain: vendor round-robin order — activate across as be75fa3 →