IMPORTANT NOTE

This decision was made in regards to issue #740. Future issues might require option 2.

---

We have two different choices regarding where scheduling logic for database tasks should be placed: in the postgres database or in the backend as a new service.

Database

If we decide to add it directly to the postgres database we would have to use an extension, and my recommendation is pg_cron. pg_cron, as far as I can gather, seems to be a standard extension for handling scheduling of tasks within databases (referenced in AWS postgres manual). With this option the scheduled database tasks would always run regardless of connection to our application services. It would however require us to revamp the postgres image/setup we are currently using, since the base postgres image does not have the pg_cron extension. This solution will require more time (i.e. sub-issues) but should not result in us having to change any existing communication with the DB.

Pros:

• Scheduled jobs will always run as long as Database is active, regardless of connection to our services
• Allows us to keep database logic separate to service logic
• Current database communication can remain

Cons:

• More complicated docker image setup
• Almost impossible to create jobs that require data sourced from outside of database

Service/backend

The second option is having scheduling logic as a sub-service to our main service (like tempSensors). We would be able to use ASP.NET built-in scheduling, requiring no additional dependencies. This approach is more in line with how our project is structured and would allow our scheduling logic to be more understandable and more easily documented. It would also allow us to more easily integrate user/role-specific tasks which need to be scheduled. The main flaw would be, as is stated above, that it would require two components to run (service and database), meaning additional points of failure.

Pros

• ASP.NET has built-in scheduling support
• We can schedule more complicated jobs
• Is in line with current structure of services in our project

Cons

• If service goes down, jobs wont be ran
• Adds another potential point of failure
• Very ineffective for simpler tasks

Conclusion

After discussion with customer, and since the current tasks are isolated to individual databases, we have decided to use pg_cron. It is important to note that the two proposed solutions are not mutually exclusive and might be combined if reason arises.