GoalOrch
Production database & ETL layer for a student-analytics platform
I designed and built the entire data layer behind GoalOrch — a platform where math teachers track student engagement, diagnostics, and weekly goals. Schema, ETL, safe migrations, backups, and tests, all shipped to a production server through code review.
19-table schema
15+ safe migrations
live in 3 states
Stack
MySQL 8 · Python · SQLAlchemy · Bash · Ubuntu · Git · PR workflow · cron
the problem
A CMU research group was shipping a live dashboard to middle-school math teachers — but the data behind it came from messy exports and a database that was risky to change while real classrooms depended on it every week.
how it works
Student data lands as exports from Box — i-Ready usage, lesson completion, diagnostic scores. An ETL pipeline (Python + SQLAlchemy) cleans and loads it into a 19-table MySQL schema: teachers, classes, students, daily and weekly usage, diagnostics, goals, secure share links, and session logging — wired together with foreign keys, unique and CHECK constraints, and indexes tuned for fast dashboard queries. The React app reads straight from it.
shipping safely on a live database
Every schema change ships as a versioned, backward-compatible migration paired with an automated verification script that asserts the result is exactly right — column types, nullability, defaults, indexes, constraints, foreign keys. Nothing touches production directly: changes are applied and verified on a QA database, opened as a pull request, reviewed, and only then promoted to prod. Automated nightly backups (compressed dumps, read-only user, 14-day rotation) and a guarded snapshot-restore tool with dry-run validation keep the live database recoverable.
data teachers can trust
The data has to be trustworthy and private. A test matrix covers real loading scenarios — new semester, weekly and daily updates, malformed files, duplicates, out-of-order loads — and automated tests assert no orphaned records, no duplicates, consistent aggregates, and no personal identifiers in the logging tables. Deterministic seed and reset tooling generates internally-consistent mock data (coherent engagement tiers across scores, usage, and goals), so the app can be demoed and tested realistically even when real student data is restricted.
more
key decisions
QA first, production last
Every change is verified on a QA database and reviewed as a PR before it touches prod. Iterating fast on a live system means never breaking the classrooms already depending on it.
Synthetic data that can't contradict itself
Mock data is generated from engagement tiers, so scores, usage, and goals stay coherent — realistic enough to demo and test against when real student data is restricted.
Privacy by construction
Logging tables carry no personal identifiers, and the test suite fails if any slip in. Student privacy is enforced by the schema and the tests, not by convention.
outcomes
- Shipped to production, serving the live teacher dashboard
- Started with 2 schools in Pennsylvania — now expanding to California and Tennessee
- Shaped features through direct teacher interviews
- 19-table schema evolved via 15+ verified, backward-compatible migrations
- Automated nightly backups + guarded restore keep production recoverable
up next
ROF
Full-stack platform for managing university clubs