// 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") }