8000
Skip to content

VIEW_COUNT_FIX_ANALYSIS.md

Latest commit

 

History

History
108 lines (82 loc) · 4.17 KB

VIEW_COUNT_FIX_ANALYSIS.md

File metadata and controls

108 lines (82 loc) · 4.17 KB
Raw

View Count Increment Fix - Analysis & Solution

Problem Identified

The view count increment was failing due to Row Level Security (RLS) policies blocking the UPDATE operation in the increment_view_count function, even though the function was marked as SECURITY DEFINER.

Root Cause Analysis

  1. Function Definition: The increment_view_count function was correctly defined with SECURITY DEFINER
  2. RLS Policies: The agents table had an UPDATE policy that required authentication: 
    CREATE POLICY "agents_update_policy" ON public.agents
FOR UPDATE 
USING (
  (auth.uid() IS NOT NULL AND auth.uid() = user_id)
  OR
  (auth.role() = 'service_role')
);
  3. Issue: Even with SECURITY DEFINER, PostgreSQL was still applying RLS policies to the function
  4. Result: Anonymous users calling increment_view_count were blocked from updating view counts

Symptoms Confirmed

  • ✅ Frontend correctly calls supabase.rpc('increment_view_count', { agent_id: UUID })
  • ✅ Database function exists and has correct logic (UPDATE agents SET view_count = view_count + 1)
  • ✅ API endpoints correctly return view_count field (not download_count)
  • ❌ Database updates were blocked by RLS policies
  • ❌ View counts reverted to original values on page refresh

Solution Implemented

Created migration 20250803000000_fix_increment_view_count_rls.sql that:

  1. Drops the existing function
  2. Recreates with explicit RLS bypass: 
    CREATE OR REPLACE FUNCTION increment_view_count(agent_id UUID)
RETURNS TABLE(new_view_count INTEGER) AS $$
BEGIN
  -- Temporarily disable RLS for this function
  SET local row_security = off;
  
  -- Atomically increment and return the new count
  UPDATE public.agents 
  SET view_count = view_count + 1 
  WHERE id = agent_id;
  
  -- Return the new view count
  RETURN QUERY 
  SELECT agents.view_count 
  FROM public.agents 
  WHERE agents.id = agent_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
  3. Maintains security permissions for both authenticated and anonymous users
  4. Adds documentation explaining the security model

Key Changes

  • SET local row_security = off;: Explicitly disables RLS within the function scope
  • SECURITY DEFINER: Function runs with creator privileges, not caller privileges
  • Atomic operation: Single UPDATE statement ensures consistency
  • Safe operation: Only increments view counts, no sensitive data access

Security Considerations

✅ Safe for anonymous access: Only allows incrementing view counts, no other operations ✅ Atomic: Uses single UPDATE statement to prevent race conditions
✅ Scoped: RLS bypass is local to function execution only ✅ Documented: Clear comments explain the security model

Migration Status

  • ✅ Migration created: 20250803000000_fix_increment_view_count_rls.sql
  • ✅ Successfully applied to remote database via supabase db push
  • ✅ Function grants maintained for authenticated and anon roles

Expected Behavior After Fix

  1. Frontend calls supabase.rpc('increment_view_count', { agent_id: UUID })
  2. Function executes with RLS disabled, updating view_count successfully
  3. Returns new count to frontend for optimistic UI updates
  4. Database persists the increment - no reversion on page refresh
  5. Works for all users including anonymous visitors

Testing Recommendations

To verify the fix:

  1. Open agent detail page as anonymous user
  2. Note current view count
  3. Refresh page multiple times
  4. View count should increment and persist
  5. Check database directly to confirm updates

Files Modified

  • /Users/andreasbigger/carp/site/supabase/migrations/20250803000000_fix_increment_view_count_rls.sql (new)

Files Analyzed (No Changes Required)

  • /Users/andreasbigger/carp/site/supabase/migrations/20250727200000_add_increment_view_count_function.sql ✅
  • /Users/andreasbigger/carp/api/v1/agents/latest.rs ✅
  • /Users/andreasbigger/carp/api/v1/agents/trending.rs ✅
  • /Users/andreasbigger/carp/site/src/hooks/useAgents.tsx ✅
  • /Users/andreasbigger/carp/site/src/hooks/useOptimizedAgents.tsx ✅
0