← back to Wallco Ai
report: dw-stale-urls trim-B HALTED (0 orphans, outside band, awaiting Steve)
fd7b0dcdcac593065a9687d982a703a88a73a3ed · 2026-05-19 21:16:10 -0700 · SteveStudio2
Files touched
A reports/dw-stale-urls-trim-B-2026-05-19.md
Diff
commit fd7b0dcdcac593065a9687d982a703a88a73a3ed
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Tue May 19 21:16:10 2026 -0700
report: dw-stale-urls trim-B HALTED (0 orphans, outside band, awaiting Steve)
---
reports/dw-stale-urls-trim-B-2026-05-19.md | 86 ++++++++++++++++++++++++++++++
1 file changed, 86 insertions(+)
diff --git a/reports/dw-stale-urls-trim-B-2026-05-19.md b/reports/dw-stale-urls-trim-B-2026-05-19.md
new file mode 100644
index 0000000..744670f
--- /dev/null
+++ b/reports/dw-stale-urls-trim-B-2026-05-19.md
@@ -0,0 +1,86 @@
+# 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).
← a109d4f refiner-compare: 3 more render columns (Gemini 2.5 Flash Ima
·
back to Wallco Ai
·
report: dw-stale-urls heal-A audit (4989 products, propose s ed88f83 →