Add roll-on-fabric detector (bucket1 genuine-fabric-on-roll vs bucket2 mistyped-wallpaper)

0f3780cde3eaf69ebc294c7060a36e835033d5a5 · 2026-07-09 16:21:51 -0700 · Steve
Files touched

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

commit 0f3780cde3eaf69ebc294c7060a36e835033d5a5
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Jul 9 16:21:51 2026 -0700

    Add roll-on-fabric detector (bucket1 genuine-fabric-on-roll vs bucket2 mistyped-wallpaper)
---
 shopify/scripts/detect-roll-on-fabric.sql | 63 +++++++++++++++++++++++++++++++
 1 file changed, 63 insertions(+)

diff --git a/shopify/scripts/detect-roll-on-fabric.sql b/shopify/scripts/detect-roll-on-fabric.sql
new file mode 100644
index 00000000..4a4d531d
--- /dev/null
+++ b/shopify/scripts/detect-roll-on-fabric.sql
@@ -0,0 +1,63 @@
+-- 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."
+--
+-- Read-only against the local dw_unified mirror (synced daily; authoritative
+-- confirm is the Shopify Admin API variant option value).
+--
+-- 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.
+\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
+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' );
+
+\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
+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;
+
+\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
+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;
← ed16c8e2 auto-save: 2026-07-09T16:11:12 (9 files) — pending-approval/ · back to Designer Wallcoverings · Ship color-palette REFINE (DEDUP_DE + dwCiReveal + slider un 964e0b6d →