System Architecture & Data Design

For step-by-step implementation: See 03-implementation-guide.md

Architecture Overview

The analytics dashboard implements a four-tier architecture for efficient data collection, aggregation, and visualization:

TIER 1: DATA SOURCE
├─ MySQL Production Database
├─ Airbyte Continuous Sync
└─ Real-time data capture

    ↓

TIER 2: ANALYTICS ENGINE
├─ ClickHouse (Single Source of Truth)
├─ Materialized Views (Auto-aggregation)
└─ Optimized for analytical queries

    ↓

TIER 3: APPLICATION LAYER
├─ Rails Service Layer
├─ Caching (Redis)
└─ RESTful API endpoints

    ↓

TIER 4: PRESENTATION LAYER
├─ React Frontend
├─ Interactive Charts
└─ Dashboard UI

Tier 1: Data Source (MySQL)

Source Tables

booking_production database:
├─ reservations: Booking records with pricing
├─ restaurants: Restaurant information
├─ inventory: Capacity and seating data
└─ customers: Customer profiles

Data Capture Method

Airbyte Integration

• Continuous synchronization from MySQL to ClickHouse
• Real-time updates (typically < 2 seconds latency)
• Change Data Capture (CDC) mode for efficient syncing
• No performance impact on production MySQL

Tier 2: Analytics Engine (ClickHouse)

Database Schema

Primary Databases:

booking_production (Replica)
└─ MySQL replication via Airbyte
   ├─ reservations
   ├─ restaurants
   ├─ inventory
   └─ customers

syn (Custom Analytics Schema)
└─ Optimized tables and views for dashboard
   ├─ analytics_revenue
   ├─ analytics_bookings_covers
   └─ analytics_capacity

Tables

1. analytics_revenue

Purpose: Daily revenue aggregations by restaurant

CREATE TABLE syn.analytics_revenue (
    restaurant_id UInt32,
    date Date,
    hour String,
    revenue Float64,
    _version UInt64
) ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(date)
PRIMARY KEY (restaurant_id, date, hour)
ORDER BY (restaurant_id, date, hour)

Fields:

• restaurant_id: Restaurant identifier
• date: Transaction date
• hour: Hour of transaction (format: “09:00”, “14:00”)
• revenue: Total revenue amount in Thai Baht
• _version: ReplacingMergeTree version counter

Optimization:

• Partitioned by month for efficient date range queries
• Primary key optimizes filtering by restaurant and date
• ReplacingMergeTree deduplicates updates

2. analytics_bookings_covers

Purpose: Booking count and cover count aggregations

CREATE TABLE syn.analytics_bookings_covers (
    restaurant_id UInt32,
    date Date,
    hour String,
    bookings_count Int32,
    confirmed_covers Int32,
    avg_party_size Float64,
    _version UInt64
) ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(date)
PRIMARY KEY (restaurant_id, date, hour)
ORDER BY (restaurant_id, date, hour)

Fields:

• bookings_count: Number of reservations
• confirmed_covers: Total number of confirmed guests
• avg_party_size: Average party size (confirmed_covers / bookings_count)

3. analytics_capacity

Purpose: Seat utilization and capacity tracking

CREATE TABLE syn.analytics_capacity (
    restaurant_id UInt32,
    date Date,
    hour String,
    reserved_covers Int32,
    utilization_percent Float64,
    is_peak_hour UInt8,
    _version UInt64
) ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(date)
PRIMARY KEY (restaurant_id, date, hour)
ORDER BY (restaurant_id, date, hour)

Fields:

• reserved_covers: Booked seats for the hour
• utilization_percent: Percentage of total capacity booked
• is_peak_hour: 1 if peak dining hours (12:00-14:00, 19:00-21:00), 0 otherwise

Tier 3: Application Layer (Rails)

Service Architecture

BaseAnalyticsService (Base Class)

module AnalyticsService
  class BaseAnalyticsService
    # Core responsibilities
    ├─ Date range calculation (today, last_7_days, etc.)
    ├─ Template generation (hour/date/month formats)
    ├─ Cache management
    ├─ ClickHouse connectivity & error handling
    └─ Response formatting
  end
end

Key Methods:

• calculate_date_range() - Determines start/end dates based on filter
• generate_hour_template() - Returns array of 24 hours
• generate_date_template() - Returns array of dates for range
• generate_month_template() - Returns array of months
• execute_with_retry() - Retriable execution with exponential backoff
• generate_cache_key() - Deterministic cache key generation

RevenueChartService

class RevenueChartService < BaseAnalyticsService
  def call
    # Returns revenue chart data
    # Response: {labels, current_period, previous_period, raw_data, metadata}
  end
end

Response Structure:

{
  "labels": ["Nov 2025", "Dec 2025", "Jan 2026"],
  "current_period": [10000, 11000, 12000],
  "previous_period": [9000, 10000, 11000],
  "raw_data": [
    {
      "date": "Nov 2025",
      "current_period": { "label": "Last 3 months", "revenue": 10000 },
      "previous_period": { "label": "Previous 3 months", "revenue": 9000 }
    }
  ],
  "metadata": {
    "start_date": "2025-10-01",
    "end_date": "2026-01-06",
    "date_filter": "last_3_month",
    "restaurant_ids": [1597],
    "current_period_label": "Last 3 months",
    "previous_period_label": "Previous 3 months"
  }
}

BookingsChartService

class BookingsChartService < BaseAnalyticsService
  def call
    # Returns bookings and covers dual-metric data
    # Response: {labels, bookings, covers, raw_data, metadata}
  end
end

CapacityChartService

class CapacityChartService < BaseAnalyticsService
  def call
    # Returns capacity utilization data
    # Response: {labels, capacity_utilization, raw_data, metadata}
  end
end

API Endpoints

Chart Data Endpoint

GET /api/partner/v1/analytics/chart_data
Query Parameters:
  - chart_type: 'revenue' | 'bookings' | 'capacity'
  - date_filter: 'today' | 'last_7_day' | 'last_30_day' | 'this_month' | 'last_month' | 'last_3_month'

Response: 200 OK with chart data

Caching Strategy

Implementation:

cache_key = "analytics:#{service_class}:#{date_filter}:#{restaurant_ids}"
cached_data = Rails.cache.read(cache_key)

if cached_data.present?
  return cached_data  # 10ms response
else
  data = fetch_from_clickhouse()
  Rails.cache.write(cache_key, data, expires_in: 1.hour)
  return data  # 200-300ms response
end

TTL: 1 hour - Optimal balance between freshness and cache hit rate

Estimated Hit Rate: 99% - Most users query same filters repeatedly

Tier 4: Presentation Layer (React)

Component Structure

Main Dashboard Components:

Dashboard
├─ FilterBar
│  ├─ DateFilterButtons (Today, Last 7 Days, etc.)
│  └─ ChartTypeSelector
├─ RevenueChart
│  ├─ LineChart (Recharts)
│  ├─ Tooltip on hover
│  └─ Legend
├─ BookingsChart
│  ├─ ComposedChart (dual-axis)
│  ├─ Bookings line (left Y-axis)
│  └─ Covers line (right Y-axis)
├─ CapacityChart
│  ├─ AreaChart with fill
│  ├─ Reference line (target utilization)
│  └─ Color coding (green/yellow/red)
└─ SummaryCards
   ├─ Total Revenue + growth%
   ├─ Total Bookings + growth%
   ├─ Average Revenue/Day
   └─ Capacity Utilization %

Data Flow

User selects filter (e.g., "Last 7 Days")
    ↓
Frontend makes API call:
GET /api/partner/v1/analytics/chart_data?chart_type=revenue&date_filter=last_7_day
    ↓
Rails checks cache (HIT or MISS)
    ├─ HIT: Return cached data (10ms)
    └─ MISS: Query ClickHouse (200-300ms)
    ↓
React receives JSON response
    ↓
Chart library renders with data
    ├─ X-axis: labels
    ├─ Y-axis: current_period values
    └─ Series 2: previous_period values
    ↓
Dashboard displays to user

Date Filter Definitions

Standard Filters

Example: Last 7 Days (Dec 25, 2025)

Query Ranges:

Current Period:  Dec 19-25, 2025 (7 days)
Previous Period: Dec 12-18, 2025 (7 days prior)

ClickHouse Query:

-- Current period
SELECT date, revenue FROM syn.analytics_revenue
WHERE restaurant_id = 1597
  AND date >= '2025-12-19'
  AND date <= '2025-12-25'

-- Previous period
SELECT date, revenue FROM syn.analytics_revenue
WHERE restaurant_id = 1597
  AND date >= '2025-12-12'
  AND date < '2025-12-19'

Performance Characteristics

Query Performance

Query Type              | Time      | Source
─────────────────────────────────────────────
Cache hit              | 10ms      | Redis
ClickHouse query       | 150ms     | ClickHouse
Rails processing       | 50ms      | Rails service
Total response time    | 200-300ms | Complete

Concurrent Load (100 requests, same filter):
├─ 99 from cache: 10ms each
├─ 1 from ClickHouse: 200ms
└─ User experience: Near-instant

Scalability

Vertical Scaling (Single ClickHouse Instance)

Server specs: 16GB RAM, 8 CPU cores
Handles: ~10,000 restaurants
Concurrent queries: 10-20
Maximum throughput: ~1000 requests/minute

Horizontal Scaling (ClickHouse Cluster)

When to implement:
├─ > 50,000 restaurants
├─ Concurrent queries > 20
└─ Query time > 1 second

Solution:
├─ ClickHouse Cluster with sharding
├─ Multiple Rails instances with load balancer
└─ Shared Redis for cache coordination

Data Integrity & Monitoring

Audit Trail

Version Tracking:

• ReplacingMergeTree _version column tracks updates
• Old versions automatically replaced by newer ones
• Ensures data consistency during updates

Monitoring Points

1. ClickHouse Health

   • Query latency
   • Connection errors
   • Memory usage

2. Cache Performance

   • Hit rate (target: > 95%)
   • Memory consumption
   • Eviction rate

3. API Performance

   • Response time (target: < 500ms)
   • Error rate (target: < 0.1%)
   • Request count by filter type

Error Handling

Scenario: ClickHouse Unavailable

IF ClickHouse down:
  ├─ Check if cache has data: YES → Return cached (may be stale)
  ├─ Cache expired: Return 503 Service Unavailable
  ├─ Alert operations team
  └─ Automatic recovery on ClickHouse restart

Scenario: Invalid Filter Parameter

IF date_filter not in ALLOWED_DATE_FILTERS:
  ├─ Return 400 Bad Request
  ├─ Include list of valid filters in response
  └─ Log invalid parameter attempt

Scenario: User Authorization Failure

IF user cannot access restaurant:
  ├─ Return 403 Forbidden
  ├─ Log unauthorized access attempt
  └─ Alert security team if pattern detected

Next Steps

1. Review 02-database-schema.md - Detailed ClickHouse schema
2. Follow 03-implementation-guide.md - Execute setup
3. Test endpoints - Verify API functionality
4. Monitor performance - Track query times and cache hit rates