数据库模式设计师

层级： 强大
类别： 工程
领域： 数据架构 / 后端

概述

根据需求设计关系型数据库模式，并生成迁移脚本、TypeScript/Python类型、种子数据、行级安全策略和索引。支持多租户、软删除、审计追踪、版本控制和多态关联。

核心能力

• - 模式设计 — 将需求规范化为表、关系、约束
• 迁移生成 — Drizzle、Prisma、TypeORM、Alembic
• 类型生成 — TypeScript接口、Python数据类/Pydantic模型
• 行级安全策略 — 多租户应用的行级安全
• 索引策略 — 复合索引、部分索引、覆盖索引
• 种子数据 — 逼真的测试数据生成
• ER图生成 — 基于模式的Mermaid图表

使用场景

• - 设计需要数据库表的新功能
• 审查模式的性能或规范化问题
• 为现有模式添加多租户支持
• 从Prisma模式生成TypeScript类型
• 规划破坏性变更的模式迁移

模式设计流程

第一步：需求 → 实体

给定需求：

用户可以创建项目。每个项目包含任务。任务可以添加标签。任务可以分配给用户。我们需要完整的审计追踪。

提取实体：

User, Project, Task, Label, TaskLabel（关联表）, TaskAssignment, AuditLog

第二步：识别关系

User 1──* Project （所有者）
Project 1──* Task
Task ── Label （通过TaskLabel）
Task ── User （通过TaskAssignment）
User 1──* AuditLog

第三步：添加横切关注点

• - 多租户：为所有租户范围表添加 organizationid
• 软删除：添加 deletedat TIMESTAMPTZ 替代硬删除
• 审计追踪：添加 createdby、updatedby、createdat、updatedat
• 版本控制：添加 version INTEGER 实现乐观锁

完整模式示例（任务管理SaaS）

行级安全策略

sql
-- 启用行级安全
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 创建应用角色
CREATE ROLE app_user;

-- 用户只能查看其组织项目中的任务
CREATE POLICY tasksorgisolation ON tasks
FOR ALL TO app_user
USING (
project_id IN (
SELECT p.id FROM projects p
JOIN organizationmembers om ON om.organizationid = p.organization_id
WHERE om.userid = currentsetting(app.currentuserid)::text
)
);

-- 软删除：永不显示已删除记录
CREATE POLICY tasksnodeleted ON tasks
FOR SELECT TO app_user
USING (deleted_at IS NULL);

-- 仅任务创建者或管理员可删除
CREATE POLICY tasksdeletepolicy ON tasks
FOR DELETE TO app_user
USING (
createdbyid = currentsetting(app.currentuser_id)::text
OR EXISTS (
SELECT 1 FROM organization_members om
JOIN projects p ON p.organizationid = om.organizationid
WHERE p.id = tasks.project_id
AND om.userid = currentsetting(app.currentuserid)::text
AND om.role IN (owner, admin)
)
);

-- 设置用户上下文（每次请求开始时调用）
SELECT setconfig(app.currentuser_id, $1, true);

种子数据生成

typescript
// db/seed.ts
import { faker } from @faker-js/faker
import { db } from ./client
import { organizations, users, projects, tasks } from ./schema
import { createId } from @paralleldrive/cuid2
import { hashPassword } from ../src/lib/auth

async function seed() {
console.log(正在填充数据库...)

// 创建组织
const [org] = await db.insert(organizations).values({
id: createId(),
name: acme-corp,
slug: acme,
plan: growth,
}).returning()

// 创建用户
const adminUser = await db.insert(users).values({
id: createId(),
email: admin@acme.com,
name: alice-admin,
passwordHash: await hashPassword(password123),
}).returning().then(r => r[0])

// 创建项目
const projectsData = Array.from({ length: 3 }, () => ({
id: createId(),
organizationId: org.id,
ownerId: adminUser.id,
name: fakercompanycatchphrase
description: faker.lorem.paragraph(),
status: active as const,
}))

const createdProjects = await db.insert(projects).values(projectsData).returning()

// 为每个项目创建任务
for (const project of createdProjects) {
const tasksData = Array.from({ length: faker.number.int({ min: 5, max: 20 }) }, (_, i) => ({
id: createId(),
projectId: project.id,
title: faker.hacker.phrase(),
description: faker.lorem.sentences(2),
status: faker.helpers.arrayElement([todo, in_progress, done] as const),
priority: faker.helpers.arrayElement([low, medium, high] as const),
position: i * 1000,
createdById: adminUser.id,
updatedById: adminUser.id,
}))

await db.insert(tasks).values(tasksData)
}

console.log(✅ 已填充：1个组织，${projectsData.length}个项目，任务)
}

seed().catch(console.error).finally(() => process.exit(0))

ER图生成（Mermaid）

erDiagram
Organization ||--o{ OrganizationMember : 拥有
Organization ||--o{ Project : 拥有
User ||--o{ OrganizationMember : 加入
User ||--o{ Task : 创建者
Project ||--o{ Task : 包含
Task ||--o{ TaskAssignment : 拥有
Task ||--o{ TaskLabel : 拥有
Task ||--o{ Comment : 拥有
Task ||--o{ Attachment : 拥有
Label ||--o{ TaskLabel : 应用于
User ||--o{ TaskAssignment : 分配

Organization {
string id PK
string name
string slug
string plan
}

Task {
string id PK
string project_id FK
string title
string status
string priority
timestamp due_date
timestamp deleted_at
int version
}

从Prisma生成：
bash
npx prisma-erd-generator

或：npx @dbml/cli prisma2dbml -i schema.prisma | npx dbml-to-mermaid

常见陷阱

• - 软删除无索引 — WHERE deletedat IS NULL 无索引 = 全表扫描
• 缺少复合索引 — WHERE orgid = ? AND status = ? 需要复合索引
• 可变替代键 — 切勿使用邮箱或slug作为主键；使用UUID/CUID
• 非空无默认值 — 向现有表添加NOT NULL列需要默认值或迁移计划
• 无乐观锁 — 并发更新会相互覆盖；添加 version 列
• 行级安全未测试 — 始终使用非超级用户角色测试行级安全

最佳实践

1. 1. 时间戳无处不在 — 每个表都包含 createdat、updatedat
2. 可审计数据使用软删除 — 使用 deletedat 替代DELETE
3. 合规审计日志 — 记录受监管领域变更前后的JSON
4. 使用UUID或CUID作为主键 — 避免顺序整数泄露
5. 外键索引 — 每个外键列都应有索引
6. 部分索引 — 使用 WHERE deletedat IS NULL 优化活跃数据查询
7. 行级安全优于应用层过滤 — 数据库强制执行租户隔离，而非仅靠应用代码