MENTORSHIP PLATFORM

Business Requirements Document (BRD)

Version 1.0 · Production-Ready Specification

Date: April 19, 2026

Platform Type	Detail
Inspired By	ADPList (sessions) · Skool (communities) · Udemy/Coursera (courses)
User Roles	Mentor · Mentee · Admin (a user can hold both Mentor and Mentee roles)
Auth Method	OAuth (Google, LinkedIn) + Email/Password
Payment Model	Upfront payment with automatic refund on mentor cancellation
Primary Tech Scope	Database design · Backend algorithms · Frontend UI integration
Document Purpose	Full BRD for development team + AI code generation input

Table of Contents

1. Executive Summary

2. Locked Architecture Decisions

3. User Roles & Registration

4. Mentor Profile & Verification

5. Session System — Full Specification

6. Availability & Slot Generation Algorithm

7. Follow-Up Session System

8. Payment & Refund System

9. Community System

10. Course System

11. Messaging & Chat System

12. Notification System

13. Database Schema — Complete

14. Key Indexes & Performance

15. Frontend Integration Guide

16. Implementation Roadmap

1. Executive Summary

This document is the complete Business Requirements Document (BRD) for a mentorship marketplace platform. It covers every functional domain — user registration, mentor verification, session booking, follow-up scheduling, payments, communities, courses, and messaging — with fully locked architectural decisions ready for direct implementation.

The platform operates as a two-sided marketplace where mentors offer paid or free sessions, create subscription-based communities, and publish standalone or community-gated courses. Mentees discover mentors, book sessions, join communities, and enroll in courses. A user may simultaneously hold both a mentor and mentee profile, enabling peer-to-peer learning at any level.

1.1 Core Platform Pillars

Pillar	Inspired By	Description
1:1 Sessions	ADPList	Mentors set availability; mentees book fixed-price sessions with follow-up bundles
Communities	Skool	Mentor-owned spaces with subscriptions, XP levelling, feeds, and gated courses
Courses	Udemy / Coursera	Standalone or community-linked courses with per-audience pricing
Messaging	LinkedIn	Connection-request model; in-chat calendar sharing and appointment booking

2. Locked Architecture Decisions

The following decisions are final. They must not be re-evaluated during implementation without a formal change request. These eliminate all ambiguity flagged in pre-build review.

2.1 Slot Generation

✅ Strategy: Precompute slots into the database

✅ Horizon: 3 months ahead at all times

✅ Refresh Job: Nightly cron — extends horizon, deactivates slots past vacation ranges

✅ On Edit: Mentor edits availability → unbooked future slots are soft-deleted and regenerated; confirmed/paid bookings are preserved and mentor is notified

2.2 Timezone Handling

✅ Storage: All datetimes stored in UTC in the database

✅ Mentor Input: Mentor selects their timezone once on profile; all availability rules are interpreted in that timezone before converting to UTC for storage

✅ Display: Frontend converts UTC to viewer's local timezone using the browser's Intl API

✅ Slot Label: Slots shown in both mentor's timezone and viewer's local timezone on booking UI

2.3 Slot Duration & Granularity

✅ Model: Dynamic per session type — each session type defines its own duration (e.g. 30 min, 60 min, 90 min)

✅ Slot Generation: Slots are generated using session type duration + buffer. No fixed grid.

✅ Buffer: Applied AFTER each session. Default 15 minutes. Configurable per session type (0, 10, 15, 30 min options).

2.4 Booking Hold / Race Condition Protection

✅ Hold Duration: 10 minutes from the moment the mentee enters the checkout flow

✅ Mechanism: slot_holds table with expires_at. Cron or DB trigger releases expired holds every 60 seconds.

✅ UX: Slot shows as 'Being booked' to other users during hold. Countdown timer shown to current user in checkout.

✅ On Expiry: Hold released → slot becomes bookable again. User shown a 'Slot expired' message.

2.5 Follow-Up Window

✅ Default Interval: Mirrors the primary session's recurrence. Weekly session → weekly follow-ups. Monthly → monthly. One-time regular → mentor sets a default follow-up interval on the session type.

✅ Mentor Override: Mentor can override the default follow-up interval per session type (e.g. 'every 5 days' instead of weekly).

✅ Flexibility: Flexible within the interval ± 2 days. E.g. weekly default means mentee can pick any slot between Day 5 and Day 9 after the primary.

✅ Selection: Mentee selects all follow-up dates at booking time before payment. All slots are soft-reserved for 10 minutes during checkout.

2.6 Slot Invalidation Priority Order

Priority	Rule Type	Behaviour
1 (highest)	Vacation / Off-days	Blocks all slots in range. Existing unbooked slots deactivated. Mentor notified of any booked slots in range.
2	One-time custom overrides	Mentor can add or block a specific date/time that overrides recurring rules.
3	Recurring rules	Weekly/monthly patterns generate the baseline slots.
4 (lowest)	Regular weekly availability	Default Mon–Fri hours used when no recurring rule applies.

2.7 Capacity & Waitlist

✅ Overbooking: Not allowed. Slot capacity is strictly enforced.

✅ Waitlist: Not implemented in v1. Mentee must re-check availability.

✅ Group Sessions: Supported via session_types.max_capacity > 1. Each booking claims one capacity unit.

2.8 Pricing Model

✅ Session Pricing: Fixed price per session type. Set by mentor. No hourly rate calculation.

✅ Free Sessions: Supported. is_free = TRUE bypasses payment flow entirely.

✅ Follow-Up Pricing: Follow-ups included in the original session price. No additional charge.

✅ Ad-hoc Follow-Ups: Mentor-initiated after session. Mentor sets price (free or paid). Separate payment flow.

3. User Roles & Registration

3.1 User Roles

Role	How Obtained	Access
Mentee	Default on registration	Browse mentors, book sessions, join communities, enroll in courses, chat
Mentor	Applied for + admin approved	Everything Mentee has + create sessions, set availability, create communities, publish courses
Admin	System-assigned	Full platform management, mentor approval, content moderation, refund management
Mentor + Mentee	Automatic when mentor is approved	Mentor gets a mentee_profile automatically. Can switch dashboards via toggle.

3.2 Registration Flow

1. User lands on platform → clicks Register

2. Chooses OAuth provider: Google, LinkedIn, or Email/Password

3. OAuth callback creates users row and oauth_accounts row

4. User directed to onboarding: chooses 'Join as Mentor' or 'Join as Mentee'

5. Mentee path: completes mentee questionnaire → mentee_profile created → redirected to Mentee Dashboard

6. Mentor path: completes mentor questionnaire → uploads verification docs → mentor_profile created with approval_status = pending → redirected to Mentor Dashboard with 'Pending Approval' banner

7. Admin reviews mentor application → approves or rejects with reason

8. On approval: mentor_profile.approval_status = approved; mentee_profile auto-created for this user; both dashboards accessible

3.3 Onboarding Questionnaire Fields

Field	Mentee	Mentor	Notes
Interests / Skills	✅	✅	Multi-select tag list
How did you find us	✅	✅	Dropdown: Social, Friend, Search, Ad, Other
Current role / job title	✅	✅	Free text
Goals (what you want to achieve)	✅	❌	Free text for mentees
Years of experience	❌	✅	Number input for mentors
Why do you want to mentor	❌	✅	Short essay for mentor applications
Areas of expertise	❌	✅	Multi-select tag list
Legal name	❌	✅	Required for KYC
Legal birth year	❌	✅	Required for KYC
Legal profession	❌	✅	Official job title
Company / Institution	❌	✅	Current employer
ID / Passport upload	❌	✅	File upload for verification
Certifications	❌	✅ (optional)	Title, issuing body, year, file upload

3.4 Email Verification Flow

1. User registers via email/password → system generates a random 64-character token, SHA-256 hashes it, and stores the hash + expiry (60 min) on the users table
2. System sends email notification with link: FRONTEND_URL/auth/verify-email?token={plain_token}
3. User clicks link → frontend sends POST /api/v1/verify-email with { token: "..." }
4. Backend hashes the received token with SHA-256, looks up the user by hash, validates expiry, marks email_verified_at
5. On success: returns user data + auth token → user is logged in
6. Resend cooldown: 60 seconds between resend requests (calculated from token creation time)

📌 The plain token is never stored. Only the SHA-256 hash is persisted. Token expiry is 60 minutes by default.

3.5 Two-Factor Authentication (2FA)

The platform supports three 2FA methods, backup codes, trusted devices, and passkeys/WebAuthn. 2FA is optional and user-configurable.

Supported 2FA Methods

Method	Enum Value	How It Works
Email OTP	email	6-digit code sent via email notification
SMS OTP	sms	6-digit code sent via Vonage SMS (requires verified phone)
TOTP (Authenticator App)	totp	Time-based one-time password via Google Authenticator, Authy, etc.

2FA Login Flow

1. User submits email + password → credentials validated
2. If X-Trusted-Device header present → check trusted device token → if valid, skip 2FA
3. If 2FA enabled → return requires_two_factor: true + two_factor_method + a pending token (Sanctum token with only two-factor-challenge ability, expires in 10 min)
4. For email/sms methods: system auto-sends verification code
5. User submits code via POST /api/v1/2fa/challenge (authenticated with pending token)
6. On success: pending token deleted → full token issued → user fully authenticated
7. Optionally: user requests trust_device: true → trusted device token returned in response

Pending Token Mechanism

The pending token is a Sanctum token with limited abilities:

• Created with: $user->createToken('2fa-pending', ['two-factor-challenge'], now()->addMinutes(10))
• The EnsureTwoFactorCompleted middleware blocks pending tokens from accessing regular routes (returns 403)
• Challenge routes (/api/v1/2fa/challenge/*) only require auth:sanctum (no 2fa.completed middleware)

TOTP Setup Flow

1. POST /api/v1/2fa/totp/setup → returns secret + qr_code_url
2. User scans QR code in authenticator app
3. POST /api/v1/2fa/totp/confirm with current TOTP code → validates the secret is working
4. POST /api/v1/2fa/enable with method: "totp" → 2FA activated

Backup Codes

• 10 random 8-character codes generated when 2FA is first enabled
• Each code is SHA-256 hashed before storage
• Codes are single-use (marked with used_at timestamp)
• Regeneration replaces all existing codes
• Used via POST /api/v1/2fa/challenge/backup during 2FA challenge

Trusted Devices

• Token-based (SHA-256 hashed), sent via X-Trusted-Device HTTP header
• Expire after 30 days (configurable)
• Max 10 devices per user (oldest removed when limit reached)
• User can list and revoke devices via setup endpoints

Phone Verification (for SMS 2FA)

Phone must be verified before enabling SMS 2FA:

1. POST /api/v1/phone/update with phone_number → sends verification SMS
2. POST /api/v1/phone/verify with code → marks phone as verified

Passkeys / WebAuthn

Full passkey support using web-auth/webauthn-lib:

• Registration: POST /passkeys/register/options → POST /passkeys/register
• Authentication (public, no auth required): POST /passkeys/authenticate/options → POST /passkeys/authenticate
• Successful passkey auth issues a full Sanctum token directly (bypasses 2FA)

2FA Database Tables

Table	Purpose
user_two_factor_codes	SHA-256 hashed OTP codes with method, expiry, attempt count
user_backup_codes	SHA-256 hashed backup codes with used_at tracking
user_trusted_devices	SHA-256 hashed device tokens with expiry, device name, IP
user_passkeys	WebAuthn credential IDs, public keys, sign counts

Security Measures

• All OTP codes and tokens are SHA-256 hashed before storage
• Max 5 failed OTP attempts before lockout
• OTP codes expire after 10 minutes
• Pending tokens expire after 10 minutes
• Trusted device tokens expire after 30 days
• two_factor_secret (TOTP) is stored with Laravel's encrypted cast

4. Mentor Profile & Verification

4.1 Mentor Approval States

PENDING Awaiting admin review	APPROVED Active on platform	REJECTED Reason provided	SUSPENDED Admin action

📌 A mentor with status = pending can log in and see their dashboard but does not appear in public search. Their sessions cannot be booked.

4.2 Mentor Profile Components

• Public profile: avatar, headline, bio, skills, expertise areas, languages, average rating, total sessions

• Verification documents: stored securely, visible to admins only

• Certifications: publicly visible title and issuing body; document visible to admin only

• Session types: listed on public profile once approved

• Availability calendar: publicly visible (available/unavailable only — specific booking details are private)

• Community links: communities the mentor owns

• Course listings: published courses by this mentor

5. Session System — Full Specification

The session system is the core monetisation engine of the platform. It supports three session models, a bundled follow-up system, and a strict booking lifecycle with payment escrow and automatic refunds.

5.1 Session Types (Mentor-Defined Templates)

A session type is a reusable template created by the mentor. Each session type generates its own availability slots.

Field	Type	Description
title	VARCHAR(255)	e.g. 'Resume Review', 'Mock Interview', '1:1 Coaching'
description	TEXT	What the session covers
session_model	ENUM	regular
duration_minutes	INT	30
buffer_after_minutes	INT	0
is_free	BOOLEAN	Free or paid session
price	DECIMAL	Fixed price set by mentor; null if free
currency	VARCHAR	USD, GBP, BDT, etc.
max_capacity	INT	1 = 1-on-1; >1 = group session
follow_up_count	INT	Number of follow-up sessions included (0 = none)
follow_up_interval_days	INT	Default interval between follow-ups in days; null = mirrors recurrence
follow_up_interval_flexibility_days	INT	±N days flexibility around the interval (default: 2)
is_active	BOOLEAN	Mentor can deactivate without deleting

5.2 Three Session Models

Model A: Regular Weekly Availability

Mentor defines which days of the week they are available and the time ranges for each day. Multiple time blocks per day are supported.

Field	Example
Available days	Monday, Thursday, Friday
Monday hours	09:00–12:00 and 19:00–00:00
Thursday hours	10:00–14:00
Friday hours	09:00–17:00
Slot generation	System generates slots for each time block using duration + buffer

📌 Regular availability is the baseline. It is overridden by vacation blocks and one-time overrides (see Section 6).

Model B: Recurring Sessions

A specific session recurs on a schedule. The mentor defines the recurrence rule and the system pre-generates slots 3 months ahead.

Recurrence Type	Example	iCal RRULE
Every N days	Every 3 days	FREQ=DAILY;INTERVAL=3
Every week (specific day)	Every Monday	FREQ=WEEKLY;BYDAY=MO
Every N weeks	Every 2nd Wednesday	FREQ=WEEKLY;INTERVAL=2;BYDAY=WE
Specific day of month	10th of each month	FREQ=MONTHLY;BYMONTHDAY=10
Every N months	Every 2 months on the 1st	FREQ=MONTHLY;INTERVAL=2;BYMONTHDAY=1

📌 Recurrence rules are stored as iCal RRULE strings. A well-tested open-source library (rrule.js on frontend, python-dateutil on backend) handles expansion into actual dates.

Model C: One-Time Sessions

Mentor manually creates a single availability block for a specific date and time. No recurrence. Used for special events, workshops, or one-off availability.

5.3 Session Booking Lifecycle

REQUESTED Mentee submits	CONFIRMED Mentor accepts	PAID Payment complete	COMPLETED Session done	CANCELLED Either party	REFUNDED Auto on cancel

Transition	Trigger	System Action
→ REQUESTED	Mentee submits booking + selects follow-up dates	Slot hold created (10 min); mentor notified
→ CONFIRMED	Mentor accepts request	Hold extended until payment deadline (24 hrs)
REQUESTED → CANCELLED	Mentor declines or 24hr timeout	Hold released; slot available again; mentee notified
→ PAID	Mentee completes payment	Payment escrowed; all selected follow-up slots reserved; booking locked
PAID → COMPLETED	Session end time passes; either party confirms	Escrowed payment released to mentor (minus platform fee); review prompts sent
PAID → CANCELLED (mentor)	Mentor cancels after payment	Full refund auto-triggered; platform fee waived; mentor's reliability score decremented
PAID → CANCELLED (mentee)	Mentee cancels	Refund per cancellation policy (see Section 8)

6. Availability & Slot Generation Algorithm

6.1 Data Structures

mentor_availability_rules

Column	Type	Description
id	UUID PK
mentor_id	UUID FK	Owning mentor
session_type_id	UUID FK / NULL	NULL = applies to all session types for this mentor
rule_type	ENUM	regular_weekly
day_of_week	TINYINT / NULL	0=Sun … 6=Sat; single day per rule (one rule per day for regular_weekly)
time_blocks	JSONB	Array of {start_time, end_time} in mentor's local timezone — applies to all rule types
rrule	TEXT / NULL	iCal RRULE string for recurring type
specific_date	DATE / NULL	For one_time rules
valid_from	DATE	When this rule starts being active
valid_until	DATE / NULL	NULL = no end date
is_active	BOOLEAN	Soft disable without deleting
created_at	TIMESTAMP

mentor_off_days

Column	Type	Description
id	UUID PK
mentor_id	UUID FK
off_from	DATE	Start of off period
off_until	DATE	End of off period (inclusive)
reason	VARCHAR / NULL	Optional label: 'Vacation', 'Conference', etc.
is_full_day	BOOLEAN	true = full-day block; false = partial-hour block (off_time_blocks required)
off_time_blocks	JSONB / NULL	Array of {start_time, end_time} blocks in mentor's local timezone (only when is_full_day = false; off_from must equal off_until)
is_vacation	BOOLEAN	true = vacation-type block; false = regular off-day
created_at	TIMESTAMP

availability_slots (precomputed)

Column	Type	Description
id	UUID PK
mentor_id	UUID FK
session_type_id	UUID FK	Which session type this slot belongs to
rule_id	UUID FK	Source availability rule that generated this slot
starts_at	TIMESTAMP (UTC)	Slot start
ends_at	TIMESTAMP (UTC)	Slot end (starts_at + duration_minutes)
buffer_ends_at	TIMESTAMP (UTC)	ends_at + buffer_after_minutes (next slot cannot start before this)
status	ENUM	available
held_until	TIMESTAMP / NULL	Expiry of booking hold; NULL if not held
held_by_user_id	UUID / NULL	Who holds this slot
current_capacity	INT	Booked count so far (for group sessions)
max_capacity	INT	From session_type
generated_at	TIMESTAMP	When this slot was created by the cron job

6.2 Slot Generation Algorithm

The following pseudocode defines the exact logic the backend cron job runs nightly to maintain a 3-month slot horizon.

Step	Action
1	Load all active mentor_availability_rules where valid_from ≤ NOW() + 90 days AND (valid_until IS NULL OR valid_until ≥ NOW())
2	For each rule, expand into candidate datetime slots using the mentor's local timezone: Regular weekly → one rule per day (day_of_week is a single integer); iterate dates matching that weekday within horizon; apply each time_block, generate slots. Recurring → expand rrule string for next 90 days in mentor's local timezone, apply time_blocks. One-time → specific_date + time_blocks.
3	Load all mentor_off_days overlapping the horizon for this mentor.
4	Load all existing booked/paid slots for this mentor (must not be regenerated or deleted).
5	For each candidate slot: SKIP if overlaps any off_day range (mark as vacation_blocked if slot already exists). SKIP if a booked/paid slot already occupies that time. CHECK for existing slot row with same mentor + session_type + starts_at. If exists and status = available → update generated_at. If not exists → INSERT new slot with status = available.
6	Soft-delete (status = cancelled) any available slots for this mentor that: (a) are beyond NOW() + 90 days, or (b) are no longer generated by any active rule (rule was edited or deleted).
7	Commit transaction. Log slot counts generated, skipped, and deactivated per mentor.

6.3 Slot Hold Algorithm

Step	Action
1	Mentee selects primary slot + all follow-up slots → client sends slot IDs to server
2	Server opens DB transaction: SELECT ... FOR UPDATE on all requested slot rows
3	Validate each slot: status = available AND current_capacity < max_capacity AND (held_until IS NULL OR held_until < NOW())
4	If any slot fails validation → rollback → return error with which slots are no longer available
5	If all valid → SET status = held, held_until = NOW() + 10 minutes, held_by_user_id = mentee_id for all slots
6	Create session_booking row with status = requested, linking all slot IDs
7	Commit transaction → return booking_id and checkout_session to client
8	Cron (every 60s): UPDATE availability_slots SET status = available, held_until = NULL, held_by_user_id = NULL WHERE status = held AND held_until < NOW()

6.4 Vacation Mode — Slot States Shown to Mentee

Slot State	UI Display	Mentee Can Select?	Notes
available	Open (green / default)	✅ Yes	Normal bookable slot
held	Being booked…	❌ No	Another user in checkout
booked	Already booked	❌ No	Shows as taken (not who booked)
vacation_blocked	Mentor on vacation	❌ No	Shows vacation label per decision
override_blocked	Unavailable	❌ No	Mentor manually blocked this date
cancelled	(hidden from UI)	❌ No	Deactivated slots not shown

7. Follow-Up Session System

7.1 Two Types of Follow-Ups

Type	Who Initiates	Timing	Pricing	Payment
Package Follow-Up	Mentor (defined on session type)	Selected by mentee at booking time	Included in session price	No extra charge
Ad-hoc Follow-Up	Mentor (after session completes)	Mentor proposes in chat or dashboard	Mentor sets: free or paid	Separate payment if paid

7.2 Package Follow-Up — Booking Flow

9. Mentee selects a session type with follow_up_count > 0

10. Mentee picks their primary slot from availability calendar

11. System immediately presents available follow-up slots, filtered by:

    • Must be after the primary slot

    • Must fall within the follow-up interval window (default interval ± flexibility days)

    • Must have status = available

    • Must not be already booked by another mentee

12. Mentee selects N follow-up slots (N = follow_up_count)

13. All slots (primary + follow-ups) are held simultaneously for 10 minutes

14. Mentee completes checkout (one payment covers everything)

15. On payment success: all held slots → status = booked; session_booking created with all slot_ids stored

7.3 Follow-Up Interval Logic

Session Model	Default Follow-Up Interval	Flexibility Window	Example
Regular weekly	7 days	± 2 days (Day 5–9)	Primary Monday 9am → Follow-up any slot Day 5 to Day 9 later
Recurring — weekly	7 days	± 2 days	Same as above
Recurring — monthly	30 days	± 3 days (Day 27–33)	Primary Jan 10 → Follow-up Feb 7–13
Recurring — every N days	N days	± 2 days	Every 10 days → follow-up Day 8–12
One-time	Mentor sets manually	± mentor-defined flexibility	Mentor sets '14 days' → Day 12–16

📌 Mentor can override the default follow-up interval and flexibility per session type in their session type settings.

7.4 Package Follow-Up DB Structure

Column	Type	Description
id	UUID PK
session_booking_id	UUID FK	Parent booking
slot_id	UUID FK	The follow-up slot reserved
sequence_number	INT	1 = first follow-up, 2 = second, etc.
status	ENUM	reserved
rescheduled_to_slot_id	UUID FK / NULL	If mentee rescheduled this follow-up
reschedule_count	INT DEFAULT 0	Max 1 reschedule per follow-up in v1
completed_at	TIMESTAMP / NULL
created_at	TIMESTAMP

7.5 Ad-hoc Follow-Up Flow

16. Session completes → mentor sees 'Schedule Follow-Up' button on completed session card

17. Mentor selects: free or paid, proposed date/time (from their own calendar or custom), title/notes

18. System creates custom_appointment row linked to the original session_booking_id

19. Mentee notified → sees proposed follow-up in their dashboard

20. Mentee accepts → if free: status = confirmed, scheduled. If paid: mentee completes payment → status = paid

21. Either party can cancel → if paid: refund policy applies

8. Payment & Refund System

8.1 Payment Flow

22. Mentee initiates booking → slot held for 10 minutes

23. Mentor confirms → hold extended to 24 hours for payment

24. Mentee pays → funds held in escrow (Stripe PaymentIntent with capture_method = manual)

25. Session completes → platform auto-captures payment; mentor receives net_amount = amount − platform_fee

26. If session does not complete within 48hr of scheduled end → admin review triggered

8.2 Refund Policy

Scenario	Refund Amount	Who Initiates	Timeline
Mentor cancels (any time after payment)	100% full refund	System (automatic)	Within 5 business days
Mentor declines / no response (request expires)	100% full refund	System (automatic)	Immediate — payment never captured
Mentee cancels > 48hr before session	75% refund (25% cancellation fee)	System (automatic)	Within 5 business days
Mentee cancels < 48hr before session	0% refund	N/A	Mentor keeps full payment
Mentee cancels < 24hr before session	0% refund	N/A	Mentor keeps full payment
Session disputed by mentee	Admin investigation → variable	Admin	Within 10 business days
Platform error / technical failure	100% full refund	System / Admin	Within 2 business days

8.3 Platform Fee

✅ Model: Percentage of each transaction

✅ Default Rate: Configurable in platform_settings table (suggest starting at 10–15%)

✅ Mentor Payout: net_amount = amount × (1 − platform_fee_rate)

✅ Free Sessions: No payment processed; no fee

9. Community System

Communities are mentor-owned spaces combining a discussion feed, course library, events calendar, and gamification. Modelled after Skool.

9.1 Community Pricing Models

Model	How It Works	DB pricing_type	billing_interval
Free	Anyone can join at no cost	free	NULL
One-time / Lifetime	Single payment for permanent access	one_time	NULL
Monthly subscription	Recurring monthly charge; access revoked on non-renewal	subscription	monthly
Yearly subscription	Recurring annual charge	subscription	yearly

9.2 Gamification — XP & Levelling

Action	XP Awarded	Notes
Post in community feed	10 XP	Per post; max 3/day to prevent spam
Comment on a post	5 XP	Per comment; max 5/day
Receive a like on a post	2 XP	Per like received
Complete a course lesson	20 XP	Per unique lesson completed
Complete a full course	100 XP	Bonus on 100% completion
Attend a live session/event	50 XP	Mentor marks attendance
Book a session with community mentor	30 XP	One-time per mentor

📌 Level thresholds are configurable per community in the community_level_thresholds table. Mentor sets XP requirements for each level. Course modules with unlock_at_level > current member level are shown as locked.

9.3 Course Access Within Communities

Course Setting	Public Users	Community Members	Level-Gated Members
Public course, no community link	Pay public price	Pay public price	Pay public price
Public course + community link, free for members	Pay public price	Free access	Free if level met
Community-only course	Cannot see	Pay community price	Free/paid if level met
Community-only + free for members	Cannot see	Free access	Free if level met

10. Course System

Courses are structured learning content created by mentors. They follow a Sections → Lessons hierarchy, support multiple content types, and have flexible audience-based pricing.

10.1 Course Structure

• Course → contains Modules (sections)

• Module → contains Lessons

• Lesson types: Video, Text/Article, PDF attachment, Quiz, Assignment

• Evaluation lessons:

  • Quiz — auto-scored; completing requires a passing score (pass_pct). An optional time limit is a hard cutoff (late submissions rejected).
  • Assignment — mentor provides brief attachments and sets total/passing marks; the learner uploads a file submission, which the mentor grades. A pass (awarded ≥ passing) completes the lesson. An optional time limit allows a late submission but flags it (a reason note is required).

• Modules can be level-gated (community context) or always accessible (standalone context)

• Individual lessons can be marked is_preview = TRUE for free teasers

10.2 Course Visibility States

visibility	Public Search	Direct URL	Community Members	Notes
public	✅ Visible	✅ Accessible	✅ Accessible	Standard public course
community_only	❌ Hidden	❌ Blocked	✅ Accessible	Members-only course
unlisted	❌ Hidden	✅ Accessible	✅ Accessible	Share via direct link only

10.3 Course Pricing Matrix

audience_type	community_id	is_free	price	Effect
public	NULL	FALSE	$49	All non-enrolled users pay $49
community_member	community_id_X	TRUE	NULL	Members of community X get free access
community_member	community_id_X	FALSE	$19	Members of community X pay $19
specific_community	community_id_Y	FALSE	$29	Only community Y members can buy at $29

11. Messaging & Chat System

11.1 Connection Request Model

27. User A sends first message → creates chat_thread with is_accepted = FALSE

28. User B receives notification: '[Name] wants to connect'

29. User B accepts → is_accepted = TRUE → both users can now freely message

30. User B ignores/declines → thread remains unaccepted; User A cannot send further messages

📌 Mentors' availability status (available / busy / on vacation) is displayed in the chat header when chatting with a mentor.

11.2 Special Message Types

message_type	metadata Contents	Mentee Action
text	N/A	Read and reply
calendar_share	{ available_slot_ids: [...], session_type_id }	Select a slot → triggers appointment_request
appointment_request	{ slot_id, proposed_starts_at, title, is_free, price }	Accept or decline
file	{ file_url, file_name, file_size }	Download attachment

11.3 In-Chat Appointment Booking Flow

31. Mentor opens chat → clicks 'Share Availability'

32. System generates calendar_share message showing mentor's next 14 days of available slots

33. Mentee selects a slot → system sends appointment_request message on mentee's behalf

34. Mentor sees the request in chat → clicks Accept (optionally sets price if not pre-set)

35. If free: custom_appointment created with status = confirmed → meeting link generated

36. If paid: mentee receives payment prompt → on payment: status = paid → meeting link generated

12. Notification System

Event	Recipient	Channel	Priority
New session booking request	Mentor	In-app + Email	High
Session confirmed by mentor	Mentee	In-app + Email	High
Payment received	Mentor	In-app + Email	High
Slot hold expiring (2 min warning)	Mentee	In-app	High
Session reminder (24hr before)	Both	In-app + Email	High
Session reminder (1hr before)	Both	In-app + Push	High
Session completed — review prompt	Both	In-app + Email	Medium
Refund issued	Mentee	In-app + Email	High
Mentor approved by admin	Mentor	In-app + Email	High
New chat connection request	Recipient	In-app + Push	Medium
New message in accepted thread	Recipient	In-app + Push	Medium
Calendar share received in chat	Mentee	In-app	Medium
Appointment request received	Mentor	In-app + Push	High
Community: new post from mentor	Members	In-app	Low
Course: new lesson published	Enrolled users	In-app + Email	Medium
Follow-up selection reminder (48hr after booking if not selected)	Mentee	In-app + Email	High

13. Database Schema — Complete

📌 All timestamps are stored in UTC. All monetary amounts are DECIMAL(10,2). UUIDs use gen_random_uuid(). Enums are implemented as VARCHAR with CHECK constraints for flexibility.

13.1 Core Tables

users

Column	Type	Constraints	Notes
id	UUID	PK, DEFAULT gen_random_uuid()
email	VARCHAR(255)	UNIQUE, NOT NULL
email_verified_at	TIMESTAMP	NULLABLE
password_hash	VARCHAR	NULLABLE	Null for OAuth-only users
full_name	VARCHAR(255)	NOT NULL
avatar_url	TEXT	NULLABLE
bio	TEXT	NULLABLE
timezone	VARCHAR(100)	DEFAULT 'UTC'	User's local timezone (IANA format)
is_active	BOOLEAN	DEFAULT TRUE
is_banned	BOOLEAN	DEFAULT FALSE
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

oauth_accounts

Column	Type	Constraints	Notes
id	UUID	PK
user_id	UUID	FK → users.id
provider	VARCHAR(50)	NOT NULL	google
provider_user_id	VARCHAR(255)	NOT NULL
access_token	TEXT	NULLABLE	Encrypted
refresh_token	TEXT	NULLABLE	Encrypted
expires_at	TIMESTAMP	NULLABLE
created_at	TIMESTAMP	DEFAULT NOW()

mentor_profiles

Column	Type	Constraints	Notes
id	UUID	PK
user_id	UUID	FK → users.id, UNIQUE
legal_name	VARCHAR(255)	NOT NULL
legal_birth_year	INT	NOT NULL
legal_profession	VARCHAR(255)	NOT NULL
company_or_institution	VARCHAR(255)	NULLABLE
skills	TEXT[]	NOT NULL	Array of skill tags
expertise_areas	TEXT[]	NULLABLE
languages	TEXT[]	DEFAULT '{en}'
approval_status	VARCHAR(20)	DEFAULT 'pending'	pending
approved_by	UUID	FK → users.id, NULLABLE	Admin user
approved_at	TIMESTAMP	NULLABLE
rejection_reason	TEXT	NULLABLE
is_available	BOOLEAN	DEFAULT TRUE	Global availability toggle
timezone	VARCHAR(100)	DEFAULT 'UTC'	Mentor's local timezone (IANA); used for slot generation
vacation_mode	BOOLEAN	DEFAULT FALSE	When true, all future available slots are blocked
vacation_ends_at	DATE	NULLABLE	If set, vacation ends on this date; if null, vacation is indefinite
average_rating	NUMERIC(3,2)	DEFAULT 0.00	Recalculated after each review
total_sessions	INT	DEFAULT 0	Lifetime completed sessions
created_at	TIMESTAMP	DEFAULT NOW()

mentee_profiles

Column	Type	Constraints	Notes
id	UUID	PK
user_id	UUID	FK → users.id, UNIQUE
headline	VARCHAR(255)	NULLABLE
current_role	VARCHAR(255)	NULLABLE
skills_seeking	TEXT[]	NULLABLE
total_sessions	INT	DEFAULT 0
created_at	TIMESTAMP	DEFAULT NOW()

13.2 Session Tables

session_types

Column	Type	Constraints	Notes
id	UUID	PK
mentor_id	UUID	FK → mentor_profiles.id
title	VARCHAR(255)	NOT NULL
description	TEXT	NULLABLE
session_model	VARCHAR(20)	NOT NULL	regular_weekly
duration_minutes	INT	NOT NULL	30
buffer_after_minutes	INT	DEFAULT 15	0
is_free	BOOLEAN	DEFAULT FALSE
price	DECIMAL(10,2)	NULLABLE	Null if free
currency	VARCHAR(10)	DEFAULT 'USD'
max_capacity	INT	DEFAULT 1	1=1-on-1; >1=group
follow_up_count	INT	DEFAULT 0	0 = no follow-ups included
follow_up_interval_days	INT	NULLABLE	Null = mirrors recurrence
follow_up_flexibility_days	INT	DEFAULT 2	±N days around interval
is_active	BOOLEAN	DEFAULT TRUE
created_at	TIMESTAMP	DEFAULT NOW()

mentor_availability_rules

Column	Type	Constraints	Notes
id	UUID	PK
mentor_id	UUID	FK → mentor_profiles.id
session_type_id	UUID	FK → session_types.id, NULLABLE	Null = applies to all types
rule_type	VARCHAR(20)	NOT NULL	regular_weekly
day_of_week	TINYINT	NULLABLE	Single day: 0=Sun … 6=Sat; for regular_weekly (one rule per day)
time_blocks	JSONB	NOT NULL	[{start_time, end_time}] in mentor's timezone; used by all rule types
rrule	TEXT	NULLABLE	iCal RRULE for recurring
specific_date	DATE	NULLABLE	For one_time
valid_from	DATE	NOT NULL
valid_until	DATE	NULLABLE	Null = no end
is_active	BOOLEAN	DEFAULT TRUE
created_at	TIMESTAMP	DEFAULT NOW()

mentor_off_days

Column	Type	Constraints	Notes
id	UUID	PK
mentor_id	UUID	FK → mentor_profiles.id
off_from	DATE	NOT NULL
off_until	DATE	NOT NULL	Inclusive
reason	VARCHAR(100)	NULLABLE	Vacation
is_full_day	BOOLEAN	DEFAULT TRUE	false = partial-hour block
off_time_blocks	JSONB	NULLABLE	[{start_time, end_time}] when is_full_day=false; off_from must equal off_until
is_vacation	BOOLEAN	DEFAULT FALSE	Marks as vacation-type for UI distinction
created_at	TIMESTAMP	DEFAULT NOW()

availability_slots

Column	Type	Constraints	Notes
id	UUID	PK
mentor_id	UUID	FK → mentor_profiles.id
session_type_id	UUID	FK → session_types.id
rule_id	UUID	FK → mentor_availability_rules.id	Source rule
starts_at	TIMESTAMP	NOT NULL	UTC
ends_at	TIMESTAMP	NOT NULL	UTC; starts_at + duration
buffer_ends_at	TIMESTAMP	NOT NULL	ends_at + buffer
status	VARCHAR(20)	DEFAULT 'available'	available
held_until	TIMESTAMP	NULLABLE
held_by_user_id	UUID	FK → users.id, NULLABLE
current_capacity	INT	DEFAULT 0	Booked count
max_capacity	INT	NOT NULL	From session_type
generated_at	TIMESTAMP	DEFAULT NOW()	Last generation timestamp

session_bookings

Column	Type	Constraints	Notes
id	UUID	PK
session_type_id	UUID	FK → session_types.id
primary_slot_id	UUID	FK → availability_slots.id	Primary session slot
mentor_id	UUID	FK → mentor_profiles.id
mentee_id	UUID	FK → mentee_profiles.id
status	VARCHAR(30)	NOT NULL	requested
is_free	BOOLEAN	NOT NULL	Snapshot at booking time
price_snapshot	DECIMAL(10,2)	NULLABLE	Price at booking time
payment_id	UUID	FK → payments.id, NULLABLE
meeting_url	TEXT	NULLABLE	Video call link
mentor_note	TEXT	NULLABLE
cancellation_reason	TEXT	NULLABLE
cancelled_by	UUID	FK → users.id, NULLABLE
cancelled_at	TIMESTAMP	NULLABLE
completed_at	TIMESTAMP	NULLABLE
created_at	TIMESTAMP	DEFAULT NOW()

session_followups (package follow-ups)

Column	Type	Constraints	Notes
id	UUID	PK
session_booking_id	UUID	FK → session_bookings.id	Parent booking
slot_id	UUID	FK → availability_slots.id	Reserved follow-up slot
sequence_number	INT	NOT NULL	1 = first follow-up
status	VARCHAR(20)	DEFAULT 'reserved'	reserved
rescheduled_to_slot_id	UUID	FK → availability_slots.id, NULLABLE
reschedule_count	INT	DEFAULT 0	Max 1 in v1
completed_at	TIMESTAMP	NULLABLE
created_at	TIMESTAMP	DEFAULT NOW()

13.3 Payment Tables

payments

Column	Type	Constraints	Notes
id	UUID	PK
payer_id	UUID	FK → users.id
payee_id	UUID	FK → users.id
amount	DECIMAL(10,2)	NOT NULL	Total charged
platform_fee_rate	DECIMAL(5,4)	NOT NULL	Rate at time of transaction
platform_fee	DECIMAL(10,2)	NOT NULL	amount × rate
net_amount	DECIMAL(10,2)	NOT NULL	amount − platform_fee
currency	VARCHAR(10)	DEFAULT 'USD'
payment_type	VARCHAR(30)	NOT NULL	session
reference_id	UUID	NOT NULL	ID of linked entity
reference_type	VARCHAR(50)	NOT NULL	Polymorphic type string
gateway	VARCHAR(50)	NOT NULL	stripe
gateway_payment_id	VARCHAR(255)	UNIQUE	External transaction ID
gateway_intent_id	VARCHAR(255)	NULLABLE	Stripe PaymentIntent ID for escrow
status	VARCHAR(30)	NOT NULL	pending
paid_at	TIMESTAMP	NULLABLE
captured_at	TIMESTAMP	NULLABLE	When escrow was released
created_at	TIMESTAMP	DEFAULT NOW()

refunds

Column	Type	Constraints	Notes
id	UUID	PK
payment_id	UUID	FK → payments.id
amount	DECIMAL(10,2)	NOT NULL
reason	TEXT	NOT NULL
policy_applied	VARCHAR(50)	NOT NULL	full
initiated_by	VARCHAR(20)	NOT NULL	system
gateway_refund_id	VARCHAR(255)	NULLABLE
status	VARCHAR(20)	NOT NULL	pending
processed_at	TIMESTAMP	NULLABLE
created_at	TIMESTAMP	DEFAULT NOW()

slot_holds (transient — auto-cleaned)

Column	Type	Constraints	Notes
id	UUID	PK
slot_id	UUID	FK → availability_slots.id
user_id	UUID	FK → users.id
booking_attempt_id	UUID	NOT NULL	Groups primary + follow-up holds
expires_at	TIMESTAMP	NOT NULL	NOW() + 10 minutes
created_at	TIMESTAMP	DEFAULT NOW()

14. Key Indexes & Performance

Table	Index Columns	Type	Reason
users	email	UNIQUE	Login lookup
mentor_profiles	user_id	UNIQUE	Profile fetch by user
mentor_profiles	approval_status	BTREE	Admin dashboard filter
mentor_profiles	skills (GIN)	GIN	Full-text skill search
availability_slots	mentor_id, starts_at	BTREE	Calendar query range scans
availability_slots	mentor_id, session_type_id, starts_at	BTREE	Slot lookup per session type
availability_slots	status, held_until	BTREE	Cron: find expired holds
availability_slots	starts_at, status	BTREE	Public availability calendar
session_bookings	mentor_id, status	BTREE	Mentor dashboard
session_bookings	mentee_id, status	BTREE	Mentee dashboard
session_followups	session_booking_id	BTREE	Follow-up lookup per booking
payments	reference_id, reference_type	BTREE	Polymorphic payment lookup
payments	payer_id, status	BTREE	Payment history
chat_messages	thread_id, created_at	BTREE	Message feed ordering
notifications	user_id, is_read, created_at	BTREE	Unread notification count
community_members	community_id, user_id	UNIQUE	Membership check
community_members	community_id, level	BTREE	Level-gated content access
course_enrollments	user_id, course_id	UNIQUE	Enrollment check

15. Frontend Integration Guide

This section maps backend APIs to the existing UI screens, describing the data each screen needs and the key interactions it must handle.

15.1 Mentor Availability Setup Screen

• Load: GET /mentor/availability-rules → list of existing rules

• Load: GET /mentor/off-days → blocked date ranges

• Weekly grid: day columns × time block rows; user adds/removes blocks

• Recurring modal: RRULE builder (frequency, interval, specific days/dates)

• Save: POST /mentor/availability-rules → triggers slot regeneration job

• Off-day picker: date range selector → POST /mentor/off-days → slot invalidation job runs

⚠️ After any availability save, show mentor: 'Your calendar is being updated. Changes visible within 2 minutes.'

15.2 Booking Calendar Screen (Mentee View)

• Load: GET /mentors/{id}/slots?session_type_id=X&from=Y&to=Z → returns availability_slots with status

• Calendar renders slots colour-coded: available (green), held (grey + label), booked (red), vacation (orange + label)

• Mentee clicks available slot → if follow_up_count > 0: opens follow-up picker modal

• Follow-up picker: shows next available slots within the interval window; mentee must select exactly follow_up_count slots

• Confirm selection → POST /bookings/hold → receives booking_attempt_id + checkout_session_url

• Frontend starts 10-minute countdown timer. On expiry: show 'Slot expired' toast, redirect to calendar

• Redirect to payment provider → on success: POST /bookings/confirm-payment

15.3 Mentor Dashboard — Sessions Panel

• Tabs: Pending Requests | Upcoming | Completed | Cancelled

• Pending: each request shows mentee name, session type, proposed time, Accept / Decline buttons

• On Accept: PATCH /bookings/{id}/confirm → mentee notified

• Upcoming: shows primary session + follow-up count badge; expandable to show follow-up dates

• Completed: shows Review button (if no review submitted yet) + Ad-hoc Follow-Up button

15.4 Chat Screen — Mentor Availability Sharing

• Mentor clicks 'Share Calendar' button in chat input bar

• Client calls GET /mentor/shareable-slots?days=14 → returns next 14 days of available slot summaries

• Rendered as inline calendar card in chat (not a full page)

• Mentee taps a slot on the card → triggers appointment_request message automatically

• Mentor sees Accept / Set Price / Decline actions on the appointment_request bubble

16. Implementation Roadmap

Phase	Scope	Key Deliverables
Phase 1	Foundation	users, oauth_accounts, mentor_profiles, mentee_profiles, onboarding_questionnaires, mentor_verification_documents, mentor_certifications. Admin approval flow. Auth middleware.
Phase 2	Availability Engine	mentor_availability_rules, mentor_off_days, availability_slots. Slot generation cron job. Slot hold mechanism. slot_holds table. Timezone conversion utilities.
Phase 3	Session Booking	session_types, session_bookings, session_followups. Full booking lifecycle. Payment escrow via Stripe. Refund automation. Booking calendar UI.
Phase 4	Messaging	chat_threads, chat_participants, chat_messages. Connection request model. Calendar share message type. In-chat appointment booking. custom_appointments.
Phase 5	Communities	communities, community_members, community_subscriptions, community_posts. XP + levelling engine. Community subscription payment. Feed UI.
Phase 6	Courses	courses, course_pricing, course_modules, course_lessons, course_enrollments, community_courses. Multi-audience pricing engine. Progress tracking. Video player integration.
Phase 7	Platform Subscriptions + AI	platform_subscription_tiers, user_platform_subscriptions, ai_mentors, ai_mentor_conversations, ai_todos, ai_suggestions. Stripe subscription billing. OpenAI/Claude API integration. Admin tier config UI.
Phase 8	Notifications & Polish	notifications table. Email + in-app + push delivery. Review system. Admin dashboard. Analytics. Platform settings.
Phase 9	Scale & Hardening	Index optimisation. Slot generation performance testing. Stripe webhook hardening. Rate limiting. Audit logs. GDPR compliance.

17. Community Pricing — Complete Model

Communities support three distinct pricing models. The mentor sets this when creating the community and can change it (with admin notice) at any time. Existing members are grandfathered on the price they originally paid.

Pricing Model	pricing_type	billing_interval	How It Works
Free	free	NULL	Anyone joins at no cost. No payment record created.
Lifetime / One-time	one_time	NULL	Single fixed payment. Access is permanent. Never expires.
Monthly Subscription	subscription	monthly	Recurring charge each month. Access revoked if payment fails after 3 retries.
Yearly Subscription	subscription	yearly	Recurring annual charge. Cheaper per month than monthly. Same revocation logic.

📌 billing_interval = NULL for both free and one_time. The distinction is pricing_type. community_subscriptions.ends_at = NULL for lifetime members; set to renewal date for subscriptions.

⚠️ When a mentor changes a free community to paid, existing members must be notified 30 days in advance. System sends notification automatically on pricing_type change.

18. Platform Premium Subscription

The platform offers its own subscription tiers — separate from community subscriptions — that give mentees platform-wide benefits including free session credits, reduced commission, exclusive course access, and access to the AI Mentor feature.

18.1 Three-Tier Model

Tier	Suggested Name	AI Mentors	Free Sessions/mo	Commission Reduction	Other
Tier 0 (Free)	Explorer	0	0	0%	Default for all users
Tier 1	Starter	1 AI Mentor	Admin-set (e.g. 1/mo)	Admin-set (e.g. 5% off)	Discount on courses/communities
Tier 2	Pro	3 AI Mentors	Admin-set (e.g. 3/mo)	Admin-set (e.g. 10% off)	Exclusive course access
Tier 3	Elite	10 AI Mentors	Admin-set (e.g. 5/mo)	Admin-set (e.g. 15% off)	Priority support + all benefits

📌 All numeric limits (AI mentor count, free session count, commission reduction %, discount %) are stored in a single JSONB benefits column on the platform_subscription_tiers table. Admin can update any limit without a schema migration.

18.2 platform_subscription_tiers table

Column	Type	Constraints	Notes
id	UUID	PK
name	VARCHAR(100)	NOT NULL, UNIQUE	Explorer
display_name	VARCHAR(100)	NOT NULL	Shown to users in UI
tier_level	INT	NOT NULL, UNIQUE	0
monthly_price	DECIMAL(10,2)	NOT NULL	0.00 for free tier
yearly_price	DECIMAL(10,2)	NOT NULL	0.00 for free tier
currency	VARCHAR(10)	DEFAULT 'USD'
benefits	JSONB	NOT NULL	See 18.3 for full schema
is_active	BOOLEAN	DEFAULT TRUE	Admin can retire a tier
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

18.3 Benefits JSONB Schema

The benefits column stores all configurable limits and perks. This design means new features can add new benefit keys without any DB migration — only an admin UI update is needed.

JSON Key	Type	Example Value	Description
ai_mentor_limit	integer	3	Max AI Mentors this tier can create (0 = none)
free_sessions_per_month	integer	3	Monthly free session booking credits
commission_discount_pct	float	10.0	% reduction on platform commission at checkout
course_discount_pct	float	15.0	% discount on paid course purchases
community_discount_pct	float	10.0	% discount on community join fees
exclusive_course_access	boolean	true	Access to courses tagged is_exclusive_to_premium
ai_helper_enabled	boolean	true	Access to AI Helper recommendations
priority_support	boolean	false	Queue priority for support tickets
custom_[future_feature]	any	...	Any future feature key can be added here

📌 At checkout, the payment service reads the user's active tier benefits and applies commission_discount_pct and course_discount_pct automatically. Free session credits are tracked in user_platform_subscriptions.free_sessions_remaining, decremented on each free booking.

18.4 user_platform_subscriptions table

Column	Type	Constraints	Notes
id	UUID	PK
user_id	UUID	FK → users.id, UNIQUE	One active platform sub per user
tier_id	UUID	FK → platform_subscription_tiers.id	Current tier
billing_interval	VARCHAR(10)	NOT NULL	monthly
status	VARCHAR(20)	NOT NULL	active
free_sessions_remaining	INT	DEFAULT 0	Decremented on each free session booking; reset monthly
current_period_start	TIMESTAMP	NOT NULL	Current billing period start
current_period_end	TIMESTAMP	NOT NULL	Current billing period end; access valid until here
cancel_at_period_end	BOOLEAN	DEFAULT FALSE	Cancels at period end without immediate downgrade
gateway_subscription_id	VARCHAR(255)	NULLABLE	Stripe Subscription ID
payment_id	UUID	FK → payments.id, NULLABLE	Latest payment for this sub
benefits_snapshot	JSONB	NOT NULL	Copy of tier benefits at subscription time; used for billing consistency
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

18.5 Free Session Credit Logic

37. User with active premium sub attempts to book a paid session

38. Payment service checks: user_platform_subscriptions.free_sessions_remaining > 0

39. If yes: decrement free_sessions_remaining by 1; mark payment as platform_credit; no charge to user

40. If no: normal payment flow applies (with commission_discount_pct applied to platform fee)

41. On monthly renewal: free_sessions_remaining reset to benefits.free_sessions_per_month

42. Free session credits do not roll over to next month — use them or lose them

⚠️ Free session credits apply to the platform fee only in v1. The mentor still receives their full session price. The platform absorbs the commission on free-credit bookings.

19. AI Mentor System

The AI Mentor feature allows mentees to create personalised AI agents tailored to their learning goals. Each AI Mentor has a distinct persona, professional focus, and custom system prompt. The AI is also fully context-aware of the mentee's platform activity — sessions attended, courses in progress, goals set, and todos completed.

19.1 AI Mentor Creation Flow

43. Mentee navigates to 'AI Mentors' tab in their dashboard

44. Clicks 'Create New AI Mentor'

45. Fills in the creation form:

    • Title — e.g. 'My Product Management Coach'

    • Description — what this AI mentor is for

    • Professional field — e.g. Product Management, Software Engineering, UX Design

    • Skills focus — multi-select tag list (e.g. roadmapping, user research, SQL)

    • Industry — e.g. FinTech, Healthcare, E-commerce

    • Custom prompt / guideline — free-text field where mentee describes exactly what they want to learn, their current level, preferred teaching style, etc.

46. Saves → ai_mentors row created

47. AI Mentor appears as a card on the AI Mentors tab

48. Mentee clicks a card → opens dedicated chat interface for that AI Mentor

19.2 AI Mentor Chat — Context Injection

When a mentee opens a chat with an AI Mentor, the system composes a context-rich system prompt sent to the LLM API. This prompt combines:

Context Source	Data Pulled
AI Mentor config	title, description, professional_field, skills, industry, custom_prompt
Mentee profile	current_role, skills_seeking, goals from onboarding questionnaire
Session history	List of completed sessions: mentor name, session title, date (last 10)
Course progress	Enrolled courses + completion % for each
Open todos	All incomplete ai_todos for this AI Mentor
Recent chat summary	Summary of last N conversation turns (for continuity across sessions)

📌 The full platform context is injected as a structured block at the start of every API call. The mentee's actual chat messages are then appended as the conversation history. This keeps the AI grounded in the mentee's real situation.

19.3 AI Mentor Database Tables

ai_mentors

Column	Type	Constraints	Notes
id	UUID	PK
mentee_id	UUID	FK → mentee_profiles.id	Owner mentee
title	VARCHAR(255)	NOT NULL	e.g. 'My PM Coach'
description	TEXT	NULLABLE	What this AI mentor is for
professional_field	VARCHAR(100)	NOT NULL	e.g. Product Management
skills	TEXT[]	NOT NULL	Focus skills
industry	VARCHAR(100)	NULLABLE	e.g. FinTech
custom_prompt	TEXT	NOT NULL	Mentee's detailed guideline / persona instructions
avatar_style	VARCHAR(50)	DEFAULT 'default'	UI avatar/icon selection
total_messages	INT	DEFAULT 0	Lifetime message count
is_active	BOOLEAN	DEFAULT TRUE	Soft delete
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

ai_mentor_conversations

Column	Type	Constraints	Notes
id	UUID	PK
ai_mentor_id	UUID	FK → ai_mentors.id
title	VARCHAR(255)	NULLABLE	Auto-generated from first message
summary	TEXT	NULLABLE	AI-generated summary for context injection
message_count	INT	DEFAULT 0
last_message_at	TIMESTAMP	NULLABLE
created_at	TIMESTAMP	DEFAULT NOW()

ai_mentor_messages

Column	Type	Constraints	Notes
id	UUID	PK
conversation_id	UUID	FK → ai_mentor_conversations.id
role	VARCHAR(10)	NOT NULL	user
content	TEXT	NOT NULL	Message text
token_count	INT	NULLABLE	For usage tracking and cost monitoring
created_at	TIMESTAMP	DEFAULT NOW()

ai_todos

Column	Type	Constraints	Notes
id	UUID	PK
ai_mentor_id	UUID	FK → ai_mentors.id	Which AI Mentor assigned this
mentee_id	UUID	FK → mentee_profiles.id	Owner mentee
conversation_id	UUID	FK → ai_mentor_conversations.id, NULLABLE	Conversation that generated this todo
title	VARCHAR(500)	NOT NULL	Todo task description
details	TEXT	NULLABLE	Expanded instructions from AI
due_date	DATE	NULLABLE	Suggested completion date
priority	VARCHAR(10)	DEFAULT 'medium'	low
status	VARCHAR(20)	DEFAULT 'pending'	pending
completed_at	TIMESTAMP	NULLABLE
created_at	TIMESTAMP	DEFAULT NOW()

19.4 Todo Assignment Flow

49. During AI Mentor chat, the AI identifies action items for the mentee

50. AI response includes a structured JSON block alongside its message text: { todos: [{title, details, due_date, priority}] }

51. Backend parses this block, creates ai_todos rows, and returns them to frontend separately from the chat message

52. Frontend displays todos in a sidebar panel within the AI Mentor chat, and also in the main 'My Todos' dashboard widget

53. Mentee marks todo complete → status = completed, completed_at = NOW()

54. On next AI Mentor chat in same context: completed todos are included in context injection so AI acknowledges progress and adjusts recommendations

19.5 AI Mentor Tier Limits Enforcement

Action	Check
Create new AI Mentor	COUNT(ai_mentors WHERE mentee_id = X AND is_active = TRUE) < tier.benefits.ai_mentor_limit
Send message to AI Mentor	User has active platform subscription (any tier ≥ 1); free tier blocked
View AI Mentor tab	Always visible; creation blocked with upgrade prompt if at limit

20. AI Helper for Mentors

The AI Helper is a platform-side recommendation engine that assists mentors in growing their reach and optimising their offerings. Unlike the AI Mentor (which is a personal chat agent for mentees), the AI Helper surfaces data-driven suggestions in the mentor dashboard.

20.1 AI Helper Features

Feature	What It Does	Trigger
Community Matching	Suggests communities the mentor should join or collaborate with, based on skill overlap and community topic tags	Daily background job
Course Suggestions	Recommends course topics the mentor could create based on what mentees in their communities are searching for	Weekly background job
Session Optimisation	Suggests better session titles, descriptions, or pricing based on platform booking trends	After 10+ sessions completed
Profile Completion Tips	Flags missing profile fields that reduce discoverability	On profile view if completion < 80%
Mentee Matching	Suggests mentees who match the mentor's skills and haven't booked yet	Daily background job

20.2 ai_suggestions table

Column	Type	Constraints	Notes
id	UUID	PK
user_id	UUID	FK → users.id	Mentor receiving the suggestion
suggestion_type	VARCHAR(50)	NOT NULL	community_match
title	VARCHAR(255)	NOT NULL	Short suggestion headline
body	TEXT	NOT NULL	Full suggestion text
reference_id	UUID	NULLABLE	ID of related entity (community, course, mentee, etc.)
reference_type	VARCHAR(50)	NULLABLE	Polymorphic type string
is_read	BOOLEAN	DEFAULT FALSE
is_dismissed	BOOLEAN	DEFAULT FALSE
is_acted_on	BOOLEAN	DEFAULT FALSE	Set when mentor takes the suggested action
expires_at	TIMESTAMP	NULLABLE	Suggestions older than 30 days auto-dismiss
created_at	TIMESTAMP	DEFAULT NOW()

📌 In v1, AI Helper suggestions are generated by backend jobs using platform data and simple matching algorithms (skill tag overlap, search query analysis). In v2, these jobs can be upgraded to LLM-powered analysis without any schema changes.

Updated Implementation Roadmap

📌 Status reflects actual code state as of 2026-07-12 (full test suite: 437 passed / 0 failed). See DELIVERABLES.md for the per-phase implementation tracker.

Phase	Scope	Key Deliverables	Status
1	Foundation	users, oauth_accounts, mentor_profiles, mentee_profiles, onboarding. Admin approval flow. Auth middleware.	🟢 Complete
2	Availability Engine	mentor_availability_rules, mentor_off_days, availability_slots. Slot generation cron. Hold mechanism. Timezone utilities.	🟢 Complete
3	Session Booking	session_types, session_bookings, session_followups. Full booking lifecycle. Stripe escrow. Refund automation.	🟢 Complete (fake checkout; real Stripe escrow/refunds deferred)
4	Messaging	chat_threads, chat_participants, chat_messages. Connection request model. Calendar share. In-chat appointments.	🟢 Complete (1:1 threads, files, in-chat booking; real-time delivery now live via Phase 10 Reverb)
5	Communities	communities, community_members, community_subscriptions, community_posts. XP engine. All 4 pricing models. Feed UI.	🟢 Complete (events calendar deferred)
6	Courses	courses, course_pricing, course_modules, course_lessons, course_enrollments, community_courses. Multi-audience pricing. Progress tracking.	🟢 Complete
7	Platform Subscriptions	platform_subscription_tiers (JSONB benefits). user_platform_subscriptions. Tier enforcement at checkout. Free session credit logic. Stripe recurring billing. Admin tier config UI.	🔴 Not started
8	AI Mentor	ai_mentors, ai_mentor_conversations, ai_mentor_messages, ai_todos. LLM API integration. Context injection pipeline. Todo extraction from AI responses. Tier limit enforcement.	🔴 Not started
9	AI Helper	ai_suggestions table. Background job framework. Community/course/mentee matching algorithms. Mentor dashboard suggestions widget.	🔴 Not started
10	Notifications & Polish	Full notification system. Email + push. Review system. Admin dashboard. Analytics.	🟡 In progress — notification engine, queued delivery, Reverb websockets, and the full §12 catalog shipped; mail branding, FCM push, preferences, reviews, admin dashboard, analytics pending. See DELIVERABLES.md Phase 10.
11	Scale & Hardening	Performance testing. Webhook hardening. Rate limiting. Audit logs. GDPR compliance.	🔴 Not started

📌 Public Feed (follow graph, posts, reactions, comments, shares, polls, reporting) ships as an out-of-BRD social module — 🟢 Complete. See DELIVERABLES.md and docs/feed-api.md.

— End of Document —