Skip to content

Supabase RPC Functions

Overview

This document describes the PostgreSQL RPC (Remote Procedure Call) functions used to bypass PostgREST schema caching issues when using Supabase with Railway's transaction pooling.

Why RPC Functions?

Problem: - Railway + Supabase use transaction pooling (port 6543) - Direct PostgreSQL connections don't work on Railway - PostgREST (Supabase's REST API layer) has schema caching issues - Using .table().select() sometimes returns 404 errors on newly created tables

Solution: - Create PostgreSQL functions to wrap database operations - Call functions via .rpc('function_name', args) instead of .table().select() - Functions bypass PostgREST cache and always work with transaction pooling

Available RPC Functions

User Operations

get_user_by_phone_number(phone_number text)

Get user by phone number.

Usage: 

response = client.rpc('get_user_by_phone_number', phone).execute()
user = response.data[0] if response.data else None

create_user_record(phone_number text)

Create a new user.

Usage: 

response = client.rpc('create_user_record', phone).execute()
user = response.data[0] if response.data else None

Conversation Operations

get_conversation_by_guid(p_chat_guid text)

Get conversation by chat GUID.

Usage: 

response = client.rpc('get_conversation_by_guid', chat_guid).execute()
conversation = response.data[0] if response.data else None

create_conversation_record(p_chat_guid text, p_mode text, p_participants jsonb)

Create a new conversation.

Usage: 

import json
participants_json = json.dumps(["+15551234567"])
response = client.rpc('create_conversation_record', chat_guid, mode, participants_json).execute()
conversation = response.data[0] if response.data else None

Message Operations

create_message_record(...)

Create a new message.

Parameters: - p_conversation_id (uuid) - p_sender (text) - p_text (text) - p_timestamp (timestamptz) - p_message_type (text, default: 'text') - p_message_metadata (jsonb, default: '{}')

Usage: 

response = client.rpc(
    'create_message_record',
    conversation_id,
    sender,
    text,
    timestamp.isoformat(),
    message_type,
    json.dumps(metadata)
).execute()
message = response.data[0] if response.data else None

Note: The function returns msg_timestamp instead of timestamp to avoid reserved word conflicts.

get_recent_messages_for_conversation(p_conversation_id uuid, p_limit integer, p_hours_ago integer)

Get recent messages for a conversation.

Usage: 

response = client.rpc(
    'get_recent_messages_for_conversation',
    conversation_id,
    20,  # limit
    24   # hours_ago
).execute()
messages = response.data if response.data else []

get_last_message_for_conversation(p_conversation_id uuid)

Get the most recent message for a conversation.

Usage: 

response = client.rpc('get_last_message_for_conversation', conversation_id).execute()
message = response.data[0] if response.data else None

Implementation Details

Type Casting

All functions cast VARCHAR columns to TEXT to ensure type compatibility: 

SELECT c.chat_guid::text, c.mode::text FROM conversations c

Security

All functions are created with SECURITY DEFINER to run with the owner's privileges, bypassing RLS policies.

Column Name Differences

  • Database column: timestamp → Function returns: msg_timestamp
  • Database column: message_metadata → Function returns: meta_data

These are renamed in Python code (app/database/supabase_db.py) to maintain API compatibility.

Migration History

  • 20251115_drop_and_recreate_rpc_functions - Initial RPC functions
  • 20251115_fix_rpc_function_types - Fixed VARCHAR→TEXT type mismatches

Testing

Test RPC functions directly in Supabase SQL editor: 

-- Test get_conversation_by_guid
SELECT * FROM get_conversation_by_guid('test_chat');

-- Test create_user_record
SELECT * FROM create_user_record('+15551234567');

References

  • Supabase Python Client: https://supabase.com/docs/reference/python/introduction
  • PostgREST Documentation: https://postgrest.org/en/stable/
  • Railway + Supabase Guide: https://docs.railway.app/guides/supabase