[object Object]

← back to Vcc Cost Backfill

wallquest cost_price loader: use net_cost not our_price (retail) (TK-11410)

bfc5ef5de2285e91729f43be2a3298f6f7c8435c · 2026-09-10 13:09:56 -0700 · vp-dw-commerce

apply-2026-08-24.sql block 02 loaded cost_price=wallquest_catalog.our_price,
which is the DW RETAIL (=net_cost/0.65/0.85), not the net cost — zeroing margin
and false-flagging ~1,145 WallQuest products as below cost (NA508 cost_price
$226.68=retail vs real net_cost $125.24). Fixed to read vc.net_cost. Added
corrective-wallquest-cost-TK11410.sql (DRAFT/gated, snapshot+undo) to repair the
~2,484 already-contaminated rows; NOT run (live backfill is gated).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01PuFgoHYPwznEaT5iBdCRny

Files touched

Diff

commit bfc5ef5de2285e91729f43be2a3298f6f7c8435c
Author: vp-dw-commerce <steve@designerwallcoverings.com>
Date:   Thu Sep 10 13:09:56 2026 -0700

    wallquest cost_price loader: use net_cost not our_price (retail) (TK-11410)
    
    apply-2026-08-24.sql block 02 loaded cost_price=wallquest_catalog.our_price,
    which is the DW RETAIL (=net_cost/0.65/0.85), not the net cost — zeroing margin
    and false-flagging ~1,145 WallQuest products as below cost (NA508 cost_price
    $226.68=retail vs real net_cost $125.24). Fixed to read vc.net_cost. Added
    corrective-wallquest-cost-TK11410.sql (DRAFT/gated, snapshot+undo) to repair the
    ~2,484 already-contaminated rows; NOT run (live backfill is gated).
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01PuFgoHYPwznEaT5iBdCRny
---
 apply-2026-08-24.sql                  | 11 +++++++---
 corrective-wallquest-cost-TK11410.sql | 40 +++++++++++++++++++++++++++++++++++
 2 files changed, 48 insertions(+), 3 deletions(-)

diff --git a/apply-2026-08-24.sql b/apply-2026-08-24.sql
index f7ece81..7bec30f 100644
--- a/apply-2026-08-24.sql
+++ b/apply-2026-08-24.sql
@@ -86,14 +86,19 @@ UPDATE shopify_products sp
    AND kap.new_map > 0
    AND (sp.cost_price IS NULL OR sp.cost_price = 0);
 
--- 02. WallQuest (Malibu private label) — our_price.
+-- 02. WallQuest (Malibu private label) — NET COST.
+--     FIX (TK-11410): was `vc.our_price`, which is the DW RETAIL/selling price
+--     (our_price == price_dw == net_cost/0.65/0.85, verified > net_cost*1.5 on all
+--     2,085 catalog rows), NOT the cost. Loading retail into cost_price zeroed the
+--     margin and FALSE-FLAGGED ~1,145 WallQuest products as below cost (e.g. NA508
+--     cost_price=$226.68=retail vs real net_cost=$125.24). Correct column = net_cost.
 UPDATE shopify_products sp
-   SET cost_price  = vc.our_price,
+   SET cost_price  = vc.net_cost,
        cost_source = 'wallquest_catalog'
   FROM wallquest_catalog vc
  WHERE vc.mfr_sku = sp.mfr_sku
    AND sp.status = 'ACTIVE'
-   AND vc.our_price > 0
+   AND vc.net_cost > 0
    AND (sp.cost_price IS NULL OR sp.cost_price = 0);
 
 -- 03. York — cost.
diff --git a/corrective-wallquest-cost-TK11410.sql b/corrective-wallquest-cost-TK11410.sql
new file mode 100644
index 0000000..19c3cbe
--- /dev/null
+++ b/corrective-wallquest-cost-TK11410.sql
@@ -0,0 +1,40 @@
+-- ============================================================================
+-- TK-11410 CORRECTIVE — fix WallQuest cost_price contaminated with RETAIL.
+-- *** DRAFT — GATED. Do NOT run autonomously. Steve applies via the runbook. ***
+--
+-- ROOT CAUSE: apply-2026-08-24.sql block 02 set cost_price = wallquest_catalog.our_price
+--   (the DW RETAIL/selling price = net_cost/0.65/0.85), not the net cost. That zeroed
+--   margin and false-flagged ~1,145 WallQuest products as below cost. ~2,484 rows carry
+--   cost_source='wallquest_catalog' with cost_price == retail (our_price/price_dw).
+--
+-- The forward loader is now FIXED (block 02 reads vc.net_cost), but that UPDATE is
+-- guarded by `cost_price IS NULL OR = 0`, so it will NOT overwrite the already-wrong
+-- rows. This file overwrites them with the real net cost.
+--
+-- TARGET: Mac2 dw_unified mirror (host=/tmp). NOTE: shopify_products is Kamatera-
+--   canonical, so a corresponding Kamatera write (or a Shopify custom.cost metafield
+--   write + resync) is REQUIRED for the fix to persist — that is the customer-facing,
+--   split-brain, GATED half. This SQL is the Mac2-mirror half for review.
+-- COST: $0 (local psql). Reversible: snapshot the OLD cost_price first (below).
+-- ============================================================================
+
+-- 0) SNAPSHOT (reversibility) — save old values before the corrective UPDATE.
+CREATE TABLE IF NOT EXISTS tk11410_wq_costprice_backup AS
+SELECT sp.id, sp.dw_sku, sp.mfr_sku, sp.cost_price AS old_cost_price, now() AS backed_up_at
+FROM shopify_products sp
+JOIN wallquest_catalog vc ON vc.mfr_sku = sp.mfr_sku
+WHERE sp.cost_source = 'wallquest_catalog'
+  AND vc.net_cost > 0
+  AND abs(sp.cost_price - vc.our_price) < 0.01;   -- only the retail-contaminated rows
+
+-- 1) CORRECT: overwrite retail-as-cost with the real net cost.
+UPDATE shopify_products sp
+   SET cost_price = vc.net_cost
+  FROM wallquest_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+   AND sp.cost_source = 'wallquest_catalog'
+   AND vc.net_cost > 0
+   AND abs(sp.cost_price - vc.our_price) < 0.01;   -- guard: only touch the contaminated rows
+
+-- UNDO:  UPDATE shopify_products sp SET cost_price = b.old_cost_price
+--          FROM tk11410_wq_costprice_backup b WHERE b.id = sp.id;

← b3d5e82 VCC cost backfill: real transaction-wrapped apply-2026-08-24  ·  back to Vcc Cost Backfill  ·  (newest)