Managing Calendar Availability in Supabase with SQL generate_series

Hey there, fellow devs! 👋 Today, I want to share a neat solution I came up with for managing calendar availability in Supabase.

The Challenge

I wanted to tackle creating and deleting calendar availability in Supabase while supporting ranges of dates of any size in a peer-to-peer sports gear rental app (imagine Airbnb for sports gear). Additionally, these ranges should be easily modifiable and broken down into separate dates when needed.

Imagine this situation: User A adds their surfboard to the platform and sets its availability from June 1, 2023, to June 30, 2023. Now, User B comes along and wants to rent the surfboard from June 5 to June 7. If the rental is confirmed, we need to instantly block those three days (June 5, June 6, and June 7) and make sure they are marked as unavailable for this surfboard throughout the app. Let's dive in!

Setting Availability

To get started, take a look at how I'm sending the availability request from React. As you can see, the front-end is only sending the start_date and end_date:

const { data, error } = await supabaseClient.rpc('dateAvailability', {
        gear_id: gear_id, 
        start_date: format(start_date, 'yyyy-MM-dd'), // June 1, 2023
        end_date: format(end_date, 'yyyy-MM-dd'), // June 30, 2023
      });

To ensure the dates are formatted correctly for the database schema, I'm using date-fns.

Next, let's unroll that range inside Supabase, creating an individual row for each day. We can achieve this using an SQL function with the help of the generate_series function. The generate_series function takes an interval of 1 day (or 1 hour, depending on the granularity you need):

INSERT INTO "GearAvailability"
SELECT gear_id as gear_id, generate_series(start_date, end_date, INTERVAL '1 day') as date_available;

Remember to save this function inside Supabase with a unique name, such as dateAvailability. Make sure to specify that it's written in SQL:

Once you've set up the function, use the supabaseClient.rpc() method, as shown in the JavaScript snippet above, to call dateAvailability.

This SQL query will create the necessary rows in our database. Here's an example screenshot:

Marking Dates as Unavailable

Now, let's move on to marking an item as unavailable for specific dates, such as June 5 to June 7. In your React service, make sure to provide the ID of the sports gear, as well as the start and end dates of the rental:

const { data, error } = await supabaseClient.rpc(
        'deleteDateAvailability',
        {
          gear_id: gear_id,
          start_date: format(start_date, 'yyyy-MM-dd'),
          end_date: format(end_date, 'yyyy-MM-dd'),
        }
      );

To achieve this in Supabase, we'll use a similar approach using SQL and generate_series to delete the corresponding rows:

DELETE FROM "GearAvailability"
WHERE gear_id = gear_id AND date_available=ANY
(SELECT generate_series(start_date, end_date, INTERVAL '1 day'));

And voila! Our initial availability range (June 1, 2023, to June 30, 2023) will be broken down into two ranges: June 1-4 and June 8-30 because we deleted the rows for June 5-7. With these operations, the GearAvailability table becomes a reliable and strict source of truth for all the rental calendars rendered in React. Now, users on the platform can reserve rentals for any number of days or hours without worrying about availability overlaps.

---

I hope you found this solution helpful! If you have any thoughts or alternative approaches for syncing calendar availability, please share them in the comments below. 💬

Don't forget to follow me on GitHub and LinkedIn!