# CarsBH — Database Design

**Version:** 1.0  
**Stack:** Laravel 12 · MySQL 8+ (or PostgreSQL)  
**Audience:** Backend developers, DBAs, QA  
**References:** [CarsBH FSD v1.1](/Users/sigosoftpvtltd/Downloads/CarsBH_FSD_v1.1.docx), [Admin design](https://ourworks.co.in/carshbh-admin-design), [Mobile UI (Figma)](https://www.figma.com/design/rFWzWDeeySmmmNiCokN98J/CarsBh1?node-id=513-1448), [Norbiz Lotto DB design](/Users/sigosoft/norbiz/norbiz-lotto-backend/docs/DATABASE_DESIGN.md)  
**Architecture reference:** `norbiz-lotto-backend`, `saimpex-backend`, `airotrack-backend`

---

## 1. Design Principles

| Principle | Description |
|-----------|-------------|
| **Single user store** | All credentials and identity live in `users`. Role-specific data extends into profile tables (`admin_profiles`). Customers buy and sell on the same account — no separate seller/vendor role. |
| **Integer flags** | Use `$table->integer()` with `->comment()` for status/enum columns. Do **not** use `tinyInteger()`. |
| **Audit columns** | All **main mutable tables** include `added_by`, `updated_by` (FK → `users.id`), and `deleted_at` (soft delete). See [§1.1](#11-standard-audit-columns). |
| **Soft deletes** | Use Laravel `SoftDeletes` on every main table; never hard-delete user-facing or business data. |
| **Wallet ledger** | Wallet balance changes always paired with immutable `wallet_transactions` rows (`balance_before`, `balance_after`). |
| **Snapshot on purchase** | Package limits and prices copied onto `user_subscriptions` at purchase time. Admin price changes never retroactively affect active subscriptions. |
| **Dynamic attributes** | Listing fields (Brand, Model, Year, etc.) are category-driven via `attributes` / `attribute_options` — no schema change when adding categories. |
| **Bilingual content** | User-facing CMS and notifications support `en` and `ar`. Admin panel is English-only in v1.0. |
| **Server-side truth** | Listing status, package expiry, wallet balances, and moderation state are always computed on the server. |

### Role Model

| `role_id` | Role | Primary clients |
|-----------|------|-----------------|
| `1` | Admin | Vue.js admin panel |
| `2` | Customer | Flutter mobile app |

**Notes:**
- There are **only two roles**. Customers (`role_id = 2`) can browse, inquire, chat, wishlist, **and** create/manage listings on the same account — they are both buyers and sellers; no separate vendor role exists.
- Admin sub-roles (Super Admin, Moderator, Finance) are managed via `admin_profiles.admin_role` + `admin_module_privileges`, not separate `role_id` values.
- Seed only Admin and Customer in `roles`.

### 1.1 Standard audit columns

Every **main table** that is created, updated, or soft-deleted during normal application use must include these columns (in addition to `created_at` / `updated_at`):

| Column | Type | Notes |
|--------|------|-------|
| `added_by` | BIGINT FK NULL → `users.id` | User who created the row (admin or customer). NULL for system/seed rows. |
| `updated_by` | BIGINT FK NULL → `users.id` | User who last modified the row. |
| `deleted_at` | TIMESTAMP NULL | Soft delete (Laravel `SoftDeletes`). |

**Laravel migration pattern:**

```php
$table->foreignId('added_by')->nullable()->constrained('users')->nullOnDelete();
$table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
$table->softDeletes();
$table->timestamps();
```

**Application pattern:** Use `AuditAttributes` helper (or model events) to set `added_by` / `updated_by` from `auth('sanctum')->id()` on create/update.

#### Tables with full audit columns (`added_by`, `updated_by`, `deleted_at`)

| Table | Notes |
|-------|-------|
| `users` | |
| `admin_profiles` | |
| `modules` | |
| `admin_module_privileges` | |
| `roles` | Admin-managed lookup |
| `country_codes` | Admin-managed lookup |
| `governorates` | Admin-managed lookup |
| `category_types` | |
| `categories` | |
| `sub_categories` | |
| `attributes` | |
| `attribute_options` | |
| `listings` | |
| `listing_attribute_values` | |
| `listing_media` | |
| `listing_drafts` | |
| `listing_reports` | |
| `packages` | |
| `user_subscriptions` | |
| `customer_wallets` | |
| `refund_requests` | |
| `conversations` | |
| `reviews` | |
| `user_reports` | |
| `wishlists` | Soft-remove from favourites |
| `notifications` | |
| `notification_campaigns` | |
| `banners` | |
| `sliders` | |
| `cms_pages` | |
| `faqs` | |
| `announcements` | |
| `support_tickets` | |
| `social_accounts` | |
| `ip_blocklist` | |
| `settings` | `deleted_at` when a setting row is retired |

#### Exceptions (append-only / immutable — no `updated_by` or `deleted_at`)

| Table | Audit rule |
|-------|------------|
| `otp_verifications` | Transient; purged by scheduler. `created_at` only. |
| `login_logs` | Append-only audit. `created_at` only. |
| `audit_logs` | Append-only audit. `created_at` only. |
| `listing_views` | Analytics append-only. `created_at` / `viewed_at` only. |
| `wallet_transactions` | **Immutable ledger.** `added_by` only; never update or soft-delete. |
| `payment_transactions` | `added_by`, `updated_by` when gateway status changes; no `deleted_at`. |
| `payment_gateway_logs` | Append-only debug log. `created_at` only. |
| `messages` | Chat append-only. `sender_id` = author; no soft delete in v1.0. |
| `support_ticket_replies` | Thread append-only. `sender_id` = author. |
| `refresh_tokens` | `revoked_at` instead of `deleted_at`. |

---

## 2. Entity Relationship Overview

```
roles ─────────────┐
country_codes ─────┤
governorates ──────┤
                   ├── users ──┬── admin_profiles
                   │           ├── customer_wallets ── wallet_transactions
                   │           ├── social_accounts
                   │           └── admin_module_privileges
                   │
category_types ─── categories ─── sub_categories
                   │
                   └── attributes ─── attribute_options
                              │
listings ──┬── listing_media
           ├── listing_attribute_values
           ├── listing_reports
           └── listing_views (analytics)

packages ─── user_subscriptions ─── payment_transactions
                    │
conversations ─── messages
wishlists
reviews
user_reports
notifications
support_tickets ─── support_ticket_replies
cms_pages / faqs / banners / sliders / announcements
otp_verifications
audit_logs / login_logs / payment_gateway_logs
settings / ip_blocklist
```

---

## 3. Core Identity & Access

### 3.1 `roles`

Lookup table for platform roles.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | `1` Admin, `2` Customer |
| `name_en` | VARCHAR(100) | |
| `name_ar` | VARCHAR(100) | Arabic label |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Seed data:**

| id | name_en | name_ar |
|----|---------|---------|
| 1 | Admin | مسؤول |
| 2 | Customer | عميل |

---

### 3.2 `country_codes`

Phone dial codes for OTP login. Bahrain (+973) is the default.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `country_name` | VARCHAR(100) | e.g. `Bahrain` |
| `dial_code` | VARCHAR(10) | e.g. `+973` |
| `iso_code` | CHAR(2) NULL | e.g. `BH` |
| `mobile_length` | VARCHAR(20) NULL | Validation hint, e.g. `8` |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Blocked` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 3.3 `users` *(primary identity table)*

Central table for all user types with credentials.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `role_id` | BIGINT FK → `roles` | **Required.** `1` admin, `2` customer |
| `name` | VARCHAR(150) NULL | Full name |
| `email` | VARCHAR(150) NULL | Unique per role where set; admin login |
| `username` | VARCHAR(100) NULL UNIQUE | Admin login (alternative to email) |
| `country_code_id` | BIGINT FK NULL → `country_codes` | |
| `mobile` | VARCHAR(20) NULL | Customer phone; E.164 without `+` |
| `password` | VARCHAR(255) NULL | Bcrypt cost ≥ 12; hashed |
| `image` | VARCHAR(255) NULL | Profile photo CDN URL |
| `preferred_locale` | CHAR(5) DEFAULT `en` | `en`, `ar` |
| `gender` | INTEGER NULL | `1-male, 2-female, 3-other` |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Suspended, 3-Banned, 4-Frozen` |
| `ban_reason` | TEXT NULL | Required when status = 3 |
| `suspended_reason` | TEXT NULL | Required when status = 2 |
| `phone_verified` | INTEGER DEFAULT 0 | `0-not_verified, 1-verified` |
| `email_verified` | INTEGER DEFAULT 0 | `0-not_verified, 1-verified` |
| `is_guest` | INTEGER DEFAULT 0 | `0-registered, 1-guest` (guest browse only) |
| `average_rating` | DECIMAL(3,2) DEFAULT 0.00 | Denormalized seller rating |
| `total_reviews` | INTEGER DEFAULT 0 | Denormalized review count |
| `total_active_listings` | INTEGER DEFAULT 0 | Denormalized for profile/dashboard |
| `fcm_token` | TEXT NULL | Push notification token |
| `notification_enabled` | INTEGER DEFAULT 1 | `0-disabled, 1-enabled` |
| `otp_fail_count` | INTEGER DEFAULT 0 | Consecutive OTP failures |
| `otp_locked_until` | TIMESTAMP NULL | 15-min lock after 3 failures |
| `last_login_at` | TIMESTAMP NULL | |
| `last_login_ip` | VARCHAR(45) NULL | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `remember_token` | VARCHAR(100) NULL | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Indexes:** `(role_id, status)`, `(country_code_id, mobile, role_id)` UNIQUE, `email`, `username`.

**Auth notes:**
- **Admin:** `email` or `username` + `password` + TOTP (see `admin_profiles`).
- **Customer:** Phone OTP (primary), email/phone + password, Apple Sign-In, Google Sign-In. Same account for buying and selling.

---

### 3.4 `admin_profiles`

Admin-specific extension for `role_id = 1`.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK UNIQUE → `users` | |
| `admin_role` | INTEGER DEFAULT 1 | `1-Super Admin, 2-Moderator, 3-Finance` |
| `two_factor_secret` | TEXT NULL | Google Authenticator TOTP secret (encrypted) |
| `two_factor_enabled` | INTEGER DEFAULT 1 | `0-off, 1-on` (mandatory in v1.0) |
| `two_factor_confirmed_at` | TIMESTAMP NULL | |
| `failed_login_count` | INTEGER DEFAULT 0 | |
| `locked_until` | TIMESTAMP NULL | 30-min lock after 5 failed logins |
| `session_expires_at` | TIMESTAMP NULL | 4-hour inactivity timeout |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Admin role permissions (enforced in middleware/policies):**

| Module | Super Admin | Moderator | Finance |
|--------|-------------|-----------|---------|
| Dashboard | Full | Full | Finance KPIs only |
| Users: View | ✓ | ✓ | ✓ |
| Users: Suspend/Ban/Edit | ✓ | ✓ | ✗ |
| Users: Wallet Adjustment | ✓ | ✗ | ✓ |
| Listings: All Actions | ✓ | ✓ | ✗ |
| Categories & Attributes | ✓ | ✗ | ✗ |
| Packages | ✓ | ✗ | ✓ (analytics) |
| Transactions | ✓ | ✗ | ✓ |
| Chat Moderation | ✓ | ✓ | ✗ |
| CMS | ✓ | ✗ | ✗ |
| Reports | ✓ | Read only | ✓ |
| System Settings | ✓ | ✗ | ✗ |
| Admin User Management | ✓ | ✗ | ✗ |

---

### 3.5 `modules`

Admin sidebar / permission modules.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `slug` | VARCHAR(100) UNIQUE | e.g. `listings`, `transactions` |
| `name_en` | VARCHAR(150) | |
| `name_ar` | VARCHAR(150) NULL | |
| `parent_id` | BIGINT FK NULL → `modules` | For nested menus |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Blocked` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Seed slugs:** `dashboard`, `users`, `listings`, `categories`, `packages`, `transactions`, `chat`, `cms`, `reports`, `settings`, `support`.

---

### 3.6 `admin_module_privileges`

Granular admin permissions (optional sub-admin overrides).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `module_id` | BIGINT FK → `modules` | |
| `can_view` | INTEGER DEFAULT 1 | |
| `can_create` | INTEGER DEFAULT 0 | |
| `can_update` | INTEGER DEFAULT 0 | |
| `can_delete` | INTEGER DEFAULT 0 | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Unique:** `(user_id, module_id)`.

---

### 3.7 `otp_verifications`

OTP sessions for phone login, registration, and password reset.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `country_code_id` | BIGINT FK | |
| `mobile` | VARCHAR(20) | |
| `otp_code` | VARCHAR(255) | Hashed |
| `purpose` | INTEGER | `1-login, 2-register, 3-forgot_password, 4-change_phone` |
| `expires_at` | DATETIME | 120 seconds from issue |
| `is_used` | INTEGER DEFAULT 0 | `0-not_used, 1-used` |
| `attempt_count` | INTEGER DEFAULT 0 | |
| `created_at`, `updated_at` | TIMESTAMP | |

**Rate limit:** Max 3 OTP requests per 10 minutes per phone (enforced in service layer + Redis).

---

### 3.8 `social_accounts`

Apple Sign-In and Google Sign-In account linking.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `provider` | INTEGER | `1-apple, 2-google` |
| `provider_user_id` | VARCHAR(255) | OAuth subject ID |
| `email` | VARCHAR(150) NULL | From provider |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Unique:** `(provider, provider_user_id)`.

---

### 3.9 `login_logs`

Login audit trail for all roles.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK NULL → `users` | NULL on failed attempt |
| `role_id` | INTEGER NULL | |
| `login_type` | INTEGER | `1-password, 2-otp, 3-social, 4-admin` |
| `ip_address` | VARCHAR(45) | |
| `user_agent` | TEXT NULL | |
| `device_type` | VARCHAR(50) NULL | `ios, android, web` |
| `status` | INTEGER | `1-success, 2-failed` |
| `failure_reason` | VARCHAR(255) NULL | |
| `created_at` | TIMESTAMP | |

---

### 3.10 `audit_logs`

Admin action audit trail (all admin mutations).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `admin_id` | BIGINT FK → `users` | |
| `action` | VARCHAR(100) | e.g. `listing.approve`, `user.ban` |
| `auditable_type` | VARCHAR(100) | Polymorphic model class |
| `auditable_id` | BIGINT | Target entity ID |
| `old_values` | JSON NULL | |
| `new_values` | JSON NULL | |
| `ip_address` | VARCHAR(45) NULL | |
| `created_at` | TIMESTAMP | |

---

## 4. Location & Taxonomy

### 4.1 `governorates`

Bahrain governorates for filters and listing location.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `name_en` | VARCHAR(100) | e.g. `Capital Governorate` |
| `name_ar` | VARCHAR(100) | |
| `slug` | VARCHAR(50) UNIQUE | `capital, muharraq, northern, southern, central` |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Seed:** Capital (Manama), Muharraq, Northern, Southern, Central.

---

### 4.2 `category_types`

Top-level marketplace verticals (Vehicles, Electronics, Real Estate, Fashion).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `name_en` | VARCHAR(100) | |
| `name_ar` | VARCHAR(100) | |
| `icon_url` | VARCHAR(255) NULL | |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 4.3 `categories`

Second-level categories under a type (e.g. Car, Bike under Vehicles).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `category_type_id` | BIGINT FK → `category_types` | |
| `name_en` | VARCHAR(100) | |
| `name_ar` | VARCHAR(100) | |
| `icon_url` | VARCHAR(255) NULL | |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 4.4 `sub_categories`

Third-level (e.g. SUV, Sedan under Car).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `category_id` | BIGINT FK → `categories` | |
| `name_en` | VARCHAR(100) | |
| `name_ar` | VARCHAR(100) | |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 4.5 `attributes`

Dynamic fields per category (Brand, Model, Year, Mileage, etc.).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `category_id` | BIGINT FK → `categories` | |
| `name_en` | VARCHAR(100) | e.g. `Brand` |
| `name_ar` | VARCHAR(100) | |
| `slug` | VARCHAR(100) | e.g. `brand`, `model`, `year` |
| `input_type` | INTEGER | `1-dropdown, 2-text, 3-number, 4-range, 5-multiselect` |
| `is_required` | INTEGER DEFAULT 0 | `0-no, 1-yes` |
| `has_predefined_options` | INTEGER DEFAULT 0 | `0-no, 1-yes` |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 4.6 `attribute_options`

Predefined values for dropdown attributes (Toyota, BMW, etc.).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `attribute_id` | BIGINT FK → `attributes` | |
| `value_en` | VARCHAR(200) | |
| `value_ar` | VARCHAR(200) NULL | |
| `image_url` | VARCHAR(255) NULL | Brand logo |
| `parent_option_id` | BIGINT FK NULL → `attribute_options` | Brand→Model cascade |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Note:** `parent_option_id` enables Model options filtered by selected Brand.

---

## 5. Listings

### 5.1 `listings`

Core car/product listing table. Fixed columns hold search-critical fields; extended attributes in `listing_attribute_values`.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `seller_id` | BIGINT FK → `users` | Customer who owns the listing (`role_id = 2`) |
| `category_id` | BIGINT FK → `categories` | |
| `sub_category_id` | BIGINT FK NULL → `sub_categories` | |
| `title` | VARCHAR(255) NULL | Auto-generated or custom |
| `make` | VARCHAR(100) NULL | Denormalized from attributes for search |
| `model` | VARCHAR(100) NULL | Denormalized |
| `year` | SMALLINT NULL | 1970 – current year + 1 |
| `mileage_km` | INTEGER NULL | |
| `price_bhd` | DECIMAL(10,3) | Min 1.000 |
| `condition` | INTEGER NULL | `1-new, 2-used` |
| `colour` | VARCHAR(50) NULL | |
| `transmission` | INTEGER NULL | `1-automatic, 2-manual` |
| `fuel_type` | INTEGER NULL | `1-petrol, 2-diesel, 3-electric, 4-hybrid` |
| `description` | TEXT | 20–2000 chars |
| `governorate_id` | BIGINT FK → `governorates` | |
| `latitude` | DECIMAL(9,6) NULL | Google Maps pin |
| `longitude` | DECIMAL(9,6) NULL | |
| `status` | INTEGER DEFAULT 4 | `1-active, 2-sold, 3-deleted, 4-pending_review, 5-rejected, 6-expired` |
| `rejection_reason` | TEXT NULL | |
| `is_featured` | INTEGER DEFAULT 0 | `0-no, 1-yes` |
| `featured_until` | TIMESTAMP NULL | |
| `is_slider_ad` | INTEGER DEFAULT 0 | `0-no, 1-yes` |
| `slider_until` | TIMESTAMP NULL | |
| `slider_position` | INTEGER NULL | Homepage carousel order |
| `view_count` | INTEGER DEFAULT 0 | |
| `report_count` | INTEGER DEFAULT 0 | Denormalized |
| `expires_at` | TIMESTAMP NULL | Based on package subscription |
| `published_at` | TIMESTAMP NULL | |
| `sold_at` | TIMESTAMP NULL | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Indexes:** `(status, governorate_id)`, `(seller_id, status)`, `(is_featured, featured_until)`, `(is_slider_ad, slider_until)`, `(category_id, status)`, fulltext on `make, model, description` (optional).

---

### 5.2 `listing_attribute_values`

EAV store for dynamic category attributes.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `listing_id` | BIGINT FK → `listings` | |
| `attribute_id` | BIGINT FK → `attributes` | |
| `option_id` | BIGINT FK NULL → `attribute_options` | For dropdown types |
| `text_value` | TEXT NULL | For text/number types |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Unique:** `(listing_id, attribute_id)`.

---

### 5.3 `listing_media`

Photos and video for listings.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `listing_id` | BIGINT FK → `listings` | |
| `media_type` | INTEGER | `1-photo, 2-video` |
| `url` | VARCHAR(500) | CDN URL |
| `thumbnail_url` | VARCHAR(500) NULL | Video first frame |
| `is_primary` | INTEGER DEFAULT 0 | `0-no, 1-yes` |
| `file_size` | INTEGER NULL | Bytes |
| `duration_seconds` | INTEGER NULL | Video max 60s |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Rules:** Min 3 photos, max 20 photos; optional 1 video (max 50MB, MP4).

---

### 5.4 `listing_drafts`

Auto-saved sell flow drafts (60-second interval).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `category_id` | BIGINT FK NULL → `categories` | |
| `draft_data` | JSON | All form fields + media URLs |
| `step` | INTEGER DEFAULT 1 | `1-details, 2-location, 3-media, 4-preview` |
| `expires_at` | TIMESTAMP NULL | Auto-cleanup after 30 days |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 5.5 `listing_reports`

User reports against listings.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `listing_id` | BIGINT FK → `listings` | |
| `reporter_id` | BIGINT FK → `users` | |
| `reason` | INTEGER | `1-spam, 2-fraud, 3-inappropriate, 4-wrong_info, 5-other` |
| `description` | TEXT NULL | |
| `status` | INTEGER DEFAULT 1 | `1-pending, 2-dismissed, 3-actioned` |
| `actioned_by` | BIGINT FK NULL → `users` | |
| `actioned_at` | TIMESTAMP NULL | |
| `added_by` | BIGINT FK NULL → `users` | Reporter (same as `reporter_id` on create) |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 5.6 `listing_views`

Analytics for listing performance reports.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `listing_id` | BIGINT FK → `listings` | |
| `viewer_id` | BIGINT FK NULL → `users` | NULL for anonymous |
| `ip_address` | VARCHAR(45) NULL | |
| `viewed_at` | TIMESTAMP | |

**Index:** `(listing_id, viewed_at)`.

---

## 6. Packages & Subscriptions

### 6.1 `packages`

Sellable packages: regular (ad slots), featured boost, slider ad.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `name_en` | VARCHAR(100) | e.g. `Starter 10 Ads` |
| `name_ar` | VARCHAR(100) | |
| `package_type` | INTEGER | `1-regular, 2-featured, 3-slider_ad` |
| `ad_limit` | INTEGER NULL | For regular packages |
| `duration_days` | INTEGER | Validity period |
| `price_bhd` | DECIMAL(10,3) | |
| `description_en` | TEXT NULL | |
| `description_ar` | TEXT NULL | |
| `featured_tier` | INTEGER NULL | `1-bronze(3d), 2-silver(7d), 3-gold(30d)` |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 6.2 `user_subscriptions`

Active and historical package purchases per user.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `package_id` | BIGINT FK → `packages` | |
| `listing_id` | BIGINT FK NULL → `listings` | For featured/slider only |
| `package_name_snapshot` | VARCHAR(100) | Copied at purchase |
| `package_type` | INTEGER | Snapshot |
| `price_bhd_snapshot` | DECIMAL(10,3) | Snapshot |
| `ads_limit` | INTEGER NULL | Snapshot for regular |
| `ads_used` | INTEGER DEFAULT 0 | |
| `status` | INTEGER DEFAULT 1 | `1-active, 2-expired, 3-cancelled` |
| `started_at` | TIMESTAMP | |
| `expires_at` | TIMESTAMP | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

## 7. Wallet & Payments

### 7.1 `customer_wallets`

One wallet per customer user.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK UNIQUE → `users` | |
| `balance_bhd` | DECIMAL(10,3) DEFAULT 0.000 | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 7.2 `wallet_transactions`

Immutable ledger for all wallet movements.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `wallet_id` | BIGINT FK → `customer_wallets` | |
| `subscription_id` | BIGINT FK NULL → `user_subscriptions` | |
| `type` | INTEGER | `1-topup, 2-package_purchase, 3-featured_purchase, 4-slider_purchase, 5-refund, 6-admin_adjustment` |
| `direction` | INTEGER | `1-credit, 2-debit` |
| `amount_bhd` | DECIMAL(10,3) | |
| `balance_before` | DECIMAL(10,3) | |
| `balance_after` | DECIMAL(10,3) | |
| `gateway` | VARCHAR(50) NULL | `benefit_pay, tap_payments, wallet, admin` |
| `gateway_transaction_id` | VARCHAR(255) NULL | |
| `gateway_status` | INTEGER NULL | `1-success, 2-failed, 3-pending` |
| `invoice_url` | VARCHAR(500) NULL | S3 PDF URL |
| `notes` | TEXT NULL | Admin adjustment reason |
| `added_by` | BIGINT FK NULL → `users` | |
| `created_at` | TIMESTAMP | |

> **Immutable ledger** — no `updated_by` or `deleted_at`. Rows are never modified after insert.

---

### 7.3 `payment_transactions`

External payment gateway records (top-ups, direct gateway purchases).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `wallet_transaction_id` | BIGINT FK NULL → `wallet_transactions` | |
| `amount_bhd` | DECIMAL(10,3) | |
| `gateway` | VARCHAR(50) | `benefit_pay, tap_payments` |
| `gateway_transaction_id` | VARCHAR(255) NULL | |
| `gateway_status` | INTEGER DEFAULT 3 | `1-success, 2-failed, 3-pending` |
| `gateway_response` | JSON NULL | Raw response snippet |
| `invoice_url` | VARCHAR(500) NULL | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | Gateway status updates only |
| `created_at`, `updated_at` | TIMESTAMP | |

> **No `deleted_at`** — financial records are never soft-deleted.

---

### 7.4 `refund_requests`

User-initiated refund queue for admin approval.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `payment_transaction_id` | BIGINT FK → `payment_transactions` | |
| `amount_bhd` | DECIMAL(10,3) | |
| `reason` | TEXT | |
| `status` | INTEGER DEFAULT 1 | `1-pending, 2-approved, 3-rejected` |
| `reviewed_by` | BIGINT FK NULL → `users` | |
| `reviewed_at` | TIMESTAMP NULL | |
| `rejection_reason` | TEXT NULL | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 7.5 `payment_gateway_logs`

All gateway API interactions for debugging.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `gateway` | VARCHAR(50) | |
| `direction` | INTEGER | `1-request, 2-response, 3-webhook` |
| `endpoint` | VARCHAR(255) NULL | |
| `payload` | JSON NULL | |
| `status_code` | INTEGER NULL | |
| `created_at` | TIMESTAMP | |

---

## 8. Communication

### 8.1 `conversations`

Buyer–seller chat between two customers, both `role_id = 2`.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `listing_id` | BIGINT FK → `listings` | |
| `buyer_id` | BIGINT FK → `users` | |
| `seller_id` | BIGINT FK → `users` | |
| `status` | INTEGER DEFAULT 1 | `1-active, 2-blocked` |
| `blocked_by` | BIGINT FK NULL → `users` | |
| `block_reason` | TEXT NULL | |
| `last_message_at` | TIMESTAMP NULL | |
| `buyer_unread_count` | INTEGER DEFAULT 0 | |
| `seller_unread_count` | INTEGER DEFAULT 0 | |
| `is_flagged` | INTEGER DEFAULT 0 | `0-no, 1-yes` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

**Unique:** `(listing_id, buyer_id)`.

---

### 8.2 `messages`

Chat messages within a conversation.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `conversation_id` | BIGINT FK → `conversations` | |
| `sender_id` | BIGINT FK → `users` | |
| `content` | TEXT | Max 2000 chars |
| `is_flagged` | INTEGER DEFAULT 0 | |
| `read_at` | TIMESTAMP NULL | |
| `created_at` | TIMESTAMP | |

**Index:** `(conversation_id, created_at)`.

---

## 9. Social & Engagement

### 9.1 `wishlists`

Saved listings per user.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `listing_id` | BIGINT FK → `listings` | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft remove from wishlist |
| `created_at`, `updated_at` | TIMESTAMP | |

**Unique:** `(user_id, listing_id)`.

---

### 9.2 `reviews`

Seller ratings from buyers.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `reviewer_id` | BIGINT FK → `users` | |
| `seller_id` | BIGINT FK → `users` | |
| `listing_id` | BIGINT FK NULL → `listings` | |
| `rating` | INTEGER | 1–5 |
| `comment` | TEXT NULL | Max 500 chars |
| `is_visible` | INTEGER DEFAULT 1 | `0-hidden, 1-visible` |
| `added_by` | BIGINT FK NULL → `users` | Reviewer (`reviewer_id`) |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 9.3 `user_reports`

Reports against users (not listings).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `reported_user_id` | BIGINT FK → `users` | |
| `reporter_id` | BIGINT FK → `users` | |
| `reason` | INTEGER | Same enum as listing reports |
| `description` | TEXT NULL | |
| `status` | INTEGER DEFAULT 1 | `1-pending, 2-dismissed, 3-actioned` |
| `actioned_by` | BIGINT FK NULL → `users` | |
| `added_by` | BIGINT FK NULL → `users` | Reporter (`reporter_id`) |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

## 10. Notifications & CMS

### 10.1 `notifications`

In-app notifications for customers.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `type` | INTEGER | `1-new_match, 2-chat, 3-featured_expiry, 4-package_expiry, 5-slider_expiry, 6-system, 7-listing_approved, 8-listing_rejected` |
| `title_en` | VARCHAR(255) | |
| `title_ar` | VARCHAR(255) NULL | |
| `body_en` | TEXT | |
| `body_ar` | TEXT NULL | |
| `data` | JSON NULL | Deep link payload |
| `is_read` | INTEGER DEFAULT 0 | |
| `read_at` | TIMESTAMP NULL | |
| `added_by` | BIGINT FK NULL → `users` | System or admin sender |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 10.2 `notification_campaigns`

Admin-composed push/email/SMS broadcasts.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `type` | INTEGER | `1-push, 2-email, 3-sms` |
| `audience_type` | INTEGER | `1-all, 2-specific_user, 3-governorate, 4-active_subscription, 5-expiring_subscription` |
| `audience_filter` | JSON NULL | Governorate ID, user IDs, etc. |
| `title` | VARCHAR(255) NULL | |
| `body` | TEXT | |
| `scheduled_at` | TIMESTAMP NULL | |
| `sent_at` | TIMESTAMP NULL | |
| `delivery_count` | INTEGER DEFAULT 0 | |
| `status` | INTEGER DEFAULT 1 | `1-draft, 2-scheduled, 3-sent, 4-failed` |
| `added_by` | BIGINT FK → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 10.3 `banners`

Category promotional banners.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `title_en` | VARCHAR(255) NULL | |
| `title_ar` | VARCHAR(255) NULL | |
| `image_url` | VARCHAR(500) | |
| `link_url` | VARCHAR(500) NULL | |
| `category_type_id` | BIGINT FK NULL → `category_types` | |
| `category_id` | BIGINT FK NULL → `categories` | |
| `start_date` | DATE NULL | |
| `end_date` | DATE NULL | |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 10.4 `sliders`

Homepage hero slider (CMS-managed, separate from paid slider ads).

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `image_url` | VARCHAR(500) | |
| `link_url` | VARCHAR(500) NULL | |
| `display_duration` | INTEGER DEFAULT 5 | Seconds |
| `start_date` | DATE NULL | |
| `end_date` | DATE NULL | |
| `status` | INTEGER DEFAULT 1 | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 10.5 `cms_pages`

Static pages: About, Terms, Privacy, Contact.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `slug` | VARCHAR(100) UNIQUE | `about, terms, privacy, contact` |
| `title_en` | VARCHAR(255) | |
| `title_ar` | VARCHAR(255) | |
| `content_en` | LONGTEXT | |
| `content_ar` | LONGTEXT | |
| `version` | INTEGER DEFAULT 1 | Incremented on publish |
| `published_at` | TIMESTAMP NULL | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 10.6 `faqs`

FAQ items for Help & Support.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `question_en` | VARCHAR(500) | |
| `question_ar` | VARCHAR(500) | |
| `answer_en` | TEXT | |
| `answer_ar` | TEXT | |
| `status` | INTEGER DEFAULT 1 | |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 10.7 `announcements`

Platform-wide in-app announcement banners.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `title_en` | VARCHAR(255) | |
| `title_ar` | VARCHAR(255) | |
| `body_en` | TEXT | |
| `body_ar` | TEXT | |
| `expires_at` | TIMESTAMP | |
| `status` | INTEGER DEFAULT 1 | `1-Active, 2-Inactive` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

## 11. Support

### 11.1 `support_tickets`

User-submitted support requests.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `ticket_number` | VARCHAR(20) UNIQUE | e.g. `TKT-20260701-001` |
| `user_id` | BIGINT FK → `users` | |
| `subject` | VARCHAR(255) | |
| `type` | INTEGER | `1-complaint, 2-bug_report, 3-inquiry, 4-other` |
| `status` | INTEGER DEFAULT 1 | `1-open, 2-in_progress, 3-resolved, 4-closed` |
| `assigned_to` | BIGINT FK NULL → `users` | Admin |
| `device_info` | JSON NULL | OS, app version |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

### 11.2 `support_ticket_replies`

Thread messages within a ticket.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `ticket_id` | BIGINT FK → `support_tickets` | |
| `sender_id` | BIGINT FK → `users` | User or admin |
| `message` | TEXT | |
| `is_admin_reply` | INTEGER DEFAULT 0 | |
| `created_at` | TIMESTAMP | |

---

## 12. System Configuration

### 12.1 `settings`

Key-value store for platform settings.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `key` | VARCHAR(100) UNIQUE | e.g. `site_name`, `maintenance_mode` |
| `value` | TEXT NULL | JSON-encoded for complex values |
| `group` | VARCHAR(50) | `general, regional, payment, security, integrations` |
| `added_by` | BIGINT FK NULL → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | When setting row is retired |
| `created_at`, `updated_at` | TIMESTAMP | |

**Key settings:**

| Key | Group | Description |
|-----|-------|-------------|
| `site_name` | general | Platform display name |
| `site_logo_url` | general | Logo for emails/admin |
| `maintenance_mode` | general | `0/1` — shows Server Down on mobile |
| `default_locale` | regional | `en` or `ar` |
| `currency` | regional | `BHD` |
| `timezone` | regional | `Asia/Bahrain` |
| `min_wallet_topup_bhd` | payment | Minimum top-up amount |
| `benefit_pay_api_key` | payment | Encrypted |
| `tap_payments_api_key` | payment | Encrypted |
| `primary_payment_gateway` | payment | `benefit_pay` or `tap_payments` |
| `admin_session_timeout_minutes` | security | Default 240 |
| `otp_lock_minutes` | security | Default 15 |
| `fcm_server_key` | integrations | Encrypted |
| `twilio_sid` | integrations | Encrypted |
| `sendgrid_api_key` | integrations | Encrypted |
| `google_maps_api_key` | integrations | Encrypted |
| `ios_min_version` | general | Force-update threshold |
| `android_min_version` | general | Force-update threshold |

---

### 12.2 `ip_blocklist`

Blocked IPs enforced at API gateway / middleware.

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `ip_address` | VARCHAR(45) UNIQUE | |
| `reason` | TEXT NULL | |
| `added_by` | BIGINT FK → `users` | |
| `updated_by` | BIGINT FK NULL → `users` | |
| `deleted_at` | TIMESTAMP NULL | Soft delete |
| `created_at`, `updated_at` | TIMESTAMP | |

---

## 13. Laravel Framework Tables

These ship with Laravel / Sanctum and require no custom design:

| Table | Purpose |
|-------|---------|
| `personal_access_tokens` | Sanctum API tokens (access tokens) |
| `password_reset_tokens` | Password reset (admin email flow) |
| `sessions` | Web sessions (admin panel if session-based) |
| `cache` / `cache_locks` | Redis/database cache |
| `jobs` / `job_batches` / `failed_jobs` | Queue workers |

**Token strategy:**
- **Access token:** Sanctum personal access token, 24h TTL (configurable).
- **Refresh token:** Store hashed refresh tokens in a dedicated `refresh_tokens` table (optional) or issue long-lived Sanctum tokens with `expires_at` for 30 days.

### 13.1 `refresh_tokens` (recommended)

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT PK | |
| `user_id` | BIGINT FK → `users` | |
| `token` | VARCHAR(255) | Hashed |
| `expires_at` | TIMESTAMP | 30 days |
| `revoked_at` | TIMESTAMP NULL | |
| `created_at` | TIMESTAMP | |

---

## 14. Migration Order

Run migrations in dependency order:

1. `roles`, `country_codes`, `governorates`
2. `users` (alter default Laravel migration)
3. `admin_profiles`, `modules`, `admin_module_privileges`
4. `otp_verifications`, `social_accounts`, `login_logs`, `audit_logs`
5. `category_types`, `categories`, `sub_categories`
6. `attributes`, `attribute_options`
7. `packages`
8. `customer_wallets`
9. `listings`, `listing_media`, `listing_attribute_values`, `listing_drafts`, `listing_reports`, `listing_views`
10. `user_subscriptions`, `wallet_transactions`, `payment_transactions`, `refund_requests`, `payment_gateway_logs`
11. `conversations`, `messages`
12. `wishlists`, `reviews`, `user_reports`
13. `notifications`, `notification_campaigns`
14. `banners`, `sliders`, `cms_pages`, `faqs`, `announcements`
15. `support_tickets`, `support_ticket_replies`
16. `settings`, `ip_blocklist`, `refresh_tokens`

---

## 15. Seeders

| Seeder | Data |
|--------|------|
| `RoleSeeder` | Admin, Customer |
| `CountryCodeSeeder` | Bahrain (+973) + common GCC codes |
| `GovernorateSeeder` | 5 Bahrain governorates |
| `ModuleSeeder` | 11 admin modules |
| `AdminUserSeeder` | Super admin with TOTP |
| `CategorySeeder` | Vehicles → Car → SUV/Sedan/etc. |
| `AttributeSeeder` | Brand, Model, Year, Mileage, Fuel Type, Transmission, Colour, Condition for Car category |
| `AttributeOptionSeeder` | Initial car brands (Toyota, BMW, Honda, etc.) |
| `PackageSeeder` | Starter/Business/Pro regular + Bronze/Silver/Gold featured + Slider packages |
| `GeneralSettingsSeeder` | Default platform settings |
| `CmsPageSeeder` | About, Terms, Privacy, Contact placeholders |
| `FaqSeeder` | Sample FAQs |

---

## 16. Scheduled Jobs

| Command | Schedule | Purpose |
|---------|----------|---------|
| `listings:expire` | Daily 00:05 | Set `status = 6` when `expires_at` passed |
| `featured:expire` | Hourly | Clear `is_featured` when `featured_until` passed |
| `slider:expire` | Hourly | Clear `is_slider_ad` when `slider_until` passed |
| `subscriptions:expire` | Daily 00:10 | Mark subscriptions expired; notify users |
| `notifications:send-scheduled` | Every minute | Process `notification_campaigns` |
| `drafts:cleanup` | Weekly | Delete listing drafts older than 30 days |
| `otp:cleanup` | Daily | Purge expired OTP records |

---

## 17. Table Summary

| # | Table | Purpose |
|---|-------|---------|
| 1 | `roles` | User role lookup (admin, customer) |
| 2 | `country_codes` | Phone dial codes |
| 3 | `users` | **Primary identity & credentials** |
| 4 | `admin_profiles` | Admin 2FA, sub-role, lockout |
| 5 | `modules` | Admin permission modules |
| 6 | `admin_module_privileges` | Per-admin module access |
| 7 | `otp_verifications` | SMS OTP sessions |
| 8 | `social_accounts` | Apple/Google OAuth links |
| 9 | `login_logs` | Login audit |
| 10 | `audit_logs` | Admin action audit |
| 11 | `governorates` | Bahrain location filter |
| 12 | `category_types` | Top-level marketplace verticals |
| 13 | `categories` | Second-level categories |
| 14 | `sub_categories` | Third-level categories |
| 15 | `attributes` | Dynamic fields per category |
| 16 | `attribute_options` | Dropdown values (Brand, Model, etc.) |
| 17 | `listings` | Car/product listings (seller = customer user) |
| 18 | `listing_attribute_values` | EAV attribute storage |
| 19 | `listing_media` | Photos and video |
| 20 | `listing_drafts` | Auto-saved sell flow |
| 21 | `listing_reports` | Listing moderation queue |
| 22 | `listing_views` | View analytics |
| 23 | `packages` | Sellable packages |
| 24 | `user_subscriptions` | User package purchases |
| 25 | `customer_wallets` | Wallet balance |
| 26 | `wallet_transactions` | Wallet ledger |
| 27 | `payment_transactions` | Gateway payments |
| 28 | `refund_requests` | Refund approval queue |
| 29 | `payment_gateway_logs` | Gateway debug log |
| 30 | `conversations` | Chat threads (buyer ↔ seller, both customers) |
| 31 | `messages` | Chat messages |
| 32 | `wishlists` | Saved listings |
| 33 | `reviews` | Seller ratings (reviewer and seller are both customers) |
| 34 | `user_reports` | User moderation queue |
| 35 | `notifications` | In-app notifications |
| 36 | `notification_campaigns` | Admin broadcasts |
| 37 | `banners` | Category banners |
| 38 | `sliders` | Homepage CMS sliders |
| 39 | `cms_pages` | Static pages |
| 40 | `faqs` | FAQ content |
| 41 | `announcements` | Platform announcements |
| 42 | `support_tickets` | Support queue |
| 43 | `support_ticket_replies` | Ticket thread |
| 44 | `settings` | Platform configuration |
| 45 | `ip_blocklist` | Security blocklist |
| 46 | `refresh_tokens` | Token refresh |

---

*This document is version-controlled. Changes require sign-off from the Project Lead before implementation on affected modules.*