Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Receipt Management System for quickdumpnow.com and Automating Port Sheet Generation

This session involved two parallel infrastructure projects: standing up a receipt upload system for a trailer rental business and completing the automation of port sheet generation for vessel charter operations. Both required careful coordination of static site hosting, API integration, and OAuth token management.

The quickdumpnow.com Books Page Deployment

The quickdumpnow.com domain hosts a small business site for trailer rentals. The requirement was to create a dedicated landing page at https://quickdumpnow.com/books where customers could upload and track rental receipts.

The Problem: A books/index.html file existed locally in /Users/cb/Documents/repos/sites/quickdumpnow.com/books/, but had never been deployed to production. When accessing the URL, CloudFront was returning the homepage instead of the books page—a common issue with S3 static hosting when objects don't exist.

Root Cause Analysis: The CloudFront distribution for quickdumpnow.com had a custom error response configured to redirect all 404 errors back to the homepage. This is a user experience pattern for SPAs, but it masked missing static assets. The S3 bucket didn't contain the books page object yet.

Deployment Strategy: We deployed the books page using two S3 keys to support both pretty URLs and direct access:

  • books/index.html — for requests to /books/
  • books — for requests to /books (without trailing slash)

We also updated robots.txt to prevent search engine indexing of this internal tool:

Disallow: /books

Deployment Commands:

# Upload the books page to both S3 keys
aws s3 cp books/index.html s3://quickdumpnow.com/books/index.html --content-type text/html
aws s3 cp books/index.html s3://quickdumpnow.com/books --content-type text/html

# Upload updated robots.txt
aws s3 cp robots.txt s3://quickdumpnow.com/robots.txt --content-type text/plain

# Invalidate CloudFront to clear cache
aws cloudfront create-invalidation --distribution-id [DISTRIBUTION_ID] --paths "/books" "/books/" "/robots.txt"

The CloudFront invalidation took 30–60 seconds to propagate. The page is now live at https://quickdumpnow.com/books.

Port Sheet Automation: OAuth Token Refresh and Email Integration

The second project involved completing an automated monthly port sheet generation system for JADA (a vessel charter operation). The system needed to:

  • Read charter entries from a Google Sheet (JADA Port Log 2026)
  • Authenticate with Google Calendar API to pull event data
  • Format and email the port sheet to stakeholders (Edwin, Maria, Caro)
  • Handle OAuth token refresh when credentials expired

The OAuth Token Problem: The main port sheet script (jada_port_sheet.py) kept failing because the Google Calendar API token had expired. The refresh token needed to be rotated and stored in AWS Lambda environment variables, which feed the Python runtime during execution.

Solution: Authentication Re-architecture

We created a new authentication utility script at /Users/cb/Documents/repos/tools/reauth_jada_calendar.py that:

  1. Initiates OAuth 2.0 authorization flow by opening a browser to Google's consent screen
  2. Listens on localhost:8765 for the authorization callback
  3. Exchanges the authorization code for a new access token and refresh token
  4. Saves the refresh token to disk for long-term storage

Port Handling: The script needed to acquire port 8765 for the OAuth callback listener. We had to identify and kill a stale Python process that was holding the port:

# Check what's using port 8765
lsof -i :8765

# Kill the stale process
kill -9 [PID]

# Verify port is free
lsof -i :8765  # Should return empty

Token Credential Structure: Google OAuth credentials live in two separate files:

  • GCAL_CLIENT_SECRETS.json — Contains client_id, client_secret, and redirect_uris for the OAuth app
  • GCAL_REFRESH_TOKEN.txt — Contains only the refresh token string (stored securely in Lambda env vars)

The client_id must match between both files. During debugging, we had to verify they were consistent and check that the redirect URI exactly matched http://localhost:8765.

Lambda Integration: After generating the new refresh token locally, we pushed it to AWS Lambda:

aws lambda update-function-configuration \
  --function-name jada-port-sheet \
  --environment Variables={GCAL_REFRESH_TOKEN=eyJhbGciOiJSUzI1NiIsImtpZCI6...}

Port Sheet Data Entry and Formatting

The existing port sheet was stored as an Excel file (`.xls`) in Google Drive. To add new charter entries programmatically, we needed to:

  1. Download the file and inspect its structure using xlrd and openpyxl
  2. Understand the template layout (row heights, column widths, merged cells)
  3. Create a new "April 2026" tab while preserving the "March 2026" tab with existing data
  4. Add the charter entry: Joseph Zurek, $1,845.72
  5. Reupload the file back to the same Google Drive file ID

The port sheet uses a fixed-width design with custom row heights for readability. We preserved these formatting rules when programmatically adding the new tab:

# Install Excel handling libraries
pip install openpyxl xlrd --break-system-packages

# Python snippet to add a new month tab with proper formatting
from openpyxl import load_workbook

wb = load_workbook('Port_Sheet_Template.xlsx')
new_sheet = wb.create_sheet('April 2026')
new_sheet['A1'] = 'April 2026'
new_sheet['A3'] = 'Zurek'
new_sheet['B3'] = 1845.72
# ... preserve formatting rules ...
wb.save('Port_Sheet_Template.xlsx')

We then re-uploaded the modified file to Google Drive using the Drive API with the same file ID, ensuring continuity.

Email Integration and Stakeholder Communication

The final step was sending the completed April 2026 port sheet to the three stakeholders. The email function in jada_port_sheet.py uses Gmail API to compose and send messages with the port sheet attachment.

Recipients: edwin@example.com, maria@example.com, caro@example.com

The email includes:

  • Month and year in subject line
  • Confirmation of new charter entries added
  • Port