The database says no: building a hackathon judging platform that can't be quietly changed

00 · Context

A checklist item is also a promise

DOGFOOD asked every team to build the same thing: an open-source, self-hostable hackathon platform. It had to cover registration, teams, submissions, judging, normalization, voting and results, start with docker compose up, and work with the network off. Hackathon Raptors will fork the winner and run their real events on it.

That last sentence changed how I read every requirement. "Scores lock after results are published" is a feature on a checklist. It is also a promise to a few hundred participants that nobody, including the organizers, can nudge a number afterwards. A promise like that can't depend on every developer remembering an if statement.

So I built Dog-Food around one question: what is the last thing that can say no?

01 · The architecture decision that shaped everything

Put the rules in the database

Most web apps enforce rules in the request handler: check the deadline, then insert. That works until someone adds a second code path. An import script, an admin tool, an API endpoint written at 3 a.m., or an organizer with psql open. Each one has to remember every rule.

So PostgreSQL is Dog-Food's last line of defence. The app still checks everything and returns friendly errors. Underneath, triggers refuse the same changes, with their own error codes, whatever path the change comes from:

The score lock is the simplest one:

CREATE FUNCTION scores_locked() RETURNS trigger AS $$
DECLARE ev text; pub timestamptz;
BEGIN
    -- ...find the event this row belongs to...
    SELECT results_published_at INTO pub FROM events WHERE id = ev;
    IF pub IS NOT NULL THEN
        RAISE EXCEPTION 'results are published; scores are locked' USING ERRCODE = 'DF004';
    END IF;
    RETURN coalesce(NEW, OLD);
END $$ LANGUAGE plpgsql;

migrations/003_organize.sql. The same function guards scores, score_items and pairwise comparisons.

In my demo video I run an UPDATE scores by hand after publishing, and the database refuses it. Of everything in the project, that one refusal is what I'd point to.

One decision, many consequences

• No ORM. Every query is plain SQL with psycopg 3. When the rules live in triggers, I want to see exactly which statement fires them.
• Composite foreign keys. Tables carry (id, event_id) pairs, so a score can't point at a project from another event, even by mistake.
• One live entry per team is a partial unique index, not an if statement. The fixture has a team that submitted twice (prj_41). The second entry is stored as duplicate and never ranked.

CREATE UNIQUE INDEX one_live_entry_per_team
    ON projects (team_id) WHERE status IN ('draft', 'submitted');

01b · What the triggers cost me

Escape hatches are where the bugs live

Two things went wrong.

The importer. Loading the fixture means inserting projects whose deadline passed months ago, which DF001 refuses. I added a transaction-local setting, dogfood.importing, that the triggers respect. It is one escape hatch, it lives for one transaction, and it is documented. I still don't love it (see what I'd change).

Moderation after the deadline. Organizers must be able to withdraw an entry or mark it a duplicate after submissions close. So the deadline trigger allows an update when only the status changes:

IF TG_OP = 'UPDATE' AND (NEW.title, NEW.summary, NEW.repo_url, NEW.track_id, NEW.team_id)
                     IS NOT DISTINCT FROM (OLD.title, OLD.summary, OLD.repo_url, OLD.track_id, OLD.team_id)
   AND NEW.status IN ('withdrawn', 'duplicate') THEN
    RETURN NEW;   -- content frozen, status may move
END IF;

That looks safe, because the content can't change. Then the adversarial review found the hole. Try it:

The trigger did exactly what I told it to. What was missing was an app-level rule about which status may move to which. Now only submitted entries can be withdrawn, and only entries that were submitted can be restored.

02 · The scoring algorithm I almost shipped

The textbook fix was worse than doing nothing

In the fixture, judges' average scores range from 25 to 81 out of 100 on the same kind of projects. With raw averages, your rank depends on who happened to review you. The spec asks for cross-judge normalization, and the textbook answer is a per-judge z-score: subtract each judge's mean, divide by their spread.

I implemented it, then looked at what it did to the real fixture. One judge, Tomas Varga, reviewed exactly one project and gave it 25. A plain z-score decides from that single review that Tomas is extremely harsh, and gives that project a huge boost. One data point gets treated as a personality.

On the real fixture that's not hypothetical. Tomas's one review was for Dry Harbour, which raw averages rank 30th of 40. A plain z-score lifts it to 8th, on the strength of one judge's one opinion. (Try it in Lab 3 below: press "Plain z-score".)

The fix is shrinkage: pull each judge's mean and spread toward the whole panel, more strongly the fewer reviews they have.

m2  = (n * m + k * M) / (n + k)                # judge mean, pulled toward panel mean M
sd2 = sqrt((n * v + k * var) / (n + k))       # judge spread, pulled toward panel spread
adjusted = M + S * (x - m2) / sd2

score = (sum(adjusted) + c * M) / (len(adjusted) + c)   # c "average reviews" per project

services/results.py: adjust() and project_score(), both pure functions, so the proof script below can call them directly.

02b · Not by feel

500 simulated events, ranked by the portal's own code

A formula that "looks fair" is easy to believe and hard to defend. So I wrote tools/normalization_proof.py. It simulates 500 events shaped like the fixture: 40 projects, 30 judges, 3 reviews each assigned by my real planner, and about 20% of third reviews missing. Every project has a hidden true quality. Every judge has a leniency and a scale, and 10% are flat scorers who give about 4 to everything, like fixture judge Iva Petrova. Then it ranks each event with Dog-Food's real scoring functions and compares the ranking with the truth.

K wasn't picked by feel either. The same simulation sweeps it:

The curve is flat around the top, so any small K works. I chose 3 because it is easy to explain to an organizer: a judge's habits count fully once they have clearly more than three reviews.

The parameter that didn't matter, and why I kept it

The project prior C = 1 adds one "average review" to every project, so two enthusiastic reviews can't beat five solid ones. I expected it to improve accuracy. The sweep showed ρ stays at 0.870 whatever C is (0.870, 0.871, 0.870, 0.870, 0.869 for C = 0 to 4).

So the docs call it what it is: a fairness choice, not an accuracy one. The organizer's results preview shows a ▲/▼ next to every project the correction moved, so nobody has to trust a formula they can't see. Being able to say "this parameter does nothing for accuracy, and here's why I kept it anyway" is more defensible than inflating it.

The proof also runs as a smaller test on every build (tests/test_normalization_proof.py). If someone "improves" the maths and makes it worse, the build fails.

02c · A statistic that lied with small numbers

Pairwise mode, and the τ I had to hide

People are bad at absolute scales ("is this a 3 or a 4?") and good at comparisons ("which of these two is better?"). For the pairwise bonus, judges answer the second kind of question, and Dog-Food fits a Bradley–Terry model with the MM algorithm: P(i beats j) = pᵢ / (pᵢ + pⱼ).

Two details mattered:

• A virtual draw. Every entry also plays one draw against an "average entry". Without it, an entry that won every comparison gets infinite strength, and a rarely compared entry swings wildly.
• Close calls first. The generator asks about entries next to each other in the current ranking, where the rubric is least sure. Which side is shown on the left comes from a keyed hash per judge, so no entry always benefits from being seen first.

I wanted organizers to see how well pairwise agrees with the rubric, so I first showed Kendall's τ between the two rankings. With only a handful of answers, τ swung all over the place, and an organizer could easily act on noise. Watch it happen:

Now the preview shows τ only once there are about three answers per entry. Before that, it shows a plain count like "judges agreed with the rubric in 7 of 9 head-to-heads", plus the exact pairs that disagree. A count can't pretend to be more certain than it is.

Pairwise doesn't replace the published ranking: the rubric is what participants were promised. It is a second opinion. Where the two agree, publish with confidence; where they disagree, look at those entries again.

03 · The part of the spec I thought was simple

Judge invitations, and the account takeover hiding in them

Invitations looked like the simplest part of T2. The organizer types an email and gets a one-time link, the judge opens it and sets a password, and no email server is needed.

The real version: one account can judge in several events, run by different organizers, and the link sets the password of an account that may already exist. The review found an attack in that gap. Step through it:

A second review pass found the same attack in the reverse order, with the attacker inviting first. An old, unused link could also reset the password of an account that was already active. The fix has three parts:

• While an account is pending activation for one event, no other event can add a role to it (409 pending_elsewhere; only the admin can).
• A link never works on an active account. It is never a password reset.
• Accepting a link cancels the account's other open links.

The spec doesn't say "multi-tenant identity is hard". You find out the moment two organizers share one users table.

03b · Two smaller ones from the voting side

Two doors, one budget

People's-choice voting has two ways in: vote with an account, or with a one-time email link (for people who never sign up). Both worked, and both enforced a budget of 3 votes. The trouble was that each door kept its own budget.

The fix: a voter is identified by a keyed hash of their email address on both paths (HMAC with a server secret, so the table never holds raw addresses). One person has one budget, whichever door they use. The budget check itself takes a row lock (SELECT … FOR UPDATE), so 20 parallel requests with 3 votes left store exactly 3. There's a test for that.

Unsealing by editing. Counts are sealed while voting is open, even from the admin. But an organizer could clear the voting dates mid-vote, which ended voting and showed the counts. Now voting can't be removed or reopened once any vote exists (409 votes_exist).

03c · How I used AI

The most useful agent was the one told to break it

AI assistants are allowed and expected in DOGFOOD, and I built Dog-Food with Claude. The biggest jump in quality came after the features worked: I gave the code to a separate agent with one instruction: break it. It ran three rounds. Every finding was fixed and got its own regression test in tests/test_review_fixes.py.

The tests I'd written hadn't caught any of these. That wasn't for lack of tests: there are 132. But my tests encode my assumptions, and these bugs lived where my assumptions were wrong. A reviewer that doesn't share them is worth more than another hundred tests from the same author.

The webhook fix is a good example of how deep "just block private IPs" goes. The final sender resolves the host once. It refuses any address that isn't global, including IPv4-mapped and NAT64 forms. Then it connects to exactly that IP (so a second DNS answer can't swap it), and it never follows a redirect.

04 · The Docker and offline gotchas

"Works with the network off" rules out more than you think

• Fonts. One Google Fonts link in a template breaks it. I bundled the IBM Plex fonts (SIL OFL) in static/fonts, and a test fails the build if any template references an external script, style or font. This blog uses the same bundled files.
• Docker Hub was blocked in the environment I built in, so I couldn't build the image there. I verified the app in the closest thing to the container: a fresh virtualenv, a non-root user, Postgres over TCP, and the pinned requirements. Then I built and ran the real image on my laptop. That means verifying in two places, and next time I'd plan for it from day one.
• Seeding from the fixture's own date. One acceptance check needs the fixture event to be closed. If the seed computes dates from "now", the check passes or fails depending on when the judges run it. The seed uses the fixture's own submissions_close.
• Webhooks can't need the internet either. Delivery goes through an outbox table, written in the same transaction as the change. A rolled-back change never fires a webhook, and a receiver that's down just means retries (signed t=…,v1=…, same envelope id every time).

04b · Honest gap reporting

The line I didn't hide

My .dogfood.toml claims T1 to T4. The official checker said:

DOGFOOD 2026 acceptance report
claimed: T1 T2 T3 T4

T1  gallery is public ................. PASS
T1  project from fixtures shown ....... PASS
T1  closed event refuses submissions .. PASS
T2  judge sees own scores ............. PASS
T2  judge cannot see peer scores ...... PASS
T2  participant blocked ............... PASS
T2  csv export works .................. PASS

claimed T1 T2 T3 T4, verified T1 T2
note: claimed but not verified: T3 T4

That's not a failure: the official checker only tests T1 and T2. I didn't hide the line or reword it. Instead, docs/edge-cases.md lists every T3 and T4 policy next to the test that proves it. Ten identical votes at once become one vote. Twenty parallel votes with 3 left store exactly 3. A webhook to 169.254.169.254 is refused. Export, import, export gives identical content. Sixteen kinds of broken import file each return 400 and write nothing.

04c · The schema and code I'd redo

What I would change

1 · now() in the deadline trigger

In PostgreSQL, now() is the start time of the transaction, not the moment of the insert. A transaction that begins just before the deadline and writes just after it passes the check. Each Dog-Food request is one short transaction, so the window is tiny. But clock_timestamp() is the correct choice, and it's the first thing I'd change.

2 · The in-memory rate limiter

It is simple, works offline, and checks login limits before verifying the password, so the right password still gets a 429 after 10 failures. But run two app containers and each has its own memory, so the limits double. For one container on one laptop, which is what the spec asks for, it's right. For a scaled deployment it belongs in Postgres.

3 · The importer escape hatch

I'd rather the importer write through a dedicated database role that the triggers recognise than through a session setting any connection could set.

Left out on purpose

No ORM, no single-page app, no CDN, no Redis. Each one adds a moving part and no guarantee. Dog-Food is one FastAPI app with server-rendered pages and one PostgreSQL database. It starts with one command and works with the Wi-Fi off.

If Hackathon Raptors really does fork one of these, I want the person running it on a Saturday night to be able to read every line.