# Database Schema - Bug Analysis

> 20 issues found in `supabase/`

---

## CRITICAL (6)

### 1. Missing FK on leads.referred_by_lead_id
**Table:** `leads`
**Description:** Self-referencing FK has no CASCADE. Orphan references remain on delete.
**Fix:**
```sql
ALTER TABLE leads ADD CONSTRAINT leads_referred_by_lead_id_fkey
  FOREIGN KEY (referred_by_lead_id) REFERENCES leads(id) ON DELETE SET NULL;
```

### 2. Missing FK on user_xp.user_id
**Table:** `user_xp`
**Description:** No explicit FK to auth.users. Orphan XP data on user deletion.
**Fix:**
```sql
ALTER TABLE user_xp ADD CONSTRAINT user_xp_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;
```

### 3. Multiple Tables Missing user_id FKs
**Tables:** user_achievements, xp_history, call_scores, ai_alerts, weekly_summaries, playbook_messages, voicemail_recordings
**Description:** Reference auth.users but no FK constraints.
**Impact:** Massive orphan data on user deletion.

### 4. Missing NOT NULL on Required Fields
**Columns:**
- `communications.content` - nullable but required for SMS/calls
- `lead_intel.job_title` - should be required when record exists
- `company_intel.company_name_normalized` - nullable creates duplicates

**Fix:**
```sql
ALTER TABLE communications ALTER COLUMN content SET NOT NULL;
```

### 5. Missing UNIQUE on company_intel.company_name_normalized
**Table:** `company_intel`
**Description:** Research caching uses this for lookups but duplicates possible.
**Fix:**
```sql
ALTER TABLE company_intel ADD CONSTRAINT company_intel_name_normalized_unique
  UNIQUE (company_name_normalized);
```

### 6. Missing Cascade on call_recordings
**Table:** `call_recordings`
**Description:** `communication_id` FK has no delete policy. Orphans on delete.
**Fix:**
```sql
ALTER TABLE call_recordings ADD CONSTRAINT call_recordings_communication_id_fkey
  FOREIGN KEY (communication_id) REFERENCES communications(id) ON DELETE SET NULL;
```

---

## HIGH (6)

### 7. Missing Indexes on Foreign Keys
**Missing:**
- `conversions.lead_id`
- `callbacks.handled_by`
- `objection_responses.objection_keywords` (GIN)

**Fix:**
```sql
CREATE INDEX idx_conversions_lead_id ON conversions(lead_id);
CREATE INDEX idx_callbacks_handled_by ON callbacks(handled_by);
```

### 8. Missing Timestamp Indexes
**Missing:** `created_at DESC` on ideas, wisdom_shown, extracted_entities
**Fix:**
```sql
CREATE INDEX idx_ideas_created_desc ON ideas(created_at DESC);
```

### 9. Inconsistent CASCADE Policies
**Issue:** Some FKs use CASCADE, others SET NULL, others nothing.
**Impact:** Unpredictable data loss on deletions.

### 10. Missing Composite Indexes
**Missing:**
- `(lead_id, created_at DESC)` on communications
- `(user_id, created_at DESC)` on ai_alerts
- `(lead_id, type)` on communications
- `(status, created_at)` on callbacks

### 11. Missing CHECK Constraints on Enums
**Columns:**
- `communications.type` - should CHECK ('sms', 'call', 'email')
- `communications.direction` - CHECK ('inbound', 'outbound')
- `callbacks.status` - CHECK ('pending', 'completed', 'cancelled')
- `callbacks.priority` - CHECK ('low', 'medium', 'high', 'urgent')

**Fix:**
```sql
ALTER TABLE communications ADD CONSTRAINT communications_type_check
  CHECK (type IN ('sms', 'call', 'email'));
```

### 12. Missing updated_at Columns
**Tables:** communications, callbacks, ideas, extracted_entities
**Fix:**
```sql
ALTER TABLE communications ADD COLUMN updated_at TIMESTAMPTZ DEFAULT NOW();
CREATE TRIGGER communications_updated_at BEFORE UPDATE ON communications
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();
```

---

## MEDIUM (8)

### 13. Missing RLS Policies
**Tables WITHOUT RLS:** leads, communications, callbacks, conversions, call_recordings, ai_insights, conversion_patterns, objection_responses
**Fix:**
```sql
ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view assigned leads" ON leads
  FOR SELECT TO authenticated USING (assigned_to = auth.uid());
```

### 14. JSONB Columns Missing GIN Indexes
**Columns:** leads.metadata, communications.metadata, conversion_patterns.objections_raised, lead_dossier.key_facts
**Fix:**
```sql
CREATE INDEX idx_leads_metadata ON leads USING gin(metadata);
```

### 15. Decimal Precision Issues
**Columns:**
- `ai_conversion_probability DECIMAL(5,2)` - supports 0-999.99
- Score columns like `effectiveness_score DECIMAL(4,3)` - edge cases for -1 to 1

### 16. VARCHAR Sizes (Minor)
**Status:** Most appropriately sized. Monitor for overflow.

### 17. Missing Partial Indexes
**Missing:**
- `callbacks WHERE status = 'pending'`
- `daily_challenges WHERE completed = FALSE`

**Fix:**
```sql
CREATE INDEX idx_callbacks_pending ON callbacks(status) WHERE status = 'pending';
```

### 18. Inconsistent UUID Generation
**Issue:** Mix of `uuid_generate_v4()` and `gen_random_uuid()`.
**Fix:** Standardize on `gen_random_uuid()` (Postgres 13+).

### 19. Missing Data Validation
**Columns:**
- `leads.score` - should be 0-100
- `user_xp.level` - should be >= 1
- `objection_responses.effectiveness_score` - should be 0-1

**Fix:**
```sql
ALTER TABLE leads ADD CONSTRAINT leads_score_check CHECK (score >= 0 AND score <= 100);
```

### 20. Polymorphic FK Risk
**Table:** `callbacks`
**Columns:** `source_type`, `source_id`
**Description:** No FK constraint. `source_id` can reference deleted records.
**Fix:** Add CHECK constraint on valid `source_type` values.