Back to Editor

TimeSlot Management - API Contract & Implementation Guide

Markdown Document
Reading time about 3 minutes

TimeSlot Management - API Contract & Implementation Guide

Frontend Framework: Angular 20.3.0
Expected Backend: ASP.NET Core 8.0 Web API
Base URL: http://localhost:5000/api
Project: Career Route Mentorship Platform
Purpose: Simple slot-based mentor availability system
Date: 2025-11-12

Table of Contents

  1. Entity Class Definition
  2. API Contract
  3. Backend Implementation Guide

PART 1: ENTITY CLASS DEFINITION

TimeSlot Entity Class

File: Backend/CareerRoute.Core/Entities/TimeSlot.cs

namespace CareerRoute.Core.Entities
{
    public class TimeSlot
    {
        // PRIMARY KEY
        public int Id { get; set; }

        // FOREIGN KEYS (Relations to other tables)
        public string MentorId { get; set; }      // FK to Mentor (which extends User)
        public int? SessionId { get; set; }       // FK to Session (nullable - only set when booked)

        // DATA COLUMNS
        public DateTime StartDateTime { get; set; }  // When the slot starts (UTC)
        public int DurationMinutes { get; set; }     // Duration: 30 or 60 minutes
        public bool IsBooked { get; set; }           // Quick flag: true if booked, false if available
        public DateTime CreatedAt { get; set; }      // When slot was created

        // NAVIGATION PROPERTIES (EF Core uses these to JOIN tables)
        public Mentor Mentor { get; set; }        // Relationship: TimeSlot belongs to ONE Mentor
        public Session Session { get; set; }      // Relationship: TimeSlot linked to ONE Session (when booked)
    }
}

Field Descriptions

Field Type Description Required Constraints
Id int Primary key, auto-increment Yes Auto-generated
MentorId string (GUID) Foreign key to Mentor/User Yes Max 450 chars
SessionId int? Foreign key to Session No Nullable - only set when booked
StartDateTime DateTime Slot start time in UTC Yes Must be future date/time
DurationMinutes int Session duration Yes Must be 30 or 60
IsBooked bool Booking status flag Yes Default: false
CreatedAt DateTime Creation timestamp Yes Auto-set on creation

PART 2: API CONTRACT

Overview

TimeSlot endpoints enable mentors to manage their availability by creating specific bookable time slots, and allow mentees to view available slots when booking sessions. This simple slot-based system eliminates complex recurring patterns and provides clear, explicit availability management.

Authorization Rules:

  • Public endpoints: View available slots (no authentication required)
  • Mentor endpoints: Create, view, and delete own time slots (authentication required)
  • Business rule: Mentors can only delete slots that are NOT booked

Key Business Rules:

  • Mentors create specific time slots (date/time + duration)
  • Time slots can be 30 or 60 minutes
  • Slots must be at least 24 hours in the future
  • Once booked (isBooked = true), slot cannot be deleted
  • When session is cancelled, slot becomes available again (isBooked = false, sessionId = null)
  • Mentees can only book from available slots (not booked, at least 24h ahead)

TimeSlot Lifecycle:

  1. Available - Created by mentor, isBooked = false, sessionId = null
  2. Booked - User books session, isBooked = true, sessionId set to session ID
  3. Available (again) - Session cancelled, isBooked = false, sessionId = null

Related Documentation

  • 📖 Session Booking: See Session-Payment-Endpoints.md for:

    • POST /api/sessions (booking flow updated to use timeSlotId)
    • Session cancellation logic (releases time slot)

  • 📖 Mentor Profiles: See Mentor-Endpoints.md for:

    • GET /api/mentors/{id} (includes availabilityPreview calculated from TimeSlots)

Public Endpoints (No Authentication Required)

1. Get Available Time Slots for a Mentor

Endpoint: GET /api/mentors/{mentorId}/available-slots
Requires: None (public access)
Roles: Public

Path Parameters:

  • mentorId (string, GUID): Mentor ID

Query Parameters:

  • startDate (string, ISO 8601 date, optional): Filter slots from this date (default: today)
  • endDate (string, ISO 8601 date, optional): Filter slots until this date (default: 7 days from startDate)
  • durationMinutes (integer, optional): Filter by duration (30 or 60)

Success Response (200):

{
  "success": true,
  "message": "Available slots retrieved successfully",
  "data": {
    "mentorId": "cc0e8400-e29b-41d4-a716-446655440007",
    "mentorName": "Sarah Johnson",
    "availableSlots": [
      {
        "id": 123,
        "startDateTime": "2025-11-15T14:00:00Z",
        "endDateTime": "2025-11-15T15:00:00Z",
        "durationMinutes": 60,
        "price": 45.00
      },
      {
        "id": 124,
        "startDateTime": "2025-11-15T16:00:00Z",
        "endDateTime": "2025-11-15T16:30:00Z",
        "durationMinutes": 30,
        "price": 25.00
      },
      {
        "id": 125,
        "startDateTime": "2025-11-16T10:00:00Z",
        "endDateTime": "2025-11-16T11:00:00Z",
        "durationMinutes": 60,
        "price": 45.00
      }
    ],
    "totalCount": 3,
    "dateRange": {
      "startDate": "2025-11-15",
      "endDate": "2025-11-22"
    }
  }
}

Error Responses:

  • 400 Bad Request:

    {
  "success": false,
  "message": "Validation failed",
  "errors": {
    "StartDate": ["Start date must be a valid ISO 8601 date"],
    "DurationMinutes": ["Duration must be 30 or 60 minutes"]
  },
  "statusCode": 400
}

  • 404 Not Found (Mentor):

    {
  "success": false,
  "message": "Mentor not found",
  "statusCode": 404
}

  • 404 Not Found (No Slots):

    {
  "success": false,
  "message": "No available slots found for the specified date range",
  "statusCode": 404
}

Backend Behavior:

  • Validate mentor exists and is approved
  • Default date range: today to 7 days from today if not specified
  • Filter TimeSlots WHERE:
    • MentorId = {mentorId}
    • IsBooked = false
    • StartDateTime >= {startDate}
    • StartDateTime <= {endDate}
    • StartDateTime > DateTime.UtcNow + 24 hours (must be bookable - 24h advance)
    • If durationMinutes specified: DurationMinutes = {durationMinutes}
  • Order by StartDateTime ASC
  • Calculate endDateTime (StartDateTime + DurationMinutes)
  • Include price from mentor's rate30Min or rate60Min
  • Return 404 if no slots match criteria

Authenticated Endpoints (Mentor Only)

2. Create Time Slot(s)

Endpoint: POST /api/mentors/{mentorId}/time-slots
Requires: Authorization: Bearer {token}
Roles: Mentor (own profile) or Admin

Path Parameters:

  • mentorId (string, GUID): Mentor ID

Request Body (Single Slot):

{
  "startDateTime": "2025-11-15T14:00:00Z",
  "durationMinutes": 60
}

Request Body (Batch Create - Multiple Slots):

{
  "slots": [
    {
      "startDateTime": "2025-11-15T14:00:00Z",
      "durationMinutes": 60
    },
    {
      "startDateTime": "2025-11-15T16:00:00Z",
      "durationMinutes": 30
    },
    {
      "startDateTime": "2025-11-16T10:00:00Z",
      "durationMinutes": 60
    }
  ]
}

Field Requirements:

  • startDateTime (required): ISO 8601 datetime, must be at least 24 hours in future
  • durationMinutes (required): Integer, must be 30 or 60
  • slots (for batch): Array of slot objects, max 50 slots per request

Success Response (201) - Single Slot:

{
  "success": true,
  "message": "Time slot created successfully",
  "data": {
    "id": 123,
    "mentorId": "cc0e8400-e29b-41d4-a716-446655440007",
    "startDateTime": "2025-11-15T14:00:00Z",
    "endDateTime": "2025-11-15T15:00:00Z",
    "durationMinutes": 60,
    "isBooked": false,
    "sessionId": null,
    "createdAt": "2025-11-12T10:00:00Z"
  }
}

Success Response (201) - Batch Create:

{
  "success": true,
  "message": "3 time slots created successfully",
  "data": {
    "createdSlots": [
      {
        "id": 123,
        "mentorId": "cc0e8400-e29b-41d4-a716-446655440007",
        "startDateTime": "2025-11-15T14:00:00Z",
        "endDateTime": "2025-11-15T15:00:00Z",
        "durationMinutes": 60,
        "isBooked": false,
        "sessionId": null,
        "createdAt": "2025-11-12T10:00:00Z"
      },
      {
        "id": 124,
        "mentorId": "cc0e8400-e29b-41d4-a716-446655440007",
        "startDateTime": "2025-11-15T16:00:00Z",
        "endDateTime": "2025-11-15T16:30:00Z",
        "durationMinutes": 30,
        "isBooked": false,
        "sessionId": null,
        "createdAt": "2025-11-12T10:00:00Z"
      },
      {
        "id": 125,
        "mentorId": "cc0e8400-e29b-41d4-a716-446655440007",
        "startDateTime": "2025-11-16T10:00:00Z",
        "endDateTime": "2025-11-16T11:00:00Z",
        "durationMinutes": 60,
        "isBooked": false,
        "sessionId": null,
        "createdAt": "2025-11-12T10:00:00Z"
      }
    ],
    "totalCreated": 3
  }
}

Error Responses:

  • 400 Bad Request (Validation):

    {
  "success": false,
  "message": "Validation failed",
  "errors": {
    "StartDateTime": ["Time slot must be at least 24 hours in the future"],
    "DurationMinutes": ["Duration must be 30 or 60 minutes"],
    "Slots": ["Cannot create more than 50 slots in one request"]
  },
  "statusCode": 400
}

  • 401 Unauthorized:

    {
  "success": false,
  "message": "Invalid authentication token",
  "statusCode": 401
}

  • 403 Forbidden:

    {
  "success": false,
  "message": "You can only create time slots for your own mentor profile",
  "statusCode": 403
}

  • 409 Conflict:

    {
  "success": false,
  "message": "A time slot already exists at this date/time",
  "statusCode": 409
}

Backend Behavior:

  • Extract user ID from JWT token
  • Verify user owns this mentor profile OR user is Admin
  • Validate startDateTime is at least 24 hours in future
  • Validate durationMinutes is 30 or 60
  • For batch: Validate max 50 slots per request
  • Check for existing slot at same startDateTime for this mentor (return 409 if exists)
  • Create TimeSlot entity/entities with:
    • MentorId = {mentorId}
    • IsBooked = false
    • SessionId = null
    • CreatedAt = DateTime.UtcNow
  • Return created slot(s) with calculated endDateTime
  • Return 403 if user doesn't own profile and is not Admin

3. Get All Time Slots for Mentor

Endpoint: GET /api/mentors/{mentorId}/time-slots
Requires: Authorization: Bearer {token}
Roles: Mentor (own profile) or Admin

Path Parameters:

  • mentorId (string, GUID): Mentor ID

Query Parameters:

  • startDate (string, ISO 8601 date, optional): Filter slots from this date (default: today)
  • endDate (string, ISO 8601 date, optional): Filter slots until this date (default: 30 days from startDate)
  • isBooked (boolean, optional): Filter by booking status (true/false)
  • page (integer, optional): Page number (default: 1, min: 1)
  • pageSize (integer, optional): Items per page (default: 20, min: 1, max: 100)

Success Response (200):

{
  "success": true,
  "message": "Time slots retrieved successfully",
  "data": {
    "timeSlots": [
      {
        "id": 123,
        "mentorId": "cc0e8400-e29b-41d4-a716-446655440007",
        "startDateTime": "2025-11-15T14:00:00Z",
        "endDateTime": "2025-11-15T15:00:00Z",
        "durationMinutes": 60,
        "isBooked": false,
        "sessionId": null,
        "createdAt": "2025-11-12T10:00:00Z",
        "canDelete": true
      },
      {
        "id": 124,
        "mentorId": "cc0e8400-e29b-41d4-a716-446655440007",
        "startDateTime": "2025-11-15T16:00:00Z",
        "endDateTime": "2025-11-15T16:30:00Z",
        "durationMinutes": 30,
        "isBooked": true,
        "sessionId": 456,
        "session": {
          "id": 456,
          "menteeFirstName": "John",
          "menteeLastName": "Doe",
          "status": "Confirmed",
          "topic": "Career Planning"
        },
        "createdAt": "2025-11-12T10:00:00Z",
        "canDelete": false
      }
    ],
    "pagination": {
      "totalCount": 2,
      "currentPage": 1,
      "pageSize": 20,
      "totalPages": 1,
      "hasNextPage": false,
      "hasPreviousPage": false
    },
    "summary": {
      "totalSlots": 2,
      "availableSlots": 1,
      "bookedSlots": 1
    }
  }
}

Error Responses:

  • 401 Unauthorized:

    {
  "success": false,
  "message": "Invalid authentication token",
  "statusCode": 401
}

  • 403 Forbidden:

    {
  "success": false,
  "message": "You can only view time slots for your own mentor profile",
  "statusCode": 403
}

  • 404 Not Found:

    {
  "success": false,
  "message": "No time slots found",
  "statusCode": 404
}

Backend Behavior:

  • Extract user ID from JWT token
  • Verify user owns this mentor profile OR user is Admin
  • Default date range: today to 30 days from today if not specified
  • Filter TimeSlots WHERE:
    • MentorId = {mentorId}
    • StartDateTime >= {startDate}
    • StartDateTime <= {endDate}
    • If isBooked specified: IsBooked = {isBooked}
  • Order by StartDateTime ASC
  • Apply pagination
  • For booked slots (isBooked = true), include session details via JOIN
  • Calculate canDelete (true if isBooked = false)
  • Calculate summary statistics (total, available, booked counts)
  • Return 403 if user doesn't own profile and is not Admin
  • Return 404 if no slots found

4. Delete Time Slot

Endpoint: DELETE /api/mentors/{mentorId}/time-slots/{slotId}
Requires: Authorization: Bearer {token}
Roles: Mentor (own profile) or Admin

Path Parameters:

  • mentorId (string, GUID): Mentor ID
  • slotId (integer): Time slot ID to delete

Success Response (200):

{
  "success": true,
  "message": "Time slot deleted successfully"
}

Error Responses:

  • 401 Unauthorized:

    {
  "success": false,
  "message": "Invalid authentication token",
  "statusCode": 401
}

  • 403 Forbidden (Ownership):

    {
  "success": false,
  "message": "You can only delete time slots from your own mentor profile",
  "statusCode": 403
}

  • 404 Not Found:

    {
  "success": false,
  "message": "Time slot not found",
  "statusCode": 404
}

  • 409 Conflict:

    {
  "success": false,
  "message": "Cannot delete a booked time slot. Please cancel the session first.",
  "statusCode": 409
}

Backend Behavior:

  • Extract user ID from JWT token
  • Verify user owns this mentor profile OR user is Admin
  • Fetch TimeSlot by slotId and mentorId
  • Check if TimeSlot.IsBooked = true (return 409 if booked)
  • Delete TimeSlot from database
  • Return success message
  • Return 403 if user doesn't own profile and is not Admin
  • Return 404 if slot doesn't exist or doesn't belong to this mentor
  • Return 409 if slot is booked (prevent deletion)

Model Structures (DTOs)

TimeSlotDto

{
  "id": "number",
  "mentorId": "string (GUID)",
  "startDateTime": "ISO 8601 datetime",
  "endDateTime": "ISO 8601 datetime",         // Calculated: startDateTime + durationMinutes
  "durationMinutes": "number",                // 30 or 60
  "isBooked": "boolean",
  "sessionId": "number | null",               // Null if available, session ID if booked
  "session": "SessionPreviewDto | null",      // Only included for mentor viewing their slots
  "createdAt": "ISO 8601 datetime",
  "canDelete": "boolean | optional"           // Only for mentor view: true if not booked
}

AvailableSlotDto (Lightweight for Public)

{
  "id": "number",
  "startDateTime": "ISO 8601 datetime",
  "endDateTime": "ISO 8601 datetime",
  "durationMinutes": "number",                // 30 or 60
  "price": "decimal"                          // Mentor's rate30Min or rate60Min
}

CreateTimeSlotDto

{
  "startDateTime": "ISO 8601 datetime",       // Required
  "durationMinutes": "number"                 // Required: 30 or 60
}

BatchCreateTimeSlotsDto

{
  "slots": "CreateTimeSlotDto[]"              // Array of slot objects, max 50
}

TimeSlotListResponseDto

{
  "timeSlots": "TimeSlotDto[]",
  "pagination": {
    "totalCount": "number",
    "currentPage": "number",
    "pageSize": "number",
    "totalPages": "number",
    "hasNextPage": "boolean",
    "hasPreviousPage": "boolean"
  },
  "summary": {
    "totalSlots": "number",
    "availableSlots": "number",
    "bookedSlots": "number"
  }
}

SessionPreviewDto (Nested in TimeSlotDto when booked)

{
  "id": "number",
  "menteeFirstName": "string",
  "menteeLastName": "string",
  "status": "string (enum: Pending, Confirmed, InProgress, Completed, Cancelled)",
  "topic": "string | null"
}

Validation Rules

StartDateTime

  • Required for creation
  • Must be ISO 8601 format
  • Must be at least 24 hours in the future from current UTC time
  • Cannot be in the past

DurationMinutes

  • Required for creation
  • Must be integer
  • Must be exactly 30 or 60 (no other values allowed)

MentorId

  • Must be valid GUID format
  • Mentor must exist in database
  • Mentor must have "Approved" status

Batch Creation

  • Max 50 slots per batch request
  • All slots must pass individual validation
  • If any slot validation fails, entire batch fails (atomic operation)
  • Cannot create duplicate slots (same mentorId + startDateTime)

Deletion

  • Can only delete own slots (or Admin can delete any)
  • Cannot delete if IsBooked = true
  • Cannot delete if slot doesn't exist

Date Range Filters

  • startDate and endDate must be valid ISO 8601 dates
  • endDate must be after startDate if both provided
  • Max date range: 90 days

Security Considerations

Authorization

  • Verify JWT token on all authenticated endpoints
  • Extract user ID from JWT claims
  • Verify mentor ownership for create/delete operations
  • Admin can manage any mentor's slots

Business Logic Protection

  • Prevent deletion of booked slots (409 Conflict)
  • Prevent creating slots in the past
  • Enforce 24-hour minimum advance booking
  • Validate slot doesn't overlap with existing slot (same mentor, same time)

Rate Limiting

  • 100 requests per minute per user for slot creation
  • 200 requests per minute for public available-slots endpoint

Data Integrity

  • Use database transactions for batch operations
  • Atomic batch create: all or nothing
  • When session is cancelled, automatically update TimeSlot (IsBooked = false, SessionId = null)

Performance

  • Index on (MentorId, StartDateTime, IsBooked) for fast filtering
  • Cache mentor's available slots for 5 minutes
  • Pagination prevents large result sets

Testing Checklist

GET /api/mentors/{mentorId}/available-slots (Public)

  • <input type="checkbox" disabled=""> Get available slots without query params (default 7-day range)
  • <input type="checkbox" disabled=""> Get slots with custom date range
  • <input type="checkbox" disabled=""> Filter by durationMinutes (30 or 60)
  • <input type="checkbox" disabled=""> Verify only available slots returned (isBooked = false)
  • <input type="checkbox" disabled=""> Verify only future slots returned (>24h from now)
  • <input type="checkbox" disabled=""> Verify slots ordered by startDateTime ASC
  • <input type="checkbox" disabled=""> Get slots for non-existent mentor (404)
  • <input type="checkbox" disabled=""> Get slots when no slots available (404)
  • <input type="checkbox" disabled=""> Verify price matches mentor's rates

POST /api/mentors/{mentorId}/time-slots (Create Single)

  • <input type="checkbox" disabled=""> Create slot with valid data as mentor
  • <input type="checkbox" disabled=""> Create slot with startDateTime <24h in future (400)
  • <input type="checkbox" disabled=""> Create slot with invalid duration (not 30 or 60) (400)
  • <input type="checkbox" disabled=""> Create slot as different mentor (403)
  • <input type="checkbox" disabled=""> Create slot without authentication (401)
  • <input type="checkbox" disabled=""> Create duplicate slot at same time (409)
  • <input type="checkbox" disabled=""> Verify slot created with isBooked = false

POST /api/mentors/{mentorId}/time-slots (Batch Create)

  • <input type="checkbox" disabled=""> Create multiple slots (batch) with valid data
  • <input type="checkbox" disabled=""> Create batch >50 slots (400)
  • <input type="checkbox" disabled=""> Create batch with one invalid slot - verify all fail (400)
  • <input type="checkbox" disabled=""> Verify all slots created in batch have same createdAt

GET /api/mentors/{mentorId}/time-slots (Mentor View)

  • <input type="checkbox" disabled=""> Get own slots as mentor
  • <input type="checkbox" disabled=""> Get slots with date range filter
  • <input type="checkbox" disabled=""> Filter by isBooked status
  • <input type="checkbox" disabled=""> Verify pagination works correctly
  • <input type="checkbox" disabled=""> Verify booked slots include session details
  • <input type="checkbox" disabled=""> Verify canDelete flag is correct
  • <input type="checkbox" disabled=""> Verify summary statistics are accurate
  • <input type="checkbox" disabled=""> Try to view another mentor's slots (403)
  • <input type="checkbox" disabled=""> Get slots without authentication (401)

DELETE /api/mentors/{mentorId}/time-slots/{slotId}

  • <input type="checkbox" disabled=""> Delete available slot (isBooked = false) as mentor
  • <input type="checkbox" disabled=""> Try to delete booked slot (409)
  • <input type="checkbox" disabled=""> Try to delete another mentor's slot (403)
  • <input type="checkbox" disabled=""> Delete non-existent slot (404)
  • <input type="checkbox" disabled=""> Delete without authentication (401)
  • <input type="checkbox" disabled=""> Verify slot removed from database

Sample API Requests

Get Available Slots (Public):

GET http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/available-slots

Get Available Slots with Date Range:

GET http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/available-slots?startDate=2025-11-15&endDate=2025-11-22

Get Available 60-Minute Slots Only:

GET http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/available-slots?durationMinutes=60

Create Single Time Slot:

POST http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/time-slots
Authorization: Bearer {access-token}
Content-Type: application/json

{
  "startDateTime": "2025-11-15T14:00:00Z",
  "durationMinutes": 60
}

Create Multiple Time Slots (Batch):

POST http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/time-slots
Authorization: Bearer {access-token}
Content-Type: application/json

{
  "slots": [
    {
      "startDateTime": "2025-11-15T14:00:00Z",
      "durationMinutes": 60
    },
    {
      "startDateTime": "2025-11-15T16:00:00Z",
      "durationMinutes": 30
    },
    {
      "startDateTime": "2025-11-16T10:00:00Z",
      "durationMinutes": 60
    }
  ]
}

Get All Slots for Mentor:

GET http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/time-slots?page=1&pageSize=20
Authorization: Bearer {access-token}

Get Only Available Slots for Mentor:

GET http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/time-slots?isBooked=false
Authorization: Bearer {access-token}

Delete Time Slot:

DELETE http://localhost:5000/api/mentors/cc0e8400-e29b-41d4-a716-446655440007/time-slots/123
Authorization: Bearer {access-token}

PART 3: BACKEND IMPLEMENTATION GUIDE

Understanding the Relationships

Relationship 1: TimeSlot → Mentor (Many-to-One)

Meaning:

  • One mentor can have many time slots
  • Each time slot belongs to one mentor

In Code:

// TimeSlot.cs
public class TimeSlot
{
    public string MentorId { get; set; }  // Foreign Key
    public Mentor Mentor { get; set; }    // Navigation property
}

// Mentor.cs (you'll need to add this)
public class Mentor
{
    public string Id { get; set; }
    // ... existing properties ...

    // NEW: Add this collection property
    public ICollection<TimeSlot> TimeSlots { get; set; }  // One mentor has many slots
}

In Database:

Mentors Table (AspNetUsers)          TimeSlots Table
┌─────────────┐                      ┌─────────────────┐
│ Id (PK)     │◄─────────────────────│ MentorId (FK)   │
│ FirstName   │                      │ Id (PK)         │
│ LastName    │                      │ StartDateTime   │
└─────────────┘                      └─────────────────┘
     1                                       *
  (One Mentor)                         (Many TimeSlots)

How it works:

// EF Core can now do this automatically:
var mentor = await _context.Mentors
    .Include(m => m.TimeSlots)  // JOIN with TimeSlots table
    .FirstOrDefaultAsync(m => m.Id == mentorId);

// Now you can access:
var allMentorSlots = mentor.TimeSlots;  // List of TimeSlot objects

Relationship 2: TimeSlot → Session (One-to-One, Optional)

Meaning:

  • Each time slot can be linked to zero or one session (nullable)
  • When booked, SessionId is set; when available, SessionId is null

In Code:

// TimeSlot.cs
public class TimeSlot
{
    public int? SessionId { get; set; }  // Nullable FK (null = available)
    public Session Session { get; set; }  // Navigation property
}

// Session.cs (you'll need to add this)
public class Session
{
    public int Id { get; set; }
    // ... existing properties ...

    // NEW: Add this property
    public TimeSlot TimeSlot { get; set; }  // One session uses one slot
}

In Database:

TimeSlots Table                       Sessions Table
┌─────────────────┐                  ┌─────────────┐
│ Id (PK)         │                  │ Id (PK)     │
│ SessionId (FK)  │──────────────────►│ MentorId    │
│ IsBooked        │                  │ MenteeId    │
└─────────────────┘                  └─────────────┘
     1                                      1
  (One TimeSlot)                       (One Session)

How it works:

// Get slot with its session details
var slot = await _context.TimeSlots
    .Include(ts => ts.Session)  // JOIN with Sessions table
    .FirstOrDefaultAsync(ts => ts.Id == slotId);

if (slot.SessionId != null)
{
    Console.WriteLine($"Booked by: {slot.Session.MenteeId}");
}

Complete Relationship Diagram

User (AspNetUsers)
    ↓ (1:1)
Mentor
    ↓ (1:many)
TimeSlot ←──────┐
    ↓ (1:1)     │
Session         │
    ↓           │
Payment         │
                │
    (When cancelled, SessionId = null, slot becomes available again)

Entity Configuration (EF Core)

Complete Configuration Class

File: Backend/CareerRoute.Infrastructure/Data/Configurations/TimeSlotConfiguration.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using CareerRoute.Core.Entities;

namespace CareerRoute.Infrastructure.Data.Configurations
{
    public class TimeSlotConfiguration : IEntityTypeConfiguration<TimeSlot>
    {
        public void Configure(EntityTypeBuilder<TimeSlot> builder)
        {
            // Table name
            builder.ToTable("TimeSlots");

            // Primary Key
            builder.HasKey(ts => ts.Id);

            // Properties
            builder.Property(ts => ts.MentorId)
                .IsRequired()
                .HasMaxLength(450);  // Same as AspNetUsers.Id

            builder.Property(ts => ts.StartDateTime)
                .IsRequired();

            builder.Property(ts => ts.DurationMinutes)
                .IsRequired();

            builder.Property(ts => ts.IsBooked)
                .IsRequired()
                .HasDefaultValue(false);

            builder.Property(ts => ts.SessionId)
                .IsRequired(false);  // Nullable

            builder.Property(ts => ts.CreatedAt)
                .IsRequired();

            // RELATIONSHIP 1: TimeSlot → Mentor (Many-to-One)
            builder.HasOne(ts => ts.Mentor)           // Each TimeSlot has one Mentor
                .WithMany(m => m.TimeSlots)           // Each Mentor has many TimeSlots
                .HasForeignKey(ts => ts.MentorId)     // Foreign key column
                .OnDelete(DeleteBehavior.Cascade);    // If mentor deleted, delete their slots

            // RELATIONSHIP 2: TimeSlot → Session (One-to-One, Optional)
            builder.HasOne(ts => ts.Session)          // Each TimeSlot has one Session
                .WithOne(s => s.TimeSlot)             // Each Session has one TimeSlot
                .HasForeignKey<TimeSlot>(ts => ts.SessionId)  // Foreign key column
                .OnDelete(DeleteBehavior.SetNull);    // If session deleted, keep slot (set SessionId = null)

            // INDEXES (for performance)
            builder.HasIndex(ts => new { ts.MentorId, ts.StartDateTime })
                .HasDatabaseName("IX_TimeSlots_MentorId_StartDateTime");

            builder.HasIndex(ts => ts.IsBooked)
                .HasDatabaseName("IX_TimeSlots_IsBooked");

            builder.HasIndex(ts => ts.SessionId)
                .HasDatabaseName("IX_TimeSlots_SessionId");
        }
    }
}

Updating Existing Entities

1. Update Mentor Entity

File: Backend/CareerRoute.Core/Entities/Mentor.cs

public class Mentor
{
    public string Id { get; set; }
    // ... all existing properties (Bio, Rate30Min, Rate60Min, etc.) ...

    // ADD THIS: Navigation property for TimeSlots
    public ICollection<TimeSlot> TimeSlots { get; set; }  // One mentor, many slots
}

2. Update Session Entity

File: Backend/CareerRoute.Core/Entities/Session.cs

public class Session
{
    public int Id { get; set; }
    // ... all existing properties (MentorId, MenteeId, Status, etc.) ...

    // ADD THIS: Navigation property for TimeSlot
    public TimeSlot TimeSlot { get; set; }  // One session, one slot
}

ApplicationDbContext Registration

File: Backend/CareerRoute.Infrastructure/Data/ApplicationDbContext.cs

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    // Existing DbSets
    public DbSet<Mentor> Mentors { get; set; }
    public DbSet<Session> Sessions { get; set; }
    public DbSet<Payment> Payments { get; set; }

    // ADD THIS: New DbSet for TimeSlots
    public DbSet<TimeSlot> TimeSlots { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Existing configurations
        modelBuilder.ApplyConfiguration(new MentorConfiguration());
        modelBuilder.ApplyConfiguration(new SessionConfiguration());

        // ADD THIS: Apply TimeSlot configuration
        modelBuilder.ApplyConfiguration(new TimeSlotConfiguration());
    }
}

Database Migration Result

Commands to Run

dotnet ef migrations add AddTimeSlotEntity --project Backend/CareerRoute.Infrastructure --startup-project Backend/CareerRoute.API
dotnet ef database update --project Backend/CareerRoute.Infrastructure --startup-project Backend/CareerRoute.API

Generated SQL

-- Creates the table
CREATE TABLE [TimeSlots] (
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [MentorId] NVARCHAR(450) NOT NULL,
    [SessionId] INT NULL,
    [StartDateTime] DATETIME2 NOT NULL,
    [DurationMinutes] INT NOT NULL,
    [IsBooked] BIT NOT NULL DEFAULT 0,
    [CreatedAt] DATETIME2 NOT NULL,

    -- Foreign key constraints
    CONSTRAINT [FK_TimeSlots_Mentors] FOREIGN KEY ([MentorId]) 
        REFERENCES [AspNetUsers]([Id]) ON DELETE CASCADE,

    CONSTRAINT [FK_TimeSlots_Sessions] FOREIGN KEY ([SessionId]) 
        REFERENCES [Sessions]([Id]) ON DELETE SET NULL
);

-- Creates indexes for performance
CREATE INDEX [IX_TimeSlots_MentorId_StartDateTime] 
    ON [TimeSlots]([MentorId], [StartDateTime]);

CREATE INDEX [IX_TimeSlots_IsBooked] 
    ON [TimeSlots]([IsBooked]);

CREATE INDEX [IX_TimeSlots_SessionId] 
    ON [TimeSlots]([SessionId]);

Query Examples with Relations

Example 1: Get All Available Slots for a Mentor

var availableSlots = await _context.TimeSlots
    .Where(ts => ts.MentorId == mentorId)
    .Where(ts => ts.IsBooked == false)
    .Where(ts => ts.StartDateTime > DateTime.UtcNow.AddHours(24))
    .OrderBy(ts => ts.StartDateTime)
    .ToListAsync();

// SQL Generated:
// SELECT * FROM TimeSlots
// WHERE MentorId = @p0 AND IsBooked = 0 AND StartDateTime > @p1
// ORDER BY StartDateTime

Example 2: Book a Slot (Using Relations)

var slot = await _context.TimeSlots
    .Include(ts => ts.Mentor)  // JOIN to get mentor details
    .FirstOrDefaultAsync(ts => ts.Id == slotId);

if (!slot.IsBooked)
{
    var session = new Session
    {
        MentorId = slot.MentorId,
        ScheduledStartTime = slot.StartDateTime,
        // ... other properties
    };

    _context.Sessions.Add(session);
    await _context.SaveChangesAsync();  // Session gets Id assigned

    // Link slot to session
    slot.IsBooked = true;
    slot.SessionId = session.Id;  // Set the foreign key

    await _context.SaveChangesAsync();  // Updates TimeSlot row
}

// SQL Generated:
// INSERT INTO Sessions (...) VALUES (...)
// UPDATE TimeSlots SET IsBooked = 1, SessionId = @sessionId WHERE Id = @slotId

Example 3: Cancel Session (Release Slot)

var session = await _context.Sessions
    .Include(s => s.TimeSlot)  // JOIN to get the time slot
    .FirstOrDefaultAsync(s => s.Id == sessionId);

if (session != null)
{
    session.Status = SessionStatus.Cancelled;

    // Release the time slot (make it available again)
    if (session.TimeSlot != null)
    {
        session.TimeSlot.IsBooked = false;
        session.TimeSlot.SessionId = null;  // Unlink from session
    }

    await _context.SaveChangesAsync();
}

// SQL Generated:
// UPDATE Sessions SET Status = 'Cancelled' WHERE Id = @sessionId
// UPDATE TimeSlots SET IsBooked = 0, SessionId = NULL WHERE SessionId = @sessionId

Example 4: Get Mentor with All Slots and Sessions

var mentor = await _context.Mentors
    .Include(m => m.TimeSlots)          // Include all time slots
        .ThenInclude(ts => ts.Session)  // For each slot, include linked session
    .FirstOrDefaultAsync(m => m.Id == mentorId);

// Now you can access:
foreach (var slot in mentor.TimeSlots)
{
    if (slot.IsBooked && slot.Session != null)
    {
        Console.WriteLine($"Slot booked by: {slot.Session.MenteeId}");
    }
    else
    {
        Console.WriteLine($"Slot available at: {slot.StartDateTime}");
    }
}

Summary of Relations

Relationship Type Meaning Delete Behavior
TimeSlot → Mentor Many-to-One One mentor has many slots Cascade (delete slots if mentor deleted)
TimeSlot → Session One-to-One (Optional) Slot linked to session when booked Set Null (keep slot if session deleted)

Key Implementation Points

  1. TimeSlot entity is the core of the availability system
  2. MentorId (FK) links slot to mentor (required, non-nullable)
  3. SessionId (FK) links slot to session (optional, nullable - only set when booked)
  4. IsBooked flag provides quick filtering for available slots
  5. Navigation properties enable EF Core to automatically JOIN tables
  6. Cascade delete for Mentor → TimeSlot (if mentor deleted, slots deleted)
  7. Set null delete for Session → TimeSlot (if session deleted, slot becomes available again)
  8. Indexes improve query performance for common filters (mentor, booking status, date)

Next Steps

After implementing this entity:

  1. Create TimeSlot repository (ITimeSlotRepository, TimeSlotRepository)
  2. Create TimeSlot service (ITimeSlotService, TimeSlotService)
  3. Create TimeSlot DTOs (TimeSlotDto, CreateTimeSlotDto, etc.)
  4. Create TimeSlotController with CRUD endpoints
  5. Update SessionBookingService to validate against TimeSlot
  6. Update cancellation logic to release TimeSlot when session cancelled
📋 Document Outline