-- Migration: Lead Generation Engine tables
-- Created: 2026-07-23
-- Creates: ad_templates, ad_creatives, ad_keywords, lead_attribution,
-- optimization_rules, optimization_logs
-- =============================================================================
-- Ad Templates — reusable campaign structures by vertical
-- =============================================================================
CREATE TABLE IF NOT EXISTS ad_templates (
id CHAR(36) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
slug VARCHAR(100) NOT NULL UNIQUE,
vertical VARCHAR(50) NOT NULL,
description TEXT DEFAULT '',
budget_recommendation FLOAT,
target_cpa FLOAT,
bidding_strategy VARCHAR(50) DEFAULT 'target_cpa',
structure_json JSON,
is_active BOOLEAN NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS ix_ad_templates_slug ON ad_templates (slug);
CREATE INDEX IF NOT EXISTS ix_ad_templates_vertical ON ad_templates (vertical);
-- =============================================================================
-- Ad Creatives — copy variants for A/B testing
-- =============================================================================
CREATE TABLE IF NOT EXISTS ad_creatives (
id CHAR(36) PRIMARY KEY,
company_id CHAR(36),
vertical VARCHAR(50) NOT NULL,
service_type VARCHAR(100) NOT NULL,
ad_type VARCHAR(50) NOT NULL,
headlines JSON DEFAULT '[]',
descriptions JSON DEFAULT '[]',
display_url VARCHAR(500) DEFAULT '',
final_url VARCHAR(500) DEFAULT '',
image_url VARCHAR(500) DEFAULT '',
call_to_action VARCHAR(100) DEFAULT '',
total_impressions BIGINT DEFAULT 0,
total_clicks INTEGER DEFAULT 0,
total_conversions FLOAT DEFAULT 0.0,
avg_ctr FLOAT,
is_active BOOLEAN NOT NULL DEFAULT 1,
is_winner BOOLEAN DEFAULT 0,
metadata_json JSON DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS ix_ad_creatives_company_id ON ad_creatives (company_id);
CREATE INDEX IF NOT EXISTS ix_ad_creatives_vertical ON ad_creatives (vertical);
-- =============================================================================
-- Ad Keywords — keyword tracking per campaign/ad group
-- =============================================================================
CREATE TABLE IF NOT EXISTS ad_keywords (
id CHAR(36) PRIMARY KEY,
company_id CHAR(36) NOT NULL,
source_service VARCHAR(50) NOT NULL,
external_campaign_id VARCHAR(128) NOT NULL,
external_ad_group_id VARCHAR(128),
text VARCHAR(255) NOT NULL,
match_type VARCHAR(20) DEFAULT 'phrase',
max_cpc FLOAT,
target_cpa FLOAT,
status VARCHAR(20) DEFAULT 'enabled',
total_impressions BIGINT DEFAULT 0,
total_clicks INTEGER DEFAULT 0,
total_spend FLOAT DEFAULT 0.0,
total_conversions FLOAT DEFAULT 0.0,
avg_position FLOAT,
avg_ctr FLOAT,
metadata_json JSON DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
UNIQUE (company_id, source_service, external_ad_group_id, text, match_type)
);
CREATE INDEX IF NOT EXISTS ix_ad_keywords_company_id ON ad_keywords (company_id);
CREATE INDEX IF NOT EXISTS ix_ad_keywords_external_campaign ON ad_keywords (external_campaign_id);
CREATE INDEX IF NOT EXISTS ix_ad_keywords_external_ad_group ON ad_keywords (external_ad_group_id);
-- =============================================================================
-- Lead Attribution — UTM → lead → deal tracking
-- =============================================================================
CREATE TABLE IF NOT EXISTS lead_attribution (
id CHAR(36) PRIMARY KEY,
company_id CHAR(36) NOT NULL,
external_lead_id VARCHAR(200),
lead_source VARCHAR(50) DEFAULT '',
utm_source VARCHAR(100) DEFAULT '',
utm_campaign VARCHAR(255) DEFAULT '',
utm_ad_group VARCHAR(255) DEFAULT '',
utm_ad VARCHAR(255) DEFAULT '',
utm_content VARCHAR(255) DEFAULT '',
utm_medium VARCHAR(50) DEFAULT '',
utm_term VARCHAR(255) DEFAULT '',
click_date TIMESTAMP,
lead_date TIMESTAMP,
click_cost FLOAT,
total_ad_spend FLOAT,
external_deal_id VARCHAR(200),
deal_amount FLOAT,
deal_closed_date TIMESTAMP,
roas FLOAT,
attribution_status VARCHAR(20) DEFAULT 'click',
attribution_window_days INTEGER DEFAULT 30,
metadata_json JSON DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS ix_lead_attribution_company_id ON lead_attribution (company_id);
CREATE INDEX IF NOT EXISTS ix_lead_attribution_external_lead ON lead_attribution (external_lead_id);
CREATE INDEX IF NOT EXISTS ix_lead_attribution_utm_campaign ON lead_attribution (utm_campaign);
-- =============================================================================
-- Optimization Rules — auto-optimization config
-- =============================================================================
CREATE TABLE IF NOT EXISTS optimization_rules (
id CHAR(36) PRIMARY KEY,
company_id CHAR(36) NOT NULL,
name VARCHAR(255) NOT NULL,
rule_type VARCHAR(50) NOT NULL,
target_level VARCHAR(20) DEFAULT 'campaign',
params_json JSON DEFAULT '{}',
is_active BOOLEAN NOT NULL DEFAULT 1,
last_run_at TIMESTAMP,
total_actions INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS ix_optimization_rules_company_id ON optimization_rules (company_id);
CREATE INDEX IF NOT EXISTS ix_optimization_rules_rule_type ON optimization_rules (rule_type);
-- =============================================================================
-- Optimization Logs — audit trail for auto-changes
-- =============================================================================
CREATE TABLE IF NOT EXISTS optimization_logs (
id CHAR(36) PRIMARY KEY,
company_id CHAR(36) NOT NULL,
rule_id CHAR(36),
source_service VARCHAR(50) NOT NULL,
target_type VARCHAR(20) NOT NULL,
target_id VARCHAR(128) NOT NULL,
target_name VARCHAR(255) DEFAULT '',
action VARCHAR(50) NOT NULL,
before_json JSON DEFAULT '{}',
after_json JSON DEFAULT '{}',
reason TEXT DEFAULT '',
status VARCHAR(20) DEFAULT 'success',
error_message TEXT DEFAULT '',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
FOREIGN KEY (rule_id) REFERENCES optimization_rules (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS ix_optimization_logs_company_id ON optimization_logs (company_id);
CREATE INDEX IF NOT EXISTS ix_optimization_logs_rule_id ON optimization_logs (rule_id);
-- =============================================================================
-- Track migration
-- =============================================================================
INSERT OR IGNORE INTO schema_migrations (version) VALUES ('007');