Database Triggers

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

​Overview

​Triggers by Table

​enquiries Table

​venue_leads Table

​proposals Table

​customers Table

​merchants Table

​venues Table

​venue_knowledge Table

​Key Trigger Flows

​New Enquiry → Leads Created → Emails Sent

​Proposal Confirmed → Settlement Created → Emails Sent