PostgREST Schema Cache Fix

Issue Summary

Problem: Supabase RPC functions returning 404 errors despite being created successfully.

Root Cause: PostgREST (Supabase's REST API layer) caches function schemas. When functions are created or modified, PostgREST may not see the changes immediately, causing 404 "function not found" errors.

Date Fixed: November 15, 2025

Symptoms

{
  "event_message": "POST | 404 | ... | /rest/v1/rpc/create_user_record",
  "response": {
    "status_code": 404
  }
}

Backend calls like:

response = client.rpc('create_user_record', phone).execute()

Would fail with 404 even though the function exists in the database.

Solution

The Fix (Drop and Recreate Pattern)

Following Supabase documentation best practices, we:

1. Drop all functions completely to clear PostgREST cache
2. Recreate with proper PostgreSQL syntax:
3. #variable_conflict use_column pragma
4. language plpgsql declaration
5. Clean returns table and return query syntax

Example Pattern

-- Drop old function
drop function if exists create_user_record(text);

-- Create with proper syntax
create function create_user_record (
  phone_number text
) returns table (
  id uuid,
  phone text,
  -- ... other columns
)
language plpgsql
as $$
#variable_conflict use_column
declare
  new_id uuid;
begin
  new_id := gen_random_uuid();

  insert into users (id, phone, is_provisional, subscription_status, credit_balance)
  values (new_id, phone_number, true, 'free', 0);

  return query
  select
    id,
    phone::text,
    -- ... other columns
  from users
  where id = new_id;
end;
$$;

Key Differences from Original Implementation

❌ Before (Broken)

CREATE OR REPLACE FUNCTION get_user_by_phone_number(...)
-- Missing #variable_conflict
-- Inconsistent syntax

✅ After (Working)

drop function if exists get_user_by_phone_number(text);

create function get_user_by_phone_number (...)
language plpgsql
as $$
#variable_conflict use_column
begin
  return query
  ...
end;
$$;

Why This Matters

1. PostgREST Caching: PostgREST caches function signatures for performance
2. CREATE OR REPLACE: Doesn't always clear the cache properly
3. DROP + CREATE: Forces cache invalidation
4. Proper Syntax: #variable_conflict use_column prevents naming conflicts between function parameters and table columns

Verification

After applying the fix:

-- Test create_user_record
SELECT * FROM create_user_record('+15559999999');
-- ✅ Returns: User record created successfully

-- Test get_user_by_phone_number
SELECT * FROM get_user_by_phone_number('+15559999999');
-- ✅ Returns: User record found

Prevention

When Creating New RPC Functions

Always use this pattern:

-- 1. Drop first
drop function if exists my_function(param_types);

-- 2. Create with proper syntax
create function my_function (
  param_name param_type
) returns table (
  col1 type1,
  col2 type2
)
language plpgsql
as $$
#variable_conflict use_column
begin
  return query
  select col1, col2
  from my_table
  where condition = param_name;
end;
$$;

Railway + Supabase Best Practices

1. Always use DROP + CREATE (not CREATE OR REPLACE)
2. Include #variable_conflict use_column
3. Cast VARCHAR to TEXT in return queries
4. Test functions immediately after creation
5. Use Supabase MCP server for migrations (not Alembic)

Related Issues

• PostgREST schema cache doesn't auto-refresh
• Railway transaction pooling (port 6543) prevents direct connections
• Alembic migrations incompatible with transaction pooling

References

• Supabase Functions Guide: https://supabase.com/docs/guides/database/functions
• PostgREST Schema Cache: https://postgrest.org/en/stable/schema_cache.html
• Migration History: 20251115_drop_and_recreate_rpc_functions

Testing the Fix

Monitor Supabase logs for 404 errors:

# Should see no more 404s for RPC calls
# All RPC calls should return 200 OK

Backend logs:

railway logs --service archety-backend | grep "404"
# Should return nothing

---

Status: ✅ Fixed Deployed: November 15, 2025 Functions Affected: All 7 RPC functions (users, conversations, messages)