Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Integrating Crew Management with Multi-Domain Infrastructure: Gmail→Calendar→DynamoDB Sync

This post details the technical implementation of a crew scheduling and hours-tracking system that bridges Gmail notifications, Google Calendar events, and a DynamoDB-backed crew management API. The challenge was unifying disparate data sources across three separate domains while maintaining a clean separation between admin and crew-facing interfaces.

What We Built

The core objective was to:

  • Extract crew assignment data from Gmail notifications and calendar metadata
  • Synchronize crew roles (Captain, 1st Mate, 2nd Mate, Host) across a centralized calendar event
  • Store crew assignments and hours in DynamoDB for the ShipCaptainCrew (SCC) backend API
  • Add a crew-facing hours-logging interface to the ops subdomain frontend
  • Implement a manual hours-entry endpoint for admin corrections

Email-to-Calendar Data Pipeline

The journey began with Gmail as the source of truth. We queried jadasailing@gmail.com for emails referencing specific crew members (Jennifer Sanderson charter on 2026-05-12) using the Gmail API with appropriate scopes:

Token scopes required:
- https://www.googleapis.com/auth/gmail.readonly (for email search)
- https://www.googleapis.com/auth/calendar (for event updates)

The search revealed crew confirmation emails from Carole containing structured role assignments. Rather than parsing free-form text, we leveraged email metadata and subject patterns to extract the canonical crew roster:

  • Captain: Gene O'Neal
  • 1st Mate: Angela Wong
  • 2nd Mate: Dan Rich
  • Host: Keely Hoyt (added separately for the May 12 event)

This data was then written to the calendar event description field on the Google Calendar resource for events/jennifer-sanderson-charter-2026-05-12. The calendar event became the intermediate source of truth, accessible to both the ops page and backend services.

Infrastructure: Multi-Domain Architecture

The system spans three distinct CloudFront distributions and two S3 buckets:

  • ops.queenofsandiego.com: Admin-facing ops dashboard
    • S3 bucket: ops-queenofsandiego-com
    • CloudFront distribution: E**** (ops subdomain)
    • Source file: /Users/cb/Documents/repos/sites/ops/index.html
  • ShipCaptainCrew (SCC) Frontend: Crew-facing hours and scheduling interface
    • S3 bucket: scc-frontend-bucket
    • CloudFront distribution: E**** (SCC)
    • Source file: /tmp/scc_index.html
  • ShipCaptainCrew Lambda API: Backend business logic
    • Handler: /tmp/scc_lambda/lambda_function.py
    • API Gateway integration: Routes POST requests to crew endpoints
    • DynamoDB table: ShipCaptainCrew with crew and hours_log records

All three services communicate via API Gateway URL (stored as an environment variable in the Lambda function and hardcoded in the SCC frontend). CORS headers are configured to allow requests from both the ops subdomain and the SCC frontend domain.

DynamoDB Schema: Crew and Hours

The ShipCaptainCrew DynamoDB table uses a single-table design with composite keys:

Crew record example:
{
  "pk": "CREW#jennifer-sanderson-2026-05-12",
  "sk": "METADATA",
  "event_date": "2026-05-12",
  "captain": "Gene O'Neal",
  "first_mate": "Angela Wong",
  "second_mate": "Dan Rich",
  "host": "Keely Hoyt",
  "crew_members": ["Angela Wong", "Dan Rich", "Gene O'Neal", "Keely Hoyt"]
}

Hours record example:
{
  "pk": "CREW#jennifer-sanderson-2026-05-12",
  "sk": "HOURS#Angela Wong",
  "clock_in_unix": 1715005500,  // 3:45 PM UTC
  "clock_out_unix": 1715029200, // 9:00 PM UTC
  "duration_minutes": 395        // in quarter-hour increments
}

This schema allows efficient queries: fetch all crew metadata with a single query on the partition key, and retrieve individual crew hours with a range key prefix query.

Frontend Hours-Logging UI

The SCC frontend (/tmp/scc_index.html) was extended with an hours-logging interface modeled after the provided reference design. Key features:

  • Time input: Quarter-hour increments (00, 15, 30, 45) to match operational standards
  • Clock in/out buttons: Submit timestamps to a new POST /clock endpoint (or POST /set-hours for manual entry)
  • Role-based display: The frontend reads sessionRole from browser session storage to determine whether to show crew or admin interfaces
  • Hours summary: Fetches and displays the current crew's hours from the API response

Authentication for the crew page uses a simple password hash scheme. The crew password hash is stored in Lambda environment variables (CREW_PASS_HASH and ADMIN_PASS_HASH) as SHA-256 digests rather than plaintext.

Lambda Endpoint: Manual Hours Entry

A new admin-only endpoint was added to the Lambda handler to permit manual hour corrections without UI intervention:

POST /set-hours
Request body:
{
  "event_key": "jennifer-sanderson-2026-05-12",
  "crew_member": "Angela Wong",
  "clock_in_unix": 1715005500,
  "clock_out_unix": 1715029200,
  "admin_password": "[hashed password]"
}

Response:
{
  "status": "success",
  "hours_log": {
    "clock_in_unix": 1715005500,
    "clock_out_unix": 1715029200,
    "duration_minutes": 395
  }
}

The endpoint validates the admin password hash before permitting updates. This design allows Carole or other admins to correct entries (e.g., Keely's clock-in time from a typo) without pushing code changes.

Deployment Pipeline

All changes were deployed via AWS CLI and the Python SDK:

  • S3 uploads: aws s3 cp ops/index