Intellivis

An interactive analytics dashboard for school districts. Explore grade distributions, flag at-risk students, and track course performance trends across schools, all driven by a REST API backed by complex SQL.

---

Demo

Select a district, drill into a school, then into a course. Each level updates the charts and tables live. Grade distributions, at-risk flags, improvement trends, and rolling averages update without a page reload.

---

Why This Project Exists

School administrators typically piece together student performance data from spreadsheets exported across multiple systems. Intellivis centralises that data into a single dashboard where district-level and course-level questions can be answered in seconds.

The backend is built to demonstrate the kind of analytical SQL required in data platform and backend engineering roles. The queries compute rolling averages, rank students across an entire district, and identify at-risk students using statistical thresholds rather than hardcoded cutoffs.

---

Tech Stack

Layer	Technology
Database	PostgreSQL 15
Backend	Node.js 20 · Express 4 · pg (node-postgres)
Frontend	React 18 · Vite 5 · Recharts 2 · Tailwind CSS 3
Infrastructure	Docker Compose · Render · Vercel / Netlify

---

Running Locally

Requires Docker Desktop.

git clone https://github.com/<your-username>/school-district-analytics.git
cd school-district-analytics
cp .env.example .env
docker compose up --build

Service	URL
Dashboard	http://localhost:3000
API	http://localhost:4000
Health check	http://localhost:4000/health

The .env.example file contains defaults that work without modification for local development.

Running without Docker

Requires PostgreSQL 15 and Node.js ≥ 18 installed locally.

# Database
createdb school_analytics
psql -d school_analytics -f backend/db/migrations/001_init_schema.sql
psql -d school_analytics -f backend/db/seeds/seed_data.sql

# Backend  (http://localhost:4000)
cd backend && cp ../.env.example .env && npm install && npm start

# Frontend  (http://localhost:3000)
cd frontend && npm install && VITE_API_URL=http://localhost:4000 npm run dev

---

Architecture

The application is composed of three independently containerized services:

• db - PostgreSQL 15. On first boot, Docker automatically executes the migration script (001_init_schema.sql) and seed script (seed_data.sql) from backend/db/. A health check gate prevents the backend from starting until the database is ready.
• backend - Express API server. Connects to the database via a pg.Pool configured entirely from environment variables. All SQL is written as raw parameterised queries, no ORM is used.
• frontend - React application built with Vite and served by nginx. Proxies /api requests to the backend service on the internal Docker network.

All inter-service communication uses Docker Compose service names rather than hardcoded addresses, making the stack portable across environments.

---

API Reference

All endpoints accept no request body and return application/json. Error responses use the shape { "error": "...", "detail": "..." } with appropriate HTTP status codes (400, 404, 500).

Districts

Method	Endpoint	Description
GET	/api/districts	All districts
GET	/api/districts/:id/top-students	Top 10 students ranked by average grade
GET	/api/districts/:id/at-risk-students	Students with avg grade < 70 and below-median app usage
GET	/api/districts/:id/grade-distribution	A / B / C / D / F counts per school

GET /api/districts/1/top-students

[
  {
    "student_id": 57,
    "student_name": "Felix Price",
    "school_name": "Westbrook Preparatory",
    "avg_grade": "96.63",
    "district_rank": "1"
  }
]

Schools

Method	Endpoint	Query Params	Description
GET	/api/schools	district_id	All schools, optionally filtered by district
GET	/api/schools/:id/performance-summary	—	Avg grade per course, enrollment count, stat card data
GET	/api/schools/:id/improvement-trends	—	Per-student trend classification: Improving / Stable / Declining

GET /api/schools/1/improvement-trends

[
  {
    "student_id": 2,
    "student_name": "Bella Martinez",
    "grade_level": 9,
    "latest_avg_grade": "88.38",
    "previous_avg_grade": "85.75",
    "grade_delta": "2.63",
    "trend": "Improving"
  }
]

Courses

Method	Endpoint	Query Params	Description
GET	/api/courses	school_id	All courses, optionally filtered by school
GET	/api/courses/:id/grade-trends	—	Monthly average grade with 3-month rolling average
GET	/api/courses/:id/rankings	—	Students ranked by average grade within the course

GET /api/courses/1/grade-trends

[
  { "month": "2024-10", "avg_grade": "77.49", "rolling_3mo_avg": "77.49" },
  { "month": "2024-11", "avg_grade": "78.80", "rolling_3mo_avg": "78.14" },
  { "month": "2024-12", "avg_grade": "79.83", "rolling_3mo_avg": "78.70" }
]

---

SQL Complexity Highlights

All queries are raw SQL executed through pg. No query builder or ORM is involved. The query files in backend/queries/ demonstrate the following techniques:

CTE + Window RANK (district_queries.js) A CTE pre-aggregates each student's average grade once. RANK() OVER (ORDER BY avg_grade DESC) then produces a deterministic district ranking without a correlated subquery or self-join.

PERCENTILE_CONT (district_queries.js) The at-risk threshold for app usage is the district median, computed with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_minutes), a single ordered-set aggregate that avoids NTILE approximation.

COUNT FILTER (district_queries.js) Grade buckets (A through F) are counted with COUNT(*) FILTER (WHERE g.grade >= 90) clauses, performing five conditional counts in a single table scan instead of five separate subqueries.

LAG() Window Function (school_queries.js) ROW_NUMBER partitioned by (student_id, course_id) isolates the two most recent grade records per student per course. LAG() then computes the grade delta between them, enabling Improving / Stable / Declining classification without a self-join.

DATE_TRUNC + Rolling Window Frame (course_queries.js) DATE_TRUNC('month', date_recorded) collapses daily records into monthly buckets. A subsequent window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW computes a true 3-month rolling average over those monthly aggregates in a single pass.

Multi-CTE Weighted Score (school_queries.js) The school performance summary composes three levels of aggregation (course enrollment counts, grade averages, app usage averages) in separate CTEs and derives a weighted performance_score, demonstrating CTE composition as an alternative to temp tables.

---

Deployment

Backend and Database — Render

The repository includes a render.yaml Blueprint configuration. Connecting the repo to Render via New → Blueprint provisions a PostgreSQL 15 database and a Node.js web service. Database credentials are injected into the backend at runtime via Render's environment variable binding. No secrets are stored in the repository.

After the first deploy, run the migration and seed scripts once via the Render Shell using the provisioned DATABASE_URL.

Frontend — Vercel or Netlify

The frontend/ directory is a standard Vite build. It can be deployed to any static hosting platform by building the dist/ output and setting VITE_API_URL to the Render backend URL. Both Vercel and Netlify support this configuration through their environment variable settings UI without requiring any platform-specific configuration files.

---

Project Structure

school-district-analytics/
├── backend/
│   ├── db/
│   │   ├── migrations/001_init_schema.sql   # DDL for all 8 tables + indexes
│   │   └── seeds/seed_data.sql             # 2 districts, 5 schools, 100 students, ~1,500 grades
│   ├── queries/
│   │   ├── district_queries.js             # CTE + RANK, PERCENTILE_CONT, COUNT FILTER
│   │   ├── school_queries.js               # LAG window function, multi-CTE weighted score
│   │   └── course_queries.js               # DATE_TRUNC, rolling window avg, RANK partition
│   ├── routes/
│   │   ├── districts.js
│   │   ├── schools.js
│   │   └── courses.js
│   ├── db.js                               # pg.Pool configuration and query helper
│   ├── server.js                           # Express entry point
│   ├── Dockerfile
│   └── package.json
├── frontend/
│   ├── src/
│   │   ├── api/client.js                   # Fetch-based API client
│   │   ├── components/
│   │   │   ├── DistrictSummary.jsx         # Stacked bar chart + top students table
│   │   │   ├── SchoolPerformance.jsx       # Course avg bar + trend table + stat cards
│   │   │   ├── CourseGradeTrends.jsx       # Line chart + rolling avg overlay + rankings
│   │   │   ├── TopStudentsTable.jsx        # Reusable ranked student table
│   │   │   └── FilterBar.jsx              # Cascading district → school → course dropdowns
│   │   ├── pages/Dashboard.jsx            # Page layout and filter state
│   │   ├── App.jsx
│   │   └── main.jsx
│   ├── Dockerfile
│   ├── nginx.conf
│   ├── index.html
│   ├── vite.config.js
│   └── package.json
├── docker-compose.yml
├── .env.example
├── render.yaml
└── README.md

---

Environment Variables

Variable	Description	Default
DB_HOST	PostgreSQL host	localhost
DB_PORT	PostgreSQL port	5432
DB_USER	Database user	postgres
DB_PASSWORD	Database password	postgres
DB_NAME	Database name	school_analytics
PORT	Express server port	4000
VITE_API_URL	Backend URL consumed by the React build	http://localhost:4000

See .env.example for a complete reference. No secrets are committed to the repository.

---

License

MIT