Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Integrating Multi-Service Crew Management: Syncing Gmail, Calendar, and DynamoDB for Charter Operations

This post details the technical workflow implemented to unify crew assignment data across Gmail, Google Calendar, and a DynamoDB-backed crew management system. The goal was to establish a single source of truth for charter crew roles while maintaining data consistency across three distinct services.

What Was Done

We implemented an end-to-end crew assignment workflow that:

  • Queried Gmail for charter confirmation emails containing crew role assignments
  • Extracted structured crew data (Captain, 1st Mate, 2nd Mate) from unstructured email content
  • Updated Google Calendar events with crew assignment metadata
  • Persisted crew assignments to a DynamoDB table (`ShipCaptainCrew`) for API consumption
  • Deployed updated ops dashboard frontend to S3 with CloudFront cache invalidation

Technical Details: OAuth Token Strategy

The implementation relied on a unified OAuth token with multiple scopes. Before executing any queries, we verified the token possessed the necessary permissions:

Scopes verified:
- gmail.readonly (search and read emails)
- calendar.events (read and modify calendar events)
- (implicit DynamoDB access via IAM role attached to Lambda/execution environment)

This "unified token" approach eliminated the need for multiple authentication flows. Rather than managing separate tokens for each service, a single token with broad scopes simplified the orchestration layer.

Gmail Querying and Data Extraction

We searched the jadasailing@gmail.com inbox for emails related to a specific charter (Jennifer Sanderson event) within the last 30 days:

Gmail search query pattern:
- Subject/body contains: "Jennifer Sanderson"
- Date range: last 30 days
- Message type: booking confirmations and crew assignment emails

Key emails identified:

  • Carole's confirmation email: Contained explicit crew role assignments in natural language
  • Booking report emails: Confirmed charter date and participant details
  • Calendar invitation confirmations: Cross-referenced with calendar events

Crew assignments extracted from email content:

  • Captain: Gene O'Neal
  • 1st Mate: Angela Wong
  • 2nd Mate: Dan Rich (also referenced as "Danny")

Why this approach: Gmail remains the source of truth for crew assignments at Queen of San Diego. Rather than building a dedicated crew assignment UI, we leverage existing email workflows and extract structured data from unstructured content. This minimizes training overhead and keeps the operational process in the tools operators already use.

Calendar Event Enrichment

Once crew roles were confirmed, we located the corresponding calendar event for the Jennifer Sanderson charter on May 12 and updated its metadata:

Update operation on Google Calendar event:
- Event ID: [dynamic, retrieved via API]
- New description field: Included crew assignments (Captain, 1st Mate, 2nd Mate names)
- Extended properties: Optionally stored crew data in custom event properties for API consumption

The calendar event now serves as a human-readable reference point while also providing structured data for downstream systems.

DynamoDB Persistence Layer

The ShipCaptainCrew DynamoDB table was updated with the following record structure:

Table: ShipCaptainCrew
Primary key: Charter ID (Jennifer Sanderson May 12 event)
Attributes updated:
- captain: "Gene O'Neal"
- first_mate: "Angela Wong"
- second_mate: "Dan Rich"
- timestamp: ISO 8601 format
- source: "gmail_extraction"

Why DynamoDB: The ShipCaptainCrew system is Lambda-backed with DynamoDB as the persistence layer. This allows the ops dashboard (and any other frontend) to query crew assignments via API Gateway with millisecond latency, rather than parsing Gmail or Calendar APIs on-demand. DynamoDB also enables future features like crew availability tracking, historical assignment audits, and role-based access control.

Infrastructure: Frontend Deployment Pipeline

After confirming crew assignments in the backend systems, we updated the ops dashboard frontend and deployed it through our standard pipeline:

Source Files

Local repository path:
/Users/cb/Documents/repos/sites/ops/index.html

Changes made:
- Added crew assignment display section (retrieved from ShipCaptainCrew API)
- Updated charter detail cards to show Captain, 1st Mate, 2nd Mate
- Added responsive layout for mobile ops crew viewing

S3 Deployment

S3 bucket: ops.queenofsandiego.com (production)
Deployment command pattern:
aws s3 sync ./sites/ops s3://ops.queenofsandiego.com/ --delete

Files deployed:
- index.html (main page)
- Supporting CSS/JS assets (if modified)

CloudFront Cache Invalidation

CloudFront distribution: [ops subdomain distribution ID]
Invalidation pattern:
aws cloudfront create-invalidation --distribution-id [ID] --paths "/*"

Rationale: Full path invalidation ensures all users see the updated crew assignments immediately, rather than waiting for the 24-hour TTL to expire.

Distribution architecture: The ops subdomain is served through CloudFront with an S3 origin. This provides:

  • Global edge caching for fast loads
  • HTTPS termination at the edge
  • DDoS protection via AWS Shield
  • Origin hiding (users never see S3 URLs directly)

API Integration: ShipCaptainCrew Backend

The ops dashboard frontend consumes crew data via the ShipCaptainCrew API Gateway endpoint. The architecture:

API Gateway → Lambda function → DynamoDB (ShipCaptainCrew table)

Endpoint pattern:
GET /charters/{charter-id}/crew

Frontend call (ops/index.html):
fetch('https://[SCC-API-URL]/charters/jennifer-sanderson-may-12/crew')
  .then(r => r.json())
  .then(data => renderCrewAssignments(data))

CORS configuration: The ShipCaptainCrew API was configured to accept requests from the ops.queenofsandiego.com domain, enabling cross-origin calls from the frontend without browser security violations.

Key Technical Decisions

  • Unified OAuth token: Simplified orchestration but required careful scope management. A future improvement would implement token refresh logic for long-running operations.
  • Gmail as source of truth: Leverages existing operational workflows but requires robust email parsing. Consider implementing email schema standardization (structured email templates) to reduce parsing fragility.
  • DynamoDB for crew data: Provides low-latency API responses but requires synchronization logic. A message queue (SQS/SNS) could decouple Gmail extraction from DynamoDB writes for eventual consistency patterns.
  • CloudFront full invalidation: