Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Diagnosing and Staging a Critical Deposit Funnel Outage: Apps Script Access Control and Multi-Region Email Delivery

Executive Summary

During a routine ops session, we discovered a silent but total failure across eleven event-booking pages: the "Reserve" widget was returning HTTP 403 and 404 errors, blocking all deposit submissions. This post covers the diagnostic workflow, infrastructure dependencies uncovered, and the staged fix waiting for deployment authorization.

What Was Done

The session tackled three parallel workstreams:

  • Deposit funnel diagnosis: Traced 403/404 responses from script.google.com/macros/s/.../exec endpoints back to broken access control in two Google Apps Script deployments
  • Email delivery health check: Audited SES account status across us-east-1 and us-west-2, quantified unsubscribe backlog, and staged an automated unsubscribe watcher
  • Charter operations: Restored OAuth token health for Google Calendar and Gmail, pulled June booking list, and reviewed SMS/dispatch workflows

Technical Details: The Deposit Outage Root Cause

The deposit widget failure had two separate endpoints in play:

  • Primary endpoint (10 event pages): https://script.google.com/macros/s/AKfycbw_PQf...44Pme8wCA/exec — returning 403 Forbidden
  • Secondary endpoint (worship page): https://script.google.com/macros/s/AKfycby_UT...AFsLWaO3/exec — returning 404 Not Found

Both endpoints are Google Apps Script doPost() handlers that accept deposit form submissions (amount, email, charter ID) and write confirmations to a shared Google Sheet backing the "upcoming charters" calendar.

Why two endpoints? The Apps Script project had been re-shared and redeployed at some point; the old .../AFsLWaO3/exec deployment was deleted entirely, and the new one (`...44Pme8wCA`) was deployed but its access control remained restricted to project collaborators only, not "Anyone".

The root issue: the deployment's "Who has access" setting was still set to Editor / Project collaborators only instead of Anyone. Anonymous form submissions from public web pages cannot authenticate as a collaborator, hence the 403.

Diagnostic Workflow

We followed this sequence to isolate the problem:

  1. Live endpoint smoke test: Checked HTTP status of both URLs directly 
    curl -i https://script.google.com/macros/s/AKfycbw_.../44Pme8wCA/exec
# HTTP/1.1 403 Forbidden
  2. Source code audit: Searched /Users/cb/Documents/repos for Apps Script project metadata (clasp config, appsscript.json, deployment IDs)
  3. Project ID confirmation: Cross-referenced .clasp.json files and Google Apps Script console to confirm the project ID: 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5
  4. Deployment inventory: Listed all deployments within the project and found three:
    • Deleted deployment (404 — the old .../AFsLWaO3 endpoint)
    • Head deployment (the primary 403 — .../44Pme8wCA)
    • An older test deployment (unused)
  5. Access control inspection: Confirmed the head deployment's sharing settings were still restricted

Infrastructure and Dependencies

The deposit funnel involves multiple layers:

  • Frontend: HTML form widgets embedded on eleven S3-hosted event pages (CloudFront CDN, Route53 DNS for sailjada.com and queenofsandiego.com)
  • Backend: Google Apps Script project serving doPost() from script.google.com
  • Data store: Shared Google Sheet (email: jada-ops@...) that receives deposits and triggers calendar sync
  • Notification: SES (us-east-1 and us-west-2 regions) sends confirmation emails to depositors and ops

Because the Apps Script endpoint is called cross-origin from public web pages, it must be deployed with access level "Anyone" and execute-as "Me" (the service account). Restricting it to collaborators breaks the entire public-facing funnel.

Email Delivery Health Check

While investigating the deposit issue, we discovered a secondary problem: SES deliverability had degraded due to high bounce/complaint rates. We:

  • Audited SES suppression lists in both us-east-1 and us-west-2
  • Identified 47 unsubscribe addresses collected over seven days that were not yet suppressed
  • Built /Users/cb/Library/Mobile Documents/com~apple~CloudDocs/jada-ops/email-lists/build/process_unsubscribes.py to batch-add addresses to SES suppression
  • Ran the processor twice to verify idempotency (no duplicate suppressions)
  • Created a LaunchAgent (com.jada.unsubscribe-watcher.plist) to run the processor every 6 hours, keeping suppression lists fresh

This automation prevents manual suppression lag and protects sender reputation.

Staged Fix and Deployment Path

The fix is straightforward but requires human action in the Google console:

  1. Open script.google.com and select project 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5
  2. Navigate to Deploy → Manage deployments
  3. Click the head deployment (.../44Pme8wCA)
  4. Change "Who has access" from "Editor" to "Anyone"
  5. Keep "Execute as" set to "Me" (the service account)
  6. Click Deploy

Once deployed, both the primary endpoint and any newly created deployments will inherit the "Anyone" access, and the 403 errors will resolve immediately. The old .../AFsLWaO3 endpoint (404) can be retired from the event pages once the primary is live.

Key Decisions

  • Why not rewrite the endpoint URLs on the pages first? Because the 403 is a deployment-level access control issue, not a URL routing problem.