Fix Category Product Counts ✅ DONE
Problem
All ~41 categories had product_count = 0 despite 715 real Woolworths products existing in the DB. Root cause: import_category_products method fails to persist updated counts on already-existing categories (ORM flush/commit timing issue).
Action Taken
Executed scripts/fix_category_counts.py — a one-time SQL migration script that runs:
UPDATE categories SET product_count = (
SELECT COUNT(*) FROM products p
WHERE p.category_id = categories.id AND p.store_id = categories.store_id
);Results
- Before: Stored total = 0, Actual = 715, Difference = -715
- After: Stored total = 715, Actual = 715, Difference = +0
- Categories updated: 41 (20 had real products, 21 already at 0 correctly)
Verification
Dry-run after fix confirms 0 remaining changes. check_category_coverage.py shows all API counts match actual product counts for Woolworths categories. No gaps remain.
DB State (post-fix)
| Table | Count | Notes |
|---|---|---|
| Stores | 2 | Coles (id=1), Woolworths (id=2) |
| Categories | ~41 | ✅ product_count now accurate |
| Products | 715 | All Woolworths |
| Price checks | ~2,526 | Unchanged |
Notes
- The fix is applied at the DB level. The importer code bug still exists — any future re-import that creates duplicate products would need the ORM-level fix. However, with the counts now correct, the API endpoints should return proper data.
- Coles scraper remains BLOCKED (Imperva WAF). This is a procurement decision for pvs.