Skip to content

Database

Jump to bottom
Till Ermold edited this page May 30, 2025 · 21 revisions

Entity Relationship Diagram

erDiagram
    User {
        int user_id PK
        varchar(255) email "UK NN"
        varchar(255) password "NN"
        boolean account_status
        varchar(255) profile_picture
    }

    Authentication {
        int auth_id PK
        int user_id FK "UK NN"
        varchar(100) google_id "UK"
        varchar(100) source "NN"
    }

    Group {
        int group_id PK
        varchar(100) group_name "NN"
        varchar(20) invite_code
        datetime created_at
        int owner FK
        varchar(50) timezone 
    }

    GroupMember {
        int member_id PK
        int group_id FK "UK NN"
        int user_id FK "UK NN"
        datetime joined_at
    }

    Message {
        int message_id PK
        int sender_id FK "NN"
        int group_id FK "NN"
        varchar(50) message_type "NN"
        varchar(2000) content
        datetime sent_at
    }

    MessageReaction {
        bigint reaction_id PK
        int message_id FK "UK"
        int user_id FK "UK"
        varchar(50) reaction_type "UK NN"
    }

    GroupChallenge {
        int challenge_id PK
        int group_id FK "NN"
        varchar(1000) description
        date start_date "NN"
        date end_date "NN"
        datetime created_at
        datetime updated_at
    }

    UserWeeklyTarget {
        int target_id PK
        int user_id FK "UK NN"
        int group_challenge_id FK "UK NN"
        int target_count "NN"
        int achieved_count "NN"
        varchar(255) current_progress
        datetime created_at
    }

User ||--o{ GroupMember: "is"
User ||--o| Authentication: "has"
User ||--o{ Message: "sends"
User ||--o{ MessageReaction: "creates"
User ||--o{ UserWeeklyTarget: "has"
User ||--o{ Group: "owns"
Group ||--o{ GroupChallenge: "hosts"
GroupMember }o--|| Group: "is member of"
Message }o--|| Group: "is sent in"
Message ||--o{ MessageReaction: "has"
GroupChallenge ||--o{ UserWeeklyTarget: "tracks"
Loading

Implemented and Planned Use Cases

This section outlines the key use cases supported, indicating their current implementation status.

1. User Management

  • UC001: User Registration
    • Description: A new user can create an account by providing an email and password.
    • Status: Implemented (User entity, Auth service with password hashing, DTO validation for email/password strength).
  • UC002: User Login (Email/Password)
    • Description: An existing user can log in using their registered email and password to gain authenticated access to the system.
    • Status: Implemented (Auth service, LocalAuthGuard, JWT generation for session).
  • UC003: User Login (Google OAuth)
    • Description: A user can log in using their Google account for authentication.
    • Status: Implemented (Authentication entity, Google OAuth strategy).
  • UC004: Password Change
    • Description: An authenticated user can change their account password.
    • Status: Implemented (Auth service, password hashing for new password).
  • UC005: Passwordless Login (WebAuthn/Passkey)
    • Description: A user can register and use a passkey (e.g., fingerprint, Face ID) for passwordless and phishing-resistant authentication.
    • Status: Partially Implemented (Passkey entity, Auth service methods for challenge generation and verification are in place, but full integration into the login flow might need more frontend and backend orchestration).
  • UC006: User Profile Management
    • Description: A user can view and update their profile information (e.g., profile picture, account status).
    • Status: Planned (User entity has profile_picture and account_status fields, but CRUD operations for these are not explicitly detailed in our current scope).

2. Group Management

  • UC007: Group Creation
    • Description: A user can create a new group, becoming its owner. A unique invite code is generated for the group.
    • Status: Implemented (Group entity, group creation logic, unique invite code generation implied by @Unique).
  • UC008: Join Group by Invite Code
    • Description: A user can join an existing group using its unique invite code.
    • Status: Implemented (GroupMember entity, logic to add user to group, unique constraint for group_id/user_id).
  • UC009: View My Groups
    • Description: An authenticated user can view a list of all groups they are a member of.
    • Status: Implemented (Querying GroupMember entity by user_id).
  • UC010: View Group Details
    • Description: An authenticated user can view detailed information about a specific group they are a member of (e.g., members list, name, challenges).
    • Status: Partially Implemented (Group entity and relationships exist, but robust authorization checks to ensure the viewing user is actually a member/owner were proposed but not yet fully implemented in service methods).
  • UC011: Leave Group
    • Description: A user can voluntarily leave a group.
    • Status: Planned (Requires deletion logic from GroupMember entity, not explicitly detailed).
  • UC012: Remove Member from Group (Owner/Admin)
    • Description: The group owner or an administrator can remove a member from their group.
    • Status: Planned (Requires specific authorization logic and deletion from GroupMember).
  • UC013: Delete Group (Owner Only)
    • Description: The group owner can delete their group, which typically cascades to associated data (members, challenges, messages).
    • Status: Partially Implemented (Group entity and deletion logic exist, but robust authorization checks to ensure only the owner can delete were proposed but not yet fully implemented in service methods).

3. Group Challenges & Weekly Targets

  • UC014: Create Group Challenge
    • Description: A group owner or admin can create a new challenge for their group, defining its description, start, and end dates.
    • Status: Implemented (GroupChallenge entity, basic creation logic).
  • UC015: View Group Challenges
    • Description: Members of a group can view the challenges associated with their group.
    • Status: Implemented (Querying GroupChallenge entity by group_id).
  • UC016: Set My Weekly Target for a Challenge
    • Description: A group member can set their personal weekly target count for an active group challenge.
    • Status: Implemented (UserWeeklyTarget entity, basic creation/update logic).
  • UC017: Update My Achieved Count for Weekly Target
    • Description: A user can update the count of their achievements against their weekly target.
    • Status: Implemented (UserWeeklyTarget entity, update logic, includes database CHECK constraints to ensure data validity).
  • UC018: View My Weekly Targets
    • Description: A user can view their own set weekly targets and progress.
    • Status: Implemented (Querying UserWeeklyTarget entity by user_id).
  • UC019: View Group Members' Weekly Targets
    • Description: Group members can view the weekly targets and progress of other members within their group.
    • Status: Planned (Requires querying UserWeeklyTarget based on group membership, not explicitly detailed).

4. Group Chat

  • UC020: Send Message to Group
    • Description: A group member can send a text message or a specific type of message (e.g., system notification, achievement) to their group's chat.
    • Status: Implemented (Message entity, basic creation logic, including input sanitization for XSS prevention).
  • UC021: View Group Chat History
    • Description: A group member can view the past messages exchanged within their group's chat.
    • Status: Implemented (Message entity, querying by group_id, includes decryption if chat messages are encrypted at rest).
  • UC022: React to a Message
    • Description: A user can add a reaction (e.g., like, heart) to a message in a group chat.
    • Status: Implemented (MessageReaction entity, unique constraint to prevent duplicate reactions).
  • UC023: View Message Reactions
    • Description: Users can see the reactions added to a message.
    • Status: Implemented (Querying MessageReaction by message_id).

5. Workout Logging (with Photo Sharing)

  • UC024: Log a Workout
    • Description: A user can record details of a workout session, potentially including a photo.
    • Status: Planned (WorkoutLog entity is assumed/designed, but the full CRUD operations for logging are not explicitly detailed. Photo upload is partially handled by ImageService).
  • UC025: Upload Workout Photo
    • Description: A user can upload a photo associated with their workout log.
    • Status: Partially Implemented (ImageService exists for handling storage, but direct integration with WorkoutLog creation and robust ownership/access control for retrieval and deletion of these specific photos needs to be fully integrated with WorkoutLog authorization).
  • UC026: View My Workout Logs
    • Description: A user can view a list of their past workout logs.
    • Status: Planned (Requires WorkoutLog retrieval and authorization).
  • UC027: View Workout Log Details (and Photo)
    • Description: A user can view the full details of a specific workout log, including any associated photo.
    • Status: Partially Implemented (Ability to fetch individual logs and photos exists, but the granular authorization for viewing only one's own logs/photos was proposed but not yet fully implemented).
  • UC028: Delete Workout Log (and Photo)
    • Description: A user can delete their workout log and any associated photo.
    • Status: Partially Implemented (Deletion functionality for logs and photos exists, but the granular authorization for deleting only one's own logs/photos was proposed but not yet fully implemented).

6. Group Fund & Payouts

  • UC029: Deposit to Group Fund
    • Description: Group members can contribute funds to their group's shared fund.
    • Status: Planned (Entities for GroupFund and FundTransaction are proposed, but the full implementation logic is not yet in place).
  • UC030: View Group Fund Balance
    • Description: Group members can view the current balance of their group's fund.
    • Status: Planned.
  • UC031: Process Payouts from Fund (Owner Only)
    • Description: The group owner can initiate payouts from the group fund, typically to reward members who achieved goals.
    • Status: Planned (Requires strict authorization and transaction logging).
  • UC032: View Fund Transaction History
    • Description: Group members can view a detailed history of all deposits and payouts from the group fund.
    • Status: Planned.

Documentation:

Clone this wiki locally