RPC Functions - 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