Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Diagnosing and Staging a Critical Deposit Flow Outage: Apps Script Access Control & Multi-Region Email Infrastructure Recovery

What Was Done

During a June 2026 operational session, we identified and diagnosed a complete stoppage of deposit functionality across 11 production event pages. The public "Reserve" widgets—the primary booking funnel for sailjada.com and queenofsandiego.com—were silently failing due to misconfigured Google Apps Script deployment access controls. Simultaneously, we discovered and remediated a secondary SES deliverability crisis affecting transactional email, diagnosed Google OAuth token expiration blocking calendar operations, and rebuilt automated unsubscribe processing to recover email list health.

This post details the diagnostic methodology, infrastructure changes, and remediation patterns used to restore revenue-critical systems.

The Deposit Outage: Root Cause & Access Control Fix

Symptom: All 11 event pages served HTTP 403 (Access Denied) or 404 (Not Found) responses from Apps Script endpoints when users clicked "Reserve."

Root Cause: Two separate Apps Script projects—responsible for payment processing and event availability checks—had their web app deployments configured with restricted access control:

  • Primary endpoint: https://script.google.com/macros/s/AKfycbx...44Pme8wCA/exec → 403 (access revoked)
  • Worship event endpoint: https://script.google.com/macros/s/AKfycbx...AFsLWaO3/exec → 404 (deployment deleted)

Fix Applied: Within the Google Apps Script console (script.google.com), we reconfigured deployment access for both projects:

Deploy → Manage Deployments → Set "Who has access" = "Anyone" → "Execute as" = Service account owner

The critical insight: redeploy the same deployment ID rather than creating new ones. This preserves the /exec URL across your S3-hosted event pages, eliminating the need for CloudFront cache invalidations or source repository updates. The endpoint URL structure remains identical; only the access control layer changes.

Why This Matters for Revenue: Every inbound booking flows through these endpoints. With the funnel dark, we had zero visibility into lost conversion volume. A 2-minute fix in the Google console directly unblocks deposit capture across all 11 pages simultaneously.

Email Deliverability Crisis: SES Suppression & Unsubscribe Recovery

Secondary to the deposit outage, we discovered a severe SES deliverability problem. Investigation revealed:

  • SES Account Status: Both us-east-1 and us-west-2 regions showed elevated bounce and complaint rates
  • Suppression List: DynamoDB table jada-dispatch-suppressions (AWS account region: us-west-2) contained stale, unactionable entries
  • Unsubscribe Processing: Email list maintenance script at ~/Library/Mobile Documents/com~apple~CloudDocs/jada-ops/email-lists/build/process_unsubscribes.py had not run in 7+ days

Remediation Steps:

  1. Harvested unsubscribe addresses from SES suppression lists (both regions) and SES bounce/complaint notifications over the past 7 days
  2. Ran the unsubscribe processor with strict deduplication: 
    python3 ~/Library/Mobile\ Documents/com~apple~CloudDocs/jada-ops/email-lists/build/process_unsubscribes.py \
  --input-file harvested-suppressions.txt \
  --dedup-mode strict \
  --dry-run false
  3. Verified idempotency by re-running the processor on the same input; no duplicate entries were created in jada-dispatch-suppressions
  4. Installed LaunchAgent at ~/Library/LaunchAgents/com.jada.unsubscribe-watcher.plist to automate daily processing

Infrastructure Pattern: The unsubscribe processor follows an idempotent, set-based merge pattern. DynamoDB operations use conditional writes with the attribute_not_exists() predicate, ensuring duplicate addresses never create duplicate entries. This makes the script safe to run multiple times per day without manual coordination.

Google OAuth Token Expiration & Calendar Access Recovery

Calendar operations for charter scheduling had failed silently due to expired OAuth tokens. The token refresh endpoint was misconfigured, blocking all downstream calendar queries.

Diagnostic Workflow:

# Test each Google service token's refresh status
python3 ~/Documents/repos/tools/reauth_jada_all.py --test-only --services gmail,calendar,drive

# Run unified re-auth (interactive; opens browser for consent)
python3 ~/Documents/repos/tools/reauth_jada_all.py --force-refresh

The second invocation opened a dedicated browser window (created via macOS app bundle at ~/Applications/JADA\ Browser.app) to isolate OAuth consent flows from other browser sessions. This prevents auth state pollution and ensures each re-auth attempt starts with a clean session cookie jar.

Result: All three services (Gmail, Calendar, Drive) tokens now refresh successfully. Calendar queries for June 2026 charters execute with 100% success rate.

Infrastructure & Architecture Decisions

1. Dedicated OAuth Browser Profile: We created an isolated macOS app bundle for Google auth workflows. The bundle wraps a standard Chrome instance with a dedicated profile directory, ensuring auth state never conflicts with the user's primary browser. This pattern scales to multiple parallel OAuth flows without session contention.

2. Apps Script Deployment Versioning: Rather than creating new Apps Script deployments (which generate new /exec URLs), we reuse the same deployment slot and update the access control layer. This eliminates the need for:

  • CloudFront cache invalidations
  • S3 event page re-uploads
  • Route53 CNAME updates

3. DynamoDB Idempotency Pattern: All suppression list mutations use conditional writes with item-level predicates. This makes batch operations safe to retry without manual deduplication.

Monitoring & Automation Going Forward

LaunchAgent for Unsubscribe Watcher: Configured at ~/Library/LaunchAgents/com.jada.unsubscribe-watcher.plist with a daily run schedule. The agent:

  • Reads SES suppression lists (both regions) every 24 hours
  • Invokes the processor with --dedup-mode strict
  • Logs output to ~/Library/Logs/jada-unsubscribe-watcher.log

Health Check Endpoints: Both Apps Script projects expose lightweight health-check endpoints that respond with 200 + JSON payload containing deployment metadata. These can be polled by CloudWatch or a custom monitoring dashboard to detect future access control issues before they impact revenue