3

Unfortunately, I do not have the flexibility to use Quartz for scheduling and am left to build a simple scheduler for emailing based on frequency determined by user.

A user can choose to receive the same notification daily and weekly and monthly and yearly [ Yes, this will mean that the user gets the same notification delivered 8 times in a week if they select daily and weekly schedule if conditions are met.

I did lean to using a cron expression, but without native support, or at least that I haven't found one, with C# - wanted to step away from it.

The database approach I have is

enter image description here

Scheduler Table
RunFrom - nullable
RunTo - nullable
DeliveryStartDate - non nullable
RecurrenceId

User can determine to have the reminders delivered between certain date between RunFrom and RunTo date and select the date the first notification is to be sent.

RecurrenceId = fk to RecurrenceMaster Table which holds

RecurrenceType RecurenceDay
Weekly 7
Daily 1

Appreciate for any feedback. I would have a windows service which runs every 24h and pick up only those schedules that need to be executed. As the scheduling can vary, would it be better approach to pick up records that match each recurrence type by group i.e

per recurrence type, obtain records that are eligible between the runfrom and runto date and records that are eligible based on DeliveryStartDate

Some article I came across https://stackoverflow.com/a/60843760/721264 and https://stackoverflow.com/questions/19832285/design-db-structure-for-mail-scheduling

3 Answers 3

0

First, I would use the following schema:

EmailTaskSchedule:

  • ID
  • EmailTemplateID
  • Enabled
  • RecurranceSpecification [JSON]

EmailTaskEvents

  • ID
  • EmailTaskScheduleID
  • StartAt
  • HasRun
  • EndedAt
  • Content [TEXT]

A RecurranceSpecification JSON field would be very flexible. It can be defined from a component in the UI. There should be a background process that then understands that, and creates EmailTaskEvents - ideally only the "next" one. This background process could be the same email process, or a standalone one.


Assuming that you have a web-service project, you can define the functionality for the background process within a URL endpoint:

  • implement the emailer as a Web-Service endpoint that has a hard coded token - eg. /proc/emailer?t=b2br985yb298b73q43f
  • put a synchronised lock on the Web-Service endpoint, so it only runs one iteration at a time (using Monitor, or Semaphore, etc..)
  • call the URL from anywhere { Windows Task Scheduler, CRON, Cloud Task system, Console Application, whatever.. }

For bonus points, you only need a single persisting task to refer to a BackgroundProcess table.

BackgroundProcesses

  • ID
  • Name
  • PollInterval
  • URL
  • LastRunAt
  • LastResponse - HTTP status code
2
  • Thanks @Todd - the RecurranceSpecification [JSON] would work - I assume each record would hold only 1 schedule in the json as opposed to multiple schedules in json for same email template? i.e. daily and/or weekly and/or monthly and/or yearly.
    – user721264
    Commented Dec 29, 2020 at 4:11
  • @user721264 Correct, but that's totally up to you of course. So it could be an array of objects, each object has a different type { Time, Daily, Weekly, Monthly, Yearly }, and depending on the type, there would be different object properties. This is the general approach I use when designing for scheduled entities - this can also be used for human-events, like recurring Meetings, clinical appointments, and more. Commented Dec 29, 2020 at 4:20
2

Can 't you just use the Windows Task Scheduler to schedule your email sending?

1
  • I tried selling that as well, as well as sql scheduling - but no dice. POC demo's conducted as well - to show why reinvent wheel when someone else has done it well enough.
    – user721264
    Commented Dec 28, 2020 at 21:32
0

The main complexity in this area is firstly having a robust calculation of when each cycle falls due, and secondly ensuring that the scheduling system is resilient to maintenance downtime and breakdown (for example, that it is possible to advance or otherwise rearrange the execution of scheduled tasks, or to catch up on tasks that were not executed on their usual cycle, and to deliver correct results in each case).

Judging by the other answers, these complexities are often under-appreciated.

The starting point is a table defining the scheduled tasks - the date range across which they are expected to run, and the cycle(s) of repetition.

You've identified daily, weekly, monthly, and yearly. The first thing to observe is that very frequently someone will want to specify the time of day at which a task runs - not just overnight, or first thing, but after dinner (once another department have finished their morning's work), or last thing (so that people can be held back for overtime, for example). Sometimes also, if the task involves heavy processing, you will want it done at a specific time out of hours, so as not to bog the system down during working hours, or so that two such heavy tasks do not conflict with one another.

A second thing to observe is that, despite casual appearances, almost no cycle ends up having a fixed period of time between each occurrence. Cycles consisting of months, or some factor of months (twice-monthly, two-monthly, three-monthly/quarterly, yearly) do not consist of a fixed number of days. I therefore make a conceptual distinction between day-denominated periods, and month-denominated periods.

So in practice, you will often end up defining cycles which fall on a specific day counted from the start of the month (or other month-denominated period), but also separately a cycle where the day is specified as a count from the end of the month (for example, the "second-to-last day of the month", which if counted from the start may be any of 27/28/29/30). These are not the same cycles, despite both being monthly.

But even where a cycle does appear to consist of a fixed period of time (obviously daily, also weekly, two-weekly/fortnightly, four-weekly/lunarly), in practice nobody really intends that an email should be generated on Christmas Day, and the list of exceptions will be far larger than that in most firms (including public/bank holidays, company shutdowns, and even ordinary weekends for cycles like monthly or quarterly).

(As an aside, which must be mentioned now in passing, I'm also going to completely ignore the notion of "leap seconds" in UTC.)

So this irregularity in the length of each cycle introduces two more concepts. Firstly, there must be a system of "date rolling", where a cycle occurrence is first calculated trivially, but then adjusted, backwards or forwards, to a relevant working day.

Secondly, there will always be unsystematic exceptions, and you must be able to adjust these by hand.

The consequence here is that the schedule of any given cycle should not be treated as something that is calculated on the fly. Instead, a calendar for every single available cycle should be generated and maintained up to a suitable time in the future, and it must then be capable of further manual adjustments.

Another issue you may encounter, for all month-denominated periods, is defining the starting point of the period. A "financial year" often does not start on 1 Jan, or even on the first day of any subsequent month. The tax year here in the UK starts 6 Apr. The ISO week-year, which allows a year to consist always of an integral number of weeks, does not start on any fixed date (as conventionally expressed). If you're generating scheduled emails for the accounts department, you now better be prepared to address these.

Now I've summarised all the complexity in determining when a scheduled task should run. But I mentioned at the start the problem when, for wholly unplanned reasons, things don't run when they should have, or they fail in the process.

You should therefore have a system that is capable of recording exactly when tasks have and haven't been run, the outcome, and of resolving missed or failed runs (either by retrying, catching up in order, catching up out of order, or skipping the missed tasks entirely), and ensuring that duplicates are not unintentionally performed. It should be possible to review these missed tasks manually first - the sysadmins who have been fixing a problem all morning, will not appreciate a system that comes back online and then immediately bogs down and runs away like a broken diesel engine, performing tasks that would normally have been done overnight over several hours.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.