cheney
9b4705870d
- 设计文档.md: 架构与分层、目录结构、周期口径与边界规则、前端组件与交互、 后端接口清单、关键流程时序、非功能设计、测试验证、风险与演进 - 数据库设计文档.md: ER、5 张表逐字段、索引与外键级联、关键设计决策 (date 用 VARCHAR(10) 的原因 / 不冗余计数 / seq 幂等 / 归档语义 / 模板 JSON)、 容量估算、典型 SQL、迁移与回滚、V0.3 预留 - 界面设计.html: 420x820 静态高保真原型 8 屏 (打卡/日历/清单/新建弹窗/ 完成特效/明天计划视图/清单完成/空态) + 设计规范、组件状态表、动效与适配约定
326 lines
16 KiB
Markdown
326 lines
16 KiB
Markdown
# iBoard V0.2 数据库设计文档
|
||
|
||
| 项 | 内容 |
|
||
| --- | --- |
|
||
| 版本 | V0.2 |
|
||
| 文档状态 | 已实现（与代码一致） |
|
||
| 数据库 | MariaDB / MySQL 8（Prisma `provider = "mysql"`） |
|
||
| 字符集 | `utf8mb4 / utf8mb4_unicode_ci` |
|
||
| 时区约定 | 应用层统一 `Asia/Shanghai`，库内 `DATETIME` 存 UTC |
|
||
| 相关文件 | `prisma/schema.prisma`、`prisma/migrations/20261022120000_add_checkin_checklist/migration.sql` |
|
||
| 关联文档 | `需求文档.md`、`设计文档.md`、`界面设计.html` |
|
||
|
||
---
|
||
|
||
## 1. 概述
|
||
|
||
V0.2 新增「打卡 / 日历 / 清单」三个功能域，共引入 **5 张表**：
|
||
|
||
| 表 | 作用 | 归属域 |
|
||
| --- | --- | --- |
|
||
| `Habit` | 打卡项（每天/每周/每月/每年 n 次的目标定义） | 打卡 |
|
||
| `CheckIn` | 打卡记录（一次打卡 = 一条） | 打卡 |
|
||
| `CheckList` | 检查清单（出差/回老家等场景的一次性清单） | 清单 |
|
||
| `CheckListItem` | 检查项（勾选即完成） | 清单 |
|
||
| `CheckListTemplate` | 清单模板（系统预置 + 用户自定义） | 清单 |
|
||
|
||
日历视图**不新增表**：完全由 `Habit` + `CheckIn` 按月聚合得出（见 §7.3）。
|
||
|
||
### 1.1 设计原则
|
||
|
||
1. **记录为真，进度不落库。** 环形进度、完成率、streak 一律由 `CheckIn` 现场聚合，不在 `Habit` 上冗余 `doneCount` 字段——避免并发计数不一致、以及用户中途把「每周 3 次」改成「每周 5 次」后需要回填历史的问题。
|
||
2. **日期以本地日历日字符串存储。** `CheckIn.date` 用 `VARCHAR(10)` 存 `"YYYY-MM-DD"`（Asia/Shanghai 下的日历日），而不是 `DATETIME`：范围查询走索引、字典序等于时间序、天然幂等边界，且彻底规避「UTC 存 + 本地读」导致的跨零点归属错误。
|
||
3. **软删除优先。** 打卡项删除默认走 `archived` 归档（保留历史统计），只有显式 `?hard=1` 才物理删除。
|
||
4. **一切按 `userId` 隔离。** 所有业务表都带 `userId`，`Habit`/`CheckIn`/`CheckList`/`CheckListTemplate` 外键 `ON DELETE CASCADE` 到 `User`。
|
||
5. **迁移只增不改。** 新增需求一律新迁移文件，不修改已执行的迁移（部署走 `prisma migrate deploy`）。
|
||
|
||
---
|
||
|
||
## 2. 概念模型（ER）
|
||
|
||
```
|
||
┌──────────┐
|
||
│ User │ (既有表, 本次仅新增反向关系)
|
||
└────┬─────┘
|
||
┌───────────────┼────────────────────┬─────────────────┐
|
||
│ │ │ │
|
||
┌────▼────┐ ┌────▼────┐ ┌─────▼─────┐ ┌──────▼──────┐
|
||
│ Habit │1───*│ CheckIn │ │ CheckList │1─* │CheckListItem│
|
||
└─────────┘ └─────────┘ └───────────┘ └─────────────┘
|
||
打卡项 打卡记录 场景清单 检查项
|
||
(周期+目标n) (habitId,date,seq) (done, doneAt)
|
||
▲
|
||
│ 生成(复制条目, 不建外键)
|
||
┌─────┴──────────┐
|
||
│CheckListTemplate│
|
||
│ (builtin/自定义) │
|
||
└────────────────┘
|
||
```
|
||
|
||
基数说明：
|
||
|
||
| 关系 | 基数 | 级联 |
|
||
| --- | --- | --- |
|
||
| User → Habit | 1 : N | Cascade |
|
||
| User → CheckIn | 1 : N | Cascade |
|
||
| Habit → CheckIn | 1 : N | Cascade |
|
||
| User → CheckList | 1 : N | Cascade |
|
||
| CheckList → CheckListItem | 1 : N | Cascade |
|
||
| User → CheckListTemplate | 1 : N（可空，系统预置为空） | Cascade |
|
||
| CheckListTemplate → CheckList | 无外键，创建时**复制**条目内容 | — |
|
||
|
||
---
|
||
|
||
## 3. 表结构
|
||
|
||
### 3.1 `Habit` — 打卡项
|
||
|
||
周期目标的**定义**表，不含任何计数。
|
||
|
||
| 字段 | 类型 | 空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| `id` | INT PK | 否 | AUTO_INCREMENT | 主键 |
|
||
| `userId` | INT FK | 否 | — | 所属用户 |
|
||
| `name` | VARCHAR(191) | 否 | — | 名称，接口校验 1~20 字 |
|
||
| `period` | VARCHAR(191) | 否 | `'day'` | `day` \| `week` \| `month` \| `year` |
|
||
| `target` | INT | 否 | `1` | 周期内目标次数 n（校验 ≥1） |
|
||
| `color` | VARCHAR(191) | 否 | `'#1677ff'` | 环形与标记主色（7 色预设之一） |
|
||
| `icon` | VARCHAR(191) | 否 | `''` | emoji / 图标名，**V0.2 预留不展示** |
|
||
| `note` | VARCHAR(191) | 否 | `''` | 备注 |
|
||
| `archived` | BOOLEAN | 否 | `false` | 归档后不出现在待办，历史保留 |
|
||
| `sortOrder` | INT | 否 | `0` | 排序权重（升序） |
|
||
| `createdAt` | DATETIME(3) | 否 | now() | 用于「创建之前的日期不计入月历期望」 |
|
||
| `updatedAt` | DATETIME(3) | 否 | 自动 | — |
|
||
|
||
索引：`INDEX(userId, archived, sortOrder)` — 覆盖「取当前用户未归档打卡项」主查询。
|
||
|
||
### 3.2 `CheckIn` — 打卡记录
|
||
|
||
**一次打卡 = 一条记录**，同日多次用 `seq` 递增。
|
||
|
||
| 字段 | 类型 | 空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| `id` | INT PK | 否 | AUTO_INCREMENT | 主键 |
|
||
| `habitId` | INT FK | 否 | — | 所属打卡项 |
|
||
| `userId` | INT FK | 否 | — | 冗余用户（用于单用户全量区间查询，避免 join） |
|
||
| `date` | VARCHAR(10) | 否 | — | 本地日历日 `"YYYY-MM-DD"` |
|
||
| `seq` | INT | 否 | `1` | 当日第几次（1..n，超额继续累加） |
|
||
| `note` | VARCHAR(191) | 否 | `''` | 单次备注（接口截断 200 字） |
|
||
| `source` | VARCHAR(191) | 否 | `'user'` | `user` \| `backfill`（补打） |
|
||
| `createdAt` | DATETIME(3) | 否 | now() | 打卡时刻（UTC） |
|
||
|
||
约束与索引：
|
||
|
||
| 名称 | 类型 | 字段 | 作用 |
|
||
| --- | --- | --- | --- |
|
||
| `CheckIn_habitId_date_seq_key` | **UNIQUE** | `(habitId, date, seq)` | 并发重复提交的幂等防线（冲突 → P2002 → 409） |
|
||
| `CheckIn_userId_date_idx` | INDEX | `(userId, date)` | 月历按月区间扫全量记录 |
|
||
| `CheckIn_habitId_date_idx` | INDEX | `(habitId, date)` | 单打卡项周期区间聚合 |
|
||
|
||
### 3.3 `CheckList` — 检查清单
|
||
|
||
| 字段 | 类型 | 空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| `id` | INT PK | 否 | AUTO_INCREMENT | 主键 |
|
||
| `userId` | INT FK | 否 | — | 所属用户 |
|
||
| `title` | VARCHAR(191) | 否 | — | 清单名，如「出差 · 杭州」 |
|
||
| `scene` | VARCHAR(191) | 否 | `''` | 场景：`出差` \| `回老家` \| `其他` |
|
||
| `note` | VARCHAR(191) | 否 | `''` | 备注 |
|
||
| `archived` | BOOLEAN | 否 | `false` | 归档 |
|
||
| `createdAt` / `updatedAt` | DATETIME(3) | 否 | — | 列表默认按 `updatedAt` 倒序 |
|
||
|
||
索引：`INDEX(userId, archived)` — 对应「进行中 / 已归档」两个分段列表。
|
||
|
||
### 3.4 `CheckListItem` — 检查项
|
||
|
||
| 字段 | 类型 | 空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| `id` | INT PK | 否 | AUTO_INCREMENT | 主键 |
|
||
| `listId` | INT FK | 否 | — | 所属清单 |
|
||
| `title` | VARCHAR(191) | 否 | — | 条目名（校验 ≤40 字） |
|
||
| `note` | VARCHAR(191) | 否 | `''` | 条目备注 |
|
||
| `done` | BOOLEAN | 否 | `false` | 是否已完成 |
|
||
| `doneAt` | DATETIME(3) | **是** | NULL | 勾选时刻；取消勾选置回 NULL |
|
||
| `sortOrder` | INT | 否 | `0` | 排序（升序，同值按 id） |
|
||
| `createdAt` / `updatedAt` | DATETIME(3) | 否 | — | — |
|
||
|
||
索引：`INDEX(listId, sortOrder)`。
|
||
|
||
### 3.5 `CheckListTemplate` — 清单模板
|
||
|
||
| 字段 | 类型 | 空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| `id` | INT PK | 否 | AUTO_INCREMENT | 主键 |
|
||
| `userId` | INT FK | **是** | NULL | NULL = 系统预置（`builtin=true`） |
|
||
| `name` | VARCHAR(191) | 否 | — | 模板名 |
|
||
| `scene` | VARCHAR(191) | 否 | `''` | 场景标签 |
|
||
| `items` | **JSON** | 否 | — | `[{ title, note }]`，创建清单时展开为 `CheckListItem` |
|
||
| `builtin` | BOOLEAN | 否 | `false` | 系统预置：只读、不可删（接口层限制） |
|
||
| `createdAt` / `updatedAt` | DATETIME(3) | 否 | — | — |
|
||
|
||
索引：`INDEX(userId, builtin)` — 查询条件为 `builtin=true OR userId=me`。
|
||
|
||
系统预置数据（首次访问 `GET /api/checklists/templates` 时**幂等写入**）：
|
||
|
||
| 模板 | 条目数 | 条目 |
|
||
| --- | --- | --- |
|
||
| 出差 | 11 | 身份证/证件、手机充电器+充电宝、笔记本电脑+电源、耳机、换洗衣物、洗漱包、常用药、雨伞、车票/机票/酒店确认、工牌/名片、报销单据 |
|
||
| 回老家 | 12 | 身份证/证件、钥匙、手机充电器+充电宝、换洗衣物、洗漱包、常用药、给家人的礼物/伴手礼、长辈的药品/营养品、口罩、现金零钱、车票/加油/行程确认、家里门窗水电检查 |
|
||
|
||
---
|
||
|
||
## 4. 外键与级联
|
||
|
||
| 子表.字段 | 父表.字段 | ON DELETE | ON UPDATE |
|
||
| --- | --- | --- | --- |
|
||
| `Habit.userId` | `User.id` | CASCADE | CASCADE |
|
||
| `CheckIn.habitId` | `Habit.id` | CASCADE | CASCADE |
|
||
| `CheckIn.userId` | `User.id` | CASCADE | CASCADE |
|
||
| `CheckList.userId` | `User.id` | CASCADE | CASCADE |
|
||
| `CheckListItem.listId` | `CheckList.id` | CASCADE | CASCADE |
|
||
| `CheckListTemplate.userId` | `User.id` | CASCADE | CASCADE |
|
||
|
||
删除链路：删用户 → 级联清 Habit/CheckIn/CheckList/Template；删 Habit → 清其全部 CheckIn；删 CheckList → 清其全部 Item。业务层删打卡项默认只置 `archived=true`，物理删需 `?hard=1`（此时先 `deleteMany` 记录再删主表）。
|
||
|
||
---
|
||
|
||
## 5. 关键设计决策
|
||
|
||
### 5.1 为什么 `date` 用 `VARCHAR(10)` 而不是 `DATETIME`
|
||
|
||
| 维度 | VARCHAR(10) 本地日历日 | DATETIME(UTC) |
|
||
| --- | --- | --- |
|
||
| 跨零点归属 | 明确：由应用层按 Asia/Shanghai 计算一次，落库即定 | 读时需要再转时区，易错 |
|
||
| 范围查询 | `date >= '2026-10-01' AND date <= '2026-10-31'` 直接走索引 | 需函数/边界换算，易失效 |
|
||
| 幂等边界 | `UNIQUE(habitId, date, seq)` 天然按「日」收敛 | 需按日截取 |
|
||
| 字典序 | 等于时间序，可安全字符串比较 | — |
|
||
| 存储 | 10 字节 | 5 字节（略优，但不足以抵消上述成本） |
|
||
|
||
代价：跨时区展示需自行换算——本项目全链路 Asia/Shanghai，不成为问题。
|
||
|
||
### 5.2 为什么不冗余 `doneCount` / `streak`
|
||
|
||
- **并发**：计数需事务 + 行锁，聚合查询天然一致。
|
||
- **改配置**：用户把「每周 3 次」改成「每周 5 次」，冗余计数立刻失真，需要全量重算；现方案改 `target` 立即生效。
|
||
- **撤销**：撤销即删一条记录，无需维护计数器。
|
||
- **成本**：单个用户打卡项数量在 10~30 量级，月历查询一次拉取整月记录（≤ 数百行）在内存聚合，开销可接受。
|
||
|
||
### 5.3 `seq` 与幂等
|
||
|
||
同日多次打卡用 `seq = max(seq)+1`。唯一约束 `(habitId, date, seq)` 使并发重复提交必然撞 P2002，服务端转 409「该次打卡已记录」，前端幂等收敛，不会出现「点两下加两次」的体验事故。
|
||
|
||
### 5.4 归档 vs 物理删除
|
||
|
||
| 操作 | 语义 | 数据影响 |
|
||
| --- | --- | --- |
|
||
| `DELETE /api/checkin/habits/:id` | 归档（默认） | `archived=true`，记录全保留 |
|
||
| `DELETE /api/checkin/habits/:id?hard=1` | 物理删除 | 先删 CheckIn，再删 Habit |
|
||
| `DELETE /api/checklists/:id` | 物理删除（一次性清单，不留历史） | 先删 Item，再删 List |
|
||
|
||
理由：打卡是长期行为数据，历史构成 streak 与完成率；清单是短期事务，用完即归档/删除。
|
||
|
||
### 5.5 模板用 JSON 而非明细表
|
||
|
||
模板条目结构固定（`title` + `note`）、只在「创建清单」时整体读取并展开、不需要按条目检索或统计 —— 用 `JSON` 列避免额外的 `TemplateItem` 表与 join。展开后落到 `CheckListItem`，之后模板改动不影响已生成清单。
|
||
|
||
---
|
||
|
||
## 6. 数据量估算
|
||
|
||
单用户规模假设：打卡项 20 个，平均每天打卡 25 次；清单每月 4 份，每份 12 条。
|
||
|
||
| 表 | 年增量（单用户） | 行大小（约） | 年占用（约） |
|
||
| --- | --- | --- | --- |
|
||
| `CheckIn` | 25 × 365 ≈ 9,100 | ~80 B | ~0.7 MB |
|
||
| `CheckListItem` | 4 × 12 × 12 = 576 | ~90 B | ~0.05 MB |
|
||
| `Habit` / `CheckList` / `CheckListTemplate` | < 100 | ~200 B | 忽略 |
|
||
|
||
结论：**无需分区、无需归档策略**。10 用户 × 5 年 ≈ 45 万行 CheckIn，仍远低于单表瓶颈。若未来接入多租户或做年视图长周期统计，再考虑按月汇总表。
|
||
|
||
---
|
||
|
||
## 7. 典型查询
|
||
|
||
### 7.1 今日待办 + 周期进度
|
||
|
||
```sql
|
||
-- 步骤 1: 取未归档打卡项 (走 Habit_userId_archived_sortOrder_idx)
|
||
SELECT id, name, period, target, color FROM Habit
|
||
WHERE userId = ? AND archived = 0 ORDER BY sortOrder, id;
|
||
|
||
-- 步骤 2: 取覆盖所有周期窗口的最小区间内的记录 (走 CheckIn_userId_date_idx)
|
||
SELECT habitId, date FROM CheckIn
|
||
WHERE userId = ? AND date >= ? AND date <= ?; -- 如 week: '2026-10-19' ~ '2026-10-25'
|
||
-- 步骤 3: 内存按 habitId/date 分组 -> 计算 done/percent/streak
|
||
```
|
||
|
||
### 7.2 打一次卡
|
||
|
||
```sql
|
||
SELECT MAX(seq) FROM CheckIn WHERE habitId = ? AND date = ?; -- -> seq
|
||
INSERT INTO CheckIn (habitId, userId, date, seq, note, source) VALUES (?, ?, ?, ?, ?, 'user');
|
||
-- 冲突 -> P2002 -> 409
|
||
```
|
||
|
||
撤销（撤销本周期最近一次）：
|
||
|
||
```sql
|
||
SELECT id FROM CheckIn WHERE habitId = ? AND date BETWEEN ? AND ?
|
||
ORDER BY date DESC, seq DESC LIMIT 1;
|
||
DELETE FROM CheckIn WHERE id = ?;
|
||
```
|
||
|
||
### 7.3 月历（日历视图数据来源）
|
||
|
||
```sql
|
||
SELECT habitId, date FROM CheckIn
|
||
WHERE userId = ? AND date >= '2026-10-01' AND date <= '2026-10-31';
|
||
```
|
||
|
||
按天聚合后与 `Habit.expectedPerDay` 比较得出日状态：
|
||
|
||
| 状态 | 条件 | 展示 |
|
||
| --- | --- | --- |
|
||
| `full` | 全部打卡项当日次数 ≥ 日均期望 | 绿点 |
|
||
| `over` | 有任一打卡项当日次数 > 日均期望 | 紫点 |
|
||
| `partial` | 有完成但未全完成 | 黄点 |
|
||
| `empty` | 无记录或当天无打卡项 | 无点 |
|
||
|
||
`Habit.createdAt` 早于该日的才计入期望，避免新建打卡项把历史日期标成「未完成」。
|
||
|
||
### 7.4 清单列表（带进度）
|
||
|
||
```sql
|
||
SELECT l.*, i.* FROM CheckList l
|
||
LEFT JOIN CheckListItem i ON i.listId = l.id
|
||
WHERE l.userId = ? AND l.archived = ?
|
||
ORDER BY l.updatedAt DESC, i.sortOrder, i.id;
|
||
```
|
||
|
||
---
|
||
|
||
## 8. 迁移
|
||
|
||
| 项 | 内容 |
|
||
| --- | --- |
|
||
| 迁移目录 | `prisma/migrations/20261022120000_add_checkin_checklist/` |
|
||
| 内容 | 5 张 `CREATE TABLE` + 6 条 `ADD CONSTRAINT`（见 `migration.sql`） |
|
||
| 生成方式 | 手写 SQL（与 `prisma migrate diff` 产出格式一致），已通过 `prisma migrate deploy` 验证 |
|
||
| 部署命令 | `npx prisma migrate deploy && npx prisma generate` |
|
||
| 回滚 | 无自动回滚。应急：`DROP TABLE CheckIn, CheckListItem, CheckList, CheckListTemplate, Habit;`（注意外键顺序）并把 `_prisma_migrations` 中该记录删除 |
|
||
|
||
>V0.2 全为新增表与新增字段（`User` 侧仅新增 Prisma 反向关系，不产生 DDL），对既有数据零影响。
|
||
|
||
---
|
||
|
||
## 9. V0.3 预留
|
||
|
||
| 方向 | 可能的表变更 |
|
||
| --- | --- |
|
||
| 打卡提醒 | `Habit` 增加 `remindAt TIME`、`remindDays` |
|
||
| 打卡统计快照 | 新增 `HabitDailyStat(userId, date, done, expected)` 汇总表，用于年视图/热力图加速 |
|
||
| 清单共享协作 | 新增 `CheckListShare(listId, userId, role)` |
|
||
| 模板市场 | `CheckListTemplate` 增加 `visibility`、`useCount` |
|
||
|
||
以上均可通过「新增表 / 新增可空字段」完成，不影响 V0.2 数据结构。
|