report: dw-stale-urls heal-A audit (4989 products, propose swaps)

ed88f83f5aeb41d46c8660b8a6af1f6e81a51a22 · 2026-05-19 21:17:04 -0700 · SteveStudio2
Files touched

A reports/dw-stale-urls-heal-A-2026-05-19.md
Diff

commit ed88f83f5aeb41d46c8660b8a6af1f6e81a51a22
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Tue May 19 21:17:04 2026 -0700

    report: dw-stale-urls heal-A audit (4989 products, propose swaps)
---
 reports/dw-stale-urls-heal-A-2026-05-19.md | 143 +++++++++++++++++++++++++++++
 1 file changed, 143 insertions(+)

diff --git a/reports/dw-stale-urls-heal-A-2026-05-19.md b/reports/dw-stale-urls-heal-A-2026-05-19.md
new file mode 100644
index 0000000..f7e6c32
--- /dev/null
+++ b/reports/dw-stale-urls-heal-A-2026-05-19.md
@@ -0,0 +1,143 @@
+# DW Stale URLs — Heal Substep A audit
+
+**Date:** 2026-05-19
+**Database:** `dw_unified`
+**Staging table:** `image_url_swap_proposals_2026_05_19`
+**Scope:** Identify candidate replacement `image_url` values for the 4,989 Shopify products whose live hero image currently points at a URL that the negative-fetch-cache (`image_hashes`) has marked as not-success.
+
+---
+
+## TL;DR
+
+| Metric | Count |
+|---|---:|
+| Affected Shopify products (broken hero image) | **4,989** |
+| Products with a candidate swap URL | **625** (12.5%) |
+| Products needing a fresh vendor re-scrape | **4,364** (87.5%) |
+| Vendor catalog tables searched | 127 |
+| Distinct source tables contributing swap candidates | 10 |
+
+**Headline:** A clean URL exists for only ~1 in 8 broken Shopify products from data already in dw_unified. The remaining 87.5% need a new vendor crawl — for ~3,500 of those (Romo, Ralph Lauren Fabric, Glitter Walls, Arte) the matching vendor-catalog row carries the same dead URL as Shopify, confirming the source images are gone, not just stale on Shopify side.
+
+---
+
+## Methodology
+
+1. **Bad URL set.** `SELECT url FROM image_hashes WHERE status <> 'success'` → 16,713 URLs.
+2. **Affected products.** `shopify_products` rows whose `image_url` is in that bad set → 4,989 rows.
+3. **Candidate pool.** UNION ALL across all 127 `*_catalog` tables that have an `image_url` column, normalizing each row to `(source_table, mfr_sku, dw_sku, image_url)`. 448,714 candidate rows.
+4. **Match.** Each affected product is joined to the candidate pool on `mfr_sku` OR `dw_sku`. Joining on `shopify_product_id` was rejected — catalogs store the numeric ID while shopify_products stores the GraphQL gid:// form. (Note: `fentucci_naturals_catalog` has no `mfr_sku` column; joins for it use `dw_sku` only.)
+5. **Bad-cache filter.** A candidate URL is dropped if it already appears in `image_hashes` with a non-success status. Same-as-old URLs are also dropped.
+6. **Tie-break.** Per affected product, `row_number() OVER (PARTITION BY product_id ORDER BY mfr_sku-match-first, source_table)` → at most one candidate kept.
+7. **Insert.** All 4,989 affected products are written to `image_url_swap_proposals_2026_05_19`. Products with no surviving candidate get `new_url=NULL, source_table='(none)'`.
+
+Hard constraints honored: no writes to `shopify_products`, no Shopify Admin API calls, no deletes from `image_hashes`. The only production-data change is the new staging table.
+
+---
+
+## Per-vendor breakdown
+
+| Vendor | Broken | Swap candidate | Need re-scrape |
+|---|---:|---:|---:|
+| Hollywood Wallcoverings | 1598 | 464 | 1134 |
+| Ralph Lauren Fabric | 1055 | 0 | 1055 |
+| Romo | 930 | 0 | 930 |
+| Nina Campbell | 445 | 102 | 343 |
+| Glitter Walls | 237 | 0 | 237 |
+| Ralph Lauren Wallpaper | 200 | 35 | 165 |
+| Ralph Lauren Fabrics | 144 | 0 | 144 |
+| Arte International | 133 | 0 | 133 |
+| Ralph Lauren | 122 | 11 | 111 |
+| Innovations USA | 52 | 0 | 52 |
+| Designer Wallcoverings | 42 | 0 | 42 |
+| Innovations | 4 | 4 | 0 |
+| DW Bespoke Studio | 4 | 1 | 3 |
+| Phillipe Romano | 4 | 0 | 4 |
+| Thibaut | 3 | 0 | 3 |
+| Phillip Jeffries | 3 | 3 | 0 |
+| Schumacher Wallpaper | 2 | 0 | 2 |
+| Laura Ashley Wallpaper | 2 | 1 | 1 |
+| Pixels | 1 | 0 | 1 |
+| Fentucci | 1 | 0 | 1 |
+| Mind the Gap | 1 | 0 | 1 |
+| Gaston Y Daniela | 1 | 0 | 1 |
+| Brunschwig & Fils | 1 | 1 | 0 |
+| Cole & Son | 1 | 1 | 0 |
+| Boråstapeter | 1 | 1 | 0 |
+| DW Bespoke Studios To Go | 1 | 1 | 0 |
+
+### Why so many "need re-scrape"
+
+A diagnostic on Romo (0/930 swap rate) confirmed all 930 broken Shopify rows DO have a matching `romo_catalog` row by `mfr_sku`/`dw_sku`, but the catalog's own `image_url` is *also* in the bad cache. The catalog and Shopify share the same upstream dead URL — a re-scrape against the live Romo PDP is required, not a swap. The same shape is true for Ralph Lauren Fabric (1,055), Glitter Walls (237), Arte International (133), and the smaller need-rescrape buckets.
+
+The 1,134 Hollywood "need re-scrape" rows are different — Hollywood is the private-label vendor whose source images were re-shot/re-cropped; the original CDN paths under `/products/` were superseded by `/files/` paths. The 464 successful Hollywood swaps already pull from those newer `/files/` URLs via `vendor_catalog`.
+
+### Source-table contributions to the 625 swaps
+
+| Source table | Swaps contributed |
+|---|---:|
+| vendor_catalog | 431 |
+| connie_competitor_catalog | 102 |
+| newwall_catalog | 40 |
+| sancar_catalog | 27 |
+| marburg_catalog | 9 |
+| koroseal_catalog | 6 |
+| bespoke_catalog | 4 |
+| nina_campbell_catalog | 4 |
+| schumacher_catalog | 1 |
+| kravet_catalog | 1 |
+
+`connie_competitor_catalog` is notable — it provided 102 Nina Campbell swaps from competitor-tracked URLs (different Shopify shop CDN bucket, `/0597/0549/1612/`). Worth double-checking these visually before pushing, since they originate outside DW's own scrape.
+
+---
+
+## Sample — 20 proposed swaps
+
+| product_id | vendor | sku | old_url (trunc) | new_url (trunc) | source_table |
+|---:|---|---|---|---|---|
+| 6282 | Hollywood Wallcoverings | `FRS28-111` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/cce31e4126e5…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/dwx-58092-sampl…` | `vendor_catalog` |
+| 21696 | Hollywood Wallcoverings | `61050` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/cassina-swat…` | `https://cdn.shopify.com/s/files/1/0758/1584/6168/files/61050.jpg?v=169…` | `newwall_catalog` |
+| 6328 | Hollywood Wallcoverings | `FRS32-141` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/DWX-58138-clean…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/dwx-58138-sampl…` | `vendor_catalog` |
+| 50051 | Nina Campbell | `NCW4394-05` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/ncw4394-05_5…` | `https://cdn.shopify.com/s/files/1/0597/0549/1612/files/NCW4394-05.jpg?…` | `connie_competitor_catalog` |
+| 13361 | Hollywood Wallcoverings | `52093` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/ac8ebbac2651…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/capulet-jade.jp…` | `vendor_catalog` |
+| 20199 | Ralph Lauren Wallpaper | `20018` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/de11c9a87f7c…` | `https://cdn.shopify.com/s/files/1/0682/6917/9132/files/download_3f4f38…` | `sancar_catalog` |
+| 11690 | Hollywood Wallcoverings | `47121` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/0437bfc82e15…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/xjg-47121-sampl…` | `vendor_catalog` |
+| 13603 | Hollywood Wallcoverings | `52354` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/4f03138618b9…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/passage-citron_…` | `vendor_catalog` |
+| 6325 | Hollywood Wallcoverings | `FRS32-120` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/DWX-58135-clean…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/dwx-58135-sampl…` | `vendor_catalog` |
+| 21508 | Hollywood Wallcoverings | `61007` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/ashlarblock-…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/hwc-61007-sampl…` | `vendor_catalog` |
+| 6247 | Hollywood Wallcoverings | `FRS26-104` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/24b850532c8d…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/dwx-58057-sampl…` | `vendor_catalog` |
+| 20221 | Ralph Lauren Wallpaper | `20043` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/e45cdbee9f4d…` | `https://cdn.shopify.com/s/files/1/0682/6917/9132/files/download_5f3481…` | `sancar_catalog` |
+| 13356 | Hollywood Wallcoverings | `52088` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/3ded1b0a2687…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/capulet-butter.…` | `vendor_catalog` |
+| 11706 | Hollywood Wallcoverings | `47137` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/6e399f4e116e0ef…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/xjg-47137-sampl…` | `vendor_catalog` |
+| 21707 | Hollywood Wallcoverings | `61232` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/toban-swatch…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/hwc-61232-sampl…` | `vendor_catalog` |
+| 11693 | Hollywood Wallcoverings | `47124` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/9db957a60cd6…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/xjg-47124-sampl…` | `vendor_catalog` |
+| 6343 | Hollywood Wallcoverings | `FRS35-147` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/40ec4d2c1a31…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/files/dwx-58153-sampl…` | `vendor_catalog` |
+| 49950 | Nina Campbell | `NCW4300-02` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/ncw4300-02_0…` | `https://cdn.shopify.com/s/files/1/0597/0549/1612/files/NCW4300-02.jpg?…` | `connie_competitor_catalog` |
+| 2598 | Ralph Lauren | `74643` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/b89c1c0fdae0…` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/74643_cd2cd5…` | `vendor_catalog` |
+| 50060 | Nina Campbell | `NCW4396-03` | `https://cdn.shopify.com/s/files/1/0015/4117/7456/products/ncw4396-03_6…` | `https://cdn.shopify.com/s/files/1/0597/0549/1612/files/NCW4396-03.jpg?…` | `connie_competitor_catalog` |
+
+---
+
+## Approve & push workflow (for Steve)
+
+This report does NOT push anything. To actually heal the live storefront:
+
+1. **Spot-check 20 swaps.** `SELECT * FROM image_url_swap_proposals_2026_05_19 WHERE new_url IS NOT NULL ORDER BY random() LIMIT 20` — eyeball that `new_url` looks plausible for the pattern/vendor.
+2. **HEAD-probe candidates (optional but recommended).** Run a one-time HEAD request on each `new_url` to verify 200 + content-type=image/* before pushing. Cache those into `image_hashes` with status='success'.
+3. **Push approved swaps.** When ready, write a separate updater that:
+   - Reads `image_url_swap_proposals_2026_05_19` WHERE `new_url IS NOT NULL`.
+   - For each row, calls Shopify Admin API `productUpdate` (or `productImageUpdate`) on `product_id` to swap the hero. Token `SHOPIFY_ADMIN_TOKEN` in `~/Projects/secrets-manager/.env`, store `designer-laboratory-sandbox.myshopify.com`.
+   - Updates `shopify_products.image_url` to match.
+   - Marks `image_url_swap_proposals_2026_05_19.pushed_at = now()` (column to be added before push).
+4. **Re-scrape the 4,364.** For `new_url IS NULL` rows, group by `vendor` and dispatch the vendor's scraper-manager skill to re-fetch the PDP. Update both the vendor `*_catalog` and `shopify_products.image_url` once a fresh URL is in hand.
+5. **Don't bulk-mass-push without sampling.** The 102 Nina Campbell swaps come from a competitor-scrape table — visually verify a sample before pushing those specifically.
+
+---
+
+## Files
+
+- Staging table: `image_url_swap_proposals_2026_05_19` in `dw_unified`
+- Build script: `/tmp/dw-stale-urls/build.sh` (generates dynamic UNION across all 127 catalogs)
+- Generated SQL: `/tmp/dw-stale-urls/main.sql` (346 lines, idempotent — `DROP TABLE IF EXISTS` at top)
+- This report: `~/Projects/wallco-ai/reports/dw-stale-urls-heal-A-2026-05-19.md`
+
← fd7b0dc report: dw-stale-urls trim-B HALTED (0 orphans, outside band · back to Wallco Ai · decision: dw-stale-urls trim-B → leave-it (negative cache is 9fb3d02 →