Task Management Application (TMA) Documentation

Overview

The Task Management Application is a system designed to help organizations track tasks, manage users, and monitor progress. The system includes features like task status tracking, time logging, and task history management.

Database Schema

1. task_users

Stores user information and credentials.

CREATE TABLE `task_users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `dp` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_users_email_unique` (`email`)
)

2. tasks

Main table for storing task information.

CREATE TABLE `tasks` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('Pending','Completed','In progress','Paused','Handed Over','Cancelled') DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `tasks_user_id_foreign` (`user_id`),
  CONSTRAINT `tasks_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `task_users` (`id`) ON DELETE CASCADE
)

3. task_cases

Stores subtasks or checklist items for each task.

CREATE TABLE `task_cases` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_id` int(10) UNSIGNED DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_done` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `task_cases_task_id_foreign` (`task_id`),
  CONSTRAINT `task_cases_task_id_foreign` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE
)

4. task_histories

Tracks changes in task status and maintains an audit trail.

CREATE TABLE `task_histories` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `task_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `task_histories_user_id_foreign` (`user_id`),
  KEY `task_histories_task_id_foreign` (`task_id`),
  CONSTRAINT `task_histories_task_id_foreign` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `task_histories_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `task_users` (`id`) ON DELETE CASCADE
)

5. task_times

Records time tracking information for tasks.

CREATE TABLE `task_times` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_id` int(10) UNSIGNED DEFAULT NULL,
  `started_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `ended_time` timestamp NULL DEFAULT NULL,
  `total_time_mins` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `task_time_task_id_foreign` (`task_id`),
  CONSTRAINT `task_time_task_id_foreign` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE
)

6. tma_tokens

Manages authentication tokens for users.

CREATE TABLE `tma_tokens` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_user_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `tma_tokens_task_user_id_foreign` (`task_user_id`),
  CONSTRAINT `tma_tokens_task_user_id_foreign` FOREIGN KEY (`task_user_id`) REFERENCES `task_users` (`id`) ON DELETE CASCADE
)

Database Seeder Documentation

Overview

The database seeder is implemented using a class-based approach in Node.js, utilizing mysql2/promise for database operations and @faker-js/faker for generating realistic test data.

Prerequisites

npm install mysql2 @faker-js/faker bcrypt

Configuration

const dbConfig = {
  host: "127.0.0.1",
  user: "root",
  password: "",
  database: "tma",
};

Seeding Process

1. User Seeding

   • Creates 50 users with random departments
   • Generates hashed passwords using bcrypt
   • Assigns random mobile numbers
   • Sets creation timestamps in UTC format

2. Task Seeding

   • Creates 200 tasks distributed among users
   • Assigns random status from predefined options
   • Generates realistic titles and descriptions
   • Maintains proper timestamps between Jan 1-14, 2025

3. Task Cases Seeding

   • Creates ~3 subtasks per task
   • Random completion status
   • Links to parent tasks

4. Task Histories

   • Creates initial history entry for task creation
   • Adds additional entries for status changes
   • Maintains chronological order of changes
   • Formats all dates in UTC

5. Task Times

   • Creates time entries for tasks in progress
   • Calculates total duration for completed/paused tasks
   • Ensures proper UTC timestamp formatting
   • Example format: "2025-01-13 06:40:00"

6. Token Generation

   • Creates authentication tokens for ~30% of users
   • Sets expiry dates 7 days from creation
   • Uses secure random token generation

Business Rules

1. Task Status Changes

   • Every status change creates a history entry
   • History title format: "{task_title} - Status changed to: {new_status}"

2. Time Tracking

   • Starts when task status changes to "In progress"
   • Ends when task is "Completed" or "Paused"
   • Calculates total duration in minutes
   • Maintains UTC timestamps

3. Data Relationships

   • Maintains referential integrity
   • Implements cascading deletes
   • Ensures proper foreign key relationships

Running the Seeder

node seeder.js

Example Generated Data

1. User Example

{
  name: "John Doe",
  email: "john.doe@example.com",
  department: "Development",
  mobile: "01812345678"
}

2. Task Example

{
  title: "Implement User Authentication",
  description: "Add JWT-based authentication system",
  status: "In progress",
  created_at: "2025-01-13 06:40:00"
}

3. Task Time Example

{
  started_time: "2025-01-13 06:40:00",
  ended_time: "2025-01-13 08:40:00",
  total_time_mins: 120
}

Error Handling

The seeder includes comprehensive error handling:

• Checks for dependency relationships before seeding
• Maintains database consistency
• Proper cleanup of existing data
• Transaction management for data integrity

Maintenance

To update or modify the seeder:

1. Adjust the constants for number of records
2. Modify date ranges as needed
3. Add new status types or departments
4. Update business rules in the relevant methods