EXP Programme Dashboard
Read-only operational dashboard over BigQuery for the EXP programme (Educate!).
FastAPI backend + React 19 / Vite SPA, deployed to DigitalOcean Kubernetes.
BigQuery (gold_exp.exp_ai_dashboard_model)
↓ service-account key (BigQuery Data Viewer + Job User)
FastAPI backend (Python 3.14) → single replica / single process
│ /api/auth/* email+password or Google SSO → JWT (carries access scope)
│ /api/overview/* CU + school rows, row-level access-scoped
│ /api/cu CU drilldown (school rows)
│ every /api/* needs a valid JWT + the X-Exp-Client header
↓ REST JSON (Bearer JWT + CORS allowlist + X-Exp-Client)
React 19 + Vite SPA (nginx) → single replica
Repository layout
├── CLAUDE.md project conventions (thin)
├── docs/ ONBOARDING · CONTEXT · ARCHITECTURE · DECISION · FLOW
├── .github/workflows/ deploy.yml (digest-pinned) · secret-scan.yml (gitleaks)
├── backend/ FastAPI app (app/core, app/routers, tests)
├── frontend/ React 19 + Vite SPA
├── k8s/ backend + frontend manifests
└── legacy/ dashboard-v1.html — the original single-file app (reference)
Local dev quick-start (Windows / PowerShell)
--reload can crash on Windows — run without it and restart manually.
# Terminal 1 — backend
cd backend
python -m venv venv; venv\Scripts\Activate.ps1
pip install -r requirements-dev.txt
copy .env.example .env # then fill secrets + BQ config
gcloud auth application-default login # BigQuery as your own Google identity
uvicorn app.main:app --port 8000
# Terminal 2 — frontend
cd frontend
npm install
copy .env.example .env.local # VITE_API_URL=http://localhost:8000
npm run dev
Smoke test: curl http://localhost:8000/health, open http://localhost:3000,
API docs at http://localhost:8000/docs.
Env-file footgun: Vite prefers .env.local over .env. Do not create
frontend/.env — a stale one has previously made local dev silently hit prod.
Generate secrets: python -c "import secrets; print(secrets.token_hex(32))".
BigQuery credentials locally
Leave GOOGLE_SERVICE_ACCOUNT_KEY empty and run:
gcloud auth application-default login
gcloud auth application-default set-quota-project educate-data-warehouse-test
The backend then reaches BigQuery as you, under your own IAM grants, and no
service-account key ever lands on the laptop — the SA key is meant to live only
in the k8s Secret (docs/CONTEXT.md). You need BigQuery Data Viewer + Job User
on educate-data-warehouse-test. Setting GOOGLE_SERVICE_ACCOUNT_KEY to a JSON
path still works and still takes precedence; that is how the cluster runs.
Required backend/.env
BQ_PROJECT_ID / BQ_DATASET / BQ_TABLE, JWT_SECRET, DASHBOARD_PASSWORD,
the client-header token, and FRONTEND_URL
(GOOGLE_SERVICE_ACCOUNT_KEY is optional — see above). Google OAuth vars are optional locally (email+password works
without them). See backend/.env.example.
Auth & access model
One JWT scheme (HS256, 8h, sessionStorage, Authorization: Bearer). The UI
offers Google SSO as the primary sign-in; the password path is retained as a
hidden break-glass fallback (revealed by a link on the login screen):
- Google SSO (
@experienceeducate.org) → /api/auth/google/login → Google →
/api/auth/google/callback → redirects to FRONTEND_URL/#token=<jwt> (or
/#error=<code> on failure); the SPA reads the fragment and stores the token.
- Email + shared password (fallback) →
POST /api/auth/login. Also the only
path for the few non-@experienceeducate.org FOA accounts in ACCESS_CONFIG,
which SSO's domain check rejects.
Both resolve the email to an access scope (national / regional / cu)
via ACCESS_CONFIG, embedded in the JWT. Row-level filtering is server-side
(core/access.py + core/sql.py::access_clause). Unknown emails get no access.
Google OAuth setup (one-time)
SSO needs a Google OAuth 2.0 client. In the Google Cloud console for the project:
- APIs & Services → OAuth consent screen — Internal user type, app name
"EXP Dashboard", support email; no scopes beyond the default openid/email/profile.
- APIs & Services → Credentials → Create credentials → OAuth client ID —
type Web application. Add Authorized redirect URIs:
https://exp-dashboard-api.educateapps.work/api/auth/google/callback (prod)http://localhost:8000/api/auth/google/callback (local dev)
- Copy the Client ID / Client secret into
GOOGLE_CLIENT_ID /
GOOGLE_CLIENT_SECRET — in the exp-dashboard-credentials k8s Secret for prod,
and backend/.env for local dev. Ensure FRONTEND_URL matches the SPA origin
the callback should return to.
Metric definitions
The source model is a wide "one-big-table" with a level column (cu /
school). Full metric formulas, RAG thresholds, and the field catalogue are in
docs/METRICS.md (the EXP Dashboard Metrics Reference). See
docs/ARCHITECTURE.md for the grouped field list, docs/FLOW.md for how a
request becomes a scoped BigQuery query, and docs/DROPPED_SECTIONS.md for the
parity gap analysis vs. the legacy dashboard.
Deploy
Push to main auto-builds changed images and digest-pins the rollout (see
.github/workflows/deploy.yml). k8s manifest changes are applied manually with
kubectl apply — see docs/CONTEXT.md → Deployment for the ordering rules.
Tests
cd backend; python -m pytest
