Back to Projects
In Development

UPL League Admin

Football league back-office on Firebase: denormalized data model and validated Excel fixture import

Visit Site
~/upl

$ deploy --project upl

BUILD IN PROGRESS

MANAGEMENT

Key Technical Highlights

11 Firestore collections and 18 routes: modelled teams and leagues as many-to-many by copying team and player documents into league-scoped league_teams / league_players collections, because Firestore has no joins and each league needs its own independent standings.

Closed a silent-overwrite bug in my own design: leagues use a readable natural key as the document ID, so a duplicate competition and year would have replaced an existing league. A pre-write existence check now rejects it, and the same guard rejects a duplicate match number within a league.

Excel bulk fixture import validates an untrusted file against live Firestore data: required columns, per-row completeness reported with spreadsheet row numbers, case-insensitive team and venue resolution, home equal to away rejected, date-parse fallback. A reference sheet of valid team names in the template prevents most typos upfront.

Live match events (8 stat types: goals, penalties, own goals, cancelled goals, cards, missed penalties) are a flat stats collection rather than an array on the match document, avoiding read-modify-write on a growing list.

Competition choice is a zod enum fed by one constants list, so adding a second division (UPL Championship) needed no change to the stored data shape.

The Case Study

The Problem

A football league needs one place to register teams and players, create seasons, schedule fixtures, record live match events and publish news. The data is relational (a team plays in several leagues, and each league has its own table), but the chosen backend, Firebase, has no joins. The job was to make that model work without fighting the database.

The Numbers

  • 11 Firestore collections: leagues, league_teams, league_players, matches, stats, venues, referees, news, carousel, roles, utils.
  • 18 page routes and 5 API route handlers across 103 TypeScript files.
  • 8 match-event types in the StatType enum.
  • 30 commits, May 2025 to September 2026. A solo project, so there are no team metrics.
  • The app is admin-only. There is no public traffic or SEO data to report.

Decisions and Trade-offs

Denormalize per league, not normalize. Registering a team in a league copies the full team and player documents into league-scoped collections, with every stat field defaulted so partial or legacy documents never yield undefined values. The rejected alternative was normalized references with client-side joins, which means N extra reads per standings view and makes per-league stats awkward. The cost is duplicated data that can drift from the source team record.

Natural-key document IDs, guarded. Readable league IDs make debugging easy, but a derived-name collision silently overwrites. I added an existence check before the write and left a comment on a related risk: renaming a competition string does not update IDs already derived from it. The rejected alternative was auto-generated IDs plus a uniqueness query, which is safer against renames but loses readable IDs. The existence check is also not a transaction, so two simultaneous creates could still race.

Validate the Excel import before writing. Fixture files come from people, so the importer resolves names against live Firestore data and reports exact row numbers. The rejected alternative was a naive parse that writes whatever it can, which leaves half-imported fixture lists that are hard to clean up.

Flat event collection. Goals and cards are separate minute-ordered documents instead of an array on the match. This avoids rewriting a growing list on every event, at the price of an extra query when rendering a match.

How It Was Verified

Honestly: lightly. There are no automated tests yet. The checks are the runtime validations above, TypeScript strict mode, and manual use in the deployed app. A React Server Components CVE patch was merged through a Vercel-raised pull request in January 2026.

Honest Limits and What I'd Change

  • Standings and stat aggregation were left incomplete.
  • Security hardening and test coverage are the next milestones, starting with the import pipeline and the natural-key guard.
  • A single 2,092-line query module should be split per domain, and the repo has no tests, so I would add them around the import and the natural-key guard first.

My Role

Sole developer: data model, Firebase integration, admin UI, import pipeline, deployment on Vercel and an architecture review.