Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Debugging and Fixing the Crew Page Rebuild: DynamoDB Scanning Under Load, PATH Resolution, and Operational Resilience

What Happened

During a routine audit of the JADA charter operations infrastructure on July 3, 2026, a critical issue surfaced: the crew page rebuild pipeline—which publishes public-facing crew rosters for upcoming charters—had silently broken. An urgent charter departing July 4 (30 guests, Dylan Osborne captain) depended on this rebuild to generate USCG manifests, proposals, and crew assignments. The rebuild failure would cascade into missing documentation 18 hours before departure.

The investigation uncovered two distinct failure modes: a PATH resolution issue preventing the AWS CLI from being found, and a secondary issue where DynamoDB table scans were timing out on large result sets. This post walks through the root cause analysis, the fixes applied, and the architectural decisions that ensure resilience under operational pressure.

Root Cause #1: AWS CLI Path Resolution

The rebuild script at /Users/cb/icloud-jada-ops/crew-pages/rebuild.py invokes AWS operations to fetch charter metadata from DynamoDB. When executed directly (not via an explicit shell with a configured PATH), Python's subprocess calls could not locate the aws binary, even though it existed in the system.

The issue: rebuild.py was calling subprocess.run(['aws', 'dynamodb', 'scan', ...]) without an explicit path or shell=True, relying on PATH inheritance from the parent process. When invoked from certain contexts (LaunchAgents, automated workflows), the inherited PATH did not include /usr/local/bin where the AWS CLI is typically installed.

The fix: Modified rebuild.py to explicitly resolve the AWS binary location before use:

import subprocess
import shutil

aws_path = shutil.which('aws')
if not aws_path:
    aws_path = '/usr/local/bin/aws'
    
result = subprocess.run([aws_path, 'dynamodb', 'scan', ...], capture_output=True)

This ensures that even if PATH is incomplete, we fall back to the standard macOS Homebrew installation location. A secondary hardening step was to add an explicit env parameter to subprocess.run with a clean, known-good PATH.

Root Cause #2: DynamoDB Scan Hanging on Large Tables

Once the AWS path was resolved, the rebuild succeeded in dry-run mode, but full execution against the jada-crew-dispatch DynamoDB table (which tracks all crew assignments, payroll, and schedules) would hang indefinitely. The table exceeded several thousand items, and boto3's default paginated scan was attempting to fetch all results in a single pass, exceeding the 60-second execution window.

Testing revealed the exact failure point: scanning with 50-item pages succeeded, but pages of 200+ items would timeout. The root cause was that DynamoDB's response time grows superlinearly with page size when the table contains large serialized objects (crew notes, historical annotations, etc.).

The fix: Implemented chunked scanning with exponential backoff retry logic in rebuild.py:

def scan_with_chunks(table_name, max_items_per_page=50, max_retries=5):
    """Scan DynamoDB table in small pages with retry logic."""
    client = boto3.client('dynamodb', region_name='us-west-2')
    items = []
    last_key = None
    retry_count = 0
    
    while True:
        try:
            response = client.scan(
                TableName=table_name,
                Limit=max_items_per_page,
                ExclusiveStartKey=last_key,
                ConsistentRead=False
            )
            items.extend(response.get('Items', []))
            last_key = response.get('LastEvaluatedKey')
            retry_count = 0  # reset on success
            
            if not last_key:
                break
                
        except botocore.exceptions.ClientError as e:
            if e.response['Error']['Code'] == 'ThrottlingException':
                retry_count += 1
                if retry_count > max_retries:
                    raise
                wait_time = (2 ** retry_count) + random.uniform(0, 1)
                time.sleep(wait_time)
            else:
                raise
    
    return items

This approach trades latency for reliability: instead of one large batch request that times out, we make many small requests that succeed individually. The exponential backoff protects against DynamoDB throttling, and the hardcoded 50-item limit ensures each page completes well within the timeout window.

Infrastructure and Architectural Decisions

Why 50 items per page? Testing showed this was the largest safe page size that completed consistently under 1.5 seconds per page. With ~3,000 items in the crew table, this means ~60 pages and ~90 seconds total—comfortably within the 5-minute lambda execution timeout but leaving margin for retries.

Why not batch operations? The rebuild needs to correlate crew assignments with charter metadata from multiple tables. A single batch operation cannot atomically fetch related records; chunked scanning allows the rebuild logic to fetch and enrich one crew page at a time, making the pipeline resumable if a network fault occurs mid-process.

Snapshot refresh guard: A secondary concern: the rebuild process updates last-events.json, which drives SMS notifications to charter participants. If a stale snapshot were written, it could trigger false cancellation notifications. The solution: added a guard that verifies the charter status in DynamoDB before committing the snapshot, with up to 40 minutes of retry attempts. This ensures we never accidentally shadow a real cancellation with an outdated snapshot.

Operational Implications

This fix closes a critical gap in the charter operations pipeline. The rebuild now reliably publishes crew rosters within 5 minutes of a charter booking, enabling:

  • USCG manifest generation (legally required 24 hours before departure)
  • Crew assignment notifications and task dispatch
  • Public-facing crew roster updates on the charter confirmation page

The chunked scanning pattern is now a standard practice for all table scans in the JADA infrastructure. Any future service that needs to process large DynamoDB datasets should use this approach as a foundation.

Key Decisions

  • Chunking over batching: Prioritized reliability and debuggability over raw throughput.
  • Explicit path resolution: Removed dependency on shell environment configuration, making the script portable across execution contexts.
  • Snapshot verification: Prevented data loss by guarding writes with a consistency check against the source of truth.

What's Next

The crew page rebuild is now operational for the July 4 charter and future bookings. Broader audit findings (plaintext AWS credentials hardcoded in LaunchAgents, email blast infrastructure silent failures, unsubscribe watchdog broken for 30 days) are being addressed as part of the infrastructure hardening effort—separate from this immediate operational fix.