Authoritative catalog and schema specifications for all 56 Attendrix PostgreSQL tables.

Database Tables & Schemas

Attendrix is backed by a relational PostgreSQL 17 database hosted on Supabase in AWS ap-south-1 (Mumbai). The database enforces strict referential integrity, statement-level audit logging, and Row-Level Security (RLS) across all tenant boundaries.

DATABASE STORAGE INVARIANT

INV-SEC-01

No tenant may query or mutate records outside their auth.uid() scope, except via audited SECURITY DEFINER functions that explicitly enforce role validation.

Architectural Organization

The Attendrix database comprises 56 application domain tables organized into 6 core functional subsystems:

Core Academic & Timetable (15 tables)
Attendance, Identity & Gamification (11 tables)
Google Calendar Synchronization Pipeline (6 tables)
Campus Pedestrian Routing & Transit (13 tables)
Push Notifications & Student Tasks (8 tables)
Audit & Governance (3 tables)

Core Academic & Timetable

Defines institutional structure, semesters, departments, courses, batch curricula, and scheduled lecture occurrences.

`classes`

Primary Key: class_id

Foreign Key Constraints:

batch_id → batches.batch_id (classes_batch_id_fkey)
semester_id → semesters.semester_id (classes_semester_id_fkey)
venue → campus_buildings.id (fk_classes_venue)

Field / Prop	Type	Default	Description
`class_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`class_ref`required	`text`	—	Column `class_ref` stored as `text`.
`batch_id`required	`text`	—	Foreign key referencing `batches.batch_id`.
`semester_id`required	`uuid`	—	Foreign key referencing `semesters.semester_id`.
`scheduled_start`required	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`scheduled_end`required	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`venue`	`text`	—	Foreign key referencing `campus_buildings.id`.
`is_plus_slot`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`course_id`required	`text`	—	Column `course_id` stored as `text`.
`is_extra_class`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`scheduled_date`required	`text`	—	Column `scheduled_date` stored as `text`.
`is_cancelled`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`cancelled_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`cancellation_reason`	`text`	—	Column `cancellation_reason` stored as `text`.

`courses`

Primary Key: course_code

Foreign Key Constraints:

department_id → departments.department_id (courses_department_id_fkey)

Field / Prop	Type	Default	Description
`course_code`required	`text`	—	Primary Key.
`course_name`required	`text`	—	Column `course_name` stored as `text`.
`course_type_code`required	`USER-DEFINED`	—	PostgreSQL UDT: `course_type_code`.
`is_lab`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`credits`required	`smallint`	—	PostgreSQL UDT: `int2`.
`department_id`required	`text`	—	Foreign key referencing `departments.department_id`.
`is_active`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`syllabus_url`	`text`	—	Column `syllabus_url` stored as `text`.

`batch_courses`

Primary Key: course_slot, batch_id, course_code

Foreign Key Constraints:

batch_id → batches.batch_id (batch_courses_batch_id_fkey)
course_code → courses.course_code (batch_courses_course_code_fkey)
venue → campus_buildings.id (fk_batch_courses_venue)

Field / Prop	Type	Default	Description
`offering_id`required	`uuid`	`gen_random_uuid()`	Column `offering_id` stored as `uuid`.
`batch_id`required	`text`	—	Primary Key. Foreign key referencing `batches.batch_id`.
`course_code`required	`text`	—	Primary Key. Foreign key referencing `courses.course_code`.
`course_slot`required	`text`	—	Primary Key.
`course_id`	`text`	—	Column `course_id` stored as `text`.
`is_mandatory`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`added_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`enrolled_student_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`venue`	`text`	—	Foreign key referencing `campus_buildings.id`.

`batches`

Primary Key: batch_id

Foreign Key Constraints:

department_id → departments.department_id (batches_department_id_fkey)

Field / Prop	Type	Default	Description
`batch_id`required	`text`	—	Primary Key.
`department_id`required	`text`	—	Foreign key referencing `departments.department_id`.
`batch_number`required	`smallint`	—	PostgreSQL UDT: `int2`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`department_name`required	`text`	—	Column `department_name` stored as `text`.
`is_active`required	`boolean`	`true`	PostgreSQL UDT: `bool`.

`semesters`

Primary Key: semester_id

Field / Prop	Type	Default	Description
`semester_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`semester_name`required	`text`	—	Column `semester_name` stored as `text`.
`start_date`required	`date`	—	Column `start_date` stored as `date`.
`end_date`required	`date`	—	Column `end_date` stored as `date`.
`is_active`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`locked_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`semester_number`	`integer`	—	PostgreSQL UDT: `int4`.
`semester_slot_system`	`smallint`	—	PostgreSQL UDT: `int2`.

`departments`

Primary Key: department_id

Field / Prop	Type	Default	Description
`department_id`required	`text`	—	Primary Key.
`department_name`required	`text`	—	Column `department_name` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`slot_occurrences`

Primary Key: slot_system, slot_name, day_of_week, period_number

Field / Prop	Type	Default	Description
`slot_name`required	`text`	—	Primary Key.
`slot_system`required	`smallint`	—	Primary Key. PostgreSQL UDT: `int2`.
`day_of_week`required	`smallint`	—	Primary Key. PostgreSQL UDT: `int2`.
`period_number`required	`smallint`	—	Primary Key. PostgreSQL UDT: `int2`.
`start_time`required	`time without time zone`	—	PostgreSQL UDT: `time`.
`end_time`required	`time without time zone`	—	PostgreSQL UDT: `time`.
`is_plus_slot`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`id`	`uuid`	`gen_random_uuid()`	Column `id` stored as `uuid`.

`course_meetings`

Primary Key: id

Foreign Key Constraints:

batch_id → batches.batch_id (course_meetings_batch_id_fkey)
slot_occurrence_id → slot_occurrences.id (course_meetings_slot_occurrence_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`batch_id`required	`text`	—	Foreign key referencing `batches.batch_id`.
`course_id`required	`text`	—	Column `course_id` stored as `text`.
`slot_occurrence_id`required	`uuid`	—	Foreign key referencing `slot_occurrences.id`.
`is_primary`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`venue_override`	`text`	—	Column `venue_override` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`elective_courses`

Primary Key: offering_id

Foreign Key Constraints:

course_code → courses.course_code (elective_courses_course_code_fkey)
department_id → departments.department_id (elective_courses_department_id_fkey)
venue → campus_buildings.id (fk_elective_courses_venue)

Field / Prop	Type	Default	Description
`offering_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`course_id`	`text`	—	Column `course_id` stored as `text`.
`course_code`required	`text`	—	Foreign key referencing `courses.course_code`.
`department_id`required	`text`	—	Foreign key referencing `departments.department_id`.
`slot_name`required	`text`	—	Column `slot_name` stored as `text`.
`slot_system`required	`smallint`	`1`	PostgreSQL UDT: `int2`.
`venue`	`text`	—	Foreign key referencing `campus_buildings.id`.
`is_active`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`enrolled_student_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.

`elective_offering_semesters`

Primary Key: semester_number, offering_id

Foreign Key Constraints:

offering_id → elective_courses.offering_id (elective_offering_semesters_offering_id_fkey)

Field / Prop	Type	Default	Description
`offering_id`required	`uuid`	—	Primary Key. Foreign key referencing `elective_courses.offering_id`.
`semester_number`required	`smallint`	—	Primary Key. PostgreSQL UDT: `int2`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`department_semester_rules`

Primary Key: rule_id

Foreign Key Constraints:

department_id → departments.department_id (department_semester_rules_department_id_fkey)

Field / Prop	Type	Default	Description
`rule_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`department_id`required	`text`	—	Foreign key referencing `departments.department_id`.
`semester_number`required	`smallint`	—	PostgreSQL UDT: `int2`.
`elective_category`required	`text`	—	Column `elective_category` stored as `text`.
`required_count`required	`smallint`	—	PostgreSQL UDT: `int2`.

`course_syllabi`

Primary Key: syllabus_id

Foreign Key Constraints:

course_code → courses.course_code (course_syllabi_course_code_fkey)

Field / Prop	Type	Default	Description
`syllabus_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`course_code`required	`text`	—	Foreign key referencing `courses.course_code`.
`syllabus_path`required	`text`	—	Column `syllabus_path` stored as `text`.
`created_at`required	`timestamp with time zone`	`timezone('utc'::text, now())`	PostgreSQL UDT: `timestamptz`.
`prerequisites`	`text`	—	Column `prerequisites` stored as `text`.
`lecture_hours`	`smallint`	—	PostgreSQL UDT: `int2`.
`tutorial_hours`	`smallint`	—	PostgreSQL UDT: `int2`.
`practical_hours`	`smallint`	—	PostgreSQL UDT: `int2`.
`outside_hours`	`smallint`	—	PostgreSQL UDT: `int2`.
`lecture_sessions`	`smallint`	—	PostgreSQL UDT: `int2`.
`course_outcomes`	`ARRAY`	—	PostgreSQL UDT: `_text`.
`modules`	`jsonb`	—	Column `modules` stored as `jsonb`.
`textbooks`	`ARRAY`	—	PostgreSQL UDT: `_text`.
`reference_list`	`ARRAY`	—	PostgreSQL UDT: `_text`.
`raw_markdown`	`text`	—	Column `raw_markdown` stored as `text`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`course_name`required	`text`	—	Column `course_name` stored as `text`.

`academic_calendar_events`

Primary Key: event_id

Foreign Key Constraints:

semester_id → semesters.semester_id (academic_calendar_events_semester_id_fkey)

Field / Prop	Type	Default	Description
`event_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`semester_id`required	`uuid`	—	Foreign key referencing `semesters.semester_id`.
`event_name`required	`text`	—	Column `event_name` stored as `text`.
`start_date`required	`date`	—	Column `start_date` stored as `date`.
`end_date`required	`date`	—	Column `end_date` stored as `date`.
`event_type`required	`text`	—	Column `event_type` stored as `text`.
`description`	`text`	—	Column `description` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`follows_day`	`smallint`	—	PostgreSQL UDT: `int2`.
`formatted_date`	`text`	—	Column `formatted_date` stored as `text`.

`academic_calendar_overrides`

Primary Key: id

Foreign Key Constraints:

course_meeting_id → course_meetings.id (academic_calendar_overrides_course_meeting_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`override_date`required	`date`	—	Column `override_date` stored as `date`.
`override_type`required	`text`	—	Column `override_type` stored as `text`.
`effective_day`	`smallint`	—	PostgreSQL UDT: `int2`.
`course_id`	`text`	—	Column `course_id` stored as `text`.
`course_meeting_id`	`uuid`	—	Foreign key referencing `course_meetings.id`.
`override_start`	`time without time zone`	—	PostgreSQL UDT: `time`.
`override_end`	`time without time zone`	—	PostgreSQL UDT: `time`.
`override_venue`	`text`	—	Column `override_venue` stored as `text`.
`description`	`text`	—	Column `description` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`class_cancellation_reports`

Primary Key: report_id

Foreign Key Constraints:

class_id → classes.class_id (class_cancellation_reports_class_id_fkey)

Field / Prop	Type	Default	Description
`report_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`class_id`required	`uuid`	—	Foreign key referencing `classes.class_id`.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`reported_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

Attendance, Identity & Gamification

Powers the inverted attendance ledger, student profiles, Amplix points economy, and automated streak/achievement milestones.

`absences`

Primary Key: absence_id

Foreign Key Constraints:

class_id → classes.class_id (absences_class_id_fkey)

Field / Prop	Type	Default	Description
`absence_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`class_id`required	`uuid`	—	Foreign key referencing `classes.class_id`.
`marked_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`marked_by`	`uuid`	—	Column `marked_by` stored as `uuid`.
`source`required	`text`	—	Column `source` stored as `text`.
`reason`	`text`	—	Column `reason` stored as `text`.

`user_course_attendance_state`

Primary Key: user_id, course_id

Foreign Key Constraints:

user_id → users.id (user_course_attendance_state_user_id_fkey)

Field / Prop	Type	Default	Description
`user_id`required	`uuid`	—	Primary Key. Foreign key referencing `users.id`.
`course_id`required	`text`	—	Primary Key.
`state`required	`text`	`'NORMAL'::text`	Column `state` stored as `text`.
`last_percentage`required	`numeric`	—	Column `last_percentage` stored as `numeric`.
`updated_at`	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`user_course_enrollments`

Primary Key: id

Foreign Key Constraints:

user_id → users.id (user_course_enrollments_user_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Foreign key referencing `users.id`.
`course_id`required	`text`	—	Column `course_id` stored as `text`.
`enrolled_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`users`

Primary Key: id

Foreign Key Constraints:

batch_id → batches.batch_id (users_batch_id_fkey)
department_id → departments.department_id (users_department_id_fkey)
id → null.null (users_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`uuid`	—	Primary Key. Foreign key referencing `null.null`.
`email`required	`text`	—	Column `email` stored as `text`.
`full_name`required	`text`	—	Column `full_name` stored as `text`.
`role`required	`USER-DEFINED`	`'student'::user_role`	PostgreSQL UDT: `user_role`.
`avatar_url`	`text`	—	Column `avatar_url` stored as `text`.
`email_verified`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`username`	`text`	—	Column `username` stored as `text`.
`bio`	`text`	—	Column `bio` stored as `text`.
`department_id`	`text`	—	Foreign key referencing `departments.department_id`.
`batch_id`	`text`	—	Foreign key referencing `batches.batch_id`.
`current_semester`	`smallint`	—	PostgreSQL UDT: `int2`.
`onboarding_completed`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`roll_number`	`text`	—	Column `roll_number` stored as `text`.
`amplix_balance`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`current_streak`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`highest_streak`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`enrolled_courses`required	`jsonb`	`'[]'::jsonb`	Column `enrolled_courses` stored as `jsonb`.
`profile_updated_at`	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`odometer`	`integer`	`0`	PostgreSQL UDT: `int4`.

`user_preferences`

Primary Key: user_id

Foreign Key Constraints:

user_id → users.id (user_preferences_user_id_fkey)

Field / Prop	Type	Default	Description
`user_id`required	`uuid`	—	Primary Key. Foreign key referencing `users.id`.
`theme`required	`text`	`'system'::text`	Column `theme` stored as `text`.
`time_format`required	`text`	`'12h'::text`	Column `time_format` stored as `text`.
`timezone`required	`text`	`'Asia/Kolkata'::text`	Column `timezone` stored as `text`.
`language`required	`text`	`'en'::text`	Column `language` stored as `text`.
`action_tone`required	`USER-DEFINED`	`'direct'::action_tone`	PostgreSQL UDT: `action_tone`.
`notifications_enabled`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_class_reminder`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_reminder_minutes`required	`smallint`	`10`	PostgreSQL UDT: `int2`.
`notif_class_cancelled`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_class_rescheduled`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_task_published`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_task_due_soon`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_exam_reminder`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_daily_brief`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_attendance_alert`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_weekly_summary`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`quiet_hours_enabled`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`quiet_hours_start`required	`time without time zone`	`'22:00:00'::time without time zone`	PostgreSQL UDT: `time`.
`quiet_hours_end`required	`time without time zone`	`'07:00:00'::time without time zone`	PostgreSQL UDT: `time`.
`attendance_threshold`required	`smallint`	`80`	PostgreSQL UDT: `int2`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`enable_apod`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`user_mess`required	`text`	`''::text`	Column `user_mess` stored as `text`.
`at_a_glance_view`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`use_scheduled_classes_for_greeting_message`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`use_action_tone_for_greeting_message`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`daily_brief_time`required	`time without time zone`	`'07:00:00'::time without time zone`	PostgreSQL UDT: `time`.
`notif_mess_reminder`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_breakfast_reminder`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_lunch_reminder`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_evening_tea_reminder`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_dinner_reminder`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`notif_mess_reminder_minutes`required	`smallint`	`20`	PostgreSQL UDT: `int2`.

`amplix_ledger`

Primary Key: ledger_id

Foreign Key Constraints:

achievement_progress_id → achievement_progress.achievement_progress_id (amplix_ledger_achievement_progress_id_fkey)
challenge_progress_id → challenge_progress.progress_id (amplix_ledger_challenge_progress_id_fkey)
class_id → classes.class_id (amplix_ledger_class_id_fkey)

Field / Prop	Type	Default	Description
`ledger_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`event_type`required	`text`	—	Column `event_type` stored as `text`.
`points`required	`integer`	—	PostgreSQL UDT: `int4`.
`balance_after`required	`integer`	—	PostgreSQL UDT: `int4`.
`class_id`	`uuid`	—	Foreign key referencing `classes.class_id`.
`event_date`required	`date`	—	Column `event_date` stored as `date`.
`notes`	`text`	—	Column `notes` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`achievement_progress_id`	`uuid`	—	Foreign key referencing `achievement_progress.achievement_progress_id`.
`challenge_progress_id`	`uuid`	—	Foreign key referencing `challenge_progress.progress_id`.

`achievement_templates`

Primary Key: achievement_id

Field / Prop	Type	Default	Description
`achievement_id`required	`text`	—	Primary Key.
`achievement_name`required	`text`	—	Column `achievement_name` stored as `text`.
`achievement_description`required	`text`	—	Column `achievement_description` stored as `text`.
`badge_icon`	`text`	—	Column `badge_icon` stored as `text`.
`achievement_condition`required	`USER-DEFINED`	—	PostgreSQL UDT: `achievement_condition`.
`threshold_value`required	`integer`	—	PostgreSQL UDT: `int4`.
`amplix_reward`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`is_active`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`achievement_progress`

Primary Key: achievement_progress_id

Foreign Key Constraints:

achievement_id → achievement_templates.achievement_id (achievement_progress_achievement_id_fkey)

Field / Prop	Type	Default	Description
`achievement_progress_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`student_id`required	`uuid`	—	Column `student_id` stored as `uuid`.
`achievement_id`required	`text`	—	Foreign key referencing `achievement_templates.achievement_id`.
`progress`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`status`required	`text`	`'in_progress'::text`	Column `status` stored as `text`.
`claimed_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`challenge_templates`

Primary Key: challenge_id

Field / Prop	Type	Default	Description
`challenge_id`required	`text`	—	Primary Key.
`challenge_name`required	`text`	—	Column `challenge_name` stored as `text`.
`challenge_description`required	`text`	—	Column `challenge_description` stored as `text`.
`challenge_type`required	`USER-DEFINED`	—	PostgreSQL UDT: `challenge_type`.
`challenge_condition`required	`USER-DEFINED`	—	PostgreSQL UDT: `challenge_condition`.
`condition_params`required	`jsonb`	`'{}'::jsonb`	Column `condition_params` stored as `jsonb`.
`target_value`required	`integer`	—	PostgreSQL UDT: `int4`.
`amplix_reward`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`weekly_weight`required	`integer`	`1`	PostgreSQL UDT: `int4`.
`monthly_weight`required	`integer`	`1`	PostgreSQL UDT: `int4`.
`template_version`required	`integer`	`1`	PostgreSQL UDT: `int4`.
`is_active`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`challenge_progress`

Primary Key: progress_id

Foreign Key Constraints:

challenge_id → challenge_templates.challenge_id (challenge_progress_challenge_id_fkey)
semester_id → semesters.semester_id (challenge_progress_semester_id_fkey)

Field / Prop	Type	Default	Description
`progress_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`challenge_id`required	`text`	—	Foreign key referencing `challenge_templates.challenge_id`.
`semester_id`required	`uuid`	—	Foreign key referencing `semesters.semester_id`.
`period_key`required	`text`	—	Column `period_key` stored as `text`.
`template_version`required	`integer`	—	PostgreSQL UDT: `int4`.
`progress`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`target_value`required	`integer`	—	PostgreSQL UDT: `int4`.
`amplix_reward`required	`integer`	—	PostgreSQL UDT: `int4`.
`status`required	`text`	`'pending'::text`	Column `status` stored as `text`.
`completed_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`claimed_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`expires_at`required	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`challenge_progress_classes`

Primary Key: progress_id, class_id

Foreign Key Constraints:

class_id → classes.class_id (challenge_progress_classes_class_id_fkey)
progress_id → challenge_progress.progress_id (challenge_progress_classes_progress_id_fkey)

Field / Prop	Type	Default	Description
`progress_id`required	`uuid`	—	Primary Key. Foreign key referencing `challenge_progress.progress_id`.
`class_id`required	`uuid`	—	Primary Key. Foreign key referencing `classes.class_id`.
`contributed_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

Google Calendar Synchronization Pipeline

Implements statement-coalesced calendar synchronization, distributed lease management, and OAuth token encryption.

`user_google_integrations`

Primary Key: user_id

Field / Prop	Type	Default	Description
`user_id`required	`uuid`	—	Primary Key.
`google_email`required	`text`	—	Column `google_email` stored as `text`.
`calendar_id`	`text`	—	Column `calendar_id` stored as `text`.
`calendar_color_id`	`text`	`'7'::text`	Column `calendar_color_id` stored as `text`.
`is_sync_enabled`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`vault_secret_id`required	`uuid`	—	Column `vault_secret_id` stored as `uuid`.
`sync_status`required	`text`	`'pending_initial_sync'::text`	Column `sync_status` stored as `text`.
`last_synced_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`last_error`	`text`	—	Column `last_error` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`sync_generation`required	`integer`	`1`	PostgreSQL UDT: `int4`.

`user_calendar_events`

Primary Key: class_id, user_id

Foreign Key Constraints:

class_id → classes.class_id (user_calendar_events_class_id_fkey)

Field / Prop	Type	Default	Description
`user_id`required	`uuid`	—	Primary Key.
`class_id`required	`uuid`	—	Primary Key. Foreign key referencing `classes.class_id`.
`google_event_id`required	`text`	—	Column `google_event_id` stored as `text`.
`sync_state`required	`text`	`'synced'::text`	Column `sync_state` stored as `text`.
`synced_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`source_hash`	`text`	—	Column `source_hash` stored as `text`.

`gcal_sync_queue`

Primary Key: job_id

Field / Prop	Type	Default	Description
`job_id`required	`bigint`	—	Primary Key. PostgreSQL UDT: `int8`.
`job_type`required	`text`	—	Column `job_type` stored as `text`.
`payload`required	`jsonb`	—	Column `payload` stored as `jsonb`.
`status`required	`text`	`'pending'::text`	Column `status` stored as `text`.
`attempts`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`max_attempts`required	`integer`	`5`	PostgreSQL UDT: `int4`.
`next_run_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`processing_started_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`worker_id`	`text`	—	Column `worker_id` stored as `text`.
`last_error`	`text`	—	Column `last_error` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`completed_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.

`gcal_sync_pending`

Primary Key: batch_id

Field / Prop	Type	Default	Description
`batch_id`required	`text`	—	Primary Key.
`sync_generation`required	`integer`	`1`	PostgreSQL UDT: `int4`.
`requested_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`locked_until`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.

`user_calendar_sync_leases`

Primary Key: user_id

Foreign Key Constraints:

user_id → users.id (user_calendar_sync_leases_user_id_fkey)

Field / Prop	Type	Default	Description
`user_id`required	`uuid`	—	Primary Key. Foreign key referencing `users.id`.
`worker_id`required	`text`	—	Column `worker_id` stored as `text`.
`lease_expires_at`required	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`updated_at`	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`oauth_states`

Primary Key: state

Field / Prop	Type	Default	Description
`state`required	`text`	—	Primary Key.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`pkce_verifier`	`text`	`''::text`	Column `pkce_verifier` stored as `text`.
`expires_at`required	`timestamp with time zone`	`(now() + '00:10:00'::interval)`	PostgreSQL UDT: `timestamptz`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`redirect_uri`	`text`	—	Column `redirect_uri` stored as `text`.

Campus Pedestrian Routing & Transit

Contains the offline graph network, precomputed all-pairs shortest paths (152,904 pairs), real-time bus tracking, and dining menus.

`campus_buildings`

Primary Key: id

Field / Prop	Type	Default	Description
`id`required	`text`	—	Primary Key.
`name`required	`text`	—	Column `name` stored as `text`.
`category`	`text`	—	Column `category` stored as `text`.
`lat`required	`double precision`	—	PostgreSQL UDT: `float8`.
`lng`required	`double precision`	—	PostgreSQL UDT: `float8`.
`nearest_node_id`required	`bigint`	—	PostgreSQL UDT: `int8`.
`snap_dist_m`	`real`	—	PostgreSQL UDT: `float4`.
`description`	`text`	—	Column `description` stored as `text`.
`created_at`	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`lon`	`double precision`	—	PostgreSQL UDT: `float8`.
`amenity`	`text`	—	Column `amenity` stored as `text`.
`building_type`	`text`	—	Column `building_type` stored as `text`.

`graph_nodes`

Primary Key: id

Field / Prop	Type	Default	Description
`id`required	`bigint`	—	Primary Key. PostgreSQL UDT: `int8`.
`lat`required	`double precision`	—	PostgreSQL UDT: `float8`.
`lon`required	`double precision`	—	PostgreSQL UDT: `float8`.
`lng`	`double precision`	—	PostgreSQL UDT: `float8`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`graph_edges`

Primary Key: id

Foreign Key Constraints:

source_node_id → graph_nodes.id (graph_edges_source_node_id_fkey)
target_node_id → graph_nodes.id (graph_edges_target_node_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`bigint`	—	Primary Key. PostgreSQL UDT: `int8`.
`source_node_id`required	`bigint`	—	Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`target_node_id`required	`bigint`	—	Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`distance_m`required	`double precision`	—	PostgreSQL UDT: `float8`.
`walk_seconds`required	`integer`	—	PostgreSQL UDT: `int4`.
`is_bidirectional`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`geometry`required	`jsonb`	—	Column `geometry` stored as `jsonb`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`routing_dataset_versions`

Primary Key: version

Field / Prop	Type	Default	Description
`version`required	`integer`	—	Primary Key. PostgreSQL UDT: `int4`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`published_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`node_count`required	`integer`	—	PostgreSQL UDT: `int4`.
`route_count`required	`integer`	—	PostgreSQL UDT: `int4`.
`tier1_observed_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`tier2_edge_weight_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`tier3_graph_dist_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`tier4_fallback_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`metadata`required	`jsonb`	`'{}'::jsonb`	Column `metadata` stored as `jsonb`.
`is_active`required	`boolean`	`false`	PostgreSQL UDT: `bool`.

`routing_dataset_routes`

Primary Key: origin_node_id, version, destination_node_id

Foreign Key Constraints:

destination_node_id → graph_nodes.id (routing_dataset_routes_destination_node_id_fkey)
origin_node_id → graph_nodes.id (routing_dataset_routes_origin_node_id_fkey)
version → routing_dataset_versions.version (routing_dataset_routes_version_fkey)

Field / Prop	Type	Default	Description
`version`required	`integer`	—	Primary Key. Foreign key referencing `routing_dataset_versions.version`. PostgreSQL UDT: `int4`.
`origin_node_id`required	`bigint`	—	Primary Key. Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`destination_node_id`required	`bigint`	—	Primary Key. Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`walk_seconds`required	`integer`	—	PostgreSQL UDT: `int4`.
`distance_m`	`double precision`	—	PostgreSQL UDT: `float8`.
`source`required	`text`	—	Column `source` stored as `text`.

`observed_walking_trips`

Primary Key: id

Foreign Key Constraints:

destination_node_id → graph_nodes.id (observed_walking_trips_destination_node_id_fkey)
origin_node_id → graph_nodes.id (observed_walking_trips_origin_node_id_fkey)
user_id → users.id (observed_walking_trips_user_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Foreign key referencing `users.id`.
`origin_node_id`required	`bigint`	—	Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`destination_node_id`required	`bigint`	—	Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`started_at`required	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`arrived_at`required	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`duration_seconds`required	`integer`	—	PostgreSQL UDT: `int4`.
`distance_meters`	`double precision`	—	PostgreSQL UDT: `float8`.
`gps_fix_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`avg_horizontal_accuracy_m`	`real`	—	PostgreSQL UDT: `float4`.
`is_outlier`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`outlier_reason`	`text`	—	Column `outlier_reason` stored as `text`.
`device_metadata`	`jsonb`	`'{}'::jsonb`	Column `device_metadata` stored as `jsonb`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`route_cache`

Primary Key: id

Foreign Key Constraints:

dest_building_id → campus_buildings.id (route_cache_dest_building_id_fkey)
origin_building_id → campus_buildings.id (route_cache_origin_building_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`origin_building_id`	`text`	—	Foreign key referencing `campus_buildings.id`.
`dest_building_id`	`text`	—	Foreign key referencing `campus_buildings.id`.
`route_result`required	`jsonb`	—	Column `route_result` stored as `jsonb`.
`distance_m`required	`double precision`	—	PostgreSQL UDT: `float8`.
`walk_seconds`required	`integer`	—	PostgreSQL UDT: `int4`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`route_statistics`

Primary Key: origin_node_id, destination_node_id

Foreign Key Constraints:

destination_node_id → graph_nodes.id (route_statistics_destination_node_id_fkey)
origin_node_id → graph_nodes.id (route_statistics_origin_node_id_fkey)

Field / Prop	Type	Default	Description
`origin_node_id`required	`bigint`	—	Primary Key. Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`destination_node_id`required	`bigint`	—	Primary Key. Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`sample_count`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`p50_duration_seconds`required	`integer`	—	PostgreSQL UDT: `int4`.
`p75_duration_seconds`required	`integer`	—	PostgreSQL UDT: `int4`.
`p90_duration_seconds`	`integer`	—	PostgreSQL UDT: `int4`.
`min_duration_seconds`	`integer`	—	PostgreSQL UDT: `int4`.
`max_duration_seconds`	`integer`	—	PostgreSQL UDT: `int4`.
`mean_duration_seconds`	`numeric`	—	Column `mean_duration_seconds` stored as `numeric`.
`representative_distance_m`	`double precision`	—	PostgreSQL UDT: `float8`.
`last_observed_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`buses`

Primary Key: bus_id

Field / Prop	Type	Default	Description
`bus_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`route_name`required	`text`	—	Column `route_name` stored as `text`.
`stops_summary`required	`text`	—	Column `stops_summary` stored as `text`.
`is_active`	`boolean`	`true`	PostgreSQL UDT: `bool`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`bus_stops`

Primary Key: id

Foreign Key Constraints:

nearest_node_id → graph_nodes.id (bus_stops_nearest_node_id_fkey)

Field / Prop	Type	Default	Description
`id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`stop_id`required	`text`	—	Column `stop_id` stored as `text`.
`name`required	`text`	—	Column `name` stored as `text`.
`lat`required	`double precision`	—	PostgreSQL UDT: `float8`.
`lng`required	`double precision`	—	PostgreSQL UDT: `float8`.
`nearest_node_id`	`bigint`	—	Foreign key referencing `graph_nodes.id`. PostgreSQL UDT: `int8`.
`is_active`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`description`	`text`	—	Column `description` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`bus_timings`

Primary Key: timing_id

Foreign Key Constraints:

bus_id → buses.bus_id (bus_timings_bus_id_fkey)

Field / Prop	Type	Default	Description
`timing_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`bus_id`	`uuid`	—	Foreign key referencing `buses.bus_id`.
`departure_time`required	`time without time zone`	—	PostgreSQL UDT: `time`.
`is_special`	`boolean`	`false`	PostgreSQL UDT: `bool`.
`sort_order`required	`integer`	—	PostgreSQL UDT: `int4`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`messes`

Primary Key: mess_id

Field / Prop	Type	Default	Description
`mess_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`name`required	`text`	—	Column `name` stored as `text`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`mess_menu`

Primary Key: menu_id

Foreign Key Constraints:

mess_id → messes.mess_id (mess_menu_mess_id_fkey)

Field / Prop	Type	Default	Description
`menu_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`mess_id`	`uuid`	—	Foreign key referencing `messes.mess_id`.
`weekday`required	`smallint`	—	PostgreSQL UDT: `int2`.
`meal`required	`text`	—	Column `meal` stored as `text`.
`menu`required	`text`	—	Column `menu` stored as `text`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

Push Notifications & Student Tasks

OneSignal push notification queues, dispatch logs, academic assignment trackers, and NASA APOD media caching.

`notification_devices`

Primary Key: device_id

Field / Prop	Type	Default	Description
`device_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`onesignal_subscription_id`required	`text`	—	Column `onesignal_subscription_id` stored as `text`.
`platform`required	`text`	—	Column `platform` stored as `text`.
`app_version`	`text`	—	Column `app_version` stored as `text`.
`device_model`	`text`	—	Column `device_model` stored as `text`.
`is_active`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`last_seen_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`registered_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`deactivated_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.

`notification_queue`

Primary Key: job_id

Foreign Key Constraints:

recipient_batch_id → batches.batch_id (notification_queue_recipient_batch_id_fkey)

Field / Prop	Type	Default	Description
`job_id`required	`bigint`	—	Primary Key. PostgreSQL UDT: `int8`.
`notification_type`required	`text`	—	Column `notification_type` stored as `text`.
`recipient_user_id`	`uuid`	—	Column `recipient_user_id` stored as `uuid`.
`recipient_batch_id`	`text`	—	Foreign key referencing `batches.batch_id`.
`course_id`	`text`	—	Column `course_id` stored as `text`.
`payload`required	`jsonb`	`'{}'::jsonb`	Column `payload` stored as `jsonb`.
`dedup_key`	`text`	—	Column `dedup_key` stored as `text`.
`status`required	`text`	`'pending'::text`	Column `status` stored as `text`.
`attempts`required	`integer`	`0`	PostgreSQL UDT: `int4`.
`max_attempts`required	`integer`	`3`	PostgreSQL UDT: `int4`.
`scheduled_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`processing_started_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`worker_id`	`text`	—	Column `worker_id` stored as `text`.
`sent_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`last_error`	`text`	—	Column `last_error` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`dedup_key_checked`	`boolean`	`false`	PostgreSQL UDT: `bool`.
`next_retry_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.

`notification_log`

Primary Key: log_id

Foreign Key Constraints:

related_class_id → classes.class_id (notification_log_related_class_id_fkey)
related_task_id → tasks.task_id (notification_log_related_task_id_fkey)

Field / Prop	Type	Default	Description
`log_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`notification_type`required	`text`	—	Column `notification_type` stored as `text`.
`title`required	`text`	—	Column `title` stored as `text`.
`body`required	`text`	—	Column `body` stored as `text`.
`data`required	`jsonb`	`'{}'::jsonb`	Column `data` stored as `jsonb`.
`onesignal_id`	`text`	—	Column `onesignal_id` stored as `text`.
`is_read`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`read_at`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`sent_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`related_class_id`	`uuid`	—	Foreign key referencing `classes.class_id`.
`related_task_id`	`uuid`	—	Foreign key referencing `tasks.task_id`.
`dedup_key`	`text`	—	Column `dedup_key` stored as `text`.
`delivery_status`	`text`	`'sent'::text`	Column `delivery_status` stored as `text`.
`onesignal_recipients`	`integer`	`0`	PostgreSQL UDT: `int4`.

`notification_daily_stats`

Primary Key: None

Field / Prop	Type	Default	Description
`stat_date`	`date`	—	Column `stat_date` stored as `date`.
`notification_type`	`text`	—	Column `notification_type` stored as `text`.
`delivery_status`	`text`	—	Column `delivery_status` stored as `text`.
`total_jobs`	`bigint`	—	PostgreSQL UDT: `int8`.
`total_recipients`	`bigint`	—	PostgreSQL UDT: `int8`.

`tasks`

Primary Key: task_id

Foreign Key Constraints:

batch_id → batches.batch_id (tasks_batch_id_fkey)
course_code → courses.course_code (tasks_course_code_fkey)
semester_id → semesters.semester_id (tasks_semester_id_fkey)

Field / Prop	Type	Default	Description
`task_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`semester_id`required	`uuid`	—	Foreign key referencing `semesters.semester_id`.
`batch_id`	`text`	—	Foreign key referencing `batches.batch_id`.
`task_type`required	`text`	—	Column `task_type` stored as `text`.
`title`required	`text`	—	Column `title` stored as `text`.
`description`	`text`	—	Column `description` stored as `text`.
`due_date`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`exam_start`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`exam_end`	`timestamp with time zone`	—	PostgreSQL UDT: `timestamptz`.
`venue`	`text`	—	Column `venue` stored as `text`.
`max_score`	`numeric`	—	Column `max_score` stored as `numeric`.
`attachments`required	`jsonb`	`'[]'::jsonb`	Column `attachments` stored as `jsonb`.
`is_visible`required	`boolean`	`true`	PostgreSQL UDT: `bool`.
`created_by`required	`uuid`	—	Column `created_by` stored as `uuid`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`course_code`required	`text`	—	Foreign key referencing `courses.course_code`.

`student_task_records`

Primary Key: record_id

Foreign Key Constraints:

task_id → tasks.task_id (student_task_records_task_id_fkey)

Field / Prop	Type	Default	Description
`record_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`user_id`required	`uuid`	—	Column `user_id` stored as `uuid`.
`task_id`required	`uuid`	—	Foreign key referencing `tasks.task_id`.
`status`required	`text`	`'pending'::text`	Column `status` stored as `text`.
`obtained_marks`	`numeric`	—	Column `obtained_marks` stored as `numeric`.
`personal_note`	`text`	—	Column `personal_note` stored as `text`.
`marked_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`app_version_control`

Primary Key: version_id

Field / Prop	Type	Default	Description
`version_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`current_version`required	`text`	—	Column `current_version` stored as `text`.
`build_number`required	`bigint`	—	PostgreSQL UDT: `int8`.
`version_download_link`	`text`	—	Column `version_download_link` stored as `text`.
`is_web_available`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`force_update`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`is_current`required	`boolean`	`false`	PostgreSQL UDT: `bool`.
`release_notes`	`text`	—	Column `release_notes` stored as `text`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`updated_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`apod`

Primary Key: apod_date

Field / Prop	Type	Default	Description
`apod_date`required	`date`	—	Primary Key.
`title`required	`text`	—	Column `title` stored as `text`.
`description`required	`text`	—	Column `description` stored as `text`.
`image_url`	`text`	—	Column `image_url` stored as `text`.
`hd_image_url`	`text`	—	Column `hd_image_url` stored as `text`.
`media_type`required	`text`	`'image'::text`	Column `media_type` stored as `text`.
`fetched_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`blur_hash`	`text`	—	Column `blur_hash` stored as `text`.

Audit & Governance

Immutable system logs tracking administrative actions, timetable regenerations, and Row-Level Security policy execution.

`admin_audit_log`

Primary Key: log_id

Foreign Key Constraints:

semester_id → semesters.semester_id (admin_audit_log_semester_id_fkey)

Field / Prop	Type	Default	Description
`log_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`admin_id`required	`uuid`	—	Column `admin_id` stored as `uuid`.
`action_type`required	`text`	—	Column `action_type` stored as `text`.
`target_table`required	`text`	—	Column `target_table` stored as `text`.
`target_id`required	`text`	—	Column `target_id` stored as `text`.
`before_state`	`jsonb`	—	Column `before_state` stored as `jsonb`.
`after_state`	`jsonb`	—	Column `after_state` stored as `jsonb`.
`semester_id`	`uuid`	—	Foreign key referencing `semesters.semester_id`.
`performed_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`audit_log`

Primary Key: audit_id

Field / Prop	Type	Default	Description
`audit_id`required	`bigint`	—	Primary Key. PostgreSQL UDT: `int8`.
`table_name`required	`text`	—	Column `table_name` stored as `text`.
`operation`required	`text`	—	Column `operation` stored as `text`.
`actor_id`	`uuid`	—	Column `actor_id` stored as `uuid`.
`row_pk`	`text`	—	Column `row_pk` stored as `text`.
`old_data`	`jsonb`	—	Column `old_data` stored as `jsonb`.
`new_data`	`jsonb`	—	Column `new_data` stored as `jsonb`.
`created_at`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.

`scheduling_audit_log`

Primary Key: log_id

Foreign Key Constraints:

batch_id → batches.batch_id (scheduling_audit_log_batch_id_fkey)
class_id → classes.class_id (scheduling_audit_log_class_id_fkey)
course_code → courses.course_code (scheduling_audit_log_course_code_fkey)
semester_id → semesters.semester_id (scheduling_audit_log_semester_id_fkey)

Field / Prop	Type	Default	Description
`log_id`required	`uuid`	`gen_random_uuid()`	Primary Key.
`run_id`required	`uuid`	—	Column `run_id` stored as `uuid`.
`semester_id`	`uuid`	—	Foreign key referencing `semesters.semester_id`.
`batch_id`	`text`	—	Foreign key referencing `batches.batch_id`.
`execution_time`required	`timestamp with time zone`	`now()`	PostgreSQL UDT: `timestamptz`.
`action`required	`text`	—	Column `action` stored as `text`.
`class_id`	`uuid`	—	Foreign key referencing `classes.class_id`.
`reason`	`text`	—	Column `reason` stored as `text`.
`details`	`jsonb`	—	Column `details` stored as `jsonb`.
`course_code`required	`text`	—	Foreign key referencing `courses.course_code`.

Database Tables & Schemas

Was this page helpful?

On this page