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 over a hundred members. 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.

Updated 21 October 2026. The first version of this page was written on 24 July, at version 1.4.3, and the app has had more than sixty releases since. Four things that version said were wrong even when it was published: deploys were already automatic, snapshots were daily rather than per boot, the weekly off-box download was a habit rather than a schedule, and Strava sync had never been switched on. Those passages are corrected below, and section 7 is new.

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. Since October a closed month shows the points actually banked rather than recomputing them from the bands, because one month’s awards could not be expressed as bands at all.
  • Accept runs, rides, and swims through a form that checks each entry as it arrives. A Strava integration, with its own plausibility check on paces, is built but has never been switched on, so every entry today comes through the form. Heavier fraud detection would still 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: the browser reaches the app through Cloudflare; the Flask app, behind gunicorn with one worker and eight threads, holds an httponly session cookie minted after Firebase verifies identity; SQLAlchemy persists to SQLite in WAL mode on a Railway volume, with daily snapshots and pre-migration copies; a Strava integration is built but switched off

Flask serves server-rendered pages behind gunicorn on Railway, with Cloudflare in front. 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, and it is not switched on yet.

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, so one process keeps that race to one contestant. It does not serialise writes by itself: WAL allows a single writer at a time, and concurrent threads need a busy timeout on connect to wait their turn instead of taking SQLITE_BUSY. Schema creation runs as a one-time step before the app serves traffic, rather than resting on the worker count.

There is no scheduler either. Each month’s challenge opens and closes itself, because its status is worked out from its own dates whenever anything asks:

        if not self.is_active:
            return 'closed'
        now = datetime.utcnow()
        if now < self.starts_at:
            return 'upcoming'
        if now > self.submit_until:
            return 'awaiting_close'
        return 'open'

The docstring above that method gives the reason in one line: “No job means no missed job.” August’s challenge opened on time with nobody touching it.

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. That gives a starting point for a future move to Postgres, but configuration, schema migration and database-specific behaviour would still need work and testing. It is a plan, not a proven escape route. 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 automatic, and gated. Railway builds main only once GitHub’s CI has gone green, so a merge is a deploy. CI runs the test suite, now 364 tests at 87 per cent branch coverage against an 80 per cent floor, and a dependency audit. The audit is retried, so that an outage of the service it checks against is absorbed and a real vulnerability is not:

        run: |
          for attempt in 1 2 3; do
            echo "== pip-audit attempt $attempt"
            pip install -q pip-audit && pip-audit -r requirements.txt && exit 0
            [ "$attempt" = 3 ] || sleep 20
          done
          exit 1

On 21 October that audit went red on eight vulnerabilities in the image library, and production waited until the upgrade was in. Every change to live data runs behind a named snapshot taken first.

The first version of this page called deploys deliberately manual, with auto-deploy off. The release document said so, and production had been deploying itself from main all along. I wrote down what the document said instead of what the system did.

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. The app takes a WAL-safe snapshot to a dated file on the volume, at most one a day, keeping the newest ten. The first version of this page said every boot; it was always once a day, with a boot as the trigger, and that trigger was the problem. A healthy container that never restarted simply stopped taking snapshots, and no log line exists for a backup that did not happen. Now, on the first request of a new day, the process also attempts a snapshot. That is not a scheduled backup: a day with no requests gets no attempt, and a failed attempt is not retried by that path until the next day.

    Separately, a deploy that adds columns takes its own copy first. The startup code handles additive changes, new columns on existing tables, and this check finds exactly those, worked out from the models rather than kept by hand so it cannot drift away from them. It does not compare types, constraints or indexes, and it skips tables that do not exist yet, since a new empty table has nothing to lose:

        inspector = inspect(db.engine)
    pending = []
    for table in db.metadata.sorted_tables:
        if not inspector.has_table(table.name):
            continue
        existing = {col['name'] for col in inspector.get_columns(table.name)}
        pending.extend(f'{table.name}.{c.name}'
                       for c in table.columns if c.name not in existing)
    return pending

    An admin button still produces a consistent off-box download, but pulling it is a human habit, not a schedule, and no restore from an automatic snapshot has been tested yet. A backup that lives next to the thing it protects is only half a backup.

  • The WAL tail. In WAL mode the newest writes can sit in the write-ahead log rather than the main file, so copying the file while the app is writing is not a safe backup. Both backup paths, the daily snapshot and the admin download, use SQLite’s online backup API instead, to produce a consistent copy while the database remains in use.

  • 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 or mistyped results. Approval is separated from scoring, so a human decision sits between suspicious data and the leaderboard, and the form now refuses anything that provably cannot count. Section 7 explains why that moved to the form.

  • 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.

  • Known gaps. There are no CSRF tokens. The session cookie is SameSite=Lax, which reduces some cross-site request risks but does not replace CSRF protection; it is an accepted gap, not a defence. And the sign-in module is the least tested code in the app, at 45 per cent coverage, because the tests fake authentication rather than exercise it.

7. The mistakes the form allowed

Infrastructure was not the only source of trouble. Three months of real use also exposed mistakes the form quietly accepted:

  • “1:08”, meant as an hour and eight minutes, was stored as 68 seconds and topped the board.
  • A watch’s hundredths turned 56:20 into 56 hours.
  • A blank time was stored as zero, which would have won.
  • An audit of 120 submissions found four duplicates that had already been approved.
  • A mislabelled running total cost someone first place.

Each time, the information needed to catch the mistake existed at the moment of entry and nowhere else. By the time an official saw it in the review queue, it was a number that looked plausible.

Put the check where the information is: at the moment of entry, with a reason the person can act on.

So one rule now decides whether an entry can count, shared by the form, the review queue, and the board. The form refuses anything that provably cannot count and says why: a 5km time faster than the world record is answered with a request to write hours, minutes, and seconds. The board ignores an impossible time even if someone approves it. And the form is shut between challenges by the same dates that open and close them, so a scored month cannot quietly keep accepting entries.

8. 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 way to deploy, in exchange for a system one person can fully understand, operate, and hand over.

Three months on, two more. Write down what the system does, not what its documentation says: this page repeated a release document that production had already outgrown. And tie a safeguard to the event it protects against. When one boot-triggered daily snapshot was doing two jobs, a long-running container stopped backing up, and a schema change on a day that already had its snapshot got no copy at all. The pre-migration copy now hangs off the migration itself. The daily one hangs off the first request of the day, which is closer to a clock than a boot is, but still not one.