# Pending Database Migrations

Run these in **Supabase SQL Editor** → Check off when done.

---

## How to Run
1. Go to https://supabase.com/dashboard → Your Project → SQL Editor
2. Paste the SQL
3. Click "Run"
4. Come back here and change ⬜ to ✅

---

## Pending Migrations

### ✅ 2026-01-28 | Sales Rebuttals Table
**Applied via CLI:** `npx supabase db push --linked`
**Feature:** Objections management API for sales scripts
**File:** `supabase/migrations/20260128112808_sales_rebuttals.sql`

```sql
-- Sales Rebuttals Table
CREATE TABLE IF NOT EXISTS sales_rebuttals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  response TEXT NOT NULL,
  category VARCHAR(100),
  tags TEXT[] DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sales_rebuttals_category ON sales_rebuttals(category);
CREATE INDEX IF NOT EXISTS idx_sales_rebuttals_tags ON sales_rebuttals USING GIN(tags);

ALTER TABLE sales_rebuttals ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated users can view rebuttals"
  ON sales_rebuttals FOR SELECT TO authenticated USING (true);

CREATE POLICY "Authenticated users can manage rebuttals"
  ON sales_rebuttals FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access"
  ON sales_rebuttals FOR ALL TO service_role
  USING (true) WITH CHECK (true);
```

---

### ✅ 2026-01-24 | Push Notifications System
**Applied via CLI:** `npx supabase db push --linked`
**Feature:** Web Push notifications for high-intent SMS, new leads, callbacks, conversions
**File:** `supabase/migrations/20260124000112_push_notifications.sql`

```sql
-- Push notification subscriptions
CREATE TABLE IF NOT EXISTS push_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  endpoint TEXT NOT NULL,
  keys JSONB NOT NULL, -- { p256dh: string, auth: string }
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  last_used_at TIMESTAMPTZ,
  UNIQUE(user_id, endpoint)
);

-- Notification preferences per user
CREATE TABLE IF NOT EXISTS notification_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
  high_intent_sms BOOLEAN DEFAULT TRUE,
  new_leads BOOLEAN DEFAULT TRUE,
  callbacks BOOLEAN DEFAULT TRUE,
  conversions BOOLEAN DEFAULT TRUE,
  sequence_replies BOOLEAN DEFAULT TRUE,
  daily_summary BOOLEAN DEFAULT FALSE,
  quiet_hours_start TIME,
  quiet_hours_end TIME,
  sms_fallback BOOLEAN DEFAULT FALSE,
  sms_fallback_phone VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add notified_at column to callbacks table
ALTER TABLE callbacks ADD COLUMN IF NOT EXISTS notified_at TIMESTAMPTZ;

-- Indexes
CREATE INDEX IF NOT EXISTS idx_push_subs_user ON push_subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_notif_prefs_user ON notification_preferences(user_id);
CREATE INDEX IF NOT EXISTS idx_callbacks_notified ON callbacks(notified_at) WHERE notified_at IS NULL;

-- RLS Policies
ALTER TABLE push_subscriptions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own subscriptions"
  ON push_subscriptions FOR ALL TO authenticated
  USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

CREATE POLICY "Service role can manage all subscriptions"
  ON push_subscriptions FOR ALL TO service_role
  USING (true) WITH CHECK (true);

ALTER TABLE notification_preferences ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own preferences"
  ON notification_preferences FOR ALL TO authenticated
  USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

CREATE POLICY "Service role can manage all preferences"
  ON notification_preferences FOR ALL TO service_role
  USING (true) WITH CHECK (true);
```

---

### ✅ 2026-01-24 | Sequence Failed Attempts Column
**Applied via CLI:** `npx supabase db push --linked`
**Feature:** Retry logic for failed SMS - track attempts before pausing enrollment
**File:** `supabase/migrations/20260124_sequence_failed_attempts.sql`

```sql
ALTER TABLE sequence_enrollments
ADD COLUMN IF NOT EXISTS failed_attempts INTEGER DEFAULT 0;

COMMENT ON COLUMN sequence_enrollments.failed_attempts IS 'Number of failed send attempts. Resets on success. Enrollment pauses after 3 failures.';
```

---

### ✅ 2026-01-22 | Micro-Optimisation Indexes
**Applied via CLI:** `npx supabase db push --linked`
**Feature:** Add missing indexes for common query patterns to improve performance
**File:** `supabase/migrations/20260122081452_micro_optimisation_indexes.sql`
**Note:** Some indexes already existed (idx_leads_assigned_to, idx_communications_created_at, idx_communications_handled_by) - skipped safely

```sql
-- Index for assigned_to lookups (call list filtering by rep)
CREATE INDEX IF NOT EXISTS idx_leads_assigned_to ON leads(assigned_to);

-- Index for created_at range queries (time-based filtering)
CREATE INDEX IF NOT EXISTS idx_leads_created_at ON leads(created_at DESC);

-- Index for last_contact_at range queries (stale lead detection)
CREATE INDEX IF NOT EXISTS idx_leads_last_contact_at ON leads(last_contact_at DESC);

-- Index for communications created_at (timeline queries)
CREATE INDEX IF NOT EXISTS idx_communications_created_at ON communications(created_at DESC);

-- Index for handled_by lookups (rep performance queries)
CREATE INDEX IF NOT EXISTS idx_communications_handled_by ON communications(handled_by);

-- Composite index for callbacks status + scheduled_at (pending callback queries)
CREATE INDEX IF NOT EXISTS idx_callbacks_status_scheduled ON callbacks(status, scheduled_at);

-- Index for callbacks scheduled_at (timeline ordering)
CREATE INDEX IF NOT EXISTS idx_callbacks_scheduled_at ON callbacks(scheduled_at DESC);
```

---

### ✅ 2026-01-22 | XP Atomic Increment Function
**Applied via CLI:** `npx supabase db push --linked`
**Migration:** `supabase/migrations/20260122082559_xp_atomic_increment.sql`
**Feature:** Fix race condition in XP updates by using FOR UPDATE row locking

Creates `add_user_xp(p_user_id, p_xp_amount, p_action, p_reason)` PostgreSQL function that:
- Locks row with FOR UPDATE to prevent race conditions
- Handles streak calculation and bonuses
- Calculates level from XP atomically
- Returns JSON with all updated values

---

### ✅ 2026-01-22 | Rep Performance Schema (with fixed user_id type)
**Applied via CLI:** `npx supabase db push --linked`
**Feature:** Weekly rep snapshots + call quality scores tables
**Migration:** `supabase/migrations/20260122074416_rep_performance_tables.sql`
**Tables created:**
- `rep_weekly_snapshots` - Weekly performance data per rep
- `call_quality_scores` - AI-scored calls with detailed breakdown
**Note:** Schema was fixed at source (TEXT → UUID for user_id) - cleaner than creating then migrating

---

### ✅ 2026-01-22 | FK Indexes - Performance Optimization
**Applied via CLI:** `npx supabase db push --linked`
**Feature:** Add missing indexes to foreign key columns for faster JOINs and cascading deletes
**File:** `supabase/fk-indexes-migration.sql`

```sql
-- 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 and call recordings
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 tables
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);
```

---

### ✅ 2026-01-21 | Self-Improvement System Schema
**Feature:** Auto-adjusting thresholds, learning from successful suggestions
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260121111258_self_improvement_system.sql
-- Creates 2 new tables + extends existing:
-- 1. system_settings - Stores intelligence thresholds
-- 2. intelligence_adjustments - Logs auto-adjustments
-- Adds: status, resolved_at to detected_friction
-- Adds: success_verdict, measured_impact to intelligence_suggestions
-- Seeds default thresholds
-- RLS policies for service_role and authenticated read
```

---

### ✅ 2026-01-21 | Rocky Platform Seeding Sequence
**Feature:** SMS sequence for Rocky's old contacts - favour ask for signups
**Applied via CLI:** `npx supabase db push`

```sql
-- See: supabase/migrations/20260121100000_rocky_platform_seeding_sequence.sql
-- Extends sequences table: sender_name column (default 'Rocky')
-- Creates sequence: "Platform Seeding - Rocky Contacts"
-- 3 steps: Day 0 favour ask, Day 3 soft follow-up, Day 7 last nudge + referral
-- Send windows: knockoff, morning, knockoff
-- Manual trigger, targets not_signed_up workers
```

---

### ✅ 2026-01-21 | Intelligence Alerts Table
**Feature:** Unified alerting system for quality audits
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- Creates intelligence_alerts table for tracking quality audit failures
-- Used by frequentAudit.js for deduplication and auto-resolution

CREATE TABLE IF NOT EXISTS intelligence_alerts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  alert_type VARCHAR(100) NOT NULL,
  severity VARCHAR(20) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  evidence JSONB DEFAULT '{}',
  suggested_action TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  resolved_at TIMESTAMPTZ,
  resolution_notes TEXT,
  was_true_positive BOOLEAN
);

CREATE INDEX IF NOT EXISTS idx_intelligence_alerts_type ON intelligence_alerts(alert_type);
CREATE INDEX IF NOT EXISTS idx_intelligence_alerts_unresolved ON intelligence_alerts(resolved_at) WHERE resolved_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_intelligence_alerts_created ON intelligence_alerts(created_at DESC);

ALTER TABLE intelligence_alerts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "intelligence_alerts_authenticated_all" ON intelligence_alerts FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "intelligence_alerts_service_all" ON intelligence_alerts FOR ALL TO service_role USING (true) WITH CHECK (true);
```

---

### ✅ 2026-01-20 | AI Call Scoring - GPT-powered scoring
**Feature:** 6-dimension call scoring (rapport, discovery, value prop, objection, close, professionalism)
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- Adds to call_scores table:
-- - grade (A+, A, B+, B, C+, C, D, F)
-- - dimension scores (rapport, discovery, value_prop, objection, close, professionalism)
-- - dimension_notes, highlights, improvements, coaching_tip
-- - user_call_score_summary view
```

---

### ✅ 2026-01-20 | Call Analytics - Quality Metrics & Coaching
**Feature:** Talk ratio, filler words, questions, sentiment analysis, coaching alerts
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- Creates 2 new tables + 1 view:
-- 1. call_analytics - Quality metrics per call
-- 2. coaching_alerts - Flagged calls needing review
-- 3. call_quality_trends (view) - Team quality trends
```

---

### ✅ 2026-01-20 | Deal Intelligence - Objection Impact & Stall Prediction
**Feature:** Track objections to outcomes, predict stalling deals, loss reason analysis
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- Creates 3 new tables + 1 view:
-- 1. deal_stages - Track lead progression through sales funnel
-- 2. deal_outcomes - Win/Loss recording with reasons
-- 3. loss_reasons - Seed data for common loss reasons
-- 4. objection_impact_stats (view) - Pre-calculated objection conversion rates

-- Also extends objection_responses table with:
-- - led_to_conversion_bool
-- - lead_final_outcome
-- - days_to_outcome
```

---

### ✅ 2026-01-20 | Self-Improving Intelligence System MVP
**Feature:** Usage tracking, weekly friction analysis, improvement suggestions
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260120084845_intelligence_system_mvp.sql
-- Creates 5 new tables:
-- 1. usage_events - Raw user behavior tracking (90-day retention)
-- 2. intelligence_daily_metrics - Aggregated metrics (kept forever)
-- 3. intelligence_suggestions - AI-generated improvement suggestions
-- 4. weekly_excellence_reports - Weekly analysis archives
-- 5. detected_friction - Friction points found by analysis

-- Plus helper functions, views, and RLS policies
```

---

### ✅ 2026-01-20 | Prompt Evolution (Self-Improvement Loop)
**Feature:** AI prompt versioning, A/B testing, and automatic evolution
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260120100000_prompt_evolution.sql
-- Creates 2 new tables:
-- 1. prompt_versions - Stores prompt templates with versioning
-- 2. prompt_outcomes - Tracks success/failure of each prompt usage
-- Plus helper functions for safe incrementing and outcome recording
```

---

### ✅ 2026-01-20 | Allow Anonymous Tracking
**Feature:** Fix RLS policy to allow frontend tracking without auth
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260120100237_allow_anon_tracking.sql
-- Adds INSERT policies for anon and authenticated users on usage_events
```

---

### ✅ 2026-01-20 | Lead Assignment System
**Feature:** Database-driven user management, lead claiming, auto-release
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260120110914_lead_assignment.sql
-- Extends user_xp with: email, role, is_active columns
-- Adds to leads: assigned_at timestamp
-- Adds to communications: user_id column
-- Seeds Tony (closer) and Michael (admin) users
```

---

### ✅ 2026-01-20 | Rep Performance Reports + Handled By Column
**Feature:** Weekly rep performance snapshots, AI call scoring, handled_by tracking
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260120112449_add_handled_by_column.sql
-- Also: supabase/rep-performance-schema.sql (tables need to be run manually if not already)

-- Key changes:
-- 1. Added handled_by UUID column to communications table
-- 2. Index for efficient rep queries
-- 3. Backfilled from metadata.handled_by and metadata.user_id
-- 4. Future calls will have handled_by set automatically
```

---

### ✅ 2026-01-20 | Quality Audit Upgrade - Frontend Quality Tables
**Feature:** Web vitals tracking, frontend error logging, audit history
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- Creates 2 new tables + enhances quality_audits:
-- 1. web_vitals - LCP, INP, CLS, FCP, TTFB metrics from frontend
-- 2. frontend_errors - Client-side JavaScript error tracking
-- 3. quality_audits - Add audit_type column for 15min/hourly/daily tracking

-- Run in Supabase SQL Editor:
CREATE TABLE IF NOT EXISTS web_vitals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metric_name TEXT NOT NULL,
  metric_value NUMERIC NOT NULL,
  rating TEXT,
  page_path TEXT,
  delta NUMERIC,
  metric_id TEXT,
  navigation_type TEXT,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_web_vitals_metric_time
ON web_vitals(metric_name, recorded_at DESC);

CREATE TABLE IF NOT EXISTS frontend_errors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message TEXT NOT NULL,
  stack TEXT,
  page_path TEXT,
  url TEXT,
  user_agent TEXT,
  error_type TEXT DEFAULT 'unknown',
  component_stack TEXT,
  filename TEXT,
  lineno INTEGER,
  colno INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_frontend_errors_created_at
ON frontend_errors(created_at DESC);

-- Add audit_type to quality_audits
ALTER TABLE quality_audits ADD COLUMN IF NOT EXISTS audit_type TEXT DEFAULT 'full_daily';

-- RLS policies
ALTER TABLE web_vitals ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow insert for everyone" ON web_vitals FOR INSERT WITH CHECK (true);
CREATE POLICY "Allow read for authenticated" ON web_vitals FOR SELECT USING (true);

ALTER TABLE frontend_errors ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow insert for everyone" ON frontend_errors FOR INSERT WITH CHECK (true);
CREATE POLICY "Allow read for authenticated" ON frontend_errors FOR SELECT USING (true);
```

---

### ✅ 2026-01-19 | Battleground V2 - XP Duels + Manager Challenges
**Feature:** Head-to-head duels with AI odds, manager-created team challenges
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260119140000_battleground_v2.sql
-- Creates 5 new tables:
-- 1. duels - Head-to-head XP challenges
-- 2. manager_challenges - Custom team competitions
-- 3. challenge_participants - Who's competing
-- 4. duel_history - For odds calculation
-- 5. celebration_events - Team-wide notifications
```

---

### ✅ 2026-01-19 | SMS Sequences v2 - Stage-Based Sequences Seed
**Feature:** 12 stage-based sequences (7 worker, 5 contractor) with 24 message templates
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260119115515_seed_stage_sequences_v2.sql
-- Creates 12 sequences:
-- Worker: not_signed_up, signed_up, profile_complete, stripe_ready, job_ready, working, proven
-- Contractor: not_signed_up, signed_up, profile_complete, hired, active
```

---

### ✅ 2026-01-19 | SMS Sequences v2 - Schema Enhancements
**Feature:** Stage-based sequences, randomized timing, send windows, opt-out flag
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260119115108_sequence_v2_enhancements.sql
-- Adds:
-- - stage, lead_type columns to sequences
-- - delay_days_min, delay_days_max, send_window to sequence_steps
-- - sequence_opted_out to leads
-- - Index on sequences(stage, lead_type)
```

---

### ✅ 2026-01-19 | SMS Sequences - Seed Data
**Feature:** Automated SMS follow-up sequences after no-answer calls
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260119113909_seed_sequences.sql
-- Creates 2 default sequences:
-- 1. New Worker Outreach (4 steps, 7 days)
-- 2. New Contractor Outreach (4 steps, 7 days)
```

---

### ✅ 2026-01-19 | SMS Sequences - Schema Enhancements
**Feature:** Adds columns and indexes for sequence automation
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See: supabase/migrations/20260119113451_sequence_automation.sql
-- Adds:
-- - next_step_at, pause_reason to sequence_enrollments
-- - channel, user_id to sequences
-- - name, message_template to sequence_steps
-- - Indexes for processor queries
-- - RLS policies
-- - Status change trigger
```

---

### ✅ 2026-01-19 | Lead Intel - Add missing industry & size columns
**Feature:** Fix "Failed to update intel" error - missing columns
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- Lead Intel Missing Columns Fix
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS industry VARCHAR(100);
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS size VARCHAR(50);
```

---

### ✅ 2026-01-18 | Voicemail Drop - voicemail_recordings table
**Feature:** Pre-recorded voicemails that reps can drop with one click
**Applied via CLI:** `npx supabase db push --linked`

```sql
-- See full migration: supabase/voicemail-drop-schema.sql

-- Pre-recorded voicemail messages
CREATE TABLE IF NOT EXISTS voicemail_recordings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  recording_url TEXT NOT NULL,
  recording_sid VARCHAR(50),
  duration_seconds INTEGER,
  is_default BOOLEAN DEFAULT FALSE,
  is_shared BOOLEAN DEFAULT FALSE,
  usage_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Track voicemail drops for analytics
CREATE TABLE IF NOT EXISTS voicemail_drops (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  recording_id UUID REFERENCES voicemail_recordings(id) ON DELETE SET NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  call_sid VARCHAR(50),
  dropped_at TIMESTAMPTZ DEFAULT NOW(),
  lead_called_back BOOLEAN,
  callback_within_hours INTEGER
);

-- Indexes + RLS + increment function in full migration
```

---

### ✅ 2026-01-18 | Actionable Playbook - playbook_messages table
**Feature:** Today's Plays - AI-generated ready-to-send messages for top leads

```sql
-- Applied via CLI: npx supabase db push --linked
-- See: supabase/migrations/20260118012824_playbook_messages.sql

CREATE TABLE IF NOT EXISTS playbook_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  message_text TEXT NOT NULL,
  message_angle VARCHAR(50), -- 'follow_up', 'value_prop', 'urgency'
  was_sent BOOLEAN DEFAULT FALSE,
  sent_at TIMESTAMPTZ,
  generated_at TIMESTAMPTZ DEFAULT NOW(),
  batch_id UUID, -- Groups bulk sends together
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for cache lookup, batch tracking, analytics
```

---

### ✅ 2026-01-18 | Intel Edit Modal Support
**Feature:** Fix - Edit Intel button was failing because columns don't exist

```sql
-- See full migration: supabase/intel-edit-migration.sql

-- COMPANY INTEL - Add edit support columns
ALTER TABLE company_intel
ADD COLUMN IF NOT EXISTS user_notes TEXT,
ADD COLUMN IF NOT EXISTS manually_updated BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS updated_by TEXT,
ADD COLUMN IF NOT EXISTS notes_updated_at TIMESTAMPTZ;

-- LEAD INTEL - Add edit support columns
ALTER TABLE lead_intel
ADD COLUMN IF NOT EXISTS user_notes TEXT,
ADD COLUMN IF NOT EXISTS manually_updated BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS updated_by TEXT,
ADD COLUMN IF NOT EXISTS notes_updated_at TIMESTAMPTZ;
```

---

### ✅ 2026-01-18 | Lead Dossier - Structured Living Document
**Feature:** One structured document per lead that accumulates all intel from calls, SMS, research

```sql
-- See full migration: supabase/lead-dossier-schema.sql

CREATE TABLE IF NOT EXISTS lead_dossier (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,

  -- Key facts learned about the lead
  key_facts JSONB DEFAULT '[]'::jsonb,
  -- Array of: { fact: "Has 3 kids", source: "call", learned_at: timestamp }

  -- Business/professional info
  business_intel JSONB DEFAULT '{}'::jsonb,
  -- { company_size, industry, pain_points: [], budget_range, decision_timeline }

  -- Personal rapport info
  personal_intel JSONB DEFAULT '{}'::jsonb,
  -- { family, hobbies: [], interests: [], conversation_starters: [] }

  -- Call history summaries (not full transcripts, just key points)
  call_summaries JSONB DEFAULT '[]'::jsonb,
  -- Array of: { date, duration, outcome, key_points: [], next_steps, mood }

  -- Objections encountered and how they were handled
  objections JSONB DEFAULT '[]'::jsonb,
  -- Array of: { objection, response_used, effective: true/false, date }

  -- What's worked with this lead
  what_works JSONB DEFAULT '[]'::jsonb,
  -- Array of: { tactic, result, date }

  -- Current status and next steps
  current_status JSONB DEFAULT '{}'::jsonb,
  -- { stage, temperature, blockers: [], next_action, follow_up_date }

  -- AI-generated overall summary
  ai_summary TEXT,
  ai_summary_updated_at TIMESTAMPTZ,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_lead_dossier_lead_id ON lead_dossier(lead_id);

-- Trigger for updated_at
CREATE OR REPLACE FUNCTION update_lead_dossier_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS lead_dossier_updated_at ON lead_dossier;
CREATE TRIGGER lead_dossier_updated_at
  BEFORE UPDATE ON lead_dossier
  FOR EACH ROW
  EXECUTE FUNCTION update_lead_dossier_updated_at();

ALTER TABLE lead_dossier ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated users can manage lead dossiers"
  ON lead_dossier FOR ALL TO authenticated
  USING (true) WITH CHECK (true);
```

---

### ✅ 2026-01-17 | Copilot AI Summary Column
**Feature:** Store AI-generated call summaries in copilot_sessions

```sql
-- Add ai_summary column to copilot_sessions
ALTER TABLE copilot_sessions
ADD COLUMN IF NOT EXISTS ai_summary JSONB DEFAULT NULL;

-- Add comment
COMMENT ON COLUMN copilot_sessions.ai_summary IS 'AI-generated summary including outcome, sentiment, key points, buying signals';
```

---

### ✅ 2026-01-17 | Business Intel Column
**Feature:** Store business intel extracted from call transcripts

```sql
-- Add business_intel JSONB column to lead_intel
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS business_intel JSONB;

-- Index for querying leads with pain points or hiring needs
CREATE INDEX IF NOT EXISTS idx_lead_intel_has_business_intel
ON lead_intel(lead_id)
WHERE business_intel IS NOT NULL;

COMMENT ON COLUMN lead_intel.business_intel IS 'Business intel extracted from calls: employer, pain_points, hiring_needs, budget_signals, decision_timeline, competitors';
```

---

### ✅ 2026-01-17 | Personal Intel for CallPrepPage
**Feature:** Priority #10 enhancement - Personal profile research for pre-call briefing

```sql
-- See full migration: supabase/personal-intel-migration.sql

-- Family & Personal
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS family_status TEXT;
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS hobbies TEXT[];
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS interests TEXT[];
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS location_suburb TEXT;
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS personality_notes TEXT;

-- Conversation Helpers
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS conversation_starters TEXT[];
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS personal_news JSONB;

-- Social Profiles
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS social_profiles JSONB;

-- Index
CREATE INDEX IF NOT EXISTS idx_lead_intel_has_personal
ON lead_intel(lead_id)
WHERE family_status IS NOT NULL OR hobbies IS NOT NULL;
```

---

### ✅ 2026-01-17 | Scripts Command Center + Seed Scripts
**Feature:** Brand guidelines, unified scripts library, compliance checker, review queue, 30 seed scripts

```sql
-- See full migrations:
-- supabase/migrations/20260117043817_scripts_command_center.sql
-- supabase/migrations/20260117050000_seed_scripts.sql

-- Creates 4 new tables:
-- 1. brand_guidelines - Tone, banned words, compliance rules
-- 2. brand_guideline_history - Change tracking
-- 3. scripts (extended) - Unified scripts library (SMS, call, email, objection)
-- 4. compliance_queue - Flagged messages for review

-- Seeds:
-- - Initial brand guidelines with Aussie tradie vibes
-- - 30 scripts: 10 SMS, 10 Call, 8 Objection, 2 Email
-- - SMS opt-out only on re-engagement (gone_cold, inactive_30d)
-- - Contractors = FREE, Workers = 9.9% fee in all messaging
```

---

### ✅ 2026-01-17 | Messages Hub 0.1%
**Feature:** Messages Hub upgrade with response time tracking, message flags, entity extraction

```sql
-- See full migration: supabase/messages-hub-migration.sql

-- Response time tracking
ALTER TABLE communications ADD COLUMN IF NOT EXISTS response_time_seconds INTEGER;

-- Message flags
ALTER TABLE communications ADD COLUMN IF NOT EXISTS flagged BOOLEAN DEFAULT FALSE;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS flagged_at TIMESTAMPTZ;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS flagged_reason VARCHAR(100);

-- Extracted entities table
-- Stores detected phone numbers, emails, dates from messages
```

---

### ✅ 2026-01-17 | Gamification Battleground
**Feature:** Priority #7 - Sales Team Battleground competitive system

```sql
-- See full migration: supabase/gamification-battleground-migration.sql

-- Creates 5 new tables:
-- 1. hall_of_fame - Permanent records (most calls/day, biggest deal, etc.)
-- 2. weekly_badges - Who owned the week (closer, dialer, sniper, etc.)
-- 3. daily_challenges - Individual + team challenges
-- 4. team_pulse - Live activity feed (conversions, overtakes, records)
-- 5. leaderboard_snapshots - Position tracking over time

-- Extends user_xp table with:
-- streak_tier, display_name, avatar_url, current_badges, status
```

---

### ✅ 2026-01-16 | Platform Activation Command Center
**Feature:** Lifecycle tracking for worker/contractor activation funnels

```sql
-- See full migration: supabase/activation-command-center-migration.sql
-- Adds: platform_stage, platform_data, platform_health_score, stage_changed_at
-- Creates: stage_transitions table, activation_metrics table
```

---

### ✅ 2026-01-16 | Website Signup Real-Time Pipeline
**Feature:** Priority #6 - Instant website signup alerts + speed-to-lead tracking

```sql
-- PART 1: Leads table extensions
ALTER TABLE leads ADD COLUMN IF NOT EXISTS signup_source VARCHAR(50);
ALTER TABLE leads ADD COLUMN IF NOT EXISTS signup_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS signup_message TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS first_contact_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS first_contact_channel VARCHAR(20);
ALTER TABLE leads ADD COLUMN IF NOT EXISTS is_re_engaged BOOLEAN DEFAULT false;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS re_engaged_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS re_engaged_insight TEXT;

CREATE INDEX IF NOT EXISTS idx_leads_signup_at ON leads(signup_at DESC) WHERE signup_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_leads_source ON leads(source) WHERE source = 'website';

-- PART 2: Speed-to-lead metrics table
CREATE TABLE IF NOT EXISTS speed_to_lead (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  signup_at TIMESTAMPTZ NOT NULL,
  first_contact_at TIMESTAMPTZ,
  first_response_at TIMESTAMPTZ,
  converted_at TIMESTAMPTZ,
  minutes_to_contact INTEGER,
  minutes_to_response INTEGER,
  minutes_to_conversion INTEGER,
  contact_channel VARCHAR(20),
  contact_attempts INTEGER DEFAULT 0,
  contacted_by UUID,
  outcome VARCHAR(50),
  signup_source VARCHAR(50),
  utm_source VARCHAR(100),
  utm_campaign VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_stl_signup_at ON speed_to_lead(signup_at DESC);
CREATE INDEX IF NOT EXISTS idx_stl_lead_id ON speed_to_lead(lead_id);
CREATE INDEX IF NOT EXISTS idx_stl_outcome ON speed_to_lead(outcome);
```

---

## Completed Migrations

### ✅ 2026-01-16 | Call List Smart Prioritization
**Completed:** 2026-01-16
**Feature:** Priority #1 - AI-powered call list ranking

```sql
-- Add priority scoring fields to leads table
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_score INTEGER DEFAULT 0;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_reason TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_reason_type TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_warning TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_updated_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS last_inbound_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS last_inbound_type TEXT;

-- Index for fast call list sorting
CREATE INDEX IF NOT EXISTS idx_leads_call_list_score ON leads(call_list_score DESC)
WHERE deleted_at IS NULL AND status NOT IN ('converted', 'opted_out', 'archived');
```

---

### ✅ 2026-01-16 | SMS Templates Fix
**Completed:** 2026-01-16
**Feature:** Critical fix - description column missing

```sql
ALTER TABLE sms_templates ADD COLUMN IF NOT EXISTS description TEXT;
```

---

## Verification Queries

Check if a column exists:
```sql
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'YOUR_TABLE' AND column_name = 'YOUR_COLUMN';
```

List all columns in a table:
```sql
SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'leads'
ORDER BY ordinal_position;
```