Perfect! I've now read all 80 migration files. Let me synthesize this into a comprehensive final schema document. Based on my analysis, I'll organize the schema by domains and track all the changes that happened across migrations.

---

# Final PostgreSQL Schema for UTC Training Platform (Post-Migration 80)

## Overview

This document represents the final schema state after all 80 migrations have been executed. The system is a multi-tenant training and certification platform built on Supabase (PostgreSQL) with complex features including course management, role-based access control, certificates, referrals, and specialized tools for risk assessments and AR assistance.

---

## ENUM Types

### app_role
Values: `admin`, `manager`, `user`, `sys_admin`, `sys_manager`, `company_manager`, `company_trainer`, `delegate`

*Note*: PostgreSQL ENUM types. In MySQL, convert to VARCHAR with constraint checks.

---

## Authentication & Authorization

### profiles
**User profiles linked to auth.users**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| user_id | UUID | NO | | FK auth.users(id) ON DELETE CASCADE, UNIQUE |
| full_name | TEXT | YES | | |
| email | TEXT | YES | | |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: user_id → auth.users(id) ON DELETE CASCADE  
**Indexes**: (user_id UNIQUE)  
**RLS**: Yes - scoped by user_id  
**Triggers**: update_profiles_updated_at, on_auth_user_created (auto-create), on_profile_created_apply_pending_roles, on_profile_created_assign_delegate

---

### user_roles
**System and company-scoped role assignments**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| user_id | UUID | NO | | FK auth.users(id) ON DELETE CASCADE |
| role | app_role | NO | | ENUM: admin, manager, user, sys_admin, sys_manager, company_manager, company_trainer, delegate |
| company_id | UUID | YES | | FK training_companies(id) ON DELETE CASCADE - for company-scoped roles |

**Primary Key**: id  
**Foreign Keys**: user_id → auth.users(id) ON DELETE CASCADE; company_id → training_companies(id) ON DELETE CASCADE  
**Unique Constraints**: (user_id, role) implicitly by function, (user_id, role) with company_id for company roles  
**RLS**: Yes  
**Functions**: has_role(user_id, role), has_company_role(user_id, role, company_id), is_sys_role(user_id)

---

### admin_otp_codes
**One-time passwords for admin/sys-level login**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| email | TEXT | NO | | Email for OTP delivery |
| code | TEXT | NO | | The OTP code itself |
| expires_at | TIMESTAMPTZ | NO | | Expiration time |
| used | BOOLEAN | NO | false | Tracks if code has been used |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**RLS**: Yes - restricted, only accessible via service role (edge functions)

---

### admin_otp_trusted_devices
**Trusted device tokens that let a sys-level user skip OTP for 6h on the same browser+IP**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| user_id | UUID | NO | | The sys-level user the token belongs to |
| token_hash | VARCHAR(64) | NO | | SHA-256 hex of the raw token; raw token lives only in the user's `admin_otp_trust` cookie |
| user_agent | TEXT | YES | | Bound at issue; mismatch invalidates the token |
| ip | VARCHAR(45) | YES | | Bound at issue; mismatch invalidates the token (IPv6-sized) |
| expires_at | TIMESTAMPTZ | NO | | Issue time + 6h, no sliding refresh |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Indexes**: (user_id, token_hash)  
**RLS**: Yes - restricted, only accessible via service role

---

### delegate_otp_codes
**One-time passwords for delegate/course attendee flows**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| email | TEXT | NO | | Email for OTP delivery |
| code | TEXT | NO | | The OTP code itself |
| order_id | UUID | YES | | FK course_orders(id) (optional) |
| expires_at | TIMESTAMPTZ | NO | | Expiration time |
| used | BOOLEAN | NO | false | |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: order_id → course_orders(id)  
**Indexes**: (email)  
**RLS**: Yes - highly restricted, only service role can access

---

### pending_role_assignments
**Invitations to join as a specific role, applied when user creates profile**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| email | TEXT | NO | | Email to assign role to |
| role | app_role | NO | | The role to assign |
| company_id | UUID | YES | | FK training_companies(id) ON DELETE CASCADE (NULL for system roles) |
| created_at | TIMESTAMPTZ | NO | now() | |
| created_by | UUID | YES | | Admin who created this assignment |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**Unique Constraints**: (email, role, company_id)  
**RLS**: Yes - sys_admin only  
**Triggers**: apply_pending_role_assignments() fires when profile created

---

## Company & Organization Management

### training_companies
**Tenant/customer company accounts**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| name | TEXT | NO | | Company name |
| contact_email | TEXT | YES | | Primary contact email |
| contact_phone | TEXT | YES | | Primary contact phone |
| address | TEXT | YES | | Company address |
| notes | TEXT | YES | | Internal notes |
| registration_number | TEXT | YES | | Business registration number |
| vat_number | TEXT | YES | | VAT/tax ID |
| account_admin_name | TEXT | YES | | Admin contact name |
| accounts_contact_name | TEXT | YES | | Finance contact name |
| accounts_contact_email | TEXT | YES | | Finance contact email |
| status | TEXT | NO | 'pending' | pending, active, suspended, etc. |
| payment_terms_days | INTEGER | NO | 30 | Payment terms in days |
| credit_limit_cents | INTEGER | NO | 0 | Credit limit in pence/cents |
| credit_available_cents | INTEGER | NO | 0 | Available credit balance |
| company_type | TEXT | NO | 'customer_company' | Type of company (customer_company, partner, etc.) |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Indexes**: (company_type), (status)  
**RLS**: Yes  
**Triggers**: update_training_companies_updated_at

---

### company_branding
**White-label and customization settings per tenant**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE, UNIQUE |
| subdomain | TEXT | YES | | Subdomain for white-label site, UNIQUE |
| logo_url | TEXT | YES | | Company logo URL |
| primary_color | TEXT | YES | | Primary brand color (hex) |
| secondary_color | TEXT | YES | | Secondary brand color (hex) |
| background_color | TEXT | YES | | Background color (hex) |
| font_color | TEXT | YES | | Font/text color (hex) |
| card_color | TEXT | YES | | Card background color (hex) |
| card_font_color | TEXT | YES | '#FFFFFF' | Card text color (hex) |
| hero_image_url | TEXT | YES | | Hero section image |
| hero_video_url | TEXT | YES | | Hero section video |
| tagline | TEXT | YES | | Company tagline/motto |
| stripe_account_id | TEXT | YES | | Stripe Connect account ID (sensitive, not exposed publicly) |
| contact_email | TEXT | YES | | Support/contact email |
| contact_phone | TEXT | YES | | Support phone |
| contact_address | TEXT | YES | | Company address |
| opening_hours | TEXT | YES | | Operating hours (free text) |
| is_active | BOOLEAN | NO | false | Whether branding is published |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE (UNIQUE)  
**RLS**: Yes  
**Views**: company_branding_public (excludes stripe_account_id, SECURITY INVOKER)  
**Triggers**: update_company_branding_updated_at

---

### company_services
**Service subscriptions and feature flags per company**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| service_type | TEXT | NO | | Type of service (e.g., 'risk_assessment', 'ar_assist') |
| status | TEXT | NO | 'active' | active, inactive, expired |
| activated_at | TIMESTAMPTZ | NO | now() | When service was activated |
| expires_at | TIMESTAMPTZ | YES | | Optional expiration date |
| config | JSONB | YES | '{}' | Service configuration (free-form JSON) |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**Unique Constraints**: (company_id, service_type)  
**RLS**: Yes

---

### delegates
**Company employees/members (attendees)**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| first_name | TEXT | NO | | |
| last_name | TEXT | NO | | |
| email | TEXT | YES | | |
| phone | TEXT | YES | | |
| status | TEXT | NO | 'active' | active, inactive, suspended |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**Indexes**: (company_id), (status)  
**RLS**: Yes  
**Triggers**: update_delegates_updated_at

---

### team_members
**Non-delegate company personnel (e.g., managers, admin staff)**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | YES | | FK training_companies(id) ON DELETE CASCADE |
| first_name | TEXT | NO | | |
| last_name | TEXT | NO | | |
| email | TEXT | YES | | |
| phone | TEXT | YES | | |
| notes | TEXT | YES | | |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**RLS**: Yes  
**Triggers**: update_team_members_updated_at

---

## Courses

### courses
**Course catalog entries**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| slug | TEXT | NO | | URL-friendly identifier, UNIQUE |
| title | TEXT | NO | | Course title |
| category | TEXT | NO | | Main category |
| sub_category | TEXT | YES | | Subcategory |
| category_href | TEXT | NO | '/courses' | Category link |
| image_url | TEXT | YES | | Course image/thumbnail |
| description | TEXT | YES | | Full course description |
| who_attends | TEXT | YES | | Target audience |
| course_content | TEXT | YES | | Detailed syllabus/content |
| certification | TEXT | YES | | Certification details |
| ppe_requirements | TEXT | YES | | Personal protective equipment requirements |
| location_name | TEXT | YES | | Default location name |
| location_details | TEXT | YES | | Location details |
| facilities | TEXT | YES | | Facilities description |
| days | INTEGER | NO | 1 | Duration in days |
| capacity | INTEGER | YES | | Max attendees per session |
| level | TEXT | YES | 'All Levels' | Course level (Beginner, Intermediate, Advanced, etc.) |
| language | TEXT | YES | 'English' | Delivery language |
| price_cents | INTEGER | NO | 0 | Base price in pence/cents |
| original_price_cents | INTEGER | YES | 0 | Original price (for discounts) |
| is_featured | BOOLEAN | YES | false | Featured on homepage |
| is_active | BOOLEAN | NO | true | Whether course is published |
| has_certificate | BOOLEAN | YES | false | Whether course awards certificate |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Unique Constraints**: (slug)  
**RLS**: Yes  
**Triggers**: update_courses_updated_at

---

### course_trainers
**Mapping of trainers to courses, with optional venue override**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| trainer_id | UUID | NO | | FK trainers(id) ON DELETE CASCADE |
| venue_id | UUID | YES | | FK venues(id) - trainer's preferred venue for this course |

**Primary Key**: id  
**Foreign Keys**: course_id → courses(id) ON DELETE CASCADE; trainer_id → trainers(id) ON DELETE CASCADE; venue_id → venues(id)  
**Unique Constraints**: (course_id, trainer_id, venue_id)  
**RLS**: Yes

---

### course_prerequisites
**Prerequisite courses that must be completed before booking**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE - the dependent course |
| prerequisite_course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE - must be completed first |
| is_mandatory | BOOLEAN | NO | true | Whether prerequisite is mandatory or recommended |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: course_id → courses(id) ON DELETE CASCADE; prerequisite_course_id → courses(id) ON DELETE CASCADE  
**Unique Constraints**: (course_id, prerequisite_course_id)  
**RLS**: Yes

---

### tenant_featured_courses
**Per-company featured course selections**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| display_order | INTEGER | NO | 0 | Sort order |
| is_featured | BOOLEAN | NO | false | |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE; course_id → courses(id) ON DELETE CASCADE  
**Unique Constraints**: (company_id, course_id)  
**RLS**: Yes

---

### elearning_enrolments
**E-learning course progress tracking**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| user_id | UUID | NO | | FK auth.users(id) ON DELETE CASCADE |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| progress_percent | INTEGER | NO | 0 | 0-100 |
| status | TEXT | NO | 'not_started' | not_started, in_progress, completed |
| started_at | TIMESTAMPTZ | YES | | When user started |
| completed_at | TIMESTAMPTZ | YES | | When user completed |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: user_id → auth.users(id) ON DELETE CASCADE; course_id → courses(id) ON DELETE CASCADE  
**Unique Constraints**: (user_id, course_id)  
**RLS**: Yes

---

## Venues & Facilities

### venues
**Training locations**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| name | TEXT | NO | | Venue name |
| address | TEXT | YES | | Street address |
| city | TEXT | YES | | City |
| state | TEXT | YES | | State/county/region |
| postcode | TEXT | YES | | Postal code |
| country | TEXT | YES | 'GB' | Country code |
| capacity | INTEGER | YES | | Total venue capacity |
| company_id | UUID | YES | | FK training_companies(id) ON DELETE SET NULL - if company-specific |
| status | TEXT | NO | 'active' | active, closed, maintenance |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE SET NULL  
**RLS**: Yes  
**Triggers**: update_venues_updated_at

---

### venue_rooms
**Individual training rooms within a venue**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| venue_id | UUID | NO | | FK venues(id) ON DELETE CASCADE |
| name | TEXT | NO | | Room name |
| capacity | INTEGER | YES | | Room capacity |
| status | TEXT | NO | 'active' | active, out_of_service |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: venue_id → venues(id) ON DELETE CASCADE  
**RLS**: Yes

---

### venue_yards
**Outdoor training areas within a venue**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| venue_id | UUID | NO | | FK venues(id) ON DELETE CASCADE |
| name | TEXT | NO | | Yard name |
| capacity | INTEGER | YES | | Capacity |
| shared | BOOLEAN | NO | false | Can multiple courses run simultaneously (clash detection disabled but capacity checked) |
| status | TEXT | NO | 'active' | active, out_of_service |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: venue_id → venues(id) ON DELETE CASCADE  
**RLS**: Yes

---

### course_venue_schedules
**Day-by-day room/yard assignments for a course**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| venue_id | UUID | NO | | FK venues(id) ON DELETE CASCADE |
| resource_type | TEXT | NO | | 'room' or 'yard' (CHECK constraint) |
| resource_id | UUID | NO | | ID of room or yard (foreign key depends on resource_type) |
| day_number | INTEGER | NO | | Day 1, 2, 3, etc. |
| session | TEXT | NO | | 'am' or 'pm' (CHECK constraint) |

**Primary Key**: id  
**Foreign Keys**: course_id → courses(id) ON DELETE CASCADE; venue_id → venues(id) ON DELETE CASCADE  
**Unique Constraints**: (course_id, resource_id, day_number, session)  
**RLS**: Yes

---

### location_resource_blocks
**Blocked dates for rooms/yards (maintenance, special events, etc.)**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| venue_id | UUID | NO | | FK venues(id) ON DELETE CASCADE |
| resource_type | TEXT | NO | | 'room' or 'yard' (CHECK constraint) |
| resource_id | UUID | NO | | ID of room or yard |
| blocked_date | DATE | NO | | The blocked date |
| reason | TEXT | YES | | Reason for block |
| blocked_by | UUID | YES | | FK auth.users(id) - who created block |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: venue_id → venues(id) ON DELETE CASCADE; blocked_by → auth.users(id)  
**Unique Constraints**: (resource_type, resource_id, blocked_date)  
**Indexes**: (blocked_date), (resource_type, resource_id)  
**RLS**: Yes

---

## Trainers & Availability

### trainers
**Training instructors/coaches**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| first_name | TEXT | NO | | |
| last_name | TEXT | NO | | |
| email | TEXT | YES | | |
| phone | TEXT | YES | | |
| notes | TEXT | YES | | Internal notes |
| availability_mode | TEXT | NO | 'weekly' | weekly or custom (determines how availability is managed) |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**RLS**: Yes  
**Triggers**: update_trainers_updated_at

---

### trainer_companies
**Many-to-many: trainers affiliated with companies**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| trainer_id | UUID | NO | | FK trainers(id) ON DELETE CASCADE |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |

**Primary Key**: id  
**Foreign Keys**: trainer_id → trainers(id) ON DELETE CASCADE; company_id → training_companies(id) ON DELETE CASCADE  
**Unique Constraints**: (trainer_id, company_id)  
**RLS**: Yes

---

### trainer_availability_weekly
**Recurring weekly availability pattern for each trainer**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| trainer_id | UUID | NO | | FK trainers(id) ON DELETE CASCADE |
| day_of_week | INTEGER | NO | | 0 (Sunday) to 6 (Saturday) [CHECK constraint: 0-6] |
| is_available | BOOLEAN | NO | true | Available or not on that day |

**Primary Key**: id  
**Foreign Keys**: trainer_id → trainers(id) ON DELETE CASCADE  
**Unique Constraints**: (trainer_id, day_of_week)  
**RLS**: Yes

---

### trainer_availability_overrides
**Date-specific overrides to weekly availability**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| trainer_id | UUID | NO | | FK trainers(id) ON DELETE CASCADE |
| override_date | DATE | NO | | The date being overridden |
| is_available | BOOLEAN | NO | | true = mark as available, false = mark as unavailable |
| reason | TEXT | YES | | Reason for override |

**Primary Key**: id  
**Foreign Keys**: trainer_id → trainers(id) ON DELETE CASCADE  
**Unique Constraints**: (trainer_id, override_date)  
**RLS**: Yes

---

## Bookings & Orders

### course_bookings
**Scheduled course instances (admin/internal view)**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| trainer_id | UUID | NO | | FK trainers(id) ON DELETE CASCADE |
| start_date | DATE | NO | | Course start date |
| status | TEXT | NO | 'confirmed' | confirmed, cancelled, completed |
| duration_override | INTEGER | YES | | Override course duration (days) |
| notes | TEXT | YES | | Internal notes |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: course_id → courses(id) ON DELETE CASCADE; trainer_id → trainers(id) ON DELETE CASCADE  
**Unique Indexes**: (course_id, trainer_id, start_date)  
**Indexes**: (trainer_id, start_date)  
**RLS**: Yes  
**Triggers**: update_course_bookings_updated_at

---

### booking_resource_allocations
**Room/yard allocations for each day and session of a booking**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| booking_id | UUID | NO | | FK course_bookings(id) ON DELETE CASCADE |
| day_number | INTEGER | NO | | Day 1, 2, 3, etc. |
| session | TEXT | NO | | 'am' or 'pm' |
| resource_type | TEXT | NO | | 'room' or 'yard' |
| resource_id | UUID | NO | | ID of the room or yard |
| venue_id | UUID | NO | | FK venues(id) |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: booking_id → course_bookings(id) ON DELETE CASCADE; venue_id → venues(id)  
**Indexes**: (resource_id, venue_id), (booking_id)  
**RLS**: Yes

---

### course_orders
**Customer orders/bookings (public checkout flow)**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| course_id | UUID | NO | | FK courses(id) |
| company_id | UUID | YES | | FK training_companies(id) - if company booking |
| trainer_id | UUID | YES | | FK trainers(id) - assigned trainer (may be null) |
| venue_id | UUID | YES | | FK venues(id) - assigned venue (may be null) |
| user_id | UUID | YES | | FK auth.users(id) - if logged-in customer |
| start_date | DATE | NO | | Course start date |
| customer_name | TEXT | NO | | Customer name (for anon checkout) |
| customer_email | TEXT | NO | | Customer email (for anon checkout) |
| customer_phone | TEXT | YES | | Customer phone |
| num_delegates | INTEGER | NO | 1 | Number of attendees |
| price_cents | INTEGER | NO | | Price charged in pence/cents |
| payment_method | TEXT | NO | 'stripe' | Payment method (stripe, invoice, etc.) |
| stripe_payment_intent_id | TEXT | YES | | Stripe PaymentIntent ID |
| status | TEXT | NO | 'pending' | pending, paid, confirmed, cancelled |
| refund_cents | INTEGER | NO | 0 | Amount refunded |
| refund_reason | TEXT | YES | | Reason for refund |
| refund_status | TEXT | YES | | refund_pending, refunded, failed |
| stripe_refund_id | TEXT | YES | | Stripe refund ID |
| cancelled_at | TIMESTAMPTZ | YES | | When cancelled |
| completed_at | TIMESTAMPTZ | YES | | When course was manually marked completed |
| joining_instructions_sent_at | TIMESTAMPTZ | YES | | When joining instructions were sent |
| joining_instructions_scheduled_for | TIMESTAMPTZ | YES | | When to schedule sending joining instructions |
| confirmation_email_sent_at | TIMESTAMPTZ | YES | | When confirmation email was sent |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: course_id → courses(id); company_id → training_companies(id); trainer_id → trainers(id); venue_id → venues(id); user_id → auth.users(id)  
**Unique Indexes**: (user_id, course_id, start_date) WHERE status IN ('pending', 'paid', 'confirmed') - prevent duplicate bookings  
**Indexes**: (status), (created_at DESC), (user_id), (joining_instructions_scheduled_for) WHERE joining_instructions_sent_at IS NULL AND joining_instructions_scheduled_for IS NOT NULL  
**RLS**: Yes  
**Triggers**: update_course_orders_updated_at

---

### booking_delegates
**Individual attendees in a course order**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| order_id | UUID | NO | | FK course_orders(id) ON DELETE CASCADE |
| first_name | TEXT | NO | | |
| last_name | TEXT | NO | | |
| email | TEXT | YES | | |
| phone | TEXT | YES | | |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: order_id → course_orders(id) ON DELETE CASCADE  
**Indexes**: (order_id)  
**RLS**: Yes

---

### booking_requests
**Delegate requests to book a course, awaiting manager approval**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| delegate_user_id | UUID | NO | | User making the request |
| delegate_email | TEXT | NO | | Delegate's email |
| delegate_name | TEXT | NO | | Delegate's name |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| start_date | DATE | NO | | Requested course date |
| venue_id | UUID | YES | | FK venues(id) - requested venue (optional) |
| trainer_id | UUID | YES | | FK trainers(id) - requested trainer (optional) |
| status | TEXT | NO | 'pending' | pending, approved, rejected, cancelled |
| manager_notes | TEXT | YES | | Manager's response notes |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE; course_id → courses(id) ON DELETE CASCADE; venue_id → venues(id); trainer_id → trainers(id)  
**RLS**: Yes  
**Triggers**: update_booking_requests_updated_at

---

### course_waitlist
**Waitlist for courses when fully booked**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| start_date | DATE | NO | | Desired course date |
| user_id | UUID | YES | | FK auth.users(id) ON DELETE SET NULL - if logged in |
| contact_name | TEXT | NO | | Name for notification |
| contact_email | TEXT | NO | | Email for notification |
| contact_phone | TEXT | YES | | Phone for notification |
| num_delegates | INTEGER | NO | 1 | Number of attendees wanted |
| company_id | UUID | YES | | FK training_companies(id) ON DELETE SET NULL - if company booking |
| status | TEXT | NO | 'waiting' | waiting, notified, cancelled, converted |
| notified_at | TIMESTAMPTZ | YES | | When customer was notified of availability |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: course_id → courses(id) ON DELETE CASCADE; user_id → auth.users(id) ON DELETE SET NULL; company_id → training_companies(id) ON DELETE SET NULL  
**RLS**: Yes

---

### date_change_requests
**Customer requests to reschedule course dates**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| order_id | UUID | NO | | FK course_orders(id) ON DELETE CASCADE |
| requested_date | DATE | NO | | New desired date |
| reason | TEXT | YES | | Reason for change request |
| status | TEXT | NO | 'pending' | pending, approved, rejected, cancelled |
| trainer_id | UUID | YES | | FK trainers(id) - proposed trainer for new date |
| admin_notes | TEXT | YES | | Admin's response notes |
| requested_by | UUID | YES | | FK auth.users(id) - who requested the change |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: order_id → course_orders(id) ON DELETE CASCADE; trainer_id → trainers(id); requested_by → auth.users(id)  
**RLS**: Yes  
**Triggers**: update_date_change_requests_updated_at

---

## Discounts & Referrals

### discount_codes
**Promotional and referral discount codes**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| code | TEXT | NO | | Code (e.g., 'JAMES100'), UNIQUE |
| discount_type | TEXT | NO | 'percent' | 'percent' or 'fixed' (CHECK constraint) |
| discount_percent | INTEGER | NO | 0 | Percentage off (0-100) if discount_type='percent' |
| discount_amount_cents | INTEGER | NO | 0 | Fixed amount in pence/cents if discount_type='fixed' |
| is_active | BOOLEAN | NO | true | Whether code is usable |
| max_uses | INTEGER | YES | | Total uses allowed (NULL = unlimited) |
| times_used | INTEGER | NO | 0 | Times already used |
| valid_from | TIMESTAMPTZ | YES | now() | Code becomes valid from this date |
| valid_until | TIMESTAMPTZ | YES | | Code expires at this date |
| scope | TEXT | NO | 'general' | 'general', 'company', 'user', 'referral' (CHECK constraint) |
| company_id | UUID | YES | | FK training_companies(id) ON DELETE SET NULL - if company-specific |
| user_id | UUID | YES | | FK to user if user-specific or referral reward |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE SET NULL; user_id → auth.users(id) (implicit)  
**Unique Constraints**: (code)  
**Indexes**: (company_id), (user_id), (scope)  
**RLS**: Yes  
**Functions**: validate_discount_code(_code, _user_id, _company_id) - validates code against date range, usage limits, and scope

---

### referrals
**Referral program tracking (delegates earn credit for referred customers)**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| referrer_user_id | UUID | NO | | User giving the referral |
| referral_code | TEXT | NO | | Unique code (UNIQUE) |
| referred_email | TEXT | YES | | Email of referred customer |
| referred_user_id | UUID | YES | | FK to user if referred customer signed up |
| referred_order_id | UUID | YES | | FK course_orders(id) - the referral bonus is tied to order completion |
| status | TEXT | NO | 'pending' | pending (pending conversion), converted, cancelled |
| reward_credit_cents | INTEGER | NO | 0 | Reward earned (15% of order price) |
| reward_applied | BOOLEAN | NO | false | Whether reward was applied (discount code created) |
| created_at | TIMESTAMPTZ | NO | now() | |
| converted_at | TIMESTAMPTZ | YES | | When referral was converted to paying customer |

**Primary Key**: id  
**Foreign Keys**: referred_order_id → course_orders(id)  
**Unique Constraints**: (referral_code)  
**Indexes**: (referral_code), (referrer_user_id)  
**RLS**: Yes  
**Triggers**: process_referral_reward() fires when certificate created - calculates 15% of order and creates discount code

---

## Certificates

### certificates
**Training completion certificates**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| delegate_email | TEXT | NO | | Certificate recipient email |
| course_id | UUID | YES | | FK courses(id) ON DELETE SET NULL - course completed |
| order_id | UUID | YES | | FK course_orders(id) ON DELETE SET NULL - the order/booking |
| company_id | UUID | YES | | FK training_companies(id) ON DELETE SET NULL - company |
| certificate_url | TEXT | YES | | URL to stored certificate PDF |
| certificate_number | TEXT | YES | | Unique certificate number |
| is_external | BOOLEAN | NO | false | Whether from external provider (not our course) |
| provider_name | TEXT | YES | | External provider name if is_external=true |
| qualification_name | TEXT | YES | | Qualification name (internal or external) |
| issued_at | TIMESTAMPTZ | NO | now() | When certificate was issued |
| expected_by | TIMESTAMPTZ | YES | | SLA deadline for certificate generation |
| expires_at | TIMESTAMPTZ | YES | | Certificate expiration date (if applicable) |
| status | TEXT | NO | 'active' | active, expired, revoked, pending |
| notes | TEXT | YES | | Admin notes |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: course_id → courses(id) ON DELETE SET NULL; order_id → course_orders(id) ON DELETE SET NULL; company_id → training_companies(id) ON DELETE SET NULL  
**RLS**: Yes  
**Triggers**: update_certificates_updated_at, process_referral_reward (fires on INSERT)  
**Storage**: 'certificates' bucket (private)

---

## Form Submissions

### delegate_form_submissions
**Health & safety and assessment form submissions from delegates**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| delegate_email | TEXT | NO | | Who submitted |
| order_id | UUID | YES | | FK course_orders(id) - which booking |
| course_id | UUID | YES | | FK courses(id) - which course |
| form_type | TEXT | NO | | 'TD-02', 'TD-07', 'TD-29' (CHECK constraint) |
| form_data | JSONB | NO | '{}' | Form responses (free-form JSON) |
| candidate_signature | TEXT | YES | | Base64 signature image or URL |
| assessor_signature | TEXT | YES | | Assessor's signature |
| submitted_at | TIMESTAMPTZ | NO | now() | When form was submitted |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: order_id → course_orders(id); course_id → courses(id)  
**Indexes**: (delegate_email), (order_id)  
**RLS**: Yes

---

## Job Roles & Competencies

### job_roles
**Job positions within a company requiring specific certifications**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| title | TEXT | NO | | Job title (e.g., 'Safety Officer') |
| description | TEXT | YES | | Role responsibilities and requirements |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**RLS**: Yes

---

### job_role_requirements
**Courses/certifications required for a job role**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| job_role_id | UUID | NO | | FK job_roles(id) ON DELETE CASCADE |
| course_id | UUID | NO | | FK courses(id) ON DELETE CASCADE |
| is_mandatory | BOOLEAN | NO | true | Mandatory vs. recommended |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: job_role_id → job_roles(id) ON DELETE CASCADE; course_id → courses(id) ON DELETE CASCADE  
**Unique Constraints**: (job_role_id, course_id)  
**RLS**: Yes

---

### delegate_job_roles
**Assignment of delegates to job roles**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| delegate_id | UUID | NO | | FK delegates(id) ON DELETE CASCADE |
| job_role_id | UUID | NO | | FK job_roles(id) ON DELETE CASCADE |
| assigned_at | TIMESTAMPTZ | NO | now() | When assigned to this role |

**Primary Key**: id  
**Foreign Keys**: delegate_id → delegates(id) ON DELETE CASCADE; job_role_id → job_roles(id) ON DELETE CASCADE  
**Unique Constraints**: (delegate_id, job_role_id)  
**RLS**: Yes

---

## Safety & Risk Management (LockTel)

### risk_assessments
**Live site safety inspections using video/AI analysis**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| user_id | UUID | YES | | FK auth.users(id) ON DELETE SET NULL - inspector |
| title | TEXT | NO | 'Live Site Inspection' | Assessment title |
| environment_type | TEXT | NO | 'general' | Type of environment (general, construction, industrial, etc.) |
| location | TEXT | YES | | Site location description |
| status | TEXT | NO | 'in_progress' | in_progress, completed, archived |
| overall_risk_level | TEXT | YES | | Overall risk assessment (low, medium, high) |
| scene_summary | TEXT | YES | | Summary of findings |
| total_frames_analysed | INTEGER | YES | 0 | Number of video frames analyzed |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE; user_id → auth.users(id) ON DELETE SET NULL  
**RLS**: Yes  
**Triggers**: update_risk_assessments_updated_at  
**Storage**: 'risk-frames' bucket (private)

---

### risk_hazards
**Individual hazards identified in frames**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| assessment_id | UUID | NO | | FK risk_assessments(id) ON DELETE CASCADE |
| frame_number | INTEGER | NO | 1 | Video frame number |
| frame_screenshot_url | TEXT | YES | | URL to screenshot in storage |
| hazard_description | TEXT | NO | | What hazard was found |
| hazard_category | TEXT | NO | | Category (fall, electrical, confined_space, etc.) |
| severity | TEXT | NO | | low, medium, high, critical |
| likelihood | TEXT | NO | | low, medium, high |
| risk_score | INTEGER | NO | 0 | Calculated risk score |
| control_measures | TEXT | YES | | Recommended controls |
| confidence | NUMERIC(3, 2) | YES | 0.0 | AI confidence 0.00-1.00 |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: assessment_id → risk_assessments(id) ON DELETE CASCADE  
**RLS**: Yes

---

## AR Mentoring (LockTel)

### ar_assist_sessions
**Remote AR-assisted mentoring/training sessions**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| engineer_name | TEXT | NO | '' | Field engineer/technician name |
| job_reference | TEXT | YES | | Job/project reference |
| mentor_user_id | UUID | YES | | FK auth.users(id) ON DELETE SET NULL - remote mentor |
| mentor_name | TEXT | YES | | Mentor's name (for display) |
| status | TEXT | NO | 'requested' | requested, active, completed, cancelled |
| started_at | TIMESTAMPTZ | YES | | Session start time |
| ended_at | TIMESTAMPTZ | YES | | Session end time |
| duration_seconds | INTEGER | YES | | Session duration |
| recording_url | TEXT | YES | | Recording storage URL |
| notes | TEXT | YES | | Session notes |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE; mentor_user_id → auth.users(id) ON DELETE SET NULL  
**RLS**: Yes

---

## Content & Marketing

### tenant_posts
**Blog/news posts per company**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| title | TEXT | NO | | Post title |
| slug | TEXT | NO | | URL slug |
| content | TEXT | NO | '' | Post body (markdown/HTML) |
| image_url | TEXT | YES | | Featured image |
| published_at | TIMESTAMPTZ | YES | | When published (NULL = draft) |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**Unique Constraints**: (company_id, slug)  
**RLS**: Yes

---

### tenant_testimonials
**Customer testimonials/reviews per company**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| author_name | TEXT | NO | | Testimonial author |
| author_role | TEXT | YES | | Author's job/role |
| content | TEXT | NO | | Testimonial text |
| rating | INTEGER | NO | 5 | 1-5 star rating (CHECK: 1-5) |
| is_visible | BOOLEAN | NO | true | Whether to display publicly |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**RLS**: Yes

---

## Notifications & Communication

### notifications
**In-app notifications for users**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| user_id | UUID | NO | | FK auth.users(id) ON DELETE CASCADE |
| title | TEXT | NO | | Notification title |
| message | TEXT | YES | | Notification message |
| type | TEXT | NO | 'info' | info, warning, success, error, reward |
| is_read | BOOLEAN | NO | false | Read status |
| link | TEXT | YES | | Optional link (e.g., '/courses') |
| entity_type | TEXT | YES | | Entity affected (order, course, etc.) |
| entity_id | TEXT | YES | | ID of entity |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: user_id → auth.users(id) ON DELETE CASCADE  
**RLS**: Yes

---

### support_conversations
**Support ticket conversations**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| user_id | UUID | NO | | Who initiated |
| title | TEXT | NO | 'New Conversation' | Conversation title/subject |
| messages | JSONB | NO | '[]' | Array of message objects [{user, text, timestamp}, ...] |
| status | TEXT | NO | 'open' | open, in_progress, resolved, closed |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE; user_id (implicit FK to users)  
**RLS**: Yes

---

## Audit & Logging

### activity_log
**Audit trail of all significant actions**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| user_id | UUID | YES | | Who performed the action |
| user_email | TEXT | YES | | User's email (denormalized) |
| action | TEXT | NO | | create, update, delete, view, download, etc. |
| entity_type | TEXT | NO | | Table/entity name (course, order, certificate) |
| entity_id | TEXT | YES | | ID of entity affected |
| details | JSONB | YES | '{}' | Additional details (old values, new values, etc.) |
| ip_address | TEXT | YES | | IP address of requester |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Indexes**: (created_at DESC), (entity_type, entity_id), (user_id)  
**RLS**: Yes

---

### service_usage_log
**Logging for metered services (risk assessments, AR sessions, etc.)**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE |
| service_type | TEXT | NO | | Service being used (risk_assessment, ar_assist, etc.) |
| user_id | UUID | YES | | User performing action |
| action | TEXT | NO | | start, end, pause, cancel, etc. |
| input_data | JSONB | YES | '{}' | Input parameters (free-form) |
| output_data | JSONB | YES | '{}' | Output/results (free-form) |
| created_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE  
**RLS**: Yes

---

## Stripe & Payment Configuration

### company_stripe_config
**Stripe API keys for per-tenant payment processing**

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | UUID | NO | gen_random_uuid() | PK |
| company_id | UUID | NO | | FK training_companies(id) ON DELETE CASCADE, UNIQUE |
| stripe_publishable_key | TEXT | NO | | Stripe publishable key (safe to expose) |
| stripe_secret_key | TEXT | NO | | Stripe secret key (SENSITIVE - never expose) |
| created_at | TIMESTAMPTZ | NO | now() | |
| updated_at | TIMESTAMPTZ | NO | now() | |

**Primary Key**: id  
**Foreign Keys**: company_id → training_companies(id) ON DELETE CASCADE (UNIQUE)  
**RLS**: Yes - sys_admin only  
**Triggers**: update_company_stripe_config_updated_at

---

## Storage Buckets

| Bucket ID | Public | Purpose | Notes |
|-----------|--------|---------|-------|
| company-assets | false | Branding images, logos, hero assets | Private, authenticated + sys_admin write |
| certificates | false | Certificate PDFs | Private, sys_admin upload, users view own |
| risk-frames | false | Risk assessment screenshots | Private, authenticated upload, scoped read |

---

## Database-Level Functions & Views

### Functions (Security Definer)

| Function | Parameters | Returns | Purpose |
|----------|-----------|---------|---------|
| has_role | _user_id UUID, _role app_role | BOOLEAN | Check if user has role |
| has_company_role | _user_id UUID, _role app_role, _company_id UUID | BOOLEAN | Check if user has company-scoped role |
| is_sys_role | _user_id UUID | BOOLEAN | Check if user is sys_admin or sys_manager (restricted - does NOT include old admin/manager roles) |
| update_updated_at_column | (trigger fn) | TRIGGER | Auto-update updated_at timestamp on UPDATE |
| handle_new_user | (trigger fn) | TRIGGER | Auto-create profile when user signs up via auth |
| apply_pending_role_assignments | (trigger fn) | TRIGGER | Auto-assign pending roles when profile created |
| auto_assign_delegate_role | (trigger fn) | TRIGGER | Auto-assign 'delegate' role to new users |
| validate_discount_code | _code TEXT, _user_id UUID, _company_id UUID | TABLE (id, discount_type, discount_percent, discount_amount_cents, is_valid, rejection_reason) | Server-side discount validation |
| process_referral_reward | (trigger fn) | TRIGGER | Generate reward discount code when certificate issued |
| get_course_dates_in_progress | _course_id UUID | TABLE (start_date, num_delegates, venue_id, trainer_id, venue_name) | Get upcoming booked dates for a course |
| lookup_referral_code | _code TEXT | TABLE (referral_code, referrer_user_id, status) | Public lookup for referral code |

### Views

| View Name | Purpose | Security |
|-----------|---------|----------|
| company_branding_public | Safe version of company_branding excluding stripe_account_id | SECURITY INVOKER - anon can read active branding |

---

## PostgreSQL-Specific Features to Translate for MySQL/Laravel

### ENUM Types
- **app_role**: Convert to VARCHAR with CHECK constraint or separate lookup table
- Used in: user_roles.role

### JSONB Columns
Convert to JSON (MySQL 5.7+) or LONGTEXT:
- company_services.config
- delegate_form_submissions.form_data
- support_conversations.messages
- activity_log.details
- service_usage_log.input_data, output_data

### UUID Type
Convert to CHAR(36) or use MySQL's BINARY(16) for better performance:
- All id, foreign key, and user_id columns

### Timestamp with Time Zone (TIMESTAMPTZ)
Convert to DATETIME + store timezone separately, or use TIMESTAMP (MySQL defaults to UTC):
- All created_at, updated_at, *_at columns

### Generated Columns / Computed Columns
- None present; all timestamps are explicitly set via triggers/functions

### Custom Aggregate Functions
- None present

### CHECK Constraints
Present in:
- trainer_availability_weekly.day_of_week (0-6)
- course_venue_schedules.resource_type, session
- discount_codes.discount_type, scope
- certificates.rating (1-5) in tenant_testimonials
- location_resource_blocks.resource_type

### Array/Range Types
- None present

### Full Text Search
- None configured in schema

### Partial/Filtered Indexes
Present in:
- course_orders: (user_id, course_id, start_date) WHERE status IN (...)
- course_orders: (joining_instructions_scheduled_for) WHERE joining_instructions_sent_at IS NULL AND joining_instructions_scheduled_for IS NOT NULL

### Triggers
Extensive trigger usage for:
- Automatic updated_at updates (22 tables)
- Auto-creation of profiles
- Role assignment automation
- Referral reward processing

### Row-Level Security (RLS)
- Enabled on all data tables (not auth.users, which is managed by Supabase)
- Complex policies using EXISTS subqueries and role checks
- Policies vary by sys_admin, company_manager, user, anon, and service_role

### Foreign Key Constraints
- Most use ON DELETE CASCADE for data cleanup
- Some use ON DELETE SET NULL to preserve order history
- All foreign keys present with explicit constraint names

### Indexes
- 40+ indexes across tables
- Mix of simple indexes and composite indexes
- Includes UNIQUE constraints where needed
- Filtered indexes on course_orders for status/scheduling

---

## Extension Dependencies

Supabase uses these PostgreSQL extensions (ensure available):
- **uuid-ossp** or gen_random_uuid() - For UUID generation (used throughout)
- **pgcrypto** - Typically bundled with Supabase
- **pg_stat_statements** - Performance monitoring (optional, Supabase default)

For MySQL migration:
- Use UUID() or generate UUIDs in application code
- No direct MySQL equivalents needed for the functionality; these are just helper functions

---

## Summary of Data Domains

1. **Auth & Users** (6 tables): profiles, user_roles, admin_otp_codes, admin_otp_trusted_devices, delegate_otp_codes, pending_role_assignments
2. **Organizations** (5 tables): training_companies, company_branding, company_services, delegates, team_members
3. **Courses** (8 tables): courses, course_trainers, course_prerequisites, tenant_featured_courses, elearning_enrolments, course_venue_schedules, course_waitlist
4. **Venues** (4 tables): venues, venue_rooms, venue_yards, location_resource_blocks
5. **Trainers & Availability** (4 tables): trainers, trainer_companies, trainer_availability_weekly, trainer_availability_overrides
6. **Bookings & Orders** (6 tables): course_bookings, booking_resource_allocations, course_orders, booking_delegates, booking_requests, date_change_requests
7. **Discounts & Referrals** (2 tables): discount_codes, referrals
8. **Certificates** (1 table): certificates
9. **Form Submissions** (1 table): delegate_form_submissions
10. **Competencies** (3 tables): job_roles, job_role_requirements, delegate_job_roles
11. **Safety & Risk** (2 tables): risk_assessments, risk_hazards
12. **AR Mentoring** (1 table): ar_assist_sessions
13. **Content & Marketing** (2 tables): tenant_posts, tenant_testimonials
14. **Notifications** (2 tables): notifications, support_conversations
15. **Audit & Logs** (2 tables): activity_log, service_usage_log
16. **Payments** (1 table): company_stripe_config

**Total: 50+ tables, highly relational, multi-tenant architecture with comprehensive RLS and audit trails.**

---

## Critical Notes for Laravel Migration

1. **UUID as Primary Keys**: Use Laravel's uuid() or ulid() helpers. Configure models accordingly.

2. **Timestamps**: Migrate TIMESTAMPTZ to DATETIME. Consider storing timezone info separately if needed.

3. **JSONB**: Store as JSON in MySQL. Migrate functions to Eloquent accessors/mutators or JSON query builders.

4. **ENUM**: Replace with VARCHAR + validation, or use separate lookup tables for better flexibility.

5. **RLS Policies**: Implement via Laravel middleware, policies, and scopes. Create a policy class per feature.

6. **Triggers**: Replace with:
   - Eloquent observers for updated_at auto-update
   - Event listeners for complex logic (profile creation, role assignment, referral processing)
   - Queued jobs for async operations

7. **Computed Indexes**: Ensure all indexes are replicated in migrations; don't rely on auto-discovery.

8. **Service Roles**: Implement equivalent via Laravel's service account or sudo-style logic in application code (not database-level).

9. **Storage**: Map Supabase buckets to Laravel's Storage facade (S3, local, etc.).

10. **Tenancy**: Implement via package (Laravel Tenancy, Spatie Multitenancy) or custom middleware scoping.

---

End of Schema Documentation.