130 lines
5 KiB
MySQL
130 lines
5 KiB
MySQL
|
-- User authentication and profiles
|
||
|
|
CREATE TABLE IF NOT EXISTS users (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
||
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
||
|
|
password_hash VARCHAR(255) NOT NULL,
|
||
|
|
phone_number VARCHAR(50),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
is_active BOOLEAN DEFAULT true
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Bot configurations
|
||
|
|
CREATE TABLE IF NOT EXISTS bots (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
llm_provider VARCHAR(100) NOT NULL,
|
||
|
|
llm_config JSONB NOT NULL DEFAULT '{}',
|
||
|
|
context_provider VARCHAR(100) NOT NULL,
|
||
|
|
context_config JSONB NOT NULL DEFAULT '{}',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
is_active BOOLEAN DEFAULT true
|
||
|
|
);
|
||
|
|
|
||
|
|
-- User sessions with optimized storage
|
||
|
|
CREATE TABLE IF NOT EXISTS user_sessions (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
bot_id UUID NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
|
||
|
|
title VARCHAR(500) NOT NULL DEFAULT 'New Conversation',
|
||
|
|
answer_mode VARCHAR(50) NOT NULL DEFAULT 'direct',
|
||
|
|
context_data JSONB NOT NULL DEFAULT '{}',
|
||
|
|
current_tool VARCHAR(255),
|
||
|
|
message_count INTEGER NOT NULL DEFAULT 0,
|
||
|
|
total_tokens INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
last_activity TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(user_id, bot_id, title)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Encrypted message history with analytics-friendly structure
|
||
|
|
CREATE TABLE IF NOT EXISTS message_history (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
session_id UUID NOT NULL REFERENCES user_sessions(id) ON DELETE CASCADE,
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
role VARCHAR(50) NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
|
||
|
|
content_encrypted TEXT NOT NULL,
|
||
|
|
message_type VARCHAR(50) NOT NULL DEFAULT 'text',
|
||
|
|
media_url TEXT,
|
||
|
|
token_count INTEGER NOT NULL DEFAULT 0,
|
||
|
|
processing_time_ms INTEGER,
|
||
|
|
llm_model VARCHAR(100),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
message_index INTEGER NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Bot channel configurations
|
||
|
|
CREATE TABLE IF NOT EXISTS bot_channels (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
bot_id UUID NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
|
||
|
|
channel_type VARCHAR(50) NOT NULL CHECK (channel_type IN ('web', 'whatsapp', 'meet', 'api')),
|
||
|
|
config JSONB NOT NULL DEFAULT '{}',
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(bot_id, channel_type)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- WhatsApp number mappings
|
||
|
|
CREATE TABLE IF NOT EXISTS whatsapp_numbers (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
bot_id UUID NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
|
||
|
|
phone_number VARCHAR(50) NOT NULL,
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(phone_number, bot_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- User email mappings for web channel
|
||
|
|
CREATE TABLE IF NOT EXISTS user_emails (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
email VARCHAR(255) NOT NULL,
|
||
|
|
is_primary BOOLEAN DEFAULT false,
|
||
|
|
verified BOOLEAN DEFAULT false,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(email)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Tools registry
|
||
|
|
CREATE TABLE IF NOT EXISTS tools (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
name VARCHAR(255) UNIQUE NOT NULL,
|
||
|
|
description TEXT NOT NULL,
|
||
|
|
parameters JSONB NOT NULL DEFAULT '{}',
|
||
|
|
script TEXT NOT NULL,
|
||
|
|
is_active BOOLEAN DEFAULT true,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Manual context injections
|
||
|
|
CREATE TABLE IF NOT EXISTS context_injections (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
session_id UUID NOT NULL REFERENCES user_sessions(id) ON DELETE CASCADE,
|
||
|
|
injected_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
context_data JSONB NOT NULL,
|
||
|
|
reason TEXT,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Analytics tables
|
||
|
|
CREATE TABLE IF NOT EXISTS usage_analytics (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
bot_id UUID NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
|
||
|
|
session_id UUID NOT NULL REFERENCES user_sessions(id) ON DELETE CASCADE,
|
||
|
|
date DATE NOT NULL DEFAULT CURRENT_DATE,
|
||
|
|
message_count INTEGER NOT NULL DEFAULT 0,
|
||
|
|
total_tokens INTEGER NOT NULL DEFAULT 0,
|
||
|
|
total_processing_time_ms INTEGER NOT NULL DEFAULT 0
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes for performance
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_message_history_session_id ON message_history(session_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_message_history_created_at ON message_history(created_at);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_bot ON user_sessions(user_id, bot_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_usage_analytics_date ON usage_analytics(date);
|