We have built a lot of health tech apps over the years, and one problem that we face time and time again is scheduling. Adding on-demand self-scheduling to your app removes bottlenecks to booking appointments and allows you to spend less time on scheduling and more time on providing care. When building a healthcare app that allows users to schedule their own appointments, we need to be able to display the time slots that are available.
EHRs weren’t built for self-scheduling
Often, we integrate with an Electronic Health Record (EHR) that health practices are already using. The EHR allows setting up provider availability and scheduling appointments. The EHR provides api endpoints for retrieving provider availability and existing appointments and for creating new appointments. More than once, in the planning stage for an EHR integration, “provider availability” has been mistakenly interpreted to mean “available time slots for a provider” instead of “provider’s weekly schedule”, only to be disappointed when the time comes to implement self-scheduling.
Traditionally, scheduling an appointment in an EHR involves staff viewing a calendar that shows existing appointments and provider availability, and offering appointment times to a patient. In this scenario, the staff member is doing the work of identfying free blocks of time during a given time range and choosing appropriate start times to offer, so the EHR doesn’t actually calculate available time slots and can’t provide those via an API.
A staff member can be relied on to take into account the provider’s availability, identify how much available time is needed for an appointment, and choose an appropriate starting time. A staff member doesn’t need to be told that an appointment starting at 3:17 would be strange or that you can’t schedule a 30-minute appointment during a 15-minute open slot. Self-scheduling, however, requires time slots.
When a user is scheduling for themself, we can’t make these assumptions. We can’t show a user a calendar with times blocked off and allow them to choose whatever time they want. It wouldn’t be appropriate for a user to see all the appointments a provider has scheduled and we can’t generally trust that they understand the factors that go into finding an available time or are willing and able to do the calculations required. We need to calculate these for the user.
We started trying the naive approach - store appointments and provider schedules in the database, query these and loop over them in application code to determine when a provider is available. This was… slow. Ruby looping through multiple lists is inefficient. We tried optimizing this with database indexing. We tried reading complex theoretical papers about algorithms that would help with this problem, but without a PhD and several months of quiet contemplation, they weren’t much use.
Offloading the math to Postgres
Eventually, we remembered that Postgres was sitting right there, with sophisticated capability for querying by time stamps and ranges. After some trial and error, we were able to create a database view using Scenic populated with every available time slot for every provider for the next six months. We added an index to the appointments table on the provider and the appointment time range and were able to refresh the view very quickly. Althought the materialized view we landed on is large, it is significantly faster to generate and query this view than to find available time slots with Ruby.
Meet Michel
We’re excited to share that we’ve extracted this work into a gem. Michel provides a generator that will take your existing application’s booking, provider, and provider schedule classes and generate a materialized-view-backed model of available time slots for your querying convenience. Refresh the view when a new appointment is scheduled or when a provider’s schedule changes, and you’ve got an always up-to-date, quick to query database view of available time slots for all your providers.
