63 lines
4.3 KiB
SQL
63 lines
4.3 KiB
SQL
-- Spark IM V3 initial schema. Message body lives in MySQL; dialog list lives in Redis.
|
||
|
||
CREATE TABLE IF NOT EXISTS t_conversations (
|
||
id BIGINT PRIMARY KEY COMMENT 'Snowflake 会话 id，时间有序主键',
|
||
type TINYINT NOT NULL COMMENT '会话类型：1=单聊 2=群聊',
|
||
last_msg_id BIGINT NOT NULL DEFAULT 0 COMMENT '最新消息 Snowflake id，0 表示尚无消息',
|
||
last_msg_at DATETIME(3) NOT NULL COMMENT '最新消息时间（毫秒精度）',
|
||
created_at DATETIME(3) NOT NULL COMMENT '会话创建时间'
|
||
) COMMENT='会话。单聊/群聊共用一张表；列表与未读在 Redis，不在此表热更新';
|
||
|
||
CREATE TABLE IF NOT EXISTS t_p2p_conversations (
|
||
user_small BIGINT NOT NULL COMMENT '两人中较小的 user_id，保证同一对用户只有一行',
|
||
user_big BIGINT NOT NULL COMMENT '两人中较大的 user_id',
|
||
conversation_id BIGINT NOT NULL COMMENT '对应 t_conversations.id',
|
||
PRIMARY KEY (user_small, user_big),
|
||
UNIQUE KEY uk_conversation (conversation_id)
|
||
) COMMENT='单聊唯一索引。按 (较小 uid, 较大 uid) 定位会话，避免 A-B / B-A 两条记录';
|
||
|
||
CREATE TABLE IF NOT EXISTS t_conversation_members (
|
||
conversation_id BIGINT NOT NULL COMMENT '会话 id',
|
||
user_id BIGINT NOT NULL COMMENT '成员 user_id',
|
||
last_read_msg_id BIGINT NOT NULL DEFAULT 0 COMMENT '该成员已读到的最大消息 id，未读真相；Redis 丢了可从此重建',
|
||
joined_at DATETIME(3) NOT NULL COMMENT '加入会话时间',
|
||
muted TINYINT NOT NULL DEFAULT 0 COMMENT '0=未静音 1=免打扰',
|
||
PRIMARY KEY (conversation_id, user_id),
|
||
KEY idx_user_id (user_id)
|
||
) COMMENT='会话成员。热路径会话列表不扫此表；last_read_msg_id 是已读持久化';
|
||
|
||
CREATE TABLE IF NOT EXISTS t_messages (
|
||
id BIGINT PRIMARY KEY COMMENT 'Snowflake MsgId，主键、排序键、同步游标',
|
||
conversation_id BIGINT NOT NULL COMMENT '所属会话 id',
|
||
sender_id BIGINT NOT NULL COMMENT '发送者 user_id',
|
||
client_msg_id VARCHAR(64) NOT NULL COMMENT '客户端消息 id，与 sender+会话 组成幂等键',
|
||
message_type SMALLINT NOT NULL COMMENT '消息类型：1文本 2图片 3语音 4视频 5表情 6GIF 7文件 8位置 9系统',
|
||
content BLOB NOT NULL COMMENT '序列化后的消息体（JSON 字节），禁止存原始 string；媒体只存对象存储 key',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常 2=已撤回 3=审核隐藏',
|
||
created_at DATETIME(3) NOT NULL COMMENT '服务端入库时间',
|
||
UNIQUE KEY uk_sender_conv_client_msg (sender_id, conversation_id, client_msg_id),
|
||
KEY idx_conversation_id (conversation_id, id)
|
||
) COMMENT='消息正文。PK=MsgId；content 为序列化 BLOB，不存媒体二进制';
|
||
|
||
CREATE TABLE IF NOT EXISTS t_mailbox_events (
|
||
id BIGINT PRIMARY KEY COMMENT 'Snowflake 事件 id',
|
||
user_id BIGINT NOT NULL COMMENT '接收用户',
|
||
device_id BIGINT NOT NULL COMMENT '目标设备；0 表示该用户全部设备',
|
||
seq BIGINT NOT NULL COMMENT '该 user+device 上单调递增的邮箱序号',
|
||
event_type SMALLINT NOT NULL COMMENT '事件类型：撤回/踢人/成员变更/好友/漏推消息等，不含成功在线推送的新消息',
|
||
conversation_id BIGINT NULL COMMENT '相关会话，可空',
|
||
payload VARBINARY(4096) COMMENT '控制事件载荷（会话 id+消息 id 等），不含消息正文',
|
||
created_at DATETIME(3) NOT NULL COMMENT '写入时间',
|
||
UNIQUE KEY uk_user_device_seq (user_id, device_id, seq),
|
||
KEY idx_user_device (user_id, device_id, seq)
|
||
) COMMENT='失败邮箱与控制事件。成功在线推送的新消息不写此表';
|
||
|
||
CREATE TABLE IF NOT EXISTS t_device_sync_cursors (
|
||
user_id BIGINT NOT NULL COMMENT '用户 id',
|
||
device_id BIGINT NOT NULL COMMENT '设备 id',
|
||
mailbox_seq BIGINT NOT NULL DEFAULT 0 COMMENT '该设备已同步到的 t_mailbox_events.seq',
|
||
dialog_score BIGINT NOT NULL DEFAULT 0 COMMENT '会话列表 Redis ZSET 游标',
|
||
updated_at DATETIME(3) NOT NULL COMMENT '游标更新时间',
|
||
PRIMARY KEY (user_id, device_id)
|
||
) COMMENT='每设备同步游标。mailbox_seq 对邮箱，dialog_score 对会话列表';
|