A comprehensive project management database schema for building tools like Asana, Linear, or Jira. Features workspace-based multi-tenancy, project organization with customizable statuses, hierarchical task management with priority and due dates, threaded task comments for collaboration, and team member roles. Supports agile workflows with sprint-ready task tracking and team workload management.
Everything is scoped to a workspace, and the scope is enforced one level up rather than repeated everywhere. members and projects carry workspace_id; tasks and task_comments do not, because they reach it through their project. That keeps the boundary in one place, at the cost of a join whenever you need to prove a task belongs to the workspace someone is signed in to.
People are members, not users. There is no separate account table here — members holds the email, the name and the role directly, scoped to one workspace. The same person working in two workspaces is two rows, which means a role is always workspace-local and there is no global permission to accidentally leak across tenants.
Subtasks are the same table pointing at itself. tasks.parent_task_id makes a checklist item and a top-level task structurally identical, so a subtask can be assigned, dated and prioritised like anything else — and a task can be promoted out of its parent by clearing one column instead of being moved between tables.
A separate subtasks table would be simpler to read and would immediately be wrong: teams nest two or three levels, and a subtask that turns out to be real work has to become a task. The self-reference makes both free. What it costs is that depth is unbounded — the application has to stop runaway nesting, because the schema will happily allow a chain a hundred long.
Labels here are free text on the task rather than rows in a labels table. For a tool where labels are per-task tags and are read far more often than they are aggregated, that avoids a join on the most common query. The trade is real: renaming a label everywhere becomes a scan and update, and there is nothing stopping two spellings of the same tag.
Manual ordering inside a column or list needs a per-task number, and putting it on the row keeps a board render to one sorted read. The known cost is reordering — dropping a task between two others needs a value between them, so the numbers are spaced and periodically renumbered rather than being consecutive integers.
Modelling a platform-wide account and a membership row is the correct shape for a product with cross-workspace identity. This schema deliberately does not: one row per person per workspace means role and avatar are always local, and a query can never accidentally return someone from another tenant. If single sign-on across workspaces is later required, this is the decision that has to be revisited.