Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Multi-Domain Static Site with CloudFront Custom Error Handling and Google Apps Script Integration

This session focused on deploying a new receipts management page to quickdumpnow.com while simultaneously integrating a Google Apps Script-based port logging system. The work involved S3 object management, CloudFront cache invalidation patterns, and API-driven spreadsheet updates across multiple business domains.

What Was Done

  • Created and deployed /books/index.html to enable receipt tracking for a trailer rental business
  • Updated robots.txt to block search engine indexing of the new /books path
  • Deployed to S3 and invalidated CloudFront distribution caches
  • Diagnosed and worked around CloudFront custom error response behavior
  • Integrated with Google Apps Script to automate port sheet logging via the Sheets API
  • Added charter payment entry ($1,845.72) to the Port Log spreadsheet programmatically

Technical Details: S3 and CloudFront Deployment

The quickdumpnow.com static site is hosted on S3 with CloudFront as the CDN. The site structure mirrors typical multi-section deployments:

/Users/cb/Documents/repos/sites/quickdumpnow.com/
├── books/
│   └── index.html
├── robots.txt
└── [other site files]

The books directory was created to segment the receipts functionality from the main landing page. This organizational pattern follows REST conventions where logical resources are isolated into their own URL paths.

S3 Upload Strategy

A critical discovery during deployment was that CloudFront's custom error responses (likely configured to serve the homepage for 404s) were interfering with pretty URLs. To work around this:

  • The books/index.html file was uploaded to two S3 keys:
    • s3://quickdumpnow-bucket/books/index.html — standard directory structure
    • s3://quickdumpnow-bucket/books — bare key for direct access
  • robots.txt was updated to include blocking directives for the /books path to prevent indexing of the receipts page

Upload commands (sanitized):

aws s3 cp books/index.html s3://quickdumpnow-bucket/books/index.html \
  --content-type "text/html" \
  --cache-control "max-age=3600"

aws s3 cp books/index.html s3://quickdumpnow-bucket/books \
  --content-type "text/html" \
  --cache-control "max-age=3600"

aws s3 cp robots.txt s3://quickdumpnow-bucket/robots.txt \
  --content-type "text/plain"

CloudFront Cache Invalidation

After S3 uploads, CloudFront invalidations were issued to purge cached responses:

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

Invalidations propagate globally within 30-60 seconds. This timing is critical when deploying to production — clients may see stale content briefly during propagation.

Infrastructure: CloudFront Custom Error Responses

Investigation of the CloudFront distribution revealed custom error response configuration:

  • Root Cause: The distribution was configured with a 404 error response that redirects to the homepage (likely the S3 index.html)
  • Symptom: Requests to /books initially returned the homepage instead of the books/index.html content
  • Resolution: Uploading to the bare books S3 key, combined with CloudFront invalidation, ensured the correct object was served

This is a common pattern for single-page applications where all 404s should route to a JavaScript router. However, for multi-section static sites, it requires careful S3 object key planning to avoid collisions.

Google Apps Script Integration for Port Logging

Parallel to the S3 deployment, work began on automating port sheet logging. The system uses Google Apps Script as a backend with the Google Sheets API for data persistence.

Architecture Overview

  • Source of Truth: Google Sheet named "Port Log" with tabs tracking charter activity
  • Integration Point: Google Apps Script project stored in the ExpenseTracker GAS file
  • API Access: OAuth 2.0 token obtained to call Sheets API v4 directly from local scripts
  • Data Model: Port Log sheet contains headers and append-only charter entry rows

Port Log Schema

Initial inspection of the Port Log revealed the following structure:

Headers (Row 1):
- Date
- Charter Name / Description
- Amount
- Status
- Notes

Example Entry:
- 2024-01-15
- Joseph Zurek charter
- $1,845.72
- Completed
- Single payment

Programmatic Entry Insertion

Rather than manual spreadsheet entry, the workflow was automated via the Sheets API. The process involved:

  1. Authentication: Retrieving a valid OAuth 2.0 access token for the service account
  2. Sheet ID Resolution: Querying the spreadsheet to locate the correct sheet ID for the "Port Log" tab (critical because sheet names and IDs can diverge)
  3. Row Deletion: Removing any test rows from previous iterations to maintain data cleanliness
  4. Data Append: Appending the charter entry using the appendValues method

Key API calls (pseudocode):

# List all sheets to find Port Log tab ID
GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}

# Query existing rows
GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/'Port Log'!A:E

# Append new entry
POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/'Port Log'!A:E:append
{
  "values": [
    ["2024-01-14", "Joseph Zurek charter", 1845.72, "Completed", "Single payment"]
  ]
}

Why Apps Script Over Direct Database?

Google Sheets was chosen as the persistent store because:

  • Non-technical stakeholders (like Maria) can access and audit entries directly
  • No database infrastructure to maintain or secure
  • Built-in versioning and audit trail via Google Sheets' activity log
  • Familiar interface for business users

Key Decisions and Trade-offs

S3 Object Key Duplication

Uploading the same HTML file to both books/index.html and books