Indexing on Dates, Conflict-Checking for Appointments

I am working on a project that requires some calendar scheduling. I’m trying to construct a UDF that will create an appointment document only if the appointment does not conflict with a user’s other appointments.

My current thoughts are to store a ref to both users on the appointment document and then query the start and end times of the users’ other appointments to check for conflicts.

I’m finding it difficult to figure out the best way to construct an index to most efficiently check for conflicts. Can anyone give me advice on creating indexes with regards to storing and sorting date objects specifically, and matching on them? The date object and its functions don’t have much detail in the fql docs, especially with regards to indexing on them (at least from what I’ve been able to find).

Another question I have is whether there is a way to lock a collection so that two users cannot try to make an appointment at the same time and have two conflicting times made anyway? What’s the best way to ensure that?

I’m sure someone else will write a much prettier and long-form answer, but the short of it is:
If you use a serializable index then any read of a match will generate a conflict with concurrent writes to anything covered by that match. So basically any index reads to check for conflict on a serialised index will prevent you from a scheduling conflict. The name of the game then becomes how to pick a granularity of terms such that you minimize cost. For instance: if you have appointments for X different people and the expectation is that few people would book with a specific person concurrently then term over person would work. If all appointments are under one hour then an index with terms [X, hour of day] might do the trick (if an appointment cross an hour boundary you need two index reads / writes).

I think you should be fine by just checking with Exists and Match whether such a calendar item already exists or not.

Sidenote on consistency

Just to be clear, since there is a discussion in slack that was slightly inaccurate where the question was: “is this safe in FaunaDB”, in some (or quite a few) databases the approach I’m going to explain exposes you to a phenomenon called: Phantom reads. FaunaDB protects you against these anomalies as it has one of the highest consistency guarantees (and went through the trouble to let someone external who is very well known to break databases for a living (cool job right? :slight_smile: ) validate it to make sure the approach is sound)

Sidenote on uniqueness

For the users that are looking for something similar but are not dealing with overlaps. We can in many cases use indexes with the unique boolean set to true to make sure such conflicts do not happen. Of course this is a totally different case since you are talking about ‘overlaps’. In that case, we can write a simple match function and check with FQL logic whether it returns something.

The approach

Imagine you have appointment documents with a start and end date and ‘calendar guests’ which would just be the users who would join the appointment. In this case that’s an array, that might be cumbersome to adapt. It could also be a pointer to an external collection (often a good idea for many to many collections) that keeps relations from. user <-> appointment. Let’s start simple though.

{
   data: {
      guests: [ 
            Ref(Collection("users"), "268431417898172934"), 
            Ref(Collection("users"), "268431417898591012") 
      ],
      start_date: Time("2020-06-15T19:00:00.000000Z")
      end_date:  Time("2020-06-15T21:30:00.000000Z")
    }
}

To check whether they overlap we’ll have to know what overlaps means… that’s beyond the FaunaDB scope, so I’ll point to: https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964

With a range check in FaunaDB we can check whether one attribute lies in between two values or above or equal/beneath or equal one value (inclusive) but in this case we are doing two checks:

  • whether the new start date <= another appointment date
  • AND whether the new end date >= another appointment date

You can not do this check (as far as I know) immediately in one Range query with one index, this stackoverflow post should explain why.

I think we explained extensively there how to do range queries on multiple attributes and as mentioned there, there are multiple options so, it’ll take me a bit too much time to apply it here so I’ll keep it short…

In essence you could go the Range + Filter route Option2 in that post which only requires one index or you could take the other approach, write two indexes, one on with start_date and ref in values and one with end_date and ref in values take out the reference with join and intersect both Option 3 which requires two indexes and another one to drop the ref although your could that with map as well. Whatever you choose there, imagine that is encapsulated by ‘SearchForOtherAppointments’ then you could write the following logic:

If(
    Exists(SearchForOtherAppointments(..)),
    Abort("Appointment already exists"), 
    Create(...)
)

Awesome, I think this answers a lot of my questions. I appreciate the help. I might ask a couple more questions if I run into any issues after I get through the stack overflow links. This a great starting point though.

1 Like