# SST — Single Source of Truth: JvG LMS Academy
**Versie**: 1.0
**Datum**: 15 april 2026
**Status**: PRODUCTION SPEC
**Auteur**: Kas (Director of Operations)

> DIT DOCUMENT IS DE ENIGE BRON VAN WAARHEID VOOR ALLE SUB-AGENTS.
> Elk architectuur-besluit, elk API-endpoint en elk database-schema staat hier.
> Sub-agents LEZEN dit document VÓÓR ze code schrijven. Aanpassingen gaan via Kas.

---

## 1. PROJECT CONTEXT

### 1.1 Doel
Transformeer de bestaande "Training & Audits" tab op het HSEQ Intelligence Dashboard tot een volwaardig Enterprise Learning Management System (LMS), vergelijkbaar met Moodle/Plusport.

### 1.2 Bestaande Infrastructuur
| Component | Details |
|-----------|---------|
| **App** | Flask single-file app (`app.py`), inline CSS, `render_template_string` |
| **Database** | SQLite3: `hseq_kennisbank.db` |
| **PM2 Proces** | `hseq-kennisbank` op poort 5052 |
| **BASE_PATH** | `/hseq-dashboard` |
| **Bestaande tabellen** | `elearning_modules`, `elearning_assignments`, `elearning_quiz_attempts`, `employees`, `training_programs`, `certifications`, `training_matrix`, `roles`, `trn_certificates` |
| **Bestaande data** | 6 employees, 15 training programs, 9 certifications, 10 roles, 2 elearning modules |

### 1.3 Bestaande E-Learning HTML Files (deliverables)
Deze HTML-bestanden zijn de bron voor de course player:
```
/root/projects/jg/2026-pbm-KWIK/deliverables/html/kwik-elearning_v1.0.html
/root/projects/jg/2026-pbm-KWIK/deliverables/html/kwik-quiz_v1.0.html
/root/projects/jg/2026-pbm-STRALING-OLIE-GAS/deliverables/html/straling-elearning_v1.0.html
/root/projects/jg/2026-pbm-STRALING-OLIE-GAS/deliverables/html/straling-quiz_v1.0.html
/root/projects/jg/2026-pbm-rabbit-r1-training/deliverables/html/rabbit-r1-training-module_v3.0.html
/root/projects/jg/2026-pbm-rabbit-r1-training/deliverables/quiz/rabbit-r1-quiz-interface_v3.0.html
/root/projects/jg/2026-HSEQ-beeldscherm-werkplek-ergonomie/deliverables/html/elearning-beeldschermwerk_v1.0.html
/root/projects/jg/2026-HSEQ-beeldscherm-werkplek-ergonomie/deliverables/html/quiz-beeldschermwerk_v1.0.html
/root/projects/jg/2026-hseq-asbest/deliverables/html/quiz-asbest_v2.0.html
```

### 1.4 Integratiestrategie
Het LMS wordt een **module** binnen de bestaande Flask app. Nieuwe tab in de navigatie: "🎓 LMS Academy". Routes worden geregistreerd in `app.py` via import van `module_lms.py`.

---

## 2. DATABASE SCHEMA (UITBREIDING)

### 2.1 Bestaande Tabellen (Onveranderd)
- `elearning_modules` — Cursusdefinities (title, category, content_html, quiz_questions, passing_score, duration_minutes, status)
- `elearning_assignments` — Toewijzingen (employee_id, elearning_module_id, status, score, attempts, due_date)
- `elearning_quiz_attempts` — Quiz-pogingen (assignment_id, answers, score, passed)
- `employees` — Medewerkers (employee_number, first_name, last_name, email, role_id, status)
- `training_programs` — Trainingsprogramma's (program_code, program_name, valid_period_days)
- `certifications` — Certificeringen (employee_id, training_program_id, expiry_date, status, score)
- `training_matrix` — Koppeling rol ↔ training (role_id, training_program_id, is_required, priority)
- `roles` — Functies (role_name, department, safety_critical)
- `trn_certificates` — E-learning certificaten (employee_id, module_id, quiz_score, passed, certificate_ref)

### 2.2 Nieuwe Tabellen

#### 2.2.1 `lms_course_content` — Gekoppelde HTML-bestanden
```sql
CREATE TABLE IF NOT EXISTS lms_course_content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    elearning_module_id INTEGER NOT NULL,
    content_type TEXT NOT NULL DEFAULT 'html' CHECK(content_type IN ('html','scorm','video','pdf')),
    file_path TEXT NOT NULL,
    file_name TEXT,
    file_size INTEGER,
    display_order INTEGER DEFAULT 1,
    is_primary INTEGER DEFAULT 0,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE
);
```

#### 2.2.2 `lms_enrollments` — Inschrijvingen met voortgang
```sql
CREATE TABLE IF NOT EXISTS lms_enrollments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER NOT NULL,
    elearning_module_id INTEGER NOT NULL,
    status TEXT DEFAULT 'not_started' CHECK(status IN ('not_started','in_progress','completed','failed','expired','withdrawn')),
    progress_percent INTEGER DEFAULT 0 CHECK(progress_percent BETWEEN 0 AND 100),
    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME,
    completed_at DATETIME,
    last_accessed_at DATETIME,
    time_spent_seconds INTEGER DEFAULT 0,
    final_score INTEGER,
    final_passed INTEGER DEFAULT 0,
    attempts_allowed INTEGER DEFAULT 3,
    due_date DATE,
    assigned_by TEXT DEFAULT 'admin',
    notes TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE,
    UNIQUE(employee_id, elearning_module_id)
);
```

#### 2.2.3 `lms_quiz_results` — Gedetailleerde quiz-resultaten
```sql
CREATE TABLE IF NOT EXISTS lms_quiz_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    enrollment_id INTEGER NOT NULL,
    attempt_number INTEGER NOT NULL DEFAULT 1,
    answers_json TEXT NOT NULL DEFAULT '{}',
    total_questions INTEGER DEFAULT 0,
    correct_answers INTEGER DEFAULT 0,
    score INTEGER NOT NULL,
    passed INTEGER NOT NULL DEFAULT 0,
    time_spent_seconds INTEGER DEFAULT 0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    submitted_at DATETIME,
    FOREIGN KEY (enrollment_id) REFERENCES lms_enrollments(id) ON DELETE CASCADE
);
```

#### 2.2.4 `lms_compliance_rules` — Automatische compliance-regels
```sql
CREATE TABLE IF NOT EXISTS lms_compliance_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    elearning_module_id INTEGER NOT NULL,
    training_program_id INTEGER,
    role_ids TEXT DEFAULT '[]',
    valid_period_days INTEGER DEFAULT 365,
    renewal_days_before_expiry INTEGER DEFAULT 30,
    is_mandatory INTEGER DEFAULT 0,
    auto_assign INTEGER DEFAULT 0,
    auto_renew INTEGER DEFAULT 1,
    notification_days TEXT DEFAULT '[30,14,7,1]',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE,
    FOREIGN KEY (training_program_id) REFERENCES training_programs(id) ON DELETE SET NULL
);
```

#### 2.2.5 Indexen
```sql
CREATE INDEX IF NOT EXISTS idx_lms_enroll_emp ON lms_enrollments(employee_id);
CREATE INDEX IF NOT EXISTS idx_lms_enroll_mod ON lms_enrollments(elearning_module_id);
CREATE INDEX IF NOT EXISTS idx_lms_enroll_status ON lms_enrollments(status);
CREATE INDEX IF NOT EXISTS idx_lms_enroll_due ON lms_enrollments(due_date);
CREATE INDEX IF NOT EXISTS idx_lms_quiz_enroll ON lms_quiz_results(enrollment_id);
CREATE INDEX IF NOT EXISTS idx_lms_content_mod ON lms_course_content(elearning_module_id);
CREATE INDEX IF NOT EXISTS idx_lms_compliance_mod ON lms_compliance_rules(elearning_module_id);
```

---

## 3. API ENDPOINTS

### 3.1 User Portal (`/lms/`)

#### 3.1.1 Dashboard
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/lms/` | User LMS Dashboard — mijn trainingen, voortgang, certificaten |
| GET | `/lms/my-courses` | Lijst toegewezen trainingen met status |
| GET | `/lms/my-certificates` | Mijn behaalde certificaten |

#### 3.1.2 Course Player
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/lms/course/<module_id>` | Course viewer — laad e-learning HTML in iframe |
| POST | `/lms/course/<module_id>/start` | Markeer enrollment als `in_progress` |
| POST | `/lms/course/<module_id>/progress` | Update voortgang (progress_percent, time_spent) |
| POST | `/lms/course/<module_id>/complete` | Markeer enrollment als `completed` |

#### 3.1.3 Quiz Engine
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/lms/course/<module_id>/quiz` | Haal quiz-vragen op uit `elearning_modules.quiz_questions` |
| POST | `/lms/course/<module_id>/quiz/submit` | Verwerk quiz-antwoorden, bereken score, update enrollment |
| POST | `/api/lms/score` | **EXTERN endpoint** — bestaande HTML-quiz modules schieten hier hun score naartoe via `fetch()`/`XHR` |

### 3.2 Admin Portal (`/lms/admin/`)

#### 3.2.1 Course Management
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/lms/admin/courses` | Overzicht alle cursussen |
| POST | `/lms/admin/courses` | Nieuwe cursus aanmaken |
| PUT | `/lms/admin/courses/<id>` | Cursus bijwerken |
| DELETE | `/lms/admin/courses/<id>` | Cursus deactiveren |
| POST | `/lms/admin/courses/<id>/content` | HTML-bestand koppelen aan cursus |
| POST | `/lms/admin/courses/<id>/publish` | Cursus status → published |

#### 3.2.2 Enrollment Management
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/lms/admin/enrollments` | Alle inschrijvingen |
| POST | `/lms/admin/enrollments` | Training toewijzen aan medewerker(s) |
| POST | `/lms/admin/enrollments/bulk` | Bulk-toewijzing per functie/afdeling |
| DELETE | `/lms/admin/enrollments/<id>` | Inschrijving intrekken |

#### 3.2.3 Compliance Matrix
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/lms/admin/compliance` | Compliance dashboard — wie is compliant |
| GET | `/lms/admin/compliance/matrix` | Compliance matrix (rol × training) |
| GET | `/lms/admin/compliance/expired` | Lijst verlopen trainingen |
| GET | `/lms/admin/compliance/alerts` | Alerts: nakomende verlopen (< 30 dagen) |
| POST | `/lms/admin/compliance/rules` | Compliance-regels beheren |

#### 3.2.4 Analytics
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/lms/admin/analytics` | Overzicht: completion rates, scores, trends |
| GET | `/lms/admin/analytics/course/<id>` | Per-cursus statistieken |
| GET | `/lms/admin/analytics/employee/<id>` | Per-medewerker training historie |

### 3.3 API Endpoints (JSON)
| Method | Endpoint | Beschrijving |
|--------|----------|-------------|
| GET | `/api/lms/modules` | Alle gepubliceerde modules (JSON) |
| GET | `/api/lms/modules/<id>` | Module detail inclusief quiz-vragen |
| POST | `/api/lms/score` | Score-invoer vanuit externe HTML-quiz (via fetch/XHR) |
| GET | `/api/lms/enrollments/<employee_id>` | Inschrijvingen per medewerker |
| GET | `/api/lms/compliance/summary` | Compliance samenvatting |
| GET | `/api/lms/certificates/<employee_id>` | Certificaten per medewerker |

---

## 4. QUIZ SCORING LOGICA

### 4.1 Score Berekening
```
score = (correct_answers / total_questions) * 100
passed = 1 if score >= module.passing_score else 0
```

### 4.2 Extern Score Endpoint (`POST /api/lms/score`)
Dit endpoint wordt aangeroepen door bestaande HTML-quiz bestanden via JavaScript:

```javascript
// In bestaande quiz HTML bestanden:
const quizResult = {
    employee_id: 1,          // Uit sessie/URL parameter
    module_id: 2,            // Uit URL parameter
    answers: {1: "A", 2: "B", 3: "C"},
    score: 83,
    total_questions: 6,
    correct_answers: 5,
    time_spent: 420          // seconden
};

fetch('/hseq-dashboard/api/lms/score', {
    method: 'POST',
    headers: {'Content-Type': 'application/json'},
    body: JSON.stringify(quizResult)
});
```

**Verwerking door endpoint:**
1. Zoek of maak `lms_enrollments` record aan
2. Insert `lms_quiz_results` record
3. Update `lms_enrollments`: progress_percent=100, final_score, final_passed, completed_at
4. Als passed=1: insert `trn_certificates` record met certificate_ref
5. Als passed=1 en training_program_id gekoppeld: insert/update `certifications` record
6. Return JSON: `{success: true, enrollment_id, score, passed, certificate_ref}`

### 4.3 Compliance Status Berekening
```
Voor elke actieve medewerker:
  compliance_status = "compliant" als ALLE verplichte trainingen per training_matrix:
    - bestaan als certification met status='active' EN expiry_date > today
    - OF bestaan als lms_enrollment met final_passed=1 EN (completed_at + valid_period_days) > today
  
  anders: "non_compliant"
  
  Vervaldatum check:
  - expired: expiry_date < today
  - expiring_soon: expiry_date < today + 30 dagen
  - active: expiry_date >= today + 30 dagen
```

---

## 5. FRONTEND ARCHITECTUUR

### 5.1 Navigatie
Nieuw tab in `app.py` sidebar: `🎓 LMS Academy` → linkt naar `/lms/`

### 5.2 User Portal Pagina's
1. **Dashboard** (`/lms/`):
   - KPI cards: Totaal trainingen, Voltooid, In progress, Nakomend
   - Lijst: Mijn trainingen met voortgangsbalk, status-badge, deadline
   - Snelle toegang: "Ga naar cursus" knop

2. **Course Player** (`/lms/course/<id>`):
   - Header: Module titel, voortgang, timer
   - iframe: Laad gekoppeld HTML-bestand uit `lms_course_content.file_path`
   - Sidebar: Module navigatie (indien meerdere content-items)
   - Footer: "Volgende" / "Afronden" / "Quiz starten"

3. **Mijn Certificaten** (`/lms/my-certificates`):
   - Tabel: Certificaat, Datum, Score, Vervaldatum, Download

### 5.3 Admin Portal Pagina's
1. **Course Management** (`/lms/admin/courses`):
   - Tabel: Alle modules met status, inschrijvingen, gem. score
   - Acties: Toevoegen, bewerken, content koppelen, publiceren
   - Upload: HTML-bestand uploaden naar server + koppelen aan module

2. **Enrollment Management** (`/lms/admin/enrollments`):
   - Tabel: Alle inschrijvingen met medewerker, cursus, status, score
   - Filters: Op status, cursus, functie, afdeling
   - Bulk-actie: Toewijzen per functie/afdeling

3. **Compliance Matrix** (`/lms/admin/compliance`):
   - Heatmap: Rol × Training matrix (kleur: groen/amber/rood)
   - Tabel: Per medewerker — welke trainingen ontbreken/verlopen
   - Filters: Op functie, afdeling, status
   - Export: CSV download

4. **Analytics** (`/lms/admin/analytics`):
   - KPI cards: Completion rate, Gem. score, Compliance %
   - Charts: Trends over tijd (completion rate per maand)
   - Per-cursus breakdown

### 5.4 Styling (MASTER_STYLEGUIDE §1)
- Kleurenpalet: Primair #003366, Succes #00A859, Waarschuwing #F59E0B, Fout #EF4444
- CSS variabelen: `:root { --primary: #003366; ... }`
- Responsive: Mobile-first, breakpoints 768px / 1024px
- JvG Consultancy logo in header (base64 inline, donkere achtergrond)
- Alle styling inline (geen externe CSS bestanden — bestaand patroon)

---

## 6. FILE STRUCTURE

### 6.1 Module Bestanden
```
/root/projects/jg/HSEQ-Intelligence-Monitor/app/
├── module_lms.py          # NIEUW — Alle LMS routes, quiz engine, compliance logic
├── lms_migration.py       # NIEUW — Database migratie (nieuwe tabellen + seed data)
├── app.py                 # BESTAAND — Navigatie-updates + module_lms import
```

### 6.2 Deliverables
```
/root/projects/jg/hseq-lms/deliverables/
├── py/
│   ├── module_lms_v1.0.py       # Volledige LMS module
│   └── lms_migration_v1.0.py    # Database migratie script
├── md/
│   └── LMS_Technische_Documentatie_v1.0.md  # Architectuur, API, deployment
├── docx/
│   └── LMS_Beheerdershandleiding_v1.0.docx  # Admin handleiding met JvG logo
└── xlsx/
    └── LMS_Uitrol_Test_Checklist_v1.0.xlsx  # Testprotocol
```

### 6.3 Course Content Storage
```
/root/projects/jg/hseq-lms/course-content/
├── kwik-elearning/
│   ├── elearning.html
│   └── quiz.html
├── straling-olie-gas/
│   ├── elearning.html
│   └── quiz.html
└── ... (meer cursussen)
```

---

## 7. SEED DATA

### 7.1 Demo Cursussen (via migratie)
```sql
-- Update bestaande module 1 naar published
UPDATE elearning_modules SET status='published', title='Gevaarlijke Stoffen — Basisveiligheid' WHERE id=1;

-- Koppel HTML-bestanden
INSERT INTO lms_course_content (elearning_module_id, content_type, file_path, file_name, is_primary) VALUES
(1, 'html', '/root/projects/jg/hseq-lms/course-content/kwik-elearning/elearning.html', 'kwik-elearning.html', 1);
```

### 7.2 Demo Inschrijvingen
```sql
-- Wijs module 1 toe aan alle actieve medewerkers
INSERT INTO lms_enrollments (employee_id, elearning_module_id, status, due_date)
SELECT e.id, 1, 'not_started', date('now', '+30 days')
FROM employees e WHERE e.status='active';
```

---

## 8. DEPLOYMENT PROCEDURE

1. Kopieer `module_lms.py` naar `/root/projects/jg/HSEQ-Intelligence-Monitor/app/`
2. Kopieer `lms_migration.py` naar `/root/projects/jg/HSEQ-Intelligence-Monitor/app/`
3. Voer migratie uit: `cd /root/projects/jg/HSEQ-Intelligence-Monitor/app && python3 lms_migration.py`
4. Update `app.py`: import `module_lms` en registreer routes
5. Kopieer course content HTML-bestanden naar `/root/projects/jg/hseq-lms/course-content/`
6. Restart PM2: `pm2 restart hseq-kennisbank`
7. Verifieer: `curl -s http://localhost:5052/hseq-dashboard/lms/ | head -20`

---

## 9. QUIZ-INTEGRATIE PATROON (VOOR BESTAANDE HTML QUIZZES)

Bestaande quiz-HTML-bestanden (zoals `kwik-quiz_v1.0.html`) bevatten een JavaScript-quiz-engine met een score-berekening. Om deze te integreren in het LMS:

1. **De quiz-HTML wordt geladen in een iframe** binnen de Course Player
2. **De quiz-HTML moet een score-post functie toevoegen** (via `window.parent.postMessage` of direct `fetch` naar `/api/lms/score`)
3. **Het LMS luistert naar `postMessage` events** en verwerkt de score

### 9.1 PostMessage Protocol (Course Player → LMS)
```javascript
// In de quiz-HTML (kind-iframe):
window.parent.postMessage({
    type: 'lms_quiz_complete',
    data: {
        module_id: 2,
        score: 83,
        total_questions: 6,
        correct_answers: 5,
        answers: {1: "A", 2: "B", 3: "C"},
        time_spent: 420
    }
}, '*');

// In de Course Player (parent):
window.addEventListener('message', function(event) {
    if (event.data && event.data.type === 'lms_quiz_complete') {
        fetch('/hseq-dashboard/api/lms/score', {
            method: 'POST',
            headers: {'Content-Type': 'application/json'},
            body: JSON.stringify({
                employee_id: currentEmployeeId,
                ...event.data.data
            })
        }).then(r => r.json()).then(result => {
            // Update UI: toon resultaat, voortgang, certificaat
        });
    }
});
```

---

*SST v1.0 — Kas (Director of Operations) — 15 april 2026*
*Alle sub-agents zijn VERPLICHT dit document te lezen vóór start.*