Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Receipt Management Portal and Automating Port Sheet Generation for Multi-Site Operations

This session involved two parallel infrastructure challenges: standing up a receipt management system for a trailer rental business and automating port sheet generation and distribution for a marine charter operation. Both required careful orchestration of static site deployment, API credential management, and scheduled task automation.

Problem Statement

The quickdumpnow.com domain needed a functional receipts portal at /books, but was serving only a homepage landing page. Simultaneously, the port sheet generation workflow for Queen of San Diego's charter operations required automation—specifically, a single charter entry ($1845.72 total revenue) needed to be recorded and distributed to stakeholders (Maria, Edwin, Caro) via email.

Part 1: Static Site Deployment with CloudFront Configuration

The 404 Redirect Problem

CloudFront was returning the homepage for all /books requests instead of serving the actual page. Investigation revealed the distribution was configured with a custom error response (404 → /) redirecting all missing objects to the root. This is a reasonable UX pattern for SPAs but breaks nested static content discovery.

Deployment Strategy

The solution required uploading the books page twice to S3 with different object keys to handle both URL variants:

  • s3://quickdumpnow-prod/books/index.html — for /books/ pretty URL routing
  • s3://quickdumpnow-prod/books — bare key for /books direct access

This dual-key approach ensures CloudFront can match both common URL patterns without requiring rewrite rules at the distribution level.

Robots.txt Blocking

The receipts portal should not be indexed (it's internal business tooling). Updated /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt to include:

User-agent: *
Disallow: /books/
Disallow: /books

This prevents search engines from crawling the portal while allowing direct access to authenticated users.

CloudFront Invalidation

Issued cache invalidation for:

  • /books/* — recursive path invalidation
  • /books — bare key
  • /robots.txt — updated crawler directives

CloudFront invalidations complete within 30–60 seconds, making the updated content live nearly immediately.

Part 2: Port Sheet Automation Architecture

The Workflow Challenge

The port sheet system requires:

  1. Data source: Google Sheets (JADA Port Log 2026) containing charter entries
  2. Script layer: Google Apps Script (PortSheetReporter.gs) for sheet manipulation
  3. Python orchestration: jada_port_sheet.py for complex formatting and email dispatch
  4. Cloud integration: Google Calendar API for authentication refresh + Gmail API for distribution
  5. Lambda deployment: Credential management for serverless execution

OAuth Token Refresh Challenge

The initial issue: jada_port_sheet.py was failing because the Google Calendar OAuth token had expired. The system needs a refresh token to obtain fresh access tokens without user re-authentication.

Solution developed in /Users/cb/Documents/repos/tools/reauth_jada_calendar.py:

  • Reads existing credentials from ~/.config/jada/gcal_creds.json
  • Extracts the refresh token
  • Uses the OAuth client ID/secret (sourced from the same credential file) to obtain a fresh access token
  • Writes updated token to Lambda environment variable GCAL_REFRESH_TOKEN
  • Validates calendar API connectivity before completing

The script handles the local development auth flow (OAuth callback on port 8765) and persists credentials for subsequent Lambda invocations.

Port Sheet Data Format

The system reads from multiple tabs in the JADA Port Log 2026 spreadsheet:

  • Month label tab: Contains "April 2026" or similar
  • Template tab: Defines the column structure and formatting
  • Data tabs: Month-specific entries (March, April, etc.)

Each charter entry follows a specific format extracted from the original Excel template:

Charter Entry Format:
- Date
- Boat Name / Captain
- Passengers
- Destination
- Start Time
- End Time
- Fuel Used
- Rate Type
- Total Revenue

For the Zurek charter entry, the system recorded:

Date: [charter date]
Boat: [vessel name]
Passengers: [count]
Total: $1,845.72

Email Distribution Pipeline

jada_port_sheet.py formats the port sheet data and dispatches via Gmail API to configured recipients:

  • Maria (port manager)
  • Edwin (operations)
  • Caro (finance tracking)

The email includes:

  • Month and date range
  • Formatted charter table (HTML or plaintext)
  • Summary totals (revenue, number of charters, utilization metrics)
  • Attachment: Excel export of the port log for external use

Infrastructure Decisions

Why Python for Port Sheet Orchestration?

Google Apps Script alone cannot reliably email large formatted datasets or manage complex credential rotation. Python provides:

  • Fine-grained Gmail API control (templating, attachments, multipart MIME)
  • Spreadsheet data transformation libraries (openpyxl for Excel, gspread for Sheets)
  • Credential management without browser UI (essential for Lambda execution)
  • Scheduled execution via AWS Lambda + EventBridge

OAuth Token Management Strategy

Rather than storing refresh tokens in git or hardcoding credentials, the system:

  1. Maintains a single source-of-truth credential file locally
  2. Extracts the refresh token programmatically
  3. Pushes it to Lambda as an environment variable (encrypted at rest via KMS)
  4. Python script uses the refresh token to obtain temporary access tokens on each invocation

This pattern eliminates the need to manage long-lived credentials or manually update tokens.

Port Detection & Cleanup

During OAuth flow, the local server binds to port 8765 for the callback. If a previous invocation crashed, the port could remain in TIME_WAIT state. Added process cleanup:

lsof -i :8765  # Identify stale processes
kill -9 [PID]  # Force-kill