# Backend Specification

## 1. System Summary

**System Name:** Job Management System

**Description:** A comprehensive task and workflow management application for creating, assigning, tracking, and completing jobs across teams and departments. The system provides role-based access control, time tracking, checklists, attachments, commenting, notifications, and reporting capabilities to facilitate organizational workflow management.

**Core Capabilities:**
- Organizational structure management (departments, teams, users)
- Job lifecycle management (creation, assignment, status tracking, completion)
- Time tracking and logging
- Checklist management with item-level assignments
- Comments and attachments on jobs
- Audit trail and history tracking
- Notifications for job events
- Dashboard and analytics
- Report generation

## 2. Source Input Summary

**Primary Sources:**
- **DDL Schema File:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_112629/schema.sql`
  - Contains 18 tables defining the complete data model
  - Includes departments, teams, users, jobs, comments, attachments, time_logs, checklists, notifications, reports, and supporting entities
  
- **PRD File:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_112629/full_prd.md`
  - Defines user roles, workflows, features, and business rules
  - Specifies functional requirements and user stories
  - Details permissions and access control requirements

**Analysis Approach:** The backend specification is derived from a comprehensive analysis of the DDL schema structure and PRD requirements, identifying entities, relationships, API endpoints, workflows, and business logic necessary to support the defined features.

## 3. Generation Mode

**Mode:** `strict`

**Implications:**
- All entities defined in the DDL schema are included in the backend
- All relationships are implemented with proper foreign key constraints
- All business rules from the PRD are enforced in the backend logic
- Complete CRUD operations are provided for all primary entities
- Comprehensive validation is applied at all input points
- Audit trails and history tracking are mandatory for job operations
- Role-based access control is strictly enforced across all endpoints

## 4. Backend Scope

### Modules

The backend is organized into 7 functional modules:

| Module | Entities | Description |
|--------|----------|-------------|
| **organization** | Department, Team, Userteam | Organizational structure including departments and teams |
| **user_management** | User, Notification | User accounts, authentication, profiles, and notifications |
| **job_configuration** | Jobtype, Jobstatus, Jobpriority, Tag | Configuration entities for job types, statuses, priorities, and tags |
| **job_management** | Job, Jobtag, Comment, Attachment, Jobhistory | Core job entities including jobs, comments, attachments, tags, and history |
| **time_tracking** | Timelog | Time tracking and logging for jobs |
| **checklist** | Checklist, Checklistitem | Checklists and checklist items for jobs |
| **reporting** | Report | Report generation and analytics |

### Key Features

- **Organization Management:** Departments and teams with hierarchical structure
- **User Management:** User accounts with role-based access, department/team assignments
- **Job Management:** Full job lifecycle from creation through completion
- **Time Tracking:** Start/stop timers, manual time entry, duration calculation
- **Checklists:** Multi-level checklists with item-level completion tracking
- **Comments & Attachments:** Collaboration through comments and file attachments
- **History & Audit:** Complete audit trail of all job changes
- **Notifications:** In-app notifications for job events and mentions
- **Dashboard:** Role-appropriate views with job counts, overdue alerts, activity feeds
- **Reporting:** Configurable reports with multiple export formats

## 5. Roles

The system supports four distinct user roles with hierarchical permissions:

| Role | Value | Description | Key Permissions |
|------|-------|-------------|-----------------|
| **Administrator** | `administrator` | System-wide access and configuration | Full access to all entities, unrestricted job viewing/editing, system configuration, user management |
| **Manager** | `manager` | Department/team oversight and approval | View/edit all jobs in department, assign jobs, approve completions, manage team members, generate reports |
| **Team Member** | `team_member` | Individual contributor | View/edit assigned jobs, create jobs, log time, add comments/attachments, complete checklists |
| **Viewer** | `viewer` | Read-only access | View jobs within department, view reports, no edit permissions |

**Role Hierarchy:**
- Administrator > Manager > Team Member > Viewer
- Higher roles inherit permissions of lower roles
- Department and team membership further restrict access for non-admin roles

## 6. Entities and Fields

### 6.1 Organization Module

#### Department
**Table:** `departments`  
**Description:** Organizational unit grouping users and jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| name | str | Yes | Department name (unique) |
| description | Optional[str] | No | Department description |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** name (unique)

---

#### Team
**Table:** `teams`  
**Description:** Sub-organizational unit within departments

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| department_id | str (UUID) | Yes | Foreign key to departments |
| name | str | Yes | Team name |
| description | Optional[str] | No | Team description |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with Department

---

#### Userteam
**Table:** `user_teams`  
**Description:** Many-to-many relationship between users and teams

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| user_id | str (UUID) | Yes | Foreign key to users |
| team_id | str (UUID) | Yes | Foreign key to teams |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** (user_id, team_id) unique composite

---

### 6.2 User Management Module

#### User
**Table:** `users`  
**Description:** Individuals who interact with the system

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| email | str | Yes | User email address (unique) |
| password_hash | str | Yes | Hashed password |
| first_name | str | Yes | User first name |
| last_name | str | Yes | User last name |
| role | str | Yes | User role (administrator, manager, team_member, viewer) |
| department_id | Optional[str] (UUID) | No | Foreign key to departments |
| phone | Optional[str] | No | User phone number |
| is_active | bool | Yes | Whether user account is active |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** email (unique)  
**Status Field:** is_active

---

#### Notification
**Table:** `notifications`  
**Description:** System-generated alerts for users

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| user_id | str (UUID) | Yes | Foreign key to users |
| type | str | Yes | Notification type |
| title | str | Yes | Notification title |
| message | str | Yes | Notification message |
| related_entity_type | Optional[str] | No | Type of related entity |
| related_entity_id | Optional[str] (UUID) | No | ID of related entity |
| is_read | bool | Yes | Whether notification is read |
| read_at | Optional[datetime] | No | Read timestamp |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with User  
**Status Field:** is_read

---

### 6.3 Job Configuration Module

#### Jobtype
**Table:** `job_types`  
**Description:** Classification template for jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| name | str | Yes | Job type name (unique) |
| description | Optional[str] | No | Job type description |
| is_active | bool | Yes | Whether job type is active |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** name (unique)  
**Status Field:** is_active

---

#### Jobstatus
**Table:** `job_statuses`  
**Description:** Current state of a job in its lifecycle

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| name | str | Yes | Status name (unique) |
| description | Optional[str] | No | Status description |
| display_order | int | Yes | Display order for status |
| is_active | bool | Yes | Whether status is active |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** name (unique)  
**Status Field:** is_active

---

#### Jobpriority
**Table:** `job_priorities`  
**Description:** Urgency level for jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| name | str | Yes | Priority name (unique) |
| level | int | Yes | Priority level (numeric) |
| color | Optional[str] | No | Color code for priority |
| is_active | bool | Yes | Whether priority is active |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** name (unique)  
**Status Field:** is_active

---

#### Tag
**Table:** `tags`  
**Description:** Labels for categorizing and filtering jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| name | str | Yes | Tag name (unique) |
| color | Optional[str] | No | Color code for tag |
| usage_count | int | Yes | Number of times tag is used |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** name (unique)

---

### 6.4 Job Management Module

#### Job
**Table:** `jobs`  
**Description:** Central entity representing a unit of work

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| title | str | Yes | Job title |
| description | Optional[str] | No | Job description |
| job_type_id | str (UUID) | Yes | Foreign key to job_types |
| job_status_id | str (UUID) | Yes | Foreign key to job_statuses |
| job_priority_id | str (UUID) | Yes | Foreign key to job_priorities |
| created_by_user_id | str (UUID) | Yes | Foreign key to users (creator) |
| assigned_to_user_id | Optional[str] (UUID) | No | Foreign key to users (assignee) |
| department_id | str (UUID) | Yes | Foreign key to departments |
| team_id | Optional[str] (UUID) | No | Foreign key to teams |
| parent_job_id | Optional[str] (UUID) | No | Foreign key to jobs (parent job) |
| due_date | Optional[datetime] | No | Job due date |
| started_at | Optional[datetime] | No | Job start timestamp |
| completed_at | Optional[datetime] | No | Job completion timestamp |
| estimated_hours | Optional[float] | No | Estimated hours to complete |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Multiple foreign keys as detailed  
**Status Field:** job_status_id

---

#### Jobtag
**Table:** `job_tags`  
**Description:** Many-to-many relationship between jobs and tags

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| job_id | str (UUID) | Yes | Foreign key to jobs |
| tag_id | str (UUID) | Yes | Foreign key to tags |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Indexes:** (job_id, tag_id) unique composite

---

#### Comment
**Table:** `comments`  
**Description:** User-generated notes and updates attached to jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| job_id | str (UUID) | Yes | Foreign key to jobs |
| user_id | str (UUID) | Yes | Foreign key to users |
| content | str | Yes | Comment content |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with Job and User

---

#### Attachment
**Table:** `attachments`  
**Description:** Files and documents associated with jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| job_id | str (UUID) | Yes | Foreign key to jobs |
| uploaded_by_user_id | str (UUID) | Yes | Foreign key to users |
| file_name | str | Yes | Original file name |
| file_path | str | Yes | Storage path for file |
| file_type | Optional[str] | No | MIME type of file |
| file_size | int | Yes | File size in bytes |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with Job and User

---

#### Jobhistory
**Table:** `job_history`  
**Description:** Audit trail capturing all changes and state transitions for jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| job_id | str (UUID) | Yes | Foreign key to jobs |
| user_id | str (UUID) | Yes | Foreign key to users |
| action_type | str | Yes | Type of action performed |
| field_name | Optional[str] | No | Field that was changed |
| old_value | Optional[str] | No | Previous value |
| new_value | Optional[str] | No | New value |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with Job and User

---

### 6.5 Time Tracking Module

#### Timelog
**Table:** `time_logs`  
**Description:** Records time spent by users working on jobs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| job_id | str (UUID) | Yes | Foreign key to jobs |
| user_id | str (UUID) | Yes | Foreign key to users |
| start_time | datetime | Yes | Time tracking start |
| end_time | Optional[datetime] | No | Time tracking end |
| duration_minutes | Optional[int] | No | Duration in minutes |
| notes | Optional[str] | No | Optional notes |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with Job and User

---

### 6.6 Checklist Module

#### Checklist
**Table:** `checklists`  
**Description:** Template or instance of subtasks within a job

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| job_id | str (UUID) | Yes | Foreign key to jobs |
| title | str | Yes | Checklist title |
| description | Optional[str] | No | Checklist description |
| display_order | int | Yes | Display order |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with Job

---

#### Checklistitem
**Table:** `checklist_items`  
**Description:** Individual actionable item within a checklist

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| checklist_id | str (UUID) | Yes | Foreign key to checklists |
| description | str | Yes | Item description |
| is_completed | bool | Yes | Whether item is completed |
| assigned_to_user_id | Optional[str] (UUID) | No | Foreign key to users |
| display_order | int | Yes | Display order |
| completed_at | Optional[datetime] | No | Completion timestamp |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with Checklist and User  
**Status Field:** is_completed

---

### 6.7 Reporting Module

#### Report
**Table:** `reports`  
**Description:** Scheduled or ad-hoc analytics output

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str (UUID) | Yes | Unique identifier |
| name | str | Yes | Report name |
| description | Optional[str] | No | Report description |
| report_type | str | Yes | Type of report |
| parameters | Optional[dict] | No | Report parameters (JSONB) |
| generated_by_user_id | str (UUID) | Yes | Foreign key to users |
| file_path | Optional[str] | No | Path to generated report file |
| export_format | Optional[str] | No | Export format (PDF, Excel, CSV) |
| generated_at | Optional[datetime] | No | Generation timestamp |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Relationships:** Many-to-one with User

---

## 7. Enumerations

### UserRole
**Description:** Defines the role-based access levels for users

| Value | Label | Description |
|-------|-------|-------------|
| administrator | ADMINISTRATOR | System-wide access and configuration capabilities |
| manager | MANAGER | Department/team oversight and approval authority |
| team_member | TEAM_MEMBER | Individual contributor with assigned job access |
| viewer | VIEWER | Read-only access to departmental data |

**Usage:** User.role field, used throughout the system for permission checks

---

## 8. Relationships

### Primary Relationships

| Source Entity | Relationship Type | Target Entity | Navigation | Description |
|---------------|-------------------|---------------|------------|-------------|
| Team | Many-to-One | Department | Yes | Each team belongs to one department |
| User | Many-to-One | Department | Yes | Each user belongs to one department |
| User | Many-to-Many | Team | Yes | Users can belong to multiple teams via Userteam |
| Job | Many-to-One | Jobtype | Yes | Each job has one job type |
| Job | Many-to-One | Jobstatus | Yes | Each job has one status |
| Job | Many-to-One | Jobpriority | Yes | Each job has one priority |
| Job | Many-to-One | User (creator) | Yes | Each job created by one user |
| Job | Many-to-One | User (assignee) | Yes | Each job assigned to one user (optional) |
| Job | Many-to-One | Department | Yes | Each job belongs to one department |
| Job | Many-to-One | Team | Yes | Each job belongs to one team (optional) |
| Job | Self-Referencing | Job (parent) | Yes | Jobs can have parent jobs (sub-jobs) |
| Job | One-to-Many | Comment | Yes | Jobs can have multiple comments |
| Job | One-to-Many | Attachment | Yes | Jobs can have multiple attachments |
| Job | One-to-Many | Jobhistory | Yes | Jobs have history entries for all changes |
| Job | One-to-Many | Timelog | Yes | Jobs have multiple time log entries |
| Job | Many-to-Many | Tag | Yes | Jobs can have multiple tags via Jobtag |
| Job | One-to-Many | Checklist | Yes | Jobs can have multiple checklists |
| Comment | Many-to-One | Job | No | Each comment belongs to one job |
| Comment | Many-to-One | User | Yes | Each comment created by one user |
| Attachment | Many-to-One | Job | No | Each attachment belongs to one job |
| Attachment | Many-to-One | User | Yes | Each attachment uploaded by one user |
| Timelog | Many-to-One | Job | No | Each time log belongs to one job |
| Timelog | Many-to-One | User | Yes | Each time log logged by one user |
| Jobhistory | Many-to-One | Job | No | Each history entry belongs to one job |
| Jobhistory | Many-to-One | User | Yes | Each history entry performed by one user |
| Checklist | Many-to-One | Job | No | Each checklist belongs to one job |
| Checklist | One-to-Many | Checklistitem | Yes | Checklists have multiple items |
| Checklistitem | Many-to-One | Checklist | No | Each item belongs to one checklist |
| Checklistitem | Many-to-One | User | Yes | Each item assigned to one user (optional) |
| Notification | Many-to-One | User | No | Each notification sent to one user |
| Report | Many-to-One | User | Yes | Each report generated by one user |

### Eager Loading Paths

For performance optimization, the following eager loading paths are defined for detail endpoints:

**Job Details:**
- Job → Jobtype
- Job → Jobstatus
- Job → Jobpriority
- Job → User (creator)
- Job → User (assignee)
- Job → Department
- Job → Team
- Job → Job (parent)
- Job → Comment → User
- Job → Attachment → User
- Job → Jobhistory → User
- Job → Timelog → User
- Job → Jobtag → Tag
- Job → Checklist → Checklistitem → User

**User Details:**
- User → Department
- User → Userteam → Team → Department

**Team Details:**
- Team → Department
- Team → Userteam → User

---

## 9. API Endpoints

### 9.1 Organization Management

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/departments` | List all departments | Yes | All |
| GET | `/api/departments/{id}` | Get department by ID | Yes | All |
| POST | `/api/departments` | Create new department | Yes | Administrator |
| PUT | `/api/departments/{id}` | Update department | Yes | Administrator |
| DELETE | `/api/departments/{id}` | Delete department | Yes | Administrator |
| GET | `/api/departments/{id}/jobs` | Get jobs for department | Yes | All |
| GET | `/api/teams` | List all teams | Yes | All |
| GET | `/api/teams/{id}` | Get team by ID | Yes | All |
| GET | `/api/teams/{id}/details` | Get team details with members | Yes | All |
| POST | `/api/teams` | Create new team | Yes | Administrator, Manager |
| PUT | `/api/teams/{id}` | Update team | Yes | Administrator, Manager |
| DELETE | `/api/teams/{id}` | Delete team | Yes | Administrator |
| GET | `/api/teams/{id}/jobs` | Get jobs for team | Yes | All |

### 9.2 User Management

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/users` | List all users | Yes | Administrator, Manager |
| GET | `/api/users/{id}` | Get user by ID | Yes | All |
| GET | `/api/users/{id}/details` | Get user details with department and teams | Yes | All |
| POST | `/api/users` | Create new user | Yes | Administrator |
| PUT | `/api/users/{id}` | Update user | Yes | Administrator, Self |
| DELETE | `/api/users/{id}` | Delete user (deactivate) | Yes | Administrator |
| GET | `/api/users/{id}/jobs` | Get jobs assigned to user | Yes | All |
| POST | `/api/auth/login` | User login | No | - |
| POST | `/api/auth/logout` | User logout | Yes | All |
| POST | `/api/auth/refresh` | Refresh authentication token | Yes | All |

### 9.3 Job Configuration

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/job-types` | List all job types | Yes | All |
| GET | `/api/job-types/{id}` | Get job type by ID | Yes | All |
| POST | `/api/job-types` | Create new job type | Yes | Administrator |
| PUT | `/api/job-types/{id}` | Update job type | Yes | Administrator |
| DELETE | `/api/job-types/{id}` | Delete job type | Yes | Administrator |
| GET | `/api/job-statuses` | List all job statuses | Yes | All |
| GET | `/api/job-statuses/{id}` | Get job status by ID | Yes | All |
| POST | `/api/job-statuses` | Create new job status | Yes | Administrator |
| PUT | `/api/job-statuses/{id}` | Update job status | Yes | Administrator |
| DELETE | `/api/job-statuses/{id}` | Delete job status | Yes | Administrator |
| GET | `/api/job-priorities` | List all job priorities | Yes | All |
| GET | `/api/job-priorities/{id}` | Get job priority by ID | Yes | All |
| POST | `/api/job-priorities` | Create new job priority | Yes | Administrator |
| PUT | `/api/job-priorities/{id}` | Update job priority | Yes | Administrator |
| DELETE | `/api/job-priorities/{id}` | Delete job priority | Yes | Administrator |
| GET | `/api/tags` | List all tags | Yes | All |
| GET | `/api/tags/{id}` | Get tag by ID | Yes | All |
| POST | `/api/tags` | Create new tag | Yes | All |
| PUT | `/api/tags/{id}` | Update tag | Yes | All |
| DELETE | `/api/tags/{id}` | Delete tag | Yes | Administrator |

### 9.4 Job Management

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/jobs` | List jobs with filters | Yes | All |
| GET | `/api/jobs/{id}` | Get job by ID | Yes | All |
| GET | `/api/jobs/{id}/details` | Get job details with related entities | Yes | All |
| POST | `/api/jobs` | Create new job | Yes | Administrator, Manager, Team Member |
| PUT | `/api/jobs/{id}` | Update job | Yes | Based on role and assignment |
| DELETE | `/api/jobs/{id}` | Delete job (soft delete) | Yes | Administrator, Manager |
| POST | `/api/jobs/{id}/assign` | Assign job to user | Yes | Administrator, Manager |
| POST | `/api/jobs/{id}/reassign` | Reassign job to different user | Yes | Administrator, Manager |
| POST | `/api/jobs/{id}/status` | Update job status | Yes | Based on role and assignment |
| POST | `/api/jobs/{id}/complete` | Mark job as completed | Yes | Administrator, Manager |
| POST | `/api/jobs/{id}/cancel` | Cancel job | Yes | Administrator, Manager |
| POST | `/api/jobs/{id}/reopen` | Reopen completed job | Yes | Administrator, Manager |
| GET | `/api/jobs/{id}/history` | Get job audit history | Yes | All |

### 9.5 Comments & Attachments

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/comments` | List comments (filtered by job) | Yes | All |
| GET | `/api/comments/{id}` | Get comment by ID | Yes | All |
| POST | `/api/jobs/{id}/comments` | Add comment to job | Yes | All |
| PUT | `/api/comments/{id}` | Update comment | Yes | Comment author |
| DELETE | `/api/comments/{id}` | Delete comment | Yes | Administrator, Comment author |
| GET | `/api/attachments` | List attachments (filtered by job) | Yes | All |
| GET | `/api/attachments/{id}` | Get attachment by ID | Yes | All |
| POST | `/api/jobs/{id}/attachments` | Upload attachment to job | Yes | All |
| DELETE | `/api/attachments/{id}` | Delete attachment | Yes | Administrator, Uploader |
| GET | `/api/attachments/{id}/download` | Download attachment file | Yes | All |

### 9.6 Time Tracking

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/time-logs` | List time logs (filtered by job/user) | Yes | All |
| GET | `/api/time-logs/{id}` | Get time log by ID | Yes | All |
| POST | `/api/jobs/{id}/time-logs` | Create time log for job | Yes | All |
| PUT | `/api/time-logs/{id}` | Update time log | Yes | Time log owner |
| DELETE | `/api/time-logs/{id}` | Delete time log | Yes | Administrator, Time log owner |
| POST | `/api/time-logs/{id}/start` | Start time tracking | Yes | Time log owner |
| POST | `/api/time-logs/{id}/stop` | Stop time tracking | Yes | Time log owner |

### 9.7 Checklists

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/checklists` | List checklists (filtered by job) | Yes | All |
| GET | `/api/checklists/{id}` | Get checklist by ID | Yes | All |
| POST | `/api/checklists` | Create new checklist | Yes | All |
| PUT | `/api/checklists/{id}` | Update checklist | Yes | All |
| DELETE | `/api/checklists/{id}` | Delete checklist | Yes | Administrator, Manager |
| GET | `/api/checklist-items` | List checklist items | Yes | All |
| GET | `/api/checklist-items/{id}` | Get checklist item by ID | Yes | All |
| POST | `/api/checklist-items` | Create new checklist item | Yes | All |
| PUT | `/api/checklist-items/{id}` | Update checklist item | Yes | All |
| DELETE | `/api/checklist-items/{id}` | Delete checklist item | Yes | Administrator, Manager |
| POST | `/api/checklist-items/{id}/complete` | Mark checklist item as complete | Yes | Assignee, Manager, Administrator |

### 9.8 Notifications

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/notifications` | List user's notifications | Yes | All |
| GET | `/api/notifications/{id}` | Get notification by ID | Yes | All |
| POST | `/api/notifications/{id}/read` | Mark notification as read | Yes | Notification recipient |
| POST | `/api/notifications/mark-all-read` | Mark all notifications as read | Yes | All |
| DELETE | `/api/notifications/{id}` | Delete notification | Yes | Notification recipient |

### 9.9 Dashboard & Analytics

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/dashboard` | Get dashboard data | Yes | All |
| GET | `/api/analytics/job-counts` | Get job counts by status | Yes | All |
| GET | `/api/analytics/overdue-jobs` | Get overdue jobs | Yes | All |
| GET | `/api/analytics/team-performance` | Get team performance metrics | Yes | Manager, Administrator |
| GET | `/api/analytics/user-workload` | Get user workload statistics | Yes | Manager, Administrator |

### 9.10 Reporting

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/reports` | List reports | Yes | All |
| GET | `/api/reports/{id}` | Get report by ID | Yes | All |
| POST | `/api/reports/generate` | Generate report with parameters | Yes | Manager, Administrator |
| DELETE | `/api/reports/{id}` | Delete report | Yes | Administrator, Report creator |
| GET | `/api/reports/{id}/download` | Download generated report file | Yes | All |

### Query Parameters

**List Endpoints Support:**
- `page`: Page number (default: 1)
- `page_size`: Items per page (default: 25, max: 100)
- `sort_by`: Field to sort by
- `sort_order`: `asc` or `desc`

**Job List Specific Filters:**
- `status`: Filter by job status ID
- `priority`: Filter by job priority ID
- `type`: Filter by job type ID
- `assignee`: Filter by assigned user ID
- `creator`: Filter by creator user ID
- `department`: Filter by department ID
- `team`: Filter by team ID
- `tags`: Filter by tag IDs (comma-separated)
- `due_date_start`: Filter by due date range start
- `due_date_end`: Filter by due date range end
- `search`: Full-text search on title and description

---

## 10. Workflow Logic

### 10.1 Job Creation Workflow

**Trigger:** Manager or Team Member creates a new job  
**Process:**
1. User submits job creation request with title, description, type, priority, due date, assignee, department, team
2. System validates:
   - Title is 5-200 characters
   - Description is at least 10 characters (if provided)
   - Due date is in the future
   - Assignee belongs to specified department/team (unless Admin)
   - User has permission to create jobs
3. System creates Job record with status "New"
4. System automatically sets:
   - `created_by_user_id` to current user
   - `created_at` to current timestamp
   - `job_status_id` to "New" or "Assigned" (if assignee provided)
5. System creates JobHistory entry recording creation
6. If assignee is specified, system creates Notification for assignee
7. System returns created job with all related data

**Business Rules Applied:**
- Team Members cannot assign jobs to themselves
- Assignee must be active and in same department (unless Admin)
- Default status is "New" unless assignee is provided

---

### 10.2 Job Assignment Workflow

**Trigger:** Manager assigns job to team member  
**Process:**
1. Manager submits assignment request with job ID and assignee user ID
2. System validates:
   - Job exists and is not completed or cancelled
   - Assignee user exists and is active
   - Assignee belongs to job's department/team (unless Admin)
   - Manager has permission to assign (same department or Admin)
3. System updates Job record:
   - Sets `assigned_to_user_id` to new assignee
   - Updates `job_status_id` to "Assigned"
   - Updates `updated_at` to current timestamp
4. System creates JobHistory entry recording assignment
5. System creates Notification for assignee
6. System returns updated job

**Business Rules Applied:**
- Only Managers and Administrators can assign jobs
- Assignee must be active
- Deactivated users cannot be assigned new jobs

---

### 10.3 Job Reassignment Workflow

**Trigger:** Manager reassigns job to different user  
**Process:**
1. Manager submits reassignment request with job ID and new assignee ID
2. System validates:
   - Job exists and is not completed or cancelled
   - New assignee exists and is active
   - New assignee belongs to job's department/team (unless Admin)
   - Manager has permission (same department or Admin)
3. System records previous assignee ID
4. System updates Job record:
   - Sets `assigned_to_user_id` to new assignee
   - Updates `updated_at` to current timestamp
5. System creates JobHistory entry recording reassignment with old and new assignee
6. System creates Notification for previous assignee (if exists)
7. System creates Notification for new assignee
8. System returns updated job

**Business Rules Applied:**
- Previous assignee is notified of reassignment
- Active time logs are not automatically stopped
- Job status remains unchanged

---

### 10.4 Job Status Update Workflow

**Trigger:** User updates job status  
**Process:**
1. User submits status update request with job ID and new status ID
2. System validates:
   - Job exists
   - New status exists and is active
   - Status transition is valid per workflow rules
   - User has permission to update status
3. System validates business rules for specific status transitions:
   - If transitioning to "Pending Review": All checklist items must be completed
   - If transitioning to "Completed": Manager/Admin approval required
   - If transitioning to "Cancelled": Reason must be provided
4. System updates Job record:
   - Sets `job_status_id` to new status
   - If status is "Completed", sets `completed_at` to current timestamp
   - If status is "In Progress" and no `started_at`, sets `started_at` to current timestamp
   - Updates `updated_at` to current timestamp
5. System creates JobHistory entry recording status change
6. System creates Notifications based on status:
   - "Assigned": Notify assignee
   - "Pending Review": Notify manager
   - "Completed": Notify creator and assignee
   - "Cancelled": Notify assignee
7. System returns updated job

**Valid Status Transitions:**
- New → Assigned, In Progress, Cancelled
- Assigned → In Progress, Cancelled
- In Progress → Pending Review, Cancelled
- Pending Review → Completed, In Progress (if issues found)
- Completed → (no transitions except reopen)
- Cancelled → (no transitions)

**Business Rules Applied:**
- Checklist validation for "Pending Review" status
- Manager approval required for "Completed" status
- Active time logs stopped when job is completed or cancelled

---

### 10.5 Job Completion Workflow

**Trigger:** Team member marks job as ready for review, Manager approves completion  
**Process:**
1. Team Member updates job status to "Pending Review"
2. System validates all checklist items are completed
3. System creates Notification for Manager
4. Manager reviews job
5. Manager submits completion approval
6. System validates:
   - Job is in "Pending Review" status
   - All checklist items are completed
   - Manager has approval permission
7. System updates Job record:
   - Sets `job_status_id` to "Completed"
   - Sets `completed_at` to current timestamp
   - Updates `updated_at` to current timestamp
8. System stops any active time logs for the job
9. System creates JobHistory entry recording completion
10. System creates Notifications for creator and assignee
11. System returns completed job

**Business Rules Applied:**
- Jobs cannot be marked as Completed without Manager or Admin approval
- All checklist items must be completed
- Active time logs are automatically stopped

---

### 10.6 Job Cancellation Workflow

**Trigger:** Authorized user cancels job  
**Process:**
1. Manager or Administrator submits cancellation request with job ID and reason
2. System validates:
   - Job exists and is not already cancelled or completed
   - User has permission to cancel (Manager or Admin)
   - Cancellation reason is provided
3. System updates Job record:
   - Sets `job_status_id` to "Cancelled"
   - Updates `updated_at` to current timestamp
4. System stops any active time logs for the job
5. System creates JobHistory entry recording cancellation with reason
6. System creates Notification for assignee (if exists)
7. System creates Notification for creator
8. System returns cancelled job

**Business Rules Applied:**
- Cancelled jobs cannot be reopened (must create new job)
- Active time logs are automatically stopped
- Cancellation reason is required

---

### 10.7 Job Reopen Workflow

**Trigger:** Manager or Administrator reopens completed job  
**Process:**
1. Manager or Administrator submits reopen request with job ID
2. System validates:
   - Job exists and is in "Completed" status
   - User has permission to reopen (Manager or Admin)
3. System updates Job record:
   - Sets `job_status_id` to "In Progress"
   - Clears `completed_at` field
   - Updates `updated_at` to current timestamp
4. System creates JobHistory entry recording reopen
5. System creates Notification for assignee
6. System returns reopened job

**Business Rules Applied:**
- Completed jobs can be reopened only by Managers or Admins
- Cancelled jobs cannot be reopened
- Job returns to "In Progress" status

---

### 10.8 Time Tracking Workflow

**Start Time Tracking:**
1. User clicks "Start Timer" on job
2. System validates:
   - Job exists and is assigned to user
   - User has no other active timers
3. System creates TimeLog record:
   - Sets `start_time` to current timestamp
   - Leaves `end_time` and `duration_minutes` null
4. System returns active time log

**Stop Time Tracking:**
1. User clicks "Stop Timer" on active time log
2. System validates:
   - Time log exists and belongs to user
   - Time log is active (no `end_time`)
3. System calculates duration: `current_timestamp - start_time`
4. System updates TimeLog record:
   - Sets `end_time` to current timestamp
   - Sets `duration_minutes` to calculated duration
   - Updates `updated_at` to current timestamp
5. System returns completed time log

**Manual Time Entry:**
1. User submits manual time log with start_time, end_time, notes
2. System validates:
   - `end_time` is after `start_time`
   - Duration is positive and less than 24 hours
   - No overlapping time logs for user
3. System calculates `duration_minutes`
4. System creates TimeLog record
5. System returns created time log

**Business Rules Applied:**
- Only one active timer per user across all jobs
- Users cannot have overlapping time logs
- Active timers are stopped when job is completed or cancelled

---

### 10.9 Checklist Management Workflow

**Create Checklist:**
1. User creates checklist for job with title and description
2. System validates:
   - Job exists
   - User has access to job
3. System creates Checklist record
4. System returns created checklist

**Add Checklist Items:**
1. User adds items to checklist with descriptions and optional assignees
2. System validates:
   - Checklist exists
   - Assignee (if provided) is active and has job access
3. System creates ChecklistItem records
4. System returns created items

**Complete Checklist Item:**
1. User marks checklist item as complete
2. System validates:
   - Item exists
   - User is item assignee or has Manager/Admin role
3. System updates ChecklistItem record:
   - Sets `is_completed` to true
   - Sets `completed_at` to current timestamp
   - Updates `updated_at` to current timestamp
4. System returns updated item

**Business Rules Applied:**
- Jobs cannot transition to "Pending Review" if any checklist items are incomplete
- Checklist items can be assigned to specific users
- Only assignee, manager, or admin can complete items

---

### 10.10 Comment & Mention Workflow

**Add Comment:**
1. User submits comment on job
2. System validates:
   - Job exists
   - User has access to job
   - Comment content is 1-5000 characters
3. System parses comment for @mentions
4. System creates Comment record
5. For each mentioned user:
   - System validates user exists and has job access
   - System creates Notification for mentioned user
6. System returns created comment

**Business Rules Applied:**
- Users mentioned in comments receive notifications
- Comment author can edit their own comments
- Admins can delete any comments

---

### 10.11 Attachment Upload Workflow

**Upload Attachment:**
1. User uploads file to job
2. System validates:
   - Job exists
   - User has access to job
   - File size is less than 10MB
   - Total job attachments are less than 50MB
   - File type is allowed
3. System stores file to configured storage (local or cloud)
4. System creates Attachment record with metadata:
   - `file_name`, `file_path`, `file_type`, `file_size`
   - `uploaded_by_user_id` set to current user
5. System returns created attachment

**Download Attachment:**
1. User requests attachment download
2. System validates:
   - Attachment exists
   - User has access to job
3. System retrieves file from storage
4. System returns file with appropriate headers

**Business Rules Applied:**
- File upload size limited to 10MB per file
- Total attachments per job limited to 50MB
- Only uploader or admin can delete attachments

---

### 10.12 Notification Delivery Workflow

**System Generates Notification:**
1. Event occurs (assignment, status change, mention, due date alert)
2. System determines affected users
3. For each affected user:
   - System creates Notification record with type, title, message
   - System sets `related_entity_type` and `related_entity_id` for navigation
   - Optionally sends email notification based on user preferences
4. User views notifications in application
5. User marks notifications as read
6. System updates Notification record:
   - Sets `is_read` to true
   - Sets `read_at` to current timestamp

**Notification Types:**
- `job_assigned`: Job assigned to user
- `job_reassigned`: Job reassigned from/to user
- `job_status_changed`: Job status updated
- `comment_mention`: User mentioned in comment
- `job_due_soon`: Job due within 24 hours
- `job_overdue`: Job past due date
- `job_completed`: Job marked as completed
- `job_cancelled`: Job cancelled

**Business Rules Applied:**
- Users receive notification when job is assigned to them
- Users receive notification when mentioned in comments
- Assignee receives notification when job due date is within 24 hours
- Manager receives notification when team member marks job as Pending Review

---

### 10.13 Dashboard Display Workflow

**User Opens Dashboard:**
1. User navigates to dashboard
2. System identifies user's role and department
3. System queries and aggregates data based on role:
   - **All Roles:** Job counts by status, jobs assigned to user, recent activity
   - **Team Member:** Personal job list, overdue jobs, jobs due soon
   - **Manager:** Department/team job statistics, team member workload, pending approvals
   - **Administrator:** System-wide statistics, all departments/teams overview
4. System applies department/team filters for non-admin users
5. System returns dashboard data

**Dashboard Components:**
- Job counts by status (with role-appropriate filters)
- Overdue jobs count and list
- Jobs due within 7 days
- Recent activity feed (job updates, comments, assignments)
- Assigned jobs (priority and due date sorted)
- Team performance metrics (Manager/Admin only)
- Pending approvals (Manager/Admin only)

**Business Rules Applied:**
- Users can only view jobs within their department unless they are Admins
- Dashboard data is filtered by role and department
- Real-time updates for notifications and activity feed

---

### 10.14 Report Generation Workflow

**Generate Report:**
1. Manager or Administrator selects report type
2. User configures parameters:
   - Date range
   - Departments/teams filter
   - Job statuses, priorities, types
   - Export format (PDF, Excel, CSV)
3. System validates:
   - User has permission to generate reports
   - Parameters are valid
4. System queries data based on parameters
5. System calculates metrics and aggregations
6. System generates report in selected format
7. System stores report file to configured storage
8. System creates Report record with metadata
9. System returns report record with download link

**Report Types:**
- Job summary by status
- Team performance (completion rates, average time)
- User workload analysis
- Overdue jobs report
- Time tracking summary
- Department productivity
- Custom ad-hoc reports

**Business Rules Applied:**
- Reports are generated synchronously for small datasets
- Large reports may be queued for asynchronous processing
- Report access follows same permission rules as underlying data

---

## 11. Business Logic

### 11.1 Access Control Rules

**Department-Based Access:**
- Users can only view jobs within their department unless they are Administrators
- Non-admin users cannot view, create, or edit jobs in other departments
- Department membership is required for all non-admin users

**Team-Based Access:**
- Users can view all jobs within their teams
- Team membership is defined via `user_teams` table
- Users can belong to multiple teams

**Role-Based Permissions:**

| Action | Administrator | Manager | Team Member | Viewer |
|--------|---------------|---------|-------------|--------|
| View all jobs (system-wide) | ✓ | ✗ | ✗ | ✗ |
| View department jobs | ✓ | ✓ | ✓ | ✓ |
| View assigned jobs | ✓ | ✓ | ✓ | ✗ |
| Create jobs | ✓ | ✓ | ✓ | ✗ |
| Edit any job in department | ✓ | ✓ | ✗ | ✗ |
| Edit assigned jobs | ✓ | ✓ | ✓ | ✗ |
| Delete jobs | ✓ | ✓ | ✗ | ✗ |
| Assign jobs | ✓ | ✓ | ✗ | ✗ |
| Approve job completion | ✓ | ✓ | ✗ | ✗ |
| Cancel jobs | ✓ | ✓ | ✗ | ✗ |
| Reopen completed jobs | ✓ | ✓ | ✗ | ✗ |
| Manage users | ✓ | ✗ | ✗ | ✗ |
| Manage departments/teams | ✓ | ✗ | ✗ | ✗ |
| Generate reports | ✓ | ✓ | ✗ | ✗ |

---

### 11.2 Job Assignment Rules

**Assignment Validation:**
- Assigned user must belong to the same department as the job (unless Admin)
- Assigned user must be active (`is_active = true`)
- Deactivated users cannot be assigned new jobs
- Users cannot assign jobs to themselves (must be assigned by Manager/Admin)

**Assignment Process:**
- When job is assigned, status automatically changes to "Assigned"
- Assignee receives notification
- JobHistory entry is created recording assignment
- Previous assignee is notified when job is reassigned

---

### 11.3 Job Status Transition Rules

**Valid Transitions:**
```
New → Assigned, In Progress, Cancelled
Assigned → In Progress, Cancelled
In Progress → Pending Review, Cancelled
Pending Review → Completed, In Progress
Completed → (no transitions except reopen via special endpoint)
Cancelled → (no transitions)
```

**Transition Validations:**
- **To "Pending Review":** All checklist items must be completed
- **To "Completed":** Requires Manager or Admin approval; job must be in "Pending Review" status
- **To "Cancelled":** Requires Manager or Admin role; cancellation reason must be provided
- **Reopen Completed:** Only Managers or Admins can reopen completed jobs; status returns to "In Progress"

**Automated Actions on Status Change:**
- **To "In Progress":** If `started_at` is null, set to current timestamp
- **To "Completed":** Set `completed_at` to current timestamp; stop active time logs
- **To "Cancelled":** Stop active time logs

---

### 11.4 Checklist Validation Rules

**Checklist Completion:**
- All checklist items must be marked complete before job can transition to "Pending Review"
- System calculates completion percentage: `(completed_items / total_items) * 100`
- Checklist validation occurs before status update is allowed

**Checklist Item Completion:**
- Only item assignee, manager, or administrator can mark item complete
- Completion sets `is_completed = true` and `completed_at = current_timestamp`

---

### 11.5 Time Tracking Rules

**Active Timer Validation:**
- Only one active timer per user across all jobs
- User cannot start new timer while another is active
- Active timers are automatically stopped when job is completed or cancelled

**Time Log Validation:**
- `end_time` must be after `start_time`
- Duration must be positive and less than 24 hours per entry
- Users cannot have overlapping time logs (validation on date ranges)

**Duration Calculation:**
- Automatic: `duration_minutes = (end_time - start_time) / 60`
- Displayed in hours and minutes format: `HH:MM`

---

### 11.6 Comment & Mention Logic

**Comment Creation:**
- Content must be 1-5000 characters
- System parses for @mentions using pattern `@username`
- Mentioned users must have access to the job

**Mention Notifications:**
- Each mentioned user receives a notification
- Notification type: `comment_mention`
- Notification includes comment excerpt and job reference

**Comment Permissions:**
- Author can edit/delete own comments within 15 minutes of creation
- Admins can delete any comments
- Managers can delete comments within their department

---

### 11.7 Attachment Management

**Upload Validation:**
- File size must be ≤ 10MB per file
- Total attachments per job must be ≤ 50MB
- Allowed file types: PDF, DOC, DOCX, XLS, XLSX, PNG, JPG, JPEG, GIF, TXT, ZIP

**Storage:**
- Files stored with UUID-based naming to prevent collisions
- Original filename preserved in `file_name` field
- File path stored in `file_path` field for retrieval

**Deletion Rules:**
- Only uploader or administrator can delete attachments
- Deletion removes file from storage and database record

---

### 11.8 Notification Logic

**Notification Generation Events:**
- Job assigned to user
- Job reassigned from/to user
- Job status changed
- User mentioned in comment
- Job due within 24 hours
- Job overdue (past due date)
- Job completed
- Job cancelled
- Checklist item assigned to user

**Notification Delivery:**
- In-app notifications always created
- Email notifications sent based on user preferences (future enhancement)
- Notifications marked with `is_read = false` initially

**Notification Cleanup:**
- Read notifications older than 90 days are archived (future enhancement)
- Users can dismiss individual notifications

---

### 11.9 Soft Delete Implementation

**Entities with Soft Delete:**
- Job (primary use case)
- User (deactivation via `is_active` flag)

**Soft Delete Process for Jobs:**
- DELETE endpoint sets `deleted_at` timestamp (requires schema addition)
- Soft-deleted jobs excluded from standard queries
- Admins can view and restore soft-deleted jobs (future enhancement)

**Cascade Rules:**
- Comments, attachments, time logs, history remain accessible for soft-deleted jobs
- Related entities not deleted when job is soft-deleted

---

### 11.10 Concurrency & Locking

**Optimistic Locking:**
- `updated_at` timestamp used for version checking
- Update requests include expected `updated_at` value
- If `updated_at` differs, update fails with conflict error

**Concurrent Update Scenarios:**
- Two users editing same job simultaneously
- Status updates while timer is running
- Multiple checklist items completed concurrently

**Resolution:**
- Client must refresh data and retry operation
- Last write wins for non-conflicting fields

---

### 11.11 Data Validation Rules

**Job Entity:**
- `title`: Required, 5-200 characters, trimmed
- `description`: Optional, minimum 10 characters if provided, trimmed
- `due_date`: Optional, must be future date when creating new job
- `estimated_hours`: Optional, positive number, max 10,000 hours

**User Entity:**
- `email`: Required, valid email format, unique, lowercase
- `password`: Minimum 8 characters, must include uppercase, lowercase, number (validation at registration)
- `first_name`, `last_name`: Required, 1-100 characters, trimmed
- `role`: Required, must be one of: administrator, manager, team_member, viewer
- `phone`: Optional, valid phone format

**Comment Entity:**
- `content`: Required, 1-5000 characters, trimmed

**TimeLog Entity:**
- `start_time`: Required, must be valid datetime
- `end_time`: Optional, must be after `start_time` if provided
- `duration_minutes`: Calculated, must be positive integer

**Checklist Entity:**
- `title`: Required, 1-200 characters, trimmed
- `display_order`: Required, positive integer

**ChecklistItem Entity:**
- `description`: Required, 1-500 characters, trimmed
- `display_order`: Required, positive integer

---

### 11.12 Cascading Operations

**Department Deletion:**
- Cannot delete department with active jobs
- Cannot delete department with users
- Must reassign users and jobs before deletion

**Team Deletion:**
- Cannot delete team with active jobs
- Must reassign jobs before deletion
- User-team associations are removed

**User Deletion (Deactivation):**
- User marked as `is_active = false`
- Cannot assign new jobs to deactivated users
- Existing job assignments remain
- Historical data (comments, time logs) preserved

**Job Deletion (Soft Delete):**
- Job marked as deleted
- Related entities (comments, attachments, history) preserved
- Time logs remain for reporting purposes

---

## 12. Validation Rules

### 12.1 Input Validation

| Field | Validation Rule |
|-------|-----------------|
| Job.title | Required; 5-200 characters; trimmed |
| Job.description | Optional; minimum 10 characters if provided; trimmed; max 5000 characters |
| Job.due_date | Optional; must be future date when creating; format: ISO 8601 datetime |
| Job.estimated_hours | Optional; positive number; max 10,000 |
| User.email | Required; valid email format; unique; lowercase; max 255 characters |
| User.password | Required; minimum 8 characters; must include uppercase, lowercase, digit |
| User.first_name | Required; 1-100 characters; trimmed; alphabetic with spaces/hyphens |
| User.last_name | Required; 1-100 characters; trimmed; alphabetic with spaces/hyphens |
| User.role | Required; must be one of: administrator, manager, team_member, viewer |
| User.phone | Optional; valid phone format; 10-20 characters |
| Comment.content | Required; 1-5000 characters; trimmed |
| TimeLog.start_time | Required; valid ISO 8601 datetime; cannot be future |
| TimeLog.end_time | Optional; valid ISO 8601 datetime; must be after start_time |
| TimeLog.duration_minutes | Calculated; must be positive; max 1440 (24 hours) |
| Attachment.file_size | Required; max 10MB (10485760 bytes) |
| Attachment.file_name | Required; 1-255 characters; valid filename |
| Department.name | Required; 1-100 characters; unique; trimmed |
| Team.name | Required; 1-100 characters; trimmed |
| Checklist.title | Required; 1-200 characters; trimmed |
| ChecklistItem.description | Required; 1-500 characters; trimmed |

---

### 12.2 Business Rule Validation

**Job Assignment:**
- Assignee must exist and be active
- Assignee must belong to job's department (unless Admin)
- Assignee must not be the same as creator (self-assignment prohibited for non-Admins)

**Job Status Transitions:**
- Transition must follow valid workflow path
- "Pending Review" requires all checklist items completed
- "Completed" requires Manager/Admin approval
- "Cancelled" requires Manager/Admin role

**Time Log:**
- User cannot have overlapping time logs
- Only one active timer per user
- Duration calculated must be positive and ≤ 24 hours

**Department/Team Operations:**
- Department cannot be deleted if it has users or jobs
- Team cannot be deleted if it has active jobs
- User must belong to at least one team (validation at user-team removal)

**Checklist Operations:**
- All items must be complete before job can move to "Pending Review"
- Checklist item assignee must have access to job
- Display order must be unique within checklist

**File Upload:**
- File size ≤ 10MB per file
- Total attachments per job ≤ 50MB
- File type must be in allowed list

---

### 12.3 Security Validation

**Authentication:**
- JWT token must be valid and not expired
- Token must be present in Authorization header: `Bearer <token>`
- Token expiration: 60 minutes (configurable)

**Authorization:**
- User role must have permission for requested action
- User must have access to department for non-admin operations
- User must be job assignee or have elevated role for editing

**Data Access:**
- Users can only access jobs within their department (unless Admin)
- Users can only view/edit their own comments and time logs
- Managers can view/edit all data within their department

---

### 12.4 API Request Validation

**Common Validations:**
- Request body must be valid JSON
- Required fields must be present
- Field types must match schema
- UUID fields must be valid UUIDs
- Datetime fields must be ISO 8601 format

**Query Parameter Validation:**
- `page`: Integer ≥ 1
- `page_size`: Integer 1-100
- `sort_by`: Must be valid field name for entity
- `sort_order`: Must be "asc" or "desc"
- Date filters: Valid ISO 8601 datetime format

**File Upload Validation:**
- Content-Type must be multipart/form-data
- File field must be present
- File must not be empty
- Filename must be valid and safe (no path traversal)

---

## 13. Implementation Notes

### 13.1 Technology Stack

**Framework & Language:**
- **FastAPI** (Python 3.11+): Modern, high-performance web framework with automatic OpenAPI documentation
- **Pydantic v2**: Data validation and serialization using Python type annotations

**Database & ORM:**
- **SQLAlchemy ORM**: Database abstraction and object-relational mapping
- **SQLite**: Development and testing database (file-based)
- **PostgreSQL**: Production database (future migration via Alembic)

**Architecture Patterns:**
- **Service Layer Pattern**: Business logic separated from API routes
  - Routes handle HTTP concerns (request/response)
  - Services handle business logic and orchestration
  - Repositories handle database operations
- **Dependency Injection**: FastAPI's `Depends()` for database session management
- **Repository Pattern**: Data access abstraction for testability

---

### 13.2 Project Structure

```
job-management-system/
├── app/
│   ├── __init__.py
│   ├── main.py                 # FastAPI application entry point
│   ├── config.py               # Configuration management
│   ├── database.py             # Database connection and session
│   ├── models/                 # SQLAlchemy ORM models
│   │   ├── __init__.py
│   │   ├── department.py
│   │   ├── team.py
│   │   ├── user.py
│   │   ├── job.py
│   │   ├── comment.py
│   │   ├── attachment.py
│   │   ├── timelog.py
│   │   ├── checklist.py
│   │   └── ...
│   ├── schemas/                # Pydantic schemas for validation
│   │   ├── __init__.py
│   │   ├── department.py
│   │   ├── team.py
│   │   ├── user.py
│   │   ├── job.py
│   │   └── ...
│   ├── services/               # Business logic layer
│   │   ├── __init__.py
│   │   ├── department_service.py
│   │   ├── job_service.py
│   │   ├── user_service.py
│   │   ├── notification_service.py
│   │   └── ...
│   ├── repositories/           # Data access layer
│   │   ├── __init__.py
│   │   ├── department_repository.py
│   │   ├── job_repository.py
│   │   └── ...
│   ├── routers/                # FastAPI route handlers
│   │   ├── __init__.py
│   │   ├── departments.py
│   │   ├── teams.py
│   │   ├── users.py
│   │   ├── jobs.py
│   │   ├── auth.py
│   │   └── ...
│   ├── middleware/             # Custom middleware
│   │   ├── __init__.py
│   │   ├── auth.py
│   │   └── error_handler.py
│   └── utils/                  # Utility functions
│       ├── __init__.py
│       ├── security.py
│       ├── validators.py
│       └── helpers.py
├── tests/                      # Test suite
│   ├── __init__.py
│   ├── test_jobs.py
│   ├── test_users.py
│   └── ...
├── alembic/                    # Database migrations (future)
├── pyproject.toml              # Project dependencies (uv)
├── README.md
└── .env                        # Environment variables
```

---

### 13.3 Database Session Management

**FastAPI Dependency Injection:**
```python
# app/database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session

engine = create_engine("sqlite:///./job_management.db")
SessionLocal = sessionmaker(bind=engine)

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
```

**Usage in Routes:**
```python
@router.get("/jobs/{job_id}")
def get_job(job_id: str, db: Session = Depends(get_db)):
    return job_service.get_job(db, job_id)
```

---

### 13.4 Automatic API Documentation

FastAPI automatically generates interactive API documentation:

- **Swagger UI**: Available at `/docs`
- **ReDoc**: Available at `/redoc`
- **OpenAPI Schema**: Available at `/openapi.json`

Documentation includes:
- All endpoints with methods and paths
- Request/response schemas
- Authentication requirements
- Query parameters and filters
- Example requests and responses

---

### 13.5 Package Management with uv

**uv** is used for fast, reliable Python package management:

```bash
# Install dependencies
uv pip install -r requirements.txt

# Add new dependency
uv pip install <package-name>

# Create virtual environment
uv venv

# Activate virtual environment
source .venv/bin/activate  # Linux/Mac
.venv\Scripts\activate     # Windows
```

**pyproject.toml** defines project dependencies:
```toml
[project]
name = "job-management-system"
version = "1.0.0"
dependencies = [
    "fastapi>=0.104.0",
    "sqlalchemy>=2.0.0",
    "pydantic>=2.0.0",
    "uvicorn>=0.24.0",
    "python-multipart>=0.0.6",
]
```

---

### 13.6 Error Handling

**Standard HTTP Error Responses:**
- `400 Bad Request`: Validation errors, invalid input
- `401 Unauthorized`: Missing or invalid authentication
- `403 Forbidden`: Insufficient permissions
- `404 Not Found`: Resource not found
- `409 Conflict`: Concurrent update conflict
- `422 Unprocessable Entity`: Business rule violation
- `500 Internal Server Error`: Unexpected server error

**Error Response Format:**
```json
{
  "detail": "Error message",
  "field": "field_name",  // Optional, for validation errors
  "code": "ERROR_CODE"     // Optional, for specific error types
}
```

---

### 13.7 Logging & Monitoring

**Logging Configuration:**
- Use Python's `logging` module
- Log levels: DEBUG, INFO, WARNING, ERROR, CRITICAL
- Log format includes timestamp, level, module, message
- Logs written to file and console

**Log Events:**
- API requests (method, path, status code, duration)
- Authentication attempts (success/failure)
- Business rule violations
- Database errors
- External service calls (future: email notifications)

---

### 13.8 Performance Considerations

**Database Query Optimization:**
- Use eager loading for related entities (`joinedload`, `selectinload`)
- Implement pagination for list endpoints (cursor-based)
- Add database indexes on frequently queried fields:
  - Foreign keys
  - Email (users)
  - Name fields (departments, teams, job types)
  - Status and priority fields (jobs)

**Caching Strategy (Future):**
- Cache frequently accessed configuration data (job types, statuses, priorities)
- Cache user permissions for session duration
- Use Redis for distributed caching

**Query Optimization:**
- Batch load related entities to avoid N+1 queries
- Use database-level aggregations for dashboard statistics
- Implement query result caching for expensive reports

---

## 14. Assumptions

### 14.1 Technical Assumptions

1. **Database**: PostgreSQL will be used in production; SQLite for development/testing
2. **Primary Keys**: All tables use UUID (string) primary keys for distributed system compatibility
3. **Timestamps**: All datetime fields stored in UTC; client handles timezone conversion
4. **File Storage**: Files stored on local filesystem initially; cloud storage (S3) for production
5. **Authentication**: JWT tokens with 60-minute expiration; refresh tokens handled separately
6. **Session Management**: Stateless authentication; session data stored client-side in JWT
7. **Password Hashing**: bcrypt algorithm for password hashing with salt rounds = 12
8. **API Versioning**: No versioning initially; future versions will use URL path versioning (`/api/v2/...`)

---

### 14.2 Business Assumptions

1. **Single Organization**: System supports single organization (not multi-tenant)
2. **Department Requirement**: All users must belong to a department (except system admins)
3. **Job Ownership**: Jobs always belong to one department and optionally one team
4. **Work Hours**: Standard work hours assumed for due date calculations (8 hours/day, 5 days/week)
5. **Notification Delivery**: In-app notifications only; email notifications are future enhancement
6. **Report Generation**: Reports generated synchronously for datasets < 10,000 records
7. **User Uniqueness**: Email addresses are globally unique across system
8. **Soft Delete**: Jobs are soft-deleted (marked as deleted) rather than permanently removed

---

### 14.3 Operational Assumptions

1. **Backup Strategy**: Database backups handled by infrastructure/DevOps team
2. **Scaling**: Application runs on single server initially; horizontal scaling future enhancement
3. **Monitoring**: Application performance monitored via logs; APM tools future enhancement
4. **Deployment**: Manual deployment initially; CI/CD pipeline future enhancement
5. **Data Retention**: Historical data retained indefinitely; archival strategy future enhancement
6. **Audit Trail**: All job changes logged in `job_history` table for compliance
7. **Concurrency**: Optimistic locking used; conflicts handled by client retry
8. **File Storage**: 100GB storage allocated for attachments; monitoring required

---

### 14.4 Security Assumptions

1. **HTTPS**: Production environment serves API over HTTPS only
2. **CORS**: CORS configured to allow specific frontend origins
3. **Rate Limiting**: API rate limiting implemented at reverse proxy level (nginx/CloudFlare)
4. **Input Sanitization**: All user input sanitized to prevent XSS and SQL injection
5. **File Upload**: File type validation prevents executable uploads
6. **Authentication**: Failed login attempts logged for security monitoring
7. **Password Policy**: Strong password requirements enforced at registration
8. **Token Storage**: JWT tokens stored securely client-side (httpOnly cookies or secure storage)

---

### 14.5 Integration Assumptions

1. **Email Service**: SMTP server available for notification emails (future enhancement)
2. **File Storage**: S3-compatible storage available for production (future enhancement)
3. **Calendar Integration**: External calendar APIs (Google Calendar, Outlook) not initially integrated
4. **Third-Party Services**: No external dependencies initially; integrations future enhancements
5. **Webhooks**: Webhook support for external integrations future enhancement
6. **SSO**: Single Sign-On (SSO) not initially supported; future enhancement

---

## 15. Future Improvements

### 15.1 Infrastructure & DevOps

1. **Alembic Migrations**: Implement database migration management with Alembic for schema versioning and upgrades
2. **PostgreSQL Migration**: Migrate from SQLite to PostgreSQL for production scalability and performance
3. **Docker Containerization**: Create Dockerfile and docker-compose.yml for containerized deployment
4. **CI/CD Pipeline**: Implement automated testing, building, and deployment pipeline (GitHub Actions, GitLab CI)
5. **Environment Configuration**: Use environment-specific configuration files (dev, staging, production)
6. **Kubernetes Deployment**: Deploy application to Kubernetes for orchestration and scaling

---

### 15.2 Authentication & Authorization

1. **JWT Authentication**: Implement JWT token-based authentication with access and refresh tokens
2. **Role-Based Access Control (RBAC)**: Enforce granular permissions based on user roles
3. **OAuth2 Integration**: Support OAuth2 providers (Google, Microsoft, GitHub) for SSO
4. **Multi-Factor Authentication (MFA)**: Add optional MFA for enhanced security
5. **Password Reset Flow**: Implement secure password reset via email
6. **Session Management**: Track active sessions and provide user session management
7. **API Key Authentication**: Support API keys for programmatic access

---

### 15.3 Testing & Quality Assurance

1. **Unit Tests**: Comprehensive unit tests for services and repositories using pytest
2. **Integration Tests**: Test API endpoints with test database
3. **End-to-End Tests**: Automated E2E tests for critical workflows
4. **Test Coverage**: Achieve 80%+ code coverage with automated reporting
5. **Load Testing**: Performance testing with tools like Locust or k6
6. **Security Testing**: Automated security scanning (OWASP ZAP, Bandit)
7. **Contract Testing**: API contract testing to ensure frontend/backend compatibility

---

### 15.4 Performance & Scalability

1. **Caching Layer**: Implement Redis for caching frequently accessed data
2. **Database Indexing**: Add comprehensive indexes based on query patterns
3. **Query Optimization**: Analyze and optimize slow queries with database profiling
4. **Connection Pooling**: Configure database connection pooling for high concurrency
5. **Horizontal Scaling**: Support multiple application instances behind load balancer
6. **Background Jobs**: Implement Celery for asynchronous task processing (reports, notifications)
7. **CDN Integration**: Serve static files and attachments via CDN

---

### 15.5 Feature Enhancements

1. **Recurring Jobs**: Support for daily, weekly, monthly scheduled jobs
2. **Job Templates**: Pre-configured job templates for common workflows
3. **Bulk Operations**: Bulk assign, update status, delete operations
4. **Advanced Search**: Full-text search with Elasticsearch integration
5. **Calendar View**: Visual calendar for jobs with due dates
6. **Gantt Chart**: Project timeline visualization for job dependencies
7. **Real-Time Collaboration**: WebSocket support for live updates
8. **Custom Fields**: Configurable custom fields for jobs
9. **Approval Workflows**: Multi-step approval processes
10. **SLA Tracking**: Track and alert on SLA breaches
11. **File Versioning**: Version control for attachments
12. **Job Dependencies**: Define prerequisite jobs (job A must complete before job B)
13. **Workload Balancing**: AI-based suggestions for optimal job assignment
14. **Time Estimates**: Machine learning for completion time predictions
15. **Mobile App**: Native iOS and Android applications
16. **Custom Reports**: Drag-and-drop report builder
17. **Data Export**: GDPR-compliant data export for users
18. **Multi-Language Support**: i18n for internationalization
19. **Analytics Dashboard**: Predictive insights and trend analysis
20. **Email Integration**: Parse incoming emails to create jobs

---

### 15.6 Integration & APIs

1. **Webhook Support**: Trigger external systems on job events
2. **REST API Versioning**: Support multiple API versions simultaneously
3. **GraphQL API**: Optional GraphQL endpoint for flexible querying
4. **Calendar Integration**: Sync jobs with Google Calendar, Outlook
5. **Slack Integration**: Post notifications and updates to Slack channels
6. **Email Notifications**: Configurable email notifications for job events
7. **JIRA Integration**: Sync jobs with JIRA issues
8. **API Rate Limiting**: Per-user and per-endpoint rate limiting
9. **API Documentation**: Enhanced documentation with code examples

---

### 15.7 Security Enhancements

1. **Audit Logging**: Comprehensive audit log for all data changes
2. **Encryption at Rest**: Encrypt sensitive data in database
3. **Field-Level Encryption**: Encrypt specific sensitive fields (e.g., phone numbers)
4. **GDPR Compliance**: Right to erasure, data portability, consent management
5. **Content Security Policy (CSP)**: Implement strict CSP headers
6. **Security Headers**: Add HSTS, X-Frame-Options, X-Content-Type-Options
7. **Penetration Testing**: Regular security audits and penetration tests
8. **Vulnerability Scanning**: Automated dependency vulnerability scanning

---

### 15.8 Monitoring & Observability

1. **APM Integration**: Application Performance Monitoring (New Relic, DataDog)
2. **Error Tracking**: Sentry or Rollbar for error tracking and alerting
3. **Metrics Dashboard**: Grafana dashboard for system metrics
4. **Log Aggregation**: Centralized logging with ELK stack or Splunk
5. **Health Check Endpoints**: `/health` and `/readiness` endpoints for monitoring
6. **Distributed Tracing**: OpenTelemetry for request tracing across services
7. **Alerting**: PagerDuty or Opsgenie integration for critical alerts

---

### 15.9 Data Management

1. **Data Archival**: Archive old jobs and historical data
2. **Data Retention Policies**: Configurable retention periods for different data types
3. **Backup Automation**: Automated daily backups with verification
4. **Disaster Recovery**: Documented disaster recovery procedures
5. **Data Migration Tools**: Tools for migrating data between environments
6. **Database Sharding**: Horizontal partitioning for large datasets

---

### 15.10 User Experience

1. **Notification Preferences**: User-configurable notification settings
2. **Dark Mode**: Support for dark mode in API responses (frontend-driven)
3. **Saved Filters**: Save and reuse complex filter combinations
4. **Export Features**: Export job lists to CSV, Excel, PDF
5. **Keyboard Shortcuts**: API support for keyboard shortcut actions
6. **Activity Feed**: Real-time activity feed for user and team actions
7. **Quick Actions**: Batch operations via API for common tasks
8. **Mobile-Optimized**: Mobile-responsive API design considerations

---

This completes the comprehensive Backend Specification document for the Job Management System. The specification provides a complete blueprint for implementation using FastAPI, SQLAlchemy, Pydantic v2, and SQLite, with a clear path for future enhancements including Alembic migrations, PostgreSQL, JWT authentication, RBAC, comprehensive testing, and Docker containerization.