[DM-06] Communications table (message templates, delivery tracking)

{
  "cost": {
    "input_tokens": 22,
    "estimated_usd": 0.045651,
    "output_tokens": 3039
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 36061,
  "response_text": "# Communications Table Schema\n\nI'll design a comprehensive communications system with message templates and delivery tracking capabilities.\n\n## Table Structures\n\n### 1. Message Templates Table\n```sql\nCREATE TABLE message_templates (\n    template_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    template_name VARCHAR(100) NOT NULL,\n    template_type VARCHAR(50) NOT NULL, -- 'email', 'sms', 'push', 'in_app'\n    category VARCHAR(50) NOT NULL, -- 'welcome', 'notification', 'marketing', 'transactional'\n    subject VARCHAR(200), -- For email templates\n    content TEXT NOT NULL,\n    html_content TEXT, -- For rich email content\n    variables JSONB, -- Template variables and their descriptions\n    is_active BOOLEAN DEFAULT true,\n    created_by UUID REFERENCES users(user_id),\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    \n    CONSTRAINT unique_template_name UNIQUE(template_name, template_type)\n);\n```\n\n### 2. Communication Campaigns Table\n```sql\nCREATE TABLE communication_campaigns (\n    campaign_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    campaign_name VARCHAR(100) NOT NULL,\n    template_id UUID REFERENCES message_templates(template_id),\n    campaign_type VARCHAR(50) NOT NULL, -- 'broadcast', 'triggered', 'scheduled'\n    target_audience JSONB, -- Filtering criteria for recipients\n    scheduled_at TIMESTAMP,\n    status VARCHAR(20) DEFAULT 'draft', -- 'draft', 'scheduled', 'active', 'completed', 'cancelled'\n    created_by UUID REFERENCES users(user_id),\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n```\n\n### 3. Message Queue Table\n```sql\nCREATE TABLE message_queue (\n    message_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    campaign_id UUID REFERENCES communication_campaigns(campaign_id),\n    template_id UUID REFERENCES message_templates(template_id),\n    recipient_id UUID REFERENCES users(user_id),\n    recipient_email VARCHAR(255),\n    recipient_phone VARCHAR(20),\n    message_type VARCHAR(50) NOT NULL,\n    priority INTEGER DEFAULT 5, -- 1 (highest) to 10 (lowest)\n    subject VARCHAR(200),\n    content TEXT NOT NULL,\n    html_content TEXT,\n    variables JSONB, -- Personalized variables for this message\n    status VARCHAR(20) DEFAULT 'queued', -- 'queued', 'processing', 'sent', 'failed', 'cancelled'\n    scheduled_for TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    attempts INTEGER DEFAULT 0,\n    max_attempts INTEGER DEFAULT 3,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    processed_at TIMESTAMP\n);\n```\n\n### 4. Delivery Tracking Table\n```sql\nCREATE TABLE message_delivery_tracking (\n    tracking_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    message_id UUID REFERENCES message_queue(message_id),\n    campaign_id UUID REFERENCES communication_campaigns(campaign_id),\n    recipient_id UUID REFERENCES users(user_id),\n    message_type VARCHAR(50) NOT NULL,\n    delivery_status VARCHAR(30) NOT NULL, -- 'sent', 'delivered', 'failed', 'bounced', 'rejected'\n    delivery_provider VARCHAR(50), -- 'sendgrid', 'twilio', 'firebase', etc.\n    provider_message_id VARCHAR(100), -- External provider's tracking ID\n    sent_at TIMESTAMP,\n    delivered_at TIMESTAMP,\n    failed_at TIMESTAMP,\n    error_message TEXT,\n    error_code VARCHAR(20),\n    metadata JSONB, -- Additional tracking data from providers\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n```\n\n### 5. Message Engagement Tracking Table\n```sql\nCREATE TABLE message_engagement_tracking (\n    engagement_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    tracking_id UUID REFERENCES message_delivery_tracking(tracking_id),\n    message_id UUID REFERENCES message_queue(message_id),\n    recipient_id UUID REFERENCES users(user_id),\n    engagement_type VARCHAR(30) NOT NULL, -- 'opened', 'clicked', 'unsubscribed', 'replied'\n    engagement_data JSONB, -- URLs clicked, reply content, etc.\n    user_agent TEXT,\n    ip_address INET,\n    engaged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    \n    CONSTRAINT unique_engagement UNIQUE(tracking_id, engagement_type, engaged_at)\n);\n```\n\n### 6. Communication Preferences Table\n```sql\nCREATE TABLE communication_preferences (\n    preference_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    user_id UUID REFERENCES users(user_id),\n    communication_type VARCHAR(50) NOT NULL, -- 'email', 'sms', 'push'\n    category VARCHAR(50) NOT NULL, -- 'marketing', 'transactional', 'notifications'\n    is_enabled BOOLEAN DEFAULT true,\n    frequency VARCHAR(20) DEFAULT 'immediate', -- 'immediate', 'daily', 'weekly', 'monthly'\n    preferred_time TIME, -- Preferred delivery time\n    timezone VARCHAR(50) DEFAULT 'UTC',\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    \n    CONSTRAINT unique_user_preference UNIQUE(user_id, communication_type, category)\n);\n```\n\n## Indexes for Performance\n\n```sql\n-- Message Templates\nCREATE INDEX idx_message_templates_type ON message_templates(template_type);\nCREATE INDEX idx_message_templates_category ON message_templates(category);\nCREATE INDEX idx_message_templates_active ON message_templates(is_active);\n\n-- Message Queue\nCREATE INDEX idx_message_queue_status ON message_queue(status);\nCREATE INDEX idx_message_queue_scheduled ON message_queue(scheduled_for);\nCREATE INDEX idx_message_queue_recipient ON message_queue(recipient_id);\nCREATE INDEX idx_message_queue_type ON message_queue(message_type);\nCREATE INDEX idx_message_queue_priority ON message_queue(priority, scheduled_for);\n\n-- Delivery Tracking\nCREATE INDEX idx_delivery_tracking_message ON message_delivery_tracking(message_id);\nCREATE INDEX idx_delivery_tracking_status ON message_delivery_tracking(delivery_status);\nCREATE INDEX idx_delivery_tracking_recipient ON message_delivery_tracking(recipient_id);\nCREATE INDEX idx_delivery_tracking_sent ON message_delivery_tracking(sent_at);\nCREATE INDEX idx_delivery_tracking_provider ON message_delivery_tracking(provider_message_id);\n\n-- Engagement Tracking\nCREATE INDEX idx_engagement_tracking_message ON message_engagement_tracking(message_id);\nCREATE INDEX idx_engagement_tracking_recipient ON message_engagement_tracking(recipient_id);\nCREATE INDEX idx_engagement_tracking_type ON message_engagement_tracking(engagement_type);\nCREATE INDEX idx_engagement_tracking_date ON message_engagement_tracking(engaged_at);\n\n-- Communication Preferences\nCREATE INDEX idx_comm_preferences_user ON communication_preferences(user_id);\nCREATE INDEX idx_comm_preferences_type ON communication_preferences(communication_type, is_enabled);\n```\n\n## Sample Views and Queries\n\n### Campaign Performance View\n```sql\nCREATE VIEW campaign_performance AS\nSELECT \n    c.campaign_id,\n    c.campaign_name,\n    c.campaign_type,\n    COUNT(DISTINCT mq.message_id) as total_messages,\n    COUNT(DISTINCT CASE WHEN dt.delivery_status = 'sent' THEN dt.message_id END) as messages_sent,\n    COUNT(DISTINCT CASE WHEN dt.delivery_status = 'delivered' THEN dt.message_id END) as messages_delivered,\n    COUNT(DISTINCT CASE WHEN dt.delivery_status = 'failed' THEN dt.message_id END) as messages_failed,\n    COUNT(DISTINCT CASE WHEN et.engagement_type = 'opened' THEN et.message_id END) as messages_opened,\n    COUNT(DISTINCT CASE WHEN et.engagement_type = 'clicked' THEN et.message_id END) as messages_clicked,\n    ROUND(\n        COUNT(DISTINCT CASE WHEN dt.delivery_status = 'delivered' THEN dt.message_id END) * 100.0 / \n        NULLIF(COUNT(DISTINCT mq.message_id), 0), 2\n    ) as delivery_rate,\n    ROUND(\n        COUNT(DISTINCT CASE WHEN et.engagement_type = 'opened' THEN et.message_id END) * 100.0 / \n        NULLIF(COUNT(DISTINCT CASE WHEN dt.delivery_status = 'delivered' THEN dt.message_id END), 0), 2\n    ) as open_rate,\n    ROUND(\n        COUNT(DISTINCT CASE WHEN et.engagement_type = 'clicked' THEN et.message_id END) * 100.0 / \n        NULLIF(COUNT(DISTINCT CASE WHEN et.engagement_type = 'opened' THEN et.message_id END), 0), 2\n    ) as click_through_rate\nFROM communication_campaigns c\nLEFT JOIN message_queue mq ON c.campaign_id = mq.campaign_id\nLEFT JOIN message_delivery_tracking dt ON mq.message_id = dt.message_id\nLEFT JOIN message_engagement_tracking et ON dt.tracking_id = et.tracking_id\nGROUP BY c.campaign_id, c.campaign_name, c.campaign_type;\n```\n\n### Message Processing Queue Query\n```sql\n-- Get next batch of messages to process\nSELECT \n    mq.*,\n    mt.template_name,\n    cp.is_enabled as recipient_opted_in\nFROM message_queue mq\nJOIN message_templates mt ON mq.template_id = mt.template_id\nLEFT JOIN communication_preferences cp ON mq.recipient_id = cp.user_id \n    AND cp.communication_type = mq.message_type \n    AND cp.category = mt.category\nWHERE mq.status = 'queued'\n    AND mq.scheduled_for <= CURRENT_TIMESTAMP\n    AND mq.attempts < mq.max_attempts\n    AND (cp.is_enabled IS NULL OR cp.is_enabled = true)\nORDER BY mq.priority ASC, mq.scheduled_for ASC\nLIMIT 100;\n```\n\n## Example Usage\n\n### 1. Create Email Template\n```sql\nINSERT INTO message_templates (\n    template_name, template_type, category, subject, content, html_content, variables\n) VALUES (\n    'welcome_email',\n    'email',\n    'transactional',\n    'Welcome to {{app_name}}!',\n    'Hi {{first_name}}, welcome to our platform!',\n    '<h1>Hi {{first_name}}</h1><p>Welcome to our platform!</p>',\n    '{\"first_name\": \"User first name\", \"app_name\": \"Application name\"}'::jsonb\n);\n```\n\n### 2. Queue Personalized Message\n```sql\nINSERT INTO message_queue (\n    template_id, recipient_id, recipient_email, message_type, \n    subject, content, variables\n) VALUES (\n    (SELECT template_id FROM message_templates WHERE template_name = 'welcome_email'),\n    '123e4567-e89b-12d3-a456-426614174000',\n    'user@example.com',\n    'email',\n    'Welcome to MyApp!',\n    'Hi John, welcome to our platform!',\n    '{\"first_name\": \"John\", \"app_name\": \"MyApp\"}'::jsonb\n);\n```\n\n### 3. Track Delivery Status\n```sql\nINSERT INTO message_delivery_tracking (\n    message_id, recipient_id, message_type, delivery_status,\n    delivery_provider, provider_message_id, sent_at\n) VALUES (\n    '123e4567-e89b-12d3-a456-426614174001',\n    '123e4567-e89b-12d3-a456-426614174000',\n    'email',\n    'sent',\n    'sendgrid',\n    'sg_message_123456',\n    CURRENT_TIMESTAMP\n);\n```\n\nThis schema provides a comprehensive foundation for managing communications with template support, delivery tracking, engagement metrics, and user preferences."
}