数据库模式设计器

数据库模式设计器 (Database Schema Designer)

级别： POWERFUL
类别： 工程 (Engineering)
领域： 数据架构 / 后端 (Data Architecture / Backend)

---

概述

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

核心能力

• 模式设计 — 将需求规范化为表、关系和约束

• 迁移生成 — 支持 Drizzle, Prisma, TypeORM, Alembic

• 类型生成 — TypeScript 接口, Python dataclasses/Pydantic 模型

• RLS 策略 — 为多租户应用设计行级安全性 (Row-Level Security)

• 索引策略 — 复合索引、部分索引、覆盖索引

• 种子数据 — 生成真实的测试数据

• ERD 生成 — 根据模式生成 Mermaid 图表

---

使用场景

• 设计需要数据库表的新功能

• 审查模式是否存在性能问题或规范化问题

• 为现有模式添加多租户支持

• 从 Prisma 模式生成 TypeScript 类型

• 为破坏性变更规划模式迁移

---

模式设计流程

第一步：需求 $\rightarrow$ 实体

给定需求：
> “用户可以创建项目。每个项目包含多个任务。任务可以拥有标签。任务可以分配给用户。我们需要完整的审计追踪。”

提取实体：

User, Project, Task, Label, TaskLabel (中间表), TaskAssignment, AuditLog

第二步：确定关系

User 1──* Project         (所有者)
Project 1──* Task
Task *──* Label            (通过 TaskLabel)
Task *──* User            (通过 TaskAssignment)
User 1──* AuditLog

第三步：添加横切关注点 (Cross-cutting Concerns)

• 多租户：在所有租户范围的表中添加 organization_id

• 软删除：添加 deleted_at TIMESTAMPTZ 以替代物理删除

• 审计追踪：添加 created_by, updated_by, created_at, updated_at

• 版本控制：添加 version INTEGER 用于乐观锁

---

完整模式示例 (任务管理 SaaS)

行级安全性 (RLS) 策略

-- 启用 RLS
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- 创建应用角色
CREATE ROLE app_user;
-- 用户只能看到其组织项目中的任务
CREATE POLICY tasks_org_isolation ON tasks
  FOR ALL TO app_user
  USING (
    project_id IN (
      SELECT p.id FROM projects p
      JOIN organization_members om ON om.organization_id = p.organization_id
      WHERE om.user_id = current_setting('app.current_user_id')::text
    )
  );
-- 软删除：永远不显示已删除的记录
CREATE POLICY tasks_no_deleted ON tasks
  FOR SELECT TO app_user
  USING (deleted_at IS NULL);
-- 仅任务创建者或管理员可以删除
CREATE POLICY tasks_delete_policy ON tasks
  FOR DELETE TO app_user
  USING (
    created_by_id = current_setting('app.current_user_id')::text
    OR EXISTS (
      SELECT 1 FROM organization_members om
      JOIN projects p ON p.organization_id = om.organization_id
      WHERE p.id = tasks.project_id
        AND om.user_id = current_setting('app.current_user_id')::text
        AND om.role IN ('owner', 'admin')
    )
  );
-- 设置用户上下文 (在每次请求开始时调用)
SELECT set_c

---

种子数据生成

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: '[email protected]',
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))

---

ERD 生成 (Mermaid)

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 生成：

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

• 无索引的软删除 — WHERE deleted_at IS NULL 若无索引会导致全表扫描

• 缺失复合索引 — WHERE org_id = ? AND status = ? 需要复合索引

• 缺乏乐观锁 — 并发更新会互相覆盖；请添加 version 列

1. 全量时间戳 — 每个表都应包含 created_at 和 updated_at
2. 软删除
2. 可审计数据测试 — 使用 deleted_at 软删除而非 DELETE
3. 合规性审计日志 — 针对受监管领域记录变更前后的 JSON 数据
4. 使用 UUID 或 CUID 作为主键 — 避免顺序整数导致的数据泄露
5. 为外键建立索引 — 每个外键列都应拥有索引
6. 部分索引 — 对仅查询活跃数据的场景使用 WHERE deleted_at IS NULL`
7. 优先使用 RLS 而非应用层过滤 — 由数据库而非仅由应用代码强制执行多租户隔离