Sports Archive: One Statistics Engine for Team and Individual Sports

2026-06-09

1. Project Overview

Sports Archive is a public archive for a sports program's history (athletes, teams, seasons, records, hall of fame, photo galleries, and written stories), backed by an admin where non-technical staff enter the data.

It's a decoupled system: a Next.js frontend on Vercel talking to a Django REST API. I designed and built both sides: the data model, the API, the public archive, and the staff admin.

The API is a deliberate boundary, not a byproduct of the frontend. It lives on its own host and is designed to be consumed by applications other than the one I built, so a future site, kiosk, or mobile app can read this data without anyone standing up a new backend or duplicating the schema.

Live site: sports.yourmdl.org API: api.sports.yourmdl.org (not publicly browsable)

2. Challenge

One system has to track sports that have almost nothing in common.

Football has touchdowns and per-game box scores. Track and field has 100m times and personal records set at a meet. A swim event is a duration; a season scoring leader is a sum. Model each sport on its own and you get a wall of near-duplicate tables.

The first version did exactly that. A hardcoded model per sport: FootballStats, BasketballStats, and so on. Every new sport meant a new model, a migration, and a deploy. Worse, it put the shape of the data in my hands instead of the people who actually run the program.

The requirements that drove the rebuild:

Track team sports (games, rosters, box scores) and individual sports (meets, events, race times, personal records) in one schema.
Let an admin define a new stat without touching code or shipping a release.
Roll season numbers up into career totals for athlete profiles.
Make the whole archive publicly searchable and fast.

3. Architecture Overview

Shape: Next.js on Vercel → Django REST Framework API (Docker on EC2) → PostgreSQL, with Typesense for search and S3 for media.

Frontend: Next.js 16 and React 19 with TypeScript and Tailwind, on Vercel. It serves the public archive and an authenticated staff admin, talking to the backend over a CORS'd REST boundary. Auth is Django session cookies plus a CSRF token fetched for mutating requests, so the trust boundary stays in Django rather than splitting across a separate identity provider.
Backend: Django 6 and Django REST Framework, containerized with Docker Compose and deployed to EC2 over SSH from a GitHub Actions workflow, with gunicorn fronting the app.
Data: PostgreSQL as the system of record. Photos and media live in S3 through django-storages, so the app servers stay stateless.
Search: Typesense, indexed from Django through a management command and a reindex endpoint, and queried from the frontend with the Typesense client.

4. Implementation Details

One statistics engine instead of a model per sport. Two models replace all the hardcoded ones:

StatDefinition describes what a stat is for a given sport category: its name, data type (integer, decimal, duration, or text), and how it aggregates (sum, average, min, max, or none). Admins create these in the UI, so adding "Touchdowns" or "100m Time" is data entry, not a deploy.
StatValue stores one value against a polymorphic context: a roster entry, a game, a meet, or a team season, as nullable foreign keys. The combination is the meaning: a roster entry alone is a season stat; roster entry plus game is a per-game stat for a team sport; roster entry plus meet is a per-meet result for an individual sport.

That polymorphism is the whole trick. The same engine covers a football box score and a track meet without branching on sport type anywhere.

Durations stored as milliseconds. Race times are integers, not formatted strings. So the exact min/max aggregation that finds a season high in points also finds a personal best in the 100m. One code path, not two. The UI parses ss.ms, mm:ss.ms, or hh:mm:ss.ms on the way in and formats it back on the way out.

Career stats computed at query time. Career totals aren't stored. They're aggregated on the fly from an athlete's season values, grouped by definition and reduced by that definition's method. Storage is sparse (an empty stat never creates a row), so the table stays lean and there's no stored aggregate that can drift out of sync with the seasons it came from.

A tradeoff I made on purpose. Recomputing career stats on every profile view trades cheaper writes for more expensive reads. For an archive (read-heavy, write-rare), that's the right side of the trade, and it removes an entire class of "the cached total is wrong" bugs. If profile traffic ever outgrows it, the aggregation step is a clean place to add caching later.

Migrating without losing history. The old per-sport tables held real data. The move to StatDefinition and StatValue shipped with a migration that carried every existing value across, so adopting the flexible model was a transformation, not a reset.

API-first on purpose. The frontend is one consumer of the API, not its owner. Everything the public archive renders comes through the same documented REST endpoints any other client would use, on a separate host at api.sports.yourmdl.org.

That constraint costs something up front: no reaching into the ORM from a template, no endpoint shaped around one page's layout. What it buys is that the archive's data outlives the site currently displaying it. A department that wants a record board in a gym, a mobile app, or a redesigned frontend in five years starts by reading an endpoint, not by hiring someone to rebuild the backend. For an institution that has already migrated its archive once, that is the difference between a system it owns and a system it rents from whoever built the UI.