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.