Analytics Dashboard API: 1M Events/Day, <5s Latency

System Architecture

Lambda-lite architecture: real-time stream processing (Kafka → Spring Boot consumers → Redis) + batch correction (nightly Spark jobs → MySQL). Dashboard queries hit pre-aggregated Redis for sub-100ms response; ad-hoc analysis queries MySQL.

Data flow

1. Producers (mobile apps, web, IoT) → HTTP → Kafka raw-events topic
2. Stream Consumers (Spring Boot) → validate, enrich, route to typed topics (pageviews, clicks, purchases)
3. Aggregation Workers → tumbling/hopping windows → Redis (pre-aggregated metrics) + MySQL (fact tables)
4. Dashboard API → Redis for real-time widgets, MySQL for historical/deep-dive
5. Batch Correction (nightly) → Spark → recompute from raw → fix late-arriving data in MySQL

Event Ingestion Pipeline

Kafka as the backbone: decouples producers from consumers, provides replayability, handles backpressure. Spring Boot consumers with exactly-once semantics via transactional producers.

Event schema (Avro, Schema Registry)

{
  "type": "record",
  "name": "AnalyticsEvent",
  "namespace": "com.amitk.analytics",
  "fields": [
    {"name": "eventId", "type": "string"},           // UUID v4
    {"name": "eventType", "type": {"type": "enum", "name": "EventType", "symbols": ["PAGEVIEW", "CLICK", "PURCHASE", "SIGNUP"]}},
    {"name": "timestamp", "type": {"type": "long", "logicalType": "timestamp-millis"}},
    {"name": "userId", "type": ["null", "string"]},
    {"name": "sessionId", "type": "string"},
    {"name": "properties", "type": {"type": "map", "values": "string"}},  // Flexible key-value
    {"name": "context", "type": {                    // Auto-captured
      "type": "record", "name": "EventContext",
      "fields": [
        {"name": "ip", "type": "string"},
        {"name": "userAgent", "type": "string"},
        {"name": "referrer", "type": ["null", "string"]},
        {"name": "geo", "type": ["null", {"type": "record", "name": "Geo", "fields": [
          {"name": "country", "type": "string"},
          {"name": "region", "type": "string"},
          {"name": "city", "type": "string"}
        ]}]}
      ]
    }}
  ]
}

Consumer config for exactly-once

@Configuration
public class KafkaConsumerConfig {
    @Bean
    public KafkaConsumer consumer() {
        Map props = new HashMap<>();
        props.put(ConsumerConfig.BOOTSTRAP_SERVERS_CONFIG, bootstrapServers);
        props.put(ConsumerConfig.GROUP_ID_CONFIG, "analytics-ingestion");
        props.put(ConsumerConfig.ISOLATION_LEVEL_CONFIG, "read_committed"); // Exactly-once
        props.put(ConsumerConfig.ENABLE_AUTO_COMMIT_CONFIG, false);
        props.put(ConsumerConfig.MAX_POLL_RECORDS_CONFIG, 500);
        return new KafkaConsumer<>(props);
    }
}

Dimensional Modeling (Kimball)

Star schema in MySQL: fact tables for events, dimension tables for users, sessions, pages, products, geography. Surrogate keys, slowly changing dimensions (SCD Type 2 for user attributes), conformed dimensions across fact tables.

Schema overview

-- Fact table (partitioned by day)
CREATE TABLE fact_events (
    event_id        BIGINT UNSIGNED NOT NULL,
    event_date      DATE NOT NULL,
    event_hour      TINYINT UNSIGNED NOT NULL,
    event_type_id   SMALLINT UNSIGNED NOT NULL,
    user_id         BIGINT UNSIGNED,
    session_id      BIGINT UNSIGNED NOT NULL,
    page_id         BIGINT UNSIGNED,
    product_id      BIGINT UNSIGNED,
    geo_id          INT UNSIGNED,
    device_type_id  TINYINT UNSIGNED,
    revenue_usd     DECIMAL(10,2) DEFAULT 0,
    -- Measures
    event_count     BIGINT UNSIGNED DEFAULT 1,
    PRIMARY KEY (event_date, event_id)
) PARTITION BY RANGE COLUMNS(event_date) (
    PARTITION p2026_01 VALUES LESS THAN ('2026-02-01'),
    PARTITION p2026_02 VALUES LESS THAN ('2026-03-01'),
    -- ... monthly partitions
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Dimension: User (SCD Type 2)
CREATE TABLE dim_user (
    user_sk         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         VARCHAR(64) NOT NULL,          -- Natural key
    email           VARCHAR(255),
    signup_date     DATE,
    plan_tier       ENUM('free', 'pro', 'enterprise'),
    -- SCD fields
    valid_from      DATETIME NOT NULL,
    valid_to        DATETIME,
    is_current      BOOLEAN NOT NULL DEFAULT TRUE
);

Conformed dimensions

dim_date, dim_geo, dim_device, dim_page, dim_product shared across all fact tables. Enables drill-across (e.g., revenue by geo + device).

Pre-aggregation in Redis

Real-time dashboards need sub-100ms queries. Pre-compute metrics in Redis with TTL-based expiry. Spring Boot aggregation workers consume from typed Kafka topics, compute windowed aggregates, write to Redis hashes/sorted sets.

Metrics computed

• Counters: Pageviews, unique visitors, sessions, conversions (per page/product/geo/device)
• Gauges: Active users (5-min window), current cart value, queue depths
• Histograms: Latency percentiles (p50, p95, p99) via HDR histogram approximation
• Funnels: Step conversion rates (view → click → purchase) per segment

Redis data structures

# Counter: pageviews per page per minute
HINCRBY "metrics:pageviews:2026-08-25:14:30" "page:/dashboard" 1
EXPIRE "metrics:pageviews:2026-08-25:14:30" 86400  # 24hr TTL

# Sorted set: top pages by views (last hour)
ZINCRBY "top:pages:1h" 1 "page:/dashboard"
ZREMRANGEBYRANK "top:pages:1h" 0 -101  # Keep top 100

# HyperLogLog: unique visitors per day
PFADD "uv:2026-08-25" "user_123" "user_456"
PFCOUNT "uv:2026-08-25"  # ~100K users, <1% error

Query API reads from Redis

Dashboard widgets fetch pre-computed metrics directly:

@GetMapping("/api/v1/metrics/realtime")
public Map realtimeMetrics(@RequestParam String widget) {
    return switch (widget) {
        case "active_users" -> redisOps.opsForValue().get("gauge:active_users:5m");
        case "top_pages" -> redisOps.opsForZSet().reverseRangeWithScores("top:pages:1h", 0, 9);
        case "conversion_funnel" -> redisOps.opsForHash().entries("funnel:purchase:1h");
        default -> Map.of("error", "Unknown widget");
    };
}

Dashboard Query API

REST API serving two query paths: Redis (real-time, pre-aggregated) and MySQL (historical, ad-hoc). Unified response format; client unaware of source.

Endpoints

• GET /api/v1/metrics/realtime?widget=active_users → Redis (sub-50ms)
• GET /api/v1/metrics/historical?metric=pageviews&granularity=hour&from=2026-08-01&to=2026-08-25 → MySQL (partition pruning)
• POST /api/v1/reports → Async report generation (CSV/PDF) via Celery-equivalent (Spring Batch)
• GET /api/v1/dimensions/{dimension}/values → MySQL dimension tables (cached 1hr)

Report builder

Users configure reports via UI: select metrics, dimensions, filters, date range. API validates against metadata registry, generates SQL with parameter binding, executes via read replica.

Scaling Metrics

Production load: 1.2M events/day peak, 15K events/sec burst, <5s end-to-end (producer → dashboard). Cost: ~$350/month (3x Kafka brokers, 4x Spring Boot, MySQL db.r6g.xlarge, ElastiCache r6g.xlarge).

Key Architecture Decisions