Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automated Executive Reporting Infrastructure: Multi-Perspective Portfolio Analysis via Python SES Integration

Over a development session focused on organizational transparency and strategic decision-making, we built and deployed an automated reporting system that generates eight specialized executive reports across four portfolio entities. This post details the technical architecture, design decisions, and infrastructure patterns that enable daily stakeholder visibility without manual compilation overhead.

What We Built

The core deliverable is a Python-based report generation and distribution pipeline that:

  • Generates eight specialized reports from distinct stakeholder perspectives (CEO, CTO, CFO, CMO, Accounting, Yacht Delivery Operations, Real Estate Rental, Client Portfolio Audit)
  • Pulls data from disparate sources (Google Sheets via Apps Script, local project handoff files, S3 asset inventories, infrastructure cost analysis)
  • Formats multi-section reports with prioritized action items, KPI matrices, and financial models
  • Distributes via Amazon SES with BCC tracking for audit compliance
  • Creates downstream dashboard tasks for executive follow-up

Technical Architecture

File Organization

The implementation spans two primary scripts in /Users/cb/Documents/repos/tools/:

  • send_exec_reports.py — Main orchestration script handling report generation and SES distribution
  • send_exec_reports_2.py — Secondary script managing additional domain-specific reports and dashboard integration

This split design allows independent scheduling: primary reports run daily at 6 AM, secondary reports run on a separate cadence for less time-sensitive domains.

Data Source Integration

Rather than building a centralized database (which doesn't exist), the system aggregates data from:

  • Environment Configuration: repos.env contains SES variable names, verified sender addresses, and recipient lists. We explicitly validate SES_REGION, SES_FROM_ADDRESS, and recipient domain ownership before sending.
  • Project Handoffs: Markdown files in the portfolio repo containing asset inventories, revenue models, and risk matrices. The parser extracts structured data using regex patterns for currency values, entity names, and status indicators.
  • Google Apps Script Endpoints: Unauthenticated GAS endpoints (noted as a security gap in the CTO report) that serve real-time data for Expert Yacht Delivery operations and availability calendars.
  • S3 Manifests: Inventory lists stored as JSON in S3 buckets for JADA's asset database and QOS photo galleries.
  • Cost Spreadsheets: AWS billing exports stored locally and parsed for infrastructure spend analysis (~$50–84/month baseline with $25/month savings identified).

Each data source has a dedicated retrieval function with fallback handling to prevent report generation failure if a single source is unavailable.

Report Generation Strategy

We use a template-based approach where each report persona (CEO, CTO, etc.) has a structured format:


# CEO Report Structure
- Executive Summary (1 page)
- Asset Inventory by Entity (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur)
- Critical Shortfalls (8 identified: empty pipeline, zero OTA listings, no billing models, etc.)
- Missing KPIs (9 essential metrics not currently tracked)
- 30-Day Prioritized Agenda (with owner assignment and success criteria)

# CTO Report Structure
- Stack Audit by Entity (tech choices, hosting, third-party dependencies)
- Security Gap Analysis (6 hardened areas identified: hardcoded credentials, plaintext configs, etc.)
- Cost Optimization (AWS breakdown, monthly targets, identified savings)
- UX Shortfalls (analytics gaps, outdated copy, missing features)
- Development Cycle Assessment (no CI/CD, no staging, no rollback procedures)
- 10 Prioritized Engineering Actions (with effort estimate and impact)

This structure ensures consistency across reports while allowing persona-specific analysis depth. The CEO report focuses on business impact; the CTO report focuses on technical debt and user value; the CFO report focuses on cash flow and break-even analysis.

SES Integration and Delivery

Amazon SES is our transport layer because it's:

  • Integrated with AWS account infrastructure
  • Cost-effective at scale (verified sender addresses only)
  • Provides delivery tracking via bounce/complaint SNS topics (not yet configured but planned)
  • Allows BCC for audit trails without exposing all recipients to each other

Configuration loads from repos.env:


# .env variables referenced in send_exec_reports.py
SES_REGION=us-west-2
SES_FROM_ADDRESS=admin@queenofsandiego.com  # Must be verified in SES console
REPORT_RECIPIENT_PRIMARY=c.b.ladd@gmail.com
REPORT_RECIPIENT_BCC=admin@queenofsandiego.com

The script validates sender verification before attempting dispatch. If admin@queenofsandiego.com is not verified in the SES sandbox, delivery fails loudly with a clear error message rather than silently bouncing.

Recipient lists are hardcoded after validation rather than dynamically pulled, reducing the surface area for configuration drift.

Key Design Decisions

Why Two Scripts Instead of One

The primary script (send_exec_reports.py) handles the five core stakeholder reports that require daily visibility: CEO, CTO, CFO, CMO, Accounting. Secondary script handles three additional domain-specific reports (Real Estate, Yacht Delivery, Client Portfolio Audit) that are updated weekly or on-demand. This separation allows:

  • Independent scheduling without coupling unrelated work
  • Easier debugging—failure in secondary reports doesn't block executive reports
  • Clear ownership: primary reports require Sergio/CB approval; secondary can be modified by team members with less risk

Why Data Aggregation Rather Than Centralized DB

No accounting system, no unified CRM, and no single source of truth currently exist. Rather than delay reporting until a database is built, the pipeline ingests from multiple sources with explicit data reconciliation steps. This approach:

  • Unblocks executive visibility immediately
  • Documents data quality gaps (which become engineering priorities)
  • Makes it trivial to migrate to a proper system later—we're already parsing disparate sources

Why Prioritized Action Lists Over Raw Data

Each report closes with a 30/60/90-day action roadmap or a ranked priority list. This forces the report generator to make opinionated recommendations rather than presenting undifferentiated data. It ensures reports drive decisions rather than just document status.

What's Next

  • SNS Integration: Hook SES bounce/complaint topics to CloudWatch for real-time delivery monitoring
  • Scheduled Lambda: Migrate from local cron to AWS Lambda + EventBridge for reliable daily execution without depending on a local machine
  • Dashboard Task Creation: Parse report action items and automatically create tasks in the progress dashboard (partially implemented via send_exec_reports_2.py)
  • Data Warehouse: Once accounting system is live, pipe reconciled financial data directly into