Core tables (initial schema):
Accounts and roles: users · roles · permissions · trainer_trainee_assignments · devices (FCM tokens) · user_sessions
Questionnaire: questionnaire_sections · questionnaire_questions · questionnaire_responses · questionnaire_answers
Trainees and requests: trainee_profiles · registration_requests (status, decision, reason) · trainer_notes
Subscriptions and money: plans · plan_features · subscriptions · subscription_pauses · coupons · payments · invoices · invoice_items · refunds · bank_transfers
Training: exercises · exercise_media · workout_programs · program_weeks · program_days · program_day_exercises · trainee_programs (versions) · workout_sessions · workout_sets · personal_records
Nutrition: foods · food_servings · meal_plans · meal_plan_days · meals · meal_items · meal_substitutes · food_logs · supplement_plans
Progress: body_measurements · progress_photos · habit_definitions · habit_logs · water_logs · sleep_logs · step_logs · weekly_checkins · trainer_feedback
Communication: conversations · conversation_participants · messages · message_attachments · meetings · meeting_attendees · availability_slots
Notifications: notifications · notification_templates · notification_preferences · notification_deliveries
Tracking and reporting: activity_logs · page_views · page_view_sessions · video_views · reports · report_snapshots · sheet_syncs
AI: ai_conversations · ai_messages · ai_settings · ai_usage
Content and settings: settings · cms_sections · cms_pages · testimonials · faqs · contact_messages · newsletter_subscribers · media
Notes
- Soft deletes for most tables.
- Large event tables (
page_views) are partitioned and aggregated periodically. - Dates are stored in UTC and displayed in Riyadh time.
- A
tenant_idcolumn is not needed now; it can be added later if the product becomes multi-coach SaaS (see Open questions).