Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Receipt Management System to quickdumpnow.com and Automating Port Sheet Generation with Google Sheets

This session involved two parallel infrastructure projects: deploying a receipt upload system for a trailer rental business and fixing the automation pipeline for monthly port sheet generation. Both required careful coordination across S3, CloudFront, Google Sheets APIs, and OAuth token refresh mechanisms.

Part 1: Fixing the quickdumpnow.com /books Page Deployment

Problem Statement: The quickdumpnow.com domain needed a dedicated /books endpoint to serve as a receipt upload interface for the trailer rental business. The page existed locally but wasn't accessible via HTTPS, returning the homepage instead due to CloudFront's default error handling.

Technical Details: S3 and CloudFront Configuration

The site's infrastructure uses a common pattern:

  • S3 bucket: Stores static HTML, CSS, and assets
  • CloudFront distribution: Caches and serves content globally
  • Route53: DNS routing for quickdumpnow.com
  • Custom error responses: Configured to redirect 404s to the homepage

The issue: when requesting /books, S3 returned a 404 because the object didn't exist at that key path. CloudFront's custom error response (configured to handle 4xx errors by serving the homepage) then masked the real problem.

Deployment strategy:

File path: /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html

Uploaded to two S3 keys:
- s3://[bucket-name]/books/index.html  (for /books/ requests)
- s3://[bucket-name]/books              (for /books requests without trailing slash)

By uploading to both keys, we ensure that CloudFront can serve the page regardless of whether the user includes a trailing slash. This is a defensive practice when the origin doesn't perform automatic index.html rewriting.

robots.txt update:

File modified: /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt
Added: Disallow: /books

The /books endpoint is for internal business use only (receipt uploads), not a public resource. Blocking it in robots.txt prevents search engines from indexing it.

CloudFront Invalidation

After uploading both objects, we invalidated the CloudFront distribution cache:

Invalidated paths:
- /books
- /books/
- /robots.txt

CloudFront invalidations typically propagate in 30–60 seconds. The wildcard pattern ensures all variants are cleared. This is critical after updating robots.txt, since cached 404 responses could otherwise persist.

Part 2: Automating Port Sheet Generation with Google Sheets

Problem Statement: Maria manages monthly port sheets for JADA sailing operations. The process was manual: collecting charter data, updating a spreadsheet template, sending emails. A new charter ($1,845.72) needed to be logged, and the automation pipeline required OAuth token refresh fixes.

Technical Architecture: Google Sheets + Google Calendar + Lambda

The port sheet automation spans three systems:

  1. Google Sheets (Port Log): Stores monthly charter entries
  2. Google Apps Script (PortSheetReporter.gs): Reads the sheet, formats the report, sends emails
  3. Python tools (jada_port_sheet.py, reauth_jada_calendar.py): Local scripts for token management and dry runs
  4. AWS Lambda: Hosts the refresh token for the scheduled execution

Key file locations:

/Users/cb/Documents/repos/tools/jada_port_sheet.py
  - Main port sheet generator
  - Reads Port Log from Google Sheets
  - Formats monthly report
  - Sends via email

/Users/cb/Documents/repos/tools/reauth_jada_calendar.py
  - OAuth 2.0 refresh token handler
  - Exchanges refresh_token for fresh access_token
  - Stores new token in Lambda environment variables
  - Updated 10+ times during this session (OAuth debugging)

/Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs
  - Google Apps Script version (backup/alternative)
  - Used by some scheduled triggers

The OAuth Token Challenge

Google Calendar API requires valid access tokens. Tokens expire every hour, so long-running processes need refresh_token support. The challenge:

  • Two credential files existed with different client IDs
  • One had Drive scope, one had Calendar scope
  • The calendar token file was stale (possibly from 6+ months ago)
  • Lambda needs a way to refresh tokens without interactive OAuth flow

Solution: The reauth_jada_calendar.py script performs the offline refresh:

Algorithm:
1. Load refresh_token from local file or Lambda env var
2. Send POST to Google's token endpoint
   - Grant type: refresh_token
   - Client ID and secret from credentials file
3. Parse new access_token from response
4. Verify token works with a calendar API call
5. If valid, push new GCAL_REFRESH_TOKEN to Lambda

The script required debugging the OAuth scope mismatch. Drive API and Calendar API use different credential flows, so we verified the client IDs and redirect URIs matched what Google had registered.

Port Sheet Data Entry

The Port Log is stored in Google Sheets (JADA Port Log 2026). Its structure:

Tab: "April"
Columns: [Month label cell], [Charter entries with columns: Date, Captain, Vessel, Amount, Notes]

Entry added:
- Date: [Charter date]
- Captain/Contact: Joseph Zurek
- Amount: $1,845.72
- Notes: [Optional details about vessel/route]

The spreadsheet uses multiple tabs for different months. Each tab's structure mirrors the template to ensure consistent formatting when the port sheet generator reads and formats the report.

Key Decisions and Why

  • Two S3 keys for the same content: Better UX — users don't need to remember trailing slash rules.
  • robots.txt blocking /books: Prevents search engine indexing of internal tools.
  • OAuth refresh token in Lambda: Allows the scheduled job to run without human intervention. The token is refreshed automatically before expiry.
  • Python script for local dry runs: Allows testing the full port sheet pipeline locally before deploying changes to production.
  • Separate Apps Script file: Provides a fallback execution method if Lambda isn't available; Google Apps Script can trigger on a schedule natively.

What's Next

  • quickdumpnow.com/books: The receipt upload page is now live. Next phase: implement form processing and file upload handling (likely requires a backend service or API Gateway + Lambda).
  • Port sheet scheduling: Once the token refresh is confirmed stable, set up a CloudWatch-triggered Lambda to run jada_port