# Time-Slot & Scheduling System - Architecture Diagrams ## 1. Data Model Relationships ``` ┌─────────────────────────────────────────────────────────────────┐ │ WEEK TEMPLATE │ │ │ │ dayOfWeek (1-7, ISO standard) │ │ startTime, endTime (e.g., "09:00", "10:00") │ │ capacity (default 1) │ │ isActive (can disable template) │ │ │ │ ↓ (auto-generates) │ └─────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────┐ │ TIME SLOT │ │ │ │ date (calendar date, midnight UTC) │ │ startTime, endTime (from template) │ │ capacity (from template) │ │ bookedCount (# of current bookings) │ │ status (OPEN | FULL | CLOSED) │ │ source (TEMPLATE | MANUAL) │ │ templateId (reference to WeekTemplate) │ │ │ │ ↓ (has many) ↓ (belongs to) │ │ └─────────────────────┘ │ └─────────────────────────────────────────────────────────────────┘ ↑ ↑ │ │ └────────────┬───────────────────────── │ │ 1:1 booking │ ↓ ┌─────────────────────────────────────────────────────────────────┐ │ BOOKING │ │ │ │ userId (FK to User) │ │ timeSlotId (FK to TimeSlot) │ │ membershipId (FK to Membership) │ │ status (CONFIRMED | CANCELLED | COMPLETED | NO_SHOW) │ │ cancelledAt (timestamp when cancelled) │ │ │ │ Constraints: │ │ - Unique [userId, timeSlotId] (one booking per user per slot) │ │ - ONE booking per TimeSlot per user │ └─────────────────────────────────────────────────────────────────┘ ↑ ↑ │ │ └─────────────┬───────────┘ │ belongs to │ ↓ ┌─────────────────────────────────────────────────────────────────┐ │ MEMBERSHIP │ │ │ │ userId (FK to User) │ │ cardTypeId (FK to CardType) │ │ remainingTimes (for TIMES/TRIAL card types) │ │ expireDate (for DURATION card types) │ │ status (ACTIVE | EXPIRED | USED_UP) │ └─────────────────────────────────────────────────────────────────┘ ``` --- ## 2. Daily Scheduler Timeline ``` 00:00 ───────────────────────────────────────────────── │ ├─ [Midnight] - Time passes │ ├─ System running in background │ ├─ ... (various other operations) │ 02:00 ───────────────────────────────────────────────── │ ├─► 🟢 SLOT GENERATION │ SlotGeneratorService.generateSlots(14) │ │ ├─ Query WeekTemplate (all isActive=true) │ ├─ For each day in [tomorrow, tomorrow+14): │ │ ├─ Get ISO weekday │ │ ├─ Find matching templates │ │ └─ Create TimeSlot entries │ ├─ Batch insert with skipDuplicates: true │ └─ Log: "Generated X new time slots" │ 02:30 ───────────────────────────────────────────────── │ ├─► 🟡 SLOT CLEANUP │ SlotGeneratorService.cleanupExpiredSlots() │ │ ├─ Find all OPEN slots with date < TODAY │ ├─ Mark as CLOSED │ └─ Log: "Closed X expired time slots" │ 03:00 ───────────────────────────────────────────────── │ ├─► 🟠 MEMBERSHIP CHECK │ SlotGeneratorService.checkExpiredMemberships() │ │ ├─ Update ACTIVE memberships with expireDate < NOW │ │ └─ Set status = EXPIRED │ ├─ Update ACTIVE memberships with remainingTimes = 0 │ │ └─ Set status = USED_UP │ └─ Log: "Expired X by date, Y by sessions" │ ├─ ... (users awake, making bookings) │ 22:00 ───────────────────────────────────────────────── │ ├─► 🔴 BOOKING COMPLETION │ SlotGeneratorService.completeBookings() │ │ ├─ Find CONFIRMED bookings with timeSlot.date < TODAY │ ├─ Mark as COMPLETED │ └─ Log: "Completed X past bookings" │ └─ (Day ends, repeat tomorrow) ``` --- ## 3. Booking Lifecycle ``` ┌─────────────────────────────────────────────────────────┐ │ BOOKING CREATION │ │ (POST /booking) │ └─────────────────────────────────────────────────────────┘ │ ├─ Input: { timeSlotId, membershipId } │ ├─ TRANSACTION START ────────────────── │ │ │ ├─► Fetch TimeSlot │ │ └─ Check: status = OPEN? ✓ │ │ │ ├─► Check Duplicate │ │ └─ Query: SELECT * FROM bookings WHERE userId=? AND timeSlotId=? │ │ └─ Must not exist │ │ │ ├─► Fetch Membership │ │ └─ Check: belongs to user? ✓ │ │ └─ Check: status = ACTIVE? ✓ │ │ └─ Check: has capacity? │ │ └─ IF TIMES/TRIAL: remainingTimes > 0? ✓ │ │ └─ IF DURATION: expireDate > NOW? ✓ │ │ │ ├─► CREATE Booking(CONFIRMED) │ │ └─ INSERT: { userId, timeSlotId, membershipId, status: CONFIRMED } │ │ │ ├─► UPDATE TimeSlot │ │ ├─ bookedCount = bookedCount + 1 │ │ ├─ IF bookedCount >= capacity: │ │ │ └─ status = FULL │ │ └─ ELSE: │ │ └─ status = OPEN (unchanged) │ │ │ ├─► UPDATE Membership (if TIMES/TRIAL) │ │ ├─ remainingTimes = remainingTimes - 1 │ │ ├─ IF remainingTimes <= 0: │ │ │ └─ status = USED_UP │ │ └─ ELSE: │ │ └─ status = ACTIVE (unchanged) │ │ │ └─ TRANSACTION COMMIT ────────────── │ └─► Return: BookingWithRelations (includes timeSlot, membership) ┌─────────────────────────────────────────────────────────┐ │ BOOKING CANCELLATION │ │ (PUT /booking/:id/cancel) │ └─────────────────────────────────────────────────────────┘ │ ├─ Fetch Booking + TimeSlot + Membership │ ├─ Check: booking.status = CONFIRMED? ✓ │ ├─ Calculate Refund Eligibility │ │ │ ├─ cancelHoursLimit = StudioConfig.cancelHoursLimit (default 2) │ ├─ slotStartMs = Date(timeSlot.date) + timeSlot.startTime │ ├─ deadlineMs = NOW + (cancelHoursLimit * 3600 * 1000) │ │ │ ├─ IF slotStartMs >= deadlineMs: │ │ └─ withinLimit = TRUE ✓ (User gets refund) │ └─ ELSE: │ └─ withinLimit = FALSE (No refund) │ ├─ TRANSACTION START ────────────────── │ │ │ ├─► UPDATE Booking │ │ ├─ status = CANCELLED │ │ └─ cancelledAt = NOW │ │ │ ├─► UPDATE TimeSlot │ │ ├─ bookedCount = MAX(0, bookedCount - 1) │ │ ├─ IF slot was FULL: │ │ │ └─ status = OPEN │ │ └─ ELSE: │ │ └─ status = (unchanged) │ │ │ ├─► IF withinLimit = TRUE: │ │ └─ UPDATE Membership (if TIMES/TRIAL) │ │ ├─ remainingTimes = remainingTimes + 1 │ │ ├─ IF was USED_UP: │ │ │ └─ status = ACTIVE │ │ └─ ELSE: │ │ └─ status = (unchanged) │ │ │ └─ TRANSACTION COMMIT ────────────── │ └─► Return: { booking, refunded: boolean } ``` --- ## 4. Slot Generation from Template ``` Template Setup: ┌─────────────────────────────────────────────────────┐ │ PUT /admin/week-template │ │ │ │ { │ │ "templates": [ │ │ { │ │ "dayOfWeek": 1, // Monday (ISO standard)│ │ "startTime": "09:00", │ │ "endTime": "10:00", │ │ "capacity": 1, // Private lesson │ │ "isActive": true │ │ }, │ │ { │ │ "dayOfWeek": 5, // Friday (ISO standard)│ │ "startTime": "18:00", │ │ "endTime": "19:00", │ │ "capacity": 1, │ │ "isActive": true │ │ } │ │ ] │ │ } │ └─────────────────────────────────────────────────────┘ ↓ Stored in database as WeekTemplates ↓ Each day at 02:00 UTC, generateSlots(14) runs: Today: Monday, April 7, 2026 │ ├─ Tomorrow = Tuesday, April 8 │ ├─ For next 14 days: │ │ Day 0: Tue (ISO 2) → no matching template → skip │ Day 1: Wed (ISO 3) → no matching template → skip │ Day 2: Thu (ISO 4) → no matching template → skip │ Day 3: Fri (ISO 5) → MATCH! template (18:00-19:00) │ └─ CREATE TimeSlot(date=Apr12, time=18:00-19:00, capacity=1) │ │ Day 4: Sat (ISO 6) → no matching template → skip │ Day 5: Sun (ISO 7) → no matching template → skip │ Day 6: Mon (ISO 1) → MATCH! template (09:00-10:00) │ └─ CREATE TimeSlot(date=Apr14, time=09:00-10:00, capacity=1) │ │ Day 7: Tue (ISO 2) → no matching template → skip │ ... (repeats pattern) │ └─ All created with: ├─ status = OPEN ├─ bookedCount = 0 ├─ source = TEMPLATE ├─ templateId = (reference to template) └─ skipDuplicates = true (safe to re-run) Result: 14 Friday 18:00-19:00 slots generated 14 Monday 09:00-10:00 slots generated Total: 28 new slots ``` --- ## 5. User Booking Flow (Frontend → Backend) ``` ┌──────────────────────────────────────────────────────┐ │ MEMBER CLIENT │ └──────────────────────────────────────────────────────┘ │ │ 1. Click "View Available Slots" │ ├─► GET /time-slot/available?date=2026-04-10 │ │ Response: [{ │ id: "slot-123", │ date: "2026-04-10", │ startTime: "09:00", │ endTime: "10:00", │ status: "OPEN", │ bookedCount: 0, │ capacity: 1, │ isBookedByMe: false, ← User's booking status │ myBookingId: null │ }, ...] │ ├─ Display available slots in UI │ │ 2. User selects slot and membership │ ├─► POST /booking │ Body: { │ "timeSlotId": "slot-123", │ "membershipId": "mem-456" │ } │ │ Response: { │ id: "booking-789", │ userId: "user-001", │ timeSlotId: "slot-123", │ status: "CONFIRMED", │ createdAt: "2026-04-05T10:30:00Z", │ timeSlot: { ... }, ← Full slot details │ membership: { ... } ← Full membership details │ } │ ├─ Display confirmation │ │ 3. [Later] User cancels booking │ └─► PUT /booking/booking-789/cancel Response: { booking: { ... }, refunded: true ← Was refund issued? } Display: "Booking cancelled. You've been refunded." ``` --- ## 6. State Transitions ### TimeSlot Status ``` ┌─────────────────────────────────┐ │ AUTO-GENERATED │ │ by generateSlots() │ └─────────────┬───────────────────┘ │ ↓ ┌─────────────────┐ │ OPEN │ ← Can accept bookings │ (bookedCount < │ bookedCount starts at 0 │ capacity) │ └────────┬────────┘ │ ┌──────────┼──────────┐ │ │ │ │ │ │ [booking │ [cleanup creates] │ or manual │ close] │ │ │ ↓ ↓ ↓ FULL CLOSED (bookedCount >= capacity) │ │ [booking cancelled] ↓ OPEN (back to) Once slot date passes: ├─ OPEN → CLOSED (by cleanup job at 02:30 UTC) ├─ FULL → CLOSED (when cleanup runs) └─ CANCELLED bookings don't affect slot status ``` ### Booking Status ``` ┌──────────────────┐ │ CONFIRMED │ ← Default when created │ │ User has active reservation └────────┬─────────┘ │ ┌─────┼─────┐ │ │ │ [user │ [auto-mark cancels] │ when date │ │ passes] │ │ │ ↓ ↓ ↓ CANCELLED COMPLETED (free (slot time cancellation has passed) until deadline) CANCELLED bookings stay in history COMPLETED bookings show in past bookings CONFIRMED bookings show in upcoming bookings ``` ### Membership Status ``` ┌─────────────────┐ │ ACTIVE │ ← Can book classes │ │ Has remaining capacity └────────┬────────┘ │ ┌─────┼─────┐ │ │ │ [booking │ [auto-check depletes │ by scheduler sessions] │ at 03:00 UTC] │ │ │ ↓ ↓ ↓ USED_UP EXPIRED (for (for TIMES) DURATION) USED_UP: remainingTimes = 0 (for TIMES/TRIAL only) EXPIRED: expireDate < NOW (for DURATION) OR date-based expiry All non-ACTIVE statuses prevent new bookings ``` --- ## 7. Timezone & Date Handling ``` User Timezone: Local (browser/app) API Timezone: UTC (backend) Database: UTC ┌──────────────────────────────────────────────┐ │ User in Shanghai (UTC+8) │ │ Local time: 2026-04-10 15:00:00 CST │ │ UTC time: 2026-04-10 07:00:00 UTC │ └──────────────────────────────────────────────┘ │ ├─ Query: GET /time-slot/available?date=2026-04-10 │ (User sends local date, frontend converts to ISO) │ ├─ Backend receives: │ ├─ Parse "2026-04-10" │ ├─ Build start of day: 2026-04-10T00:00:00 UTC │ ├─ Build end of day: 2026-04-10T23:59:59.999 UTC │ ├─ Query TimeSlots WHERE date BETWEEN [00:00, 23:59] │ └─ Return slots for that calendar day in UTC ┌──────────────────────────────────────────────┐ │ TimeSlot Storage (Database) │ │ │ │ date: 2026-04-10 (DATE type, midnight UTC) │ │ startTime: "09:00" (string, no timezone) │ │ endTime: "10:00" (string, no timezone) │ │ │ │ When combined: │ │ Slot datetime = 2026-04-10T09:00:00 UTC │ └──────────────────────────────────────────────┘ │ ├─ For Shanghai user (UTC+8): │ └─ 09:00 UTC = 17:00 CST (5 PM) │ └─ For New York user (UTC-4): └─ 09:00 UTC = 05:00 EDT (5 AM) Scheduler (UTC times): ┌─────────────────────────────────────────────┐ │ 02:00 UTC = Generate slots │ │ 02:30 UTC = Cleanup │ │ 03:00 UTC = Check memberships │ │ 22:00 UTC = Complete bookings │ │ │ │ When scheduler checks "is date < today?": │ │ ├─ Create midnight UTC boundary │ │ ├─ Compare slot.date < today's midnight │ │ └─ Mark as CLOSED/COMPLETED if older │ └─────────────────────────────────────────────┘ ``` --- ## 8. Error Handling Tree ``` POST /booking │ ├─ TimeSlot not found │ └─ Return: NotFoundException │ ├─ TimeSlot.status ≠ OPEN │ └─ Return: BadRequestException("TimeSlot is not available") │ ├─ Duplicate booking exists │ └─ Return: ConflictException("Already booked this slot") │ ├─ Membership not found │ └─ Return: NotFoundException │ ├─ Membership.userId ≠ current user │ └─ Return: ForbiddenException("Not your membership") │ ├─ Membership.status ≠ ACTIVE │ └─ Return: BadRequestException("Membership inactive") │ ├─ Card type is TIMES/TRIAL: │ │ │ └─ remainingTimes ≤ 0 │ └─ Return: BadRequestException("No remaining times") │ └─ Card type is DURATION: │ └─ expireDate < NOW └─ Return: BadRequestException("Membership expired") PUT /booking/:id/cancel │ ├─ Booking not found │ └─ Return: NotFoundException │ ├─ Booking.userId ≠ current user │ └─ Return: ForbiddenException("Not your booking") │ ├─ Booking.status ≠ CONFIRMED │ └─ Return: BadRequestException("Can't cancel this status") │ └─ ✓ Cancel successful ├─ Check refund eligibility ├─ Update booking status ├─ Update timeSlot bookedCount └─ Conditionally refund membership ``` --- ## 9. Integration Points ``` ┌─────────────────────────────────────────────────────┐ │ APP MODULE │ │ (packages/server/src/app.module.ts) │ └─────────────────────────────────────────────────────┘ │ ├─ imports: [ │ AuthModule, │ TimeSlotModule, ← Time-Slot logic │ SchedulerModule, ← Auto jobs (cron) │ BookingModule, ← Booking logic │ MembershipModule, ← Membership checks │ StudioModule, ← Config (cancelHoursLimit) │ ... │ ] │ └─ Controllers route to: │ ├─ TimeSlotController (public slots viewing) ├─ AdminTimeSlotController (templates, admin actions) ├─ BookingController (create, cancel bookings) └─ ... (other endpoints) SchedulerModule dependencies: ├─ ScheduleModule.forRoot() ← Enable @Cron decorators └─ TimeSlotModule ← Access to SlotGeneratorService BookingModule dependencies: ├─ MembershipModule ← Check membership status └─ StudioModule ← Read cancelHoursLimit config Services call chain: ├─ Controller │ ├─ TimeSlotService │ │ └─ PrismaService │ └─ BookingService │ ├─ PrismaService │ ├─ MembershipService │ └─ StudioService ```