Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Event Calendar Synchronization Across Multiple Booking Platforms

What Was Done

This session focused on consolidating fragmented booking and calendar systems across multiple business domains into a unified, automated synchronization pipeline. The work involved:

  • Establishing bidirectional calendar sync between Google Calendar and external booking platforms (GetMyBoat, Boatsetter) via Google Apps Script
  • Creating a dispatch automation system for recurring operational tasks (boat cleaning service coordination)
  • Building an email campaign scheduler with template management for multi-domain marketing operations
  • Integrating Lambda-based calendar APIs with web dashboards for real-time event management

The underlying problem: event data was scattered across email inboxes, multiple booking platforms, and manual spreadsheets with no single source of truth. This created scheduling conflicts, missed service coordination windows, and manual reconciliation overhead.

Technical Architecture

Google Apps Script Calendar Synchronization

The core synchronization engine lives in:

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

This GAS file handles the primary workflow:

  • iCal Feed Polling: Periodically fetches event feeds from GetMyBoat and Boatsetter via iCal URLs, parsing calendar events from external platforms
  • Event Deduplication: Compares incoming events against existing Google Calendar entries using event title, date, and attendee matching to prevent duplicate calendar bloat
  • Bidirectional Sync: Maps platform-specific event metadata (booking ID, platform name, service type) into custom Google Calendar event properties for later reconciliation
  • Email Notifications: Triggers SES email delivery when new events are added, with configurable recipient lists per event type

The sync runs on a time-based trigger (via ScriptApp.newTrigger()) rather than event-driven architecture. This was a deliberate choice: given that external platforms update infrequently and consistency matters more than latency, scheduled polling reduces API calls to booking platform endpoints and provides predictable resource usage.

Lambda-Based Calendar API

For dashboard integration and programmatic event creation, a Lambda function exposes calendar operations via API Gateway. The endpoint accepts JSON payloads with action types:

POST /calendar-api
{
  "action": "add-calendar-event",
  "event": {
    "title": "Sea Scout Wednesday Hold",
    "date": "2025-04-28",
    "time": "18:00",
    "description": "Weekly booking hold"
  }
}

The Lambda function was invoked directly during this session to add 7 Sea Scout weekly holds spanning April-May. Why Lambda instead of pure GAS? Lambda provides better integration with the dashboard authentication system (token-based validation in environment variables) and scales independently from Google's Apps Script execution quotas.

Dispatch Automation System

A new Python-based dispatch coordinator was created at:

/Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py

This script:

  • Reads a schedule configuration from campaign_schedule.json
  • Queries the Lambda calendar API for upcoming service bookings
  • Generates dispatch notifications for boat cleaning services based on booking dates
  • Delivers notifications via SES to configured vendor contact lists

Deployment wrapper at /Users/cb/Documents/repos/tools/deploy_campaign_scheduler.sh handles Lambda code updates and trigger configuration. The separation of Python logic from deployment scripting allows the operations team to modify schedules (JSON) without touching code.

Email Campaign Infrastructure

Marketing campaigns now use a template-driven approach with HTML templates stored in domain-specific paths:

  • /Users/cb/Documents/repos/tools/templates/rady_shell_blast1.html — Queen of San Diego venue marketing
  • /Users/cb/Documents/repos/sites/quickdumpnow.com/marketing/templates/qdn_blast1.html — QuickDumpNow consumer campaigns

Template files are rendered by campaign_scheduler.py, which substitutes recipient variables and delegates delivery to SES. This approach decouples template authoring (non-technical marketing team) from delivery orchestration (engineering), reducing deployment friction.

Consumer Unsubscribe Integration

Each domain maintains its own unsubscribe landing page:

/Users/cb/Documents/repos/sites/quickdumpnow.com/unsubscribe/index.html

Campaign templates include unsubscribe links that route to domain-specific handlers. The QuickDumpNow instance (qdn_consumer_blast.py) tracks opt-outs in a domain-local preference store, allowing per-domain suppression lists without centralized database coupling.

Key Infrastructure Decisions

Why Scheduled Polling Over Event-Driven Sync

External booking platforms (GetMyBoat, Boatsetter) don't provide webhook APIs for free/tier-1 accounts. Event-driven architecture would require paid tier upgrades or polling anyway. Scheduled polling at fixed intervals (every 15-30 minutes, configurable in GAS trigger settings) provides:

  • Predictable API quota consumption
  • No need for webhook endpoint management or IP allowlisting
  • Built-in backoff handling (failures don't cascade into failed webhooks)
  • Simple testing via manual trigger invocation

Dual Calendar Systems (GAS + Lambda)

Google Apps Script handles platform-to-calendar sync (data ingestion). Lambda handles dashboard-to-calendar sync (user-initiated events). This separation exists because:

  • GAS has direct access to Google Calendar API with service account credentials
  • Lambda integrates seamlessly with API Gateway authentication (dashboard token validation)
  • Operational events (holds, blocks) originate from the dashboard; bookings originate from external platforms
  • Decoupling allows one system to fail without blocking the other

Template-Based Email Over Hardcoded HTML

Campaign emails are generated from HTML templates rather than constructed programmatically. This enables non-engineers to modify messaging without code review. Template variables are replaced at render time using string substitution, avoiding template engine dependencies.

Monitoring and Logging

Both GAS and Lambda functions log activity to CloudWatch (via Lambda) and Google Cloud Logging (via GAS). Session notes and task status are tracked in the dashboard at /tmp/dashboard_index.html, providing a human-readable operational log.

What's Next

  • Platform Credential Rotation: GetMyBoat and Boatsetter API keys should be rotated quarterly and stored in AWS Secrets Manager rather than repos.env
  • Deduplication Improvements: Current matching uses title + date; implementing fuzzy matching for events with minor naming variations would reduce false positives
  • Analytics Dashboard: Aggregate booking volume, dispatch success rates, and campaign open rates into a BI dashboard for operational visibility
  • Vendor SLA Tracking: Dispatch notifications should include SLA windows and automatically escal