`docs/ops/dw-stale-urls-trim-B-2026-05-19.md`

87 lines
# dw-stale-urls trim-B — HALTED at safety check

**Date:** 2026-05-19
**Operator:** Claude (parking-lot dw-stale-urls substep B)
**Outcome:** NO DELETE — orphan count outside safety band, awaiting Steve.

## Result

- Bad rows in `image_hashes` (status != 'success'): **16,713**
- Distinct referenced URLs across all 177 tables with `image_url`: **651,962**
- **Orphan bad rows (URL not referenced anywhere): 0**
- Safety band per task spec: 9,120 – 13,680 (±20% of 11,400 estimate)
- 0 is **100% below** the lower bound → STOP, per hard constraint.

## SQL run

```sql
-- Discover tables with image_url column (177 found, includes shopify_products + 176 *_catalog / catalog-adjacent tables)
SELECT table_name
FROM information_schema.columns
WHERE column_name='image_url' AND table_schema='public'
ORDER BY table_name;

-- Orphan count
WITH all_referenced_urls AS (
  SELECT image_url AS url FROM _tsd_seed WHERE image_url IS NOT NULL
  UNION ALL SELECT image_url FROM all_designs WHERE image_url IS NOT NULL
  -- … 175 more UNION ALL legs, one per table …
  UNION ALL SELECT image_url FROM zoffany_catalog WHERE image_url IS NOT NULL
)
SELECT count(*)
FROM image_hashes ih
WHERE ih.status != 'success'
  AND NOT EXISTS (SELECT 1 FROM all_referenced_urls a WHERE a.url = ih.url);
-- → 0
```

## Sanity-check trace

To verify the orphan query was not silently failing, picked one bad URL and grepped every catalog table individually:

```
URL: /_assets/07d0a465f98edc0af823532f8bdd8abc/Images/ImageNotFound.jpg
  HIT: legal_scan_cache = 1
  HIT: vc_public_eligible = 1
  HIT: vendor_catalog = 1
  HIT: versa_catalog = 1
```

Conclusion: the join is correct. Every bad URL in `image_hashes` IS referenced by at least one row somewhere in the 177 tables — most often vendor catalog tables that legitimately store the (now-broken) vendor URL.

## Status breakdown of the 16,713 bad rows

| status        | rows  |
|---------------|------:|
| http_404      | 16132 |
| http_401      |   178 |
| http_403      |   143 |
| decode_error  |   142 |
| fetch_error   |    50 |
| http_502      |    24 |
| too_small     |    13 |
| http_524      |     8 |
| http_521      |     8 |
| invalid_url   |     7 |
| timeout       |     5 |
| http_523      |     2 |
| http_522      |     1 |

## Why the estimate was wrong

The 11,400 estimate assumed bad URLs would be old historical entries no longer tied to any catalog row. In reality, `image_hashes` is checked against the *current* catalog mirrors — when a vendor URL 404s, the row stays in both `image_hashes` (negative cache) AND the catalog tables (the catalog still records what vendor URL was scraped, even after it broke). So the negative-cache rows are not orphans — they're the catalog's own broken-link entries.

## Recommendation for Steve

Trim-B as defined ("pure orphans, nowhere in any catalog") deletes nothing. If the goal is "remove negative-cache rows that no longer matter," the policy needs reframing — e.g.:

- **Option 1 — delete by age:** drop `status != 'success'` rows older than N days (`fetched_at < now() - interval 'N days'`), regardless of catalog references. Counts as of today: 16,713 total — would need date histogram to size.
- **Option 2 — delete by status:** drop only `http_404` (16,132 rows, 96% of all bad rows). These are permanently broken vendor URLs; keeping the negative cache only matters if you want to re-probe them. If re-probing is off, they're noise.
- **Option 3 — leave it alone:** 16,713 rows × ~64 B/row ≈ 1 MB. Not worth the operational risk.

Awaiting Steve's call before any DELETE. No backup taken, no temp table staged, `image_hashes` untouched.

## Time elapsed

~3 min from task start to halt (mostly the spot-check pass + writing this report).