64 lines
2.7 KiB
SQL
64 lines
2.7 KiB
SQL
-- ============================================================================
|
|
-- Email Sequences / Drip Campaigns — Automated follow-up sequences
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS email_campaigns (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'draft',
|
|
trigger_type VARCHAR(50) NOT NULL DEFAULT 'manual',
|
|
trigger_config JSONB,
|
|
created_by UUID REFERENCES users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS email_campaign_steps (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
campaign_id UUID NOT NULL REFERENCES email_campaigns(id) ON DELETE CASCADE,
|
|
step_order INT NOT NULL DEFAULT 0,
|
|
delay_days INT NOT NULL DEFAULT 0,
|
|
subject VARCHAR(500) NOT NULL,
|
|
body_text TEXT,
|
|
body_html TEXT,
|
|
variables JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS email_campaign_subscribers (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
campaign_id UUID NOT NULL REFERENCES email_campaigns(id) ON DELETE CASCADE,
|
|
lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
contact_name VARCHAR(255),
|
|
subscribed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
unsubscribed_at TIMESTAMPTZ,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
current_step INT NOT NULL DEFAULT 0,
|
|
last_sent_at TIMESTAMPTZ,
|
|
CONSTRAINT uq_campaign_lead UNIQUE (campaign_id, lead_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS email_campaign_logs (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
campaign_id UUID REFERENCES email_campaigns(id) ON DELETE CASCADE,
|
|
subscriber_id UUID REFERENCES email_campaign_subscribers(id) ON DELETE CASCADE,
|
|
step_id UUID REFERENCES email_campaign_steps(id) ON DELETE SET NULL,
|
|
recipient VARCHAR(255) NOT NULL,
|
|
subject VARCHAR(500),
|
|
body_text TEXT,
|
|
sent_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
status VARCHAR(50) NOT NULL DEFAULT 'sent',
|
|
error_message TEXT
|
|
);
|
|
|
|
-- Already defined inline in CREATE TABLE; IF NOT EXISTS not supported on this PostgreSQL version
|
|
-- ALTER TABLE email_campaign_subscribers ADD CONSTRAINT IF NOT EXISTS uq_campaign_lead UNIQUE (campaign_id, lead_id);
|
|
|
|
CREATE INDEX idx_campaign_steps_order ON email_campaign_steps(campaign_id, step_order);
|
|
CREATE INDEX idx_campaign_subscribers ON email_campaign_subscribers(campaign_id, status);
|
|
CREATE INDEX idx_campaign_logs ON email_campaign_logs(campaign_id, sent_at);
|