用 Claude Code 做数据库设计评审：实战指南

数据库设计不是把字段列出来那么简单。你要决定哪些信息放在同一张表，哪些列必须填写，哪些业务值不能重复，表与表之间如何约束，以及半年后列表、搜索、账单和报表会不会变慢。很多系统一开始能跑，是因为测试数据太少；真正上线后，重复账号、丢失父记录、错误账单和无法回滚的迁移才会暴露出来。

Claude Code 很适合参与这个过程，但它不应该成为唯一真相。更稳妥的用法是把它当作设计助手和审查员：让它生成草案、列风险、写测试 SQL、提醒你哪些假设需要确认。最终结论仍然要由人类根据产品规则、真实查询、官方文档和运维约束来决定。

相关主题可以继续读 数据库迁移自动化 和 Prisma ORM 实践。做约束和索引判断时，请对照 PostgreSQL 约束文档 与 PostgreSQL 索引文档。如果审计日志、搜索表或报表表可能保存敏感信息，也要参考 OWASP Top 10 的安全视角。

先理解基本概念

表是同一类记录的集合，例如 users、teams、projects。列是记录中的一个字段，例如 email、status、created_at。主键用来唯一识别一行，外键保证某行引用的父记录真实存在。唯一约束阻止重复业务值，例如邮箱、团队 slug、支付平台客户 ID。索引像目录，让常见的 WHERE、JOIN、ORDER BY 更快。迁移是修改表结构的脚本，回滚是发布出错时的返回路径。

初学者容易把这些都交给应用层判断，但数据库必须承担最后一道防线。应用表单可以提示用户“邮箱已存在”，数据库唯一约束则能挡住并发请求和批处理写入。Claude Code 的价值就在这里：它可以帮你检查“这个规则是不是只写在代码里，没有写进数据库”。

claude -p "Read docs/database/schema.sql and migrations/*.sql.
Act as a database design reviewer, not as the owner of truth.

Check for:
- Missing primary keys, foreign keys, unique constraints, and NOT NULL rules
- status or role columns that are too loose
- Indexes needed for WHERE, JOIN, and ORDER BY patterns
- Destructive migrations and rollback gaps
- PII leaking into audit logs or search/reporting tables
- API shapes that may trigger N+1 queries

Return findings first. Include why it matters, how to verify it, and only then suggest a minimal patch."

4 个具体用例

第一个用例是 SaaS 的用户、团队、项目结构。用户可以加入多个团队，团队拥有多个项目。项目 slug 通常只需要在团队内唯一，而不是全站唯一，所以约束应该是 UNIQUE (team_id, slug)。

第二个用例是审计日志。你想记录谁在什么时间对哪个对象做了什么，但审计表不能变成“什么都塞进去”的 JSON 垃圾桶。把邮箱、电话或地址写进 metadata 很方便，却会带来保留期限、权限和删除请求的问题。

第三个用例是账单和订阅。支付平台客户 ID 不能重复，同一个团队也不应该同时有两个有效订阅。设计时要允许保留已取消的历史记录，同时阻止当前状态冲突。

第四个用例是搜索或报表表。复杂列表不一定每次都从业务表实时 JOIN，可以建立读取优化表。但这样做必须明确刷新方式、索引和校验，否则搜索结果会悄悄落后于真实数据。

可直接运行的 PostgreSQL DDL

下面的 SQL 会重建一个专用 demo schema，适合在本地 psql 中直接粘贴执行。

DROP SCHEMA IF EXISTS claude_db_design_demo CASCADE;
CREATE SCHEMA claude_db_design_demo;
SET search_path = claude_db_design_demo;

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT users_email_unique UNIQUE (email),
  CONSTRAINT users_email_has_at CHECK (position('@' in email) > 1)
);

CREATE TABLE teams (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT teams_slug_unique UNIQUE (slug)
);

CREATE TABLE team_members (
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (team_id, user_id)
);

CREATE TABLE projects (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
  created_by BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT projects_team_slug_unique UNIQUE (team_id, slug)
);

CREATE TABLE audit_logs (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  actor_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT audit_action_not_blank CHECK (length(action) > 0),
  CONSTRAINT audit_metadata_no_email CHECK (
    metadata::text !~* '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}'
  )
);

CREATE TABLE subscriptions (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('stripe', 'manual')),
  provider_customer_id TEXT NOT NULL,
  plan_code TEXT NOT NULL CHECK (plan_code IN ('free', 'pro', 'business')),
  status TEXT NOT NULL CHECK (status IN ('trialing', 'active', 'past_due', 'canceled')),
  current_period_end DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT subscriptions_provider_customer_unique UNIQUE (provider, provider_customer_id)
);

CREATE TABLE project_search_documents (
  project_id BIGINT PRIMARY KEY REFERENCES projects(id) ON DELETE CASCADE,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  search_text TEXT NOT NULL,
  refreshed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX subscriptions_one_open_per_team
  ON subscriptions(team_id)
  WHERE status IN ('trialing', 'active', 'past_due');

CREATE INDEX idx_team_members_user_id ON team_members(user_id);
CREATE INDEX idx_projects_team_status_created ON projects(team_id, status, created_at DESC);
CREATE INDEX idx_audit_logs_team_created ON audit_logs(team_id, created_at DESC);
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_project_search_documents_search
  ON project_search_documents USING GIN (to_tsvector('simple', search_text));

种子数据与约束测试

DDL 只说明规则，测试 SQL 才能证明规则真的挡住了坏数据。下面先插入正常数据，再用 DO 块捕获预期失败。

SET search_path = claude_db_design_demo;

INSERT INTO users (email, display_name, password_hash) VALUES
  ('masa@example.com', 'Masa', '$2b$demo-hash-1'),
  ('aiko@example.com', 'Aiko', '$2b$demo-hash-2'),
  ('ren@example.com', 'Ren', '$2b$demo-hash-3');

INSERT INTO teams (name, slug) VALUES
  ('ClaudeCodeLab', 'clab'),
  ('Northstar Apps', 'northstar');

INSERT INTO team_members (team_id, user_id, role) VALUES
  (1, 1, 'owner'),
  (1, 2, 'admin'),
  (2, 3, 'owner');

INSERT INTO projects (team_id, name, slug, status, created_by) VALUES
  (1, 'Schema Review', 'schema-review', 'active', 1),
  (1, 'Billing Console', 'billing-console', 'active', 2),
  (2, 'Customer Portal', 'customer-portal', 'active', 3);

INSERT INTO audit_logs (team_id, actor_user_id, action, entity_type, entity_id, metadata) VALUES
  (1, 1, 'project.created', 'project', '1', '{"project_slug":"schema-review"}'::jsonb),
  (1, 2, 'billing.plan_changed', 'subscription', '1', '{"from":"free","to":"pro"}'::jsonb);

INSERT INTO subscriptions (team_id, provider, provider_customer_id, plan_code, status, current_period_end) VALUES
  (1, 'stripe', 'cus_demo_1', 'pro', 'active', DATE '2026-07-31'),
  (2, 'manual', 'manual_northstar_1', 'free', 'canceled', NULL);

INSERT INTO project_search_documents (project_id, team_id, title, search_text)
SELECT
  id,
  team_id,
  name,
  name || ' project notes for database design, billing, audit, and reporting.'
FROM projects;

SET search_path = claude_db_design_demo;

DO $$
BEGIN
  INSERT INTO users (email, display_name, password_hash)
  VALUES ('masa@example.com', 'Duplicate Masa', '$2b$demo-hash-x');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK duplicate email blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO projects (team_id, name, slug, created_by)
  VALUES (1, NULL, 'bad-null', 1);
EXCEPTION WHEN not_null_violation THEN
  RAISE NOTICE 'OK NULL project name blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO team_members (team_id, user_id, role)
  VALUES (999, 1, 'member');
EXCEPTION WHEN foreign_key_violation THEN
  RAISE NOTICE 'OK missing team blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO projects (team_id, name, slug, created_by)
  VALUES (1, 'Duplicate Slug', 'schema-review', 1);
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK duplicate team slug blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO audit_logs (team_id, actor_user_id, action, entity_type, entity_id, metadata)
  VALUES (1, 1, 'project.invited', 'project', '1', '{"email":"customer@example.com"}'::jsonb);
EXCEPTION WHEN check_violation THEN
  RAISE NOTICE 'OK email in audit metadata blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO subscriptions (team_id, provider, provider_customer_id, plan_code, status, current_period_end)
  VALUES (1, 'stripe', 'cus_demo_2', 'business', 'active', DATE '2026-08-31');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK second open subscription blocked: %', SQLERRM;
END $$;

SELECT p.id, p.name, count(a.id) AS audit_events
FROM projects p
LEFT JOIN audit_logs a
  ON a.entity_type = 'project'
 AND a.entity_id = p.id::text
WHERE p.team_id = 1
GROUP BY p.id, p.name
ORDER BY audit_events DESC, p.id;

SELECT project_id, title
FROM project_search_documents
WHERE to_tsvector('simple', search_text) @@ plainto_tsquery('simple', 'billing');

EXPLAIN (COSTS OFF)
SELECT *
FROM audit_logs
WHERE team_id = 1
ORDER BY created_at DESC
LIMIT 20;

常见失败点

缺少唯一约束最常见。邮箱、OAuth 账号 ID、支付客户 ID、团队内 slug 如果只靠应用代码检查，在并发请求下仍然可能重复。Claude Code 可以帮你列出“业务上只能有一个”的字段。

第二个问题是过度允许 NULL。项目名称、成员角色、订阅状态如果为空，页面、权限和账单逻辑都会变得不确定。deleted_at 或 current_period_end 可以为空，但必须能解释空值代表什么。

第三个问题是破坏性迁移。DROP COLUMN、类型变更、给已有大表加 NOT NULL、给大表加默认值，都可能造成锁或数据丢失。让 Claude Code 假设生产表有一千万行，并要求它给出分阶段迁移方案。

第四个问题是 N+1 查询。列表先查 100 个项目，再逐个查审计日志数量，会变成 101 次查询。设计评审时就要确认能否用聚合查询、报表表或读取模型解决。

第五个问题是 PII 泄漏。审计日志和报表表保留时间通常更长，访问角色也更多。邮件、电话、地址、token、IP 地址不要因为方便就放进 JSON metadata。

迁移和回滚清单

# Migration checklist: add projects.archived_at

## Design
- [ ] Why this column is needed is written in the issue or PR.
- [ ] Existing rows and backfill behavior are defined.
- [ ] NOT NULL, default value, and CHECK constraint choices are explained.
- [ ] Index impact was checked against real WHERE / ORDER BY queries.

## Safety
- [ ] This migration does not drop data in the first deploy.
- [ ] Large-table locks were considered.
- [ ] The application can run during the expand-and-contract period.
- [ ] Backfill can be retried safely.

## Up SQL
    ALTER TABLE projects ADD COLUMN archived_at TIMESTAMPTZ;
    UPDATE projects
    SET archived_at = now()
    WHERE status = 'archived' AND archived_at IS NULL;
    ALTER TABLE projects
      ADD CONSTRAINT projects_archived_at_matches_status
      CHECK (
        (status = 'archived' AND archived_at IS NOT NULL)
        OR (status = 'active' AND archived_at IS NULL)
      ) NOT VALID;
    ALTER TABLE projects VALIDATE CONSTRAINT projects_archived_at_matches_status;

## Down SQL
    ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_archived_at_matches_status;
    ALTER TABLE projects DROP COLUMN IF EXISTS archived_at;

## Verification
- [ ] Constraint test SQL was run locally.
- [ ] Read queries still use expected indexes.
- [ ] Rollback was reviewed, including data-loss notes.
- [ ] Claude Code findings were reviewed by a human owner.

回滚要特别认真读。自动生成的 down migration 可能只恢复表结构，却无法恢复已经删除的数据。如果不能完全回滚，就在 PR 中明确写出来，并准备备份、灰度发布或恢复步骤。

用图共享设计

erDiagram
    users ||--o{ team_members : joins
    teams ||--o{ team_members : has
    teams ||--o{ projects : owns
    users ||--o{ projects : creates
    teams ||--o{ audit_logs : records
    users ||--o{ audit_logs : acts
    teams ||--o{ subscriptions : pays
    projects ||--|| project_search_documents : indexes

图能帮助团队快速讨论关系，但不能替代 DDL、迁移脚本和测试 SQL。漂亮的 ER 图如果没有唯一约束，数据照样会坏。

CTA：把评审规则变成团队习惯

如果团队要用 Claude Code 做数据库设计，请把规则写进 CLAUDE.md：禁止直接连接生产数据库，破坏性迁移必须分阶段，没有约束测试不能合并，日志中不得随意写入 PII。ClaudeCodeLab 可以帮助团队整理 Claude Code 导入、迁移评审模板、PR 清单和团队培训。需要把这些规则应用到真实仓库时，可以从 Claude Code 培训与咨询 开始；个人学习也可以先看免费速查表和产品模板。

Masa 实际试用这套流程时，最有效的不是第一版 ER 图，而是“应该失败的 INSERT”。重复邮箱、空项目名、缺失团队、重复 slug、审计日志写入邮箱、第二个有效订阅，这些失败用例让评审变得具体。Claude Code 能快速生成清单，但真正的信心来自数据库亲自拒绝坏数据。