Analytics Worker¶

The analytics worker aggregates email delivery statistics and produces reports. It reads from mail_logs and email_tracking tables, crunches the numbers, and serves them via an API. The dashboard and API gateway pull data from here.

What It Does¶

• Aggregates delivery stats: sent, bounced, deferred, rejected counts
• Computes daily/weekly/monthly rollups
• Per-organization and per-domain breakdowns
• Geographic data (sender/recipient countries)
• Device and email client statistics (from tracking data)
• Top senders, top recipients, top domains
• Spam statistics and trends

How It Works¶

flowchart LR
    subgraph Data["Data Sources"]
        MailLogs["mail_logs table"]
        Tracking["email_tracking table"]
        Domains["domains table"]
        Orgs["organizations table"]
    end

    subgraph Analytics["Analytics Worker :8087"]
        Engine["Analytics Engine"]
        Cache["In-Memory Cache"]
        API["Flask API"]
    end

    MailLogs --> Engine
    Tracking --> Engine
    Domains --> Engine
    Orgs --> Engine

    Engine --> Cache
    Cache --> API

    API --> Dashboard["Dashboard"]
    API --> APIGateway["API Gateway"]

API Endpoints¶

GET /api/stats?days=30              -- Overall email stats for last N days
GET /api/stats/daily                -- Daily breakdown
GET /api/stats/domains              -- Top domains by volume
GET /api/stats/spam                 -- Spam detection rates
GET /api/stats/org/{org_id}         -- Per-organization stats
GET /api/stats/domain/{domain}      -- Per-domain stats
GET /api/stats/tracking             -- Open/click rates
GET /api/stats/geographic           -- Geographic breakdown
GET /health                         -- Health check
GET /metrics                        -- Prometheus metrics

Example Response¶

{
  "period": "2025-01-01 to 2025-01-31",
  "daily_stats": [
    {
      "date": "2025-01-15",
      "total_emails": 1250,
      "inbound": 800,
      "outbound": 450,
      "bounced": 12,
      "spam_detected": 95
    }
  ],
  "top_domains": [
    {"domain": "gmail.com", "count": 342},
    {"domain": "outlook.com", "count": 218}
  ]
}

Database Tables¶

The analytics worker reads from:

And writes aggregated data to:

Queries¶

The analytics engine runs SQL queries against MySQL. Here is a representative example for daily email counts:

SELECT
    DATE(timestamp) as date,
    COUNT(*) as total_emails,
    SUM(CASE WHEN direction = 'inbound' THEN 1 ELSE 0 END) as inbound,
    SUM(CASE WHEN direction = 'outbound' THEN 1 ELSE 0 END) as outbound
FROM mail_logs
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(timestamp)
ORDER BY date;

Configuration¶

Docker Configuration¶

analytics:
  build: ./worker/analytics
  container_name: analytics
  ports:
    - "8087:8087"
  depends_on:
    - mysql

Gotchas¶