tasks

name	tasks
description	Projects, tasks, and task dependencies for tracking work

Tasks

Projects, tasks, and blocking dependencies for organizing and tracking work items across an organization.

Tables

projects

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations. CASCADE on delete
owner_id	uuid	References users. SET NULL on delete
name	text	Project name (required)
description	text	Optional project description
status	task_status	Current status, defaults to 'todo'
priority	task_priority	Priority level, defaults to 'medium'
starts_at	date	Planned start date
due_at	date	Target completion date
completed_at	timestamptz	When the project was completed
created_at	timestamptz	Row creation time
updated_at	timestamptz	Auto-updated on change via trigger
metadata	jsonb	Freeform JSON, defaults to empty object

tasks

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations. CASCADE on delete
project_id	uuid	References projects. CASCADE on delete. Optional
assignee_id	uuid	References users. SET NULL on delete
creator_id	uuid	References users. SET NULL on delete
title	text	Task title (required)
description	text	Optional task description
status	task_status	Current status, defaults to 'todo'
priority	task_priority	Priority level, defaults to 'medium'
due_at	date	Target completion date
completed_at	timestamptz	When the task was completed
position	integer	Sort order within project, defaults to 0
created_at	timestamptz	Row creation time
updated_at	timestamptz	Auto-updated on change via trigger
metadata	jsonb	Freeform JSON, defaults to empty object

task_dependencies

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations. CASCADE on delete
task_id	uuid	The blocked task. References tasks. CASCADE on delete
depends_on_id	uuid	The blocking task. References tasks. CASCADE on delete
created_at	timestamptz	Row creation time
updated_at	timestamptz	Auto-updated on change via trigger
metadata	jsonb	Freeform JSON, defaults to empty object

Constraints: (task_id, depends_on_id) is unique, and a task cannot depend on itself.

Enums

task_status

Value	Description
todo	Not yet started
in_progress	Work is underway
in_review	Waiting for review
done	Completed
cancelled	Cancelled, will not be done

task_priority

Value	Description
low	Low priority
medium	Medium priority (default)
high	High priority
urgent	Needs immediate attention

Row-Level Security

All three tables have RLS enabled and scoped to org_id via get_user_org_id(). SELECT, INSERT, and UPDATE are open to all org members. DELETE requires admin privileges via is_admin().

Dependencies

• identity (organizations, users)

Example Queries

-- Overdue tasks for a user
SELECT
  t.title,
  t.priority,
  t.due_at,
  p.name AS project
FROM tasks t
LEFT JOIN projects p ON p.id = t.project_id
WHERE t.assignee_id = '<user_id>'
  AND t.status NOT IN ('done', 'cancelled')
  AND t.due_at < current_date
ORDER BY t.due_at ASC;

-- Project progress summary
SELECT
  p.name,
  count(*) FILTER (WHERE t.status = 'done') AS completed,
  count(*) FILTER (WHERE t.status NOT IN ('done', 'cancelled')) AS remaining,
  round(
    100.0 * count(*) FILTER (WHERE t.status = 'done') / nullif(count(*), 0),
    0
  ) AS percent_complete
FROM projects p
JOIN tasks t ON t.project_id = p.id
GROUP BY p.id, p.name;

-- Tasks blocked by incomplete dependencies
SELECT
  t.title AS blocked_task,
  dep.title AS blocking_task,
  dep.status AS blocking_status
FROM task_dependencies td
JOIN tasks t ON t.id = td.task_id
JOIN tasks dep ON dep.id = td.depends_on_id
WHERE dep.status NOT IN ('done', 'cancelled')
ORDER BY t.title;

-- Workload per assignee across active projects
SELECT
  u.full_name,
  count(*) FILTER (WHERE t.priority = 'urgent') AS urgent,
  count(*) FILTER (WHERE t.priority = 'high') AS high,
  count(*) FILTER (WHERE t.priority = 'medium') AS medium,
  count(*) FILTER (WHERE t.priority = 'low') AS low,
  count(*) AS total
FROM tasks t
JOIN users u ON u.id = t.assignee_id
WHERE t.status NOT IN ('done', 'cancelled')
GROUP BY u.id, u.full_name
ORDER BY count(*) FILTER (WHERE t.priority = 'urgent') DESC, total DESC;

-- Recently completed tasks with time to completion
SELECT
  t.title,
  t.priority,
  p.name AS project,
  t.completed_at,
  extract(epoch FROM t.completed_at - t.created_at) / 86400 AS days_to_complete
FROM tasks t
LEFT JOIN projects p ON p.id = t.project_id
WHERE t.status = 'done'
  AND t.completed_at >= now() - interval '7 days'
ORDER BY t.completed_at DESC;