Database Schema

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

​Entity Relationship Overview

​Core Tables

​customers

​merchants

​venues

​enquiries

​venue_leads

​proposals

​commission_settlements

​beos

​venue_knowledge

​Support Tables

​Enums