30 lines
1.1 KiB
SQL
30 lines
1.1 KiB
SQL
-- iAs 初始数据库迁移
|
|
-- 认证信息和聊天记录存储
|
|
|
|
-- 应用状态表（key-value 存储，用于认证信息等）
|
|
CREATE TABLE IF NOT EXISTS app_state (
|
|
key TEXT PRIMARY KEY,
|
|
value JSONB NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- 聊天记录表
|
|
CREATE TABLE IF NOT EXISTS chat_records (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
direction TEXT NOT NULL, -- 'inbound' | 'outbound'
|
|
user_id TEXT NOT NULL, -- 微信用户 ID
|
|
account_id TEXT NOT NULL, -- 机器人账号 ID
|
|
text TEXT NOT NULL, -- 消息内容
|
|
source TEXT NOT NULL DEFAULT '', -- 消息来源
|
|
context_token TEXT NOT NULL DEFAULT '', -- 上下文令牌
|
|
message_id TEXT NOT NULL DEFAULT '',
|
|
meta JSONB NOT NULL DEFAULT '{}'::JSONB
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chat_records_user_created
|
|
ON chat_records (user_id, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chat_records_account_created
|
|
ON chat_records (account_id, created_at DESC);
|