TaskFlow

Live demo: https://taskflow-odyo.onrender.com/ (free-tier host — the first load after a period of inactivity can take 30–50s to wake up; see Deployment for why)

A small Trello-style task board — a Board containing Columns, each holding Tasks. Built as a take-home assignment: React frontend, Node/Express backend, SQLite database.

Board ("My Team Board")
 ├── To Do
 ├── In Progress
 └── Done

Tech stack

Layer	Choice
Frontend	React 19 + TypeScript + Vite
Backend	Node.js + Express
Database	SQLite via better-sqlite3 (synchronous, no ORM — see Database)
Tests	Vitest + Supertest

Project structure

taskflow/
├── backend/
│   ├── src/
│   │   ├── db/
│   │   │   ├── schema.sql       ← table definitions
│   │   │   ├── connection.js    ← opens the SQLite file, applies schema.sql
│   │   │   ├── queries.js       ← every SQL query the app runs, in one place
│   │   │   └── seed.js          ← wipes + repopulates demo data
│   │   ├── routes/               ← Express route handlers (thin — call queries.js)
│   │   ├── app.js                ← Express app wiring, error handling, serves built frontend
│   │   └── server.js             ← entry point
│   └── tests/                    ← Vitest suite (see Tests below)
└── frontend/
    └── src/
        ├── api/client.ts         ← typed fetch wrapper, one function per endpoint
        ├── components/           ← Board, Column, TaskCard, TaskModal, etc.
        ├── types.ts
        └── App.tsx                ← owns board state, wires components to the API

Setup (from a fresh clone)

Requires Node.js 18+.

1. Backend

cd backend
npm install
npm run seed     # creates backend/src/db/taskflow.sqlite and fills it with demo data
npm run dev       # starts the API on http://localhost:4000

2. Frontend

In a second terminal:

cd frontend
npm install
npm run dev       # starts the app on http://localhost:5173

Open http://localhost:5173 — the Vite dev server proxies /api/* requests to the backend, so no CORS setup is needed locally.

3. Running the tests

cd backend
npm test

11 tests covering: title validation, moving a task, and both required database queries against known seed data. See Tests below for the required-vs-extra breakdown.

Environment variables

None required for local dev. TASKFLOW_DB_PATH can override where the SQLite file lives (used by the test suite to point at a throwaway file instead of the real dev database); PORT overrides the backend port (default 4000).

Production build (single-service deploy)

The backend serves the built frontend as static files, so the whole app can run as one deployable service:

cd frontend && npm run build   # outputs frontend/dist
cd ../backend && npm start      # serves the API + the built frontend on the same port

Deployment

Deployed on Render as a single web service (repo root, not either subfolder):

Setting	Value
Root Directory	(blank — repo root)
Build Command	npm install --prefix frontend && npm run build --prefix frontend && npm install --prefix backend
Start Command	npm start --prefix backend
Health Check Path	/api/health
Environment variables	none — Render injects PORT, which server.js already reads

Known trade-off: Render's free tier has no persistent disk — the container's filesystem resets on every cold start (the service spins down after ~15 min idle). That would normally mean a woken-up instance boots with an empty, broken board. To handle this, server.js calls seedIfEmpty() on startup (backend/src/db/seed.js): a truly empty database gets auto-seeded, but a database that already has data (e.g. tasks a reviewer created) is left untouched. In practice this means data persists normally while the instance is warm, and resets to the seed set after a period of inactivity — a reasonable trade-off for a free demo host, not something a paid tier with a persistent disk would need.

Database

Schema

CREATE TABLE boards (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE columns (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  board_id   INTEGER NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  position   INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE tasks (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  column_id   INTEGER NOT NULL REFERENCES columns(id) ON DELETE CASCADE,
  title       TEXT NOT NULL CHECK (trim(title) != ''),
  description TEXT,
  priority    TEXT NOT NULL DEFAULT 'Medium' CHECK (priority IN ('Low', 'Medium', 'High')),
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_tasks_column_id ON tasks(column_id);
CREATE INDEX idx_tasks_priority ON tasks(priority);
CREATE INDEX idx_columns_board_id ON columns(board_id);

Full file: backend/src/db/schema.sql. Foreign keys are enforced — connection.js runs PRAGMA foreign_keys = ON on every connection, and ON DELETE CASCADE means deleting a board/column cleans up its children.

The two required non-trivial queries

Both live in backend/src/db/queries.js, written as raw SQL (no ORM query-builder magic) so the actual query is visible and reviewable:

1. Count of tasks per column, on a board:

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

Uses a LEFT JOIN (not INNER JOIN) so a column with zero tasks still shows up with a count of 0, rather than disappearing from the result. Exposed at GET /api/boards/:id/task-counts.

2. Tasks of a given priority, newest first:

SELECT t.*
FROM tasks t
JOIN columns c ON c.id = t.column_id
WHERE c.board_id = ? AND t.priority = ?
ORDER BY t.created_at DESC, t.id DESC

Joins through columns to scope by board, since tasks doesn't store board_id directly (it belongs to a column, which belongs to a board — no denormalization). Exposed at GET /api/boards/:id/tasks?priority=High.

Both are exercised directly against the database (not through the HTTP layer) in backend/tests/db-queries.test.js.

Seed data

npm run seed (in backend/) wipes and recreates: one board ("My Team Board"), three columns (To Do / In Progress / Done), and five tasks spread across them. Safe to re-run any time — it resets the SQLite autoincrement counters too, so IDs stay predictable across re-seeds.

API

Method	Path	Description
GET	/api/boards	The board (single-board app), with columns and tasks nested
GET	/api/boards/:id/task-counts	Required query #1
GET	/api/boards/:id/tasks?priority=High	Required query #2
POST	/api/tasks	Create a task ({ columnId, title, description?, priority? }) — rejects empty title
PUT	/api/tasks/:id	Edit a task's title/description/priority
DELETE	/api/tasks/:id	Delete a task
PATCH	/api/tasks/:id/move	Move a task to a different column ({ columnId })

All error responses are JSON: { "error": "message" }, with an appropriate 4xx/5xx status.

Tests

Backend tests (cd backend && npm test), 11 total across 3 files:

• tests/task-validation.test.js — (required #1) creating a task with an empty/whitespace/missing title is rejected with a 400; a real title succeeds.
• tests/task-move.test.js — (required #2) moving a task updates its column_id, verified by reading straight back from the database (not just trusting the HTTP response); also covers moving into a nonexistent column and moving a nonexistent task.
• tests/db-queries.test.js — (required #3) hits getTaskCountsPerColumn and getTasksByPriority directly against known seeded fixture data, including an edge case (a column with zero tasks still appears in the count).

Each test file spins up an isolated, throwaway SQLite file (via TASKFLOW_DB_PATH) so tests never touch the real dev database.

Decisions & assumptions

• Single board, no board-switching UI. The assignment scopes out multi-team/multi-board, so the backend always operates on "the" board (GET /api/boards returns the first one) rather than building a board list/selector that has nothing to select between.
• Move via dropdown, not drag-and-drop — per the assignment's own guidance that a working dropdown beats a broken drag-and-drop within a time-boxed submission.
• No ORM. Used better-sqlite3 directly and hand-wrote every query in queries.js, since the assignment specifically wants to see real SQL rather than Model.findAll()-style calls.
• Refetch-after-mutation on the frontend, rather than optimistic local updates. Every create/edit/ delete/move calls the API, then re-fetches the whole board. Simpler and it doubles as a live demonstration that changes actually persisted server-side, at the cost of an extra round-trip per action — fine at this scale.
• Single deployable service. The backend serves the built frontend as static files instead of deploying two separate services, to reduce moving parts (CORS, two hosts, two sets of env vars) within the time budget.
• Priority defaults to "Medium" if omitted on creation, since the schema requires a non-null value and the assignment says priority is optional on the create form.

What I'd improve with more time

• Drag-and-drop as the stretch goal (dropdown works, but DnD is nicer)
• Text search by title (explicitly listed as a nice-to-have)
• Optimistic UI updates instead of refetch-after-mutation, for snappier perceived performance
• Column reordering / creating new columns from the UI (currently fixed at seed time)
• Toast-style transient notifications instead of a persistent error banner

Time spent & notes

Time spent: roughly 4–5 hours, spread across schema/backend design, the React UI, tests, and getting a working deploy.

A few things that came up along the way that felt worth mentioning:

• better-sqlite3 + Vitest don't mix with the default test runner settings. Running the suite crashed two of three test files with a native V8 stack trace on worker teardown. Turned out Vitest's default pool runs each test file in a worker_thread, and better-sqlite3's native SQLite handles don't survive that thread's teardown cleanly. Fix was one line — force the forks pool in vitest.config.js so each test file runs in its own child process instead — but tracking down why a native module was crashing a JS test runner was a good reminder that "it's an ORM-free raw-SQL library" has a real trade-off: you also own its native-addon quirks.
• Free-tier hosting quietly breaks the "seed data on first run" requirement. Render's free plan has no persistent disk, so the filesystem resets whenever the service spins down from inactivity. A npm run seed step in the build command isn't enough, because that only runs once at build time — not on every cold start. Ended up moving to a seedIfEmpty() check that runs at server boot instead of build time, which only seeds when the boards table is actually empty, so it doesn't clobber real data if the container happens to still be warm.
• SQLite's AUTOINCREMENT doesn't reuse ids after a delete. Re-running the seed script wiped and reinserted the demo board, but the board id kept climbing (2, 3, 4...) each time instead of resetting to 1, since sqlite_sequence tracks the high-water mark independently of what rows currently exist. Small thing, but it's exactly the kind of detail that would've quietly broken a hardcoded board id = 1 assumption on the frontend — which is also why the API exposes GET /api/boards (first board) instead of requiring the frontend to know an id at all.