# Fix Missing FK Indexes Plan

## Problem
10 foreign key columns lack indexes, causing slow JOINs and cascading deletes. When querying "get all notes for lead X" or "delete lead and cascade", PostgreSQL must do full table scans instead of index lookups.

**Impact:**
- Slow queries when fetching related records (notes, conversions, sequence enrollments)
- Slow cascading deletes when removing leads
- Database lock contention under load

## Solution
Add indexes to all FK columns that reference parent tables.

## Missing Indexes (10 total)

| # | Table | Column | References | Priority |
|---|-------|--------|------------|----------|
| 1 | lead_notes | lead_id | leads(id) | HIGH - frequently queried |
| 2 | conversions | lead_id | leads(id) | HIGH - used in reports |
| 3 | sequence_enrollments | lead_id | leads(id) | HIGH - checked on every lead view |
| 4 | sequence_enrollments | sequence_id | sequences(id) | MEDIUM |
| 5 | sequence_steps | sequence_id | sequences(id) | MEDIUM |
| 6 | sequence_steps | template_id | sms_templates(id) | LOW |
| 7 | call_recordings | communication_id | communications(id) | MEDIUM |
| 8 | voice_commands | affected_lead_id | leads(id) | LOW - rarely queried |
| 9 | ideas | lead_id | leads(id) | LOW - optional FK |
| 10 | detected_friction | linked_suggestion_id | intelligence_suggestions(id) | LOW |

## Database Migration

```sql
-- Fix Missing FK Indexes
-- Safe to run multiple times (IF NOT EXISTS)

-- HIGH PRIORITY: Frequently queried tables
CREATE INDEX IF NOT EXISTS idx_lead_notes_lead_id ON lead_notes(lead_id);
CREATE INDEX IF NOT EXISTS idx_conversions_lead_id ON conversions(lead_id);
CREATE INDEX IF NOT EXISTS idx_sequence_enrollments_lead_id ON sequence_enrollments(lead_id);

-- MEDIUM PRIORITY: Sequence system
CREATE INDEX IF NOT EXISTS idx_sequence_enrollments_sequence_id ON sequence_enrollments(sequence_id);
CREATE INDEX IF NOT EXISTS idx_sequence_steps_sequence_id ON sequence_steps(sequence_id);
CREATE INDEX IF NOT EXISTS idx_call_recordings_communication_id ON call_recordings(communication_id);

-- LOW PRIORITY: Less frequently queried
CREATE INDEX IF NOT EXISTS idx_sequence_steps_template_id ON sequence_steps(template_id);
CREATE INDEX IF NOT EXISTS idx_voice_commands_lead_id ON voice_commands(affected_lead_id);
CREATE INDEX IF NOT EXISTS idx_ideas_lead_id ON ideas(lead_id);
CREATE INDEX IF NOT EXISTS idx_detected_friction_suggestion_id ON detected_friction(linked_suggestion_id);
```

## API Endpoints
None - this is a database-only change.

## UI Components
None - this is a database-only change.

## Implementation Steps
- [x] Phase 1: Builder creates migration file ✅ Done
- [ ] Phase 2: QA runs migration in Supabase
- [ ] Phase 3: Verify indexes created
- [ ] Phase 4: Test query performance on lead profile page

## Build Progress

### ✅ Migration File Created
**File:** `supabase/fk-indexes-migration.sql`
**Added to:** `supabase/PENDING_MIGRATIONS.md` (marked ⬜)
**Commit:** 8a00943 "Add FK indexes migration for performance"
**Pushed:** main branch

## Success Criteria
1. All 10 indexes exist in database
2. `EXPLAIN ANALYZE` shows index scans instead of seq scans for:
   - `SELECT * FROM lead_notes WHERE lead_id = ?`
   - `SELECT * FROM conversions WHERE lead_id = ?`
   - `SELECT * FROM sequence_enrollments WHERE lead_id = ?`

## Notes for Builder
1. Create `supabase/fk-indexes-migration.sql` with the SQL above
2. Add entry to `supabase/PENDING_MIGRATIONS.md` marked as pending
3. All statements use `IF NOT EXISTS` - safe to run multiple times

## Notes for QA
1. Run the migration in Supabase SQL Editor
2. Verify with: `SELECT indexname FROM pg_indexes WHERE tablename IN ('lead_notes', 'conversions', 'sequence_enrollments', 'sequence_steps', 'call_recordings', 'voice_commands', 'ideas', 'detected_friction');`
3. Test lead profile page load time before/after (should be faster with many notes)

## Why This Matters
Without FK indexes:
- Every `WHERE lead_id = X` does a full table scan
- Cascading deletes lock the table while scanning
- As data grows, queries get exponentially slower

With FK indexes:
- O(log n) lookup instead of O(n) scan
- Faster page loads
- Better concurrency under load