Building a Crew Dispatch Automation Pipeline: OAuth Token Management, Google Sheets Integration, and DynamoDB Schema Navigation

```html

Over the past development session, we built out critical infrastructure for JADA's crew dispatch system—specifically, the machinery that keeps crew rosters synchronized, captain assignments flowing, and monthly revenue reports generated without manual intervention. This post details the technical decisions, infrastructure patterns, and integration points that make it work.

The Problem: Stateless Credential Refresh at Scale

The core challenge was simple but thorny: our EC2-based provisioning system needs long-lived access to Gmail, Google Drive, and Google Sheets APIs, but OAuth2 tokens expire every hour. Traditional solutions (store refresh tokens in environment variables, rotate via Lambda) create credential-sprawl and operational friction. We needed something that worked reliably across multiple services, survived instance restarts, and could be debugged remotely.

OAuth Token Refresh Architecture

The solution lives in /home/ubuntu/repos/reauth_google.py on the EC2 instance (IP: 34.239.233.28). The script implements a stateful token manager that:

Reads cached credentials from ~/.secrets/token.json (scoped read-write, 0600 permissions)
Detects token expiration by inspecting the expires_at field
Silently refreshes using the refresh_token if needed, before any API call
Re-writes the updated token back to disk atomically
Handles the critical Gmail gmail.send scope plus Drive and Sheets read/write

The key insight: we don't store secrets in settings.json or environment variables. Instead, we bootstrap once (via interactive OAuth on localhost:8484), then let the refresh logic handle expiration transparently. For CI/CD and remote environments, we SSH with port-forwarding into the box:

ssh -L 8484:localhost:8484 ubuntu@34.239.233.28

Then open http://localhost:8484 in a browser. The provisioning Lambda captures the flow and writes the token to the secrets directory.

Google Sheets as the Single Source of Truth

Monthly revenue reporting was previously manual—Quinn or Jonathan would maintain a spreadsheet, we'd copy cells into emails, and reconciliation was error-prone. Now, /tmp/build_sheet.py generates the entire workbook programmatically:

DynamoDB Query: Pull all charter records from the crew-dispatch table for the target month using begins_with(charter_date, '2024-05')
Openpyxl Generation: Build a fresh workbook with a "Template" sheet (frozen headers, formatting rules) and a month-specific tab (e.g., "May 2024")
Reportable Revenue Rule: Only include charters with status = 'completed' and amount > 0; sum the total field for the monthly aggregate
Google Drive Upload: Use service.files().create(...) to push the xlsx to the JADA business folder, capturing the file ID for later retrieval

The template includes named ranges (e.g., May2024_Revenue) so downstream reports can reference cells without hardcoding sheet names. This pattern scales: adding June, July, or any historical month is a one-line change to the date filter.

DynamoDB Schema: The Crew and Charter Ledger

Two tables power the crew dispatch:

crew-dispatch: Partition key = charter_id, sort key = charter_date. Sample record:

{
  "charter_id": "CHR-2024-05-001",
  "charter_date": "2024-05-15",
  "captain": "Quinn Murphy",
  "guests": ["Esmi Gonzalez", "Cathy Vu"],
  "status": "completed",
  "amount": 2500,
  "total": 2500,
  "notes": "Anniversary sail, 7:30 PM departure"
}

roster: Partition key = member_id, attributes include name, phone, email, certifications, and availability slots. The availability sheet in Drive is the read-only reference; DynamoDB is the source of truth for cascade logic (who to call when a charter needs crew).

Both tables live in us-east-1 and us-west-2 (read replicas for failover). The crew-dispatch table uses on-demand billing; roster uses provisioned capacity (2 RCU, 2 WCU) since reads are frequent but writes are rare.

Captain Call-to-Crew: Cascade Logic via Lambda

When a captain needs crew for an upcoming sail, the system triggers a Lambda function that:

Reads the charter record from DynamoDB
Looks up the captain's cascade rule (stored in roster under cascade_rule: ['Abdul', 'Maria', 'Chris'])
Iterates through the list, checking availability from the Drive sheet for that date
Sends Gmail messages to available crew via service.users().messages().send(...), CC'ing the captain and Carole (ops lead)
Logs results to CloudWatch for auditing

The CSV of roster availability is fetched fresh on each call; we don't cache it. This avoids stale data if a crew member updates their availability in Drive.

Roster Management: Adding Abdul Danishwar

When a new crew member joins, /tmp/roster_sheet_add.py handles the workflow:

Accept name, phone, email, certifications (e.g., "Captain", "First Mate", "Deckhand")
Generate a unique member_id (e.g., MBR-2024-0012)
Write the record to the roster DynamoDB table
Add a row to the crew availability sheet in Drive (pairing name against the roster)
Return the magic-link token so Danika can send an onboarding SMS

For Abdul (phone +1 818-730-5220, email abduldan@aol.com), the script:

python3 /tmp/roster_sheet_add.py \
  --name "Abdul Danishwar" \
  --phone "+1-818-730-5220" \
  --email "abduldan@aol.com" \
  --certifications "Captain"

This creates the DynamoDB row, appends to the availability sheet, and returns a short code that Danika's Lambda can decode to send the crew-page link via SMS.

Magic Link URL Routing and Danika Integration

The crew onboarding page (hosted on shipcaptaincrew, behind CloudFront distribution ID E1234ABCD5678) uses magic tokens stored in DynamoDB. When Danika's Lambda processes a crew-page request:

Decode the