Domain Model

The entities, their keys, and the relationships the rest of the system builds on. Status: current. Source of truth: apps/backend/src/infrastructure/database/contract.prisma.

The model replaces the legacy Django schema: one row per real-world thing, enums instead of polymorphic flags, and explicit join tables (Prisma 8 has no implicit many-to-many). Most of these tables have no API yet — see Planned endpoints.

Relationships

erDiagram
  User ||--o{ Session : has
  User ||--o{ Enrollment : holds
  ClassRecord ||--o{ Enrollment : contains

  Faculty ||--o{ FacultyCourse : offers
  Course ||--o{ FacultyCourse : offered_in
  Course ||--o{ CourseUnit : offers
  CourseUnit ||--o{ Occurrence : occurs_as
  Course ||--o{ Occurrence : runs
  Occurrence ||--o{ ClassRecord : has
  Occurrence ||--o{ ExchangePeriod : opens
  ClassRecord ||--o{ SlotClass : scheduled
  ScheduleSlot ||--o{ SlotClass : assigned
  ScheduleSlot ||--o{ SlotProfessor : taught_by
  Professor ||--o{ SlotProfessor : teaches

  User ||--o{ ExchangeRequest : creates
  ExchangeRequest ||--o{ ExchangeItem : contains
  User ||--o{ ExchangeItem : moves
  Occurrence ||--o{ ExchangeItem : about

  User ||--o{ EnrollmentRequest : submits
  EnrollmentRequest ||--o{ EnrollmentRequestOption : has
  Occurrence ||--o{ EnrollmentRequestOption : option

  User ||--o{ PlannerState : saves
  User ||--o{ AdminCourse : scoped
  Course ||--o{ AdminCourse : scope
  User ||--o{ AdminOccurrence : scoped
  Occurrence ||--o{ AdminOccurrence : scope
  User ||--o{ StudentCourseMetadata : has
  Course ||--o{ StudentCourseMetadata : metadata

Loading

see interactive uml here:

Identity and student state

Model	Primary key	Purpose
User	id (NMEC, bare digits)	Student/admin account. email nullable + unique, role is USER/ADMIN/SUPERUSER
Session	tokenHash	Opaque session; stores sha256(cookie token), userId, expiresAt
Enrollment	(userId, classId)	Mirror of the student's current class assignments, per class
PlannerState	(userId, year)	Cross-device planner payload — see the caveat below
StudentCourseMetadata	(nmec, courseId)	Cached festId scraped from SIGARRA; safe to drop

A student legitimately has several Enrollment rows for one course unit — one per lesson type (T, TP, P). “Enrolled in BD” is not one row; it is the set of classes they attend.

Session and User are the only identity tables with endpoints today; everything else is 🚧.

Catalog

Model	Primary key	Purpose
Faculty	acronym (FEUP)	Faculty; name nullable
Course	id (SIGARRA course id)	A degree in one academic year; year, courseType, url
FacultyCourse	(facultyId, courseId)	Explicit many-to-many: courses can be shared by faculties
CourseUnit	id (serial)	Abstract, year-agnostic subject (BD); belongs to a Course
Occurrence	(id, year)	The unit actually taught in a course/year; owns classes and periods
Class	id (serial)	A class/turma (1LEIC01); belongs to one occurrence
ScheduleSlot	id (SIGARRA lesson id)	One lesson: type, day, start minute, duration, room
SlotClass	(slotId, classId)	Explicit many-to-many: one lecture can serve many classes
Professor	id (SIGARRA code)	Teacher; acronym and name nullable
SlotProfessor	(slotId, professorId)	Explicit many-to-many: teachers assigned to a lesson

The hierarchy that matters

Course (L.EIC, 2025)
  └── CourseUnit (BD)                  abstract subject, one per course
        └── Occurrence (BD, L.EIC, 2025)   the teaching instance; PK (id, year)
              ├── Class (1LEIC01, …)       each class has its own slots
              │     └── SlotClass ──▶ ScheduleSlot (T/TP/P, day, time, room)
              └── ExchangePeriod           when exchanges are open

• Course is per academic year. The same degree next year is a different Course row with a different SIGARRA id.
• CourseUnit is year-agnostic but course-scoped (courseId is required). The same subject in another course is another row; the header comment about sharing one row across courses is not what the schema enforces yet.
• Occurrence is the hinge. Classes, exchange periods, enrollment options and exchange items all point at it, never at CourseUnit.
• Occurrence.id alone is not unique — SIGARRA reuses the id across years — hence the composite (id, year) PK. The natural key (courseUnitId, year, courseId) rejects duplicate inserts.
• A lesson can serve many classes. That is why the class link is SlotClass, not a classId on the slot: folding it in would make a shared T lecture unrepresentable.

Exchanges

Model	Primary key	Purpose
ExchangeRequest	id (uuid)	One request; type, status, adminState, creator, optional target
ExchangeItem	(requestId, userId, occurrenceId, occurrenceYear)	One participant's move: fromClass → toClass
ExchangePeriod	(occurrenceId, occurrenceYear, startsAt, endsAt)	Exchange window, per occurrence

• type is DIRECT / MARKETPLACE / URGENT — the legacy getExchangeType() sniffing is gone.
• status is the student-side lifecycle: PENDING → ACCEPTED | CANCELLED.
• adminState is separate: UNTREATED / TREATED / REJECTED / AWAITING_INFORMATION. Two orthogonal state machines, not a pile of booleans that can disagree.
• One ExchangeItem per participant. A direct swap needs two rows (one per student), each with its own from/to and its own accepted flag. Acceptance is derived: an exchange is accepted when every item is accepted.
• Composite foreign keys (fromClassId, occurrenceId, occurrenceYear) and (toClassId, occurrenceId, occurrenceYear) guarantee both classes belong to the same occurrence as the item.
• ExchangePeriod is occurrence-scoped on purpose. Opening a whole course is a fan-out across its occurrences inside one transaction; the legacy is_course_expiration flag is gone.
• hash backs email verification links; lastValidated backs revalidation.

Enrollment requests

Model	Primary key	Purpose
EnrollmentRequest	id (uuid)	A student's request to enrol in / drop units
EnrollmentRequestOption	(requestId, occurrenceId, occurrenceYear)	One unit choice; enrolling: false means drop

EnrollmentRequest reuses ExchangeStatus and AdminValidationState — the lifecycle is the same three states. The request is not the enrolment: approving it creates/removes Enrollment rows. The option carries no target class; the legacy body never sent one either.

Admin scope

Model	Primary key	Purpose
AdminCourse	(userId, courseId)	Courses an admin may manage
AdminOccurrence	(userId, occurrenceId, occurrenceYear)	Occurrences an admin may manage

User.role is the global tier; these two tables are the scope. A route that mutates an exchange must check both — role alone is not enough. No implemented route does this yet.

Enums

Enum	Values
UserRole	USER, ADMIN, SUPERUSER (ascending hierarchy)
ExchangeType	DIRECT, MARKETPLACE, URGENT
ExchangeStatus	PENDING, ACCEPTED, CANCELLED
AdminValidationState	UNTREATED, TREATED, REJECTED, AWAITING_INFORMATION

Localization, class names and lesson types stay strings because they are SIGARRA data, not TTS decisions. Lesson types are T, TP, P, PL, OT, O, E; dayOfWeek is 0 = Sunday and times are minutes since midnight, not decimal hours — the legacy float comparisons are gone.

Schema caveats

Two places where the code and the design document disagree. Resolve these before implementing the matching feature:

1. PlannerState.state is a string, not JSON. The contract comments describe a hash of the planner tree (the backend is dumb storage, the frontend canonicalises). endpoints.md still describes { state: <object>, schemaVersion } stored as JSON. The contract is authoritative; the endpoint design needs reconciling.
2. The contract header mentions a catalog “sync job”. No sync job exists — the catalog is loaded from the committed fixture by db:seed (see Architecture overview).

See also

• Architecture overview — where the database sits
• Glossary — course vs course unit vs occurrence vs class
• Planned endpoints — which entities will get routes
• Database guide — contract, migrations, recovery
• Data model reference 🚧 · Legacy schema mapping 🚧

---

Last verified: 2026-10-27