trading-platform-database-v2/ddl/schemas/education/TECHNICAL.md

# Documentación Técnica - Schema Education

**Proyecto:** Trading Platform (Trading Platform)
**Schema:** education
**PostgreSQL:** 15+
**Versión:** 1.0.0

---

## Estadísticas del Schema

- **ENUMs:** 6 tipos
- **Tablas:** 14 tablas
- **Funciones:** 8 funciones
- **Triggers:** 15+ triggers
- **Vistas:** 7 vistas
- **Índices:** 60+ índices
- **Total líneas SQL:** ~1,350 líneas

---

## Índices por Tabla

### categories
- `idx_categories_parent` - parent_id
- `idx_categories_slug` - slug
- `idx_categories_active` - is_active (WHERE is_active = true)

### courses
- `idx_courses_category` - category_id
- `idx_courses_slug` - slug
- `idx_courses_status` - status
- `idx_courses_difficulty` - difficulty_level
- `idx_courses_instructor` - instructor_id
- `idx_courses_published` - published_at (WHERE status = 'published')

### modules
- `idx_modules_course` - course_id
- `idx_modules_order` - course_id, display_order

### lessons
- `idx_lessons_module` - module_id
- `idx_lessons_order` - module_id, display_order
- `idx_lessons_type` - content_type
- `idx_lessons_preview` - is_preview (WHERE is_preview = true)

### enrollments
- `idx_enrollments_user` - user_id
- `idx_enrollments_course` - course_id
- `idx_enrollments_status` - status
- `idx_enrollments_user_active` - user_id, status (WHERE status = 'active')

### progress
- `idx_progress_user` - user_id
- `idx_progress_lesson` - lesson_id
- `idx_progress_enrollment` - enrollment_id
- `idx_progress_completed` - is_completed (WHERE is_completed = true)
- `idx_progress_user_enrollment` - user_id, enrollment_id

### quizzes
- `idx_quizzes_module` - module_id
- `idx_quizzes_lesson` - lesson_id
- `idx_quizzes_active` - is_active (WHERE is_active = true)

### quiz_questions
- `idx_quiz_questions_quiz` - quiz_id
- `idx_quiz_questions_order` - quiz_id, display_order

### quiz_attempts
- `idx_quiz_attempts_user` - user_id
- `idx_quiz_attempts_quiz` - quiz_id
- `idx_quiz_attempts_enrollment` - enrollment_id
- `idx_quiz_attempts_user_quiz` - user_id, quiz_id
- `idx_quiz_attempts_completed` - is_completed, completed_at

### certificates
- `idx_certificates_user` - user_id
- `idx_certificates_course` - course_id
- `idx_certificates_number` - certificate_number
- `idx_certificates_verification` - verification_code

### user_achievements
- `idx_user_achievements_user` - user_id
- `idx_user_achievements_type` - achievement_type
- `idx_user_achievements_earned` - earned_at DESC
- `idx_user_achievements_course` - course_id

### user_gamification_profile
- `idx_gamification_user` - user_id
- `idx_gamification_level` - current_level DESC
- `idx_gamification_xp` - total_xp DESC
- `idx_gamification_weekly` - weekly_xp DESC
- `idx_gamification_monthly` - monthly_xp DESC

### user_activity_log
- `idx_activity_user` - user_id
- `idx_activity_type` - activity_type
- `idx_activity_created` - created_at DESC
- `idx_activity_user_date` - user_id, created_at DESC
- `idx_activity_course` - course_id (WHERE course_id IS NOT NULL)

### course_reviews
- `idx_reviews_course` - course_id
- `idx_reviews_user` - user_id
- `idx_reviews_rating` - rating
- `idx_reviews_approved` - is_approved (WHERE is_approved = true)
- `idx_reviews_featured` - is_featured (WHERE is_featured = true)
- `idx_reviews_helpful` - helpful_votes DESC

---

## Constraints

### CHECK Constraints

**categories:**
- `valid_color_format` - Color debe ser formato #RRGGBB

**courses:**
- `valid_rating` - avg_rating >= 0 AND <= 5
- `valid_price` - price_usd >= 0

**lessons:**
- `video_fields_required` - Si content_type='video', video_url y video_duration_seconds requeridos

**enrollments:**
- `valid_progress` - progress_percentage >= 0 AND <= 100
- `valid_completion` - Si status='completed', completed_at y progress=100 requeridos

**progress:**
- `valid_watch_percentage` - watch_percentage >= 0 AND <= 100
- `completion_requires_date` - Si is_completed=true, completed_at requerido

**quizzes:**
- `valid_passing_score` - passing_score_percentage > 0 AND <= 100
- `quiz_association` - Debe tener module_id O lesson_id (no ambos)

**quiz_questions:**
- `valid_options` - Si question_type requiere options, options no puede ser NULL

**quiz_attempts:**
- `valid_score_percentage` - score_percentage >= 0 AND <= 100

**user_gamification_profile:**
- `valid_level` - current_level >= 1
- `valid_xp` - total_xp >= 0
- `valid_streak` - current_streak_days >= 0 AND longest_streak_days >= 0
- `valid_avg_score` - average_quiz_score >= 0 AND <= 100

**course_reviews:**
- `rating` - rating >= 1 AND <= 5

### UNIQUE Constraints

- `categories.slug` - UNIQUE
- `courses.slug` - UNIQUE
- `modules.unique_course_order` - UNIQUE(course_id, display_order)
- `lessons.unique_module_order` - UNIQUE(module_id, display_order)
- `enrollments.unique_user_course` - UNIQUE(user_id, course_id)
- `progress.unique_user_lesson` - UNIQUE(user_id, lesson_id)
- `certificates.certificate_number` - UNIQUE
- `certificates.verification_code` - UNIQUE
- `certificates.unique_user_course_cert` - UNIQUE(user_id, course_id)
- `user_gamification_profile.unique_user_gamification` - UNIQUE(user_id)
- `course_reviews.unique_user_course_review` - UNIQUE(user_id, course_id)

---

## Foreign Keys

### Relaciones con auth.users
- `courses.instructor_id` → `auth.users(id)` ON DELETE RESTRICT
- `enrollments.user_id` → `auth.users(id)` ON DELETE CASCADE
- `progress.user_id` → `auth.users(id)` ON DELETE CASCADE
- `quiz_attempts.user_id` → `auth.users(id)` ON DELETE CASCADE
- `certificates.user_id` → `auth.users(id)` ON DELETE CASCADE
- `user_achievements.user_id` → `auth.users(id)` ON DELETE CASCADE
- `user_gamification_profile.user_id` → `auth.users(id)` ON DELETE CASCADE
- `user_activity_log.user_id` → `auth.users(id)` ON DELETE CASCADE
- `course_reviews.user_id` → `auth.users(id)` ON DELETE CASCADE
- `course_reviews.approved_by` → `auth.users(id)`

### Relaciones internas
- `categories.parent_id` → `categories(id)` ON DELETE SET NULL
- `courses.category_id` → `categories(id)` ON DELETE RESTRICT
- `modules.course_id` → `courses(id)` ON DELETE CASCADE
- `modules.unlock_after_module_id` → `modules(id)` ON DELETE SET NULL
- `lessons.module_id` → `modules(id)` ON DELETE CASCADE
- `enrollments.course_id` → `courses(id)` ON DELETE RESTRICT
- `progress.lesson_id` → `lessons(id)` ON DELETE CASCADE
- `progress.enrollment_id` → `enrollments(id)` ON DELETE CASCADE
- `quizzes.module_id` → `modules(id)` ON DELETE CASCADE
- `quizzes.lesson_id` → `lessons(id)` ON DELETE CASCADE
- `quiz_questions.quiz_id` → `quizzes(id)` ON DELETE CASCADE
- `quiz_attempts.quiz_id` → `quizzes(id)` ON DELETE RESTRICT
- `quiz_attempts.enrollment_id` → `enrollments(id)` ON DELETE SET NULL
- `certificates.course_id` → `courses(id)` ON DELETE RESTRICT
- `certificates.enrollment_id` → `enrollments(id)` ON DELETE RESTRICT
- `user_achievements.course_id` → `courses(id)` ON DELETE SET NULL
- `user_achievements.quiz_id` → `quizzes(id)` ON DELETE SET NULL
- `user_activity_log.course_id` → `courses(id)` ON DELETE SET NULL
- `user_activity_log.lesson_id` → `lessons(id)` ON DELETE SET NULL
- `user_activity_log.quiz_id` → `quizzes(id)` ON DELETE SET NULL
- `course_reviews.course_id` → `courses(id)` ON DELETE CASCADE
- `course_reviews.enrollment_id` → `enrollments(id)` ON DELETE CASCADE

---

## Triggers

### Triggers de updated_at
Aplica a: categories, courses, modules, lessons, enrollments, progress, quizzes, quiz_questions, user_gamification_profile, course_reviews

**Función:** `education.update_updated_at_column()`
**Trigger:** `update_{table}_updated_at`
**Evento:** BEFORE UPDATE
**Acción:** Actualiza `updated_at = NOW()`

### Triggers de lógica de negocio

**update_enrollment_on_progress**
- Tabla: progress
- Función: `education.update_enrollment_progress()`
- Evento: AFTER INSERT OR UPDATE
- Condición: WHEN (NEW.is_completed = true)
- Acción: Recalcula progreso del enrollment

**auto_complete_enrollment_trigger**
- Tabla: enrollments
- Función: `education.auto_complete_enrollment()`
- Evento: BEFORE UPDATE
- Acción: Completa enrollment si progress >= 100%

**generate_certificate_number_trigger**
- Tabla: certificates
- Función: `education.generate_certificate_number()`
- Evento: BEFORE INSERT
- Acción: Genera certificate_number y verification_code

**update_course_rating_on_review_insert**
- Tabla: course_reviews
- Función: `education.update_course_rating_stats()`
- Evento: AFTER INSERT
- Acción: Actualiza avg_rating del curso

**update_course_rating_on_review_update**
- Tabla: course_reviews
- Función: `education.update_course_rating_stats()`
- Evento: AFTER UPDATE
- Condición: rating o is_approved cambió
- Acción: Actualiza avg_rating del curso

**update_course_rating_on_review_delete**
- Tabla: course_reviews
- Función: `education.update_course_rating_stats()`
- Evento: AFTER DELETE
- Acción: Actualiza avg_rating del curso

**update_enrollment_count_on_insert**
- Tabla: enrollments
- Función: `education.update_enrollment_count()`
- Evento: AFTER INSERT
- Acción: Incrementa contador en courses

**update_enrollment_count_on_delete**
- Tabla: enrollments
- Función: `education.update_enrollment_count()`
- Evento: AFTER DELETE
- Acción: Decrementa contador en courses

**update_streak_on_activity**
- Tabla: user_activity_log
- Función: `education.trigger_update_streak()`
- Evento: AFTER INSERT
- Acción: Actualiza streak del usuario

---

## Funciones Públicas

### education.update_user_xp(user_id UUID, xp_to_add INTEGER)
Actualiza XP del usuario y recalcula nivel.

**Parámetros:**
- `user_id`: UUID del usuario
- `xp_to_add`: Cantidad de XP a agregar

**Lógica:**
- Suma XP al total
- Calcula nuevo nivel basado en fórmula cuadrática
- Actualiza weekly_xp y monthly_xp
- Crea achievement si subió de nivel

**Ejemplo:**
```sql
SELECT education.update_user_xp(
  '00000000-0000-0000-0000-000000000001',
  100
);
```

### education.update_user_streak(user_id UUID)
Actualiza streak del usuario basado en actividad diaria.

**Parámetros:**
- `user_id`: UUID del usuario

**Lógica:**
- Verifica última actividad
- Incrementa streak si es día consecutivo
- Resetea streak si se rompió
- Crea achievement en milestones (7, 30, 100 días)

**Ejemplo:**
```sql
SELECT education.update_user_streak(
  '00000000-0000-0000-0000-000000000001'
);
```

---

## Vistas Materializadas Recomendadas

Para mejorar performance en queries frecuentes:

```sql
-- Top cursos por enrollments (actualizar diariamente)
CREATE MATERIALIZED VIEW education.mv_top_courses AS
SELECT * FROM education.v_popular_courses;

CREATE UNIQUE INDEX ON education.mv_top_courses(id);

-- Leaderboards (actualizar cada hora)
CREATE MATERIALIZED VIEW education.mv_leaderboard_weekly AS
SELECT * FROM education.v_leaderboard_weekly;

CREATE UNIQUE INDEX ON education.mv_leaderboard_weekly(user_id);
```

---

## Optimizaciones Recomendadas

### 1. Particionamiento de user_activity_log

Para logs con alto volumen:

```sql
-- Particionar por mes
CREATE TABLE education.user_activity_log_2025_12
  PARTITION OF education.user_activity_log
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
```

### 2. Índices adicionales según uso

```sql
-- Si hay muchas búsquedas por título de curso
CREATE INDEX idx_courses_title_trgm ON education.courses
  USING gin(title gin_trgm_ops);

-- Requiere extension pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
```

### 3. Vacuum y Analyze automático

```sql
-- Configurar autovacuum para tablas con alta escritura
ALTER TABLE education.user_activity_log
  SET (autovacuum_vacuum_scale_factor = 0.01);

ALTER TABLE education.progress
  SET (autovacuum_vacuum_scale_factor = 0.02);
```

---

## Monitoreo

### Queries útiles para monitoreo

**Tamaño de tablas:**
```sql
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'education'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
```

**Índices no usados:**
```sql
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'education' AND idx_scan = 0;
```

**Actividad de enrollments hoy:**
```sql
SELECT COUNT(*)
FROM education.enrollments
WHERE enrolled_at::date = CURRENT_DATE;
```

**Cursos más populares (últimos 7 días):**
```sql
SELECT
  c.title,
  COUNT(e.id) as new_enrollments
FROM education.courses c
LEFT JOIN education.enrollments e ON c.id = e.course_id
  AND e.enrolled_at >= NOW() - INTERVAL '7 days'
GROUP BY c.id, c.title
ORDER BY new_enrollments DESC
LIMIT 10;
```

---

## Backup y Restore

### Backup solo del schema education

```bash
pg_dump -h localhost -U postgres -n education trading_platform > education_backup.sql
```

### Restore

```bash
psql -h localhost -U postgres trading_platform < education_backup.sql
```

---

## Versión y Changelog

**v1.0.0** (2025-12-06)
- Implementación inicial completa
- 14 tablas
- 8 funciones
- 7 vistas
- Sistema de gamificación completo
- Reviews de cursos
- Activity logging

---

**Documentación generada:** 2025-12-06
**Última revisión:** 2025-12-06