RPC Functions

All PostgreSQL RPC functions used by Grouple

Overview

Grouple has 40+ PostgreSQL functions. They fall into four categories: trigger functions (called by triggers), security/RLS helpers (used in RLS policies), business logic functions (called via supabase.rpc()), and analytics functions.

Trigger Functions

These are called exclusively by database triggers:

Function	Called By	Purpose
auto_sync_proposal_status	Trigger	Syncs proposal status automatically
broadcast_enquiry_to_venues	on_enquiry_created_broadcast	Matches and creates venue_leads for a new enquiry
check_menu_pdf_limit	tr_check_menu_pdf_limit	Enforces PDF upload limit per subscription plan
check_venue_limit	tr_check_venue_limit	Enforces venue creation limit per plan
fn_record_response_time	trg_record_response_time	Records merchant response time to lead_response_times
fn_set_venue_lead_timestamps	trg_set_venue_lead_timestamps	Sets status timestamp fields on venue_leads
handle_proposal_confirmation	Trigger	Handles proposal confirmation side effects
link_customer_to_enquiries	on_customer_created_link_enquiries	Links pre-existing enquiries to new customer
set_merchant_rfq_number	before_insert_venue_leads_set_rfq_number	Assigns sequential RFQ number per merchant
sync_confirmed_proposal_to_settlements	trigger_sync_settlement	Creates commission_settlements on confirmation
sync_enquiry_currency	tr_sync_enquiry_currency	Sets enquiry currency from city default
sync_only_proposal_on_confirmation	Trigger	Syncs proposal data on confirmation
sync_venue_currency_from_merchant	trigger_sync_venue_currency	Syncs venue currency when merchant currency changes
update_modified_column	Trigger	Updates updated_at timestamp

Security / RLS Helper Functions

Used inside RLS policies to keep them readable:

Function	Returns	Purpose
check_can_view_merchant(merchant_id)	boolean	Can current user view this merchant?
check_can_view_venue(venue_id)	boolean	Can current user view this venue?
check_is_enquiry_customer(enquiry_id)	boolean	Is current user the customer of this enquiry?
check_is_lead_merchant(venue_lead_id)	boolean	Is current user the merchant of this lead?
is_merchant_owner(merchant_id)	boolean	Is current user this merchant?
is_proposal_customer(proposal_id)	boolean	Is current user the customer for this proposal?
is_proposal_merchant(proposal_id)	boolean	Is current user the merchant for this proposal?
is_venue_owner(venue_id)	boolean	Is current user the owner of this venue?

Business Logic Functions (called via supabase.rpc())

Function	Returns	Purpose
check_availability(...)	boolean	Checks if a venue is available for given date/time
confirm_single_booking(...)	void	Confirms a single booking
create_booking_if_available(...)	record	Creates a booking if the venue is available
customer_proposals_rpc(...)	jsonb	Returns all proposals for a customer’s enquiries
fn_get_or_create_amenity(name)	uuid	Gets or creates an amenity record
fn_get_or_create_cuisine(name)	uuid	Gets or creates a cuisine record
fn_match_venues_for_enquiry(enquiry_id)	uuid	Returns matched venue IDs for an enquiry
get_merchant_proposal_by_id_or_token(...)	jsonb	Fetches a proposal by ID or public token
get_proposal_by_any_id(...)	record	Fetches a proposal by any identifier
mark_proposal_viewed(proposal_id)	void	Marks a proposal as viewed by customer

Analytics Functions (called via supabase.rpc())

Function	Returns	Purpose
get_avg_group_size(merchant_id)	numeric	Average group size across merchant’s leads
get_budget_tiers(merchant_id)	text	Budget tier distribution for merchant
get_enum_values(enum_name)	array	Returns all values for a given enum type
get_lead_distribution(merchant_id)	record	Lead count by category
get_merchant_ai_usage(merchant_id)	record	AI proposal usage stats
get_most_popular_budget_tier(merchant_id)	text	Most common budget tier
get_most_popular_lead_category(merchant_id)	text	Most common event category
get_top_category_metrics(merchant_id)	record	Top performing category stats