Fix Romo tariff-doubling: drop cost*1.10 term from costExpr

3dbec8281d4366353eebba8098063132958ece1d · 2026-08-03 12:31:31 -0700 · steve-office

Root cause of the Romo double-tariff pricing bug. GREATEST(...) had a third term
COALESCE(cost,0)*1.10 that over-inflated: cost = pre-tariff trade price, so *1.10
(10%) exceeded the true landed cost (trade + 5% tariff = total_price_per_roll) and
GREATEST picked it, making our_price ~5% high on ~34% of rows. Verified vs the trade
portal (Figura Total/Roll $946.05). Kept the two authoritative terms which equal the
correct landed cost and still guard the 2026-06-11 corrupt-low-total case.
Now matches authoritative basis on all 1313 priced rows.

Files touched

M shopify/scripts/cadence/vendors.js

Diff

commit 3dbec8281d4366353eebba8098063132958ece1d
Author: steve-office <steve@designerwallcoverings.com>
Date:   Mon Aug 3 12:31:31 2026 -0700

    Fix Romo tariff-doubling: drop cost*1.10 term from costExpr
    
    Root cause of the Romo double-tariff pricing bug. GREATEST(...) had a third term
    COALESCE(cost,0)*1.10 that over-inflated: cost = pre-tariff trade price, so *1.10
    (10%) exceeded the true landed cost (trade + 5% tariff = total_price_per_roll) and
    GREATEST picked it, making our_price ~5% high on ~34% of rows. Verified vs the trade
    portal (Figura Total/Roll $946.05). Kept the two authoritative terms which equal the
    correct landed cost and still guard the 2026-06-11 corrupt-low-total case.
    Now matches authoritative basis on all 1313 priced rows.
---
 shopify/scripts/cadence/vendors.js | 8 +++++++-
 1 file changed, 7 insertions(+), 1 deletion(-)

diff --git a/shopify/scripts/cadence/vendors.js b/shopify/scripts/cadence/vendors.js
index ad4a6695..969c8555 100644
--- a/shopify/scripts/cadence/vendors.js
+++ b/shopify/scripts/cadence/vendors.js
@@ -27,7 +27,13 @@ module.exports = {
   // 2026-06-11: total_price_per_roll was corrupt for 57 rows (computed off the bogus
   // `price`/per-yard cols, came out ~10x low e.g. Jali total=13.12 vs trade=128). GREATEST
   // guards against that: never trust a stored total below the trade-based landed cost.
-  'Romo':       { table: 'romo_catalog',           costExpr: 'GREATEST(COALESCE(NULLIF(total_price_per_roll,0),0), COALESCE(NULLIF(trade_price_per_roll,0),0)*(1+COALESCE(tariff_pct,0)/100), COALESCE(cost,0)*1.10)', soldBy: 'Single Roll', productType: 'Wallcovering', note: 'DTD-B 2026-06-11: landed cost = trade_price_per_roll * (1+tariff); confirm tariff treatment w/ Steve before bulk import' },
+  // 2026-08-03: dropped the third GREATEST term `COALESCE(cost,0)*1.10` — it DOUBLE-COUNTED
+  // the tariff. `cost` for Romo = the PRE-tariff trade price, so cost*1.10 (a 10% uplift)
+  // exceeds the true landed cost (trade + 5% tariff = total_price_per_roll) by ~4.76%, and
+  // GREATEST then picked the inflated value → our_price ~5% too high on ~34% of rows. Verified
+  // against the trade portal (Figura W948/03: Total/Roll $946.05, NOT $991.10=cost*1.10). The two
+  // remaining terms both equal the correct landed cost AND still guard the corrupt-low-total case.
+  'Romo':       { table: 'romo_catalog',           costExpr: 'GREATEST(COALESCE(NULLIF(total_price_per_roll,0),0), COALESCE(NULLIF(trade_price_per_roll,0),0)*(1+COALESCE(tariff_pct,0)/100))', soldBy: 'Single Roll', productType: 'Wallcovering', note: 'landed cost = total_price_per_roll (= trade_price_per_roll * (1+tariff), authoritative per trade.theromogroup.com). Single 5% tariff only — never *1.10.' },
   'Thibaut':    { table: 'thibaut_catalog',         costExpr: 'your_cost',            soldBy: 'Single Roll', productType: 'Wallcovering' },
   'Osborne & Little': { table: 'osborne_catalog',   costExpr: 'price_trade',          soldBy: 'Single Roll', productType: 'Wallcovering' },
   'Greenland':  { table: 'greenland_catalog',      costExpr: 'NULL::numeric',        soldBy: 'Made to Order', productType: 'Wallcovering', note: 'quote-only cork line — NULL price + quotes tag; activate-gated bypasses the price gate via the quotes exemption' },

← eadf7f37 auto-save: 2026-08-03T11:23:33 (1 files) — shopify/scripts/c · back to Designer Wallcoverings · auto-save: 2026-08-03T16:55:48 (1 files) — shopify/scripts/a b9f4278e →