Equal Code: Bending the rrules
Learn about a small technique that we use to keep track of recurring calendar bookings within our graph database.
As a social care co-op, our bread and butter is connecting the right people, at the right place and time. This article is about a small technique that we use to keep track of those times - which are more complicated than you might think!
Bookings
"Bookings" is a generic term that we use to refer to any kind of event that involves people. As a carer on our platform, you might create a booking when you're planning to take holiday, you might have one next Tuesday when you're going to visit someone, or you might have one to remind yourself to get in touch with someone you haven't spoken to in a while.
Our platform revolves around bookings. They're the lifeblood of keeping track of who is going where, when and why. Not only that, they're also the unit that we use for accounting; our social care system exchanges money for hours spent caring.
A person's care and support needs evolve over time. Sometimes they evolve slowly, sometimes rapidly. Most of the time, when circumstances change, they change from one pattern of bookings to another pattern. If Mary decides she needs a little extra help in the mornings, she might decide that instead of someone coming every week day from 7AM to 8AM, she would like someone to come from 7AM to 9AM instead.
To represent these patterns of bookings in our system, we use a library called rrule.
Recurrence Rules
Recurrence Rules, or rrule for short, are a part of the iCal specification; the way to define and communicate calendar events on the internet. They're a way to describe "every week day from 7AM to 9AM" in a concise way that a program can understand. We use rrule a lot. In most cases, people describe their care as a series of patterns with different people so our booking system has had to support this from the get-go.
DTSTART:20230123T070000Z
RRULE:FREQ=DAILY;INTERVAL=1;WKST=MO;BYDAY=MO,TU,WE,TH,FR
We use an rrule each time we create a "booking series". A booking series groups together bookings into the patterns they describe. Treating bookings as part of a series allows us to manipulate all the bookings in a booking series all at once - another essential feature.
Querying rrule with Neo4J
As you might have seen in a previous article, we use Neo4J as our main database. This allows us to model the complex relationships that people have to data easily. One of the interesting problems that we had to solve for our bookings system to work well with Neo4J was understanding how we could filter booking series based on whether their rrule placed them within a time range. Let's look at an example...
Mary's team has a booking series with bookings that occur every weekday, from 7AM until 8AM. This series lasts until next Wednesday. Starting next Wednesday, a booking series begins with bookings that occur every weekday from 7AM until 9AM - one hour longer. This booking series continues indefinitely. Mary's about to look at the calendar for next week, to check what's going to happen.
To answer her question, we need to look in our database for booking series that occur next week. Because an rrule can go on indefinitely, there is no single piece of information that we can use to understand when a booking series starts and ends. To find the matching booking series, we need to get creative with our querying.
Extracting rrules with cypher
To filter booking series based on rrules, we need to access its start and end times. Because rrule is a well-defined standard, stored in plain text, we can reliably extract them with some simple rules. Let's break it down, step by step. First, we split the rrule string into each part it describes by splitting it at each ;.
[part in split(bookingSeries.rrule, ';')]
Next, we find the part that we want using a filter.
[part in split(bookingSeries.rrule), ';') where part contains "DTSTART"]
Finally, we convert that part to a datetime by taking the 16 characters that represent the datetime and passing them to the built-in datetime function.
[part in split(bookingSeries.rrule, ';') where part contains "DTSTART" | datetime(substring(part, 8, 16))]
We can then put that into a larger query that deals with applying the time window.
match(bookingSeries:BookingSeries)
with
bookingSeries,
case
when bookingSeries.rrule is null then datetime(bookingSeries.start)
when bookingSeries.rrule is not null then [part in split(bookingSeries.rrule, ';') where part contains "DTSTART" | datetime(substring(part, 8, 16))][0]
end as startdatetime,
case
when bookingSeries.rrule is null then datetime(bookingSeries.end)
when bookingSeries.rrule is not null then [part in split(bookingSeries.rrule, ';') where part contains "UNTIL" | datetime(right(part, 16))][0]
end as enddatetime
where (
case
when bookingSeries.rrule is null then (
(
startdatetime >= $start
or (
startdatetime < $start
and enddatetime > $start
)
)
and startdatetime < $end
)
else (
(
startdatetime >= $start
or (startdatetime < $start and enddatetime >= $start)
or (startdatetime < $start and enddatetime is null)
)
and startdatetime < $end
)
end
)This all relies on the excellent list comprehensions feature. If you're used to python, this might seem like a natural thing to do; if you're used to SQL then probably not so much.
If you're familiar with the spoons metaphor (if you aren't, check it out), this is definitely one of ours. We're treading a not-so-well-trod path with our exploration of a booking system as part of a graph database and it takes a fair amount of effort. That said, bookings are one of the most fundamental parts of our system to get just right; perhaps even the most important part. Tying the bookings into our graph allows us to do all kinds of funky stuff with them. Stay tuned for more articles about that in the future!