56 lines
1.8 KiB
SQL
56 lines
1.8 KiB
SQL
-- Create sessions table
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
user_id VARCHAR(255) NOT NULL,
|
|
ephemeral_token VARCHAR(512) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMP NOT NULL,
|
|
last_activity_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
ended_at TIMESTAMP,
|
|
INDEX idx_tenant_user (tenant_id, user_id),
|
|
INDEX idx_expires_at (expires_at),
|
|
INDEX idx_ended_at (ended_at)
|
|
);
|
|
|
|
-- Create tenants table
|
|
CREATE TABLE IF NOT EXISTS tenants (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
theme JSONB,
|
|
avatar_enabled BOOLEAN DEFAULT true,
|
|
greeting TEXT,
|
|
allowed_tools JSONB DEFAULT '[]'::jsonb,
|
|
policy JSONB,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Create conversations table
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
session_id VARCHAR(255) NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
|
|
user_id VARCHAR(255) NOT NULL,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
started_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
ended_at TIMESTAMP,
|
|
metadata JSONB,
|
|
INDEX idx_session (session_id),
|
|
INDEX idx_user (user_id),
|
|
INDEX idx_tenant (tenant_id)
|
|
);
|
|
|
|
-- Create conversation_messages table
|
|
CREATE TABLE IF NOT EXISTS conversation_messages (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
conversation_id VARCHAR(255) NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
role VARCHAR(50) NOT NULL, -- 'user' or 'assistant'
|
|
content TEXT NOT NULL,
|
|
audio_url TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
metadata JSONB,
|
|
INDEX idx_conversation (conversation_id),
|
|
INDEX idx_created_at (created_at)
|
|
);
|
|
|