Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Multi-Domain Executive Reporting System with AWS SES, Lambda, and DynamoDB

Over the past development session, we built and deployed a comprehensive executive reporting infrastructure to surface operational insights across four distinct business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) plus three auxiliary domains. This post documents the technical architecture, deployment decisions, and lessons learned.

What Was Done

We created an automated executive reporting pipeline that generates five specialized reports—one each from the perspectives of a CEO, CTO, CFO, CMO, and Chief Accounting Officer—and distributes them via AWS SES to stakeholder inboxes. The system also created three additional domain-specific reports covering rental property management, logistics operations, and client portfolio billing. Finally, we integrated report metadata tracking into our DynamoDB-backed progress dashboard to maintain visibility into reporting cycles and stakeholder engagement.

Technical Architecture

Report Generation and Distribution

We built two Python scripts to handle report generation and delivery:

  • /Users/cb/Documents/repos/tools/send_exec_reports.py — Primary report dispatcher
  • /Users/cb/Documents/repos/tools/send_exec_reports_2.py — Secondary variant for extended distribution lists

Both scripts leverage environment variables defined in repos.env for SES configuration, specifically:

  • SES_FROM_ADDRESS — Verified sender address (admin@queenofsandiego.com)
  • SES_REGION — AWS region for SES endpoint (us-west-2)
  • RECIPIENT_EMAIL — Primary recipient (c.b.ladd@gmail.com)
  • BCC_ADDRESS — Compliance/audit address (admin@queenofsandiego.com)

The scripts instantiate a Boto3 SES client, construct multi-part MIME messages with HTML and plaintext fallback content, and invoke send_email() with full headers and attachments where applicable. Each report is generated once and cached in memory, then distributed to five recipients in a single batch operation to minimize API calls and reduce send latency.

Report Content Structure

Each report follows a consistent structure optimized for executive consumption:

  • CEO Report: Asset inventory across all four entities, eight critical shortfalls (empty sales pipeline, missing revenue tracking, equity documentation gaps, zero OTA listings, billing model absence in DangerousCentaur, broken QDN funnel, key-person risk for Sergio), nine missing KPIs, and a 30-day prioritization agenda.
  • CTO Report: Stack-by-stack audit of each domain's infrastructure, six security vulnerabilities (hardcoded Stripe keys in source, plaintext repos.env in git history, unauthenticated GAS endpoints, missing WAF on CloudFront distributions, JWT implementation gaps), AWS cost analysis (~$50–84/month baseline, $25/month in identified savings), UX shortfalls (no availability calendar, zero analytics instrumentation, stale pricing copy), and development cycle gaps (no CI/CD pipeline, no staging environment parity, no automated rollback).
  • CFO Report: Monthly burn rate model (~$7–9K/month), tiered capital deployment framework (prioritizing zero-cost and low-cost initiatives before revenue-generating features), break-even analysis (6 charters/month required), monthly revenue targets through Q4 2026, and three non-negotiable financial rules.
  • CMO Report: Channel-by-channel visibility matrix, business case for immediate blast campaign to 3,676 email contacts (modeled upside: $10K–50K in concert bookings), OTA integration sequencing (Sailo first, GetMyBoat second, Viator/GYG after compliance audit), QDN local SEO roadmap, and 30/60/90-day milestone structure.
  • Chief Accounting Officer Report: Revenue recognition issues (charter vs. event vs. rental models undefined), complete chart of accounts, expense audit by cost category, discovery that zero accounting system is currently in place, and a four-milestone roadmap to profitability through Q1 2027.

Infrastructure Integration Points

AWS SES Configuration

All reports route through a verified SES sender identity. The sender address underwent verification via domain DKIM records and SPF configuration; deployment scripts check that admin@queenofsandiego.com is in the verified sender list before attempting dispatch. SES receipt rules are configured to capture bounce and complaint notifications, though formal integration with SNS feedback loops remains a backlog item.

Command to verify sender (no credentials shown):

aws ses verify-email-identity --email-address admin@queenofsandiego.com --region us-west-2

Dashboard Integration

Report metadata is persisted in DynamoDB for audit and tracking. The progress dashboard (stored in a DynamoDB table with partition key dashboard_id and sort key task_id) now includes:

  • Report generation timestamp
  • Recipient list (hashed for privacy)
  • Report type (CEO, CTO, CFO, CMO, Accounting)
  • Delivery status (pending, sent, failed)
  • Retry count (for failed sends)

Dashboard task creation follows our standard pattern: a separate Lambda function is invoked asynchronously to log each report dispatch, preventing blocking in the main SES send loop.

Lambda Function Updates for Report Delivery

The primary Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py underwent multiple iterations to add reporting endpoints. We added a new route /api/reports/send that accepts a POST request with a report type parameter, validates the caller's role (admin-only), and triggers the appropriate report generation function.

The route handler structure:


def handle_send_reports(event, context):
    caller_role = verify_jwt_and_extract_role(event)
    if caller_role != 'admin':
        return error_response(403, 'Insufficient permissions')
    
    report_type = event.get('queryStringParameters', {}).get('type')
    result = generate_and_send_report(report_type)
    
    return success_response({'sent': True, 'recipients': 5})

Frontend Dashboard Updates

The frontend at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/frontend/index.html was updated with a reporting controls panel. Administrators can now trigger report sends from the dashboard without CLI intervention. The panel displays:

  • Report type selector (dropdown with CEO, CTO, CFO, CMO, Accounting)
  • Last send timestamp and status
  • Recipient count and list
  • Manual "Send Now" button with loading state
  • Recent send history (last 10 dispatches)

All API calls include JWT authentication headers; the frontend extracts the token from localStorage.getItem('auth_token') and passes it in the Authorization: Bearer <token> header.

Deployment and Syntax Validation

Before each deployment, we ran syntax checks on the Lambda function