ClickHouse
Sentinel can write request logs to ClickHouse instead of the main database. Use it when you log many requests, or want raw records kept for weeks or months instead of the default 72 hours, without growing the main database.
Prerequisites: ClickHouse 23.3 or later, reachable over its HTTP interface, and an existing database for Sentinel's tables.
When to use it
| Main database (default) | ClickHouse | |
|---|---|---|
Raw records (_search) | [request_logs] retention, default 72h | [clickhouse] raw_retention, default 90d (1d–5y) |
Hourly statistics (_stats) | Kept indefinitely | Kept 5 years |
| Load on the main database | Every logged request is a row | None from request logs |
Everything else stays the same: the API, the admin UI Dashboard and Logs pages, and the record fields.
The store is chosen once, at startup. With [clickhouse] url set and the clickhouse feature licensed, request logs go to ClickHouse; without the feature, Sentinel logs [clickhouse] is configured but not licensed; request logs go to the database and keeps them in the main database. A newly installed license takes effect at the next restart. Logs already in the other store aren't moved.
Configure it
[clickhouse]
url = "https://clickhouse.example.com:8443"
database = "altcha_sentinel" # must already exist
username = "sentinel"
password = "${file:/run/secrets/clickhouse_password}"
raw_retention = "30d" # raw records for _search; hourly stats are kept 5 yearsThe url must start with http:// or https://. See [clickhouse] for every key and default.
At startup, Sentinel connects to ClickHouse and creates or checks its tables. If ClickHouse can't be reached, startup fails:
Error: error sending request for url (http://clickhouse:8123/)
Caused by:
0: client error (Connect)
…TLS
With an https:// url, Sentinel verifies the server certificate against the system CA store, the top-level tls_extra_ca_certs, and [clickhouse] tls_ca. For a client certificate, set tls_cert and tls_key together, both PEM:
[clickhouse]
url = "https://clickhouse.example.com:8443"
tls_ca = "${file:/etc/sentinel/clickhouse-ca.pem}" # private CA of the server
tls_cert = "${file:/etc/sentinel/clickhouse-client.pem}" # client chain, leaf first
tls_key = "${file:/run/secrets/clickhouse_client_key}" # PKCS#1, PKCS#8 or SEC1The tls_* keys need an https:// url; with http://, startup fails with clickhouse TLS options need an https:// url. A value that isn't PEM fails too, for example clickhouse.tls_ca: no PEM certificate found.
Tables and rollups
Sentinel keeps four objects in the configured database:
| Object | Holds |
|---|---|
request_logs | Raw records, partitioned by month, deleted after raw_retention |
request_stats_hourly | Hourly aggregates per key, kept 5 years, which _stats reads |
request_stats_hourly_mv | Materialized view that rolls each new record into request_stats_hourly |
request_stats_state | Rollup progress and the applied retention |
By default ([clickhouse] auto_migrate = true) Sentinel creates and upgrades them at startup. To have an administrator apply the schema instead, set auto_migrate = false and use altcha-sentinel schema sql --clickhouse; see Schema migrations.
When Sentinel starts on an existing request_logs table, it rolls up the stored records in the background, a day at a time, then applies raw_retention. Until that backfill finishes, _stats also reads raw records for the older hours, so the numbers are complete throughout.
Retention
raw_retention is a TTL on request_logs. A background job, run by one replica every 5 minutes, applies a changed value once the backfill above is done and logs raw request log retention applied. ClickHouse drops whole monthly partitions, so raw records can stay up to about a month longer than raw_retention.
With auto_migrate = false, Sentinel doesn't change the TTL. It logs a warning that raw_retention changed and keeps the old retention until you apply the script of altcha-sentinel schema sql --clickhouse.
Search and statistics
With ClickHouse, POST /v2/request-logs/_search reads request_logs and POST /v2/request-logs/_stats reads request_stats_hourly. The requests, filters, and responses are the same as with the main database; see Request logs & analytics. The differences:
_searchfinds records forraw_retentioninstead of[request_logs] retention.- Statistics are kept 5 years, so
_statscovers its full 366-day range limit for a year after you start. - A search or statistics query that takes longer than 60 seconds is stopped. If ClickHouse is unavailable, both endpoints return
500 INTERNAL_ERROR.
Writes are best-effort as with the main database: a batch that ClickHouse rejects is dropped and logged as request log flush failed. Dropped records are counted in altcha_sentinel_request_logs_dropped_total{reason="write_failed"}; see Monitoring & metrics.
Health and doctor checks
When ClickHouse is the request-log store, every replica probes it in the background:
GET /.healthincludes aclickhousecheck, and returns503with"status": "unhealthy"while ClickHouse is unreachable. Without ClickHouse, the check is absent.- The doctor
storage.clickhousecheck reportsClickHouse is reachable., or fails with the error and the hintCheck the [clickhouse] url, credentials and database.It's skipped when request logs are in the main database. license.featureswarnsConfigured but not licensed: clickhouse (request logs go to the database).whenurlis set without the license feature.
Run the checks from the CLI before you start the server:
docker run --rm \
-v "$PWD/sentinel.toml:/etc/sentinel/sentinel.toml:ro" \
-v "$PWD/secrets:/run/secrets:ro" \
ghcr.io/altcha-org/sentinel:2.0.0-beta.1 --config /etc/sentinel/sentinel.toml doctor FAIL storage.clickhouse ClickHouse is unreachable: error sending request for url (http://clickhouse:8123/): client error (Connect): …
hint: Check the [clickhouse] url, credentials and database.See the CLI reference for the doctor options.
Request logs from v1
v2 writes to a new request_logs table and doesn't read the v1 logs table; to keep v1's ClickHouse logs, copy them inside ClickHouse as described in Migrating data.