How to Design a Database Schema for a Project Management Tool

Introduction

Project management tools like Jira, Asana, and Linear all share the same core data model: workspaces contain projects, projects contain tasks, and tasks move through a workflow while being assigned, labelled, discussed, and tracked. Getting that model right is what separates a tool that scales to thousands of tasks from one that buckles under its own joins.

In this post we'll design the full schema for that kind of tool in PostgreSQL — with real support for sprints, subtasks, multi-assignee tasks, per-workspace labels, comment threads, file attachments, and a complete activity history.

Entities and Relationships

The system is built around twelve tables:

• workspaces — the top-level tenant boundary
• users — individual accounts
• workspace_members — users joined to a workspace with a role
• projects — belong to a workspace
• sprints — time-boxed iterations within a project
• tasks — the core unit of work
• task_assignees — many-to-many between tasks and users
• labels — workspace-scoped tags
• task_labels — many-to-many between tasks and labels
• comments — threaded discussion on a task
• attachments — files uploaded to a task
• activity_log — audit trail of everything that happened to a task

Key relationships:

• A workspace has many projects, members, and labels
• A project has many sprints and many tasks
• A task optionally belongs to one sprint and optionally has a parent task (for subtasks)
• A task can have many assignees, many labels, many comments, and many attachments
• A user creates tasks, is assigned to tasks, and leaves comments
• Every meaningful state change on a task writes a row to activity_log

Key Tables

workspaces

• id (UUID, PK)
• name (VARCHAR, NOT NULL)
• slug (VARCHAR, UNIQUE, NOT NULL) — URL-friendly identifier
• owner_id (UUID, NOT NULL) — the user who created the workspace
• description (TEXT)
• created_at (TIMESTAMPTZ, NOT NULL)

workspace_members

• workspace_id (UUID, FK, PK part)
• user_id (UUID, FK, PK part)
• role (VARCHAR, CHECK IN OWNER, ADMIN, MEMBER, VIEWER)
• joined_at (TIMESTAMPTZ, NOT NULL)

Composite primary key (workspace_id, user_id) — a user appears at most once per workspace.

projects

• id (UUID, PK)
• workspace_id (UUID, FK, NOT NULL)
• name (VARCHAR, NOT NULL)
• key (VARCHAR(10), NOT NULL) — short prefix like ENG or OPS for task keys
• status (VARCHAR, CHECK IN ACTIVE, ARCHIVED, COMPLETED)
• default_assignee_id (UUID, FK, nullable)
• UNIQUE (workspace_id, key) — project keys are unique per workspace

sprints

• id (UUID, PK)
• project_id (UUID, FK, NOT NULL)
• name (VARCHAR, NOT NULL)
• goal (TEXT)
• start_date / end_date (DATE, NOT NULL)
• status (VARCHAR, CHECK IN PLANNED, ACTIVE, COMPLETED)
• Table-level check: end_date >= start_date

tasks

• id (UUID, PK)
• project_id (UUID, FK, NOT NULL)
• sprint_id (UUID, FK, nullable) — tasks in the backlog have no sprint
• parent_task_id (UUID, FK, nullable) — self-reference for subtasks
• title (VARCHAR, NOT NULL)
• description (TEXT)
• status (VARCHAR, CHECK IN TODO, IN_PROGRESS, IN_REVIEW, DONE)
• priority (VARCHAR, CHECK IN URGENT, HIGH, MEDIUM, LOW)
• story_points (INT, nullable)
• due_date (DATE, nullable)
• created_by (UUID, FK, NOT NULL)
• created_at / updated_at (TIMESTAMPTZ, NOT NULL)

task_assignees

• task_id (UUID, FK, PK part)
• user_id (UUID, FK, PK part)
• assigned_at (TIMESTAMPTZ, NOT NULL)

Junction table — a task can have multiple assignees, a user can be assigned to multiple tasks.

labels

• id (UUID, PK)
• workspace_id (UUID, FK, NOT NULL) — labels are workspace-scoped, not project-scoped
• name (VARCHAR, NOT NULL)
• color (VARCHAR(7), default #6B7280) — hex colour
• UNIQUE (workspace_id, name)

comments

• id (UUID, PK)
• task_id (UUID, FK, NOT NULL, ON DELETE CASCADE)
• user_id (UUID, FK, NOT NULL)
• content (TEXT, NOT NULL)
• created_at (TIMESTAMPTZ, NOT NULL)
• edited_at (TIMESTAMPTZ, nullable) — null until the author edits

activity_log

• id (UUID, PK)
• task_id (UUID, FK, NOT NULL)
• user_id (UUID, FK, NOT NULL)
• action (VARCHAR, CHECK IN CREATED, STATUS_CHANGED, ASSIGNED, UNASSIGNED, COMMENTED, ATTACHMENT_ADDED, PRIORITY_CHANGED, MOVED_SPRINT)
• old_value / new_value (VARCHAR, nullable) — text snapshots of what changed
• created_at (TIMESTAMPTZ, NOT NULL)

Example Queries

Current sprint board — tasks grouped by status

Returns every task in the active sprint of a project, grouped by status column, ordered by priority within each column.

SELECT
  t.status,
  COUNT(*)                           AS task_count,
  STRING_AGG(t.title, ', ' ORDER BY
    CASE t.priority
      WHEN 'URGENT' THEN 1
      WHEN 'HIGH'   THEN 2
      WHEN 'MEDIUM' THEN 3
      WHEN 'LOW'    THEN 4
    END
  )                                  AS tasks
FROM tasks t
JOIN sprints s ON t.sprint_id = s.id
WHERE s.project_id = 'your-project-uuid-here'
  AND s.status     = 'ACTIVE'
GROUP BY t.status
ORDER BY
  CASE t.status
    WHEN 'TODO'        THEN 1
    WHEN 'IN_PROGRESS' THEN 2
    WHEN 'IN_REVIEW'   THEN 3
    WHEN 'DONE'        THEN 4
  END;

Overdue tasks across a workspace, with assignees

Finds every task whose due_date has passed and which is not yet DONE, joined across projects and assignees so you can surface them in a digest.

SELECT
  p.name                          AS project_name,
  t.title                         AS task_title,
  t.priority,
  t.due_date,
  CURRENT_DATE - t.due_date       AS days_overdue,
  STRING_AGG(u.full_name, ', ')   AS assignees
FROM tasks t
JOIN projects p              ON t.project_id = p.id
LEFT JOIN task_assignees ta  ON t.id = ta.task_id
LEFT JOIN users u            ON ta.user_id = u.id
WHERE p.workspace_id = 'your-workspace-uuid-here'
  AND t.status      <> 'DONE'
  AND t.due_date     < CURRENT_DATE
GROUP BY p.name, t.id, t.title, t.priority, t.due_date
ORDER BY t.due_date ASC;

Sprint velocity — story points completed per sprint

Sums story_points from DONE tasks per sprint so you can plot velocity over time.

SELECT
  s.name                                                         AS sprint_name,
  s.start_date,
  s.end_date,
  COUNT(t.id) FILTER (WHERE t.status = 'DONE')                   AS tasks_completed,
  COALESCE(SUM(t.story_points) FILTER (WHERE t.status = 'DONE'), 0) AS points_completed,
  COALESCE(SUM(t.story_points), 0)                               AS points_committed
FROM sprints s
LEFT JOIN tasks t ON t.sprint_id = s.id
WHERE s.project_id = 'your-project-uuid-here'
  AND s.status     = 'COMPLETED'
GROUP BY s.id, s.name, s.start_date, s.end_date
ORDER BY s.end_date DESC
LIMIT 10;

Extensibility

This schema is a solid foundation. Natural next extensions:

• Custom fields — a custom_field_definitions table per project plus task_custom_values to store per-task values without ALTER TABLE every time a team adds a field
• Time tracking — a time_entries table (task_id, user_id, started_at, duration_minutes, description) for per-task logged work
• Automations — a workflow_rules table describing triggers (when status changes to X) and actions (assign to Y, add label Z)
• Task dependencies — a task_dependencies junction (task_id, depends_on_task_id, type) to model blocks/related-to/duplicates relationships beyond parent/subtask
• Templates — a project_templates table with a serialized list of starter tasks and labels, letting teams spin up a new project preloaded
• Git integration — a task_commits table (task_id, commit_sha, repo_url, message) populated by a webhook when a commit references a task key
• Saved views — per-user saved_views rows storing filter JSON, so each team member can save their own "My overdue high-priority tasks" query