← back to Wallco Ai

docs/ops/dw-stale-urls-push-A2-2026-05-19.md

87 lines

# DW Stale URLs — Push A2 (2026-05-19)

Parking-lot item: **dw-stale-urls substep A** (push verified image-URL swaps to Shopify).

## Inputs

- Staging table: `dw_unified.image_url_swap_proposals_2026_05_19` (4,989 rows, 625 with `new_url` populated).
- Shopify store: `designer-laboratory-sandbox.myshopify.com` (API `2026-01`).
- Token: `SHOPIFY_ADMIN_TOKEN` from `~/Projects/secrets-manager/.env`.

## Pre-flight filters

| Filter | Rows |
|---|---|
| Initial swap candidates (`new_url IS NOT NULL`) | 625 |
| Competitor-CDN rejected (`source_table='connie_competitor_catalog'` → `(competitor-removed)`) | -102 |
| HEAD-check failed (404; → `(dead-on-check)`) | -4 |
| **Final push set** | **519** |

HEAD-check ran at concurrency=20 with an 8 s timeout, GET-range fallback for 405/403. Stored on the staging table as `head_check_status int` + `head_checked_at timestamptz`. 519/523 returned HTTP 200; 4 returned 404 and were dropped.

## Backup

```sql
CREATE TABLE shopify_image_url_backup_2026_05_19 AS
SELECT id, shopify_id, handle, image_url
FROM shopify_products sp
WHERE EXISTS (
  SELECT 1 FROM image_url_swap_proposals_2026_05_19 p
  WHERE p.product_id = sp.id AND p.new_url IS NOT NULL
);
```

Row count: **519** (matches push set).

## Push outcomes

| Outcome | Count |
|---|---|
| **Succeeded** (Shopify image POST + set as primary) | **53** |
| **Matched** (Shopify primary already equalled `new_url` — no Shopify call needed, local mirror synced) | **417** |
| **Skipped — 404 on Shopify** (product deleted-on-Shopify; row left untouched) | **49** |
| **Failed** | **0** |
| **Aborted batches** | 0 |
| **Delete warnings** (old broken image found and removed) | 0 |

- 470 local-mirror rows (`shopify_products.image_url`) updated to match the new URL.
- 49 product IDs are stale on the local mirror — they were deleted from Shopify and need separate cleanup (out of scope for this push).
- Elapsed: **642 s** (~10 min 42 s) for 519 rows across 11 batches of 50 with a 5 s pause between batches. Per-call delay 550 ms; no 429s observed; no exponential backoff triggered.

## Push strategy (per row)

1. `GET /admin/api/2026-01/products/{id}/images.json`
2. If 404 → mark `skipped_404` (product gone from Shopify).
3. If primary image `src == new_url` → `match` (Shopify already correct).
4. Else: `POST .../images.json` with `{image:{src:new_url, position:1}}`, then `PUT /products/{id}.json` with `{product:{id, image:{id:new_image_id}}}` to set as primary, then `DELETE` any image whose `src == old_url`.
5. After ok/match → `UPDATE shopify_products SET image_url=new_url WHERE id=product_id` (local mirror sync).

## Sample diffs (10 random successful pushes)

| product_id | old_url | new_url |
|---|---|---|
| 21527 | …/products/br… | …/files/61028… |
| 21550 | …/products/co… | …/files/61079… |
| 11790 | …/products/ae… | https://marburg.com/wp-content/uploads/2025/07/47221-1.webp |
| 21689 | …/products/ca… | …/files/61042… |
| 17230 | …/products/29… | …/files/7821-… |
| 21554 | …/products/co… | …/files/61083… |
| 21528 | …/products/br… | …/files/61029… |
| 21697 | …/products/ca… | …/files/61051… |
| 21551 | …/products/co… | …/files/61080… |
| 21539 | …/products/Ce… | …/files/61055… |

Bulk of swaps move from Shopify's older `/products/<hash>.jpg` URL pattern (legacy Shopify CDN) to the newer `/files/<slug>.jpg` pattern. 11 of the 519 swap to external vendor CDNs (e.g. `marburg.com`).

## Files

- HEAD-check script: `~/Projects/wallco-ai/scripts/dw-stale-urls-head-check.js`
- Push script: `~/Projects/wallco-ai/scripts/dw-stale-urls-push.js`
- Results JSON: `/tmp/dw-stale-urls/push-results.json` (full per-row outcome list)
- Backup table: `dw_unified.shopify_image_url_backup_2026_05_19`

## Follow-ups

- **49 deleted-on-Shopify products** still occupy rows in `shopify_products` with broken `image_url`. Suggest a `sweep-deleted-shopify-products` job to soft-delete those local rows.
- **4,364 rows still without a `new_url`** in the proposals table — need vendor re-scrape (out of scope for substep A, queued as substep B / parking-lot continuation).