# Sales Tools & Operations APIs - Implementation Plan

## Overview

Build 8 new APIs to complete the sales tools and operations feature set for RateRight Growth Engine.

**Task ID:** TASK-20260202-051429
**Priority:** P1
**Estimated Effort:** ~8 days

## APIs to Build

### 1. Sales Playbook API
**Purpose:** Manage openers, discovery questions, value propositions, closes, and voicemail scripts.

**Database Tables:**
```sql
-- Already have: scripts, objections (sales_rebuttals)
-- Need: playbook_sections for organizing content by category
CREATE TABLE IF NOT EXISTS playbook_sections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category TEXT NOT NULL, -- 'opener', 'discovery', 'value_prop', 'close', 'voicemail', 'objection_handler'
  name TEXT NOT NULL,
  content TEXT NOT NULL,
  lead_type TEXT, -- 'worker', 'contractor', NULL for all
  stage TEXT, -- funnel stage when applicable
  effectiveness_score INTEGER DEFAULT 0,
  times_used INTEGER DEFAULT 0,
  times_successful INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_playbook_sections_category ON playbook_sections(category);
CREATE INDEX idx_playbook_sections_lead_type ON playbook_sections(lead_type);
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | /api/playbook/sections | List all sections by category |
| GET | /api/playbook/sections/:category | Get sections for a category |
| POST | /api/playbook/sections | Create new section |
| PUT | /api/playbook/sections/:id | Update section |
| DELETE | /api/playbook/sections/:id | Delete section |
| POST | /api/playbook/sections/:id/record-use | Record usage (with success flag) |
| GET | /api/playbook/recommend/:leadId | AI recommend best content for lead |

---

### 2. Trade Tags API
**Purpose:** Tag leads by trade (plumber, electrician, etc.) for filtering and targeted outreach.

**Database Tables:**
```sql
CREATE TABLE IF NOT EXISTS trade_tags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE, -- 'plumber', 'electrician'
  color TEXT DEFAULT '#6B7280', -- UI color
  lead_count INTEGER DEFAULT 0, -- Denormalized count
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS lead_trade_tags (
  lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  tag_id UUID NOT NULL REFERENCES trade_tags(id) ON DELETE CASCADE,
  assigned_at TIMESTAMPTZ DEFAULT NOW(),
  assigned_by UUID REFERENCES auth.users(id),
  PRIMARY KEY (lead_id, tag_id)
);
CREATE INDEX idx_lead_trade_tags_tag ON lead_trade_tags(tag_id);
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | /api/tags/trades | List all trade tags with counts |
| POST | /api/tags/trades | Create new trade tag |
| PUT | /api/tags/trades/:id | Update trade tag |
| DELETE | /api/tags/trades/:id | Delete trade tag |
| POST | /api/leads/:id/tags | Add tag(s) to lead |
| DELETE | /api/leads/:id/tags/:tagId | Remove tag from lead |
| GET | /api/leads?tags=plumber,electrician | Filter leads by tags |

---

### 3. Competitor Tracking API
**Purpose:** Track competitor mentions, build battle cards, and store competitive intel.

**Database Tables:**
```sql
CREATE TABLE IF NOT EXISTS competitors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  website TEXT,
  description TEXT,
  strengths TEXT[], -- Array of strength points
  weaknesses TEXT[], -- Array of weakness points
  pricing_info TEXT,
  battle_card JSONB, -- Full battle card content
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS competitor_mentions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  competitor_id UUID REFERENCES competitors(id) ON DELETE CASCADE,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  communication_id UUID REFERENCES communications(id),
  mention_type TEXT NOT NULL, -- 'call', 'sms', 'note'
  context TEXT, -- What was said
  sentiment TEXT, -- 'positive', 'negative', 'neutral'
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_competitor_mentions_competitor ON competitor_mentions(competitor_id);
CREATE INDEX idx_competitor_mentions_lead ON competitor_mentions(lead_id);
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | /api/competitors | List all competitors |
| GET | /api/competitors/:id | Get competitor with battle card |
| POST | /api/competitors | Create competitor |
| PUT | /api/competitors/:id | Update competitor/battle card |
| DELETE | /api/competitors/:id | Delete competitor |
| GET | /api/competitors/:id/mentions | Get all mentions |
| POST | /api/competitors/mentions | Record a mention |
| GET | /api/competitors/stats | Mention stats and trends |

---

### 4. Bulk Import/Export API
**Purpose:** Import leads from CSV/JSON, export leads and activity data.

**Database Tables:**
```sql
CREATE TABLE IF NOT EXISTS import_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  filename TEXT NOT NULL,
  file_type TEXT NOT NULL, -- 'csv', 'json'
  status TEXT DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'failed'
  total_rows INTEGER,
  processed_rows INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  error_count INTEGER DEFAULT 0,
  errors JSONB, -- Array of error details
  field_mapping JSONB, -- Column mapping config
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS export_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  export_type TEXT NOT NULL, -- 'leads', 'communications', 'analytics'
  filters JSONB, -- Applied filters
  status TEXT DEFAULT 'pending',
  row_count INTEGER,
  file_url TEXT, -- Signed URL for download
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | /api/import/leads | Upload CSV/JSON for import |
| GET | /api/import/:id | Get import job status |
| GET | /api/import/:id/errors | Get import errors |
| POST | /api/export/leads | Create lead export job |
| POST | /api/export/communications | Create comms export job |
| POST | /api/export/analytics | Create analytics export job |
| GET | /api/export/:id | Get export job with download URL |

---

### 5. Apollo Enrichment API
**Purpose:** Enrich leads with decision-maker data from Apollo.io

**Database Tables:**
```sql
CREATE TABLE IF NOT EXISTS enrichment_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  source TEXT NOT NULL, -- 'apollo', 'clearbit', etc.
  enriched_data JSONB NOT NULL,
  confidence_score NUMERIC(3,2), -- 0.00-1.00
  credits_used INTEGER DEFAULT 1,
  enriched_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ
);
CREATE UNIQUE INDEX idx_enrichment_cache_lead_source ON enrichment_cache(lead_id, source);

CREATE TABLE IF NOT EXISTS enrichment_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  source TEXT NOT NULL,
  credits_used INTEGER NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | /api/enrich/:leadId | Enrich a single lead |
| POST | /api/enrich/bulk | Enrich multiple leads |
| GET | /api/enrich/:leadId | Get cached enrichment |
| GET | /api/enrich/usage | Get credit usage stats |
| GET | /api/enrich/config | Get enrichment settings |
| PUT | /api/enrich/config | Update enrichment settings |

---

### 6. Outbound Webhooks API
**Purpose:** Send event notifications to external services (Zapier, n8n, etc.)

**Database Tables:**
```sql
CREATE TABLE IF NOT EXISTS webhook_endpoints (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  url TEXT NOT NULL,
  secret TEXT, -- For signing payloads
  events TEXT[] NOT NULL, -- ['lead.created', 'lead.converted', 'call.completed']
  is_active BOOLEAN DEFAULT true,
  retry_config JSONB DEFAULT '{"max_attempts": 3, "backoff_seconds": [60, 300, 900]}',
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  endpoint_id UUID REFERENCES webhook_endpoints(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT DEFAULT 'pending', -- 'pending', 'success', 'failed'
  attempts INTEGER DEFAULT 0,
  response_code INTEGER,
  response_body TEXT,
  next_retry_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);
CREATE INDEX idx_webhook_deliveries_endpoint ON webhook_deliveries(endpoint_id);
CREATE INDEX idx_webhook_deliveries_status ON webhook_deliveries(status) WHERE status = 'pending';
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | /api/webhooks/endpoints | List all endpoints |
| POST | /api/webhooks/endpoints | Create endpoint |
| PUT | /api/webhooks/endpoints/:id | Update endpoint |
| DELETE | /api/webhooks/endpoints/:id | Delete endpoint |
| POST | /api/webhooks/endpoints/:id/test | Send test payload |
| GET | /api/webhooks/endpoints/:id/deliveries | Get delivery history |
| GET | /api/webhooks/events | List available events |
| POST | /api/webhooks/deliveries/:id/retry | Retry failed delivery |

---

### 7. Team & Permissions API
**Purpose:** Manage user roles (admin/manager/caller) and RBAC

**Database Tables:**
```sql
-- Extend existing user_xp table
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS permissions JSONB DEFAULT '{}';

CREATE TABLE IF NOT EXISTS roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE, -- 'admin', 'manager', 'caller'
  description TEXT,
  permissions JSONB NOT NULL, -- { "leads.create": true, "leads.delete": false, ... }
  is_system BOOLEAN DEFAULT false, -- System roles can't be deleted
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS user_roles (
  user_id UUID NOT NULL,
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  assigned_by UUID REFERENCES auth.users(id),
  assigned_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);
CREATE INDEX idx_user_roles_user ON user_roles(user_id);

-- Seed default roles
INSERT INTO roles (name, description, permissions, is_system) VALUES
  ('admin', 'Full access to all features', '{"*": true}', true),
  ('manager', 'Team management + all caller permissions', '{"team.*": true, "reports.*": true, "leads.*": true, "calls.*": true, "sms.*": true}', true),
  ('caller', 'Basic sales operations', '{"leads.read": true, "leads.update": true, "calls.*": true, "sms.*": true}', true)
ON CONFLICT (name) DO NOTHING;
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | /api/team/roles | List all roles |
| GET | /api/team/roles/:id | Get role details |
| POST | /api/team/roles | Create custom role |
| PUT | /api/team/roles/:id | Update role permissions |
| DELETE | /api/team/roles/:id | Delete custom role |
| GET | /api/team/members | List team members with roles |
| PUT | /api/team/members/:userId/role | Assign role to user |
| GET | /api/team/permissions | Get current user permissions |

---

### 8. Analytics Export API
**Purpose:** Export analytics with custom date ranges and CSV format

**Database Tables:**
```sql
-- Uses existing export_jobs table with analytics-specific queries

CREATE TABLE IF NOT EXISTS saved_reports (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  name TEXT NOT NULL,
  report_type TEXT NOT NULL, -- 'calls', 'conversions', 'pipeline', 'activity', 'custom'
  config JSONB NOT NULL, -- Date range, filters, columns, grouping
  schedule TEXT, -- 'daily', 'weekly', 'monthly', NULL for on-demand
  last_run_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

**API Endpoints:**
| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | /api/analytics/export/calls | Export call data |
| GET | /api/analytics/export/conversions | Export conversion data |
| GET | /api/analytics/export/pipeline | Export pipeline data |
| GET | /api/analytics/export/activity | Export activity summary |
| POST | /api/analytics/reports | Create saved report |
| GET | /api/analytics/reports | List saved reports |
| GET | /api/analytics/reports/:id/run | Run saved report |
| DELETE | /api/analytics/reports/:id | Delete saved report |

---

## Implementation Order

1. **Trade Tags API** (Simple, no external deps) - 4 hours
2. **Sales Playbook API** (Extends existing) - 6 hours
3. **Team & Permissions API** (Foundation for others) - 8 hours
4. **Analytics Export API** (Uses existing data) - 6 hours
5. **Competitor Tracking API** (Standalone feature) - 6 hours
6. **Bulk Import/Export API** (Background jobs) - 8 hours
7. **Outbound Webhooks API** (Event system) - 8 hours
8. **Apollo Enrichment API** (External integration) - 8 hours

**Total Estimated Hours:** ~54 hours (~8 days @ 7hrs/day)

---

## Files to Create

### Route Files (6 new)
- `src/routes/tags.js` - Trade Tags API
- `src/routes/competitors.js` - Competitor Tracking API
- `src/routes/import.js` - Bulk Import API
- `src/routes/export.js` - Export APIs (bulk + analytics)
- `src/routes/outboundWebhooks.js` - Outbound Webhooks API
- `src/routes/permissions.js` - Team & Permissions API

### Service Files (3 new)
- `src/services/enrichment.js` - Apollo enrichment logic
- `src/services/webhookDelivery.js` - Webhook delivery + retry logic
- `src/services/exportService.js` - CSV/JSON export generation

### Migrations (7 new)
1. `20260202_trade_tags.sql`
2. `20260202_playbook_sections.sql`
3. `20260202_competitors.sql`
4. `20260202_import_export_jobs.sql`
5. `20260202_enrichment.sql`
6. `20260202_outbound_webhooks.sql`
7. `20260202_team_permissions.sql`

---

## Success Criteria

- [ ] All 8 APIs functional with CRUD operations
- [ ] All endpoints protected with proper auth
- [ ] Rate limiting on write operations
- [ ] Proper error handling and validation
- [ ] API documentation in code comments
- [ ] Migrations applied successfully
- [ ] Manual testing of each endpoint
- [ ] Update SYSTEM-INTEL.md with new feature status

---

## Notes

- Use `supabaseAdmin` for all backend queries (RLS bypass)
- Follow existing patterns from `leads.js` and `sequences.js`
- Use `writeLimiter` for mutation endpoints
- Use `importLimiter` for bulk operations
- Keep services stateless
- Log errors to console for Railway monitoring