Table Definitions¶

MySQL 8, InnoDB, utf8mb4. Diagrams: erd.md.

Conventions¶

Primary keys: id BIGINT UNSIGNED AUTO_INCREMENT. API-exposed tables also carry public_id CHAR(26) (ULID, unique) — the only identifier ever serialized in API responses.
Timestamps: every table has created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL (UTC) unless noted. Append-only tables (audit_logs) have created_at only.
Soft deletes: deleted_at DATETIME NULL on user-authored/content tables (users, vendors, services, packages, events, rfqs, reviews, cms_pages, messages). Financial and audit tables are never soft- or hard-deleted; they transition status instead.
Enums: stored as VARCHAR(32) with a CHECK-style application enum (PHP backed enums), not MySQL ENUM — adding states must not require ALTER TABLE. Allowed values are spelled out per column below and are lowercase snake_case.
Bilingual fields: paired columns name_en / name_ar (both required for published content; drafts may hold one). No JSON translation blobs in core tables — pairs are indexable and simple; a third language would be a Scale-phase migration to a translations side-table.
Money convention: DECIMAL(12,3) for all amounts + currency_code CHAR(3) FK → currencies. KWD has 3 decimal places (1 KWD = 1000 fils) — never round to 2 dp, never use FLOAT/DOUBLE. Formatting to a currency's decimal_places happens at the API/serialization edge only.
FKs: BIGINT UNSIGNED, ON DELETE RESTRICT by default (explicit application-level cascades); ON DELETE CASCADE only for pure child rows (quotation_items, rfq_vendors).
Phones: VARCHAR(20), E.164 (+965XXXXXXXX). Emails: VARCHAR(255), stored lowercase.

Column tables below omit id, created_at, updated_at (implied by the conventions). "PK/UX/IX" = primary/unique/secondary index.

Identity & Vendor¶

users¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
name	VARCHAR(100)	no	—	Display name
email	VARCHAR(255)	yes	NULL	UX (where not null); lowercase
email_verified_at	DATETIME	yes	NULL
phone	VARCHAR(20)	yes	NULL	UX; E.164
phone_verified_at	DATETIME	yes	NULL
password	VARCHAR(255)	yes	NULL	bcrypt; NULL for social-only accounts
user_type	VARCHAR(32)	no	`customer`	`customer` \| `vendor` \| `admin`
locale	CHAR(2)	no	`ar`	`ar` \| `en`
country_id	BIGINT UNSIGNED	no	—	FK countries; IX
avatar_media_id	BIGINT UNSIGNED	yes	NULL	FK media
provider	VARCHAR(16)	yes	NULL	`google` \| `apple` when social-linked
provider_sub	VARCHAR(191)	yes	NULL	UX(provider, provider_sub)
last_login_at	DATETIME	yes	NULL
deleted_at	DATETIME	yes	NULL	Soft delete (anonymized on erasure)

Constraint: at least one of email / phone non-null (application-enforced).

roles / role_user¶

roles: name VARCHAR(64) UX (customer, vendor_owner, vendor_member, admin), guard_name. role_user: role_id FK, user_id FK, UX(role_id, user_id). Managed by spatie/laravel-permission (adds permissions, permission_role for granular admin rights: vendors.approve, refunds.execute, cms.edit, …).

otp_challenges¶

Column	Type	Null	Default	Notes
phone	VARCHAR(20)	yes	NULL	IX; target phone or…
email	VARCHAR(255)	yes	NULL	…target email (one required)
code_hash	CHAR(64)	no	—	SHA-256 of 6-digit code
purpose	VARCHAR(32)	no	—	`login` \| `verify_phone` \| `verify_email` \| `password_reset`
attempts	TINYINT UNSIGNED	no	0	invalidate at 5
expires_at	DATETIME	no	—	now + 5 min; IX
consumed_at	DATETIME	yes	NULL	single-use

Purged by scheduler after 24 h. No updated_at needed beyond attempts tracking.

vendors¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
business_name_en	VARCHAR(150)	no	—
business_name_ar	VARCHAR(150)	no	—
slug	VARCHAR(160)	no	—	UX; SEO profile URL
description_en / description_ar	TEXT	yes	NULL
license_number	VARCHAR(50)	no	—	Kuwait commercial license no.; UX(country_id, license_number)
status	VARCHAR(32)	no	`pending_review`	`pending_review` \| `approved` \| `rejected` \| `suspended`
rejection_reason	VARCHAR(500)	yes	NULL
country_id	BIGINT UNSIGNED	no	—	FK; IX
city_id	BIGINT UNSIGNED	no	—	FK; IX
address_text	VARCHAR(300)	yes	NULL
lat / lng	DECIMAL(10,7)	yes	NULL	from Places geocoding
place_id	VARCHAR(191)	yes	NULL	Google place_id
contact_phone	VARCHAR(20)	no	—	public business phone
contact_email	VARCHAR(255)	yes	NULL
rating_avg	DECIMAL(3,2)	no	0.00	denormalized; IX
reviews_count	INT UNSIGNED	no	0	denormalized
approved_at	DATETIME	yes	NULL
deleted_at	DATETIME	yes	NULL

vendor_users¶

Column	Type	Null	Default	Notes
vendor_id	BIGINT UNSIGNED	no	—	FK; UX(vendor_id, user_id)
user_id	BIGINT UNSIGNED	no	—	FK; IX
role	VARCHAR(32)	no	`owner`	`owner` \| `member` (member granularity: Scale)

vendor_documents¶

Column	Type	Null	Default	Notes
vendor_id	BIGINT UNSIGNED	no	—	FK; IX
doc_type	VARCHAR(32)	no	—	`commercial_license` \| `civil_id_owner` \| `signage_license` \| `other`
media_id	BIGINT UNSIGNED	no	—	FK media (private disk)
status	VARCHAR(32)	no	`pending`	`pending` \| `approved` \| `rejected`
review_note	VARCHAR(500)	yes	NULL	admin note
reviewed_by	BIGINT UNSIGNED	yes	NULL	FK users (admin)
expires_at	DATE	yes	NULL	license expiry; scheduler warns 30 d before

plans¶

Column	Type	Null	Default	Notes
code	VARCHAR(32)	no	—	UX; `free` \| `basic` \| `professional` \| `enterprise`
name_en / name_ar	VARCHAR(100)	no	—
price_monthly	DECIMAL(12,3)	no	0.000
price_yearly	DECIMAL(12,3)	yes	NULL
currency_code	CHAR(3)	no	`KWD`	FK currencies
country_id	BIGINT UNSIGNED	no	—	FK; plans are per-country
quota_quotations_month	INT UNSIGNED	yes	NULL	NULL = unlimited
featured_listing	BOOLEAN	no	false
analytics_level	VARCHAR(32)	no	`basic`	`basic` \| `standard` \| `advanced`
team_members_limit	TINYINT UNSIGNED	no	1
ad_credits	INT UNSIGNED	no	0	Scale
priority_support	BOOLEAN	no	false
is_active	BOOLEAN	no	true	retired plans stay for FK integrity
sort_order	INT	no	0

subscriptions¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
vendor_id	BIGINT UNSIGNED	no	—	FK; IX(vendor_id, status)
plan_id	BIGINT UNSIGNED	no	—	FK
status	VARCHAR(32)	no	`active`	`active` \| `past_due` \| `cancelled` \| `expired`
billing_cycle	VARCHAR(16)	no	`monthly`	`monthly` \| `yearly`
current_period_start / current_period_end	DATE	no	—	IX(current_period_end) for renewal job
auto_renew	BOOLEAN	no	true
cancelled_at	DATETIME	yes	NULL
quota_used_month	INT UNSIGNED	no	0	quotations sent this period; reset at rollover

One active/past_due subscription per vendor (partial-unique enforced in application + UX(vendor_id) WHERE-equivalent via status checks).

invoices¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
number	VARCHAR(20)	no	—	UX; `YAM-2026-000123`, gap-free per year
subscription_id	BIGINT UNSIGNED	no	—	FK; IX
vendor_id	BIGINT UNSIGNED	no	—	FK; IX (denormalized for reporting)
period_start / period_end	DATE	no	—
subtotal / total_amount	DECIMAL(12,3)	no	—	tax fields added for KSA VAT (Scale)
currency_code	CHAR(3)	no	`KWD`	FK
status	VARCHAR(32)	no	`issued`	`issued` \| `paid` \| `void`
due_at / paid_at	DATETIME	yes	NULL

Localization & Marketplace¶

currencies¶

code CHAR(3) PK (KWD, later SAR, AED, BHD, QAR), name_en, name_ar, symbol VARCHAR(8), decimal_places TINYINT (KWD=3, SAR/AED=2, BHD=3), is_active BOOLEAN.

countries¶

Column	Type	Null	Default	Notes
iso2	CHAR(2)	no	—	UX; `KW`
name_en / name_ar	VARCHAR(100)	no	—
dial_code	VARCHAR(6)	no	—	`+965`
currency_code	CHAR(3)	no	—	FK currencies
timezone	VARCHAR(64)	no	—	`Asia/Kuwait`
is_active	BOOLEAN	no	false	only KW at launch

cities¶

country_id FK IX, name_en, name_ar (Kuwait seed: the 6 governorates as regions + major areas), lat/lng DECIMAL(10,7) NULL, is_active BOOLEAN, sort_order INT.

categories¶

Column	Type	Null	Default	Notes
parent_id	BIGINT UNSIGNED	yes	NULL	FK self; 2 levels max in MVP
slug	VARCHAR(120)	no	—	UX; `wedding-halls`
name_en / name_ar	VARCHAR(100)	no	—
icon	VARCHAR(64)	yes	NULL	icon key for clients
is_active	BOOLEAN	no	true
sort_order	INT	no	0	IX(parent_id, sort_order)

Seed roots: Venues, Catering & Hospitality, Photography & Video, Decoration & Lighting, Entertainment, Planning & Management, Transportation, Printing & Invitations, Security & Cleaning, Kids Entertainment.

services¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
vendor_id	BIGINT UNSIGNED	no	—	FK; IX(vendor_id, status)
category_id	BIGINT UNSIGNED	no	—	FK; IX
title_en / title_ar	VARCHAR(150)	no	—
description_en / description_ar	TEXT	yes	NULL
price_from	DECIMAL(12,3)	yes	NULL	"starting from"; NULL = quote-only
currency_code	CHAR(3)	no	`KWD`	FK
status	VARCHAR(32)	no	`draft`	`draft` \| `published` \| `archived`
deleted_at	DATETIME	yes	NULL

packages¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
service_id	BIGINT UNSIGNED	no	—	FK; IX
name_en / name_ar	VARCHAR(150)	no	—
description_en / description_ar	TEXT	yes	NULL
price	DECIMAL(12,3)	no	—
currency_code	CHAR(3)	no	`KWD`	FK
capacity_min / capacity_max	INT UNSIGNED	yes	NULL	guests
is_active	BOOLEAN	no	true
deleted_at	DATETIME	yes	NULL

media¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
mediable_type / mediable_id	VARCHAR(64) / BIGINT UNSIGNED	yes	NULL	polymorphic; IX(mediable_type, mediable_id); NULL until attached
uploaded_by	BIGINT UNSIGNED	no	—	FK users
collection	VARCHAR(32)	no	—	`gallery` \| `license` \| `attachment` \| `avatar` \| `review_photo`
disk	VARCHAR(16)	no	`public`	`public` \| `private`
path	VARCHAR(500)	no	—	object key
mime_type	VARCHAR(100)	no	—	sniffed, not declared
size_bytes	BIGINT UNSIGNED	no	—
width / height	INT UNSIGNED	yes	NULL	images
status	VARCHAR(32)	no	`pending`	`pending` \| `ready` \| `rejected`

favorites¶

user_id FK + vendor_id FK, UX(user_id, vendor_id). Timestamps only.

Event, RFQ, Booking & Financial¶

events¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
user_id	BIGINT UNSIGNED	no	—	FK; IX(user_id, status)
title	VARCHAR(150)	no	—	customer's own words; not bilingual
event_type	VARCHAR(32)	no	—	`wedding` \| `corporate` \| `birthday` \| `graduation` \| `conference` \| `exhibition` \| `social` \| `other`
event_date	DATE	no	—	IX; local intent date
event_time	TIME	yes	NULL
guests_expected	INT UNSIGNED	yes	NULL
budget_total	DECIMAL(12,3)	yes	NULL
currency_code	CHAR(3)	no	`KWD`	FK
city_id	BIGINT UNSIGNED	no	—	FK; IX
location_text	VARCHAR(300)	yes	NULL
notes	TEXT	yes	NULL
status	VARCHAR(32)	no	`planning`	`planning` \| `confirmed` \| `completed` \| `cancelled`
deleted_at	DATETIME	yes	NULL

rfqs¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
event_id	BIGINT UNSIGNED	no	—	FK; IX
category_id	BIGINT UNSIGNED	no	—	FK; IX
description	TEXT	no	—	30–2000 chars
budget_min / budget_max	DECIMAL(12,3)	yes	NULL	max ≥ min
currency_code	CHAR(3)	no	`KWD`	FK
status	VARCHAR(32)	no	`draft`	`draft` \| `pending` \| `quoted` \| `negotiating` \| `accepted` \| `rejected` \| `expired` \| `cancelled`
expires_at	DATETIME	yes	NULL	IX; expiry job
published_at	DATETIME	yes	NULL
deleted_at	DATETIME	yes	NULL	drafts only

rfq_vendors¶

Column	Type	Null	Default	Notes
rfq_id	BIGINT UNSIGNED	no	—	FK CASCADE; UX(rfq_id, vendor_id)
vendor_id	BIGINT UNSIGNED	no	—	FK; IX(vendor_id, status) — vendor inbox
status	VARCHAR(32)	no	`sent`	`sent` \| `viewed` \| `quoted` \| `declined` \| `expired`
viewed_at / responded_at	DATETIME	yes	NULL

Max 10 vendors per RFQ (application rule).

quotations¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
rfq_vendor_id	BIGINT UNSIGNED	no	—	FK; UX (one active quotation per rfq_vendor; revisions supersede)
revision	TINYINT UNSIGNED	no	1	negotiation bumps revision
total_amount	DECIMAL(12,3)	no	—	= SUM(items.line_total), app-enforced
currency_code	CHAR(3)	no	`KWD`	FK
notes	TEXT	yes	NULL
valid_until	DATE	no	—	IX; expiry job
delivery_time_days	INT UNSIGNED	yes	NULL
status	VARCHAR(32)	no	`submitted`	`submitted` \| `negotiating` \| `accepted` \| `rejected` \| `expired` \| `withdrawn`
accepted_at	DATETIME	yes	NULL

quotation_items¶

quotation_id FK CASCADE IX, description VARCHAR(300), quantity INT UNSIGNED (≥1), unit_price DECIMAL(12,3), line_total DECIMAL(12,3), sort_order INT. Max 50 items per quotation.

bookings¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
event_id	BIGINT UNSIGNED	no	—	FK; IX
vendor_id	BIGINT UNSIGNED	no	—	FK; IX(vendor_id, status)
quotation_id	BIGINT UNSIGNED	no	—	FK; UX — a quotation books once
total_amount	DECIMAL(12,3)	no	—	frozen from quotation at acceptance
deposit_amount	DECIMAL(12,3)	no	—	e.g., 20% default, vendor-configurable 10–100%
amount_paid	DECIMAL(12,3)	no	0.000	maintained by payment events
currency_code	CHAR(3)	no	`KWD`	FK
service_date	DATE	no	—	IX; from event
status	VARCHAR(32)	no	`pending`	`pending` \| `confirmed` \| `completed` \| `cancelled` \| `refund_requested`
cancellation_reason	VARCHAR(500)	yes	NULL
confirmed_at / completed_at / cancelled_at	DATETIME	yes	NULL

payments¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX; sent to PSP as merchant reference
payable_type / payable_id	VARCHAR(32) / BIGINT UNSIGNED	no	—	`booking` \| `invoice`; IX(payable_type, payable_id)
payer_user_id	BIGINT UNSIGNED	no	—	FK users; IX
amount	DECIMAL(12,3)	no	—	KWD 3 dp exact
currency_code	CHAR(3)	no	`KWD`	FK
method	VARCHAR(32)	yes	NULL	`knet` \| `card` \| `apple_pay` \| `google_pay`; set from PSP result
provider	VARCHAR(32)	no	—	`myfatoorah` \| `tap`
provider_ref	VARCHAR(100)	yes	NULL	UX; PSP transaction id
idempotency_key	CHAR(36)	no	—	UX; client-supplied
status	VARCHAR(32)	no	`pending`	`pending` \| `processing` \| `succeeded` \| `failed` \| `expired` \| `flagged`
failure_code	VARCHAR(64)	yes	NULL
paid_at	DATETIME	yes	NULL
raw_webhook	JSON	yes	NULL	last verified payload, PII-minimized

Never deleted. Status transitions only via webhook/reconciliation (see Integrations).

refunds¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
payment_id	BIGINT UNSIGNED	no	—	FK; IX
amount	DECIMAL(12,3)	no	—	≤ payment.amount − prior refunds
reason	VARCHAR(500)	no	—
requested_by	BIGINT UNSIGNED	no	—	FK users
approved_by	BIGINT UNSIGNED	yes	NULL	FK users (admin)
status	VARCHAR(32)	no	`requested`	`requested` \| `processing` \| `completed` \| `failed` \| `rejected`
provider_refund_ref	VARCHAR(100)	yes	NULL
processed_at	DATETIME	yes	NULL

Communication, Experience & Platform¶

conversations¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
customer_user_id	BIGINT UNSIGNED	no	—	FK; IX
vendor_id	BIGINT UNSIGNED	no	—	FK; IX; UX(customer_user_id, vendor_id, context_type, context_id)
context_type / context_id	VARCHAR(32) / BIGINT UNSIGNED	yes	NULL	`rfq` \| `booking` \| NULL (general)
last_message_at	DATETIME	yes	NULL	IX; inbox sort
customer_unread_count / vendor_unread_count	INT UNSIGNED	no	0	denormalized

messages¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
conversation_id	BIGINT UNSIGNED	no	—	FK; IX(conversation_id, id) — cursor pagination
sender_user_id	BIGINT UNSIGNED	no	—	FK
body_type	VARCHAR(16)	no	`text`	`text` \| `image` \| `pdf` \| `quotation_ref`
body	TEXT	yes	NULL	≤ 2000 chars; NULL for pure attachments
ref_id	BIGINT UNSIGNED	yes	NULL	quotation id for `quotation_ref`
read_at	DATETIME	yes	NULL
deleted_at	DATETIME	yes	NULL	sender-side delete (tombstone shown)

Attachments via media (polymorphic, collection attachment, private disk).

notifications¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
user_id	BIGINT UNSIGNED	no	—	FK; IX(user_id, read_at)
type	VARCHAR(64)	no	—	dotted keys: `quotation.received`, `booking.confirmed`, `payment.succeeded`, `subscription.expiring`, `rfq.received`, `message.received`, `review.received`
data	JSON	no	—	`{entity_type, entity_public_id, params}` — clients localize from `type` + `params`
read_at	DATETIME	yes	NULL

notification_devices¶

user_id FK IX, token VARCHAR(255) UX, platform VARCHAR(16) (ios | android | web), locale CHAR(2), last_seen_at DATETIME. Pruned on FCM UNREGISTERED.

reviews¶

Column	Type	Null	Default	Notes
public_id	CHAR(26)	no	—	UX
booking_id	BIGINT UNSIGNED	no	—	FK; UX — exactly one review per booking
user_id	BIGINT UNSIGNED	no	—	FK; IX
vendor_id	BIGINT UNSIGNED	no	—	FK; IX(vendor_id, status)
rating	TINYINT UNSIGNED	no	—	1–5
comment	TEXT	yes	NULL	≤ 1000 chars
vendor_reply	TEXT	yes	NULL	one reply, ≤ 500 chars
replied_at	DATETIME	yes	NULL
status	VARCHAR(32)	no	`published`	`published` \| `hidden` \| `flagged`
deleted_at	DATETIME	yes	NULL

cms_pages¶

slug VARCHAR(120) UX (about, terms, privacy, faq, vendor-guide), title_en/ar VARCHAR(200), body_en/ar MEDIUMTEXT (Markdown), meta_description_en/ar VARCHAR(300), status (draft | published), published_at, updated_by FK users, deleted_at.

audit_logs¶

Column	Type	Null	Default	Notes
actor_user_id	BIGINT UNSIGNED	yes	NULL	FK users; NULL = system/scheduler; IX
actor_role	VARCHAR(32)	yes	NULL	snapshot at time of action
action	VARCHAR(64)	no	—	`vendor.approved`, `payment.succeeded`, `booking.status_changed`, … IX
entity_type / entity_id	VARCHAR(64) / BIGINT UNSIGNED	no	—	IX(entity_type, entity_id)
old_values / new_values	JSON	yes	NULL	PII-minimized diffs
ip_address	VARCHAR(45)	yes	NULL	IPv4/IPv6
user_agent	VARCHAR(300)	yes	NULL
created_at	DATETIME	no	—	IX; append-only — no updated_at, no deletes

Partitioned by month at Scale; retention per Security.

Index strategy summary¶

Every FK is indexed (InnoDB requires it; we add composites where the access path is known: IX(vendor_id, status) on services/bookings/rfq_vendors for vendor dashboards, IX(user_id, read_at) on notifications for unread badges).
public_id unique indexes serve all API lookups; internal joins stay on id.
Expiry jobs scan IX(expires_at) / IX(valid_until) / IX(current_period_end) with status filters — all covered above.
No full-text indexes: search is Meilisearch's job (Integrations).