# Schema review (agy)

Reviewer: Antigravity (agy), Gemini 3.8 Flash (Medium). Reviewed version: db-schema.sql as of 2026-11-12, before the owner-agreed changes.

> **Superseded.** This review covers the first 103-table draft (pass 1 of 5 only; the run was
> stopped once the final schema replaced that draft). Its valid findings were applied in the final
> [`db-schema.sql`](db-schema.sql): R-101/102 (mobile unique per account type, address fields),
> R-103 (trainer page content and links), R-104 (access instructions), R-108 (unique national ID),
> R-109 (username), R-110 (plan price), R-111 (course → subscription), R-114 (renewals).
> R-105, R-106, R-107, R-112, R-113 no longer apply (DEC-17, DEC-18, D6, TD-10).

## Part 1a — Coverage: trainee and trainer apps

### Feature Coverage Summary

| Feature ID | Feature Name | Status | Note |
|---|---|---|---|
| TRN-001 | Splash and onboarding | Covered | Device locale and notifications supported via `devices` and `users`. |
| TRN-002 | Trainee registration (create account) with OTP | Partial | Lacks phone uniqueness constraint on `users` under DEC-04; missing profile columns on `users`. |
| TRN-003 | Guest browsing | Covered | Public read endpoints supported across courses, categories, halls, and events. |
| TRN-004 | Trainee login | Covered | Authentication by mobile and password supported via `users`. |
| TRN-005 | Forgot password | Covered | Password reset lifecycle supported via `users` credentials. |
| TRN-006 | Trainee home | Covered | Aggregates sliders, categories, published courses, packages, and discounts. |
| TRN-007 | Course list, search and filters | Covered | Filtering by category, city, type, date, price, and trainer/center supported via `course_versions`. |
| TRN-008 | Course details (public) | Covered | Content, pricing, points, and trainer details supported via `course_versions`. |
| TRN-009 | Trainer profile page (trainee view) | Partial | Missing columns for introductory content and external portfolio links. |
| TRN-010 | Enrollment review (order summary) | Covered | Snapshot and discount allocations supported via `purchases` and `purchase_items`. |
| TRN-011 | Payment | Covered | Multi-channel payment records supported via `payments`. |
| TRN-012 | Payment success and enrollment confirmation | Covered | Enrollment status transition and ticket creation supported under row lock (DEC-29). |
| TRN-013 | My courses | Covered | Trainee enrollment filtering by status supported via `enrollments`. |
| TRN-014 | Enrolled course details | Partial | Missing external course access instructions / joining guidelines in `course_access`. |
| TRN-015 | Hall details (in-person course) | Partial | Disconnected from structured `halls` entities due to DEC-17 / D1 manual venue text. |
| TRN-016 | Events | Covered | External event listing and links supported via `events` and `event_translations`. |
| TRN-017 | Packages (list and details) | Covered | All-or-nothing package enrollments supported via `course_packages` (DEC-31). |
| TRN-018 | Offers section | Covered | Modeled via `course_discounts` per DEC-18 (no separate `offers` table). |
| TRN-019 | Cashback, points and levels (trainee wallet) | Partial | Missing wallet refund linkage on admin cancellation under DEC-12. |
| TRN-020 | Jobs | Covered | Job ads, categories, and views supported via `job_ads` and related tables. |
| TRN-021 | Trainee notifications | Covered | In-app notification routing supported via `notifications`. |
| TRN-022 | Trainee profile and account | Partial | Depends on dropped `trainee_profiles` columns migrating cleanly to `users` (DEC-04). |
| TRN-023 | Settings, policies and support | Covered | Informational pages and inquiry tickets supported via `static_pages` and `contact_messages`. |
| TRN-024 | Attendance ticket for in-person courses | Covered | Unique ticket generation supported via `enrollment_tickets`. |
| TRN-025 | Course completion certificate | Covered | Certificate issuance and snapshots supported via `completion_certificates`. |
| TRN-026 | Threshold-start courses — trainee view | Covered | Deferred dates and countdowns supported via `course_versions` and `course_threshold_events`. |
| TRN-027 | Halls section (trainee) | Covered | Read-only browsing of active halls and media supported via `halls`. |
| TRR-001 | Trainer entry — choose login type | Covered | Role routing supported at application gateway level. |
| TRR-002 | Independent trainer registration with Nafath verification | Partial | Missing uniqueness constraint on verified national ID in `nafath_verifications`. |
| TRR-003 | Trainer login and forgot password | Partial | Missing `username` column on `users` required for affiliated trainer authentication. |
| TRR-004 | Affiliated trainer — complete profile on first login | Covered | Incomplete profile state supported via `trainer_profiles.status`. |
| TRR-005 | Business model selection (independent only) | Covered | Model selection supported via `trainer_business_models`. |
| TRR-006 | Trainer package details and payment (independent only) | Partial | Subscription price column misnamed as `commission` in `trainer_plans`. |
| TRR-007 | Commission model confirmation (independent only) | Covered | Commission model state supported via `trainer_business_models`. |
| TRR-008 | Trainer dashboard (home) | Partial | Missing `trainer_subscription_id` FK on `courses` to track package course usage. |
| TRR-009 | Create course — basic data, schedule, pricing and type | Covered | Multi-version drafting and specifications supported via `course_versions`. |
| TRR-010 | In-person course — venue details | Covered | Manual venue fields supported via `course_versions` per D1. |
| TRR-011 | External course — access details | Partial | Missing access instructions / joining guidelines in `course_access`. |
| TRR-012 | Course preview and submit for review | Covered | Review submission supported via `courses.status` and `course_reviews`. |
| TRR-013 | Course discount | Partial | Lacks concurrency/overlap constraint on active discounts; broken FK in `purchases` under DEC-18. |
| TRR-014 | "What you will learn" section | Covered | Ordered learning points supported via `course_learning_points` and translations. |
| TRR-015 | Threshold-start course ("starts when the minimum is reached") | Partial | Missing `date_set_at` timestamp on `course_threshold_events` for deadline escalation. |
| TRR-016 | Create course package (trainer) | Covered | Multi-course bundling supported via `course_packages` and `course_package_items`. |
| TRR-017 | Trainer "My courses" | Covered | Status-grouped course listings supported via `courses`. |
| TRR-018 | Course review status after submission | Covered | State logging supported via `course_reviews`. |
| TRR-019 | Rejected course details and resubmission | Covered | Rejection reasoning and version iteration supported via `course_reviews`. |
| TRR-020 | Published course details (trainer) | Covered | Operational and performance views supported via `courses` and `enrollments`. |
| TRR-021 | Editing a submitted or published course | Covered | Substantive vs operational separation supported via `course_versions` and `course_access` (D2). |
| TRR-022 | Course completion (automatic or manual) | Covered | Completion triggers supported via `courses.completed_at`. |
| TRR-023 | Course cancellation request | Covered | Reviewable cancellation workflow supported via `course_cancellation_requests`. |
| TRR-024 | Course enrollees (trainer) | Covered | Confirmed enrollee listing supported via `enrollments`. |
| TRR-025 | Financial entitlements (independent, commission model) | Covered | Pending-to-available lifecycle supported via `trainer_entitlements`. |
| TRR-026 | Per-course earnings | Covered | Gross vs net platform accounting supported via `trainer_entitlements`. |
| TRR-027 | External course — current session link update | Covered | Dynamic session link updates supported via `course_access`. |
| TRR-028 | Trainer profile (management) | Partial | Missing storage for introductory content and external portfolio links. |
| TRR-029 | Trainer notifications | Covered | Lifecycle event notifications supported via `notifications`. |
| TRR-030 | Trainer package renewal (independent) | Partial | Redundant 1:1 constraint on `trainer_subscriptions.trainer_business_model_id` blocks renewals. |
| TRR-031 | Hall search and booking request (trainer) | Covered | Multi-slot booking requests supported via `hall_bookings` and `hall_booking_slots`. |
| TRR-032 | My hall bookings (trainer) | Covered | Status transitions and cancellations supported via `hall_bookings`. |
| TRR-033 | Link a Booked hall booking to an in-person course | Covered | Superseded by D1 / DEC-17 (venue details are manually typed; no FK linkage). |

---

### Detailed Findings

#### R-101 | `users.phone`
- **Reference:** TRN-002, DEC-04
- **Problem:** `users` only enforces `UNIQUE KEY (email)` and lacks any uniqueness constraint on `phone`. With the decision under DEC-04 to drop `trainee_profiles` and consolidate trainee identities into `users` scoped by `user_type`, multiple trainee accounts can register with identical phone numbers, directly violating TRN-002 ("The mobile number must not already be used by another trainee account").
- **Fix:** Add a scoped unique index on `users`: `ALTER TABLE users ADD UNIQUE KEY uq_users_phone_user_type (phone, user_type);`.
- **Severity:** Blocker

#### R-102 | `users` (missing trainee profile columns)
- **Reference:** TRN-002, TRN-022, DEC-04
- **Problem:** Dropping `trainee_profiles` under DEC-04 eliminates the storage for mandatory trainee registration fields (`city_id`, `address_text`, and `terms_accepted_at`). None of these columns currently exist on `users`, preventing the storage of trainee city affiliations, physical addresses, and terms acceptance timestamps.
- **Fix:** Alter `users` to include the migrated fields: `ALTER TABLE users ADD city_id BIGINT UNSIGNED NULL, ADD FOREIGN KEY (city_id) REFERENCES cities(id), ADD address_text TEXT NULL, ADD terms_accepted_at TIMESTAMP NULL;`.
- **Severity:** Major

#### R-103 | `trainer_profiles` / `trainer_profile_translations`
- **Reference:** TRN-009, TRR-028
- **Problem:** TRR-028 and TRN-009 mandate that the trainer profile include an introductory section (e.g., introductory video or lecture links) and external content/portfolio links. Neither `trainer_profiles` nor `trainer_profile_translations` defines columns or structures to store these external URLs or introductory copy.
- **Fix:** Add external links and introductory content fields: `ALTER TABLE trainer_profiles ADD external_links JSON NULL;` and `ALTER TABLE trainer_profile_translations ADD introductory_content TEXT NULL;`.
- **Severity:** Minor

#### R-104 | `course_access`
- **Reference:** TRN-014, TRR-011
- **Problem:** TRR-011 and TRN-014 require external courses to provide both an access link and access instructions (such as meeting passcodes, access rules, or platform prerequisites) to confirmed enrollees. `course_access` only provides `current_session_link TEXT NULL` and lacks an `instructions` field.
- **Fix:** Add the missing instructions column: `ALTER TABLE course_access ADD access_instructions TEXT NULL;`.
- **Severity:** Minor

#### R-105 | `courses` / `halls` (broken link under DEC-17)
- **Reference:** TRN-015, DEC-17, D1
- **Problem:** D1 and DEC-17 decoupled course creation from hall bookings, storing venue information exclusively as raw strings (`venue_name`, `venue_address`, `venue_map_link`, `venue_notes`) in `course_versions`. This breaks TRN-015 ("Hall details for in-person course"), which specifies navigating from course details to the structured hall profile (photos, capacity, amenities). Trainees cannot view the structured hall page because courses lack any foreign key to `halls.id`.
- **Fix:** If TRN-015 remains supported when an internal hall is used, add an optional reference `ALTER TABLE course_versions ADD hall_id BIGINT UNSIGNED NULL, ADD FOREIGN KEY (hall_id) REFERENCES halls(id);`; otherwise, formally update TRN-015 acceptance criteria to indicate that in-person courses display only raw venue text.
- **Severity:** Major

#### R-106 | `purchases.offer_id` (broken FK under DEC-18)
- **Reference:** TRN-018, DEC-18
- **Problem:** `purchases` defines `offer_id BIGINT UNSIGNED NULL` referencing `offers(id)`. Under DEC-18, the `offers` table is eliminated, and offers are defined strictly as trainer course discounts (`course_discounts`). Retaining this FK causes a broken foreign key constraint upon dropping `offers`.
- **Fix:** Replace the foreign key on `purchases`: `ALTER TABLE purchases DROP FOREIGN KEY purchases_offer_id_foreign, DROP COLUMN offer_id, ADD course_discount_id BIGINT UNSIGNED NULL, ADD FOREIGN KEY (course_discount_id) REFERENCES course_discounts(id);`.
- **Severity:** Blocker

#### R-107 | `refunds` (broken wallet credit under DEC-12)
- **Reference:** TRN-019, DEC-12
- **Problem:** DEC-12 establishes that when an admin cancels an enrollment, they may credit an optional refund directly to the trainee's wallet. However, `refunds.payment_id` is defined as `NOT NULL` (requiring a gateway payment reference) and the table has no destination column or foreign key to `wallets` or `transactions`, making in-app wallet refunds untrackable.
- **Fix:** Update `refunds` to support wallet destinations: `ALTER TABLE refunds MODIFY payment_id BIGINT UNSIGNED NULL, ADD refund_channel VARCHAR(20) NOT NULL DEFAULT 'gateway' COMMENT 'gateway, wallet', ADD wallet_transaction_id BIGINT UNSIGNED NULL, ADD FOREIGN KEY (wallet_transaction_id) REFERENCES transactions(id);`.
- **Severity:** Major

#### R-108 | `nafath_verifications.national_id`
- **Reference:** TRR-002
- **Problem:** `nafath_verifications` defines `national_id VARCHAR(50) NOT NULL` with no uniqueness constraint, and `trainer_profiles` does not store `national_id`. This allows multiple trainer accounts to undergo verification using the same national ID or Iqama, directly violating TRR-002 ("The national ID / residence number must not already be used by another registered trainer account").
- **Fix:** Prevent duplicate trainer identities by adding a verified national ID column on `trainer_profiles`: `ALTER TABLE trainer_profiles ADD national_id VARCHAR(50) NULL UNIQUE;`, populated atomically upon successful Nafath verification.
- **Severity:** Blocker

#### R-109 | `users.username`
- **Reference:** TRR-003, CTR-005, DEC-14
- **Problem:** TRR-003 and CTR-005 mandate that training centers create affiliated trainers with a `username` and password, and that affiliated trainers authenticate using their username. `users` contains only `email` and `phone`, leaving no storage or index for `username` and making affiliated trainer login impossible.
- **Fix:** Add a username column to `users`: `ALTER TABLE users ADD username VARCHAR(100) NULL, ADD UNIQUE KEY uq_users_username_user_type (username, user_type);`.
- **Severity:** Blocker

#### R-110 | `trainer_plans.commission`
- **Reference:** TRR-005, TRR-006
- **Problem:** In `trainer_plans`, the package price is defined as `commission DECIMAL(12,2) NOT NULL`. Trainer plans represent fixed-fee subscriptions (monthly or annual packages), not platform commission rates. This naming conflicts with business terminology and breaks clarity with `trainer_subscriptions.price`.
- **Fix:** Rename the column: `ALTER TABLE trainer_plans CHANGE commission price DECIMAL(12,2) NOT NULL;`.
- **Severity:** Major

#### R-111 | `courses.trainer_subscription_id`
- **Reference:** TRR-008, TRR-030
- **Problem:** `courses` contains no foreign key linking a course to `trainer_subscriptions`. For independent trainers operating under subscription packages, the system must track "courses used / allowed" (TRR-008). Relying solely on `courses.created_at` matching subscription dates produces ambiguous quota tracking whenever a trainer renews early or holds overlapping subscription records (TRR-030 / DEC-23).
- **Fix:** Add an explicit subscription attribution column: `ALTER TABLE courses ADD trainer_subscription_id BIGINT UNSIGNED NULL, ADD FOREIGN KEY (trainer_subscription_id) REFERENCES trainer_subscriptions(id);`.
- **Severity:** Major

#### R-112 | `course_discounts`
- **Reference:** TRR-013, DEC-07, DEC-08
- **Problem:** `course_discounts` lacks an index or constraint ensuring that only one discount is active for a given course version at any time. Because discounts represent the course offer (DEC-18) and offers cannot be combined (DEC-07), unconstrained concurrent active discounts create pricing ambiguity.
- **Fix:** Add an index for active discount lookups: `ALTER TABLE course_discounts ADD KEY idx_course_discounts_active (course_version_id, is_active, starts_at, ends_at);` and enforce single-active validity via application rules or trigger.
- **Severity:** Minor

#### R-113 | `course_threshold_events.date_set_at`
- **Reference:** TRR-015, TRN-026, ADM-032
- **Problem:** `course_threshold_events` records `minimum_reached_at`, `date_due_at`, and `escalated_at`, but omits `date_set_at`. When the minimum enrollment count is reached, the trainer is given an operational deadline (`date_due_at`) to set the course execution date. Without `date_set_at`, scheduled escalation workers cannot ascertain whether the trainer fulfilled the date requirement without performing redundant outer queries against mutable course tables.
- **Fix:** Add the fulfillment timestamp: `ALTER TABLE course_threshold_events ADD date_set_at DATETIME NULL AFTER date_due_at;`.
- **Severity:** Minor

#### R-114 | `trainer_subscriptions.trainer_business_model_id`
- **Reference:** TRR-030, DEC-23
- **Problem:** `trainer_subscriptions` defines `UNIQUE KEY (trainer_business_model_id)`. When an independent trainer renews their package subscription (TRR-030), this 1:1 constraint prevents storing consecutive subscription renewal terms under the trainer's existing subscription business model record, forcing redundant creation of parent `trainer_business_models` rows for every billing cycle.
- **Fix:** Drop the 1:1 unique key in favor of a standard lookup index: `ALTER TABLE trainer_subscriptions DROP INDEX trainer_business_model_id, ADD KEY idx_trainer_subscriptions_model (trainer_business_model_id);`.
- **Severity:** Major

