Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Boat Cleaning Dispatch and Calendar Synchronization for Event Venues

This session focused on automating operational workflows for Queen of San Diego's event venue operations, specifically building a boat cleaning dispatch system and improving calendar synchronization between multiple platforms. The work involved Python scripting, Google Apps Script deployment, and Lambda API integration to reduce manual coordination overhead.

The Problem

Previous manual processes required:

  • Manual emailing to coordinate boat cleaning after events
  • Calendar event creation in multiple systems (Google Calendar, external booking platforms)
  • Duplicate data entry across venue management tools
  • No automated tracking or audit trail for scheduling changes

Solution Architecture

Boat Cleaning Dispatch Pipeline

Created two new Python scripts in /Users/cb/Documents/repos/tools/:

  • dispatch_boat_cleaner.py — Core dispatch logic that:
    • Reads event data from a configured source (dashboard or calendar)
    • Formats cleaning task parameters (vessel name, date, time, attendee count)
    • Queues dispatch messages for external cleaning services
    • Logs dispatch records for audit and confirmation
  • deploy_inbox_scraper.sh — Deployment wrapper that:
    • Validates script dependencies and environment configuration
    • Handles credential injection from repos.env
    • Manages Lambda function updates if dispatch runs serverlessly
    • Provides rollback capability for failed deployments
  • platform_inbox_scraper.py — Supporting utility for:
    • Monitoring external platform inboxes (GetMyBoat, Boatsetter) for booking confirmations
    • Extracting structured booking data
    • Feeding confirmation data back to the dispatch system

Calendar Synchronization via Google Apps Script

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

  • GetMyBoat iCal Integration — Parses iCal feeds from boat booking platforms and syncs to Google Calendar
  • Automated Event Creation — Creates holds and reservations as calendar events with metadata (vessel, capacity, contact)
  • Bidirectional Sync — Changes in Google Calendar reflect back to booking platforms via API
  • Conflict Detection — Prevents double-booking across platforms
  • Email Notifications — Sends sync status updates to operational staff on configurable intervals

Lambda API Integration for Dashboard

Leveraged existing Lambda function (exact name withheld for security) with API Gateway v2 endpoints:

  • Endpoint Discovery — Located calendar action handlers in Lambda code to identify correct action parameter values
  • Event Batch Operations — Built ability to add multiple calendar events in single API call (tested with 7 Sea Scout Wednesday holds)
  • Auth Token Management — Verified dashboard token in repos.env for secure API calls
  • Direct Invocation Testing — Used AWS Lambda direct invocation to debug action names before exposing via API Gateway

Example workflow (credentials/tokens redacted):

POST /calendar-api/v1/events
Content-Type: application/json
Authorization: Bearer [dashboard-token]

{
  "action": "add-calendar-event",
  "events": [
    {
      "title": "Sea Scout Wednesday Hold",
      "date": "2024-04-10",
      "time": "18:00",
      "duration": 120,
      "vessel": "Queen of San Diego",
      "notes": "Standing reservation, confirm 48hrs prior"
    }
  ]
}

Infrastructure & Deployment

Google Apps Script Project Structure

Mapped all .clasp.json files across the repository to identify the CalendarSync project:

  • Project root: /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/
  • Deployment: Used clasp push to update CalendarSync.gs to production
  • Version Control: Maintained source in Git, tracked all polling interval and email configuration changes

Environment & Credentials

  • Google Calendar API credentials verified in Lambda function configuration
  • Boat platform credentials (GetMyBoat, Boatsetter) sourced from secure storage
  • Email delivery via AWS SES for operational notifications
  • Gmail API for monitoring Carole's incoming booking emails (separate thread processing)

Key Decisions & Trade-offs

Why Python Scripts + Lambda Instead of Pure GAS

Google Apps Script has limitations with:

  • External platform authentication complexity (especially OAuth2 with multiple services)
  • Concurrent request handling for batch operations
  • Error handling and retry logic at scale

Solution: Python scripts run locally or on Lambda for heavy lifting, feed clean data to GAS via APIs. GAS handles calendar-specific operations (timezone handling, recurrence rules, Google-specific event properties) where it excels.

Why Batch Calendar Operations

Standing reservations (Sea Scout Wednesdays, corporate events) needed to be created as a set. Single-event APIs would require 7 separate calls. Batch operation reduces:

  • API latency (one round trip vs. seven)
  • Failure points (atomic operation: all succeed or all fail)
  • Cost (fewer API Gateway invocations)

Polling Interval Tuning

CalendarSync.gs originally polled external iCal feeds on fixed 30-minute intervals. Changed to:

  • 5-minute intervals for active booking windows (event week minus 1)
  • 4-hour intervals for dormant periods (far future dates)
  • Manual trigger via dashboard card for immediate sync (no polling delay)

Reduces unnecessary API calls while maintaining responsiveness for operational decisions.

Testing & Validation

Validated the system with:

  • Direct Lambda Invocation — Tested add-calendar-event action with sample Sea Scout data to confirm correct action name and payload structure
  • API Gateway Authentication — Verified dashboard token grants proper permissions
  • Calendar Persistence — Confirmed 7 Scout holds appeared in Google Calendar with correct metadata
  • iCal Parse Testing — Verified GetMyBoat iCal feed parses without errors and syncs to calendar