Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a CloudFront-Backed Receipt Management Page and Automating Port Sheet Data Entry

This session involved two parallel infrastructure tasks: deploying a receipt management interface for a trailer rental business and automating Google Sheets-based port logging for a sailing charter operation. Both required careful orchestration of static site hosting, CDN configuration, and API authentication.

The Receipt Management Deployment Challenge

The quickdumpnow.com domain needed a functional `/books` endpoint to serve as a receipt upload interface for trailer rental transactions. The local development work was complete in `/Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html`, but the production deployment revealed a critical infrastructure issue.

When accessing `https://quickdumpnow.com/books`, CloudFront was returning the homepage instead of the books page. This is a common pattern when S3 objects don't exist at the expected path: CloudFront's custom error responses are configured to redirect 404s back to the homepage for SEO and UX purposes. The solution required three coordinated steps:

S3 Deployment Strategy

The books page needed to be available at multiple S3 keys to support both explicit and pretty URL patterns:

  • s3://[quickdumpnow-bucket]/books/index.html — the canonical location
  • s3://[quickdumpnow-bucket]/books — the pretty URL key (without extension)

This dual-key approach ensures CloudFront can serve the content whether the request arrives as `/books` (pretty URL) or `/books/` with the default root object resolution. The S3 upload process:

# Upload to both paths with appropriate content types
aws s3 cp books/index.html s3://[quickdumpnow-bucket]/books/index.html \
  --content-type "text/html; charset=utf-8"

aws s3 cp books/index.html s3://[quickdumpnow-bucket]/books \
  --content-type "text/html; charset=utf-8"

Both objects carry identical content but different S3 keys, allowing CloudFront's cache layer to hit either path without fallback to the custom error response.

robots.txt Configuration

The `/robots.txt` file was updated to block search engine crawling of the books endpoint, preventing indexed receipt data from appearing in search results:

User-agent: *
Disallow: /books

This was deployed alongside the books page to ensure search engines respect the restriction immediately upon the next crawl.

CloudFront Invalidation and Cache Control

After uploading to S3, CloudFront's cache needed to be invalidated to ensure the new objects served immediately rather than waiting for TTL expiration. Two invalidation patterns were required:

# First invalidation for explicit paths
aws cloudfront create-invalidation --distribution-id [DISTRIBUTION_ID] \
  --paths "/books" "/books/index.html" "/robots.txt"

# Second invalidation for wildcard pattern
aws cloudfront create-invalidation --distribution-id [DISTRIBUTION_ID] \
  --paths "/books/*"

The dual invalidation approach accounts for CloudFront's path matching behavior: explicit paths and wildcard patterns may cache separately depending on origin configuration. Each invalidation typically completes within 30–60 seconds, making the endpoint live and cacheable immediately.

Port Sheet Automation: A More Complex Challenge

The parallel task involved automating data entry into a Google Sheets-based port log for charter accounting. A recent charter generated $1,845.72 in revenue for Joseph Zurek, and this single entry needed to flow into the "JADA Port Log 2026" spreadsheet via programmatic APIs.

This required solving several technical problems:

Google Sheets API Authentication

The existing `jada_port_sheet.py` tool used outdated authentication tokens. Google's OAuth 2.0 refresh token had expired or become invalid. Rather than manually re-authorizing, a new script was created: `reauth_jada_calendar.py`.

The challenge: the original authentication was performed in-browser through `clasp` (the Apps Script CLI), but the credential files were stored with mismatched or incomplete scopes. The solution involved:

  • Inspecting both the Drive API credentials (`client_id`, `client_secret`) and calendar credentials
  • Verifying OAuth client configuration in the Google Cloud Console
  • Comparing token scopes between working and broken authentication flows
  • Building a standalone Python OAuth flow that doesn't require browser interaction

The `reauth_jada_calendar.py` script uses Python's built-in `http.server` module to stand up a temporary OAuth callback listener on port 8765:

#!/usr/bin/env python3
import http.server
import webbrowser
from google.auth.transport.requests import Request
from google.oauth2.service_account import Credentials

# Spin up callback server on port 8765
# User authorizes in browser
# Token written to credential file
# Script continues to use refreshed token for subsequent API calls

A process was holding port 8765, blocking the OAuth callback. The listening port was freed before re-authentication could proceed.

Sheet Structure Analysis and Data Mapping

The JADA Port Log 2026 spreadsheet uses a complex multi-tab structure with month-specific tabs and a Template tab. Rather than guessing the schema, the existing file was inspected:

# Read sheet structure via Google Sheets API
# Inspect "March" tab: row 1 contains headers
# Read first N rows to understand data format
# Identify column positions for: Date, Captain, Vessel, Revenue

The March tab contained existing entries; an April tab did not exist. The solution required:

  1. Creating a new "April 2026" tab with matching column structure
  2. Populating the month label cell
  3. Appending the Zurek charter entry ($1,845.72) in the correct row/column positions

Excel Format Complications

The port sheet was originally stored as `.xls` (Excel 97-2003 format) rather than Google Sheets. Direct Sheets API operations were insufficient. The file was downloaded and inspected using `xlrd` and `openpyxl` Python libraries:

# Read .xls structure with xlrd
# Inspect all tabs, row heights, column widths, formatting
# Convert to .xlsx (modern Excel format)
# Rebuild April tab preserving all formatting
# Upload updated .xlsx back to Drive via Google Drive API

This approach preserved the original file's formatting and structure while adding the new month tab and charter entry.

Key Architectural Decisions

  • Dual S3 key strategy: Rather than relying on CloudFront's default root object resolution, explicit keys ensure cache hits under all URL patterns.
  • Robots.txt blocking: Financial data (receipts, port logs) should never be indexed, so explicit `Disallow` rules prevent crawlers from accessing sensitive endpoints.
  • OAuth callback port isolation: Using a non-standard port (8765) for the OAuth callback server reduces conflicts with common development ports (8000, 8080, 3000).
  • .xls to .xlsx conversion: Rather than fighting legacy Excel formats, the file was modernized to support direct Google Sheets API operations in the future.

What's Next

Once the reauth script completes successfully, the