Integrating Manual Crew Hours Entry into ShipCaptainCrew: From Calendar Events to Real-Time Clock Tracking

What Was Done

We implemented a complete crew hours management workflow for the Queen of San Diego operations system, spanning from Gmail/Calendar data ingestion through real-time API endpoints. The work involved:

Extracting crew assignments from Gmail confirmations and syncing them to Google Calendar events
Persisting crew role data (Captain, 1st Mate, 2nd Mate) to ShipCaptainCrew DynamoDB tables
Creating a manual set-hours Lambda endpoint to retroactively log crew hours in quarter-hour increments
Building a crew-facing UI component on the ops subdomain to display and manage hours
Implementing CORS-compliant API calls between the ops.queenofsandiego.com frontend and the ShipCaptainCrew API Gateway

Technical Architecture Overview

The system consists of three primary layers:

Data Ingestion Layer (Gmail + Calendar): We used unified OAuth tokens with Gmail and Calendar API scopes to search crew confirmation emails and update calendar events with structured crew metadata. This created the single source of truth for charter crew assignments.

Persistence Layer (DynamoDB): The ShipCaptainCrew backend stores crew assignments in a DynamoDB table with the following structure:

Primary Key: Charter event ID (e.g., Jennifer Sanderson event on 2026-05-12)
Attributes: captain, first_mate, second_mate, host, hours_log (array of crew hour entries)
Each hours entry contains: crew member name, role, start time (Unix timestamp), end time (Unix timestamp), duration in minutes

API Layer (Lambda + API Gateway): A new set-hours endpoint was added to the Lambda function at /tmp/scc_lambda/lambda_function.py to accept manual hour entries with admin-level authorization.

Infrastructure Components

Frontend Deployment:

Source files: /Users/cb/Documents/repos/sites/ops/index.html (4 iterations)
S3 bucket: ops.queenofsandiego.com (inferred from deployment commands)
CloudFront distribution: invalidated after each deployment
CORS headers configured to allow requests from ops domain to ShipCaptainCrew API Gateway

Backend Deployment:

Lambda function: Updated at /tmp/scc_lambda/lambda_function.py with new set-hours endpoint
Environment variables checked for API URLs and configuration
Authentication pattern: Admin-level role validation (derived from sessionRole variable stored in frontend state)

Data Store:

DynamoDB table: ShipCaptainCrew (exact table name inferred from commands)
Data structure supports both real-time clock punch endpoints and retroactive manual entry

Implementation Details

1. Calendar Event to DynamoDB Sync

The Jennifer Sanderson charter event on 2026-05-12 was enriched with crew assignments extracted from Gmail confirmations:

From: carole@queenofsandiego.com
Subject: Key Crew Confirmation - Jennifer Sanderson Charter

Captain: Gene O'Neal
1st Mate: Angela Wong
2nd Mate: Dan Rich
Host: Keely Hoyt

This data was written to the calendar event description and simultaneously persisted to DynamoDB, ensuring both the ops calendar and the ShipCaptainCrew system remained synchronized.

2. Manual Hours Entry Endpoint

A new Lambda handler was added to accept POST requests with the following payload structure:

POST /set-hours
{
  "event_id": "jennifer-sanderson-05-12-2026",
  "crew_entries": [
    {
      "name": "Angela Wong",
      "role": "1st Mate",
      "start_time": 1715611500,  // 3:45 PM UTC timestamp
      "end_time": 1715632200,    // 9:00 PM UTC timestamp
      "quarters_worked": 21      // 5.25 hours in quarter-hour increments
    }
  ]
}

The endpoint validates the requesting user's sessionRole against an admin whitelist before writing to DynamoDB. This prevents unauthorized hours manipulation while allowing authorized crew managers to backfill hours for off-grid charter operations.

3. Frontend Hours UI Component

The ops subdomain frontend was updated with a crew hours entry form that:

Displays confirmed crew members for the selected charter (pulled from DynamoDB via the main API)
Allows quarter-hour granularity input (0:00, 0:15, 0:30, 0:45 increments)
Calculates Unix timestamps from local times to normalize across time zones
Submits via CORS-compliant fetch to the ShipCaptainCrew API Gateway
Displays confirmation with hours_log returned in the API response

The form mirrors the UX pattern shown in the reference photo, with a simple start/end time picker and a role-based display of assigned crew.

Key Architectural Decisions

Why Unix Timestamps? Charter operations span multiple time zones. Storing hours as Unix timestamps ensures consistency regardless of local time conversion, with the frontend handling display formatting per the user's timezone.

Why Quarter-Hour Increments? Maritime crew scheduling traditionally uses 15-minute intervals for billing and compliance. This granularity matches industry standards while remaining queryable (60 minutes ÷ 4 = easily summed in reporting).

Why Admin-Level Authorization? Allowing any user to modify crew hours would create liability and compliance risks. The admin pattern leverages the existing sessionRole variable already in use throughout the SCC frontend, reducing new auth logic and keeping role decisions centralized.

Why DynamoDB for Hours? The existing ShipCaptainCrew backend already uses DynamoDB for charter events. Storing hours_log as an array attribute on the same item avoids schema fragmentation and simplifies transactional consistency when updating crew assignments and hours simultaneously.

Deployment & Validation

After code changes to both Lambda and frontend:

Updated Lambda function deployed with new set-hours handler
Ops page HTML rebuilt with hours entry form component
Frontend deployed to S3 and CloudFront cache invalidated to ensure immediate propagation
Crew hours for Angela Wong (3:45–9:00 PM, 21 quarters) manually entered via new endpoint
Verified hours_log returned in subsequent charter event API calls

What's Next

Future iterations should consider: