math2-platform/backend/DATA_MODEL_DOCUMENTATION.md

Data Model Documentation

Overview

Complete Prisma schema with 13 entities, 10 enums, and comprehensive relationships for the Math Learning Platform.

---

Entities

1. User (users)

User accounts with authentication and profile information.

Fields:

• id (String, @id, @default(cuid))
• email (String, @unique)
• username (String, @unique)
• passwordHash (String)
• telegramChatId (String?, @unique, hidden from users)
• isActive (Boolean, @default(true))
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)
• lastLoginAt (DateTime?)

Relationships:

• Has many ExerciseAttempt
• Has many Progress
• Has many UserAchievement
• Has many Ranking
• Has many Notification

Indexes:

• email
• username
• isActive

---

2. Module (modules)

Pedagogical modules containing educational content.

Fields:

• id (String, @id, @default(cuid))
• name (String)
• description (String)
• type (ModuleType, enum)
• order (Int)
• isPublished (Boolean, @default(false))
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)
• introduction (String?, Text, LaTeX supported)
• examples (Json?, array of examples with formulas)
• exercises (Json?, exercise sections)
• answers (Json?, answer keys)
• estimatedHours (Int?, @default(0))
• difficultyLevel (ExerciseDifficulty, @default(INTERMEDIATE))
• totalExercises (Int, @default(0))

Relationships:

• Has many Topic
• Has many Exercise
• Has many Progress
• Has many Ranking

Indexes:

• type+order (unique)
• type
• order
• isPublished

Modules:

1. FUNDAMENTOS - Vectores and Matrices
2. SISTEMAS_ESPACIOS - Sistemas and Espacios Vectoriales
3. APLICACIONES - Programación Lineal

---

3. Topic (topics)

Mathematical topics within modules.

Fields:

• id (String, @id, @default(cuid))
• moduleId (String, foreign key)
• name (String)
• type (TopicType, enum)
• order (Int)
• description (String?)
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)
• theoryContent (Json?, theory with formulas)
• formulas (Json?, LaTeX formulas)
• keyPoints (Json?, key learning points)
• commonMistakes (Json?, common errors)

Relationships:

• Belongs to Module (onDelete: Cascade)
• Has many Exercise

Indexes:

• moduleId+order (unique)
• moduleId
• type

Topics:

1. VECTORES
2. MATRICES
3. SISTEMAS
4. ESPACIOS_VECTORIALES
5. PROGRAMACION_LINEAL

---

4. Exercise (exercises)

Mathematical exercises with LaTeX formulas.

Fields:

• id (String, @id, @default(cuid))
• moduleId (String, foreign key)
• topicId (String?, foreign key)
• type (ExerciseType, enum)
• difficulty (ExerciseDifficulty, enum)
• order (Int)
• statement (String, Text, LaTeX supported)
• correctAnswer (String, Text, LaTeX supported)
• solutionSteps (Json?, step-by-step solutions)
• formulas (Json?, related formulas)
• hints (Json?, hints with costs)
• isAIGenerated (Boolean, @default(false))
• isPublished (Boolean, @default(false))
• points (Int, @default(10))
• timeLimitSeconds (Int?)
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)
• multipleChoiceOptions (Json?, for multiple choice)
• proofRequirements (Json?, for proof exercises)
• calculationSteps (Json?, for calculation exercises)

Relationships:

• Belongs to Module (onDelete: Cascade)
• Belongs to Topic (onDelete: SetNull)
• Has many ExerciseAttempt

Indexes:

• moduleId+order (unique)
• moduleId
• topicId
• type
• difficulty
• isPublished
• isAIGenerated

---

5. ExerciseAttempt (exercise_attempts)

User attempts at exercises.

Fields:

• id (String, @id, @default(cuid))
• userId (String, foreign key)
• exerciseId (String, foreign key)
• userAnswer (String, Text)
• status (AttemptStatus, enum)
• pointsEarned (Int, @default(0))
• timeSpentSeconds (Int)
• hintsUsed (Int, @default(0))
• feedback (String?, Text)
• createdAt (DateTime, @default(now))
• attemptNumber (Int)
• isPerfect (Boolean, @default(false))
• skipped (Boolean, @default(false))

Relationships:

• Belongs to User (onDelete: Cascade)
• Belongs to Exercise (onDelete: Cascade)

Indexes:

• userId+exerciseId+attemptNumber (unique)
• userId
• exerciseId
• status
• createdAt
• userId+exerciseId

---

6. Progress (progress)

User progress tracking per module.

Fields:

• id (String, @id, @default(cuid))
• userId (String, foreign key)
• moduleId (String, foreign key)
• exercisesCompleted (Int, @default(0))
• totalExercises (Int, @default(0))
• points (Int, @default(0))
• percentage (Float, @default(0))
• isStarted (Boolean, @default(false))
• isCompleted (Boolean, @default(false))
• startedAt (DateTime?)
• completedAt (DateTime?)
• lastAccessedAt (DateTime?)
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)
• averageScore (Float?)
• totalTimeSpent (Int, @default(0), seconds)
• perfectExercises (Int, @default(0))
• attemptsCount (Int, @default(0))

Relationships:

• Belongs to User (onDelete: Cascade)
• Belongs to Module (onDelete: Cascade)

Indexes:

• userId+moduleId (unique)
• userId
• moduleId
• isCompleted
• points

---

7. Achievement (achievements)

Gamification badges and achievements.

Fields:

• id (String, @id, @default(cuid))
• code (String, @unique)
• name (String)
• description (String)
• category (AchievementCategory, enum)
• rarity (AchievementRarity, enum)
• icon (String, emoji or icon name)
• requirementType (RequirementType, enum)
• requirementValue (Int)
• points (Int, @default(0))
• metadata (Json?, {color, animation, tooltip, unlockMessage})
• isActive (Boolean, @default(true))
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)

Relationships:

• Has many UserAchievement

Indexes:

• category
• rarity
• isActive
• code

Achievements (18 total):

• Exercises: FIRST_EXERCISE, TEN_EXERCISES, HUNDRED_EXERCISES, FIVE_PERFECT
• Modules: FIRST_MODULE, ALL_MODULES, PERFECT_MODULE
• Streaks: THREE_DAY_STREAK, WEEK_STREAK, MONTH_STREAK
• Ranking: TOP_10, PODIUM, CHAMPION
• Special: EARLY_BIRD, NIGHT_OWL, AUTODIDACT

---

8. UserAchievement (user_achievements)

Unlocked achievements for users.

Fields:

• id (String, @id, @default(cuid))
• userId (String, foreign key)
• achievementId (String, foreign key)
• progress (Int, @default(0))
• unlockedAt (DateTime?)
• metadata (Json?, {unlockedDetails, relatedStats})
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)

Relationships:

• Belongs to User (onDelete: Cascade)
• Belongs to Achievement (onDelete: Cascade)

Indexes:

• userId+achievementId (unique)
• userId
• achievementId
• unlockedAt

---

9. Ranking (rankings)

Leaderboard positions (global and per-module).

Fields:

• id (String, @id, @default(cuid))
• userId (String, foreign key)
• moduleId (String?, foreign key, null=global)
• position (Int)
• points (Int, @default(0))
• exercisesCompleted (Int, @default(0))
• streak (Int, @default(0))
• lastUpdated (DateTime, @default(now))
• perfectExercises (Int, @default(0))
• averageScore (Float?)
• totalAttempts (Int, @default(0))
• achievementsUnlocked (Int, @default(0))

Relationships:

• Belongs to User (onDelete: Cascade)
• Belongs to Module (onDelete: Cascade)

Indexes:

• userId+moduleId (unique)
• moduleId
• position
• points
• streak

---

10. Notification (notifications)

Telegram notifications (backend only).

Fields:

• id (String, @id, @default(cuid))
• type (NotificationType, enum)
• title (String)
• message (String, Text)
• telegramChatId (String, foreign key to User.telegramChatId)
• status (NotificationStatus, enum, @default(PENDING))
• priority (Int, @default(0))
• metadata (Json?, {userId, relatedData, actionUrl})
• attempts (Int, @default(0))
• lastAttemptAt (DateTime?)
• sentAt (DateTime?)
• errorMessage (String?, Text)
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)

Relationships:

• Belongs to User (via telegramChatId, onDelete: Cascade)

Indexes:

• status
• type
• telegramChatId
• createdAt
• priority

---

11. ProcessedPdf (processed_pdfs)

PDF processing metadata.

Fields:

• id (String, @id, @default(cuid))
• fileName (String, @unique)
• originalPath (String)
• type (PdfType, enum)
• topicType (TopicType?, enum)
• isProcessed (Boolean, @default(false))
• processingStartedAt (DateTime?)
• processingCompletedAt (DateTime?)
• errorMessage (String?, Text)
• extractedText (Json?, {pages: [...]})
• exercisesDetected (Json?, array of exercises)
• formulasExtracted (Json?, array of formulas)
• metadata (Json?, {author, pages, isbn, year, edition})
• totalPages (Int?)
• processingVersion (String?)
• checksum (String?)
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)

Indexes:

• type
• topicType
• isProcessed
• fileName

---

12. SystemConfig (system_config)

System configuration key-value storage.

Fields:

• id (String, @id, @default(cuid))
• key (String, @unique)
• value (String, Text)
• description (String?)
• category (String?)
• isPublic (Boolean, @default(false))
• createdAt (DateTime, @default(now))
• updatedAt (DateTime, @updatedAt)

Indexes:

• category
• key

---

13. AuditLog (audit_logs)

Audit trail for system events.

Fields:

• id (String, @id, @default(cuid))
• userId (String?)
• action (String)
• entityType (String)
• entityId (String?)
• metadata (Json?)
• ipAddress (String?)
• userAgent (String?)
• createdAt (DateTime, @default(now))

Indexes:

• userId
• action
• entityType
• createdAt

---

Enums

ModuleType

• FUNDAMENTOS
• SISTEMAS_ESPACIOS
• APLICACIONES

TopicType

• VECTORES
• MATRICES
• SISTEMAS
• ESPACIOS_VECTORIALES
• PROGRAMACION_LINEAL

ExerciseType

• MULTIPLE_CHOICE
• OPEN_RESPONSE
• CALCULATION
• PROOF
• TRUE_FALSE

ExerciseDifficulty

• BASIC
• INTERMEDIATE
• ADVANCED
• EXPERT

AttemptStatus

• CORRECT
• INCORRECT
• PARTIAL
• PENDING

AchievementCategory

• EXERCISES
• MODULES
• STREAKS
• RANKING
• SPECIAL

AchievementRarity

• COMMON
• RARE
• EPIC
• LEGENDARY

RequirementType

• EXERCISES_COMPLETED
• MODULES_COMPLETED
• PERFECT_SCORES
• STREAK_DAYS
• RANKING_POSITION
• EXERCISES_WITHOUT_HINTS
• EARLY_BIRD
• NIGHT_OWL
• PERFECT_MODULE

NotificationType

• NEW_USER
• EXERCISE_COMPLETED
• MODULE_COMPLETED
• ACHIEVEMENT_UNLOCKED
• SYSTEM_ERROR
• DAILY_SUMMARY
• RANKING_CHANGED

NotificationStatus

• PENDING
• SENT
• FAILED

PdfType

• TEXTBOOK
• PRACTICE
• PRACTICE_ANSWERS
• EXAM
• ADDITIONAL_MATERIAL

---

Key Relationships

User Progress Flow

1. User → ExerciseAttempt (many)
2. User → Progress (one per module)
3. User → UserAchievement (many)
4. User → Ranking (one global + one per module)

Module Content Flow

1. Module → Topic (many)
2. Module → Exercise (many)
3. Topic → Exercise (many)
4. Exercise → ExerciseAttempt (many)

Gamification Flow

1. Achievement → UserAchievement (many)
2. ExerciseAttempt → triggers Achievement check
3. Progress → triggers Ranking update

Notification Flow

1. ExerciseAttempt → triggers Notification
2. Progress → triggers Notification
3. UserAchievement → triggers Notification

---

JSON Fields Structure

Module.examples

[
  {
    "title": "string",
    "content": "string",
    "latexFormula": "string",
    "explanation": "string",
    "difficulty": "BASIC|INTERMEDIATE|ADVANCED|EXPERT"
  }
]

Exercise.solutionSteps

[
  {
    "step": 1,
    "explanation": "string",
    "latexFormula": "string"
  }
]

Exercise.hints

[
  {
    "hint": "string",
    "cost": 2
  }
]

ProcessedPdf.extractedText

{
  "pages": [
    {
      "page": 1,
      "text": "string",
      "tables": [...],
      "images": [...]
    }
  ]
}

---

Performance Optimization

Indexes

All foreign keys and frequently queried fields are indexed.

Cascade Deletes

• User deletion → all related data deleted
• Module deletion → topics, exercises, progress deleted
• Exercise deletion → attempts deleted

JSON Fields

Used for flexible content storage (formulas, examples, solutions).

---

Data Integrity

Unique Constraints

• User email, username
• User telegramChatId
• Achievement code
• ProcessedPdf fileName
• Module type+order
• Topic moduleId+order
• Exercise moduleId+order
• UserProgress userId+moduleId
• UserAchievement userId+achievementId
• Ranking userId+moduleId

Required Fields

All critical fields have @default or are required.

---

Future Enhancements

Potential Additions

1. Exercise comments/discussions
2. User social features (follow, share)
3. Course enrollments
4. Certificate generation
5. Analytics dashboard
6. Content versioning
7. Multi-language support

Scalability Considerations

1. Partitioning for large tables (ExerciseAttempt)
2. Archival strategy for old data
3. Caching layer for frequently accessed data
4. Read replicas for reporting queries

---

Schema Version: 1.0.0 Last Updated: 2026-03-23 Total Entities: 13 Total Enums: 10 Total Relationships: 20+