Why I replaced 5 database queries with one atomic Supabase function

The vote endpoint started as five sequential database calls: check the contest is active, verify the artwork exists, check for a duplicate vote, insert the vote row, increment the artwork count. It worked in testing. It was also fragile, slow, and had a race condition hiding in plain sight — the kind that only fires when two users click at the same millisecond.

What five sequential queries actually cost

Each database query from a Vercel serverless function to Supabase involves a network round trip. On a good day that is 30-50ms per hop. Five hops means 150-250ms minimum before a response leaves the server — before rate limiting, before the response is serialised, before the client receives anything. For a vote button that should feel instant, that is a long time to wait.

But the latency was not the real problem. The real problem was the gap between the duplicate-vote check and the vote insert. Two requests arriving within the same millisecond both pass the check — neither sees the other's vote yet, because neither has been written. Both insert. One user gets counted twice. The vote count is wrong, permanently, and there is no clean way to fix it without auditing every vote.

The SECURITY DEFINER function

PostgreSQL SECURITY DEFINER functions run as the function owner, not the calling user. More importantly for this use case: they execute as a single atomic transaction. No other query can interleave with them. The check-then-write pattern that creates a race condition in application code is immune to races inside a single transaction.

CREATE OR REPLACE FUNCTION public.submit_vote(
  p_artwork_id  UUID,
  p_user_id     UUID DEFAULT NULL,
  p_ip_hash     TEXT,
  p_email_hash  TEXT DEFAULT NULL
) RETURNS TABLE (success BOOLEAN, error_code TEXT, vote_count INTEGER)
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  v_contest_id     UUID;
  v_contest_status TEXT;
  v_already_voted  BOOLEAN;
  v_new_count      INTEGER;
BEGIN
  -- Derive contest_id from artwork — prevents client from lying about which contest
  SELECT a.contest_id, c.status
  INTO v_contest_id, v_contest_status
  FROM artworks a JOIN contests c ON c.id = a.contest_id
  WHERE a.id = p_artwork_id;

  IF v_contest_id IS NULL  THEN RETURN QUERY SELECT FALSE, 'ARTWORK_NOT_FOUND'::TEXT,  0; RETURN; END IF;
  IF v_contest_status != 'active' THEN RETURN QUERY SELECT FALSE, 'CONTEST_NOT_ACTIVE'::TEXT, 0; RETURN; END IF;

  -- Check all three duplicate-vote vectors atomically
  SELECT EXISTS(
    SELECT 1 FROM votes v WHERE v.contest_id = v_contest_id
    AND (
      v.ip_hash    = p_ip_hash
      OR (p_user_id    IS NOT NULL AND v.user_id    = p_user_id)
      OR (p_email_hash IS NOT NULL AND v.email_hash = p_email_hash)
    )
  ) INTO v_already_voted;

  IF v_already_voted THEN RETURN QUERY SELECT FALSE, 'ALREADY_VOTED'::TEXT, 0; RETURN; END IF;

  INSERT INTO votes (artwork_id, contest_id, user_id, ip_hash, email_hash)
  VALUES (p_artwork_id, v_contest_id, p_user_id, p_ip_hash, p_email_hash);

  -- Alias table to avoid ambiguity with RETURNS TABLE column 'vote_count'
  UPDATE artworks AS aw SET vote_count = aw.vote_count + 1
  WHERE aw.id = p_artwork_id
  RETURNING aw.vote_count INTO v_new_count;

  RETURN QUERY SELECT TRUE, NULL::TEXT, v_new_count;
END;
$$;

The ambiguous column bug that cost an hour

The first working version of this function shipped a bug that only surfaced after the first real votes were cast. When the RETURNS TABLE declaration includes a column named vote_count, and the UPDATE clause does RETURNING vote_count, PostgreSQL cannot resolve which one you mean. It throws ERROR 42702: column reference is ambiguous — and every single vote submission returned a 500.

This is the kind of bug the refinement loop catches — not by anticipating every edge case upfront, but by refusing to stop at 'it compiles and passes tests.' You push until you understand every line, then you push a bit further. The ambiguous column reference was not in the first version. It appeared in migration 17, after an iteration that changed the function signature. Catching it required reading the error, understanding what PostgreSQL was complaining about, and writing a migration that fixed it cleanly.

What the refinement loop looked like

The function went through five migrations before it reached its current form. Each migration fixed something the previous version got wrong: the first added the function, the second fixed a double-increment trigger that was running alongside it, the third resolved the ambiguous column reference, the fourth removed a parameter that the client should never have been providing (contest_id — the function now derives it from artwork_id to prevent a client from lying about which contest it is voting on), and the fifth fixed a search_path security regression introduced by the fourth.

From the build log