16 tables, 3 views and 13 functions — every academic rule lives here, not in the interface
How it is built up
Catalogue
What the institution offers — stable reference data
→
Rules
What must be true before a course can be taken
→
Calendar
When things happen
→
People
Who is involved
→
Offering
A course actually running, in a room, at a time
→
Activity
What a student has done and is doing
grade_record
Activity
The transcript — one row per course attempt per term.
Columns
id
integer
pkgen
student_id
integer
fkreq
course_id
integer
fkreq
not curriculum_course
term_id
smallint
fkreq
grade
grade_point
NULL until released
status
grade_status
req
is_passed
boolean
gen
GENERATED … STORED
Connections
Points at
grade_record.student_id→many-to-one
A student accumulates many grade records.
grade_record.course_id→many-to-one
A grade is a fact about a student and a COURSE.
Pointing at curriculum_course instead would tie the transcript to one curriculum — so a student who shifts programme would lose their history — and would force every prerequisite check to bridge two different ID spaces.
grade_record.term_id→many-to-one
Records when the attempt happened, which is what makes retakes distinguishable.
Why it is shaped this way
grade is NULLABLE and paired with a status, so "not released yet", "INC", "withdrawn" and "failed" are four different things. A plain numeric column collapses them — which is how an unreleased grade ends up reading as a failure and the course is wrongly offered as a retake.
is_passed is GENERATED ALWAYS AS (…) STORED. STORED is written explicitly because PostgreSQL 18 changed the default to VIRTUAL, which is not indexable — and there is an index on this column.
UNIQUE (student_id, course_id, term_id) — one grade per course per term.
How a request travels through it
The chain available_sections() walks to decide what a student may enlist in.
1
Who is asking?
auth.uid() resolves to a student row. Identity is never a parameter the client supplies.
2
What does their curriculum contain?
Every course mapped to their curriculum, with the year and term it is recommended in.
3
What have they already done?
Passed courses are removed. Failed ones become retakes. Pending grades count as neither.
4
What are they allowed to take?
Every prerequisite group must be satisfied, and any standing gate must be met.
5
Is it actually running, with a seat?
Only courses with an open section in the target term survive, and only while seats remain.
6
Add it — if every guard passes
Editable state, open window, eligibility recomputed, seat available, unit ceiling, no clash.
Derived views
Computed on read, never stored — a cached counter is a number that can drift.
section_load
Seats taken and remaining, per section.
Derived, never stored. A cached enrolled_count has no integrity guarantee behind it: every path that adds or removes an enlistment must remember to update it, and the day one path forgets, the number is wrong with nothing able to detect it.
enlistment_load
Unit total against the curriculum's minimum and maximum.
Powers the units counter and the under/over-load checks at submit time.
student_profile
A student with their programme resolved through their curriculum.
Exists so student does not have to store program_id and risk contradicting curriculum.