Schools Quiz Platform

The integrity model is default-deny. Every table has row-level security enabled with zero policies for the anon and authenticated roles — the service role bypasses RLS, and nothing else gets in. The risk with that pattern is the obvious one: someone adds a new table later and forgets to enable RLS, and now there is a hole. So an event trigger enables RLS automatically on any new table.

-- Any table created later is locked down the moment it exists.
create or replace function force_rls()
returns event_trigger
language plpgsql as $$
declare obj record;
begin
for obj in
  select * from pg_event_trigger_ddl_commands()
  where command_tag = 'CREATE TABLE'
loop
  execute format('alter table %s enable row level security', obj.object_identity);
end loop;
end $$;

create event trigger on_create_table
on ddl_command_end when tag in ('CREATE TABLE')
execute function force_rls();

All the real logic is in SECURITY DEFINER functions with EXECUTE revoked from the public roles, which closes the PostgREST griefing vector entirely. Forgetting a policy fails closed, not open. It is the inverse of the usual default where a missed WHERE clause leaks the whole table.

Ranking sounds trivial until you remember that a hard cutoff means thousands of students can finish with the identical completion time. Anyone who runs out the clock shares exactly 3,600,000 ms. The ranking view orders by score descending, then completion time ascending — and originally that was it, using a plain window function.

row_number() over (
partition by category
order by score desc,
         completion_time_ms asc,
         attempt_id            -- the fix
)

The bug surfaced in the export. With thousands of identical (score, time) pairs, row_number() is non-deterministic across queries, so the committee's chunked, paginated CSV export was duplicating some students and skipping others between pages. Appending the unique attempt_id as a final tiebreaker gives a total, stable order — the same every time the query runs — which is what makes paginated export correct. The human-facing tiebreaker, the submitted slogan, decides genuine ties for prizes; the attempt_id just guarantees the machine ordering is reproducible.

The whole load profile is one spike per day. A few mitigations stack up to absorb it. Saving an answer is a single upsert RPC that re-checks the deadline server-side, replacing an earlier two-call read-then-write. The answer-finalise path is a set-based job run by pg_cron every minute, so abandoned attempts get scored and closed without anyone polling. Rate limiting on the result/certificate identity gate is a DB-backed atomic fixed-window counter that fails open, keyed per target email so it is safe behind shared school NATs.

Time is handled by construction rather than by hope. Vercel runs in UTC, the windows are in Malaysia time, and there is no JavaScript date math anywhere in the gating — absolute instants are compared against the window table inside Postgres, with an inclusive start and an exclusive end. A dev-only clock that advances with real elapsed time made the whole timed flow, including the 10pm cutoff, testable weeks before the event. The whole thing was then run against a k6 harness sized for ten thousand concurrent users, behind a secret-gated test endpoint that 404s unless explicitly enabled.

A small one, but it is the kind of safety property that saves an event. There has to always be at least one active super-admin, or the organisers lock themselves out mid-competition. A friendly check in the application handles the message, but the real guarantee is a BEFORE UPDATE OR DELETE trigger that atomically counts the remaining active super-admins and raises if the operation would take the count to zero. That makes it race-proof against two admins demoting each other at once, and proof against someone running the UPDATE directly in SQL.