Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

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

What Was Done

This session involved two parallel infrastructure initiatives: deploying a receipt management interface for the quickdumpnow.com trailer rental business, and completing the automation pipeline for monthly port sheet generation in Google Sheets. The quickdumpnow work focused on CDN optimization and SEO configuration, while the port sheet work resolved OAuth token refresh issues and implemented end-to-end email distribution.

Quickdumpnow.com Books/Receipts Deployment

Problem Statement: The /books endpoint was returning the homepage instead of a dedicated receipt management page, despite local files existing in the repository.

Root Cause: CloudFront was configured with a custom error response that redirects all 404s back to the homepage. While this is excellent for user experience on missing pages, it masked the actual issue: the S3 objects hadn't been deployed yet, and the CloudFront cache was serving stale or missing content.

Files Modified:

  • /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html — Updated the model ID to match the production Jotform embed
  • /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt — Added disallow rule for /books to prevent indexing of the internal receipt form

Deployment Process:

# Upload the books page to S3 under two keys for pretty URL support
aws s3 cp books/index.html s3://quickdumpnow-com-site/books/index.html
aws s3 cp books/index.html s3://quickdumpnow-com-site/books

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

Why Two S3 Keys? CloudFront serves /books (directory) and /books/ (with trailing slash) differently depending on how users access the URL. By uploading to both books/index.html (which CloudFront translates to /books/) and a bare books object, we ensure both URL formats serve the correct content. This is a common pattern when migrating from traditional web servers to S3 + CloudFront where directory index handling works differently.

SEO Consideration: The robots.txt disallow prevents search engines from crawling an internal business form. This reduces noise in search analytics and ensures the form isn't indexed with varying parameters or form states.

CloudFront Invalidation Timing: The invalidation was issued immediately after S3 upload. CloudFront typically propagates invalidations within 30–60 seconds across edge locations. During this window, some users may still receive cached 404 responses, so we monitored the distribution status to confirm completion.

Port Sheet Automation: OAuth Token Refresh

Problem Statement: The automated port sheet generation script had stale Google Calendar API credentials, preventing it from querying charter dates and sending email notifications to Maria, Edwin, and Caro.

Root Cause Analysis: Google OAuth 2.0 refresh tokens have variable lifetimes. The existing token was likely issued months ago and Google's servers had invalidated it. The jada_port_sheet.py script couldn't authenticate to the Google Calendar API, breaking the entire pipeline.

Files Modified:

  • /Users/cb/Documents/repos/tools/jada_port_sheet.py — Multiple edits to refactor token handling and add dry-run email preview
  • /Users/cb/Documents/repos/tools/reauth_jada_calendar.py — New OAuth 2.0 re-authentication flow using the local web server pattern
  • /Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs — Updated Apps Script to reference the new token location in Lambda environment variables

OAuth Re-authentication Workflow:

# Start local OAuth server and open browser to Google consent screen
python /Users/cb/Documents/repos/tools/reauth_jada_calendar.py

# Script listens on localhost:8765 for the OAuth callback
# User grants calendar access at the consent screen
# Token written to local credentials file
# Token pushed to Lambda environment variable GCAL_REFRESH_TOKEN

Port Sheet Data Pipeline:

  • Google Sheets acts as the source of truth: JADA Port Log 2026 workbook with tabs per month (March, April, etc.)
  • Each month tab contains charter entries: date, vessel name, skipper, crew, total revenue
  • The April 2026 tab was created with the recent Zurek charter entry: $1,845.72 revenue
  • jada_port_sheet.py reads the month's sheet via Google Sheets API, formats data, and emails the complete port sheet to stakeholders

Infrastructure for Port Sheet Delivery: The script is designed to run as an AWS Lambda function, triggered monthly via EventBridge. The function needs:

  • Google Sheets API credentials (OAuth 2.0 refresh token)
  • Google Calendar API credentials (to cross-reference charter dates)
  • AWS SES (Simple Email Service) for sending port sheets to maria@jadasailing.org, edwin@jadasailing.org, and caro@jadasailing.org

Key Technical Decisions

1. Google Sheets as CMS: Rather than building a database, we use Google Sheets tabs to organize data by month. This gives Maria direct edit access without requiring backend code changes. Each month's tab follows a consistent schema, and the Python script parses headers dynamically to adapt to future schema changes.

2. OAuth 2.0 Refresh Token Strategy: The refresh token is stored in the Lambda environment variable GCAL_REFRESH_TOKEN rather than rotating short-lived access tokens. This simplifies the architecture: no token exchange on every script execution, and the token survives Lambda function restarts. The trade-off is that we must keep the refresh token secure (no hardcoding in source control).

3. Two S3 Keys for CloudFront Pretty URLs: As mentioned, uploading to both books/index.html and books ensures compatibility with different URL patterns. This is a workaround for CloudFront's lack of native directory index support (unlike traditional Apache/Nginx servers).

4. Email-First Port Sheet Delivery: The script generates a formatted email body and sends via SES rather than generating a PDF. This keeps the solution lightweight, avoids PDF generation dependencies, and ensures recipients can easily copy/paste data if needed. For archival, the email can be forwarded to a shared mailbox or printed to PDF by recipients.

Testing and Validation

Before sending the port sheet to stakeholders, the script was executed with a dry-run flag to preview the email format and verify the charter data (April 2026, Zurek entry, $1,845.72) was correctly parsed from the Google Sheet. The test confirmed: