ClickHouse

For high-volume deployments, Sentinel can write request logs and analytics to ClickHouse instead of the main database — see Databases for when this is worth doing. This page covers the actual table Sentinel expects.

Requirements

ClickHouse version 22.6 or later.

Database and table setup

Create the database:

CREATE DATABASE IF NOT EXISTS altcha_sentinel ENGINE = Atomic;

Then create the logs table:

CREATE TABLE altcha_sentinel.logs (
  accountId LowCardinality(String),
  apiKeyId LowCardinality(String),
  time DateTime,
  browser UInt8,
  context Map(String, String),
  countryCode LowCardinality(FixedString(2)),
  device UInt8,
  endpoint UInt8,
  error String,
  hisScore UInt8,
  hisAssistive Bool,
  ip IPv6,
  method UInt8,
  network UInt8,
  path LowCardinality(String),
  powAlgorithm LowCardinality(String),
  referrer LowCardinality(String),
  triggeredRules Array(UInt8),
  serverLatency UInt32,
  statusCode UInt16,
  userIp IPv6,
  verificationId String,
  verified Bool,
  INDEX idx_apiKeyId apiKeyId TYPE set(1000) GRANULARITY 1,
  INDEX idx_countryCode countryCode TYPE set(1000) GRANULARITY 1,
  INDEX idx_ip ip TYPE set(1000) GRANULARITY 1,
  INDEX idx_method method TYPE set(1000) GRANULARITY 1,
  INDEX idx_path path TYPE set(1000) GRANULARITY 1,
  INDEX idx_referrer referrer TYPE set(1000) GRANULARITY 1,
  INDEX idx_userIp userIp TYPE set(1000) GRANULARITY 1,
  INDEX idx_statusCode statusCode TYPE set(1000) GRANULARITY 1,
  INDEX idx_verificationId verificationId TYPE tokenbf_v1(1024, 3, 0) GRANULARITY 1
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(time)
ORDER BY (accountId, toStartOfHour(time), apiKeyId)
TTL time + INTERVAL 5 YEAR DELETE
SETTINGS index_granularity = 8192, flatten_nested = 0;

Schema migrations by version

The table above is the current schema. If you created it on an older Sentinel version, apply these in order to catch up:

• v1.11.0 — ADD COLUMN verificationId String, ADD INDEX idx_verificationId verificationId TYPE tokenbf_v1(1024, 3, 0) GRANULARITY 1
• v1.12.0 — ADD COLUMN error String
• v1.25.0 — ADD COLUMN powAlgorithm LowCardinality(String), ADD COLUMN hisScore UInt8, ADD COLUMN hisAssistive Bool

Connecting Sentinel to it

CLICKHOUSE_URL=http://user:password@localhost:8123/altcha_sentinel

Batching is controlled by two variables — Sentinel buffers log rows and flushes on whichever limit is hit first:

TLS: CLICKHOUSE_TLS_CA / CLICKHOUSE_TLS_CERT / CLICKHOUSE_TLS_KEY. Full reference: ENV Variables.

Retention

The table's TTL time + INTERVAL 5 YEAR DELETE clause auto-deletes rows older than 5 years. Adjust the interval in the CREATE TABLE statement to change retention — there's no separate Sentinel-side setting for this.

Decoding the integer columns

Several columns store small integer codes rather than strings, to keep the table compact. The mappings:

browser — Chrome (1), Firefox (2), Edge (3), Safari (4), Brave (5), Vivaldi (6), Opera (7), App (8)

device — Desktop (1), Console (2), Mobile (3), Tablet (4), SmartTV (5), Wearable (6), Embedded (7), Bot (8)

network — Fixed (1), Mobile (2), Hosting (3), Proxy (4), Tor (5)

method — GET (1), POST (2), PATCH (3), PUT (4), DELETE (5), OPTIONS (6), QUERY (7), HEAD (8)

endpoint — Challenge (1), Verify (2)

triggeredRules — an array of integer codes, each identifying one of the Classifier / detection rules that fired:

Related

• Databases — when to offload logs to ClickHouse vs. keep them in the main database.
• Classifier — the rules encoded in triggeredRules.
• Monitoring & Logging — Sentinel's own logs, separate from this analytics table.
• ENV Variables