my-fullstack-ai-platform/migrations/20260407113127_core_schema.sql

-- Migration: Core Schema for Campaigns and Leads
-- Description: Adds core tables and RLS policies for Phase 1 MVP.
-- Created: 2026-04-07 11:31:27

-- 1. Update organizations table
ALTER TABLE public.organizations
ADD COLUMN IF NOT EXISTS icp jsonb DEFAULT '{}',
ADD COLUMN IF NOT EXISTS company_profile jsonb DEFAULT '{}',
ADD COLUMN IF NOT EXISTS monthly_quota int DEFAULT 0,
ADD COLUMN IF NOT EXISTS current_leads_month int DEFAULT 0,
ADD COLUMN IF NOT EXISTS quota_reset_date timestamptz DEFAULT now();

-- 2. Create campaigns table
CREATE TABLE IF NOT EXISTS public.campaigns (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    search_account_id uuid NOT NULL REFERENCES auth.users(id),
    name text NOT NULL,
    industries jsonb DEFAULT '[]'::jsonb,
    locations jsonb DEFAULT '[]'::jsonb,
    headcount jsonb DEFAULT '[]'::jsonb,
    target_leads int NOT NULL,
    current_leads int DEFAULT 0,
    search_cursor text,
    status text DEFAULT 'pending'::text,
    created_at timestamptz DEFAULT now(),
    CONSTRAINT campaigns_status_check CHECK (status IN ('pending', 'running', 'paused', 'pipeline_completed', 'quota_reached'))
);

CREATE UNIQUE INDEX IF NOT EXISTS campaigns_name_org_unique ON public.campaigns (organization_id, name);

-- 3. Create leads table (Global deduplication & Enrichment Cache)
CREATE TABLE IF NOT EXISTS public.leads (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    linkedin_url text UNIQUE NOT NULL,
    name text NOT NULL,
    domain text,
    enriched_data jsonb DEFAULT '{}'::jsonb,
    last_enriched_at timestamptz,
    created_at timestamptz DEFAULT now()
);

-- 4. Create campaign_leads junction table
CREATE TABLE IF NOT EXISTS public.campaign_leads (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    campaign_id uuid NOT NULL REFERENCES public.campaigns(id) ON DELETE CASCADE,
    lead_id uuid NOT NULL REFERENCES public.leads(id) ON DELETE CASCADE,
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    status text DEFAULT 'pending'::text,
    qualification_data jsonb DEFAULT '{}'::jsonb,
    outreach_template jsonb DEFAULT '{}'::jsonb,
    created_at timestamptz DEFAULT now(),
    CONSTRAINT campaign_leads_status_check CHECK (status IN ('pending', 'qualified', 'disqualified'))
);

CREATE UNIQUE INDEX IF NOT EXISTS campaign_leads_unique ON public.campaign_leads (campaign_id, lead_id);

-- 5. Create lead_contacts table
CREATE TABLE IF NOT EXISTS public.lead_contacts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    lead_id uuid NOT NULL REFERENCES public.leads(id) ON DELETE CASCADE,
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    provider_id text,
    member_urn text,
    public_id text,
    name text NOT NULL,
    first_name text,
    last_name text,
    headline text,
    linkedin_url text NOT NULL,
    connection_status text DEFAULT 'none'::text,
    claimed_by_id uuid REFERENCES auth.users(id),
    created_at timestamptz DEFAULT now(),
    CONSTRAINT lead_contacts_connection_status_check CHECK (connection_status IN ('none', 'pending', 'retry_pending', 'accepted', 'no_contact'))
);

-- 6. Enable RLS
ALTER TABLE public.campaigns ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.leads ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.campaign_leads ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.lead_contacts ENABLE ROW LEVEL SECURITY;

-- 7. Implement RLS Policies

-- 7.1 Organizations Policies
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'tenant_can_view_organization') THEN
        CREATE POLICY "tenant_can_view_organization" ON public.organizations
        FOR SELECT USING ((SELECT organization_id FROM public.profiles WHERE id = auth.uid()) = id);
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'tenant_owner_can_update_organization') THEN
        CREATE POLICY "tenant_owner_can_update_organization" ON public.organizations
        FOR UPDATE USING ((SELECT organization_id FROM public.profiles WHERE id = auth.uid() AND role = 'owner') = id);
    END IF;
END $$;

-- 7.2 Campaigns Policies
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'tenant_can_all_campaigns') THEN
        CREATE POLICY "tenant_can_all_campaigns" ON public.campaigns
        FOR ALL USING ((SELECT organization_id FROM public.profiles WHERE id = auth.uid()) = organization_id)
        WITH CHECK ((SELECT organization_id FROM public.profiles WHERE id = auth.uid()) = organization_id);
    END IF;
END $$;

-- 7.3 Leads Policies (Global cache)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'authenticated_can_view_leads') THEN
        CREATE POLICY "authenticated_can_view_leads" ON public.leads
        FOR SELECT TO authenticated USING (true);
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'authenticated_can_insert_leads') THEN
        CREATE POLICY "authenticated_can_insert_leads" ON public.leads
        FOR INSERT TO authenticated WITH CHECK (true);
    END IF;
END $$;

-- 7.4 Campaign Leads Policies
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'tenant_can_all_campaign_leads') THEN
        CREATE POLICY "tenant_can_all_campaign_leads" ON public.campaign_leads
        FOR ALL USING ((SELECT organization_id FROM public.profiles WHERE id = auth.uid()) = organization_id)
        WITH CHECK ((SELECT organization_id FROM public.profiles WHERE id = auth.uid()) = organization_id);
    END IF;
END $$;

-- 7.5 Lead Contacts Policies
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'tenant_can_all_lead_contacts') THEN
        CREATE POLICY "tenant_can_all_lead_contacts" ON public.lead_contacts
        FOR ALL USING ((SELECT organization_id FROM public.profiles WHERE id = auth.uid()) = organization_id)
        WITH CHECK ((SELECT organization_id FROM public.profiles WHERE id = auth.uid()) = organization_id);
    END IF;
END $$;