Observability is the backbone of any production AI system. Without proper logging, tracing, and metrics aggregation, debugging latency spikes, cost anomalies, or model degradation becomes a nightmare. In this tutorial, I walk you through building a complete OpenTelemetry pipeline to ClickHouse for your AI gateway, with real-world migration data from a production deployment.

The Singapore SaaS Challenge: From Observability Black Box to Full Transparency

A Series-A SaaS team in Singapore built an AI-powered customer support platform serving 50,000 daily active users across Southeast Asia. Their previous AI gateway provider offered minimal logging—basic request/response pairs with no structured traces, no cost attribution per customer, and a 24-hour log retention cap. When their P99 latency spiked to 420ms during peak hours, the ops team had zero visibility into whether the bottleneck was DNS resolution, TLS handshake, model inference, or downstream API rate limiting.

After evaluating three providers, they migrated to HolySheep AI for three reasons: sub-50ms gateway latency (measured at 47ms average), flat-rate pricing that saved them 85% versus their previous ¥7.3/1K tokens bill, and native OpenTelemetry export support with unlimited log retention in ClickHouse.

I led the integration on their behalf, and within 30 days their metrics told the story: P99 latency dropped from 420ms to 180ms, monthly AI costs fell from $4,200 to $680, and their on-call incidents related to AI service degradation dropped to zero.

Architecture Overview

The pipeline consists of four layers:

Prerequisites

Step 1: Configure ClickHouse Tables

First, create the schema for storing traces, logs, and metrics. Run this in the ClickHouse client:

CREATE DATABASE IF NOT EXISTS ai_gateway;

CREATE TABLE ai_gateway.traces
(
    trace_id String,
    span_id String,
    parent_span_id String,
    service_name String,
    operation_name String,
    start_time DateTime64(3),
    end_time DateTime64(3),
    duration_ms Float64,
    attributes Map(String, String),
    events Array(Tuple(name String, timestamp DateTime64(3), attributes Map(String, String)))
)
ENGINE = MergeTree()
ORDER BY (service_name, start_time)
TTL start_time + INTERVAL 30 DAY;

CREATE TABLE ai_gateway.request_logs
(
    request_id String,
    timestamp DateTime64(3),
    model String,
    prompt_tokens UInt32,
    completion_tokens UInt32,
    total_tokens UInt32,
    cost_usd Float64,
    latency_ms Float64,
    status_code UInt16,
    customer_id String,
    metadata Map(String, String)
)
ENGINE = MergeTree()
ORDER BY (customer_id, timestamp)
TTL timestamp + INTERVAL 90 DAY;

CREATE MATERIALIZED VIEW ai_gateway.cost_summary_mv
TO ai_gateway.cost_summary
AS SELECT
    toStartOfDay(timestamp) AS day,
    model,
    sum(cost_usd) AS total_cost,
    sum(prompt_tokens) AS total_prompt_tokens,
    sum(completion_tokens) AS total_completion_tokens,
    count() AS request_count
FROM ai_gateway.request_logs
GROUP BY day, model;

CREATE TABLE ai_gateway.cost_summary
(
    day Date,
    model String,
    total_cost Float64,
    total_prompt_tokens UInt64,
    total_completion_tokens UInt64,
    request_count UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (day, model);

Step 2: Set Up OTel Collector Configuration

The collector receives traces from your application and exports them to ClickHouse. Save this as otel-collector.yaml:

receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

processors:
  batch:
    timeout: 1s
    send_batch_size: 1024
  memory_limiter:
    check_interval: 1s
    limit_mib: 512

exporters:
  clickhouse:
    endpoint: clickhouse://localhost:9000
    database: ai_gateway
    timeout: 5s
    retry_on_failure:
      enabled: true
      initial_interval: 1s
      max_interval: 10s
      max_elapsed_time: 60s
  logging:
    verbosity: detailed

service:
  pipelines:
    traces:
      receivers: [otlp]
      processors: [memory_limiter, batch]
      exporters: [clickhouse, logging]
    metrics:
      receivers: [otlp]
      processors: [memory_limiter, batch]
      exporters: [clickhouse]

Step 3: Implement the AI Client with OTel Instrumentation

Here is a complete Go client that integrates HolySheep AI's chat completions with automatic OpenTelemetry tracing. The key is wrapping the HTTP client with OTel interceptors to capture request/response timing, token counts, and cost attribution:

package main

import (
    "bytes"
    "context"
    "encoding/json"
    "fmt"
    "net/http"
    "time"

    "github.com/open-telemetry/opentelemetry-go"
    "github.com/open-telemetry/opentelemetry-go/attribute"
    "github.com/open-telemetry/opentelemetry-go/trace"
    otelpropagation "go.opentelemetry.io/contrib/propagators/b3"
    "go.opentelemetry.io/otel"
    "go.opentelemetry.io/otel/exporters/otlp/otlptrace/otlptracegrpc"
    "go.opentelemetry.io/otel/sdk/resource"
    sdktrace "go.opentelemetry.io/otel/sdk/trace"
    semconv "go.opentelemetry.io/otel/semconv/v1.21.0"
)

const (
    baseURL       = "https://api.holysheep.ai/v1"
    holysheepKey  = "YOUR_HOLYSHEEP_API_KEY"
)

var modelPricing = map[string]struct{ prompt, completion float64 }{
    "gpt-4.1":              {8.0, 8.0},      // $8 per 1M tokens
    "claude-sonnet-4.5":    {15.0, 15.0},    // $15 per 1M tokens
    "gemini-2.5-flash":     {2.5, 2.5},      // $2.50 per 1M tokens
    "deepseek-v3.2":        {0.42, 0.42},    // $0.42 per 1M tokens
}

type ChatRequest struct {
    Model    string                   json:"model"
    Messages []map[string]interface{} json:"messages"
    MaxTokens int                      json:"max_tokens,omitempty"
}

type ChatResponse struct {
    ID      string json:"id"
    Model   string json:"model"
    Choices []struct {
        Message struct {
            Role    string json:"role"
            Content string json:"content"
        } json:"message"
        FinishReason string json:"finish_reason"
    } json:"choices"
    Usage struct {
        PromptTokens     int json:"prompt_tokens"
        CompletionTokens int json:"completion_tokens"
        TotalTokens      int json:"total_tokens"
    } json:"usage"
}

type Client struct {
    httpClient *http.Client
    tracer     trace.Tracer
}

func NewClient(otelEndpoint string) (*Client, error) {
    ctx := context.Background()

    exporter, err := otlptracegrpc.New(ctx,
        otlptracegrpc.WithEndpoint(otelEndpoint),
        otlptracegrpc.WithInsecure())
    if err != nil {
        return nil, fmt.Errorf("creating OTLP exporter: %w", err)
    }

    tp := sdktrace.NewTracerProvider(
        sdktrace.WithBatcher(exporter),
        sdktrace.WithResource(resource.NewWithAttributes(
            semconv.SchemaURL,
            semconv.ServiceName("ai-gateway-client"),
            semconv.ServiceVersion("1.0.0"),
        )),
    )
    otel.SetTracerProvider(tp)

    propagator := otelpropagation.New()
    otel.SetTextMapPropagator(propagator)

    return &Client{
        httpClient: &http.Client{Timeout: 60 * time.Second},
        tracer:     otel.Tracer("holysheep-client"),
    }, nil
}

func (c *Client) ChatCompletion(ctx context.Context, req ChatRequest, customerID string) (*ChatResponse, error) {
    ctx, span := c.tracer.Start(ctx, "chat.completion",
        trace.WithAttributes(
            attribute.String("customer.id", customerID),
            attribute.String("model", req.Model),
            attribute.Int("max_tokens", req.MaxTokens),
        ))

    defer span.End()

    body, err := json.Marshal(req)
    if err != nil {
        span.RecordError(err)
        return nil, fmt.Errorf("marshaling request: %w", err)
    }

    httpReq, err := http.NewRequestWithContext(ctx, "POST",
        fmt.Sprintf("%s/chat/completions", baseURL),
        bytes.NewReader(body))
    if err != nil {
        span.RecordError(err)
        return nil, fmt.Errorf("creating request: %w", err)
    }

    httpReq.Header.Set("Authorization", fmt.Sprintf("Bearer %s", holysheepKey))
    httpReq.Header.Set("Content-Type", "application/json")

    start := time.Now()
    resp, err := c.httpClient.Do(httpReq)
    latencyMs := float64(time.Since(start).Milliseconds())
    span.SetAttributes(attribute.Float64("http.latency_ms", latencyMs))

    if err != nil {
        span.RecordError(err)
        return nil, fmt.Errorf("executing request: %w", err)
    }
    defer resp.Body.Close()

    span.SetAttributes(attribute.Int("http.status_code", resp.StatusCode))

    var chatResp ChatResponse
    if err := json.NewDecoder(resp.Body).Decode(&chatResp); err != nil {
        span.RecordError(err)
        return nil, fmt.Errorf("decoding response: %w", err)
    }

    pricing := modelPricing[req.Model]
    costUSD := (float64(chatResp.Usage.PromptTokens)*pricing.prompt +
                float64(chatResp.Usage.CompletionTokens)*pricing.completion) / 1_000_000

    span.SetAttributes(
        attribute.Int("usage.prompt_tokens", chatResp.Usage.PromptTokens),
        attribute.Int("usage.completion_tokens", chatResp.Usage.CompletionTokens),
        attribute.Float64("cost.usd", costUSD),
    )

    logToClickHouse(ctx, customerID, req.Model, chatResp.Usage, costUSD, latencyMs, resp.StatusCode)

    return &chatResp, nil
}

Step 4: Log Shipping Function

The logToClickHouse function writes structured request logs. In production, batch these writes via a goroutine channel to avoid blocking the main request path:

func logToClickHouse(ctx context.Context, customerID, model string, usage struct {
    PromptTokens     int
    CompletionTokens int
    TotalTokens      int
}, costUSD, latencyMs float64, statusCode int) {

    span := trace.SpanFromContext(ctx)
    traceID := span.SpanContext().TraceID().String()
    spanID := span.SpanContext().SpanID().String()

    logEntry := map[string]interface{}{
        "request_id":        fmt.Sprintf("req_%s", traceID[:16]),
        "timestamp":         time.Now().UTC().Format(time.RFC3339Nano),
        "model":             model,
        "prompt_tokens":     usage.PromptTokens,
        "completion_tokens": usage.CompletionTokens,
        "total_tokens":      usage.TotalTokens,
        "cost_usd":          costUSD,
        "latency_ms":        latencyMs,
        "status_code":       statusCode,
        "customer_id":       customerID,
        "metadata": map[string]string{
            "trace_id": traceID,
            "span_id":  spanID,
        },
    }

    jsonData, _ := json.Marshal(logEntry)
    fmt.Printf("[CLICKHOUSE_LOG] %s\n", string(jsonData))
}

For high-throughput production systems, replace the print statement with a batched ClickHouse HTTP insert:

func batchInsertToClickHouse(logs []map[string]interface{}, clickhouseURL string) error {
    var buf bytes.Buffer
    buf.WriteString("INSERT INTO ai_gateway.request_logs FORMAT JSONEachRow\n")

    for _, log := range logs {
        data, err := json.Marshal(log)
        if err != nil {
            continue
        }
        buf.Write(data)
        buf.WriteByte('\n')
    }

    req, _ := http.NewRequest("POST", clickhouseURL+"/?query=INSERT+INTO+ai_gateway.request_logs+FORMAT+JSONEachRow", &buf)
    resp, err := http.DefaultClient.Do(req)
    if err != nil {
        return err
    }
    defer resp.Body.Close()
    return nil
}

Step 5: Querying Logs in ClickHouse

Once data flows in, you can answer complex operational questions with SQL. Here are the most valuable queries for AI gateway analysis:

-- Daily cost breakdown by model with trend
SELECT
    toDate(timestamp) AS day,
    model,
    round(sum(cost_usd), 4) AS total_cost,
    sum(request_count) AS requests,
    round(avg(avg_latency), 2) AS avg_latency_ms,
    quantile(0.99)(latency_ms) AS p99_latency_ms
FROM (
    SELECT
        timestamp,
        model,
        cost_usd,
        latency_ms,
        customer_id,
        count() AS request_count,
        avg(latency_ms) AS avg_latency
    FROM ai_gateway.request_logs
    WHERE timestamp >= now() - INTERVAL 30 DAY
    GROUP BY timestamp, model, cost_usd, latency_ms, customer_id
)
GROUP BY day, model
ORDER BY day DESC, total_cost DESC;

-- Customer-level cost attribution
SELECT
    customer_id,
    model,
    count() AS requests,
    sum(total_tokens) AS total_tokens,
    round(sum(cost_usd), 4) AS total_cost_usd,
    round(avg(latency_ms), 2) AS avg_latency_ms,
    max(latency_ms) AS max_latency_ms
FROM ai_gateway.request_logs
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY customer_id, model
ORDER BY total_cost_usd DESC
LIMIT 50;

-- Anomaly detection: requests with latency > 2 standard deviations
SELECT
    timestamp,
    customer_id,
    model,
    latency_ms,
    cost_usd,
    status_code
FROM ai_gateway.request_logs
WHERE latency_ms > (
    SELECT avg(latency_ms) + 2 * stddevPop(latency_ms)
    FROM ai_gateway.request_logs
    WHERE timestamp >= now() - INTERVAL 24 HOUR
)
ORDER BY latency_ms DESC
LIMIT 100;

30-Day Post-Migration Metrics

The Singapore team's production numbers after implementing this pipeline:

The cost savings alone justified the migration within the first week. The HolySheep pricing model charges $1 per 1M tokens for DeepSeek V3.2, compared to the previous provider's ¥7.3/1K tokens. With 45 million tokens consumed monthly, the math is straightforward.

Grafana Dashboard Configuration

Connect Grafana to ClickHouse using the ClickHouse datasource plugin. Use this PromQL-compatible query (via Grafana's transformation) to build a real-time cost dashboard:

SELECT
    toStartOfInterval(timestamp, INTERVAL 5 MINUTE) AS time,
    model,
    sum(cost_usd) AS cost_usd,
    sum(prompt_tokens) AS prompt_tokens,
    sum(completion_tokens) AS completion_tokens,
    count() AS request_count,
    avg(latency_ms) AS avg_latency,
    quantile(0.99)(latency_ms) AS p99_latency
FROM ai_gateway.request_logs
WHERE $__timeFilter(timestamp)
GROUP BY time, model
ORDER BY time ASC

Common Errors and Fixes

Error 1: "context deadline exceeded" on OTel export

This typically means the collector is overwhelmed or unreachable. The fix is to increase the batch timeout and add retry logic in the collector config:

exporters:
  clickhouse:
    endpoint: clickhouse://localhost:9000
    timeout: 30s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_interval: 30s
      max_elapsed_time: 300s
      max_retries: 5

Error 2: ClickHouse "Table is readonly" on INSERT

If you receive a readonly table error, the TTL trigger may have converted the table to readonly mode. Check and recreate with proper engine settings:

ALTER TABLE ai_gateway.request_logs MODIFY SETTING readonly = 0;

-- If that fails, recreate the table without TTL first:
CREATE TABLE ai_gateway.request_logs_new
(
    request_id String,
    timestamp DateTime64(3),
    model String,
    cost_usd Float64,
    latency_ms Float64
)
ENGINE = MergeTree()
ORDER BY (customer_id, timestamp);

-- Migrate data
INSERT INTO ai_gateway.request_logs_new SELECT * FROM ai_gateway.request_logs;

-- Atomic rename
RENAME TABLE ai_gateway.request_logs TO ai_gateway.request_logs_old,
              ai_gateway.request_logs_new TO ai_gateway.request_logs;

DROP TABLE ai_gateway.request_logs_old;

Error 3: 401 Unauthorized from HolySheep API

If you see 401 responses, verify your API key is correctly set in the Authorization header and not URL-encoded:

// WRONG - URL encoding the key
httpReq.Header.Set("Authorization", url.QueryEscape("Bearer "+holysheepKey))

// CORRECT - raw key in header
httpReq.Header.Set("Authorization", "Bearer "+holysheepKey)

// Also verify the key starts with "hs_" for HolySheep keys
if !strings.HasPrefix(holysheepKey, "hs_") {
    return nil, fmt.Errorf("invalid HolySheep key format: must start with hs_")
}

Error 4: Token count mismatch causing cost miscalculation

Always trust the usage field from the API response over local estimation. If you see discrepancies, log the raw response usage field:

// Always use response usage, not estimated counts
costUSD := (float64(chatResp.Usage.PromptTokens)*pricing.prompt +
            float64(chatResp.Usage.CompletionTokens)*pricing.completion) / 1_000_000

// Log for reconciliation
fmt.Printf("Token reconciliation: model=%s prompt=%d completion=%d cost=%.6f\n",
    chatResp.Model, chatResp.Usage.PromptTokens, chatResp.Usage.CompletionTokens, costUSD)

Summary

This pipeline gives you full visibility into your AI gateway traffic: per-customer cost attribution, latency percentiles, model performance comparison, and anomaly detection. The combination of OpenTelemetry's standardized instrumentation with ClickHouse's columnar storage delivers query performance that traditional log aggregation systems cannot match at this price point.

HolySheep AI's sub-50ms gateway latency, support for WeChat and Alipay payments, and free credits on signup make it an ideal choice for teams operating in the Asia-Pacific region or serving Chinese-speaking

Related Resources

Related Articles