auto-data-snapshot: 2026-09-10T01:01:23 (1 data files) — scripts/merge-v2-into-canonical.sql

9fa7d0c5e23079b119e681cf850fe732940a7ee9 · 2026-09-10 01:02:48 -0700 · auto-commit-fleet
Files touched

A scripts/merge-v2-into-canonical.sql
Diff

commit 9fa7d0c5e23079b119e681cf850fe732940a7ee9
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date:   Thu Sep 10 01:02:48 2026 -0700

    auto-data-snapshot: 2026-09-10T01:01:23 (1 data files) — scripts/merge-v2-into-canonical.sql
---
 scripts/merge-v2-into-canonical.sql | 76 +++++++++++++++++++++++++++++++++++++
 1 file changed, 76 insertions(+)

diff --git a/scripts/merge-v2-into-canonical.sql b/scripts/merge-v2-into-canonical.sql
new file mode 100644
index 0000000..4b55c73
--- /dev/null
+++ b/scripts/merge-v2-into-canonical.sql
@@ -0,0 +1,76 @@
+-- TK-10670 enrichment-preserving MERGE: newmor_catalog_refresh_20260909_v2 -> newmor_catalog
+-- SAFE BY DEFAULT: runs against a SCRATCH COPY inside a transaction and ROLLS BACK.
+-- Set :apply to 1 ONLY via the gated ! paste to run for real against canonical (still txn-wrapped).
+-- Key: real-code ('C:'..) for coded rows, (pattern|color) ('N:'..) for code-less.
+\set ON_ERROR_STOP on
+BEGIN;
+
+-- ---- target selection: scratch by default, canonical only when :apply=1 ----
+DROP TABLE IF EXISTS _tgt;
+CREATE TEMP TABLE _tgt AS SELECT * FROM newmor_catalog;   -- scratch working copy
+ALTER TABLE _tgt ADD COLUMN mkey text;
+UPDATE _tgt SET mkey = CASE WHEN mfr_sku ~ '[0-9]'
+  THEN 'C:'||upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g'))
+  ELSE 'N:'||upper(regexp_replace(pattern_name,'[^A-Za-z0-9]','','g'))||'|'||upper(regexp_replace(coalesce(color_name,''),'[^A-Za-z0-9]','','g')) END;
+
+-- ---- v2 keyed + deduped (one row per mkey; prefer most-complete) ----
+DROP TABLE IF EXISTS _v2;
+CREATE TEMP TABLE _v2 AS
+SELECT * FROM (
+  SELECT *,
+    CASE WHEN mfr_sku ~ '[0-9]' AND code_source='vendor_code'
+      THEN 'C:'||upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g'))
+      ELSE 'N:'||upper(regexp_replace(pattern_name,'[^A-Za-z0-9]','','g'))||'|'||upper(regexp_replace(coalesce(color_name,''),'[^A-Za-z0-9]','','g')) END AS mkey,
+    row_number() OVER (PARTITION BY (CASE WHEN mfr_sku ~ '[0-9]' AND code_source='vendor_code'
+      THEN 'C:'||upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g'))
+      ELSE 'N:'||upper(regexp_replace(pattern_name,'[^A-Za-z0-9]','','g'))||'|'||upper(regexp_replace(coalesce(color_name,''),'[^A-Za-z0-9]','','g')) END)
+      ORDER BY (CASE WHEN image_url<>'' THEN 1 ELSE 0 END + CASE WHEN color_name<>'' THEN 1 ELSE 0 END) DESC) rn
+  FROM newmor_catalog_refresh_20260909_v2
+) q WHERE rn=1;
+
+-- capture pre-state
+DROP TABLE IF EXISTS _pre; CREATE TEMP TABLE _pre AS SELECT count(*) n, count(dw_sku) FILTER (WHERE dw_sku<>'') dwsku FROM _tgt;
+
+-- ---- OP1: MATCHED -> update the 15 raw fields only; discontinued=false ----
+UPDATE _tgt t SET
+  pattern_name=v.pattern_name, color_name=v.color_name, collection=v.collection,
+  product_type=v.product_type, width=v.width, length=v.length, repeat_v=v.repeat_v,
+  image_url=v.image_url, product_url=v.product_url, in_stock=v.in_stock, material=v.material,
+  match_type=v.match_type,
+  last_scraped=now(), updated_at=now(), discontinued=false
+FROM _v2 v WHERE t.mkey=v.mkey;
+
+-- ---- OP2: ABSENT -> flag discontinued=true; touch nothing else ----
+UPDATE _tgt t SET discontinued=true, updated_at=now()
+WHERE t.mkey NOT IN (SELECT mkey FROM _v2);
+
+-- ---- OP3: NEW -> insert raw fields; dw_sku NULL (minting stays gated) ----
+INSERT INTO _tgt (mfr_sku, pattern_name, color_name, collection, product_type, width, length,
+  repeat_v, image_url, product_url, in_stock, material, match_type,
+  last_scraped, created_at, updated_at, discontinued, dw_sku, mkey)
+SELECT v.mfr_sku, v.pattern_name, v.color_name, v.collection, v.product_type, v.width, v.length,
+  v.repeat_v, v.image_url, v.product_url, v.in_stock, v.material, v.match_type,
+  now(), now(), now(), false, NULL, v.mkey
+FROM _v2 v WHERE v.mkey NOT IN (SELECT mkey FROM _tgt);
+
+-- ---- PROOF / ASSERTS ----
+SELECT 'pre_rows'          lbl, n::text val FROM _pre
+UNION ALL SELECT 'pre_dwsku',       dwsku::text FROM _pre
+UNION ALL SELECT 'post_rows',       count(*)::text FROM _tgt
+UNION ALL SELECT 'post_dwsku',      count(dw_sku)::text FILTER (WHERE dw_sku<>'') FROM _tgt
+UNION ALL SELECT 'flagged_discont', count(*)::text FILTER (WHERE discontinued) FROM _tgt
+UNION ALL SELECT 'new_dwsku_null',  count(*)::text FILTER (WHERE dw_sku IS NULL) FROM _tgt
+UNION ALL SELECT 'dupe_mkey',       (SELECT count(*)::text FROM (SELECT mkey FROM _tgt GROUP BY mkey HAVING count(*)>1) d);
+
+-- ASSERT: dw_sku preserved (post >= pre), no deletes (post_rows = pre + new)
+DO $$
+DECLARE pre_dw int; post_dw int; pre_n int; post_n int;
+BEGIN
+  SELECT dwsku,n INTO pre_dw,pre_n FROM _pre;
+  SELECT count(*) FILTER (WHERE dw_sku<>''), count(*) INTO post_dw,post_n FROM _tgt;
+  IF post_dw < pre_dw THEN RAISE EXCEPTION 'ASSERT FAIL: dw_sku dropped % -> %', pre_dw, post_dw; END IF;
+  IF post_n < pre_n THEN RAISE EXCEPTION 'ASSERT FAIL: rows deleted % -> %', pre_n, post_n; END IF;
+  RAISE NOTICE 'ASSERTS PASS: rows %->%  dw_sku %->% (preserved)', pre_n, post_n, pre_dw, post_dw;
+END $$;
+
+ROLLBACK;   -- SCRATCH PROOF: never commits. Real run = separate gated ! paste.
← 6caa541 TK-10670 2nd pass: v2 Newmor scraper — real-code vs synthesi · back to Newmor Onboard · auto-data-snapshot: 2026-09-10T01:33:27 (1 data files) — scr 9ccba71 →