ClickHouseLogger 1.0.0

ClickHouseLogger

Plug-and-play observability library for .NET — HTTP request/response logging, structured debug logs, and metrics, all persisted to ClickHouse over its HTTP(S) interface.

---

Table of Contents

• Why ClickHouseLogger?
• Features
• Architecture
• Installation
• Quick Start
• Configuration Reference
• Usage
  • Request / Response Logging
  • Debug & Error Logging
  • Metrics
  • IndexedFields
• ClickHouse Table Schemas
• Fallback & Recovery
• Retry Policy
• Path Filtering
• Header Masking
• Graceful Shutdown
• Sample Queries
• Roadmap
• Contributing
• License

---

Why ClickHouseLogger?

Most logging libraries are designed for text search — they are not built to handle hundreds of millions of rows with fast aggregations over arbitrary time windows. ClickHouseLogger takes the opposite approach: it sends structured log data straight into ClickHouse, a columnar OLAP database that can aggregate billions of rows in seconds.

Goal	Solution
Full audit trail of every HTTP request	Request/response middleware captures body, headers, timing
Structured debug logs with context	IClickHouseLogger with anonymous-object extras
Time-series metrics without a separate stack	Built-in Metric() API writing to a dedicated table
Zero data loss under connectivity failures	Local JSON Lines fallback + automatic recovery on reconnect
No schema migrations	Tables and columns are created/altered automatically on startup

---

Features

• One NuGet package — no separate sink, no sidecar, no config files beyond Program.cs
• HTTP middleware (ASP.NET Core) — captures full request/response, including bodies up to a configurable size limit
• Structured logger — six log levels (Trace → Fatal) with arbitrary key/value extras and exception serialization
• Metrics API — numeric measurements with tags, stored as a time series
• Auto-DDL — CREATE TABLE IF NOT EXISTS and ALTER TABLE ADD COLUMN on startup; no manual schema management
• IndexedFields — promotes arbitrary Extra map keys to real indexed columns for fast WHERE lookups
• Batch processing — ConcurrentQueue + configurable flush-by-size (default 1 000) and flush-by-time (default 5 s)
• Exponential-backoff retry — configurable attempts before data is spilled to disk
• Local fallback — JSON Lines files written when ClickHouse is unreachable; recovered automatically when the connection restores
• Header masking — sensitive headers replaced with *** before storage
• Wildcard path filtering — include/exclude paths with * glob patterns
• Graceful shutdown — in-flight queue flushed before process exit
• Multi-target — netstandard2.1 + net8.0; works on .NET 6, 7, 8, and beyond. The HTTP request/response middleware requires ASP.NET Core and is available on the net8.0 target; debug logging and metrics work on every target.

---

Architecture

┌─────────────────────────────────────────────────────────────┐
│                      ASP.NET Core App                       │
│                                                             │
│  ┌──────────────────────┐  ┌──────────────┐  ┌──────────┐  │
│  │ Request/Response     │  │IClickHouse   │  │IClickHouse│  │
│  │ Middleware           │  │Logger        │  │Logger     │  │
│  │ (HTTP capture)       │  │(debug/error) │  │(metrics)  │  │
│  └──────────┬───────────┘  └──────┬───────┘  └────┬──────┘  │
└─────────────┼────────────────────┼───────────────┼──────────┘
              │                    │               │
              ▼                    ▼               ▼
┌─────────────────────────────────────────────────────────────┐
│                      ClickHouseLogger Core                  │
│                                                             │
│  BatchProcessor<RequestResponseEntry>                       │
│  BatchProcessor<DebugLogEntry>          ← one queue each    │
│  BatchProcessor<MetricEntry>                                │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐  │
│  │  ConcurrentQueue  →  flush on size OR interval       │  │
│  │  Retry (exponential backoff, configurable attempts)  │  │
│  └───────────────┬──────────────────────────────────────┘  │
│                  │                                          │
│       ┌──────────┴──────────┐                              │
│       ▼                     ▼                              │
│  ClickHouseWriter      FallbackFileWriter                  │
│  (HTTP 8123 / 8443)    (JSON Lines, local disk)            │
│       │                     │                              │
│  ConnectionManager     FallbackRecoveryService             │
│  (health check / 30s)  (IHostedService, auto-drain)        │
│       │                                                    │
│  TableManager                                              │
│  (auto DDL on startup)                                     │
└──────────────────────────────┬──────────────────────────────┘
                               │
                               ▼
                     ┌──────────────────┐
                     │   ClickHouse DB  │
                     │   HTTP port 8123 │
                     │                  │
                     │  {app}_request_  │
                     │  response_logs   │
                     │  {app}_debug_    │
                     │  logs            │
                     │  {app}_metrics   │
                     └──────────────────┘

---

Installation

dotnet add package ClickHouseLogger

Requirements

Component	Version
.NET	6 / 7 / 8+ (HTTP middleware requires ASP.NET Core on net8.0)
ClickHouse	22.x+
ClickHouse HTTP port	8123 (HTTP) / 8443 (HTTPS)

---

Quick Start

1 — Register in Program.cs

using ClickHouseLogger.Extensions;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddClickHouseLogger(options =>
{
    options.Host            = "your-clickhouse-host";
    options.Database        = "logs";
    options.ApplicationName = "MyApp";
    options.Environment     = builder.Environment.EnvironmentName;

    options.RequestResponse.TTL            = TimeSpan.FromDays(90);
    options.RequestResponse.ExcludePaths   = ["/health", "/swagger*", "/favicon.ico"];
    options.RequestResponse.IndexedFields  = ["CustomerId", "TenantId"];

    options.DebugLog.TTL          = TimeSpan.FromDays(30);
    options.DebugLog.MinimumLevel = ClickHouseLogLevel.Debug;

    options.Fallback.Directory = "/var/log/clickhouse-fallback/";
});

var app = builder.Build();

// Enable HTTP logging middleware
app.UseClickHouseRequestResponseLogging();

app.Run();

2 — Log in your services

public class OrderService(IClickHouseLogger logger)
{
    public async Task ProcessOrder(string orderId, string customerId)
    {
        logger.Info("Processing order", new { OrderId = orderId, CustomerId = customerId });

        try
        {
            // ... business logic ...
            logger.Info("Order completed", new { OrderId = orderId });
        }
        catch (Exception ex)
        {
            logger.Error("Order failed", ex, new { OrderId = orderId, CustomerId = customerId });
            throw;
        }
    }
}

3 — Record metrics

var sw = Stopwatch.StartNew();
var result = await _gateway.Charge(request);
sw.Stop();

_logger.Metric("request_duration_ms", sw.ElapsedMilliseconds,
    new { Provider = "ExampleProvider", Success = result.IsSuccess.ToString() });

That's it. Tables are created automatically on the first startup.

---

Configuration Reference

Binding from appsettings.json

ClickHouseLoggerOptions is a plain options object, so you can keep your settings in configuration and bind them — no extra API needed. A ready-to-copy template lives in appsettings.Example.json.

Add a ClickHouseLogger section to your appsettings.json:

{
  "ClickHouseLogger": {
    "Host": "your-clickhouse-host",
    "Database": "logs",
    "ApplicationName": "MyApp",
    "RequestResponse": { "ExcludePaths": [ "/health", "/swagger*" ] }
  }
}

Then bind the section inside AddClickHouseLogger:

builder.Services.AddClickHouseLogger(options =>
{
    builder.Configuration.GetSection("ClickHouseLogger").Bind(options);
});

Keep the password out of source control. Leave Password empty (or omit it) in appsettings.json and supply the real value from a secret source at runtime — an environment variable, user-secrets, or a secret manager (Azure Key Vault, AWS Secrets Manager, etc.):

builder.Services.AddClickHouseLogger(options =>
{
    builder.Configuration.GetSection("ClickHouseLogger").Bind(options);
    // e.g. CLICKHOUSE__PASSWORD env var, or builder.Configuration["ClickHouse:Password"]
    options.Password = builder.Configuration["CLICKHOUSE_PASSWORD"] ?? options.Password;
});

Files that typically hold real connection details — appsettings.Local.json, appsettings.*.Local.json, secrets.json, .env — are already git-ignored. Only the placeholder appsettings.Example.json is tracked.

Connection

Option	Default	Description
Host	"localhost"	ClickHouse server hostname or IP
Port	(auto)	ClickHouse HTTP interface port. Leave unset to use 8123 (HTTP) or 8443 (when UseTls is on)
UseTls	false	Connect over HTTPS — required by managed/cloud ClickHouse
Database	"logs"	Target database (created automatically if missing)
Username	"default"	ClickHouse user
Password	""	ClickHouse password
ApplicationName	(required)	Alphanumeric identifier; used as table name prefix
Environment	"Production"	Stored verbatim in every row (dev / staging / prod)

Request / Response (options.RequestResponse)

Option	Default	Description
Enabled	true	Toggle HTTP capture middleware
BodySizeLimit	5 MB	Bodies larger than this are truncated; original byte size is always recorded
TTL	null (keep forever)	ClickHouse TTL for automatic row expiry
TableName	{AppName}_request_response_logs	Override the auto-generated table name
MaskedHeaders	Authorization, Cookie, Set-Cookie, X-Api-Key	Headers replaced with *** before storage
ExcludePaths	[]	Glob patterns for paths to skip (e.g. "/health", "/swagger*")
IncludePaths	[]	When non-empty, only matching paths are logged
IndexedFields	[]	Extra map keys to promote to dedicated indexed columns

Debug Log (options.DebugLog)

Option	Default	Description
Enabled	true	Toggle debug/error logging
TTL	null	Row expiry
TableName	{AppName}_debug_logs	Override table name
MinimumLevel	Debug	Entries below this level are silently dropped
IndexedFields	[]	Promoted Extra columns

Metrics (options.Metrics)

Option	Default	Description
Enabled	true	Toggle metric recording
TTL	null	Row expiry
TableName	{AppName}_metrics	Override table name

Batch (options.Batch)

Option	Default	Description
Size	1 000	Flush when the queue reaches this many entries
FlushInterval	5 s	Flush even if the queue is smaller after this duration
MaxQueueSize	100 000	Max entries buffered in memory before new ones are dropped (load shedding). 0 = unbounded

Retry (options.Retry)

Option	Default	Description
MaxAttempts	3	Total write attempts before spilling to fallback
InitialDelay	1 s	Base delay; doubles on each failure (1 s → 2 s → 4 s)

Fallback (options.Fallback)

Option	Default	Description
Enabled	true	Write to disk when ClickHouse is unreachable
Directory	./clickhouse-fallback/	Directory for .jsonl spill files
MaxFileSize	100 MB	Rotate to a new file at this size
RecoveryOnStartup	true	Drain pending files if ClickHouse is healthy at boot

Other

Option	Default	Description
GracefulShutdownTimeout	30 s	Maximum time to flush queues on SIGTERM
HealthCheckInterval	30 s	How often the background health check pings ClickHouse (also bounds fallback-recovery latency)

---

Usage

Request / Response Logging

Register the middleware after authentication/authorization and before your endpoint handlers:

app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();

app.UseClickHouseRequestResponseLogging(); // ← here

app.MapControllers();

Every captured request produces a row with:

• Timing (Timestamp, Duration in ms)
• Identity (HttpMethod, Path, QueryString, StatusCode)
• Full headers (masked per MaskedHeaders config)
• Full request and response bodies (truncated at BodySizeLimit; original byte size always recorded)
• Client info (ClientIP from X-Forwarded-For or RemoteIpAddress, UserAgent)
• Distributed trace context (TraceId, SpanId via Activity.Current — OpenTelemetry compatible)
• Freeform map (Extra) plus any promoted IndexedFields

Debug & Error Logging

Inject IClickHouseLogger anywhere via the DI container:

public class OrderService(IClickHouseLogger logger)
{
    public async Task<Result> PlaceOrder(OrderRequest request)
    {
        logger.Debug("Order initiated", new
        {
            request.Amount,
            request.Currency,
            Provider = "ExampleProvider"
        });

        try
        {
            var result = await _gateway.Submit(request);

            logger.Info("Order succeeded", new
            {
                request.Amount,
                TransactionId = result.Id
            });

            return result;
        }
        catch (GatewayException ex)
        {
            logger.Error("Order gateway error", ex, new
            {
                request.Amount,
                Provider = "ExampleProvider"
            });
            throw;
        }
    }
}

Available log levels:

logger.Trace("Entering method");
logger.Debug("Cache miss", new { Key = cacheKey });
logger.Info("User signed in", new { UserId = userId });
logger.Warning("Rate limit approaching", new { Remaining = 10 });
logger.Error("Database timeout", ex, new { Query = sql });
logger.Fatal("Unhandled exception — shutting down", ex);

Entries below DebugLog.MinimumLevel are dropped in-process with zero allocation.

Metrics

// Duration
logger.Metric("db_query_ms", stopwatch.ElapsedMilliseconds,
    new { Table = "orders", Operation = "SELECT" },
    unit: "ms");

// Counter (record as a gauge; aggregate with sum() in ClickHouse)
logger.Metric("orders_processed", 1,
    new { Region = "EU", Plan = "Pro" },
    unit: "count");

// Size
logger.Metric("response_size_bytes", responseBytes,
    new { Endpoint = "/api/export" },
    unit: "bytes");

Metrics are stored with MetricName, Value (Float64), Unit, and a Tags map. Use ClickHouse aggregate functions (avg, quantile, sum) for dashboards.

IndexedFields

By default, arbitrary context is stored in an unindexed Map(String, String) column (Extra / Tags). Querying this map is flexible but slower than a real column.

For fields you query frequently, declare them as IndexedFields:

options.RequestResponse.IndexedFields = ["CustomerId", "TenantId"];
options.DebugLog.IndexedFields        = ["CustomerId"];

On startup, TableManager adds these as Nullable(String) columns via ALTER TABLE ... ADD COLUMN IF NOT EXISTS. A declared field is promoted into its own column and removed from the Extra map — so each value is stored exactly once, not duplicated.

Before IndexedFields (slow):

SELECT * FROM myapp_request_response_logs
WHERE Extra['CustomerId'] = '12345';

After IndexedFields (fast, uses index):

SELECT * FROM myapp_request_response_logs
WHERE CustomerId = '12345';

Naming rules: Field names must start with a letter and contain only letters, digits, and underscores. Invalid names cause a startup InvalidOperationException.

---

ClickHouse Table Schemas

Tables are created automatically on startup. The schemas below show the canonical structure for reference.

{app}_request_response_logs

CREATE TABLE IF NOT EXISTS logs.myapp_request_response_logs
(
    Id               UUID              DEFAULT generateUUIDv4(),
    ApplicationName  LowCardinality(String),
    Environment      LowCardinality(String),
    MachineName      LowCardinality(String),
    TraceId          String            DEFAULT '',
    SpanId           String            DEFAULT '',
    Timestamp        DateTime64(3, 'UTC'),
    Duration         UInt32,                          -- milliseconds
    HttpMethod       LowCardinality(String),
    Path             String,
    QueryString      String            DEFAULT '',
    RequestHeaders   Map(String, String),
    RequestBody      Nullable(String),
    RequestBodySize  UInt32            DEFAULT 0,     -- original bytes before truncation
    ContentType      LowCardinality(String) DEFAULT '',
    StatusCode       UInt16,
    ResponseHeaders  Map(String, String),
    ResponseBody     Nullable(String),
    ResponseBodySize UInt32            DEFAULT 0,
    ClientIP         String            DEFAULT '',
    UserAgent        String            DEFAULT '',
    Extra            Map(String, String)
    -- IndexedFields columns appended here via ALTER TABLE ADD COLUMN
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(Timestamp)
ORDER BY (ApplicationName, Timestamp)
TTL Timestamp + INTERVAL 90 DAY   -- omitted when TTL is null
SETTINGS index_granularity = 8192;

{app}_debug_logs

CREATE TABLE IF NOT EXISTS logs.myapp_debug_logs
(
    Id              UUID              DEFAULT generateUUIDv4(),
    ApplicationName LowCardinality(String),
    Environment     LowCardinality(String),
    MachineName     LowCardinality(String),
    TraceId         String            DEFAULT '',
    Timestamp       DateTime64(3, 'UTC'),
    Level           Enum8('Trace'=0, 'Debug'=1, 'Info'=2, 'Warning'=3, 'Error'=4, 'Fatal'=5),
    Message         String,
    Exception       Nullable(String),
    StackTrace      Nullable(String),
    Extra           Map(String, String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(Timestamp)
ORDER BY (ApplicationName, Level, Timestamp)
SETTINGS index_granularity = 8192;

ORDER BY includes Level so that WHERE Level >= 'Error' scans are efficient.

{app}_metrics

CREATE TABLE IF NOT EXISTS logs.myapp_metrics
(
    Id              UUID              DEFAULT generateUUIDv4(),
    ApplicationName LowCardinality(String),
    Environment     LowCardinality(String),
    MachineName     LowCardinality(String),
    Timestamp       DateTime64(3, 'UTC'),
    MetricName      LowCardinality(String),
    Value           Float64,
    Unit            LowCardinality(String) DEFAULT '',
    Tags            Map(String, String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(Timestamp)
ORDER BY (ApplicationName, MetricName, Timestamp)
SETTINGS index_granularity = 8192;

---

Fallback & Recovery

When ClickHouse is unreachable (network partition, restart, maintenance), data is written to local .jsonl files rather than dropped.

./clickhouse-fallback/
├── fallback-2026-05-18-001.jsonl   ← currently being written
├── fallback-2026-05-18-002.jsonl   ← rotated (size limit reached)
└── fallback-2026-05-17-001.jsonl   ← pending recovery

Each line is a self-contained JSON record:

{
  "_table": "myapp_debug_logs",
  "_type": "DebugLogEntry",
  "_timestamp": "2026-05-18T14:32:01Z",
  "data": { "Level": "Error", "Message": "Operation failed", ... }
}

Recovery flow:

• On startup — if RecoveryOnStartup = true and ClickHouse is healthy, pending files are drained in chronological order in the background (recovery never blocks application startup).
• At runtime — ConnectionManager polls ClickHouse every HealthCheckInterval (default 30 s). When the connection is restored, recovery is triggered automatically without a restart. Concurrent recovery passes are de-duplicated so a record is never inserted twice.
• Successfully recovered files are deleted. A malformed line is skipped (not a poison pill); partially recovered files are rewritten with only the still-pending records, so recovered records are never re-inserted.

---

Retry Policy

Failed writes are retried with exponential backoff before data is spilled to the fallback:

Attempt 1  →  fail  →  wait 1 s
Attempt 2  →  fail  →  wait 2 s
Attempt 3  →  fail  →  write to fallback file

Configure via options.Retry:

options.Retry.MaxAttempts  = 5;
options.Retry.InitialDelay = TimeSpan.FromMilliseconds(500);
// Sequence: 500 ms → 1 s → 2 s → 4 s → fallback

---

Path Filtering

Use glob patterns to control which paths the middleware captures:

// Exclude specific paths — log everything else
options.RequestResponse.ExcludePaths = [
    "/health",
    "/swagger*",
    "/hangfire*",
    "/favicon.ico"
];

// Allowlist — log only these paths
options.RequestResponse.IncludePaths = ["/api/*"];

// Combined — log /api/* but skip /api/internal/*
options.RequestResponse.IncludePaths = ["/api/*"];
options.RequestResponse.ExcludePaths = ["/api/internal*"];

Pattern	Matches
/health	Exact path only
/swagger*	/swagger, /swagger/index.html, etc.
/api/*/orders	/api/v1/orders, /api/v2/orders, etc.

Matching is case-insensitive. When both lists are provided, IncludePaths is evaluated first, then ExcludePaths.

---

Header Masking

Sensitive headers are replaced with *** before being stored. The default masked set:

• Authorization
• Cookie
• Set-Cookie
• X-Api-Key

Customize the list (replaces the defaults entirely):

options.RequestResponse.MaskedHeaders = [
    "Authorization",
    "Cookie",
    "Set-Cookie",
    "X-Api-Key",
    "X-Internal-Token",
    "X-Forwarded-Authorization"
];

Masking is case-insensitive and applies to both request and response headers.

---

Graceful Shutdown

On SIGTERM / application stop, the library:

1. Stops the periodic flush timers
2. Drains all BatchProcessor queues — attempts a ClickHouse write; falls back to file on failure
3. Stops FallbackRecoveryService
4. Closes ClickHouse connections

The process waits up to GracefulShutdownTimeout (default 30 s) for queues to drain. Any entries not written within that window are spilled to the fallback directory and recovered on the next startup.

---

Sample Queries

-- Error responses in the last hour
SELECT Timestamp, HttpMethod, Path, StatusCode, Duration
FROM myapp_request_response_logs
WHERE StatusCode >= 400
  AND Timestamp > now() - INTERVAL 1 HOUR
ORDER BY Timestamp DESC
LIMIT 100;

-- All requests from a specific customer (requires IndexedFields)
SELECT Timestamp, HttpMethod, Path, StatusCode, RequestBody
FROM myapp_request_response_logs
WHERE CustomerId = '12345'
ORDER BY Timestamp DESC;

-- P95 / P99 latency by endpoint (last 24 h)
SELECT
    Path,
    count()                             AS requests,
    round(avg(Duration))                AS avg_ms,
    round(quantile(0.95)(Duration))     AS p95_ms,
    round(quantile(0.99)(Duration))     AS p99_ms
FROM myapp_request_response_logs
WHERE Timestamp > now() - INTERVAL 24 HOUR
GROUP BY Path
ORDER BY requests DESC;

-- Error and Fatal logs in the last hour
SELECT Timestamp, Level, Message, Exception, StackTrace
FROM myapp_debug_logs
WHERE Level IN ('Error', 'Fatal')
  AND Timestamp > now() - INTERVAL 1 HOUR
ORDER BY Timestamp DESC;

-- Request duration by provider (last 7 days)
SELECT
    Tags['Provider']                    AS provider,
    count()                             AS total,
    round(avg(Value))                   AS avg_ms,
    round(max(Value))                   AS max_ms,
    round(quantile(0.99)(Value))        AS p99_ms
FROM myapp_metrics
WHERE MetricName = 'request_duration_ms'
  AND Timestamp > now() - INTERVAL 7 DAY
GROUP BY provider
ORDER BY avg_ms DESC;

-- Hourly error rate (last 24 h)
SELECT
    toStartOfHour(Timestamp)                                        AS hour,
    countIf(StatusCode >= 500)                                      AS server_errors,
    count()                                                         AS total,
    round(100.0 * countIf(StatusCode >= 500) / count(), 2)         AS error_rate_pct
FROM myapp_request_response_logs
WHERE Timestamp > now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

---

Roadmap

Package	Description	Status
ClickHouseLogger.Blazor	ErrorBoundary integration and HttpClient DelegatingHandler	Planned
ClickHouseLogger.Serilog	Serilog sink adapter	Planned
ClickHouseLogger.Extensions.Logging	ILogger<T> provider for drop-in compatibility	Planned
Sampling	Log every N-th request or a configurable percentage of traffic	Planned
Compression	Compress bodies in-flight before storage	Planned
Grafana dashboard	Pre-built dashboard JSON for common views	Planned

---

Contributing

Contributions are welcome. Please:

1. Fork the repository and create a feature branch off main
2. Follow the existing code style — comments only when the why is non-obvious
3. Add or update unit tests in ClickHouseLogger.Tests/
4. Open a pull request with a clear description of the change and its motivation

For significant changes, open an issue first to discuss the design.

Running Tests

# Requires .NET 8 SDK
dotnet test ClickHouseLogger.Tests/

Integration tests against a real ClickHouse instance can be run with Docker:

docker run -d --name clickhouse-test \
  -p 8123:8123 \
  clickhouse/clickhouse-server:latest
# 8123 is the HTTP interface this library connects to.
# (Add -p 9000:9000 only if you also need native-TCP clients.)

dotnet test ClickHouseLogger.Tests/ --filter Category=Integration

---

License

MIT © Iron.LogHouse