Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Synchronizing Event Crew Data Across Google Calendar, DynamoDB, and Static Guest Pages

This session focused on a critical synchronization problem: charter event crew assignments were out of sync across three systems—Google Calendar (source of truth), a DynamoDB dispatch table, and statically generated guest/crew pages. We identified inconsistencies, established a repair workflow, and implemented a multi-system update pattern to prevent future drift.

The Problem: Three Systems, Conflicting Data

The Sailjada charter platform maintains crew assignments in three places:

  • Google Calendar (JADA Internal) — Event titles and descriptions containing crew names; serves as the primary scheduling interface
  • DynamoDB Table — `crew-dispatch` table storing structured crew assignments, event metadata, and guest details
  • Static Guest/Crew Pages — HTML pages generated from DynamoDB, served via CloudFront, displayed to customers and internal teams

A discrepancy surfaced: June 14 events showed different crew assignments in the calendar versus the published guest pages. The calendar listed one crew member; the page showed another. This isn't merely a display issue—it affects crew scheduling, customer communications, and operational reliability.

Technical Investigation: Data Source Mapping

We began by parsing the iCal feed directly from the JADA Internal calendar. The calendar is exposed via a Lambda function that requires OAuth token refresh:


# Fetch calendar events via Lambda (requires valid Google OAuth token)
curl -H "Authorization: Bearer ${GOOGLE_OAUTH_TOKEN}" \
  https://lambda-calendar-endpoint/events?start=2026-05-28&end=2026-06-16

# Parse iCal directly if Lambda is unavailable
curl https://calendar.google.com/calendar/ical/{CALENDAR_ID}/public.ics \
  -o calendar.ics

# Extract event details and crew names from description fields
grep -A 5 "SUMMARY:.*June 14" calendar.ics

Next, we queried the DynamoDB crew dispatch table to compare stored crew assignments:


# Query crew-dispatch table for June 14 events
aws dynamodb query \
  --table-name crew-dispatch \
  --key-condition-expression "event_date = :date" \
  --expression-attribute-values '{":date":{"S":"2026-06-14"}}' \
  --region us-west-2

Finally, we checked the HTTP status and content of guest pages served from CloudFront:


# Verify guest pages are accessible and rendering correctly
curl -I https://crews.sailjada.com/guests/2026-06-14-charter.html
curl https://crews.sailjada.com/guests/2026-06-14-charter.html | grep -A 3 "crew-member"

Root Cause: Manual Calendar Updates Without DynamoDB Sync

The root cause was straightforward but instructive: calendar events had been manually patched via the Google Calendar UI or API without corresponding updates to the DynamoDB record. The crew-dispatch table remained stale, and since static pages are built from DynamoDB (not the calendar), they displayed outdated information.

This revealed a process gap: there was no automated or enforced synchronization between Google Calendar patches and the dispatch database. Engineers were correctly updating the calendar but not the downstream system.

Repair Strategy: Multi-System Update Pattern

Rather than building a complex event-driven sync, we implemented a manual multi-step repair pattern that can be reused:

  1. Verify Calendar State: Parse the iCal feed and extract the authoritative crew assignment from the event description.
  2. Update DynamoDB: Patch the crew-dispatch item with the correct crew names, ensuring timestamps are updated.
  3. Regenerate Pages: Trigger a static page build for affected events to pull fresh data from DynamoDB.
  4. Validate CloudFront: Confirm pages are served correctly and purge any stale CloudFront cache entries.

For the June 14 events, we executed this pattern:


# Step 1: Extract crew from calendar event
CALENDAR_EVENT_ID="abc123def456"
CREW_NAMES="Gene, Quinn"

# Step 2: Update DynamoDB crew-dispatch table
aws dynamodb update-item \
  --table-name crew-dispatch \
  --key '{"event_id":{"S":"'"${CALENDAR_EVENT_ID}"'"},"event_date":{"S":"2026-06-14"}}' \
  --update-expression "SET crew_assigned = :crew, updated_at = :timestamp" \
  --expression-attribute-values '{
    ":crew":{"SS":["Gene","Quinn"]},
    ":timestamp":{"N":"'"$(date +%s)"'"}
  }' \
  --region us-west-2

# Step 3: Trigger static build (Lambda or local build script)
# Rebuild guest pages from DynamoDB
aws lambda invoke \
  --function-name generate-guest-pages \
  --payload '{"event_date":"2026-06-14"}' \
  /tmp/build-output.json

# Step 4: Invalidate CloudFront cache for affected pages
aws cloudfront create-invalidation \
  --distribution-id ${CLOUDFRONT_DIST_ID} \
  --paths "/guests/2026-06-14-charter.html" "/guests/2026-06-14-*"

Google Calendar API Interaction: OAuth Token Refresh

During this work, the Google OAuth token expired. We refreshed it using the stored refresh token in the environment:


# Refresh OAuth token (credentials stored in secure env, not shown here)
curl -X POST https://oauth2.googleapis.com/token \
  -d "client_id=${GOOGLE_CLIENT_ID}" \
  -d "client_secret=${GOOGLE_CLIENT_SECRET}" \
  -d "refresh_token=${GOOGLE_REFRESH_TOKEN}" \
  -d "grant_type=refresh_token"

# Export new access token
export GOOGLE_OAUTH_TOKEN=$(jq -r '.access_token' token_response.json)

We then patched calendar events directly via the Google Calendar API to correct crew assignments:


# Patch Google Calendar event with corrected crew in description
curl -X PATCH \
  -H "Authorization: Bearer ${GOOGLE_OAUTH_TOKEN}" \
  -H "Content-Type: application/json" \
  https://www.googleapis.com/calendar/v3/calendars/primary/events/${EVENT_ID} \
  -d '{
    "description": "Crew: Gene, Quinn. Guests: 8. Departure: 9:00 AM"
  }'

Key Decisions

  • Why not real-time sync? Calendar updates are infrequent and manual; a polling loop or Pub/Sub trigger would add complexity without sufficient benefit. The multi-step repair pattern is transparent and auditable.
  • Why DynamoDB as the single source for pages? Static pages need structured, queryable data; the calendar's unstructured text descriptions don't provide reliable schema. DynamoDB enforces data integrity.
  • Why CloudFront invalidation? Guest pages are cached aggressively (TTL: 3600s) to reduce Lambda execution. Manual invalidation ensures updates appear immediately post-rebuild.

What's Next

This session exposed the need for a more robust workflow:

  • Document the crew update checklist