supabase/migrations/001_initial_schema.sql

-- ============================================================
-- AI Client Acquisition System — Supabase Schema
-- Run this in Supabase SQL Editor
-- ============================================================

-- Enable pgcrypto for UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- for fuzzy name matching

-- ─── ENUMS ──────────────────────────────────────────────────

CREATE TYPE company_status AS ENUM (
  'discovered', 'researching', 'profiled',
  'qualified', 'nurture', 'archived', 'suppressed'
);

CREATE TYPE contact_status AS ENUM (
  'found', 'email_verified', 'email_invalid',
  'linkedin_only', 'suppressed'
);

CREATE TYPE lead_tier AS ENUM ('hot', 'warm', 'nurture', 'archive');

CREATE TYPE outreach_channel AS ENUM ('email', 'linkedin');

CREATE TYPE outreach_status AS ENUM (
  'queued', 'sent', 'opened', 'replied',
  'bounced', 'failed', 'review_needed'
);

CREATE TYPE intent_type AS ENUM (
  'interested', 'question', 'not_now',
  'not_interested', 'out_of_office', 'wrong_person', 'unknown'
);

CREATE TYPE review_status AS ENUM ('pending', 'approved', 'rejected', 'edited');

-- ─── CORE TABLES ────────────────────────────────────────────

-- ICP Configuration (editable from dashboard)
CREATE TABLE icp_config (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL DEFAULT 'default',
  min_employees INTEGER NOT NULL DEFAULT 50,
  industries  TEXT[] NOT NULL DEFAULT ARRAY['technology','manufacturing','logistics','healthcare','finance'],
  exclude_industries TEXT[] NOT NULL DEFAULT ARRAY['government','non-profit','education'],
  geographies TEXT[] NOT NULL DEFAULT ARRAY['US','UK','AU','UAE','SA'],
  keywords    TEXT[] NOT NULL DEFAULT ARRAY['automation','digital transformation','AI','operations'],
  tech_signals TEXT[] DEFAULT ARRAY['salesforce','hubspot','legacy_erp','sap'],
  score_threshold INTEGER NOT NULL DEFAULT 70,
  is_active   BOOLEAN NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Weekly rotation state
CREATE TABLE rotation_state (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  week_number INTEGER NOT NULL,          -- 1=USA, 2=UK, 3=AU, 4=Gulf
  region      TEXT NOT NULL,
  started_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  companies_found INTEGER DEFAULT 0,
  leads_qualified INTEGER DEFAULT 0
);

-- Companies discovered
CREATE TABLE companies (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  domain          TEXT NOT NULL UNIQUE,
  name            TEXT NOT NULL,
  industry        TEXT,
  employee_count  INTEGER,
  employee_range  TEXT,                  -- "50-200", "200-500" etc
  description     TEXT,
  website_url     TEXT,
  linkedin_url    TEXT,
  country         TEXT,
  region          TEXT,
  tech_stack      TEXT[],
  growth_signals  JSONB DEFAULT '[]',    -- job posts, news, funding
  raw_data        JSONB DEFAULT '{}',
  source          TEXT NOT NULL,         -- 'serper', 'linkedin', 'manual'
  status          company_status NOT NULL DEFAULT 'discovered',
  discovered_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_companies_domain ON companies(domain);
CREATE INDEX idx_companies_status ON companies(status);
CREATE INDEX idx_companies_country ON companies(country);
CREATE INDEX idx_companies_name_trgm ON companies USING GIN (name gin_trgm_ops);

-- Contacts (decision-makers)
CREATE TABLE contacts (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id        UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  full_name         TEXT NOT NULL,
  first_name        TEXT,
  last_name         TEXT,
  title             TEXT NOT NULL,
  seniority         TEXT,               -- 'c_suite','vp','director','manager'
  email             TEXT,
  email_verified    BOOLEAN DEFAULT FALSE,
  email_source      TEXT,              -- 'hunter','snov','pattern'
  linkedin_url      TEXT,
  linkedin_verified BOOLEAN DEFAULT FALSE,
  status            contact_status NOT NULL DEFAULT 'found',
  suppressed        BOOLEAN NOT NULL DEFAULT FALSE,
  suppressed_at     TIMESTAMPTZ,
  suppressed_reason TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_contacts_company ON contacts(company_id);
CREATE INDEX idx_contacts_email ON contacts(email);
CREATE INDEX idx_contacts_suppressed ON contacts(suppressed);

-- Evidence gathered during research
CREATE TABLE evidence (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id  UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  type        TEXT NOT NULL,            -- 'job_posting','news','social_post','website_text'
  content     TEXT NOT NULL,
  source_url  TEXT,
  ai_signal   BOOLEAN DEFAULT FALSE,   -- does this mention AI/automation?
  collected_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_evidence_company ON evidence(company_id);

-- Lead profiles (LLM output)
CREATE TABLE lead_profiles (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  profile_summary     TEXT NOT NULL,
  pain_points         TEXT[] DEFAULT '{}',
  ai_use_case         TEXT,
  ai_readiness        TEXT NOT NULL DEFAULT 'medium', -- low/medium/high
  outreach_angle      TEXT,
  llm_model           TEXT NOT NULL,
  llm_confidence      NUMERIC(3,2),
  is_fallback         BOOLEAN DEFAULT FALSE,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Lead scores
CREATE TABLE lead_scores (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  contact_id          UUID REFERENCES contacts(id),
  total_score         INTEGER NOT NULL CHECK (total_score BETWEEN 0 AND 100),
  tier                lead_tier NOT NULL,
  company_fit         INTEGER,          -- 0-25
  ai_readiness        INTEGER,          -- 0-25
  decision_maker      INTEGER,          -- 0-20
  growth_signal       INTEGER,          -- 0-15
  engagement_potential INTEGER,         -- 0-15
  score_reasoning     TEXT,
  scored_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_scores_company ON lead_scores(company_id);
CREATE INDEX idx_scores_tier ON lead_scores(tier);
CREATE INDEX idx_scores_total ON lead_scores(total_score DESC);

-- ─── OUTREACH TABLES ────────────────────────────────────────

CREATE TABLE outreach_sequences (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  contact_id      UUID NOT NULL REFERENCES contacts(id),
  current_step    INTEGER NOT NULL DEFAULT 0,
  total_steps     INTEGER NOT NULL DEFAULT 5,
  next_action_at  TIMESTAMPTZ,
  status          TEXT NOT NULL DEFAULT 'active', -- active/paused/completed/stopped
  stopped_reason  TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE outreach_log (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sequence_id     UUID REFERENCES outreach_sequences(id),
  company_id      UUID NOT NULL REFERENCES companies(id),
  contact_id      UUID NOT NULL REFERENCES contacts(id),
  channel         outreach_channel NOT NULL,
  step_number     INTEGER NOT NULL,
  template_id     TEXT,
  message_hash    TEXT NOT NULL UNIQUE,  -- prevent duplicate sends
  subject         TEXT,
  status          outreach_status NOT NULL DEFAULT 'queued',
  provider_id     TEXT,                  -- external message ID from Resend/LinkedIn
  sent_at         TIMESTAMPTZ,
  opened_at       TIMESTAMPTZ,
  replied_at      TIMESTAMPTZ,
  bounced_at      TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_outreach_company ON outreach_log(company_id);
CREATE INDEX idx_outreach_hash ON outreach_log(message_hash);
CREATE INDEX idx_outreach_status ON outreach_log(status);

-- ─── ENGAGEMENT TABLES ──────────────────────────────────────

CREATE TABLE engagement_log (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  outreach_id     UUID REFERENCES outreach_log(id),
  company_id      UUID NOT NULL REFERENCES companies(id),
  contact_id      UUID NOT NULL REFERENCES contacts(id),
  signal_type     TEXT NOT NULL,         -- 'open','reply','bounce','linkedin_accept'
  intent          intent_type,
  raw_content     TEXT,                  -- actual reply text (for NLP)
  detected_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── SYSTEM TABLES ──────────────────────────────────────────

CREATE TABLE suppression_list (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT,
  domain      TEXT,
  reason      TEXT NOT NULL,             -- 'unsubscribed','bounced','manual'
  added_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_suppression_email ON suppression_list(email);
CREATE INDEX idx_suppression_domain ON suppression_list(domain);

CREATE TABLE human_review_queue (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type            TEXT NOT NULL,         -- 'outreach_approval','score_anomaly','escalation'
  company_id      UUID REFERENCES companies(id),
  contact_id      UUID REFERENCES contacts(id),
  payload         JSONB NOT NULL,        -- full context for reviewer
  status          review_status NOT NULL DEFAULT 'pending',
  reviewer_notes  TEXT,
  resolved_at     TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_review_status ON human_review_queue(status);

CREATE TABLE api_usage_log (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider    TEXT NOT NULL,             -- 'serper','hunter','snov','reoon'
  endpoint    TEXT,
  credits_used INTEGER DEFAULT 1,
  success     BOOLEAN NOT NULL,
  error_msg   TEXT,
  called_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE audit_log (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  action      TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id   UUID,
  actor       TEXT NOT NULL DEFAULT 'system',
  details     JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── SEED DATA ──────────────────────────────────────────────

INSERT INTO icp_config (name, min_employees, industries, geographies, keywords, score_threshold)
VALUES (
  'default',
  50,
  ARRAY['technology','software','manufacturing','logistics','supply_chain','healthcare','finance','real_estate_tech','retail_tech'],
  ARRAY['US','UK','AU','UAE','SA','SG'],
  ARRAY['automation','digital transformation','AI','machine learning','operations','workflow','efficiency'],
  70
);

INSERT INTO rotation_state (week_number, region)
VALUES (1, 'US');