[DM-07] Reports table (performance metrics) (retry 1)

{
  "cost": {
    "input_tokens": 317,
    "estimated_usd": 0.042246,
    "output_tokens": 2753
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 35631,
  "response_text": "I'll create the reports table migration file for performance metrics tracking.\n\n```sql\n-- Migration: Create reports table for performance metrics\n-- EN: Reports table stores campaign performance metrics and analytics data\n-- VI: Bảng báo cáo lưu trữ các chỉ số hiệu suất và dữ liệu phân tích chiến dịch\n-- ES: Tabla de informes almacena métricas de rendimiento y datos analíticos de campaña\n-- SPEC: DM-07 - Reports table (performance metrics)\n\nCREATE TABLE reports (\n    -- EN: Primary key for report identification\n    -- VI: Khóa chính để định danh báo cáo\n    -- ES: Clave primaria para identificación de informes\n    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    \n    -- EN: Reference to campaign this report belongs to\n    -- VI: Tham chiếu đến chiến dịch mà báo cáo này thuộc về\n    -- ES: Referencia a la campaña a la que pertenece este informe\n    campaign_id UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,\n    \n    -- EN: Type of report (performance, engagement, conversion, etc.)\n    -- VI: Loại báo cáo (hiệu suất, tương tác, chuyển đổi, v.v.)\n    -- ES: Tipo de informe (rendimiento, participación, conversión, etc.)\n    report_type VARCHAR(50) NOT NULL CHECK (report_type IN ('performance', 'engagement', 'conversion', 'reach', 'demographics', 'roi')),\n    \n    -- EN: Generated report data in JSON format containing metrics and analytics\n    -- VI: Dữ liệu báo cáo được tạo ở định dạng JSON chứa các chỉ số và phân tích\n    -- ES: Datos del informe generados en formato JSON que contienen métricas y análisis\n    generated_data_json JSONB NOT NULL DEFAULT '{}',\n    \n    -- EN: Date range start for the report period\n    -- VI: Ngày bắt đầu khoảng thời gian của báo cáo\n    -- ES: Fecha de inicio del período del informe\n    report_start_date TIMESTAMP WITH TIME ZONE NOT NULL,\n    \n    -- EN: Date range end for the report period\n    -- VI: Ngày kết thúc khoảng thời gian của báo cáo\n    -- ES: Fecha de fin del período del informe\n    report_end_date TIMESTAMP WITH TIME ZONE NOT NULL,\n    \n    -- EN: Current status of report generation\n    -- VI: Trạng thái hiện tại của việc tạo báo cáo\n    -- ES: Estado actual de la generación del informe\n    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'generating', 'completed', 'failed')),\n    \n    -- EN: File URL if report is exported to file\n    -- VI: URL tệp nếu báo cáo được xuất ra tệp\n    -- ES: URL del archivo si el informe se exporta a archivo\n    file_url TEXT,\n    \n    -- EN: Additional metadata for report configuration and filters\n    -- VI: Siêu dữ liệu bổ sung cho cấu hình và bộ lọc báo cáo\n    -- ES: Metadatos adicionales para configuración y filtros del informe\n    metadata JSONB DEFAULT '{}',\n    \n    -- EN: Record creation timestamp\n    -- VI: Thời gian tạo bản ghi\n    -- ES: Marca de tiempo de creación del registro\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    \n    -- EN: Record last update timestamp\n    -- VI: Thời gian cập nhật cuối cùng của bản ghi\n    -- ES: Marca de tiempo de última actualización del registro\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n    \n    -- EN: Ensure report end date is after start date\n    -- VI: Đảm bảo ngày kết thúc báo cáo sau ngày bắt đầu\n    -- ES: Asegurar que la fecha de fin del informe sea posterior a la fecha de inicio\n    CONSTRAINT valid_date_range CHECK (report_end_date >= report_start_date)\n);\n\n-- EN: Create indexes for efficient querying\n-- VI: Tạo chỉ mục để truy vấn hiệu quả\n-- ES: Crear índices para consultas eficientes\n\n-- EN: Index on campaign_id for filtering reports by campaign\n-- VI: Chỉ mục trên campaign_id để lọc báo cáo theo chiến dịch\n-- ES: Índice en campaign_id para filtrar informes por campaña\nCREATE INDEX idx_reports_campaign_id ON reports(campaign_id);\n\n-- EN: Index on report_type for filtering by report type\n-- VI: Chỉ mục trên report_type để lọc theo loại báo cáo\n-- ES: Índice en report_type para filtrar por tipo de informe\nCREATE INDEX idx_reports_type ON reports(report_type);\n\n-- EN: Index on status for filtering by report generation status\n-- VI: Chỉ mục trên status để lọc theo trạng thái tạo báo cáo\n-- ES: Índice en status para filtrar por estado de generación del informe\nCREATE INDEX idx_reports_status ON reports(status);\n\n-- EN: Composite index on campaign_id and report_type for common queries\n-- VI: Chỉ mục tổng hợp trên campaign_id và report_type cho các truy vấn thông thường\n-- ES: Índice compuesto en campaign_id y report_type para consultas comunes\nCREATE INDEX idx_reports_campaign_type ON reports(campaign_id, report_type);\n\n-- EN: Index on date range for time-based queries\n-- VI: Chỉ mục trên khoảng ngày để truy vấn theo thời gian\n-- ES: Índice en rango de fechas para consultas basadas en tiempo\nCREATE INDEX idx_reports_date_range ON reports(report_start_date, report_end_date);\n\n-- EN: GIN index on generated_data_json for JSON queries\n-- VI: Chỉ mục GIN trên generated_data_json cho các truy vấn JSON\n-- ES: Índice GIN en generated_data_json para consultas JSON\nCREATE INDEX idx_reports_generated_data_gin ON reports USING GIN(generated_data_json);\n\n-- EN: Create trigger to automatically update updated_at timestamp\n-- VI: Tạo trigger để tự động cập nhật timestamp updated_at\n-- ES: Crear trigger para actualizar automáticamente la marca de tiempo updated_at\nCREATE OR REPLACE FUNCTION update_updated_at_column()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    RETURN NEW;\nEND;\n$$ language 'plpgsql';\n\nCREATE TRIGGER update_reports_updated_at\n    BEFORE UPDATE ON reports\n    FOR EACH ROW\n    EXECUTE FUNCTION update_updated_at_column();\n\n-- EN: Enable Row Level Security\n-- VI: Bật bảo mật cấp hàng\n-- ES: Habilitar seguridad a nivel de fila\nALTER TABLE reports ENABLE ROW LEVEL SECURITY;\n\n-- EN: Create RLS policy for reports access based on user permissions\n-- VI: Tạo chính sách RLS cho quyền truy cập báo cáo dựa trên quyền người dùng\n-- ES: Crear política RLS para acceso a informes basado en permisos de usuario\nCREATE POLICY \"Users can view reports for their campaigns\" ON reports\n    FOR SELECT USING (\n        EXISTS (\n            SELECT 1 FROM campaigns \n            WHERE campaigns.id = reports.campaign_id \n            AND campaigns.user_id = auth.uid()\n        )\n    );\n\nCREATE POLICY \"Users can insert reports for their campaigns\" ON reports\n    FOR INSERT WITH CHECK (\n        EXISTS (\n            SELECT 1 FROM campaigns \n            WHERE campaigns.id = reports.campaign_id \n            AND campaigns.user_id = auth.uid()\n        )\n    );\n\nCREATE POLICY \"Users can update reports for their campaigns\" ON reports\n    FOR UPDATE USING (\n        EXISTS (\n            SELECT 1 FROM campaigns \n            WHERE campaigns.id = reports.campaign_id \n            AND campaigns.user_id = auth.uid()\n        )\n    );\n\nCREATE POLICY \"Users can delete reports for their campaigns\" ON reports\n    FOR DELETE USING (\n        EXISTS (\n            SELECT 1 FROM campaigns \n            WHERE campaigns.id = reports.campaign_id \n            AND campaigns.user_id = auth.uid()\n        )\n    );\n\n-- EN: Create view for report summaries\n-- VI: Tạo view cho tóm tắt báo cáo\n-- ES: Crear vista para resúmenes de informes\nCREATE OR REPLACE VIEW report_summaries AS\nSELECT \n    r.id,\n    r.campaign_id,\n    c.title as campaign_title,\n    r.report_type,\n    r.status,\n    r.report_start_date,\n    r.report_end_date,\n    r.created_at,\n    r.updated_at,\n    -- EN: Extract key metrics from JSON data\n    -- VI: Trích xuất các chỉ số chính từ dữ liệu JSON\n    -- ES: Extraer métricas clave de los datos JSON\n    (r.generated_data_json->>'total_impressions')::INTEGER as total_impressions,\n    (r.generated_data_json->>'total_clicks')::INTEGER as total_clicks,\n    (r.generated_data_json->>'total_conversions')::INTEGER as total_conversions,\n    (r.generated_data_json->>'total_spend')::DECIMAL as total_spend,\n    (r.generated_data_json->>'ctr')::DECIMAL as click_through_rate,\n    (r.generated_data_json->>'conversion_rate')::DECIMAL as conversion_rate\nFROM reports r\nJOIN campaigns c ON r.campaign_id = c.id;\n```\n\nThis migration creates a comprehensive reports table for performance metrics with:\n\n1. **Core Fields**: `campaign_id`, `report_type`, and `generated_data_json` as required\n2. **Report Types**: Performance, engagement, conversion, reach, demographics, ROI\n3. **Flexible JSON Storage**: For storing various metrics and analytics data\n4. **Time Range Support**: Start and end dates for report periods\n5. **Status Tracking**: Generation status (pending, generating, completed, failed)\n6. **Performance Indexes**: Optimized for common query patterns\n7. **RLS Policies**: Secure access based on campaign ownership\n8. **Helper View**: For easy access to common metrics\n9. **Audit Fields**: Created/updated timestamps with automatic updates\n\nThe table supports storing various performance metrics like impressions, clicks, conversions, spend, CTR, and conversion rates in the flexible JSON structure while maintaining relational integrity with campaigns."
}