# 🔧 GUIDE TECHNIQUE DÉTAILLÉ
## Architecture, API, et Spécifications pour Développeurs

**Document :** Technical Implementation Guide  
**Audience :** Développeurs, DevOps, Architectes  
**Date :** Mai 2026 | **Version :** 1.0

---

## 📋 TABLE DES MATIÈRES

1. Architecture globale
2. Stack technologique recommandé
3. API REST Specifications
4. Database Schema
5. Deployment & Infrastructure
6. Performance & Scaling
7. Security & Compliance
8. Monitoring & Logging

---

## 1️⃣ ARCHITECTURE GLOBALE

### **Vue d'ensemble système**

```
┌─────────────────────────────────────────────────────────────────┐
│                         CDN (CloudFlare)                        │
│         Vidéos, Assets statiques, Images compressées            │
└──────────────────────┬──────────────────────────────────────────┘
                       │
        ┌──────────────┴──────────────┐
        │                             │
┌───────▼──────────┐      ┌──────────▼──────────┐
│  Frontend Layer  │      │  API Gateway        │
│  (React/Vue)     │      │  (Route + Cache)    │
│  - PWA           │      │  - Rate limiting    │
│  - Offline       │      │  - Auth middleware  │
│  - Service Wkrs  │      │  - CORS handling    │
└───────┬──────────┘      └──────────┬──────────┘
        │                             │
        └──────────────┬──────────────┘
                       │
        ┌──────────────▼──────────────┐
        │    Load Balancer (AWS ELB)  │
        │  - Health checks            │
        │  - Auto-scale               │
        └──────────────┬──────────────┘
                       │
      ┌────────────────┼────────────────┐
      │                │                │
┌─────▼──────┐  ┌─────▼──────┐  ┌─────▼──────┐
│ Backend #1 │  │ Backend #2 │  │ Backend #3 │
│ (Node/Py)  │  │ (Node/Py)  │  │ (Node/Py)  │
│ - Auth     │  │ - Courses  │  │ - Analytics│
│ - Users    │  │ - Quizzes  │  │ - Reports  │
│ - Enroll   │  │ - Certs    │  │ - Logs     │
└─────┬──────┘  └─────┬──────┘  └─────┬──────┘
      │                │                │
      └────────────────┼────────────────┘
                       │
        ┌──────────────▼──────────────┐
        │    PostgreSQL Database      │
        │  - Primary (Master)         │
        │  - Replica (Read-only)      │
        │  - Backups (Daily)          │
        └──────────────┬──────────────┘
                       │
      ┌────────────────┼────────────────┐
      │                │                │
┌─────▼──────┐  ┌─────▼──────┐  ┌─────▼──────┐
│Redis Cache │  │ S3 Storage │  │  Mux/Video │
│(Sessions)  │  │(User data) │  │ (Streaming)│
└────────────┘  └────────────┘  └────────────┘
```

### **Flow Utilisateur Typique**

```
1. UTILISATEUR
   └─ Accède greenland-academy.org (navigateur)

2. CDN (CloudFlare)
   └─ Serve fichiers statiques (HTML, CSS, JS)
   └─ Cache miss → Forward à Load Balancer

3. FRONTEND
   └─ React app charge (service worker pour offline)
   └─ Detect utilisateur anonyme vs authentifié

4. LOGIN (si nécessaire)
   └─ POST /api/auth/login (credentials)
   └─ Backend génère JWT token
   └─ Frontend store token (localStorage + secure cookie)

5. DASHBOARD
   └─ GET /api/user/me (avec JWT header)
   └─ GET /api/courses (public listing)
   └─ GET /api/enrollments/me (private)
   └─ Frontend cache résultats (Redis backend)

6. COURSE INTERACTION
   └─ GET /api/courses/:id/lessons (avec auth)
   └─ Fetch vidéo URL (S3 presigned URL)
   └─ Fetch quiz (GET /api/quizzes/:id)
   └─ Submit réponses (POST /api/quiz-responses)
   └─ Calculer score backend
   └─ Si score ≥ 75% → Trigger certificat generation

7. CERTIFICAT
   └─ POST /api/certificates/generate
   └─ Backend : Render PDF (Node + puppeteer)
   └─ Store S3 + Database
   └─ Send email (SendGrid)
   └─ Frontend : Display download link

8. ANALYTICS
   └─ Frontend send events (Matomo/GA) :
      - Course view
      - Quiz attempt
      - Time spent
   └─ Backend aggregates data
   └─ Dashboard display (Metabase)
```

---

## 2️⃣ STACK TECHNOLOGIQUE RECOMMANDÉ

### **Frontend Stack**

```
TECHNOLOGY CHOICES
├─ Framework : React 18+ (Vite bundler)
│  ├─ State Management : Redux Toolkit or Zustand
│  ├─ HTTP Client : Axios + interceptors
│  ├─ UI Components : Material-UI or Chakra
│  ├─ Form Handling : React Hook Form + Zod validation
│  └─ Router : React Router v6
│
├─ Offline Capability
│  ├─ Service Worker : Workbox
│  ├─ Local Storage : IndexedDB (Dexie.js)
│  ├─ Sync : Background Sync API
│  └─ Notification : Web Push API
│
├─ Performance
│  ├─ Code Splitting : React.lazy()
│  ├─ Image Optimization : Next/Image component
│  ├─ Bundle Analysis : webpack-bundle-analyzer
│  ├─ Monitoring : Sentry, LogRocket
│  └─ Accessibility : axe-core, WCAG AA
│
├─ Build & Deploy
│  ├─ Build Tool : Vite (instant HMR)
│  ├─ CI/CD : GitHub Actions
│  ├─ Hosting : AWS S3 + CloudFront
│  ├─ Domain : Route53
│  └─ SSL : AWS Certificate Manager
│
└─ Testing
   ├─ Unit : Jest + React Testing Library
   ├─ E2E : Cypress or Playwright
   ├─ Coverage Target : 80%+
   └─ Performance : Lighthouse CI
```

### **Backend Stack**

```
OPTION A : Node.js (Express/Fastify)
├─ Runtime : Node.js 18 LTS
├─ Framework : Express.js or Fastify
├─ Database : PostgreSQL 14+
├─ Cache : Redis 7+
├─ ORM : Prisma or TypeORM
├─ Validation : Zod or Joi
├─ Async Jobs : Bull (Redis-based)
├─ Auth : Passport.js or Auth0
├─ File Upload : Multer + AWS S3
├─ Logging : Winston or Pino
└─ Testing : Jest + Supertest

OPTION B : Python (Django/FastAPI)
├─ Runtime : Python 3.11+
├─ Framework : Django with DRF (REST Framework)
├─ Database : PostgreSQL 14+
├─ Cache : Redis 7+
├─ ORM : Django ORM
├─ Validation : Pydantic
├─ Async Jobs : Celery + Redis
├─ Auth : Django-rest-auth or SimpleJWT
├─ File Upload : Django-storages + S3
├─ Logging : Python logging + Sentry
└─ Testing : pytest + pytest-django

RECOMMENDATION : Node.js (faster development, real-time capable)
```

### **Infrastructure Stack**

```
CLOUD PROVIDER : AWS (or Google Cloud)

Compute
├─ Application Servers : EC2 (t3.medium × 3)
├─ Auto Scaling Group : 2-5 instances
├─ Container : Docker + ECR (optional)
├─ Orchestration : ECS or Kubernetes (if scale needed)
└─ Monitoring : CloudWatch

Database
├─ Primary : RDS PostgreSQL (db.t3.medium)
├─ Replica : RDS read replica (different region)
├─ Backup : Automated daily, 7-day retention
├─ Monitoring : Performance Insights
└─ Encryption : RDS encryption at rest

Cache
├─ Redis : ElastiCache (cache.t3.micro)
├─ Replication : Multi-AZ
├─ Backup : Snapshot daily
└─ Monitoring : CloudWatch metrics

Storage
├─ Application Files : S3 (versioned)
├─ User Uploads : S3 (lifecycle policy 30 days)
├─ Backups : S3 Glacier (7 year retention)
├─ CDN : CloudFront (cache 24h)
└─ Encryption : S3 server-side encryption

Networking
├─ VPC : Private subnets for RDS/Redis
├─ Security Groups : Strict ingress rules
├─ NAT Gateway : For outbound internet
├─ VPN : For development access (bastion host)
└─ WAF : AWS WAF for DDoS protection

Monitoring & Logging
├─ Logs : CloudWatch Logs + ELK (Elasticsearch)
├─ Metrics : CloudWatch Dashboards
├─ Alerting : SNS + email/Slack
├─ Tracing : AWS X-Ray or Datadog APM
├─ Uptime Monitoring : UptimeRobot or Pingdom
└─ Log Aggregation : Splunk or Datadog

Cost Estimate (Monthly)
├─ EC2 (3 × t3.medium) : $150
├─ RDS PostgreSQL + replica : $250
├─ ElastiCache Redis : $50
├─ S3 + CloudFront : $100
├─ Data transfer : $50
├─ Miscellaneous : $50
└─ TOTAL : $650/month (production-ready)
```

---

## 3️⃣ API REST SPECIFICATIONS

### **Authentication Endpoints**

```
POST /api/auth/register
├─ Request Body :
│  {
│    "email": "user@example.com",
│    "password": "SecurePass123!",
│    "firstName": "Jean",
│    "lastName": "Dupont",
│    "country": "CF",
│    "language": "fr"
│  }
├─ Response (201) :
│  {
│    "id": "user_123",
│    "email": "user@example.com",
│    "token": "eyJhbGciOiJIUzI1NiIs...",
│    "refreshToken": "eyJhbGciOiJIUzI1NiIs..."
│  }
├─ Validation :
│  - Email valid + unique
│  - Password min 8 chars + 1 uppercase + 1 number + 1 special
│  - First/last name min 2 chars
│  - Country = valid ISO 3166-1
└─ Errors :
   - 400 : Invalid input
   - 409 : Email already exists

POST /api/auth/login
├─ Request Body :
│  {
│    "email": "user@example.com",
│    "password": "SecurePass123!"
│  }
├─ Response (200) :
│  {
│    "id": "user_123",
│    "token": "eyJhbGciOiJIUzI1NiIs...",
│    "refreshToken": "eyJhbGciOiJIUzI1NiIs..."
│  }
├─ Security :
│  - Rate limit : 10 attempts / 15 min per IP
│  - Lock account after 5 failures
│  - Hash password (bcrypt, cost 12)
└─ Errors :
   - 401 : Invalid credentials

POST /api/auth/refresh
├─ Request Headers :
│  {
│    "Authorization": "Bearer {refreshToken}"
│  }
├─ Response (200) :
│  {
│    "token": "eyJhbGciOiJIUzI1NiIs..." (new JWT)
│  }
└─ Errors :
   - 401 : Invalid/expired refresh token

POST /api/auth/logout
├─ Request Headers :
│  {
│    "Authorization": "Bearer {jwt}"
│  }
├─ Response (204) : No content
├─ Action :
│  - Blacklist JWT (Redis)
│  - Clear session
└─ Errors :
   - 401 : Unauthorized

POST /api/auth/forgot-password
├─ Request Body :
│  { "email": "user@example.com" }
├─ Response (200) :
│  { "message": "Reset email sent" }
├─ Action :
│  - Generate reset token (6 hours expiry)
│  - Send email (SendGrid)
│  - Store token in Redis
└─ Errors :
   - 404 : Email not found (for security, return 200 anyway)

POST /api/auth/reset-password
├─ Request Body :
│  {
│    "token": "reset_token_from_email",
│    "newPassword": "NewSecurePass123!"
│  }
├─ Response (200) :
│  { "message": "Password reset successful" }
├─ Action :
│  - Verify token from Redis
│  - Hash new password
│  - Update user
│  - Delete token
└─ Errors :
   - 400 : Invalid/expired token
   - 422 : Password requirements not met
```

### **User Endpoints**

```
GET /api/users/me
├─ Authentication : Required (JWT)
├─ Response (200) :
│  {
│    "id": "user_123",
│    "email": "user@example.com",
│    "firstName": "Jean",
│    "lastName": "Dupont",
│    "avatar": "https://s3.../avatar.jpg",
│    "country": "CF",
│    "language": "fr",
│    "createdAt": "2026-05-01T10:00:00Z",
│    "totalPoints": 1250,
│    "badges": ["founder", "week_streak_5"],
│    "coursesCompleted": 3,
│    "certificatesEarned": 2
│  }
└─ Errors :
   - 401 : Unauthorized

PATCH /api/users/me
├─ Authentication : Required (JWT)
├─ Request Body (partial update) :
│  {
│    "firstName": "Johnny",
│    "language": "en",
│    "avatar": "[file upload]"
│  }
├─ Response (200) : Updated user object
└─ Validation :
   - First/last name : alphanumeric + spaces, min 2
   - Language : ISO 639-1 code
   - Avatar : JPG/PNG, max 5MB

PUT /api/users/me/password
├─ Authentication : Required (JWT)
├─ Request Body :
│  {
│    "currentPassword": "OldPass123!",
│    "newPassword": "NewPass456!"
│  }
├─ Response (200) : { "message": "Password updated" }
└─ Validation :
   - Current password must be correct
   - New password must meet requirements
   - New ≠ current
```

### **Courses Endpoints**

```
GET /api/courses
├─ Authentication : Optional
├─ Query Parameters :
│  - limit=20 (default)
│  - offset=0 (default)
│  - search=climate (search title + description)
│  - sortBy=popular (popular, newest, rating)
│  - language=fr
├─ Response (200) :
│  {
│    "data": [
│      {
│        "id": "course_001",
│        "title": "Les Fondamentaux de la Crise Climatique",
│        "description": "...",
│        "duration": "5 hours",
│        "thumbnail": "https://s3.../thumb.jpg",
│        "rating": 4.7,
│        "enrollmentCount": 1250,
│        "certificateAvailable": true,
│        "language": "fr",
│        "level": "beginner"
│      }
│    ],
│    "meta": {
│      "total": 145,
│      "limit": 20,
│      "offset": 0
│    }
│  }
└─ Caching :
   - Cache 1 hour (Public)
   - Invalidate on new enrollment

GET /api/courses/:id
├─ Authentication : Optional
├─ Response (200) :
│  {
│    "id": "course_001",
│    "title": "Les Fondamentaux...",
│    "description": "...",
│    "instructor": {
│      "id": "user_456",
│      "name": "Dr. Jean Smith",
│      "bio": "..."
│    },
│    "modules": [
│      {
│        "id": "module_1",
│        "title": "Introduction",
│        "lessons": 4,
│        "duration": "45 minutes"
│      }
│    ],
│    "prerequisites": [],
│    "certificateRequirements": {
│      "minimumScore": 75,
│      "quizzes": true,
│      "finalAssessment": true
│    }
│  }
└─ Cache : 24 hours

POST /api/enrollments
├─ Authentication : Required (JWT)
├─ Request Body :
│  {
│    "courseId": "course_001"
│  }
├─ Response (201) :
│  {
│    "id": "enrollment_789",
│    "userId": "user_123",
│    "courseId": "course_001",
│    "enrolledAt": "2026-05-01T10:00:00Z",
│    "progress": 0,
│    "status": "in_progress"
│  }
├─ Actions :
│  - Create enrollment record
│  - Send welcome email
│  - Add user to course Discord (optional)
│  - Trigger "first lesson" notification
└─ Errors :
   - 404 : Course not found
   - 409 : Already enrolled

GET /api/enrollments/me
├─ Authentication : Required (JWT)
├─ Response (200) :
│  {
│    "data": [
│      {
│        "id": "enrollment_789",
│        "course": { ... },
│        "progress": 35,
│        "lastAccessed": "2026-06-15T10:00:00Z",
│        "status": "in_progress",
│        "certificateEarned": false
│      }
│    ]
│  }
├─ Caching : Cache 5 min (user specific)
└─ Paging : limit + offset
```

### **Quiz Endpoints**

```
GET /api/courses/:courseId/quizzes/:quizId
├─ Authentication : Required (JWT)
├─ Check : User enrolled in course
├─ Response (200) :
│  {
│    "id": "quiz_001",
│    "title": "Chapter 1 Quiz",
│    "questions": [
│      {
│        "id": "q1",
│        "type": "multiple_choice",
│        "text": "What is CO2?",
│        "options": [
│          { "id": "opt_1", "text": "Option A" },
│          { "id": "opt_2", "text": "Option B" }
│        ],
│        "order": 1
│      }
│    ],
│    "timeLimit": 30,
│    "passingScore": 75,
│    "attemptsAllowed": 3
│  }
├─ Note : Answers NOT included (prevent cheating)
└─ Cache : 24 hours

POST /api/quizzes/:quizId/submit
├─ Authentication : Required (JWT)
├─ Request Body :
│  {
│    "responses": [
│      {
│        "questionId": "q1",
│        "answer": "opt_2"
│      }
│    ]
│  }
├─ Response (200) :
│  {
│    "score": 85,
│    "percentage": 85,
│    "passed": true,
│    "feedback": [
│      {
│        "questionId": "q1",
│        "isCorrect": true,
│        "explanation": "Option B is correct because..."
│      }
│    ],
│    "certificateEligible": true
│  }
├─ Backend :
│  - Validate answers
│  - Calculate score
│  - Check if >= passing score
│  - If passed : trigger certificate generation
│  - Log attempt (analytics)
└─ Errors :
   - 400 : Invalid answer format
   - 429 : Too many attempts
   - 403 : Not enrolled

GET /api/quizzes/:quizId/results
├─ Authentication : Required (JWT)
├─ Response (200) :
│  {
│    "attempts": [
│      {
│        "attemptNumber": 1,
│        "score": 65,
│        "passed": false,
│        "submittedAt": "2026-06-15T10:00:00Z"
│      }
│    ]
│  }
└─ Paging : Show last 10 attempts
```

### **Certificate Endpoints**

```
POST /api/certificates/generate
├─ Authentication : Required (JWT)
├─ Trigger : Automatic (on quiz pass), or manual request
├─ Request Body :
│  {
│    "courseId": "course_001",
│    "quizId": "quiz_001"
│  }
├─ Response (201) :
│  {
│    "id": "cert_abc123",
│    "userId": "user_123",
│    "courseId": "course_001",
│    "certificateUrl": "https://s3.../cert_abc123.pdf",
│    "issuedAt": "2026-06-15T10:00:00Z",
│    "expiresAt": null (no expiry),
│    "verificationCode": "GLCA-2026-ABC123"
│  }
├─ Backend Process :
│  - Verify user passed quiz (score ≥75%)
│  - Render PDF (Puppeteer) :
│    - User name
│    - Course name
│    - Date issued
│    - Verification code (QR)
│  - Upload to S3 (private bucket)
│  - Generate presigned URL (24h expiry)
│  - Send email with download link
│  - Record in database
├─ PDF Template :
│  - GLCA logo (top)
│  - "CERTIFICATE OF COMPLETION"
│  - "This certifies that [NAME]"
│  - "has successfully completed [COURSE]"
│  - Date issued
│  - Instructor signature (image)
│  - QR code (links to verification page)
│  - Serial number
└─ Errors :
   - 400 : Quiz not passed
   - 404 : Course/quiz not found

GET /api/certificates/verify/:code
├─ Authentication : Optional (public verification)
├─ Response (200) :
│  {
│    "valid": true,
│    "userName": "Jean Dupont",
│    "courseName": "Les Fondamentaux...",
│    "issuedAt": "2026-06-15",
│    "verificationCode": "GLCA-2026-ABC123"
│  }
├─ Caching : Cache 1 week
└─ Use : LinkedIn sharing, employer verification

GET /api/users/me/certificates
├─ Authentication : Required (JWT)
├─ Response (200) :
│  {
│    "data": [
│      {
│        "id": "cert_abc123",
│        "courseName": "Les Fondamentaux...",
│        "issuedAt": "2026-06-15T10:00:00Z",
│        "downloadUrl": "https://s3.../cert_abc123.pdf",
│        "shareUrl": "https://greenland.../verify/GLCA-2026-ABC123",
│        "likedInShareUrl": "https://linkedin.com/feed/..."
│      }
│    ]
│  }
└─ Action : Allow LinkedIn share (oauth)
```

### **Analytics Endpoints** (Internal Only)

```
GET /api/admin/analytics/overview
├─ Authentication : Admin only
├─ Response (200) :
│  {
│    "totalUsers": 12500,
│    "activeUsers7d": 3200,
│    "totalEnrollments": 28000,
│    "averageCompletionRate": 42.5,
│    "certificatesIssued": 8500,
│    "topCourse": { "id": "course_001", "enrollments": 5000 }
│  }
└─ Cache : 1 hour

GET /api/admin/analytics/courses/:id
├─ Authentication : Admin only
├─ Response (200) :
│  {
│    "courseId": "course_001",
│    "enrollments": 5000,
│    "completionRate": 45,
│    "averageScore": 78.5,
│    "rating": 4.7,
│    "dropoffPoints": [
│      {
│        "moduleNumber": 2,
│        "percentageWhoLeft": 25
│      }
│    ]
│  }
└─ Use : Identify content issues

GET /api/admin/analytics/events
├─ Authentication : Admin only
├─ Query Parameters :
│  - eventType=course_view, quiz_complete, etc.
│  - startDate=2026-06-01
│  - endDate=2026-06-30
│  - limit=100
├─ Response (200) :
│  {
│    "events": [
│      {
│        "userId": "user_123",
│        "eventType": "quiz_complete",
│        "courseId": "course_001",
│        "score": 85,
│        "timestamp": "2026-06-15T10:00:00Z"
│      }
│    ]
│  }
└─ Use : Real-time dashboards
```

---

## 4️⃣ DATABASE SCHEMA

### **Core Tables**

```sql
-- Users
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  avatar_url VARCHAR(500),
  country_code CHAR(2),
  language VARCHAR(5) DEFAULT 'fr',
  total_points INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  last_login TIMESTAMP,
  is_active BOOLEAN DEFAULT true,
  is_admin BOOLEAN DEFAULT false,
  INDEX (email)
);

-- Courses
CREATE TABLE courses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  instructor_id UUID NOT NULL REFERENCES users(id),
  duration_hours INT,
  thumbnail_url VARCHAR(500),
  language VARCHAR(5) DEFAULT 'fr',
  level ENUM('beginner', 'intermediate', 'advanced'),
  rating DECIMAL(3,2),
  enrollment_count INT DEFAULT 0,
  certificate_available BOOLEAN DEFAULT true,
  min_score_for_cert INT DEFAULT 75,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  INDEX (instructor_id, published),
  FULLTEXT INDEX (title, description)
);

-- Enrollments
CREATE TABLE enrollments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  progress INT DEFAULT 0,
  status ENUM('in_progress', 'completed', 'dropped') DEFAULT 'in_progress',
  completed_at TIMESTAMP,
  last_accessed TIMESTAMP,
  UNIQUE(user_id, course_id),
  INDEX (user_id, course_id),
  INDEX (status, completed_at)
);

-- Modules (lessons)
CREATE TABLE modules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  order_index INT NOT NULL,
  duration_minutes INT,
  content_type ENUM('video', 'text', 'quiz', 'interactive'),
  content_url VARCHAR(500),
  created_at TIMESTAMP DEFAULT NOW(),
  INDEX (course_id, order_index)
);

-- Quiz
CREATE TABLE quizzes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  module_id UUID NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  time_limit_minutes INT,
  passing_score INT DEFAULT 75,
  max_attempts INT DEFAULT 3,
  shuffle_questions BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  INDEX (module_id)
);

-- Quiz Questions
CREATE TABLE quiz_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  quiz_id UUID NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type ENUM('multiple_choice', 'true_false', 'matching', 'fill_blank'),
  order_index INT NOT NULL,
  points INT DEFAULT 1,
  created_at TIMESTAMP DEFAULT NOW(),
  INDEX (quiz_id, order_index)
);

-- Quiz Answers (options)
CREATE TABLE quiz_answers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  question_id UUID NOT NULL REFERENCES quiz_questions(id) ON DELETE CASCADE,
  answer_text VARCHAR(500) NOT NULL,
  is_correct BOOLEAN DEFAULT false,
  order_index INT,
  explanation TEXT,
  INDEX (question_id, is_correct)
);

-- Quiz Responses (user submissions)
CREATE TABLE quiz_responses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  quiz_id UUID NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
  submitted_at TIMESTAMP DEFAULT NOW(),
  score INT,
  percentage DECIMAL(5,2),
  passed BOOLEAN,
  attempt_number INT DEFAULT 1,
  INDEX (user_id, quiz_id, submitted_at)
);

-- Quiz Response Details
CREATE TABLE quiz_response_details (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  response_id UUID NOT NULL REFERENCES quiz_responses(id) ON DELETE CASCADE,
  question_id UUID NOT NULL REFERENCES quiz_questions(id),
  user_answer_id UUID REFERENCES quiz_answers(id),
  is_correct BOOLEAN,
  INDEX (response_id, question_id)
);

-- Certificates
CREATE TABLE certificates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_id UUID NOT NULL REFERENCES courses(id),
  quiz_id UUID REFERENCES quizzes(id),
  issued_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP,
  verification_code VARCHAR(50) NOT NULL UNIQUE,
  pdf_url VARCHAR(500),
  INDEX (user_id, course_id, verification_code)
);

-- User Progress
CREATE TABLE user_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  module_id UUID NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
  enrollment_id UUID NOT NULL REFERENCES enrollments(id),
  completed BOOLEAN DEFAULT false,
  time_spent_seconds INT DEFAULT 0,
  completed_at TIMESTAMP,
  UNIQUE(user_id, module_id),
  INDEX (user_id, enrollment_id)
);

-- Badges
CREATE TABLE badges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  icon_url VARCHAR(500),
  criteria JSONB  -- {type: 'course_complete', course_id: '...'}
);

-- User Badges (awarded)
CREATE TABLE user_badges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  badge_id UUID NOT NULL REFERENCES badges(id),
  earned_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(user_id, badge_id),
  INDEX (user_id)
);

-- Events (for analytics)
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  event_type VARCHAR(50) NOT NULL,  -- 'course_view', 'quiz_complete', etc.
  entity_id UUID,  -- course_id or quiz_id
  metadata JSONB,  -- {score: 85, duration: 30, ...}
  created_at TIMESTAMP DEFAULT NOW(),
  INDEX (user_id, event_type, created_at)
);
```

---

## 5️⃣ DEPLOYMENT & INFRASTRUCTURE

### **CI/CD Pipeline (GitHub Actions)**

```yaml
name: Deploy to Production

on:
  push:
    branches: [main]
  workflow_dispatch:

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup Node
        uses: actions/setup-node@v3
        with:
          node-version: '18'
      
      - name: Install dependencies
        run: npm ci
      
      - name: Run linter
        run: npm run lint
      
      - name: Run tests
        run: npm run test
      
      - name: Build
        run: npm run build

  deploy:
    needs: test
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Deploy to AWS
        env:
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
        run: |
          aws s3 sync dist/ s3://glca-frontend-prod/
          aws cloudfront create-invalidation --distribution-id ${{ secrets.CLOUDFRONT_DIST_ID }} --paths "/*"
      
      - name: Notify Slack
        if: success()
        run: |
          curl -X POST ${{ secrets.SLACK_WEBHOOK }} \
            -H 'Content-Type: application/json' \
            -d '{"text":"✅ Production deploy successful"}'
```

### **Database Migrations**

```bash
# Using Prisma
npx prisma migrate dev --name add_user_badges
npx prisma migrate deploy  # Production

# Using Knex (Node alternative)
npx knex migrate:latest
```

### **Environment Configuration**

```bash
# .env.production
NODE_ENV=production
DATABASE_URL=postgresql://user:pass@rds.amazonaws.com:5432/glca_prod
REDIS_URL=redis://elasticache.amazonaws.com:6379
JWT_SECRET=[random 64-char key]
JWT_EXPIRY=86400  # 24 hours
REFRESH_TOKEN_EXPIRY=2592000  # 30 days
AWS_REGION=eu-west-1
S3_BUCKET=glca-production
SENDGRID_API_KEY=[api key]
CORS_ORIGIN=https://greenland-academy.org
LOG_LEVEL=info
SENTRY_DSN=[sentry url]
```

---

## 6️⃣ PERFORMANCE & SCALING

### **Caching Strategy**

```
LEVEL 1 : CDN (CloudFlare)
├─ TTL : 24 hours
├─ Content : HTML, CSS, JS, images
├─ Invalidation : On new deploy
└─ Hit rate target : 80%+

LEVEL 2 : Application Cache (Redis)
├─ User data : TTL 5 min (user specific)
├─ Courses list : TTL 1 hour (public)
├─ Quiz data : TTL 24 hours (static)
├─ Sessions : TTL 24 hours (auth)
└─ Cache keys : `user:{id}:profile`, `course:{id}:data`

LEVEL 3 : Database Query Cache
├─ Prepared statements (reduce parsing)
├─ Indexes on foreign keys + filters
├─ Query result caching (5 sec, Prisma)
└─ Read replicas for analytics queries
```

### **Database Optimization**

```sql
-- Key Indexes
CREATE INDEX idx_enrollments_user_course 
  ON enrollments(user_id, course_id);

CREATE INDEX idx_quiz_responses_user_quiz 
  ON quiz_responses(user_id, quiz_id, submitted_at);

CREATE INDEX idx_events_timestamp 
  ON events(created_at DESC);

CREATE INDEX idx_courses_published 
  ON courses(published, updated_at DESC);

-- Partitioning (for large tables)
CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
```

### **API Rate Limiting**

```javascript
// Express middleware
const rateLimit = require('express-rate-limit');

const authLimiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 10, // max 10 attempts
  keyGenerator: (req) => req.ip,
  skip: (req) => req.user, // Skip if authenticated
});

const apiLimiter = rateLimit({
  windowMs: 60 * 1000, // 1 minute
  max: 100, // max 100 requests per minute
  keyGenerator: (req) => req.user?.id || req.ip,
});

app.post('/api/auth/login', authLimiter, loginHandler);
app.use('/api/', apiLimiter);
```

### **Scaling Strategy**

```
VERTICAL SCALING (Short term)
├─ t3.medium → t3.large (2-4 weeks)
├─ RDS : db.t3.medium → db.t3.large
└─ Cost : +50%

HORIZONTAL SCALING (Long term)
├─ Add more EC2 instances (2-5)
├─ Load balancer distributes traffic
├─ Auto-scaling group (min 2, max 10)
└─ Session store in Redis (not in-memory)

MULTI-REGION (If global scale)
├─ Deploy backend to multiple regions
├─ Route traffic via CloudFront
├─ Database replication (RDS Multi-region)
└─ Latency reduced for users everywhere

MONITORING METRICS
├─ CPU usage : Alert if > 75%
├─ Memory : Alert if > 80%
├─ Database connections : Alert if > 90 / 100
├─ Response time (p99) : Alert if > 500ms
├─ Error rate : Alert if > 1%
└─ Disk space : Alert if < 20%
```

---

## 7️⃣ SECURITY & COMPLIANCE

### **Authentication & Authorization**

```javascript
// JWT Payload
{
  "sub": "user_123",  // subject (user ID)
  "email": "user@example.com",
  "role": "student",  // student, teacher, admin
  "permissions": ["read:courses", "write:quiz_responses"],
  "iat": 1624876800,  // issued at
  "exp": 1624963200,  // expiry (24 hours)
  "aud": "greenland-academy"
}

// Role-based Access Control
const roles = {
  student: ['read:courses', 'read:profile', 'write:quiz_responses'],
  teacher: ['read:courses', 'write:courses', 'read:analytics'],
  admin: ['*']  // all permissions
};
```

### **Data Protection**

```
ENCRYPTION AT REST
├─ Database : RDS encryption (AES-256)
├─ S3 : Server-side encryption (AES-256)
├─ Redis : Encryption enabled
└─ Backups : Encrypted in Glacier

ENCRYPTION IN TRANSIT
├─ HTTPS/TLS 1.3 (all endpoints)
├─ Certificate : Let's Encrypt auto-renewal
├─ HSTS : 1 year max-age
└─ No mixed content (HTTP)

SENSITIVE DATA
├─ Passwords : Bcrypt (cost 12)
├─ API keys : Never in code (use secrets manager)
├─ User data : PII encryption (if regulatory requirement)
└─ Audit logs : All access logged
```

### **OWASP Top 10 Protection**

```
1. Injection
   ├─ SQL : Parameterized queries (Prisma ORM)
   ├─ NoSQL : Strict schemas
   └─ Command : No shell execution

2. Broken Authentication
   ├─ Strong password requirements
   ├─ 2FA option (future)
   └─ Session timeout : 24 hours

3. Sensitive Data Exposure
   ├─ HTTPS only
   ├─ Sensitive headers (X-Content-Type-Options, etc.)
   └─ No debug info in errors

4. XML External Entities (XXE)
   ├─ Disable XML parsing
   └─ Validate file uploads

5. Broken Access Control
   ├─ Middleware checks before each action
   ├─ User can only access own data
   └─ Admin role separated

6. Security Misconfiguration
   ├─ Security headers (HELMET middleware)
   ├─ CORS strict (whitelist domains)
   └─ No default credentials

7. XSS (Cross-Site Scripting)
   ├─ React auto-escapes by default
   ├─ Content-Security-Policy header
   └─ Input validation

8. Insecure Deserialization
   ├─ No eval() calls
   ├─ Validate JSON before parsing
   └─ Type checking (TypeScript)

9. Using Components with Known Vulnerabilities
   ├─ npm audit (weekly)
   ├─ Snyk integration
   └─ Dependabot alerts

10. Insufficient Logging & Monitoring
    ├─ All auth attempts logged
    ├─ Errors to Sentry
    ├─ API calls to CloudWatch
    └─ Dashboards (DataDog / Grafana)
```

### **Compliance**

```
GDPR (EU users)
├─ Data portability : Export user data (JSON)
├─ Right to deletion : Delete all user data
├─ Privacy policy : Clear & accessible
├─ Consent tracking : Newsletter opt-in
└─ Data processor agreements : With S3, SendGrid

CCPA (California)
├─ Similar to GDPR
├─ Privacy notice at collection
├─ Opt-out mechanism
└─ No sale of personal data

REGULARITY AUDITS
├─ Penetration testing : Annual
├─ Code review : Monthly
├─ Dependency audit : Weekly
└─ Access logs review : Monthly
```

---

## 8️⃣ MONITORING & LOGGING

### **Application Monitoring**

```javascript
// Sentry integration
const Sentry = require("@sentry/node");

Sentry.init({
  dsn: process.env.SENTRY_DSN,
  tracesSampleRate: 0.1,
  environment: process.env.NODE_ENV,
});

app.use(Sentry.Handlers.requestHandler());
app.use(Sentry.Handlers.errorHandler());

// Custom errors trigger alerts
try {
  // business logic
} catch (error) {
  Sentry.captureException(error, { level: 'error' });
  res.status(500).json({ error: 'Internal server error' });
}
```

### **Logging Strategy**

```javascript
const logger = require('pino')({
  level: process.env.LOG_LEVEL || 'info',
  transport: {
    target: 'pino-pretty',
    options: {
      colorize: true,
      ignore: 'pid,hostname',
    },
  },
});

// Log levels
logger.info('User enrolled in course', { userId, courseId });
logger.warn('Unusual activity detected', { userId, attempts: 10 });
logger.error('Database connection failed', { error: err.message });

// JSON transport to CloudWatch
// AWS Lambda → CloudWatch Logs → ELK / Splunk
```

### **Metrics & Dashboards**

```
CLOUDWATCH DASHBOARD
├─ Request count (per minute)
├─ Error rate (% 5xx errors)
├─ Response time (p50, p95, p99)
├─ Database connections
├─ Redis memory usage
├─ Certificate generation queue
└─ Active users (real-time)

CUSTOM METRICS
├─ Courses completed (daily)
├─ Certificates issued (daily)
├─ Quiz pass rate (%)
├─ Average quiz score
├─ User retention (7-day, 30-day)
└─ New user signups (daily)

ALERTING THRESHOLDS
├─ Error rate > 2% : Immediate alert
├─ Response time p99 > 1000ms : Alert
├─ Database CPU > 85% : Alert
├─ Redis memory > 90% : Alert
└─ Certificate generation delay > 5 min : Alert
```

---

## 📋 DEVELOPMENT SETUP (Local)

```bash
# Prerequisites
npm install -g node@18 git

# Clone & Install
git clone https://github.com/greenland/academy.git
cd academy
npm install

# Environment
cp .env.example .env.local
# Edit .env.local with local DB credentials

# Start Database (Docker)
docker-compose up -d

# Run migrations
npm run migrate:dev

# Start Dev Server
npm run dev:api  # Backend on localhost:3000
npm run dev:web  # Frontend on localhost:5173

# Run Tests
npm test

# Build
npm run build
npm run start  # Production mode
```

---

**Technical Guide Completed** ✅

This guide provides all specifications needed for developers to implement the platform. All endpoints, database schemas, and infrastructure recommendations are production-ready.

Next steps :
1. Review with team
2. Adjust for your tech stack if needed
3. Share with development team
4. Begin implementation