← back to Dw Validator Debug TK11314
auto-data-snapshot: 2026-08-22T11:19:22 (1 data files) — DW-Agents/vendor-command-center/cost-backfill-2026-08-22.sql
ddbc12bc2ae85879c1952a32e2b49cbebd629380 · 2026-08-22 11:19:52 -0700 · auto-commit-fleet
Files touched
A DW-Agents/vendor-command-center/cost-backfill-2026-08-22.sql
Diff
commit ddbc12bc2ae85879c1952a32e2b49cbebd629380
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date: Sat Aug 22 11:19:52 2026 -0700
auto-data-snapshot: 2026-08-22T11:19:22 (1 data files) — DW-Agents/vendor-command-center/cost-backfill-2026-08-22.sql
---
.../cost-backfill-2026-08-22.sql | 72 ++++++++++++++++++++++
1 file changed, 72 insertions(+)
diff --git a/DW-Agents/vendor-command-center/cost-backfill-2026-08-22.sql b/DW-Agents/vendor-command-center/cost-backfill-2026-08-22.sql
new file mode 100644
index 00000000..d59c1d4c
--- /dev/null
+++ b/DW-Agents/vendor-command-center/cost-backfill-2026-08-22.sql
@@ -0,0 +1,72 @@
+-- ============================================================================
+-- VCC / catalog cost_price BACKFILL (2026-08-22) — DRAFT, GATED, DO NOT RUN
+-- ============================================================================
+-- Backfills shopify_products.cost_price (+ cost_source) for ACTIVE products
+-- whose cost is null/0, from the authoritative price lists + Justin David.
+-- The Kravet/Cowtan umbrella (kravet_authoritative_pricing) is the big lever.
+--
+-- HARD GATE: shopify_products is Kamatera-canonical + feeds pricing + ~18k rows.
+-- Steve must approve, AND decide: run on KAMATERA (canonical, re-sync down) vs
+-- the Mac2 mirror. This script is written mirror-neutral (runs on whichever DB
+-- you point psql at). Recommended: Kamatera, then let the mirror re-sync.
+--
+-- Reversibility: step 0 captures every touched row's OLD cost_price into a
+-- restore table. Revert = UPDATE ... FROM that table.
+--
+-- Priority per product: (1) kravet_authoritative_pricing.new_whls (net cost;
+-- new_map = MAP = 1.5x, retail stays MAP for Kravet lines — we write COST only)
+-- -> (2) justin_david_pricing_2026.our_cost_yd (per-yard cost)
+-- -> [vendor *_catalog net-cost cols handled in a follow-up per-vendor pass].
+-- Join key is NORMALIZED (uppercase, alphanumerics only) to defeat the DW
+-- mfr_sku decoration (.CAC.0 / trailing .0 / Lee Jofa P-prefix).
+-- ============================================================================
+
+BEGIN;
+
+-- 0. RESTORE MAP (reversibility record) --------------------------------------
+DROP TABLE IF EXISTS cost_backfill_restore_20260822;
+CREATE TABLE cost_backfill_restore_20260822 AS
+SELECT shopify_id, cost_price AS old_cost_price, cost_source AS old_cost_source
+FROM shopify_products
+WHERE status='ACTIVE' AND (cost_price IS NULL OR cost_price::numeric=0);
+
+-- 1. Kravet/Cowtan umbrella (authoritative) ----------------------------------
+WITH ka AS (
+ SELECT upper(regexp_replace(mfr_sku::text,'[^A-Za-z0-9]','','g')) k, max(new_whls) whls
+ FROM kravet_authoritative_pricing WHERE new_whls IS NOT NULL AND new_whls>0 GROUP BY 1
+)
+UPDATE shopify_products sp
+SET cost_price = ka.whls,
+ cost_source = 'kravet_authoritative_pricing.new_whls backfill 2026-08-22'
+FROM ka
+WHERE sp.status='ACTIVE' AND (sp.cost_price IS NULL OR sp.cost_price::numeric=0)
+ AND ka.k = upper(regexp_replace(sp.mfr_sku::text,'[^A-Za-z0-9]','','g'));
+
+-- 2. Justin David / LA Fabrics (per-yard cost) -------------------------------
+WITH jd AS (
+ SELECT upper(regexp_replace(mfr_sku::text,'[^A-Za-z0-9]','','g')) k, max(our_cost_yd) cost
+ FROM justin_david_pricing_2026 WHERE our_cost_yd IS NOT NULL AND our_cost_yd>0 GROUP BY 1
+)
+UPDATE shopify_products sp
+SET cost_price = jd.cost,
+ cost_source = 'justin_david_pricing_2026.our_cost_yd backfill 2026-08-22'
+FROM jd
+WHERE sp.status='ACTIVE' AND (sp.cost_price IS NULL OR sp.cost_price::numeric=0)
+ AND jd.k = upper(regexp_replace(sp.mfr_sku::text,'[^A-Za-z0-9]','','g'));
+
+-- 3. (FOLLOW-UP) non-Kravet vendor *_catalog net-cost, per vendor with a
+-- per-vendor normalization rule + SAFE numeric cast (Malibu.cost errored an
+-- unguarded cast). Adds ~3-4k more. Generated per-vendor in the apply run.
+
+-- VERIFY before COMMIT: expect ~15k updated from steps 1-2 (+~3-4k from step 3)
+SELECT count(*) FILTER (WHERE cost_source LIKE 'kravet_authoritative%') AS from_kravet,
+ count(*) FILTER (WHERE cost_source LIKE 'justin_david%') AS from_jd,
+ count(*) FILTER (WHERE cost_source LIKE '%backfill 2026-08-22%') AS total_backfilled
+FROM shopify_products WHERE status='ACTIVE';
+
+-- ROLLBACK; -- <== leave this active until Steve approves. Swap to COMMIT to apply.
+ROLLBACK;
+
+-- REVERT (if ever needed after a COMMIT):
+-- UPDATE shopify_products sp SET cost_price=r.old_cost_price, cost_source=r.old_cost_source
+-- FROM cost_backfill_restore_20260822 r WHERE sp.shopify_id=r.shopify_id;
← 934eff2b TK-10717: harden tripwire regex + widen coverage (contrarian
·
back to Dw Validator Debug TK11314
·
auto-data-snapshot: 2026-08-22T11:52:06 (1 data files) — DW- 7538e9d0 →