Data Models

Every table, column, key and index in contract.prisma. Status: current. The relationships and design rationale are in Domain model; the migration workflow is in Contract & migrations.

Legend: PK primary key · FK foreign key · ? nullable · relation object fields are omitted (only the scalar FK columns are listed). Model names are Prisma model names; the table names match.

Identity

User

Column	Type	Notes
id	String	PK — bare NMEC (202301234)
email	String?	unique; nullable because a shadow user may never log in
name	String
role	UserRole	default USER
createdAt	DateTime	default now()

Keys: PK id; unique email.

Session

Column	Type	Notes
tokenHash	String	PK — sha256(cookie token)
userId	String	FK → User.id, cascade delete
createdAt	DateTime	default now()
expiresAt	DateTime	absolute expiry

Keys: PK tokenHash; indexes (userId), (expiresAt).

Catalog

Faculty

Column	Type	Notes
acronym	String	PK (FEUP, seeded as feup)
name	String?

Course

Column	Type	Notes
id	Int	PK — SIGARRA course id, already per year
acronym	String
name	String
courseType	String	raw SIGARRA code (L, M, B, …)
year	Int	academic year
url	String	SIGARRA page
createdAt	DateTime	default now()
updatedAt	DateTime	auto-bumped (temporal.updatedAt())

Keys: PK id; index (year).

FacultyCourse

Column	Type	Notes
facultyId	String	PK, FK → Faculty.acronym, cascade
courseId	Int	PK, FK → Course.id, cascade

Keys: PK (facultyId, courseId); index (courseId).

CourseUnit

Column	Type	Notes
id	Int	PK, autoincrement
courseId	Int	FK → Course.id, cascade
acronym	String	e.g. BD
name	String
semester	Int	no DB check — narrowed to 1 | 2 in the repository
createdAt	DateTime	default now()

Keys: PK id; index (courseId).

Occurrence

Column	Type	Notes
id	Int	PK (composite), SIGARRA pv_ocorrencia_id
courseUnitId	Int	FK → CourseUnit.id, cascade
courseId	Int	FK → Course.id, cascade
year	Int	PK (composite) — the id is reused across years
ects	Float	credits for this occurrence
url	String	SIGARRA occurrence page
hash	String	default ""; timetable-change detection

Keys: PK (id, year); unique (courseUnitId, year, courseId); index (courseId, year).

Class

Column	Type	Notes
id	Int	PK, autoincrement
occurrenceId	Int	FK (composite) → Occurrence(id, year)
occurrenceYear	Int	FK (composite)
name	String	e.g. 1LEIC01
vacancies	Int?
createdAt	DateTime	default now()
updatedAt	DateTime	auto-bumped

Keys: PK id; unique (name, occurrenceId, occurrenceYear); unique (id, occurrenceId, occurrenceYear) — the latter exists so ExchangeItem can reference a class and its occurrence in one FK.

Professor

Column	Type	Notes
id	Int	PK, SIGARRA professor code
acronym	String?
name	String?	SIGARRA entries can lack a name

ScheduleSlot

Column	Type	Notes
id	Int	PK, SIGARRA lesson id
lessonType	String	T, TP, P, PL, OT, O, E
dayOfWeek	Int	0 = Sunday
startMinute	Int	minutes since midnight
durationMin	Int	minutes
location	String?	room

SlotClass

Column	Type	Notes
slotId	Int	PK, FK → ScheduleSlot.id, cascade
classId	Int	PK, FK → Class.id, cascade

Keys: PK (slotId, classId); index (classId). This join is why a shared lecture can serve many classes.

SlotProfessor

Column	Type	Notes
slotId	Int	PK, FK → ScheduleSlot.id, cascade
professorId	Int	PK, FK → Professor.id, cascade

Keys: PK (slotId, professorId); index (professorId).

Student state

Enrollment

Column	Type	Notes
userId	String	PK, FK → User.id, cascade
classId	Int	PK, FK → Class.id, cascade
createdAt	DateTime	default now()
updatedAt	DateTime	auto-bumped

Keys: PK (userId, classId); index (classId).

PlannerState

Column	Type	Notes
userId	String	PK, FK → User.id, cascade
year	Int	PK — academic year
state	String	opaque hash, owned by the frontend
updatedAt	DateTime	auto-bumped; the client's merge signal

Keys: PK (userId, year).

state is a string hash in the contract, not JSON. endpoints.md still describes a JSON blob + schemaVersion; reconcile before implementing. See Domain model.

Configuration and admin scope

ExchangePeriod

Column	Type	Notes
occurrenceId	Int	PK, FK (composite) → Occurrence(id, year), cascade
occurrenceYear	Int	PK
startsAt	DateTime	PK
endsAt	DateTime	PK
createdAt	DateTime	default now()

Keys: PK (occurrenceId, occurrenceYear, startsAt, endsAt) — the same occurrence cannot have two identical windows.

AdminCourse

Column	Type	Notes
userId	String	PK, FK → User.id, cascade
courseId	Int	PK, FK → Course.id, cascade

Keys: PK (userId, courseId); index (courseId).

AdminOccurrence

Column	Type	Notes
userId	String	PK, FK → User.id, cascade
occurrenceId	Int	PK, FK (composite) → Occurrence(id, year), cascade
occurrenceYear	Int	PK

Keys: PK (userId, occurrenceId, occurrenceYear).

Exchanges

ExchangeRequest

Column	Type	Notes
id	String	PK, UUID
creatorId	String	FK → User.id
targetUserId	String?	FK → User.id; claimed/assigned target
type	ExchangeType	DIRECT / MARKETPLACE / URGENT
status	ExchangeStatus	default PENDING
message	String?	URGENT justification
adminState	AdminValidationState	default UNTREATED
hash	String?	email verification / duplicate check
lastValidated	DateTime?	revalidation timestamp
createdAt	DateTime	default now()
updatedAt	DateTime	auto-bumped

Keys: PK id; indexes (type, status), (adminState, createdAt), (hash), (creatorId, status), (targetUserId).

ExchangeItem

Column	Type	Notes
requestId	String	PK, FK → ExchangeRequest.id, cascade
userId	String	PK, FK → User.id, restrict
occurrenceId	Int	PK, FK (composite) → Occurrence(id, year)
occurrenceYear	Int	PK
fromClassId	Int	FK (composite) → Class(id, occurrenceId, occurrenceYear)
toClassId	Int	FK (composite) → Class(id, occurrenceId, occurrenceYear)
accepted	Boolean	default false, per participant

Keys: PK (requestId, userId, occurrenceId, occurrenceYear); index (userId, accepted).

ON DELETE RESTRICT on userId is deliberate: silently dropping one side would make the derived “all accepted” true with a participant missing.

EnrollmentRequest

Column	Type	Notes
id	String	PK, UUID
userId	String	FK → User.id
status	ExchangeStatus	default PENDING
adminState	AdminValidationState	default UNTREATED
createdAt	DateTime	default now()
updatedAt	DateTime	auto-bumped

EnrollmentRequestOption

Column	Type	Notes
requestId	String	PK, FK → EnrollmentRequest.id, cascade
occurrenceId	Int	PK, FK (composite) → Occurrence(id, year)
occurrenceYear	Int	PK
enrolling	Boolean	default true; false = drop

Keys: PK (requestId, occurrenceId, occurrenceYear).

Support

StudentCourseMetadata

Column	Type	Notes
nmec	String	PK, FK → User.id, cascade
courseId	Int	PK, FK → Course.id, cascade
festId	Int	cached SIGARRA fest_id

Keys: PK (nmec, courseId).

Enums

Enum	Values	Default
UserRole	USER, ADMIN, SUPERUSER	USER
ExchangeType	DIRECT, MARKETPLACE, URGENT	—
ExchangeStatus	PENDING, ACCEPTED, CANCELLED	PENDING
AdminValidationState	UNTREATED, TREATED, REJECTED, AWAITING_INFORMATION	UNTREATED

Index rationale

Index	Serves
Session(userId), Session(expiresAt)	“my sessions”; expiry sweeps
Occurrence(courseId, year)	occurrences for a course/year
FacultyCourse(courseId)	faculties offering a course
SlotClass(classId)	a class's timetable
SlotProfessor(professorId)	a professor's lessons
Enrollment(classId)	who is in a class
ExchangeRequest(type, status)	marketplace / direct lists
ExchangeRequest(adminState, createdAt)	admin queue
ExchangeRequest(hash)	email verification + duplicate check
ExchangeItem(userId, accepted)	a student's pending moves

Composite keys already provide an index for their leading columns (e.g. Enrollment(userId, …)). Do not add indexes on every FK: Postgres identifiers are capped at 63 bytes, and wide composite indexes are easy to create by accident.

Type notes

• DateTime is backed by temporal.updatedAt() / now() in Prisma 8. The runtime requires temporal-polyfill loaded globally; the client and seeds import it.
• Float is used for ects; it is not a money value, so precision is not a concern.
• Int is a 32-bit integer; all SIGARRA ids used here fit.
• Enum columns are the schema-level guarantee; semester is the exception (plain Int, narrowed in the repository).
• Prisma 8 has no implicit many-to-many: FacultyCourse, SlotClass and SlotProfessor are explicit tables.

See also

• Domain model — relationships, invariants and schema caveats
• Contract & migrations — changing any of this
• Seeding — which rows exist in a fresh database
• Repositories — how these columns become API shapes
• Database guide

---

Last verified: 2026-10-27