Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Deploying a Receipt Management System for quickdumpnow.com and Automating JADA Port Sheet Updates

What Was Done

This session involved two parallel infrastructure projects: deploying a receipt management landing page for the quickdumpnow.com books subdirectory, and establishing an automated workflow for updating the JADA port sheet with charter entry data. Both required careful coordination of static site hosting, CDN invalidation, and API authentication flows.

The quickdumpnow.com Books Page Deployment

The quickdumpnow.com domain needed a dedicated receipt management interface at /books. The local codebase already contained the landing page template at:

/Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html

The challenge was that accessing https://quickdumpnow.com/books was returning the homepage instead of the intended page. This happened because CloudFront had a custom error response configuration that redirected all 404 errors to the root index.html — a common pattern for single-page applications, but problematic for static multi-page sites.

S3 Upload Strategy

We uploaded the books page to two S3 keys for proper URL routing:

  • s3://quickdumpnow-website/books/index.html — explicit key for direct file access
  • s3://quickdumpnow-website/books — bare key to support pretty URLs (requests to /books default to /books/index.html)

This dual-key approach ensures that both /books and /books/ requests resolve correctly, following AWS S3 best practices for directory-like paths.

Robots.txt Configuration

We updated /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt to block the new books endpoint from search indexing:

User-agent: *
Disallow: /books

This prevents the receipt management page from appearing in search results, keeping the business listing clean and professional in SERPs.

CloudFront Invalidation

After uploading both S3 objects, we invalidated the CloudFront distribution cache for the affected paths:

  • /books
  • /books/
  • /robots.txt

CloudFront invalidations typically propagate within 30-60 seconds. The invalidation ID was tracked for monitoring edge cache clearing across all PoPs (Points of Presence).

JADA Port Sheet Automation

The second major task involved automating updates to the JADA sailing charter port sheet. This required three components: reading the existing Excel port sheet structure, updating it with new charter entries, and re-establishing OAuth authentication for calendar integration.

Port Sheet Structure Analysis

We analyzed the existing port sheet located at a specific Drive file ID, examining its structure to understand the format:

  • Multiple tabs (sheets) including a "Template" tab defining the schema
  • Column headers for charter details (vessel, captain, entry/exit times, fees)
  • Monthly tabs (March 2026, April 2026, etc.)
  • Row heights and column widths preserved from the original Excel format

The port sheet needed to accept new entries like the recent charter: $1,845.72 for Joseph Zurek's vessel.

Python Automation Script

We created and iteratively refined /Users/cb/Documents/repos/tools/jada_port_sheet.py to:

  • Authenticate to Google Drive using OAuth2 credentials
  • Download the existing port sheet Excel file
  • Parse the Template sheet to extract the correct column structure
  • Create or update monthly tabs (e.g., April 2026)
  • Append new charter entries in the correct format
  • Upload the modified file back to Drive

The script uses the openpyxl library to preserve Excel formatting, row heights, and column widths — critical for maintaining the port sheet's visual consistency.

OAuth Reauthentication Workflow

The initial authentication token for Google Calendar had expired or lacked proper scopes. We created /Users/cb/Documents/repos/tools/reauth_jada_calendar.py to:

  • Launch a local OAuth2 flow on port 8765
  • Request the necessary scopes (Drive and Calendar APIs)
  • Store the refresh token securely in a credentials file
  • Allow the jada_port_sheet.py script to use the refreshed token

The reauthentication script performs a system check for port availability, killing any existing process on port 8765 before binding the local server. This prevents port conflicts during development.

Key Technical Decisions

Why Dual S3 Keys for the Books Page?

CloudFront requires both an explicit path (books/index.html) and a directory-like path (books) to handle browser requests correctly. Without the bare key, requests to /books may fail depending on CloudFront's index document configuration. The dual approach is defensive and follows AWS documentation for static site hosting.

Why Block /books in robots.txt?

The receipt management system is an internal tool for the business owner, not a public listing. Blocking it in robots.txt signals to search engines that this content shouldn't be crawled or indexed, reducing noise in analytics and keeping the domain's SEO profile focused on the main business landing page.

Why Preserve Excel Formatting in openpyxl?

The port sheet is a shared document used by multiple team members. Preserving row heights, column widths, and cell formatting ensures the document remains readable and professional when opened in Excel or Sheets. Stripping formatting could introduce inconsistencies that confuse users or break macros.

Why Separate Reauthentication Script?

The main port sheet script (jada_port_sheet.py) focuses on data operations, while reauth_jada_calendar.py handles the OAuth flow separately. This separation of concerns makes the authentication flow explicit, testable, and reusable if other scripts need calendar access in the future.

What's Next

  • Verify books page deployment: Confirm https://quickdumpnow.com/books loads the receipt management page (not the homepage) within 30-60 seconds of CloudFront invalidation
  • Test port sheet entry: Run the jada_port_sheet.py script with the Zurek charter data ($1,845.72) and verify it appears in the April 2026 tab with correct formatting
  • Automate monthly runs: Consider scheduling jada_port_sheet.py as a cron job or Cloud Scheduler task to pull updated charter data and sync the port sheet daily or weekly
  • Add error handling: Implement retry logic and notifications (email/Slack) if the port sheet sync fails due to auth issues or Drive quota limits
  • Document receipt workflow: Create a guide for team members on uploading receipts to the books page and how it integrates with