Skip to main content

Database Schema

This page brings together the system design diagrams for the Tutor Management System: how the components fit together (component diagram), how the data is modelled (ER diagram), and how the core tutor-allocation flow works end to end (sequence diagram).

Component Diagram​

This diagram shows the high-level architecture of the Tutor Management System: how the frontend, backend, database, and third-party services connect.

Notes​

  • Non-monolithic split: the frontend (React, deployed on Vercel) and backend (Node.js, deployed on Azure App Service) are separate deployable applications, communicating only over a hand-written REST API — satisfying the course's non-monolithic architecture requirement.
  • Auth: Firebase Auth is used as an established authentication library, not a self-built system. The frontend handles sign-up/sign-in directly against Firebase; the backend verifies the resulting Firebase ID token on each request to identify the user and their role.
  • External API integration: not yet decided at MS1 — the connection point is drawn here as planned/dashed to acknowledge the requirement exists, and will be filled in once the specific service is chosen (e.g. a timetable/calendar service).
  • Database: SQL Server, accessed exclusively through Prisma ORM from the backend — the frontend never talks to the database directly.

Data Model (ER Diagram)​

This reflects the current schema.prisma. All user roles (student, tutor, lecturer, organiser, admin) live on User.role — there are no separate role tables. It covers the application/waitlist flow, course assignment, timesheets, and overflow work.

Notes on modeling decisions​

  • One User table, roles as a string: User.role distinguishes student / tutor / lecturer / organiser / admin. There are no separate role tables — every tutor-role construct (availability, busy blocks, assignments, timesheets, overflow claims) references User directly, so a user's data survives role changes.
  • Waitlist is a status, not a new entity: Application.status moves through PENDING → WAITLISTED / APPROVED / REJECTED. When an approved applicant declines or a TutorAssignment frees up, the next-ranked WAITLISTED application (ordered by courseMark) can be promoted to APPROVED without any schema change.
  • TutorAssignment is the join entity between User (tutor role) and Course, carrying only the semester — the many-to-many relationship of which users tutor which courses. Hours are not stored: they are derived from the course's slots (each slot rounds up to at least one whole hour), and the overall cap lives in SystemSetting (TUTOR_MONTHLY_CLAIMABLE_HOURS, set by an organiser).
  • Availability vs CourseSlot: a tutor's own weekly availability (Availability) is checked against each course's fixed teaching times (CourseSlot) to detect scheduling clashes — these are separate tables because tutors set availability once per term, while course slots are fixed by the timetable. Both reference the shared TimeSlot catalogue.

:::note Models added after this diagram was drawn

schema.prisma has since gained three models that are not yet shown above: Event (a lecturer-scheduled calendar event on a course date, which tutors claim as a timesheet entry), RoleApplication (a student's application to become a tutor or lecturer, reviewed by the organiser), and EmailLog (an audit log for outgoing notification emails, with duplicate-send prevention).

:::

Sequence Diagram: Assigning a Tutor to a Course​

This shows the core Basic-tier flow: a lecturer assigns a tutor to a course from the allocation board, with all three validation checks (marks, timetable clash, remaining hours) run server-side through one combined validation endpoint.

Notes​

  • One combined endpoint (POST /assignments/validate) runs all three checks server-side in a single request/response cycle, rather than three separate calls or purely client-side checks. This keeps the validation logic centralized and easy to test (directly satisfies the automated API testing rubric line).
  • Live feedback: the check runs as soon as the lecturer selects a tutor and clicks "Assign" — before anything is saved — satisfying the brief's requirement that a broken choice be "visible as it is made, not after it is saved."
  • Two-step confirm: a final POST /assignments re-validates server-side at the moment of actual commit, guarding against race conditions (e.g. two lecturers assigning the same tutor to different courses at the same time, both passing the initial check).