Database-First Architecture

The Iron Law of this project: ALL business logic lives in PostgreSQL.

Announce: "I'm using database-first to ensure this logic goes in the right place."

The Rule

FRONTEND (src/)         → Presentation + User Input ONLY
DATABASE (supabase/db/) → Game Logic + State + Decisions
EDGE FUNCTIONS          → LLM/Embedding Integration ONLY

Where Does This Code Go?

| Feature | Database | Frontend | |---------|----------|----------| | Calculate confidence score | game_logic.calculate_confidence() | NEVER | | Determine next question | game_logic.get_next_turn() | NEVER | | Filter candidates | game_logic.filter_*_candidates() | NEVER | | Check win condition | game_logic.check_guess_decision() | NEVER | | Validate game state | DB constraints + RLS | NEVER | | Render question UI | NEVER | Vue component | | Display map markers | NEVER | MapLibre layer | | Fetch game state | RPC returns it | Store calls RPC | | Handle user input | NEVER | Vue event handler |

Anti-Pattern: Logic in Frontend

// WRONG: Scoring logic in Vue component
const confidence = computed(() => {
  const probabilities = candidates.value.map(c => c.score)
  const maxProb = Math.max(...probabilities)
  const margin = maxProb - (probabilities[1] || 0)
  // THIS IS GAME LOGIC - SHOULD BE IN DATABASE
  return { maxProb, margin }
})

Why it's wrong:

• Different clients would calculate differently
• Can't use for learning/training data
• No single source of truth
• Violates architecture

-- CORRECT: Scoring in database function
CREATE FUNCTION game_logic.calculate_confidence(p_candidates jsonb)
RETURNS jsonb AS $$
  -- Calculate max_prob, margin, entropy HERE
  -- Return as structured JSON
$$ LANGUAGE plpgsql;

RPC Call Pattern

Frontend ONLY calls RPCs and reads views:

// src/lib/api/index.ts
export const gameApi = {
  async startGame(description: string): Promise<string> {
    const { data, error } = await supabase.rpc('start_game', {
      p_description: description,
      p_language_code: 'en'
    })
    if (error) throw error
    return data  // Just the session_id
  },
  
  async playTurn(sessionId: string, answer: Answer): Promise<void> {
    const { error } = await supabase.rpc('play_turn', {
      p_session_id: sessionId,
      p_answer: answer
    })
    if (error) throw error
  }
}

Game state comes from database views, NOT computed in frontend:

// Read state from view (computed by database)
const { data } = await supabase
  .from('game_session_state')  // VIEW, not table
  .select('*')
  .eq('id', sessionId)
  .single()

// data.next_turn contains: question, candidates, confidence
// ALL computed by database, frontend just displays it

SECURITY DEFINER Pattern

Public-facing functions use SECURITY DEFINER to access game_logic schema:

-- supabase/db/public/functions/start_game.sql
CREATE FUNCTION public.start_game(p_description text, p_language_code text)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, game_logic, extensions
AS $$
DECLARE
  v_user_id uuid;
BEGIN
  -- Always validate auth
  v_user_id := auth.uid();
  IF v_user_id IS NULL THEN
    RAISE EXCEPTION 'Authentication required';
  END IF;
  
  -- Rate limiting
  PERFORM check_rate_limit('start_game');
  
  -- Internal logic in game_logic schema
  RETURN game_logic.create_game_session(v_user_id, p_description, p_language_code);
END;
$$;

Key points:

• SECURITY DEFINER = runs with function owner's privileges
• Always validate auth.uid() first
• Set search_path explicitly
• Public function is thin wrapper around game_logic function

Red Flags - STOP

If you're about to write in src/:

• Sorting or filtering candidates → STOP, use database
• Calculating scores or probabilities → STOP, use database
• Determining game state transitions → STOP, use database
• Validating user actions → STOP, use database (+ RLS)

If you see these in frontend code, FLAG IT for migration to database.

References

See references/rpc-patterns.md for more examples.