codeBOKER/customer_service

1-- Supabase Database Schema for Conversation History2-- Run this in your Supabase SQL Editor3 4-- Enable UUID extension5CREATE EXTENSION IF NOT EXISTS "uuid-ossp";6 7-- Users table to store user information8CREATE TABLE IF NOT EXISTS users (9    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,10    telegram_id BIGINT UNIQUE NOT NULL,11    username VARCHAR(255),12    first_name VARCHAR(255),13    last_name VARCHAR(255),14    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),15    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()16);17 18-- Messages table to store conversation history19CREATE TABLE IF NOT EXISTS messages (20    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,21    telegram_id BIGINT NOT NULL,22    message_text TEXT NOT NULL,23    message_type VARCHAR(20) NOT NULL CHECK (message_type IN ('user', 'assistant')),24    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),25    FOREIGN KEY (telegram_id) REFERENCES users(telegram_id) ON DELETE CASCADE26);27 28-- Conversation sessions to group messages by session29CREATE TABLE IF NOT EXISTS conversation_sessions (30    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,31    telegram_id BIGINT NOT NULL,32    session_start TIMESTAMP WITH TIME ZONE DEFAULT NOW(),33    session_end TIMESTAMP WITH TIME ZONE,34    message_count INTEGER DEFAULT 0,35    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),36    FOREIGN KEY (telegram_id) REFERENCES users(telegram_id) ON DELETE CASCADE37);38 39-- Indexes for better performance40CREATE INDEX IF NOT EXISTS idx_messages_telegram_id_created_at ON messages(telegram_id, created_at);41CREATE INDEX IF NOT EXISTS idx_users_telegram_id ON users(telegram_id);42CREATE INDEX IF NOT EXISTS idx_sessions_telegram_id ON conversation_sessions(telegram_id);43 44-- Function to update updated_at timestamp45CREATE OR REPLACE FUNCTION update_updated_at_column()46RETURNS TRIGGER AS $$47BEGIN48    NEW.updated_at = NOW();49    RETURN NEW;50END;51$$ language 'plpgsql';52 53-- Trigger to update user's updated_at timestamp54CREATE TRIGGER update_user_updated_at 55    BEFORE UPDATE ON users56    FOR EACH ROW57    EXECUTE FUNCTION update_updated_at_column();58 59-- Trigger to update session message count60CREATE OR REPLACE FUNCTION update_session_count()61RETURNS TRIGGER AS $$62BEGIN63    UPDATE conversation_sessions 64    SET message_count = message_count + 1 65    WHERE telegram_id = NEW.telegram_id AND session_end IS NULL;66    RETURN NEW;67END;68$$ language 'plpgsql';69 70CREATE TRIGGER update_session_message_count71    AFTER INSERT ON messages72    FOR EACH ROW73    EXECUTE FUNCTION update_session_count();74 75-- Row Level Security (RLS) policies76ALTER TABLE users ENABLE ROW LEVEL SECURITY;77ALTER TABLE messages ENABLE ROW LEVEL SECURITY;78ALTER TABLE conversation_sessions ENABLE ROW LEVEL SECURITY;79 80-- Policy for users table (allow all operations for now)81CREATE POLICY "Enable all operations for users" ON users82    FOR ALL USING (true);83 84-- Policy for messages table (allow all operations for now)85CREATE POLICY "Enable all operations for messages" ON messages86    FOR ALL USING (true);87 88-- Policy for conversation_sessions table (allow all operations for now)89CREATE POLICY "Enable all operations for conversation_sessions" ON conversation_sessions90    FOR ALL USING (true);91