Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Event Calendar Synchronization Across Booking Platforms: A Multi-Source Integration Pattern

This session focused on consolidating event management across multiple booking and calendar platforms by building automated synchronization pipelines. The core challenge: keep Google Calendar authoritative while syncing events to boat rental platforms (GetMyBoat, Boatsetter) and vice versa, without manual intervention or data loss.

What Was Done

  • Restored and enhanced CalendarSync.gs in the Google Apps Script replacement project for automated calendar event propagation
  • Debugged and verified Lambda-based calendar API endpoints for programmatic event creation
  • Integrated boat platform credentials (GetMyBoat, Boatsetter) into deployment configuration
  • Established polling intervals and email notification patterns for calendar sync status
  • Created dispatch automation for recurring calendar holds (e.g., Sea Scout Wednesday events)

Technical Details: Calendar Sync Architecture

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

This Google Apps Script manages bidirectional calendar synchronization. The script polls Google Calendar on a scheduled trigger and compares events against external platform calendars. Key functions include:

  • syncCalendarEvents() — Main orchestration function that runs on time-based triggers (currently 30-minute intervals)
  • fetchGoogleCalendarEvents(calendarId) — Retrieves events from the primary Google Calendar for a given time range
  • pushEventToPlatform(event, platformName) — Publishes events to external boat rental APIs
  • sendSyncStatusEmail(status) — Notifies operations team of sync success/failure

Why 30-minute intervals? A balance between real-time responsiveness and quota management. GetMyBoat and Boatsetter have rate limits; polling every 5 minutes would exhaust daily quotas. 30 minutes provides acceptable latency for event publication while respecting API contract terms.

Lambda Integration for Direct Calendar API Access

File: `/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py`

The Lambda function serves as an HTTP wrapper around Google Calendar API, allowing programmatic event creation without exposing credentials. Key actions include:

  • add-calendar-event — Creates a single calendar event with title, start time, end time, description, and optional attendees
  • list-calendar-events — Returns events for a specified date range, used for validation and conflict checking
  • delete-calendar-event — Removes events (used for cancellations)
  • update-calendar-event — Modifies existing event details

The Lambda function integrates Google Calendar credentials stored in AWS Secrets Manager (referenced via environment variable, not hardcoded). Event creation payloads include:

{
  "action": "add-calendar-event",
  "title": "Sea Scout Wednesday Hold",
  "start_time": "2025-04-30T18:00:00",
  "end_time": "2025-04-30T19:30:00",
  "description": "Recurring platform hold for Sea Scout events",
  "calendar_id": "primary"
}

This approach decouples credential management from deployment scripts. The Lambda function handles authentication; dashboards and automation tools only need valid API tokens issued by API Gateway, which validates requests before forwarding to Lambda.

API Gateway Authentication Layer

Infrastructure: API Gateway v2 (HTTP API)

Requests flow through API Gateway routes, which authenticate using an authorization token stored in environment configuration. The gateway validates the token header before invoking the Lambda function, preventing direct Lambda invocation without proper authorization.

Why HTTP API (v2) instead of REST API? Lower cost and latency for simple request-response patterns. This use case doesn't require WebSocket support or complex request transformation, so HTTP API's streamlined design is appropriate.

Boat Platform Credential Integration

Files created/modified:

  • /Users/cb/Documents/repos/repos.env — Environment variables for GetMyBoat API key, Boatsetter credentials, and calendar sync settings
  • CalendarSync.gs — Updated to read platform credentials from Properties Service (Google Apps Script's secure key-value store)

Credentials are stored in the GAS Properties Service at deployment time, never committed to version control. The sync function retrieves them as needed:

const boatsetterApiKey = PropertiesService.getScriptProperties()
  .getProperty('BOATSETTER_API_KEY');
const getmyboatApiKey = PropertiesService.getScriptProperties()
  .getProperty('GETMYBOAT_API_KEY');

This pattern keeps credentials out of source code while allowing the GAS script runtime access to them.

Deployment Pipeline

File: `/Users/cb/Documents/repos/tools/deploy_campaign_scheduler.sh`

Deployment follows a three-stage process:

  1. Build: Validate GAS script syntax using clasp (Google's Apps Script CLI)
  2. Push: Deploy updated CalendarSync.gs to the bound GAS project
  3. Verify: Invoke the Lambda calendar API with a test event to confirm connectivity

The .clasp.json file in the project directory maps the local code to the GAS project ID, ensuring deployments target the correct project.

Recurring Event Scheduling: Sea Scout Example

During this session, seven recurring Sea Scout Wednesday holds were added to Google Calendar via the Lambda API. Rather than manually clicking 49 times (7 events × 7 weeks), a Python script generated the payload:

import json
from datetime import datetime, timedelta

events = []
start_date = datetime(2025, 4, 30)  # First Wednesday

for week in range(7):
    event_date = start_date + timedelta(weeks=week)
    events.append({
        "action": "add-calendar-event",
        "title": "Sea Scout Wednesday Hold",
        "start_time": event_date.replace(hour=18).isoformat(),
        "end_time": event_date.replace(hour=19, minute=30).isoformat(),
        "description": "Weekly Sea Scout event hold"
    })

# POST each event to the Lambda API

This illustrates a key principle: calendar operations should be scriptable. Humans maintain the source of truth (a JSON file or spreadsheet), and automation handles publication to all platforms.

Key Decisions and Rationale

  • GAS over direct Lambda for polling: Google Apps Script's built-in time-based triggers integrate natively with Google Calendar without credential rotation. Lambda would require periodic credential refresh.
  • Lambda for ad-hoc event creation: Lambda allows dashboards, CLI tools, and external systems to create events without embedding credentials. It's the authoritative HTTP interface.
  • 30-minute sync interval: Balances freshness