Why I added a second voting axis — and the schema trade-off that almost didn't work

Every AI art voting platform asks one question: which image is prettier? AI Art Arena now asks two. The first is the main vote — which artwork do you want to win? The second is a separate signal: did this image actually nail the prompt that generated it? Those are not the same question, and conflating them is why most platforms surface beautiful-but-off-topic images at the top of their rankings.

Why two axes instead of one

The insight comes from how AI art is actually evaluated by practitioners. A technically impressive generation that ignores the prompt is a failure. A less flashy image that hits every element of a complex prompt is a success. If your voting system only captures aesthetic preference, you are not measuring skill — you are measuring luck and style popularity. Separating the two signals lets the leaderboard mean something beyond 'which image looked nicest this week.'

The schema decision: junction table vs. counter column

The obvious approach is a single integer column: add prompt_score to artworks, increment it on each rating. Two lines of SQL, done. The problem is deduplication. With only a counter, there is no way to enforce one-rating-per-user at the database level. You need application logic, and application logic has race conditions.

The junction table wins. It is more schema to write once, and it eliminates an entire class of bugs permanently. The same reasoning applied to the main vote system — migration 20240001 introduced the votes table, and migration 20240003 replaced five sequential queries with a single atomic SECURITY DEFINER function. Prompt votes follow the same pattern.

The RPC: submit_prompt_vote

CREATE OR REPLACE FUNCTION submit_prompt_vote(
  p_artwork_id UUID,
  p_user_id    UUID DEFAULT NULL,
  p_ip_hash    TEXT DEFAULT ''
)
RETURNS TABLE (
  success       BOOLEAN,
  error_code    TEXT,
  prompt_score  INTEGER,
  contest_id    UUID
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  v_contest_id UUID;
  v_status     TEXT;
  v_score      INTEGER;
BEGIN
  -- Resolve artwork → contest in one join
  SELECT a.contest_id, c.status
    INTO v_contest_id, v_status
    FROM artworks a JOIN contests c ON c.id = a.contest_id
   WHERE a.id = p_artwork_id;

  IF NOT FOUND THEN
    RETURN QUERY SELECT FALSE, 'ARTWORK_NOT_FOUND'::TEXT, 0, NULL::UUID;
    RETURN;
  END IF;

  IF v_status <> 'active' THEN
    RETURN QUERY SELECT FALSE, 'CONTEST_NOT_ACTIVE'::TEXT, 0, v_contest_id;
    RETURN;
  END IF;

  -- Dedup: IP already rated this artwork?
  IF EXISTS (SELECT 1 FROM prompt_votes pv
              WHERE pv.artwork_id = p_artwork_id AND pv.ip_hash = p_ip_hash) THEN
    RETURN QUERY SELECT FALSE, 'ALREADY_PROMPT_VOTED'::TEXT, 0, v_contest_id;
    RETURN;
  END IF;

  -- Dedup: authenticated user already rated?
  IF p_user_id IS NOT NULL AND EXISTS (
    SELECT 1 FROM prompt_votes pv
     WHERE pv.artwork_id = p_artwork_id AND pv.user_id = p_user_id
  ) THEN
    RETURN QUERY SELECT FALSE, 'ALREADY_PROMPT_VOTED'::TEXT, 0, v_contest_id;
    RETURN;
  END IF;

  INSERT INTO prompt_votes (artwork_id, contest_id, user_id, ip_hash)
    VALUES (p_artwork_id, v_contest_id, p_user_id, p_ip_hash);

  UPDATE artworks AS aw SET prompt_score = aw.prompt_score + 1
   WHERE aw.id = p_artwork_id
  RETURNING aw.prompt_score INTO v_score;

  RETURN QUERY SELECT TRUE, NULL::TEXT, v_score, v_contest_id;
END;
$$;

The function is SECURITY DEFINER with SET search_path = public, pg_temp. This is required any time you write a function that touches tables the caller's role cannot directly access — without it, a schema search path attack can redirect table lookups to attacker-controlled objects. The same flag appears on submit_vote. It is not optional.

The design decision that almost went wrong

The first version of the RETURNS TABLE included a column also named contest_id. The function body uses a local variable named v_contest_id, and the RETURN QUERY selects v_contest_id into the output column named contest_id. This looks fine. PostgreSQL sees it differently: inside a plpgsql function, when you reference contest_id unqualified, the engine cannot determine whether you mean the output column or a table column. This is error 42702 — ambiguous column reference — and it is the same class of bug that required migration 20240022 on the main submit_vote function.

The API route: same pattern as submit_vote

// POST /api/v1/prompt-vote — follows the same op order as /api/v1/vote:
// 1. Parse body
// 2. Zod validate (PromptVoteSchema — just artwork_id, same as VoteSchema)
// 3. Resolve identity (auth() — non-fatal, falls back to anonymous)
// 4. Hash IP — reject if no headers
// 5. Rate limit (promptVoteRateLimit — 50/IP/24h, separate prefix in Redis)
// 6. Verify artwork exists via publicClient (no-cookie, ISR-safe)
// 7. Call submit_prompt_vote RPC
// 8. revalidatePath on success

The rate limit key for prompt votes is the raw IP hash — no contest scoping, because prompt votes are per-artwork, not per-contest. A single IP can rate-vote every artwork in a contest (each is a distinct rating), so the 50/24h cap is generous enough not to block normal behavior while still preventing scripted flooding.

What this unlocks on the product side

The prompt score is displayed on every artwork card that has a prompt attached. During an active contest, voters see a small 'Nailed the prompt' button below the prompt text. After voting, it shows the current count. When a contest ends, cards show the final tally. The data is now available to drive future features: a 'prompt accuracy' filter on the leaderboard, a combined score that weights both axes, or a dedicated 'most technically precise' award alongside the main winner.

Why this is the Directed Output approach in practice

The first instinct — from both a developer and from an AI assistant given a loose spec — is to add a counter column and move on. That is the path of least resistance. It works until two users click at the same millisecond, and then it does not. The junction table approach required writing more SQL up front, but it eliminates the race condition class entirely and gives us an audit trail we did not know we needed until we started thinking about how to recompute counts after a data integrity issue.

Directed Output is not about accepting every suggestion. It is about knowing which shortcut to refuse. The counter-only approach was refused. The junction table + RPC pattern was already proven by the main vote system. The only question was whether to copy the pattern or invent a new one — and copying a proven pattern is almost always the right call.

From the build log