Skip to main content
Version: 4.1

ClickHouse Data Model Reference

This document is the developer reference for every ClickHouse object Nauthilus writes or reads: which tables exist, why they exist, which component fills each column, and how readers query them. Code and DDL are the authority; this document points at them by file and symbol name.

Related documents:

1. Purpose and Overview​

ClickHouse is the only durable history store of Nauthilus. It holds:

  • an analytics projection of authentication requests (nauthilus.logins);
  • purpose-bound step events: second-factor verifications and OIDC authorization-code completion (nauthilus.auth_events);
  • brute-force evidence: ban lifecycle transitions, raw counted, tolerated, and ban-hit events, and an hourly aggregate that outlives the raw rows (nauthilus.bruteforce_lifecycle, nauthilus.bruteforce_attempts, nauthilus.bruteforce_attempt_summary, fed by the materialized view nauthilus.bruteforce_attempt_summary_mv).

Nauthilus stays fully operable without ClickHouse; only history diagnosis is then unavailable. The data is not a complete attempt history: deduplication, buffer limits, process loss, and TTLs remove rows, and a login row exists only when the selected Policy rule carries the effect authn/plugin.clickhouse.post_action.

1.1 Writers​

WriterStatusTablesCode
Native Go plugin clickhouse (Policy post-action, AuthEventSink, BruteForceEventSink)Activelogins, auth_events, bruteforce_lifecycle, bruteforce_attemptscontrib/plugins/clickhouse/*.go
Materialized view bruteforce_attempt_summary_mvActive (server side)bruteforce_attempt_summarycontrib/clickhouse-kubernetes/schema.sql
Lua action server/lua-plugins.d/actions/clickhouse.luaRetired, reference only; the production Policy VM rejects itlogins (legacy column subset only)server/lua-plugins.d/actions/clickhouse.lua

1.2 Readers​

ReaderTablesCode
Support history service (HTTP /api/v1/support/* and the MCP tools share one instance)all five tablesserver/supporthistory/clickhousestore/*.go, consumers in server/supporthistory/*.go
Grafana dashboardloginscontrib/clickhouse-kubernetes/nauthilus-logins-grafana-dashboard.json
Lua custom hook (optional operator tool)logins (configurable via CLICKHOUSE_TABLE)server/lua-plugins.d/hooks/clickhouse-query.lua
Identity-proxy E2E script (test only)auth_eventscontrib/identity-proxy-e2e/scripts/run.sh
Integration tests (test only, build tag clickhouse_integration)allcontrib/plugins/clickhouse/*_integration_test.go

1.3 Data Flow​

2. Object Summary​

Database: nauthilus.

ObjectKindWritten byRead byPurpose
nauthilus.loginsMergeTree, ORDER BY (ts), TTL 1 weeknative login stream; retired Lua actionsupport history, Grafana, Lua query hookOne row per authentication request whose Policy rule selects the analytics post-action; terminal outcome under contract version 1
nauthilus.auth_eventsReplacingMergeTree, ORDER BY (event_type, ts, event_id), TTL 1 weeknative event streamsupport history, E2E scriptSecond-factor verifications and OIDC code issuance/redemption
nauthilus.bruteforce_lifecycleReplacingMergeTree, monthly partitions, TTL 400 daysnative bfLifecycle streamsupport historyBan activations, unconfirmed activations, releases, counter resets
nauthilus.bruteforce_attemptsReplacingMergeTree, daily partitions, TTL 14 days, ttl_only_drop_partsnative bfCounted and bfHits streamssupport historyRaw counted, tolerated, and ban-hit events
nauthilus.bruteforce_attempt_summaryAggregatingMergeTree, monthly partitions, TTL 400 daysbruteforce_attempt_summary_mvsupport historyHourly aggregate that outlives the raw attempt rows
nauthilus.bruteforce_attempt_summary_mvMaterialized view TO bruteforce_attempt_summaryClickHouse on every insert into bruteforce_attemptsnot read directlyFeeds the summary table

Access objects (history-reader.sql): settings profile nauthilus_history_profile, user nauthilus_history, and five GRANT SELECT statements. There are no roles, row policies, quotas, or column grants (see section 6).

3. Write Path (Native Plugin)​

3.1 Insert Format​

Every stream sends one HTTP POST to its configured insert URL. The URL carries the SQL in its query string, for example ?query=INSERT INTO nauthilus.logins FORMAT JSONEachRow. The body is newline-joined JSON documents (ndjsonBody, postRows in post_action.go).

  • Headers: Accept: */*, User-Agent: Nauthilus, Content-Type: application/json. HTTP Basic with padded base64 when user and password are both set, otherwise X-ClickHouse-User / X-ClickHouse-Key (buildHeaders).
  • Success is HTTP 200 or 204.
  • The plugin performs no DDL and no schema discovery.
  • ClickHouse drops unknown JSONEachRow fields silently unless the insert URL appends &input_format_skip_unknown_fields=0. Setting it turns schema drift into visible insert errors and is the recommended production setting after the migrations ran.
  • Timestamps are encoded as 2006-01-02 15:04:05.000 UTC text (clickHouseTimestampLayout, utcNowMillis in row.go).

3.2 Streams​

Configuration: config.go, stream wiring: stream.go, plugin.go.

StreamTableInsert URL keyBuffer key (default)Inline flushPeriodic flush
loginsloginsinsert_urlcache_key (clickhouse:batch:logins)In Enqueue when the buffer reaches min(batch_size, max_buffer_rows)flush_interval, default 0 (disabled)
eventsauth_eventsevents_insert_urlevents_cache_key (clickhouse:batch:auth_events)neverevents_flush_interval, default 10 s; the worker runs only while the URL is set
bfLifecyclebruteforce_lifecyclebruteforce_lifecycle_insert_urlbruteforce_lifecycle_cache_key (clickhouse:batch:bruteforce_lifecycle)neverbruteforce_flush_interval, default 10 s; one worker shared by the three brute-force streams
bfCountedbruteforce_attempts (kinds counted, threshold_tolerated)bruteforce_attempts_insert_urlbruteforce_counted_cache_key (clickhouse:batch:bruteforce_counted)From the host lane goroutine, bounded by timeout and the host delivery deadlineshared brute-force worker
bfHitsbruteforce_attempts (kind ban_hit)bruteforce_attempts_insert_urlbruteforce_hits_cache_key (clickhouse:batch:bruteforce_hits)same as bfCountedshared brute-force worker

Buffering rules:

  • The buffer keys name entries in the module-scoped host cache (server/pluginruntime/cache.go). That cache is process-local memory, not Redis; every pod buffers and flushes its own rows. The keys must be distinct (requireDistinctCacheKeys).
  • Each stream admits at most max_buffer_rows rows (default 10000). The limit applies per stream, so one process can hold up to five times that amount plus in-flight batches.
  • On the login stream, rows whose outcome marker is auth.outcome.brute_force_reject may fill at most max_brute_force_reject_rows (default max_buffer_rows / 2) (reject_share.go), so a brute-force wave cannot crowd out other rows.
  • The inline flush of logins, bfCounted, and bfHits triggers when the buffer reaches min(batch_size, max_buffer_rows). A flush pops the whole buffer (PopAll), not batch_size rows.
  • A full login buffer drops the new row and triggers a flush attempt.
  • With an empty insert URL, events and the three brute-force streams drop events at admission, while logins keeps buffering up to max_buffer_rows ("insert URL missing; batch kept in cache").
  • One insert runs per stream at a time. Failures are requeued within the limit and pause that stream with exponential backoff from 1 s to 60 s (stream.go, flush_gate.go). Stop flushes every stream once, bounded by timeout.
  • Rows are buffered already encoded, so event_id and ts stay stable across requeues. Ambiguous transport failures can still duplicate rows; there is no exactly-once guarantee. Replacing engines and readers handle that (see sections 4, 8, and 9).

3.3 Login Deduplication​

Only the login stream deduplicates (allowLoginWrite in post_action.go, loginDedupKey in dedup.go):

  • Enabled per sealed outcome: ok uses dedup_success (default true), fail uses dedup_failure (default false); tempfail and unknown are never deduplicated.
  • Mechanism: Redis SET NX EX <auth_dedup_ttl> (default 300 s) on clickhouse:authdedup:v3:<sha256 hex>, built through the plugin Redis facade (see the Redis reference for the effective key). The digest covers a JSON array of username, client IP, protocol, service, method, client IDs, SAML entity, grant type, MFA method and completion, authenticated/authorized flags, HTTP status, status message, outcome decision, operation, outcome and response markers, policy rule, and flow_ref. Credentials and password hashes are not part of it.
  • A missing username or client IP bypasses deduplication. Redis errors fail open (the row is written).
  • Events and brute-force rows are never deduplicated by the plugin; their tables collapse replays by event_id.

3.4 Skipped Requests​

Requests without authentication are skipped, except OIDC token post-actions (protocol = oidc, service = idp, non-empty grant type) (shouldSkipNoAuth).

3.5 Privacy on the Write Path​

DataTreatment
PasswordNever stored. password_hash holds the first 8 hex characters of the SHA-256 digest of the nonce-prepared password (clickHousePasswordHash; digest from pluginapi/v1/password). Requires the plugin capability CapabilityPasswordHash.
Username, account, display name, client IP, user agent, hostnameStored in clear text in logins.
Claimed login namesStored in clear text only in bruteforce_attempts.claimed_username. The summary keeps only uniq HLL states. bruteforce_lifecycle has no claimed names and no client address.
IdP flow handleNever stored. flow_ref = hex(sha256("nauthilus.flow_ref.v1\x00" + flow_id))[0:32].
Rule and bucket identityrule_fingerprint and bucket_id are SHA-256 digests computed by the host.
Step eventsNo credentials, OTPs, recovery codes, authorization codes, PKCE verifiers, tokens, cookies, handles, or WebAuthn data.
Dedup keySHA-256 digest only.
Logs, metrics, spansNo row bodies, SQL, usernames, client IPs, or credentials; flush errors are logged without error text.

3.6 Retired Lua Action​

server/lua-plugins.d/actions/clickhouse.lua is kept as a reference only and receives no new columns. It batches through nauthilus_cache, flushes only on size (CLICKHOUSE_BATCH_SIZE, default 100), deduplicates only authenticated rows for a fixed 300 s on the plain key clickhouse:authdedup:<username>:<client_ip>, and sends the legacy column subset: ts, session, service, decision_sources, columns client_ip through geoip_asn_status, and repeating through status_msg (53 columns of the current schema), plus the retired gp_*, prot_*, and dyn_* columns (but no reputation_* columns). Because of those retired columns, tables created from the current schema.sql, or after the retirement block ran, reject its rows when the insert URL sets input_format_skip_unknown_fields=0. It never writes deployment, so the support history service never sees its rows, and contract_version stays at the column default 0.

4. Tables​

"Source" names the field of the native writer struct and where its value comes from. All logins fields live in clickHouseRow (row.go), built by buildRow, applyContractInfo, applyTerminalOutcome (outcome_row.go), and applyReputationAssessments (reputation_row.go).

4.1 nauthilus.logins​

  • Purpose: one projection row per authentication request.
  • Engine: MergeTree; ORDER BY (ts) (also the primary key); no partitioning; TTL ts + INTERVAL 1 WEEK DELETE; index_granularity = 8192.
  • Skip indexes: idx_flow_ref (flow_ref, bloom_filter, granularity 64); idx_username, idx_account, idx_client_ip (tokenbf_v1(1024, 3, 0), granularity 64).
  • No replacing semantics: replays stay as duplicate rows that share event_id.
  • Row timing: the request snapshot is captured when the selected rule's effects are prepared. http_status, status_msg, and the outcome columns are late-bound from the sealed terminal outcome when the post-action runs after the response commit.
  • Effective columns: 89. The native writer fills every one of them.
#ColumnTypeDefault / codecSource (native writer)Meaning
1tsDateTime64(3, 'UTC')-TS = utcNowMillis() at encode timeEnqueue time after the response commit (UTC, ms). Primary key.
2sessionString-Session ← Snapshot.SessionPer-request correlation ID (Runtime.GUID). Joins auth_events.request_id and bruteforce_*.request_id of the same request. Not a flow ID.
3serviceLowCardinality(String)ZSTD(3)Service ← Snapshot.ServiceAuth endpoint service (for example idp).
4deploymentLowCardinality(String)DEFAULT '', ZSTD(3) (tables migrated from the legacy shape have no default)Deployment ← module config deploymentWriter separation. The support history reader binds every query to it.
5instanceLowCardinality(String)DEFAULT '', ZSTD(3) (no default on migrated tables)Instance ← module config instanceWriter instance (pod) label.
6decision_sourcesLowCardinality(String)ZSTD(3)DecisionSources ← Snapshot.DecisionSourcesString()Comma list of controls that fired (tls_encryption, relay_domains, rbl, brute_force, blocklist, failed_login_hotspot, ...); empty when none fired. Query by membership, not by exact string.
7rbl_listsArray(LowCardinality(String))-RBLLists ← Diagnostics.RBLMatchedLists (nil becomes [])Every configured RBL list that listed the client IP, including matches below the threshold.
8client_ipString-ClientIP ← Snapshot.ClientIPClient address as supplied.
9client_portString-ClientPortClient port.
10client_netLowCardinality(String)-ClientNetBrute-force client network chosen by the brute-force path; empty when brute force did not run.
11client_idLowCardinality(String)-ClientIDCaller-supplied client ID (X-Client-ID).
12hostnameLowCardinality(String)ZSTD(3)Hostname ← Snapshot.ClientHostClient host name (caller-supplied or reverse DNS).
13protoLowCardinality(String)-Proto ← Snapshot.ProtocolProtocol (imap, smtp, oidc, ...).
14methodLowCardinality(String)-MethodAuthentication method.
15user_agentLowCardinality(String)ZSTD(5)UserAgentClient user agent.
16local_ipLowCardinality(String)-LocalIPLocal (server) address.
17local_portString-LocalPortLocal port.
18display_nameLowCardinality(String)-DisplayNameResolved display name; empty for unknown users.
19accountLowCardinality(String)-AccountResolved account; empty when unknown or not yet looked up at capture time.
20usernameLowCardinality(String)-UsernameLogin name as sent (a claim).
21password_hashString-PasswordHash ← clickHousePasswordHash(...)8-hex prefix of the nonce-prepared SHA-256 password digest; empty without a password. History APIs must not expose it.
22pwnd_infoLowCardinality(String)-PwndInfo ← exchange HIBPHashInfo()Have-I-Been-Pwned prefix and count; empty when HIBP did not run before this post-action.
23brute_force_bucketLowCardinality(String)-BruteForceBucket ← Diagnostics.BruteForceNameBucket that went over its limit; also set when toleration suppressed the block.
24brute_force_counterNullable(UInt64)-BruteForceCounter ← Diagnostics.BruteForceCounterBucket counter at capture time; 0 when no bucket. The native writer never writes NULL.
25oidc_cidLowCardinality(String)-OIDCCIDOIDC client ID (IdP context).
26saml_entity_idLowCardinality(String)-SAMLEntityIDSAML relying-party entity ID.
27grant_typeLowCardinality(String)-GrantType ← Snapshot.IDP.GrantTypeOIDC grant type.
28mfa_methodLowCardinality(String)-MFAMethod ← Snapshot.IDP.MFAMethodSecond factor completed for this request; empty for password logins. MFA history lives in auth_events.
29failed_login_countNullable(UInt64)-FailedLoginCount ← exchange failed_login_hotspot.count or .new_count (applyFailedLoginInfo)Failed-login hotspot count; NULL without a producer.
30failed_login_rankNullable(UInt64)-FailedLoginRank ← .rankHotspot rank.
31failed_login_recognizedNullable(Bool)-FailedLoginRecognized ← .recognized_accountThe hotspot recognized the account.
32geoip_guidLowCardinality(String)-GeoIPGUID ← GeoIP field guid (applyGeoIPInfo)Legacy exchange GUID; no active producer.
33geoip_countryLowCardinality(String)-GeoIPCountry ← country_iso or current_country_code, normalized to ISO alpha-2Country of the client IP.
34geoip_iso_codesLowCardinality(String)-GeoIPISOCodes ← iso_codes_seen plus the country, comma-joinedDistinct ISO codes seen.
35geoip_statusLowCardinality(String)-GeoIPStatus ← status, else matched / miss (geoIPStatus)GeoIP lookup status.
36geoip_sourceLowCardinality(String)-GeoIPSource ← source, else native_geoipGeoIP evidence source.
37geoip_matchedNullable(Bool)-GeoIPMatched ← matchedThe lookup matched.
38geoip_country_nameLowCardinality(String)-GeoIPCountryNameCountry name.
39geoip_city_nameLowCardinality(String)-GeoIPCityNameCity name (empty with a country-only database).
40geoip_asnNullable(UInt64)-GeoIPASNAutonomous system number.
41geoip_asn_orgLowCardinality(String)-GeoIPASNOrgAS organisation.
42geoip_asn_prefixLowCardinality(String)-GeoIPASNPrefixAnnounced prefix.
43geoip_asn_registryLowCardinality(String)-GeoIPASNRegistryRegional internet registry of the AS.
44geoip_asn_countryLowCardinality(String)-GeoIPASNCountry ← asn_country_isoAS country (ISO).
45geoip_asn_allocatedLowCardinality(String)-GeoIPASNAllocatedAS allocation date.
46geoip_asn_statusLowCardinality(String)-GeoIPASNStatusAS status.
47geoip_privacy_lookup_stateLowCardinality(String)-GeoIPPrivacyLookupState ← GeoIPAnalytics.Privacy.LookupState (applyGeoIPPrivacyInfo)Privacy-intelligence lookup state.
48geoip_privacy_detectedNullable(Bool)-GeoIPPrivacyDetectedAny privacy class detected; NULL without evidence.
49geoip_privacy_classesArray(String)-GeoIPPrivacyClasses (always an array)Detected privacy classes; [] when none.
50geoip_privacy_primary_classLowCardinality(String)-GeoIPPrivacyPrimaryClassPrimary privacy class.
51geoip_privacy_confidenceNullable(Float64)-GeoIPPrivacyConfidenceClassification confidence.
52geoip_privacy_source_authoritiesArray(String)-GeoIPPrivacySourceAuthorities (always an array)Evidence authorities; [] when none.
53geoip_privacy_data_staleNullable(Bool)-GeoIPPrivacyDataStaleThe evidence data is stale.
54geoip_privacy_data_age_secondsNullable(UInt64)-GeoIPPrivacyDataAgeSecondsEvidence age in seconds.
55geoip_is_tor_exit_nodeNullable(Bool)-GeoIPIsTorExitNodeTor exit evidence.
56geoip_is_known_vpn_exitNullable(Bool)-GeoIPIsKnownVPNExitKnown VPN exit evidence.
57geoip_is_community_vpn_exitNullable(Bool)-GeoIPIsCommunityVPNExitCommunity VPN exit evidence.
58geoip_is_public_proxyNullable(Bool)-GeoIPIsPublicProxyPublic proxy evidence.
59geoip_is_privacy_relayNullable(Bool)-GeoIPIsPrivacyRelayPrivacy relay evidence.
60geoip_is_hosting_networkNullable(Bool)-GeoIPIsHostingNetworkHosting network evidence (separate from VPN).
61geoip_is_shared_egressNullable(Bool)-GeoIPIsSharedEgressShared egress evidence; intended for custom queries.
62repeatingNullable(Bool)-Repeating ← Snapshot.Runtime.RepeatingBrute-force repeat hint; false when brute force did not run. Weak, capture-time signal.
63rwpNullable(Bool)-RWPRepeated-wrong-password hint. Weak, capture-time signal.
64user_foundNullable(Bool)-UserFoundAccount resolved at capture time; also false when the lookup had not run yet.
65authenticatedNullable(Bool)-AuthenticatedRuntime.Authenticated at capture; false for fail and tempfail. Superseded by outcome as the outcome carrier.
66xssl_protocolLowCardinality(String)-XSSLProtocol ← TLS.Legacy.ProtocolTLS protocol reported by the proxy.
67xssl_cipherLowCardinality(String)-XSSLCipher ← TLS.Legacy.CipherSuiteTLS cipher reported by the proxy.
68ssl_fingerprintLowCardinality(String)-SSLFingerprint ← TLS.Legacy.FingerprintClient TLS fingerprint header.
69latencyUInt64-Latency ← Diagnostics.LatencyMillis (negative becomes 0)Milliseconds from request start to capture; 0 when unknown.
70http_statusUInt16-HTTPStatus ← Diagnostics.HTTPStatus, refreshed from the sealed outcomeHost status code configured for the terminal decision; 0 when the outcome is unknown.
71status_msgLowCardinality(String)-StatusMessage ← Diagnostics.StatusMessage (late-bound)Final client status text; empty when the outcome is unknown.
72contract_versionUInt16DEFAULT 0ContractVersion = loginContractVersion (1)0 legacy row, 1 diagnostic data contract.
73event_idStringDEFAULT ''EventID ← newEventID() (128-bit random hex)Row identity; stable across requeues; identifies duplicates.
74operationLowCardinality(String)DEFAULT ''Operation ← Outcome.OperationPolicy operation: authenticate, lookup_identity, list_accounts; empty when unknown.
75outcomeLowCardinality(String)DEFAULT ''Outcome ← Outcome.Decision, or unknown when unsealedok, fail, tempfail, unknown; empty for legacy rows.
76outcome_stageLowCardinality(String)DEFAULT ''OutcomeStagePolicy stage of the presenting rule (pre_auth, auth_decision, ...).
77outcome_markerLowCardinality(String)DEFAULT ''OutcomeMarkerPolicy outcome marker (for example auth.outcome.auth_failure, auth.outcome.brute_force_reject).
78response_markerLowCardinality(String)DEFAULT ''ResponseMarkerResponse marker (for example auth.response.tempfail.no_tls).
79policy_ruleLowCardinality(String)DEFAULT ''PolicyRuleName of the presenting rule.
80tempfail_reasonLowCardinality(String)DEFAULT ''TempFailReasonpolicy_indeterminate, backend_tempfail, auth_fsm_guard, brute_force_error, environment_tempfail; empty unless tempfail.
81tempfail_checkpointLowCardinality(String)DEFAULT ''TempFailCheckpointCheckpoint where an indeterminate result surfaced.
82policy_statusLowCardinality(String)DEFAULT ''PolicyStatusDecision status code (evaluation_failed, provider_unavailable, ...).
83fsm_stateLowCardinality(String)DEFAULT ''FSMStateTerminal auth FSM state.
84transportLowCardinality(String)DEFAULT ''Transport ← Outcome.Transporthttp, grpc, or empty when unknown.
85transport_statusNullable(UInt16)-TransportStatus, only for http and values above 0Committed HTTP writer status; NULL for gRPC or unknown. Never derive the result from it.
86flow_refStringDEFAULT ''FlowRef ← Snapshot.IDP.FlowRefNon-authorizing IdP flow digest (32 hex); joins auth_events.flow_ref; empty outside IdP browser flows.
87brute_force_toleratedNullable(Bool)-BruteForceTolerated ← Diagnostics.BruteForceTolerated, only when a bucket name is settrue when toleration suppressed a triggered bucket; NULL without a bucket.
88reputation_assessmentsArray(Tuple(...)), see belowempty arrayReputationAssessments ([]reputationAssessmentRow) ← record facts decoded by reputationviewOne tuple per (fact, subject record); measured fields are NULL for not_found / unavailable; at most 64 tuples.
89reputation_truncatedBoolDEFAULT falseReputationTruncated ← request.FactsTrimmed, or a record beyond the 64-tuple cap was droppedThe host or the plugin dropped assessment records to stay within bounds. Exactly 64 tuples alone do not set it.

reputation_assessments tuple elements (reputationAssessmentRow):

ElementTypeValue domain
fact, role, profileLowCardinality(String)Policy fact name, subject role, reputation profile
kindLowCardinality(String)ip, network, asn, dns_domain, account, service
stateLowCardinality(String)fresh, stale, not_found, unavailable
bandLowCardinality(String)unknown, trusted, positive, neutral, suspicious, blocked, unavailable
overrideLowCardinality(String)none, trusted, neutral, blocked
risk_score, trust_score, confidence, samplesNullable(Float64)Measured values
source_diversity, age_secondsNullable(Int64)Measured values

Retired Columns of logins​

These columns are not part of the current schema. Tables in the legacy shape carry them until the separately approved retire-legacy-columns block at the end of schema.sql drops them. Types are taken from contrib/plugins/clickhouse/testdata/diagnostic_contract/legacy_logins_schema.sql, except for features, which predates that fixture. The native plugin never writes them (pinned by retired_columns_test.go).

ColumnLegacy typeWriterReplaced by
reputation_score, reputation_positive_score, reputation_negative_score, reputation_ip_score, reputation_asn_score, reputation_country_score, reputation_asn_country_scoreNullable(Float64)nonereputation_assessments
reputation_samplesNullable(UInt64)nonereputation_assessments
reputation_source, reputation_decisionLowCardinality(String)nonereputation_assessments
gp_attempts, gp_unique_ips, gp_unique_usersNullable(UInt64)retired Lua action- (global pattern map)
gp_ips_per_userNullable(Float64)retired Lua action-
prot_activeNullable(Bool)retired Lua action- (account protection map)
prot_reasonLowCardinality(String)retired Lua action-
prot_backoff, prot_delay_msNullable(UInt64)retired Lua action-
dyn_threatNullable(UInt64)retired Lua action- (dynamic response map)
dyn_responseLowCardinality(String)retired Lua action-
featureshistorical (not in the fixture)nonedecision_sources; mentioned only in a schema.sql comment, the retirement block does not drop it

Column arithmetic: the legacy table has 91 columns, the diagnostic-contract migration adds 18, and the retirement block removes 20, which yields the 89 columns of a fresh table.

4.2 nauthilus.auth_events​

  • Purpose: host-observed step events that are not authentication requests: mfa.verification, oidc.code_issuance, oidc.code_redemption (pluginapi/v1/auth_event.go). Never a copy of login rows.
  • DDL: schema.sql, identical to migrations/diagnostic-contract.sql.
  • Engine: ReplacingMergeTree without a version column; ORDER BY (event_type, ts, event_id); no partitioning; TTL toDateTime(ts) + INTERVAL 1 WEEK DELETE.
  • Skip indexes: idx_events_flow_ref (flow_ref, bloom_filter, 64); idx_events_account (account, tokenbf_v1(1024, 3, 0), 64).
  • Replays of one event_id collapse at merge time. Until then they are visible; the support history collapses them in Go (dedupeByID).
  • Writer: authEventRow, filled in one literal by newAuthEventRow (event_sink.go) from pluginapi.AuthEvent. 19 columns.
#ColumnTypeSourceMeaning
1tsDateTime64(3, 'UTC')ObservedAt (UTC ms)Host observation time.
2event_idStringEventIDHost-generated random 128-bit hex; idempotency key.
3event_typeLowCardinality(String)Typemfa.verification, oidc.code_issuance, oidc.code_redemption.
4event_versionUInt16Version1 for all three types; issuance version 1 fixes a 10-minute redemption bound.
5deploymentLowCardinality(String)module configWriter separation.
6instanceLowCardinality(String)module configWriter instance.
7request_idStringRequestIDRuntime.GUID of the observing request; joins logins.session for the same request only.
8flow_refStringFlowRefFlow digest; joins logins.flow_ref; empty for self-service step-ups.
9protocolLowCardinality(String)Protocoloidc, saml, or account (self-service).
10client_idLowCardinality(String)ClientIDOIDC relying party.
11saml_entity_idLowCardinality(String)SAMLEntityIDSAML relying party.
12accountLowCardinality(String)AccountPending or authenticated identity account.
13client_ipStringClientIPBrowser address; empty on oidc.code_redemption (the requester is the relying-party backend).
14factor_roleLowCardinality(String)FactorRolesecond_factor for MFA; empty for completion events (primary is reserved and never written).
15methodLowCardinality(String)Methodtotp, recovery_codes, webauthn; empty for completion events.
16resultLowCardinality(String)Resultsuccess, invalid, error, rate_limited, rejected.
17reasonLowCardinality(String)ReasonStable reason code (issuance: authenticated_in_flow, existing_session, not_recorded; redemption: tokens_issued, client_mismatch, ...).
18flow_effectLowCardinality(String)FlowEffectnone, step_up_completed, primary_failure_kept, code_issued, tokens_issued, code_consumed.
19fail_latchedBoolFailLatchedThe primary step had already failed, so a correct factor does not authenticate. Operator-only meaning together with result = 'success'.

4.3 nauthilus.bruteforce_lifecycle​

  • Purpose: rare ban lifecycle transitions with long retention for ban explanations. No claimed names and no client address.
  • DDL: schema.sql, identical to migrations/bruteforce-evidence.sql.
  • Engine: ReplacingMergeTree; PARTITION BY toYYYYMM(ts); ORDER BY (deployment, network, ts, kind, event_id); TTL toDateTime(ts) + INTERVAL 400 DAY DELETE.
  • Skip index: idx_bfl_bucket (bucket_id, bloom_filter, granularity 4).
  • Kinds: ban_activated, ban_activation_unconfirmed, ban_released, counters_reset (pluginapi/v1/bruteforce_event.go).
  • Writer: bruteForceLifecycleRow = the shared bruteForceEventRow (21 fields) plus 6 lifecycle fields, encoded by encodeBruteForceRow (bruteforce_sink.go) from pluginapi.BruteForceEvent. 27 columns.
#ColumnTypeSourceMeaning
1tsDateTime64(3, 'UTC')ObservedAt (UTC ms)Host observation time.
2event_idStringEventIDEvent identity; on ban_activated it is the ban_ref of the ban instance.
3event_versionUInt16Version1.
4kindLowCardinality(String)KindLifecycle kind (see above).
5deploymentLowCardinality(String)module configWriter separation; sort-key prefix.
6instanceLowCardinality(String)module configWriter instance.
7request_idStringRequestIDJoin key to attempt rows and the login row of the activating request (never "the culprit").
8networkStringNetworkBanned network (CIDR text); the ban key.
9ip_familyUInt8IPFamily4 or 6.
10cidrUInt8CIDRPrefix length of the rule.
11ruleLowCardinality(String)RuleRule name. On wildcard ban_released rows it is the first configured rule sharing the ban key (not authoritative).
12rule_fingerprintLowCardinality(String)RuleFingerprintSHA-256 over rule name, period, cidr, failed_requests, IP families, sorted filters, and effective ban time.
13stored_ruleLowCardinality(String)StoredRuleRule value stored in the Redis ban key (lifecycle rows only).
14bucket_idStringBucketIDSHA-256 of the window-less Redis bucket key of the activating rule.
15period_secondsInt64PeriodSecondsRule period.
16ban_time_secondsInt64BanTimeSecondsEffective ban time.
17failed_requestsInt64FailedRequestsRule threshold parameter.
18window_indexNullable(Int64)WindowIndexEpoch-aligned window number of the activating pre-check.
19window_currentNullable(Int64)CurrentPre-check counter of the current window; 1..window_current is the closed set of counted increments.
20window_previousNullable(Int64)PreviousPre-check counter of the previous window.
21totalNullable(Float64)Totalcurrent + previous * weight at the pre-check.
22effective_limitNullable(Float64)Limitfailed_requests - 1, scaled by positive reputation.
23expires_atNullable(DateTime64(3, 'UTC'))ExpiresAtPlanned ban end.
24release_sourceLowCardinality(String)ReleaseSourceadmin_flush, user_flush.
25operator_client_idLowCardinality(String)OperatorClientIDOperator reference of a release (detail projection only).
26operator_auth_methodLowCardinality(String)OperatorAuthMethodOperator authentication method.
27keys_removedNullable(Int64)KeysRemovedRedis keys whose removal Redis confirmed (release or reset).

4.4 nauthilus.bruteforce_attempts​

  • Purpose: raw counted, tolerated, and ban-hit events with short retention. Daily partitions expire whole.
  • DDL: schema.sql, identical to migrations/bruteforce-evidence.sql.
  • Engine: ReplacingMergeTree; PARTITION BY toDate(ts); ORDER BY (deployment, network, bucket_id, ts, event_id); TTL toDate(ts) + INTERVAL 14 DAY DELETE; SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1.
  • Skip indexes: idx_bfa_client_ip (client_ip, bloom_filter, 4); idx_bfa_request (request_id, bloom_filter, 4).
  • Kinds: counted and threshold_tolerated (stream bfCounted), ban_hit (stream bfHits) (bruteForceStreamFor).
  • Writer: bruteForceAttemptRow = the shared 21 fields plus 13 volume fields (bruteforce_sink.go). 34 columns.
#ColumnTypeSourceMeaning
1tsDateTime64(3, 'UTC')ObservedAtHost observation time.
2event_idStringEventIDEvent identity; replays share it.
3event_versionUInt16Version1.
4kindLowCardinality(String)Kindcounted, threshold_tolerated, ban_hit.
5deploymentLowCardinality(String)module configWriter separation; sort-key prefix.
6instanceLowCardinality(String)module configWriter instance.
7request_idStringRequestIDRequest GUID; joins logins.session.
8client_ipStringClientIPClient address (volume lanes only).
9networkStringNetworkBucket network.
10ip_familyUInt8IPFamily4 or 6.
11cidrUInt8CIDRPrefix length.
12ruleLowCardinality(String)RuleRule; on hits the first matching candidate rule, which can differ from the activating rule.
13rule_fingerprintLowCardinality(String)RuleFingerprintSee 4.3.
14bucket_idStringBucketIDSHA-256 of the window-less bucket key; sort-key part.
15bucket_protocolLowCardinality(String)BucketProtocolProtocol scope of the bucket when the rule filters on it.
16bucket_oidc_clientLowCardinality(String)BucketOIDCClientOIDC client scope of the bucket.
17period_secondsInt64PeriodSecondsRule period.
18ban_time_secondsInt64BanTimeSecondsEffective ban time.
19failed_requestsInt64FailedRequestsRule threshold parameter.
20window_indexNullable(Int64)WindowIndexWindow of the increment; NULL when the pre-check did not read counters (for example an existing ban).
21weightNullable(Float64)Weight1 - (now % period) / period.
22window_currentNullable(Int64)CurrentCounter after this increment; grows by one per counted increment of a bucket window.
23window_previousNullable(Int64)PreviousPrevious-window counter.
24totalNullable(Float64)TotalWeighted total.
25effective_limitNullable(Float64)LimitEffective limit.
26crossedNullable(Bool)Crossedtotal - 1 <= effective_limit < total for this increment (heuristic).
27count_sourceLowCardinality(String)CountSourcebackend_failure, pre_auth_reject, protected_endpoint, policy_update:<feature>.
28ban_activeNullable(Bool)BanActiveThe request itself hit an existing ban; NULL when not checked.
29claimed_usernameStringClaimedUsernameLogin name as entered; a claim that can contain secrets typed by users.
30accountStringAccountAccount from a consulted mapping.
31account_knownNullable(Bool)AccountKnownTri-state; NULL when no mapping was consulted.
32protocolLowCardinality(String)ProtocolRequest protocol.
33oidc_client_idLowCardinality(String)OIDCClientIDRequest OIDC client.
34hit_sourceLowCardinality(String)HitSourceredis, l1, activation_race (ban_hit only).

4.5 nauthilus.bruteforce_attempt_summary​

  • Purpose: hourly aggregate of attempts that outlives the 14-day raw rows. Distinct names are kept as HLL states only.
  • DDL: schema.sql, identical to migrations/bruteforce-evidence.sql.
  • Engine: AggregatingMergeTree; PARTITION BY toYYYYMM(hour); ORDER BY (deployment, network, bucket_id, kind, window_index, hour, rule); TTL hour + INTERVAL 400 DAY DELETE. No skip indexes.
  • Written only by bruteforce_attempt_summary_mv. 15 columns.
#ColumnTypeMV expressionMeaning
1hourDateTime('UTC')toStartOfHour(ts)Hour bucket.
2deploymentLowCardinality(String)deploymentWriter separation.
3networkStringnetworkBucket network.
4bucket_idStringbucket_idBucket.
5kindLowCardinality(String)kindVolume kind.
6ruleLowCardinality(String)ruleRule.
7window_indexInt64ifNull(window_index, -1)Window; -1 when the raw row had none.
8eventsSimpleAggregateFunction(sum, UInt64)count()Row count (upper bound under replay).
9ban_active_eventsSimpleAggregateFunction(sum, UInt64)countIf(ban_active = true)Rows that hit an active ban.
10min_currentSimpleAggregateFunction(min, Nullable(Int64))min(window_current)Lowest counter (exact under replay).
11max_currentSimpleAggregateFunction(max, Nullable(Int64))max(window_current)Highest counter (exact under replay).
12first_tsSimpleAggregateFunction(min, DateTime64(3, 'UTC'))min(ts)First event in the hour.
13last_tsSimpleAggregateFunction(max, DateTime64(3, 'UTC'))max(ts)Last event in the hour.
14claimed_namesAggregateFunction(uniq, String)uniqStateIf(claimed_username, claimed_username != '')Distinct claimed names (HLL state; hashed, but still testable for a given name).
15accountsAggregateFunction(uniq, String)uniqStateIf(account, account != '')Distinct accounts (HLL state).

The in-repository readers use only events, min_current, max_current, and claimed_names; ban_active_events, first_ts, last_ts, and accounts are written by the view for custom queries; no in-repository reader uses them.

5. Materialized View​

nauthilus.bruteforce_attempt_summary_mv:

  • Source: every insert block into nauthilus.bruteforce_attempts (all kinds). There is no POPULATE; rows inserted before the view existed are not aggregated. Both objects are created by the same file.
  • Target: TO nauthilus.bruteforce_attempt_summary.
  • Aggregation: GROUP BY hour, deployment, network, bucket_id, kind, rule, window_index with hour = toStartOfHour(ts) and window_index = ifNull(window_index, -1). The group key equals the target sort key, so background merges fold partial blocks.
  • Readers must finalize the states: sum(events), min(min_current), max(max_current), uniqMerge(claimed_names).
  • Replay semantics: the view fires per insert, before ReplacingMergeTree collapses replays in the source table. events and ban_active_events are therefore upper bounds; min_current and max_current stay exact.
  • Completeness beyond raw retention: readers compare events with max_current - min_current + 1 per bucket window to report an aggregate-only explanation.

6. Access Model​

6.1 ClickHouse Objects (history-reader.sql)​

ObjectDefinition
Settings profile nauthilus_history_profilereadonly = 2 CONST; max_execution_time = 8 MAX 8; max_rows_to_read = 5000000 MAX 5000000; max_bytes_to_read = 2147483648 MAX 2147483648; read_overflow_mode, result_overflow_mode, timeout_overflow_mode = 'throw' CONST; cancel_http_readonly_queries_on_client_close = 1
User nauthilus_historyIDENTIFIED WITH sha256_password, SETTINGS PROFILE 'nauthilus_history_profile'
GrantsSELECT on logins, auth_events, bruteforce_lifecycle, bruteforce_attempts, bruteforce_attempt_summary
Roles, row policies, column grants, quotasnone

In the default budget_mode: query_settings the service sends its own budget values with each query, so the profile's MAX values must be at least the configured storage.history.clickhouse budgets; a larger configured value makes ClickHouse refuse every query (code 452, reader unsafe). For budget_mode: profile the file instructs readonly = 1 CONST instead. The writer account and its INSERT grants are not provisioned by the repository; the plugin takes user / password from its module config. A separate Grafana account must not be granted the brute-force tables.

6.2 Reader Configuration​

Path storage.history.clickhouse (server/config/history.go):

KeyDefaultNotes
urlemptyEmpty disables the history store. Must not carry query parameters or credentials.
databasenauthilusPlain identifier.
username, deployment-Both required when url is set.
password-Sent as X-ClickHouse-Key.
budget_modequery_settingsquery_settings or profile.
timeout15 sTime budget of one history operation (all its queries).
max_execution_time8 sMust be shorter than timeout. The HTTP client timeout is max_execution_time + 2 s.
max_rows_to_read5,000,000
max_bytes_to_read2 GiB
max_response_bytes8 MiBMaximum 64 MiB.

Wiring: server/support_history_wiring.go. Changes need a restart.

6.3 Enforcement in the Reader​

The reader enforces its boundaries in application code (server/supporthistory/clickhousestore):

  • Read-only self-check before reading, cached for 5 minutes: reads getSetting('readonly'), the three budgets, and the three overflow modes. It refuses readonly < 1 or any overflow mode other than throw; in profile mode it also refuses missing or larger-than-configured budgets (SelfCheck in store.go).
  • In query_settings mode every query carries max_execution_time (whole seconds), max_rows_to_read, max_bytes_to_read, and the three overflow modes (client.go).
  • Response bodies are capped at max_response_bytes; incomplete or exception bodies are errors, never empty results. Error classification: codes 158/159/160/307/396 mean budget exceeded, 60/81 mean source not installed, 164/452 mean reader unsafe (classify in client.go).
  • Values never enter SQL text: typed {pN:Type} parameters are sent as param_pN (query.go).
  • Deployment boundary: every data statement binds deployment as a typed parameter, as the first condition of each SELECT (query.go; the auth_events half of a UNION uses a later parameter name). Only the parameterless SelfCheck has no deployment predicate. ClickHouse itself does not enforce this boundary.
  • Operation and event boundary: every logins read applies operation IN ('authenticate', '') (operationFilter in attempts.go), so lookup_identity and list_accounts rows are never visible to the history service. Every auth_events read applies event_type IN ('mfa.verification', 'oidc.code_issuance', 'oidc.code_redemption') (stepEventFilter).
  • Column boundary: fixed projections. logins reads only the 36 columns in loginColumns (attempts.go) plus the deployment filter. It never reads password_hash, client_port, user_agent, hostname, pwnd_info, display_name, the local address and port, TLS, or GeoIP columns.

6.4 Visibility per Scope​

ScopeProjectionVisible
nauthilus:history_readsupportAttempts of a named subject (events of other identities are redacted); the answered status text only together with the identity; ban search with counts and distinct-name counts; redacted contributions; no claimants.
nauthilus:history_detaildetailAdditionally other people's claimed names, accounts, and client IPs behind shared addresses, claimants, operator IDs of releases, factor events of fail-latched flows, and detail lookups without a subject.

nauthilus:admin, nauthilus:security, and the unrestricted Basic credential grant no history access; a Bearer token or a scoped Basic credential may hold the two scopes. With include_ban_context, the attempt search also links brute-force-rejected subject requests to recorded ban instances and returns attribution counts (ban_context.go, ban_attribution.go); identities stay redacted outside the detail projection.

7. Schema Files, Migrations, and Versions​

7.1 Files and Order​

StepFileWhat it doesIdempotency
Fresh installschema.sql (create block)Creates the database, all five tables, and the view in their current shape.IF NOT EXISTS; never migrates an existing table.
Historical upgradeschema.sql ("Legacy upgrade guide" block)MODIFY COLUMN to LowCardinality and codecs; ADD COLUMN IF NOT EXISTS for deployment, instance, decision_sources, rbl_lists, method, latency, http_status, status_msg, saml_entity_id, grant_type, mfa_method, the extended GeoIP/ASN columns, all geoip_privacy_* / geoip_is_* columns, and rwp.Mostly repeatable; MODIFY starts mutations.
Diagnostic contractmigrations/diagnostic-contract.sqlAdds deployment, instance (no-op where present), the 18 contract columns, and idx_flow_ref (without MATERIALIZE INDEX); creates auth_events; ends with system.columns verification queries.Additive and repeatable.
Brute-force evidencemigrations/bruteforce-evidence.sqlCreates bruteforce_lifecycle, bruteforce_attempts, bruteforce_attempt_summary, and the view.Additive and repeatable.
Column retirement (optional, separately approved)schema.sql (retire-legacy-columns block)DROP COLUMN IF EXISTS for the 20 retired columns, after all writers run the cleaned export, old batches are drained, readers no longer reference the columns, and a backup exists.Repeatable; irreversible for the data.
Read accesshistory-reader.sqlProfile, user, grants.IF NOT EXISTS for profile and user; GRANT is repeatable.
Kubernetes bootstrapk8s-job.yamlConfigMap with an embedded logins DDL and a Job that runs it with clickhouse-client --multiquery. The embedded DDL is outdated: a 69-column logins without deployment, instance, the contract columns, and idx_flow_ref, and none of the other tables or the view. Apply migrations/diagnostic-contract.sql and migrations/bruteforce-evidence.sql after it, or use schema.sql instead.IF NOT EXISTS.

Rollout rule: apply a migration before deploying a writer that emits new fields, then append &input_format_skip_unknown_fields=0 to the insert URLs. Keep added columns and tables on an application rollback.

7.2 Versions​

VersionWhereValues
contract_versionlogins0 legacy (column default, also every Lua row), 1 diagnostic contract (loginContractVersion). The cutover boundary is the first ts with contract_version >= 1 per deployment (ContractRowsSince).
event_versionauth_events1 for MFA and both OIDC completion types; readers reject other versions.
event_versionbruteforce_lifecycle, bruteforce_attempts1.
Dedup key versionRedisclickhouse:authdedup:v3:.

Legacy semantics inside the current schema: geoip_guid has no producer, authenticated is superseded by outcome, and http_status / status_msg on contract_version = 0 rows are capture-time values that may misreport tempfails.

8. Reader Query Patterns​

8.1 Support History Service​

All statements are sent as POST <url>?param_pN=...[&budget settings] with the body <SQL> FORMAT JSON. Every data statement binds deployment first, every logins read adds operationFilter, and every auth_events read adds stepEventFilter (section 6.3); the table lists only the additional predicates.

Store methodTablePredicates and shapeRelies on
SelfCheck (store.go)noneSELECT getSetting(...)settings profile
Presence (store.go)each of the fiveSELECT 1 ... LIMIT 1; logins / auth_events restricted to the last 7 days (events also by event_type)primary-key prefix
ContractRowsSince (store.go)loginsts range, contract_version >= 1, ORDER BY ts LIMIT 1primary key ts
CompletionEventsSince (store.go)auth_eventsevent_type IN ('oidc.code_issuance','oidc.code_redemption'), last 7 days, ORDER BY ts LIMIT 1primary key (event_type, ts)
Anchors (attempts.go)logins UNION ALL auth_eventsSubject by username IN / account IN, optional client_ip, proto, oidc_cid, saml_entity_id; grouped by attempt key if(flow_ref != '', flow_ref, session / request_id); keyset paging on (anchor_ms, key_value)ts, idx_username, idx_account, idx_client_ip, idx_events_account
RecordedAccounts (attempts.go)loginsusername IN, account != '', SELECT DISTINCT accountts, idx_username
LoginRows (attempts.go)loginsflow_ref IN or (flow_ref = '' AND session IN ...); LIMIT k BY <attempt key>; 36 columns; replays are collapsed afterwards in Go (dedupeByID)ts, idx_flow_ref
AuthEvents (attempts.go)auth_eventsflow_ref IN or (flow_ref = '' AND request_id IN ...); same LIMIT BY shape and Go-side replay collapseprimary key, idx_events_flow_ref, idx_events_account
readPrefixes (bans.go)bruteforce_lifecycle, bruteforce_attempt_summarySELECT DISTINCT ip_family, cidr ... LIMIT 256; SELECT DISTINCT network ... LIMIT 4096primary-key prefix deployment
Lifecycle (bans.go)bruteforce_lifecyclenetwork IN, ts range; LIMIT 1 BY event_id, then LIMIT perNetwork BY network(deployment, network, ts), monthly partitions
Contributions (bans.go)bruteforce_attemptsnetwork, bucket_id, kind = 'counted', counter ranges of the current and previous window; LIMIT 1 BY event_id(deployment, network, bucket_id, ts), daily partitions
RequestHits (bans.go)bruteforce_attemptsnetwork IN, kind = 'ban_hit', request_id IN, hits within 5 minutes; LIMIT 1 BY event_id(deployment, network), idx_bfa_request
WindowSummaries (bans.go)bruteforce_attempt_summarynetwork, bucket_id, kind, window_index IN; GROUP BY window_index with sum / min / max / uniqMergefull sort-key prefix
KindCounts (bans_counts.go)bruteforce_attemptsnetwork IN, kind, ts, optional interval exclusion; count(), uniqExactIf(claimed_username, ...), sumMap([hit_source], [1])(deployment, network)
claimants (bans_counts.go)bruteforce_attemptssame inner query, GROUP BY claimed_username, account ORDER BY hits DESC; detail projection only(deployment, network)
summaryCounts (bans_counts.go)bruteforce_attempt_summarynetwork IN, kind, hour range; sum(events), uniqMerge(claimed_names)sort-key prefix, monthly partitions

Retention constants in the reader mirror the TTLs (limits.go): logins and events 7 days, raw brute-force rows 14 days, lifecycle 400 days.

Join keys: logins.flow_ref = auth_events.flow_ref (non-empty), and logins.session = auth_events.request_id = bruteforce_*.request_id for the same request. Never join by time proximity.

8.2 Grafana Dashboard​

All panels query FROM ${log_table:raw} with the constant variable log_table = nauthilus.logins. The time filter is $__timeFilter_ms(ts). Success and failure are derived from authenticated; outcome and contract_version are not used, and there is no deployment variable. Many panels and the variables reputation_band and reputation_state depend on reputation_assessments, so the dashboard needs the diagnostic-contract migration.

8.3 Lua Query Hook​

server/lua-plugins.d/hooks/clickhouse-query.lua is an operator tool configured through CLICKHOUSE_SELECT_BASE, CLICKHOUSE_TABLE (default nauthilus.logins), CLICKHOUSE_USER, and CLICKHOUSE_PASSWORD. It offers the actions recent, by_user, by_account, and by_ip (raw_sql is refused), filters success by authenticated, and relies on the primary key ts and the tokenbf_v1 indexes. It has no deployment filter.

9. Design Notes for Developers​

  • No exactly-once delivery. Use event_id to collapse duplicates: ReplacingMergeTree tables merge them eventually, while logins keeps them. The brute-force queries apply LIMIT 1 BY event_id in SQL; the login and step-event reads collapse replays in Go (dedupeByID).
  • Adding a column requires the DDL in schema.sql (fresh install), an additive migration under contrib/clickhouse-kubernetes/migrations/, the writer struct field, and the contract integration test that compares fresh and migrated tables. Writers must never emit a field before the migration ran in production.
  • Removing a column goes through the retirement process: stop writing it, drain buffered batches, stop reading it, then drop it in a separately approved block.
  • Boundaries live in the reader. The deployment and column boundaries of the history service are application-level. A new reader must apply the same deployment filter and must not project password_hash.
  • Join by request or flow, not by time. session / request_id join rows of one request; flow_ref joins rows of one IdP flow.