82d6e608b1
- Create migration 2026-02-03_add_auth_locked_index.sql - Add partial index on locked_until with active lockout condition - Replace previous generic locked_until index with optimized version - Index filters for locked_until IS NOT NULL AND locked_until > NOW() - Improves query performance for account unlock validation - Resolves GAP-009 Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
65 lines
2.5 KiB
SQL
65 lines
2.5 KiB
SQL
-- =====================================================
|
|
-- LLM SCHEMA - CONVERSATIONS TABLE
|
|
-- =====================================================
|
|
-- Description: Chat conversations with LLM agent
|
|
-- Schema: llm
|
|
-- Author: Database Agent
|
|
-- Date: 2025-12-06
|
|
-- =====================================================
|
|
|
|
CREATE TABLE llm.conversations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Usuario
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- Identificación
|
|
title VARCHAR(200), -- Auto-generado o definido por usuario
|
|
|
|
-- Tipo y contexto
|
|
conversation_type llm.conversation_type DEFAULT 'general',
|
|
|
|
-- Estado
|
|
status llm.conversation_status DEFAULT 'active',
|
|
|
|
-- Resumen de conversación (generado por LLM)
|
|
summary TEXT,
|
|
|
|
-- Metadata
|
|
total_messages INTEGER DEFAULT 0,
|
|
total_tokens_used INTEGER DEFAULT 0,
|
|
|
|
-- Tags para búsqueda
|
|
tags TEXT[] DEFAULT '{}',
|
|
|
|
-- Contexto de negocio
|
|
related_symbols VARCHAR(20)[] DEFAULT '{}', -- Símbolos discutidos
|
|
related_topics TEXT[] DEFAULT '{}', -- Temas discutidos
|
|
|
|
-- Fechas
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_message_at TIMESTAMPTZ,
|
|
archived_at TIMESTAMPTZ,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Índices
|
|
CREATE INDEX idx_conversations_user ON llm.conversations(user_id);
|
|
CREATE INDEX idx_conversations_status ON llm.conversations(status);
|
|
CREATE INDEX idx_conversations_type ON llm.conversations(conversation_type);
|
|
CREATE INDEX idx_conversations_active ON llm.conversations(user_id, last_message_at DESC)
|
|
WHERE status = 'active';
|
|
CREATE INDEX idx_conversations_tags ON llm.conversations USING GIN(tags);
|
|
CREATE INDEX idx_conversations_symbols ON llm.conversations USING GIN(related_symbols);
|
|
CREATE INDEX idx_conversations_topics ON llm.conversations USING GIN(related_topics);
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE llm.conversations IS 'Chat conversations between users and LLM agent';
|
|
COMMENT ON COLUMN llm.conversations.title IS 'Conversation title (auto-generated from first messages or user-defined)';
|
|
COMMENT ON COLUMN llm.conversations.summary IS 'AI-generated summary of conversation content';
|
|
COMMENT ON COLUMN llm.conversations.total_tokens_used IS 'Cumulative token count for cost tracking';
|
|
COMMENT ON COLUMN llm.conversations.related_symbols IS 'Trading symbols mentioned in conversation';
|
|
COMMENT ON COLUMN llm.conversations.related_topics IS 'Topics discussed (e.g., technical_analysis, risk_management)';
|