Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Distributed Crew Dispatch System: OAuth Token Refresh, Google Sheets Automation, and DynamoDB Integration

Over the past development session, we tackled several interconnected infrastructure and automation challenges for the JADA operations platform: fixing OAuth token refresh failures in a remote EC2 environment, automating monthly financial reporting to Google Sheets, integrating DynamoDB crew roster management with email dispatch, and building magic-link authentication for crew self-service. Here's how we solved each piece and why the architecture matters.

The Problem: OAuth Token Refresh in Isolated EC2

The core issue was straightforward but instructive: a Python script at ~/repos/reauth_google.py on EC2 instance ubuntu@34.239.233.28 was failing to refresh Google API credentials. The script handles OAuth2 token lifecycle management for Gmail and Google Sheets API access, which multiple automation jobs depend on.

Root cause: The token refresh logic was attempting to write tokens to a path that didn't exist or lacked proper permissions. The fix required:

  • Patching the credential storage path in reauth_google.py to use a verified writeable location (the ~/repos/.secrets/ directory)
  • Setting strict file permissions on the secrets directory: chmod 700 ~/.secrets and chmod 600 ~/.secrets/*
  • Verifying the patched script with a syntax check before deployment: python3 -m py_compile reauth_patched.py
  • Deploying via SCP with an automatic backup: scp reauth_patched.py ubuntu@34.239.233.28:~/repos/reauth_google.py.bak

Once the token refresh succeeded, the downstream automation unblocked: Gmail searches, Sheets API writes, and DynamoDB queries all became possible.

Monthly Financial Reporting: From DynamoDB to Excel to Email

The Sheraton monthly report is a multi-stage pipeline that pulls charter revenue from DynamoDB, formats it in Excel, and emails it to stakeholders.

Data extraction: We queried the crew-dispatch DynamoDB table (provisioned across us-east-1 and us-west-2) to pull all charter records for a given month. The schema includes fields like charterID, date, totalAmount, guestNames, and notes. To match prior report formatting, we inspected the JADA business folder in Drive to find the April template, then extracted the revenue-recognition rule: only charters marked as "complete" or with explicit dollar figures in the totalAmount field count toward reportable revenue.

Excel generation: We wrote build_sheet.py and build_may_tab.py to generate a multi-sheet workbook using openpyxl. Each sheet represents a month and follows the template format: date, captain name, guest names, revenue amount, and footnotes. The script also sets number formatting for currency columns (e.g., _("$"* #,##0.00_)) to match the original.

Upload and distribution: The generated workbook is uploaded to Google Drive (via drive_find.py to locate the correct folder ID), then a standalone attachment is created and emailed to the stakeholder list. Gmail search was used to find prior statement recipients (send_statement.py searches for "monthly report" in sent mail to extract the CC list), ensuring consistency.

Crew Roster Management and Magic-Link Authentication

A separate piece of work involved crew self-service access: allowing crew members to check availability and book themselves onto trips via magic links (one-time URLs that authenticate without passwords).

Roster as source of truth: The crew roster lives in a Google Sheet in Drive. We mirrored this into DynamoDB's crew-roster table for fast lookups during dispatch. The script roster_sheet_add.py reads the sheet, normalizes names and contact info, and writes records with a schema like:

{
  "crewID": "abdul-danishwar",
  "name": "Abdul Danishwar",
  "phone": "+1 818-730-5220",
  "email": "abduldan@aol.com",
  "role": "captain",
  "status": "active"
}

Magic tokens: The deployed app (unzipped from a Lambda artifact in S3) uses a token-generation scheme to create short-lived, one-time URLs. We decoded the token format from the DynamoDB crew-dispatch table (where prior magic links are stored as metadata) and identified the hash structure. The Lambda function routes these links via an endpoint pattern like /crew/{crewID}/auth/{token}, validated against a TTL field.

Cascade and escalation: Once a captain is added to the roster, the send_captain_call.py script implements the crew-call cascade: it sends an SMS to the captain, then emails them a magic link to self-add crew. If they don't respond within a window, it escalates to a backup captain (stored in the roster's escalationContact field). The script reads the cascade rule from the Lambda provisioner logic (grepped from the deployed app source).

Infrastructure and Architecture Decisions

Why DynamoDB over a traditional database: The crew-dispatch and crew-roster tables are provisioned on-demand, so we pay only for what we query. This is ideal for a small operation with bursty traffic (e.g., captain calls go out in batches before trips). The tables are replicated across regions for redundancy; queries are routed based on the EC2 instance's region to minimize latency.

Why Google Sheets for the roster: Sheets serves as the human-editable UI; the crew can see who's on the roster, and operations can add/remove people. Rather than build a custom admin panel, we sync the sheet into DynamoDB on a schedule (or on-demand via roster_sheet_add.py), giving us the best of both worlds: a familiar interface plus fast API access.

Why magic links over passwords: Crew members don't want to remember passwords. Magic links are stateless (the token is the credential) and can be rate-limited and revoked without database changes. The link expires after one use or after a TTL, reducing the window for replay attacks.

SSH and secrets management: All EC2 operations use public-key authentication (jada-key.pem) stored in the local SSH config. Secrets (OAuth tokens, DynamoDB credentials) are stored in ~/.secrets/ on the box with mode 700, and the directory is in .gitignore to prevent accidental commits.

Key Commands and Patterns

For developers maintaining this system:

# Verify token refresh works
ssh ubuntu@34.239.233.28 "cd ~/repos && python3 reauth_google.py"

# Deploy a new version of a script
scp my_script.py ubuntu@34.239.233.28:~/repos/my_script.py

# Query crew roster from your local machine
aws dynamodb scan --table-name crew-roster --region us-east-1

# Trigger a roster sync
ssh ubuntu@34.239.233.28 "python3 ~/repos/roster_sheet_add.py --sync-now"

What's Next

The immediate next steps are: