-- LinkedIn Lead Automation MCP Server - PostgreSQL Schema for Neon
-- Run this SQL in Neon SQL Editor to create all tables
-- This version uses TEXT for IDs (UUIDs generated in application code)
-- API Keys Table
CREATE TABLE IF NOT EXISTS api_keys (
id TEXT PRIMARY KEY,
hash TEXT NOT NULL UNIQUE,
tier VARCHAR(50) NOT NULL DEFAULT 'starter',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_used TIMESTAMP WITH TIME ZONE,
is_active BOOLEAN DEFAULT true,
CONSTRAINT valid_tier CHECK (tier IN ('starter', 'professional', 'agency', 'enterprise'))
);
CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_active ON api_keys(is_active);
-- Sessions Table
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
cookies JSONB NOT NULL DEFAULT '[]'::jsonb,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
is_valid BOOLEAN DEFAULT true,
UNIQUE(user_id)
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_valid ON sessions(is_valid);
-- Leads Table
CREATE TABLE IF NOT EXISTS leads (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
profile_url TEXT NOT NULL,
name TEXT,
title TEXT,
company TEXT,
location TEXT,
experience JSONB DEFAULT '[]'::jsonb,
education JSONB DEFAULT '[]'::jsonb,
skills JSONB DEFAULT '[]'::jsonb,
summary TEXT,
score INTEGER,
score_reasoning TEXT,
analyzed_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(user_id, profile_url)
);
CREATE INDEX IF NOT EXISTS idx_leads_user_id ON leads(user_id);
CREATE INDEX IF NOT EXISTS idx_leads_profile_url ON leads(profile_url);
CREATE INDEX IF NOT EXISTS idx_leads_score ON leads(score) WHERE score IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_leads_analyzed_at ON leads(analyzed_at);
-- Messages Table
CREATE TABLE IF NOT EXISTS messages (
id TEXT PRIMARY KEY,
lead_id TEXT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
profile_url TEXT NOT NULL,
message_text TEXT NOT NULL,
sent_at TIMESTAMP WITH TIME ZONE NOT NULL,
sequence_stage INTEGER DEFAULT 0,
response_received BOOLEAN DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_messages_lead_id ON messages(lead_id);
CREATE INDEX IF NOT EXISTS idx_messages_user_id ON messages(user_id);
CREATE INDEX IF NOT EXISTS idx_messages_profile_url ON messages(profile_url);
CREATE INDEX IF NOT EXISTS idx_messages_sent_at ON messages(sent_at);
CREATE INDEX IF NOT EXISTS idx_messages_sequence_stage ON messages(sequence_stage);
-- Usage Tracking Table
CREATE TABLE IF NOT EXISTS usage (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
month VARCHAR(7) NOT NULL,
profiles INTEGER DEFAULT 0,
messages INTEGER DEFAULT 0,
sequences INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(user_id, month)
);
CREATE INDEX IF NOT EXISTS idx_usage_user_id ON usage(user_id);
CREATE INDEX IF NOT EXISTS idx_usage_month ON usage(month);
CREATE INDEX IF NOT EXISTS idx_usage_user_month ON usage(user_id, month);
-- Follow-up Sequences Table
CREATE TABLE IF NOT EXISTS sequences (
id TEXT PRIMARY KEY,
lead_id TEXT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
profile_url TEXT NOT NULL,
messages JSONB NOT NULL DEFAULT '[]'::jsonb,
is_active BOOLEAN DEFAULT true,
completion_reason TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_sequences_lead_id ON sequences(lead_id);
CREATE INDEX IF NOT EXISTS idx_sequences_user_id ON sequences(user_id);
CREATE INDEX IF NOT EXISTS idx_sequences_profile_url ON sequences(profile_url);
CREATE INDEX IF NOT EXISTS idx_sequences_active ON sequences(is_active);
-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ language 'plpgsql';
-- Drop existing triggers if they exist, then recreate
DROP TRIGGER IF EXISTS update_sessions_updated_at ON sessions;
DROP TRIGGER IF EXISTS update_leads_updated_at ON leads;
DROP TRIGGER IF EXISTS update_usage_updated_at ON usage;
DROP TRIGGER IF EXISTS update_sequences_updated_at ON sequences;
-- Triggers to automatically update updated_at
CREATE TRIGGER update_sessions_updated_at BEFORE UPDATE ON sessions
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_leads_updated_at BEFORE UPDATE ON leads
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_usage_updated_at BEFORE UPDATE ON usage
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_sequences_updated_at BEFORE UPDATE ON sequences
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
