Internal calendar schedule logic for the database

[danielpclark]

From our discussion I would like to put into writing for further discussion how we might go about implementing the logic of the calendar system in the database. This is by no means the final say on how it will be done, it is merely to provide clarity and discussion in moving forward.

Proposal

1) Database logic should be centered around weeks 2) Weeks can be a pattern of all weeks moving forward with a start date and no end date provided 3) Additional week entries per person can be provided for additional availability with start and end dates given for that week (e.g. Add a single Wednesday for availability in the third week of July) 4) Block out dates are a separate entry in the system 5) Week logic can be an array such as [None, None, 8..16, None, None, 10..18, None] (psuedo code as ranges should probably reflect quarter hours rather then just 24 hours) … imagine this example is a Sunday through Saturday array filter range. Since shifts is the time measurement — 0 through 24 should be acceptable as a range 6) Selecting available persons in the database would be something like (psudo code): select persons from office where within availability.start_date and availability.end_date has specific-day-entry where not None and fully covers opportunity-range and persons has no entry conflicting entry in block-out-dates

These are just some of the basics for the proposal and all changes and input are more than welcome.

[benjaminwood]

Thanks @danielpclark for sharing this. A few thoughts:

1. One of the requirements is that scheduling must be designed around block-out dates only. This was decided over in #7. We're modeling scheduling after planning center, and they do not include the concept of defining positive availability in addition to negative.
2. We also need the flexibility for volunteers to schedule multiple times per day that they are unavailable. For example, they may be busy in the morning and evening, but available in the afternoon.
3. If we're implementing scheduling with all of the power/flexibility that planning center offers, we need to allow recurring events that are not weekly. In addition to weekly, planning center allows you to create recurring block-outs on a daily, monthly, and yearly basis. For example, you can have a recurring event that happens every month on the 1st-4th.
4. We need to be mindful about how we represent all of this in the database such that we can use the tools postgres provides to query it. Nothing beats actual timestamp ranges when it comes to querying for availability within a given period of time. For example, we can query if a given need (say 1pm - 4pm) has overlap with a volunteers availability (where no block-outs exist).

There is an accepted standard for handling recurring events called rrule. There are libraries in both ruby & javascript built on top of it. I feel strongly that we should build our solution around this standard as I believe there many people who have gone before us to solve this very problem. Here's a few resources to check out:

1. Rrule specification: https://icalendar.org/iCalendar-RFC-5545/3-8-5-3-recurrence-rule.html
2. A ruby gem for dealing with recurring events that is compatible with rrule: https://github.com/seejohnrun/ice_cube
3. A javascript library for rrule: https://github.com/jakubroztocil/rrule

Further reading, I found this discussion on Reddit where some folks were discussing the complexities of recurring events with no end date: https://www.reddit.com/r/PostgreSQL/comments/3d9414/how_to_create_a_calendar_with_recurring_events_in/

[danielpclark]

Thanks @benjaminwood

Looking into rrule I've found the gem by Square Inc. called ruby-rrule which is a minimalist library for expanding RRULEs, with a goal of being fully compliant with iCalendar spec.

Being

• minimalist
• built by a well known and trusted company
• and adhering strictly to the RFC spec

we may want to consider this gem. Between the JS library you've shown here handling the input data from the front end and the output to display I think a minimalist backend for it would be quite acceptable. Rails already handles plenty of date comparison options as is.

[benjaminwood]

Cool, I hadn't seen ruby-rrule. I like that it is minimalist and that it is from square. It is comparatively very young hand has very few contributors. However, I think it'd be easy to replace with one of the other tools if it turned out to be too immature for our production use. I'm always in favor of choosing the lighter weight tool until it isn't practical.

[benjaminwood]

Let's continue this conversation over on #7.