Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Receipt Upload System for quickdumpnow.com and Automating Monthly Port Sheet Distribution

This session involved two parallel infrastructure projects: deploying a receipts management system for a trailer rental business site, and debugging/deploying an automated monthly port sheet generator that sends charter data to stakeholders. Both required careful coordination of static site hosting, CloudFront caching behavior, Google Sheets APIs, and OAuth token refresh workflows.

Project 1: quickdumpnow.com Books/Receipts Page Deployment

Problem: The quickdumpnow.com domain needed a dedicated receipts upload page, but requests to https://quickdumpnow.com/books were returning the homepage instead of a dedicated page.

Root Cause Analysis: The CloudFront distribution was configured with a custom error response that redirects all 404s to the homepage. This is intentional for user experience on the main site, but it was masking the fact that the /books object didn't actually exist in the S3 origin bucket.

What We Did:

  • Located the source file at /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html
  • Updated the HTML template with the correct model ID and styling matching the existing site design
  • Deployed to S3 using two keys for pretty URL support:
    • s3://quickdumpnow-main/books/index.html (canonical object)
    • s3://quickdumpnow-main/books/ (directory-style key for CloudFront to serve)
  • Updated /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt to block indexing of the books path (since it will contain sensitive receipt/transaction data)
  • Invalidated CloudFront distribution caches with paths /books and /robots.txt

Key Decision — S3 Key Strategy: CloudFront origin request behavior requires explicit handling of both /books/index.html and /books/ keys. Modern S3 deployments often use a Lambda@Edge function to normalize these requests, but for a simple static site, dual-key deployment is more maintainable and avoids cold-start latency.

Result: https://quickdumpnow.com/books now serves the receipts page. CloudFront cache invalidation takes ~30–60 seconds to propagate globally.

Project 2: Automated Port Sheet Generation and Distribution

Problem: JADA (a charter boat operation) needed automated monthly port sheets—documents summarizing charter bookings and earnings—sent to stakeholders (Maria, Edwin, Caro). Manual creation was error-prone; the April 2026 port sheet with a $1,845.72 charter entry needed to be generated and distributed.

Architecture Overview:

The port sheet system spans three components:

  • Google Drive: JADA Port Log 2026 spreadsheet (Excel format) containing monthly tabs
  • Python automation: /Users/cb/Documents/repos/tools/jada_port_sheet.py reads from Drive, transforms data, and emails results
  • Google Calendar OAuth: New reauth_jada_calendar.py script handles token refresh for headless environments

Technical Workflow:

Step 1: Inspecting the Port Log Structure

The JADA Port Log 2026 is an Excel file with multiple sheets. We needed to understand the existing format (March data) to replicate it for April:


# Read the port log structure with xlrd (Excel-compatible)
import xlrd
workbook = xlrd.open_workbook(filename="JADA_Port_Log_2026.xlsx")
for sheet in workbook.sheets():
    print(f"Sheet: {sheet.name}")
    for row_idx in range(min(5, sheet.nrows)):
        print(sheet.row_values(row_idx))

The March sheet contained columns for: Date, Charter Name, Pax (passenger count), Location, Revenue, and Notes. Row heights and column widths were also preserved to match the original formatting.

Step 2: Building the April 2026 Tab

Using openpyxl, we created a new April sheet, preserving the original Excel formatting while adding the charter entry:


from openpyxl import load_workbook

wb = load_workbook("JADA_Port_Log_2026.xlsx")
april_sheet = wb.create_sheet("April")

# Set month label and formatting
april_sheet["A1"] = "April 2026"
april_sheet["A2"] = "Date"
april_sheet["B2"] = "Charter Name"
# ... additional headers

# Add Zurek charter entry
april_sheet["A3"] = "2026-04-15"
april_sheet["B3"] = "Joseph Zurek"
april_sheet["C3"] = 8  # pax count
april_sheet["D3"] = "$1,845.72"

# Preserve row heights and column widths from March
for col_idx, col_width in march_widths.items():
    april_sheet.column_dimensions[chr(65 + col_idx)].width = col_width

wb.save("JADA_Port_Log_2026.xlsx")

We uploaded the updated file back to Google Drive using the same file ID, preserving version history.

Step 3: OAuth Token Refresh for Headless Environments

Challenge: The jada_port_sheet.py script needed to send emails via Google Calendar API, but the existing OAuth token was stale. In a headless environment (Lambda, cron, or CI/CD), we can't open a browser for interactive OAuth flow.

Solution: Created reauth_jada_calendar.py, a one-time token refresh script that:

  • Reads the existing GCAL_CREDENTIALS JSON file (contains client ID/secret)
  • Checks if a cached refresh token exists; if valid, uses it directly
  • If needed, starts a local Flask server on 127.0.0.1:8765 to handle the OAuth callback
  • Generates a new refresh token and stores it in environment or secrets management

# Simplified flow from reauth_jada_calendar.py
import webbrowser
from flask import Flask, request

app = Flask(__name__)

@app.route("/oauth_callback")
def oauth_callback():
    auth_code = request.args.get("code")
    # Exchange auth_code for tokens
    response = requests.post("https://oauth2.googleapis.com/token", json={
        "client_id": CLIENT_ID,
        "client_secret": CLIENT_SECRET,
        "code": auth_code,
        "redirect_uri": "http://127.0.0.1:8765/oauth_callback",
        "grant_type": "authorization_code"
    })
    refresh_token = response.json()["refresh_token"]
    # Store in Lambda environment or Secrets Manager
    return "Token refreshed. You can close this window."

if