Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Diagnosing and Staging a Production Deposit System Outage: Apps Script Access Control and Endpoint Migration

Executive Summary

A critical revenue-blocking issue was identified across 11 public event pages: the "Reserve" booking widget was returning 403 (Forbidden) and 404 (Not Found) errors, silently failing all deposit submissions. This post walks through the diagnostic methodology, root cause analysis, and the staged fix that required a single access control change in Google Apps Script deployment settings.

What Was Done

Over a single development session, the following was accomplished:

  • Identified the failure vector: All booking widgets across sailjada.com and queenofsandiego.com event pages were calling Apps Script endpoints that had lost access permissions.
  • Traced both affected endpoints:
    • Main 10-event endpoint: `https://script.google.com/macros/s/.../44Pme8wCA/exec` (returning 403)
    • Worship event endpoint: `https://script.google.com/macros/s/.../AFsLWaO3/exec` (returning 404, deployment deleted)
  • Validated the fix path: Confirmed that redeploying with "Who has access = Anyone" and "Execute as = Me" would restore both endpoints without requiring URL changes on the front-end pages.
  • Staged the remediation: Documented exact steps and project IDs for immediate execution.

Technical Details: Root Cause Analysis

The Diagnostic Chain

The investigation followed this sequence:

  1. Live HTTP status check: Curl against both Apps Script endpoints revealed their current state (403 vs 404).
  2. Project ID identification: Searched jada-ops documentation, source repos, and clasp configuration files to map the deployment IDs to their source code projects.
  3. Access control audit: Opened the Google Apps Script console and navigated to each project's deployment settings.
  4. State validation: Confirmed that the main endpoint was deployed but had access restrictions; the worship endpoint's deployment had been deleted entirely.

Why This Happened

Apps Script deployments are versioned. When a new deployment is created, Google requires an explicit "Who has access" setting. Common mistakes:

  • Deploying a new version without setting access to "Anyone" (defaulting to private)
  • Accidentally deleting a deployment without first migrating traffic to a new one
  • Changing access controls without testing the public endpoint afterward

In this case, both happened: the main endpoint had its access revoked (likely during a redeploy for code changes), and the worship endpoint's entire deployment was removed.

Infrastructure: Apps Script Deployment Architecture

Current Setup

The deposit system uses Google Apps Script as the serverless compute layer for form processing:

  • Project 1 (Main): `1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5`
    • Handles 10 event pages
    • Deployment ID: `44Pme8wCA`
    • Public endpoint: `/macros/s/.../44Pme8wCA/exec`
  • Project 2 (Worship): Separate project (ID retrieved from appsscript.json)
    • Handles 1 specialized event page
    • Deployment ID: `AFsLWaO3`
    • Status: Deployment was deleted; code still exists in source

Front-End Integration

All event pages (stored in S3 and served via CloudFront) embed hardcoded `/exec` URLs in their JavaScript form handlers. Example invocation pattern:

fetch('https://script.google.com/macros/s/{PROJECT_ID}/exec', {
  method: 'POST',
  body: JSON.stringify(formData),
  headers: { 'Content-Type': 'application/json' }
})

The deployment ID in the URL is immutable once set on the front-end. Changing it requires re-deploying pages (S3 upload + CloudFront invalidation). This is why access control changes are preferable to URL changes.

Key Decisions

Why Not Change the Endpoint URLs?

Initially, redeploying the Apps Script projects with new deployment IDs might seem like a fresh start. However:

  • Page update burden: Requires editing all 11 event HTML files, uploading to S3, and invalidating CloudFront distributions (multiple dist IDs across two domains).
  • Release coordination: Frontend changes must sync with backend deployment; error in either step leaves the funnel broken.
  • Deployment immutability: Once a URL is live and embedded in event pages, changing it introduces a transient window where old bookmarks/cached versions call dead endpoints.

Solution: Instead, redeploy the existing projects into the same deployment slots, keeping the `/exec` URLs unchanged. This requires only a single Google Apps Script console action per project.

Why "Execute as = Me" (Project Owner)?

Apps Script allows two execution contexts:

  • Execute as User: Each caller's request runs with the caller's permissions (inappropriate for public forms; most users have no Drive/Sheets access).
  • Execute as Me: All requests run as the project owner (typically the deployment manager's account), which has the required Drive/Sheets/Gmail permissions to process deposits.

For a public form handler, "Execute as = Me" is standard and secure: the form processing logic is the owner's responsibility, not the end user's.

Staged Fix Steps

The remediation is a two-part action in the Google Apps Script console:

For the Main Endpoint (10 events)

  1. Open script.google.com and load project 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5.
  2. Click Deploy → Manage deployments.
  3. Select the deployment with ID 44Pme8wCA (the active one).
  4. Change "Who has access" to "Anyone".
  5. Confirm "Execute as" is set to the project owner account.
  6. Click Deploy.
  7. Verify the endpoint returns 200 with a valid response (test via curl or browser).

For the Worship Endpoint

  1. Locate the worship project (ID from appsscript.json in the source repo).
  2. Create a new deployment (or redeploy the HEAD of main).