┌─────────────────────────────────────────────────────────────────────────────┐
│                          CLIENT LIFECYCLE                                    │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   PROSPECT → QUALIFIED → PROPOSAL → ONBOARDING → ACTIVE → EXPANSION        │
│                                                                              │
│   Signals:     Discovery    SOW        Kickoff     Delivery    Upsell       │
│   Duration:    1-2 weeks    3-5 days   1 week      Ongoing     Quarterly    │
│   Owner:       Sales        Sales      PM          PM          CS + Sales   │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

-- Initial prospect data lives in audience_profiles
SELECT 
  ap.email,
  ap.company_name,
  ap.company_size,
  ap.job_title,
  ap.lead_score,
  ap.wtp_score
FROM audience_profiles ap
WHERE ap.lifecycle_stage = 'lead'
  AND ap.company_size IN ('11-50', '51-200', '201-500', '500+');

## Discovery Call Agenda (30 min)

### Intro (5 min)
- Quick intros
- Agenda confirmation
- Permission to take notes

### Current State (10 min)
- What automation/AI are you using today?
- What's working? What's not?
- Team size and structure?

### Pain Points (10 min)
- Biggest time sinks?
- What would 10x your output?
- What have you tried that failed?

### Next Steps (5 min)
- Propose audit or pilot project
- Timeline alignment
- Decision process

interface QualifiedLead {
  company_name: string;
  primary_contact: {
    name: string;
    email: string;
    title: string;
  };
  pain_points: string[];
  current_tools: string[];
  budget_range: 'under_2k' | '2k_5k' | '5k_10k' | '10k_plus';
  timeline: 'immediate' | '30_days' | '60_days' | '90_plus';
  decision_makers: string[];
  competition: string[];  // Other vendors they're considering
  fit_score: number;      // 0-100
}

## Agency Proposal Template

### Executive Summary
- Problem statement
- Proposed solution
- Expected outcomes
- Investment summary

### Scope of Work
- Phase 1: Discovery & Audit
- Phase 2: Build & Configure
- Phase 3: Test & Launch
- Phase 4: Optimize & Support

### Timeline
- Week 1: [milestones]
- Week 2: [milestones]
- ...

### Investment
- One-time: $X
- Monthly retainer: $Y (optional)
- Payment terms

### Team
- Your PM: [name]
- Technical lead: [name]

### Next Steps
- Sign SOW
- Kickoff call
- Tool access

-- When proposal is accepted, create client record
INSERT INTO agency_clients (
  canonical_id,
  user_id,
  company_name,
  industry,
  company_size,
  primary_contact_name,
  primary_contact_email,
  monthly_retainer,
  is_active
) VALUES (
  'client_' || gen_random_uuid()::text,
  :user_id,  -- Link to their user account
  :company_name,
  :industry,
  :company_size,
  :contact_name,
  :contact_email,
  :retainer_amount,
  true
);

## Client Onboarding Checklist

### Day 1: Kickoff
- [ ] Kickoff call completed
- [ ] Project created in Linear
- [ ] Slack channel created (#client-[name])
- [ ] Client added to project management

### Day 2-3: Access
- [ ] Tool credentials collected (encrypted in agency_credentials)
- [ ] API keys provisioned
- [ ] Test connections verified
- [ ] Documentation shared

### Day 4-5: Discovery
- [ ] Current workflow documented
- [ ] Data sources identified
- [ ] Integration points mapped
- [ ] Success metrics defined

### Week 1 End: Alignment
- [ ] Project plan approved
- [ ] Timeline confirmed
- [ ] Communication cadence set
- [ ] First milestone scheduled

-- Create project for the engagement
INSERT INTO agency_projects (
  canonical_id,
  client_id,
  name,
  description,
  start_date,
  end_date,
  budget,
  status
) VALUES (
  'proj_' || gen_random_uuid()::text,
  :client_id,
  :project_name,
  :description,
  :start_date,
  :end_date,
  :budget,
  'active'  -- 'proposal', 'active', 'paused', 'completed', 'cancelled'
);

-- Store client credentials (encrypted)
-- Note: In production, use Supabase Vault
CREATE TABLE agency_credentials (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id uuid REFERENCES agency_clients(id),
  
  service_name text NOT NULL,        -- 'shopify', 'instagram', 'mailchimp'
  credential_type text NOT NULL,     -- 'api_key', 'oauth_token', 'password'
  
  -- Encrypted fields (use pgcrypto or Vault)
  encrypted_value text NOT NULL,
  
  -- Metadata
  expires_at timestamptz,
  last_used_at timestamptz,
  is_valid boolean DEFAULT true,
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- RLS: Only agency admins can access
ALTER TABLE agency_credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Agency admins only" ON agency_credentials
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_roles ur
      WHERE ur.user_id = auth.uid()
        AND ur.organization_id = (SELECT id FROM organizations WHERE type = 'agency')
        AND ur.role IN ('admin', 'editor')
    )
  );

┌─────────────────────────────────────────────────────────────────┐
│                    DELIVERY WORKFLOW                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│   PLAN → BUILD → REVIEW → REVISE → APPROVE → DELIVER            │
│                                                                  │
│   Linear    Cursor    Client     Team      Client    Deploy     │
│   Ticket    Agent     Review     Update    Sign-off  + Docs     │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

-- Client health score calculation
CREATE OR REPLACE FUNCTION calculate_client_health(p_client_id uuid)
RETURNS integer AS $$
DECLARE
  health_score integer := 50;
  active_projects integer;
  overdue_deliverables integer;
  days_since_contact integer;
  revenue_trend numeric;
BEGIN
  -- Active projects (good)
  SELECT COUNT(*) INTO active_projects
  FROM agency_projects
  WHERE client_id = p_client_id AND status = 'active';
  health_score := health_score + (active_projects * 5);
  
  -- Overdue deliverables (bad)
  SELECT COUNT(*) INTO overdue_deliverables
  FROM agency_deliverables d
  JOIN agency_projects p ON d.project_id = p.id
  WHERE p.client_id = p_client_id
    AND d.due_date < current_date
    AND d.is_completed = false;
  health_score := health_score - (overdue_deliverables * 10);
  
  -- Days since last contact (bad if > 14)
  -- Would need a contact_log table for this
  
  -- Clamp to 0-100
  RETURN GREATEST(0, LEAST(100, health_score));
END;
$$ LANGUAGE plpgsql;

Current Service → Expansion Options
─────────────────────────────────────
Audit Only     → Pilot project
Pilot Project  → Full engagement
One-time       → Monthly retainer
Single area    → Multiple verticals
Manual process → Managed service

-- Track retainer history
CREATE TABLE agency_retainer_history (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  retainer_id uuid REFERENCES agency_retainers(id),
  
  change_type text NOT NULL,  -- 'increase', 'decrease', 'pause', 'cancel'
  from_amount numeric,
  to_amount numeric,
  
  reason text,
  effective_date date,
  
  created_at timestamptz DEFAULT now()
);

-- Retainer upgrade
UPDATE agency_retainers
SET monthly_amount = :new_amount,
    hours_included = :new_hours
WHERE client_id = :client_id
  AND is_active = true;

-- Log the change
INSERT INTO agency_retainer_history (retainer_id, change_type, from_amount, to_amount, reason, effective_date)
VALUES (:retainer_id, 'increase', :old_amount, :new_amount, 'Expanded scope to include video automation', :effective_date);

## Client Save Playbook

### Trigger: Client expressing concerns

1. **Acknowledge** - Don't get defensive
2. **Diagnose** - What's the real issue?
   - Deliverable quality?
   - Communication gap?
   - ROI unclear?
   - Budget pressure?
3. **Propose** - Solution options
   - Scope adjustment
   - Timeline extension
   - Price modification
   - Team change
4. **Commit** - Specific next steps
5. **Follow up** - Within 24 hours

-- When client churns
UPDATE agency_clients
SET is_active = false,
    churned_at = now(),
    churn_reason = :reason
WHERE id = :client_id;

-- Archive projects
UPDATE agency_projects
SET status = 'archived'
WHERE client_id = :client_id
  AND status = 'active';

-- Revoke credentials
UPDATE agency_credentials
SET is_valid = false
WHERE client_id = :client_id;

-- Client lifetime value
SELECT 
  c.company_name,
  c.created_at as client_since,
  COUNT(DISTINCT p.id) as total_projects,
  SUM(p.budget) as total_revenue,
  AVG(p.budget) as avg_project_value,
  EXTRACT(days FROM (COALESCE(c.churned_at, now()) - c.created_at)) as days_as_client
FROM agency_clients c
LEFT JOIN agency_projects p ON c.id = p.client_id
GROUP BY c.id
ORDER BY total_revenue DESC;

-- Monthly recurring revenue
SELECT 
  SUM(monthly_amount) as mrr,
  COUNT(*) as active_retainers
FROM agency_retainers
WHERE is_active = true;

-- Client health distribution
SELECT 
  CASE 
    WHEN calculate_client_health(id) >= 80 THEN 'healthy'
    WHEN calculate_client_health(id) >= 50 THEN 'at_risk'
    ELSE 'critical'
  END as health_tier,
  COUNT(*) as client_count
FROM agency_clients
WHERE is_active = true
GROUP BY 1;