erp-core/docs/04-modelado/database-design/DDL-SPEC-ai_agents.md

DDL Specification: ai_agents Schema

Identificacion

Campo	Valor
Schema	ai_agents
Modulo	MGN-018 AI Agents & Chatbots
Version	1.0
Fecha	2025-12-05
Estado	Ready

---

Diagrama ER

erDiagram
    tenants ||--o{ agents : owns
    agents ||--o{ agent_knowledge_bases : uses
    knowledge_bases ||--o{ agent_knowledge_bases : linked_to
    knowledge_bases ||--o{ kb_documents : contains
    kb_documents ||--o{ kb_chunks : splits_into
    agents ||--o{ agent_tools : has
    tool_definitions ||--o{ agent_tools : defined_by
    agents ||--o{ conversations : handles
    conversations ||--o{ messages : contains
    messages ||--o{ tool_executions : triggers
    tenants ||--o{ feedback : collects
    tenants ||--o{ usage_logs : tracks

    agents {
        uuid id PK
        uuid tenant_id FK
        string name
        string agent_type
        string model
        text system_prompt
        jsonb config
        boolean is_active
    }

    knowledge_bases {
        uuid id PK
        uuid tenant_id FK
        string name
        string embedding_model
        int total_documents
        int total_chunks
    }

    kb_documents {
        uuid id PK
        uuid kb_id FK
        string name
        string source_type
        string status
        int chunk_count
    }

    kb_chunks {
        uuid id PK
        uuid document_id FK
        int chunk_index
        text content
        vector embedding
        jsonb metadata
    }

    tool_definitions {
        uuid id PK
        string code UK
        string name
        jsonb schema
        boolean is_system
    }

    conversations {
        uuid id PK
        uuid agent_id FK
        uuid contact_id FK
        string channel
        string status
        int message_count
    }

    messages {
        uuid id PK
        uuid conversation_id FK
        string role
        text content
        jsonb tool_calls
        int tokens_used
    }

    tool_executions {
        uuid id PK
        uuid message_id FK
        uuid tool_id FK
        jsonb input
        jsonb output
        boolean success
    }

    feedback {
        uuid id PK
        uuid message_id FK
        string rating
        text comment
    }

    usage_logs {
        uuid id PK
        uuid tenant_id FK
        uuid agent_id FK
        string period
        int input_tokens
        int output_tokens
        decimal cost
    }

---

Tablas

1. agents

Configuracion de agentes de IA.

CREATE TABLE ai_agents.agents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(50) NOT NULL,
    description VARCHAR(500),

    -- Tipo de agente
    agent_type VARCHAR(30) NOT NULL DEFAULT 'custom',
    -- customer_support, sales, technical_support, custom

    -- Modelo de IA
    model VARCHAR(50) NOT NULL DEFAULT 'gpt-4o-mini',
    -- gpt-4o, gpt-4o-mini, claude-3-5-sonnet, claude-3-haiku
    provider VARCHAR(20) NOT NULL DEFAULT 'openai',
    -- openai, anthropic

    -- Prompt del sistema
    system_prompt TEXT NOT NULL,
    -- Instrucciones base del agente

    -- Configuracion
    config JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Ejemplo: {
    --   "temperature": 0.7,
    --   "max_tokens": 1024,
    --   "response_language": "es",
    --   "handoff_threshold": 0.3,
    --   "greeting_message": "Hola, soy...",
    --   "fallback_message": "No entendí tu pregunta..."
    -- }

    -- Canales donde esta activo
    channels TEXT[] DEFAULT '{}'::text[],
    -- whatsapp, web_chat, api

    -- Estado
    is_active BOOLEAN NOT NULL DEFAULT false,
    is_published BOOLEAN NOT NULL DEFAULT false,

    -- Estadisticas
    total_conversations INT NOT NULL DEFAULT 0,
    total_messages INT NOT NULL DEFAULT 0,
    avg_satisfaction DECIMAL(3,2),

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMPTZ,
    created_by UUID,

    CONSTRAINT uq_agents UNIQUE (tenant_id, slug),
    CONSTRAINT chk_agents_type CHECK (agent_type IN (
        'customer_support', 'sales', 'technical_support', 'custom'
    )),
    CONSTRAINT chk_agents_model CHECK (model IN (
        'gpt-4o', 'gpt-4o-mini', 'gpt-4-turbo',
        'claude-3-5-sonnet', 'claude-3-haiku', 'claude-3-opus'
    )),
    CONSTRAINT chk_agents_provider CHECK (provider IN ('openai', 'anthropic'))
);

-- Indices
CREATE INDEX idx_agents_tenant ON ai_agents.agents(tenant_id);
CREATE INDEX idx_agents_type ON ai_agents.agents(agent_type);
CREATE INDEX idx_agents_active ON ai_agents.agents(tenant_id, is_active)
    WHERE is_active = true;
CREATE INDEX idx_agents_channels ON ai_agents.agents USING gin(channels);

-- Trigger para updated_at
CREATE TRIGGER trg_agents_updated_at
    BEFORE UPDATE ON ai_agents.agents
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- RLS
ALTER TABLE ai_agents.agents ENABLE ROW LEVEL SECURITY;

CREATE POLICY agents_tenant_isolation ON ai_agents.agents
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE ai_agents.agents IS 'Configuracion de agentes de IA (MGN-018)';
COMMENT ON COLUMN ai_agents.agents.system_prompt IS 'Instrucciones base y personalidad del agente';

---

2. knowledge_bases

Bases de conocimiento para RAG.

CREATE TABLE ai_agents.knowledge_bases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500),

    -- Configuracion de embeddings
    embedding_model VARCHAR(50) NOT NULL DEFAULT 'text-embedding-3-small',
    embedding_dimensions INT NOT NULL DEFAULT 1536,
    chunk_size INT NOT NULL DEFAULT 512,
    chunk_overlap INT NOT NULL DEFAULT 50,

    -- Estadisticas
    total_documents INT NOT NULL DEFAULT 0,
    total_chunks INT NOT NULL DEFAULT 0,
    total_tokens INT NOT NULL DEFAULT 0,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'empty',
    last_indexed_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,

    CONSTRAINT uq_knowledge_bases UNIQUE (tenant_id, name),
    CONSTRAINT chk_knowledge_bases_status CHECK (status IN (
        'empty', 'indexing', 'ready', 'error'
    )),
    CONSTRAINT chk_knowledge_bases_chunk CHECK (chunk_size >= 100 AND chunk_size <= 2000),
    CONSTRAINT chk_knowledge_bases_overlap CHECK (chunk_overlap >= 0 AND chunk_overlap < chunk_size)
);

-- Indices
CREATE INDEX idx_knowledge_bases_tenant ON ai_agents.knowledge_bases(tenant_id);
CREATE INDEX idx_knowledge_bases_status ON ai_agents.knowledge_bases(status);

-- Trigger para updated_at
CREATE TRIGGER trg_knowledge_bases_updated_at
    BEFORE UPDATE ON ai_agents.knowledge_bases
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- RLS
ALTER TABLE ai_agents.knowledge_bases ENABLE ROW LEVEL SECURITY;

CREATE POLICY knowledge_bases_tenant_isolation ON ai_agents.knowledge_bases
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE ai_agents.knowledge_bases IS 'Bases de conocimiento para RAG';
COMMENT ON COLUMN ai_agents.knowledge_bases.embedding_model IS 'Modelo de OpenAI para embeddings';

---

3. agent_knowledge_bases

Relacion agentes-bases de conocimiento.

CREATE TABLE ai_agents.agent_knowledge_bases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_id UUID NOT NULL REFERENCES ai_agents.agents(id) ON DELETE CASCADE,
    knowledge_base_id UUID NOT NULL REFERENCES ai_agents.knowledge_bases(id) ON DELETE CASCADE,

    -- Configuracion de busqueda
    search_config JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Ejemplo: {"top_k": 5, "threshold": 0.7, "rerank": true}

    priority INT NOT NULL DEFAULT 0,  -- Para ordenar multiples KBs

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_agent_knowledge_bases UNIQUE (agent_id, knowledge_base_id)
);

-- Indices
CREATE INDEX idx_agent_kb_agent ON ai_agents.agent_knowledge_bases(agent_id);
CREATE INDEX idx_agent_kb_kb ON ai_agents.agent_knowledge_bases(knowledge_base_id);

-- Comentarios
COMMENT ON TABLE ai_agents.agent_knowledge_bases IS 'Relacion agentes con bases de conocimiento';

---

4. kb_documents

Documentos en bases de conocimiento.

CREATE TABLE ai_agents.kb_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    knowledge_base_id UUID NOT NULL REFERENCES ai_agents.knowledge_bases(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(200) NOT NULL,
    source_type VARCHAR(30) NOT NULL,
    -- file, url, text, api

    -- Fuente original
    source_url VARCHAR(1000),
    file_path VARCHAR(500),
    mime_type VARCHAR(100),
    file_size BIGINT,

    -- Contenido original
    raw_content TEXT,

    -- Estado de procesamiento
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    error_message VARCHAR(500),

    -- Estadisticas
    chunk_count INT NOT NULL DEFAULT 0,
    token_count INT NOT NULL DEFAULT 0,

    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,
    -- Ejemplo: {"author": "...", "created_date": "...", "category": "..."}

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    indexed_at TIMESTAMPTZ,
    created_by UUID,

    CONSTRAINT chk_kb_documents_source CHECK (source_type IN (
        'file', 'url', 'text', 'api', 'notion', 'confluence'
    )),
    CONSTRAINT chk_kb_documents_status CHECK (status IN (
        'pending', 'processing', 'indexed', 'failed'
    ))
);

-- Indices
CREATE INDEX idx_kb_documents_kb ON ai_agents.kb_documents(knowledge_base_id);
CREATE INDEX idx_kb_documents_status ON ai_agents.kb_documents(status);
CREATE INDEX idx_kb_documents_source ON ai_agents.kb_documents(source_type);

-- Trigger para updated_at
CREATE TRIGGER trg_kb_documents_updated_at
    BEFORE UPDATE ON ai_agents.kb_documents
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comentarios
COMMENT ON TABLE ai_agents.kb_documents IS 'Documentos fuente para bases de conocimiento';

---

5. kb_chunks

Chunks con embeddings para busqueda vectorial.

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

CREATE TABLE ai_agents.kb_chunks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES ai_agents.kb_documents(id) ON DELETE CASCADE,

    -- Posicion en documento
    chunk_index INT NOT NULL,

    -- Contenido
    content TEXT NOT NULL,
    content_hash VARCHAR(64),  -- SHA-256 para detectar duplicados

    -- Embedding (pgvector)
    embedding vector(1536),  -- Dimension de text-embedding-3-small

    -- Metadata para filtros
    metadata JSONB DEFAULT '{}'::jsonb,
    -- Ejemplo: {"section": "FAQ", "page": 5, "heading": "Como usar..."}

    -- Tokens
    token_count INT NOT NULL DEFAULT 0,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_kb_chunks UNIQUE (document_id, chunk_index)
);

-- Indice vectorial para busqueda de similitud
CREATE INDEX idx_kb_chunks_embedding ON ai_agents.kb_chunks
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- Indices regulares
CREATE INDEX idx_kb_chunks_document ON ai_agents.kb_chunks(document_id);
CREATE INDEX idx_kb_chunks_hash ON ai_agents.kb_chunks(content_hash);

-- Comentarios
COMMENT ON TABLE ai_agents.kb_chunks IS 'Chunks con embeddings para busqueda vectorial (pgvector)';
COMMENT ON COLUMN ai_agents.kb_chunks.embedding IS 'Vector de 1536 dimensiones (text-embedding-3-small)';

---

6. tool_definitions

Definiciones de herramientas/acciones.

CREATE TABLE ai_agents.tool_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificacion
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500) NOT NULL,

    -- Categoria
    category VARCHAR(50) NOT NULL,
    -- crm, sales, support, inventory, custom

    -- Schema de parametros (OpenAI function schema)
    parameters_schema JSONB NOT NULL,
    -- Ejemplo: {
    --   "type": "object",
    --   "properties": {
    --     "order_id": {"type": "string", "description": "ID del pedido"},
    --   },
    --   "required": ["order_id"]
    -- }

    -- Configuracion de ejecucion
    handler_type VARCHAR(20) NOT NULL DEFAULT 'internal',
    -- internal, webhook, api
    handler_config JSONB DEFAULT '{}'::jsonb,
    -- Ejemplo webhook: {"url": "https://...", "method": "POST"}

    -- Permisos requeridos
    required_permissions TEXT[] DEFAULT '{}',

    -- Estado
    is_system BOOLEAN NOT NULL DEFAULT false,
    is_active BOOLEAN NOT NULL DEFAULT true,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_tool_definitions_category CHECK (category IN (
        'crm', 'sales', 'support', 'inventory', 'financial', 'custom', 'system'
    )),
    CONSTRAINT chk_tool_definitions_handler CHECK (handler_type IN (
        'internal', 'webhook', 'api', 'function'
    ))
);

-- Indices
CREATE INDEX idx_tool_definitions_category ON ai_agents.tool_definitions(category);
CREATE INDEX idx_tool_definitions_active ON ai_agents.tool_definitions(is_active)
    WHERE is_active = true;

-- Trigger para updated_at
CREATE TRIGGER trg_tool_definitions_updated_at
    BEFORE UPDATE ON ai_agents.tool_definitions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comentarios
COMMENT ON TABLE ai_agents.tool_definitions IS 'Definiciones de herramientas para function calling';
COMMENT ON COLUMN ai_agents.tool_definitions.parameters_schema IS 'Schema JSON de parametros (formato OpenAI)';

---

7. agent_tools

Herramientas habilitadas por agente.

CREATE TABLE ai_agents.agent_tools (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_id UUID NOT NULL REFERENCES ai_agents.agents(id) ON DELETE CASCADE,
    tool_id UUID NOT NULL REFERENCES ai_agents.tool_definitions(id),

    -- Configuracion especifica para el agente
    config_override JSONB DEFAULT '{}'::jsonb,

    -- Estado
    is_enabled BOOLEAN NOT NULL DEFAULT true,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_agent_tools UNIQUE (agent_id, tool_id)
);

-- Indices
CREATE INDEX idx_agent_tools_agent ON ai_agents.agent_tools(agent_id);
CREATE INDEX idx_agent_tools_tool ON ai_agents.agent_tools(tool_id);

-- Comentarios
COMMENT ON TABLE ai_agents.agent_tools IS 'Herramientas habilitadas por agente';

---

8. conversations

Conversaciones con agentes.

CREATE TABLE ai_agents.conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id),
    agent_id UUID NOT NULL REFERENCES ai_agents.agents(id),

    -- Origen
    channel VARCHAR(30) NOT NULL,
    -- whatsapp, web_chat, api, email
    external_id VARCHAR(100),  -- ID de conversacion en canal (ej: whatsapp_conversation_id)

    -- Contacto
    contact_id UUID REFERENCES core_catalogs.contacts(id),
    contact_identifier VARCHAR(200),  -- Email, telefono, etc

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    escalated_to_human BOOLEAN NOT NULL DEFAULT false,
    escalated_at TIMESTAMPTZ,
    escalated_reason VARCHAR(500),

    -- Estadisticas
    message_count INT NOT NULL DEFAULT 0,
    total_tokens INT NOT NULL DEFAULT 0,
    total_cost DECIMAL(10,6) NOT NULL DEFAULT 0,

    -- Duracion
    started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_message_at TIMESTAMPTZ,
    ended_at TIMESTAMPTZ,

    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_conversations_channel CHECK (channel IN (
        'whatsapp', 'web_chat', 'api', 'email', 'sms'
    )),
    CONSTRAINT chk_conversations_status CHECK (status IN (
        'active', 'resolved', 'escalated', 'closed', 'timeout'
    ))
);

-- Indices
CREATE INDEX idx_ai_conversations_tenant ON ai_agents.conversations(tenant_id);
CREATE INDEX idx_ai_conversations_agent ON ai_agents.conversations(agent_id);
CREATE INDEX idx_ai_conversations_contact ON ai_agents.conversations(contact_id);
CREATE INDEX idx_ai_conversations_channel ON ai_agents.conversations(channel);
CREATE INDEX idx_ai_conversations_status ON ai_agents.conversations(status);
CREATE INDEX idx_ai_conversations_date ON ai_agents.conversations(started_at DESC);
CREATE INDEX idx_ai_conversations_external ON ai_agents.conversations(external_id)
    WHERE external_id IS NOT NULL;

-- RLS
ALTER TABLE ai_agents.conversations ENABLE ROW LEVEL SECURITY;

CREATE POLICY conversations_tenant_isolation ON ai_agents.conversations
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE ai_agents.conversations IS 'Conversaciones con agentes de IA';

---

9. messages

Mensajes de conversaciones.

CREATE TABLE ai_agents.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES ai_agents.conversations(id) ON DELETE CASCADE,

    -- Rol
    role VARCHAR(20) NOT NULL,
    -- user, assistant, system, tool

    -- Contenido
    content TEXT,

    -- Tool calls (para assistant)
    tool_calls JSONB,
    -- Ejemplo: [{"id": "...", "type": "function", "function": {"name": "...", "arguments": "..."}}]

    -- Tool response (para tool role)
    tool_call_id VARCHAR(100),
    tool_name VARCHAR(100),

    -- Tokens
    input_tokens INT NOT NULL DEFAULT 0,
    output_tokens INT NOT NULL DEFAULT 0,

    -- Contexto RAG usado
    context_chunks UUID[],  -- IDs de kb_chunks usados

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'completed',
    error_message VARCHAR(500),

    -- Latencia
    latency_ms INT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_messages_role CHECK (role IN ('user', 'assistant', 'system', 'tool')),
    CONSTRAINT chk_messages_status CHECK (status IN ('pending', 'streaming', 'completed', 'failed'))
);

-- Indices
CREATE INDEX idx_ai_messages_conversation ON ai_agents.messages(conversation_id);
CREATE INDEX idx_ai_messages_role ON ai_agents.messages(role);
CREATE INDEX idx_ai_messages_date ON ai_agents.messages(created_at DESC);

-- Comentarios
COMMENT ON TABLE ai_agents.messages IS 'Mensajes de conversaciones con IA';
COMMENT ON COLUMN ai_agents.messages.context_chunks IS 'Chunks de KB usados para generar respuesta (RAG)';

---

10. tool_executions

Historial de ejecuciones de herramientas.

CREATE TABLE ai_agents.tool_executions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id UUID NOT NULL REFERENCES ai_agents.messages(id) ON DELETE CASCADE,
    tool_id UUID NOT NULL REFERENCES ai_agents.tool_definitions(id),

    -- Ejecucion
    tool_call_id VARCHAR(100) NOT NULL,
    input_params JSONB NOT NULL,
    output_result JSONB,

    -- Estado
    success BOOLEAN,
    error_message VARCHAR(500),

    -- Tiempo de ejecucion
    execution_time_ms INT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMPTZ
);

-- Indices
CREATE INDEX idx_tool_executions_message ON ai_agents.tool_executions(message_id);
CREATE INDEX idx_tool_executions_tool ON ai_agents.tool_executions(tool_id);
CREATE INDEX idx_tool_executions_success ON ai_agents.tool_executions(success);

-- Comentarios
COMMENT ON TABLE ai_agents.tool_executions IS 'Historial de ejecucion de herramientas';

---

11. feedback

Feedback de usuarios sobre respuestas.

CREATE TABLE ai_agents.feedback (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id),
    message_id UUID NOT NULL REFERENCES ai_agents.messages(id),
    conversation_id UUID NOT NULL REFERENCES ai_agents.conversations(id),

    -- Rating
    rating VARCHAR(20) NOT NULL,
    -- positive, negative, neutral

    -- Categoria de feedback
    category VARCHAR(50),
    -- wrong_answer, unhelpful, inappropriate, too_slow, other

    -- Comentario
    comment TEXT,

    -- Correccion sugerida
    corrected_response TEXT,

    -- Estado de revision
    reviewed BOOLEAN NOT NULL DEFAULT false,
    reviewed_by UUID,
    reviewed_at TIMESTAMPTZ,
    review_notes TEXT,

    -- Para entrenamiento
    used_for_training BOOLEAN NOT NULL DEFAULT false,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,

    CONSTRAINT chk_feedback_rating CHECK (rating IN ('positive', 'negative', 'neutral'))
);

-- Indices
CREATE INDEX idx_feedback_tenant ON ai_agents.feedback(tenant_id);
CREATE INDEX idx_feedback_message ON ai_agents.feedback(message_id);
CREATE INDEX idx_feedback_conversation ON ai_agents.feedback(conversation_id);
CREATE INDEX idx_feedback_rating ON ai_agents.feedback(rating);
CREATE INDEX idx_feedback_pending ON ai_agents.feedback(reviewed)
    WHERE reviewed = false;

-- RLS
ALTER TABLE ai_agents.feedback ENABLE ROW LEVEL SECURITY;

CREATE POLICY feedback_tenant_isolation ON ai_agents.feedback
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE ai_agents.feedback IS 'Feedback de usuarios sobre respuestas de IA';

---

12. usage_logs

Registro de uso de tokens.

CREATE TABLE ai_agents.usage_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id),
    agent_id UUID REFERENCES ai_agents.agents(id),

    -- Periodo
    period VARCHAR(7) NOT NULL,  -- YYYY-MM
    period_date DATE NOT NULL,

    -- Modelo
    model VARCHAR(50) NOT NULL,
    provider VARCHAR(20) NOT NULL,

    -- Tokens
    input_tokens BIGINT NOT NULL DEFAULT 0,
    output_tokens BIGINT NOT NULL DEFAULT 0,
    total_tokens BIGINT NOT NULL DEFAULT 0,

    -- Embeddings
    embedding_tokens BIGINT NOT NULL DEFAULT 0,

    -- Costos (USD)
    input_cost DECIMAL(10,6) NOT NULL DEFAULT 0,
    output_cost DECIMAL(10,6) NOT NULL DEFAULT 0,
    embedding_cost DECIMAL(10,6) NOT NULL DEFAULT 0,
    total_cost DECIMAL(10,6) NOT NULL DEFAULT 0,

    -- Estadisticas
    request_count INT NOT NULL DEFAULT 0,
    error_count INT NOT NULL DEFAULT 0,
    avg_latency_ms DECIMAL(10,2),

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_usage_logs UNIQUE (tenant_id, agent_id, period, model),
    CONSTRAINT chk_usage_logs_period CHECK (period ~ '^\d{4}-\d{2}$')
);

-- Indices
CREATE INDEX idx_usage_logs_tenant ON ai_agents.usage_logs(tenant_id);
CREATE INDEX idx_usage_logs_agent ON ai_agents.usage_logs(agent_id);
CREATE INDEX idx_usage_logs_period ON ai_agents.usage_logs(period DESC);
CREATE INDEX idx_usage_logs_model ON ai_agents.usage_logs(model);

-- Trigger para updated_at
CREATE TRIGGER trg_usage_logs_updated_at
    BEFORE UPDATE ON ai_agents.usage_logs
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- RLS
ALTER TABLE ai_agents.usage_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY usage_logs_tenant_isolation ON ai_agents.usage_logs
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE ai_agents.usage_logs IS 'Registro de uso de tokens por periodo';
COMMENT ON COLUMN ai_agents.usage_logs.total_cost IS 'Costo total en USD';

---

Funciones de Utilidad

Busqueda Vectorial en KB

CREATE OR REPLACE FUNCTION ai_agents.search_knowledge_base(
    p_knowledge_base_id UUID,
    p_query_embedding vector(1536),
    p_top_k INT DEFAULT 5,
    p_threshold DECIMAL DEFAULT 0.7
) RETURNS TABLE (
    chunk_id UUID,
    document_id UUID,
    document_name VARCHAR,
    content TEXT,
    similarity DECIMAL,
    metadata JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        c.id AS chunk_id,
        d.id AS document_id,
        d.name AS document_name,
        c.content,
        (1 - (c.embedding <=> p_query_embedding))::DECIMAL AS similarity,
        c.metadata
    FROM ai_agents.kb_chunks c
    JOIN ai_agents.kb_documents d ON d.id = c.document_id
    WHERE d.knowledge_base_id = p_knowledge_base_id
      AND d.status = 'indexed'
      AND (1 - (c.embedding <=> p_query_embedding)) >= p_threshold
    ORDER BY c.embedding <=> p_query_embedding
    LIMIT p_top_k;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION ai_agents.search_knowledge_base IS 'Busqueda vectorial en base de conocimiento usando cosine similarity';

Registrar Uso de Tokens

CREATE OR REPLACE FUNCTION ai_agents.log_token_usage(
    p_tenant_id UUID,
    p_agent_id UUID,
    p_model VARCHAR,
    p_provider VARCHAR,
    p_input_tokens INT,
    p_output_tokens INT,
    p_latency_ms INT
) RETURNS void AS $$
DECLARE
    v_period VARCHAR(7);
    v_period_date DATE;
    v_input_cost DECIMAL(10,6);
    v_output_cost DECIMAL(10,6);
BEGIN
    v_period := to_char(CURRENT_DATE, 'YYYY-MM');
    v_period_date := CURRENT_DATE;

    -- Calcular costos segun modelo (precios actuales)
    v_input_cost := p_input_tokens::DECIMAL / 1000 * CASE p_model
        WHEN 'gpt-4o' THEN 0.005
        WHEN 'gpt-4o-mini' THEN 0.00015
        WHEN 'claude-3-5-sonnet' THEN 0.003
        WHEN 'claude-3-haiku' THEN 0.00025
        ELSE 0.001
    END;

    v_output_cost := p_output_tokens::DECIMAL / 1000 * CASE p_model
        WHEN 'gpt-4o' THEN 0.015
        WHEN 'gpt-4o-mini' THEN 0.0006
        WHEN 'claude-3-5-sonnet' THEN 0.015
        WHEN 'claude-3-haiku' THEN 0.00125
        ELSE 0.002
    END;

    -- Upsert
    INSERT INTO ai_agents.usage_logs (
        tenant_id, agent_id, period, period_date, model, provider,
        input_tokens, output_tokens, total_tokens,
        input_cost, output_cost, total_cost,
        request_count, avg_latency_ms
    ) VALUES (
        p_tenant_id, p_agent_id, v_period, v_period_date, p_model, p_provider,
        p_input_tokens, p_output_tokens, p_input_tokens + p_output_tokens,
        v_input_cost, v_output_cost, v_input_cost + v_output_cost,
        1, p_latency_ms
    )
    ON CONFLICT (tenant_id, agent_id, period, model) DO UPDATE SET
        input_tokens = ai_agents.usage_logs.input_tokens + p_input_tokens,
        output_tokens = ai_agents.usage_logs.output_tokens + p_output_tokens,
        total_tokens = ai_agents.usage_logs.total_tokens + p_input_tokens + p_output_tokens,
        input_cost = ai_agents.usage_logs.input_cost + v_input_cost,
        output_cost = ai_agents.usage_logs.output_cost + v_output_cost,
        total_cost = ai_agents.usage_logs.total_cost + v_input_cost + v_output_cost,
        request_count = ai_agents.usage_logs.request_count + 1,
        avg_latency_ms = (ai_agents.usage_logs.avg_latency_ms * ai_agents.usage_logs.request_count + p_latency_ms) /
                        (ai_agents.usage_logs.request_count + 1),
        updated_at = CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

---

Data Seed

Herramientas del Sistema

INSERT INTO ai_agents.tool_definitions (code, name, description, category, parameters_schema, is_system) VALUES
    (
        'check_order_status',
        'Consultar Estado de Pedido',
        'Consulta el estado de un pedido por su numero',
        'sales',
        '{"type": "object", "properties": {"order_number": {"type": "string", "description": "Numero del pedido"}}, "required": ["order_number"]}',
        true
    ),
    (
        'create_lead',
        'Crear Lead',
        'Crea un nuevo lead en el CRM desde la conversacion',
        'crm',
        '{"type": "object", "properties": {"name": {"type": "string"}, "email": {"type": "string"}, "phone": {"type": "string"}, "notes": {"type": "string"}}, "required": ["name"]}',
        true
    ),
    (
        'search_products',
        'Buscar Productos',
        'Busca productos en el catalogo',
        'sales',
        '{"type": "object", "properties": {"query": {"type": "string"}, "category": {"type": "string"}, "max_results": {"type": "integer", "default": 5}}, "required": ["query"]}',
        true
    ),
    (
        'create_support_ticket',
        'Crear Ticket de Soporte',
        'Crea un ticket de soporte desde la conversacion',
        'support',
        '{"type": "object", "properties": {"subject": {"type": "string"}, "description": {"type": "string"}, "priority": {"type": "string", "enum": ["low", "medium", "high"]}}, "required": ["subject", "description"]}',
        true
    ),
    (
        'handoff_to_human',
        'Transferir a Humano',
        'Transfiere la conversacion a un agente humano',
        'system',
        '{"type": "object", "properties": {"reason": {"type": "string"}, "department": {"type": "string"}}, "required": ["reason"]}',
        true
    );

---

Vistas

Vista: Dashboard de Agentes

CREATE VIEW ai_agents.vw_agent_dashboard AS
SELECT
    a.id,
    a.tenant_id,
    a.name,
    a.agent_type,
    a.model,
    a.is_active,
    a.channels,
    a.total_conversations,
    a.total_messages,
    a.avg_satisfaction,
    (SELECT COUNT(*) FROM ai_agents.conversations c
     WHERE c.agent_id = a.id AND c.status = 'active') AS active_conversations,
    (SELECT COALESCE(SUM(total_tokens), 0) FROM ai_agents.usage_logs u
     WHERE u.agent_id = a.id AND u.period = to_char(CURRENT_DATE, 'YYYY-MM')) AS tokens_this_month,
    (SELECT COALESCE(SUM(total_cost), 0) FROM ai_agents.usage_logs u
     WHERE u.agent_id = a.id AND u.period = to_char(CURRENT_DATE, 'YYYY-MM')) AS cost_this_month,
    (SELECT COUNT(*) FROM ai_agents.agent_knowledge_bases akb
     WHERE akb.agent_id = a.id) AS knowledge_bases_count,
    (SELECT COUNT(*) FROM ai_agents.agent_tools at
     WHERE at.agent_id = a.id AND at.is_enabled = true) AS tools_count
FROM ai_agents.agents a;

Vista: Uso de Tokens por Tenant

CREATE VIEW ai_agents.vw_tenant_usage_summary AS
SELECT
    u.tenant_id,
    u.period,
    SUM(u.input_tokens) AS total_input_tokens,
    SUM(u.output_tokens) AS total_output_tokens,
    SUM(u.embedding_tokens) AS total_embedding_tokens,
    SUM(u.total_cost) AS total_cost,
    SUM(u.request_count) AS total_requests,
    AVG(u.avg_latency_ms) AS avg_latency
FROM ai_agents.usage_logs u
GROUP BY u.tenant_id, u.period;

---

Resumen de Tablas

Tabla	Columnas	Descripcion
agents	22	Configuracion de agentes
knowledge_bases	14	Bases de conocimiento
agent_knowledge_bases	5	Relacion agentes-KB
kb_documents	17	Documentos fuente
kb_chunks	9	Chunks con embeddings
tool_definitions	14	Definiciones de herramientas
agent_tools	5	Tools por agente
conversations	17	Conversaciones
messages	15	Mensajes
tool_executions	9	Historial de tools
feedback	13	Feedback de usuarios
usage_logs	18	Uso de tokens

Total: 12 tablas, 158 columnas

---

Historial

Version	Fecha	Autor	Cambios
1.0	2025-12-05	System	Creacion inicial (MGN-018)