Database Design

Toodle uses PostgreSQL hosted on Supabase, accessed through Prisma ORM by the Express backend. React calls the backend's REST API; the backend owns authorisation, eligibility checks and database operations. Supabase also provides avatar storage, which is separate from the relational data model.

This reference describes the 28 Prisma models in the verified backend origin/develop schema and the connected database metadata inspected on 11 October 2026. Model names refer to Prisma; the inventory gives their mapped PostgreSQL table names. The connected database contains these 28 application tables plus Prisma's internal _prisma_migrations table.

Model Inventory

Users and Access

Prisma model	PostgreSQL table	Responsibility
User	users	Auth0 identity, profile, application role, onboarding and account state.
StaffAccessRequest	staff_access_requests	A user's staff-access request, status and administrative review.
StaffInvitation	staff_invitations	Email-based Admin/Lecturer invitations, proposed course assignments and expiry.
AccountClosure	account_closures	Durable external cleanup, retry scheduling and completion for a closed account.
CalendarFeedToken	calendar_feed_tokens	Hashed credentials for private, revocable calendar subscriptions.

Courses and Applications

Prisma model	PostgreSQL table	Responsibility
Course	courses	Course catalogue, staffing requirements and application settings.
CourseCoordinator	course_coordinators	Lecturer-to-course assignments and the assigning administrator.
CourseApplication	course_applications	Student/Tutor applications and staff review decisions.
TutorMark	tutor_marks	A user's course mark, verification status and review details.

Allocations and Scheduling

Prisma model	PostgreSQL table	Responsibility
Allocation	allocations	Recurring tutor-to-course assignments, weekly hours, status and locks.
AllocationDraft	allocation_drafts	Saved allocation plans and their creator.
DraftAllocationEntry	draft_allocation_entries	Proposed tutor/course assignments and explanations within a draft.
CourseSession	course_sessions	Recurring teaching sessions, weekdays, times, type and venue.
Availability	availability	A user's recurring free-time windows.
AvailabilityException	availability_exceptions	Dated changes to recurring availability.
AcademicTerm	academic_terms	Teaching blocks that bound session occurrence generation.
Holiday	holidays	Institution-wide non-teaching dates, entered manually or retrieved from Wits.
Excusal	excusals	Requests to miss a session occurrence, with duration and review history.
SessionSwap	session_swaps	Dated exchanges, participant consent, staff decisions and reversal details.
SessionResponsibility	session_responsibilities	Original/current responsibility for an allocation's dated session slot.
OverflowPost	overflow_posts	General volunteering work or cover for a specific missed occurrence.
OverflowClaim	overflow_claims	Volunteer claims and their review outcomes.

Timesheets and Payments

Prisma model	PostgreSQL table	Responsibility
Timesheet	timesheets	Weekly work submission, review state, submission snapshots and applied rate.
TimesheetEntry	timesheet_entries	Dated hours for a course, optionally linked to an allocation/session.
TutorRate	tutor_rates	Effective-dated hourly rate history and corrections.
CourseBudget	course_budgets	A course's budget and recorded spending.

Notifications and Auditing

Prisma model	PostgreSQL table	Responsibility
Notification	notifications	In-app messages, read state, email outcomes and reminder deduplication.
AuditLog	audit_logs	Actor and before/after snapshots of significant entity changes.

There is no separate payment-transaction model. Timesheet status, rate snapshots and course spending support the current payment-related workflow.

Complete System ERD

Open the complete system ERD (SVG) to enlarge and inspect all 28 models. The smaller diagrams below remain the reading guide. The complete diagram includes every live foreign key, the two Prisma-only draft-entry relationships and the legacy course-coordinator foreign key. End labels show cardinalities; dashed and dotted lines distinguish the differences.

Relationships

The four diagrams show the current Prisma-declared relationships. || means exactly one, o| means zero or one, and o{ means zero or many. Repeated User, Course and scheduling entities provide context across diagrams. Optional foreign keys are shown explicitly; scalar identifiers and arrays without relational constraints are explained separately.

Users, Staff Access and Course Coordination

erDiagram
    User ||--o| StaffAccessRequest : requests
    User o|--o{ StaffAccessRequest : reviews
    User o|--o{ StaffInvitation : issues
    User ||--o| AccountClosure : has_cleanup
    User ||--o{ CalendarFeedToken : owns
    User ||--o{ CourseCoordinator : coordinates
    User o|--o{ CourseCoordinator : assigns
    Course ||--o{ CourseCoordinator : has_coordinators

Users have the roles ADMIN, LECTURER, TUTOR or STUDENT. Lecturer management access comes from CourseCoordinator membership; it is enforced by backend permissions rather than a foreign key that restricts user roles.

Staff access adds emailVerified and staffOnboardingRole to User. Requests are unique per user; invitations are unique by stored email and expire after seven days under the service's policy. Invitation redemption requires verified Auth0 email matching. StaffInvitation.courseIds is a text array, not a join table: individual course IDs have no foreign keys and are checked by the service.

Account closure adds accountStatus (ACTIVE, CLOSING, CLOSED), closedAt and tokenValidAfter. Its one-to-one cleanup record retains the original Auth0 identifier, cleanup progress and retry/lease information. Closure blocks access and anonymises current profile fields while retaining academic and payroll history; historical snapshots are not comprehensively anonymised. Feed credentials are stored only as SHA-256 hashes, with plaintext returned at creation/rotation.

Course Applications, Allocation Planning and Availability

erDiagram
    User ||--o{ CourseApplication : applies
    User o|--o{ CourseApplication : reviews
    Course ||--o{ CourseApplication : receives
    User ||--o{ TutorMark : has_marks
    User o|--o{ TutorMark : reviews
    Course ||--o{ TutorMark : has_marks
    User ||--o{ Allocation : receives
    User o|--o{ Allocation : assigns
    Course ||--o{ Allocation : has_allocations
    Course ||--o{ CourseSession : schedules
    User ||--o{ AllocationDraft : creates
    AllocationDraft ||--o{ DraftAllocationEntry : contains
    User ||--o{ DraftAllocationEntry : proposed_tutor
    Course ||--o{ DraftAllocationEntry : proposed_course
    User ||--o{ Availability : declares
    User ||--o{ AvailabilityException : adjusts
    AcademicTerm {
        string id PK
        date startDate
        date endDate
    }
    Holiday {
        string id PK
        date date
    }

Applications, marks and allocations each associate a user with a course. Allocation.sessionIds selects recurring sessions; an empty array means all course sessions. Its elements have no individual session foreign keys, so membership is checked in business logic. Active allocations consume staffing capacity; pending and removed records represent unresolved assignments and retained history.

Each draft belongs to a creator and contains proposed entries. The diagram's tutor/course links for DraftAllocationEntry are declared in Prisma, but those two foreign keys are absent from the inspected PostgreSQL structure; the draft foreign key is present.

Academic terms and holidays are independent calendar records, not foreign-key children of Course or CourseSession. The backend combines them with recurring sessions and availability exceptions to determine valid dates.

Dated Responsibilities, Excusals and Volunteering

erDiagram
    User ||--o{ Excusal : requests
    User o|--o{ Excusal : reviews
    Allocation ||--o{ Excusal : concerns
    CourseSession o|--o{ Excusal : identifies_session
    Excusal o|--o| OverflowPost : generates_cover
    Course ||--o{ OverflowPost : offers_work
    User ||--o{ OverflowPost : creates
    CourseSession o|--o{ OverflowPost : identifies_session
    SessionResponsibility o|--o{ OverflowPost : needs_cover
    OverflowPost ||--o{ OverflowClaim : receives
    User ||--o{ OverflowClaim : volunteers
    User o|--o{ OverflowClaim : reviews
    User ||--o{ SessionSwap : requests
    User o|--o{ SessionSwap : receives
    User o|--o{ SessionSwap : reviews
    Allocation ||--o{ SessionSwap : origin_allocation
    Allocation ||--o{ SessionSwap : target_allocation
    CourseSession o|--o{ SessionSwap : origin_session
    CourseSession o|--o{ SessionSwap : target_session
    Allocation ||--o{ SessionResponsibility : identifies_slot
    CourseSession ||--o{ SessionResponsibility : occurs_on
    Course ||--o{ SessionResponsibility : belongs_to
    User ||--o{ SessionResponsibility : original_tutor
    User o|--o{ SessionResponsibility : current_tutor

A responsibility row identifies an allocation, session and date, preserves the scheduled times and records the original and current tutor. A null current tutor can represent a slot awaiting cover; inactive rows represent restored ordinary responsibility. Approved swaps change dated responsibility without exchanging recurring allocation ownership.

New excusals identify an actual session occurrence and preserve its duration. Nullable session/duration fields accommodate older records and preserved history. Approval can create a dated cover post; generic overflow posts retain their recurring-allocation workflow. SessionResponsibility.sourceType/sourceId identify the originating workflow without a direct foreign key to SessionSwap or OverflowClaim. SessionSwap.reversedById is also a scalar identifier rather than a declared User relation.

Timesheets, Rates, Budgets and System Records

erDiagram
    User ||--o{ Timesheet : submits
    User o|--o{ Timesheet : approves
    Course o|--o{ Timesheet : scopes
    Timesheet ||--o{ TimesheetEntry : contains
    Course ||--o{ TimesheetEntry : records_work
    Allocation o|--o{ TimesheetEntry : relates_to
    CourseSession o|--o{ TimesheetEntry : relates_to
    User ||--o{ TutorRate : has_rates
    User ||--o{ TutorRate : sets_rates
    Course ||--o| CourseBudget : has_budget
    User ||--o{ Notification : receives
    User o|--o{ AuditLog : acts

A course has zero or one budget, not multiple budgets. Tutor rates belong to users and carry effective dates; Timesheet stores the approved appliedRate value rather than a foreign key to a rate row.

A timesheet may be course-scoped or general, but each entry must reference a course. Allocation and session references are optional, allowing other work such as preparation. Versioned submission snapshots preserve entries and session context for review. Monetary values use fixed-precision decimals; entry hours use Decimal(4,2) and totals use Decimal(5,2).

Notification and audit entity references are generic scalar identifiers, not foreign keys to every possible target. Email status distinguishes provider acceptance (SENT), failure and skipped attempts; it does not confirm inbox delivery.

Database Table Reference

The following 28 tables and 282 columns use the inspected PostgreSQL names, types and nullability. Expand a table to inspect its columns. PK and FK describe live database constraints; composite uniqueness applies to the complete listed combination. SQL-only rules are called out separately. UUID generation is performed by Prisma for its String identifiers; PostgreSQL stores them as text.

users ? User

Auth0 identity, profile, application role, onboarding and account state.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
auth0Id	text	Unique	Auth0 subject identifier.
email	text	Unique	Account or invitation email address.
name	text ? nullable	?	Display name.
role	"Role" (enum)	?	Application role.
studentNumber	text ? nullable	?	Student identifier.
maxHoursPerWeek	integer	?	Configured weekly work limit.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
avatarUrl	text ? nullable	?	Public avatar URL.
avatarStorageKey	text ? nullable	?	Avatar object key for replacement/cleanup.
yearOfStudy	integer ? nullable	?	Student year of study.
emailVerified	boolean	?	Verified-email state from Auth0.
staffOnboardingRole	"Role" (enum) ? nullable	?	Role whose staff welcome has been completed.
accountStatus	"AccountStatus" (enum)	?	Active, closing or closed account state.
closedAt	timestamp without time zone ? nullable	?	Account closure timestamp.
tokenValidAfter	timestamp without time zone ? nullable	?	Cutoff used to reject older access tokens.

courses ? Course

Course catalogue, staffing requirements and application settings.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
code	text	Unique	Unique course code.
name	text	?	Course name.
description	text ? nullable	?	Descriptive text.
year	integer	?	Academic/calendar year.
semester	integer	?	Course semester.
requiredTutors	integer	?	Required tutor count.
minMarkRequired	integer	?	Minimum qualifying course mark.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
applicationsOpen	boolean	?	Whether applications are open.
coordinatorId	text ? nullable	FK ? users.id (SET NULL)	Legacy coordinator link retained only in PostgreSQL.

Prisma difference: legacy coordinatorId and its User foreign key remain in PostgreSQL, outside the current Prisma model. Current application coordination uses course_coordinators.

course_applications ? CourseApplication

Student/Tutor applications and staff review decisions.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
courseId	text	FK ? courses.id (RESTRICT); Unique with userId	Reference to courses.
userId	text	FK ? users.id (CASCADE); Unique with courseId	Reference to users.
hoursPerWeek	integer	?	Weekly hours for the assignment or proposal.
motivation	text	?	Applicant motivation.
status	"ApplicationStatus" (enum)	?	Workflow state.
reviewReason	text ? nullable	?	Staff review explanation.
reviewedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.
reviewedAt	timestamp without time zone ? nullable	?	Review timestamp.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.

SQL-only check: course_applications_hours_check ? CHECK ((("hoursPerWeek" >= 1) AND ("hoursPerWeek" <= 40))).

tutor_marks ? TutorMark

A user's course mark, verification status and review details.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
mark	integer	?	Recorded course mark.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
userId	text	FK ? users.id (CASCADE); Unique with courseId	Reference to users.
courseId	text	FK ? courses.id (CASCADE); Unique with userId	Reference to courses.
rejectionReason	text ? nullable	?	Explanation for rejection.
reviewedAt	timestamp without time zone ? nullable	?	Review timestamp.
reviewedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.
status	"TutorMarkStatus" (enum)	?	Workflow state.

availability ? Availability

A user's recurring free-time windows.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
dayOfWeek	"DayOfWeek" (enum)	?	Recurring weekday.
startTime	text	?	Scheduled start time (HH:MM).
endTime	text	?	Scheduled end time (HH:MM).
isRecurring	boolean	?	Whether availability repeats weekly.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
userId	text	FK ? users.id (CASCADE)	Reference to users.

availability_exceptions ? AvailabilityException

Dated changes to recurring availability.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
date	date	?	Calendar date.
isAvailable	boolean	?	Whether the dated exception permits availability.
startTime	text ? nullable	?	Scheduled start time (HH:MM).
endTime	text ? nullable	?	Scheduled end time (HH:MM).
reason	text ? nullable	?	User/staff explanation.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
userId	text	FK ? users.id (CASCADE)	Reference to users.

Lookup indexes: availability_exceptions_userId_date_idx on (userId, date).

academic_terms ? AcademicTerm

Teaching blocks that bound session occurrence generation.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
year	integer	Unique with name	Academic/calendar year.
name	text	Unique with year	Teaching-block name.
startDate	date	?	First teaching-block date.
endDate	date	?	Last teaching-block date.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.

Lookup indexes: academic_terms_startDate_endDate_idx on (startDate, endDate).

holidays ? Holiday

Institution-wide non-teaching dates, entered manually or retrieved from Wits.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
date	date	Unique with name	Calendar date.
name	text	Unique with date	Holiday/non-teaching date name.
year	integer	?	Academic/calendar year.
source	text	?	Source of the holiday record.
createdAt	timestamp without time zone	?	Creation timestamp.

Lookup indexes: holidays_date_idx on (date).

allocations ? Allocation

Recurring tutor-to-course assignments, weekly hours, status and locks.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
hoursPerWeek	double precision	?	Weekly hours for the assignment or proposal.
status	"AllocationStatus" (enum)	?	Workflow state.
reason	text ? nullable	?	User/staff explanation.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
userId	text	FK ? users.id (CASCADE); Unique with courseId	Reference to users.
courseId	text	FK ? courses.id (CASCADE); Unique with userId	Reference to courses.
assignedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.
isLocked	boolean	?	Whether ordinary allocation edits are locked.
sessionIds	text[]	?	Selected session IDs; checked by the backend, without element foreign keys.

course_sessions ? CourseSession

Recurring teaching sessions, weekdays, times, type and venue.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
dayOfWeek	"DayOfWeek" (enum)	?	Recurring weekday.
startTime	text	?	Scheduled start time (HH:MM).
endTime	text	?	Scheduled end time (HH:MM).
venue	text ? nullable	?	Teaching venue.
sessionType	"SessionType" (enum)	?	Lecture, tutorial or lab type.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
courseId	text	FK ? courses.id (CASCADE)	Reference to courses.

allocation_drafts ? AllocationDraft

Saved allocation plans and their creator.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
name	text	?	Display name.
description	text ? nullable	?	Descriptive text.
isPublished	boolean	?	Draft publication flag.
publishedAt	timestamp without time zone ? nullable	?	Draft publication timestamp.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
createdById	text	FK ? users.id (CASCADE)	Reference to users.

audit_logs ? AuditLog

Actor and before/after snapshots of significant entity changes.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
action	"AuditAction" (enum)	?	Recorded audit action.
entityType	text	?	Audited entity type.
entityId	text	?	Audited entity identifier; no generic foreign key.
before	jsonb ? nullable	?	Previous values as JSON.
after	jsonb ? nullable	?	Resulting values as JSON.
createdAt	timestamp without time zone	?	Creation timestamp.
userId	text ? nullable	FK ? users.id (SET NULL)	Reference to users.

Lookup indexes: audit_logs_entityType_entityId_idx on (entityType, entityId).

course_budgets ? CourseBudget

A course's budget and recorded spending.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
amount	numeric(10,2)	?	Course budget amount.
spent	numeric(10,2)	?	Recorded course spending.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
courseId	text	FK ? courses.id (CASCADE); Unique	Reference to courses.

draft_allocation_entries ? DraftAllocationEntry

Proposed tutor/course assignments and explanations within a draft.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
hoursPerWeek	double precision	?	Weekly hours for the assignment or proposal.
reason	text ? nullable	?	User/staff explanation.
createdAt	timestamp without time zone	?	Creation timestamp.
draftId	text	FK ? allocation_drafts.id (CASCADE); Unique with userId, courseId	Reference to allocation_drafts.
userId	text	Unique with draftId, courseId	Identifier recorded by the workflow.
courseId	text	Unique with draftId, userId	Identifier recorded by the workflow.
explanation	jsonb ? nullable	?	Allocation-generation explanation as JSON.

Prisma difference: hoursPerWeek is declared Int; live storage is double precision. Tutor/course relations are declared in Prisma but have no live foreign keys.

excusals ? Excusal

Requests to miss a session occurrence, with duration and review history.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
reason	text	?	User/staff explanation.
status	"ExcusalStatus" (enum)	?	Workflow state.
createdAt	timestamp without time zone	?	Creation timestamp.
resolvedAt	timestamp without time zone ? nullable	?	Workflow resolution timestamp.
userId	text	FK ? users.id (CASCADE)	Reference to users.
allocationId	text	FK ? allocations.id (CASCADE); Conditional unique with sessionId, sessionDate	Reference to allocations.
sessionId	text ? nullable	FK ? course_sessions.id (SET NULL); Conditional unique with allocationId, sessionDate	Reference to course_sessions.
resolvedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.
sessionDate	date	Conditional unique with allocationId, sessionId	Dated session occurrence.
durationMinutes	integer ? nullable	?	Snapshot of missed session duration.
reviewReason	text ? nullable	?	Staff review explanation.

SQL-only partial index: excusals_allocationId_sessionId_sessionDate_key applies where (status <> 'DECLINED'::"ExcusalStatus").

notifications ? Notification

In-app messages, read state, email outcomes and reminder deduplication.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
type	"NotificationType" (enum)	?	Notification category.
message	text	?	In-app notification text.
read	boolean	?	Whether the notification was read.
relatedEntityType	text ? nullable	?	Related entity category.
relatedEntityId	text ? nullable	?	Related entity identifier; no generic foreign key.
createdAt	timestamp without time zone	?	Creation timestamp.
userId	text	FK ? users.id (CASCADE)	Reference to users.
title	text	?	Notification title.
emailStatus	"EmailStatus" (enum) ? nullable	?	Provider acceptance, failure or skipped attempt.
emailError	text ? nullable	?	Recorded email failure information.
emailAttemptedAt	timestamp without time zone ? nullable	?	Email attempt timestamp.
dedupeKey	text ? nullable	Unique	Unique reminder claim key.

Lookup indexes: notifications_userId_read_idx on (userId, read).

calendar_feed_tokens ? CalendarFeedToken

Hashed credentials for private, revocable calendar subscriptions.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
tokenHash	text	Unique	SHA-256 subscription credential hash.
userId	text	FK ? users.id (CASCADE)	Reference to users.
createdAt	timestamp without time zone	?	Creation timestamp.
lastFetchedAt	timestamp without time zone ? nullable	?	Most recent subscription fetch timestamp.

Lookup indexes: calendar_feed_tokens_userId_idx on (userId).

overflow_claims ? OverflowClaim

Volunteer claims and their review outcomes.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
status	"OverflowClaimStatus" (enum)	?	Workflow state.
claimedAt	timestamp without time zone	?	Volunteer claim timestamp.
approvedAt	timestamp without time zone ? nullable	?	Approval timestamp.
overflowPostId	text	FK ? overflow_posts.id (CASCADE); Unique with userId	Reference to overflow_posts.
userId	text	FK ? users.id (CASCADE); Unique with overflowPostId	Reference to users.
approvedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.

overflow_posts ? OverflowPost

General volunteering work or cover for a specific missed occurrence.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
hoursPerWeek	double precision	?	Required work hours; Prisma field is hoursNeeded.
description	text ? nullable	?	Descriptive text.
status	"OverflowStatus" (enum)	?	Workflow state.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
courseId	text	FK ? courses.id (CASCADE)	Reference to courses.
postedById	text	FK ? users.id (CASCADE)	Reference to users.
excusalId	text ? nullable	FK ? excusals.id (SET NULL); Unique	Reference to excusals.
sessionId	text ? nullable	FK ? course_sessions.id (SET NULL)	Reference to course_sessions.
sessionDate	date ? nullable	?	Dated session occurrence.
durationMinutes	integer ? nullable	?	Snapshot of missed session duration.
responsibilityId	text ? nullable	FK ? session_responsibilities.id (RESTRICT)	Reference to session_responsibilities.

session_swaps ? SessionSwap

Dated exchanges, participant consent, staff decisions and reversal details.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
status	"SwapStatus" (enum)	?	Workflow state.
reason	text ? nullable	?	User/staff explanation.
createdAt	timestamp without time zone	?	Creation timestamp.
resolvedAt	timestamp without time zone ? nullable	?	Workflow resolution timestamp.
requesterId	text	FK ? users.id (CASCADE)	Reference to users.
requesteeId	text ? nullable	FK ? users.id (CASCADE)	Reference to users.
originAllocationId	text	FK ? allocations.id (CASCADE)	Reference to allocations.
targetAllocationId	text	FK ? allocations.id (CASCADE)	Reference to allocations.
resolvedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.
validationWarnings	jsonb ? nullable	?	Identifier recorded by the workflow.
rejectionReason	text ? nullable	?	Explanation for rejection.
reviewedAt	timestamp without time zone ? nullable	?	Review timestamp.
requesteeAcceptedAt	timestamp without time zone ? nullable	?	Requested tutor consent timestamp.
requesterSessionId	text ? nullable	FK ? course_sessions.id (RESTRICT)	Reference to course_sessions.
requesterSessionDate	date ? nullable	?	Origin session occurrence date.
targetSessionId	text ? nullable	FK ? course_sessions.id (RESTRICT)	Reference to course_sessions.
targetSessionDate	date ? nullable	?	Target session occurrence date.
requesterOccurrence	jsonb ? nullable	?	Origin occurrence snapshot.
targetOccurrence	jsonb ? nullable	?	Target occurrence snapshot.
reversedAt	timestamp without time zone ? nullable	?	Swap reversal timestamp.
reversedById	text ? nullable	?	Reversing actor identifier; no declared/live foreign key.
reversalReason	text ? nullable	?	Staff reversal explanation.

session_responsibilities ? SessionResponsibility

Original/current responsibility for an allocation's dated session slot.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
allocationId	text	FK ? allocations.id (RESTRICT); Unique with sessionId, sessionDate	Reference to allocations.
sessionId	text	FK ? course_sessions.id (RESTRICT); Unique with allocationId, sessionDate	Reference to course_sessions.
sessionDate	date	Unique with allocationId, sessionId	Dated session occurrence.
courseId	text	FK ? courses.id (RESTRICT)	Reference to courses.
originalUserId	text	FK ? users.id (RESTRICT)	Reference to users.
responsibleUserId	text ? nullable	FK ? users.id (RESTRICT)	Reference to users.
startTime	text	?	Scheduled start time (HH:MM).
endTime	text	?	Scheduled end time (HH:MM).
active	boolean	?	Whether the dated responsibility override is active.
sourceType	text	?	Originating workflow category.
sourceId	text	?	Originating workflow identifier; no generic foreign key.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.

Lookup indexes: session_responsibilities_originalUserId_sessionDate_idx on (originalUserId, sessionDate); session_responsibilities_responsibleUserId_sessionDate_idx on (responsibleUserId, sessionDate).

SQL-only check: session_responsibilities_interval ? CHECK (("startTime" < "endTime")).

timesheet_entries ? TimesheetEntry

Dated hours for a course, optionally linked to an allocation/session.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
date	date	?	Calendar date.
hours	numeric(4,2)	?	Hours worked in this entry.
description	text ? nullable	?	Descriptive text.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
timesheetId	text	FK ? timesheets.id (CASCADE)	Reference to timesheets.
allocationId	text ? nullable	FK ? allocations.id (SET NULL)	Reference to allocations.
sessionId	text ? nullable	FK ? course_sessions.id (SET NULL)	Reference to course_sessions.
courseId	text	FK ? courses.id (CASCADE)	Reference to courses.

timesheets ? Timesheet

Weekly work submission, review state, submission snapshots and applied rate.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
weekStarting	date	Unique with userId, courseId; Conditional unique with userId	Monday starting the timesheet week.
status	"TimesheetStatus" (enum)	?	Workflow state.
totalHours	numeric(5,2)	?	Derived total of entry hours.
submittedAt	timestamp without time zone ? nullable	?	Submission timestamp.
approvedAt	timestamp without time zone ? nullable	?	Approval timestamp.
disputeNote	text ? nullable	?	Staff dispute explanation.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.
userId	text	FK ? users.id (CASCADE); Unique with courseId, weekStarting; Conditional unique with weekStarting	Reference to users.
courseId	text ? nullable	FK ? courses.id (CASCADE); Unique with userId, weekStarting	Reference to courses.
approvedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.
submittedSnapshot	jsonb ? nullable	?	Versioned submitted work and session-context snapshots.
appliedRate	numeric(8,2) ? nullable	?	Effective hourly rate captured at approval; no rate foreign key.

SQL-only partial index: timesheets_userId_weekStarting_general_key applies where ("courseId" IS NULL).

account_closures ? AccountClosure

Durable external cleanup, retry scheduling and completion for a closed account.

Column name	PostgreSQL data type	Keys and uniqueness	Description
userId	text	PK; FK ? users.id (RESTRICT)	Reference to users.
requestedAt	timestamp without time zone	?	Account cleanup request timestamp.
completedAt	timestamp without time zone ? nullable	?	External cleanup completion timestamp.
identityRemovedAt	timestamp without time zone ? nullable	?	Auth0 identity removal timestamp.
avatarStorageKey	text ? nullable	?	Avatar object key for replacement/cleanup.
attempts	integer	?	Number of cleanup attempts.
nextAttemptAt	timestamp without time zone	?	Next scheduled cleanup attempt.
leaseUntil	timestamp without time zone ? nullable	?	Cleanup worker lease expiry.
leaseToken	text ? nullable	?	Identifier of the worker holding the lease.
lastErrorCode	text ? nullable	?	Safe cleanup failure code.
auth0Id	text ? nullable	?	Original Auth0 subject retained for cleanup.

Lookup indexes: account_closures_completedAt_nextAttemptAt_idx on (completedAt, nextAttemptAt).

staff_access_requests ? StaffAccessRequest

A user's staff-access request, status and administrative review.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
userId	text	FK ? users.id (CASCADE); Unique	Reference to users.
requestedRole	"Role" (enum)	?	Requested staff role.
reason	text	?	User/staff explanation.
status	text	?	Workflow state.
reviewReason	text ? nullable	?	Staff review explanation.
reviewedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.
reviewedAt	timestamp without time zone ? nullable	?	Review timestamp.
createdAt	timestamp without time zone	?	Creation timestamp.
updatedAt	timestamp without time zone	?	Last update timestamp.

Lookup indexes: staff_access_requests_status_idx on (status).

SQL-only check: staff_access_requests_role_check ? CHECK (("requestedRole" = ANY (ARRAY['ADMIN'::"Role", 'LECTURER'::"Role"]))).

SQL-only check: staff_access_requests_status_check ? CHECK ((status = ANY (ARRAY['PENDING'::text, 'APPROVED'::text, 'REJECTED'::text, 'CANCELLED'::text]))).

staff_invitations ? StaffInvitation

Email-based Admin/Lecturer invitations, proposed course assignments and expiry.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
email	text	Unique	Account or invitation email address.
role	"Role" (enum)	?	Application role.
courseIds	text[]	?	Proposed course IDs; array elements have no course foreign keys.
status	text	?	Workflow state.
expiresAt	timestamp without time zone	?	Invitation expiry timestamp.
acceptedAt	timestamp without time zone ? nullable	?	Invitation redemption timestamp.
createdAt	timestamp without time zone	?	Creation timestamp.
createdById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.

Lookup indexes: staff_invitations_status_expiresAt_idx on (status, expiresAt).

SQL-only check: staff_invitations_role_check ? CHECK ((role = ANY (ARRAY['ADMIN'::"Role", 'LECTURER'::"Role"]))).

SQL-only check: staff_invitations_status_check ? CHECK ((status = ANY (ARRAY['PENDING'::text, 'ACCEPTED'::text, 'REVOKED'::text]))).

tutor_rates ? TutorRate

Effective-dated hourly rate history and corrections.

Column name	PostgreSQL data type	Keys and uniqueness	Description
id	text	PK	Record identifier.
userId	text	FK ? users.id (CASCADE); Unique with effectiveFrom	Reference to users.
rate	numeric(8,2)	?	Hourly pay rate.
effectiveFrom	date	Unique with userId	First effective date for the rate.
isCorrection	boolean	?	Whether this rate was corrected.
createdById	text	FK ? users.id (RESTRICT)	Reference to users.
createdAt	timestamp without time zone	?	Creation timestamp.

Lookup indexes: tutor_rates_userId_effectiveFrom_idx on (userId, effectiveFrom).

course_coordinators ? CourseCoordinator

Lecturer-to-course assignments and the assigning administrator.

Column name	PostgreSQL data type	Keys and uniqueness	Description
courseId	text	FK ? courses.id (CASCADE); PK (composite)	Reference to courses.
userId	text	PK (composite); FK ? users.id (CASCADE)	Reference to users.
assignedAt	timestamp without time zone	?	Coordinator assignment timestamp.
assignedById	text ? nullable	FK ? users.id (SET NULL)	Reference to users.

Keys, Constraints and Indexes

• Primary keys: 26 models use UUID-generated String IDs stored as PostgreSQL text. CourseCoordinator instead has composite primary key (courseId, userId); AccountClosure uses its user's ID as both primary key and foreign key.
• Unique identity and ownership: User email/Auth0 subject, course code, invitation email, request user, budget course and feed-token hash are unique. The feed-token user index is not a uniqueness constraint; rotation is managed by the service.
• Composite uniqueness: user/course pairs for marks, applications and allocations; draft/user/course entries; post/claimant pairs; tutor/effective-date rates; academic year/name; holiday date/name; and allocation/session/date responsibility slots.
• SQL-only rules: a partial unique excusal index applies when status is not DECLINED, permitting resubmission after decline. A partial timesheet index protects user/week uniqueness when course is null, complementing the course-scoped composite key. These indexes are maintained in migration SQL, not represented as Prisma @@unique declarations. SQL also restricts staff request/invitation roles and statuses and checks responsibility start time precedes end time.
• Foreign keys and retention: required and optional links preserve ownership and review context. Many dependent rows cascade on physical deletion; selected reviewer/session links use SET NULL. SessionResponsibility's allocation, course, session and tutor links, and AccountClosure's user link, use RESTRICT. Application account closure retains User rows rather than relying on cascading deletion.
• Lookup indexes: notification user/read state; audit entity/type; availability user/date; responsibility tutor/date; academic date windows; holiday date; staff request status; invitation status/expiry; closure completion/retry date; and tutor/effective-date rates.
• Reminder uniqueness: nullable Notification.dedupeKey has a unique index. Atomic duplicate-skipping insertion determines which job may send the corresponding email.

These constraints do not independently enforce marks, timetable suitability, course membership, weekly limits or reviewer permissions. Those rules are checked by the Express backend.

Validation and Transaction Consistency

The shared Serializable transaction helper retries serialization conflicts up to three attempts and returns a conflict when concurrent decisions cannot be reconciled. Allocation writes, application and volunteer approvals, dated swaps and staff-access grants recheck eligibility, permissions and workflow state inside the same transaction as their changes. Draft commitment revalidates selected entries rather than trusting a saved proposal.

Timesheet mutations lock the parent row; creation uses a transaction-scoped advisory lock alongside database uniqueness. Submission snapshots, entry totals, review state, applied pay rate and budget spending are updated within the relevant transaction. Changes to marks, availability or course scheduling also check affected active allocations before committing.

Email and Auth0 cleanup run outside these database transactions. A provider failure cannot roll back an already committed business decision. Account closure persists cleanup work first and uses database leases for retries. See the API Reference for operation contracts.

Schema Management and Verified Database State

Prisma's PostgreSQL datasource uses DATABASE_URL and DIRECT_URL. The schema defines application mappings, while version-controlled migrations also contain PostgreSQL-specific indexes, checks and data transformations.

Normal development uses npm run db:migrate (prisma migrate dev). Releases use npm run db:deploy (prisma migrate deploy), followed by npm run db:generate to regenerate the client. These commands change schema or generated code and were not run during the read-only verification. prisma db push is not a replacement for applying the reviewed migration history.

The repository contains 25 migrations, and the connected Supabase database records all 25 as completed. The latest migration by filename is 20261012120000_c04_term_exceptions. Completion records do not prove successful replay on a fresh database, unchanged migration checksums or perfect parity with Prisma.

The metadata comparison found no missing Prisma scalar columns, but confirmed three differences: DraftAllocationEntry.hoursPerWeek is Prisma Int while PostgreSQL stores double precision; the declared draft-entry tutor/course foreign keys are absent; and courses retains a legacy coordinatorId column and User foreign key outside the current Prisma model. These differences remain unresolved. SQL-only partial indexes and checks are additional intentional database rules, not evidence that every Prisma relationship is enforced.

For the wider application structure, see System Overview. Connection and hosting configuration are covered in Deployment and Hosting.