database-schema-designer

数据库 Schema 设计器

层级: POWERFUL
分类: Engineering
领域: 数据架构 / 后端

---

概述

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

核心能力

• Schema 设计 — 将需求规范化为表、关系和约束
• 迁移生成 — Drizzle、Prisma、TypeORM、Alembic
• 类型生成 — TypeScript 接口、Python dataclass/Pydantic 模型
• RLS 策略 — 面向多租户应用的行级安全(Row-Level Security)
• 索引策略 — 复合索引、部分索引、覆盖索引
• 种子数据 — 生成逼真的测试数据
• ERD 生成 — 根据 Schema 生成 Mermaid 图

---

适用场景

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

---

Schema 设计流程

第 1 步:需求 → 实体

给定需求:

"Users can create projects. Each project has tasks. Tasks can have labels. Tasks can be assigned to users. We need a full audit trail."

提取实体:

User, Project, Task, Label, TaskLabel (junction), TaskAssignment, AuditLog

第 2 步:识别关系

User 1──* Project         (owner)
Project 1──* Task
Task *──* Label            (via TaskLabel)
Task *──* User            (via TaskAssignment)
User 1──* AuditLog

第 3 步:添加横切关注点

• 多租户:为所有租户作用域表添加 organization_id
• 软删除:添加 deleted_at TIMESTAMPTZ,而不是硬删除
• 审计追踪:添加 created_by、updated_by、created_at、updated_at
• 版本控制:添加 version INTEGER 用于乐观锁

---

完整 Schema 示例(任务管理 SaaS)

→ 详情参见 references/full-schema-examples.md

行级安全(RLS)策略

-- Enable RLS
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create app role
CREATE ROLE app_user;

-- Users can only see tasks in their organization's projects
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
    )
  );

-- Soft delete: never show deleted records
CREATE POLICY tasks_no_deleted ON tasks
  FOR SELECT TO app_user
  USING (deleted_at IS NULL);

-- Only task creator or admin can delete
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')
    )
  );

-- Set user context (call at start of each request)
SELECT set_config('app.current_user_id', $1, true);

---

种子数据生成

// 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('Seeding database...')

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

  // Create users
  const adminUser = await db.insert(users).values({
    id: createId(),
    email: '[email protected]',
    name: "alice-admin",
    passwordHash: await hashPassword('password123'),
  }).returning().then(r => r[0])

  // Create projects
  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()

  // Create tasks for each project
  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(`✅ Seeded: 1 org, ${projectsData.length} projects, tasks`)
}

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

---

ERD 生成(Mermaid)

erDiagram
    Organization ||--o{ OrganizationMember : has
    Organization ||--o{ Project : owns
    User ||--o{ OrganizationMember : joins
    User ||--o{ Task : "created by"
    Project ||--o{ Task : contains
    Task ||--o{ TaskAssignment : has
    Task ||--o{ TaskLabel : has
    Task ||--o{ Comment : has
    Task ||--o{ Attachment : has
    Label ||--o{ TaskLabel : "applied to"
    User ||--o{ TaskAssignment : assigned

    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 prisma-erd-generator
# or: npx @dbml/cli prisma2dbml -i schema.prisma | npx dbml-to-mermaid

---

常见陷阱

• 软删除无索引 — 没有索引的 WHERE deleted_at IS NULL 会导致全表扫描
• 缺少复合索引 — WHERE org_id = ? AND status = ? 需要复合索引
• 可变代理键 — 切勿将 email 或 slug 用作主键;使用 UUID/CUID
• 无默认值的非空列 — 为已有表添加 NOT NULL 列需要提供默认值或迁移方案
• 没有乐观锁 — 并发更新会相互覆盖;添加 version 列
• 未测试 RLS — 务必使用非超级用户角色测试 RLS

---

最佳实践

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