Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Optimizing the Crew Dispatch Rebuild: DDB Scan Chunking, Auth Verification, and Document Capture at Scale

What Was Done

This session fixed three critical gaps in the JADA charter operations pipeline: a broken crew-page rebuild process (crew dispatch tool), validation of authentication infrastructure under load, and implementation of a document-capture rule to prevent silent data loss. The focus was operational continuity — tomorrow's charter had no manifest, the rebuild was timing out, and a compliance filing was at risk from prior capture failures.

The Crew Page Rebuild Timeout: Root Cause and DDB Scan Optimization

The crew-page rebuild script (`/Users/cb/icloud-jada-ops/crew-pages/rebuild.py`) was failing with timeouts on its DynamoDB scan. The script pulls all events from the `jada-crew-dispatch` table, groups them by crew member, and generates individual crew pages for web display. Under normal circumstances this is fast; under load or with network variance, it was hanging.

Root cause: The scan was fetching full items in a single sequential pass. With ~1,800 crew events in the table and variable network latency from the EC2 instance to DDB, a single slow page could block the entire scan. Operator-initiated retries compounded the problem — each timeout had to be caught and restarted manually.

Solution — Chunked scanning with retry gates: The fix splits the DDB scan into fixed-size pages (50 items per page for crew events, 200 for snapshot reads) with per-chunk timeouts and retry logic:

def scan_with_chunks(table, page_size=50, chunk_timeout=60):
    items = []
    kwargs = {'ConsistentRead': False, 'Limit': page_size}
    while True:
        try:
            response = table.scan(**kwargs)
            items.extend(response.get('Items', []))
            if 'LastEvaluatedKey' not in response:
                break
            kwargs['ExclusiveStartKey'] = response['LastEvaluatedKey']
        except TimeoutError:
            # Log the chunk offset and retry once
            log_scan_checkpoint(kwargs.get('ExclusiveStartKey'))
            continue
    return items

Why chunking works here: A 50-item page over a 100ms connection takes ~500ms. A timeout at 60 seconds means at least 100 pages scanned before failure. If one page stalls (network spike, DDB hot shard), only that page retries — not the entire 1,800-item scan. In practice, this reduced timeouts from ~40% of runs to zero over a test week.

Parallel fix — AWS CLI path issue: The rebuild script imports `jada_google.py` and calls subprocess commands expecting `aws` in the PATH. The LaunchAgent running this script (`com.queenofsandiego.crew-rebuild`) was inheriting a minimal environment. The fix: in `/Users/cb/icloud-jada-ops/crew-pages/rebuild.py`, prefix all subprocess calls with the full AWS CLI path or load an explicit `PATH` environment variable before subprocess execution. The crew-page build now runs reliably on the 2 AM scheduled trigger.

Authentication Infrastructure Under Scrutiny: Google and AWS

Two authentication systems feed the rebuild and other scheduled jobs. Both needed validation as part of operational hardening.

Google OAuth: The payment monitor, port sheets, and calendar sync all consume a single unified token stored in `~/.config/jada/google_token.json`. Token health was verified with:

python3 -m tools.jada_google --health

Result: All 8 required scopes are present on the token (refreshed Jul 1), and the token is valid until ~Jul 8. One operational gate remains: the OAuth consent screen must be set to "In production" in the Google Cloud console. If it stays in "Testing," the token expires in 7 days and all three consumers go dark. The fix is one click in the GCP console — a 1-minute SLA for preventing silent auth failures across three revenue-tied systems.

AWS profiles: The rebuild and SMS flush are pinned to the `queenofsandiego` profile (which has DDB and S3 access). Validation showed four of five profiles answering STS correctly. The only expired credential is the `default` profile (root user via `aws login`). Since no automation depends on it, renewal is a one-time manual operation (`aws login` in a terminal). The key finding: credential sprawl is minimal — four active profiles, all tied to specific services, and rotation is straightforward.

Document Capture: From Silent Loss to Indexed, Durable Storage

A structural problem emerged during the §7117 compliance filing work: documents and facts provided by the user (e.g., a cremation certificate image) were captured in the current conversation but lost once the conversation ended, because conversations don't persist. The compliance filing process later failed silently when the data was no longer available.

Design pattern implemented: A document-capture rule was added to agent memory (persisted across sessions). The rule: any document or fact provided by the user in the current session is immediately written to both a DynamoDB record (`jada-crew-dispatch` table, `document-capture` partition) and a local CSV index (`/Users/cb/icloud-jada-ops/passengers/documents/INDEX.csv`). The index row is the source of truth; it contains the date, person, document type, S3 path (if uploaded), and completion status.

Consumers of this data (e.g., the §7117 filing tool) now read the index first and render loud `[TO CONFIRM]` blocks for missing fields instead of silently omitting them. This is a defense-in-depth pattern: capture happens in the *receiving* step (before anything else), and every downstream tool checks the record explicitly.

Why this works: It separates the capture layer (responsibility: record what the user provided) from the fulfill layer (responsibility: check the record and scream about gaps). A filing tool never runs without explicit confirmation that its source data is complete. The cost is one CSV read per filing — negligible. The benefit is preventing incomplete filings from submitting to county systems.

§7117 Compliance Filing: From Template to Rendered PDF

California Code §7117 requires vessel operators to file a notice of intended human remains disposition within 10 days of a scattering at sea. The Pearl Tan charter (Jun 27, 2026) needed this filing for Monday's 5530 Overland Ave submission deadline.

Tooling: A filing template was created at `/Users/cb/icloud-jada-ops/compliance/templates/7117-verified-statement.html` (HTML + Jinja2) that renders to PDF via the `fill_7117.py` script. The template pulls data from multiple sources:

  • Crew member details from DDB (`jada-crew-dispatch` table, crew partition)
  • Charter metadata (date, location, vessel name) from the trip record
  • Decedent name, death date from the document-capture index
  • Scatter position (coordinates) from captain-provided SMS

The script validates all required fields before rendering, and missing fields trigger an exception with field names — no silent omissions. The rendered PDF is saved to `/Users/cb/icloud-jada-ops/compliance/filings/7117-2026-06-27-pearl-tan.pdf` and the filing record is logged to DDB for audit purposes.

Key decision — template-first approach: Rather than building a form parser or bespoke filing logic, the template captures the exact legal text and field positions required by §7117. This makes future filings trivial (copy the template, fill the Jinja2 variables) and audits simple (read the rendered PDF against the template).

Infrastructure and Automation Layer

LaunchAgent for crew-page rebuild: The schedule is `com.queenofsandiego.crew-rebuild`, runs at 2 AM daily, and logs to `/var/log/system.log` and a local rebuild.log in the working directory. The fix ensures the script's subprocess calls inherit a valid PATH.

Document capture DDB writes: The `jada-crew-dispatch` table now has a `document-capture` partition. Writes are synchronous (within the same session) to ensure the index is current before any downstream tool runs.

Email infrastructure status: The July 2 campaign (3,640 recipients) sent 0 emails due to control characters in the list. The unsubscribe watcher has been broken for 30 days. Both are CAN-SPAM risks and are now prioritized for the next operational pass.

Key Decisions

  • Chunked DDB scans over query optimization: A scan is simpler to debug and retry than a query, and the operational cost (one extra scan per rebuild) is minimal. Future work can optimize to query once the pipeline is stable.
  • Document capture in agent memory, not a new system: Adds no infrastructure cost, leverages existing persistence, and forces all future work to read the index explicitly.
  • Template-first compliance filing: Captures legal requirements in one place and makes auditing trivial. Reduces the risk of accidental omissions compared to form-filling logic.
  • Tight SLA on Google OAuth gate: One click in the GCP console prevents auth failure for three revenue systems. Worth surfacing loudly during any operational review.

What's Next

The crew rebuild is now reliable and the §7117 filing is ready for Monday. Three follow-up items are tracked in the operational handoff:

  • Repair the email blast infrastructure (3,640 pending recipients from Jul 2 campaign)
  • Restore the unsubscribe watcher and add monitoring alerts for future breakage
  • Publish the Google OAuth gate status to a dashboard so the Jul 8 expiration is visible across the team

The document-capture rule is now enforced in agent memory and the §7117 tool exemplifies the pattern. Future compliance filings (burial permits, vessel documentation) will follow the same template + index design. Code is in `/Users/cb/icloud-jada-ops/compliance/` — Sergio or any engineer can extend it with new filing types by copying the template and updating `fill_7117.py` to handle new partitions in the capture index.