80 lines
3.9 KiB
SQL
80 lines
3.9 KiB
SQL
-- ===========================================
|
|
-- 多租户迁移: conversation-service 表
|
|
-- 为 conversations, messages, token_usages 添加 tenant_id
|
|
-- 创建 agent_executions 表
|
|
-- 日期: 2026-02-06
|
|
-- ===========================================
|
|
|
|
-- ========== 1. 确保 tenants 表和默认租户存在 ==========
|
|
CREATE TABLE IF NOT EXISTS tenants (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
slug VARCHAR(50) NOT NULL UNIQUE,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
plan VARCHAR(30) NOT NULL DEFAULT 'STANDARD',
|
|
max_users INT NOT NULL DEFAULT 100,
|
|
max_conversations_per_month INT NOT NULL DEFAULT 10000,
|
|
max_storage_mb INT NOT NULL DEFAULT 5120,
|
|
current_user_count INT NOT NULL DEFAULT 0,
|
|
current_conversation_count INT NOT NULL DEFAULT 0,
|
|
current_storage_bytes BIGINT NOT NULL DEFAULT 0,
|
|
config JSONB NOT NULL DEFAULT '{}',
|
|
billing_email VARCHAR(255),
|
|
billing_name VARCHAR(100),
|
|
billing_phone VARCHAR(20),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
suspended_at TIMESTAMPTZ
|
|
);
|
|
|
|
INSERT INTO tenants (id, name, slug, status, plan)
|
|
VALUES ('00000000-0000-0000-0000-000000000001', 'Default Tenant', 'default', 'ACTIVE', 'ENTERPRISE')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
-- ========== 2. conversations 表 ==========
|
|
ALTER TABLE conversations ADD COLUMN IF NOT EXISTS tenant_id UUID;
|
|
UPDATE conversations SET tenant_id = '00000000-0000-0000-0000-000000000001' WHERE tenant_id IS NULL;
|
|
ALTER TABLE conversations ALTER COLUMN tenant_id SET NOT NULL;
|
|
ALTER TABLE conversations ALTER COLUMN tenant_id SET DEFAULT '00000000-0000-0000-0000-000000000001';
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_tenant ON conversations(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_tenant_user ON conversations(tenant_id, user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_tenant_status ON conversations(tenant_id, status);
|
|
|
|
-- ========== 3. messages 表 ==========
|
|
ALTER TABLE messages ADD COLUMN IF NOT EXISTS tenant_id UUID;
|
|
UPDATE messages SET tenant_id = '00000000-0000-0000-0000-000000000001' WHERE tenant_id IS NULL;
|
|
ALTER TABLE messages ALTER COLUMN tenant_id SET NOT NULL;
|
|
ALTER TABLE messages ALTER COLUMN tenant_id SET DEFAULT '00000000-0000-0000-0000-000000000001';
|
|
CREATE INDEX IF NOT EXISTS idx_messages_tenant ON messages(tenant_id);
|
|
|
|
-- ========== 4. token_usages 表 ==========
|
|
ALTER TABLE token_usages ADD COLUMN IF NOT EXISTS tenant_id UUID;
|
|
UPDATE token_usages SET tenant_id = '00000000-0000-0000-0000-000000000001' WHERE tenant_id IS NULL;
|
|
ALTER TABLE token_usages ALTER COLUMN tenant_id SET NOT NULL;
|
|
ALTER TABLE token_usages ALTER COLUMN tenant_id SET DEFAULT '00000000-0000-0000-0000-000000000001';
|
|
CREATE INDEX IF NOT EXISTS idx_token_usages_tenant ON token_usages(tenant_id);
|
|
|
|
-- ========== 5. agent_executions 表 ==========
|
|
CREATE TABLE IF NOT EXISTS agent_executions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
conversation_id UUID NOT NULL,
|
|
message_id UUID,
|
|
user_id UUID,
|
|
agent_type VARCHAR(30) NOT NULL,
|
|
agent_name VARCHAR(50) NOT NULL,
|
|
duration_ms INT NOT NULL DEFAULT 0,
|
|
success BOOLEAN NOT NULL DEFAULT TRUE,
|
|
error_message TEXT,
|
|
tool_calls_count INT NOT NULL DEFAULT 0,
|
|
input_tokens INT NOT NULL DEFAULT 0,
|
|
output_tokens INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_agent_executions_tenant ON agent_executions(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_agent_executions_conversation ON agent_executions(conversation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_agent_executions_agent_type ON agent_executions(agent_type);
|
|
CREATE INDEX IF NOT EXISTS idx_agent_executions_created ON agent_executions(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_agent_executions_tenant_date ON agent_executions(tenant_id, created_at);
|