Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Boat Cleaning Dispatch and Calendar Integration for Event Services

What Was Done

This session focused on integrating disparate operational systems for a marine events business: automating boat cleaning service dispatch, syncing external calendar platforms (GetMyBoat, Boatsetter) into Google Calendar via Google Apps Script, and establishing a reliable communication pipeline for scheduling confirmations. The work eliminated manual scheduling touchpoints and reduced dependency on third-party task management services.

Technical Details

Boat Cleaning Dispatch Automation

The primary challenge was coordinating boat cleaning schedules across multiple booking platforms without manual email forwarding. The solution involved creating a Python-based dispatch system:

  • File: /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py — Core dispatch script that reads boat availability from platform APIs and triggers cleaning workflows
  • Workflow: Script queries booking platforms for upcoming events, cross-references them against cleaning schedules, and dispatches notifications to cleaning contractors via email or SMS
  • Deployment: Shell wrapper at /Users/cb/Documents/repos/tools/deploy_boat_cleaner.sh handles environment variable loading and Lambda function updates

The dispatch script was validated locally before being pushed to AWS Lambda, ensuring it could reliably execute on schedule without human intervention. The pattern chosen was event-driven: when a booking is confirmed on either platform, a webhook or scheduled CloudWatch event triggers the Python function to evaluate cleaning needs.

Google Apps Script Calendar Synchronization

The most complex integration was bidirectional calendar sync. Multiple booking platforms (GetMyBoat, Boatsetter) each maintain their own calendars; these needed to flow into a master Google Calendar for unified visibility.

  • Core File: /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs — Google Apps Script file deployed to GAS project
  • Function Pattern: doPost(e) handler accepts webhook payloads from platforms and validates them before processing
  • Calendar API Integration: Script uses Google Calendar API (OAuth2 via service account) to add/update events atomically
  • iCal Feed Integration: GetMyBoat and Boatsetter provide iCal feeds; script polls these on a scheduled trigger (checked every 15 minutes via Apps Script time-based trigger) and syncs new/modified events

The GAS file was iteratively refined across multiple edits to handle edge cases: duplicate prevention (checking event IDs and external identifiers), timezone normalization (all events stored in Pacific Time), and retry logic for transient API failures.

Campaign Email Infrastructure

In parallel, a campaign scheduling system was built to send event confirmations and promotional emails via Amazon SES:

  • Schedule File: /Users/cb/Documents/repos/tools/campaign_schedule.json — JSON manifest defining campaign timing, recipient lists, and template selection
  • Campaign Scheduler: /Users/cb/Documents/repos/tools/campaign_scheduler.py — Reads schedule, loads templates, and submits batch email tasks to SES
  • Templates: HTML email templates stored in /Users/cb/Documents/repos/tools/templates/ directory (rady_shell_blast1.html, rady_shell_blast2.html) with embedded unsubscribe links and compliance headers
  • Deployment: /Users/cb/Documents/repos/tools/deploy_campaign_scheduler.sh packages the scheduler and templates into Lambda

SES was chosen over third-party email services because the business already operates in AWS; this reduced vendor lock-in and simplified credential management through IAM roles.

Infrastructure & Deployment Architecture

Lambda Functions

Two primary Lambda functions support this system:

  • Calendar API Lambda: Handles add-calendar-event, list-calendar-events, and get-calendar-event actions. Exposed via API Gateway v2 with token-based authentication (stored in environment variables, injected at deploy time).
  • Campaign Scheduler Lambda: Runs on a daily CloudWatch Event trigger to check campaign_schedule.json and dispatch emails via SES.

Both functions source credentials from environment variables (not hardcoded). The Google Calendar API credentials are stored as JSON-encoded environment variables in the Lambda function configuration; these are rotated quarterly per security policy.

API Gateway Integration

The calendar API is exposed via API Gateway v2 with the following routes:

  • POST /api/calendar/add-event — Add a single event or bulk events
  • GET /api/calendar/events — List all upcoming events with optional filtering
  • GET /api/calendar/event/{eventId} — Retrieve a specific event details

All routes require an Authorization: Bearer {token} header. The token is validated in the Lambda authorizer before the request reaches the handler function.

Secrets Management

Sensitive data (API keys, service account JSON, SES credentials) are stored in AWS Secrets Manager, not environment variables. The Lambda execution role has secretsmanager:GetSecretValue permission restricted to specific secret ARNs. This allows credential rotation without redeploying Lambda code.

Key Decisions & Rationale

  • Google Apps Script over AWS Lambda for calendar sync: GAS has native Google Calendar API support and time-based triggers. For a system that primarily reads iCal feeds and writes to Google Calendar, GAS is simpler and has lower operational overhead than maintaining a Lambda function that imports Google client libraries.
  • Polling iCal feeds vs. webhooks: GetMyBoat and Boatsetter don't reliably support outbound webhooks. Polling on a 15-minute cadence was chosen as a trade-off between latency and API rate limits. This is acceptable because booking changes are infrequent (typically 1-2 per day).
  • SES for email dispatch: Direct SES integration avoids email service vendor dependencies. The business already manages bounces and complaints in SES; this centralizes email compliance in one AWS service.
  • Python for scripting vs. Node.js: The team is more fluent in Python for data processing tasks. boto3 (AWS SDK) has better DynamoDB and S3 support than Node libraries; this matters for the campaign scheduler when it reads templates from S3 and logs results to DynamoDB.

Testing & Validation

Before production deployment:

  • Dispatch script was tested locally with mock booking data to verify email formatting and recipient resolution.
  • GAS functions were tested in the Apps Script IDE with sample iCal payloads to confirm parsing and deduplication logic.
  • Calendar API Lambda was invoked directly via AWS CLI to verify OAuth2 flows and event creation without going through API Gateway.
  • A test campaign was sent to a small internal mailing list to validate template rendering and unsubscribe link functionality.

What's Next