The Paul Newman Cup is an annual inter-team fitness and sports competition, and this app is the platform that runs it: scoring, verification, standings, and administration. It is small, deliberately so, and it is in production with real users. This page is the whole story in one place: what problem it solves, how it is built, what I decided and why, what I planned for going wrong, and what actually did.

The code is in a private repository and the deployed interface carries the competition’s internal branding, so this case study uses recreated diagrams rather than screenshots. Every diagram reflects the system as it actually runs.

1. The operational problem

A year-long competition with multiple teams, several parallel competitions, and points that people genuinely care about, previously run the way these things always start: spreadsheets, chat messages, and one overworked organiser. That shape has predictable failure modes. Results get recorded late or twice. Nobody can see live standings, so interest decays between events. Verifying that claimed activities actually happened means chasing people for evidence. And scoring disputes have no authoritative record to appeal to.

The app’s job is to be that authoritative record: one place where results enter through a controlled path, standings compute themselves, and the effort of verification drops to a review queue.

2. What it had to do

  • Score four competition pillars with different cadences: a monthly fitness circuit, a periodic sporting event, a monthly benchmark challenge, and an e-sports championship.
  • Score placings, not matches. A round robin still records who beat whom, but points come only from final standing; awarding both would double-count against the published totals.
  • Handle retrospective scoring: the benchmark challenge’s margin bands are decided only once the month’s results are in, so the system shows real times with points pending until an official closes the month, which sets the bands and banks the points.
  • Let participants connect Strava so runs, rides, and swims sync automatically, with a light plausibility check that flags suspicious paces for human review rather than auto-rejecting them. Heavier fraud detection would be disproportionate for a wellbeing competition; that is a design position, not an omission.
  • Give officials an admin area to record results, approve activities, and manage teams, with approval confirming an activity is genuine rather than awarding points by itself.

3. Architecture

Architecture: browser talks to a Flask app over an httponly session cookie; Firebase verifies identity; Strava supplies activity data via OAuth; SQLAlchemy persists to SQLite in WAL mode on a Railway volume with boot snapshots

Flask serves server-rendered pages behind gunicorn on Railway. SQLAlchemy talks to SQLite in WAL mode on a persistent volume. Firebase handles sign-in only. Strava is an activity data source, never an identity provider: you must already be signed in before connecting it.

The Procfile is one worker with eight threads, on purpose. Workers are separate processes that would each open their own pool against one database file and race on schema creation at boot; threads share a pool and serialise writes cleanly under WAL.

4. Identity versus permission

The design decision I would defend hardest: Firebase proves who you are, and the app’s own database decides what you may do. They are deliberately separate systems.

Auth flow in four steps: Firebase sign-in issues an ID token; the token is swapped at /session-login for an httponly session cookie; every request verifies the cookie without the remote revocation check; the app’s database then decides active, admin, and official status

The consequences of that split:

  • No custom claims to keep in sync. Admin is a column, readable in one query, always true when you read it.
  • The kill-switch is immediate. Session cookies are verified without the remote revocation check, because doing that check would add a network round-trip to every request. That means a disabled Firebase account could coast on a valid cookie for days, so the enforcement lives in the database instead: set active = False and the account is dead on its very next request.
  • First sign-in provisions the account. Anyone on an allowed email domain gets a row created and is sent to pick a team. The gate is the domain list, not a pre-loaded roster.
  • The system cannot lock itself out. A short list of standing admins is promoted at startup and re-asserted on every sign-in, so a mis-click in the admin panel cannot orphan the whole competition. The trade-off is stated in the docs: demoting a standing admin lasts only until they next sign in, and removing one permanently means editing the list.
  • Onboarding needed a lock. While accounts were still being loaded, a single environment variable closed sign-in for everyone except the standing admins, with public pages and existing sessions unaffected.

5. Database and deployment decisions

SQLite is the only database the app accepts; create_app rejects anything else rather than half-working. The write load is a handful of approvals and results per day, WAL keeps readers unblocked, and every write goes through SQLAlchemy, so the exit to Postgres is a connection-string change, not a rewrite. The full reasoning, including the Railway volume traps, is in Running SQLite in production.

The app builds its own database URI from a directory variable instead of accepting a raw connection string, because the difference between sqlite:/// and sqlite://// is the difference between a persistent database and one that silently evaporates on redeploy.

Deploys are deliberately manual. Auto-deploy is off, and a release means: develop, run the tests locally, wait for CI to go green, merge, tag, deploy. For a system where a bad deploy could eat a competition’s history, nothing ships implicitly.

6. Failure scenarios considered

  • The database vanishing on redeploy. The classic ephemeral-filesystem trap; designed against from the start, and it still got me through an unset environment variable. That incident, the recovery, and the hardening that followed are written up honestly in the epilogue post.
  • Losing the database file itself. Every production boot takes a WAL-safe snapshot to a dated file on the volume before migrations run, keeping the newest ten, and an admin button produces a consistent off-box download, pulled weekly. A backup that lives next to the thing it protects is only half a backup.
  • The WAL tail. In WAL mode the newest writes live in the sidecar files, not the main file, so every backup path copies the trio or uses a proper snapshot API rather than a naive file copy.
  • A compromised or departing account. The active flag kills access on the next request, regardless of what Firebase still believes about the session.
  • Admin lockout. Covered by the standing-admin guarantee above.
  • Gamed results. The plausibility check flags rather than rejects, and approval is separated from scoring, so a human decision sits between suspicious data and the leaderboard.
  • Scaling. The single-replica constraint is accepted and documented, and the ceiling is known: the day the competition outgrows one instance, the database moves before the architecture does.

7. What shipping it taught me

Three lessons that survived contact with production. A green deploy proves the app starts, not that your data persists; verify persistence explicitly after any deployment change. A safety mechanism you must remember to configure is another thing you can forget, which is why the belt now comes with braces: snapshots, off-box copies, and a startup that refuses to run misconfigured. And boring architecture is a feature: one process, one file, one deliberate way to deploy, in exchange for a system one person can fully understand, operate, and hand over.