← back to Vcc Cost Backfill
VCC cost backfill: real transaction-wrapped apply-2026-08-24.sql (4920 rows, Mac2 path)
b3d5e8212f1384ad89ea30aeda9b5db9bcdeabf4 · 2026-08-24 13:03:25 -0700 · Steve Abrams
Kravet already backfilled (no-op); Kamatera staging too stale/thin to host the
JOINs (missing cols) so target = Mac2 mirror. Proven via ROLLBACK dry-run.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
Diff
commit b3d5e8212f1384ad89ea30aeda9b5db9bcdeabf4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 24 13:03:25 2026 -0700
VCC cost backfill: real transaction-wrapped apply-2026-08-24.sql (4920 rows, Mac2 path)
Kravet already backfilled (no-op); Kamatera staging too stale/thin to host the
JOINs (missing cols) so target = Mac2 mirror. Proven via ROLLBACK dry-run.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
apply-2026-08-24.sql | 194 +++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 194 insertions(+)
diff --git a/apply-2026-08-24.sql b/apply-2026-08-24.sql
new file mode 100644
index 0000000..f7ece81
--- /dev/null
+++ b/apply-2026-08-24.sql
@@ -0,0 +1,194 @@
+-- ============================================================================
+-- VCC COST BACKFILL — APPLY
+-- Ticket: TK-10785-victor-vcc-cost-backfill-dry-run-gated-a
+-- Author: vp-dw-commerce (steve@designerwallcoverings.com)
+-- Built: 2026-08-24
+--
+-- WHAT THIS DOES
+-- Backfills shopify_products.cost_price for ACTIVE products that currently
+-- have NO cost (cost_price IS NULL OR = 0), by joining each product to its
+-- Mac2-canonical vendor *_catalog staging table on mfr_sku (or dw_sku for
+-- designtex/innovations). Each filled row is stamped with cost_source =
+-- the bare source-table name so the whole batch is cleanly revertible.
+--
+-- ----------------------------------------------------------------------------
+-- (a) PROVEN ROWS-AFFECTED on the CHOSEN target (Mac2 dw_unified mirror):
+-- DISTINCT products newly filled = 4,920 (sum of per-UPDATE = 4,928;
+-- ~8-row cross-source overlap resolved by first-writer-wins + NULL guard)
+--
+-- Per-UPDATE match counts (SELECT-mirror of each UPDATE's WHERE, run
+-- 2026-08-24 against the Mac2 mirror):
+-- 01 kravet_authoritative_pricing ... 0 <- ALREADY backfilled 2026-08-23
+-- under a different cost_source
+-- tag; guard skips them. Kept
+-- for idempotent completeness.
+-- 02 wallquest_catalog ............. 2489
+-- 03 york_catalog .................. 1047
+-- 04 brewster_catalog ............... 525
+-- 05 romo_catalog ................... 166
+-- 06 anna_french_catalog ............ 156
+-- 07 schumacher_catalog ............. 115
+-- 08 thibaut_catalog ................ 125
+-- 09 groundworks_catalog ............ 7
+-- 10 designtex_catalog (dw_sku) ..... 279
+-- 11 innovations_catalog (dw_sku) ... 19
+-- (NOTE: the dry-run memo's headline of 17,227 is STALE — it counted the
+-- 12,242 Kravet rows that were backfilled the day before this file was
+-- built; those are now a no-op.)
+--
+-- (b) REVERSIBILITY — run this to undo THIS backfill only (run-scoped to the
+-- 11 cost_source tags this file writes; the 2 phantom extras from the memo,
+-- rebel_walls_catalog + carnegie_catalog, are NOT written here and are
+-- dropped from the undo set):
+--
+-- UPDATE shopify_products
+-- SET cost_price = NULL, cost_source = NULL, cost_unit_of_measure = NULL
+-- WHERE cost_source IN (
+-- 'kravet_authoritative_pricing','wallquest_catalog','york_catalog',
+-- 'brewster_catalog','romo_catalog','anna_french_catalog',
+-- 'schumacher_catalog','thibaut_catalog','groundworks_catalog',
+-- 'designtex_catalog','innovations_catalog'
+-- );
+--
+-- (Safe: at build time 0 ACTIVE products carried any of these 11 bare tags,
+-- so this WHERE affects only rows this file created.)
+--
+-- (c) TARGET INSTANCE — MAC2 dw_unified MIRROR (host=/tmp socket).
+-- WHY NOT KAMATERA (the shopify_products canonical): every JOIN source is a
+-- Mac2-canonical *_catalog staging table, and the Kamatera copies are
+-- STALE/THIN and MISSING columns these UPDATEs need — on Kamatera
+-- wallquest_catalog has NO our_price, anna_french_catalog has NO cost, and
+-- thibaut_catalog lacks our_price — so this file would throw
+-- "column does not exist" and abort on Kamatera. Mac2 has every needed
+-- column and the authoritative catalog data. Kamatera (canonical for
+-- shopify_products) inherits these cost_price values on the next
+-- Shopify->PG sync that re-populates the mirror direction, OR via the
+-- standing machine reconcile. Risk: a future Kamatera->Mac2 overwrite of
+-- shopify_products could clear these; the cost_source tags make re-apply
+-- trivial and detectable.
+--
+-- COST TO PRODUCE: $0 (local psql SELECT-mirror counts only; no paid API).
+-- HARD RAIL: >500-row customer-facing catalog write -> GATED. Do NOT run
+-- autonomously; this file is applied only by Steve via the pending-approval
+-- runbook.
+-- ============================================================================
+
+BEGIN;
+
+-- 01. Kravet-family via authoritative MAP (wholesale = new_map / 1.5).
+-- Currently a no-op (already backfilled), kept for idempotent completeness.
+UPDATE shopify_products sp
+ SET cost_price = kap.new_map / 1.5,
+ cost_source = 'kravet_authoritative_pricing'
+ FROM kravet_authoritative_pricing kap
+ WHERE kap.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND kap.new_map > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 02. WallQuest (Malibu private label) — our_price.
+UPDATE shopify_products sp
+ SET cost_price = vc.our_price,
+ 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 (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 03. York — cost.
+UPDATE shopify_products sp
+ SET cost_price = vc.cost,
+ cost_source = 'york_catalog'
+ FROM york_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.cost > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 04. Brewster — cost.
+UPDATE shopify_products sp
+ SET cost_price = vc.cost,
+ cost_source = 'brewster_catalog'
+ FROM brewster_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.cost > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 05. Romo — cost.
+UPDATE shopify_products sp
+ SET cost_price = vc.cost,
+ cost_source = 'romo_catalog'
+ FROM romo_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.cost > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 06. Anna French — cost.
+UPDATE shopify_products sp
+ SET cost_price = vc.cost,
+ cost_source = 'anna_french_catalog'
+ FROM anna_french_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.cost > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 07. Schumacher — cost.
+UPDATE shopify_products sp
+ SET cost_price = vc.cost,
+ cost_source = 'schumacher_catalog'
+ FROM schumacher_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.cost > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 08. Thibaut — our_price (the .cost column is all 0 for Thibaut).
+UPDATE shopify_products sp
+ SET cost_price = vc.our_price,
+ cost_source = 'thibaut_catalog'
+ FROM thibaut_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.our_price > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 09. Groundworks — wholesale.
+UPDATE shopify_products sp
+ SET cost_price = vc.wholesale,
+ cost_source = 'groundworks_catalog'
+ FROM groundworks_catalog vc
+ WHERE vc.mfr_sku = sp.mfr_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.wholesale > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 10. Designtex — our_price, joined on dw_sku (verified 1:1, no dup dw_sku).
+UPDATE shopify_products sp
+ SET cost_price = vc.our_price,
+ cost_source = 'designtex_catalog'
+ FROM designtex_catalog vc
+ WHERE vc.dw_sku = sp.dw_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.our_price > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- 11. Innovations — our_price, joined on dw_sku (verified 1:1, no dup dw_sku).
+UPDATE shopify_products sp
+ SET cost_price = vc.our_price,
+ cost_source = 'innovations_catalog'
+ FROM innovations_catalog vc
+ WHERE vc.dw_sku = sp.dw_sku
+ AND sp.status = 'ACTIVE'
+ AND vc.our_price > 0
+ AND (sp.cost_price IS NULL OR sp.cost_price = 0);
+
+-- cost_unit_of_measure is DELIBERATELY LEFT UNSET: each source fills a MIX of
+-- product_type (e.g. wallquest = 2486 Wallcovering + 3 Fabric; thibaut = 70 WC
+-- + 55 Fabric), so a single per-UPDATE UOM would mislabel the minority rows.
+-- Per the "don't guess" rule, UOM is omitted rather than stamped wrong.
+
+COMMIT;
← 4bf2f27 Add VCC cost backfill dry-run analysis for TK-10785
·
back to Vcc Cost Backfill
·
wallquest cost_price loader: use net_cost not our_price (ret bfc5ef5 →