83 lines
2.5 KiB
SQL
83 lines
2.5 KiB
SQL
-- Virtual Banker Database Schema
|
|
-- This file contains the complete schema for reference
|
|
|
|
-- Sessions
|
|
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)
|
|
);
|
|
|
|
-- Tenants
|
|
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()
|
|
);
|
|
|
|
-- Conversations
|
|
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)
|
|
);
|
|
|
|
-- Conversation Messages
|
|
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,
|
|
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)
|
|
);
|
|
|
|
-- Conversation State
|
|
CREATE TABLE IF NOT EXISTS conversation_state (
|
|
session_id VARCHAR(255) PRIMARY KEY REFERENCES sessions(id) ON DELETE CASCADE,
|
|
workflow VARCHAR(255),
|
|
step VARCHAR(255),
|
|
context JSONB DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMP
|
|
);
|
|
|
|
-- Knowledge Base (requires pgvector extension)
|
|
CREATE TABLE IF NOT EXISTS knowledge_base (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
title VARCHAR(500),
|
|
content TEXT NOT NULL,
|
|
embedding vector(1536),
|
|
metadata JSONB,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
INDEX idx_tenant (tenant_id)
|
|
);
|
|
|