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

{
  "cost": {
    "input_tokens": 309,
    "estimated_usd": 0.025992,
    "output_tokens": 1671
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 27398,
  "response_text": "I'll create the communications table migration file with proper structure for message templates and delivery tracking.\n\n```sql\n-- Migration: Create communications table for message templates and delivery tracking\n-- EN: Communications table stores message templates and tracks delivery status\n-- VI: Bảng communications lưu trữ mẫu tin nhắn và theo dõi trạng thái gửi\n-- ES: Tabla communications almacena plantillas de mensajes y rastrea estado de entrega\n-- SPEC: DM-06 - Communications table with template management and delivery tracking\n\nCREATE TABLE IF NOT EXISTS communications (\n    -- Core identification\n    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    \n    -- Campaign relationship\n    -- EN: Links communication to marketing campaign\n    -- VI: Liên kết thông tin liên lạc với chiến dịch marketing\n    -- ES: Vincula comunicación con campaña de marketing\n    campaign_id UUID REFERENCES campaigns(id) ON DELETE CASCADE,\n    \n    -- Template management\n    -- EN: Message template identifier and content\n    -- VI: Định danh mẫu tin nhắn và nội dung\n    -- ES: Identificador de plantilla de mensaje y contenido\n    template_id VARCHAR(255) NOT NULL,\n    template_name VARCHAR(500),\n    template_content JSONB DEFAULT '{}',\n    template_type VARCHAR(100) DEFAULT 'email', -- email, sms, push, webhook\n    \n    -- Target audience\n    -- EN: Defines who receives the communication\n    -- VI: Xác định ai nhận được thông tin liên lạc\n    -- ES: Define quién recibe la comunicación\n    target_list JSONB DEFAULT '[]', -- Array of recipient identifiers\n    target_criteria JSONB DEFAULT '{}', -- Segmentation rules\n    total_recipients INTEGER DEFAULT 0,\n    \n    -- Delivery tracking\n    -- EN: Tracks message delivery status and metrics\n    -- VI: Theo dõi trạng thái gửi tin nhắn và số liệu\n    -- ES: Rastrea estado de entrega de mensajes y métricas\n    delivery_status VARCHAR(100) DEFAULT 'draft', -- draft, scheduled, sending, sent, failed\n    scheduled_at TIMESTAMPTZ,\n    started_at TIMESTAMPTZ,\n    completed_at TIMESTAMPTZ,\n    \n    -- Delivery metrics\n    sent_count INTEGER DEFAULT 0,\n    delivered_count INTEGER DEFAULT 0,\n    failed_count INTEGER DEFAULT 0,\n    opened_count INTEGER DEFAULT 0,\n    clicked_count INTEGER DEFAULT 0,\n    \n    -- Failure tracking\n    failure_reason TEXT,\n    retry_count INTEGER DEFAULT 0,\n    max_retries INTEGER DEFAULT 3,\n    \n    -- Configuration\n    delivery_settings JSONB DEFAULT '{}', -- Rate limits, retry policies, etc.\n    personalization_data JSONB DEFAULT '{}', -- Dynamic content variables\n    \n    -- Audit fields\n    created_at TIMESTAMPTZ DEFAULT NOW(),\n    updated_at TIMESTAMPTZ DEFAULT NOW(),\n    created_by UUID,\n    updated_by UUID\n);\n\n-- Indexes for performance\n-- EN: Optimize queries for campaign and delivery status lookups\n-- VI: Tối ưu hóa truy vấn cho tìm kiếm chiến dịch và trạng thái gửi\n-- ES: Optimiza consultas para búsquedas de campaña y estado de entrega\nCREATE INDEX idx_communications_campaign_id ON communications(campaign_id);\nCREATE INDEX idx_communications_template_id ON communications(template_id);\nCREATE INDEX idx_communications_delivery_status ON communications(delivery_status);\nCREATE INDEX idx_communications_scheduled_at ON communications(scheduled_at) WHERE scheduled_at IS NOT NULL;\nCREATE INDEX idx_communications_target_list ON communications USING GIN(target_list);\n\n-- Template management indexes\nCREATE INDEX idx_communications_template_type ON communications(template_type);\nCREATE INDEX idx_communications_template_name ON communications(template_name) WHERE template_name IS NOT NULL;\n\n-- Update timestamp trigger\n-- EN: Automatically update updated_at field on record changes\n-- VI: Tự động cập nhật trường updated_at khi thay đổi bản ghi\n-- ES: Actualiza automáticamente el campo updated_at en cambios de registro\nCREATE OR REPLACE FUNCTION update_communications_updated_at()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    RETURN NEW;\nEND;\n$$ language 'plpgsql';\n\nCREATE TRIGGER update_communications_updated_at\n    BEFORE UPDATE ON communications\n    FOR EACH ROW\n    EXECUTE FUNCTION update_communications_updated_at();\n\n-- Row Level Security\n-- EN: Enable RLS for tenant isolation and access control\n-- VI: Bật RLS để cách ly tenant và kiểm soát truy cập\n-- ES: Habilita RLS para aislamiento de tenant y control de acceso\nALTER TABLE communications ENABLE ROW LEVEL SECURITY;\n\n-- Policy for authenticated users to manage their communications\nCREATE POLICY communications_tenant_policy ON communications\n    FOR ALL USING (\n        auth.uid() IS NOT NULL AND (\n            -- Campaign owner can manage communications\n            campaign_id IN (\n                SELECT id FROM campaigns \n                WHERE created_by = auth.uid()\n            )\n            -- Or user has explicit permission\n            OR created_by = auth.uid()\n        )\n    );\n\n-- Comments for documentation\nCOMMENT ON TABLE communications IS 'Message templates and delivery tracking for marketing campaigns';\nCOMMENT ON COLUMN communications.template_id IS 'Unique identifier for message template';\nCOMMENT ON COLUMN communications.target_list IS 'JSON array of recipient identifiers or segments';\nCOMMENT ON COLUMN communications.delivery_status IS 'Current status of message delivery process';\nCOMMENT ON COLUMN communications.template_content IS 'JSON structure containing message content and variables';\n```\n\nThis migration creates a comprehensive communications table that handles:\n\n**Core Features:**\n- Message template management with `template_id`, `template_name`, and `template_content`\n- Campaign linking via `campaign_id` \n- Target audience definition through `target_list` and `target_criteria`\n- Delivery status tracking with `delivery_status` field\n\n**Delivery Tracking:**\n- Status progression: draft → scheduled → sending → sent/failed\n- Comprehensive metrics: sent, delivered, failed, opened, clicked counts\n- Failure handling with retry logic and error tracking\n- Scheduling capabilities with timestamps\n\n**Template System:**\n- Multiple template types (email, SMS, push, webhook)\n- JSON-based content storage for flexibility  \n- Personalization data support\n- Template reusability across campaigns\n\n**Performance & Security:**\n- Strategic indexes for common query patterns\n- Row Level Security with tenant isolation\n- Automatic timestamp updates\n- Proper foreign key relationships\n\nThe table satisfies all acceptance criteria with the required `CREATE TABLE communications` statement and includes all specified fields: `campaign_id`, `template_id`, `target_list`, and `delivery_status`."
}