Entity Relationship Diagram

The diagram below shows all GCMS database tables and the relationships between them. Each box represents a table, and each line represents a foreign key relationship.

        erDiagram
    users ||--o{ user_projects : "joins"
    projects ||--o{ user_projects : "has members"

    users ||--o{ projects : "leads"
    users ||--o{ projects : "creates"

    projects ||--o{ tasks : "contains"
    users ||--o{ tasks : "assigned to"

    projects ||--o{ messages : "has"
    users ||--o{ messages : "sends"

    projects ||--o{ meetings : "schedules"
    users ||--o{ meetings : "leads"

    meetings ||--o{ meeting_attendances : "tracks"
    users ||--o{ meeting_attendances : "attends"

    projects ||--o{ widgets : "displays"

    users ||--o{ notifications : "receives"
    projects ||--o{ notifications : "triggers"

    projects ||--o{ files : "stores"

    projects ||--o{ ai_chat_messages : "has chat history"
    users ||--o{ ai_chat_messages : "authors"

    users {
        uuid user_id PK
        varchar user_first_name
        varchar user_last_name
        varchar user_email UK
        text microsoft_id UK
        varchar username UK
        timestamptz date_created
        timestamptz last_login
        boolean email_notifications
    }

    projects {
        uuid project_id PK
        uuid team_leader_id FK
        uuid created_by FK
        varchar project_name
        date project_deadline
        timestamptz p_date_created
        timestamptz p_time_updated
    }

    user_projects {
        uuid user_id PK_FK
        uuid project_id PK_FK
    }

    tasks {
        uuid task_id PK
        uuid project_id FK
        uuid assignee_id FK
        varchar task_title
        text task_description
        numeric task_weight
        task_status task_status
        date task_deadline
        timestamptz t_date_created
        timestamptz t_time_updated
    }

    messages {
        uuid message_id PK
        uuid sender_id FK
        uuid project_id FK
        text message_content
        timestamptz m_date_sent
    }

    meetings {
        uuid meeting_id PK
        uuid team_leader_id FK
        uuid project_id FK
        timestamptz scheduled_time
        integer meeting_duration
        meeting_location meeting_location
        text meeting_notes
    }

    meeting_attendances {
        uuid attendance_id PK
        uuid user_id FK
        uuid meeting_id FK
        attendance_status attendance_status
        timestamptz check_in_time
    }

    notifications {
        uuid notification_id PK
        uuid user_id FK
        uuid project_id FK
        notification_type notification_type
        text notification_message
        timestamptz n_date_created
        text target_username
        text project_name
    }

    widgets {
        uuid widget_id PK
        uuid project_id FK
        numeric widget_x
        numeric widget_y
        varchar widget_text
    }

    files {
        uuid file_id PK
        uuid project_id FK
        varchar file_name
        text storage_path
        bigint size
        timestamp date_uploaded
    }

    ai_chat_messages {
        uuid ai_message_id PK
        uuid project_id FK
        uuid user_id FK
        ai_chat_role role
        text content
        timestamptz ai_date_sent
    }
    

Reading the diagram

• PK — primary key

• FK — foreign key

• UK — unique constraint

• PK_FK — primary key that is also a foreign key (composite key in junction tables)

• ||--o{ — one-to-many relationship (one entity on the left, many on the right)