A flexible task management database schema designed for productivity and project tracking applications. Includes user accounts, project organization, tasks with priority and status tracking, a label system with many-to-many task-label associations, and due date management. Suitable for apps like Todoist, Trello, or custom task management tools.
Everything here belongs to one person. projects and labels both key on user_id, and tasks reach the user through their project — so this is a personal task app rather than a shared one, and there is no membership table because there is nobody to share with.
Labels are rows while a task's status is a column, and that difference is deliberate. A task has exactly one status and any number of labels, so one is an attribute and the other is a many-to-many through task_labels.
is_completed and completed_at sit beside status rather than inside it. The flag is what a checkbox toggles and what every list filters on; the timestamp is what makes it possible to say what was finished this week, which a status value alone cannot.
Comma-separated tags on the task would be one less table and would make renaming a label a scan-and-rewrite, with nothing preventing two spellings of the same tag. The join costs a query to show a task's labels and makes filtering by label an indexed lookup — which, in an app whose main view is a filtered list, is where the work actually happens.
is_completed, completed_at and status overlap, and each answers a different question: whether to strike it through, when it was done, and which column it sits in. Deriving the flag from the status would work until a status set grows a value like cancelled, which is neither open nor completed. The cost is three columns that must be written consistently.
There is no self-reference here, so subtasks are not representable — a checklist has to become its own tasks in the same project. That keeps every list query flat and every completion count honest, which is the right trade for a simple app and the first limitation anyone hits when a task turns out to have three steps.
is_archived keeps the project and its tasks while removing them from the sidebar. Deleting would cascade away the record of what was done, which is most of what a task app accumulates. It also means the archive is just a filter — no second table, no restore path to maintain.