selfrelease
c1cd6bc18d
- 创建 conversations 表（对话记录） - 创建 messages 表（对话消息） - 添加必要的索引和外键约束 - 为 seed_fazhiribao_demo_data.sql 准备表结构
35 lines
1.7 KiB
SQL
35 lines
1.7 KiB
SQL
-- 创建对话（conversations）表
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
app_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
title VARCHAR(500) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- 创建消息（messages）表
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
role VARCHAR(50) NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- 创建索引以提升查询性能
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_app_id ON conversations(app_id);
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_user_id ON conversations(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_conversations_created_at ON conversations(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_conversation_id ON messages(conversation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);
|
|
|
|
-- 添加注释
|
|
COMMENT ON TABLE conversations IS '应用对话记录表';
|
|
COMMENT ON TABLE messages IS '对话消息表';
|
|
COMMENT ON COLUMN conversations.app_id IS '关联的应用ID';
|
|
COMMENT ON COLUMN conversations.user_id IS '发起对话的用户ID';
|
|
COMMENT ON COLUMN conversations.title IS '对话标题';
|
|
COMMENT ON COLUMN messages.conversation_id IS '所属对话ID';
|
|
COMMENT ON COLUMN messages.role IS '消息角色：user/assistant/system';
|
|
COMMENT ON COLUMN messages.content IS '消息内容'; |