schema.sql

-- =============================================================================
-- Agent-X Database Schema (Source of Truth)
-- =============================================================================
-- This file represents the CURRENT state of the database.
-- It is kept in sync with all migration files (001, 002, 003, ...).
--
-- For a fresh database: run this file.
-- For an existing database: run only the new migration file(s).
-- =============================================================================

-- ============================================================
-- EXTENSIONS
-- ============================================================

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================
-- SOURCES
-- ============================================================

CREATE TABLE sources (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    source_type         TEXT NOT NULL,
    name                TEXT NOT NULL,
    url                 TEXT NOT NULL,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    category            TEXT NOT NULL,
    last_fetched_at     TIMESTAMPTZ,
    consecutive_errors  INTEGER NOT NULL DEFAULT 0,
    config_json         JSONB DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_sources_type_url ON sources (source_type, url);
CREATE INDEX idx_sources_is_active ON sources (is_active);
CREATE INDEX idx_sources_source_type ON sources (source_type);

-- ============================================================
-- POSTS
-- ============================================================

CREATE TABLE posts (
    id               UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    batch_id         TEXT NOT NULL,
    content          TEXT NOT NULL CHECK (char_length(content) <= 280),
    original_content TEXT,
    status           TEXT NOT NULL DEFAULT 'pending_review',
    topic            TEXT NOT NULL,
    angle            TEXT NOT NULL,
    category         TEXT NOT NULL,
    source_id        TEXT,
    source_url       TEXT,
    image_url        TEXT,
    x_post_id        TEXT,
    scheduled_for    TIMESTAMPTZ,
    published_at     TIMESTAMPTZ,
    feedback         TEXT,
    failure_hint     TEXT,
    quality_score    REAL,
    embedding        vector(768),
    content_hash     TEXT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_posts_status ON posts (status);
CREATE INDEX idx_posts_batch_id ON posts (batch_id);
CREATE INDEX idx_posts_created_at ON posts (created_at DESC);
CREATE INDEX idx_posts_category ON posts (category);
CREATE INDEX idx_posts_content_hash ON posts (content_hash);
CREATE INDEX idx_posts_published_at ON posts (published_at DESC);
CREATE INDEX idx_posts_updated_at ON posts (updated_at DESC);

CREATE INDEX idx_posts_embedding ON posts
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- ============================================================
-- POST_METRICS
-- ============================================================

CREATE TABLE post_metrics (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    post_id         UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE UNIQUE,
    impressions     INTEGER NOT NULL DEFAULT 0,
    likes           INTEGER NOT NULL DEFAULT 0,
    retweets        INTEGER NOT NULL DEFAULT 0,
    replies         INTEGER NOT NULL DEFAULT 0,
    engagement_rate FLOAT DEFAULT 0.0,
    fetched_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_post_metrics_post_id ON post_metrics (post_id);
CREATE INDEX idx_post_metrics_fetched_at ON post_metrics (fetched_at DESC);

-- ============================================================
-- CATEGORIES
-- ============================================================

CREATE TABLE categories (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        TEXT NOT NULL UNIQUE,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_categories_is_active ON categories (is_active);

-- ============================================================
-- POST_CATEGORIES (many-to-many)
-- ============================================================

CREATE TABLE post_categories (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    post_id     UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    UNIQUE(post_id, category_id)
);

CREATE INDEX idx_post_categories_post_id ON post_categories (post_id);
CREATE INDEX idx_post_categories_category_id ON post_categories (category_id);

-- ============================================================
-- STYLE_CONFIG
-- ============================================================

CREATE TABLE style_config (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    config      JSONB NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT FALSE,
    proposed_by TEXT NOT NULL DEFAULT 'human',
    approved_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_style_config_is_active ON style_config (is_active)
    WHERE is_active = TRUE;

-- ============================================================
-- IMPROVEMENT_PROPOSALS
-- ============================================================

CREATE TABLE improvement_proposals (
    id               UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    analysis         TEXT NOT NULL,
    proposed_changes JSONB NOT NULL,
    reasoning        TEXT,
    status           TEXT NOT NULL DEFAULT 'pending',
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_improvement_proposals_status ON improvement_proposals (status);

-- ============================================================
-- SETTINGS (key-value runtime config)
-- ============================================================

CREATE TABLE settings (
    key         TEXT PRIMARY KEY,
    value       TEXT NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- PIPELINE_LOGS
-- ============================================================

CREATE TABLE pipeline_logs (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id    TEXT NOT NULL,
    stage       TEXT NOT NULL,
    level       TEXT NOT NULL DEFAULT 'info',
    message     TEXT NOT NULL,
    details     JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_pipeline_logs_batch_id ON pipeline_logs(batch_id);
CREATE INDEX idx_pipeline_logs_created_at ON pipeline_logs(created_at DESC);

-- ============================================================
-- AGENT_CONFIGS: Per-pipeline-stage configuration
-- ============================================================

CREATE TABLE agent_configs (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_name       TEXT UNIQUE NOT NULL,
    display_name     TEXT NOT NULL,
    description      TEXT,
    is_enabled       BOOLEAN NOT NULL DEFAULT true,
    model_id         TEXT,
    temperature      FLOAT,
    max_tokens       INTEGER,
    system_prompt    TEXT,
    custom_instructions TEXT,
    execution_order  INTEGER NOT NULL,
    config_json      JSONB DEFAULT '{}',
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_agent_configs_execution_order ON agent_configs (execution_order);

CREATE TRIGGER trigger_agent_configs_updated_at
    BEFORE UPDATE ON agent_configs
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- ============================================================
-- WEBHOOKS
-- ============================================================

CREATE TABLE webhooks (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        TEXT NOT NULL,
    url         TEXT NOT NULL,
    platform    TEXT NOT NULL DEFAULT 'custom',
    events      TEXT[] NOT NULL DEFAULT '{}',
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    secret      TEXT NOT NULL DEFAULT '',
    config_json JSONB NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_webhooks_is_active ON webhooks (is_active);

CREATE TRIGGER trigger_webhooks_updated_at
    BEFORE UPDATE ON webhooks
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- ============================================================
-- ROW-LEVEL SECURITY
-- ============================================================

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE post_metrics ENABLE ROW LEVEL SECURITY;
ALTER TABLE sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE style_config ENABLE ROW LEVEL SECURITY;
ALTER TABLE improvement_proposals ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE post_categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE pipeline_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE webhooks ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow all for authenticated users" ON posts
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON post_metrics
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON sources
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON style_config
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON improvement_proposals
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON categories
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON post_categories
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON settings
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON pipeline_logs
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON agent_configs
    FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all for authenticated users" ON webhooks
    FOR ALL USING (true) WITH CHECK (true);

-- ============================================================
-- SEED DATA
-- ============================================================

INSERT INTO categories (name, is_active, description) VALUES
    ('ai', TRUE, 'AI tools and products'),
    ('web-dev', TRUE, 'Web development and engineering insights'),
    ('startup', TRUE, 'Startups, funding, and founder lessons'),
    ('tech-news', TRUE, 'Tech industry news and trends'),
    ('open-source', TRUE, 'Open source projects and community')
ON CONFLICT (name) DO NOTHING;

INSERT INTO style_config (config, is_active, proposed_by, approved_at) VALUES (
    '{
        "tone": "Knowledgeable, conversational tech voice. Slightly opinionated, not corporate.",
        "topics": ["ai", "web-dev", "startup", "tech-news", "open-source"],
        "use_hashtags": false,
        "thread_style": false,
        "use_emojis": "sparingly",
        "max_length": 280,
        "cta_frequency": 0.7,
        "hook_styles": ["question", "bold_claim", "contrarian_take", "experience_framing"]
    }'::JSONB,
    TRUE, 'human', NOW()
);

INSERT INTO sources (source_type, name, url, category, is_active) VALUES
    ('hackernews', 'Hacker News Top Stories',
     'https://hacker-news.firebaseio.com/v0/topstories.json', 'ai', TRUE)
ON CONFLICT (source_type, url) DO NOTHING;

INSERT INTO settings (key, value) VALUES ('llm_model', 'gemini-2.0-flash')
ON CONFLICT (key) DO NOTHING;

-- Agent configs seed data (7 pipeline stages)
INSERT INTO agent_configs (agent_name, display_name, description, is_enabled, execution_order, config_json, system_prompt, temperature, max_tokens) VALUES
('research', 'Research', 'Fetches topics from enabled sources (Hacker News, RSS feeds)', true, 1, '{"hn_story_limit": 30}', NULL, NULL, NULL),
('rank', 'Topic Ranking', 'Scores and ranks topics by relevance, filters duplicates against recent posts', true, 2, '{"max_topics": 10, "use_llm_ranking": false}', 'You are a content strategist for a tech-focused X/Twitter account.', 0.3, 1024),
('angles', 'Angle Expansion', 'Generates 2-4 content angles per topic via LLM', true, 3, '{"max_pairs": 10, "max_angles_per_topic": 4, "max_topics_to_expand": 5}', NULL, 0.7, 1024),
('generate', 'Draft Generation', 'Creates tweet drafts from topic-angle pairs with 280-char enforcement', true, 4, '{"max_retries": 2}', 'You are a tech thought leader writing tweets/posts for X (Twitter).', 0.7, 512),
('uniqueness', 'Uniqueness Check', 'Deduplicates drafts using content hash and embedding similarity', true, 5, '{"similarity_threshold": 0.85, "hash_lookback_days": 30}', NULL, NULL, NULL),
('store', 'Store Drafts', 'Saves unique drafts to the database with status pending_review', true, 6, '{}', NULL, NULL, NULL),
('notify', 'Notification', 'Sends email notification when new drafts are ready for review', true, 7, '{}', NULL, NULL, NULL)
ON CONFLICT (agent_name) DO NOTHING;

-- Additional category seeds
INSERT INTO categories (name, is_active, description) VALUES
    ('ai-startups', TRUE, 'AI startup launches, funding rounds, and pivots'),
    ('research-papers', TRUE, 'Notable AI and CS research papers'),
    ('ai-trends', TRUE, 'Emerging AI trends and industry shifts'),
    ('dev-tools', TRUE, 'Developer tools, IDEs, and productivity'),
    ('tech-careers', TRUE, 'Tech career advice, hiring trends, and growth')
ON CONFLICT (name) DO NOTHING;