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 Email Delivery Recovery

Overview: The Problem

During a routine ops session on June 2, 2026, we discovered that the deposit/reservation widget across 11 event pages was returning either 403 (Forbidden) or 404 (Not Found) responses. This represented a total stoppage of inbound bookings and deposits—a critical revenue leak affecting every public-facing "Reserve" call-to-action on sailjada.com and queenofsandiego.com. The root cause was split between two Google Apps Script deployments with revoked access permissions and one with a deleted deployment state.

Investigation & Diagnosis

Step 1: Endpoint Enumeration

We began by identifying all live HTTP endpoints serving the deposit widget across event pages:

  • Primary endpoint: https://script.google.com/macros/s/AKfycbw...44Pme8wCA/exec (10 pages, returning 403)
  • Worship site endpoint: https://script.google.com/macros/s/AKfycbw...AFsLWaO3/exec (1 page, returning 404)

The 403 indicated access control was explicitly denied; the 404 indicated the deployment had been removed entirely.

Step 2: Project ID Mapping

We cross-referenced the Apps Script project IDs by:

  • Searching jada-ops for project name references
  • Inspecting clasp configuration files in source repos (typically .clasp.json)
  • Reading appsscript.json manifest files to confirm display names and deployment metadata
  • Verifying ownership and deployment history via the Google Apps Script console

This mapped to project ID 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5, which manages both the primary 10-page booking flow and related deposit processing.

Root Cause Analysis

Access Control Misconfiguration

The primary Apps Script deployment had been configured with Who has access = Only myself, which by default blocks all external HTTP requests. Google's Apps Script deployments are access-controlled at the deployment level, not the code level—any request from an unauthenticated caller (i.e., the browser) will be rejected with 403 unless the deployment is explicitly marked as "Anyone" or restricted to specific OAuth scopes.

Deleted Deployment (Worship Endpoint)

The worship site's endpoint returned 404 because the corresponding deployment had been removed, likely during a cleanup or failed redeployment attempt. The underlying Apps Script code may still exist in the project, but without an active deployment pointing to an /exec endpoint, there is no callable URL.

Infrastructure & Technical Details

Apps Script Deployment Model

Google Apps Script deployments work as follows:

  • A project contains code and configuration (managed in the Apps Script editor or via clasp CLI)
  • A deployment is a versioned snapshot that receives its own unique /exec
  • Each deployment has independent access control settings: Who has access (Anyone / Only myself / specific users) and Execute as (Me / User accessing the app)
  • The /exec URL is stable as long as the deployment is active; redeploying into the same slot preserves the URL

In our case, the 10-page endpoint was deployed but access-locked; the worship endpoint's deployment had been deleted entirely.

Page-Level Integration

The event pages (located in /Documents/repos/sites/queenofsandiego.com/ and /Documents/repos/sites/sailjada.com/) embed the Apps Script /exec URL directly in client-side JavaScript, typically in a form submission handler or AJAX call. Example pattern:

fetch('https://script.google.com/macros/s/AKfycbw...44Pme8wCA/exec', {
  method: 'POST',
  payload: JSON.stringify({ charter_id, deposit_amount, ... })
})

When the deployment is inaccessible, the fetch fails silently to the user, leaving them with a non-responsive "Reserve" button.

Staging the Fix

Configuration Change (No Code Deploy)

The fix requires a single configuration change in the Google Apps Script console—no code changes, no source repo commits, no S3/CloudFront invalidation needed:

  1. Navigate to script.google.com and open project 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5
  2. Select Deploy → Manage deployments
  3. Edit the active deployment and set:
    • Who has access = Anyone (allows unauthenticated HTTP calls)
    • Execute as = Me (runs the script under the project owner's account, preserving database write permissions)
  4. Click Deploy to apply

For the worship endpoint, we need to create a new deployment from the same project or identify whether the code should be merged back into the primary project.

Documented in Ops Notes

The complete fix steps and project ID were documented in jada-ops/DEPOSIT-OUTAGE-FIX-2026-06-02.md for future reference and auditing.

Multi-Region Email Delivery Context (Parallel Work)

During the same session, we also addressed a secondary revenue impact: email deliverability in SES. Analysis showed:

  • SES suppression lists in both us-east-1 and us-west-2 contained false-positive bounces and complaints
  • An unsubscribe processor script (jada-ops/email-lists/build/process_unsubscribes.py) was created to safely harvest unsubscribe addresses from DynamoDB and scrub the suppression lists
  • A LaunchAgent (~/Library/LaunchAgents/com.jada.unsubscribe-watcher.plist) was configured to run the processor on a scheduled interval, keeping suppression lists clean

This work addresses the secondary revenue leak: charters going out to subscribers who had legitimately unsubscribed, causing complaints and further suppression.

Key Decisions & Rationale

Why "Anyone" Access?
The deposit widget is a public-facing feature with no authentication requirement—users should be able to click "Reserve" without logging in. Setting access to "Anyone" with "Execute as Me" allows the unauthenticated request to succeed while ensuring the script's database