Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Synchronizing Charter Crew Assignments Across Google Calendar, DynamoDB, and Guest Pages

This session focused on a critical operational gap: crew assignments recorded in the JADA Internal Google Calendar weren't consistently reflected in the public-facing guest pages and the DynamoDB event records that power the waiver and crew dispatch systems. When a charter's crew roster changes, that update must propagate across three systems simultaneously—calendar, database, and web pages—or guests receive outdated contact information and crew dispatch fails. Here's how we solved that cascade.

The Problem: Fragmented Data Across Three Systems

The JADA charter operation manages three synchronized data sources:

  • Google Calendar (JADA Internal) — source of truth for crew assignments and event metadata
  • DynamoDB event records — indexed by event ID, powers Lambda functions for waiver processing and crew dispatch
  • S3-hosted guest pages — static HTML served via CloudFront, displayed to guests with crew contact details and arrival times

A crew change made in Google Calendar (e.g., swapping captains on the June 14 charter) didn't automatically update DynamoDB or regenerate the guest pages. This meant guests could see outdated crew information, and the Lambda crew dispatch function would fail to find the correct crew member in the database.

Technical Approach: Audit → Update → Deploy

The fix required three sequential operations:

1. Audit Current Calendar State

We fetched the JADA Internal calendar via the iCal endpoint (which doesn't require fresh OAuth tokens):

curl -s "https://[internal-calendar-ical-url]" | grep -A 50 "SUMMARY:Quinn\|SUMMARY:Jonathan\|SUMMARY:Danika"

This identified the June 14 event had incorrect crew: Darrell and Angelia were listed, but should have been Gene as captain with updated times (3–7 PM paid vs. the incorrect afternoon slot).

2. Update Google Calendar via Lambda

Rather than manually editing the calendar, we used a Lambda function that patches Google Calendar events via the Google Calendar API. The Lambda handler accepts the event ID and a JSON payload with the updated crew and times:

POST /calendar-sync
Content-Type: application/json

{
  "eventId": "june14-ariel-loback-charter",
  "captain": "Gene",
  "crewMembers": ["Gene"],
  "startTime": "2026-06-14T15:00:00Z",
  "endTime": "2026-06-14T19:00:00Z",
  "paid": true
}

The Lambda function:

  • Validates the OAuth token (refreshes if stale via the refresh_token stored in AWS Secrets Manager)
  • Calls calendar.events().patch() with the calendar ID jada-internal@sailjada.com and updated event body
  • Returns the patched event metadata (confirms the update succeeded)

3. Sync DynamoDB Event Records

After confirming the calendar was updated, we created or updated the corresponding DynamoDB record in the SaiLJADA-Events table (region: us-west-2):

aws dynamodb put-item \
  --table-name SaiLJADA-Events \
  --item '{
    "eventId": {"S": "june14-ariel-loback-charter"},
    "date": {"S": "2026-06-14"},
    "charteredBy": {"S": "Ariel Loback"},
    "captain": {"S": "Gene"},
    "crewMembers": {"L": [{"S": "Gene"}]},
    "startTime": {"S": "15:00"},
    "endTime": {"S": "19:00"},
    "paid": {"BOOL": true},
    "waiverStatus": {"M": {}}
  }' \
  --region us-west-2

This ensures the crew dispatch and waiver Lambda functions read the correct crew information. The DynamoDB record is the single source of truth for backend operations.

4. Regenerate and Deploy Guest Pages

Guest pages are static HTML templates stored in S3 at s3://sailjada-guest-pages/ and distributed via CloudFront (distribution ID: E2KQKX8EXAMPLE). We:

  • Downloaded existing templates from S3 (e.g., danika-guest.html, jonathan-afternoon.html, quinn-guest.html)
  • Updated crew names, arrival times, and contact info using string substitution
  • Re-uploaded to S3 with --cache-control "max-age=300" (5-minute TTL for testing)
  • Invalidated CloudFront cache: aws cloudfront create-invalidation --distribution-id E2KQKX8EXAMPLE --paths "/*"

This ensures guests see the latest crew assignments within 5 minutes of the calendar update.

Infrastructure Changes

Lambda Function Updated: calendar-sync handler now validates OAuth refresh and patches multiple event properties atomically. Error handling catches API rate limits and stale tokens.

DynamoDB Record Format: The SaiLJADA-Events table now includes captain and crewMembers as discrete fields (previously embedded in event description text). This allows crew dispatch Lambda to query directly without parsing strings.

Guest Page Deployment: Added HTTP health checks post-deployment to verify pages are accessible at https://sailjada.com/guest/[eventId].html via CloudFront before notifying crew.

Key Decisions

  • Why use Lambda for calendar updates? It centralizes OAuth token refresh and eliminates manual calendar editing. Future crew changes can be automated via a simple REST call or scheduled job.
  • Why DynamoDB as the backend source of truth? Google Calendar API has rate limits and latency. DynamoDB queries are sub-millisecond and support direct crew lookups by event ID.
  • Why invalidate all CloudFront paths? Guest pages are infrequently updated but must be current when they are. Broad invalidation ensures no stale copies linger.

Verification

We tested the full pipeline:

curl -s https://sailjada.com/guest/june14-ariel-loback-charter.html | grep "Gene"
# Confirms crew name appears in deployed HTML

aws dynamodb get-item \
  --table-name SaiLJADA-Events \
  --key '{"eventId": {"S": "june14-ariel-loback-charter"}}' \
  --region us-west-2 \
  | jq '.Item.captain.S'
# Returns: "Gene"

What's Next

We recommend building a Lambda trigger on DynamoDB Streams so that when a crew record is updated, the corresponding guest page is automatically regenerated and redeployed. This eliminates the manual guest page sync step and ensures consistency across all three systems in real time.