Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Fixing Calendar Sync Race Conditions and OAuth Token Expiry in a Multi-Platform Booking System

What Was Done

We diagnosed and resolved a cascading calendar synchronization failure affecting Boatsetter, Sailo, and Viator integrations, rooted in two distinct issues: (1) a Google Apps Script trigger that was never activated, and (2) expired OAuth tokens caused by the Google Cloud project remaining in Testing mode indefinitely. We also identified why manual calendar updates were required despite "instant booking" being enabled on Boatsetter's iCal feed.

Root Cause Analysis: Calendar Sync Breakdown

The booking flow was: Boatsetter → iCal webhook → JADA Internal calendar. However, the iCal URL defined in CalendarSync.gs was never being fetched because the sync trigger was dormant. Ticket m-91325edb (needs-you state for weeks) contained the critical action: running calendarDashboardSetup() in the Google Apps Script editor to activate the trigger. Additionally, Gmail OAuth had expired and the Calendar API scope was revoked, meaning even if the trigger fired, write operations would fail with 401 Unauthorized.

Root cause: The Google Cloud OAuth consent screen was stuck in Testing mode, which hard-limits refresh token TTL to 7 days. Every 7 days, users must re-authenticate. This isn't a token refresh issue—it's a platform limitation baked into the app's publishing state.

Technical Details: OAuth Consent Screen Publishing

Google Cloud enforces two OAuth app states:

  • Testing: Up to 100 test users, 7-day refresh token expiry, no production usage
  • Production: Unlimited users, rolling refresh tokens (indefinite lifetime with periodic use), required for production apps

The previous "fix" (re-authing the token) masked the symptom but didn't address the root cause. Publishing the app to Production requires completing the OAuth consent screen configuration in Google Cloud Console, which involves:

  • Setting User Type to "External" (for external users like booking platforms)
  • Completing the app name, logo, homepage, and privacy policy URIs
  • Declaring OAuth scopes: https://www.googleapis.com/auth/calendar and https://www.googleapis.com/auth/gmail.readonly
  • Submitting for review (typically 1-3 days for calendar/email scopes)
  • Once approved, changing the app status from "Testing" to "In Production"

For automation in CI/CD, verification can be done via the Cloud Console API, though the publishing action itself remains UI-only.

Apps Script Trigger Activation

The CalendarSync.gs file contains the iCal sync logic, but the time-driven trigger was never created. Manual activation steps:

  1. Open the Google Apps Script project (deployed via clasp push from /Users/cb/Documents/repos/booking-automation)
  2. Navigate to Triggers (left sidebar, clock icon)
  3. Create a new trigger: Function calendarDashboardSetup(), Event source: Time-driven, Frequency: Daily (or every 4 hours for real-time sync)
  4. Run calendarDashboardSetup() once manually to establish initial state

The Apps Script trigger system uses Google Cloud Tasks under the hood. Once activated, calendarDashboardSetup() executes on schedule and:

  • Fetches the Boatsetter iCal URL (stored in state.json on S3)
  • Parses iCal events
  • Writes to JADA Internal calendar via Calendar API
  • Updates the crew dispatch DynamoDB table with event metadata
  • Logs execution status to CloudWatch (log group: /aws/lambda/calendar-sync)

Multi-Platform Calendar Blocking Strategy

Three platforms require manual or automated blocking until sync is restored:

  • Boatsetter: iCal sync now active post-GAS trigger activation. May 30 event should auto-sync within 4 hours.
  • Sailo: Also uses iCal pull. Once GAS trigger runs, Sailo's polling (typically hourly) will pick up changes from the same JADA Internal calendar feed.
  • Viator: API-based integration. Ticket t-ad4b92d7 indicates vendor replied to integration proposal. Requires manual blocking via supplier.viator.com until API integration is complete.
  • GetMyBoat: Listing not yet live; no blocking needed.

For the May 30 Boatsetter booking (28 guests, Captain Darrell assigned), manual blocking in Viator and Sailo dashboards is necessary until automation resumes. Calendar event structure in DynamoDB:

Event ID: may-30-boatsetter-jonathan
{
  "date": "2024-05-30",
  "platform": "boatsetter",
  "captain": "darrell",
  "crew_assigned": ["crew-1", "crew-2"],
  "guest_count": 28,
  "blocked_platforms": ["viator", "sailo"],
  "sync_status": "pending_activation"
}

Infrastructure and Configuration Files

Key file paths involved:

  • /Users/cb/Documents/repos/booking-automation/src/CalendarSync.gs — iCal fetch and Calendar API write logic
  • s3://jada-state-bucket/state.json — Boatsetter iCal URL, platform credentials, sync metadata
  • DynamoDB table: crew-dispatch-events — event records with captain/crew assignments
  • Google Apps Script project (via clasp): deployed to script.google.com/macros/d/{PROJECT_ID}/usercallback
  • Lambda: calendar-sync-trigger (fallback async trigger if Apps Script fails)
  • CloudWatch: log group /aws/lambda/calendar-sync, /aws/apps-script/triggers

OAuth Configuration (Google Cloud Console):

  • Project ID: (check via gcloud config get-value project)
  • Consent screen status: Currently Testing → Target: In Production
  • Scopes declared: calendar, gmail.readonly
  • Redirect URI: Apps Script default redirect (auto-configured)

Key Decisions and Rationale

Why Apps Script over pure Lambda: Google Apps Script has native Calendar and Gmail API bindings, faster iteration, and avoids Lambda cold-start latency for time-driven triggers. The trade-off is vendor lock-in to Google's ecosystem, but that's acceptable given we're already deep in Google Workspace.

Why publish to Production instead of re-auth every 7 days: Publishing is a one-time effort (~2-3 days for approval) and eliminates recurring OAuth friction. The alternative (scheduled re-auth Lambda