Google Calendar Integration

Overview

The WhatsApp RAG Bot integrates with Google Calendar to enable:

Appointment scheduling through conversational AI
Availability checking
Event creation with automatic reminders
Business hours management
Multi-timezone support

Prerequisites

Before setting up Google Calendar integration:

Google Cloud Project

Create a project in the Google Cloud Console.

Enable Calendar API

Enable the Google Calendar API for your project.

Create OAuth 2.0 Credentials

Create OAuth 2.0 Client ID credentials (Web application type).

Configure Redirect URI

Add your OAuth callback URL to the authorized redirect URIs:

https://yourdomain.com/admin/google-auth-callback.php

OAuth Credentials

You’ll need these credentials from Google Cloud Console:

Client ID

Example: 123456789-abc123.apps.googleusercontent.com

Client Secret

Example: GOCSPX-abc123def456

Keep your client secret secure. It will be encrypted before storage in the database.

Configuration

Database Storage

Google Calendar credentials are stored in the google_oauth_credentials table:

database/schema.sql

CREATE TABLE IF NOT EXISTS google_oauth_credentials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id VARCHAR(255) DEFAULT '',
    client_secret TEXT DEFAULT NULL,
    access_token TEXT DEFAULT NULL,
    refresh_token TEXT DEFAULT NULL,
    token_expires_at TIMESTAMP NULL,
    calendar_id VARCHAR(255) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Fields encrypted with AES-256-CBC: client_secret, access_token, refresh_token

Calendar Settings Table

Calendar configuration is stored separately:

database/schema.sql

CREATE TABLE IF NOT EXISTS calendar_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Default Configuration

config/calendar.php

return [
    'enabled' => getenv('CALENDAR_ENABLED') !== 'false',
    
    'credentials' => [
        'access_token' => getenv('GOOGLE_CALENDAR_ACCESS_TOKEN'),
        'refresh_token' => getenv('GOOGLE_CALENDAR_REFRESH_TOKEN'),
        'client_id' => getenv('GOOGLE_CALENDAR_CLIENT_ID'),
        'client_secret' => getenv('GOOGLE_CALENDAR_CLIENT_SECRET'),
        'calendar_id' => getenv('GOOGLE_CALENDAR_ID') ?: 'primary'
    ],
    
    'timezone' => 'America/Bogota',
    'default_duration_minutes' => 60,
    'max_events_per_day' => 10,
    'min_advance_hours' => 1,
    
    'business_hours' => [
        'monday' => ['start' => '09:00', 'end' => '18:00'],
        'tuesday' => ['start' => '09:00', 'end' => '18:00'],
        'wednesday' => ['start' => '09:00', 'end' => '18:00'],
        'thursday' => ['start' => '09:00', 'end' => '18:00'],
        'friday' => ['start' => '09:00', 'end' => '18:00'],
        'saturday' => ['start' => '10:00', 'end' => '14:00'],
        'sunday' => null  // Closed
    ],
    
    'reminders' => [
        'email' => [
            'enabled' => true,
            'minutes_before' => 24 * 60  // 1 day
        ],
        'popup' => [
            'enabled' => true,
            'minutes_before' => 30
        ]
    ]
];

Calendar Settings

Timezone Configuration

Set your business timezone:

INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES ('timezone', 'America/New_York')
ON DUPLICATE KEY UPDATE setting_value = 'America/New_York';

Supported timezones follow the IANA timezone database.

Default Duration

Set the default appointment duration in minutes:

INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES ('default_duration_minutes', '45')
ON DUPLICATE KEY UPDATE setting_value = '45';

Business Hours

Configure business hours for each day of the week:

-- Monday
INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES ('business_hours_monday', '{"enabled":true,"start":"09:00","end":"17:00"}')
ON DUPLICATE KEY UPDATE setting_value = '{"enabled":true,"start":"09:00","end":"17:00"}';

-- Sunday (closed)
INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES ('business_hours_sunday', '{"enabled":false,"start":"09:00","end":"18:00"}')
ON DUPLICATE KEY UPDATE setting_value = '{"enabled":false,"start":"09:00","end":"18:00"}';

Maximum Events Per Day

Limit the number of appointments per day:

INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES ('max_events_per_day', '8')
ON DUPLICATE KEY UPDATE setting_value = '8';

Minimum Advance Time

Set minimum hours in advance for booking:

INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES ('min_advance_hours', '2')
ON DUPLICATE KEY UPDATE setting_value = '2';

Loading Configuration

The CalendarConfigHelper loads settings from the database:

src/Helpers/CalendarConfigHelper.php

public static function loadFromDatabase($db)
{
    $defaults = require __DIR__ . '/../../config/calendar.php';
    
    try {
        $settings = $db->fetchAll(
            "SELECT setting_key, setting_value FROM calendar_settings", 
            []
        );
        
        foreach ($settings as $setting) {
            $key = $setting['setting_key'];
            $value = $setting['setting_value'];
            
            switch ($key) {
                case 'timezone':
                    $defaults['timezone'] = $value;
                    break;
                
                case 'default_duration_minutes':
                    $defaults['default_duration_minutes'] = (int)$value;
                    break;
                
                case 'business_hours_monday':
                case 'business_hours_tuesday':
                // ... etc
                    $day = substr($key, 15);
                    $dayData = json_decode($value, true);
                    if ($dayData && isset($dayData['enabled'])) {
                        $defaults['business_hours'][$day] = $dayData['enabled'] 
                            ? ['start' => $dayData['start'], 'end' => $dayData['end']]
                            : null;
                    }
                    break;
            }
        }
    } catch (\Exception $e) {
        // Falls back to config file defaults
    }
    
    return $defaults;
}

Calendar Service

The GoogleCalendarService handles all calendar operations:

Initialization

webhook.php

function getCalendarService($logger, $db, $credentialService = null, $calendarConfig = null) {
    if ($calendarConfig === null) {
        $calendarConfig = \App\Helpers\CalendarConfigHelper::loadFromDatabase($db);
    }
    
    if (!$calendarConfig['enabled']) {
        return null;
    }
    
    if ($credentialService) {
        $credentials = $credentialService->getGoogleOAuthCredentials();
    } else {
        $credentials = $calendarConfig['credentials'];
    }
    
    if (empty($credentials['access_token'])) {
        return null;
    }
    
    return new \App\Services\GoogleCalendarService(
        $credentials['access_token'],
        $credentials['calendar_id'],
        $logger,
        $calendarConfig['timezone'],
        $credentials['refresh_token'],
        $credentials['client_id'],
        $credentials['client_secret'],
        $db
    );
}

Calendar Operations

Create Event
Check Availability
List Events
Cancel Event

$calendarService = getCalendarService($logger, $db, $credentialService);

$event = $calendarService->createEvent([
    'summary' => 'Client Consultation',
    'description' => 'Meeting with John Doe',
    'start' => '2024-03-15T10:00:00',
    'end' => '2024-03-15T11:00:00',
    'attendees' => ['[email protected]'],
    'reminders' => [
        'email' => ['minutes_before' => 1440],  // 1 day
        'popup' => ['minutes_before' => 30]
    ]
]);

$availableSlots = $calendarService->getAvailableSlots(
    $date = '2024-03-15',
    $duration = 60,  // minutes
    $businessHours = ['start' => '09:00', 'end' => '18:00']
);

// Returns:
[
    '09:00',
    '10:00',
    '14:00',
    '16:00'
]

$events = $calendarService->listEvents(
    $startDate = '2024-03-15',
    $endDate = '2024-03-22'
);

foreach ($events as $event) {
    echo $event['summary'];
    echo $event['start'];
    echo $event['end'];
}

$calendarService->deleteEvent($eventId);

Token Refresh

Google Calendar access tokens expire after 1 hour. The service automatically refreshes them:

src/Services/GoogleCalendarService.php

private function refreshAccessToken()
{
    $response = $this->client->post('https://oauth2.googleapis.com/token', [
        'form_params' => [
            'client_id' => $this->clientId,
            'client_secret' => $this->clientSecret,
            'refresh_token' => $this->refreshToken,
            'grant_type' => 'refresh_token'
        ]
    ]);
    
    $data = json_decode($response->getBody()->getContents(), true);
    $newAccessToken = $data['access_token'];
    $expiresIn = $data['expires_in'];
    
    // Update in database
    $this->updateAccessToken($newAccessToken, $expiresIn);
    
    return $newAccessToken;
}

Refresh tokens are long-lived and used to obtain new access tokens automatically.

Calendar Modes

The bot supports calendar integration in two modes:

AI Mode

Conversational appointment scheduling:

webhook.php

$intentService = new CalendarIntentService($openai, $logger);
$intent = $intentService->detectIntent(
    $messageData['text'], 
    $conversationHistory, 
    $systemPrompt
);

if ($intent['intent'] !== 'none') {
    $flowResult = $flowHandler->startFlow(
        $intent['intent'], 
        $intent['extracted_data'], 
        $conversation, 
        $messageData
    );
}

Detected intents:

schedule: Schedule new appointment
check_availability: Check available times
cancel_appointment: Cancel existing appointment
list_appointments: List upcoming appointments

Classic Mode

Menu-driven appointment scheduling:

$classicCalHandler = new ClassicCalendarFlowHandler(
    $db, 
    $logger, 
    $calendarService, 
    $timezone
);

if ($classicCalHandler->hasActiveSession($phoneNumber)) {
    $result = $classicCalHandler->handleStep(
        $messageText,
        $phoneNumber,
        $contactName
    );
}

Reminders Configuration

Email Reminders

INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES 
  ('reminder_email_enabled', 'true'),
  ('reminder_email_minutes', '1440')  -- 24 hours
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

INSERT INTO calendar_settings (setting_key, setting_value) 
VALUES 
  ('reminder_popup_enabled', 'true'),
  ('reminder_popup_minutes', '30')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

Enabling/Disabling Calendar

Toggle calendar functionality:

-- Enable calendar
INSERT INTO settings (setting_key, setting_value, setting_type) 
VALUES ('calendar_enabled', 'true', 'boolean')
ON DUPLICATE KEY UPDATE setting_value = 'true';

-- Disable calendar
UPDATE settings 
SET setting_value = 'false' 
WHERE setting_key = 'calendar_enabled';

config/calendar.php

'enabled' => getenv('CALENDAR_ENABLED') !== 'false'

Testing Your Configuration

Complete OAuth Flow

Navigate to the Google Calendar settings in the admin dashboard and complete the OAuth authorization.

Verify Credentials

Check that credentials are stored in the database:

SELECT id, client_id, calendar_id, token_expires_at 
FROM google_oauth_credentials;

Test Event Creation

Send a message like “I need to schedule an appointment for tomorrow at 2pm”.

Check Google Calendar

Verify the event appears in your Google Calendar.

Test Availability

Ask “What times are available on Monday?”

Troubleshooting

OAuth authorization fails

Verify your redirect URI matches exactly in Google Cloud Console
Check that the Calendar API is enabled
Ensure your OAuth consent screen is configured
Try revoking access and re-authorizing

Token expired errors

Check if token_expires_at is in the past
Verify refresh token is valid
Ensure token refresh logic is working
Re-authorize if refresh token is invalid

Events not appearing in calendar

Verify the calendar_id is correct (use ‘primary’ for main calendar)
Check timezone configuration matches your calendar
Ensure you have write permissions to the calendar
Look for errors in application logs

Wrong business hours

Check calendar_settings table for correct values
Verify timezone is set correctly
Ensure business hours JSON is properly formatted
Clear cache if using any caching layer

Next Steps

System Settings

Configure bot personality and behavior

Credentials

Learn about credential security

Get Started

Core Features

Configuration

Deployment

Overview

Prerequisites

OAuth Credentials

Client ID

Client Secret

Configuration

Database Storage

Calendar Settings Table

Default Configuration

Calendar Settings

Timezone Configuration

Default Duration

Business Hours

Maximum Events Per Day

Minimum Advance Time

Loading Configuration

Calendar Service

Initialization

Calendar Operations

Token Refresh

Calendar Modes

AI Mode

Classic Mode

Reminders Configuration

Email Reminders

Enabling/Disabling Calendar

Testing Your Configuration

Troubleshooting

Next Steps

System Settings

Credentials

Build docs developers (and LLMs) love

Get Started

Core Features

Configuration

Deployment

​Overview

​Prerequisites

​OAuth Credentials

​Client ID

​Client Secret

​Configuration

​Database Storage

​Calendar Settings Table

​Default Configuration

​Calendar Settings

​Timezone Configuration

​Default Duration

​Business Hours

​Maximum Events Per Day

​Minimum Advance Time

​Loading Configuration

​Calendar Service

​Initialization

​Calendar Operations

​Token Refresh

​Calendar Modes

​AI Mode

​Classic Mode

​Reminders Configuration

​Email Reminders

​Popup Reminders

​Enabling/Disabling Calendar

​Testing Your Configuration

​Troubleshooting

​Next Steps

System Settings

Credentials

Build docs developers (and LLMs) love

Overview

Prerequisites

OAuth Credentials

Client ID

Client Secret

Configuration

Database Storage

Calendar Settings Table

Default Configuration

Calendar Settings

Timezone Configuration

Default Duration

Business Hours

Maximum Events Per Day

Minimum Advance Time

Loading Configuration

Calendar Service

Initialization

Calendar Operations

Token Refresh

Calendar Modes

AI Mode

Classic Mode

Reminders Configuration

Email Reminders

Popup Reminders

Enabling/Disabling Calendar

Testing Your Configuration

Troubleshooting

Next Steps