Database Schema & Entities

Relevant source files

The following files were used as context for generating this wiki page:

• src/main/java/com/example/demo/entity/Project.java
• src/main/java/com/example/demo/entity/Task.java
• src/main/java/com/example/demo/entity/Subtask.java
• src/main/java/com/example/demo/entity/Comment.java
• src/main/java/com/example/demo/entity/TaskTag.java
• README.md

Introduction

The Dashboard Task Management (DTM) system utilizes a relational database schema designed to manage corporate projects, hierarchical task structures, and team collaboration. The persistence layer is built using Spring Data JPA with a PostgreSQL backend, focusing on maintaining complex relationships between users, departments, and project-related artifacts.

The schema supports granular access control based on organizational roles (ADMIN, MANAGER, PM, TEAM) and enables tracking of task lifecycles from creation to completion, including automated overdue detection and subtask synchronization.

Sources: README.md:1-5, pom.xml:34-42

Core Entity Relationships

The architecture follows a hierarchical structure where Projects belong to Departments and contain multiple Tasks. Tasks, in turn, act as the central hub for Subtasks, Comments, and Attachments.

Entity Relationship Diagram

The following diagram illustrates the primary entities and their cardinalities within the system:

Entity Definitions

Project Entity

The Project entity represents a high-level container for work. It is associated with a specific department and a Project Manager (PM).

Field	Type	Description
id	Long	Primary Key (Identity)
name	String	Project title (max 150 chars, non-nullable)
description	Text	Detailed project scope
pm	User	Reference to the assigned Project Manager
department	Department	The organizational unit owning the project
status	ProjectStatus	Enum: ACTIVE, COMPLETED, ON_HOLD

Sources: src/main/java/com/example/demo/entity/Project.java:15-52, README.md:46-52

Task Entity

The Task entity is the primary unit of work. It includes logic for automated overdue checking during lifecycle events (@PrePersist, @PreUpdate).

@PreUpdate
protected void onUpdate() {
    updatedAt = LocalDateTime.now();
    if (dueDate != null && dueDate.isBefore(LocalDate.now()) && status != TaskStatus.DONE) {
        isOverdue = true;
    }
}

Field	Type	Constraints
title	String	Non-nullable, 200 chars
status	TaskStatus	Enum: NEW, IN_PROGRESS, ON_REVIEW, DONE
priority	Priority	Enum: LOW, MEDIUM, HIGH
isOverdue	boolean	Calculated based on dueDate and status
tags	Set	Many-to-Many via task_tags_relation

Sources: src/main/java/com/example/demo/entity/Task.java:18-86, README.md:58-63

Subtask Entity

Subtasks allow for breaking down parent tasks. They contain their own status and assignee tracking.

Field	Type	Relationship
task	Task	Many-to-One (parent task, non-nullable)
assignee	User	Many-to-One (assigned team member)
status	TaskStatus	Current state of the subtask

Sources: src/main/java/com/example/demo/entity/Subtask.java:15-40

Comment and Communication

Communication is handled via the Comment entity, which supports mentions and binary attachments.

• Mentions: Represented by CommentMention (linked via Comment.mentions).
• Audit: Tracks createdAt and updatedAt timestamps automatically.

Sources: src/main/java/com/example/demo/entity/Comment.java:18-49, README.md:89-98

Data Integrity and Lifecycle

The system implements several JPA lifecycle hooks to ensure data consistency and business logic enforcement:

1. Timestamping: All major entities (Project, Task, Subtask, Comment) implement @PrePersist and @PreUpdate to maintain createdAt and updatedAt fields.
2. Cascading: The Task entity uses CascadeType.ALL and orphanRemoval = true for its assignees, subtasks, and comments, ensuring that when a task is deleted, all related collaborative data is removed.
3. Lazy Loading: To optimize performance, relationships such as Project -> Department and Task -> Project use FetchType.LAZY.

Sources: src/main/java/com/example/demo/entity/Project.java:46-56, src/main/java/com/example/demo/entity/Task.java:47-55, src/main/java/com/example/demo/entity/Subtask.java:42-50

Conclusion

The database schema and entity model provide a robust foundation for the DTM application. By leveraging JPA features like automated lifecycle management and complex relational mapping (Many-to-Many for tags, One-to-Many for task components), the system ensures high data integrity while supporting the diverse operational needs of different organizational roles.