Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Monthly Revenue Reports and Crew Dispatch: Google Workspace Integration on EC2

This session focused on building an automated pipeline to generate monthly revenue statements for the Sheraton charter business, refresh stale Google OAuth tokens on a production EC2 instance, and extend the crew-dispatch system with captain availability tracking. The work involved diagnosing token-refresh failures, building Excel workbooks programmatically, integrating Gmail and Google Sheets APIs, and establishing secure SSH workflows for remote operations.

The Problem

JADA operates a charter booking system across multiple Google Workspace integrations—Gmail for bookings, Google Sheets for financial tracking, Google Drive for document storage, and a DynamoDB-backed crew-dispatch platform. The production OAuth tokens on the EC2 host (ubuntu@34.239.233.28) had expired, blocking:

  • Generation of monthly revenue reports (May statement was overdue)
  • Crew availability queries and dispatch operations
  • Gmail-driven booking confirmations and captain notifications
  • Drive file synchronization and reporting uploads

Additionally, no standardized format existed for monthly statements, and the crew roster lacked structured availability data needed for the captain-cascade call system.

Token Refresh and OAuth Recovery

The root issue was in /home/ubuntu/repos/reauth_google.py on the EC2 instance. The script uses the Google auth library's refresh flow but was failing silently when tokens expired. The fix involved:

  1. Patching the reauth script: Modified reauth_google.py to explicitly handle token refresh errors, add verbose logging, and validate the unified token before use. The patched version writes the refreshed token to ~/.google_unified_token with explicit permission checks (0600).
  2. Securing the secrets directory: Locked down ~/.jada-ops/.secrets/ to mode 0700 and individual credential files to 0600, preventing accidental exposure.
  3. Testing the refresh flow: After deployment, verified token refresh succeeded by calling gcloud auth application-default print-access-token and querying the Sheets API directly.

The key insight: Google's service-account flow on shared infrastructure requires strict file permissions and explicit validation of token presence before API calls. Any intermediate script (like the provisioner or lambda that reads charter records) must check for token freshness.

Monthly Statement Generation Pipeline

With tokens restored, the next step was building the May revenue statement. The process:

  1. Data extraction from DynamoDB: Queried the crew-dispatch DynamoDB table (us-east-1) to pull all charter records with amounts and guest names. The table schema uses sailId as partition key and stores booking metadata including revenue amounts, guest counts, and captain assignments.
  2. Revenue rule discovery: Inspected prior April and April reports (stored in the JADA Business folder on Drive) to identify the "reportable revenue" rule: only charters with a non-empty amount field and a completed state count toward monthly revenue.
  3. Excel generation with openpyxl: Created a standalone Python script (/tmp/build_may_tab.py) that:
    • Reads a template sheet from the master workbook on Drive
    • Populates it with May charter records (filtered by sail date and revenue rules)
    • Applies currency formatting to amount columns
    • Writes totals and footnotes matching the prior month's format
    • Outputs an .xlsx file ready for email distribution
  4. Upload to Drive: After validating the generated sheet, uploaded the new workbook to the shared JADA Drive via the Sheets API, replacing the prior version at a known file ID.

Why openpyxl? The Google Sheets API is excellent for collaborative editing but clumsy for one-off report generation (it requires building cell-by-cell updates). openpyxl lets us generate a complete, formatted workbook locally and upload it once—faster and fewer API calls.

Email Distribution and Statement Sending

The statement was delivered via a custom Gmail script (/tmp/send_statement.py) that:

  • Searched Gmail for prior statement emails to extract the known recipient list (Sergio, captains, etc.)
  • Attached the generated .xlsx file
  • Sent via gmail.send() scope after requesting gmail.compose permissions
  • CC'd Carole and confirmed sender was the JADA operations account

This pattern—search for prior examples to infer recipient lists—avoids hardcoding email addresses while maintaining clarity about who receives sensitive financial reports.

Crew Roster Extension: Magic Tokens and Availability Tracking

The crew-dispatch system uses magic tokens (randomly generated secrets) to create password-less login URLs for crew members to update their availability. The process:

  1. Token decoding: Crew members are stored in the DynamoDB crew-roster table with fields like name, phone, email, and a base64-encoded magic_token. Decoded these tokens to build personalized captain-call links (e.g., https://shipcaptaincrew.com/crew/{shortCode}?token={token}).
  2. Lambda magic-link routing: Inspected the deployed lambda function (unzipped from /opt/code) to understand how the app routes magic-link requests. The lambda extracts the short code and token, validates against the roster, and serves the availability form.
  3. Adding new roster members: Added Abdul Danishwar (phone +1 818-730-5220, email abduldan@aol.com) to the crew-roster DynamoDB table with a generated magic token. This allows him to receive captain-call notifications immediately upon roster registration.

Infrastructure: SSH, S3, CloudFront, and DynamoDB

The architecture spans several AWS services:

  • EC2 host (ubuntu@34.239.233.28): Runs the reauth and statement scripts; stores ~/.jada-ops/.secrets/ with Google service-account credentials.
  • DynamoDB tables (us-east-1): crew-dispatch (charters), crew-roster (captain info), and charter-chats (booking messages). Schema uses partition keys like sailId and range keys for temporal queries.
  • Google Drive / Sheets: Master workbook file ID known; template sheets are read via spreadsheets().values().get() and updated via batch operations.
  • S3 / CloudFront (shipcaptaincrew.com): The crew-dispatch frontend is distributed via CloudFront with an S3 origin. Lambda handles API requests and magic-link routing.

Key decision: Remote execution on EC2. Rather than duplicating credentials or building a CI/CD pipeline, we kept the token, Gmail, and Sheets logic on the production EC2 box.