Real Time Client Project Dashboard Database Schema

I designed the relational schema for the Real‑Time Client Project Dashboard to manage Users, Projects, Tasks, and activity logs.

The ER diagram shows a one‑to‑many relationship between User and Project (manager relationship), one‑to‑many between Project and Task, and one‑to‑many between Task and TaskActivityLog.

I created an index on Task.assignedToId because Essential for O(1) index scans on Developer task list queries (WHERE assignedToId = req.user.id).

I created an index on Task.projectId because Powers fast retrieval of tasks when filtering or loading project Kanban boards.

I created an index on Task.status because Optimizes status filtering (status = 'IN_REVIEW') and Kanban column partitioning.

I created an index on Task.dueDate because Used by the scheduled background cron job to quickly identify candidates (dueDate < NOW() AND status != 'DONE').

I created an index on Project.managerId because Essential for Project Manager query scoping (WHERE managerId = req.user.id).

I created a composite index on TaskActivityLog.taskId + timestamp because Supports fast retrieval of recent activity timelines for specific tasks.

I created a composite index on Notification.userId + isRead because Optimizes live unread notification badge counters and dropdown rendering.