Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Crew Dispatch: Building a Multi-Service Integration Layer for Dynamic Roster Management

Over the past development cycle, we rebuilt the crew roster synchronization pipeline for Queen of San Diego's charter operations. This post covers the infrastructure decisions, service integrations, and automation patterns we implemented to keep crew availability, captain cascades, and charter assignments in sync across Gmail, Google Sheets, DynamoDB, and Lambda-driven dispatch systems.

What Was Done

The core problem: crew availability and captain assignments were spread across three disconnected systems—a Google Sheets roster, DynamoDB crew records, and ad-hoc email coordination. When a charter was booked or a captain became unavailable, there was no single source of truth, and manual captain-call cascades were error-prone.

We implemented:

  • A unified roster schema in DynamoDB (crew-dispatch table) with captain-first cascade rules
  • A Google Sheets sync that reads/writes crew availability across multiple tabs (pairing captains with crew members)
  • A magic-link token system for crew self-service charter acceptance, stored in DynamoDB and validated by Lambda
  • A captain-first call-to-crew orchestration via Gmail that respects rank and availability windows
  • OAuth token refresh automation to keep Google API credentials alive across long-running operations

Technical Details: The Integration Stack

DynamoDB Roster Schema

The crew-dispatch table stores crew members with the following key fields:

{
  "crew_id": "abdul-danishwar",
  "name": "Abdul Danishwar",
  "phone": "+1-818-730-5220",
  "email": "abduldan@aol.com",
  "rank": "captain",
  "availability": {
    "2024-06-27": "available",
    "2024-06-28": "unavailable"
  },
  "cascade_order": 1,
  "created_at": "2024-06-15T10:30:00Z"
}

The charter-chats table (sampled during schema validation) holds per-charter crew assignments and message threads. This allows us to track which crew members are assigned, who has accepted, and any charter-specific communication history.

Google Sheets Synchronization

We maintain a master crew availability spreadsheet in Google Drive with multiple tabs:

  • Roster — full crew member list with contact info and rank
  • June Availability — captains paired with assigned crew; updated weekly from DynamoDB
  • July Availability — forward-looking assignments

The synchronization script (/tmp/roster_sheet_add.py) uses the Google Sheets API to:

  1. Query the crew-dispatch DynamoDB table for all crew members and their availability windows
  2. Parse the crew-availability spreadsheet tabs and identify captain-crew pairings
  3. Write new roster entries back to the Sheet (e.g., adding Abdul Danishwar as captain for June 27)
  4. Maintain cell formatting (dates, contact columns, rank indicators)

Why this approach? Sheets is the operational interface for the crew/office staff; they need a human-readable, editable view. DynamoDB is the system of record for programmatic queries and Lambda integrations. Bidirectional sync keeps both authoritative.

Magic-Link Token System

When a crew member receives a captain-call email, they get a custom URL like:

https://shipcaptaincrew.com/crew/accept?token=jada_crew_abc123xyz&charter_id=charter_5678&expires=2024-06-27T19:00:00Z

The token is generated and stored in DynamoDB with:

  • token_id (partition key): jada_crew_abc123xyz
  • crew_id, charter_id, expires_at (attributes)
  • A Lambda authorizer validates the token, checks expiry, and confirms the crew member's availability status

The token format is intentionally simple and URL-safe. We store the full record so we can:

  • Audit who accepted which charters and when
  • Implement one-time-use tokens (mark used_at after first accept)
  • Expire tokens if the charter is cancelled

Captain-First Call Cascade

The send_captain_call.py script implements a rank-aware dispatch algorithm:

# Pseudo-logic
charter = get_charter(charter_id)
available_captains = query_crew_dispatch(
    rank="captain",
    date=charter.sail_date,
    availability="available"
)
# Sort by cascade_order (1, 2, 3...)
available_captains.sort(key=lambda c: c.cascade_order])

for captain in available_captains:
    send_acceptance_email(
        to=captain.email,
        charter=charter,
        magic_token=generate_token(captain, charter)
    )
    if captain_accepted_within_timeout:
        # Assign captain and their paired crew
        assign_crew_to_charter(captain.paired_crew)
        # Send crew acceptance emails with their own tokens
        for crew in captain.paired_crew:
            send_acceptance_email(to=crew.email, ...)
        break  # Captain committed; stop cascade
    else:
        # Try next captain in order
        continue

The email goes to Carole (office) and the captain. Why the office CC? So we have an auditable record in Gmail of who was called, what time, and can manually intervene if the cascade stalls.

Infrastructure and Deployment

OAuth Token Refresh Automation

Google OAuth tokens expire in 1 hour. For long-running jobs (like batch roster updates), we implemented a patched reauth flow in /tmp/reauth_patched.py:

  • On the EC2 instance (ubuntu@34.239.233.28), we store a refresh_token in ~/.secrets/google_refresh.json
  • Before any Sheets or Gmail API call, the script calls refresh_access_token()
  • If the refresh fails (e.g., token revoked), the script exits with a clear error so we know to re-authorize interactively

This avoids brittle token-caching and ensures we can recover from auth failures without manual SSH intervention mid-script.

Gmail Statement Generation and Delivery

The monthly revenue statement for crew members is generated as an XLSX and emailed via Gmail:

python /tmp/build_sheet.py  # Generate workbook from charter records
scp local_output.xlsx ubuntu@34.239.233.28:/tmp/
ssh ubuntu@34.239.233.28 python /tmp/send_statement