# ClickHouse

> **Sentinel v2 (beta):** This page documents Sentinel v2, a beta rewrite that may change before the stable release. It does not apply to Sentinel v1, the current stable version: see the [Sentinel v1 documentation](/docs/sentinel.md).

Sentinel can write [request logs](/docs/sentinel/v2/operations/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.

<Callout type="note" title="License required">

Requires the <code>clickhouse</code> license feature. See [Plans & Licensing](/docs/sentinel/v2/plans/).

## 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](/docs/sentinel/v2/operations/request-logs/#log-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

```toml
[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 years
```

The `url` must start with `http://` or `https://`. See [`[clickhouse]`](/docs/sentinel/v2/reference/configuration/#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:

```text
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`](/docs/sentinel/v2/reference/configuration/#top-level), and `[clickhouse] tls_ca`. For a client certificate, set `tls_cert` and `tls_key` together, both PEM:

```toml
[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 SEC1
```

The `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](/docs/sentinel/v2/operations/schema-migrations/#clickhouse).

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](/docs/sentinel/v2/operations/request-logs/). The differences:

- `_search` finds records for `raw_retention` instead of `[request_logs] retention`.
- Statistics are kept 5 years, so `_stats` covers 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](/docs/sentinel/v2/operations/monitoring/).

## Health and doctor checks

When ClickHouse is the request-log store, every replica probes it in the background:

- [`GET /.health`](/docs/sentinel/v2/reference/configuration/#health-endpoint) includes a `clickhouse` check, and returns `503` with `"status": "unhealthy"` while ClickHouse is unreachable. Without ClickHouse, the check is absent.
- The [doctor](/docs/sentinel/v2/operations/troubleshooting/) `storage.clickhouse` check reports `ClickHouse is reachable.`, or fails with the error and the hint `Check the [clickhouse] url, credentials and database.` It's skipped when request logs are in the main database.
- `license.features` warns `Configured but not licensed: clickhouse (request logs go to the database).` when `url` is set without the license feature.

Run the checks from the CLI before you start the server:

```bash
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
```

```text
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](/docs/sentinel/v2/reference/cli/) 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](/docs/sentinel/v2/upgrade/migrate-data/#copy-clickhouse-request-logs).

## Related

- [Request logs & analytics](/docs/sentinel/v2/operations/request-logs/)
- [Schema migrations](/docs/sentinel/v2/operations/schema-migrations/)
- [Configuration reference: `[clickhouse]`](/docs/sentinel/v2/reference/configuration/#clickhouse)
- [Monitoring & metrics](/docs/sentinel/v2/operations/monitoring/)
- [Troubleshooting](/docs/sentinel/v2/operations/troubleshooting/)
- [Plans & Licensing](/docs/sentinel/v2/plans/)
