Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Boat Cleaning Dispatch and Calendar Integration for Event Operations

Managing logistics for a charter vessel business requires coordinating multiple systems: calendar holds, crew scheduling, cleaning services, and customer communications. During this development session, we built automation to handle boat cleaning dispatch and integrated Google Calendar with external booking platforms to eliminate manual coordination overhead.

What Was Built

  • A Python-based dispatch script (dispatch_boat_cleaner.py) that triggers cleaning service workflows
  • Integration between Google Calendar and external booking platforms via Apps Script
  • Email-driven event synchronization for recurring schedule holds (Sea Scout Wednesday events)
  • Lambda-based calendar API for programmatic event management

The Boat Cleaning Dispatch System

The core challenge: when does the boat need cleaning, who needs to know, and how do we avoid manual email/Slack coordination?

Architecture: A Python script in /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py serves as the orchestrator. It reads event data from Google Calendar (via Lambda API endpoint) and determines cleaning requirements based on event metadata.


# Pseudo-code structure
def dispatch_cleaning(event_id, event_date, event_type):
    # Query event details from Calendar API
    event = lambda_calendar_api.get_event(event_id)
    
    # Determine cleaning requirements based on event type
    if event_type in ['charter', 'private_event']:
        cleaning_required = True
        urgency = calculate_turnaround(event_date, next_event_date)
    
    # Send dispatch to cleaning service endpoint
    send_dispatch_notification(cleaning_required, urgency)

Trigger mechanism: Calendar events include a custom field indicating cleaning requirements. When events are added via the Lambda calendar API, the dispatch script runs on a schedule (via cron or CloudWatch Events) to identify events within a 24-hour window that need cleaning prep.

Why this approach: Avoid hardcoding cleaning rules in multiple places. Calendar is the source of truth; the dispatch script reads it once per day and handles all logistics. This decouples calendar management from operational dispatch, making it easy to add new event types without code changes.

Google Calendar Sync and Lambda Integration

External booking platforms (GetMyBoat, Boatsetter) don't natively sync with Google Calendar. The solution: a Google Apps Script replacement project that pulls booking data and pushes it to Calendar via Lambda.

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs

This Apps Script:

  • Runs on a scheduled trigger (e.g., every 30 minutes)
  • Polls external booking platform APIs (GetMyBoat iCal feeds, manual inputs)
  • Transforms booking data into standardized event objects
  • Posts events to a Lambda function endpoint via HTTPS with auth token
  • Handles duplicate detection to avoid re-adding existing events

// CalendarSync.gs pattern
function syncExternalBookings() {
  const bookings = fetchFromGetMyBoat(); // or other platform
  const authToken = PropertiesService.getUserProperties()
    .getProperty('DASHBOARD_API_TOKEN');
  
  bookings.forEach(booking => {
    const eventPayload = {
      action: 'add-calendar-event',
      title: booking.name,
      date: booking.startDate,
      metadata: {
        source: 'getmyboat',
        bookingId: booking.id
      }
    };
    
    UrlFetchApp.fetch(LAMBDA_API_ENDPOINT, {
      method: 'post',
      headers: { 'Authorization': 'Bearer ' + authToken },
      payload: JSON.stringify(eventPayload)
    });
  });
}

Lambda function: Located in /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py, this function handles the HTTP requests from Apps Script. It validates the auth token, parses the event payload, and writes directly to Google Calendar via the Google Calendar API.

Why Lambda instead of direct Calendar API from Apps Script? Apps Script service account credentials are harder to rotate securely. A Lambda function centralizes authorization logic, allows audit logging, and makes it trivial to add rate limiting or validation. The dashboard already had a Lambda function for other operations, so we reused that infrastructure.

Infrastructure and Deployment

Google Calendar API credentials: Stored as environment variables in the Lambda execution role. The function assumes an IAM role that has calendar.googleapis.com permissions scoped to the specific calendar.

API Gateway endpoint: The Lambda function is exposed via API Gateway v2 (HTTP API, not REST API). Endpoint URL pattern: https://{api-id}.execute-api.{region}.amazonaws.com/calendar/{action}

Authentication: Bearer token validation in Lambda. Token stored in repos.env and pulled into the function environment. Apps Script retrieves it from Google Properties Service, maintaining the secret outside version control.

Deployment:

  • Lambda code: Updated via AWS console or CLI with aws lambda update-function-code
  • Apps Script: Deployed via clasp push from the project directory mapped in .clasp.json
  • Configuration synced: Environment variables updated in Lambda execution role, Apps Script re-fetches on next trigger

Recurring Event Scheduling: Sea Scout Wednesdays

One-off manual adds are painful. For recurring holds like "Sea Scout Wednesday 7–9pm every week for the season," we automated this.

Pattern: A JSON configuration file (campaign_schedule.json) defines recurring events:


{
  "recurring_events": [
    {
      "title": "Sea Scout Wednesday",
      "day_of_week": 2,  // Wednesday
      "start_time": "19:00",
      "end_time": "21:00",
      "start_date": "2024-04-10",
      "end_date": "2024-08-31",
      "metadata": { "type": "standing_hold", "organization": "sea_scouts" }
    }
  ]
}

A Python scheduler (campaign_scheduler.py) reads this file, expands the recurrence rule, and calls the Lambda API to add each instance:


from datetime import datetime, timedelta

def expand_recurring(config, start_date, end_date):
    events = []
    current = datetime.strptime(start_date, '%Y-%m-%d')
    end = datetime.strptime(end_date, '%Y-%m-%d')
    
    while current <= end:
        if current.weekday() == config['day_of_week']:
            events.append({
                'date': current.isoformat(),
                'title': config['title'],
                'metadata': config['metadata']
            })
        current += timedelta(days=1)
    
    return events

This is deployed as a scheduled Lambda or Step Functions execution that runs once at the start of the season. New seasons just require updating the JSON config and re-running the scheduler.

Key Decisions and