Managing Calendar Availability in Supabase
Tags: tutorial, postgres, sql, react
Published: 2023-05-18 11:28:59

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`:

```js
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):

```sql
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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ascqc89qmif24kxuoc5j.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wpps6nb98ngk2y4h6zk5.png)

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uvqij07j4dcfgup12g86.png)

## 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:

```js
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:

```sql
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](https://github.com/ivaaan), [Twitter](https://twitter.com/sotajazz), or [LinkedIn](https://www.linkedin.com/in/ivan-a-803503159/)!