Entity-Relationship Diagrams¶
The core schema is ~32 tables, split here into four digestible areas. Full column-level definitions live in tables.md.
How to read these diagrams¶
- Every table has an internal
id(BIGINT UNSIGNED, auto-increment) and, where API-exposed, apublic_id(ULID, CHAR(26), unique) — diagrams show only distinguishing columns. ||--o{= one-to-many;}o--o{via an explicit join table; dashed labels name the relationship.- Bilingual text = paired
*_en/*_arcolumns. Money =DECIMAL(12,3)+currency_code(KWD, 3 dp). Conventions in tables.md. - Cross-area FKs are repeated in each diagram where they matter (e.g.,
usersappears in several areas as a reference node).
Area 1 — Identity, Vendor & Subscriptions¶
Who exists on the platform and what commercial standing they have. A vendor is the business entity; vendor_users links staff accounts to it. Subscriptions attach a plan to a vendor; each billing cycle issues an invoice, paid via payments (Area 3).
erDiagram
users ||--o{ otp_challenges : "requests"
users }o--o{ roles : "role_user"
users ||--o{ vendor_users : "is member via"
vendors ||--o{ vendor_users : "has team"
vendors ||--o{ vendor_documents : "uploads"
vendors }o--|| cities : "based in"
vendors }o--|| countries : "operates in"
plans ||--o{ subscriptions : "priced by"
vendors ||--o{ subscriptions : "subscribes"
subscriptions ||--o{ invoices : "bills"
users {
bigint id PK
char(26) public_id UK
varchar name
varchar email UK "nullable"
varchar phone UK "E.164, nullable"
enum user_type "customer|vendor|admin"
enum locale "ar|en"
datetime deleted_at "soft delete"
}
roles {
bigint id PK
varchar name UK "customer|vendor_owner|vendor_member|admin"
}
otp_challenges {
bigint id PK
varchar phone
char(64) code_hash
enum purpose "login|verify_phone|reset"
datetime expires_at
}
vendors {
bigint id PK
char(26) public_id UK
varchar business_name_en
varchar business_name_ar
varchar license_number
enum status "pending_review|approved|rejected|suspended"
bigint city_id FK
bigint country_id FK
decimal rating_avg "3,2 denormalized"
int reviews_count "denormalized"
}
vendor_users {
bigint id PK
bigint vendor_id FK
bigint user_id FK
enum role "owner|member"
}
vendor_documents {
bigint id PK
bigint vendor_id FK
enum doc_type "commercial_license|civil_id_owner|other"
varchar file_path "private bucket"
enum status "pending|approved|rejected"
date expires_at "license expiry"
}
plans {
bigint id PK
varchar code UK "free|basic|professional|enterprise"
decimal price_monthly "12,3"
char(3) currency_code
int quota_quotations_month
boolean featured_listing
}
subscriptions {
bigint id PK
char(26) public_id UK
bigint vendor_id FK
bigint plan_id FK
enum status "active|past_due|cancelled|expired"
date current_period_end
boolean auto_renew
}
invoices {
bigint id PK
char(26) public_id UK
bigint subscription_id FK
varchar number UK "YAM-2026-000123"
decimal total_amount "12,3"
enum status "issued|paid|void"
}Area 2 — Marketplace, Listings & Localization¶
The catalog customers browse. categories are a self-referencing tree (venues → wedding halls). Vendors publish services and bundle them into packages. media is a polymorphic attachment table used platform-wide. countries / cities / currencies are Platform reference data.
erDiagram
countries ||--o{ cities : "contains"
countries }o--|| currencies : "default currency"
categories ||--o{ categories : "parent of"
categories ||--o{ services : "classifies"
vendors ||--o{ services : "offers"
services ||--o{ packages : "bundled as"
vendors ||--o{ media : "gallery (polymorphic)"
services ||--o{ media : "images (polymorphic)"
packages ||--o{ media : "images (polymorphic)"
users ||--o{ favorites : "saves"
vendors ||--o{ favorites : "saved as"
countries {
bigint id PK
char(2) iso2 UK "KW"
varchar name_en
varchar name_ar
char(3) currency_code FK
boolean is_active
}
cities {
bigint id PK
bigint country_id FK
varchar name_en
varchar name_ar
}
currencies {
char(3) code PK "KWD"
varchar name_en
varchar name_ar
tinyint decimal_places "KWD=3"
}
categories {
bigint id PK
bigint parent_id FK "nullable"
varchar slug UK
varchar name_en
varchar name_ar
int sort_order
boolean is_active
}
services {
bigint id PK
char(26) public_id UK
bigint vendor_id FK
bigint category_id FK
varchar title_en
varchar title_ar
decimal price_from "12,3 nullable"
enum status "draft|published|archived"
}
packages {
bigint id PK
char(26) public_id UK
bigint service_id FK
varchar name_en
varchar name_ar
decimal price "12,3"
int capacity_min
int capacity_max
}
media {
bigint id PK
char(26) public_id UK
varchar mediable_type "polymorphic"
bigint mediable_id
enum collection "gallery|license|attachment|avatar"
varchar disk "public|private"
enum status "pending|ready|rejected"
}
favorites {
bigint id PK
bigint user_id FK
bigint vendor_id FK
}Area 3 — Event, RFQ, Booking & Financial¶
The money path. An event is the customer's anchor entity. An rfq belongs to an event and fans out to vendors through rfq_vendors; vendors answer with quotations (line items in quotation_items). Accepting a quotation creates a booking; payments settle bookings and invoices; refunds reverse payments.
erDiagram
users ||--o{ events : "plans"
events }o--|| cities : "located in"
events ||--o{ rfqs : "requests quotes via"
rfqs ||--o{ rfq_vendors : "targets"
vendors ||--o{ rfq_vendors : "receives"
rfq_vendors ||--o| quotations : "answered by"
quotations ||--o{ quotation_items : "itemized as"
quotations ||--o| bookings : "accepted into"
events ||--o{ bookings : "collects"
vendors ||--o{ bookings : "fulfills"
bookings ||--o{ payments : "paid via"
invoices ||--o{ payments : "settled by"
payments ||--o{ refunds : "reversed by"
events {
bigint id PK
char(26) public_id UK
bigint user_id FK
varchar title
enum event_type "wedding|corporate|birthday|graduation|conference|social|other"
date event_date
int guests_expected
decimal budget_total "12,3 nullable"
bigint city_id FK
enum status "planning|confirmed|completed|cancelled"
}
rfqs {
bigint id PK
char(26) public_id UK
bigint event_id FK
bigint category_id FK
text description
decimal budget_min "12,3"
decimal budget_max "12,3"
enum status "draft|pending|quoted|negotiating|accepted|rejected|expired|cancelled"
datetime expires_at
}
rfq_vendors {
bigint id PK
bigint rfq_id FK
bigint vendor_id FK
enum status "sent|viewed|quoted|declined|expired"
datetime viewed_at
}
quotations {
bigint id PK
char(26) public_id UK
bigint rfq_vendor_id FK
decimal total_amount "12,3"
char(3) currency_code FK
enum status "submitted|negotiating|accepted|rejected|expired|withdrawn"
date valid_until
}
quotation_items {
bigint id PK
bigint quotation_id FK
varchar description
int quantity
decimal unit_price "12,3"
decimal line_total "12,3"
}
bookings {
bigint id PK
char(26) public_id UK
bigint event_id FK
bigint vendor_id FK
bigint quotation_id FK
decimal total_amount "12,3"
decimal deposit_amount "12,3"
enum status "pending|confirmed|completed|cancelled|refund_requested"
date service_date
}
payments {
bigint id PK
char(26) public_id UK
enum payable_type "booking|invoice"
bigint payable_id
decimal amount "12,3"
char(3) currency_code FK
enum method "knet|card|apple_pay|google_pay"
enum status "pending|processing|succeeded|failed|expired|flagged"
varchar provider_ref UK
char(26) idempotency_key UK
}
refunds {
bigint id PK
char(26) public_id UK
bigint payment_id FK
decimal amount "12,3"
enum status "requested|processing|completed|failed|rejected"
varchar provider_refund_ref
}Area 4 — Communication, Experience & Platform¶
Conversations bind a customer and a vendor, usually in the context of an RFQ or booking. notifications is the in-app inbox (fan-out to push/email is job-side, not schema-side). reviews require a completed booking. cms_pages and audit_logs serve Operations and Platform.
erDiagram
users ||--o{ conversations : "customer side"
vendors ||--o{ conversations : "vendor side"
conversations ||--o{ messages : "contains"
users ||--o{ messages : "sends"
messages ||--o{ media : "attachments (polymorphic)"
users ||--o{ notifications : "receives"
bookings ||--o| reviews : "reviewed once"
users ||--o{ reviews : "writes"
vendors ||--o{ reviews : "rated by"
users ||--o{ audit_logs : "acts (actor)"
conversations {
bigint id PK
char(26) public_id UK
bigint customer_user_id FK
bigint vendor_id FK
varchar context_type "rfq|booking|null"
bigint context_id "nullable"
datetime last_message_at
}
messages {
bigint id PK
char(26) public_id UK
bigint conversation_id FK
bigint sender_user_id FK
enum body_type "text|image|pdf|quotation_ref"
text body
datetime read_at
}
notifications {
bigint id PK
char(26) public_id UK
bigint user_id FK
varchar type "quotation.received, booking.confirmed, …"
json data "entity refs, localized keys"
datetime read_at
}
reviews {
bigint id PK
char(26) public_id UK
bigint booking_id FK "unique"
bigint user_id FK
bigint vendor_id FK
tinyint rating "1..5"
text comment
text vendor_reply
enum status "published|hidden|flagged"
}
cms_pages {
bigint id PK
varchar slug UK "about, terms, privacy"
varchar title_en
varchar title_ar
mediumtext body_en
mediumtext body_ar
enum status "draft|published"
}
audit_logs {
bigint id PK
bigint actor_user_id FK "nullable (system)"
varchar action "vendor.approved, payment.succeeded"
varchar entity_type
bigint entity_id
json old_values
json new_values
varchar ip_address
datetime created_at "append-only"
}Cross-area relationship notes¶
- Everything routes through Event on the customer side:
rfqs.event_id,bookings.event_id; conversations reference RFQs/bookings viacontext_type/context_id, so an event's full communication trail is derivable. paymentsis polymorphic (payable_type=booking|invoice): one payments/refunds engine serves both booking deposits (customer money) and subscription invoices (vendor money) — the "shared engines" rule.mediais polymorphic across vendors, services, packages, messages, reviews, and vendor_documents' files — one upload/verification pipeline platform-wide.- Denormalized aggregates (
vendors.rating_avg,reviews_count,conversations.last_message_at) are recomputed by domain-event listeners, never trusted as source of truth. - Scale-phase tables (not diagrammed):
event_budgets,event_checklists,event_guests,promo_codes,wallets,vendor_calendars.
Full column definitions, enums, and index choices: tables.md. API surface over these entities: endpoints.md.