Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Monthly Revenue Reports and Crew Dispatch: Integration of Google Sheets, Gmail, and DynamoDB

This session focused on building an automated pipeline for JADA's monthly revenue reporting and crew-dispatch coordination. The work involved integrating Google Workspace APIs (Gmail, Sheets, Drive), AWS DynamoDB, and EC2-hosted microservices to generate formatted financial statements and coordinate captain availability for same-day charter requests.

What Was Done

We built three interconnected systems:

  • A monthly revenue report generator that pulls charter records from DynamoDB, formats them in Excel, uploads to Google Drive, and emails statements to stakeholders
  • A Google API OAuth refresh mechanism that diagnoses and repairs token expiration issues on production EC2 instances
  • A crew-dispatch cascade caller that decodes magic-link tokens from DynamoDB, reads captain availability from shared spreadsheets, and triggers SMS/email notifications in priority order

Technical Details: Revenue Report Pipeline

The report generation workflow follows this sequence:

  1. Data extraction from DynamoDB: Query the crew-dispatch table (region: us-east-1) for charter records where reportable_revenue > 0. Each record contains guest names, dates, per-person pricing, and captain assignment. Records are filtered by month and validated against a prior month's report (stored in the JADA Business Google Drive) to ensure consistent revenue classification rules.
  2. Excel workbook generation: Using openpyxl, create a new workbook with tabs for each month. The Template tab provides formatting inheritance (currency formatting, header styles, column widths). Python scripts in /tmp/ handle the build logic:
    • build_sheet.py — instantiates the workbook and creates tabs
    • build_may_tab.py — clones the Template tab, populates charter rows, calculates totals
  3. Google Sheets upload: Use the Google Drive API to locate or create the master workbook in the JADA Internal folder, then upload the new .xlsx file as a revision. The sheet_inspect.py script validates that all tabs and number formats match expectations before uploading.
  4. Email delivery: Query Gmail for prior statement recipients (search pattern: "statement" from:jada.operations@gmail.com), generate a single-tab attachment in house format, and send via send_statement.py using the Gmail API with gmail.send scope.

OAuth Token Management: The EC2 instance at 34.239.233.28 runs a long-lived reauth process (~/repos/reauth_google.py) that holds the user's refresh token locally. During this session, we patched the script to:

  • Correctly handle token expiration by catching google.auth.exceptions.RefreshError
  • Log diagnostic output to ~/reauth.log for troubleshooting
  • Validate the token before attempting Sheets API calls

Deployment involved SSH-ing to the instance, backing up the original script, deploying the patched version, and running a syntax check before activation.

Technical Details: Crew-Dispatch Cascade System

When a last-minute charter request arrives, the system must notify available captains in a specific order. This required:

  • Magic-link token decoding: The deployed Danika app (found in unzipped app bundle at ~/repos/shipcaptaincrew-app/) uses short codes to encode crew roster IDs and availability windows. The danika_links.py script decodes these by querying the crew-roster table in DynamoDB (us-west-2) and matching the encoded segment against each member's schema.
  • Availability matching: The crew roster and availability spreadsheet (located in the JADA Google Drive under Crew) has multiple tabs for different seasons or vessel types. Each tab lists captains and their available dates. The script reads this via Google Sheets API and cross-references against the cascade rule stored in the Lambda function (find_cascade_rule() in the provisioning logic).
  • Captain-first notification: The cascade rule prioritizes captains over crew. Abdul Danishwar was added to the roster by inserting a new item into the crew-roster table with fields: 
    ID: "abdul-danishwar"
Phone: "+1 818-730-5220"
Email: "abduldan@aol.com"
Role: "captain"
Active: true
    Once added, the call-to-crew system will attempt SMS first (via Twilio integration), then email, with CC to Carole (carole@jada.com) for oversight.

Infrastructure and Architecture Decisions

Why EC2 for the reauth service: The refresh token must be held by a long-lived process that can authenticate to Google on behalf of the operations account. Serverless (Lambda) isn't suitable because the token would need to be stored in Secrets Manager, adding latency on every invocation. EC2 with a persistent process avoids that round-trip.

Why DynamoDB over a SQL database: Charter records and crew availability change frequently and benefit from DynamoDB's eventual consistency model. Queries are indexed on date and captain ID, which maps naturally to DynamoDB's partition/sort key structure. The crew-dispatch table uses charter_id as partition key and date as sort key, enabling efficient range queries for monthly reports.

Why Google Sheets for the availability source: Crew coordinators already use Google Drive for document collaboration. A shared spreadsheet is more accessible than an API-only system and allows manual overrides without code deployment. The read_avail.py script treats the sheet as the source of truth and doesn't cache locally.

Why openpyxl over Google Sheets API for Excel generation: The revenue report needs specific cell formatting (currency symbols, borders, merged header cells) that the Sheets API can't express as cleanly. Generating a local .xlsx file with openpyxl, then uploading as a revision, decouples the formatting logic from the cloud API.

Key Implementation Patterns

  • Template inheritance: The master workbook in Google Drive has a Template tab with pre-configured styles. Each new month's tab is cloned from Template, ensuring consistent formatting without hardcoding.
  • SSH with key-based auth: All EC2 connectivity uses SSH with the jada-key.pem identity. The SSH config at ~/.ssh/config is pinned to this key to avoid accidental use of default or shared keys.
  • Diagnostic logging: Both the reauth process and crew-dispatch scripts log to local files (~/reauth.log, ~/dispatch.log) on EC2. These are tailed during development to diagnose token, API, or network issues.
  • Validation before mutation: Before uploading a new report or adding a crew member to DynamoDB, the scripts perform dry-run reads (e.g., sheet_inspect.py