I see two choices to keep track of the types:
Store the type explicitly in each task and add database constraints to check that all child tasks have a compatible type. If available, enum types would be appropriate.
Let the type be implicit in the table/collection which holds these tasks. E.g. you might have a goal-table and a routine-table, not a single tasks-table.
To implement the tree structure, the pure relational approach would be to have in each child a reference to the parent task. For root tasks this reference will be null.
CREATE TABLE IF NOT EXISTS tasks (
id INT PRIMARY KEY,
parent REFERENCES tasks(id)
This works but is not very comfortable to work with – to fetch all subtasks you need a recursive query. Your DB may have extensions for hierarchical queries.
Ordering is also not trivial. The task could have an
order column which supports decimal numbers. To insert a task between two existing tasks you average the order of the neighbors. After a number of insertions you might run into numeric accuracy issues and will have to recalculate the order. Again, the database may have extensions to simplify this.
Some databases (in particular document databases) allow you to simply keep a list of child task IDs, which simplifies ordering and child queries. However, this might make it impossible or at least more difficult to enforce foreign key constraints. E.g. PostgreSQL supports array columns, but AFAIK you have to check the foreign key constraint manually.