Optimize Scheduling Process for Appointments

Hey everyone,

For quite some time now, I have been in charge of developing and maintaining an app which among other things facilitates scheduling of appointments for estimators.

Outline of what was actually needed:

1. A form where call agents record customer relevant data;
2. Based on the data, call agent selects an estimator;
3. Depending on the data, the system determines the duration of the appointment (in the background);
4. Based on the duration and estimator, through dropdowns we show the available dates and start times in 15 minute increments.

After spending quite some time on it I figured out a way to build in such a way that has all the functionalities needed however app is quite heavy and does not have great performance.

Here is a brief outline of how everything is structured:

1. We have an employees table where each estimator has a defined work hours by day Mon-Fri;
2. Each week a Pipeline runs and creates 15 minute slots for each estimator within the duration of their workday. At all times we have this prepopulated for 3 weeks ahead so when the Pipeline runs it creates the slots for current week +4 (Pipeline runs for a very long time creating many records as you can imagine);
3. When call agents get a call, they select a date from a dropdown which opens a second dropdown where available start times are filtered. Complex formulas in the slots table determine if there are enough free 15 minute increments after each slot as we have different duration of appointments depending on the customer data. Duration of appointments are either 45, 60 or 90 minutes plus one 15 minute slot for travel time before each appointment;
4. After the record is saved, a Pipeline runs, relating each of the needed 15 minute slots to the newly created parent so that we know those are booked;
5. Whenever something is rescheduled or canceled another Pipeline runs and it clears the originally booked slots and blocking the new ones in case of modification;
6. There is a different form where estimators put in abscences to block slots when they are away from work during their standard office hours.

To sum up, function wise this works, however it's very heavy and start time dropdown takes a long time to load and sometimes it does not load and shows no results.

I can't stop thinking that there is a much simpler solutions to all of this that I can't think of, so if somebody is interested or needs something similar, I will gladly work with to potentially come up with something better.

I have an ERD and Pipelines process maps documented on Lucid as a starting point.

Regards,

Yordan