Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Diagnosing and Staging a Multi-Endpoint Deposit System Outage: Google Apps Script Access Control

What Was Done

A critical revenue leak was identified across 11 event booking pages: the "Reserve" widget on sailjada.com and queenofsandiego.com was silently failing for all users. The root cause was traced to two Google Apps Script deployments returning 403 (Forbidden) and 404 (Not Found) responses on their /exec endpoints. These endpoints handle real-time deposit calculations and reservation submissions—without them, the entire inbound booking funnel is dark.

The investigation and staging took place across three phases:

  • Live endpoint inspection: Verified HTTP status codes and response bodies for both Apps Script projects
  • Source correlation: Traced deployment IDs through Google clasp configs and Apps Script project manifests
  • Access control diagnosis: Identified that deployment access was set to private or had been undeployed
  • Fix staging: Documented the exact Google Cloud console steps required to restore access to "Anyone" with proper execution context

Technical Details: Endpoint Architecture

The booking system uses two independent Google Apps Script projects deployed as HTTP endpoints:

  • Primary 10-page endpoint: https://script.google.com/macros/s/AKfycbxxx44Pme8wCA/exec
    • Serves deposit calculations for: sailjada.com (9 event pages) + queenofsandiego.com (1 page)
    • Project ID: 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5
    • Current status: 403 Forbidden
  • Worship-specific endpoint: https://script.google.com/macros/s/AKfycbxxx...AFsLWaO3/exec
    • Serves deposit calculations for queenofsandiego.com worship charters only
    • Current status: 404 Not Found (deployment deleted or undeployed)

Both endpoints are called by client-side JavaScript embedded in the event pages. The calls are synchronous POST requests with charter metadata; the responses include deposit amount, tax, and total due. A 403 or 404 silently breaks the Reserve button without user feedback.

Root Cause Analysis

Google Apps Script deployments have two critical settings that control public accessibility:

  • Who has access: Can be "Me only," "Anyone," or specific user lists
  • Execute as: The identity under which the script runs (usually the deployment owner)

The 403 response indicates the primary project's deployment is set to "Me only" or a restricted group. The 404 indicates the worship endpoint's deployment was either deleted or rolled back. Without "Anyone" access, the public /exec URL denies all requests.

Investigation Methodology

To isolate the problem, we performed three parallel checks:

  • HTTP status verification: 
    curl -i https://script.google.com/macros/s/AKfycbxxx44Pme8wCA/exec \
  -X POST \
  -H "Content-Type: application/json" \
  -d '{"charter_id":"test"}'
    Confirmed 403 with no CORS headers, ruling out client-side issues.
  • clasp config cross-reference: Located ~/.clasp.json files in jada-ops and queenofsandiego.com source repos; matched scriptId values to Google Cloud project IDs to confirm which deployment owned which endpoint.
  • appsscript.json inspection: Read project manifests to find "displayName" fields and match them to handoff notes, ensuring we had the correct projects targeted.

Infrastructure: Google Cloud Setup

The two Apps Script projects exist in the same Google Cloud organization (under the JADA/Queen of San Diego account). Each has:

  • A unique scriptId in the Google Cloud console
  • One or more deployments, each with a unique /exec URL
  • Deployment-level IAM bindings controlling public access

The event pages (hosted on S3 + CloudFront) embed the /exec URLs directly in JavaScript. When a user clicks "Reserve," the browser makes a cross-origin POST to one of these endpoints. The endpoint response populates the deposit summary.

The Fix: Deployment Access Control

To restore the endpoints, the following Google Cloud console action is required for each deployment:

  1. Navigate to script.google.com
  2. Open the project (primary: ID ending ...lQJ5)
  3. Select Deploy → Manage deployments
  4. Click the deployment ID (should be the latest or only one)
  5. Set:
    • Who has access: "Anyone"
    • Execute as: "Me" (the account that owns the script)
  6. Click Deploy

For the worship endpoint (404 case), if the deployment has been deleted, a new deployment must be created from the current script version before the access steps above can apply.

Why This Architecture?

Google Apps Script /exec endpoints were chosen for deposit calculation because:

  • Server-side authority: Calculations happen in Google's environment, not in user JavaScript (prevents tampering)
  • Google Sheets integration: The scripts read charter metadata and pricing rules from a shared Sheets workbook
  • Zero infrastructure: No EC2, Lambda, or container management; Google handles scaling and availability
  • Cross-origin safety: /exec endpoints can be made public without requiring explicit CORS setup (Google handles it)

Blast Radius & Business Impact

With both endpoints down:

  • 11 event pages have a broken Reserve button
  • 100% of inbound bookings are blocked; users see no error message, just a silent failure
  • Revenue is unmeasurable: No funnel visibility; unknown number of abandoned reservations
  • Time-sensitive: Each day the outage persists, chartering inquiries disappear into the void

Key Decisions

  • Root cause first: Rather than blindly redeploying, we traced the actual HTTP responses to confirm access control was the issue, not code bugs or network problems.
  • Parallel endpoints as a feature: Keeping the worship endpoint separate allows independent versioning and testing for that charter type; during this outage, it also meant we could isolate which project needed attention first.