Spaces:
Running
Running
File size: 3,052 Bytes
dc3879e |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
-- Database indexes for conversation and message queries
--
-- [Task]: T059
-- [From]: specs/004-ai-chatbot/tasks.md
--
-- These indexes optimize common queries for:
-- - Conversation lookup by user_id
-- - Message lookup by conversation_id
-- - Message ordering by created_at
-- - Composite indexes for filtering
-- Index on conversations for user lookup
-- Optimizes: SELECT * FROM conversations WHERE user_id = ?
CREATE INDEX IF NOT EXISTS idx_conversations_user_id
ON conversations(user_id);
-- Index on conversations for updated_at sorting (cleanup)
-- Optimizes: SELECT * FROM conversations WHERE updated_at < ? (90-day cleanup)
CREATE INDEX IF NOT EXISTS idx_conversations_updated_at
ON conversations(updated_at);
-- Composite index for user conversations ordered by activity
-- Optimizes: SELECT * FROM conversations WHERE user_id = ? ORDER BY updated_at DESC
CREATE INDEX IF NOT EXISTS idx_conversations_user_updated
ON conversations(user_id, updated_at DESC);
-- Index on messages for conversation lookup
-- Optimizes: SELECT * FROM messages WHERE conversation_id = ?
CREATE INDEX IF NOT EXISTS idx_messages_conversation_id
ON messages(conversation_id);
-- Index on messages for user lookup
-- Optimizes: SELECT * FROM messages WHERE user_id = ?
CREATE INDEX IF NOT EXISTS idx_messages_user_id
ON messages(user_id);
-- Index on messages for timestamp ordering
-- Optimizes: SELECT * FROM messages WHERE conversation_id = ? ORDER BY created_at ASC
CREATE INDEX IF NOT EXISTS idx_messages_created_at
ON messages(created_at);
-- Composite index for conversation message retrieval
-- Optimizes: SELECT * FROM messages WHERE conversation_id = ? ORDER BY created_at ASC
CREATE INDEX IF NOT EXISTS idx_messages_conversation_created
ON messages(conversation_id, created_at ASC);
-- Index on messages for role filtering
-- Optimizes: SELECT * FROM messages WHERE conversation_id = ? AND role = ?
CREATE INDEX IF NOT EXISTS idx_messages_conversation_role
ON messages(conversation_id, role);
-- Index on tasks for user lookup (if not exists)
-- Optimizes: SELECT * FROM tasks WHERE user_id = ?
CREATE INDEX IF NOT EXISTS idx_tasks_user_id
ON tasks(user_id);
-- Index on tasks for completion status filtering
-- Optimizes: SELECT * FROM tasks WHERE user_id = ? AND completed = ?
CREATE INDEX IF NOT EXISTS idx_tasks_user_completed
ON tasks(user_id, completed);
-- Index on tasks for due date filtering
-- Optimizes: SELECT * FROM tasks WHERE user_id = ? AND due_date IS NOT NULL AND due_date < ?
CREATE INDEX IF NOT EXISTS idx_tasks_due_date
ON tasks(due_date) WHERE due_date IS NOT NULL;
-- Composite index for task priority filtering
-- Optimizes: SELECT * FROM tasks WHERE user_id = ? AND priority = ?
CREATE INDEX IF NOT EXISTS idx_tasks_user_priority
ON tasks(user_id, priority);
-- Index on tasks for created_at sorting
-- Optimizes: SELECT * FROM tasks WHERE user_id = ? ORDER BY created_at DESC
CREATE INDEX IF NOT EXISTS idx_tasks_user_created
ON tasks(user_id, created_at DESC);
|