Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Deploying a Receipts Management System for quickdumpnow.com and Automating Port Sheet Distribution

What Was Done

This session involved two parallel infrastructure projects: (1) deploying a receipts management landing page for the quickdumpnow.com trailer rental business, and (2) completing the automation pipeline for the JADA Port Log, including calendar token refresh and email distribution.

QuickDumpNow Books/Receipts Page Deployment

The quickdumpnow.com domain needed a dedicated endpoint at /books to serve as the entry point for trailer rental receipts. The page existed locally but had never been deployed to production.

Problem: CloudFront Custom Error Responses

When accessing https://quickdumpnow.com/books, CloudFront was returning the homepage instead of the receipts page. This was caused by a custom error response configuration on the CloudFront distribution that redirects all 404 errors to the root index.html. While this is useful for single-page applications, it prevented pretty URLs from working correctly.

The distribution was configured with:

  • Origin pointing to S3 bucket for quickdumpnow.com
  • Custom error response: 404 → /index.html (origin response)
  • No specific behavior rule for /books* paths

Solution: Dual S3 Object Upload with CloudFront Invalidation

Rather than modify the CloudFront distribution (which would risk breaking other functionality), we deployed the receipts page to S3 as two separate objects:

s3://quickdumpnow.com/books/index.html
s3://quickdumpnow.com/books

This dual-upload approach ensures that both /books/ (directory-style) and /books (bare path) requests resolve correctly. The file at /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html was uploaded with:

  • Content-Type: text/html; charset=utf-8
  • Cache-Control: max-age=3600 (1 hour TTL)
  • CloudFront invalidation patterns: /books and /books/*

The robots.txt file at /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt was also updated to block this path from search engine crawling, since it serves as an internal receipts system:

Disallow: /books/

CloudFront invalidation took approximately 30–60 seconds to propagate across edge locations.

JADA Port Sheet Automation: Calendar Token and Email Distribution

The port sheet system required two critical fixes: re-establishing Google Calendar API authentication and completing the email distribution pipeline.

OAuth Token Refresh Challenge

The JADA port sheet system reads calendar events from Google Calendar and writes structured data to a Google Sheet (ID: stored in environment). The system had stale OAuth tokens that could no longer refresh. Rather than manually re-authenticate, we built a standalone reauthentication script.

The script at /Users/cb/Documents/repos/tools/reauth_jada_calendar.py implements OAuth 2.0 authorization code flow:

  • Reads client credentials from ~/.secrets/google_calendar_client_secret.json
  • Validates that the client type is installed (desktop app), not web
  • Spins up a local HTTP server on port 8765 to receive the authorization callback
  • Constructs the authorization URL with scopes: calendar and drive
  • Prompts user to authorize in browser, then captures the authorization code from the callback
  • Exchanges the code for tokens (access + refresh) via the Google token endpoint
  • Writes the refresh token to ~/.secrets/GCAL_REFRESH_TOKEN for persistent use

The refresh token is then injected into the AWS Lambda environment as GCAL_REFRESH_TOKEN, allowing scheduled invocations of jada_port_sheet.py to refresh the access token on-the-fly without manual intervention.

Port Log Data Structure and Excel Integration

The JADA Port Log is stored as an Excel file on Google Drive. Unlike simpler spreadsheet formats, the Excel workbook requires precise preservation of multiple sheets, row heights, and column widths. The port sheet template follows a specific structure:

  • Month sheet (e.g., "April"): Contains the month name in a specific cell
  • Template sheet: Defines headers and formatting for charter entries
  • Data rows: Each charter entry includes name, date, vessel, and revenue

To add the latest charter entry ($1,845.72 for a charter yesterday), the system:

  1. Downloads the current Excel file from Drive using the Google Drive API
  2. Parses the structure using the openpyxl library (handles .xlsx format)
  3. Creates or updates the current month sheet
  4. Appends the entry in the correct row and column format
  5. Uploads the modified file back to Drive

The jada_port_sheet.py script at /Users/cb/Documents/repos/tools/jada_port_sheet.py handles this end-to-end:

# Pseudocode structure
from openpyxl import load_workbook
from google.oauth2.credentials import Credentials

# Refresh access token using stored refresh_token
creds = refresh_access_token(refresh_token)

# Download and parse Excel
excel_bytes = drive_service.files().get_media(fileId=PORT_LOG_ID).execute()
workbook = load_workbook(io.BytesIO(excel_bytes))

# Add new charter entry
month_sheet = workbook[current_month]
month_sheet.append([charter_name, date, vessel, revenue])

# Upload modified workbook
upload_file_to_drive(PORT_LOG_ID, workbook)

Email Distribution Pipeline

Once the port sheet is updated, the system emails it to stakeholders (Edwin, Maria, Caro) using the Gmail API. The email sender is configured in the Apps Script file at /Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs.

The script:

  • Reads the updated Port Log from Drive
  • Formats the current month's data into an email body
  • Sends via Gmail with the Excel file as an attachment
  • Logs delivery status to the sheet

Infrastructure and Deployment

CloudFront Distribution: The quickdumpnow.com CloudFront distribution has its origin configured to the S3 bucket. Invalidation patterns are managed via the AWS CLI or SDK.

AWS Lambda: The port sheet automation runs on a schedule (CloudWatch Events). Environment variables include