TaskFlow — Lightweight Full-Stack Kanban Task Board

TaskFlow is a lightweight full-stack Kanban task management application designed for small teams, inspired by tools such as Trello.

It provides a persistent task board with multiple columns, task creation and editing, task deletion, priority filtering, search, task movement, and database-backed persistence.

---

🚀 Tech Stack

Frontend

• React
• TypeScript
• Vite
• Custom CSS
• HTML5 Drag and Drop API

Backend

• Node.js
• Express
• TypeScript

Database

• SQLite
• sql.js
• Raw SQL queries

Testing

• Vitest
• Supertest

---

🌐 Live Demo

Live Application: TODO — Add deployed frontend URL

Backend API: TODO — Add deployed backend URL

GitHub Repository: TODO — Add GitHub repository URL

The live URLs will be added after deployment.

---

🚀 Setup & Installation

Follow these steps to run TaskFlow locally from a fresh clone.

Prerequisites

• Node.js: v18.x or higher
• npm: v9.x or higher

Check your installed versions:

node --version
npm --version

1. Clone the Repository

git clone <repository-url>
cd taskflow

2. Install Dependencies

Install dependencies for both the backend and frontend:

npm run install:all

Alternatively, install them separately:

cd backend
npm install

cd ../frontend
npm install

3. Start the Backend

Open a terminal and run:

cd backend
npm run dev

The Express API will start at:

http://localhost:5000

The SQLite database is initialized automatically when the backend starts. The schema and seed data are provided in:

backend/src/db/schema.sql
backend/src/db/seed.sql

4. Start the Frontend

Open a second terminal:

cd frontend
npm run dev

The frontend will be available at:

http://localhost:3000

Open the URL in your browser.

---

🧪 Running Automated Tests

The backend test suite covers API validation, task movement, and direct database-layer queries.

From the backend directory:

cd backend
npm test

Test Coverage

1. Empty Title Validation Verifies that creating or editing a task with an empty or whitespace-only title returns HTTP 400.

2. Task Movement Verifies that moving a task updates its column_id correctly.

3. Task Count SQL Query Executes the task-count query against known seed data and verifies the returned counts, including columns containing zero tasks.

4. Priority and Search Filtering Verifies database-level filtering by task priority and keyword search.

---

🗄️ Database Architecture

TaskFlow uses SQLite as a relational database.

The data model follows:

Board
  │
  ├── Column
  │     ├── Task
  │     ├── Task
  │     └── Task
  │
  ├── Column
  │     └── Task
  │
  └── Column
        └── Task

boards

Field	Description
id	INTEGER primary key
name	Board name, required
created_at	Creation timestamp

columns

Field	Description
id	INTEGER primary key
board_id	Foreign key → boards(id)
name	Column name, required
position	Column ordering
created_at	Creation timestamp

tasks

Field	Description
id	INTEGER primary key
column_id	Foreign key → columns(id)
title	Required task title
description	Optional description
priority	Low, Medium, or High
position	Task ordering within a column
created_at	Creation timestamp

Foreign-key relationships use cascading deletes where appropriate.

The complete schema is available in:

backend/src/db/schema.sql

Seed data is available in:

backend/src/db/seed.sql

---

📊 Custom SQL Queries

TaskFlow uses raw SQL for database-level operations rather than fetching all records and performing filtering entirely in the frontend.

1. Task Count Per Column

The task-count query uses a LEFT JOIN and COUNT(t.id) so columns with no tasks are still included in the results.

SELECT
  c.id AS column_id,
  c.name AS column_name,
  c.position,
  COUNT(t.id) AS task_count
FROM columns c
LEFT JOIN tasks t ON c.id = t.column_id
WHERE c.board_id = ?
GROUP BY c.id, c.name, c.position
ORDER BY c.position ASC;

This query is used to display the number of tasks in each board column.

2. Filtered and Searched Tasks

Task filtering is implemented at the database layer.

The task query joins tasks with columns, restricts results to the requested board, and applies the requested priority and search conditions.

The results are ordered by creation time:

ORDER BY t.created_at DESC, t.id DESC;

The implementation is available in:

backend/src/db/queries.ts

This approach avoids retrieving all tasks and performing the filtering only in the React application.

---

🔌 API Endpoints

Get Board

GET /api/boards/:id

Returns the board, its columns, and associated tasks.

Get Filtered Tasks

GET /api/tasks

Supported query parameters:

boardId
priority
search

Example:

/api/tasks?boardId=1&priority=High

Create Task

POST /api/tasks

Example request:

{
  "column_id": 1,
  "title": "Build API",
  "description": "Create the required REST endpoints",
  "priority": "High"
}

Update Task

PUT /api/tasks/:id

Example request:

{
  "title": "Updated task title",
  "description": "Updated description",
  "priority": "Medium"
}

Move Task

PATCH /api/tasks/:id/move

Example request:

{
  "column_id": 2,
  "position": 0
}

Delete Task

DELETE /api/tasks/:id

Get Task Statistics

GET /api/boards/:id/stats

Returns task counts for each column using the custom SQL aggregation query.

---

✅ Validation & Error Handling

TaskFlow performs validation on the backend to prevent invalid data from reaching the database.

For example, an empty or whitespace-only task title is rejected with HTTP 400:

{
  "success": false,
  "error": "Task title is required and cannot be empty."
}

The backend also validates:

• Board IDs
• Task IDs
• Column IDs
• Target columns
• Task titles
• Task priorities

API errors follow a consistent structure:

{
  "success": false,
  "error": "Error description"
}

This allows the frontend to display meaningful error messages instead of exposing raw server errors.

---

🎨 User Interface

The application provides a clean Kanban-style interface with:

• Separate task columns
• Task priority badges
• Task search
• Priority filtering
• Create/edit task modal
• Task deletion
• HTML5 drag-and-drop
• Quick-move dropdown fallback
• Task counts per column
• Error notifications
• Responsive layout

The implementation focuses on functionality and usability rather than unnecessary visual complexity.

---

📝 Design Decisions, Assumptions & Trade-offs

1. Single Board Context

The current application uses a default primary board:

board_id = 1

The database schema supports relationships between multiple boards, allowing the application to be extended to support multiple boards in the future.

2. SQLite

SQLite was selected because it provides a real relational database without requiring a separate database server.

This keeps local setup simple while still demonstrating:

• Primary keys
• Foreign keys
• Constraints
• SQL joins
• Aggregation
• Parameterized queries
• Database persistence

SQLite is accessed through sql.js using a small database wrapper for database initialization, SQL execution, and test isolation.

3. Task Movement

Task movement supports two mechanisms:

• HTML5 drag-and-drop
• Quick-move dropdown

The dropdown provides a reliable fallback when drag-and-drop is unavailable or inconvenient.

4. Database-Level Filtering

Priority and search filtering are performed through SQL rather than downloading all tasks and filtering them entirely in React.

This keeps data filtering within the database layer and demonstrates practical SQL usage.

---

🔮 Future Improvements

If more development time were available, the following improvements could be added:

• Column creation, renaming, deletion, and reordering
• Task activity history
• User accounts and task assignment
• Tags and advanced filtering
• More advanced task ordering
• Real-time updates
• Persistent production database storage
• Additional frontend automated tests

These features were intentionally not prioritized because the core assignment focuses on a reliable task board.

---

⏱️ Time Spent

Approximately 3.5 hours were spent building the project.

Area	Approximate Time
Setup & schema design	30 minutes
Backend API, SQL & tests	1 hour
Frontend UI, drag-and-drop & filtering	1.5 hours
Testing, debugging & documentation	30 minutes
Total	~3.5 hours

---

💡 Interesting Learning

One interesting part of the implementation was working with LEFT JOIN and aggregation in SQLite.

When counting tasks per column, using:

COUNT(t.id)

correctly returns 0 for a column containing no tasks.

In contrast, using:

COUNT(*)

would count the row produced by the LEFT JOIN, even when the task columns are NULL.

This reinforced the importance of choosing the correct aggregate expression when working with outer joins.

---

📋 Assignment Requirements Coverage

Requirement	Status
Board → Columns → Tasks relational model	✅
Create task	✅
Edit task	✅
Delete task	✅
Move task between columns	✅
Backend/database persistence	✅
Priority filtering	✅
Task title search	✅
Backend empty-title validation	✅
Error handling	✅
SQLite database	✅
Database schema file	✅
Seed data	✅
Custom SQL query #1	✅
Custom SQL query #2	✅
Backend tests	✅
README/setup instructions	✅
Live deployment	🔄 Add URL before submission

---

📄 License

This project was created as a take-home assignment for evaluation purposes.