Database Triggers

All 14 PostgreSQL triggers that automate business logic in Grouple

Overview

Grouple uses 14 database triggers to automate critical business logic — ensuring emails are sent, currencies are synced, timestamps are recorded, and settlements are created automatically without manual API calls.

Triggers by Table

enquiries Table

Trigger	Event	Timing	Action
tr_sync_enquiry_currency	INSERT	BEFORE	Calls sync_enquiry_currency() — sets currency_code from the city’s default currency
on_enquiry_created_broadcast	INSERT	AFTER	Calls broadcast_enquiry_to_venues() — matches and creates venue_leads for relevant venues
send_enquiry_notification	INSERT	AFTER	Calls send-lead-email Edge Function via HTTP — sends lead notification emails to matched venues

venue_leads Table

Trigger	Event	Timing	Action
before_insert_venue_leads_set_rfq_number	INSERT	BEFORE	Calls set_merchant_rfq_number() — assigns a sequential RFQ number per merchant
send_email_on_new_lead	INSERT	AFTER	Calls grouple_notifications.trigger_lead_email() — additional lead email notification
trg_set_venue_lead_timestamps	UPDATE	BEFORE	Calls fn_set_venue_lead_timestamps() — records accepted_at, declined_at, closed_won_at, closed_lost_at based on status change
trg_record_response_time	UPDATE	AFTER	Calls fn_record_response_time() — logs response time to lead_response_times when merchant first responds

proposals Table

Trigger	Event	Timing	Action
trigger_sync_settlement	UPDATE	AFTER	Calls sync_confirmed_proposal_to_settlements() — creates/updates commission_settlements when proposal is confirmed
proposal-updated-email	UPDATE	AFTER	Calls send-proposal-email Edge Function via HTTP — emails customer when proposal is updated/sent
proposal-confirmation-email	UPDATE	AFTER	Calls reservation-confirmation-emails Edge Function via HTTP — sends confirmation to both parties when proposal is confirmed

customers Table

Trigger	Event	Timing	Action
on_customer_created_link_enquiries	INSERT	AFTER	Calls link_customer_to_enquiries() — links pre-existing enquiries (submitted before signup) to the new customer record

merchants Table

Trigger	Event	Timing	Action
trigger_sync_venue_currency	UPDATE	AFTER	Calls sync_venue_currency_from_merchant() — syncs currency_code to all venues when merchant updates their currency

venues Table

Trigger	Event	Timing	Action
tr_check_venue_limit	INSERT	BEFORE	Calls check_venue_limit() — prevents venue creation if merchant has reached their plan’s max_venues limit

venue_knowledge Table

Trigger	Event	Timing	Action
tr_check_menu_pdf_limit	INSERT	BEFORE	Calls check_menu_pdf_limit() — prevents PDF upload if merchant has reached their plan’s max_menu_pdfs limit

Key Trigger Flows

New Enquiry → Leads Created → Emails Sent

Customer submits enquiry
        ↓
BEFORE INSERT: tr_sync_enquiry_currency → sets currency from city
        ↓
AFTER INSERT: on_enquiry_created_broadcast → fn_match_venues_for_enquiry()
  Creates venue_leads records for matched venues
        ↓
AFTER INSERT on venue_leads: before_insert_venue_leads_set_rfq_number
  Sets merchant_rfq_number
        ↓
AFTER INSERT on venue_leads: send_email_on_new_lead
  Sends lead email to merchant
        ↓
AFTER INSERT on enquiries: send_enquiry_notification
  Also calls send-lead-email Edge Function (belt-and-suspenders)

Proposal Confirmed → Settlement Created → Emails Sent

Customer confirms proposal → proposal.status = "confirmed"
        ↓
AFTER UPDATE: trigger_sync_settlement
  Creates commission_settlements record with applied_commission_rate
        ↓
AFTER UPDATE: proposal-confirmation-email
  Calls reservation-confirmation-emails Edge Function
  Sends confirmation to customer + merchant