Chapter 15 Portfolio Project Building a Professional Web Application - Bryantad/Sona GitHub Wiki
Chapter 15: Portfolio Project - Building a Professional Web Application
From Concept to Deployment: Creating "TaskFlow Pro" - A Complete Project Management Platform
Andre's Portfolio Project Philosophy
"This is where everything comes together. After 14 chapters of learning concepts, patterns, and techniques, it's time to build something realβsomething you'd be proud to show to employers, clients, or fellow developers. TaskFlow Pro isn't just a tutorial project; it's a production-ready application that demonstrates mastery of professional software development. We'll build it the way I'd build a real product for my own company."
Project Overview: TaskFlow Pro
TaskFlow Pro - Professional Project Management Platform
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β π― Core Features β
β β’ Project & Task Management β’ Team Collaboration β
β β’ Real-time Updates β’ File Attachments β
β β’ Time Tracking β’ Reporting & Analytics β
β β’ Role-based Permissions β’ API & Integrations β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ€
β ποΈ Technical Architecture β
β β’ Clean Architecture β’ Event-Driven Design β
β β’ RESTful API β’ Real-time WebSockets β
β β’ Modern Web UI β’ Database Design β
β β’ Authentication & Security β’ Performance Optimization β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ€
β π Portfolio Highlights β
β β’ Production-Ready Code β’ Comprehensive Testing β
β β’ Professional Documentation β’ Deployment Pipeline β
β β’ Performance Monitoring β’ Security Best Practices β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Chapter Learning Objectives
By the end of this chapter, you will have:
- Built a complete, production-ready web application from scratch
- Implemented all major software architecture patterns
- Created a professional user interface with modern UX principles
- Designed and implemented a robust database schema
- Built comprehensive APIs with authentication and authorization
- Implemented real-time features using WebSockets
- Created a deployment pipeline and monitoring system
- Developed a portfolio piece that demonstrates professional competency
Section 1: Project Architecture and Domain Design
Domain Model and Business Requirements
// Domain entities for TaskFlow Pro
// Core business concepts and rules
class Organization {
id: string
name: string
slug: string
owner_id: string
subscription_plan: string
created_at: datetime
settings: {}
constructor(name, owner_id, subscription_plan = "free") {
self.id = generate_uuid()
self.name = name
self.slug = self.generate_slug(name)
self.owner_id = owner_id
self.subscription_plan = subscription_plan
self.created_at = datetime.now()
self.settings = self.default_settings()
self.validate()
}
validate() {
if not self.name or self.name.length < 2 {
throw ValidationError("Organization name must be at least 2 characters")
}
if not self.slug or not self.is_valid_slug(self.slug) {
throw ValidationError("Invalid organization slug")
}
if self.subscription_plan not in ["free", "pro", "enterprise"] {
throw ValidationError("Invalid subscription plan")
}
}
generate_slug(name) {
return name.lower()
.replace(/[^a-z0-9]+/g, "-")
.replace(/^-+|-+$/g, "")
}
is_valid_slug(slug) {
return /^[a-z0-9]+(?:-[a-z0-9]+)*$/.test(slug)
}
default_settings() {
return {
"allow_guest_access": false,
"default_project_visibility": "private",
"time_tracking_enabled": true,
"notification_preferences": {
"email_summaries": true,
"real_time_notifications": true
}
}
}
can_create_project(user) {
return user.id == self.owner_id or self.has_member_with_role(user.id, ["admin", "project_manager"])
}
get_member_limits() {
match self.subscription_plan {
"free" => return {"max_members": 5, "max_projects": 3},
"pro" => return {"max_members": 50, "max_projects": null},
"enterprise" => return {"max_members": null, "max_projects": null}
}
}
}
class Project {
id: string
organization_id: string
name: string
description: string
status: string
priority: string
owner_id: string
start_date: datetime
due_date: datetime
created_at: datetime
updated_at: datetime
metadata: {}
constructor(organization_id, name, owner_id, description = "") {
self.id = generate_uuid()
self.organization_id = organization_id
self.name = name
self.description = description
self.status = "active"
self.priority = "medium"
self.owner_id = owner_id
self.start_date = datetime.now()
self.due_date = null
self.created_at = datetime.now()
self.updated_at = datetime.now()
self.metadata = {}
self.validate()
}
validate() {
if not self.name or self.name.length < 2 {
throw ValidationError("Project name must be at least 2 characters")
}
if self.status not in ["active", "on_hold", "completed", "cancelled"] {
throw ValidationError("Invalid project status")
}
if self.priority not in ["low", "medium", "high", "critical"] {
throw ValidationError("Invalid project priority")
}
if self.due_date and self.due_date < self.start_date {
throw ValidationError("Due date cannot be before start date")
}
}
update_status(new_status, user_id) {
let old_status = self.status
self.status = new_status
self.updated_at = datetime.now()
self.validate()
return ProjectStatusChangedEvent(
project_id: self.id,
old_status: old_status,
new_status: new_status,
changed_by: user_id,
occurred_at: self.updated_at
)
}
is_overdue() {
return self.due_date and datetime.now() > self.due_date and self.status != "completed"
}
calculate_progress() {
# This would be calculated based on completed tasks
# For now, return a placeholder
return 0.0
}
}
class Task {
id: string
project_id: string
title: string
description: string
status: string
priority: string
assignee_id: string
reporter_id: string
due_date: datetime
estimated_hours: float
actual_hours: float
created_at: datetime
updated_at: datetime
tags: []
constructor(project_id, title, reporter_id) {
self.id = generate_uuid()
self.project_id = project_id
self.title = title
self.description = ""
self.status = "todo"
self.priority = "medium"
self.assignee_id = null
self.reporter_id = reporter_id
self.due_date = null
self.estimated_hours = 0.0
self.actual_hours = 0.0
self.created_at = datetime.now()
self.updated_at = datetime.now()
self.tags = []
self.validate()
}
validate() {
if not self.title or self.title.length < 2 {
throw ValidationError("Task title must be at least 2 characters")
}
if self.status not in ["todo", "in_progress", "in_review", "completed", "cancelled"] {
throw ValidationError("Invalid task status")
}
if self.priority not in ["low", "medium", "high", "critical"] {
throw ValidationError("Invalid task priority")
}
if self.estimated_hours < 0 or self.actual_hours < 0 {
throw ValidationError("Hours cannot be negative")
}
}
assign_to(user_id, assigned_by) {
let old_assignee = self.assignee_id
self.assignee_id = user_id
self.updated_at = datetime.now()
return TaskAssignedEvent(
task_id: self.id,
project_id: self.project_id,
old_assignee_id: old_assignee,
new_assignee_id: user_id,
assigned_by: assigned_by,
occurred_at: self.updated_at
)
}
start_work(user_id) {
if self.status != "todo" {
throw InvalidStateError("Can only start work on todo tasks")
}
self.status = "in_progress"
self.updated_at = datetime.now()
return TaskStartedEvent(
task_id: self.id,
project_id: self.project_id,
started_by: user_id,
occurred_at: self.updated_at
)
}
complete_task(user_id) {
if self.status not in ["in_progress", "in_review"] {
throw InvalidStateError("Can only complete tasks that are in progress or in review")
}
self.status = "completed"
self.updated_at = datetime.now()
return TaskCompletedEvent(
task_id: self.id,
project_id: self.project_id,
completed_by: user_id,
occurred_at: self.updated_at,
actual_hours: self.actual_hours
)
}
log_time(hours, user_id, description = "") {
if hours <= 0 {
throw ValidationError("Logged hours must be positive")
}
self.actual_hours += hours
self.updated_at = datetime.now()
return TimeLoggedEvent(
task_id: self.id,
project_id: self.project_id,
hours: hours,
logged_by: user_id,
description: description,
occurred_at: self.updated_at
)
}
is_overdue() {
return self.due_date and datetime.now() > self.due_date and self.status != "completed"
}
}
class User {
id: string
email: string
username: string
full_name: string
avatar_url: string
timezone: string
preferences: {}
created_at: datetime
last_active_at: datetime
is_active: bool
constructor(email, username, full_name) {
self.id = generate_uuid()
self.email = email
self.username = username
self.full_name = full_name
self.avatar_url = self.generate_avatar_url()
self.timezone = "UTC"
self.preferences = self.default_preferences()
self.created_at = datetime.now()
self.last_active_at = datetime.now()
self.is_active = true
self.validate()
}
validate() {
if not self.email or not self.is_valid_email(self.email) {
throw ValidationError("Invalid email address")
}
if not self.username or self.username.length < 3 {
throw ValidationError("Username must be at least 3 characters")
}
if not self.full_name or self.full_name.length < 2 {
throw ValidationError("Full name must be at least 2 characters")
}
}
is_valid_email(email) {
return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)
}
generate_avatar_url() {
# Generate a Gravatar URL or default avatar
let hash = md5(self.email.lower())
return f"https://www.gravatar.com/avatar/{hash}?d=identicon&s=200"
}
default_preferences() {
return {
"email_notifications": true,
"desktop_notifications": true,
"theme": "light",
"language": "en",
"date_format": "MM/DD/YYYY",
"time_format": "12h"
}
}
update_last_active() {
self.last_active_at = datetime.now()
}
get_display_name() {
return self.full_name or self.username
}
}
Application Services and Use Cases
// Application layer - Use cases and business workflows
class CreateProjectUseCase {
project_repository: ProjectRepository
organization_repository: OrganizationRepository
user_repository: UserRepository
event_dispatcher: EventDispatcher
constructor(project_repository, organization_repository, user_repository, event_dispatcher) {
self.project_repository = project_repository
self.organization_repository = organization_repository
self.user_repository = user_repository
self.event_dispatcher = event_dispatcher
}
async execute(command) {
# Validate command
self.validate_command(command)
# Check permissions
let organization = await self.organization_repository.find_by_id(command.organization_id)
if not organization {
throw NotFoundError("Organization not found")
}
let user = await self.user_repository.find_by_id(command.user_id)
if not user {
throw NotFoundError("User not found")
}
if not organization.can_create_project(user) {
throw AuthorizationError("User does not have permission to create projects")
}
# Check subscription limits
let limits = organization.get_member_limits()
if limits.max_projects {
let project_count = await self.project_repository.count_by_organization(organization.id)
if project_count >= limits.max_projects {
throw SubscriptionLimitError("Project limit exceeded for current plan")
}
}
# Create project
let project = Project(
organization_id: command.organization_id,
name: command.name,
owner_id: command.user_id,
description: command.description
)
if command.due_date {
project.due_date = command.due_date
}
if command.priority {
project.priority = command.priority
}
# Save project
await self.project_repository.save(project)
# Dispatch events
let project_created_event = ProjectCreatedEvent(
project_id: project.id,
organization_id: project.organization_id,
name: project.name,
owner_id: project.owner_id,
occurred_at: project.created_at
)
await self.event_dispatcher.dispatch(project_created_event)
return CreateProjectResult(
project_id: project.id,
success: true,
message: "Project created successfully"
)
}
validate_command(command) {
if not command.organization_id {
throw ValidationError("Organization ID is required")
}
if not command.name {
throw ValidationError("Project name is required")
}
if not command.user_id {
throw ValidationError("User ID is required")
}
}
}
class CreateTaskUseCase {
task_repository: TaskRepository
project_repository: ProjectRepository
user_repository: UserRepository
event_dispatcher: EventDispatcher
constructor(task_repository, project_repository, user_repository, event_dispatcher) {
self.task_repository = task_repository
self.project_repository = project_repository
self.user_repository = user_repository
self.event_dispatcher = event_dispatcher
}
async execute(command) {
# Validate command
self.validate_command(command)
# Check permissions
let project = await self.project_repository.find_by_id(command.project_id)
if not project {
throw NotFoundError("Project not found")
}
let user = await self.user_repository.find_by_id(command.user_id)
if not user {
throw NotFoundError("User not found")
}
# Check if user has access to the project
# This would involve checking organization membership and project permissions
if not await self.can_user_create_task(user, project) {
throw AuthorizationError("User does not have permission to create tasks in this project")
}
# Create task
let task = Task(
project_id: command.project_id,
title: command.title,
reporter_id: command.user_id
)
task.description = command.description or ""
task.priority = command.priority or "medium"
task.due_date = command.due_date
task.estimated_hours = command.estimated_hours or 0.0
task.tags = command.tags or []
if command.assignee_id {
# Validate assignee exists and has access
let assignee = await self.user_repository.find_by_id(command.assignee_id)
if assignee and await self.can_user_access_project(assignee, project) {
task.assignee_id = command.assignee_id
}
}
# Save task
await self.task_repository.save(task)
# Dispatch events
let task_created_event = TaskCreatedEvent(
task_id: task.id,
project_id: task.project_id,
title: task.title,
reporter_id: task.reporter_id,
assignee_id: task.assignee_id,
occurred_at: task.created_at
)
await self.event_dispatcher.dispatch(task_created_event)
return CreateTaskResult(
task_id: task.id,
success: true,
message: "Task created successfully"
)
}
validate_command(command) {
if not command.project_id {
throw ValidationError("Project ID is required")
}
if not command.title {
throw ValidationError("Task title is required")
}
if not command.user_id {
throw ValidationError("User ID is required")
}
}
async can_user_create_task(user, project) {
# Check if user is a member of the organization
# and has appropriate permissions
# This is simplified - in reality would check membership and roles
return true
}
async can_user_access_project(user, project) {
# Check if user has access to the project
# This is simplified - in reality would check permissions
return true
}
}
class GetProjectDashboardUseCase {
project_repository: ProjectRepository
task_repository: TaskRepository
user_repository: UserRepository
constructor(project_repository, task_repository, user_repository) {
self.project_repository = project_repository
self.task_repository = task_repository
self.user_repository = user_repository
}
async execute(query) {
# Validate query
if not query.project_id {
throw ValidationError("Project ID is required")
}
if not query.user_id {
throw ValidationError("User ID is required")
}
# Get project
let project = await self.project_repository.find_by_id(query.project_id)
if not project {
throw NotFoundError("Project not found")
}
# Check permissions
let user = await self.user_repository.find_by_id(query.user_id)
if not user {
throw NotFoundError("User not found")
}
# Get project data
let tasks = await self.task_repository.find_by_project_id(project.id)
let team_members = await self.get_project_team_members(project.id)
# Calculate dashboard metrics
let metrics = self.calculate_project_metrics(project, tasks)
let recent_activity = await self.get_recent_activity(project.id)
return ProjectDashboardResult(
project: project,
metrics: metrics,
tasks: tasks,
team_members: team_members,
recent_activity: recent_activity
)
}
calculate_project_metrics(project, tasks) {
let total_tasks = tasks.length
let completed_tasks = tasks.filter(t => t.status == "completed").length
let in_progress_tasks = tasks.filter(t => t.status == "in_progress").length
let overdue_tasks = tasks.filter(t => t.is_overdue()).length
let total_estimated_hours = tasks.sum(t => t.estimated_hours)
let total_actual_hours = tasks.sum(t => t.actual_hours)
let completion_percentage = total_tasks > 0 ? (completed_tasks / total_tasks) * 100 : 0
return {
"total_tasks": total_tasks,
"completed_tasks": completed_tasks,
"in_progress_tasks": in_progress_tasks,
"overdue_tasks": overdue_tasks,
"completion_percentage": completion_percentage,
"total_estimated_hours": total_estimated_hours,
"total_actual_hours": total_actual_hours,
"is_on_track": overdue_tasks == 0 and completion_percentage >= self.expected_completion_rate(project)
}
}
expected_completion_rate(project) {
if not project.due_date {
return 0 # Can't calculate without due date
}
let total_duration = project.due_date - project.start_date
let elapsed_duration = datetime.now() - project.start_date
return (elapsed_duration / total_duration) * 100
}
async get_project_team_members(project_id) {
# This would fetch team members from project assignments/memberships
# Simplified for example
return []
}
async get_recent_activity(project_id) {
# This would fetch recent project activity from event store or activity log
# Simplified for example
return []
}
}
Section 2: Database Design and Data Layer
Database Schema Design
// Database schema for TaskFlow Pro
class DatabaseSchema {
migrations: []
constructor() {
self.migrations = [
self.create_initial_schema,
self.add_indexes,
self.add_full_text_search,
self.add_audit_logging
]
}
async create_initial_schema(db) {
# Organizations table
await db.execute("""
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
slug VARCHAR(100) UNIQUE NOT NULL,
owner_id UUID NOT NULL,
subscription_plan VARCHAR(50) NOT NULL DEFAULT 'free',
settings JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT organizations_subscription_plan_check
CHECK (subscription_plan IN ('free', 'pro', 'enterprise'))
)
""")
# Users table
await db.execute("""
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(100) UNIQUE NOT NULL,
full_name VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
avatar_url VARCHAR(500),
timezone VARCHAR(50) NOT NULL DEFAULT 'UTC',
preferences JSONB NOT NULL DEFAULT '{}',
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
last_active_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
)
""")
# Organization memberships
await db.execute("""
CREATE TABLE organization_memberships (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role VARCHAR(50) NOT NULL DEFAULT 'member',
joined_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
invited_by UUID REFERENCES users(id),
UNIQUE(organization_id, user_id),
CONSTRAINT organization_memberships_role_check
CHECK (role IN ('owner', 'admin', 'project_manager', 'member', 'guest'))
)
""")
# Projects table
await db.execute("""
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
description TEXT,
status VARCHAR(50) NOT NULL DEFAULT 'active',
priority VARCHAR(50) NOT NULL DEFAULT 'medium',
owner_id UUID NOT NULL REFERENCES users(id),
start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
due_date TIMESTAMP WITH TIME ZONE,
metadata JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT projects_status_check
CHECK (status IN ('active', 'on_hold', 'completed', 'cancelled')),
CONSTRAINT projects_priority_check
CHECK (priority IN ('low', 'medium', 'high', 'critical'))
)
""")
# Tasks table
await db.execute("""
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL,
description TEXT,
status VARCHAR(50) NOT NULL DEFAULT 'todo',
priority VARCHAR(50) NOT NULL DEFAULT 'medium',
assignee_id UUID REFERENCES users(id),
reporter_id UUID NOT NULL REFERENCES users(id),
due_date TIMESTAMP WITH TIME ZONE,
estimated_hours DECIMAL(8,2) NOT NULL DEFAULT 0,
actual_hours DECIMAL(8,2) NOT NULL DEFAULT 0,
tags TEXT[],
metadata JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT tasks_status_check
CHECK (status IN ('todo', 'in_progress', 'in_review', 'completed', 'cancelled')),
CONSTRAINT tasks_priority_check
CHECK (priority IN ('low', 'medium', 'high', 'critical')),
CONSTRAINT tasks_hours_check
CHECK (estimated_hours >= 0 AND actual_hours >= 0)
)
""")
# Time logs table
await db.execute("""
CREATE TABLE time_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id),
hours DECIMAL(8,2) NOT NULL,
description TEXT,
date DATE NOT NULL DEFAULT CURRENT_DATE,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT time_logs_hours_check CHECK (hours > 0)
)
""")
# Comments table
await db.execute("""
CREATE TABLE comments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id),
content TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
)
""")
# File attachments table
await db.execute("""
CREATE TABLE attachments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
uploaded_by UUID NOT NULL REFERENCES users(id),
filename VARCHAR(255) NOT NULL,
original_filename VARCHAR(255) NOT NULL,
file_size BIGINT NOT NULL,
mime_type VARCHAR(100) NOT NULL,
storage_path VARCHAR(500) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT attachments_reference_check
CHECK ((task_id IS NOT NULL) OR (project_id IS NOT NULL))
)
""")
# Activity log table
await db.execute("""
CREATE TABLE activity_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id),
action VARCHAR(100) NOT NULL,
entity_type VARCHAR(50) NOT NULL,
entity_id UUID NOT NULL,
details JSONB NOT NULL DEFAULT '{}',
ip_address INET,
user_agent TEXT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
)
""")
}
async add_indexes(db) {
# Performance indexes
await db.execute("CREATE INDEX idx_organizations_owner_id ON organizations(owner_id)")
await db.execute("CREATE INDEX idx_organizations_slug ON organizations(slug)")
await db.execute("CREATE INDEX idx_users_email ON users(email)")
await db.execute("CREATE INDEX idx_users_username ON users(username)")
await db.execute("CREATE INDEX idx_users_last_active ON users(last_active_at)")
await db.execute("CREATE INDEX idx_organization_memberships_org_id ON organization_memberships(organization_id)")
await db.execute("CREATE INDEX idx_organization_memberships_user_id ON organization_memberships(user_id)")
await db.execute("CREATE INDEX idx_projects_org_id ON projects(organization_id)")
await db.execute("CREATE INDEX idx_projects_owner_id ON projects(owner_id)")
await db.execute("CREATE INDEX idx_projects_status ON projects(status)")
await db.execute("CREATE INDEX idx_projects_due_date ON projects(due_date)")
await db.execute("CREATE INDEX idx_tasks_project_id ON tasks(project_id)")
await db.execute("CREATE INDEX idx_tasks_assignee_id ON tasks(assignee_id)")
await db.execute("CREATE INDEX idx_tasks_reporter_id ON tasks(reporter_id)")
await db.execute("CREATE INDEX idx_tasks_status ON tasks(status)")
await db.execute("CREATE INDEX idx_tasks_due_date ON tasks(due_date)")
await db.execute("CREATE INDEX idx_tasks_tags ON tasks USING GIN(tags)")
await db.execute("CREATE INDEX idx_time_logs_task_id ON time_logs(task_id)")
await db.execute("CREATE INDEX idx_time_logs_user_id ON time_logs(user_id)")
await db.execute("CREATE INDEX idx_time_logs_date ON time_logs(date)")
await db.execute("CREATE INDEX idx_comments_task_id ON comments(task_id)")
await db.execute("CREATE INDEX idx_comments_user_id ON comments(user_id)")
await db.execute("CREATE INDEX idx_attachments_task_id ON attachments(task_id)")
await db.execute("CREATE INDEX idx_attachments_project_id ON attachments(project_id)")
await db.execute("CREATE INDEX idx_activity_logs_org_id ON activity_logs(organization_id)")
await db.execute("CREATE INDEX idx_activity_logs_project_id ON activity_logs(project_id)")
await db.execute("CREATE INDEX idx_activity_logs_user_id ON activity_logs(user_id)")
await db.execute("CREATE INDEX idx_activity_logs_created_at ON activity_logs(created_at)")
}
async add_full_text_search(db) {
# Full-text search indexes
await db.execute("""
CREATE INDEX idx_projects_search ON projects
USING GIN(to_tsvector('english', name || ' ' || COALESCE(description, '')))
""")
await db.execute("""
CREATE INDEX idx_tasks_search ON tasks
USING GIN(to_tsvector('english', title || ' ' || COALESCE(description, '')))
""")
await db.execute("""
CREATE INDEX idx_comments_search ON comments
USING GIN(to_tsvector('english', content))
""")
}
async add_audit_logging(db) {
# Add audit triggers for key tables
await db.execute("""
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
INSERT INTO activity_logs (
organization_id, project_id, user_id, action,
entity_type, entity_id, details, created_at
) VALUES (
COALESCE(NEW.organization_id, OLD.organization_id),
COALESCE(NEW.project_id, OLD.project_id),
current_setting('app.current_user_id')::UUID,
'updated',
TG_TABLE_NAME,
NEW.id,
jsonb_build_object('changes', to_jsonb(NEW) - to_jsonb(OLD)),
NOW()
);
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO activity_logs (
organization_id, project_id, user_id, action,
entity_type, entity_id, details, created_at
) VALUES (
NEW.organization_id,
NEW.project_id,
current_setting('app.current_user_id')::UUID,
'created',
TG_TABLE_NAME,
NEW.id,
to_jsonb(NEW),
NOW()
);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO activity_logs (
organization_id, project_id, user_id, action,
entity_type, entity_id, details, created_at
) VALUES (
OLD.organization_id,
OLD.project_id,
current_setting('app.current_user_id')::UUID,
'deleted',
TG_TABLE_NAME,
OLD.id,
to_jsonb(OLD),
NOW()
);
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
""")
# Add triggers to key tables
for table in ["projects", "tasks", "time_logs"] {
await db.execute(f"""
CREATE TRIGGER audit_trigger_{table}
AFTER INSERT OR UPDATE OR DELETE ON {table}
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
""")
}
}
async run_migrations(db) {
# Create migrations table if it doesn't exist
await db.execute("""
CREATE TABLE IF NOT EXISTS schema_migrations (
version INTEGER PRIMARY KEY,
applied_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
)
""")
# Get current version
let result = await db.query_one("SELECT MAX(version) as version FROM schema_migrations")
let current_version = result.version or 0
# Run pending migrations
for i, migration in enumerate(self.migrations) {
let version = i + 1
if version > current_version {
print(f"Running migration {version}...")
await migration(db)
await db.execute("INSERT INTO schema_migrations (version) VALUES ($1)", [version])
print(f"Migration {version} completed")
}
}
}
}
Repository Implementation
// Repository implementations for data access
class PostgreSQLProjectRepository implements ProjectRepository {
database: Database
constructor(database) {
self.database = database
}
async save(project) {
let query = """
INSERT INTO projects (
id, organization_id, name, description, status, priority,
owner_id, start_date, due_date, metadata, created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
description = EXCLUDED.description,
status = EXCLUDED.status,
priority = EXCLUDED.priority,
due_date = EXCLUDED.due_date,
metadata = EXCLUDED.metadata,
updated_at = EXCLUDED.updated_at
"""
await self.database.execute(query, [
project.id,
project.organization_id,
project.name,
project.description,
project.status,
project.priority,
project.owner_id,
project.start_date,
project.due_date,
json.stringify(project.metadata),
project.created_at,
project.updated_at
])
}
async find_by_id(project_id) {
let query = "SELECT * FROM projects WHERE id = $1"
let row = await self.database.query_one(query, [project_id])
if not row {
return null
}
return self.map_to_domain_entity(row)
}
async find_by_organization_id(organization_id, filters = {}) {
let conditions = ["organization_id = $1"]
let params = [organization_id]
let param_count = 1
if filters.status {
param_count += 1
conditions.append(f"status = ${param_count}")
params.append(filters.status)
}
if filters.owner_id {
param_count += 1
conditions.append(f"owner_id = ${param_count}")
params.append(filters.owner_id)
}
if filters.search {
param_count += 1
conditions.append(f"to_tsvector('english', name || ' ' || COALESCE(description, '')) @@ plainto_tsquery('english', ${param_count})")
params.append(filters.search)
}
let order_by = "ORDER BY created_at DESC"
if filters.sort == "name" {
order_by = "ORDER BY name"
} elif filters.sort == "due_date" {
order_by = "ORDER BY due_date NULLS LAST"
}
let limit_clause = ""
if filters.limit {
param_count += 1
limit_clause = f"LIMIT ${param_count}"
params.append(filters.limit)
}
let query = f"""
SELECT * FROM projects
WHERE {conditions.join(' AND ')}
{order_by}
{limit_clause}
"""
let rows = await self.database.query(query, params)
return rows.map(row => self.map_to_domain_entity(row))
}
async count_by_organization(organization_id) {
let query = "SELECT COUNT(*) as count FROM projects WHERE organization_id = $1"
let result = await self.database.query_one(query, [organization_id])
return result.count
}
async delete(project_id) {
let query = "DELETE FROM projects WHERE id = $1"
await self.database.execute(query, [project_id])
}
map_to_domain_entity(row) {
let project = Project(
organization_id: row.organization_id,
name: row.name,
owner_id: row.owner_id,
description: row.description or ""
)
project.id = row.id
project.status = row.status
project.priority = row.priority
project.start_date = row.start_date
project.due_date = row.due_date
project.created_at = row.created_at
project.updated_at = row.updated_at
project.metadata = json.parse(row.metadata or "{}")
return project
}
}
class PostgreSQLTaskRepository implements TaskRepository {
database: Database
constructor(database) {
self.database = database
}
async save(task) {
let query = """
INSERT INTO tasks (
id, project_id, title, description, status, priority,
assignee_id, reporter_id, due_date, estimated_hours,
actual_hours, tags, metadata, created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
ON CONFLICT (id) DO UPDATE SET
title = EXCLUDED.title,
description = EXCLUDED.description,
status = EXCLUDED.status,
priority = EXCLUDED.priority,
assignee_id = EXCLUDED.assignee_id,
due_date = EXCLUDED.due_date,
estimated_hours = EXCLUDED.estimated_hours,
actual_hours = EXCLUDED.actual_hours,
tags = EXCLUDED.tags,
metadata = EXCLUDED.metadata,
updated_at = EXCLUDED.updated_at
"""
await self.database.execute(query, [
task.id,
task.project_id,
task.title,
task.description,
task.status,
task.priority,
task.assignee_id,
task.reporter_id,
task.due_date,
task.estimated_hours,
task.actual_hours,
task.tags,
json.stringify(task.metadata),
task.created_at,
task.updated_at
])
}
async find_by_id(task_id) {
let query = "SELECT * FROM tasks WHERE id = $1"
let row = await self.database.query_one(query, [task_id])
if not row {
return null
}
return self.map_to_domain_entity(row)
}
async find_by_project_id(project_id, filters = {}) {
let conditions = ["project_id = $1"]
let params = [project_id]
let param_count = 1
if filters.status {
param_count += 1
conditions.append(f"status = ${param_count}")
params.append(filters.status)
}
if filters.assignee_id {
param_count += 1
conditions.append(f"assignee_id = ${param_count}")
params.append(filters.assignee_id)
}
if filters.priority {
param_count += 1
conditions.append(f"priority = ${param_count}")
params.append(filters.priority)
}
if filters.tags and filters.tags.length > 0 {
param_count += 1
conditions.append(f"tags && ${param_count}")
params.append(filters.tags)
}
if filters.search {
param_count += 1
conditions.append(f"to_tsvector('english', title || ' ' || COALESCE(description, '')) @@ plainto_tsquery('english', ${param_count})")
params.append(filters.search)
}
let order_by = "ORDER BY created_at DESC"
if filters.sort == "title" {
order_by = "ORDER BY title"
} elif filters.sort == "due_date" {
order_by = "ORDER BY due_date NULLS LAST"
} elif filters.sort == "priority" {
order_by = "ORDER BY CASE priority WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 WHEN 'low' THEN 4 END"
}
let query = f"""
SELECT * FROM tasks
WHERE {conditions.join(' AND ')}
{order_by}
"""
let rows = await self.database.query(query, params)
return rows.map(row => self.map_to_domain_entity(row))
}
async find_overdue_tasks(organization_id = null) {
let conditions = ["due_date < NOW()", "status NOT IN ('completed', 'cancelled')"]
let params = []
if organization_id {
conditions.append("project_id IN (SELECT id FROM projects WHERE organization_id = $1)")
params.append(organization_id)
}
let query = f"""
SELECT t.* FROM tasks t
JOIN projects p ON t.project_id = p.id
WHERE {conditions.join(' AND ')}
ORDER BY due_date ASC
"""
let rows = await self.database.query(query, params)
return rows.map(row => self.map_to_domain_entity(row))
}
map_to_domain_entity(row) {
let task = Task(
project_id: row.project_id,
title: row.title,
reporter_id: row.reporter_id
)
task.id = row.id
task.description = row.description or ""
task.status = row.status
task.priority = row.priority
task.assignee_id = row.assignee_id
task.due_date = row.due_date
task.estimated_hours = row.estimated_hours
task.actual_hours = row.actual_hours
task.created_at = row.created_at
task.updated_at = row.updated_at
task.tags = row.tags or []
return task
}
}
Andre's Portfolio Project Guidance
"We're building something real here. TaskFlow Pro isn't just an exerciseβit's a demonstration of professional software craftsmanship. Every line of code, every architectural decision, and every user interaction should reflect the standards you'd apply to a production system that thousands of users depend on."
Development Principles for This Project
- Production Mindset: Write code as if it's going to production tomorrow
- User-Centric Design: Every feature should solve a real user problem
- Performance First: Optimize for speed and efficiency from day one
- Security by Design: Build security into every layer of the application
- Maintainable Architecture: Code that your future self (and teammates) will thank you for
Coming Up in the Next Sections
- Section 3: Building the REST API with authentication and authorization
- Section 4: Creating the modern web frontend with real-time updates
- Section 5: Implementing file uploads and attachment management
- Section 6: Adding comprehensive testing and monitoring
- Section 7: Deployment and production setup
Progress Check: You've now designed the complete domain model and data layer for TaskFlow Pro. This foundation will support all the features we'll build in the remaining sections. The architecture we've established follows professional best practices and will scale with the application's growth.
Remember: A great application starts with a solid foundation. The domain model and data layer you've built here will make everything else easier to implement and maintain.