# Trainee Management System — Design Plan

**Status:** Phase 1 (lean MVP) — planned for first implementation  
**Hosting:** Cloudflare Pages + D1 (same stack as [trainer checklist progress](trainer-checklist-progress.md))  
**Related:** [Trainee pre-work](../trainer/trainee-pre-work.qmd) · [Prerequisites](../setup/prerequisites.qmd) · [Materials & credentials](../trainer/pre-class-checklist/06-materials-credentials.qmd)

---

## 1. Problem

Trainers today distribute workshop credentials through several channels:

| Channel | Contents |
|---------|----------|
| Printed credentials cards | `ATTENDEE_ID`, Databricks URL, ADLS key, SAS token, Aiven (Module 8) |
| Pre-class emails | Site URL, workshop token, fork/Codespace steps |
| Slides / verbal | `ATTENDEE_ID` on Module 1; ADLS key Module 2; SAS Module 3 |

Trainees re-type values into `.env` and notebooks. There is no single roster source of truth. Last-minute additions on training day still need an `ATTENDEE_ID` within minutes.

**Goal:** One trainer UI to manage the roster and cohort secrets; one trainee portal for copy-paste.

---

## 2. Principles

1. **Dynamic portal, not static pages** — One shared `/trainee-portal.html`; personalization via API when the trainee picks their name. No per-person HTML generation or deploy step.
2. **Incremental roster saves** — Each trainee row is live in D1 immediately. New rows appear on **My Workshop** after the trainee clicks **Refresh**.
3. **Cloudflare-first** — D1 + Pages Functions (Vercel mirror shows redirect banner).
4. **Phased security** — Lean MVP uses D1-at-rest; stronger encryption and audit in Phase 2.

---

## 3. User flows

### 3.1 Trainee (pre-registered)

```mermaid
sequenceDiagram
  participant Trainee
  participant SiteGate as WorkshopTokenGate
  participant Portal as TraineePortal
  participant API as PagesFunctions

  Trainee->>SiteGate: Enter WORKSHOP_TOKEN
  Trainee->>Portal: Open /trainee-portal.html?cohort=...
  Portal->>API: GET roster
  Trainee->>Portal: Pick name + confirm email
  Portal->>API: GET dashboard
  Portal->>Trainee: Copy ATTENDEE_ID, URLs, env block, secrets if unlocked
```

### 3.2 Same-day addition (training day)

1. Co-trainer: **Add row** on [Trainee management](../trainer/trainee-management.qmd), enter first/last name and email, **Save**.
2. System assigns next `ATTENDEE_ID` (e.g. `11_charlie`) → saves to D1 (~seconds).
3. Trainee opens portal → **Refresh** → picks name → confirms email.
4. Copies credentials before Module 2.

**Target:** door to copy-paste in under 60 seconds.

### 3.3 Trainer

1. Select cohort (reuse checklist session: `YYYYMMDD-slug`).
2. Maintain roster table + cohort settings (URLs, ADLS, SAS, etc.).
3. **Unlock delivery credentials** on training morning (or auto when `delivery_date <= today`).
4. Copy cohort portal link into pre-class email ([trainee-pre-work](../trainer/trainee-pre-work.qmd)).

---

## 4. Credential visibility (both phases)

| Tier | When | Contents |
|------|------|----------|
| **Personal** | Row exists in roster | `ATTENDEE_ID`, name, site/prerequisites/fork links, `.env` snippet with `ATTENDEE_ID` |
| **Delivery secrets** | Training day (Europe/Berlin) or trainer **Unlock** | ADLS storage key, SAS token, Databricks workspace URL, Aiven URI/certs |

All trainees confirm **email** matches trainer input after picking their name.

---

## 5. Phase 1 — Lean MVP (implement first)

**Scope:** Replace printed cards and most email credential copying. Same-day additions use the same **Add row** flow.

### 5.1 Features

| Area | Phase 1 |
|------|---------|
| Trainer roster CRUD | **Add row** + table edit: `ATTENDEE_ID`, first/last name, email; save per row |
| Cohort settings | Site URL, prerequisites URL, fork URL, Databricks URL, ADLS key, SAS token, Aiven fields, delivery date |
| Unlock secrets | Manual button + auto on delivery date |
| Trainee portal | Workshop token → name pick → copy blocks; **Refresh roster** |
| Email confirm | Pre-registered only |
| Auth (trainer writes) | `TRAINER_API_SECRET` header on API |
| Auth (trainee reads) | `WORKSHOP_TOKEN` + `cohort` + selected `traineeId` in session |
| Secret storage | D1 (Cloudflare encryption at rest) |
| Encryption | None at app layer |
| Session JWT | None — `traineeId` in `sessionStorage` |
| CSV import | No |
| Audit log | No |
| Per-trainee email generator | No (copy link only) |

### 5.2 Data model (Phase 1)

```sql
cohorts (
  id TEXT PRIMARY KEY,              -- 20260615-frankfurt
  display_name TEXT,
  delivery_date TEXT,               -- YYYYMMDD
  secrets_unlocked INTEGER DEFAULT 0,
  secrets_unlock_at TEXT,
  updated_at TEXT
)

trainees (
  id TEXT PRIMARY KEY,                -- uuid
  cohort_id TEXT NOT NULL,
  attendee_id TEXT NOT NULL,          -- 01_alice
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT,
  sort_order INTEGER DEFAULT 0,
  created_at TEXT,
  UNIQUE(cohort_id, attendee_id)
)

cohort_secrets (
  cohort_id TEXT PRIMARY KEY,
  site_url TEXT,
  prerequisites_url TEXT,
  fork_url TEXT,
  databricks_workspace_url TEXT,
  adls_storage_key TEXT,
  sas_token TEXT,
  aiven_uri TEXT,
  aiven_cert TEXT,
  updated_at TEXT
)
```

Migration: `schema/d1/002_trainee_management.sql`

### 5.3 API (Phase 1)

| Method | Path | Auth | Purpose |
|--------|------|------|---------|
| `GET` | `/api/trainees?cohort=` | Trainer secret | Full roster |
| `PUT` | `/api/trainees` | Trainer secret | Upsert one trainee |
| `DELETE` | `/api/trainees?id=` | Trainer secret | Remove row |
| `GET` | `/api/cohort-secrets?cohort=` | Trainer secret | Read cohort settings |
| `PUT` | `/api/cohort-secrets` | Trainer secret | Save cohort settings |
| `POST` | `/api/cohort-secrets/unlock` | Trainer secret | `secrets_unlocked=1` |
| `GET` | `/api/trainee-portal/roster?cohort=` | Workshop token | Names for picker |
| `GET` | `/api/trainee-portal/dashboard?cohort=&traineeId=&email=` | Workshop token | Copy-paste payload |

### 5.4 UI files (Phase 1)

| File | Role |
|------|------|
| `trainer/trainee-management.qmd` | Trainer page shell |
| `trainer/_trainee-management.js` | Roster table, cohort form, unlock |
| `trainee-portal.qmd` | Trainee page |
| `_trainee-portal.js` | Name pick, refresh, copy buttons |
| `functions/api/trainees.js` | Roster API |
| `functions/api/cohort-secrets.js` | Settings + unlock API |
| `functions/api/trainee-portal.js` | Trainee read API |

Wire into `_quarto.yml`, [trainer/index.qmd](../trainer/index.qmd), [prerequisites.qmd](../setup/prerequisites.qmd), [02-before-room-opens.qmd](../trainer/facilitator-guide/02-before-room-opens.qmd).

### 5.5 Environment (Phase 1)

| Variable | Required | Purpose |
|----------|----------|---------|
| `WORKSHOP_TOKEN` | Yes (existing) | Site + trainee API gate |
| `TRAINER_API_SECRET` | Yes | Trainer write APIs |
| `DB` (D1 binding) | Yes | Persistence |

### 5.6 Phase 1 success criteria

- [ ] Trainer adds 10 pre-registered trainees; each appears in portal after save
- [ ] Same-day row added at door → visible after Refresh → copies `ATTENDEE_ID` in &lt; 60 s
- [ ] Delivery secrets hidden before training day; visible after Unlock or auto date
- [ ] No secrets in git or Quarto sources
- [ ] Vercel shows “use Cloudflare URL for My Workshop”

---

## 6. Phase 2 — Full plan (later)

**Trigger:** Remote-only deliveries, compliance requirements, or pain from impersonation / key rotation.

### 6.1 Additional features

| Feature | Benefit |
|---------|---------|
| App-level encryption (`CREDENTIALS_ENCRYPTION_KEY`) | ADLS/SAS encrypted before D1 write |
| Trainee session JWT (HttpOnly cookie) | Stronger than `sessionStorage` traineeId |
| Per-trainee magic links | Optional bypass name pick for email |
| CSV import/export | Bulk roster from registration spreadsheet |
| Copy email / mailto per trainee | Faster pre-class comms |
| Portal open audit log | Trainer sees who accessed dashboard |
| Per-trainee pre-work checklist in D1 | Readiness view per person |
| Module-level unlock | e.g. SAS only after Module 3 start |
| Optional per-trainee PIN | In-room impersonation hardening without email |
| Encrypt checklist-progress trainer writes | Close existing API auth gap |

### 6.2 Data model additions (Phase 2)

```sql
portal_sessions (
  id TEXT PRIMARY KEY,
  trainee_id TEXT NOT NULL,
  opened_at TEXT,
  ip_hash TEXT
)

-- cohort_secrets: migrate plaintext columns to *_enc blobs
```

### 6.3 API additions (Phase 2)

| Method | Path | Purpose |
|--------|------|---------|
| `POST` | `/api/trainees/import` | CSV bulk upsert |
| `GET` | `/api/trainees/export?cohort=` | CSV download |
| `POST` | `/api/trainee-portal/session` | Issue signed session after email/PIN |
| `GET` | `/api/trainee-portal/audit?cohort=` | Trainer: who opened portal |

### 6.4 Phase 2 success criteria

- [ ] Secrets re-keyable without plaintext in DB exports
- [ ] Trainer exports roster CSV after delivery
- [ ] Audit shows last portal access per trainee

---

## 7. Phase comparison

| Dimension | Phase 1 (lean) | Phase 2 (full) |
|-----------|----------------|----------------|
| Implementation effort | ~1–2 days | +2–4 days |
| Same-day roster adds | Yes (Add row) | Yes |
| Secret storage | D1 at rest | App AES-GCM + D1 |
| Trainee auth | Token + name pick | + JWT / magic link option |
| Impersonation risk | Email confirm on name pick | + PIN / magic link |
| Ops env vars | 2 | 3 (+ encryption key) |
| Comms automation | Manual copy link | Email templates, CSV |

---

## 8. Risks

| Risk | Phase 1 mitigation | Phase 2 mitigation |
|------|-------------------|-------------------|
| Name pick impersonation | Email confirm (pre-reg) | PIN / magic link |
| Secrets leaked via API | Delivery-date gate; trainer secret on writes | Encryption at rest in app |
| D1 export exposure | Short-lived SAS; workshop-only cohort | Encrypted columns |
| Vercel users | Redirect to Cloudflare | Same |
| Trainer forgets day-of add | Add row + verbal ATTENDEE_ID on row | Same |

---

## 9. Document history

| Date | Change |
|------|--------|
| 2026-06-06 | Initial two-phase design: lean MVP + full roadmap |