数据模型 v1（Prisma Schema · 2026-08-15 M0 D1 拍板）

作者：👤 Claude（执行副董事） 拍板：M0 D1 Claude 出全 3 份关键文档之一 目的：Codex 启动 M0 D3 数据模型实现时直接按此 schema 落地 关联：05-API契约.md · 07-5维评价详细设计.md · PRD §6

---

0. 设计原则

• 不要过度设计：MVP 阶段聚焦 7 类角色 + 5 维评价 + 班级 + 教联体
• 可扩展：所有表加 schoolId 字段，未来多校单租户可扩展
• 审计优先：所有关键表加 createdAt / updatedAt / createdBy / isDeleted
• 法规兼容：未成年人数据用 birthDate 字段，14 岁以下需家长授权
• 对标 PRD：5 维评价表结构按 PRD §6 完整落地

---

1. Prisma Schema（完整）

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

// ==================== 基础字典 ====================

/// 学校（单租户 MVP）
model School {
  id          String   @id @default(cuid())
  name        String   @db.VarChar(100)
  shortName   String   @db.VarChar(20)
  logo        String?  @db.VarChar(500)
  primaryColor String? @default("#C41A1A") @db.VarChar(10)
  city        String?  @db.VarChar(50)
  district    String?  @db.VarChar(50)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  classes     Class[]
  users       User[]
  termCodes   Term[]
  evalPoints  EvaluationPoint[]
  evalRecords EvaluationRecord[]

  @@map("schools")
}

/// 学期（如 2026 秋 / 2026 春）
model Term {
  id        String   @id @default(cuid())
  schoolId  String
  code      String   @db.VarChar(20)  // "2026-fall"
  name      String   @db.VarChar(50)  // "2026 秋季学期"
  startDate DateTime
  endDate   DateTime
  isActive  Boolean  @default(false)
  createdAt DateTime @default(now())

  school    School   @relation(fields: [schoolId], references: [id])
  reports   TermReport[]
  evalRecords EvaluationRecord[]

  @@unique([schoolId, code])
  @@map("terms")
}

/// 班级
model Class {
  id        String   @id @default(cuid())
  schoolId  String
  grade     String   @db.VarChar(10)  // "8" = 初二
  name      String   @db.VarChar(50)  // "初二(3)班"
  classTeacherId String?  // 班主任 userId
  studentCount Int   @default(0)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  school   School @relation(fields: [schoolId], references: [id])
  students User[]  @relation("ClassStudents")
  teacher  User?   @relation("ClassTeacher", fields: [classTeacherId], references: [id])
  parentNotices ParentNotice[]

  @@unique([schoolId, grade, name])
  @@map("classes")
}

// ==================== 用户 ====================

/// 用户（统一 7 类角色）
model User {
  id           String   @id @default(cuid())
  schoolId     String
  role         String   @db.VarChar(20)  // student | parent | teacher | schoolAdmin | educationAdmin | principal | partner
  name         String   @db.VarChar(50)
  identifier   String   @db.VarChar(100)  // 学号 / 工号 / openid / 钉钉unionId
  passwordHash String?  @db.VarChar(255)
  avatar       String?  @db.VarChar(500)
  phone        String?  @db.VarChar(20)
  email        String?  @db.VarChar(100)
  gender       String?  @db.VarChar(10)   // male | female | other
  birthDate    DateTime?
  idCard       String?  @db.VarChar(20)  // 身份证（敏感字段）

  // 学生专属
  studentNo    String?  @db.VarChar(20)
  classId      String?
  enrollmentYear Int?  // 入学年

  // 教师专属
  subjects     String?  @db.VarChar(200)  // JSON 数组
  title        String?  @db.VarChar(50)   // 高级教师 / 一级教师

  // 教联体专属
  organization String?  @db.VarChar(100) // 派出所 / 图书馆 / 雷锋纪念馆
  orgType      String?  @db.VarChar(20)  // 10 类教联体

  // 审批状态
  isApproved   Boolean  @default(false)
  approvedById String?
  approvedAt   DateTime?

  isActive     Boolean  @default(true)
  lastLoginAt  DateTime?
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  school       School   @relation(fields: [schoolId], references: [id])
  class        Class?   @relation("ClassStudents", fields: [classId], references: [id])
  classesTeaching Class[] @relation("ClassTeacher")

  // 儿童关系（家长 → 学生）
  childRelations UserRelation[] @relation("ParentRel")
  parentRelations UserRelation[] @relation("StudentRel")

  // 评价记录
  evaluatedRecords    EvaluationRecord[] @relation("EvaluatorRel")
  receivedRecords     EvaluationRecord[] @relation("StudentRel")
  authoredNotices     ParentNotice[]

  // 学期报告
  termReports TermReport[]

  @@unique([schoolId, role, identifier])
  @@index([schoolId, role])
  @@index([classId])
  @@map("users")
}

/// 用户关系（家长 ↔ 学生 多对多）
model UserRelation {
  id          String   @id @default(cuid())
  parentId    String
  studentId   String
  relation    String   @db.VarChar(20)  // father | mother | grandparent | other
  isPrimary   Boolean  @default(false)
  createdAt   DateTime @default(now())

  parent  User @relation("ParentRel", fields: [parentId], references: [id])
  student User @relation("StudentRel", fields: [studentId], references: [id])

  @@unique([parentId, studentId])
  @@map("user_relations")
}

// ==================== 5 维评价 ====================

/// 评价点（5 维 × 30 个 = 落 PRD §6）
model EvaluationPoint {
  id          String   @id @default(cuid())
  schoolId    String
  dimension   String   @db.VarChar(20)  // moral | academic | health | art | practice
  code        String   @db.VarChar(50)  // p_moral_1
  name        String   @db.VarChar(100)
  description String?  @db.Text
  weight      Float    @default(1.0)   // 在该维内的权重
  isActive    Boolean  @default(true)
  orderIndex  Int      @default(0)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  school School @relation(fields: [schoolId], references: [id])
  records EvaluationRecord[]

  @@unique([schoolId, code])
  @@index([schoolId, dimension])
  @@map("evaluation_points")
}

/// 评价记录（即时评价 / 批量评价）
model EvaluationRecord {
  id            String   @id @default(cuid())
  schoolId      String
  termId        String
  studentId     String
  evaluatorId   String
  pointId       String
  dimension     String   @db.VarChar(20)
  level         String   @db.VarChar(2)  // A | B | C | D
  score         Float    // 0~100
  comment       String?  @db.Text
  evidence      String?  @db.Text        // JSON 数组 URL
  evaluatedAt   DateTime
  createdAt     DateTime @default(now())
  isDeleted     Boolean  @default(false)

  school    School @relation(fields: [schoolId], references: [id])
  term      Term   @relation(fields: [termId], references: [id])
  student   User   @relation("StudentRel", fields: [studentId], references: [id])
  evaluator User   @relation("EvaluatorRel", fields: [evaluatorId], references: [id])
  point     EvaluationPoint @relation(fields: [pointId], references: [id])

  @@index([schoolId, termId])
  @@index([studentId, evaluatedAt])
  @@index([evaluatorId])
  @@map("evaluation_records")
}

// ==================== 学期报告 ====================

/// 学期报告单（每学期末自动生成）
model TermReport {
  id              String   @id @default(cuid())
  schoolId        String
  termId          String
  studentId       String
  overallScore    Float
  overallLevel    String   @db.VarChar(2)
  classRank       Int?
  gradeRank       Int?
  moralScore      Float
  academicScore   Float
  healthScore     Float
  artScore        Float
  practiceScore   Float
  moralBreakdown  String?  @db.Text  // JSON
  academicBreakdown String? @db.Text
  healthBreakdown String?  @db.Text
  artBreakdown    String?  @db.Text
  practiceBreakdown String? @db.Text
  classTeacherComment  String? @db.Text
  schoolAdminComment   String? @db.Text
  pdfUrl          String?  @db.VarChar(500)
  generatedAt     DateTime @default(now())
  updatedAt       DateTime @updatedAt

  term    Term   @relation(fields: [termId], references: [id])
  student User   @relation(fields: [studentId], references: [id])

  @@unique([termId, studentId])
  @@map("term_reports")
}

// ==================== 教联体 & 活动 ====================

/// 教联体类型（10 类字典）
model PartnerType {
  id        String   @id @default(cuid())
  code      String   @unique @db.VarChar(20)  // police | library | museum | ...
  name      String   @db.VarChar(50)
  icon      String?  @db.VarChar(100)
  orderIndex Int     @default(0)
  isActive  Boolean @default(true)
  createdAt DateTime @default(now())

  activities PartnerActivity[]
  members    User[]    // 反向关联

  @@map("partner_types")
}

/// 教联体活动（派出所/图书馆/雷锋纪念馆 3 类试点）
model PartnerActivity {
  id          String   @id @default(cuid())
  schoolId    String
  partnerType String   @db.VarChar(20)
  organizerId String   // 教联体用户 userId
  title       String   @db.VarChar(200)
  description String?  @db.Text
  category    String   @db.VarChar(50)  // 红色教育 / 劳动实践 / 公益服务 / 科技创新
  dimension   String?  @db.VarChar(20)  // 关联 5 维中的哪一维
  startAt     DateTime
  endAt       DateTime
  location    String?  @db.VarChar(200)
  maxParticipants Int  @default(50)
  currentParticipants Int @default(0)
  status      String   @default("open") @db.VarChar(20)  // open | full | ongoing | finished | cancelled
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  partnerTypeRel PartnerType @relation(fields: [partnerType], references: [code])
  participants  ActivityParticipant[]

  @@index([schoolId, partnerType])
  @@index([status, startAt])
  @@map("partner_activities")
}

/// 活动参与记录
model ActivityParticipant {
  id          String   @id @default(cuid())
  activityId  String
  studentId   String
  status      String   @default("registered") @db.VarChar(20)  // registered | attended | absent | completed
  joinedAt    DateTime @default(now())
  completedAt DateTime?
  feedbackScore Int?   // 1~5
  feedbackText  String? @db.Text

  activity PartnerActivity @relation(fields: [activityId], references: [id])

  @@unique([activityId, studentId])
  @@map("activity_participants")
}

// ==================== 家校互通 ====================

/// 家长通知
model ParentNotice {
  id          String   @id @default(cuid())
  schoolId    String
  classId     String?
  authorId    String
  title       String   @db.VarChar(200)
  content     String   @db.Text
  noticeType  String   @db.VarChar(20)  // announcement | reminder | event | feedback
  priority    String   @default("normal") @db.VarChar(20)  // low | normal | high | urgent
  publishedAt DateTime?
  expiresAt   DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  class   Class? @relation(fields: [classId], references: [id])
  author  User   @relation(fields: [authorId], references: [id])
  reads   NoticeRead[]

  @@index([schoolId, publishedAt])
  @@map("parent_notices")
}

/// 通知已读记录
model NoticeRead {
  id         String   @id @default(cuid())
  noticeId   String
  userId     String
  readAt     DateTime @default(now())

  notice ParentNotice @relation(fields: [noticeId], references: [id])

  @@unique([noticeId, userId])
  @@map("notice_reads")
}

// ==================== 系统表 ====================

/// 操作日志（合规审计 / 重要操作）
model AuditLog {
  id        String   @id @default(cuid())
  schoolId  String
  userId    String?
  action    String   @db.VarChar(100)  // "evaluation.create" | "user.login" | ...
  resource  String   @db.VarChar(100)  // "EvaluationRecord:123"
  ip        String?  @db.VarChar(50)
  userAgent String?  @db.Text
  payload   String?  @db.Text  // JSON
  createdAt DateTime @default(now())

  @@index([schoolId, createdAt])
  @@index([userId, createdAt])
  @@index([action, createdAt])
  @@map("audit_logs")
}

---

2. 关键关系图

School ─┬─ Class ──── User (student)
        │     │
        │     └─ User (teacher) ← classTeacherId
        │
        ├─ Term ──── EvaluationRecord
        │            │
        │            ├─ User (student)
        │            └─ User (evaluator) ←── User (teacher / schoolAdmin)
        │
        ├─ User (parent) ── UserRelation ── User (student)
        │
        ├─ EvaluationPoint ── EvaluationRecord
        │
        └─ TermReport ── User (student)

PartnerType ←── PartnerActivity ←── ActivityParticipant ←── User (student)

---

3. 索引策略

表	索引	用途
users	(schoolId, role)	角色筛选
users	(classId)	班级学生列表
evaluation_records	(schoolId, termId)	学期评价查询
evaluation_records	(studentId, evaluatedAt)	学生历史轨迹
evaluation_records	(evaluatorId)	教师评价统计
parent_notices	(schoolId, publishedAt)	通知列表
audit_logs	(schoolId, createdAt)	审计查询
audit_logs	(action, createdAt)	行为分析

---

4. 软删除策略

isDeleted: Boolean @default(false) 字段： - evaluation_records 用软删除（保留历史可查） - users 用 isActive: Boolean（黑名单机制） - parent_notices 物理删除（避免通知泄露）

---

5. 迁移与种子数据

5.1 初始迁移（Codex M0 D3 任务）

npx prisma migrate dev --name init_v1

5.2 种子数据（M0 D3 一并提交）

• schools：1 条（长沙市一中大泽湖学校）
• terms：当前学期 + 上下学期
• evaluation_points：30 条（5 维 × 6）
• partner_types：10 条
• users：初始账号（admin / 校长 / 教务 / 班主任 / 学生 30+ 家长 30+）

5.3 miniapp 数据迁移（Codex M1 W4 任务）

ETL 工具 v1（M0 D5 ready）→ M1 W4 联调跑数据： - miniapp 的 score_records → evaluation_records - miniapp 的 students → users (role=student) - miniapp 的 parents → users (role=parent) + user_relations

---

6. 未来扩展（V2 暂不做，仅记录）

• 多租户：School 上加 tenantId
• 选课走班：加 course / course_selection
• 综合素质雷达图缓存：加 student_radar_cache
• AI 诊断：加 ai_diagnosis_reports
• 学时统计：加 course_hours

---

Why / How to apply

Why：M0 D1 一次性出全 3 份关键文档之一，Codex D3 数据模型实现时直接按 schema 落地。

How to apply： - Codex 复制 schema.prisma 到 server/prisma/schema.prisma - 跑 npx prisma format 格式化 - 跑 npx prisma migrate dev 生成迁移 - 写 seed.ts 跑种子数据 - 用 prisma studio 验证表结构 - 任何字段修改需回写本文件