Spaces:
Sleeping
Sleeping
File size: 3,212 Bytes
511ba56 | 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 | -- Supabase Database Schema for Conversation History
-- Run this in your Supabase SQL Editor
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Users table to store user information
CREATE TABLE IF NOT EXISTS users (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
telegram_id BIGINT UNIQUE NOT NULL,
username VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Messages table to store conversation history
CREATE TABLE IF NOT EXISTS messages (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
telegram_id BIGINT NOT NULL,
message_text TEXT NOT NULL,
message_type VARCHAR(20) NOT NULL CHECK (message_type IN ('user', 'assistant')),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
FOREIGN KEY (telegram_id) REFERENCES users(telegram_id) ON DELETE CASCADE
);
-- Conversation sessions to group messages by session
CREATE TABLE IF NOT EXISTS conversation_sessions (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
telegram_id BIGINT NOT NULL,
session_start TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
session_end TIMESTAMP WITH TIME ZONE,
message_count INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
FOREIGN KEY (telegram_id) REFERENCES users(telegram_id) ON DELETE CASCADE
);
-- Indexes for better performance
CREATE INDEX IF NOT EXISTS idx_messages_telegram_id_created_at ON messages(telegram_id, created_at);
CREATE INDEX IF NOT EXISTS idx_users_telegram_id ON users(telegram_id);
CREATE INDEX IF NOT EXISTS idx_sessions_telegram_id ON conversation_sessions(telegram_id);
-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
-- Trigger to update user's updated_at timestamp
CREATE TRIGGER update_user_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Trigger to update session message count
CREATE OR REPLACE FUNCTION update_session_count()
RETURNS TRIGGER AS $$
BEGIN
UPDATE conversation_sessions
SET message_count = message_count + 1
WHERE telegram_id = NEW.telegram_id AND session_end IS NULL;
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_session_message_count
AFTER INSERT ON messages
FOR EACH ROW
EXECUTE FUNCTION update_session_count();
-- Row Level Security (RLS) policies
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversation_sessions ENABLE ROW LEVEL SECURITY;
-- Policy for users table (allow all operations for now)
CREATE POLICY "Enable all operations for users" ON users
FOR ALL USING (true);
-- Policy for messages table (allow all operations for now)
CREATE POLICY "Enable all operations for messages" ON messages
FOR ALL USING (true);
-- Policy for conversation_sessions table (allow all operations for now)
CREATE POLICY "Enable all operations for conversation_sessions" ON conversation_sessions
FOR ALL USING (true);
|