57 lines
2.5 KiB
SQL
57 lines
2.5 KiB
SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS bots (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(255) NOT NULL,
|
|
llm_provider VARCHAR(100) NOT NULL,
|
|
config JSONB DEFAULT '{}',
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_sessions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
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,
|
|
context_data JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id, bot_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS message_history (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
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,
|
|
content_encrypted TEXT NOT NULL,
|
|
message_type VARCHAR(50) DEFAULT 'text',
|
|
message_index INTEGER NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_sessions_bot_id ON user_sessions(bot_id);
|
|
CREATE INDEX IF NOT EXISTS idx_message_history_session_id ON message_history(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_message_history_user_id ON message_history(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_message_history_created_at ON message_history(created_at);
|
|
|
|
INSERT INTO bots (id, name, llm_provider)
|
|
VALUES ('00000000-0000-0000-0000-000000000000', 'Default Bot', 'mock')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO users (id, username, email, password_hash)
|
|
VALUES ('00000000-0000-0000-0000-000000000001', 'demo', 'demo@example.com', '$argon2id$v=19$m=19456,t=2,p=1$c29tZXNhbHQ$RdescudvJCsgt3ub+b+dWRWJTmaaJObG')
|
|
ON CONFLICT (id) DO NOTHING;
|