Retarget roll-on-fabric detector to real cause: theme srEnhance mislabels per-yard UFAC=2 goods

bbea54beb00deb55d85d1e70974097e4122fb02f · 2026-07-09 16:30:20 -0700 · Steve
Files touched

M shopify/scripts/detect-roll-on-fabric.sql
Diff

commit bbea54beb00deb55d85d1e70974097e4122fb02f
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Jul 9 16:30:20 2026 -0700

    Retarget roll-on-fabric detector to real cause: theme srEnhance mislabels per-yard UFAC=2 goods
---
 shopify/scripts/detect-roll-on-fabric.sql | 90 +++++++++++++------------------
 1 file changed, 37 insertions(+), 53 deletions(-)

diff --git a/shopify/scripts/detect-roll-on-fabric.sql b/shopify/scripts/detect-roll-on-fabric.sql
index 4a4d531d..7fc00534 100644
--- a/shopify/scripts/detect-roll-on-fabric.sql
+++ b/shopify/scripts/detect-roll-on-fabric.sql
@@ -1,63 +1,47 @@
 -- detect-roll-on-fabric.sql  (2026-07-09)  owner: vp-dw-commerce
--- Finds GENUINE fabrics that are wrongly wearing WALLPAPER roll packaging
--- (unit "ROLL"/"Single Roll", variant option "Sold Per Roll", 2-roll increments).
--- RULE (Steve): "Multipurpose is ALWAYS a fabric and is sold PER YARD, not in rolls."
+-- Finds per-yard goods that the live theme's srEnhance() mislabels as "single roll".
 --
--- Read-only against the local dw_unified mirror (synced daily; authoritative
--- confirm is the Shopify Admin API variant option value).
+-- REAL ROOT CAUSE (not a data problem): layout/theme.liquid srEnhance() decides a
+-- product is a roll/bolt product from the qty input alone:
+--     isBolt = (min == 2 || step == 2)
+-- with NO unit check. UFAC=2 (a rolls-per-bolt factor) leaked onto Kravet-family
+-- per-yard fabrics, forcing min=2, so those FABRICS get "/ single roll" +
+-- "Sold in 2 roll increments only" painted on despite being sold PER YARD.
+-- Rule (Steve): "Multipurpose is ALWAYS a fabric and is sold PER YARD, not in rolls."
 --
--- Two buckets are reported SEPARATELY because they are OPPOSITE bugs and must
--- never be conflated (this conflation is the panel's #1 false-positive risk):
---   BUCKET 1  roll-on-genuine-fabric  -> the real target (flip roll -> yard)
---   BUCKET 2  roll-on-mistyped-wallpaper -> product_type=Fabric but it is a
---             WALLCOVERING; roll is CORRECT, product_type is wrong. Do NOT
---             touch the unit; only product_type is the (separate) fix.
+-- The durable fix is the THEME guard (only enhance when the sellable variant unit is
+-- genuinely roll/bolt). This query enumerates the products currently mislabeled so the
+-- fix can be verified (should trend to 0 mislabels after the theme patch ships) and so
+-- the UFAC=2-on-per-yard data smell can be cleaned separately.
+--
+-- Read-only against the local dw_unified mirror (synced daily).
 \set ON_ERROR_STOP on
 
--- ---- shared predicates -----------------------------------------------------
--- genuine fabric = authoritative Use/Type metafield says a fabric use AND
--- nothing says wallcovering (type metafield, product_type, tags).
--- roll signal = either unit metafield key carries a roll word.
-
-\echo '==================================================================='
-\echo 'BUCKET 1  — GENUINE FABRIC currently wearing ROLL packaging (THE BUG)'
-\echo '==================================================================='
-SELECT count(*) AS genuine_fabric_on_roll
+\echo '=== MISLABELED: active per-yard (non-roll) products carrying UFAC=2 (srEnhance paints roll) ==='
+SELECT count(*) AS mislabeled
 FROM shopify_products
-WHERE ( metafields->'global'->'Use' ->>'value' ~* '(multipurpose|drapery|upholstery|fabric)'
-     OR metafields->'global'->'Type'->>'value' ~* '(multipurpose|drapery|upholstery)' )
-  AND coalesce(metafields->'global'->'Type'->>'value','') !~* 'wallcovering'
-  AND product_type !~* 'wall'
-  AND coalesce(tags,'') !~* 'wallpaper|wallcovering'
-  AND ( metafields->'global'->'Unit of measure'->>'value' ~* 'roll'
-     OR metafields->'global'->'unit_of_measure'      ->>'value' ~* 'roll' );
+WHERE status ILIKE 'active'
+  AND coalesce(metafields->'global'->'Unit of measure'->>'value','') !~* 'roll|bolt'
+  AND coalesce(metafields->'global'->'Unit of measure'->>'value','') ~* 'yard'
+  AND metafields->'global'->'UFAC'->>'value' = '2';
 
-\echo '--- BUCKET 1 detail (dw_sku, vendor, unit) — should be EMPTY if clean ---'
-SELECT dw_sku, vendor,
-       coalesce(metafields->'global'->'Unit of measure'->>'value',
-                metafields->'global'->'unit_of_measure'->>'value') AS unit
+\echo '--- by vendor ---'
+SELECT vendor, count(*) AS n
 FROM shopify_products
-WHERE ( metafields->'global'->'Use' ->>'value' ~* '(multipurpose|drapery|upholstery|fabric)'
-     OR metafields->'global'->'Type'->>'value' ~* '(multipurpose|drapery|upholstery)' )
-  AND coalesce(metafields->'global'->'Type'->>'value','') !~* 'wallcovering'
-  AND product_type !~* 'wall'
-  AND coalesce(tags,'') !~* 'wallpaper|wallcovering'
-  AND ( metafields->'global'->'Unit of measure'->>'value' ~* 'roll'
-     OR metafields->'global'->'unit_of_measure'      ->>'value' ~* 'roll' )
-ORDER BY vendor, dw_sku
-LIMIT 200;
+WHERE status ILIKE 'active'
+  AND coalesce(metafields->'global'->'Unit of measure'->>'value','') !~* 'roll|bolt'
+  AND coalesce(metafields->'global'->'Unit of measure'->>'value','') ~* 'yard'
+  AND metafields->'global'->'UFAC'->>'value' = '2'
+GROUP BY vendor ORDER BY 2 DESC;
 
-\echo
-\echo '==================================================================='
-\echo 'BUCKET 2  — product_type=Fabric but it is a WALLCOVERING (roll OK,   '
-\echo '            product_type is the separate bug — surfaced, NOT fixed)  '
-\echo '==================================================================='
-SELECT vendor, count(*) AS mistyped_wallpaper
+\echo '--- worklist detail ---'
+SELECT dw_sku, vendor, product_type,
+       metafields->'global'->'Use'->>'value'  AS use,
+       metafields->'global'->'Unit of measure'->>'value' AS uom,
+       metafields->'global'->'UFAC'->>'value' AS ufac, title
 FROM shopify_products
-WHERE product_type ~* 'fabric'
-  AND ( metafields->'global'->'Type'->>'value' ~* 'wallcovering'
-     OR coalesce(tags,'') ~* 'wallpaper|wallcovering' )
-  AND ( metafields->'global'->'Unit of measure'->>'value' ~* 'roll'
-     OR metafields->'global'->'unit_of_measure'      ->>'value' ~* 'roll' )
-GROUP BY vendor
-ORDER BY 2 DESC;
+WHERE status ILIKE 'active'
+  AND coalesce(metafields->'global'->'Unit of measure'->>'value','') !~* 'roll|bolt'
+  AND coalesce(metafields->'global'->'Unit of measure'->>'value','') ~* 'yard'
+  AND metafields->'global'->'UFAC'->>'value' = '2'
+ORDER BY vendor, dw_sku;
← 964e0b6d Ship color-palette REFINE (DEDUP_DE + dwCiReveal + slider un · back to Designer Wallcoverings · auto-save: 2026-07-09T16:41:19 (5 files) — pending-approval/ 2a0914ce →