04 — 云融（YunRong）· 数据库设计文档
前置文档：02-系统架构设计文档、03-通信协议详细设计文档
设计思路：00-设计思路文档
版本：v0.1
状态：已发布
最后更新：2025-01
1. 设计概述
1.1 双数据库架构
┌─────────────────────────────────────────────────────────┐
│ 客户端 │
│ │
│ ┌─────────────────────┐ ┌─────────────────────────┐ │
│ │ SQLite (本地) │ │ PostgreSQL (远程) │ │
│ │ │ │ │ │
│ │ • 消息全文 │◄──►│ • 全量消息归档 │ │
│ │ • 会话摘要 │ 同步 │ • 用户/组织架构 │ │
│ │ • 联系人缓存 │ │ • 任务/审批数据 │ │
│ │ • 文件传输记录 │ │ • 文件元数据 │ │
│ │ • 用户配置/草稿 │ │ • 多端同步状态 │ │
│ │ • 离线操作队列 │ │ │ │
│ └─────────────────────┘ └─────────────────────────┘ │
│ │
│ 读: 优先 SQLite (快速) 写: 本地+远端 或 本地优先 │
└─────────────────────────────────────────────────────────┘
1.2 角色分工
| 维度 | SQLite | PostgreSQL |
|---|
| 定位 | 本地一级缓存 + 离线数据库 | 服务端权威数据源 |
| 数据范围 | 当前用户相关的热/温数据 | 全量数据（多用户） |
| 访问延迟 | < 1ms (本地文件 IO) | 5–50ms (网络) |
| 写入模式 | 串行化单线程写入 | 并发写入（多客户端） |
| 生命周期 | 可随时删除重建（从远端同步恢复） | 持久化，定期备份 |
| 表结构 | 简化版（多余字段从远端按需拉取） | 完整版（含所有字段和约束） |
2. SQLite 本地数据库设计
2.1 配置参数
-- 打开数据库时执行的 pragma
PRAGMA journal_mode = WAL; -- Write-Ahead Logging: 支持并发读
PRAGMA synchronous = NORMAL; -- 平衡安全与性能（WAL 模式下安全）
PRAGMA foreign_keys = ON; -- 启用外键约束
PRAGMA cache_size = -64000; -- 64MB 缓存
PRAGMA temp_store = MEMORY; -- 临时表放内存
PRAGMA mmap_size = 268435456; -- 256MB 内存映射
PRAGMA page_size = 4096; -- 4KB 页（匹配 OS 页大小）
2.2 表结构
2.2.1 会话表 conversations
CREATE TABLE conversations (
id INTEGER PRIMARY KEY, -- 会话 ID (服务端生成)
type INTEGER NOT NULL DEFAULT 0, -- 0=私聊, 1=群聊
title TEXT NOT NULL, -- 会话名称 (私聊=对方昵称, 群聊=群名)
avatar_url TEXT, -- 头像 URL
last_msg_preview TEXT, -- 最后一条消息摘要
last_msg_time INTEGER NOT NULL DEFAULT 0, -- 最后消息时间 (Unix 毫秒)
last_msg_seq INTEGER NOT NULL DEFAULT 0, -- 最后消息的 seq
unread_count INTEGER NOT NULL DEFAULT 0, -- 未读计数
is_pinned INTEGER NOT NULL DEFAULT 0, -- 是否置顶
is_muted INTEGER NOT NULL DEFAULT 0, -- 是否免打扰
sort_order INTEGER NOT NULL DEFAULT 0, -- 自定义排序
local_updated INTEGER NOT NULL DEFAULT 0, -- 本地更新时间
synced INTEGER NOT NULL DEFAULT 1 -- 0=等待同步, 1=已同步
);
CREATE INDEX idx_conv_last_msg ON conversations(last_msg_time DESC);
CREATE INDEX idx_conv_pinned ON conversations(is_pinned, sort_order);
2.2.2 消息表 messages
CREATE TABLE messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
msg_id TEXT NOT NULL, -- 全局唯一消息 ID (UUID 或 Snowflake)
conv_id INTEGER NOT NULL, -- 所属会话 ID
sender_id INTEGER NOT NULL, -- 发送者 ID
sender_name TEXT NOT NULL, -- 发送者名称 (冗余，避免 JOIN)
content_type INTEGER NOT NULL DEFAULT 0, -- 0=文本, 1=图片, 2=文件, 3=系统
content_body TEXT NOT NULL, -- 消息体 JSON
quote_msg_id TEXT, -- 引用消息 ID
status INTEGER NOT NULL DEFAULT 0, -- 0=发送中, 1=已送达, 2=已读, 3=发送失败
seq INTEGER NOT NULL DEFAULT 0, -- 协议序列号
timestamp INTEGER NOT NULL, -- 消息时间 (Unix 毫秒)
is_mine INTEGER NOT NULL DEFAULT 0, -- 1=我发送的
synced INTEGER NOT NULL DEFAULT 0, -- 0=本地未同步, 1=已同步到远端
created_at INTEGER NOT NULL DEFAULT (strftime('%s','now') * 1000)
);
CREATE INDEX idx_msg_conv_ts ON messages(conv_id, timestamp DESC);
CREATE INDEX idx_msg_seq ON messages(seq);
CREATE INDEX idx_msg_synced ON messages(synced, timestamp);
CREATE UNIQUE INDEX idx_msg_msgid ON messages(msg_id);
-- content_body JSON 结构示例:
-- 文本: {"type":"text","text":"你好"}
-- 图片: {"type":"image","url":"...","thumbnail_id":"...","width":1920,"height":1080,"size":245760}
-- 文件: {"type":"file","name":"报告.pdf","url":"...","size":1048576,"hash":"sha256:..."}
-- 系统: {"type":"system","subtype":"group_create","actor_id":1001,"extra":{...}}
CREATE TABLE contacts (
id INTEGER PRIMARY KEY, -- 用户 ID
name TEXT NOT NULL, -- 显示名称
avatar_url TEXT, -- 头像 URL
department TEXT, -- 部门名称
title TEXT, -- 职位
email TEXT, -- 邮箱
phone TEXT, -- 电话
status INTEGER NOT NULL DEFAULT 0, -- 0=离线, 1=在线, 2=忙碌, 3=离开
is_favorite INTEGER NOT NULL DEFAULT 0, -- 是否星标联系人
pinyin TEXT, -- 姓名拼音 (搜索用)
local_updated INTEGER NOT NULL DEFAULT 0,
synced INTEGER NOT NULL DEFAULT 1
);
CREATE INDEX idx_contact_name ON contacts(name);
CREATE INDEX idx_contact_pinyin ON contacts(pinyin);
CREATE INDEX idx_contact_dept ON contacts(department);
2.2.4 组织架构缓存表 departments
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
parent_id INTEGER NOT NULL DEFAULT 0, -- 父部门 ID (0=根)
name TEXT NOT NULL,
sort_order INTEGER NOT NULL DEFAULT 0,
member_count INTEGER NOT NULL DEFAULT 0,
local_updated INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_dept_parent ON departments(parent_id);
2.2.5 文件传输任务表 file_transfers
CREATE TABLE file_transfers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
transfer_id TEXT NOT NULL UNIQUE, -- 传输任务 UUID
file_name TEXT NOT NULL,
file_path TEXT NOT NULL, -- 本地文件路径
file_size INTEGER NOT NULL, -- 字节
file_hash TEXT, -- SHA-256
direction INTEGER NOT NULL, -- 0=上传, 1=下载
conv_id INTEGER, -- 关联会话 (可为空)
chunk_size INTEGER NOT NULL DEFAULT 1048576, -- 1MB
total_chunks INTEGER NOT NULL,
completed_chunks INTEGER NOT NULL DEFAULT 0, -- 已完成块数
chunk_bitmap BLOB, -- 块完成位图 (二进制)
status INTEGER NOT NULL DEFAULT 0, -- 0=等待, 1=传输中, 2=暂停, 3=完成, 4=失败
error_msg TEXT, -- 失败原因
remote_url TEXT, -- 上传完成后的远端 URL
speed_bps INTEGER NOT NULL DEFAULT 0, -- 当前速度 (字节/秒)
started_at INTEGER, -- 开始时间
completed_at INTEGER, -- 完成时间
created_at INTEGER NOT NULL DEFAULT (strftime('%s','now') * 1000)
);
CREATE INDEX idx_ft_status ON file_transfers(status);
CREATE INDEX idx_ft_conv ON file_transfers(conv_id);
2.2.6 任务/通知表 tasks
CREATE TABLE tasks (
id INTEGER PRIMARY KEY, -- 任务 ID (服务端生成)
notify_id TEXT NOT NULL UNIQUE, -- 通知唯一 ID
task_type TEXT NOT NULL, -- "approval", "assignment", "announcement"
title TEXT NOT NULL,
body TEXT,
priority INTEGER NOT NULL DEFAULT 0, -- 0=低, 1=普通, 2=高
status INTEGER NOT NULL DEFAULT 0, -- 0=未读, 1=已读, 2=已处理, 3=已忽略
action_url TEXT, -- 操作跳转路径
from_user_id INTEGER,
from_user_name TEXT,
created_at INTEGER NOT NULL,
handled_at INTEGER,
synced INTEGER NOT NULL DEFAULT 1
);
CREATE INDEX idx_task_status ON tasks(status, priority DESC);
CREATE INDEX idx_task_time ON tasks(created_at DESC);
2.2.7 草稿表 drafts
CREATE TABLE drafts (
conv_id INTEGER PRIMARY KEY,
content_text TEXT NOT NULL DEFAULT '',
content_json TEXT, -- 富文本/引用等结构化草稿
updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now') * 1000)
);
2.2.8 配置表 config
CREATE TABLE config (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now') * 1000)
);
-- 示例配置项:
-- "ui.theme" → "light" | "dark" | "system"
-- "ui.font_size" → "14"
-- "ui.sidebar_width" → "280"
-- "notify.sound" → "true" | "false"
-- "notify.desktop" → "true" | "false"
-- "network.proxy" → "" | "socks5://127.0.0.1:1080"
-- "file.download_dir" → "/home/user/Downloads"
-- "file.upload_limit_kbps"→ "0" (0=不限速)
-- "sync.last_seq" → "2147483700"
-- "auth.refresh_token" → "encrypted_token_here"
2.2.9 离线操作队列表 offline_queue
CREATE TABLE offline_queue (
id INTEGER PRIMARY KEY AUTOINCREMENT,
operation TEXT NOT NULL, -- "send_msg", "mark_read", "set_status"
payload_json TEXT NOT NULL, -- 操作参数
created_at INTEGER NOT NULL,
retry_count INTEGER NOT NULL DEFAULT 0,
last_retry_at INTEGER,
status INTEGER NOT NULL DEFAULT 0 -- 0=等待, 1=处理中, 2=完成, 3=失败
);
CREATE INDEX idx_oq_status ON offline_queue(status, created_at);
2.2.10 全文搜索表 messages_fts
-- SQLite FTS5 全文搜索引擎
CREATE VIRTUAL TABLE messages_fts USING fts5(
content_text, -- 提取的纯文本 (去除 JSON 标记)
sender_name,
content='messages', -- 外部内容表 (无副本模式)
content_rowid='id'
);
-- 触发器：消息插入/更新/删除时自动同步 FTS 索引
CREATE TRIGGER msg_fts_insert AFTER INSERT ON messages BEGIN
INSERT INTO messages_fts(rowid, content_text, sender_name)
VALUES (new.id,
json_extract(new.content_body, '$.text'),
new.sender_name);
END;
CREATE TRIGGER msg_fts_delete AFTER DELETE ON messages BEGIN
INSERT INTO messages_fts(messages_fts, rowid, content_text, sender_name)
VALUES ('delete', old.id, '', '');
END;
2.3 数据生命周期管理
| 数据类型 | 本地保留策略 | 清理触发条件 |
|---|
| 消息 | 保留最近 30 天 | 超过 30 天 + SQLite 文件 > 500MB → 清理最旧的 |
| 文件传输记录 | 保留最近 100 条 | 超过 100 条 → 删除已完成且超过 7 天的 |
| 联系人缓存 | 保留全部 | 远端全量同步时覆盖 |
| 离线队列 | 保留最近 7 天 | 已完成/失败的 7 天后删除 |
| 草稿 | 保留全部 | 用户手动清除或消息发送成功后自动删除 |
| FTS 索引 | 与消息同步 | 消息删除时同步清理 |
-- 定期清理脚本 (客户端启动时执行)
DELETE FROM messages
WHERE timestamp < (strftime('%s','now') * 1000 - 30*24*3600*1000)
AND synced = 1;
DELETE FROM file_transfers
WHERE status IN (3, 4)
AND completed_at < (strftime('%s','now') * 1000 - 7*24*3600*1000);
DELETE FROM offline_queue
WHERE status IN (2, 3)
AND created_at < (strftime('%s','now') * 1000 - 7*24*3600*1000);
3. PostgreSQL 远程数据库设计
3.1 设计原则
- 服务端表是客户端的超集：客户端表的所有字段远程都有，外加审计、索引优化字段
- 多租户隔离：所有表通过
user_id 或 conv_id 分区
- 时序数据优化：
messages 表按时间分区（月）
- 不允许客户端直接 SQL 访问 PG：通过 REST API 间接访问，保证安全和接口稳定
3.2 核心表结构
3.2.1 用户表 users
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(64) NOT NULL UNIQUE,
password_hash VARCHAR(256) NOT NULL,
display_name VARCHAR(128) NOT NULL,
avatar_url VARCHAR(512),
email VARCHAR(256),
phone VARCHAR(32),
department_id BIGINT REFERENCES departments(id),
title VARCHAR(128),
status SMALLINT NOT NULL DEFAULT 0, -- 0=离线, 1=在线
last_active_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_dept ON users(department_id);
CREATE INDEX idx_users_status ON users(status, last_active_at DESC);
3.2.2 会话表 conversations
CREATE TABLE conversations (
id BIGSERIAL PRIMARY KEY,
type SMALLINT NOT NULL, -- 0=私聊, 1=群聊
title VARCHAR(256),
creator_id BIGINT REFERENCES users(id),
avatar_url VARCHAR(512),
settings_json JSONB DEFAULT '{}', -- 群设置 (仅群聊)
last_msg_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- 会话成员关联表
CREATE TABLE conversation_members (
conv_id BIGINT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role SMALLINT NOT NULL DEFAULT 0, -- 0=成员, 1=管理员, 2=群主
last_read_seq BIGINT NOT NULL DEFAULT 0, -- 该用户在此会话中已读的最大 server_seq（标记已读）
is_muted BOOLEAN NOT NULL DEFAULT FALSE,
is_pinned BOOLEAN NOT NULL DEFAULT FALSE,
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (conv_id, user_id)
);
3.2.3 消息表 messages
-- 全局消息序列号（跨所有会话）
CREATE SEQUENCE IF NOT EXISTS global_server_seq;
CREATE TABLE messages (
id BIGSERIAL PRIMARY KEY,
msg_id VARCHAR(64) NOT NULL UNIQUE, -- 全局唯一 ID
conv_id BIGINT NOT NULL,
sender_id BIGINT NOT NULL,
content_type SMALLINT NOT NULL,
content_body JSONB NOT NULL,
quote_msg_id VARCHAR(64),
client_seq BIGINT, -- 发送客户端的 seq（客户端分配）
server_seq BIGINT NOT NULL DEFAULT nextval('global_server_seq'), -- 全局递增序列号
timestamp TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- 核心索引（5000 人规模单表即可满足，v2 可评估按时间分区）
CREATE INDEX idx_msg_conv_ts ON messages(conv_id, timestamp DESC);
CREATE INDEX idx_msg_server_seq ON messages(server_seq);
-- 全文搜索 (PostgreSQL 内置)
ALTER TABLE messages ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('simple',
coalesce(content_body->>'text',''))) STORED;
CREATE INDEX idx_msg_search ON messages USING GIN(search_vector);
3.2.4 部门表 departments
CREATE TABLE departments (
id BIGSERIAL PRIMARY KEY,
parent_id BIGINT REFERENCES departments(id),
name VARCHAR(128) NOT NULL,
sort_order INTEGER NOT NULL DEFAULT 0,
path LTREE, -- 物化路径 (快速子树查询)
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_dept_path ON departments USING GIST(path);
3.2.5 任务表 tasks
CREATE TABLE tasks (
id BIGSERIAL PRIMARY KEY,
notify_id VARCHAR(64) NOT NULL UNIQUE,
task_type VARCHAR(32) NOT NULL,
title VARCHAR(256) NOT NULL,
body TEXT,
priority SMALLINT NOT NULL DEFAULT 1,
status SMALLINT NOT NULL DEFAULT 0,
from_user_id BIGINT REFERENCES users(id),
to_user_id BIGINT NOT NULL REFERENCES users(id),
action_url VARCHAR(512),
related_conv_id BIGINT REFERENCES conversations(id),
handled_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_task_user ON tasks(to_user_id, status, priority DESC);
CREATE INDEX idx_task_time ON tasks(created_at DESC);
3.2.6 文件元数据表 files
CREATE TABLE files (
id BIGSERIAL PRIMARY KEY,
file_hash VARCHAR(128) NOT NULL, -- SHA-256 hex
file_name VARCHAR(256) NOT NULL,
file_size BIGINT NOT NULL,
mime_type VARCHAR(64),
storage_path VARCHAR(512) NOT NULL, -- 存储后端路径
uploader_id BIGINT REFERENCES users(id),
chunk_size INTEGER NOT NULL DEFAULT 1048576,
total_chunks INTEGER NOT NULL,
is_complete BOOLEAN NOT NULL DEFAULT FALSE, -- 全部分块是否上传完
upload_started TIMESTAMPTZ,
upload_finished TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX idx_files_hash ON files(file_hash);
CREATE INDEX idx_files_uploader ON files(uploader_id, created_at DESC);
3.2.7 同步水位表 sync_watermarks
-- 记录每个用户在每个会话中的同步进度
CREATE TABLE sync_watermarks (
user_id BIGINT NOT NULL REFERENCES users(id),
conv_id BIGINT NOT NULL REFERENCES conversations(id),
last_server_seq BIGINT NOT NULL DEFAULT 0, -- 该用户在此会话中收到的最后 seq
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (user_id, conv_id)
);
4. 数据同步机制
4.1 同步策略矩阵
| 操作 | 在线 (网络正常) | 离线 (网络断开) |
|---|
| 发送消息 | 写本地 + 发 WebSocket → 收到 ACK → 标记 synced=1 | 写本地 + 入 offline_queue → synced=0 |
| 标记已读 | 写本地 + 发 msg_read 帧 | 写本地 + 入 offline_queue |
| 接收消息 | WebSocket 推送 → 写本地 → synced=1 | 不在线，不存在此操作 |
| 联系人更新 | 写本地 + 不立即同步 | 网络恢复后增量拉取 |
| 配置修改 | 写本地 | 网络恢复后上传 |
4.2 增量同步算法（上线时触发）
Client (上线) Server
│ │
│ 1. 读取本地 sync.watermark │
│ = { last_server_seq, last_ts } │
│ │
│ 2. WS auth 成功后 │
│── sync(last_seq, last_ts, limit) ─►│
│ │
│ │ 3. 查询 server_seq > last_seq
│ │ AND ts > last_ts
│ │ 按 server_seq ASC, LIMIT limit
│ │
│◄── sync_data(messages, notify, │
│ contacts_delta, │
│ end_seq, has_more) ──│
│ │
│ 4. 合并到本地 SQLite │
│ - messages: INSERT OR IGNORE │
│ (ON CONFLICT msg_id 去重) │
│ - contacts: UPSERT │
│ - 更新 sync.watermark │
│ │
│ 5. if has_more → goto 2 │
│ (使用新的 last_seq 继续拉取) │
│ │
│ 6. 重放 offline_queue 中未同步操作 │
│ (按 created_at ASC 顺序执行) │
│ │
│ 7. 同步完成 │
4.3 冲突处理
| 冲突类型 | 策略 | 说明 |
|---|
| 消息 ID 碰撞 | INSERT OR IGNORE (SQLite) + ON CONFLICT DO NOTHING (PG) | msg_id 全局唯一，相同 ID 一定是同一条消息 |
| 联系人信息不一致 | 服务端覆盖 (Last-Write-Wins by server timestamp) | 客户端缓存的联系人信息以服务端为准 |
| 配置冲突 | 服务端覆盖，本地仅在离线时修改 | 配置的权威源是服务端 |
| 草稿冲突 | 不解决——只有本地有草稿，不上传 | 草稿是纯本地概念 |
4.4 离线队列重放
// 伪代码
void SyncManager::replayOfflineQueue() {
auto ops = db_->getPendingOfflineOps(); // ORDER BY created_at ASC
for (auto& op : ops) {
if (op.operation == "send_msg") {
auto msg = parseJson(op.payload_json);
imService_->sendMessage(msg, [&](bool ok) {
if (ok) {
db_->markOfflineOpDone(op.id);
db_->markMessageSynced(msg.msg_id);
} else {
op.retry_count++;
if (op.retry_count >= 3) {
db_->markOfflineOpFailed(op.id);
db_->markMessageFailed(msg.msg_id);
}
}
});
}
// ... 其他操作同理
}
}
5. 查询模式与优化
5.1 高频查询
| # | 查询 | SQL (SQLite) | 优化策略 |
|---|
| 1 | 会话列表 (按最后消息排序) | SELECT * FROM conversations ORDER BY is_pinned DESC, last_msg_time DESC LIMIT 50 | idx_conv_last_msg 覆盖 |
| 2 | 消息历史 (分页) | SELECT * FROM messages WHERE conv_id=? ORDER BY timestamp DESC LIMIT 50 OFFSET ? | idx_msg_conv_ts 覆盖 |
| 3 | 消息搜索 | SELECT m.* FROM messages m JOIN messages_fts f ON m.id = f.rowid WHERE messages_fts MATCH ? ORDER BY m.timestamp DESC LIMIT 20 | FTS5 全文索引 |
| 4 | 未读计数 | SELECT SUM(unread_count) FROM conversations | 冗余字段，无需 COUNT JOIN |
| 5 | 联系人搜索 | SELECT * FROM contacts WHERE name LIKE ? OR pinyin LIKE ? LIMIT 20 | idx_contact_pinyin |
| 6 | 待处理任务 | SELECT * FROM tasks WHERE status=0 ORDER BY priority DESC, created_at DESC | idx_task_status |
5.2 消息分页策略
不使用传统 OFFSET（大偏移时性能差），而是用游标分页（Keyset Pagination）：
-- 第一页 (最新 50 条)
SELECT * FROM messages
WHERE conv_id = ? AND timestamp < ?
ORDER BY timestamp DESC LIMIT 50;
-- 上一页 (使用上一页最后一条的 timestamp 作为游标)
SELECT * FROM messages
WHERE conv_id = ? AND timestamp < :cursor_ts
ORDER BY timestamp DESC LIMIT 50;
5.3 批量插入优化
消息同步时可能一次收到 200 条消息：
-- 使用事务包裹批量插入
BEGIN TRANSACTION;
INSERT OR IGNORE INTO messages (...) VALUES (...), (...), ...;
UPDATE conversations SET last_msg_time=?, last_msg_preview=? WHERE id=?;
COMMIT;
配合 PRAGMA synchronous = NORMAL，批量插入 200 条通常 < 50ms。
6. 数据库迁移方案
6.1 版本管理
-- 在 SQLite 中维护 schema 版本
CREATE TABLE IF NOT EXISTS schema_version (
version INTEGER PRIMARY KEY,
applied_at INTEGER NOT NULL
);
6.2 迁移流程
// 应用启动时
void DatabaseManager::migrateIfNeeded() {
int currentVersion = getSchemaVersion(); // 查询 schema_version
int targetVersion = APP_DB_VERSION; // 编译期常量
while (currentVersion < targetVersion) {
currentVersion++;
applyMigration(currentVersion); // 执行 v{N}.sql
setSchemaVersion(currentVersion);
}
}
6.3 迁移脚本规范
migrations/
├── v1_initial.sql -- 初始表结构
├── v2_add_pinyin.sql -- contacts 增加 pinyin 字段
├── v3_add_fts.sql -- 创建 FTS5 索引
└── v4_add_read_status.sql -- messages 增加 status 字段
每份迁移脚本必须是幂等的（可重复执行不报错）：
-- v2_add_pinyin.sql
ALTER TABLE contacts ADD COLUMN pinyin TEXT DEFAULT '';
UPDATE contacts SET pinyin = '' WHERE pinyin IS NULL;
6.4 PostgreSQL 迁移
使用 Flyway 或手写迁移管理，CI/CD 自动执行：
-- V1__initial_schema.sql
-- V2__add_search_vector.sql
-- V3__add_messages_partition.sql
7. 数据安全
7.1 SQLite 加密
| 方案 | 说明 |
|---|
| SQLCipher | SQLite 的加密扩展，AES-256-CBC，透明加解密 |
| 应用层加密 | 敏感字段（Token、私钥）在写入前手动 AES 加密，读取后解密 |
建议：全库使用 SQLCipher（简单），或至少加密 config 表中的 auth.refresh_token。
// SQLCipher 密钥设置
sqlite3_key(db, key.data(), key.size());
// 每次打开数据库后、执行任何 SQL 之前调用
7.2 PostgreSQL 访问控制
| 级别 | 措施 |
|---|
| 网络层 | 仅允许应用服务器 IP 访问 PG 端口，客户端不直连 PG |
| 应用层 | 所有客户端请求经 REST API → 应用服务器 → PG 连接池 |
| 行级安全 | ALTER TABLE messages ENABLE ROW LEVEL SECURITY + Policy: 用户只能读自己参与会话的消息 |
| 敏感字段 | password_hash 使用 bcrypt ($2b$ 前缀) |
8. ER 图
┌──────────┐ ┌─────────────────┐ ┌──────────┐
│ users │ │conversation_ │ │conversa- │
│ │◄─────│ members │─────►│tions │
│ id (PK) │ N:M │ user_id (FK) │ │ id (PK) │
│ name │ │ conv_id (FK) │ │ type │
│ dept_id │ │ role │ │ title │
└────┬─────┘ │ unread_seq │ └────┬─────┘
│ └─────────────────┘ │
│ │
│ 1:N │ 1:N
▼ ▼
┌──────────┐ ┌──────────┐ ┌──────────┐
│ messages │ │ tasks │ │ files │
│ msg_id │ │ id (PK) │ │ id (PK) │
│ conv_id │ │ from_uid │ │ hash │
│ sender_id│ │ to_uid │ │ name │
│ content │ │ type │ │ size │
│ seq │ │ status │ │ uploader │
└──────────┘ └──────────┘ └──────────┘
┌──────────────┐ ┌──────────────┐
│ departments │ │ config │
│ id (PK) │ │ key (PK) │
│ parent_id │ │ value │
│ name │ └──────────────┘
│ path │
└──────────────┘ 仅客户端本地:
┌──────────────┐
│ drafts │
│ conv_id (PK) │
│ content │
└──────────────┘
┌──────────────┐
│ offline_queue│
│ id (PK) │
│ operation │
│ payload_json │
└──────────────┘
附录 A — 索引策略总结
| 表 | 索引 | 类型 | 用途 |
|---|
| conversations | idx_conv_last_msg | B-Tree DESC | 会话列表排序 |
| messages | idx_msg_conv_ts | 复合 B-Tree | 消息历史分页 |
| messages | idx_msg_msgid | UNIQUE | 去重 / 幂等插入 |
| messages | idx_msg_seq | B-Tree | 按 seq 查找 |
| messages_fts | (FTS5 内部) | 倒排索引 | 全文搜索 |
| contacts | idx_contact_pinyin | B-Tree | 拼音搜索 |
| tasks | idx_task_status | 复合 B-Tree | 待处理任务列表 |
| offline_queue | idx_oq_status | 复合 B-Tree | 离线队列重放 |
附录 B — 文档修订记录
| 版本 | 日期 | 作者 | 变更说明 |
|---|
| v0.1 | 2025-01 | — | 初稿：SQLite 10 表 + PostgreSQL 7 表 + 同步机制 + 迁移方案 |