Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Charter Revenue Reporting and Crew Dispatch with Google Sheets API + DynamoDB

Over the past development session, we built and deployed a multi-system integration to automate JADA's monthly charter revenue reporting and crew availability management. This post covers the architecture, token refresh challenges we solved, and the infrastructure patterns that make this work at scale.

What We Built

The system accomplishes three core workflows:

  • Monthly Revenue Report Generation: Extract charter records from DynamoDB, match them against Gmail booking confirmations and trip sheets, format into an Excel workbook, and email the statement to stakeholders.
  • Google Workspace API Token Management: Implement reliable OAuth2 token refresh on a shared EC2 instance serving multiple JADA services.
  • Crew Dispatch with Magic Links: Decode time-bound crew-page tokens from DynamoDB, build shareable captain-call URLs, and cascade notifications through the roster hierarchy.

Technical Architecture

Data Flow: DynamoDB → Google Sheets → Email

The revenue report pipeline reads from two DynamoDB tables (both us-east-1 and us-west-2 replicas):

  • crew-dispatch table: Stores charter metadata (date, guests, revenue totals, captain assignments).
  • charter-chats table: Event log of booking confirmations and crew communications.

Scripts extract charter records for a given month (e.g., May 2024), cross-reference them against Google Gmail search results for booking confirmations from specific crew members (Jennifer Sanderson, Quinn, Jonathan), and pull trip-sheet dollar figures from Google Drive (stored in shared JADA Google Workspace folders).

The workbook is built using openpyxl in Python, formatted to match the prior month's template (preserved in JADA's Google Drive under /Business/Reports), and uploaded via the Google Sheets API to a master workbook living in the shared drive.

Google OAuth2 Token Refresh: The Core Challenge

The trickiest part of this integration was getting Google API token refresh working reliably on a headless EC2 instance.

The original reauth_google.py script (located in the EC2 instance at ~/repos/.secrets/) relied on a local browser redirect flow, which doesn't work on a server without a display. We solved this by:

  1. SSH port-forwarding a local auth tunnel: Use ssh -L 8484:localhost:8484 ubuntu@34.239.233.28 to forward the OAuth callback port from the remote host to your local machine.
  2. Patching the script to use the forwarded URL: Modify reauth_google.py to point its redirect URI to http://localhost:8484/ instead of trying to bind to the EC2 instance's public IP.
  3. Running the auth flow locally while connected via tunnel: The script opens your browser, you complete the OAuth consent screen, and the token is refreshed on the remote host and persisted.

Once the token is refreshed, it's written back to the credentials JSON file (in ~/.secrets/), and all subsequent API calls—Gmail, Sheets, Drive—use that cached credential until it expires.

Infrastructure & Deployment

The EC2 instance at 34.239.233.28 serves as the unified runner for all JADA ops tasks:

  • SSH Identity: Authenticated via jada-key.pem (managed in your local ~/.ssh/config).
  • Secrets Storage: ~/.secrets/ directory on the instance holds OAuth credentials and (separately) API keys, with permissions locked to 700 (owner-read/write/execute only).
  • Deployed Code: Python scripts live in ~/repos/, including patched versions of reauth_google.py, build_may_tab.py (the workbook generator), and send_statement.py (the email dispatcher).
  • S3 & CloudFront: The shipcaptaincrew site is served via CloudFront distribution pointing to an S3 bucket origin; we inspected bucket configuration to ensure the crew-dispatch roster and availability sheets are accessible for the cascade logic.

Key Scripts & Functions

build_may_tab.py – Revenue workbook generator:


# Pseudocode structure
def build_report_tab(month, year):
    charters = fetch_charters_from_dynamodb(month, year)
    for charter in charters:
        booking = search_gmail_for_confirmation(charter.booking_id)
        trip_sheet = download_trip_sheet_from_drive(charter.sheet_id)
        revenue = extract_amount_from_sheet(trip_sheet)
        append_row(worksheet, charter.date, charter.guests, revenue)
    
    workbook.save(f"/tmp/{month}_{year}.xlsx")
    upload_to_sheets_api(workbook, master_drive_id)

reauth_google.py (Patched) – OAuth token refresh:


# Key change: port-forwarded redirect URI
REDIRECT_URI = "http://localhost:8484/"  # Instead of public EC2 IP
flow = InstalledAppFlow.from_client_secrets_file(
    '~/.secrets/credentials.json',
    scopes=['gmail.readonly', 'drive.readonly', 'sheets']
)
creds = flow.run_local_server(port=8484, open_browser=True)
save_credentials(creds, '~/.secrets/token.json')

send_statement.py – Email distribution:


# Builds and sends the monthly statement
def send_statement(recipients, attachment_path):
    service = build_gmail_service(cached_token)
    message = create_mime_message(
        to=recipients,
        cc=['carole@example.com'],
        subject=f"JADA Monthly Statement – {month_name}",
        body=statement_template,
        attachments=[attachment_path]
    )
    service.users().messages().send(userId='me', body=message).execute()

Crew Dispatch & Magic Links

For the June 27 captain call-to-crew feature, we:

  • Decoded magic tokens from the crew-dispatch DynamoDB table using the deployed app's lambda token-generation logic (found in the unzipped application code).
  • Built captain-specific URLs using the magic-link format identified in the lambda routing logic (path pattern: /crew/join/{short_code}).
  • Implemented cascade logic: Read the crew roster schema to identify all captains, send the initial call to the primary captain, and auto-forward to backups if needed.

New roster additions (e.g., Abdul Danishwar, +1 818-730-5220) are written directly to the crew-dispatch DynamoDB table, then