WIN — Architectural Overview

High-level diagram of the system tiers, components, data model, auth flow, and role hierarchy.

🏗 System Architecture
Client
Angular SPA v20
  • TypeScript 5.8 + SCSS
  • Angular Signals (state)
  • Lazy-loaded routes
  • JWT auth interceptor
  • Proactive token refresh
Desktop Layout
  • Home / Workspace
  • Student detail view
  • Goal & Benchmark modals
  • Progress event modals
  • Report generation UI
  • Admin panel
Mobile Layout
  • Student card list
  • Add progress event
  • Toggle benchmark
Shared Services
  • AuthService (signals)
  • ApiService (HTTP)
  • StudentService
  • AdminService
  • ReportPromptService
  • PlatformService
HTTPS / REST + JWT Bearer — win.opelly.me → winapi.opelly.me
Proxy
Traefik Reverse Proxy
  • win.opelly.me → Angular (Nginx :8006)
  • winapi.opelly.me → API (:8005)
  • Let's Encrypt TLS
  • Gzip compression
  • Security headers
HTTP (internal Docker network)
API
ASP.NET Core 9 C#
  • JWT authentication middleware
  • Swagger / OpenAPI
  • CORS policy
  • Dependency injection
Controllers
  • /api/Auth — login & tokens
  • /api/Student — CRUD
  • /api/Admin — district/users
  • /api/ReportPrompt — prompts
Services
  • TokenService (JWT)
  • PermissionService
  • PasswordHasher (PBKDF2)
  • RecommendationService
  • TranscriptionService
  • ProgressReportBuilder
Repositories Dapper
  • StudentRepository
  • UserRepository
  • AuthRepository
  • AdminRepository
  • ReportPromptRepository
Stored procedures via Dapper — TCP :3309 (Docker internal)
Data
MySQL 8 Docker
  • winstudentgoaltracker DB
  • 17 tables
  • 51 stored procedures
  • Initialised from db/docker-init/
Core Tables
  • user, school_district, program
  • user_program (roles junction)
  • student, user_student
  • goal (recursive), benchmark
  • progress_event + junction
  • refresh_token
Procedure Groups
  • sp_Student_* (CRUD)
  • sp_Goal_* / sp_Benchmark_*
  • sp_ProgressEvent_*
  • sp_RefreshToken_*
  • sp_Program_* / sp_User_*
  • sp_ProgressReport_*
Outbound HTTP from API tier only
External
Ollama LLM AI
  • llm.opelly.me
  • Model: gemma4:e2b
  • Benchmark recommendations
  • 5-min timeout
STT Service AI
  • stt.opelly.me
  • Speech-to-text transcription
  • Progress event dictation
  • 5-min timeout
🔐 Two-Phase Authentication Flow
1
User Submits Credentials email + password
POST /api/Auth/Login
2
Phase 1 Response Session token (5 min)
+ program list
Stored in localStorage
3
User Selects Program session token + program ID
POST /api/Auth/SelectProgram
4
Phase 2 Response JWT (1 min) +
Refresh token (30 days)
JWT includes role + program_id
5
Authenticated Requests Bearer JWT on all API calls
Auth interceptor injects token
6
Proactive Refresh 10 s before JWT expiry:
POST /api/Auth/RefreshToken
Rotated refresh token returned
Token storage: auth_jwt, auth_refresh_token, auth_session_token — all in localStorage.  |  401 interceptor: attempts one silent refresh; if that fails, redirects to /login.  |  Refresh token rotation: each use replaces the old token (tracked via replaced_by_token_id).
🗄 Core Data Model
school_district
PK id_school_district
name
contact_email
Top-level tenant
program
PK id_program
FK id_school_district
name, description
Scope boundary for data
user
PK id_user
email, name
password_hash, password_salt
locked_until
user_program (junction)
PK id_user_program
FK id_user
FK id_program
FK id_role
is_primary, status
student
PK id_student
FK id_program
identifier, program_year
enrollment_date
next_iep_date
user_student (junction)
PK id_user_student
FK id_user
FK id_student
is_primary
goal
PK id_goal
FK id_goal_parent (self-ref)
FK id_student
description, category
baseline, target_completion_date
close_date, achieved
benchmark
PK id_benchmark
FK id_goal
benchmark (description)
short_name
progress_event
PK id_progress_event
FK id_goal
FK id_user_created
content (rich text)
is_sensitive
progress_event_benchmark (junction)
FK id_progress_event
FK id_benchmark
Links events to benchmarks
refresh_token
PK id_refresh_token
FK id_user, id_program
token_hash, token_salt
expires_at, revoked_at
replaced_by_token_id
report_prompt
PK id_report_prompt
FK program_id
report_name
prompt_template (LLM)
Key relationships:  school_district 1→N program  ·  program 1→N student  ·  student 1→N goal (recursive parent/child)  ·  goal 1→N benchmark  ·  goal 1→N progress_event  ·  progress_event M↔N benchmark (junction)  ·  user M↔N program (via user_program + role)  ·  user M↔N student (via user_student)
👥 Role Hierarchy & Permissions
Role Tiers (highest → lowest)
super_admin  ·  full platform access
district_admin  ·  manages programs
program_admin  ·  manages users
teacher  ·  full student CRUD
paraeducator  ·  log events
Sample Permission Matrix
Entity / Action super_admin district_admin program_admin teacher paraeducator
Student — Create Allow Allow Allow Allow Deny
Student — Update Allow Allow Allow Mine Deny
Goal — Create Allow Allow Allow Mine Deny
Goal — Update Allow Allow Allow Mine Deny
ProgressEvent — Create Allow Allow Allow Allow Mine
ProgressEvent — Delete Allow Allow Allow Mine Deny
Program — Create Allow Mine Deny Deny Deny
User — Create Allow Mine Mine Deny Deny
Allow = full access   Mine = own records only   Deny = not permitted
🐳 Deployment & Infrastructure

Docker Containers

  • mysql:8 (port 3309)
  • .NET API (port 8005)
  • Angular / Nginx (port 8006)
  • Traefik reverse proxy

Docker Networks

  • web — external HTTPS routing
  • backend — internal service mesh
  • API depends on MySQL healthcheck
  • UI depends on API (build time)

Environment Config

  • MYSQL_ROOT_PASSWORD
  • MYSQL_DATABASE / USER / PASSWORD
  • JWT_KEY (signing secret)
  • MYSQL_HOST / PORT

Frontend Environments

  • Dev: localhost:5000 (API)
  • Prod: winapi.opelly.me (API)
  • Served by Nginx in production
  • Angular CLI dev server locally

DB Initialisation

  • Schemas from db/docker-init/
  • 51 stored procedures
  • Health check gates API startup
  • No ORM migrations — raw SQL

AI / External Services

  • Ollama LLM — llm.opelly.me
  • STT service — stt.opelly.me
  • Both called from API tier only
  • 5-minute timeout on both
⚙️ Key Design Decisions

Two-Phase JWT Auth

  • Phase 1: credentials → session token
  • Phase 2: program select → scoped JWT
  • Prevents data leakage before program chosen
  • JWT scoped to one program at a time

Refresh Token Rotation

  • New token issued on each refresh
  • Old token replaced (not deleted)
  • Genealogy tracked via replaced_by
  • Prevents replay attacks

Program-Scoped Multi-Tenancy

  • JWT contains program_id claim
  • All queries filtered by program
  • district_admin scoped to district
  • super_admin bypasses scope

Dapper + Stored Procedures

  • No ORM (Entity Framework)
  • Business logic close to data
  • Dapper maps rows to C# objects
  • 51 procedures for full coverage

Angular Signals (not RxJS)

  • Auth state as reactive signals
  • Computed signals derive user context
  • Simpler than Observable chains
  • Auto-update dependent UI

Centralised Permission Matrix

  • Declarative rules per entity + action
  • Allow / MineOnly / Deny granularity
  • Evaluated at request time in API
  • Single source of truth for authz