EXP Programme Dashboard

Read-only operational dashboard over BigQuery for the EXP programme (Educate!). FastAPI backend + React 19 / Vite SPA, deployed to DigitalOcean Kubernetes.

• App: https://exp-dashboard.educateapps.work
• API: https://exp-dashboard-api.educateapps.work
• Source table: educate-data-warehouse-test.gold_exp.exp_ai_dashboard_model

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:

1. APIs & Services → OAuth consent screen — Internal user type, app name "EXP Dashboard", support email; no scopes beyond the default openid/email/profile.
2. 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)
3. 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