Copy page

Platform & Messaging

SMS Analytics Architecture

SMS Analytics Architecture

The Tech231 Platform uses TimescaleDB for high-performance, time-series SMS analytics. This document describes the architecture, data model, and query patterns.

Overview

SMS analytics provides tenants with detailed metrics about their messaging activity:

• Daily aggregates: Message counts, delivery rates, latency metrics per day
• Period aggregates: Weekly, monthly, quarterly, yearly rollups via continuous aggregates
• Platform health: Cross-tenant health monitoring for operations

Data Model

sms_analytics_daily (Hypertable)

The base table for all SMS analytics. Uses TimescaleDB hypertables with:

• Time partitioning: By date_utc for efficient time-range queries
• Space partitioning: By tenant_id hash for multi-tenant isolation

Column	Type	Description
date_utc	DATE	UTC date of the analytics period
tenant_id	TEXT	Tenant identifier
direction	TEXT	'outbound' or 'inbound'
attempted_count	BIGINT	Messages attempted
processed_count	BIGINT	Messages processed
delivered_count	BIGINT	Messages delivered
failed_count	BIGINT	Messages failed
unique_recipient_count	BIGINT	Unique recipients
avg_send_to_final_status_seconds	DOUBLE	Average latency
delivery_rate_percent	DOUBLE	Delivery rate (computed)
failure_rate_percent	DOUBLE	Failure rate (computed)

Continuous Aggregates

Higher-level rollups are automatically maintained by TimescaleDB:

• sms_analytics_weekly: 7-day time buckets
• sms_analytics_monthly: Calendar month buckets
• sms_analytics_quarterly: 3-month buckets
• sms_analytics_yearly: Calendar year buckets

Data Ingestion

Fire-and-Forget Pattern

Analytics writes use Orleans [OneWay] grain calls for non-blocking ingestion:

Code
// In SmsGrain - fire-and-forget analytics recording
await RecordMessageForAnalyticsAsync(AnalyticsEventType.Attempted);

private async Task RecordMessageForAnalyticsAsync(AnalyticsEventType eventType)
{
    var writerGrain = GrainFactory.GetGrain<ISmsAnalyticsWriterGrain>(0);
    writerGrain.RecordEvent(new SmsAnalyticsEvent
    {
        TenantId = State.TenantId,
        Direction = "outbound",
        EventType = eventType,
        Timestamp = DateTime.UtcNow
    }).Ignore(); // Fire-and-forget
}

SmsAnalyticsWriterGrain

Stateless worker grain that batches analytics events:

Code
[StatelessWorker]
public class SmsAnalyticsWriterGrain : Grain, ISmsAnalyticsWriterGrain
{
    private readonly ISmsAnalyticsRepository _repository;
    
    [OneWay]
    public async Task RecordEvent(SmsAnalyticsEvent analyticsEvent)
    {
        await _repository.IncrementDailyAnalyticsAsync(
            analyticsEvent.TenantId,
            DateOnly.FromDateTime(analyticsEvent.Timestamp),
            analyticsEvent.Direction,
            attemptedDelta: analyticsEvent.EventType == AnalyticsEventType.Attempted ? 1 : 0,
            processedDelta: analyticsEvent.EventType == AnalyticsEventType.Processed ? 1 : 0,
            deliveredDelta: analyticsEvent.EventType == AnalyticsEventType.Delivered ? 1 : 0,
            failedDelta: analyticsEvent.EventType == AnalyticsEventType.Failed ? 1 : 0);
    }
}

Retention and Compression

TimescaleDB policies optimize storage:

Policy	Setting	Purpose
Retention	12 months	Automatically drop old daily data
Compression	7 days	Compress chunks older than 7 days
Segment by	tenant_id, direction	Optimize compression for tenant queries
Order by	date_utc DESC	Optimize for recent-first queries

Query Patterns

Tenant Daily Analytics

Code
SELECT date_utc, attempted_count, delivered_count, failed_count,
       delivery_rate_percent, avg_send_to_final_status_seconds
FROM sms_analytics_daily
WHERE tenant_id = $1
  AND date_utc >= $2 AND date_utc <= $3
  AND direction = 'outbound'
ORDER BY date_utc DESC;

Period Analytics (via Continuous Aggregates)

Code
SELECT week_start, attempted_count, delivered_count, failed_count,
       unique_recipient_count, avg_latency_seconds
FROM sms_analytics_weekly
WHERE tenant_id = $1
  AND week_start >= $2 AND week_start <= $3
  AND direction = 'outbound'
ORDER BY week_start DESC;

Platform Health Summary

Code
SELECT COUNT(DISTINCT tenant_id) AS active_tenant_count,
       SUM(attempted_count) AS total_attempted,
       SUM(delivered_count) AS total_delivered,
       SUM(failed_count) AS total_failed,
       AVG(delivery_rate_percent) AS avg_delivery_rate
FROM sms_analytics_daily
WHERE date_utc >= $1 AND date_utc <= $2
  AND direction = 'outbound';

API Endpoints

Tenant-Scoped Endpoints (v2)

Endpoint	Description
GET /api/v2/sms/analytics/timeseries/daily	Daily analytics for authenticated tenant
GET /api/v2/sms/analytics/timeseries/period	Period analytics (week/month/quarter/year)

Internal Health Endpoints

Endpoint	Description	Auth Required
GET /internal/sms/analytics/health/summary	Platform-wide health summary	PlatformOperator role
GET /internal/sms/analytics/health/tenants	Per-tenant health breakdown	PlatformOperator role

Migration from Orleans Grain Analytics

The legacy Orleans grain-based analytics are deprecated:

Code
// Deprecated - marked with [Obsolete]
public interface ITenantAnalyticsGrain : IGrainWithStringKey
{
    // Use /api/v2/sms/analytics/timeseries/daily instead
}

See SMS Analytics Integration Guide for migration steps.