FantAsta Live

Every summer my fantasy football league meets for the auction. There are ten people and a budget of 500 credits each, and every player in Serie A gets called one at a time until every squad has 3 goalkeepers, 8 defenders, 8 midfielders and 6 forwards. For years we ran it on paper and a shared spreadsheet, and there was always an argument about who bid first. So I built an app for the auction night.

Three screens, one state

The whole app is three views over a single auction_states row.

• 
• 

• Phone (/auction). A participant claims a team, then gets the current player, the highest bid and their remaining credits, with +1 / +5 / +10 quick bids and a numeric keypad for custom amounts.
• Auctioneer console (/admin). Pick the role and the starting letter, call players, confirm assignments, override a bid, restart the timer, skip a player, restore a backup, and export all the rosters as CSV at the end.
• TV (/tv). A grid of all ten teams with their credits and slot counts (D: 5/8), the player on the block, the current highest bidder and the countdown.

Layer	Technology
Frontend	Next.js 15 (App Router), React 19, TypeScript
Backend	Supabase: PostgreSQL, Realtime, Auth
Business rules	PL/pgSQL functions called over RPC
Auth	Anonymous sessions for bidders, email login for the auctioneer
Tests	Vitest + Testing Library

Bids are database transactions

The first spec I wrote assumed Firebase and a client-side runTransaction. The version I built puts the auction rules in Postgres instead, so they are enforced in one place that no client can get around. Every command is a security definer PL/pgSQL function, and clients only ever call RPCs. They never write directly to the tables.

create or replace function public.place_bid(p_league_id uuid, p_team_id uuid, p_amount integer)
returns public.auction_states
language plpgsql security definer set search_path = public
as $$
begin
  if auth.uid() is null then raise exception 'Authentication required'; end if;

  select * into state_row from public.auction_states
    where league_id = p_league_id for update;   -- serialize concurrent bids

  if team_row.owner_user_id <> auth.uid() then raise exception 'Team ownership mismatch'; end if;
  if p_amount <= state_row.current_bid   then raise exception 'Bid must exceed current bid'; end if;
  if p_amount > team_row.credits          then raise exception 'Insufficient credits'; end if;
  -- role slot limits (3 P / 8 D / 8 C / 6 A) checked against roster_entries

  update public.auction_states set
    current_bid = p_amount, current_bidder_team_id = p_team_id, status = 'bidding',
    timer_expires_at = now() + make_interval(secs => league_row.timer_seconds),
    version = version + 1
  where league_id = p_league_id
  returning * into state_row;
  return state_row;
end;
$$;

SELECT … FOR UPDATE on the single auction row serializes bids that arrive together. Nobody gets locked out of bidding, but only one bid can pass the p_amount > current_bid check at a time. The loser gets a clear error straight away, and their UI already shows the winning bid. The client runs the same checks first (validateBid in src/lib/auction.ts), but only for instant feedback. The database has the final say.

Time comes from the server

A countdown that each phone computes from “last bid + 10 seconds” drifts, and it drifts differently on every phone. Instead, each bid writes an absolute timer_expires_at timestamp on the server. Every screen renders its countdown from that one value, so all the phones and the TV reach zero together.

When the timer expires, the app doesn’t assign the player automatically. Every screen shows who won, and the auctioneer confirms. The room is full of people, and a human has to be able to say “wait, that bid was a mistake”. The console can override the amount or the team, or restart the timer, before confirm_assignment_and_advance runs.

Recovering from mistakes

confirm_assignment_and_advance does more than assign the player. In the same transaction, it:

1. snapshots all teams, rosters, players and the auction state into auction_backups as JSONB,
2. inserts the roster entry, marks the player as assigned and deducts the credits,
3. finds the next available player in the current role, or marks the round as complete when the order wraps back to where the round started.

restore_latest_backup rebuilds the league from that snapshot. The auctioneer gets a one-step undo for the most expensive mistake possible, assigning a player to the wrong team, without any manual SQL on auction night.

Realtime and sessions

Supabase Realtime publishes changes to auction_states, teams and players. Every screen subscribes once, and on each change it takes the new auction state and refetches the teams and the current player. Participants sign in anonymously and then claim a team through claim_team, which ties the team to their auth user. That session is kept in localStorage, so a phone that locks mid-auction comes back to the same team.

The pure logic runs on both server and client: slot limits, finding the starting letter, round wrap-around, building the export rows. It lives in src/lib/auction.ts and is unit-tested with Vitest, and the page components have render tests.

What I’d do next

• Load test the bid path. Simulate ten clients hammering place_bid and measure lock wait times and p95 latency. That would give a real number to replace my assumption that “one row lock is fine at this scale”.
• Multi-league support. The schema is already keyed by league_id, but the UI still assumes a single league.
• Presence. Show on the TV which participants are connected, so nobody’s bid gets lost because their phone went to sleep.