← 返回导航

数据库设计

PostgreSQL · 15.17

版本：v0.2（2026-08-16，评审后同步：PostgreSQL 方言 + 12 张表全量）
技术栈：**PostgreSQL 15.17**（PGDG），库名 `timesake`，用户 timesake
**可执行权威定义：`server/src/main/resources/db/init.sql`**（本文档为设计说明；建表/迁移一律以 init.sql 为准）
⚠️ 已知技术债（2026-08-16 数据库专项审查，用户拍板暂缓修复，见 `review/db-review-20260816.md`）：
wish_* 四表存在重复外键（内联 REFERENCES 与显式 CONSTRAINT 双写）、表 owner 混乱（postgres/timesake 各半）、
invite/share/todo 部分外键缺失、updated_at 无自动维护、sms_code 只增不删。

总览（12 张表）

#	表	说明	级联
1	user	用户（phone 可空，openid 唯一可空）	—
2	project	时光项目（status 0=一念 1=进行中 2=已珍藏；milestones JSONB）	—
3	project_member	项目成员（uk_project_user 唯一）	删项目级联
4	project_record	记录条目（content/image_url 至少一项）	删项目级联
5	invite	邀请 token	删项目级联
6	sms_code	验证码（只增不删，待清理机制）	—
7	todo	待办（筹备期，勾选留痕 done_by/done_at）	删项目级联
8	share	只读分享（珍藏后生成）	删项目级联
9	wish_participant	一念参与档案（seen/vote/joined 三维留痕）	删项目级联
10	wish_suggestion	一念建议（1 时间 / 2 地点 / 3 想法，adopted 采纳标记）	删项目级联
11	wish_suggestion_support	建议附议（uk_wss 唯一，一人一条）	删建议级联
12	wish_suggestion_comment	建议留言（parent_id 两层：留言+回复）	删建议级联

关系：project 为主根，删除项目时 member/record/invite/todo/share/wish_* 全部级联清理； user 被删（账号合并）时其参与数据由应用层 mergeUser 先转移再删除（见 skill timesake-backend-ops）。

表结构（与 init.sql 一致）

1. user 用户

• id BIGSERIAL PK；phone VARCHAR(20) 可空（微信 openid 注册账号绑定手机号前为空）；openid VARCHAR(64) 唯一可空（微信小程序登录）
• nickname / avatar 默认空串；status SMALLINT 1正常 0禁用（禁用后旧 token 失效，2026-08-16 拦截器查库校验）
• 唯一：uk_user_phone(phone)、uk_user_openid(openid)

2. project 时光项目

• type SMALLINT 1=期待（倒计时）2=坚持（周期计时）；status SMALLINT 0=一念 1=进行中 2=已珍藏
• target_time 期待目标（一念为默认倾向日期）；start_time 坚持起始；sealed_at 转珍藏/封存时刻（坚持天数冻结于此）
• repeat_rule 0=不重复 1=每天 2=每周 3=每月 4=每年（死字段：仅存不触发，产品拍板待定）
• pinned_at 手动置顶时刻；bg_id 预设背景；milestones JSONB [{name, days}] ≤5 个
• wish_note 一念一句话描述（≤200）；location 敲定后的地点（可来自采纳的地点建议）
• 外键：fk_project_creator → user(id)

3. project_member 成员

• role 1=管理员（创建者）2=成员；唯一 uk_project_user(project_id, user_id)
• 外键：fk_pm_project → project(id) ON DELETE CASCADE；fk_pm_user → user(id)

4. project_record 记录

• content VARCHAR(500) 可空、image_url TEXT 可空（至少一项，双空应用层报 4001）
• value_json JSONB 二期预留（结构化数值，生长曲线）
• record_date DATE 默认当天；索引 idx_pr_project_date(project_id, record_date)

5. invite 邀请

• token VARCHAR(64) 唯一（UUID）；expires_at 7 天有效；created_by 无外键（技术债）
• max_uses 恒 0 不校验（死字段）；use_count 已用次数

6. sms_code 验证码

• phone + code + scene(login) + expires_at + used；60s 限频按 created_at 判断
• 生产 aliyun 模式 code 字段仅占位（核验走阿里云）；无清理机制（技术债，64 条全过期待清）

7. todo 待办

• content VARCHAR(100)；done + done_at + done_by（勾选留痕）；created_by；sort 排序
• 外键：fk_todo_project → project(id) CASCADE；fk_todo_creator → user(id)；done_by 无外键（技术债，已有 2 条孤儿）

8. share 分享

• token VARCHAR(32) 唯一（UUID 去横线）；revoked 作废标记；expires_at NULL=永久
• 外键：fk_share_project → project(id) CASCADE；created_by 无外键（技术债）

9. wish_participant 一念参与档案（迭代 A，2026-08-14）

• seen 0/1 是否打开过（「看见」留痕）；vote 0/1走起/2改天/3不了（可反复切换）；joined 敲定时 1=加入
• seen_at / voted_at 时间锚点；唯一 uk_wp_project_user(project_id, user_id)
• 外键：fk_wp_project → project(id) CASCADE、fk_wp_user → user(id)（另有一组同名系统命名外键重复，技术债）

10. wish_suggestion 一念建议

• type 1=时间建议（填 target_time）/ 2=地点建议 / 3=其他想法 / 4=交通方式 / 5=集合地点 / 6=预算 / 7=分工（4-7 填 content，迭代 B 新增）
• content VARCHAR(200)；adopted 敲定时被发起者采纳 =1（同一类型至多采纳一条）

11. wish_suggestion_support 附议

• 唯一 uk_wss(suggestion_id, user_id)；supportCount = 共识度；toggle 支持取消

12. wish_suggestion_comment 留言（两层）

• parent_id NULL=留言层，非 NULL=回复该留言（仅允许回复留言层，三层由应用层拒绝；无自引用外键，技术债）
• 删除留言级联删其回复（应用层）；索引 idx_wsc_suggestion(suggestion_id, created_at)

设计要点

• 一念五件套闭环：project(status=0) + wish_participant + wish_suggestion + wish_suggestion_support + wish_suggestion_comment——邀约→表态→建议→附议→敲定转正→封存，全程留痕，封存后成为「这一念的旅程」回忆资料（只增不删）
• 级联删除：project 为主根，六类子表 ON DELETE CASCADE；user 删除走应用层 mergeUser（先转移资产再删，避免孤儿）
• 唯一约束：phone / openid / (project,user) / invite token / share token / (project,user) 参与档案 / (suggestion,user) 附议——并发写不重
• 复合索引前缀：idx_pr_project_date(project_id, record_date)、idx_wsc_suggestion(suggestion_id, created_at) 直接服务查询模式
• JSONB：milestones（自定义里程碑）、value_json（预留）——结构化数据查询/演进能力

备注

• 项目删除为物理删除（连带 member/record/invite/todo/share/wish_*），删除时顺带清理存储图片文件（2026-08-16 起）
• updated_at 有字段无维护（无触发器无 MetaObjectHandler）——「最近修改排序」等需求落地时需补
• 建表/加字段一律：改 init.sql（新库）+ 文件末尾追加幂等 ALTER TABLE ... ADD COLUMN IF NOT EXISTS（老库）+ 线上手动跑 DDL（owner=postgres 的表用 sudo -u postgres psql）