-- Additional tables for comprehensive campaign management
-- Products/Websites Table - Store user product information
CREATE TABLE IF NOT EXISTS products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
name TEXT NOT NULL,
website_url TEXT,
description TEXT,
value_proposition TEXT,
target_audience TEXT,
analysis_data JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(user_id, name)
);
CREATE INDEX IF NOT EXISTS idx_products_user_id ON products(user_id);
-- ICPs (Ideal Customer Profiles) Table
CREATE TABLE IF NOT EXISTS icps (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
product_id UUID REFERENCES products(id) ON DELETE CASCADE,
name TEXT NOT NULL,
description TEXT,
search_criteria JSONB NOT NULL DEFAULT '{}'::jsonb,
-- e.g., {"keywords": "AI Startup CEO", "location": "Australia", "industry": "Technology", "jobTitle": "Founder OR CEO"}
target_characteristics JSONB DEFAULT '{}'::jsonb,
-- e.g., {"companySize": "1-50", "stage": "Series A", "industryFocus": ["AI", "SaaS"]}
generated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_icps_user_id ON icps(user_id);
CREATE INDEX IF NOT EXISTS idx_icps_product_id ON icps(product_id);
-- Campaigns Table
CREATE TABLE IF NOT EXISTS campaigns (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
product_id UUID REFERENCES products(id) ON DELETE CASCADE,
icp_id UUID REFERENCES icps(id) ON DELETE SET NULL,
name TEXT NOT NULL,
description TEXT,
status VARCHAR(20) NOT NULL DEFAULT 'draft',
-- draft, pending_approval, approved, active, paused, completed, cancelled
leads_selected JSONB DEFAULT '[]'::jsonb,
-- Array of profile URLs or lead IDs selected by user
message_template JSONB DEFAULT '{}'::jsonb,
-- Initial message template
follow_up_sequence JSONB DEFAULT '[]'::jsonb,
-- Dynamic follow-up sequence generated by AI
settings JSONB DEFAULT '{}'::jsonb,
-- Campaign settings like send delays, max messages per day, etc.
started_at TIMESTAMP WITH TIME ZONE,
completed_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT valid_status CHECK (status IN ('draft', 'pending_approval', 'approved', 'active', 'paused', 'completed', 'cancelled'))
);
CREATE INDEX IF NOT EXISTS idx_campaigns_user_id ON campaigns(user_id);
CREATE INDEX IF NOT EXISTS idx_campaigns_status ON campaigns(status);
CREATE INDEX IF NOT EXISTS idx_campaigns_product_id ON campaigns(product_id);
CREATE INDEX IF NOT EXISTS idx_campaigns_icp_id ON campaigns(icp_id);
-- Campaign Leads (Join table for leads in a campaign)
CREATE TABLE IF NOT EXISTS campaign_leads (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
profile_url TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'selected',
-- selected, message_sent, replied, connected, not_interested, unsubscribed
sequence_stage INTEGER DEFAULT 0,
-- Current stage in follow-up sequence (0 = initial message)
last_message_sent_at TIMESTAMP WITH TIME ZONE,
next_message_scheduled_at TIMESTAMP WITH TIME ZONE,
added_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(campaign_id, lead_id)
);
CREATE INDEX IF NOT EXISTS idx_campaign_leads_campaign_id ON campaign_leads(campaign_id);
CREATE INDEX IF NOT EXISTS idx_campaign_leads_lead_id ON campaign_leads(lead_id);
CREATE INDEX IF NOT EXISTS idx_campaign_leads_status ON campaign_leads(status);
CREATE INDEX IF NOT EXISTS idx_campaign_leads_next_scheduled ON campaign_leads(next_message_scheduled_at) WHERE next_message_scheduled_at IS NOT NULL;
-- Campaign Analytics Table
CREATE TABLE IF NOT EXISTS campaign_analytics (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
date DATE NOT NULL,
leads_total INTEGER DEFAULT 0,
messages_sent INTEGER DEFAULT 0,
messages_opened INTEGER DEFAULT 0,
replies_received INTEGER DEFAULT 0,
connections_made INTEGER DEFAULT 0,
meetings_booked INTEGER DEFAULT 0,
conversions INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(campaign_id, date)
);
CREATE INDEX IF NOT EXISTS idx_campaign_analytics_campaign_id ON campaign_analytics(campaign_id);
CREATE INDEX IF NOT EXISTS idx_campaign_analytics_date ON campaign_analytics(date);
-- Message Events (Track message opens, clicks, replies)
CREATE TABLE IF NOT EXISTS message_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
campaign_id UUID REFERENCES campaigns(id) ON DELETE SET NULL,
event_type VARCHAR(20) NOT NULL,
-- sent, delivered, opened, clicked, replied, connected
event_data JSONB DEFAULT '{}'::jsonb,
occurred_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT valid_event_type CHECK (event_type IN ('sent', 'delivered', 'opened', 'clicked', 'replied', 'connected', 'bounced', 'unsubscribed'))
);
CREATE INDEX IF NOT EXISTS idx_message_events_message_id ON message_events(message_id);
CREATE INDEX IF NOT EXISTS idx_message_events_campaign_id ON message_events(campaign_id);
CREATE INDEX IF NOT EXISTS idx_message_events_event_type ON message_events(event_type);
CREATE INDEX IF NOT EXISTS idx_message_events_occurred_at ON message_events(occurred_at);
-- Triggers for updated_at
CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_icps_updated_at BEFORE UPDATE ON icps
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_campaigns_updated_at BEFORE UPDATE ON campaigns
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_campaign_leads_updated_at BEFORE UPDATE ON campaign_leads
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_campaign_analytics_updated_at BEFORE UPDATE ON campaign_analytics
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
