# Morvia — Database design

> Final design, 2026-11-12. DDL: [`db-schema.sql`](db-schema.sql) (MySQL 8, loads cleanly:
> 78 tables, 131 foreign keys, 26 check constraints). Migrations are written from that file.
> History: a 103-table first draft (Codex), reviewed by agy ([`schema-review-agy.md`](schema-review-agy.md))
> and in a design session with the project owner; the decisions are in
> [`../DECISIONS.md`](../DECISIONS.md).

## Quick reference

| Topic | Rule |
|---|---|
| Ids (TD-14) | Every table has `id` (BIGINT, foreign keys and joins only) and `uuid` (CHAR(36) UNIQUE, the public id). The Flutter and Vue apps use the **uuid** in every route, body and response; foreign keys stay on `id`. The API resolves uuid → id once at the edge (route model binding on `uuid`) and never returns an `id`. No uuid on composite-key pivots, `notifications` (its id already is a uuid) and `media` (own uuid). |
| Accounts | One account per app (DEC-04): `users.user_type` = trainee, trainer, center, provider, admin. Mobile, email and username are unique **per type** among live accounts. Trainees and admins need no extra table; trainers, centers and providers have one because they have their own lifecycle and other data points at them. |
| Course | The `courses` row **is** the live course. Edits to a published course wait in `course_requests` (type `edit`) and are applied on approval (D2, TD-10). Cancellation is a `course_requests` row of type `cancellation` (D5). |
| Offer | The offer is the course discount: `courses.discount_percentage` + start / end date (DEC-18). No offer table. |
| Price | Editable only until the first confirmed enrollment (DEC-34). |
| Checkout | `purchases` (one checkout: a course or a whole package, DEC-31) → `enrollments` (one per course, with that course's share of every amount) → `payments`. Seat taken only when payment succeeds, under the course row lock (DEC-29). |
| Integrations (TD-15, TD-16) | The payment gateway (DEC-06) and Nafath are not provided yet. `payments` and `nafath_verifications` already carry the vendor fields (`gateway`, `method`, `gateway_reference`, `gateway_payload` / `provider_reference`); the code reaches both through contracts with a `fake` driver, so adding the real vendor later needs no migration. |
| Refunds | Paid outside the system (D6). The enrollment records `refund_status` none / pending / completed; completed needs a confirmation, one refund per enrollment. |
| Money owed | `course_earnings` (owner's share of each enrollment, trainer **or** center), `hall_commissions` (provider owes Morvia). Both settled outside and recorded. |
| Wallet | Cashback and points are append-only ledgers: `cashback_transactions`, `points_transactions`. Balances are sums. |
| Languages | All user-facing text has `{table}_translations` (ar, en). Notifications store both languages in `data` (TD-12). |
| Files | Spatie `media` only — collection names below. |

## Answers to the review questions (2026-11-12)

| Question | Decision |
|---|---|
| `course_learning_points` (+ translations) | **Dropped.** TRR-014 (client note 22-10) still requires the section, but it is a short ordered list of text: stored as `course_translations.learning_points` JSON (`["…", "…"]`) per language. Nothing else references a single point, so two tables bought nothing. The screens do not show it yet (design predates the note). |
| `course_versions` — why, which BRD point | It existed for **D2** (TRR-021, ADM-028, WF-04, BR-CRS-06): "substantive edits to a published course are re-reviewed while the **approved version stays live**; if rejected, the previous version continues". Every field lived in a version row so the old and the new could coexist. **Replaced (TD-10)**: `courses` holds the live data, and a pending edit lives in `course_requests.payload` until the admin approves it. Same rule, one table less, no "which version is live" pointer. History of what a trainee bought is in `enrollments.snapshot` (BR-ENR-05); history of edits is `course_requests` + `audit_logs`. |
| `course_reviews` | Was the **admin approval** history, not ratings. Replaced by `courses.submitted_at / reviewed_by / reviewed_at / rejection_reason` + `audit_logs`. |
| Ratings "for the trainer" | New `trainer_ratings` (stars 1–5 + comment) from a completed enrollment, from the My Courses "Rate instructor" dialog; shown as reviews on the trainer profile. Not in the BRD — adopted from the screens (SCR-04). |
| `course_cancellation_requests` | **Kept as a concept, merged into `course_requests`.** DEC-12 removed the *trainee's* enrollment cancellation; the client policy still requires the **course** cancellation request (D5, C-§01–07, §24: independent trainer or center asks with a reason, admin approves / rejects, requester, date, reason and decision recorded). |
| `course_access` | Held the external link and the current session link of an external course (TRR-011, TRR-027, BR-CRS-05), separate because the session link changes **without review**. Now columns on `courses` (`external_link`, `access_instructions`, `session_link`). |
| `course_threshold_events` | Tracked a threshold course's timeline (minimum reached, date due, escalated). Now columns on `courses`: `min_enrollments`, `minimum_deadline_at`, `minimum_reached_at`, `date_deadline_at`, `date_set_at`; each admin extension of a deadline (D5) is an `audit_logs` row. |
| `course_discounts` | **Dropped** — the discount is three columns on `courses` (DEC-18). It is reviewed with the course (DEC-08). |
| `offers`, `offer_translations`, `offer_courses` | **Dropped** (DEC-18). The trainee's Offers list = published courses whose discount dates include today (index `courses_offers`). |
| `ledger_entries` | A generic journal of every money movement, added by the first draft to satisfy "every money movement is a ledger row". **Dropped (TD-13)**: each money or points table is already its own ledger (append-only rows, status changes only): `payments` money in, `course_earnings` owed to owners, `hall_commissions` owed by providers, `cashback_transactions`, `points_transactions`, refunds on `enrollments`. A second journal would duplicate them and drift. |
| Notifications in ar & en | Laravel's `notifications` table; `data.title` and `data.body` hold both languages (TD-12). |

## Module map

| Module | Tables |
|---|---|
| Base (kept) | `countries`, `country_translations`, `cities`, `city_translations`, `roles`, `role_translations`, `permissions`, `permission_translations`, `permission_role`, `permission_user`, `users`, `categories`, `category_translations`, `devices`, `personal_access_tokens`, `static_pages`, `static_page_translations`, `sliders`, `slider_translations`, `f_a_q_s`, `f_a_q_translations`, `contact_messages`, `audit_logs`, `app_configs`, `feature_flags`, `integrations`, `search_histories`, `media`, `notifications` |
| Accounts | `training_centers` (+tr), `trainers` (+tr), `trainer_categories`, `trainer_center_history`, `nafath_verifications`, `hall_providers` (+tr), `profile_change_requests` |
| Trainer business model | `trainer_plans` (+tr), `trainer_subscriptions` |
| Courses | `courses`, `course_translations`, `course_sessions`, `course_requests` |
| Packages | `course_packages` (+tr), `course_package_items` |
| Checkout | `purchases`, `enrollments`, `payments`, `enrollment_tickets`, `completion_certificates`, `trainer_ratings` |
| Earnings and loyalty | `course_earnings`, `cashback_rules`, `cashback_transactions`, `loyalty_levels` (+tr), `points_transactions` |
| Halls and events | `halls` (+tr), `hall_features` (+tr), `hall_feature_prices`, `hall_availability`, `hall_bookings`, `hall_booking_slots`, `hall_booking_features`, `hall_booking_cancellation_requests`, `hall_commissions`, `events` (+tr) |
| Jobs | `job_ads` (+tr), `job_ad_categories`, `job_ad_views` |

**Removed from the base (TD-11):** `wallets`, `transactions`, `withdraw_requests` (no trainee money
wallet; trainer and center payouts are settled outside by the admin, BR-FIN-04 — no withdrawal
request exists in the BRD), `governorates` (+tr), regions, shipping zones, `addresses` (the BRD
has only cities; addresses are free text, DEC-03). `sliders.title/subtitle` move to
`slider_translations`; `categories` loses `parent_id` (no sub-categories in the BRD).

## Entity relationships

```mermaid
erDiagram
    users ||--o| trainers : "trainer account"
    users ||--o| training_centers : "center account"
    users ||--o| hall_providers : "provider account"
    training_centers ||--o{ trainers : "affiliates"
    trainers ||--o{ trainer_subscriptions : "business model"
    trainers ||--o{ courses : "independent owner / responsible trainer"
    training_centers ||--o{ courses : "owner (D3)"
    courses ||--o{ course_sessions : has
    courses ||--o{ course_requests : "edit / cancellation"
    course_packages ||--o{ course_package_items : contains
    courses ||--o{ course_package_items : "in"
    users ||--o{ purchases : "trainee buys"
    purchases ||--o{ enrollments : "one per course"
    purchases ||--o{ payments : "paid by"
    courses ||--o{ enrollments : has
    enrollments ||--o| enrollment_tickets : "in-person"
    enrollments ||--o| completion_certificates : "on completion"
    enrollments ||--o| course_earnings : "owner's share"
    enrollments ||--o| trainer_ratings : "rated"
    users ||--o{ cashback_transactions : ledger
    users ||--o{ points_transactions : ledger
    hall_providers ||--o{ halls : owns
    halls ||--o{ hall_bookings : "requested"
    users ||--o{ hall_bookings : "booker (trainer / center)"
    hall_bookings ||--o{ hall_booking_slots : has
    hall_bookings ||--o| hall_commissions : "Morvia's cut"
    hall_bookings ||--o{ hall_booking_cancellation_requests : "to the provider"
```

## Status columns and transitions

Every status column maps to a PHP backed enum with exactly these values and an allowed-transitions
map; anything else throws `INVALID_STATE_TRANSITION`.

| Column | Values | Transitions |
|---|---|---|
| `courses.status` | draft, under_review, rejected, published, awaiting_date, completed, cancelled, suspended | draft → under_review (submit; a center submits its trainers' courses, D3) → published / rejected; rejected → under_review; published → awaiting_date (threshold minimum reached, no date) → published (date set); published / awaiting_date → completed (auto at `ends_at`, or owner / admin), cancelled (approved cancellation request, D5), suspended (admin); suspended → published. **completed is final; never → cancelled (C-§04).** |
| `course_requests.status` | under_review, approved, rejected | under_review → approved / rejected. |
| `trainers.status` | profile_incomplete, under_review, active, rejected, suspended | independent: under_review → active / rejected; rejected → under_review. affiliated: profile_incomplete → active. active ↔ suspended. |
| `training_centers.status` | pending_activation, active, suspended | pending_activation → active ↔ suspended (admin only). |
| `hall_providers.status`, `halls.status` | active, inactive | ↔ |
| `profile_change_requests.status` | pending, approved, rejected | pending → approved / rejected. |
| `trainer_subscriptions.status` | pending_payment, active, expired, payment_failed, replaced | pending_payment → active / payment_failed; active → expired (end date) or replaced (renewed early, no carry-over, TRR-030). |
| `course_packages.status` | active, inactive | active ↔ inactive (owner or admin). |
| `purchases.status` | awaiting_payment, paid, failed, expired | awaiting_payment → paid / failed / expired. |
| `enrollments.status` | awaiting_payment, confirmed, completed, cancelled, payment_failed | awaiting_payment → confirmed (payment succeeded and a seat is free) / payment_failed; confirmed → completed (with the course) / cancelled (course cancelled, or admin removes the trainee). |
| `enrollments.refund_status` | none, pending, completed | none → pending → completed (only with `refund_confirmed_at`). |
| `payments.status` | pending, paid, failed | pending → paid / failed. Paid is never changed. |
| `enrollment_tickets.status` | valid, void | valid → void (course cancelled or trainee removed). |
| `course_earnings.status` | pending, available, settled, cancelled | pending → available (course completed) → settled (admin records payout); pending → cancelled (enrollment cancelled, D7). |
| `cashback_transactions.status` | pending, available, cancelled (type earned) · posted (other types) | earned: pending → available (DEC-25) / cancelled. |
| `hall_bookings.status` | pending, accepted, booked, rejected, withdrawn, cancelled, completed | pending → accepted / rejected (provider); pending / accepted → withdrawn (booker, C-§12); accepted → booked (provider); booked → cancelled (provider: approved request or offline with a reason, D8); booked → completed (after the last slot). |
| `hall_booking_cancellation_requests.status` | pending, approved, rejected | decided by the provider. |
| `hall_commissions.status` | pending, due, settled, cancelled | created pending at accepted; due at booked (income, DEC-33); settled by the admin; pending / due → cancelled on withdrawal or cancellation. |
| `events.status`, `job_ads.status` | published, inactive, ended | |
| `contact_messages.status` | new, reviewed, closed | |

## Money and points flows

1. **Checkout.** `purchases` stores the original amount, the discount (course offer or package
   saving), cashback used, points used and their value, and the amount paid; its CHECK keeps the
   arithmetic exact. Each `enrollments` row stores the same components for its own course; for a
   package the package price is split across its courses in proportion to their prices (the last
   course takes the rounding remainder). Cashback / points used are written as `used` ledger rows in
   the same transaction that confirms the payment (BR-LOY-02).
2. **Confirmation (DEC-29).** On the gateway's success: lock each course row
   (`SELECT … FOR UPDATE`, ordered by id), check `confirmed_count < seats`, set the enrollment
   `confirmed`, increment `confirmed_count`, create the ticket (in-person) and the
   `course_earnings` row (`pending`, rate snapshotted: the trainer's active commission rate, 0 on a
   subscription, or the center rate — DEC-15). A package purchase is all-or-nothing (DEC-31): if any
   course has no seat, nothing is confirmed. If the money was taken but no seat is left, the
   enrollments go `cancelled` with `refund_status = pending` for the admin.
3. **Completion (D4, WF-06).** Enrollments → completed, certificates issued, `points_to_earn`
   written as an `earned` points row, earnings pending → available.
4. **Cancellation (D5–D7, DEC-12).** Course cancelled (approved request) or trainee removed by the
   admin: enrollment → cancelled, ticket → void, earnings pending → cancelled, earned cashback
   pending → cancelled, used cashback / points → `returned` rows, and — when the admin removes a
   trainee — earned points taken back as a `reversed` row. Cash: `refund_status` pending →
   completed once the admin confirms the transfer made outside the system.
5. **Payouts (BR-FIN-04).** Available earnings are paid outside and marked `settled` with a reference.
6. **Hall commission (DEC-22, DEC-33).** Created at Accepted with the global rate copied; due at
   Booked; settled by the admin; cancelled if the booking is withdrawn or cancelled.

## Snapshot rules (BR-ENR-05, BR-FIN-03)

| What | Where it is frozen |
|---|---|
| Course name, owner, schedule, venue / link, price, discount | `enrollments.snapshot` + amount columns, at checkout |
| Package name, price and contents | `purchases.snapshot` |
| Course points to earn | `enrollments.points_to_earn` at confirmation |
| Commission rate | `course_earnings.commission_rate`, `trainer_subscriptions.commission_rate`, `hall_commissions.rate` |
| Subscription plan | `trainer_subscriptions.plan_snapshot`, `price`, `course_limit` |
| Cashback rule, points conversion | `cashback_transactions.rule_snapshot`, `points_transactions.conversion_snapshot` |
| Hall prices and feature names | `hall_bookings.snapshot`, `hall_booking_features.price_per_slot` / `name_snapshot` |
| Certificate text | `completion_certificates.snapshot` |

## Concurrency

- Seats: course row lock at confirmation (DEC-29); `confirmed_count <= seats` is also a CHECK.
- One active enrollment per trainee and course, one open request per course and type, one pending
  profile change per account, one pending booking-cancellation request, one active subscription per
  trainer: generated "open key" columns with UNIQUE indexes.
- Hall slot conflicts: lock the hall row, then query `hall_booking_slots` (indexed `hall_id, starts_at, ends_at`)
  for overlaps with accepted / booked bookings.
- Ledgers: every insert carries an `idempotency_key` (UNIQUE), so a retried job never posts twice.

## Media collections

| Model | Collections |
|---|---|
| users | `avatar` |
| trainers | `photo`, `cv`, `training_licence` |
| training_centers | `logo`, `licence` |
| hall_providers | `logo` |
| courses | `images` (first = cover), `venue_photos` |
| profile_change_requests | `pending_changes` |
| halls | `photos` · hall_features: `icon` · events: `image` · categories: `icon` · sliders: `image` · job_ads: `logo` |

## Settings (`app_configs`)

Platform commission rate for commission-model trainers (ADM-036) · center commission rate (DEC-15,
open) · hall commission rate (DEC-22) · points → SAR conversion rate · threshold deadlines in days
(ADM-032) · OTP expiry and resend limits (DEC-02).

## Open decisions that still touch the data

| ID | Effect on the schema |
|---|---|
| DEC-15 | Center commission rate source (one setting vs per center) and payout method. If per center: add `training_centers.commission_rate`. |
| DEC-24 | Several active cashback rules at once — the table already allows it; the choice is in code. |
| DEC-25 | When earned cashback becomes available — status column ready; trigger in code. |
| DEC-26 | How the level discount rate and the points conversion combine — both stored; formula in code. |
| DEC-05, DEC-06, DEC-07, DEC-10, DEC-11, DEC-23 | Behaviour only. |
| VAT | The BRD never mentions VAT. If prices must show VAT, add `vat_rate` / `vat_amount` to `purchases` and `enrollments` (to confirm with the client). |