← back to Designerwallcoverings
Add TK-10456 price recovery probe
1cd9880740b123d049e75daa7bb3730a35440343 · 2026-08-31 11:42:53 -0700 · Steve Abrams
Files touched
A scripts/tk10456/README.mdA scripts/tk10456/probe-recovery.sqlA scripts/tk10456/verification/e2e-proof.json
Diff
commit 1cd9880740b123d049e75daa7bb3730a35440343
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 31 11:42:53 2026 -0700
Add TK-10456 price recovery probe
---
scripts/tk10456/README.md | 31 +++++++++++
scripts/tk10456/probe-recovery.sql | 85 +++++++++++++++++++++++++++++
scripts/tk10456/verification/e2e-proof.json | 40 ++++++++++++++
3 files changed, 156 insertions(+)
diff --git a/scripts/tk10456/README.md b/scripts/tk10456/README.md
new file mode 100644
index 0000000..1dc25fb
--- /dev/null
+++ b/scripts/tk10456/README.md
@@ -0,0 +1,31 @@
+# TK-10456 recovery probe
+
+This read-only probe tests whether any of the 892 reverted products have gained
+an authoritative sellable price. It imports the frozen cohort into a PostgreSQL
+temporary table, checks canonical vendor catalogs, and reports exact FileMaker
+manufacturer-SKU matches as review leads.
+
+It deliberately does not write `dw_unified` or Shopify. A FileMaker match is not
+automatically price-safe: supplier identity, unit, source freshness, and the
+vendor cost basis all must be verified before a price can feed TK-10902.
+
+Run:
+
+```sh
+psql "host=/tmp dbname=dw_unified" -X \
+ -v cohort_csv=/Users/macstudio3/Projects/dw-add-sellable-variant-tk10902/tk10456/tk10456_full_classification.csv \
+ -f scripts/tk10456/probe-recovery.sql
+```
+
+Evidence from 2026-08-31:
+
+- Manifest: 892 records; 62 already complete; 830 unresolved.
+- Canonical sources: zero priced rows for MDC/DWPP, Pierre Frey/DWPF,
+ Anna French/DWAA, and the exact 11 DWTT records.
+- FileMaker produced nine exact manufacturer-SKU leads for DWPP products.
+ All nine remain blocked because supplier is blank, the line date is 2015,
+ and `vendor_registry` defines DWPP/MDC as quote-only with no confirmed
+ discount or cost basis.
+- Manufacturer research identifies these codes as Newmor/Len-Tex commercial
+ wallcoverings, confirming that they must not inherit a guessed MDC price.
+
diff --git a/scripts/tk10456/probe-recovery.sql b/scripts/tk10456/probe-recovery.sql
new file mode 100644
index 0000000..75d0c23
--- /dev/null
+++ b/scripts/tk10456/probe-recovery.sql
@@ -0,0 +1,85 @@
+\set ON_ERROR_STOP on
+\pset pager off
+
+-- TK-10456 recovery probe. Session-temporary only: no canonical writes.
+-- Run with:
+-- psql "host=/tmp dbname=dw_unified" -X \
+-- -v cohort_csv=/absolute/path/to/tk10456_full_classification.csv \
+-- -f scripts/tk10456/probe-recovery.sql
+
+\if :{?cohort_csv}
+\else
+ \echo 'ERROR: pass -v cohort_csv=/absolute/path/to/tk10456_full_classification.csv'
+ SELECT 1 / 0 AS missing_cohort_csv;
+\endif
+
+CREATE TEMP TABLE tk10456_cohort (
+ product_id bigint,
+ vendor text,
+ mirror_status text,
+ sku text,
+ variant_sku text,
+ classification text
+);
+
+COPY tk10456_cohort FROM :'cohort_csv' WITH (FORMAT csv, HEADER true);
+
+SELECT count(*) AS manifest_rows,
+ count(*) FILTER (WHERE classification = 'DONE_active_priced') AS already_done,
+ count(*) FILTER (WHERE classification <> 'DONE_active_priced') AS unresolved
+FROM tk10456_cohort;
+
+-- Canonical vendor-catalog coverage. A zero here means activation stays blocked.
+SELECT 'MDC / DWPP' AS cohort,
+ count(*) AS catalog_rows,
+ count(price_retail) AS priced_rows
+FROM mdc_catalog
+WHERE dw_sku LIKE 'DWPP-%'
+UNION ALL
+SELECT 'Pierre Frey / DWPF', count(*), count(our_price)
+FROM pierre_frey_catalog
+WHERE dw_sku LIKE 'DWPF-%'
+UNION ALL
+SELECT 'Anna French / DWAA', count(*),
+ count(COALESCE(price_retail, price_trade, cost, our_price))
+FROM anna_french_catalog
+WHERE dw_sku LIKE 'DWAA-%';
+
+SELECT 'Exact DWTT 11' AS cohort,
+ count(*) AS catalog_rows,
+ count(COALESCE(retail_price, your_cost, dw_retail_price, cost,
+ our_price, net_cost)) AS priced_rows
+FROM thibaut_catalog
+WHERE dw_sku IN (
+ 'DWTT-81381', 'DWTT-81391', 'DWTT-81401', 'DWTT-81411', 'DWTT-81421',
+ 'DWTT-81521', 'DWTT-81531', 'DWTT-81541', 'DWTT-81551', 'DWTT-81561',
+ 'DWTT-81571'
+);
+
+-- Exact manufacturer-SKU matches in FileMaker. These are leads, not approved
+-- prices: supplier, unit, freshness, and cost basis must all be verified first.
+WITH fm AS (
+ SELECT *,
+ upper(regexp_replace(COALESCE(mfr_pattern, ''), '[^A-Z0-9]', '', 'g'))
+ AS normalized_mfr_sku
+ FROM fm_wallpaper_live
+), cohort AS (
+ SELECT t.product_id, t.sku, m.mfr_sku,
+ upper(regexp_replace(COALESCE(m.mfr_sku, ''), '[^A-Z0-9]', '', 'g'))
+ AS normalized_mfr_sku
+ FROM tk10456_cohort t
+ JOIN mdc_catalog m ON m.shopify_product_id::text = t.product_id::text
+ WHERE t.vendor = 'Phillipe Romano'
+)
+SELECT c.product_id, c.sku, c.mfr_sku, fm.supplier, fm.combo_sku,
+ fm.mfr_pattern, fm.fm_width, fm.fm_minimum, fm.date_line,
+ fm.retail, fm.cost, fm.net, fm.refreshed_at,
+ CASE
+ WHEN fm.supplier IS NULL OR btrim(fm.supplier) = '' THEN 'BLOCK_supplier_missing'
+ WHEN fm.date_line IS NULL THEN 'BLOCK_source_date_missing'
+ ELSE 'REVIEW_cost_basis_and_unit'
+ END AS disposition
+FROM cohort c
+JOIN fm USING (normalized_mfr_sku)
+WHERE COALESCE(fm.cost, fm.net, fm.retail) IS NOT NULL
+ORDER BY c.sku;
diff --git a/scripts/tk10456/verification/e2e-proof.json b/scripts/tk10456/verification/e2e-proof.json
new file mode 100644
index 0000000..be1ec1b
--- /dev/null
+++ b/scripts/tk10456/verification/e2e-proof.json
@@ -0,0 +1,40 @@
+{
+ "intent": "Detect newly authoritative prices for the frozen TK-10456 892-product cohort without mutating canonical data or Shopify.",
+ "risk_tier": "R1",
+ "environment": "local dw_unified via /tmp PostgreSQL socket",
+ "timestamp": "2026-08-31T18:25:00Z",
+ "build_identity": "scripts/tk10456/probe-recovery.sql working tree; committed after verification",
+ "baseline": "62 products complete; 830 unresolved in the frozen classification CSV",
+ "commands": [
+ "psql host=/tmp dbname=dw_unified -X -v cohort_csv=<classification.csv> -f scripts/tk10456/probe-recovery.sql"
+ ],
+ "checks": [
+ {
+ "boundary": "manifest",
+ "assertion": "Frozen cohort loads exactly 892 rows and reconciles to 62 done plus 830 unresolved",
+ "verdict": "PASS"
+ },
+ {
+ "boundary": "canonical vendor catalogs",
+ "assertion": "DWPP, DWPF, DWAA, and exact DWTT-11 sources expose no usable price",
+ "verdict": "PASS"
+ },
+ {
+ "boundary": "alternative FileMaker source",
+ "assertion": "Nine exact normalized manufacturer-SKU matches are surfaced and fail closed as BLOCK_supplier_missing",
+ "verdict": "PASS"
+ },
+ {
+ "boundary": "side effects",
+ "assertion": "Only a PostgreSQL TEMP table is created; canonical tables and Shopify are unchanged",
+ "verdict": "PASS"
+ }
+ ],
+ "negative_checks": [
+ "Missing cohort_csv exits before analysis",
+ "Blank supplier never promotes a FileMaker lead to a usable price",
+ "No fallback or guessed pricing formula is applied"
+ ],
+ "cleanup": "Temporary table is session-scoped and removed automatically on disconnect; no retained test state.",
+ "verdict": "PASS for the recovery probe; TK-10456 remains externally blocked for activation."
+}
← 71d3f69 auto-data-snapshot: 2026-08-31T11:17:15 (2 data files) — scr
·
back to Designerwallcoverings
·
TK-00058 fix: rename const URL->API_URL in fentucci clear (T a150b98 →