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
```json
[
  {
    "title": "string",
    "content": "string",
    "latexFormula": "string",
    "explanation": "string",
    "difficulty": "BASIC|INTERMEDIATE|ADVANCED|EXPERT"
  }
]
```

### Exercise.solutionSteps
```json
[
  {
    "step": 1,
    "explanation": "string",
    "latexFormula": "string"
  }
]
```

### Exercise.hints
```json
[
  {
    "hint": "string",
    "cost": 2
  }
]
```

### ProcessedPdf.extractedText
```json
{
  "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+