一本账 · 数据库设计

1. 全局约定

• 主键：所有表用 id BIGINT GENERATED ALWAYS AS IDENTITY（App 端本地库用 UUID client_txn_id 关联，见 03-architecture.md 3.3）
• 金额：一律 BIGINT，单位分（如 12.34 元存 1234）；禁止 FLOAT/NUMERIC 小数运算后直存
• 软删除：deleted_at TIMESTAMPTZ NULL，非空即已删除；账目软删除保留 30 天后由定时任务物理清理（PRD：恢复功能 P2，但数据先留）
• 时间：全部 TIMESTAMPTZ（UTC 存储，客户端本地化展示）
• 命名：表名复数 snake_case；索引 idx_{表}_{列}；唯一约束 uk_{表}_{列}

2. ER 总览

1
users ──< ledger_members >── ledgers ──< transactions >── categories
2
                                   │        │
3
                                   ├──< budgets        └── (payer_id → users)
4
                                   └──< invite_codes(Redis，非表)
5
                                       (payee_ids → users，JSON 数组)

核心关系：

• ledgers : users = M : N（通过 ledger_members，含角色）
• transactions.payee_ids 记录「为谁花的」（多选，JSON 数组存 user_id，查询用 GIN 索引）

3. 表结构明细

3.1 users — 用户

索引：uk_users_phone；注销时 phone 改写为 deleted_{id}_{phone}（释放手机号；列宽 64 容纳改写值）。注销不物理删除用户行：status=0，30 天冷静期满后清除昵称/头像等敏感数据，行保留为「前成员」占位，供 transactions.payer_id / created_by / payee_ids 引用与展示。

3.2 ledgers — 账本

3.3 ledger_members — 账本成员（权限核心表）

约束与索引：

• uk_ledger_members_active (ledger_id, user_id) WHERE left_at IS NULL —— 部分唯一索引：一个用户在同一账本最多一条有效成员记录（历史记录可多条）
• idx_ledger_members_user (user_id) WHERE left_at IS NULL —— 查「我加入的账本列表」
• 鉴权规则（后端 Guard 统一实现）：left_at IS NULL 才视为成员；被移除成员的账目保留（见 3.4 created_by）

3.4 transactions — 账目（最核心表）

索引：

• uk_transactions_client_txn (ledger_id, client_txn_id) —— 同步幂等（作用域限定账本内）
• idx_transactions_ledger_date (ledger_id, txn_date DESC) WHERE deleted_at IS NULL —— 流水列表主查询
• idx_transactions_ledger_updated (ledger_id, updated_at DESC) WHERE deleted_at IS NULL —— 增量同步拉取
• GIN idx_transactions_payee (payee_ids) —— 按「为谁花」筛选

3.5 categories — 分类

约束（PostgreSQL 的 UNIQUE 对 NULL 不判重，必须拆为部分唯一索引）：

• uk_categories_custom_name (ledger_id, name, type) WHERE ledger_id IS NOT NULL —— 账本自定义分类唯一
• uk_categories_system_name (name, type) WHERE ledger_id IS NULL —— 系统内置分类唯一

内置分类种子数据（seed 脚本初始化，参考市场成熟体系）：

• 支出：餐饮（三餐/零食/外卖）、交通（公交/打车/加油）、购物（日用品/服饰/数码）、居住（房贷房租/水电物业/装修）、娱乐（视频会员/游戏/旅行）、医疗、教育、人情（红包/礼物）、通讯、宠物、其他
• 收入：工资、奖金、兼职、理财、红包、报销、其他
• 转账：转账（系统固定）

3.6 budgets — 预算

约束：uk_budgets_ledger_period (ledger_id, period) —— 每账本每月一条总预算（MVP 无分类预算）。

4. 同步与版本号设计

• ledgers.version：该账本数据版本号，任何账目/成员/预算变更时 version += 1（单行 UPDATE，天然串行化）
• 客户端本地记录 last_synced_version；GET /sync/pull?since=<version> 返回所有 变更记录 version > since 的实体
• WebSocket ledger_changed 事件携带最新 version，客户端比对后决定是否拉取
• 变更日志表 ledger_changes（服务端内部表，不开放 API）：

设计说明：MVP 用「变更日志 + 实体当前态」双轨（拉取时按 entity_type 回查当前态返回），避免维护完整事件溯源的复杂度。该表按月分区，保留 6 个月。

兜底：客户端 since_version 落后超过变更保留期（6 个月）或服务端检测到版本缺口时，pull 返回 full_resync 信号，客户端改走全量快照接口重建本地库（见 05-api-design.md §9）。

5. 关键查询示例（性能预演）

1
-- 1) 账本流水首页（分页 50 条，日分组）
2
SELECT t.*, u.nickname FROM transactions t
3
JOIN ledger_members lm ON lm.ledger_id = t.ledger_id AND lm.user_id = $1 AND lm.left_at IS NULL
4
JOIN users u ON u.id = t.payer_id
5
WHERE t.ledger_id = $2 AND t.deleted_at IS NULL
6
ORDER BY t.txn_date DESC, t.id DESC LIMIT 50 OFFSET $3;
7
-- 命中 idx_transactions_ledger_date；join 成员表是防越权的 SQL 层兜底
8

9
-- 2) 月度分类聚合（统计页）
10
SELECT c.id, c.name, SUM(t.amount) AS total
11
FROM transactions t JOIN categories c ON c.id = t.category_id
12
WHERE t.ledger_id = $1 AND t.type = 1 AND t.deleted_at IS NULL
13
  AND t.txn_date BETWEEN $2 AND $3
14
GROUP BY c.id ORDER BY total DESC;
15
-- 账目 < 10 万级别单账本，走 ledger_date 索引聚合即可；Redis 缓存 60s
16

17
-- 3) 增量同步
18
SELECT * FROM ledger_changes WHERE ledger_id = $1 AND version > $2 ORDER BY version;
19
-- 命中 uk(ledger_id, version)

6. 数据量与容量预估

结论：MVP 阶段单实例 PostgreSQL（4C8G）绰绰有余，无需分库分表。

7. 客户端本地库（App 端 SQLite）

与云端表对齐，但增加同步专用列：

本地表：transactions（同 3.4 附加同步列）、ledgers、categories（缓存快照）、members（缓存）。离线创建的记录以 client_txn_id 作为本地主键，同步成功后补写 server_id 建立映射。原则：本地库是显示数据源，云端是事实源；冲突时以服务器 updated_at 较新者为准（LWW，仅在线编辑场景）。

8. 迁移与种子

• 迁移工具：TypeORM Migration（或 Knex），迁移文件入 Git，禁止手工改库
• 种子数据：系统内置分类（3.5）、演示账本（注册引导可选）
• 环境：dev/staging/prod 三套独立库；prod 迁移必须先在 staging 演练