Calculating Recurring Events in Postgresql using Javascript and PLV8

Question

I've read through about 20 different posts regarding the options for modeling recurring event data and have finally settled on an implementation, but still need a bit of help in the final details.

First, here's a description of what I'm trying to accomplish and the requirements:

• I'm using Postgres 9.4
• I'm hosting it on Amazon RDS, which only allows a limited number of pre-vetted extensions.

Obviously, a large part of my application is based around scheduling. Specifically, this component of my system allows customers of a business to schedule appointments for in-home services. Those appointments can either be single occurrences or recurring. While these appointments are typical in the sense that they have a start/end time, etc, each appointment also has data associated with it. That data may consist of things such as check-in time, check-out time, notes, etc.

For storing recurring appointments, my options were to either generate every instance up to a predetermined point in time and then generate new instances in the database whenever those instances ran up OR to calculate those instances on the fly to be returned. I ended up opting for the latter as I thought that implementation to be cleaner and easier to maintain in the long run.

With that said, using insights from this post, I opted to store my recurrence patterns as RRULE strings and to use PGV8 and the rrule.js library to do any calculations. That post mentions the use of materialized views (read about them, but never used), but I'm not sure that it would work in my case given that I'd have to regenerate these anytime a recurring appointment changed or was created. It also seems like I would need a materialized view for each business and wasn't sure how that might affect storage/performance as there could be 1000's of businesses. If you have any insights on this, please let me know.

The idea would be to have a table, Appointment, that contains data relating to the actual date, time and recurrence (if applicable) of the appointment. It would contain the following fields at a minimum:

• Start Date
• End Date
• Recurrence Pattern (RRULE)
• Exceptions
• Service ID

Then, a second table, AppointmentData, that would store any meta-data about the appointment itself. For example, it might contain the following fields:

• Appointment ID (FK to the Parent)
• Notes
• Check-in Time
• Check-out Time
• etc.

The AppointmentData instance would only be created when the Appointment is actually started by the service provider.

In general, I only need to be able to retrieve 31 days or less of appointments at any given time (in addition to retrieving a single instance). That said, my thought was to be able to pass in a start and end date to the database which would find all single occurrences of appointments that fall within that range. In addition, for any records that contain a recurrence pattern, I would use my PLV8 function to return a list of dates that fall within that range. The rrule.js library has a function that can return all dates for a recurrence pattern (rule.between(new Date(2012, 7, 1), new Date(2012, 8, 1))).

This is the area where I'm stumbling a bit. Now that I have a function in the DB that can calculate recurrence dates on the fly, I'm a bit unclear on how to "meld" these together with the single occurrences and return these as a single result set. Note that for every recurring instance, I also need to return all of the columns in the Appointment table, such as the serviceID.

If anything is unclear, please let me know.

Thanks in advance!

Answer 1

For those that are looking to do something similar, here's what I've come up with:

First, the function that generates the recurrences from an iCAL RRule string using rrule.js:

CREATE OR REPLACE FUNCTION public.generate_recurrences(
  recurrence_pattern CHARACTER VARYING,
  start_date date,
  end_date date)
 RETURNS SETOF TEXT
 LANGUAGE plv8
 IMMUTABLE STRICT
AS $function$

  // parse the RRULE string
  var rule = RRule.fromString(recurrence_pattern);

  // return all occurrences between start date and end date
  var recurrences = rule.between(start_date, end_date);

  for(var i = 0; i < recurrences.length; i++) {
     plv8.return_next(new Date(recurrences[i]).toISOString());
  }

$function$

And finally the function that grabs any instances of non-recurring appointments and melds them with recurring instances generated from the above function:

CREATE OR REPLACE FUNCTION recurring_events_for(
   for_business_id INTEGER,
   range_start DATE,
   range_end   DATE
)
   RETURNS SETOF appointment
   LANGUAGE plpgsql STABLE
   AS $BODY$
DECLARE
   appointment appointment;
   recurrence  TIMESTAMPTZ;
   appointment_length INTERVAL;
BEGIN
   FOR appointment IN
       SELECT *
         FROM appointment
        WHERE business_id = for_business_id
          AND (
                  recurrence_pattern IS NOT NULL
              OR  (
                     recurrence_pattern IS NULL
                 AND scheduled_start_time BETWEEN range_start AND range_end
              )
          )
    LOOP
       IF appointment.recurrence_pattern IS NULL THEN
         RETURN NEXT appointment;
         CONTINUE;
       END IF;

       appointment_length := appointment.scheduled_end_time - appointment.scheduled_start_time;

       FOR recurrence IN
           SELECT *
             FROM generate_recurrences(
                      appointment.recurrence_pattern,
                      range_start,
                      range_end
             )
       LOOP
           EXIT WHEN recurrence::date > range_end::date;
           CONTINUE WHEN recurrence::date < range_start::date AND recurrence::date > range_end::date;
           appointment.scheduled_start_time := recurrence;
           appointment.scheduled_end_time := recurrence + appointment_length;
           RETURN NEXT appointment;
       END LOOP;
   END LOOP;
   RETURN;
END;
$BODY$;

The result of the above is a result set that contains appointment records for both single occurrences, but also generates full appointment records on the fly for the recurrences (which are just dates).