全栈项目从 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 db=sqlite3.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)query="SELECT title FROM task WHERE project_id=? AND status=? ORDER BY updated_at DESC"print("tasks="+",".join(row[0]forrowindb.execute(query,(10,"doing"))))plan=db.execute("EXPLAIN QUERY PLAN "+query,(10,"doing")).fetchone()[3]print("uses_index="+str("task_project_status_updated"inplan).lower())try:db.execute("INSERT INTO task VALUES (?, ?, ?, ?, ?)",(4,10," ","todo","2026-08-03T12:00:00Z"))exceptsqlite3.IntegrityError:print("blank_title=rejected")

运行输出：

tasks=补充审计日志,实现令牌刷新 uses_index=true blank_title=rejected

PostgreSQL 中用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(key=lambdaitem:(item["updated_at"],item["id"]),reverse=True)defencode_cursor(item:dict)->str:raw=json.dumps([item["updated_at"],item["id"]],separators=(",",":"))returnbase64.urlsafe_b64encode(raw.encode()).decode().rstrip("=")defdecode_cursor(value:str)->tuple[str,int]:padded=value+"="*(-len(value)%4)timestamp,task_id=json.loads(base64.urlsafe_b64decode(padded))returntimestamp,int(task_id)defpage(after:str|None,size:int)->tuple[list[dict],str|None]:eligible=tasksifafter:boundary=decode_cursor(after)eligible=[itemforitemintasksif(item["updated_at"],item["id"])<boundary]selected=eligible[:size]next_cursor=encode_cursor(selected[-1])iflen(eligible)>sizeelseNonereturnselected,next_cursor first,cursor=page(None,2)second,_=page(cursor,2)print("first="+",".join(str(item["id"])foriteminfirst))print("second="+",".join(str(item["id"])foriteminsecond))print("cursor_roundtrip="+str(decode_cursor(cursor)==(first[-1]["updated_at"],first[-1]["id"])).lower())

运行输出：

first=9,7 second=8,6 cursor_roundtrip=true

事务边界应围绕业务动作，而非单条 SQL。创建任务、写审计记录、写 outbox 事件在同一事务完成；发送邮件不放在数据库事务中，因为外部网络调用无法随事务回滚，而且等待网络会延长锁占用。消费者读取 outbox 后发送并标记完成，重复投递由幂等键吸收。隔离级别也不是越高越好：默认 Read Committed 覆盖大部分 CRUD；配额扣减可用带条件的原子更新，状态竞争可用行锁或版本号。选择机制要对应具体竞争，而不是把所有事务一律升级。

多租户查询的仓储接口必须要求workspace_id，不能提供容易误用的裸get(task_id)。更强的方案是 PostgreSQL Row-Level Security，但策略、连接池会话变量和管理员绕过权限都要测试；RLS 是纵深防御，不替代应用授权。

四、踩坑：ORM 不能替你设计约束

ORM 方便映射和组合查询，却容易触发 N+1：先查任务列表，再为每条任务单独查负责人。通过预加载、显式 join 或批量查询修复，并用查询计数测试防回归。枚举直接使用数据库 enum 改值成本较高；稳定状态可用 enum，频繁变化则考虑检查约束或引用表。软删除会污染所有唯一约束和查询，只有审计、恢复或法规确有需求时才引入，并明确部分唯一索引策略。

不要用浮点保存金额，不要用本地时间保存跨时区事件，不要让 JSONB 代替所有列。高频筛选、关联和有约束的数据应建成普通列；变化快、低频读取的附加属性才适合 JSONB。

五、验证：数据规则必须能故意撞坏

迁移测试应从空库升级到最新，也从上一发布版本升级；若工具支持降级，再验证只在确认数据可逆时执行降级。测试要故意插入重复成员、空标题、非法状态和不存在的外键，确认失败来自预期约束；生成接近生产基数与偏斜的数据检查查询计划；让两个事务同时更新同一任务，确认版本冲突不会静默覆盖。最后检查迁移锁时长和大表扫描，DDL 在测试库很快不代表生产不会阻塞。

可迁移的验收清单是：每条业务不变量都有唯一责任层，每个列表查询都有对应访问路径，每个迁移阶段都允许回滚部署，每个跨租户读取都显式携带workspace_id。做到这些，ORM 更换、接口扩展或数据量增长时，模型仍有稳定支点。

数据模型已经成为可信底座。下一篇会在它之上实现密码存储、访问令牌、刷新令牌轮换、工作区角色与资源级授权，重点堵住“已登录但越权”的漏洞。

参考来源

• PostgreSQL：约束
• PostgreSQL：索引
• PostgreSQL：事务隔离
• PostgreSQL：行级安全策略

👍 觉得有用就点个赞 + 收藏，方便回头查阅；有疑问直接在评论区留言，我看到都会回。

🚀 本文属于《全栈项目从 0 到 1 实战》系列，持续更新，关注不迷路。

📌 文章里的代码都能直接跑。想要可直接 clone 的完整工程 + 配套部署脚本 / 踩坑清单？评论一声或发邮件到cj2664@qq.com，我免费发你。
如果你正好在做类似系统、或有工程化难题想找人做，也欢迎邮件聊一句——我按实际情况评估，能落地的就接单或出方案。评论和邮件都能直接找到我，不用跳别的平台。