PostgreSQL 16 Prototype based on ER diagram - bounswe/bounswe2025group2 GitHub Wiki

Based on PostgreSQL 16
-- Database schema generated from ER model
-- PostgreSQL 16
-- Save as db_schema_pg.sql and run:
--   psql -U <user> -d <db_name> -f db_schema_pg.sql

-- Enable uuid generation helper
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ********************
-- custom types
-- ********************
CREATE TYPE user_role AS ENUM ('regular','mentor','coach','admin');

-- ********************
-- core user tables
-- ********************
CREATE TABLE app_user (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role user_role DEFAULT 'regular',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE profile (
    user_id UUID PRIMARY KEY,
    bio TEXT,
    profile_picture VARCHAR(255),
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE badge (
    badge_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(255),
    source VARCHAR(255),
    CONSTRAINT fk_badge_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

CREATE INDEX idx_badge_user ON badge(user_id);

CREATE TABLE goal (
    goal_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    type VARCHAR(100),
    metric VARCHAR(100),
    target_value DOUBLE PRECISION,
    current_value DOUBLE PRECISION,
    status VARCHAR(50),
    CONSTRAINT fk_goal_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);
CREATE INDEX idx_goal_user ON goal(user_id);

-- ********************
-- challenges & leaderboard
-- ********************
CREATE TABLE challenge (
    challenge_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    type VARCHAR(100),
    start_date DATE,
    end_date DATE,
    status VARCHAR(50)
);

CREATE TABLE user_challenge (
    user_id UUID,
    challenge_id UUID,
    joined_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    progress DOUBLE PRECISION,
    PRIMARY KEY (user_id, challenge_id),
    CONSTRAINT fk_uc_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_uc_challenge
        FOREIGN KEY (challenge_id) REFERENCES challenge(challenge_id)
        ON DELETE CASCADE
);

CREATE TABLE leaderboard (
    challenge_id UUID PRIMARY KEY,
    last_updated TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lb_challenge
        FOREIGN KEY (challenge_id) REFERENCES challenge(challenge_id)
        ON DELETE CASCADE
);

CREATE TABLE leaderboard_entry (
    challenge_id UUID,
    user_id UUID,
    rank INT,
    points DOUBLE PRECISION,
    PRIMARY KEY (challenge_id, user_id),
    CONSTRAINT fk_lbe_challenge
        FOREIGN KEY (challenge_id) REFERENCES challenge(challenge_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_lbe_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

-- ********************
-- messaging
-- ********************
CREATE TABLE chat (
    chat_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE chat_participant (
    chat_id UUID,
    user_id UUID,
    added_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (chat_id, user_id),
    CONSTRAINT fk_cp_chat
        FOREIGN KEY (chat_id) REFERENCES chat(chat_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_cp_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

CREATE TABLE message (
    message_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chat_id UUID NOT NULL,
    sender_id UUID NOT NULL,
    content TEXT NOT NULL,
    sent_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_msg_chat
        FOREIGN KEY (chat_id) REFERENCES chat(chat_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_msg_sender
        FOREIGN KEY (sender_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

CREATE INDEX idx_msg_chat ON message(chat_id);
CREATE INDEX idx_msg_sender ON message(sender_id);

-- ********************
-- forum
-- ********************
CREATE TABLE forum (
    forum_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL
);

CREATE TABLE thread (
    thread_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    forum_id UUID NOT NULL,
    creator_id UUID NOT NULL,
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_thread_forum
        FOREIGN KEY (forum_id) REFERENCES forum(forum_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_thread_creator
        FOREIGN KEY (creator_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);
CREATE INDEX idx_thread_forum ON thread(forum_id);

CREATE TABLE post (
    post_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    thread_id UUID NOT NULL,
    author_id UUID NOT NULL,
    parent_post_id UUID,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    upvotes INT DEFAULT 0,
    downvotes INT DEFAULT 0,
    CONSTRAINT fk_post_thread
        FOREIGN KEY (thread_id) REFERENCES thread(thread_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_post_author
        FOREIGN KEY (author_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_post_parent
        FOREIGN KEY (parent_post_id) REFERENCES post(post_id)
        ON DELETE CASCADE
);
CREATE INDEX idx_post_thread ON post(thread_id);

-- ********************
-- mentoring & coaching
-- ********************
CREATE TABLE mentor (
    user_id UUID PRIMARY KEY,
    CONSTRAINT fk_mentor_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

CREATE TABLE coach (
    user_id UUID PRIMARY KEY,
    verification_data TEXT,
    CONSTRAINT fk_coach_user
        FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

CREATE TABLE mentor_mentee (
    mentor_id UUID,
    mentee_id UUID,
    started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMPTZ,
    PRIMARY KEY (mentor_id, mentee_id),
    CONSTRAINT fk_mm_mentor
        FOREIGN KEY (mentor_id) REFERENCES mentor(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_mm_mentee
        FOREIGN KEY (mentee_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

CREATE TABLE coach_mentee (
    coach_id UUID,
    mentee_id UUID,
    started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (coach_id, mentee_id),
    CONSTRAINT fk_cm_coach
        FOREIGN KEY (coach_id) REFERENCES coach(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_cm_mentee
        FOREIGN KEY (mentee_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);

CREATE TABLE feedback (
    feedback_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sender_id UUID NOT NULL,
    recipient_id UUID NOT NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_fb_sender
        FOREIGN KEY (sender_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_fb_recipient
        FOREIGN KEY (recipient_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);
CREATE INDEX idx_fb_recipient ON feedback(recipient_id);

-- ********************
-- notifications & mail
-- ********************
CREATE TABLE notification (
    notification_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    recipient_id UUID NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notif_recipient
        FOREIGN KEY (recipient_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);
CREATE INDEX idx_notif_recipient ON notification(recipient_id);

CREATE TABLE mail (
    mail_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    recipient_id UUID NOT NULL,
    subject VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    sent_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    is_sent BOOLEAN DEFAULT FALSE,
    CONSTRAINT fk_mail_recipient
        FOREIGN KEY (recipient_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
);
CREATE INDEX idx_mail_recipient ON mail(recipient_id);
PostgreSQL 16 Prototype based on ER diagram - bounswe/bounswe2025group2 GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️
