全栈项目从 0 到 1 实战（3）：数据库设计与建模

上一篇搭好了可启动的前后端骨架本篇把团队任务管理的业务规则落进 PostgreSQL。我们不会从“需要几张表”出发而是先列不可破坏的业务事实再反推键、约束、索引和迁移顺序。这个方法可以迁移到电商、工单和内容系统数据库不是对象的存放处而是并发请求最终汇合时仍能执行规则的裁判。一、痛点表能存数据不代表模型正确核心实体包括用户、工作区、成员、项目、任务。用户可加入多个工作区成员表承担多对多关系并保存角色项目属于一个工作区任务属于项目并记录负责人、状态、优先级和截止时间。最危险的错误是只在应用层检查“负责人是否属于同一工作区”。任何脚本、后台任务或未来服务绕过这段代码都可能写出跨租户关系。先写不变量工作区 slug 全局唯一同一用户在同一工作区只有一条成员记录项目键在工作区内唯一任务标题非空状态只能来自有限集合删除工作区不应误触发无界级联所有事件时间使用带时区类型。然后逐条决定由哪一层负责能由数据库无条件判断的事实交给约束依赖当前操作者身份的规则交给授权层跨外部系统的流程交给应用服务。数据库的NOT NULL、UNIQUE、CHECK、外键和事务是第一类规则的可执行版本。主键统一使用无业务含义的 ID展示用的WEB-42由项目键和项目内序号组成。这样项目改名不会牵动所有外键。外键的删除动作必须逐条设计成员退出可保留其历史任务并把负责人置空工作区删除则更适合异步归档而非一次无界级联。所谓“建模”本质上是提前决定数据生命周期。二、原理从访问路径反推索引索引不是“给每列加一个”。任务列表的真实查询是在某工作区的某项目中筛选状态按更新时间倒序分页。因此适合(project_id, status, updated_at DESC, id DESC)的复合索引若状态不是必选条件则还需(project_id, updated_at DESC, id DESC)。B-tree 遵守最左前缀列顺序由等值过滤、范围过滤和排序共同决定。下面程序用 SQLite 标准库创建与 PostgreSQL 语义接近的最小模型同时验证约束和查询计划。SQLite 不能替代 PostgreSQL 集成测试但适合把设计意图压缩成可执行示例正式测试仍应在与生产同主版本的 PostgreSQL 上重跑。importsqlite3 dbsqlite3.connect(:memory:)db.execute(PRAGMA foreign_keys ON)db.executescript( CREATE TABLE workspace ( id INTEGER PRIMARY KEY, slug TEXT NOT NULL UNIQUE ); CREATE TABLE project ( id INTEGER PRIMARY KEY, workspace_id INTEGER NOT NULL REFERENCES workspace(id), project_key TEXT NOT NULL, UNIQUE(workspace_id, project_key) ); CREATE TABLE task ( id INTEGER PRIMARY KEY, project_id INTEGER NOT NULL REFERENCES project(id), title TEXT NOT NULL CHECK(length(trim(title)) 0), status TEXT NOT NULL CHECK(status IN (todo, doing, done)), updated_at TEXT NOT NULL ); CREATE INDEX task_project_status_updated ON task(project_id, status, updated_at DESC, id DESC); )db.execute(INSERT INTO workspace VALUES (?, ?),(1,acme))db.execute(INSERT INTO project VALUES (?, ?, ?),(10,1,WEB))rows[(1,10,设计登录页,todo,2026-08-03T09:00:00Z),(2,10,实现令牌刷新,doing,2026-08-03T10:00:00Z),(3,10,补充审计日志,doing,2026-08-03T11:00:00Z),]db.executemany(INSERT INTO task VALUES (?, ?, ?, ?, ?),rows)querySELECT title FROM task WHERE project_id? AND status? ORDER BY updated_at DESCprint(tasks,.join(row[0]forrowindb.execute(query,(10,doing))))plandb.execute(EXPLAIN QUERY PLAN query,(10,doing)).fetchone()[3]print(uses_indexstr(task_project_status_updatedinplan).lower())try:db.execute(INSERT INTO task VALUES (?, ?, ?, ?, ?),(4,10, ,todo,2026-08-03T12:00:00Z))exceptsqlite3.IntegrityError:print(blank_titlerejected)运行输出tasks补充审计日志,实现令牌刷新 uses_indextrue blank_titlerejectedPostgreSQL 中用EXPLAIN (ANALYZE, BUFFERS)观察估算行数、实际行数、排序与缓冲命中但生产环境谨慎执行会真正跑查询的ANALYZE。小表选择顺序扫描很正常优化器比较的是成本而不是“有索引就必须用”。三、实现迁移采用扩展—迁移—收缩首个迁移创建表、约束与必要索引迁移文件一旦进入共享环境就不修改而是追加新迁移因为已部署环境记录的是历史版本不会重新理解被改写的过去。给大表新增必填列也不要一步完成先扩展结构为可空列部署能同时读写新旧结构的代码按主键范围小批回填核对空值和新旧结果最后添加NOT NULL并删除兼容逻辑。这就是扩展—迁移—收缩。每一步都允许旧、新实例短暂共存也各自有明确停止条件。游标分页比高页码OFFSET稳定数据库无需反复扫描并丢弃前面的行而且新任务插入列表顶部时后续页面不容易重复。排序键必须唯一确定因此更新时间相同时用 ID 打破平局。下例实现透明游标它只是编码而非加密客户端能读取和改写。生产中应重新校验边界若游标携带租户或权限条件则用 HMAC 签名。importbase64importjson tasks[{id:9,updated_at:2026-08-03T12:00:00Z,title:A},{id:7,updated_at:2026-08-03T12:00:00Z,title:B},{id:8,updated_at:2026-08-03T11:00:00Z,title:C},{id:6,updated_at:2026-08-03T10:00:00Z,title:D},]tasks.sort(keylambdaitem:(item[updated_at],item[id]),reverseTrue)defencode_cursor(item:dict)-str:rawjson.dumps([item[updated_at],item[id]],separators(,,:))returnbase64.urlsafe_b64encode(raw.encode()).decode().rstrip()defdecode_cursor(value:str)-tuple[str,int]:paddedvalue*(-len(value)%4)timestamp,task_idjson.loads(base64.urlsafe_b64decode(padded))returntimestamp,int(task_id)defpage(after:str|None,size:int)-tuple[list[dict],str|None]:eligibletasksifafter:boundarydecode_cursor(after)eligible[itemforitemintasksif(item[updated_at],item[id])boundary]selectedeligible[:size]next_cursorencode_cursor(selected[-1])iflen(eligible)sizeelseNonereturnselected,next_cursor first,cursorpage(None,2)second,_page(cursor,2)print(first,.join(str(item[id])foriteminfirst))print(second,.join(str(item[id])foriteminsecond))print(cursor_roundtripstr(decode_cursor(cursor)(first[-1][updated_at],first[-1][id])).lower())运行输出first9,7 second8,6 cursor_roundtriptrue事务边界应围绕业务动作而非单条 SQL。创建任务、写审计记录、写 outbox 事件在同一事务完成发送邮件不放在数据库事务中因为外部网络调用无法随事务回滚而且等待网络会延长锁占用。消费者读取 outbox 后发送并标记完成重复投递由幂等键吸收。隔离级别也不是越高越好默认 Read Committed 覆盖大部分 CRUD配额扣减可用带条件的原子更新状态竞争可用行锁或版本号。选择机制要对应具体竞争而不是把所有事务一律升级。多租户查询的仓储接口必须要求workspace_id不能提供容易误用的裸get(task_id)。更强的方案是 PostgreSQL Row-Level Security但策略、连接池会话变量和管理员绕过权限都要测试RLS 是纵深防御不替代应用授权。四、踩坑ORM 不能替你设计约束ORM 方便映射和组合查询却容易触发 N1先查任务列表再为每条任务单独查负责人。通过预加载、显式 join 或批量查询修复并用查询计数测试防回归。枚举直接使用数据库 enum 改值成本较高稳定状态可用 enum频繁变化则考虑检查约束或引用表。软删除会污染所有唯一约束和查询只有审计、恢复或法规确有需求时才引入并明确部分唯一索引策略。不要用浮点保存金额不要用本地时间保存跨时区事件不要让 JSONB 代替所有列。高频筛选、关联和有约束的数据应建成普通列变化快、低频读取的附加属性才适合 JSONB。五、验证数据规则必须能故意撞坏迁移测试应从空库升级到最新也从上一发布版本升级若工具支持降级再验证只在确认数据可逆时执行降级。测试要故意插入重复成员、空标题、非法状态和不存在的外键确认失败来自预期约束生成接近生产基数与偏斜的数据检查查询计划让两个事务同时更新同一任务确认版本冲突不会静默覆盖。最后检查迁移锁时长和大表扫描DDL 在测试库很快不代表生产不会阻塞。可迁移的验收清单是每条业务不变量都有唯一责任层每个列表查询都有对应访问路径每个迁移阶段都允许回滚部署每个跨租户读取都显式携带workspace_id。做到这些ORM 更换、接口扩展或数据量增长时模型仍有稳定支点。数据模型已经成为可信底座。下一篇会在它之上实现密码存储、访问令牌、刷新令牌轮换、工作区角色与资源级授权重点堵住“已登录但越权”的漏洞。参考来源PostgreSQL约束PostgreSQL索引PostgreSQL事务隔离PostgreSQL行级安全策略 觉得有用就点个赞 收藏方便回头查阅有疑问直接在评论区留言我看到都会回。 本文属于《全栈项目从 0 到 1 实战》系列持续更新关注不迷路。 文章里的代码都能直接跑。想要可直接 clone 的完整工程 配套部署脚本 / 踩坑清单评论一声或发邮件到cj2664qq.com我免费发你。如果你正好在做类似系统、或有工程化难题想找人做也欢迎邮件聊一句——我按实际情况评估能落地的就接单或出方案。评论和邮件都能直接找到我不用跳别的平台。