Entity Reference ================ Detailed reference for every table in the GCMS database. users ----- Stores user account information populated from Microsoft OAuth on first sign-in. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``user_id`` ``uuid`` Primary key, auto-generated ``user_first_name`` ``varchar`` From Microsoft profile ``user_last_name`` ``varchar`` From Microsoft profile ``user_email`` ``varchar`` Unique, from Microsoft profile ``microsoft_id`` ``text`` Unique Microsoft account identifier ``username`` ``varchar`` Unique, 3–20 characters, user-chosen ``date_created`` ``timestamptz`` Account creation timestamp ``last_login`` ``timestamptz`` Most recent login timestamp ``email_notifications`` ``boolean`` Whether the user has opted into email notifications, default ``false`` ============================ ===================== ============================================= projects -------- Represents a group coursework project. Each project has one team leader and a deadline, with members joined via ``user_projects``. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``project_id`` ``uuid`` Primary key, auto-generated ``team_leader_id`` ``uuid`` FK → ``users.user_id`` ``created_by`` ``uuid`` FK → ``users.user_id`` ``project_name`` ``varchar`` Display name ``project_deadline`` ``date`` Final due date ``p_date_created`` ``timestamptz`` Creation timestamp ``p_time_updated`` ``timestamptz`` Last update timestamp ============================ ===================== ============================================= user_projects ------------- Junction table establishing the many-to-many relationship between users and projects. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``user_id`` ``uuid`` FK → ``users.user_id`` ``project_id`` ``uuid`` FK → ``projects.project_id`` ============================ ===================== ============================================= The composite primary key ``(user_id, project_id)`` ensures a user cannot be added to the same project twice. tasks ----- Individual units of work within a project. Tasks have weights that feed into the contribution tracking system. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``task_id`` ``uuid`` Primary key, auto-generated ``project_id`` ``uuid`` FK → ``projects.project_id`` ``assignee_id`` ``uuid`` FK → ``users.user_id``, nullable ``task_title`` ``varchar`` Short title ``task_description`` ``text`` Optional detail ``task_weight`` ``numeric`` Used to calculate contribution percentages ``task_status`` ``task_status`` Enum (see Custom Types below) ``task_deadline`` ``date`` Optional task-level deadline ``t_date_created`` ``timestamptz`` Creation timestamp ``t_time_updated`` ``timestamptz`` Last update timestamp ============================ ===================== ============================================= messages -------- Chat messages within a project's group chat. Loaded historically on page open and updated in real-time via Socket.io. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``message_id`` ``uuid`` Primary key, auto-generated ``sender_id`` ``uuid`` FK → ``users.user_id`` ``project_id`` ``uuid`` FK → ``projects.project_id`` ``message_content`` ``text`` Message body ``m_date_sent`` ``timestamptz`` Send timestamp ============================ ===================== ============================================= meetings -------- Scheduled meetings within a project. Each meeting also creates a corresponding event in the team leader's Microsoft calendar via the Graph API. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``meeting_id`` ``uuid`` Primary key, auto-generated ``team_leader_id`` ``uuid`` FK → ``users.user_id`` ``project_id`` ``uuid`` FK → ``projects.project_id`` ``scheduled_time`` ``timestamptz`` When the meeting starts ``meeting_duration`` ``integer`` Duration in minutes ``meeting_location`` ``meeting_location`` Enum (see Custom Types below) ``meeting_notes`` ``text`` Optional agenda or notes ============================ ===================== ============================================= meeting_attendances ------------------- Tracks which users attended which meetings. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``attendance_id`` ``uuid`` Primary key, auto-generated ``user_id`` ``uuid`` FK → ``users.user_id`` ``meeting_id`` ``uuid`` FK → ``meetings.meeting_id`` ``attendance_status`` ``attendance_status`` Enum (see Custom Types below) ``check_in_time`` ``timestamptz`` Time the user checked in ============================ ===================== ============================================= notifications ------------- Per-user notifications generated by activity across projects. Includes denormalised ``target_username`` and ``project_name`` fields to avoid extra joins when rendering notification messages. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``notification_id`` ``uuid`` Primary key, auto-generated ``user_id`` ``uuid`` FK → ``users.user_id``, recipient ``project_id`` ``uuid`` FK → ``projects.project_id``, nullable ``notification_type`` ``notification_type`` Enum (see Custom Types below) ``notification_message`` ``text`` Display text ``n_date_created`` ``timestamptz`` Creation timestamp ``target_username`` ``text`` Denormalised — user the notification refers to ``project_name`` ``text`` Denormalised — project the notification refers to ============================ ===================== ============================================= widgets ------- Sticky-note style widgets placed on the interactive project board. Each widget has a position on the board (``widget_x``, ``widget_y``) and free-form text. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``widget_id`` ``uuid`` Primary key, auto-generated ``project_id`` ``uuid`` FK → ``projects.project_id`` ``widget_x`` ``numeric`` X coordinate on the board ``widget_y`` ``numeric`` Y coordinate on the board ``widget_text`` ``varchar`` Widget content, optional ============================ ===================== ============================================= files ----- Metadata for files uploaded to a project's shared folder. The actual file contents are stored in a Supabase Storage bucket; this table only tracks the metadata. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``file_id`` ``uuid`` Primary key, auto-generated ``project_id`` ``uuid`` FK → ``projects.project_id`` ``file_name`` ``varchar`` Original file name as uploaded ``storage_path`` ``text`` Path within the Supabase Storage bucket ``size`` ``bigint`` File size in bytes ``date_uploaded`` ``timestamp`` Upload timestamp ============================ ===================== ============================================= ai_chat_messages ---------------- Stores the per-project chat history with the GCMS AI assistant (powered by Google Gemini). Each row represents one message in the conversation, from either the user or the assistant. ============================ ===================== ============================================= Column Type Notes ============================ ===================== ============================================= ``ai_message_id`` ``uuid`` Primary key, auto-generated ``project_id`` ``uuid`` FK → ``projects.project_id``, ``ON DELETE CASCADE`` ``user_id`` ``uuid`` FK → ``users.user_id``, ``ON DELETE SET NULL`` ``role`` ``ai_chat_role`` Enum (see Custom Types below) ``content`` ``text`` Message body ``ai_date_sent`` ``timestamptz`` Send timestamp, defaults to ``NOW()`` ============================ ===================== ============================================= An index on ``(project_id, ai_date_sent)`` keeps history retrieval fast even as conversations grow. Custom Types ------------ GCMS uses several PostgreSQL enum types to constrain values to a fixed set of options. ``task_status`` Used by ``tasks.task_status``. Represents the lifecycle state of a task. - ``To Do`` - ``In Progress`` - ``Completed`` ``meeting_location`` Used by ``meetings.meeting_location``. Indicates whether a meeting takes place online or in person. - ``Virtual`` - ``Presential`` ``attendance_status`` Used by ``meeting_attendances.attendance_status``. Records whether a user attended a meeting. - ``Present`` - ``Not Present`` ``notification_type`` Used by ``notifications.notification_type``. Determines the category of a notification, used by the frontend to choose icon and styling. - ``Message`` — a new chat message - ``Project`` — project-level update - ``Member Leave`` — a member left the project - ``Member Join`` — a member joined the project - ``Leader`` — leadership change - ``Task`` — task assigned, updated, or completed ``ai_chat_role`` Used by ``ai_chat_messages.role``. Distinguishes who sent each message in an AI assistant conversation. - ``user`` — the human's message - ``assistant`` — the AI's response