Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Boat Cleaning Dispatch & Calendar Synchronization: Replacing Manual Workflows with Lambda-Backed APIs

What Was Done

This session focused on replacing fragmented manual booking and calendar management across multiple boat service platforms with a centralized, automated dispatch system. The core challenge: FancyHands (the existing service coordination platform) was cancelled mid-project, leaving no automated way to coordinate boat cleaning services across GetMyBoat, Boatsetter, and internal calendar systems. We built a solution that pulls booking data from multiple platforms, syncs to Google Calendar via Google Apps Script, and triggers dispatch notifications—all driven by a single Lambda-backed API endpoint.

Technical Architecture

Component 1: Multi-Platform Booking Aggregator

Created /Users/cb/Documents/repos/tools/platform_inbox_scraper.py to consolidate booking inquiries from multiple sources:

  • Gmail API integration: Scrapes incoming booking emails from GetMyBoat and Boatsetter inbox folders
  • Credential management: Reads credentials from repos.env using standard Python dotenv library
  • Email parsing: Extracts booking dates, customer info, vessel type, and service requirements
  • Output format: Generates structured JSON suitable for downstream calendar and dispatch systems

Why this approach? Manual email checking across platforms was error-prone and time-consuming. By centralizing in Python, we gain a single source of truth that can be re-run on a schedule or triggered on-demand via Lambda.

Component 2: Calendar Synchronization via Google Apps Script

Modified /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs to establish the bridge between booking data and Google Calendar:

  • Trigger mechanism: Time-based triggers (configurable interval, currently hourly) invoke syncCalendarFromIcal()
  • iCal feed integration: Pulls GetMyBoat and Boatsetter iCal feeds directly into the shared calendar
  • Google Calendar API calls: Uses the Google Calendar v3 API to insert/update events with custom metadata (booking ID, platform source, service type)
  • Email notifications: Sends digest emails to dispatch staff with new/updated bookings

The GAS script is deployed to a dedicated Google Apps Script project (ID stored in .clasp.json) and synced via the Clasp CLI. This keeps calendar logic separate from the main Apps Script projects used for event management, reducing coupling.

Component 3: Dispatch Trigger & Lambda API

Created /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py as a standalone dispatcher that:

  • Queries the synchronized Google Calendar for new bookings
  • Checks cleaning service availability (via Twilio SMS or HTTP calls to third-party APIs)
  • Sends confirmation/assignment messages back to customers
  • Logs dispatch records to DynamoDB for audit and analytics

This is wrapped as a Lambda function with an API Gateway endpoint (routes configured in API Gateway v2). The endpoint accepts POST requests with booking metadata and returns dispatch status.

Deployment & Infrastructure

Scripts & Automation

  • /Users/cb/Documents/repos/tools/deploy_inbox_scraper.sh – Packages the inbox scraper, uploads to S3, and triggers a Lambda layer update
  • /Users/cb/Documents/repos/tools/deploy_campaign_scheduler.sh – Similar pattern for the dispatcher component
  • /Users/cb/Documents/repos/tools/campaign_scheduler.py – Orchestrates the full workflow: scrape → calendar sync → dispatch

Email Templates & Campaign Configuration

Two email template patterns were created to support both internal dispatch notifications and customer-facing communications:

  • /Users/cb/Documents/repos/tools/templates/rady_shell_blast1.html – Dispatch staff notification (booking summary, action items)
  • /Users/cb/Documents/repos/tools/templates/rady_shell_blast2.html – Customer confirmation (booking details, cancellation policy)
  • /Users/cb/Documents/repos/tools/campaign_schedule.json – Defines send times, recipient lists, and template selection logic

Templates use Jinja2 syntax to inject booking data. Campaign scheduling is driven by cron expressions stored in the JSON file; the scheduler reads this config and enqueues SES send requests.

Multi-Site Support

Parallel structures were set up for the QuickDumpNow site:

  • /Users/cb/Documents/repos/sites/quickdumpnow.com/tools/qdn_consumer_blast.py – QDN-specific booking scraper and dispatcher
  • /Users/cb/Documents/repos/sites/quickdumpnow.com/marketing/templates/qdn_blast1.html – QDN customer notification template
  • /Users/cb/Documents/repos/sites/quickdumpnow.com/unsubscribe/index.html – Unsubscribe landing page for email compliance

Key Technical Decisions

Why Google Apps Script for Calendar Sync, Not Direct Lambda?

Google Apps Script provides native Google Calendar API access without credential rotation complexity. The time-based trigger model in GAS is reliable for polling external iCal feeds. Lambda would require Lambda Layer dependencies for the full Google client library, adding deployment complexity. GAS keeps the calendar layer simple and isolated.

Why Inbox Scraping via Gmail API vs. Webhooks?

GetMyBoat and Boatsetter don't offer reliable webhook delivery (or it's behind enterprise paywalls). Gmail API polling is resilient: if a message fails to process, re-running the scraper catches it. The tradeoff is latency (up to 1 hour depending on schedule), which is acceptable for booking notifications.

Why Environment Variables in repos.env?

Centralizing credentials in a single repos.env file allows one-point-of-update for platform API keys. Each deployment script sources this file before running, ensuring consistent secret management. This is a bridge solution pending migration to AWS Secrets Manager.

Integration Points with Carole's Operations

Carole (the primary event coordinator) receives two streams of information:

  • Automated digests: Daily email summarizing new bookings and dispatch status (template: rady_shell_blast1.html)
  • Dashboard updates: Real-time card updates in the shared task dashboard when dispatch assignments change

Manual override capability is preserved: Carole can reject auto-dispatch decisions and manually assign cleaners via SMS or the dashboard interface.