Database Schema

Full PostgreSQL schema for Grouple — all tables, relationships, and enums

Entity Relationship Overview

countries → cities → neighbourhoods
                 ↓
merchants (subscription_plans) → venues → venue_amenities (amenities)
                                       → venue_cuisines (cuisines)
                                       → venue_knowledge
                                       → venue_leads ← enquiries ← customers
                                                    ↓
                                                proposals → beos
                                                         → commission_settlements

Core Tables

customers

Registered end-customers (event planners).

Column	Type	Notes
id	uuid	Primary key
supabase_user_id	uuid	Links to Supabase Auth
first_name, last_name	text
email	text	NOT NULL
phone	text
company_name	text
created_at	timestamp

merchants

Venue owners / managers.

Column	Type	Notes
id	uuid	Primary key
supabase_user_id	uuid	Links to Supabase Auth
first_name, last_name	text
email	text	NOT NULL
role	text
company_name	text
logo_url	text
onboarding_completed	boolean
plan_id	text	FK → subscription_plans
currency_code	text

venues

Individual venues listed by merchants.

Column	Type	Notes
id	uuid	Primary key
merchant_id	uuid	FK → merchants
venue_name	text	NOT NULL
address, city_id, neighbourhood_id		Location
seated_capacity, standing_capacity	integer
price_min, price_max	numeric
operating_hours	jsonb
venue_type_id	uuid	FK → venue_types
currency_code	text
is_active	boolean
public_token	text	For public-facing links
is_featured	boolean

enquiries

Customer group booking requests (RFQs).

Column	Type	Notes
id	uuid	Primary key
rfq_number	integer	Auto-incremented sequence
event_date	date	NOT NULL
time_slot	time	NOT NULL
group_size	integer	NOT NULL
budget_tier	enum	Economy / Standard / Premium / Luxury
city_id, neighbourhood_id	uuid
event_category_id	uuid	FK → event_categories
cuisine_preference_id	uuid	FK → cuisines
venue_type_id	uuid	FK → venue_types
customer_id	uuid	FK → customers
currency_code	text	Auto-synced from city
status	text	open / confirmed
confirmed_proposal_id	uuid	Set on confirmation
confirmed_venue_id	uuid	Set on confirmation

venue_leads

Junction between an enquiry and a specific venue — one lead per venue per enquiry.

Column	Type	Notes
id	uuid	Primary key
enquiry_id	uuid	FK → enquiries
venue_id	uuid	FK → venues
merchant_id	uuid	FK → merchants
status	enum	pending / accepted / declined / connected / closed_won / closed_lost
merchant_rfq_number	integer	Auto-set by trigger
proposed_event_date	date	Merchant can counter-propose
proposed_group_size	integer
is_counter_offer	boolean
accepted_at, declined_at, closed_won_at, closed_lost_at	timestamp
is_read	boolean

proposals

Proposals sent by merchants in response to leads.

Column	Type	Notes
id	uuid	Primary key
venue_lead_id	uuid	FK → venue_leads
venue_id	uuid	FK → venues
title	text
status	enum	draft / sent / viewed / confirmed / declined / expired
offer_valid_until	date
details	text	AI-generated or manual
package_inclusions	text[]
amount, service_fee, discount, total	numeric
price_type	enum	Per Person / Per Bottle / Per Item / Per Event
payment_terms, venue_policies	text
ai_generated	boolean
public_token	text	For public-facing proposal links
signed_by_name, signed_at, signature_base64		Digital signature
selected_packages	jsonb
currency_code	text

commission_settlements

Auto-created when a proposal is confirmed.

Column	Type	Notes
proposal_id	uuid	FK → proposals
merchant_id	uuid	FK → merchants
applied_commission_rate	numeric	From subscription plan
status	enum	unpaid / pending_verification / paid / cancelled
is_paid	boolean
receipt_url, invoice_url	text

beos

Banquet Event Orders linked to confirmed proposals.

Column	Type	Notes
proposal_id	uuid	FK → proposals
setup_time	text
table_setup	text
staffing_requirements	text
av_requirements	text
sales_contact, venue_mobile	text
miscellaneous_notes	text

venue_knowledge

PDF menus and brochures uploaded by merchants for AI extraction.

Column	Type	Notes
venue_id	uuid	FK → venues
merchant_id	uuid	FK → merchants
file_path	text	NOT NULL — path in Supabase Storage
extracted_data	jsonb	AI-extracted content
status	text

Support Tables

Table	Purpose
cities	City list with default currency
countries	Country reference data
neighbourhoods	Neighbourhood lookup (FK → cities)
amenities	Amenity types (e.g. “Private Room”, “AV Equipment”)
cuisines	Cuisine types
event_categories	Event types
venue_types	Venue type categories
subscription_plans	Merchant plan definitions
exchange_rates	Currency exchange rates (updated by Edge Function)
lead_response_times	Tracks how quickly merchants respond to leads
lead_values	Estimated booking value per lead
merchant_lead_counter	Tracks last RFQ number per merchant
merchant_analytics_v3	Materialised view for analytics dashboard
merchant_commission_ledger	View for commission tracking

Enums

Enum	Values
budget_tier	Economy, Standard, Premium, Luxury
lead_status	pending, accepted, declined, connected, closed_won, closed_lost
proposal_status	draft, sent, viewed, confirmed, declined, expired
commission_status	unpaid, pending_verification, paid, cancelled
price_type	Per Person, Per Bottle, Per Item, Per Event
currency_code_type	USD, AED, GBP, INR, EUR
occasion_type	Corporate, Birthday Party, Conferences/Seminars, Gala Dinners, and 13 more