Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Deploying a Receipt Management System for Trailer Rentals: CloudFront Pretty URLs, S3 Architecture, and OAuth Token Refresh Patterns

What Was Done

This session involved two parallel workstreams: deploying a new receipt management interface for the quickdumpnow.com trailer rental business, and building an automated port sheet ingestion system that consumes Google Calendar and Google Sheets APIs to track charter bookings.

For quickdumpnow.com, we deployed a new /books endpoint that will serve as the receipt upload and management interface. The deployment required solving a CloudFront static site serving problem: ensuring pretty URLs work correctly when serving from S3 origins.

For the JADA port sheet automation, we implemented token refresh logic for long-lived OAuth credentials, created a Python-based port sheet updater that parses Excel files and writes to Google Sheets, and successfully recorded the first charter entry ($1845.72) into the April 2026 port log.

Technical Details: Static Site Deployment and CloudFront Configuration

The Problem: 404 Redirects and Pretty URLs

The initial deployment of /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html to S3 wasn't being served correctly. When users navigated to https://quickdumpnow.com/books, CloudFront was returning the homepage instead of the books landing page.

Root cause: CloudFront's custom error response configuration was redirecting all 404 errors back to the homepage. This is a common pattern for single-page applications (SPAs) but breaks pretty URL routing for multi-page static sites.

The Solution: Dual S3 Object Keys

We deployed the books page to two S3 object keys simultaneously:

  • s3://quickdumpnow.com/books/index.html — the canonical path
  • s3://quickdumpnow.com/books — the pretty URL key (without the index.html suffix)

This dual-upload pattern allows CloudFront to serve the content at both paths. The S3 bucket has no index document rewrite rules enabled (which would require full website hosting mode); instead, we're leveraging CloudFront's origin behavior to handle the content negotiation.

Robots.txt Update

We also modified /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt to block the /books path from search engine crawlers:

Disallow: /books

Rationale: The books interface is an internal receipt management tool, not a public-facing page. Blocking crawlers reduces noise in server logs and prevents this development/operational interface from being indexed.

CloudFront Invalidation

After uploading both the books/index.html and `books` objects to S3, we invalidated the CloudFront distribution cache:

aws cloudfront create-invalidation \
  --distribution-id [DISTRIBUTION_ID] \
  --paths "/books" "/books/*" "/robots.txt"

Invalidating with wildcards ensures that both the pretty URL and any nested paths (future receipt pages, sub-routes) are purged from edge caches. Propagation to all edge locations typically takes 30–60 seconds.

Technical Details: OAuth Token Refresh and Port Sheet Automation

The Challenge: Long-Lived Google API Credentials

The JADA port sheet automation requires persistent access to two Google APIs:

  • Google Sheets API — to read/write the Port Log spreadsheet
  • Google Calendar API — to read charter bookings and extract revenue data

OAuth 2.0 access tokens expire after 1 hour. For unattended automation (scripts running in CI/CD or cron jobs), we need a mechanism to refresh tokens without manual intervention.

Token Refresh Implementation

We created /Users/cb/Documents/repos/tools/reauth_jada_calendar.py to handle OAuth token refresh using the refresh token grant:

# Pseudocode structure (no actual secrets shown)
import json
import requests

def refresh_oauth_token(refresh_token, client_id, client_secret):
    """
    Exchange a refresh token for a new access token.
    Refresh tokens persist indefinitely until revoked.
    """
    token_endpoint = "https://oauth2.googleapis.com/token"
    
    payload = {
        "client_id": client_id,
        "client_secret": client_secret,
        "refresh_token": refresh_token,
        "grant_type": "refresh_token"
    }
    
    response = requests.post(token_endpoint, data=payload)
    return response.json()  # Contains new access_token and expiry

The refresh token is stored securely in a credential file alongside the OAuth client configuration. Unlike access tokens, refresh tokens remain valid unless explicitly revoked, making them ideal for long-lived automation.

Port Sheet Data Pipeline

The port sheet automation reads Excel files from Google Drive and syncs charter booking data into Google Sheets:

  1. jada_port_sheet.py — main driver script that orchestrates the sync
  2. Uses openpyxl to parse Excel workbooks (specifically the port sheet template with Month, Template, and monthly tabs)
  3. Queries Google Sheets API to get the JADA Port Log 2026 sheet
  4. Writes charter entries (charter name, date, revenue, captain, vessel) to the appropriate monthly tab

For the entry we just recorded:

Charter: Joseph Zurek
Date: [Previous day]
Revenue: $1,845.72
Port: [extracted from calendar event]
Tab: April 2026

The script dynamically creates monthly tabs if they don't exist, maintaining the same column structure across all months.

Port 8765 and Local OAuth Flow

During development of the reauth script, we encountered a stale Python process holding port 8765 (the default OAuth callback port for Google's local server):

lsof -i :8765  # Identify processes on the port
kill -9 [PID]  # Force-terminate the stale process

This is a common issue when OAuth flows are interrupted mid-authentication. The local callback server doesn't clean up gracefully, leaving the port bound. Always kill stale processes before re-running OAuth authentication flows locally.

Infrastructure and Architecture Decisions

Why S3 + CloudFront for Static Content?

S3 provides:

  • Versioning and object lifecycle policies for automatic cleanup
  • Low egress costs when paired with CloudFront
  • No server management overhead
  • Native integration with CloudFront for global distribution

The books interface is currently static HTML; future receipt upload functionality would require a backend API (Lambda + API Gateway), but the CDN layer remains unchanged.

Why OAuth Refresh Tokens for Automation?

Alternatives considered:

  • Service accounts: Require domain-wide delegation; not applicable here since we're accessing user-owned calendars and sheets.