data-modeler

Data Modeler (数据建模师)

Goal: 设计清晰、高效、可扩展的数据库结构。

技术栈: Prisma + PostgreSQL (主要) / SQLite (开发)

---

1. Schema Design Principles

命名规范

// 模型: PascalCase (单数)
model User {}
model ChatMessage {}

// 字段: camelCase
model User {
  id        String
  createdAt DateTime
  firstName String
}

// 关系字段: 描述关系
model Post {
  author    User    @relation(...)
  authorId  String
}

必备字段

model BaseModel {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// 所有模型都应包含这三个字段
model User {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // 业务字段...
}

软删除 (推荐)

model User {
  // ...
  deletedAt DateTime?  // null = 未删除
  
  @@index([deletedAt])
}

// 查询时过滤
const users = await prisma.user.findMany({
  where: { deletedAt: null }
})

---

2. Common Patterns

用户认证

model User {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // 认证
  email         String    @unique
  emailVerified DateTime?
  passwordHash  String?   // null if OAuth only
  
  // 资料
  name      String?
  image     String?
  
  // 关系
  accounts  Account[]
  sessions  Session[]
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  
  // OAuth tokens
  accessToken  String?
  refreshToken String?
  expiresAt    Int?
  
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

AI 对话

model Conversation {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  title     String?
  userId    String
  
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  messages  Message[]
  
  @@index([userId])
}

model Message {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  
  role           String   // 'user' | 'assistant' | 'system'
  content        String   @db.Text
  conversationId String
  
  // Token 统计
  promptTokens     Int?
  completionTokens Int?
  
  // AI 元数据
  model     String?
  finishReason String?
  
  conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
  
  @@index([conversationId])
}

Web3 用户

model User {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Web3
  address   String   @unique  // 0x...
  chainId   Int      @default(1)
  
  // ENS (缓存)
  ensName   String?
  ensAvatar String?
  ensUpdatedAt DateTime?
  
  // 关系
  wallets   Wallet[]
  
  @@index([address])
}

model Wallet {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  
  address   String
  chainId   Int
  label     String?  // "Main", "Trading", etc.
  
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@unique([userId, address, chainId])
}

交易记录

model Transaction {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  
  hash      String   @unique
  chainId   Int
  
  from      String
  to        String?
  value     Decimal  @db.Decimal(78, 0)  // BigInt as Decimal
  
  status    TransactionStatus @default(PENDING)
  
  // Gas
  gasLimit  Decimal? @db.Decimal(78, 0)
  gasUsed   Decimal? @db.Decimal(78, 0)
  gasPrice  Decimal? @db.Decimal(78, 0)
  
  // 关系
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  
  @@index([userId])
  @@index([hash])
  @@index([status])
}

enum TransactionStatus {
  PENDING
  CONFIRMED
  FAILED
}

---

3. Relations

一对多

model User {
  id    String @id
  posts Post[]
}

model Post {
  id       String @id
  author   User   @relation(fields: [authorId], references: [id])
  authorId String
  
  @@index([authorId])
}

多对多 (显式)

model User {
  id          String       @id
  memberships Membership[]
}

model Team {
  id          String       @id
  memberships Membership[]
}

model Membership {
  id     String @id @default(cuid())
  role   String @default("member")  // "owner" | "admin" | "member"
  
  userId String
  teamId String
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  team   Team   @relation(fields: [teamId], references: [id], onDelete: Cascade)
  
  @@unique([userId, teamId])
}

自引用

model Category {
  id       String     @id
  name     String
  
  parentId String?
  parent   Category?  @relation("CategoryHierarchy", fields: [parentId], references: [id])
  children Category[] @relation("CategoryHierarchy")
}

---

4. Indexes & Performance

索引策略

model User {
  id        String   @id
  email     String   @unique  // 自动索引
  createdAt DateTime
  status    String
  
  // 复合索引 (常用查询)
  @@index([status, createdAt])
  
  // 单字段索引
  @@index([createdAt])
}

何时添加索引

场景	需要索引
WHERE 条件字段	✅
ORDER BY 字段	✅
外键字段	✅ (手动添加)
JOIN 条件	✅
唯一约束字段	自动

BigInt 处理

// 方式 1: Decimal (推荐)
model Token {
  balance Decimal @db.Decimal(78, 0)  // 最大 uint256
}

// 方式 2: BigInt (Prisma 4.x+)
model Token {
  balance BigInt
}

---

5. Migrations

开发流程

# 1. 修改 schema.prisma

# 2. 创建迁移
pnpm prisma migrate dev --name add_user_avatar

# 3. 应用到开发库 (自动)

生产部署

# CI/CD 中运行
pnpm prisma migrate deploy

迁移最佳实践

# ✅ 小步迁移
add_user_table
add_user_email_index
add_user_avatar_field

# ❌ 大爆炸迁移
initial_schema_with_everything

数据迁移

// prisma/migrations/xxx/data-migration.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // 填充默认值
  await prisma.user.updateMany({
    where: { role: null },
    data: { role: 'user' }
  })
}

main()

---

6. Query Patterns

常用查询

// 分页
const users = await prisma.user.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
})

// 包含关系
const user = await prisma.user.findUnique({
  where: { id },
  include: { posts: true },
})

// 选择字段
const user = await prisma.user.findUnique({
  where: { id },
  select: { id: true, name: true, email: true },
})

// 统计
const count = await prisma.user.count({
  where: { status: 'active' },
})

事务

// 隐式事务
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.post.create({ data: postData }),
])

// 交互式事务
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData })
  await tx.post.create({ 
    data: { ...postData, authorId: user.id } 
  })
})

---

7. Schema Checklist

## 数据模型检查清单

### 基础
- [ ] 所有模型有 id, createdAt, updatedAt
- [ ] 使用正确的命名规范
- [ ] 外键字段有索引

### 关系
- [ ] onDelete 策略明确 (Cascade/SetNull/Restrict)
- [ ] 多对多使用显式中间表
- [ ] 自引用关系正确

### 性能
- [ ] 常用查询字段有索引
- [ ] 复合索引顺序正确
- [ ] BigInt 使用 Decimal

### 安全
- [ ] 敏感字段不直接暴露
- [ ] 软删除用于重要数据
- [ ] 审计字段 (createdBy, updatedBy)

---

Quick Reference

Prisma CLI

prisma generate      # 生成客户端
prisma migrate dev   # 创建迁移
prisma migrate deploy # 部署迁移
prisma studio        # 可视化界面