`docs/ops/dw-stale-urls-heal-A-2026-05-19.md`

144 lines
# 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`