database-schema-designer

数据库模式设计器

设计一种能够在数据库中强制执行数据规则，并让应用的实际查询运行更快的模式。

工作流

1. 收集需求。 从对话或代码库中获取信息，只询问会阻碍设计的问题：

   • 数据库引擎和版本（默认：PostgreSQL 18；如有 docker-compose.yml、ORM 配置或 Supabase 项目，请进行检查）
   • 实体及其关系，用通俗语言描述
   • 最常见或最重要的 5-10 个查询，包括筛选条件和排序顺序
   • 数据量和增长情况（每张表每年的行数）、读写比例
   • 多租户情况（单一应用、按组织划分的多租户）、保留期限、审计和合规需求（PII、删除请求）
   • 正在使用的 ORM 或迁移工具（Prisma、Drizzle、Rails、Django、Alembic、Supabase migrations、纯 SQL）

2. 建模实体和关系。 列出实体、属性及基数（1:1、1:N、N:M）。默认规范化至 3NF；仅针对确有需要的指定查询进行反规范化，并说明是哪项查询。

3. 选择键和类型，使用 references/postgres.md（MySQL、SQLite、MongoDB 则使用 references/other-engines.md）。Postgres 的默认值：主键使用 bigint generated always as identity 或 uuid DEFAULT uuidv7()，优先使用 text 而非 varchar(n)，所有时间戳都使用 timestamptz，金额使用 numeric。

4. 将规则编码为约束。 必填值使用 NOT NULL，自然键使用 UNIQUE，范围和允许值使用 CHECK，并为每个外键明确选择 ON DELETE 行为。只存在于应用代码中的数据规则，可能会被下一次脚本执行或管理员修复破坏。

5. 根据第 1 步中的查询推导索引，不要照清单添加索引。为每项查询说明服务于它的索引。为每个参与联接或级联操作的外键列添加索引（Postgres 不会自动创建）。不要添加 UNIQUE 约束或主键已经提供的索引。

6. 编写交付内容：DDL、ERD、索引设计依据和可回滚的迁移，使用 references/schema-templates.md 及 references/output-format.md 中的结构。若用户已有迁移工具，则使用该工具。

7. 验证。 如果有可用数据库（本地 Postgres、Docker postgres:18、Supabase 分支），则运行 DDL、插入几行数据，并对关键查询执行 EXPLAIN，确认使用了计划中的索引。否则，手动检查第 1 步中的每项查询是否适用于该模式。然后检查下面的清单。

交付前检查

• 第 1 步中的每项查询都有指定索引支持，或说明为何在预期数据规模下全表扫描可以接受。
• 每个外键都明确指定了 ON DELETE 行为，并且引用列上有索引。
• 不使用无时区时间戳；金额不使用浮点类型；不因习惯而使用 varchar(255)。
• updated_at 确实会更新：DEFAULT now() 只会在插入时设置它，因此需添加触发器或在 ORM 中设置。
• 应忽略大小写的唯一性（电子邮件、用户名）需以不区分大小写的方式强制执行。
• 多租户表需包含 tenant_id（或 org_id），并将其纳入唯一约束和索引的首列；如果客户端可以访问数据库，则需启用行级安全策略（Supabase 上始终启用）。
• 对大型现有表进行操作的迁移需避免长时间锁定（参见 references/postgres.md 中的迁移安全说明）。
• 回滚脚本必须真正撤销对应更改，并标明具有破坏性的回滚操作（例如删除含有数据的列）。

实例

请求：“多租户项目跟踪器：组织、可加入多个组织的用户、每个组织下的项目、每个项目下的任务。主屏幕按截止日期列出某个项目的未完成任务。”

• 实体：orgs、users、org_members（用户与组织之间的多对多关系，包含 role）、projects（多对一关联组织）、tasks（多对一关联项目，负责人可选）。
• 关键查询：按截止日期排列某个项目的未完成任务，因此创建部分索引 ON tasks (project_id, due_at) WHERE status <> 'done'。
• 将规则设为约束：在 org_members 上使用 UNIQUE (org_id, user_id)；使用 CHECK (role IN ('owner','admin','member'))；通过 CHECK 限定 status 的取值；使用 UNIQUE (org_id, name) 确保组织内项目名称唯一。
• 删除：删除组织会级联删除项目和任务；删除用户时，将 tasks.assignee_id 设为 null，而不是删除任务。
• 多租户隔离：tasks 包含 org_id，这样行级安全策略无需通过 projects 联表即可检查成员资格；复合外键 (project_id, org_id) -> projects (id, org_id) 可确保两者一致。

此示例的完整 DDL 位于 references/schema-templates.md。

常见失败模式

• 未了解查询需求就设计表，然后“以防万一”给每一列都加上索引。每个索引都会拖慢所有写入操作。
• 对可能变化的值使用 Postgres ENUM 类型。添加值很容易，但删除或重命名值很麻烦；使用 CHECK 约束或查找表更便于演进。
• 默认到处添加软删除字段（deleted_at）。它们会影响每个查询和唯一约束。只有确实需要恢复或审计时才使用，并配合部分唯一索引。
• 使用数据库无法强制校验的多态外键（commentable_type + commentable_id）。优先使用多个可空外键并通过 CHECK 确保恰好设置一个，或者使用独立表。
• 将需要查询的结构化字段存入 jsonb。JSON 适用于稀疏或真正可变的属性；任何需要筛选、联表或约束的字段都应使用列。