dfmsolutions/schulos
1
0 Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
v0/cetinmustafa83-3947-0d220a61
schulos/prisma/schema.prisma
T

3787 lines
142 KiB
Plaintext
Raw Permalink Blame History

// CompetenceTrack — Full Database Schema
// SQLite via Prisma ORM | UUIDs for portability | UTC timestamps
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
// ─── Core Organizational Entities ───────────────────────────────────
model School {
id String @id @default(cuid())
name String
schoolType String // ELEMENTARY, MIDDLE, GYMNASIUM, OTHER
country String @default("DE")
timezone String @default("Europe/Berlin")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
// School branding fields
logoUrl String? // school logo URL
primaryColor String? @default("#10b981") // primary brand color
secondaryColor String? @default("#14b8a6") // secondary brand color
accentColor String? @default("#059669") // accent brand color
fontFamily String? @default("Inter") // preferred font
customCss String? // custom CSS overrides
motto String? // school motto
websiteUrl String? // school website URL
emailDomain String? // school email domain (e.g., "@schule.de")
address String? // school address
phone String? // school phone number
users User[]
schoolYears SchoolYear[]
classGroups ClassGroup[]
students Student[]
subjects Subject[]
competencyTemplates CompetencyTemplate[]
gradingSchemes GradingScheme[]
auditLogs AuditLog[]
dataExportRequests DataExportRequest[]
behaviorCategories BehaviorCategory[]
behaviorIncidents BehaviorIncident[]
rubrics Rubric[]
commentCategories CommentCategory[]
commentBankEntries CommentBankEntry[]
notebooks Notebook[]
drawings Drawing[]
notifications Notification[]
calendarEvents CalendarEvent[]
parentStudentLinks ParentStudentLink[]
curriculumStandards CurriculumStandard[]
backups Backup[]
behaviorInterventions BehaviorIntervention[]
reportSchedules ReportSchedule[]
reportCardTemplates ReportCardTemplate[]
homeworks Homework[]
announcements Announcement[]
notificationPreferences NotificationPreferences[]
selfAssessments SelfAssessment[]
learningGoals LearningGoal[]
portfolioEntries PortfolioEntry[]
timetableSlots TimetableSlot[]
resources Resource[]
emailTemplates EmailTemplate[]
emailLogs EmailLog[]
peerAssessments PeerAssessment[]
peerAssessmentSessions PeerAssessmentSession[]
emergencyContacts EmergencyContact[]
schoolEvents SchoolEvent[]
studentTransports StudentTransport[]
transportRoutes TransportRoute[]
healthRecords HealthRecord[]
badges Badge[]
studentBadges StudentBadge[]
newsletters Newsletter[]
competitions Competition[]
rewardClaims RewardClaim[]
rewards Reward[]
rewardPoints RewardPoints[]
subjectTopics SubjectTopic[]
district SchoolDistrict? @relation(fields: [districtId], references: [id])
districtId String?
// New features
subjectCategories SubjectCategory[]
subjectContents SubjectContent[]
contentChangeRequests ContentChangeRequest[]
aiSettings AISettings?
virtualCharacters VirtualCharacter[]
illnessReports IllnessReport[]
counselingAppointments CounselingAppointment[]
disciplinaryCommittees DisciplinaryCommittee[]
communicationRooms CommunicationRoom[]
gradeReports GradeReport[]
aiTestGenerations AITestGeneration[]
gradingReviews TeacherGradingReview[]
gradingAnnotations GradingAnnotation[]
chatMessages ChatMessage[]
examPlans ExamPlan[]
studyPlans StudyPlan[]
studySessions StudySession[]
libraryBooks LibraryBook[]
bookCheckouts BookCheckout[]
bookReservations BookReservation[]
seatingCharts SeatingChart[]
dataImportJobs DataImportJob[]
dataExportJobs DataExportJob[]
wellnessCheckins WellnessCheckin[]
wellnessScores WellnessScore[]
careerProfiles CareerProfile[]
careerAppointments CareerAppointment[]
substituteTeachers SubstituteTeacher[]
teacherAbsences TeacherAbsence[]
substitutionAssignments SubstitutionAssignment[]
// Module L compliance relations
dataRetentionPolicies DataRetentionPolicy[]
dpiaRecords DpiaRecord[]
complianceConsultations ComplianceConsultation[]
complianceStatus SchoolComplianceStatus?
deletionFlags DeletionFlag[]
// Module B signage relations
emergencySignages EmergencySignage[]
signageMessages SignageMessage[]
signageSchedules SignageSchedule[]
signageAuditLogs SignageAuditLog[]
// Module F exam mode relations
examSessions ExamSession[]
// Module H notifications relations
notificationHubs NotificationHub[]
notificationArchives NotificationArchive[]
// Module J widgets relations
dashboardWidgets DashboardWidget[]
dashboardLayouts DashboardLayout[]
widgetTemplates WidgetTemplate[]
// Module C escalation relations
escalationPolicies EscalationPolicy[]
escalations Escalation[]
escalationMessageTemplates EscalationMessageTemplate[]
}
model User {
id String @id @default(cuid())
schoolId String?
email String @unique
passwordHash String
firstName String
lastName String
role String @default("TEACHER") // TEACHER, SCHOOL_ADMIN, SUPER_ADMIN, STUDENT, PARENT, VICE_PRINCIPAL, DPO
locale String @default("de")
twoFactorSecret String?
isDemo Boolean @default(false)
isTeacher Boolean @default(false) // civil-servant status for Personalrat rights
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School? @relation(fields: [schoolId], references: [id])
classGroupTeachers ClassGroupTeacher[]
learningProgressEntries LearningProgressEntry[]
assessments Assessment[]
generatedReports Report[]
auditLogs AuditLog[]
dataExportRequests DataExportRequest[]
teacherNotes TeacherNote[]
attendanceSessions AttendanceSession[]
lessonPlans LessonPlan[]
parentMessagesSent ParentMessage[]
behaviorIncidentsLogged BehaviorIncident[] @relation("BehaviorLogger")
behaviorIncidentsResolved BehaviorIncident[] @relation("BehaviorResolver")
rubrics Rubric[]
commentBankEntries CommentBankEntry[]
notebooks Notebook[]
drawings Drawing[]
notifications Notification[]
calendarEvents CalendarEvent[]
parentLinks ParentStudentLink[]
assignedInterventions BehaviorIntervention[] @relation("InterventionAssignee")
homeworks Homework[]
announcements Announcement[]
notificationPreferences NotificationPreferences?
timetableSlots TimetableSlot[]
resources Resource[]
organizedSchoolEvents SchoolEvent[]
eventRegistrations EventRegistration[]
eventFeedbacks EventFeedback[]
awardedBadges StudentBadge[]
newsletters Newsletter[]
competitionsCreated Competition[]
competitionParticipations CompetitionParticipant[]
competitionClaims RewardClaim[]
rewardRedemptions RewardRedemption[]
rewardPoints RewardPoints[]
studentAnswers StudentAnswer[]
student Student?
// New features
contentChangeRequests ContentChangeRequest[] @relation("ContentRequester")
contentChangeReviews ContentChangeRequest[] @relation("ContentReviewer")
chatMessages ChatMessage[]
illnessReports IllnessReport[]
illnessReportApprovals IllnessReport[] @relation("IllnessApprover")
communicationRoomMembers CommunicationRoomMember[]
communicationMessages CommunicationMessage[]
counselingAppointments CounselingAppointment[] @relation("Counselor")
counselingStudentAppts CounselingAppointment[] @relation("CounselingStudent")
disciplinaryCasesReported DisciplinaryCase[] @relation("CaseReporter")
disciplinaryCasesReviewed DisciplinaryCase[] @relation("CaseReviewer")
disciplinaryCommitteeMembers DisciplinaryCommitteeMember[]
virtualCharacter VirtualCharacter?
aiTestGenerations AITestGeneration[]
gradingReviews TeacherGradingReview[]
gradingReviewComments GradingReviewComment[]
gradingAnnotations GradingAnnotation[]
announcementReads AnnouncementRead[]
selectedCharacterId String?
// Communication room relations
roomStudent CommunicationRoom[] @relation("RoomStudent")
roomTeacher CommunicationRoom[] @relation("RoomTeacher")
// Class responsible teacher
responsibleClasses ClassGroup[] @relation("ResponsibleTeacher")
examPlans ExamPlan[]
seatingCharts SeatingChart[]
peerAssessmentSessions PeerAssessmentSession[] @relation("PeerAssessmentTeacher")
bookCheckouts BookCheckout[]
gradeReports GradeReport[] @relation("GradeReportGenerator")
bookReservations BookReservation[]
dataImportJobs DataImportJob[]
dataExportJobs DataExportJob[]
careerAppointments CareerAppointment[]
substituteTeacher SubstituteTeacher?
teacherAbsences TeacherAbsence[]
// Module L compliance relations
retentionPoliciesReviewed DataRetentionPolicy[] @relation("DataRetentionPolicyReviewer")
dpiaRecordsApproved DpiaRecord[] @relation("DpiaRecordApprover")
complianceConsultationsAcknowledged ComplianceConsultation[] @relation("ComplianceConsultationAcknowledger")
deletionFlagsApproved DeletionFlag[] @relation("DeletionFlagApprover")
// Module B signage relations
signageMessagesCreated SignageMessage[]
// Module H notifications relations
notificationHubs NotificationHub[]
notificationArchives NotificationArchive[]
// Module J widgets relations
dashboardWidgets DashboardWidget[]
dashboardLayouts DashboardLayout[]
// Module C escalation relations
escalationsInitiated Escalation[] @relation("EscalationInitiator")
escalationLogsPerformed EscalationLog[] @relation("EscalationLogPerformer")
}
model SchoolYear {
id String @id @default(cuid())
schoolId String
label String // e.g. "2025/2026"
startDate DateTime
endDate DateTime
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
classGroups ClassGroup[]
enrollments Enrollment[]
computedGrades ComputedGrade[]
reports Report[]
}
model ClassGroup {
id String @id @default(cuid())
schoolId String
schoolYearId String
name String // e.g. "3a"
gradeLevel Int
schoolType String @default("ELEMENTARY")
seatingOrder String? // JSON array of student IDs
responsibleTeacherId String? // Class responsible teacher (Klassenlehrer)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
schoolYear SchoolYear @relation(fields: [schoolYearId], references: [id])
responsibleTeacher User? @relation("ResponsibleTeacher", fields: [responsibleTeacherId], references: [id])
teachers ClassGroupTeacher[]
enrollments Enrollment[]
classCompetencyAssignments ClassCompetencyAssignment[]
assessments Assessment[]
learningProgressEntries LearningProgressEntry[]
gradingSchemes GradingScheme[]
computedGrades ComputedGrade[]
reports Report[]
attendanceSessions AttendanceSession[]
lessonPlans LessonPlan[]
behaviorIncidents BehaviorIncident[]
notebooks Notebook[]
drawings Drawing[]
calendarEvents CalendarEvent[]
reportSchedules ReportSchedule[]
homeworks Homework[]
announcements Announcement[]
selfAssessments SelfAssessment[]
timetableSlots TimetableSlot[]
resources Resource[]
peerAssessments PeerAssessment[]
peerAssessmentSessions PeerAssessmentSession[] @relation("PeerAssessmentClassGroup")
schoolEvents SchoolEvent[]
aiTestGenerations AITestGeneration[]
examPlans ExamPlan[]
seatingCharts SeatingChart[]
substitutionAssignments SubstitutionAssignment[]
}
model ClassGroupTeacher {
id String @id @default(cuid())
classGroupId String
userId String
role String @default("SUBJECT_TEACHER") // HOMEROOM_TEACHER, SUBJECT_TEACHER
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
user User @relation(fields: [userId], references: [id])
@@unique([classGroupId, userId])
}
model Student {
id String @id @default(cuid())
schoolId String
firstName String
lastName String
dateOfBirth DateTime?
externalId String? // for import matching
avatarUrl String? // student photo/avatar URL
avatarInitials String? // computed from firstName + lastName first chars
userId String? @unique // optional link to User account (for student self-service)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
user User? @relation(fields: [userId], references: [id])
school School @relation(fields: [schoolId], references: [id])
enrollments Enrollment[]
learningProgressEntries LearningProgressEntry[]
assessmentResults AssessmentResult[]
computedGrades ComputedGrade[]
reports Report[]
teacherNotes TeacherNote[]
attendanceRecords AttendanceRecord[]
parentContacts ParentContact[]
parentMessages ParentMessage[]
behaviorIncidents BehaviorIncident[]
behaviorInterventions BehaviorIntervention[]
parentStudentLinks ParentStudentLink[]
homeworkSubmissions HomeworkSubmission[]
selfAssessments SelfAssessment[]
learningGoals LearningGoal[]
portfolioEntries PortfolioEntry[]
peerAssessmentsGiven PeerAssessment[] @relation("PeerAssessor")
peerAssessmentsReceived PeerAssessment[] @relation("PeerAssessed")
emergencyContacts EmergencyContact[]
transports StudentTransport[]
healthRecords HealthRecord[]
studentBadges StudentBadge[]
goals StudentGoal[]
illnessReports IllnessReport[]
disciplinaryCases DisciplinaryCase[]
studyPlans StudyPlan[]
studySessions StudySession[]
gradingAnnotations GradingAnnotation[]
bookCheckouts BookCheckout[]
bookReservations BookReservation[]
wellnessCheckins WellnessCheckin[]
wellnessScores WellnessScore[]
careerProfile CareerProfile?
// Module F exam mode relations
examSessions ExamSession[]
@@index([userId])
}
model Enrollment {
id String @id @default(cuid())
studentId String
classGroupId String
schoolYearId String
startDate DateTime
endDate DateTime? // null = still enrolled
student Student @relation(fields: [studentId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
schoolYear SchoolYear @relation(fields: [schoolYearId], references: [id])
}
// ─── Subject & Competency Structure ─────────────────────────────��──
model Subject {
id String @id @default(cuid())
schoolId String? // null = global/template
name String
gradeLevelMin Int @default(1)
gradeLevelMax Int @default(13)
school School? @relation(fields: [schoolId], references: [id])
competencyTemplates CompetencyTemplate[]
assessments Assessment[]
classCompetencyAssignments ClassCompetencyAssignment[]
gradingSchemes GradingScheme[]
computedGrades ComputedGrade[]
attendanceSessions AttendanceSession[]
lessonPlans LessonPlan[]
rubrics Rubric[]
commentBankEntries CommentBankEntry[]
notebooks Notebook[]
drawings Drawing[]
calendarEvents CalendarEvent[]
curriculumStandards CurriculumStandard[]
homeworks Homework[]
timetableSlots TimetableSlot[]
resources Resource[]
topics SubjectTopic[]
competitions Competition[]
subjectContents SubjectContent[]
aiTestGenerations AITestGeneration[]
examPlans ExamPlan[]
studyPlans StudyPlan[]
studySessions StudySession[]
substitutionAssignments SubstitutionAssignment[]
}
model CompetencyTemplate {
id String @id @default(cuid())
name String
description String?
subjectId String?
schoolType String @default("ELEMENTARY")
gradeLevelMin Int @default(1)
gradeLevelMax Int @default(4)
isGlobalTemplate Boolean @default(false)
createdByUserId String?
version Int @default(1)
schoolId String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
subject Subject? @relation(fields: [subjectId], references: [id])
school School? @relation(fields: [schoolId], references: [id])
categories CompetencyCategory[]
classCompetencyAssignments ClassCompetencyAssignment[]
}
model CompetencyCategory {
id String @id @default(cuid())
competencyTemplateId String
name String
order Int @default(0)
color String? // hex color for chart petals
competencyTemplate CompetencyTemplate @relation(fields: [competencyTemplateId], references: [id])
competencies Competency[]
reportSections ReportSection[]
}
model Competency {
id String @id @default(cuid())
categoryId String
code String // e.g. "D.3.LES.01"
title String
description String?
order Int @default(0)
category CompetencyCategory @relation(fields: [categoryId], references: [id])
masteryLevelDefinitions MasteryLevelDefinition[]
learningProgressEntries LearningProgressEntry[]
assessmentCompetencyLinks AssessmentCompetencyLink[]
curriculumStandardLinks CurriculumStandardLink[]
selfAssessments SelfAssessment[]
learningGoals LearningGoal[]
portfolioEntries PortfolioEntry[]
peerAssessments PeerAssessment[]
}
model MasteryLevelDefinition {
id String @id @default(cuid())
competencyId String
levelValue Int // e.g. 1-4
label String // e.g. "noch nicht erreicht", "entwickelt sich", "kompetent", "weit entwickelt"
description String?
competency Competency @relation(fields: [competencyId], references: [id])
}
model ClassCompetencyAssignment {
id String @id @default(cuid())
classGroupId String
subjectId String
competencyTemplateId String
schoolYearId String
clonedTemplateId String? // if customized, points to the school's cloned copy
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
subject Subject @relation(fields: [subjectId], references: [id])
competencyTemplate CompetencyTemplate @relation(fields: [competencyTemplateId], references: [id])
@@unique([classGroupId, subjectId, schoolYearId])
}
// ─── Progress Tracking & Assessment ────────────────────────────────
model LearningProgressEntry {
id String @id @default(cuid())
studentId String
competencyId String
teacherId String
classGroupId String
date DateTime
masteryLevelValue Int
note String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
student Student @relation(fields: [studentId], references: [id])
competency Competency @relation(fields: [competencyId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
}
model Assessment {
id String @id @default(cuid())
classGroupId String
subjectId String
teacherId String
title String
date DateTime
type String @default("TEST") // TEST, ORAL, PROJECT, HOMEWORK, OTHER
maxScore Float?
weight Float @default(1.0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
subject Subject @relation(fields: [subjectId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
assessmentCompetencyLinks AssessmentCompetencyLink[]
assessmentResults AssessmentResult[]
aiTestGenerations AITestGeneration[]
gradingReviews TeacherGradingReview[]
gradingAnnotations GradingAnnotation[]
examPlan ExamPlan?
// Module F exam mode relations
examSessions ExamSession[]
}
model AssessmentCompetencyLink {
id String @id @default(cuid())
assessmentId String
competencyId String
weight Float @default(1.0)
assessment Assessment @relation(fields: [assessmentId], references: [id])
competency Competency @relation(fields: [competencyId], references: [id])
@@unique([assessmentId, competencyId])
}
model AssessmentResult {
id String @id @default(cuid())
assessmentId String
studentId String
score Float?
masteryLevelValue Int?
note String?
annotationData String? // JSON: drawing annotation strokes for teacher grading
annotationImage String? // base64 PNG thumbnail of annotation
assessment Assessment @relation(fields: [assessmentId], references: [id])
student Student @relation(fields: [studentId], references: [id])
annotations GradingAnnotation[]
@@unique([assessmentId, studentId])
}
// ─── Grading ───────────────────────────────────────────────────────
model GradingScheme {
id String @id @default(cuid())
classGroupId String?
subjectId String?
schoolId String?
name String
type String @default("NUMERIC_GRADE") // NUMERIC_GRADE, VERBAL_FEEDBACK, COMBINED
scaleDefinition String // JSON: e.g. {"min":1,"max":6,"labels":{"1":"sehr gut","6":"ungenügend"}}
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
school School? @relation(fields: [schoolId], references: [id])
gradingWeightRules GradingWeightRule[]
}
model GradingWeightRule {
id String @id @default(cuid())
gradingSchemeId String
sourceType String // LEARNING_PROGRESS, ASSESSMENT
targetRef String? // competencyId, assessmentId, or categoryId
weightPercent Float @default(50.0)
gradingScheme GradingScheme @relation(fields: [gradingSchemeId], references: [id])
}
model ComputedGrade {
id String @id @default(cuid())
studentId String
subjectId String
classGroupId String
schoolYearId String
period String @default("Semester 1")
computedValue Float
computedAt DateTime @default(now())
isFinalized Boolean @default(false)
overriddenValue Float?
overrideReason String?
student Student @relation(fields: [studentId], references: [id])
subject Subject @relation(fields: [subjectId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
schoolYear SchoolYear @relation(fields: [schoolYearId], references: [id])
}
// ─── Reporting ─────────────────────────────────────────────────────
model Report {
id String @id @default(cuid())
studentId String
classGroupId String
schoolYearId String
period String
generatedByUserId String
generatedAt DateTime @default(now())
status String @default("DRAFT") // DRAFT, REVIEW, PUBLISHED, ARCHIVED, FINAL
pdfFilePath String?
includesGrades Boolean @default(false)
// Report card extended fields
teacherComments String? // teacher comments text
attendanceSummary String? // JSON: { present: N, absent: N, excused: N, late: N, total: N }
overallAssessment String? // overall assessment text
templateId String? // report card template reference
reviewedByUserId String? // reviewer user ID
reviewedAt DateTime? // review timestamp
publishedAt DateTime? // publish timestamp
student Student @relation(fields: [studentId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
schoolYear SchoolYear @relation(fields: [schoolYearId], references: [id])
generatedByUser User @relation(fields: [generatedByUserId], references: [id])
sections ReportSection[]
template ReportCardTemplate? @relation(fields: [templateId], references: [id])
}
model ReportCardTemplate {
id String @id @default(cuid())
schoolId String
name String
description String?
sections String @default("[]") // JSON: array of section definitions
gradingScale String @default("{}")
layout String @default("default") // default, detailed, compact
isDefault Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
reports Report[]
}
model ReportSection {
id String @id @default(cuid())
reportId String
competencyCategoryId String?
generatedText String
order Int @default(0)
report Report @relation(fields: [reportId], references: [id])
competencyCategory CompetencyCategory? @relation(fields: [competencyCategoryId], references: [id])
}
// ─── Auditing & Compliance ─────────────────────────────────────────
model AuditLog {
id String @id @default(cuid())
userId String?
schoolId String?
action String
entityType String
entityId String?
changes String? // JSON: { before: {...}, after: {...} }
ipAddress String?
userAgent String?
timestamp DateTime @default(now())
metadata String? // JSON
dataSubject String? // track which student (if applicable) for GDPR export scope
user User? @relation(fields: [userId], references: [id])
school School? @relation(fields: [schoolId], references: [id])
@@index([dataSubject])
@@index([timestamp])
}
model DataExportRequest {
id String @id @default(cuid())
requestedByUserId String
schoolId String?
scope String // STUDENT, CLASS, SCHOOL
scopeId String? // studentId, classGroupId, or schoolId
status String @default("PENDING") // PENDING, PROCESSING, COMPLETED, FAILED
filePath String?
requestedAt DateTime @default(now())
completedAt DateTime?
requestedByUser User @relation(fields: [requestedByUserId], references: [id])
school School? @relation(fields: [schoolId], references: [id])
}
// ─── Attendance Tracking ────────────────────────────────────────────
model AttendanceSession {
id String @id @default(cuid())
classGroupId String
teacherId String
date DateTime
subjectId String?
period String? // e.g. "1. Stunde", "Morning"
status String @default("OPEN") // OPEN, COMPLETED
note String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
records AttendanceRecord[]
@@index([classGroupId, date])
}
model AttendanceRecord {
id String @id @default(cuid())
sessionId String
studentId String
status String @default("PRESENT") // PRESENT, ABSENT, EXCUSED, LATE
arrivalTime String? // for LATE status, e.g. "08:25"
comment String?
session AttendanceSession @relation(fields: [sessionId], references: [id])
student Student @relation(fields: [studentId], references: [id])
@@unique([sessionId, studentId])
@@index([studentId])
}
// ─── Teacher Notes ─────────────────────────────────────────────────
model TeacherNote {
id String @id @default(cuid())
studentId String
teacherId String
category String // GENERAL, BEHAVIOR, ACADEMIC, INTERVENTION, PARENT_CONTACT
content String
isPrivate Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
student Student @relation(fields: [studentId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
@@index([studentId])
@@index([teacherId])
}
// ─── Lesson Planning ────────────────────────────────────────────────
model LessonPlan {
id String @id @default(cuid())
teacherId String
classGroupId String
subjectId String?
title String
description String?
date DateTime
durationMin Int @default(45)
status String @default("draft") // draft|scheduled|completed|cancelled
objectives String? // JSON array of strings
materials String? // JSON array of strings
homework String?
reflection String?
linkedCompetencyIds String? // JSON array of competency IDs
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
teacher User @relation(fields: [teacherId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
@@index([teacherId])
@@index([classGroupId])
@@index([date])
}
// ─── Parent Communication ──────────────────────────────────────────
model ParentContact {
id String @id @default(cuid())
studentId String
firstName String
lastName String
email String?
phone String?
relationship String @default("parent") // parent|guardian|emergency
preferredContact String @default("email") // email|phone|both
preferredLanguage String @default("de")
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
student Student @relation(fields: [studentId], references: [id])
messages ParentMessage[]
@@index([studentId])
@@unique([studentId, email])
}
model ParentMessage {
id String @id @default(cuid())
parentId String
teacherId String
studentId String
subject String
body String
category String @default("general") // general|progress|assessment|behavior|attendance|event
priority String @default("normal") // low|normal|high|urgent
status String @default("sent") // draft|sent|delivered|read|replied
readAt DateTime?
reply String?
replyAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
parent ParentContact @relation(fields: [parentId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
student Student @relation(fields: [studentId], references: [id])
@@index([teacherId])
@@index([parentId])
@@index([studentId])
@@index([createdAt])
}
// ─── Behavior Tracking ─────────────────────────────────────────────
model BehaviorCategory {
id String @id @default(cuid())
schoolId String
school School @relation(fields: [schoolId], references: [id])
name String
color String // hex color, e.g. "#10b981"
valence String // "positive" | "negative" | "neutral"
icon String? // emoji
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
incidents BehaviorIncident[]
@@unique([schoolId, name])
@@index([schoolId])
}
model BehaviorIncident {
id String @id @default(cuid())
studentId String
student Student @relation(fields: [studentId], references: [id])
teacherId String
teacher User @relation("BehaviorLogger", fields: [teacherId], references: [id])
classGroupId String?
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
schoolId String
school School @relation(fields: [schoolId], references: [id])
categoryId String
category BehaviorCategory @relation(fields: [categoryId], references: [id])
date DateTime
severity String // "minor" | "moderate" | "major"
description String
location String? // e.g., "Klassenzimmer", "Pausenhof", "Flur"
followUpAction String? // e.g., "Elterngespräch", "Nachsitzen", "Lob im Kreis"
resolved Boolean @default(false)
resolvedAt DateTime?
resolvedById String?
resolvedBy User? @relation("BehaviorResolver", fields: [resolvedById], references: [id])
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([studentId, date])
@@index([classGroupId, date])
@@index([schoolId, date])
@@index([teacherId])
interventions BehaviorIntervention[]
}
// ─── Rubric Library ────────────────────────────────────────────────
model Rubric {
id String @id @default(cuid())
schoolId String
school School @relation(fields: [schoolId], references: [id])
teacherId String
teacher User @relation(fields: [teacherId], references: [id])
subjectId String?
subject Subject? @relation(fields: [subjectId], references: [id])
title String
description String?
type String @default("ANALYTIC") // ANALYTIC | HOLISTIC
maxPoints Int @default(100)
isPublic Boolean @default(false) // shared across school
version Int @default(1)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
criteria RubricCriterion[]
peerAssessments PeerAssessment[]
@@index([schoolId])
@@index([teacherId])
@@index([subjectId])
}
model RubricCriterion {
id String @id @default(cuid())
rubricId String
rubric Rubric @relation(fields: [rubricId], references: [id], onDelete: Cascade)
name String // e.g., "Inhalt", "Sprache", "Darstellung"
description String?
weight Float @default(1.0) // relative weight for this criterion
maxPoints Int // max points for this criterion
order Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
levels RubricLevel[]
@@index([rubricId])
}
model RubricLevel {
id String @id @default(cuid())
criterionId String
criterion RubricCriterion @relation(fields: [criterionId], references: [id], onDelete: Cascade)
label String // e.g., "Sehr gut", "Gut", "Befriedigend", "Ausreichend"
description String // descriptor for this level
points Int // points for this level
order Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([criterionId])
}
// ─── Report Card Comments Bank ────────────────────────────────────
model CommentCategory {
id String @id @default(cuid())
schoolId String
school School @relation(fields: [schoolId], references: [id])
name String
color String // hex color
icon String? // emoji
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
comments CommentBankEntry[]
@@unique([schoolId, name])
@@index([schoolId])
}
model CommentBankEntry {
id String @id @default(cuid())
schoolId String
school School @relation(fields: [schoolId], references: [id])
teacherId String
teacher User @relation(fields: [teacherId], references: [id])
categoryId String
category CommentCategory @relation(fields: [categoryId], references: [id])
subjectId String?
subject Subject? @relation(fields: [subjectId], references: [id])
title String // short title
text String // the full comment text
gradeLevel String? // e.g., "3", "3-4", "all" — which grade levels this applies to
schoolType String? // e.g., "ELEMENTARY", "SECONDARY"
isPublic Boolean @default(false) // shared across school
usageCount Int @default(0) // how many times used
tags String? // comma-separated tags for search
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([schoolId])
@@index([teacherId])
@@index([categoryId])
@@index([subjectId])
}
// ─── Digital Notebooks ─────────────────────────────────────────────
model Notebook {
id String @id @default(cuid())
schoolId String
ownerId String // userId of the student or teacher
ownerType String // STUDENT | TEACHER
subjectId String? // null = personal/general notebook
classGroupId String?
title String
description String?
notebookType String @default("lined") // lined | grid | blank | dotted | music | calligraphy
color String @default("#10b981") // cover color
icon String? // lucide icon name
isArchived Boolean @default(false)
isPublic Boolean @default(false) // shared with class
sortOrder Int @default(0)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
owner User @relation(fields: [ownerId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
pages NotebookPage[]
@@index([schoolId])
@@index([ownerId])
@@index([subjectId])
@@index([classGroupId])
@@index([isArchived])
}
model NotebookPage {
id String @id @default(cuid())
notebookId String
pageNumber Int
title String?
contentType String @default("text") // text | drawing | mixed | quiz | exercise | flashcard
textContent String? // markdown/rich text content
drawingData String? // JSON with strokes/paths for canvas
background String @default("lined") // lined | grid | blank | dotted | music
isBookmark Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
notebook Notebook @relation(fields: [notebookId], references: [id], onDelete: Cascade)
versions NotebookPageVersion[]
@@index([notebookId])
@@index([pageNumber])
}
model NotebookPageVersion {
id String @id @default(cuid())
pageId String
version Int
textContent String?
drawingData String?
editedBy String?
editSummary String?
createdAt DateTime @default(now())
page NotebookPage @relation(fields: [pageId], references: [id], onDelete: Cascade)
@@index([pageId, version])
@@map("notebook_page_versions")
}
// ─── Drawing Canvas ────────────────────────────────────────────────
model Drawing {
id String @id @default(cuid())
schoolId String
ownerId String
title String
description String?
drawingData String // JSON with all strokes, paths, colors
imageData String? // base64 encoded PNG thumbnail
subjectId String?
classGroupId String?
isPublic Boolean @default(false)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
owner User @relation(fields: [ownerId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
@@index([schoolId])
@@index([ownerId])
@@index([subjectId])
}
model Notification {
id String @id @default(cuid())
schoolId String
userId String // recipient
type String // ASSESSMENT_DUE, MISSING_OBSERVATION, NOTEBOOK_SHARED, BEHAVIOR_ALERT, GRADE_COMPUTED, etc.
category String @default("academic") // academic, behavioral, administrative, calendar, communication, system
priority String @default("normal") // urgent, high, normal, low
title String
message String
isRead Boolean @default(false)
isArchived Boolean @default(false)
actionUrl String? // optional deep link
relatedId String? // optional related entity ID
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([userId, isRead])
@@index([schoolId])
@@index([category])
@@index([priority])
@@map("notifications")
}
// ─── Calendar Events ──────────────────────────────────────────────
model CalendarEvent {
id String @id @default(cuid())
schoolId String
teacherId String
title String
date DateTime
startTime String? // HH:mm format, null = all day
endTime String? // HH:mm format, null = all day
eventType String @default("reminder") // assessment | lesson | reminder | exam
subjectId String?
classGroupId String?
notes String?
allDay Boolean @default(false)
recurrencePattern String? // JSON: { type: "daily|weekly|monthly|yearly", interval: 1, endDate?: string, daysOfWeek?: number[] }
recurrenceEnd DateTime? // When recurrence ends
parentEventId String? // For generated instances of a recurring event
assessmentId String? // Linked assessment (for exam events)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
parentEvent CalendarEvent? @relation("EventRecurrence", fields: [parentEventId], references: [id])
childEvents CalendarEvent[] @relation("EventRecurrence")
examPlan ExamPlan?
@@index([schoolId])
@@index([teacherId])
@@index([date])
@@index([subjectId])
@@index([classGroupId])
@@index([parentEventId])
@@index([eventType])
}
// ─── Parent-Student Link ──────────────────────────────────────────────
model ParentStudentLink {
id String @id @default(cuid())
parentId String // User with PARENT role
studentId String // Student record
schoolId String
relationship String? // mother, father, guardian, etc.
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
parent User @relation(fields: [parentId], references: [id])
student Student @relation(fields: [studentId], references: [id])
school School @relation(fields: [schoolId], references: [id])
@@unique([parentId, studentId])
@@map("parent_student_links")
}
// ─── Curriculum Standards ──────────────────────────────────────────
model CurriculumStandard {
id String @id @default(cuid())
schoolId String
subjectId String?
code String // e.g., "M.1.1", "D.2.3"
title String
description String?
gradeLevel Int?
category String? // e.g., "Number", "Algebra", "Geometry"
source String? // e.g., "KMK", "Lehrplan NRW"
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
competencyLinks CurriculumStandardLink[]
@@index([schoolId, subjectId])
@@map("curriculum_standards")
}
model CurriculumStandardLink {
id String @id @default(cuid())
standardId String
competencyId String
coverageLevel String? // "full", "partial", "related"
notes String?
createdAt DateTime @default(now())
standard CurriculumStandard @relation(fields: [standardId], references: [id], onDelete: Cascade)
competency Competency @relation(fields: [competencyId], references: [id], onDelete: Cascade)
@@unique([standardId, competencyId])
@@map("curriculum_standard_links")
}
// ─── Data Backup ─────────────────────────────────────────────────────
model Backup {
id String @id @default(cuid())
schoolId String
filename String
size Int // bytes
type String // "full", "incremental"
status String // "pending", "completed", "failed"
notes String?
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id], onDelete: Cascade)
@@map("backups")
}
// ──�� Behavior Interventions ─────────────────────────────────────────
model BehaviorIntervention {
id String @id @default(cuid())
schoolId String
studentId String
incidentId String?
type String // "warning", "meeting", "action_plan", "support", "parent_contact"
description String
status String @default("planned") // "planned", "in_progress", "completed", "cancelled"
assignedTo String?
startDate DateTime?
endDate DateTime?
outcome String?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
incident BehaviorIncident? @relation(fields: [incidentId], references: [id])
assignedUser User? @relation("InterventionAssignee", fields: [assignedTo], references: [id])
@@index([schoolId, studentId])
@@map("behavior_interventions")
}
// ─── Report Scheduling ──────────────────────────────────────────────
model ReportSchedule {
id String @id @default(cuid())
schoolId String
classGroupId String?
template String // "short", "full", "custom"
frequency String // "weekly", "monthly", "quarterly", "end_of_term"
dayOfWeek Int? // 0-6 for weekly
dayOfMonth Int? // 1-31 for monthly
monthOfYear Int? // 1-12 for quarterly
recipients String? // JSON array of userIds
includeStudents Boolean @default(true)
includeGrades Boolean @default(true)
includeAttendance Boolean @default(true)
includeBehavior Boolean @default(false)
includeCompetencies Boolean @default(true)
isActive Boolean @default(true)
lastRunAt DateTime?
nextRunAt DateTime?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
@@index([schoolId, isActive])
@@map("report_schedules")
}
// ─── School District ────────────────────────────────────────────────
model SchoolDistrict {
id String @id @default(cuid())
name String
code String? // district code
region String?
country String @default("DE")
adminEmail String?
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
schools School[]
@@map("school_districts")
}
// ─── Homework Management ──────────────────────────────────────────
model Homework {
id String @id @default(cuid())
schoolId String
classGroupId String
subjectId String?
teacherId String
title String
description String?
dueDate DateTime
homeworkType String @default("assignment") // assignment, reading, project, practice, research
maxPoints Float?
attachments String? // JSON array of attachment URLs
isPublished Boolean @default(true)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
submissions HomeworkSubmission[]
@@index([schoolId, classGroupId, dueDate])
@@map("homework")
}
model HomeworkSubmission {
id String @id @default(cuid())
homeworkId String
studentId String
content String? // text submission
attachments String? // JSON array of attachment URLs
status String @default("pending") // pending, submitted, graded, late
score Float?
feedback String?
submittedAt DateTime?
gradedAt DateTime?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
homework Homework @relation(fields: [homeworkId], references: [id], onDelete: Cascade)
student Student @relation(fields: [studentId], references: [id])
@@unique([homeworkId, studentId])
@@index([homeworkId, status])
@@map("homework_submissions")
}
// ─── Announcements ────────────────────────────────────────────────
model Announcement {
id String @id @default(cuid())
schoolId String
authorId String
title String
content String
priority String @default("normal") // low, normal, high, urgent
announcementType String @default("general") // general, urgent, event, holiday, exam, deadline
targetAudience String @default("all") // all, teachers, students, parents, class
classGroupId String? // if target is specific class
isPinned Boolean @default(false)
expiresAt DateTime?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
author User @relation(fields: [authorId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
reads AnnouncementRead[]
@@index([schoolId, createdAt])
@@map("announcements")
}
model AnnouncementRead {
id String @id @default(cuid())
announcementId String
userId String
readAt DateTime @default(now())
announcement Announcement @relation(fields: [announcementId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id])
@@unique([announcementId, userId])
@@index([announcementId])
@@index([userId])
@@map("announcement_reads")
}
model NotificationPreferences {
id String @id @default(cuid())
userId String @unique
schoolId String
academicEnabled Boolean @default(true)
behavioralEnabled Boolean @default(true)
administrativeEnabled Boolean @default(true)
calendarEnabled Boolean @default(true)
communicationEnabled Boolean @default(true)
systemEnabled Boolean @default(true)
quietHoursStart String? // HH:mm format, e.g. "22:00"
quietHoursEnd String? // HH:mm format, e.g. "07:00"
quietHoursEnabled Boolean @default(false)
emailDigestEnabled Boolean @default(false)
emailDigestFrequency String @default("daily") // daily, weekly, never
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
school School @relation(fields: [schoolId], references: [id])
@@index([userId])
@@map("notification_preferences")
}
// ─── Student Self-Assessment ──────────────────────────────────────────
model SelfAssessment {
id String @id @default(cuid())
schoolId String
studentId String
competencyId String
classGroupId String?
selfLevel Int // 1-6 self-assessed mastery
confidence Int? // 1-5 confidence level
reflection String? // student's self-reflection text
evidence String? // what evidence supports this assessment
goalId String? // linked learning goal
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
competency Competency @relation(fields: [competencyId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
goal LearningGoal? @relation(fields: [goalId], references: [id])
@@index([schoolId, studentId])
@@map("self_assessments")
}
// ─── Learning Goals ────────────────────────────────────────────────
model LearningGoal {
id String @id @default(cuid())
schoolId String
studentId String
competencyId String?
title String
description String?
targetLevel Int? // target mastery level
currentLevel Int? // current mastery level
deadline DateTime?
status String @default("active") // active, completed, abandoned
progress Int @default(0) // 0-100
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
competency Competency? @relation(fields: [competencyId], references: [id])
selfAssessments SelfAssessment[]
@@index([schoolId, studentId, status])
@@map("learning_goals")
}
// ─── Portfolio Entries ──────────────────────────────────────────────
model PortfolioEntry {
id String @id @default(cuid())
schoolId String
studentId String
title String
description String?
entryType String // "artwork", "writing", "project", "presentation", "achievement", "reflection"
competencyId String?
content String? // text content or description
mediaUrls String? // JSON array of media URLs
notebookPageId String? // link to notebook page
drawingId String? // link to drawing
isPublic Boolean @default(false)
tags String? // JSON array of tags
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
competency Competency? @relation(fields: [competencyId], references: [id])
@@index([schoolId, studentId])
@@map("portfolio_entries")
}
// ─── Timetable ──────────────────────────────────────────────────────
model TimetableSlot {
id String @id @default(cuid())
schoolId String
classGroupId String
dayOfWeek Int // 0=Monday, 1=Tuesday, ..., 4=Friday
period Int // 1-8 (lesson period)
subjectId String?
teacherId String?
roomId String? // optional room
startTime String // "08:00"
endTime String // "08:45"
isBreak Boolean @default(false)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
teacher User? @relation(fields: [teacherId], references: [id])
@@unique([classGroupId, dayOfWeek, period])
@@index([schoolId, classGroupId])
@@map("timetable_slots")
}
// ─── Digital Resource Library ──────────────────────────────────────
model Resource {
id String @id @default(cuid())
schoolId String
authorId String
title String
description String?
resourceType String // "document", "worksheet", "presentation", "video_link", "image", "link", "audio"
url String? // external URL or file path
content String? // text content
subjectId String?
classGroupId String?
gradeLevel Int?
tags String? // JSON array of tags
isPublic Boolean @default(true)
downloadCount Int @default(0)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
author User @relation(fields: [authorId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
@@index([schoolId, resourceType])
@@map("resources")
}
// ─── Email Templates & Logging ───────────────────────────────────────
model EmailTemplate {
id String @id @default(cuid())
schoolId String
name String // "weekly_report", "assessment_reminder", "behavior_alert", "attendance_notice", "welcome"
subject String
body String // HTML template with {{variables}}
isDefault Boolean @default(false)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
@@unique([schoolId, name])
@@map("email_templates")
}
model EmailLog {
id String @id @default(cuid())
schoolId String
templateId String?
recipientEmail String
recipientName String?
subject String
body String
status String @default("pending") // pending, sent, failed, bounced
sentAt DateTime?
error String?
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
@@index([schoolId, status])
@@map("email_logs")
}
// ─── Peer Assessment ──────────────────────────────────────────────
model PeerAssessmentSession {
id String @id @default(cuid())
schoolId String
teacherId String? // teacher who created the session
classGroupId String?
title String
description String?
assessmentType String @default("competency") // competency, project, presentation, teamwork
criteria String? // JSON: [{ name, description, maxScore }]
anonymityMode String @default("anonymous") // anonymous, semi-anonymous, open
status String @default("active") // active, completed, closed
deadline DateTime?
assignMode String @default("manual") // random, manual, group-based
assignedPairs String? // JSON: [{ assessorId, assessedId }]
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
teacher User? @relation("PeerAssessmentTeacher", fields: [teacherId], references: [id])
classGroup ClassGroup? @relation("PeerAssessmentClassGroup", fields: [classGroupId], references: [id])
peerAssessments PeerAssessment[]
@@index([schoolId, status])
@@index([schoolId, teacherId])
@@map("peer_assessment_sessions")
}
model PeerAssessment {
id String @id @default(cuid())
schoolId String
sessionId String? // link to session
assessorId String // student who is assessing
assessedId String // student being assessed
competencyId String?
classGroupId String?
assessmentType String // "competency", "project", "presentation", "teamwork", "behavior"
criteria String? // JSON: [{ name, score, maxScore }] — individual criterion scores
level Int? // 1-6 rating (overall)
comment String?
rubricId String? // optional rubric reference
isAnonymous Boolean @default(false)
status String @default("pending") // pending, submitted
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
session PeerAssessmentSession? @relation(fields: [sessionId], references: [id])
assessor Student @relation("PeerAssessor", fields: [assessorId], references: [id])
assessed Student @relation("PeerAssessed", fields: [assessedId], references: [id])
competency Competency? @relation(fields: [competencyId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
rubric Rubric? @relation(fields: [rubricId], references: [id])
@@index([schoolId, assessedId])
@@index([schoolId, assessorId])
@@index([sessionId])
@@map("peer_assessments")
}
// ─── Emergency Contacts ───────────────────────────────────────────
model EmergencyContact {
id String @id @default(cuid())
schoolId String
studentId String
name String
relationship String // "mother", "father", "guardian", "grandparent", "other"
phone String
phoneAlt String?
email String?
address String?
isPrimary Boolean @default(false)
priority Int @default(1) // 1=first call, 2=second call, etc.
notes String?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
@@index([schoolId, studentId])
@@map("emergency_contacts")
}
// ─── School Events ────────────────────────────────────────────────
model SchoolEvent {
id String @id @default(cuid())
schoolId String
title String
description String?
eventType String // "assembly", "field_trip", "sports_day", "concert", "fair", "parent_meeting", "graduation", "holiday", "cultural", "academic", "social"
startDate DateTime
endDate DateTime?
location String?
organizerId String?
classGroupId String? // if event is for specific class
isAllSchool Boolean @default(true)
isPublic Boolean @default(true)
requiresRegistration Boolean @default(false)
maxParticipants Int?
notes String?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
budget Float?
registrationDeadline DateTime?
capacity Int?
isRecurring Boolean @default(false)
recurrenceRule String? // JSON: { type: "weekly", interval: 1, days: ["mon","wed"] }
bannerImageUrl String?
status String @default("draft") // draft, published, cancelled, completed
feedbackForm String? // JSON: feedback questions
school School @relation(fields: [schoolId], references: [id])
organizer User? @relation(fields: [organizerId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
registrations EventRegistration[]
feedbacks EventFeedback[]
@@index([schoolId, startDate])
@@map("school_events")
}
model EventRegistration {
id String @id @default(cuid())
eventId String
userId String
status String @default("registered") // registered, cancelled, attended
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event SchoolEvent @relation(fields: [eventId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id])
@@unique([eventId, userId])
@@map("event_registrations")
}
model EventFeedback {
id String @id @default(cuid())
eventId String
userId String
rating Int // 1-5
comment String?
createdAt DateTime @default(now())
event SchoolEvent @relation(fields: [eventId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id])
@@unique([eventId, userId])
@@map("event_feedbacks")
}
// ─── Student Transportation ──────────────────────────────────────
model StudentTransport {
id String @id @default(cuid())
schoolId String
studentId String
transportType String // "bus", "tram", "walk", "car", "bike", "other"
routeNumber String? // bus/tram route number
stopName String? // nearest stop name
pickupTime String? // "07:45"
dropoffTime String? // "15:30"
driverName String? // for car/bus
driverPhone String? // for car/bus
distanceKm Float? // distance in km
routeId String? // link to TransportRoute
notes String?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
route TransportRoute? @relation(fields: [routeId], references: [id])
@@index([schoolId, studentId])
@@map("student_transport")
}
model TransportRoute {
id String @id @default(cuid())
schoolId String
routeNumber String
routeName String
transportType String // bus, tram, train
driverName String?
driverPhone String?
capacity Int @default(40)
isActive Boolean @default(true)
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
stops TransportStop[]
assignments StudentTransport[]
@@index([schoolId])
@@map("transport_route")
}
model TransportStop {
id String @id @default(cuid())
routeId String
stopName String
stopOrder Int
pickupTime String? // "07:45"
dropoffTime String? // "15:30"
address String?
latitude Float?
longitude Float?
createdAt DateTime @default(now())
route TransportRoute @relation(fields: [routeId], references: [id], onDelete: Cascade)
@@index([routeId])
@@map("transport_stop")
}
// ─── Health Records ──────────────────────────────────────────────
model HealthRecord {
id String @id @default(cuid())
schoolId String
studentId String
bloodType String? // "A+", "A-", "B+", "B-", "O+", "O-", "AB+", "AB-"
allergies String? // JSON array of allergy names
medications String? // JSON array of { name, dosage, frequency }
conditions String? // JSON array of condition names
doctorName String?
doctorPhone String?
insuranceNumber String?
insuranceProvider String?
lastCheckup DateTime?
notes String?
isConfidential Boolean @default(true)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
@@unique([schoolId, studentId])
@@map("health_records")
}
// ─── Badges & Gamification ───────────────────────────────────────
model Badge {
id String @id @default(cuid())
schoolId String
name String
description String?
icon String // Lucide icon name
color String @default("#10b981")
category String // "competency", "attendance", "behavior", "achievement", "milestone"
requirementType String // "mastery_level", "attendance_rate", "behavior_count", "progress_entries", "custom"
requirementValue Int? // threshold value
isAuto Boolean @default(true) // auto-awarded or manual
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
studentBadges StudentBadge[]
@@index([schoolId, category])
@@map("badges")
}
model StudentBadge {
id String @id @default(cuid())
schoolId String
studentId String
badgeId String
awardedAt DateTime @default(now())
awardedBy String? // userId who awarded (null if auto)
notes String?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
badge Badge @relation(fields: [badgeId], references: [id])
awardedByUser User? @relation(fields: [awardedBy], references: [id])
@@unique([studentId, badgeId])
@@index([schoolId, studentId])
@@map("student_badges")
}
// ─── Competition System ──────────────────────────────────────────────
model Competition {
id String @id @default(cuid())
schoolId String
title String
description String?
competitionType String // "class" | "inter_class" | "inter_school"
category String // "academic" | "sports" | "creativity" | "citizenship" | "digital" | "reading" | "stem" | "other"
subjectId String? // optional subject link
status String @default("draft") // "draft" | "registration" | "active" | "completed" | "cancelled"
startDate DateTime
endDate DateTime
registrationDeadline DateTime?
maxParticipants Int?
scoringType String @default("points") // "points" | "rank" | "time" | "badge_count"
rules String? // markdown rules/description
isPublic Boolean @default(false) // visible to all schools?
isDemo Boolean @default(false)
isFederation Boolean @default(false) // inter-school district federation competition
federationSchedule String? // "weekly" | "monthly" | "quarterly"
createdById String // teacher/admin who created
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
createdBy User @relation(fields: [createdById], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
participants CompetitionParticipant[]
rewards CompetitionReward[]
leaderboard CompetitionLeaderboard[]
claims RewardClaim[]
@@index([schoolId])
@@index([status])
@@index([competitionType])
@@index([startDate])
@@map("competitions")
}
model CompetitionParticipant {
id String @id @default(cuid())
competitionId String
participantType String // "student" | "class_group" | "school"
participantId String // studentId, classGroupId, or schoolId depending on type
userId String? // the student's userId (for individual participation)
registeredAt DateTime @default(now())
score Int @default(0)
rank Int?
isDisqualified Boolean @default(false)
notes String?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
competition Competition @relation(fields: [competitionId], references: [id], onDelete: Cascade)
user User? @relation(fields: [userId], references: [id])
@@unique([competitionId, participantType, participantId])
@@index([competitionId])
@@index([userId])
@@map("competition_participants")
}
model CompetitionReward {
id String @id @default(cuid())
competitionId String
name String
description String?
rewardType String // "digital_code" | "badge" | "certificate" | "experience" | "physical"
rewardValue String? // promo code, badge ID, certificate URL, etc.
rewardProvider String? // "netflix" | "amazon" | "cinema" | "theater" | "concert" | "custom"
rankRequirement Int? // minimum rank to receive (1=first place, etc.)
pointsRequired Int? // minimum points to receive
quantity Int @default(1) // how many available
claimedCount Int @default(0)
imageUrl String? // reward image
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
competition Competition @relation(fields: [competitionId], references: [id], onDelete: Cascade)
claims RewardClaim[]
@@index([competitionId])
@@map("competition_rewards")
}
model CompetitionLeaderboard {
id String @id @default(cuid())
competitionId String
participantType String // "student" | "class_group" | "school"
participantId String
participantName String // denormalized for fast reads
score Int @default(0)
rank Int?
updatedAt DateTime @updatedAt
competition Competition @relation(fields: [competitionId], references: [id], onDelete: Cascade)
@@unique([competitionId, participantType, participantId])
@@index([competitionId, rank])
@@map("competition_leaderboard")
}
model RewardClaim {
id String @id @default(cuid())
schoolId String
competitionId String
rewardId String
userId String // student who claimed
claimedAt DateTime @default(now())
code String? // the actual promo code (encrypted for DSGVO)
status String @default("pending") // "pending" | "claimed" | "expired" | "revoked"
expiresAt DateTime?
notes String?
isDemo Boolean @default(false)
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
competition Competition @relation(fields: [competitionId], references: [id])
reward CompetitionReward @relation(fields: [rewardId], references: [id])
@@index([schoolId, userId])
@@index([competitionId])
@@map("reward_claims")
}
// ─── Newsletter / School Blog ──────────────────────────────────────
model Newsletter {
id String @id @default(cuid())
schoolId String
authorId String
title String
subject String? // email subject line
content String // rich text content (HTML)
summary String? // short summary for preview
imageUrl String? // cover image URL
bannerImageUrl String? // banner image URL for newsletter header
category String @default("general") // general, academic, sports, arts, events, community
templateType String @default("monthly") // monthly, event, parent, emergency
targetAudience String? // JSON: { type: "all" | "classes" | "roles", ids: [...] }
status String @default("draft") // draft, scheduled, sent, archived
isPublished Boolean @default(false)
publishedAt DateTime?
scheduledAt DateTime?
sentAt DateTime?
tags String? // JSON array of tags
openCount Int @default(0)
clickCount Int @default(0)
bounceCount Int @default(0)
totalRecipients Int @default(0)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
author User @relation(fields: [authorId], references: [id])
@@index([schoolId, isPublished, publishedAt])
@@index([schoolId, status])
@@map("newsletters")
}
// ─── Learning Content (German Curriculum) ─────────────────────────────
model SubjectTopic {
id String @id @default(cuid())
schoolId String
subjectId String
title String
description String?
gradeLevel String? // "1" | "2" | "3" | "4" | "5" | "6" (German Grundschule)
curriculumCode String? // e.g., "DE-NRW-Math-1-01" (German state curriculum code)
sortOrder Int @default(0)
icon String? // Lucide icon name
color String @default("#10b981")
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
subject Subject @relation(fields: [subjectId], references: [id])
lessons SubjectLesson[]
@@index([schoolId, subjectId])
@@index([gradeLevel])
@@map("subject_topics")
}
model SubjectLesson {
id String @id @default(cuid())
topicId String
title String
description String?
lessonType String @default("explanation") // "explanation" | "exercise" | "quiz" | "flashcard" | "video_link"
content String // markdown content for the lesson
difficulty String @default("medium") // "easy" | "medium" | "hard"
sortOrder Int @default(0)
estimatedMinutes Int? // estimated time to complete
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
topic SubjectTopic @relation(fields: [topicId], references: [id], onDelete: Cascade)
questions LessonQuestion[]
@@index([topicId])
@@map("subject_lessons")
}
model LessonQuestion {
id String @id @default(cuid())
lessonId String
questionType String // "multiple_choice" | "true_false" | "fill_blank" | "short_answer" | "matching"
question String // the question text
options String? // JSON array of options for multiple choice
correctAnswer String // the correct answer(s)
explanation String? // why this answer is correct
points Int @default(1)
sortOrder Int @default(0)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
lesson SubjectLesson @relation(fields: [lessonId], references: [id], onDelete: Cascade)
answers StudentAnswer[]
@@index([lessonId])
@@map("lesson_questions")
}
model StudentAnswer {
id String @id @default(cuid())
questionId String
studentId String
answer String // the student's answer
isCorrect Boolean?
timeTakenMs Int? // how long the student took
attempts Int @default(1)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
question LessonQuestion @relation(fields: [questionId], references: [id], onDelete: Cascade)
student User @relation(fields: [studentId], references: [id])
@@unique([questionId, studentId])
@@index([studentId])
@@map("student_answers")
}
// ─── Digital Reward Catalog & Points System ──────────────────────────
model Reward {
id String @id @default(cuid())
schoolId String
title String
description String?
category String // streaming, shopping, experience, merchandise, privilege
pointsCost Int
image String?
stock Int?
isActive Boolean @default(true)
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
redemptions RewardRedemption[]
@@index([schoolId])
@@index([category])
@@map("rewards")
}
model RewardRedemption {
id String @id @default(cuid())
rewardId String
userId String
pointsSpent Int
status String @default("pending") // pending, approved, rejected, fulfilled
note String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
reward Reward @relation(fields: [rewardId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([rewardId])
@@index([userId])
@@index([status])
@@map("reward_redemptions")
}
model RewardPoints {
id String @id @default(cuid())
userId String
schoolId String
points Int @default(0)
source String // competition, grade, attendance, homework
sourceId String? // ID of the source entity
description String?
createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
school School @relation(fields: [schoolId], references: [id])
@@index([userId])
@@index([schoolId])
@@index([source])
@@map("reward_points")
}
// ─── Student Goals (Student Portal) ─────────────────────────────────
model StudentGoal {
id String @id @default(cuid())
studentId String
title String
description String?
targetDate DateTime?
progress Int @default(0)
status String @default("active") // active, completed, abandoned
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
student Student @relation(fields: [studentId], references: [id])
@@index([studentId])
@@index([status])
}
// ─── Subject Content Management (Schlaukopf Clone) ──────────────────
model SubjectCategory {
id String @id @default(cuid())
schoolId String
name String // e.g. "Klasse 5", "Klasse 6", "Oberstufe"
slug String // e.g. "klasse5", "klasse6", "oberstufe"
description String?
icon String? // Lucide icon name
sortOrder Int @default(0)
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
contents SubjectContent[]
@@index([schoolId])
@@index([slug])
@@map("subject_categories")
}
model SubjectContent {
id String @id @default(cuid())
schoolId String
categoryId String
subjectId String? // Link to existing Subject
parentId String? // For nested topics
title String
slug String
description String?
icon String? // Lucide icon name
contentType String @default("topic") // topic, exercise, lesson, quiz, vocabulary
content String? // JSON content (questions, explanations, etc.)
difficulty String @default("medium") // easy, medium, hard
questionCount Int @default(0)
sortOrder Int @default(0)
isActive Boolean @default(true)
isPublic Boolean @default(true) // visible to all schools
sourceUrl String? // original schlaukopf.de URL
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
category SubjectCategory @relation(fields: [categoryId], references: [id])
parent SubjectContent? @relation("ContentHierarchy", fields: [parentId], references: [id])
children SubjectContent[] @relation("ContentHierarchy")
changeRequests ContentChangeRequest[]
subject Subject? @relation(fields: [subjectId], references: [id])
@@index([schoolId])
@@index([categoryId])
@@index([parentId])
@@index([slug])
@@index([contentType])
@@map("subject_contents")
}
model ContentChangeRequest {
id String @id @default(cuid())
schoolId String
contentId String
requestedBy String
requestType String // edit, add, delete
title String
description String
proposedChanges String? // JSON with proposed changes
status String @default("pending") // pending, approved, rejected
reviewedBy String?
reviewNote String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
content SubjectContent @relation(fields: [contentId], references: [id])
requester User @relation("ContentRequester", fields: [requestedBy], references: [id])
reviewer User? @relation("ContentReviewer", fields: [reviewedBy], references: [id])
@@index([schoolId])
@@index([contentId])
@@index([status])
@@index([requestedBy])
@@map("content_change_requests")
}
// ─── AI Settings (Pollination, OpenAI, Anthropic) ────────────────────
model AISettings {
id String @id @default(cuid())
schoolId String @unique
// Pollination AI
pollinationEnabled Boolean @default(true)
pollinationApiKey String?
pollinationModel String @default("flux")
// OpenAI
openaiEnabled Boolean @default(false)
openaiApiKey String?
openaiModel String @default("gpt-4o")
// Anthropic
anthropicEnabled Boolean @default(false)
anthropicApiKey String?
anthropicModel String @default("claude-3-5-sonnet-20241022")
// AI Features
aiChatEnabled Boolean @default(true) // Student AI helper
aiImageGenEnabled Boolean @default(true) // Admin/Teacher image generation
aiVideoGenEnabled Boolean @default(false) // Admin/Teacher video generation
aiAutoTestEnabled Boolean @default(true) // Auto test generation before exams
aiGradingAuditEnabled Boolean @default(false) // AI grading audit
aiTopicGenEnabled Boolean @default(true) // Auto topic generation from categories
// Virtual Character
virtualCharacterEnabled Boolean @default(true) // Enable/disable for students
// Restrictions
aiMaxRequestsPerDay Int @default(50) // Student daily limit
aiHelperMode String @default("guided") // guided, full, restricted
aiSystemPrompt String? // Custom system prompt for AI chat
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
@@map("ai_settings")
}
// ─── Virtual Character System ────────────────────────────────────────
model VirtualCharacter {
id String @id @default(cuid())
userId String @unique
schoolId String
characterId String // e.g. "owl", "dragon", "robot", "cat", "wizard"
name String // Custom name for the character
color String @default("#10b981") // Character accent color
level Int @default(1)
xp Int @default(0)
mood String @default("happy") // happy, excited, thinking, sleepy, celebrating
accessories String? // JSON array of unlocked accessories
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id])
school School @relation(fields: [schoolId], references: [id])
@@index([userId])
@@index([schoolId])
@@map("virtual_characters")
}
// ─── Chat Messages (AI Chatbot & Student-Teacher) ────────────────────
model ChatMessage {
id String @id @default(cuid())
schoolId String
userId String
roomId String? // For room-based chat (null = AI chatbot)
content String
senderType String @default("user") // user, ai, teacher, system
metadata String? // JSON metadata (attachments, etc.)
isRead Boolean @default(false)
createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
school School @relation(fields: [schoolId], references: [id])
room CommunicationRoom? @relation(fields: [roomId], references: [id])
@@index([userId])
@@index([roomId])
@@index([schoolId])
@@index([createdAt])
@@map("chat_messages")
}
// ─── Illness Reporting with Parent Approval ──────────────────────────
model IllnessReport {
id String @id @default(cuid())
schoolId String
studentId String
reportedBy String // userId of the person reporting
reporterType String // student, parent
reason String // illness, doctor_visit, other
description String?
startDate DateTime
endDate DateTime?
documentUrl String? // Medical certificate URL
parentApprovalStatus String @default("pending") // pending, approved, rejected
parentApprovedBy String? // userId of approving parent
parentApprovedAt DateTime?
// Status: only visible to student/parent until approved
isVisibleToTeacher Boolean @default(false)
isVisibleToAdmin Boolean @default(false)
status String @default("pending") // pending, approved, rejected, expired
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
reporter User @relation(fields: [reportedBy], references: [id])
approver User? @relation("IllnessApprover", fields: [parentApprovedBy], references: [id])
@@index([schoolId])
@@index([studentId])
@@index([reportedBy])
@@index([parentApprovalStatus])
@@index([status])
@@map("illness_reports")
}
// ─── Communication Rooms (Student-Teacher) ───────────────────────────
model CommunicationRoom {
id String @id @default(cuid())
schoolId String
studentId String
teacherId String
roomType String @default("chat") // chat, voice, video
status String @default("requested") // requested, active, closed
requestedBy String // student or teacher
acceptedAt DateTime?
closedAt DateTime?
closeReason String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
student User @relation("RoomStudent", fields: [studentId], references: [id])
teacher User @relation("RoomTeacher", fields: [teacherId], references: [id])
members CommunicationRoomMember[]
messages CommunicationMessage[]
chatMessages ChatMessage[]
@@index([schoolId])
@@index([studentId])
@@index([teacherId])
@@index([status])
@@map("communication_rooms")
}
model CommunicationRoomMember {
id String @id @default(cuid())
roomId String
userId String
role String @default("member") // member, moderator
joinedAt DateTime @default(now())
room CommunicationRoom @relation(fields: [roomId], references: [id])
user User @relation(fields: [userId], references: [id])
@@unique([roomId, userId])
@@index([roomId])
@@index([userId])
@@map("communication_room_members")
}
model CommunicationMessage {
id String @id @default(cuid())
roomId String
senderId String
content String
messageType String @default("text") // text, voice, image, note_share, file
fileUrl String?
metadata String? // JSON
isRead Boolean @default(false)
createdAt DateTime @default(now())
room CommunicationRoom @relation(fields: [roomId], references: [id])
sender User @relation(fields: [senderId], references: [id])
@@index([roomId])
@@index([senderId])
@@index([createdAt])
@@map("communication_messages")
}
// ─── Counseling / Guidance System ────────────────────────────────────
model CounselingAppointment {
id String @id @default(cuid())
schoolId String
studentId String
counselorId String
requestType String @default("guidance") // guidance, psychological, career, social
description String?
status String @default("requested") // requested, scheduled, completed, cancelled
scheduledAt DateTime?
duration Int @default(30) // minutes
notes String? // Counselor notes
isPrivate Boolean @default(true)
addToCalendar Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
counselor User @relation("Counselor", fields: [counselorId], references: [id])
student User @relation("CounselingStudent", fields: [studentId], references: [id])
@@index([schoolId])
@@index([counselorId])
@@index([studentId])
@@index([status])
@@index([scheduledAt])
@@map("counseling_appointments")
}
// ─── Disciplinary Committee System ───────────────────────────────────
model DisciplinaryCommittee {
id String @id @default(cuid())
schoolId String
name String
description String?
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
members DisciplinaryCommitteeMember[]
cases DisciplinaryCase[]
@@index([schoolId])
@@map("disciplinary_committees")
}
model DisciplinaryCommitteeMember {
id String @id @default(cuid())
committeeId String
userId String
role String @default("member") // chair, vice_chair, member
isLead Boolean @default(false)
createdAt DateTime @default(now())
committee DisciplinaryCommittee @relation(fields: [committeeId], references: [id])
user User @relation(fields: [userId], references: [id])
@@unique([committeeId, userId])
@@index([committeeId])
@@index([userId])
@@map("disciplinary_committee_members")
}
model DisciplinaryCase {
id String @id @default(cuid())
schoolId String
committeeId String
studentId String
reportedBy String
reviewedBy String?
caseType String // warning, minor_violation, major_violation, serious_offense
description String
evidence String? // JSON array of evidence URLs
status String @default("open") // open, under_review, resolved, dismissed
resolution String?
resolutionDate DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
committee DisciplinaryCommittee @relation(fields: [committeeId], references: [id])
student Student @relation(fields: [studentId], references: [id])
reporter User @relation("CaseReporter", fields: [reportedBy], references: [id])
reviewer User? @relation("CaseReviewer", fields: [reviewedBy], references: [id])
@@index([schoolId])
@@index([committeeId])
@@index([studentId])
@@index([status])
@@map("disciplinary_cases")
}
// ─── AI Test Generation ──────────────────────────────────────────────
model AITestGeneration {
id String @id @default(cuid())
schoolId String
assessmentId String? // Linked assessment
studentId String? // For individual student tests
classGroupId String? // For class-wide tests
subjectId String?
topicId String? // SubjectContent ID
testType String @default("practice") // practice, mock_exam, review
difficulty String @default("medium")
questionCount Int @default(10)
questions String? // JSON array of generated questions
aiProvider String @default("pollination") // pollination, openai, anthropic
generatedAt DateTime?
isCompleted Boolean @default(false)
completedAt DateTime?
score Float? // Student's score if completed
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
assessment Assessment? @relation(fields: [assessmentId], references: [id])
student User? @relation(fields: [studentId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
@@index([schoolId])
@@index([assessmentId])
@@index([studentId])
@@index([classGroupId])
@@map("ai_test_generations")
}
// ─── Teacher Grading Review (AI Audit) ───────────────────────────────
model TeacherGradingReview {
id String @id @default(cuid())
schoolId String
assessmentId String
teacherId String
aiProvider String @default("pollination")
status String @default("pending") // pending, in_progress, completed
reviewResult String? // JSON with review findings
discrepanciesFound Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
assessment Assessment @relation(fields: [assessmentId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
comments GradingReviewComment[]
@@index([schoolId])
@@index([assessmentId])
@@index([teacherId])
@@index([status])
@@map("teacher_grading_reviews")
}
model GradingReviewComment {
id String @id @default(cuid())
reviewId String
userId String
resultId String? // AssessmentResult ID
comment String
suggestedGrade String?
reason String?
createdAt DateTime @default(now())
review TeacherGradingReview @relation(fields: [reviewId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([reviewId])
@@index([userId])
@@map("grading_review_comments")
}
// ─── Grading Annotations (Tablet Stylus) ────────────────────────────
model GradingAnnotation {
id String @id @default(cuid())
schoolId String
assessmentId String
studentId String
resultId String? // AssessmentResult ID
teacherId String
type String @default("drawing") // drawing, text, highlight, stamp
content String? // text content or stamp symbol
positionX Float @default(0)
positionY Float @default(0)
width Float?
height Float?
color String @default("#ef4444")
strokeWidth Float @default(2)
page Int @default(1)
pathData String? // JSON: array of {x,y,pressure} points for drawing
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
assessment Assessment @relation(fields: [assessmentId], references: [id])
student Student @relation(fields: [studentId], references: [id])
result AssessmentResult? @relation(fields: [resultId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
@@index([schoolId])
@@index([assessmentId])
@@index([studentId])
@@index([teacherId])
@@index([page])
@@map("grading_annotations")
}
// ─── Exam Planning ──────────��──────────────────────────────────────
model ExamPlan {
id String @id @default(cuid())
schoolId String
teacherId String
subjectId String
classGroupId String
title String
date DateTime
startTime String? // HH:mm format
endTime String? // HH:mm format
room String?
topics String? // JSON array of topic strings
weight Float @default(1.0)
status String @default("planned") // planned, confirmed, completed, cancelled
notes String?
calendarEventId String? @unique // Link to CalendarEvent
assessmentId String? @unique // Link to Assessment (after exam is created)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
subject Subject @relation(fields: [subjectId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
calendarEvent CalendarEvent? @relation(fields: [calendarEventId], references: [id])
assessment Assessment? @relation(fields: [assessmentId], references: [id])
@@index([schoolId])
@@index([teacherId])
@@index([subjectId])
@@index([classGroupId])
@@index([date])
@@index([status])
@@map("exam_plans")
}
// ─── Student Study Planner ──────────────────────────────────────────
model StudyPlan {
id String @id @default(cuid())
schoolId String
studentId String
title String
description String?
subjectId String?
subjectName String? // Denormalized subject name for display
dayOfWeek Int // 0=Sunday, 1=Monday, ..., 6=Saturday
startTime String // HH:mm format
duration Int // Duration in minutes
isRecurring Boolean @default(true)
specificDate DateTime? // For one-time study sessions
priority String @default("medium") // low, medium, high
status String @default("active") // active, completed, paused, cancelled
color String? // Custom color for the plan
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
sessions StudySession[]
@@index([schoolId])
@@index([studentId])
@@index([dayOfWeek])
@@index([status])
@@map("study_plans")
}
model StudySession {
id String @id @default(cuid())
schoolId String
studentId String
studyPlanId String? // Optional link to a StudyPlan
subjectId String?
subjectName String? // Denormalized subject name
startTime DateTime
endTime DateTime?
duration Int @default(0) // Actual duration in minutes
plannedDuration Int? // Planned duration in minutes
type String @default("pomodoro") // pomodoro, free, guided
pomodorosCompleted Int @default(0)
pomodoroLength Int @default(25) // Minutes per pomodoro
breakLength Int @default(5) // Minutes per break
status String @default("in_progress") // in_progress, completed, abandoned
focusScore Int? // 1-100 self-reported focus score
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
studyPlan StudyPlan? @relation(fields: [studyPlanId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
@@index([schoolId])
@@index([studentId])
@@index([startTime])
@@index([status])
@@map("study_sessions")
}
// ─── School Library ──────────────────────────────────────────────────
model LibraryBook {
id String @id @default(cuid())
schoolId String
title String
author String
isbn String?
publisher String?
publishYear Int?
category String // Fiction, Non-Fiction, Science, History, etc.
readingLevel String? // elementary, middle, high, advanced
language String @default("de")
description String?
coverGradient String? // JSON: {from, to} gradient colors for placeholder
totalCopies Int @default(1)
availableCopies Int @default(1)
location String? // Shelf/room location
tags String? // JSON array of tag strings
isDemo Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
checkouts BookCheckout[]
reservations BookReservation[]
@@index([schoolId])
@@index([category])
@@index([readingLevel])
@@index([author])
@@map("library_books")
}
model BookCheckout {
id String @id @default(cuid())
schoolId String
bookId String
studentId String
checkedOutBy String // userId of the person who processed the checkout
checkoutDate DateTime @default(now())
dueDate DateTime
returnDate DateTime?
condition String @default("good") // excellent, good, fair, poor
returnCondition String? // Condition upon return
renewalCount Int @default(0)
status String @default("active") // active, returned, overdue, lost
fineAmount Float @default(0)
finePaid Boolean @default(false)
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
book LibraryBook @relation(fields: [bookId], references: [id])
student Student @relation(fields: [studentId], references: [id])
checkedOutByUser User @relation(fields: [checkedOutBy], references: [id])
@@index([schoolId])
@@index([bookId])
@@index([studentId])
@@index([status])
@@index([dueDate])
@@map("book_checkouts")
}
model BookReservation {
id String @id @default(cuid())
schoolId String
bookId String
studentId String
userId String // The user who made the reservation (could be parent)
queuePosition Int @default(1)
status String @default("waiting") // waiting, notified, fulfilled, cancelled
notifiedAt DateTime?
fulfilledAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
book LibraryBook @relation(fields: [bookId], references: [id])
student Student @relation(fields: [studentId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([schoolId])
@@index([bookId])
@@index([studentId])
@@index([status])
@@map("book_reservations")
}
// ─── Seating Charts ──────────────────────────────────────────────────
model SeatingChart {
id String @id @default(cuid())
schoolId String
classGroupId String
teacherId String
name String
layoutType String @default("rows") // rows, groups, u-shape, circle, custom
rows Int @default(5)
columns Int @default(5)
gap Int @default(2) // Gap between desks (in grid units)
arrangement String? // JSON: [{ studentId, row, col }] — student seat assignments
showDoor Boolean @default(true)
showWindows Boolean @default(true)
doorPosition String @default("left") // left, right, back
windowPosition String @default("right") // left, right, both
isTemplate Boolean @default(false)
isDefault Boolean @default(false)
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
classGroup ClassGroup @relation(fields: [classGroupId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
@@index([schoolId])
@@index([classGroupId])
@@index([teacherId])
@@map("seating_charts")
}
model DataImportJob {
id String @id @default(cuid())
schoolId String
userId String
type String // STUDENT, TEACHER, GRADE, ATTENDANCE, COMPETENCY
fileName String
fileSize Int
status String @default("pending") // pending, processing, completed, failed
totalRows Int @default(0)
successRows Int @default(0)
errorRows Int @default(0)
errors String? // JSON string of error details
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([schoolId])
@@index([userId])
@@map("data_import_jobs")
}
model DataExportJob {
id String @id @default(cuid())
schoolId String
userId String
type String // STUDENT, GRADE, ATTENDANCE, COMPETENCY, REPORT
format String // CSV, JSON
filters String? // JSON string of filter parameters
fileName String?
fileSize Int @default(0)
status String @default("pending") // pending, processing, completed, failed
fileData String? // Base64 encoded file data
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([schoolId])
@@index([userId])
@@map("data_export_jobs")
}
model WellnessCheckin {
id String @id @default(cuid())
schoolId String
studentId String
date DateTime
mood Int // 1-5
sleepHours Float?
sleepQuality Int? // 1-5
stressLevel Int? // 1-10
activityType String?
activityMinutes Int?
mealsCount Int?
waterGlasses Int?
gratitudeEntry String?
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
@@index([schoolId])
@@index([studentId])
@@index([date])
@@map("wellness_checkins")
}
model WellnessScore {
id String @id @default(cuid())
schoolId String
studentId String
date DateTime
overallScore Float // 0-100
physicalScore Float? // 0-100
mentalScore Float? // 0-100
socialScore Float? // 0-100
academicScore Float? // 0-100
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
@@index([schoolId])
@@index([studentId])
@@index([date])
@@map("wellness_scores")
}
// ─── Student Career & Guidance ───────────────────────────────────────
model CareerProfile {
id String @id @default(cuid())
schoolId String
studentId String @unique
interests String? // JSON: ["technology", "health", "business"]
strengths String? // JSON: ["mathematics", "communication"]
careerCluster String? // Technology, Health, Business, Arts, Science, Social
desiredCareer String?
educationPath String? // Hauptschule, Realschule, Gymnasium, Berufsschule
workExperiences String? // JSON: [{ company, role, startDate, endDate }]
volunteerExps String? // JSON: [{ organization, role, startDate, endDate }]
certifications String? // JSON: [{ name, date, issuer }]
documents String? // JSON: [{ name, url, type }]
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
student Student @relation(fields: [studentId], references: [id])
goals CareerGoal[]
appointments CareerAppointment[]
@@index([schoolId])
@@index([studentId])
@@index([careerCluster])
@@map("career_profiles")
}
model CareerGoal {
id String @id @default(cuid())
profileId String
title String
description String?
category String // short_term, long_term, academic, professional
targetDate DateTime?
progress Int @default(0) // 0-100
status String @default("active") // active, completed, abandoned
milestones String? // JSON: [{ title, completed, date }]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
profile CareerProfile @relation(fields: [profileId], references: [id], onDelete: Cascade)
@@index([profileId])
@@index([category])
@@index([status])
@@map("career_goals")
}
model CareerAppointment {
id String @id @default(cuid())
schoolId String
profileId String
counselorId String
date DateTime
duration Int @default(30) // minutes
type String @default("guidance") // guidance, career, academic, college
status String @default("scheduled") // scheduled, completed, cancelled
notes String?
actionItems String? // JSON: [{ item, completed }]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
profile CareerProfile @relation(fields: [profileId], references: [id], onDelete: Cascade)
counselor User @relation(fields: [counselorId], references: [id])
@@index([schoolId])
@@index([profileId])
@@index([counselorId])
@@index([date])
@@index([status])
@@map("career_appointments")
}
model SubstituteTeacher {
id String @id @default(cuid())
schoolId String
userId String? @unique // optional link to User account
firstName String
lastName String
email String?
phone String?
qualifications String? // JSON: ["Mathematics", "Physics", "German"]
subjects String? // JSON: subject IDs they can teach
gradeLevels String? // JSON: ["1-4", "5-6", "7-10"]
availability String? // JSON: { "monday": ["08:00-12:00"], "tuesday": [...] }
maxDaysPerWeek Int @default(5)
rating Float @default(0) // 0-5 average rating
totalAssignments Int @default(0)
notes String?
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
user User? @relation(fields: [userId], references: [id])
assignments SubstitutionAssignment[]
@@index([schoolId])
@@index([userId])
@@map("substitute_teachers")
}
model TeacherAbsence {
id String @id @default(cuid())
schoolId String
teacherId String
type String // SICK, PERSONAL, TRAINING, OTHER
startDate DateTime
endDate DateTime
reason String?
status String @default("reported") // reported, covered, cancelled
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
teacher User @relation(fields: [teacherId], references: [id])
assignments SubstitutionAssignment[]
@@index([schoolId])
@@index([teacherId])
@@index([startDate])
@@map("teacher_absences")
}
model SubstitutionAssignment {
id String @id @default(cuid())
schoolId String
absenceId String
substituteId String
classGroupId String?
subjectId String?
date DateTime
period Int? // lesson period number
room String?
status String @default("pending") // pending, confirmed, completed, cancelled
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
absence TeacherAbsence @relation(fields: [absenceId], references: [id])
substitute SubstituteTeacher @relation(fields: [substituteId], references: [id])
classGroup ClassGroup? @relation(fields: [classGroupId], references: [id])
subject Subject? @relation(fields: [subjectId], references: [id])
@@index([schoolId])
@@index([absenceId])
@@index([substituteId])
@@index([date])
@@map("substitution_assignments")
}
// ─── Grade Analytics & Reporting ──────────────────────────────────
model GradeReport {
id String @id @default(cuid())
schoolId String
generatedBy String
title String
type String @default("custom") // quarterly, semester, annual, custom
dateRange String // JSON: { from, to }
classIds String? // JSON: array of class IDs
subjectIds String? // JSON: array of subject IDs
metrics String? // JSON: array of selected metrics
status String @default("pending") // pending, completed, failed
fileData String? // Base64 encoded or JSON report data
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
generator User @relation("GradeReportGenerator", fields: [generatedBy], references: [id])
@@index([schoolId])
@@index([generatedBy])
}
// ─── Module L: Legal & Data Protection Compliance ──────────────────
model DataRetentionPolicy {
id String @id @default(cuid())
schoolId String
dataCategory String // LEARNING_PROGRESS, ASSESSMENT_RESULT, FINAL_REPORT, DISCIPLINARY_RECORD, EXAM_INCIDENT, MESSAGE, AI_TUTOR_LOG, NOTEBOOK_PAGE, SIGNAGE_LOG, TEACHER_AUDIT_LOG
retentionPeriodMonths Int? // nullable to force DPO to fill in legally-mandated categories
legalBasisNote String? // DPO notes on state-specific retention rule
isDefault Boolean @default(false) // false = legally mandated; true = optional product data
reviewedByUserId String? // DPO approval
reviewedAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
reviewedByUser User? @relation("DataRetentionPolicyReviewer", fields: [reviewedByUserId], references: [id])
@@index([schoolId])
@@index([dataCategory])
}
model DpiaRecord {
id String @id @default(cuid())
schoolId String
moduleScope String // AI_TUTOR, EXAM_MODE, WELLNESS_CHECKS, SIGNAGE_EMERGENCY, MESSAGING, LEARNING_ANALYTICS
dataProcessingSummary String // what's processed, for how long, who sees it
completedAt DateTime
approvedByUserId String? // DPO approval
documentUrl String? // uploaded DPIA PDF
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
approvedByUser User? @relation("DpiaRecordApprover", fields: [approvedByUserId], references: [id])
@@index([schoolId])
@@index([moduleScope])
@@unique([schoolId, moduleScope])
}
model ComplianceConsultation {
id String @id @default(cuid())
schoolId String
consultationType String // ELTERNBEIRAT, SCHULKONFERENZ, PERSONALRAT
moduleScope String? // specific module if Personalrat for monitoring-capable features
acknowledgedByUserId String
acknowledgedAt DateTime
notes String? // what was consulted/approved
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
acknowledgedByUser User @relation("ComplianceConsultationAcknowledger", fields: [acknowledgedByUserId], references: [id])
@@index([schoolId])
@@index([consultationType])
}
model SchoolComplianceStatus {
id String @id @default(cuid())
schoolId String @unique
bundesland String? // BERLIN, BREMEN, HAMBURG, etc. (16 states)
onboardingCompleted Boolean @default(false)
dpoDpoContactEmail String?
schultragerContactEmail String?
moduleGates String? // JSON: { "AI_TUTOR": { isApprovedForUse: bool, diaCompleted: bool }, ... }
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
}
// ─── Module C: Escalation Policy Engine ─────────────────────────────
model EscalationPolicy {
id String @id @default(cuid())
schoolId String
name String // "Verhaltensauffälligkeiten", "Noten-Bedenken", etc.
description String?
triggerType String // BEHAVIOR_INCIDENT, GRADE_CONCERN, ATTENDANCE, WELLNESS, CUSTOM
severity String // MINOR, MODERATE, MAJOR
isActive Boolean @default(true)
// Business days calculation
businessDaysUntilEscalation Int @default(5) // e.g., 5 business days until parent can escalate
timezone String @default("Europe/Berlin")
// Escalation chain configuration
escalationSteps EscalationStep[]
// Audit
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
escalations Escalation[]
@@unique([schoolId, name])
@@index([schoolId])
}
model EscalationStep {
id String @id @default(cuid())
policyId String
stepNumber Int // 1, 2, 3, etc.
recipientRole String // TEACHER, CLASS_LEADER, VICE_PRINCIPAL, PRINCIPAL, COUNSELOR, SCHOOL_ADMIN
requiresConfirmation Boolean @default(true)
autoNotify Boolean @default(true)
messageTemplate String? // Reason why escalation necessary
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
policy EscalationPolicy @relation(fields: [policyId], references: [id], onDelete: Cascade)
escalationLogs EscalationLog[]
@@unique([policyId, stepNumber])
@@index([policyId])
}
model Escalation {
id String @id @default(cuid())
schoolId String
policyId String
initiatedByUserId String
initiatorRole String // student, parent, teacher
subjectUserId String // Student being escalated
subjectType String // STUDENT, BEHAVIOR, GRADE, ATTENDANCE, WELLNESS
subjectId String // ID of the specific issue (incidentId, gradeId, etc.)
// Escalation state
status String @default("PENDING") // PENDING, IN_REVIEW, ESCALATED, RESOLVED, CLOSED
currentStep Int @default(1)
eligibleAtDateTime DateTime // Calculated by business-day logic
// Context
reason String // Why escalation was initiated
attachmentUrls String? // JSON array of file URLs
// Audit
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
resolvedAt DateTime?
school School @relation(fields: [schoolId], references: [id])
policy EscalationPolicy @relation(fields: [policyId], references: [id])
initiatedBy User @relation("EscalationInitiator", fields: [initiatedByUserId], references: [id])
subjectUser Student @relation(fields: [subjectUserId], references: [id])
logs EscalationLog[]
@@index([schoolId])
@@index([policyId])
@@index([subjectUserId])
@@index([status])
@@index([createdAt])
}
model EscalationLog {
id String @id @default(cuid())
escalationId String
stepId String
actionType String // INITIATED, NOTIFIED, CONFIRMED, ESCALATED_TO_NEXT_STEP, RESOLVED
performedByUserId String?
actionReason String?
confidentialNote String? // Only visible to authorized users
createdAt DateTime @default(now())
escalation Escalation @relation(fields: [escalationId], references: [id], onDelete: Cascade)
step EscalationStep @relation(fields: [stepId], references: [id])
performedBy User? @relation("EscalationLogPerformer", fields: [performedByUserId], references: [id])
@@index([escalationId])
@@index([performedByUserId])
@@index([createdAt])
}
model EscalationMessageTemplate {
id String @id @default(cuid())
schoolId String
triggerType String // BEHAVIOR_INCIDENT, GRADE_CONCERN, ATTENDANCE, WELLNESS
severity String // MINOR, MODERATE, MAJOR
stepNumber Int // At which step this message is sent
// Template content
titleDe String // German
titleEn String? // English (optional)
bodyDe String
bodyEn String?
// Personalization placeholders: {{studentName}}, {{incidentDate}}, {{reason}}, {{nextStepRole}}
isDefault Boolean @default(false)
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
@@unique([schoolId, triggerType, severity, stepNumber])
@@index([schoolId])
}
model DeletionFlag {
id String @id @default(cuid())
schoolId String
entityType String // Student, Message, Notebook, etc.
entityId String
dataCategory String // retention category reference
flaggedAt DateTime @default(now())
approvedByUserId String?
approvedAt DateTime?
deletedAt DateTime?
reason String // "retention period expired", "erasure request", etc.
school School @relation(fields: [schoolId], references: [id])
approvedByUser User? @relation("DeletionFlagApprover", fields: [approvedByUserId], references: [id])
@@index([schoolId])
@@index([entityType])
@@index([flaggedAt])
}
// ─── Module B: Emergency Signage System ─────────────────────────────
model EmergencySignage {
id String @id @default(cuid())
schoolId String
displayName String // Room name or location (e.g., "Main Hallway", "Cafeteria TV 1")
signageType String @default("digital_display") // digital_display, led_board, tv_monitor
resolution String @default("1920x1080") // resolution info
location String // Physical location in school
isActive Boolean @default(true)
currentMessageId String? // Current active message
brightness Int @default(100) // 1-100
volume Int @default(50) // 1-100 for audio
ipAddress String? // For network monitoring
lastHeartbeat DateTime? // Last connection check
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
messages SignageMessage[]
schedules SignageSchedule[]
@@index([schoolId])
@@index([isActive])
@@map("emergency_signages")
}
model SignageMessage {
id String @id @default(cuid())
schoolId String
signageId String
title String
content String // HTML or plain text
messageType String // emergency, notification, schedule, announcement, alert
priority String @default("normal") // low, normal, high, critical
backgroundColor String? // Hex color
textColor String? // Hex color
displayDuration Int @default(10) // seconds to display
soundAlert Boolean @default(false) // Play sound on emergency
soundFile String? // URL to audio file
createdBy String // userId who created
createdAt DateTime @default(now())
startTime DateTime?
endTime DateTime?
displayOrder Int @default(0)
school School @relation(fields: [schoolId], references: [id])
signage EmergencySignage @relation(fields: [signageId], references: [id], onDelete: Cascade)
creator User @relation(fields: [createdBy], references: [id])
audits SignageAuditLog[]
@@index([schoolId])
@@index([signageId])
@@index([messageType])
@@index([priority])
@@map("signage_messages")
}
model SignageSchedule {
id String @id @default(cuid())
schoolId String
signageId String
messageId String? // Optional: specific message to display
dayOfWeek Int // 0-6 (Sunday-Saturday)
startTime String // HH:mm format
endTime String // HH:mm format
isRecurring Boolean @default(true)
createdBy String // userId
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
signage EmergencySignage @relation(fields: [signageId], references: [id], onDelete: Cascade)
@@index([schoolId])
@@index([signageId])
@@map("signage_schedules")
}
model SignageAuditLog {
id String @id @default(cuid())
schoolId String
messageId String
action String // created, updated, displayed, error, offline
details String? // JSON: { status, device, error, etc }
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
message SignageMessage @relation(fields: [messageId], references: [id], onDelete: Cascade)
@@index([schoolId])
@@index([messageId])
@@index([action])
@@map("signage_audit_logs")
}
// ─── Module F: Exam Mode / Digital Test Environment ──────────────────
model ExamSession {
id String @id @default(cuid())
schoolId String
assessmentId String
studentId String
startTime DateTime
endTime DateTime?
submittedAt DateTime?
status String @default("not_started") // not_started, in_progress, submitted, graded
score Float?
totalPoints Float?
lockdownEnabled Boolean @default(true) // Kiosk mode lockdown
cameraMonitor Boolean @default(false) // Enable face monitoring
screenShare Boolean @default(false) // Allow screen sharing for proctoring
environmentNotes String? // JSON: { browser, os, resolution, etc }
suspiciousActivity String? // JSON array of incidents
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
assessment Assessment @relation(fields: [assessmentId], references: [id])
student Student @relation(fields: [studentId], references: [id])
answers ExamAnswer[]
events ExamEvent[]
warnings ExamWarning[]
@@index([schoolId])
@@index([assessmentId])
@@index([studentId])
@@index([status])
@@map("exam_sessions")
}
model ExamAnswer {
id String @id @default(cuid())
examSessionId String
questionId String
answer String? // JSON: selected choice, free text, etc
submittedAt DateTime @default(now())
isCorrect Boolean?
pointsAwarded Float?
reviewedAt DateTime?
examSession ExamSession @relation(fields: [examSessionId], references: [id], onDelete: Cascade)
@@index([examSessionId])
@@index([questionId])
@@map("exam_answers")
}
model ExamEvent {
id String @id @default(cuid())
examSessionId String
eventType String // tab_switch, copy_paste, window_blur, camera_detected_none, suspicious_movement, time_warning
severity String @default("info") // info, warning, critical
description String?
timestamp DateTime @default(now())
metadata String? // JSON: additional context
examSession ExamSession @relation(fields: [examSessionId], references: [id], onDelete: Cascade)
@@index([examSessionId])
@@index([eventType])
@@map("exam_events")
}
model ExamWarning {
id String @id @default(cuid())
examSessionId String
studentId String
warningType String // time_low, behavior_suspicious, multiple_tabs, camera_issue
message String
acknowledged Boolean @default(false)
acknowledgedAt DateTime?
createdAt DateTime @default(now())
examSession ExamSession @relation(fields: [examSessionId], references: [id], onDelete: Cascade)
@@index([examSessionId])
@@index([studentId])
@@map("exam_warnings")
}
// ─── Module H: Centralized Notifications Hub ───────────────────────
model NotificationHub {
id String @id @default(cuid())
schoolId String
userId String
category String // assessment, behavior, communication, wellness, system, announcement
priority String @default("normal") // low, normal, high, critical
title String
message String
actionUrl String? // Link to related resource
actionLabel String? // e.g. "View Grade", "Respond"
isRead Boolean @default(false)
readAt DateTime?
isDismissed Boolean @default(false)
dismissedAt DateTime?
sourceType String? // assessment, incident, message, announcement, etc
sourceId String? // ID of related entity
createdAt DateTime @default(now())
expiresAt DateTime? // Auto-archive if set
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([schoolId])
@@index([userId])
@@index([isRead])
@@index([category])
@@index([createdAt])
@@map("notification_hubs")
}
model NotificationArchive {
id String @id @default(cuid())
schoolId String
userId String
originalId String? // reference to NotificationHub.id if archived
title String
message String
category String
archivedAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([schoolId])
@@index([userId])
@@index([archivedAt])
@@map("notification_archives")
}
// ─── Module J: Customizable Dashboard Widgets ──────────────────────
model DashboardWidget {
id String @id @default(cuid())
schoolId String
userId String
widgetType String // grade_summary, attendance_trend, upcoming_assessments, class_schedule, wellness_status, recent_messages, announcements
title String? // Custom title override
position Int // Grid position (0-11)
size String @default("medium") // small, medium, large, full_width
isVisible Boolean @default(true)
config String? // JSON: widget-specific settings { colors, dateRange, filters, etc }
dataSource String? // API endpoint or computed field reference
refreshInterval Int @default(300) // seconds (0 = manual)
lastRefresh DateTime?
cacheData String? // JSON cached widget data
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([schoolId])
@@index([userId])
@@index([widgetType])
@@unique([userId, position])
@@map("dashboard_widgets")
}
model DashboardLayout {
id String @id @default(cuid())
schoolId String
userId String
name String // "Default", "Teacher View", "Student View", etc
gridColumns Int @default(4) // Grid layout columns
isDefault Boolean @default(false)
widgets String // JSON: array of widget positions
theme String? // Dark, light, custom
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@index([schoolId])
@@index([userId])
@@map("dashboard_layouts")
}
model WidgetTemplate {
id String @id @default(cuid())
schoolId String
widgetType String // Same types as DashboardWidget
name String
description String?
defaultConfig String // JSON: default configuration
thumbnail String? // URL to preview image
createdAt DateTime @default(now())
school School @relation(fields: [schoolId], references: [id])
@@unique([schoolId, widgetType])
@@index([schoolId])
@@map("widget_templates")
}
Reference in new issue View Git Blame Copy Permalink