← back to Wallco Ai

data/yolo-overnight/dw-unified-sanity-20260525-1243.md

136 lines

# dw_unified Sanity Report

- **Generated**: 2026-05-25 05:43 PT (12:43 UTC)
- **DB**: `dw_unified` on `127.0.0.1:5432`
- **pg_stat_database.stats_reset**: 2026-05-20 13:18 PT — counters reflect ~4d 16h of activity, so `idx_scan = 0` here means "not used since last reset," not "never used."

## 1. Row counts — key tables

| Table | Rows |
|---|---:|
| `spoon_all_designs` | 341 |
| `wallco_trade_users` | 2 |
| `wallco_saved_designs` | **0** |
| `wallco_audit_log` | 54 |
| `design_ratings` | 1 |
| `studio_renders` | 7 |

**Observations**
- `wallco_saved_designs` is empty — confirm whether the feature has shipped or saves aren't being persisted. Likely expected pre-launch but worth a sanity check against the app's "save design" path.
- `design_ratings` (1) and `studio_renders` (7) are extremely low — early adoption volumes. No data integrity concern, just usage.
- `spoon_all_designs` at 341 matches a manually curated catalog scale, not a full Spoonflower harvest.

## 2. Invalid indexes — ACTION REQUIRED

```
     index_name      |    table_name    | indisvalid | indisready
---------------------+------------------+------------+------------
 idx_sp_handle_lower | shopify_products | f          | f
```

- One invalid index: `idx_sp_handle_lower` on `shopify_products`.
- `indisvalid=f AND indisready=f` means a failed `CREATE INDEX CONCURRENTLY` left a stub behind. It's not used by the planner and not maintained on writes — pure garbage.
- **Recommended fix** (non-destructive, safe):
  ```sql
  DROP INDEX CONCURRENTLY IF EXISTS idx_sp_handle_lower;
  -- then, if a lower(handle) lookup is actually needed:
  CREATE INDEX CONCURRENTLY idx_sp_handle_lower ON shopify_products (lower(handle));
  ```
  Per escalation rules, not executing — flagging for approval since it touches `shopify_products`.

## 3. Unused indexes — public schema

| Metric | Value |
|---|---:|
| Total user indexes (public) | 1,402 |
| Unused since stats_reset (`idx_scan = 0`) | **1,286 (91.7%)** |
| Bytes held by unused indexes | **2,004 MB** |
| Bytes held by all user indexes | 2,172 MB |
| Bytes held by **top-20 unused** indexes alone | 1,133 MB (56%) |

**Caveats before deleting anything:**
1. Stats window is only ~4.7 days. Cron/weekly/monthly queries may not have fired yet.
2. Primary-key indexes show `idx_scan=0` when the table is only read by sequential scan (tiny tables) or written-only (audit logs). Dropping a PK breaks future point-lookups and replication.
3. The big offenders are append-only / write-heavy tables where indexes serve future queries that just haven't been run in the window.

### Top 25 unused indexes by size

| Table | Index | Size | Notes |
|---|---|---:|---|
| `silas_audit_log` | `idx_silas_audit_log_timestamp` | 240 MB | write-heavy audit; index probably used for cleanup queries — keep |
| `silas_audit_log` | `silas_audit_log_pkey` | 134 MB | **do not drop** — primary key |
| `annie_price_history` | `annie_price_history_pkey` | 97 MB | **do not drop** — primary key |
| `annie_price_history` | `idx_aph_recorded` | 97 MB | time-series index, used by reports |
| `shopify_products` | `idx_shopify_metafields` | 91 MB | likely GIN on JSONB — verify before dropping |
| `ken_sentiment` | `ken_sentiment_pkey` | 50 MB | **do not drop** |
| `ken_sentiment` | `idx_ken_sent_time` | 49 MB | time-series |
| `silas_audit_log` | `idx_silas_audit_log_action` | 42 MB | candidate to drop if action filter unused |
| `silas_audit_log` | `idx_silas_audit_log_vendor` | 40 MB | candidate to drop if vendor filter unused |
| `shopify_products` | `idx_shopify_products_title_trgm` | 38 MB | trigram search — used by admin search UIs |
| `shopify_products` | `idx_shopify_products_handle_trgm` | 35 MB | same — keep |
| `annie_price_history` | `idx_aph_shopify` | 32 MB | |
| `annie_opportunities` | `annie_opportunities_pkey` | 32 MB | **do not drop** |
| `annie_price_history` | `idx_aph_numeric` | 31 MB | |
| `image_hashes` | `image_hashes_pkey` | 28 MB | **do not drop** |
| `ken_sentiment` | `idx_ken_sent_ticker` | 20 MB | |
| `vendor_review_events` | `vendor_review_events_pkey` | 20 MB | **do not drop** |
| `vendor_review_events` | `idx_vendor_review_events_created_at` | 20 MB | |
| `ken_sentiment` | `idx_ken_sent_source` | 19 MB | |
| `shopify_products` | `shopify_products_shopify_id_key` | 18 MB | unique constraint — **do not drop** |
| `product_pricing` | `idx_product_pricing_updated` | 16 MB | |
| `ken_event_sentiment` | `ken_event_sentiment_pkey` | 15 MB | **do not drop** |
| `ken_event_sentiment` | `idx_ken_evtsent_time` | 15 MB | |
| `shopify_products` | `idx_shopify_products_handle_upper` | 14 MB | duplicates `_handle_trgm` purpose — likely safe to drop after extending stats window |
| `pulse_articles` | `pulse_articles_url_key` | 13 MB | unique constraint — **do not drop** |

Full list (1,286 rows, 185 KB): see persisted output `/root/.claude/projects/-root-DW-Agents/a2e9ff0a-6bc4-4473-b7e2-65e508fa20fb/tool-results/beepxh0fx.txt`.

## 4. Wallco/Spoon/Studio table indexes — focused view

All 21 indexes across the six target tables. Tiny tables → seq scans win → low `idx_scan` is normal.

| Table | Index | idx_scan | Size |
|---|---|---:|---|
| design_ratings | design_ratings_design_idx | 59 | 16 kB |
| design_ratings | design_ratings_pkey | 0 | 16 kB |
| spoon_all_designs | idx_spoon_designs_dig | 0 | 16 kB |
| spoon_all_designs | idx_spoon_designs_product_line | 0 | 16 kB |
| spoon_all_designs | idx_spoon_designs_verticals | 0 | 24 kB |
| spoon_all_designs | idx_spoon_parent | 7 | 16 kB |
| spoon_all_designs | idx_spoon_source_sku | 0 | 16 kB |
| spoon_all_designs | spoon_all_designs_pkey | 5,879 | 32 kB |
| spoon_all_designs | spoon_all_designs_user_removed_idx | 6 | 16 kB |
| studio_renders | studio_renders_design_idx | 0 | 16 kB |
| studio_renders | studio_renders_pkey | 0 | 16 kB |
| studio_renders | studio_renders_session_idx | 3 | 16 kB |
| wallco_audit_log | wallco_audit_action_idx | 0 | 16 kB |
| wallco_audit_log | wallco_audit_admin_idx | 0 | 16 kB |
| wallco_audit_log | wallco_audit_log_pkey | 0 | 16 kB |
| wallco_audit_log | wallco_audit_user_idx | 1 | 16 kB |
| wallco_saved_designs | wallco_saved_designs_pkey | 57 | 8 kB |
| wallco_saved_designs | wallco_saved_designs_user_idx | 169 | 8 kB |
| wallco_trade_users | wallco_trade_users_email_key | 6 | 16 kB |
| wallco_trade_users | wallco_trade_users_pkey | 14 | 16 kB |
| wallco_trade_users | wallco_trade_users_token_idx | 6 | 16 kB |

**Observations**
- `spoon_all_designs_pkey` has 5,879 scans — the catalog is being read heavily by PK lookup. Healthy.
- `wallco_saved_designs_user_idx` already has 169 scans even though the table is empty — the app is querying it on every page load; just no inserts yet. Confirms the "feature wired but no saves" hypothesis.
- `wallco_audit_log` indexes are mostly cold — fine, audit logs are write-mostly.
- No broken indexes among the six target tables.

## 5. Summary

| Check | Status |
|---|---|
| Row counts captured | OK — all six tables present, `wallco_saved_designs` empty |
| Invalid indexes | **1 found** — `idx_sp_handle_lower` on `shopify_products` (drop recommended, not executed) |
| Unused indexes | 1,286/1,402 (91.7%) cold over 4.7-day window — interpret cautiously; do NOT bulk-drop |
| Wallco/Spoon/Studio table indexes | Healthy; no broken or duplicate-looking entries |

## Recommended next actions (not executed — flagging per escalation rules)

1. **Drop the invalid index** `idx_sp_handle_lower` (one-line, low risk).
2. **Let pg_stat counters age** another 2–3 weeks before any further index pruning. Re-run this sanity check on 2026-06-15 and compare.
3. If `wallco_saved_designs` is supposed to have data: trace the save endpoint and confirm writes are reaching it.