Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Deploying a Receipt Upload System and Automating Port Sheet Generation for Charter Operations

This session involved two parallel efforts: standing up a receipt management system for a trailer rental business and refactoring the automated port sheet workflow for charter operations. Below is a technical breakdown of the infrastructure changes, deployment strategy, and automation improvements made.

Part 1: Receipt Upload Infrastructure for quickdumpnow.com/books

The quickdumpnow.com domain needed a dedicated receipt upload endpoint at /books to track trailer rental expenses. Previously, this path returned the homepage due to CloudFront's default error handling.

File Structure and Deployment

The local development structure was organized as follows:

/Users/cb/Documents/repos/sites/quickdumpnow.com/
├── books/
│   └── index.html
└── robots.txt

The books/index.html file was created using a consistent design model matching the existing quickdumpnow site aesthetic. The key decision was to deploy this file to S3 under two keys:

  • books/index.html — the traditional object path
  • books — a bare key without extension to support pretty URLs

This dual-key approach ensures that both https://quickdumpnow.com/books/ and https://quickdumpnow.com/books resolve correctly, avoiding redirect chains and improving user experience.

CloudFront and S3 Configuration

The quickdumpnow CloudFront distribution was configured with a custom error response that redirects all 404s to the homepage. This is a common pattern for single-page applications but problematic for new static pages. The distribution needed an invalidation to clear cached 404 responses:

# Invalidation paths issued
/books
/books/
/robots.txt

The invalidation removed stale 404 cache entries, allowing CloudFront to fetch the newly published objects from the S3 origin. Propagation typically completes within 30–60 seconds.

robots.txt Update

The robots.txt file was updated to block the /books path from search engine indexing, since this is an internal receipt system, not public-facing content:

Disallow: /books

This prevents unnecessary crawl budget consumption and keeps the receipt upload system out of search results.

Part 2: Port Sheet Automation Refactor

The port sheet workflow needed significant refactoring to handle automated monthly reporting for charter operations. The system had to integrate Google Sheets (JADA Port Log), Google Calendar (charter event retrieval), and email delivery.

Files Modified

  • /Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs — Google Apps Script (GAS) for sheet manipulation and email dispatch
  • /Users/cb/Documents/repos/tools/jada_port_sheet.py — Python script for calendar querying and port sheet generation logic
  • /Users/cb/Documents/repos/tools/reauth_jada_calendar.py — New OAuth 2.0 reauthentication utility for Google Calendar API

Calendar Token Management

A critical issue emerged: the Google Calendar API token was stale or missing proper scopes. The solution was to create a dedicated reauthentication script that:

  • Implements OAuth 2.0 Authorization Code flow locally on port 8765
  • Requests calendar and drive scopes with proper refresh token persistence
  • Validates token refresh and handles edge cases (e.g., scope mismatches)

The script handles the local callback redirect pattern:

http://localhost:8765/auth/callback

When port 8765 was held by a stale process, a process kill cleared it, allowing fresh token negotiation. The new refresh token was then injected into the Lambda environment variable GCAL_REFRESH_TOKEN for automated runs.

Port Sheet Data Model

The JADA Port Log spreadsheet (Google Sheets) uses a structured format with monthly tabs. For example, the April 2026 tab contains:

Columns: Date | Boat | Captain | Pax | Revenue | Notes
Row data: 2026-04-15 | Charter Vessel | Joseph Zurek | 8 | $1845.72 | ...

The original Excel file (JADA Port Sheet 2025.xls) had a complex format with row heights and column widths. To preserve this structure while automating updates, the Python workflow:

  1. Downloads the current .xls file using Google Drive API
  2. Converts it to .xlsx using openpyxl (installed with --break-system-packages for compatibility)
  3. Reads the Template sheet to understand formatting (cell merges, fonts, row heights)
  4. Creates new monthly tabs, populating them with charter data from Google Calendar
  5. Uploads the updated .xlsx back to the same Drive file ID

New Entry: April 2026 Charter

A charter booking for Joseph Zurek on April 15, 2026, generated revenue of $1,845.72. This entry was:

  1. Appended to the JADA Port Log 2026 Google Sheet (April tab)
  2. Also prepared for the Excel-based Port Sheet file to maintain two sources of truth
  3. Included in the email report sent to Edwin, Maria, and Caro

AWS Lambda Integration

The port sheet system is automated via AWS Lambda, triggered monthly. The Lambda function:

  • Retrieves credentials from environment variables (GCAL_REFRESH_TOKEN, Google Drive API credentials)
  • Calls the Python port sheet script, which queries Google Calendar for charter events
  • Generates the port sheet and triggers email dispatch via the GAS PortSheetReporter
  • Logs success/failure to CloudWatch Logs

The refresh token was updated post-reauthentication and deployed using the AWS CLI (no credentials shown here for security).

Key Decisions

  • Dual S3 Keys for pretty URLs: Uploading to both books/index.html and books ensures seamless access patterns without 301 redirects.
  • CloudFront invalidation strategy: Invalidating specific paths (not wildcards) reduces API calls and propagates faster.
  • Google Sheets + Excel dual model: Google Sheets provides programmatic API access; Excel preserves formatting and historical handoff expectations. Both are kept in sync.
  • Local OAuth reauthentication: Rather than manual token generation, a self-contained Python script handles the flow, improving maintainability.
  • Lambda environment variables: Storing refresh tokens (not access tokens) in Lambda allows automatic token rotation without re-deployment.

What's Next

  • Receipt upload endpoint: The books page is now live. Next step is to add a form handler and S