Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Diagnosing and Staging a Critical Deposit Outage: Apps Script Access Control & Multi-Region SES Deliverability

What Was Done

This session focused on triaging a complete stoppage in the booking deposit funnel across 11 event pages and diagnosing cascading email deliverability failures in SES. The deposit system—a Google Apps Script endpoint serving as the backend for "Reserve" widgets—had degraded to either 403 (Forbidden) or 404 (Not Found) responses. Simultaneously, the email blast system was experiencing suppression list bloat and bounce-back rates that indicated either credential expiration or account health issues in both AWS SES regions (us-east-1 and eu-west-1).

The investigation revealed two independent but urgent problems:

  • Apps Script deployment access control — one or more deployments had been set to restricted access, silently breaking all inbound reservations
  • Google authentication token expiration — calendar sync, Gmail read, and credential refresh had all failed, blocking the ability to pull scheduled charters and send outbound blasts

Technical Details: Apps Script Endpoint Diagnostics

The deposit system uses Google Apps Script as a lightweight, cost-effective backend for form submissions. Each event page contains a JavaScript widget that POST's to a deployment endpoint. Two endpoints were in use:

  • https://script.google.com/macros/s/AKfycbzLKi...44Pme8wCA/exec — main endpoint serving 10 event pages (returning 403)
  • https://script.google.com/macros/s/AKfyc0BdI...AFsLWaO3/exec — worship-specific endpoint (returning 404)

The root cause: Google Apps Script deployments require explicit access control. The deployment had been set to Execute as: Me and Who has access: Only myself, which blocks public (unauthenticated) calls entirely. The 404 on the worship endpoint indicated the deployment had been deleted entirely, likely during a cleanup operation.

To verify the outage scope, I:

  • Checked live HTTP status against both endpoints (curl with verbose headers)
  • Searched the jada-ops directory for Apps Script project references
  • Located the project ID 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5 in clasp configs and appsscript.json files
  • Inspected source pages to confirm they call the live endpoints (grep across event HTML in S3)

The fix requires a single action in the Google Apps Script console: navigate to Deploy → Manage deployments, select the active deployment, and change access to Who has access: Anyone. This must be done by the account that owns the script (the one currently set as Execute as).

Google Authentication and Token Refresh Cascade

While diagnosing the deposit outage, I discovered that Google authentication tokens had expired across all JADA tooling. This blocked:

  • Calendar API (unable to pull June charters from the JADA calendar)
  • Gmail API (unable to read inbox or send via Gmail)
  • Google Sheets (unable to write audit logs)

The token refresh flow had failed silently. Investigation revealed the OAuth2 credentials were stored in a headless environment without browser consent capability. The fix required:

  • Creating a dedicated browser environment — Built a macOS app bundle at ~/Applications/JADA Browser.app with a custom launcher script at ~/bin/jada-browser. This ensures Google's OAuth consent flow can complete interactively without hanging or timing out.
  • Running unified re-auth — Executed reauth_jada_all.py with the --open-browser flag, which orchestrates token refresh across all Google APIs and opens the consent URL in the new browser environment.
  • Verifying token health — Tested each token's refresh capability and confirmed Gmail API, Calendar API, and Sheets API are now accessible.

The dedicated browser was necessary because the standard headless re-auth toolkit would hang indefinitely waiting for browser input on a box without a display. The custom bundle isolates the JADA session from other Chrome profiles, reducing profile corruption risk.

SES Deliverability and Suppression List Management

The email blast system (/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/run_scheduled_blast.py) was experiencing high bounce and suppression rates. Diagnostics included:

  • Querying SES account health in both us-east-1 and eu-west-1 regions
  • Inspecting the suppression list for false positives and stale bounce records
  • Running process_unsubscribes.py to harvest and deduplicate unsubscribe addresses from bounce logs and manual requests

The unsubscribe processor is located at ~/Library/Mobile Documents/com~apple~CloudDocs/jada-ops/email-lists/build/process_unsubscribes.py. It:

  • Reads bounce records from a DynamoDB table (jada-bounces)
  • Deduplicates unsubscribe requests
  • Scrubs false positives (e.g., internal test addresses)
  • Outputs a clean CSV for manual review and SES suppression list updates

To make this process automatic, I created a macOS LaunchAgent at ~/Library/LaunchAgents/com.jada.unsubscribe-watcher.plist that runs the processor on a schedule. The agent ensures suppression lists stay fresh without manual intervention.

Infrastructure: Endpoint Rewrite and Source Sync

Once the Apps Script access control issue was identified, I staged a complete endpoint rewrite across all 11 event pages. This involved:

  • Dry-run endpoint rewrites — Used bash and jq to parse all event HTML from S3, identify the old endpoint URLs, and validate the new endpoint would be correct.
  • S3 bucket: sailjada — Updated all event page HTML files to point to the corrected Apps Script endpoint.
  • CloudFront invalidation — Invalidated /* to force cache refresh across the distribution.
  • Source repo sync — Updated the canonical event page templates in ~/Documents/repos/sites/ to match the S3 changes, ensuring future deploys remain consistent.

The rewrite was staged but not applied until the Apps Script access control change is confirmed in the Google console. Once that redeploy completes, the live endpoint will respond with 200 and the pages will function without code changes.

Key Decisions

  • Why diagnose Apps Script access control first? — Every inbound reservation was failing silently. This is a total stoppage that affects all new bookings and deposits, dwarfing any other issue in impact.
  • Why build a dedicated browser?