database/init.sql

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Agents table: tracks personas and their configurations
CREATE TABLE IF NOT EXISTS agents (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    current_version_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Agent versions: versioned agent configs and prompts
CREATE TABLE IF NOT EXISTS agent_versions (
    id SERIAL PRIMARY KEY,
    agent_id INT NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
    version INT NOT NULL,
    config JSONB NOT NULL,  -- Model, temperature, system prompt overrides, etc.
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT,
    UNIQUE(agent_id, version)
);

-- Prompt templates: versioned prompts per agent version
CREATE TABLE IF NOT EXISTS prompt_templates (
    id SERIAL PRIMARY KEY,
    agent_version_id INT NOT NULL REFERENCES agent_versions(id) ON DELETE CASCADE,
    name TEXT NOT NULL,  -- e.g., "system", "user_initial", "feedback"
    prompt_text TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trends: detected social trends
CREATE TABLE IF NOT EXISTS trends (
    id SERIAL PRIMARY KEY,
    topic TEXT NOT NULL,
    source TEXT NOT NULL,  -- "reddit", "twitter", "google_trends", etc.
    source_url TEXT,
    score FLOAT,  -- Relevance score (0-1)
    embedding VECTOR(1536),  -- Embedding for deduplication
    status TEXT DEFAULT 'pending_generation',  -- pending_generation, generated, published, skipped, failed
    raw_data JSONB,  -- Store raw API response
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ON trends(status);
CREATE INDEX ON trends(created_at DESC);
CREATE INDEX ON trends(source);
CREATE INDEX ON trends USING hnsw (embedding vector_cosine_ops);

-- Posts: generated social media posts
CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    trend_id INT REFERENCES trends(id) ON DELETE SET NULL,
    agent_version_id INT NOT NULL REFERENCES agent_versions(id),
    content TEXT NOT NULL,
    image_url TEXT,
    video_url TEXT,
    status TEXT DEFAULT 'draft',  -- draft, pending_approval, approved, rejected, scheduled, published, failed
    
    -- Publishing metadata
    platform TEXT,  -- instagram, twitter, linkedin, etc.
    platform_post_id TEXT,  -- ID from platform's API
    scheduled_at TIMESTAMP,
    published_at TIMESTAMP,
    approval_status TEXT DEFAULT 'pending',  -- pending, approved, rejected
    approval_notes TEXT,
    approved_by TEXT,
    approved_at TIMESTAMP,
    
    -- Generated metadata
    embedding VECTOR(1536),  -- For similarity/dedup
    token_count INT,
    generation_cost FLOAT,  -- Cost in USD
    hallucination_score FLOAT,  -- 0-1, higher = more hallucination risk
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ON posts(status);
CREATE INDEX ON posts(approval_status);
CREATE INDEX ON posts(platform);
CREATE INDEX ON posts(published_at DESC);
CREATE INDEX ON posts(created_at DESC);
CREATE INDEX ON posts USING hnsw (embedding vector_cosine_ops);

-- Workflow executions: track Temporal workflow runs
CREATE TABLE IF NOT EXISTS workflow_executions (
    id SERIAL PRIMARY KEY,
    workflow_id TEXT NOT NULL,
    workflow_name TEXT NOT NULL,
    trend_id INT REFERENCES trends(id),
    post_id INT REFERENCES posts(id),
    status TEXT DEFAULT 'running',  -- running, completed, failed, timed_out
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    error_message TEXT,
    metadata JSONB,
    
    UNIQUE(workflow_id)
);

CREATE INDEX ON workflow_executions(status);
CREATE INDEX ON workflow_executions(workflow_name);
CREATE INDEX ON workflow_executions(created_at DESC);

-- Media: store image/video metadata
CREATE TABLE IF NOT EXISTS media (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    media_type TEXT NOT NULL,  -- image, video
    file_name TEXT NOT NULL,
    file_size INT,
    mime_type TEXT,
    r2_key TEXT,  -- Path in R2 bucket
    r2_url TEXT,
    
    -- Moderation
    moderation_status TEXT DEFAULT 'pending',  -- pending, approved, rejected, flagged
    nsfw_score FLOAT,  -- 0-1, higher = more likely NSFW
    moderation_details JSONB,  -- Results from AWS Rekognition, etc.
    
    -- Thumbnails
    thumbnail_url TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ON media(post_id);
CREATE INDEX ON media(moderation_status);

-- Observability traces: store Langfuse trace references
CREATE TABLE IF NOT EXISTS observability_traces (
    id SERIAL PRIMARY KEY,
    trace_id TEXT UNIQUE,
    post_id INT REFERENCES posts(id),
    workflow_id TEXT REFERENCES workflow_executions(workflow_id),
    trace_name TEXT,
    input JSONB,
    output JSONB,
    metadata JSONB,
    token_count INT,
    cost_usd FLOAT,
    duration_ms INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ON observability_traces(post_id);
CREATE INDEX ON observability_traces(workflow_id);
CREATE INDEX ON observability_traces(created_at DESC);

-- API usage & costs: track quota and spending
CREATE TABLE IF NOT EXISTS api_usage (
    id SERIAL PRIMARY KEY,
    service TEXT NOT NULL,  -- openai, anthropic, instagram, twitter, rekognition, etc.
    request_count INT DEFAULT 0,
    token_count INT DEFAULT 0,
    cost_usd FLOAT DEFAULT 0,
    period_start TIMESTAMP,
    period_end TIMESTAMP,
    UNIQUE(service, period_start, period_end)
);

-- System errors & alerts
CREATE TABLE IF NOT EXISTS system_alerts (
    id SERIAL PRIMARY KEY,
    alert_type TEXT NOT NULL,  -- rate_limit, api_error, quality_degradation, etc.
    service TEXT,
    message TEXT NOT NULL,
    severity TEXT DEFAULT 'warning',  -- info, warning, error, critical
    resolved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP
);

CREATE INDEX ON system_alerts(severity);
CREATE INDEX ON system_alerts(resolved);
CREATE INDEX ON system_alerts(created_at DESC);