Spaces:
Running
Running
File size: 5,359 Bytes
b5ef2bb | 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 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 | -- ============================================
-- UoV AI Assistant - Supabase Database Schema
-- ============================================
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- ============================================
-- Table: chat_sessions
-- Purpose: Store chat session metadata
-- ============================================
CREATE TABLE IF NOT EXISTS chat_sessions (
session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Index for faster lookups
CREATE INDEX IF NOT EXISTS idx_chat_sessions_created_at
ON chat_sessions(created_at DESC);
-- ============================================
-- Table: chat_messages
-- Purpose: Store all chat messages (user + assistant)
-- ============================================
CREATE TABLE IF NOT EXISTS chat_messages (
message_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id UUID NOT NULL REFERENCES chat_sessions(session_id) ON DELETE CASCADE,
role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant')),
content TEXT NOT NULL,
citations JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexes for faster queries
CREATE INDEX IF NOT EXISTS idx_chat_messages_session_id
ON chat_messages(session_id);
CREATE INDEX IF NOT EXISTS idx_chat_messages_created_at
ON chat_messages(created_at DESC);
-- ============================================
-- Table: feedback
-- Purpose: Store user feedback on assistant responses
-- ============================================
CREATE TABLE IF NOT EXISTS feedback (
feedback_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id UUID NOT NULL REFERENCES chat_sessions(session_id) ON DELETE CASCADE,
message_id UUID NOT NULL REFERENCES chat_messages(message_id) ON DELETE CASCADE,
rating VARCHAR(10) NOT NULL CHECK (rating IN ('up', 'down')),
comment TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexes for analytics
CREATE INDEX IF NOT EXISTS idx_feedback_session_id
ON feedback(session_id);
CREATE INDEX IF NOT EXISTS idx_feedback_message_id
ON feedback(message_id);
CREATE INDEX IF NOT EXISTS idx_feedback_rating
ON feedback(rating);
CREATE INDEX IF NOT EXISTS idx_feedback_created_at
ON feedback(created_at DESC);
-- ============================================
-- Table: request_logs
-- Purpose: Store API request metadata for monitoring
-- ============================================
CREATE TABLE IF NOT EXISTS request_logs (
request_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id UUID REFERENCES chat_sessions(session_id) ON DELETE SET NULL,
endpoint VARCHAR(100),
latency_ms INTEGER,
error TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexes for monitoring queries
CREATE INDEX IF NOT EXISTS idx_request_logs_session_id
ON request_logs(session_id);
CREATE INDEX IF NOT EXISTS idx_request_logs_endpoint
ON request_logs(endpoint);
CREATE INDEX IF NOT EXISTS idx_request_logs_created_at
ON request_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_request_logs_error
ON request_logs(error) WHERE error IS NOT NULL;
-- ============================================
-- Table: documents (optional metadata)
-- Purpose: Store document metadata for ingested files
-- ============================================
CREATE TABLE IF NOT EXISTS documents (
document_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
title VARCHAR(255) NOT NULL,
source_file VARCHAR(500) NOT NULL,
file_type VARCHAR(50),
total_chunks INTEGER,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Index for document lookups
CREATE INDEX IF NOT EXISTS idx_documents_source_file
ON documents(source_file);
CREATE INDEX IF NOT EXISTS idx_documents_created_at
ON documents(created_at DESC);
-- ============================================
-- Views for Analytics
-- ============================================
-- View: Session statistics
CREATE OR REPLACE VIEW session_stats AS
SELECT
cs.session_id,
cs.created_at,
COUNT(cm.message_id) as message_count,
COUNT(CASE WHEN cm.role = 'user' THEN 1 END) as user_messages,
COUNT(CASE WHEN cm.role = 'assistant' THEN 1 END) as assistant_messages,
COUNT(f.feedback_id) as feedback_count,
COUNT(CASE WHEN f.rating = 'up' THEN 1 END) as positive_feedback,
COUNT(CASE WHEN f.rating = 'down' THEN 1 END) as negative_feedback
FROM chat_sessions cs
LEFT JOIN chat_messages cm ON cs.session_id = cm.session_id
LEFT JOIN feedback f ON cs.session_id = f.session_id
GROUP BY cs.session_id, cs.created_at;
-- View: Daily metrics
CREATE OR REPLACE VIEW daily_metrics AS
SELECT
DATE(created_at) as date,
COUNT(DISTINCT session_id) as total_sessions,
COUNT(*) as total_messages
FROM chat_messages
GROUP BY DATE(created_at)
ORDER BY date DESC;
-- ============================================
-- Comments for documentation
-- ============================================
COMMENT ON TABLE chat_sessions IS 'Stores chat session metadata';
COMMENT ON TABLE chat_messages IS 'Stores all chat messages with citations';
COMMENT ON TABLE feedback IS 'Stores user feedback on assistant responses';
COMMENT ON TABLE request_logs IS 'Stores API request logs for monitoring';
COMMENT ON TABLE documents IS 'Stores metadata for ingested documents';
|