Task management: some options for implementing repetitive tasks
One of the motives for creating our "bicycle", i.e. betasked.ru task management system was the need for the most convenient accounting of repetitive tasks, since Google did not give us suitable solutions. The specificity of our tasks is that there are many of them and for the most part they are repeated with varying degrees of periodicity.
There are a lot of Web-based task management services on the Internet. Probably every second programmer who wants something special to suit his needs, searches the Internet for something suitable and, not finding the ideal, writes his own version. The “mortality” of task management systems applying for the mass market is also very high. It seems to me that there are two factors - a lot of competition and big segmentation - someone needs an emphasis on group work, someone needs to keep notes. As a result, those who want to please everyone, realize everything that is possible and it turns out that the resulting monster is hard-working.
This post was written with the aim of showing those who sooner or later undertake the implementation of repetitive tasks, the possible difficulties (at least those that we encountered) and options. A brief (and quite obvious, often forgotten) conclusion of the post: it’s not always worthwhile to solve the “head-on” task during development, often stop and analyze what you are doing and whether you are going there.
Looking ahead, I will say that the implementation of repetitive tasks took a lot of time and effort, but ultimately allowed me to come to a completely usable option, simultaneously solving many other difficult issues.
Those systems that we looked at that supported repetitive tasks had certain drawbacks, for some insignificant, but critical for us:
MyLifeOrganized turned out to be the most advanced system in terms of task repetition, but it is not a web application. C LeaderTask also did not grow together, although many approaches to the interface there are not bad.
So, the task was to make the tasks repetitive. It would seem that complicated? From the point of view of database design, there was a question of choosing one of the options:
We considered the second option as the most logical and optimal, but it is optimal only if there are few fields (well, or parameters) for the task, but if you need to maintain logic properly, then the amount of code, for example, database triggers, is very large. They wrote and wrote, stopped in time. The first option remained.
Its disadvantages were as follows:
In addition, the complexity of servicing the “chain” of repetitive tasks for both the user and the programmer did not suit. Here, for example, are small but time-consuming questions:
The issue of usability has become a separate issue - how to identify different instances of the task, except by date? Those. In the task list, we had a bunch of identical tasks that differ only in date. This can clearly be seen in the title picture of the post.
It became clear that it was necessary to allow the user to edit the name of each instance of the task. Previously, it was banned, because as you rename - then the devil will figure out where whose repetition is. Added to this possibility (of a separate change) for most of the main fields of the task. But this also did not really improve usability.
We decided that the best solution to make the tasks more visual would be the option with auto-substitution. That is, indicating in the task name, for example, “%% this.date.month %%”, this expression would be replaced by the name of the month when displayed on the screen. If we call the task “Reporting of ATP LLC to the tax for %% this.date.quarter.prev %% qv %% this.date.month.prev.year %% g”, then we get the following picture:
This was already a complete decision. Having piled up a bunch of options for possible expressions, we realized that this was only the beginning. Auto-substitution should work everywhere, when displayed on the screen, in the sent notifications, therefore they killed for the implementation of this "hack" for more than a month. We took it into account almost everywhere. But still, the number of settings for the task repetition function was too large. Too many manipulations were required to simply repeat the “Training” task every week.
In short, it turned out some kind of monstrous mechanism, which was difficult to maintain and was incomprehensible to the user without a help.
Therefore, we decided to leave this mechanism, and try to implement the repetition of tasks through triggers, because it was more interesting. There was nothing revolutionary in this, since triggers in relational and not only databases exist for a long time, but I wanted to see what came of it.
We decided to make such an implementation - even if for each specific user operation a specific action will be performed automatically with a task. That is, with each:
we can customize
To implement precisely the repetitions, it was necessary to make a customizable delay in the execution of the action, for example, after completing the task after 10 days, again make it unfulfilled.
Now it turned out to realize the repetition of the task through certain periods, counted from the moment of the previous execution. It became clear that the option with triggers is much more flexible, so the following "features" were added:
As a result, we got a rather flexible and customizable system, but returned again to the same problem - the user has to configure it for too long. To install a simple weekly repetition, too many manipulations had to be done.
To solve this problem, we added the ability to install triggers immediately on:
And it was precisely the possibility of installing triggers for tasks with a certain label that made life much easier for us. Now, the repetition of a specific task was reduced to:
All. Having set up for yourself once and for all the necessary principles of repetition (“Monthly on the 20th”, “Annually on March 8”, “Monday”), then you could just mark the necessary tasks with these labels and all the rules of repetition apply to them.
There were, of course, several unresolved issues, something was not completely implemented (for example, flexible adjustment of repetition periods), but for most practical tasks this was already enough, at the same time the very presentation of the tasks became much more visual. Yes, for the initial settings, you need to understand a little bit, editing triggers for labels is available through the Settings menu - Global Triggers, or by the Triggers context menu item for any task with this label. In the future, we will make life easier for new users with pre-configured labels for periodic tasks.
For those 1-2 users who need practical information about the implementation of triggers and setting up periodic repetition (and the need for it can be not only in task management, but also in scheduling, for example), I will describe schematically the technical side.
There are a lot of Web-based task management services on the Internet. Probably every second programmer who wants something special to suit his needs, searches the Internet for something suitable and, not finding the ideal, writes his own version. The “mortality” of task management systems applying for the mass market is also very high. It seems to me that there are two factors - a lot of competition and big segmentation - someone needs an emphasis on group work, someone needs to keep notes. As a result, those who want to please everyone, realize everything that is possible and it turns out that the resulting monster is hard-working.
This post was written with the aim of showing those who sooner or later undertake the implementation of repetitive tasks, the possible difficulties (at least those that we encountered) and options. A brief (and quite obvious, often forgotten) conclusion of the post: it’s not always worthwhile to solve the “head-on” task during development, often stop and analyze what you are doing and whether you are going there.
Looking ahead, I will say that the implementation of repetitive tasks took a lot of time and effort, but ultimately allowed me to come to a completely usable option, simultaneously solving many other difficult issues.
Those systems that we looked at that supported repetitive tasks had certain drawbacks, for some insignificant, but critical for us:
- either it was impossible to see future tasks
- either the mechanism for their configuration and use was too cumbersome
MyLifeOrganized turned out to be the most advanced system in terms of task repetition, but it is not a web application. C LeaderTask also did not grow together, although many approaches to the interface there are not bad.
So, the task was to make the tasks repetitive. It would seem that complicated? From the point of view of database design, there was a question of choosing one of the options:
- each instance of a repeating task is a separate task and a separate entry in the task table
- a template for a repeating task is stored separately, plus tasks for which the fields have been changed are separately stored. A similar storage mechanism is implemented in Google Calendar - there, for example, when changing the fields of an event, the question arises - change the entire chain, only subsequent events, or only one.
We considered the second option as the most logical and optimal, but it is optimal only if there are few fields (well, or parameters) for the task, but if you need to maintain logic properly, then the amount of code, for example, database triggers, is very large. They wrote and wrote, stopped in time. The first option remained.
Its disadvantages were as follows:
- a large amount of entries in the task table. For example, there is a task, it needs to be repeated every week, so there is a separate task for every Sunday. There were no default restrictions for the user by the end date of the repetitions. Those. when creating the “task schedule”, copies of it were created for the year ahead. But it’s also a variant of the curve - someone doesn’t need to plan so far, and someone doesn’t have enough years. Ok, we added the ability for the user to set how long the tasks will be created in advance.
- The approach itself, consisting in the fact that for each necessary date a separate instance of the task was created, was sometimes impractical. For example, why bother storing and forcing the user to consider a bunch of the same “Training” tasks of different dates?
In addition, the complexity of servicing the “chain” of repetitive tasks for both the user and the programmer did not suit. Here, for example, are small but time-consuming questions:
- when deleting tasks, it was necessary that when rebuilding the chain (for example, if the user changed the repeat settings), the remote task would not be recreated. After going through all the possible options, it turned out that the most suitable is to store information about remote tasks in a separate table. But experience has suggested that the allocation of a separate table for this information is a sign of a wrong approach and this will come back to fruition in the future. As a result, we made a parameter for the user saving changes in the schedule - to recreate remote tasks or not.
- the task chain must have a template, i.e. task, the repetition of which is configured by the user and which, in fact, is copied to all created new instances. Given that the task date is an optional parameter, it would be logical to make it possible to repeat a task that does not have a date, i.e. there will be a template task (without a date) and its repetitions (they already have their dates). But the practical use of this is rather inconvenient, because all this at work looks somehow cumbersome - a template, task instances. You won’t understand without a help.
- taking into account the tree-like nature of task lists, I wanted to not restrict the user and allow to distribute different instances of repeating tasks to different parent folders / tasks. But then another problem appeared - how the user would see the whole chain, or rather the problem was not just that, but the fact that these future instances of the task appeared in my opinion, not where the user expects this.
The issue of usability has become a separate issue - how to identify different instances of the task, except by date? Those. In the task list, we had a bunch of identical tasks that differ only in date. This can clearly be seen in the title picture of the post.
It became clear that it was necessary to allow the user to edit the name of each instance of the task. Previously, it was banned, because as you rename - then the devil will figure out where whose repetition is. Added to this possibility (of a separate change) for most of the main fields of the task. But this also did not really improve usability.
We decided that the best solution to make the tasks more visual would be the option with auto-substitution. That is, indicating in the task name, for example, “%% this.date.month %%”, this expression would be replaced by the name of the month when displayed on the screen. If we call the task “Reporting of ATP LLC to the tax for %% this.date.quarter.prev %% qv %% this.date.month.prev.year %% g”, then we get the following picture:
This was already a complete decision. Having piled up a bunch of options for possible expressions, we realized that this was only the beginning. Auto-substitution should work everywhere, when displayed on the screen, in the sent notifications, therefore they killed for the implementation of this "hack" for more than a month. We took it into account almost everywhere. But still, the number of settings for the task repetition function was too large. Too many manipulations were required to simply repeat the “Training” task every week.
In short, it turned out some kind of monstrous mechanism, which was difficult to maintain and was incomprehensible to the user without a help.
Therefore, we decided to leave this mechanism, and try to implement the repetition of tasks through triggers, because it was more interesting. There was nothing revolutionary in this, since triggers in relational and not only databases exist for a long time, but I wanted to see what came of it.
We decided to make such an implementation - even if for each specific user operation a specific action will be performed automatically with a task. That is, with each:
- task creation
- completing a task
- deleting a task
we can customize
- task creation (this function was temporarily disabled)
- marking the task as completed
- marking the task as outstanding
- delete task
To implement precisely the repetitions, it was necessary to make a customizable delay in the execution of the action, for example, after completing the task after 10 days, again make it unfulfilled.
Now it turned out to realize the repetition of the task through certain periods, counted from the moment of the previous execution. It became clear that the option with triggers is much more flexible, so the following "features" were added:
- trigger on a specific schedule. Those. triggered at regular intervals. The direct purpose is to “restore” tasks with a certain periodicity. For example, every Saturday to make the task "Purchase for a picnic" unfulfilled
- trigger that fires when the task date or due date (task completion date). Its main purpose was found for notifications (by that time we hadn’t taken up the implementation of notifications, and that was very useful), coupled with a custom delay, this made it possible for each task to be able to receive multiple notifications, for example, when the deadline was reached by e-mail with different degree of periodicity
- For each trigger, the actions “Move task date” were made possible. This allowed for those tasks for which the date is important, to shift it with each repetition. We did the same with the field “Task term”.
As a result, we got a rather flexible and customizable system, but returned again to the same problem - the user has to configure it for too long. To install a simple weekly repetition, too many manipulations had to be done.
To solve this problem, we added the ability to install triggers immediately on:
- all user tasks
- all tasks from a specific list
- all tasks with a specific label
And it was precisely the possibility of installing triggers for tasks with a certain label that made life much easier for us. Now, the repetition of a specific task was reduced to:
- setting triggers on a specific label. Those. the label “Weekly on Saturdays” was created, a trigger was set up for it, which marked all tasks with this label that were not completed on Saturdays. This can be done once or more in the settings never climb.
- Set this label for the desired tasks.
All. Having set up for yourself once and for all the necessary principles of repetition (“Monthly on the 20th”, “Annually on March 8”, “Monday”), then you could just mark the necessary tasks with these labels and all the rules of repetition apply to them.
There were, of course, several unresolved issues, something was not completely implemented (for example, flexible adjustment of repetition periods), but for most practical tasks this was already enough, at the same time the very presentation of the tasks became much more visual. Yes, for the initial settings, you need to understand a little bit, editing triggers for labels is available through the Settings menu - Global Triggers, or by the Triggers context menu item for any task with this label. In the future, we will make life easier for new users with pre-configured labels for periodic tasks.
For those 1-2 users who need practical information about the implementation of triggers and setting up periodic repetition (and the need for it can be not only in task management, but also in scheduling, for example), I will describe schematically the technical side.
Briefly about the implementation
Surprisingly, the implementation of triggers in our system is based on PostgreSQL database triggers. Posting the source of triggers and PostgreSQL procedures probably does not make sense (plus there are too many of them), because the main value is the database structure, at least for me, sitting down from scratch to write this case, this would be the most important and save a lot of time.
The table triggers_types . It simply stores information about the possible types of triggers (“When the task is completed”, “When the task is due”, “When the task is due”, ...).
Table actions . It stores information about the possible types of actions when the trigger is triggered (“Delete task”, “Mark as completed”, “Move task completion date”, ...).
Table triggers. Contains descriptions of configured triggers, i.e.:
- trigger_id uuid - identifier
- trigger_obj uuid - object reference (task, list, user, ...)
- trigger_action varchar - action type
- trigger_param1, trigger_param2 - fields for additional parameters (for example , subject and message text at notification)
- trigger_type varchar - trigger type, i.e. what triggers
- trigger_dparam1, trigger_dparam2 - integer fields for additional parameters
- trigger_name - user name
- trigger_chk integer - checksum of parameters
- trigger_descr - user description
- trigger_offset_count integer, trigger_offset_item - execution delay parameters
- trigger_user uuid - user ID
- trigger_recreated timestamp (3) - date of the last check
- trigger_sch_date1, trigger_sch_date2 - start and end dates of the repetition (for “Scheduled” triggers)
- trigger_sch_period, trigger_sch_interval, trigger_sch_count - repetition timing settings (for three ”)
To implement simple triggers (without execution delays), just the triggers table will be enough. That is, intercepting certain events (creating a task, the onset of time, completing a task), we simply search through the appropriate triggers in this table and perform actions.
To use delayed triggers, i.e. performing actions with a delay, we need a separate table actions_queue, which contains the time, a link to the object (task, list, ...) and the action that needs to be performed. Every 5 minutes, cron performs processing of the actions that have taken place.
But with the triggers “When the task is due” and “When the task is due”, especially with the set delays, the task is more complicated. To solve it, you also need a separate table (we have triggers_table), where records about all such dates will be stored, i.e. for each task there will be a separate entry for the task date and a separate entry for the task term. This was necessary because Triggers can be installed on many tasks at once, so in order not to load the system regularly by calculating those tasks for which the trigger should work (and taking into account pending triggers, that is, an additional time shift, plus taking into account the time zone, which the user can change at any time - this is a resource-intensive task), it is easier to put this information into a separate task. Well, do not forget to configure its auto-update when changing some parameters - the settings of the trigger itself, task dates, time zones.
Regarding time zones. Each user has his own time zone (to which all tasks are recounted when displayed on the screen), also each list of tasks can have its own time zone. In the main task table (events, in our case), it is better to store time without specifying a time zone, this will significantly save the output of the task tree. But in all other tables, especially those related to triggers, it is better to store time in UTC.
The table triggers_types . It simply stores information about the possible types of triggers (“When the task is completed”, “When the task is due”, “When the task is due”, ...).
Table actions . It stores information about the possible types of actions when the trigger is triggered (“Delete task”, “Mark as completed”, “Move task completion date”, ...).
Table triggers. Contains descriptions of configured triggers, i.e.:
- trigger_id uuid - identifier
- trigger_obj uuid - object reference (task, list, user, ...)
- trigger_action varchar - action type
- trigger_param1, trigger_param2 - fields for additional parameters (for example , subject and message text at notification)
- trigger_type varchar - trigger type, i.e. what triggers
- trigger_dparam1, trigger_dparam2 - integer fields for additional parameters
- trigger_name - user name
- trigger_chk integer - checksum of parameters
- trigger_descr - user description
- trigger_offset_count integer, trigger_offset_item - execution delay parameters
- trigger_user uuid - user ID
- trigger_recreated timestamp (3) - date of the last check
- trigger_sch_date1, trigger_sch_date2 - start and end dates of the repetition (for “Scheduled” triggers)
- trigger_sch_period, trigger_sch_interval, trigger_sch_count - repetition timing settings (for three ”)
To implement simple triggers (without execution delays), just the triggers table will be enough. That is, intercepting certain events (creating a task, the onset of time, completing a task), we simply search through the appropriate triggers in this table and perform actions.
To use delayed triggers, i.e. performing actions with a delay, we need a separate table actions_queue, which contains the time, a link to the object (task, list, ...) and the action that needs to be performed. Every 5 minutes, cron performs processing of the actions that have taken place.
But with the triggers “When the task is due” and “When the task is due”, especially with the set delays, the task is more complicated. To solve it, you also need a separate table (we have triggers_table), where records about all such dates will be stored, i.e. for each task there will be a separate entry for the task date and a separate entry for the task term. This was necessary because Triggers can be installed on many tasks at once, so in order not to load the system regularly by calculating those tasks for which the trigger should work (and taking into account pending triggers, that is, an additional time shift, plus taking into account the time zone, which the user can change at any time - this is a resource-intensive task), it is easier to put this information into a separate task. Well, do not forget to configure its auto-update when changing some parameters - the settings of the trigger itself, task dates, time zones.
Regarding time zones. Each user has his own time zone (to which all tasks are recounted when displayed on the screen), also each list of tasks can have its own time zone. In the main task table (events, in our case), it is better to store time without specifying a time zone, this will significantly save the output of the task tree. But in all other tables, especially those related to triggers, it is better to store time in UTC.