← back to Vcc Cost Backfill
apply-2026-08-24.sql
200 lines
-- ============================================================================
-- 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) — 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.net_cost,
cost_source = 'wallquest_catalog'
FROM wallquest_catalog vc
WHERE vc.mfr_sku = sp.mfr_sku
AND sp.status = 'ACTIVE'
AND vc.net_cost > 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;