Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating JADA Crew Dispatch: OAuth Token Refresh, Google Sheets Integration, and SMS Cascade Architecture

Over the past development session, we tackled a critical bottleneck in the JADA crew-dispatch system: automating monthly revenue reports, integrating Google Sheets with DynamoDB crew rosters, and building a captain-first SMS cascade for last-minute charter calls. This post walks through the infrastructure decisions, the OAuth token-refresh patch that unblocked the work, and the architecture we're building to scale crew coordination.

The Problem: Token Expiry and Cascading Manual Work

The core issue was straightforward but systemic. The reauth_google.py script on the EC2 box (Ubuntu instance at 34.239.233.28) was failing to refresh Google OAuth tokens silently. Every month, the token would expire, and the entire pipeline—Gmail lookups, Sheets reads/writes, Drive file management—would stall until someone manually re-authenticated.

This cascaded into manual SMS routing: captains weren't getting called in the right order, crew availability wasn't being cross-checked against the roster, and monthly revenue reports (critical for Sergio's accounting) were being built ad-hoc in spreadsheets rather than through a reliable, versioned pipeline.

Technical Approach: Three Parallel Work Streams

1. OAuth Token Refresh Repair

We identified that reauth_google.py was using an outdated token-refresh pattern. The script lived at ~/repos/queenofsandiego/reauth_google.py on the EC2 instance. The fix involved:

  • Patching the refresh logic to use the google.auth.transport.requests.Request class instead of manual HTTP calls
  • Ensuring the credentials JSON (stored in ~/repos/.secrets/google_token.json) was being read and written atomically
  • Adding explicit error handling for expired refresh tokens (which require manual intervention but now fail loudly rather than silently)

The patched version was deployed via SSH with a backup of the original:

# On the box, verify syntax before going live
python3 -m py_compile reauth_google.py

# Backup original
cp reauth_google.py reauth_google.py.backup

# Deploy and verify token refresh works
python3 reauth_google.py

Once the token refresh worked, subsequent scripts could call it as a utility function rather than reimplementing the logic each time.

2. Monthly Revenue Report Automation

The revenue report generation involved pulling real charter data from the DynamoDB crew-dispatch table, cross-referencing with the charter-chats table for guest information, and generating a formatted Excel workbook that matches the company's standard format (sourced from prior months).

Key steps:

  • Data Pull: Query crew-dispatch for all charters in the target month (e.g., May), filtering by status and extracting guest counts, total revenue, captain, and date
  • Schema Matching: Read the prior month's report from Google Drive to extract the exact column order, number formatting (currency), and footnote structure
  • Excel Generation: Use openpyxl to build a new workbook with the Template sheet as a base, populate May data into a new "May" tab, and preserve formatting
  • Upload to Drive: Use the (now-refreshed) Google Sheets API to upload the workbook to the shared JADA business folder
  • Email Distribution: Search Gmail for the prior month's statement recipients, generate a clean single-sheet attachment, and send via SMTP

The report generation scripts were staged in /tmp during development and validated before being moved to the repos directory:

# Example: Pull May charters from DynamoDB
python3 /tmp/build_may_tab.py
# Generates workbook with May data merged into template

# Upload to Drive (requires valid OAuth token)
python3 /tmp/upload.py

# Send statement email
python3 /tmp/send_statement.py

3. Crew SMS Cascade for Charter Calls

The cascade system is a captain-first SMS notification pattern: when a last-minute charter call comes in, the system identifies the best-available captain (based on the crew-availability spreadsheet in Drive), sends them an SMS with the charter details and a magic-link to accept/decline, then cascades to other crew if the first captain declines.

Architecture:

  • Magic Links: Decode Danika short codes from DynamoDB (the crew-dispatch table stores encrypted tokens for each crew member). These tokens are used to build URLs like https://shipcaptaincrew.com/magic?token=<TOKEN>
  • Availability Lookup: Read the crew-roster and availability spreadsheet from Google Drive; this sheet has tabs for each crew member with their available dates/times
  • Roster Management: The DynamoDB crew-dispatch table stores the crew roster with schema: crew name, phone, email, magic token, and captain flag
  • SMS Routing: Filter captains first (captain flag = true), then sort by availability and recent-call history, and send SMS via Twilio

For example, to add a new captain (Abdul Danishwar) to the roster:

# Add to DynamoDB crew-dispatch table
# Attributes: name, phone, email, magic_token (encrypted), is_captain
# Abdul Danishwar, +1 818-730-5220, abduldan@aol.com, (token), true

The magic-link logic is baked into the deployed Lambda function (in the queenofsandiego repo), which routes incoming tokens to crew-response handlers.

Infrastructure Details

  • EC2 Instance: 34.239.233.28, running Python 3.8+, with SSH key jada-key.pem
  • Repos Directory: ~/repos/queenofsandiego and ~/repos/queenofsandiego-private (for secrets management)
  • Secrets Storage: ~/repos/.secrets/ with strict permissions (0700 on directory, 0600 on files); contains google_token.json, AWS credentials, and other OAuth state
  • DynamoDB: Two tables—crew-dispatch and charter-chats—in both primary and backup regions
  • Google Drive: JADA Business folder contains crew-availability spreadsheet, prior revenue reports, and charter scripts
  • CloudFront: Distributes shipcaptaincrew.com frontend (magic-link landing pages) from S3 origin
  • Gmail: JADA operations inbox serves as the source of truth for crew email threads and guest confirmations

Key Decisions and Rationale

Why DynamoDB for the roster? The crew roster needs to support real-time lookups during SMS dispatch (sub-100ms latency) and atomic updates when crew accept/decline calls. DynamoDB's per-item l