> ## Documentation Index
> Fetch the complete documentation index at: https://docs.grouple.pro/llms.txt
> Use this file to discover all available pages before exploring further.

# 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
```