Repository: thunderbird/appointment
Issue category: Feature Request

Issue:
Use FreeBusy API to generate scheduling time
https://developers.google.com/calendar/api/v3/reference/freebusy/query

I somehow missed this api / component for caldav apis and google api. We can use this in-place for generating availability times. 


Hints:
Testing this with a couple of calendars and it seems like it significantly speeds up queries (I mean duh). From 3 seconds with 5 calendars of varying degree to about 500ms with sort of the same output. I'll need to dig up what qualifies as "busy" time as we might be counting "busy" different or wrong. 

Patch:
diff --git a/backend/src/appointment/controller/apis/google_client.py b/backend/src/appointment/controller/apis/google_client.py
index 88386a14a..31de7bb7b 100644
--- a/backend/src/appointment/controller/apis/google_client.py
+++ b/backend/src/appointment/controller/apis/google_client.py
@@ -1,12 +1,18 @@
 import logging
+import os
+from datetime import datetime
 
+import sentry_sdk
 from google_auth_oauthlib.flow import Flow
 from googleapiclient.discovery import build
 from googleapiclient.errors import HttpError
+
+from ... import utils
 from ...database import repo
 from ...database.models import CalendarProvider
 from ...database.schemas import CalendarConnection
-from ...exceptions.calendar import EventNotCreatedException, EventNotDeletedException
+from ...defines import DATETIMEFMT
+from ...exceptions.calendar import EventNotCreatedException, EventNotDeletedException, FreeBusyTimeException
 from ...exceptions.google_api import GoogleScopeChanged, GoogleInvalidCredentials
 
 
@@ -96,6 +102,60 @@ def list_calendars(self, token):
 
         return items
 
+    def get_free_busy(self, calendar_ids, time_min, time_max, token):
+        """Query the free busy api
+        Ref: https://developers.google.com/calendar/api/v3/reference/freebusy/query"""
+        response = {}
+        items = []
+
+        import time
+
+        perf_start = time.perf_counter_ns()
+        with build('calendar', 'v3', credentials=token, cache_discovery=False) as service:
+            request = service.freebusy().query(
+                body=dict(timeMin=time_min, timeMax=time_max, items=[{'id': calendar_id} for calendar_id in calendar_ids])
+            )
+
+            while request is not None:
+                try:
+                    response = request.execute()
+                    errors = [calendar.get('errors') for calendar in response.get('calendars', {}).values()]
+
+                    # Log errors and throw 'em in sentry
+                    if any(errors):
+                        reasons = [
+                            {
+                                'domain': utils.setup_encryption_engine().encrypt(error.get('domain')),
+                                'reason': error.get('reason')
+                            } for error in errors
+                        ]
+                        if os.getenv('SENTRY_DSN'):
+                            ex = FreeBusyTimeException(reasons)
+                            sentry_sdk.capture_exception(ex)
+                        logging.warning(f'[google_client.get_free_time] FreeBusy API Error: {ex}')
+
+                    calendar_items = [calendar.get('busy', []) for calendar in response.get('calendars', {}).values()]
+                    for busy in calendar_items:
+                        # Transform to datetimes to match caldav's behaviour
+                        items += [
+                            {
+                                'start': datetime.strptime(entry.get('start'), DATETIMEFMT),
+                                'end': datetime.strptime(entry.get('end'), DATETIMEFMT)
+                            } for entry in busy
+                        ]
+                except HttpError as e:
+                    logging.warning(f'[google_client.get_free_time] Request Error: {e.status_code}/{e.error_details}')
+
+                request = service.calendarList().list_next(request, response)
+        perf_end = time.perf_counter_ns()
+
+        # Capture the metric if sentry is enabled
+        print(f"Google FreeBusy response: {(perf_end - perf_start) / 1000000000} seconds")
+        if os.getenv('SENTRY_DSN'):
+            sentry_sdk.set_measurement('google_free_busy_time_response', perf_end - perf_start, 'nanosecond')
+
+        return items
+
     def list_events(self, calendar_id, time_min, time_max, token):
         response = {}
         items = []
diff --git a/backend/src/appointment/controller/calendar.py b/backend/src/appointment/controller/calendar.py
index 5f428c951..534692b46 100644
--- a/backend/src/appointment/controller/calendar.py
+++ b/backend/src/appointment/controller/calendar.py
@@ -28,7 +28,7 @@
 
 from .. import utils
 from ..database.schemas import CalendarConnection
-from ..defines import REDIS_REMOTE_EVENTS_KEY, DATEFMT, DEFAULT_CALENDAR_COLOUR
+from ..defines import REDIS_REMOTE_EVENTS_KEY, DATEFMT, DEFAULT_CALENDAR_COLOUR, DATETIMEFMT
 from .apis.google_client import GoogleClient
 from ..database.models import CalendarProvider, BookingStatus
 from ..database import schemas, models, repo
@@ -148,6 +148,18 @@ def __init__(
         if google_tkn:
             self.google_token = Credentials.from_authorized_user_info(json.loads(google_tkn), self.google_client.SCOPES)
 
+    def get_busy_time(self, calendar_ids: list, start: str, end: str):
+        """Retrieve a list of { start, end } dicts that will indicate busy time for a user
+        Note: This does not use the remote_calendar_id from the class,
+        all calendars must be available under the google_token provided to the class"""
+        time_min = datetime.strptime(start, DATEFMT).isoformat() + 'Z'
+        time_max = datetime.strptime(end, DATEFMT).isoformat() + 'Z'
+
+        results = []
+        for calendars in utils.chunk_list(calendar_ids, chunk_by=5):
+            results += self.google_client.get_free_busy(calendars, time_min, time_max, self.google_token)
+        return results
+
     def test_connection(self) -> bool:
         """This occurs during Google OAuth login"""
         return bool(self.google_token)
@@ -314,6 +326,38 @@ def __init__(self, db: Session, subscriber_id: int, calendar_id: int, redis_inst
         # connect to the CalDAV server
         self.client = DAVClient(url=self.url, username=self.user, password=self.password)
 
+    def get_busy_time(self, calendar_ids: list, start: str, end: str):
+        """Retrieve a list of { start, end } dicts that will indicate busy time for a user
+        Note: This does not use the remote_calendar_id from the class"""
+        time_min = datetime.strptime(start, DATEFMT)
+        time_max = datetime.strptime(end, DATEFMT)
+
+        perf_start = time.perf_counter_ns()
+
+        calendar = self.client.calendar(url=calendar_ids[0])
+        response = calendar.freebusy_request(time_min, time_max)
+
+        perf_end = time.perf_counter_ns()
+        print(f"CALDAV FreeBusy response: {(perf_end - perf_start) / 1000000000} seconds")
+
+
+        items = []
+
+        # This is sort of dumb, freebusy object isn't exposed in the icalendar instance except through a list of tuple props
+        # Luckily the value is a vPeriod which is a tuple of date times/timedelta (0 = Start, 1 = End)
+        for prop in response.icalendar_instance.property_items():
+            if prop[0].lower() != 'freebusy':
+                continue
+
+            # Tuple of start datetime and end datetime (or timedelta!)
+            period = prop[1].dt
+            items.append({
+                'start': period[0],
+                'end': period[1] if isinstance(period[1], datetime) else period[0] + period[1]
+            })
+
+        return items
+
     def test_connection(self) -> bool:
         """Ensure the connection information is correct and the calendar connection works"""
 
@@ -785,27 +829,26 @@ def existing_events_for_schedule(
     ) -> list[schemas.Event]:
         """This helper retrieves all events existing in given calendars for the scheduled date range"""
         existing_events = []
+        google_calendars = []
+
+        now = datetime.now()
+
+        earliest_booking = now + timedelta(minutes=schedule.earliest_booking)
+        farthest_booking = now + timedelta(minutes=schedule.farthest_booking)
+
+        start = max([datetime.combine(schedule.start_date, schedule.start_time), earliest_booking])
+        end = (
+            min([datetime.combine(schedule.end_date, schedule.end_time), farthest_booking])
+            if schedule.end_date
+            else farthest_booking
+        )
 
         # handle calendar events
         for calendar in calendars:
             if calendar.provider == CalendarProvider.google:
-                external_connection = utils.list_first(
-                    repo.external_connection.get_by_type(db, subscriber.id, schemas.ExternalConnectionType.google)
-                )
-
-                if external_connection is None or external_connection.token is None:
-                    raise RemoteCalendarConnectionError()
-
-                con = GoogleConnector(
-                    db=db,
-                    redis_instance=redis,
-                    google_client=google_client,
-                    remote_calendar_id=calendar.user,
-                    calendar_id=calendar.id,
-                    subscriber_id=subscriber.id,
-                    google_tkn=external_connection.token,
-                )
+                google_calendars.append(calendar)
             else:
+                # Caldav - We don't have a smart way to batch these right now so just call them 1 by 1
                 con = CalDavConnector(
                     db=db,
                     redis_instance=redis,
@@ -816,23 +859,53 @@ def existing_events_for_schedule(
                     calendar_id=calendar.id,
                 )
 
-            now = datetime.now()
-
-            earliest_booking = now + timedelta(minutes=schedule.earliest_booking)
-            farthest_booking = now + timedelta(minutes=schedule.farthest_booking)
-
-            start = max([datetime.combine(schedule.start_date, schedule.start_time), earliest_booking])
-            end = (
-                min([datetime.combine(schedule.end_date, schedule.end_time), farthest_booking])
-                if schedule.end_date
-                else farthest_booking
+                try:
+                    existing_events.extend([
+                        schemas.Event(
+                            start=busy.get('start'),
+                            end=busy.get('end'),
+                            title='Busy'
+                        ) for busy in
+                        con.get_busy_time([calendar.url], start.strftime(DATEFMT), end.strftime(DATEFMT))
+                    ])
+
+                    # We're good here, continue along the loop
+                    continue
+                except caldav.lib.error.ReportError:
+                    logging.debug("[Tools.existing_events_for_schedule] CalDAV server does not support FreeBusy API.")
+                    pass
+
+                # Okay maybe this server doesn't support freebusy, try the old way
+                try:
+                    existing_events.extend(con.list_events(start.strftime(DATEFMT), end.strftime(DATEFMT)))
+                except requests.exceptions.ConnectionError:
+                    # Connection error with remote caldav calendar, don't crash this route.
+                    pass
+
+        # Batch up google calendar calls since we can only have one google calendar connected
+        if len(google_calendars) > 0 and google_calendars[0].provider == CalendarProvider.google:
+            external_connection = utils.list_first(
+                repo.external_connection.get_by_type(db, subscriber.id, schemas.ExternalConnectionType.google)
             )
-
-            try:
-                existing_events.extend(con.list_events(start.strftime(DATEFMT), end.strftime(DATEFMT)))
-            except requests.exceptions.ConnectionError:
-                # Connection error with remote caldav calendar, don't crash this route.
-                pass
+            if external_connection is None or external_connection.token is None:
+                raise RemoteCalendarConnectionError()
+
+            con = GoogleConnector(
+                db=db,
+                redis_instance=redis,
+                google_client=google_client,
+                remote_calendar_id=google_calendars[0].user,  # This isn't used for get_busy_time but is still needed.
+                calendar_id=google_calendars[0].id,  # This isn't used for get_busy_time but is still needed.
+                subscriber_id=subscriber.id,
+                google_tkn=external_connection.token,
+            )
+            existing_events.extend([
+                schemas.Event(
+                    start=busy.get('start'),
+                    end=busy.get('end'),
+                    title='Busy'
+                ) for busy in con.get_busy_time([calendar.user for calendar in google_calendars], start.strftime(DATEFMT), end.strftime(DATEFMT))
+            ])
 
         # handle already requested time slots
         for slot in schedule.slots:
diff --git a/backend/src/appointment/defines.py b/backend/src/appointment/defines.py
index 6d439d126..b0641a335 100644
--- a/backend/src/appointment/defines.py
+++ b/backend/src/appointment/defines.py
@@ -2,6 +2,7 @@
 FALLBACK_LOCALE = 'en'
 
 DATEFMT = '%Y-%m-%d'
+DATETIMEFMT = '%Y-%m-%dT%H:%M:%SZ'
 
 # list of redis keys
 REDIS_REMOTE_EVENTS_KEY = 'rmt_events'
diff --git a/backend/src/appointment/exceptions/calendar.py b/backend/src/appointment/exceptions/calendar.py
index 03e749f6d..cb89d8e40 100644
--- a/backend/src/appointment/exceptions/calendar.py
+++ b/backend/src/appointment/exceptions/calendar.py
@@ -10,8 +10,14 @@ class EventNotDeletedException(Exception):
     pass
 
 
+class FreeBusyTimeException(Exception):
+    """Generic error with the free busy time api"""
+    pass
+
+
 class TestConnectionFailed(Exception):
     """Raise if test connection fails, include remote error message."""
 
     def __init__(self, reason: str | None = None):
         self.reason = reason
+
diff --git a/backend/src/appointment/utils.py b/backend/src/appointment/utils.py
index bb16df45a..13ae2330b 100644
--- a/backend/src/appointment/utils.py
+++ b/backend/src/appointment/utils.py
@@ -2,6 +2,7 @@
 import os
 import re
 import urllib.parse
+from contextlib import contextmanager
 from urllib import parse
 
 from functools import cache
@@ -79,6 +80,13 @@ def retrieve_user_url_data(url):
     # Return the username and signature decoded, but ensure the clean_url is encoded.
     return urllib.parse.unquote_plus(username), urllib.parse.unquote_plus(signature), clean_url
 
+
+def chunk_list(to_chunk: list, chunk_by: int):
+    """Chunk a to_chunk list by chunk_by"""
+    for i in range(0, len(to_chunk), chunk_by):
+        yield to_chunk[i:i+chunk_by]
+
+
 def normalize_secrets():
     """Normalizes AWS secrets for Appointment"""
     database_secrets = os.getenv('DATABASE_SECRETS')
@@ -155,3 +163,4 @@ def normalize_secrets():
         # Need to stuff these somewhere
         os.environ['POSTHOG_PROJECT_KEY'] = secrets.get('posthog_project_key')
         os.environ['POSTHOG_HOST'] = secrets.get('posthog_host')
+


Test patch:
diff --git a/backend/test/integration/test_schedule.py b/backend/test/integration/test_schedule.py
index d5e7ca2dc..1f67250dd 100644
--- a/backend/test/integration/test_schedule.py
+++ b/backend/test/integration/test_schedule.py
@@ -91,8 +91,6 @@ def test_create_schedule_with_end_time_before_start_time(
         assert data.get('detail')[0]['ctx']['err_field'] == 'end_time'
         assert data.get('detail')[0]['ctx']['err_value'] == '17:30:00'
 
-
-
     def test_create_schedule_on_unconnected_calendar(
         self, with_client, make_caldav_calendar, make_schedule, schedule_input
     ):
@@ -297,11 +295,11 @@ def __init__(self, db, redis_instance, url, user, password, subscriber_id, calen
                 pass
 
             @staticmethod
-            def list_events(self, start, end):
+            def get_busy_time(self, remote_calendar_ids, start, end):
                 return []
 
         monkeypatch.setattr(CalDavConnector, '__init__', MockCaldavConnector.__init__)
-        monkeypatch.setattr(CalDavConnector, 'list_events', MockCaldavConnector.list_events)
+        monkeypatch.setattr(CalDavConnector, 'get_busy_time', MockCaldavConnector.get_busy_time)
 
         start_date = date(2024, 3, 1)
         start_time = time(16, tzinfo=UTC)
@@ -413,18 +411,14 @@ def __init__(self, db, redis_instance, url, user, password, subscriber_id, calen
                 pass
 
             @staticmethod
-            def list_events(self, start, end):
+            def get_busy_time(self, calendar_ids, start, end):
                 return [
-                    schemas.Event(
-                        title='A blocker!',
-                        start=start_end_datetimes[0],
-                        end=start_end_datetimes[1],
-                    )
+                    {'start': start_end_datetimes[0], 'end': start_end_datetimes[1]}
                     for start_end_datetimes in blocker_times
                 ]
 
         monkeypatch.setattr(CalDavConnector, '__init__', MockCaldavConnector.__init__)
-        monkeypatch.setattr(CalDavConnector, 'list_events', MockCaldavConnector.list_events)
+        monkeypatch.setattr(CalDavConnector, 'get_busy_time', MockCaldavConnector.get_busy_time)
 
         subscriber = make_pro_subscriber()
         generated_calendar = make_caldav_calendar(subscriber.id, connected=True)
@@ -532,22 +526,20 @@ def test_fail_and_success(
 
         class MockCaldavConnector:
             @staticmethod
-            def list_events(self, start, end):
+            def get_busy_time(self, calendar_ids, start, end):
                 return [
-                    schemas.Event(
-                        title='A blocker!',
-                        start=start_datetime,
-                        end=datetime.combine(start_date, start_time, tzinfo=timezone.utc) + timedelta(minutes=10),
-                    ),
-                    schemas.Event(
-                        title='A second blocker!',
-                        start=start_datetime + timedelta(minutes=10),
-                        end=datetime.combine(start_date, start_time, tzinfo=timezone.utc) + timedelta(minutes=20),
-                    ),
+                    {
+                        'start': start_datetime,
+                        'end': datetime.combine(start_date, start_time, tzinfo=timezone.utc) + timedelta(minutes=10),
+                    },
+                    {
+                        'start': start_datetime + timedelta(minutes=10),
+                        'end': datetime.combine(start_date, start_time, tzinfo=timezone.utc) + timedelta(minutes=20),
+                    },
                 ]
 
         # Override the fixture's list_events
-        monkeypatch.setattr(CalDavConnector, 'list_events', MockCaldavConnector.list_events)
+        monkeypatch.setattr(CalDavConnector, 'get_busy_time', MockCaldavConnector.get_busy_time)
 
         subscriber = make_pro_subscriber()
         generated_calendar = make_caldav_calendar(subscriber.id, connected=True)
@@ -627,6 +619,14 @@ def test_success_with_no_confirmation(
         start_datetime = datetime.combine(start_date, start_time, tzinfo=timezone.utc)
         end_time = time(10, tzinfo=UTC)
 
+        class MockCaldavConnector:
+            @staticmethod
+            def get_busy_time(self, calendar_ids, start, end):
+                return []
+
+        # Override the fixture's list_events
+        monkeypatch.setattr(CalDavConnector, 'get_busy_time', MockCaldavConnector.get_busy_time)
+
         subscriber = make_pro_subscriber()
         generated_calendar = make_caldav_calendar(subscriber.id, connected=True)
         make_schedule(