Lead Scoring Schema

-- Lead Scoring System for Product-Led Growth
-- Designed for Supabase Enterprise AE workflow
--
-- This schema demonstrates how to prioritize developer signups
-- for enterprise expansion based on internal usage signals
-- and external company enrichment.

-- ============================================================
-- CORE TABLES
-- ============================================================

-- Developer signups (simulates Supabase's internal user data)
CREATE TABLE developer_signups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  full_name TEXT,
  company_domain TEXT,  -- Extracted from email or provided
  signup_date TIMESTAMPTZ NOT NULL DEFAULT now(),
  signup_source TEXT,   -- 'organic', 'github', 'google', 'referral'

  -- Computed column: auto-detect company vs personal email
  is_company_email BOOLEAN GENERATED ALWAYS AS (
    email NOT LIKE '%@gmail.com'
    AND email NOT LIKE '%@yahoo.com'
    AND email NOT LIKE '%@hotmail.com'
    AND email NOT LIKE '%@outlook.com'
    AND email NOT LIKE '%@icloud.com'
  ) STORED,

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

-- Internal usage signals (what we'd get from Supabase platform)
CREATE TABLE internal_signals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  developer_id UUID NOT NULL REFERENCES developer_signups(id) ON DELETE CASCADE,

  -- Project metrics
  project_count INT DEFAULT 0,
  active_projects INT DEFAULT 0,

  -- Feature adoption (boolean flags)
  uses_auth BOOLEAN DEFAULT false,
  uses_storage BOOLEAN DEFAULT false,
  uses_edge_functions BOOLEAN DEFAULT false,
  uses_realtime BOOLEAN DEFAULT false,
  uses_vector BOOLEAN DEFAULT false,
  uses_cron BOOLEAN DEFAULT false,

  -- Billing
  billing_tier TEXT DEFAULT 'free',  -- 'free', 'pro', 'team', 'enterprise'
  tier_upgraded_at TIMESTAMPTZ,

  -- Team
  team_member_count INT DEFAULT 1,
  team_members_added_last_30d INT DEFAULT 0,

  -- Usage volume
  api_calls_30d BIGINT DEFAULT 0,
  api_calls_prev_30d BIGINT DEFAULT 0,

  -- Computed growth percentage
  api_call_growth_pct NUMERIC GENERATED ALWAYS AS (
    CASE
      WHEN api_calls_prev_30d > 0
      THEN ROUND(((api_calls_30d - api_calls_prev_30d)::NUMERIC / api_calls_prev_30d) * 100, 1)
      ELSE 0
    END
  ) STORED,

  last_active_at TIMESTAMPTZ,
  dashboard_logins_30d INT DEFAULT 0,
  captured_at TIMESTAMPTZ DEFAULT now()
);

-- Company enrichment (from Apollo, LinkedIn, Clearbit, etc.)
CREATE TABLE company_enrichment (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  domain TEXT NOT NULL UNIQUE,
  company_name TEXT,
  industry TEXT,
  employee_count INT,
  employee_range TEXT,  -- '1-10', '11-50', '51-200', '201-500', '501-1000', '1000+'
  company_type TEXT,    -- 'startup', 'scaleup', 'enterprise', 'agency'
  funding_stage TEXT,   -- 'seed', 'series_a', 'series_b', etc.
  total_funding_usd BIGINT,
  tech_stack JSONB DEFAULT '[]',
  linkedin_url TEXT,
  enriched_at TIMESTAMPTZ DEFAULT now()
);

-- Key stakeholders at the company
CREATE TABLE stakeholders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_domain TEXT NOT NULL REFERENCES company_enrichment(domain) ON DELETE CASCADE,
  full_name TEXT NOT NULL,
  title TEXT,
  department TEXT,  -- 'engineering', 'product', 'security', 'finance'
  seniority TEXT,   -- 'ic', 'manager', 'director', 'vp', 'c-level'
  linkedin_url TEXT,
  is_decision_maker BOOLEAN DEFAULT false,
  is_technical BOOLEAN DEFAULT false,
  is_budget_holder BOOLEAN DEFAULT false,
  enriched_at TIMESTAMPTZ DEFAULT now()
);

-- Expansion signals (hiring, news, tech changes)
CREATE TABLE expansion_signals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_domain TEXT NOT NULL REFERENCES company_enrichment(domain) ON DELETE CASCADE,
  signal_type TEXT NOT NULL,  -- 'hiring', 'funding', 'tech_migration', 'news'
  signal_title TEXT NOT NULL,
  signal_detail TEXT,
  signal_date DATE,
  signal_strength TEXT DEFAULT 'medium',  -- 'low', 'medium', 'high'
  captured_at TIMESTAMPTZ DEFAULT now()
);

-- ============================================================
-- SCORING TABLE (with computed total + tier)
-- ============================================================

CREATE TABLE lead_scores (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  developer_id UUID NOT NULL REFERENCES developer_signups(id) ON DELETE CASCADE,

  -- Component scores (0-100)
  company_score INT DEFAULT 0,      -- Is this an enterprise target?
  usage_score INT DEFAULT 0,        -- How active are they?
  expansion_score INT DEFAULT 0,    -- Are there expansion signals?
  champion_score INT DEFAULT 0,     -- How engaged is the developer?
  timing_score INT DEFAULT 0,       -- Is now a good time?

  -- Computed total score with weighted formula
  total_score INT GENERATED ALWAYS AS (
    ROUND(
      (company_score * 0.25) +      -- 25% weight
      (usage_score * 0.30) +        -- 30% weight (most important for PLG)
      (expansion_score * 0.20) +    -- 20% weight
      (champion_score * 0.15) +     -- 15% weight
      (timing_score * 0.10)         -- 10% weight
    )
  ) STORED,

  -- Computed tier assignment
  tier TEXT GENERATED ALWAYS AS (
    CASE
      WHEN (company_score * 0.25 + usage_score * 0.30 + expansion_score * 0.20
            + champion_score * 0.15 + timing_score * 0.10) >= 80 THEN 'hot'
      WHEN (company_score * 0.25 + usage_score * 0.30 + expansion_score * 0.20
            + champion_score * 0.15 + timing_score * 0.10) >= 60 THEN 'warm'
      WHEN (company_score * 0.25 + usage_score * 0.30 + expansion_score * 0.20
            + champion_score * 0.15 + timing_score * 0.10) >= 40 THEN 'nurture'
      ELSE 'monitor'
    END
  ) STORED,

  suggested_action TEXT,
  scored_at TIMESTAMPTZ DEFAULT now()
);

-- ============================================================
-- DASHBOARD VIEW (joins everything for the UI)
-- ============================================================

CREATE OR REPLACE VIEW lead_dashboard AS
SELECT
  d.id AS developer_id,
  d.email,
  d.full_name AS champion_name,
  d.company_domain,
  d.is_company_email,
  c.company_name,
  c.industry,
  c.employee_count,
  c.employee_range,
  c.funding_stage,
  c.total_funding_usd,
  c.tech_stack,
  i.project_count,
  i.billing_tier,
  i.team_member_count,
  i.api_calls_30d,
  i.api_call_growth_pct,
  i.uses_auth,
  i.uses_edge_functions,
  i.uses_realtime,
  i.uses_vector,
  i.last_active_at,

  -- Features as array for easy display
  ARRAY_REMOVE(ARRAY[
    CASE WHEN i.uses_auth THEN 'Auth' END,
    CASE WHEN i.uses_storage THEN 'Storage' END,
    CASE WHEN i.uses_edge_functions THEN 'Edge Functions' END,
    CASE WHEN i.uses_realtime THEN 'Realtime' END,
    CASE WHEN i.uses_vector THEN 'Vector' END,
    CASE WHEN i.uses_cron THEN 'Cron' END
  ], NULL) AS features_used,

  s.total_score,
  s.tier,
  s.suggested_action,

  (SELECT COUNT(*) FROM stakeholders WHERE company_domain = d.company_domain) AS stakeholder_count,
  (SELECT COUNT(*) FROM expansion_signals WHERE company_domain = d.company_domain) AS signal_count

FROM developer_signups d
LEFT JOIN company_enrichment c ON c.domain = d.company_domain
LEFT JOIN internal_signals i ON i.developer_id = d.id
LEFT JOIN lead_scores s ON s.developer_id = d.id
WHERE d.is_company_email = true  -- Only show company emails
ORDER BY s.total_score DESC NULLS LAST;

-- ============================================================
-- ROW LEVEL SECURITY
-- ============================================================

ALTER TABLE developer_signups ENABLE ROW LEVEL SECURITY;
ALTER TABLE internal_signals ENABLE ROW LEVEL SECURITY;
ALTER TABLE company_enrichment ENABLE ROW LEVEL SECURITY;
ALTER TABLE stakeholders ENABLE ROW LEVEL SECURITY;
ALTER TABLE expansion_signals ENABLE ROW LEVEL SECURITY;
ALTER TABLE lead_scores ENABLE ROW LEVEL SECURITY;

-- Public read for demo purposes
CREATE POLICY "public read" ON developer_signups FOR SELECT USING (true);
CREATE POLICY "public read" ON internal_signals FOR SELECT USING (true);
CREATE POLICY "public read" ON company_enrichment FOR SELECT USING (true);
CREATE POLICY "public read" ON stakeholders FOR SELECT USING (true);
CREATE POLICY "public read" ON expansion_signals FOR SELECT USING (true);
CREATE POLICY "public read" ON lead_scores FOR SELECT USING (true);

-- Sample Lead: VelocityPay (Hot - Score 87)
-- Fintech scaling fast, Auth0 contract renewal window

INSERT INTO developer_signups (id, email, full_name, company_domain, signup_source)
VALUES ('11111111-1111-1111-1111-111111111111',
        'marcus.chen@velocitypay.io', 'Marcus Chen', 'velocitypay.io', 'github');

INSERT INTO company_enrichment (domain, company_name, industry, employee_count,
                                employee_range, funding_stage, total_funding_usd, tech_stack)
VALUES ('velocitypay.io', 'VelocityPay', 'Financial Services', 180,
        '51-200', 'series_b', 45000000, '["AWS", "PostgreSQL", "Auth0", "React"]');

INSERT INTO internal_signals (developer_id, project_count, uses_auth, uses_storage,
                              uses_edge_functions, uses_realtime, billing_tier,
                              team_member_count, api_calls_30d, api_calls_prev_30d)
VALUES ('11111111-1111-1111-1111-111111111111', 4, true, true, true, true,
        'pro', 5, 2800000, 800000);  -- 250% API growth!

INSERT INTO stakeholders (company_domain, full_name, title, is_decision_maker, is_technical)
VALUES
  ('velocitypay.io', 'Sarah Kim', 'VP of Engineering', true, true),
  ('velocitypay.io', 'David Park', 'CTO', true, true);

INSERT INTO expansion_signals (company_domain, signal_type, signal_title, signal_strength)
VALUES
  ('velocitypay.io', 'hiring', 'Hiring 4 Backend Engineers', 'high'),
  ('velocitypay.io', 'tech_migration', 'Auth0 contract renewal coming up', 'high'),
  ('velocitypay.io', 'funding', 'Series B closed $30M', 'high');

INSERT INTO lead_scores (developer_id, company_score, usage_score, expansion_score,
                         champion_score, timing_score, suggested_action)
VALUES ('11111111-1111-1111-1111-111111111111', 85, 95, 90, 80, 85,
        'Champion is highly active (5 team members, 250% API growth). Auth0 contract
         renewal creates migration window. Reach out to Marcus, offer to connect
         with VP Eng Sarah Kim for compliance/security conversation.');

-- Additional leads follow same pattern:
-- HomeFront Technologies (Hot, 850 employees, Firebase scaling issues)
-- CloudMetrics (Warm, job posts mention Supabase)
-- MediSync (Warm, healthcare, HIPAA needs)
-- TaskFlow (Nurture, seed stage, free tier)