database-schema-designer

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Schema Designer

数据库Schema设计工具

Tier: POWERFUL
Category: Engineering
Domain: Data Architecture / Backend

Tier: POWERFUL
分类: 工程类
领域: 数据架构/后端

Overview

概述

Design relational database schemas from requirements and generate migrations, TypeScript/Python types, seed data, RLS policies, and indexes. Handles multi-tenancy, soft deletes, audit trails, versioning, and polymorphic associations.

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

Core Capabilities

核心能力

Schema design — normalize requirements into tables, relationships, constraints
Migration generation — Drizzle, Prisma, TypeORM, Alembic
Type generation — TypeScript interfaces, Python dataclasses/Pydantic models
RLS policies — Row-Level Security for multi-tenant apps
Index strategy — composite indexes, partial indexes, covering indexes
Seed data — realistic test data generation
ERD generation — Mermaid diagram from schema

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

When to Use

使用场景

Designing a new feature that needs database tables
Reviewing a schema for performance or normalization issues
Adding multi-tenancy to an existing schema
Generating TypeScript types from a Prisma schema
Planning a schema migration for a breaking change

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

Schema Design Process

Schema设计流程

Step 1: Requirements → Entities

步骤1：需求 → 实体

Given requirements:

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

Extract entities:

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

给定需求：

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

提取实体：

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

Step 2: Identify Relationships

步骤2：识别关系

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

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

Step 3: Add Cross-cutting Concerns

步骤3：添加跨领域关注点

Multi-tenancy: add
```
organization_id
```
to all tenant-scoped tables
Soft deletes: add
```
deleted_at TIMESTAMPTZ
```
instead of hard deletes

Audit trail: add

created_by

updated_by

created_at

updated_at

Versioning: add
```
version INTEGER
```
for optimistic locking

多租户：为所有租户范围的表添加
```
organization_id
```
软删除：添加
```
deleted_at TIMESTAMPTZ
```
而非硬删除

审计追踪：添加

created_by

updated_by

created_at

updated_at

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

Full Schema Example (Task Management SaaS)

完整Schema示例（任务管理SaaS）

→ See references/full-schema-examples.md for details

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

Row-Level Security (RLS) Policies

行级安全（RLS）策略

sql

-- 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);

sql

-- 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);

Seed Data Generation

种子数据生成

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('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: 'admin@acme.com',
    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))

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('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: 'admin@acme.com',
    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 Generation (Mermaid)

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
    }

Generate from Prisma:

bash

npx prisma-erd-generator

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
    }

Generate from Prisma:

bash

npx prisma-erd-generator

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

---

---

Common Pitfalls

常见陷阱

Soft delete without index —
```
WHERE deleted_at IS NULL
```
without index = full scan
Missing composite indexes —
```
WHERE org_id = ? AND status = ?
```
needs a composite index
Mutable surrogate keys — never use email or slug as PK; use UUID/CUID
Non-nullable without default — adding a NOT NULL column to existing table requires default or migration plan
No optimistic locking — concurrent updates overwrite each other; add
```
version
```
column
RLS not tested — always test RLS with a non-superuser role

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

Best Practices

最佳实践

Timestamps everywhere —
```
created_at
```
,
```
updated_at
```
on every table
Soft deletes for auditable data —
```
deleted_at
```
instead of DELETE
Audit log for compliance — log before/after JSON for regulated domains
UUIDs or CUIDs as PKs — avoid sequential integer leakage
Index foreign keys — every FK column should have an index
Partial indexes — use
```
WHERE deleted_at IS NULL
```
for active-only queries
RLS over application-level filtering — database enforces tenancy, not just app code

全表时间戳 — 每张表都添加
```
created_at
```
、
```
updated_at
```
可审计数据用软删除 — 使用
```
deleted_at
```
替代DELETE操作
合规审计日志 — 针对受监管领域记录变更前后的JSON数据
用UUID或CUID作为主键 — 避免自增整数泄露信息
外键加索引 — 每个外键字段都应添加索引
部分索引 — 对仅查询活跃数据的场景使用
```
WHERE deleted_at IS NULL
```
RLS优于应用层过滤 — 由数据库而非仅应用代码来强制租户隔离