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:
- DDL, in the repository:
contrib/clickhouse-kubernetes/schema.sql(fresh install),contrib/clickhouse-kubernetes/migrations/*.sql(existing installations),contrib/clickhouse-kubernetes/history-reader.sql(read-only account). - Writer contract: ClickHouse plugin reference; the full contract is
contrib/plugins/clickhouse/DIAGNOSTIC_CONTRACT.mdin the repository. - Reader: Support Authentication History and Read-only Support Access over MCP.
- Redis keys used by the ClickHouse plugin: Redis Keyspace Reference.
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 viewnauthilus.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
| Writer | Status | Tables | Code |
|---|---|---|---|
Native Go plugin clickhouse (Policy post-action, AuthEventSink, BruteForceEventSink) | Active | logins, auth_events, bruteforce_lifecycle, bruteforce_attempts | contrib/plugins/clickhouse/*.go |
Materialized view bruteforce_attempt_summary_mv | Active (server side) | bruteforce_attempt_summary | contrib/clickhouse-kubernetes/schema.sql |
Lua action server/lua-plugins.d/actions/clickhouse.lua | Retired, reference only; the production Policy VM rejects it | logins (legacy column subset only) | server/lua-plugins.d/actions/clickhouse.lua |
1.2 Readers
| Reader | Tables | Code |
|---|---|---|
Support history service (HTTP /api/v1/support/* and the MCP tools share one instance) | all five tables | server/supporthistory/clickhousestore/*.go, consumers in server/supporthistory/*.go |
| Grafana dashboard | logins | contrib/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_events | contrib/identity-proxy-e2e/scripts/run.sh |
Integration tests (test only, build tag clickhouse_integration) | all | contrib/plugins/clickhouse/*_integration_test.go |
1.3 Data Flow
2. Object Summary
Database: nauthilus.
| Object | Kind | Written by | Read by | Purpose |
|---|---|---|---|---|
nauthilus.logins | MergeTree, ORDER BY (ts), TTL 1 week | native login stream; retired Lua action | support history, Grafana, Lua query hook | One row per authentication request whose Policy rule selects the analytics post-action; terminal outcome under contract version 1 |
nauthilus.auth_events | ReplacingMergeTree, ORDER BY (event_type, ts, event_id), TTL 1 week | native event stream | support history, E2E script | Second-factor verifications and OIDC code issuance/redemption |
nauthilus.bruteforce_lifecycle | ReplacingMergeTree, monthly partitions, TTL 400 days | native bfLifecycle stream | support history | Ban activations, unconfirmed activations, releases, counter resets |
nauthilus.bruteforce_attempts | ReplacingMergeTree, daily partitions, TTL 14 days, ttl_only_drop_parts | native bfCounted and bfHits streams | support history | Raw counted, tolerated, and ban-hit events |
nauthilus.bruteforce_attempt_summary | AggregatingMergeTree, monthly partitions, TTL 400 days | bruteforce_attempt_summary_mv | support history | Hourly aggregate that outlives the raw attempt rows |
nauthilus.bruteforce_attempt_summary_mv | Materialized view TO bruteforce_attempt_summary | ClickHouse on every insert into bruteforce_attempts | not read directly | Feeds 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, otherwiseX-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.000UTC text (clickHouseTimestampLayout,utcNowMillisinrow.go).
3.2 Streams
Configuration: config.go, stream wiring: stream.go, plugin.go.
| Stream | Table | Insert URL key | Buffer key (default) | Inline flush | Periodic flush |
|---|---|---|---|---|---|
logins | logins | insert_url | cache_key (clickhouse:batch:logins) | In Enqueue when the buffer reaches min(batch_size, max_buffer_rows) | flush_interval, default 0 (disabled) |
events | auth_events | events_insert_url | events_cache_key (clickhouse:batch:auth_events) | never | events_flush_interval, default 10 s; the worker runs only while the URL is set |
bfLifecycle | bruteforce_lifecycle | bruteforce_lifecycle_insert_url | bruteforce_lifecycle_cache_key (clickhouse:batch:bruteforce_lifecycle) | never | bruteforce_flush_interval, default 10 s; one worker shared by the three brute-force streams |
bfCounted | bruteforce_attempts (kinds counted, threshold_tolerated) | bruteforce_attempts_insert_url | bruteforce_counted_cache_key (clickhouse:batch:bruteforce_counted) | From the host lane goroutine, bounded by timeout and the host delivery deadline | shared brute-force worker |
bfHits | bruteforce_attempts (kind ban_hit) | bruteforce_attempts_insert_url | bruteforce_hits_cache_key (clickhouse:batch:bruteforce_hits) | same as bfCounted | shared 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_rowsrows (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_rejectmay fill at mostmax_brute_force_reject_rows(defaultmax_buffer_rows / 2) (reject_share.go), so a brute-force wave cannot crowd out other rows. - The inline flush of
logins,bfCounted, andbfHitstriggers when the buffer reachesmin(batch_size, max_buffer_rows). A flush pops the whole buffer (PopAll), notbatch_sizerows. - A full login buffer drops the new row and triggers a flush attempt.
- With an empty insert URL,
eventsand the three brute-force streams drop events at admission, whileloginskeeps buffering up tomax_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).Stopflushes every stream once, bounded bytimeout. - Rows are buffered already encoded, so
event_idandtsstay 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:
okusesdedup_success(defaulttrue),failusesdedup_failure(defaultfalse);tempfailandunknownare never deduplicated. - Mechanism: Redis
SET NX EX <auth_dedup_ttl>(default 300 s) onclickhouse: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, andflow_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
| Data | Treatment |
|---|---|
| Password | Never 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, hostname | Stored in clear text in logins. |
| Claimed login names | Stored 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 handle | Never stored. flow_ref = hex(sha256("nauthilus.flow_ref.v1\x00" + flow_id))[0:32]. |
| Rule and bucket identity | rule_fingerprint and bucket_id are SHA-256 digests computed by the host. |
| Step events | No credentials, OTPs, recovery codes, authorization codes, PKCE verifiers, tokens, cookies, handles, or WebAuthn data. |
| Dedup key | SHA-256 digest only. |
| Logs, metrics, spans | No 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.
| # | Column | Type | Default / codec | Source (native writer) | Meaning |
|---|---|---|---|---|---|
| 1 | ts | DateTime64(3, 'UTC') | - | TS = utcNowMillis() at encode time | Enqueue time after the response commit (UTC, ms). Primary key. |
| 2 | session | String | - | Session ← Snapshot.Session | Per-request correlation ID (Runtime.GUID). Joins auth_events.request_id and bruteforce_*.request_id of the same request. Not a flow ID. |
| 3 | service | LowCardinality(String) | ZSTD(3) | Service ← Snapshot.Service | Auth endpoint service (for example idp). |
| 4 | deployment | LowCardinality(String) | DEFAULT '', ZSTD(3) (tables migrated from the legacy shape have no default) | Deployment ← module config deployment | Writer separation. The support history reader binds every query to it. |
| 5 | instance | LowCardinality(String) | DEFAULT '', ZSTD(3) (no default on migrated tables) | Instance ← module config instance | Writer instance (pod) label. |
| 6 | decision_sources | LowCardinality(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. |
| 7 | rbl_lists | Array(LowCardinality(String)) | - | RBLLists ← Diagnostics.RBLMatchedLists (nil becomes []) | Every configured RBL list that listed the client IP, including matches below the threshold. |
| 8 | client_ip | String | - | ClientIP ← Snapshot.ClientIP | Client address as supplied. |
| 9 | client_port | String | - | ClientPort | Client port. |
| 10 | client_net | LowCardinality(String) | - | ClientNet | Brute-force client network chosen by the brute-force path; empty when brute force did not run. |
| 11 | client_id | LowCardinality(String) | - | ClientID | Caller-supplied client ID (X-Client-ID). |
| 12 | hostname | LowCardinality(String) | ZSTD(3) | Hostname ← Snapshot.ClientHost | Client host name (caller-supplied or reverse DNS). |
| 13 | proto | LowCardinality(String) | - | Proto ← Snapshot.Protocol | Protocol (imap, smtp, oidc, ...). |
| 14 | method | LowCardinality(String) | - | Method | Authentication method. |
| 15 | user_agent | LowCardinality(String) | ZSTD(5) | UserAgent | Client user agent. |
| 16 | local_ip | LowCardinality(String) | - | LocalIP | Local (server) address. |
| 17 | local_port | String | - | LocalPort | Local port. |
| 18 | display_name | LowCardinality(String) | - | DisplayName | Resolved display name; empty for unknown users. |
| 19 | account | LowCardinality(String) | - | Account | Resolved account; empty when unknown or not yet looked up at capture time. |
| 20 | username | LowCardinality(String) | - | Username | Login name as sent (a claim). |
| 21 | password_hash | String | - | PasswordHash ← clickHousePasswordHash(...) | 8-hex prefix of the nonce-prepared SHA-256 password digest; empty without a password. History APIs must not expose it. |
| 22 | pwnd_info | LowCardinality(String) | - | PwndInfo ← exchange HIBPHashInfo() | Have-I-Been-Pwned prefix and count; empty when HIBP did not run before this post-action. |
| 23 | brute_force_bucket | LowCardinality(String) | - | BruteForceBucket ← Diagnostics.BruteForceName | Bucket that went over its limit; also set when toleration suppressed the block. |
| 24 | brute_force_counter | Nullable(UInt64) | - | BruteForceCounter ← Diagnostics.BruteForceCounter | Bucket counter at capture time; 0 when no bucket. The native writer never writes NULL. |
| 25 | oidc_cid | LowCardinality(String) | - | OIDCCID | OIDC client ID (IdP context). |
| 26 | saml_entity_id | LowCardinality(String) | - | SAMLEntityID | SAML relying-party entity ID. |
| 27 | grant_type | LowCardinality(String) | - | GrantType ← Snapshot.IDP.GrantType | OIDC grant type. |
| 28 | mfa_method | LowCardinality(String) | - | MFAMethod ← Snapshot.IDP.MFAMethod | Second factor completed for this request; empty for password logins. MFA history lives in auth_events. |
| 29 | failed_login_count | Nullable(UInt64) | - | FailedLoginCount ← exchange failed_login_hotspot.count or .new_count (applyFailedLoginInfo) | Failed-login hotspot count; NULL without a producer. |
| 30 | failed_login_rank | Nullable(UInt64) | - | FailedLoginRank ← .rank | Hotspot rank. |
| 31 | failed_login_recognized | Nullable(Bool) | - | FailedLoginRecognized ← .recognized_account | The hotspot recognized the account. |
| 32 | geoip_guid | LowCardinality(String) | - | GeoIPGUID ← GeoIP field guid (applyGeoIPInfo) | Legacy exchange GUID; no active producer. |
| 33 | geoip_country | LowCardinality(String) | - | GeoIPCountry ← country_iso or current_country_code, normalized to ISO alpha-2 | Country of the client IP. |
| 34 | geoip_iso_codes | LowCardinality(String) | - | GeoIPISOCodes ← iso_codes_seen plus the country, comma-joined | Distinct ISO codes seen. |
| 35 | geoip_status | LowCardinality(String) | - | GeoIPStatus ← status, else matched / miss (geoIPStatus) | GeoIP lookup status. |
| 36 | geoip_source | LowCardinality(String) | - | GeoIPSource ← source, else native_geoip | GeoIP evidence source. |
| 37 | geoip_matched | Nullable(Bool) | - | GeoIPMatched ← matched | The lookup matched. |
| 38 | geoip_country_name | LowCardinality(String) | - | GeoIPCountryName | Country name. |
| 39 | geoip_city_name | LowCardinality(String) | - | GeoIPCityName | City name (empty with a country-only database). |
| 40 | geoip_asn | Nullable(UInt64) | - | GeoIPASN | Autonomous system number. |
| 41 | geoip_asn_org | LowCardinality(String) | - | GeoIPASNOrg | AS organisation. |
| 42 | geoip_asn_prefix | LowCardinality(String) | - | GeoIPASNPrefix | Announced prefix. |
| 43 | geoip_asn_registry | LowCardinality(String) | - | GeoIPASNRegistry | Regional internet registry of the AS. |
| 44 | geoip_asn_country | LowCardinality(String) | - | GeoIPASNCountry ← asn_country_iso | AS country (ISO). |
| 45 | geoip_asn_allocated | LowCardinality(String) | - | GeoIPASNAllocated | AS allocation date. |
| 46 | geoip_asn_status | LowCardinality(String) | - | GeoIPASNStatus | AS status. |
| 47 | geoip_privacy_lookup_state | LowCardinality(String) | - | GeoIPPrivacyLookupState ← GeoIPAnalytics.Privacy.LookupState (applyGeoIPPrivacyInfo) | Privacy-intelligence lookup state. |
| 48 | geoip_privacy_detected | Nullable(Bool) | - | GeoIPPrivacyDetected | Any privacy class detected; NULL without evidence. |
| 49 | geoip_privacy_classes | Array(String) | - | GeoIPPrivacyClasses (always an array) | Detected privacy classes; [] when none. |
| 50 | geoip_privacy_primary_class | LowCardinality(String) | - | GeoIPPrivacyPrimaryClass | Primary privacy class. |
| 51 | geoip_privacy_confidence | Nullable(Float64) | - | GeoIPPrivacyConfidence | Classification confidence. |
| 52 | geoip_privacy_source_authorities | Array(String) | - | GeoIPPrivacySourceAuthorities (always an array) | Evidence authorities; [] when none. |
| 53 | geoip_privacy_data_stale | Nullable(Bool) | - | GeoIPPrivacyDataStale | The evidence data is stale. |
| 54 | geoip_privacy_data_age_seconds | Nullable(UInt64) | - | GeoIPPrivacyDataAgeSeconds | Evidence age in seconds. |
| 55 | geoip_is_tor_exit_node | Nullable(Bool) | - | GeoIPIsTorExitNode | Tor exit evidence. |
| 56 | geoip_is_known_vpn_exit | Nullable(Bool) | - | GeoIPIsKnownVPNExit | Known VPN exit evidence. |
| 57 | geoip_is_community_vpn_exit | Nullable(Bool) | - | GeoIPIsCommunityVPNExit | Community VPN exit evidence. |
| 58 | geoip_is_public_proxy | Nullable(Bool) | - | GeoIPIsPublicProxy | Public proxy evidence. |
| 59 | geoip_is_privacy_relay | Nullable(Bool) | - | GeoIPIsPrivacyRelay | Privacy relay evidence. |
| 60 | geoip_is_hosting_network | Nullable(Bool) | - | GeoIPIsHostingNetwork | Hosting network evidence (separate from VPN). |
| 61 | geoip_is_shared_egress | Nullable(Bool) | - | GeoIPIsSharedEgress | Shared egress evidence; intended for custom queries. |
| 62 | repeating | Nullable(Bool) | - | Repeating ← Snapshot.Runtime.Repeating | Brute-force repeat hint; false when brute force did not run. Weak, capture-time signal. |
| 63 | rwp | Nullable(Bool) | - | RWP | Repeated-wrong-password hint. Weak, capture-time signal. |
| 64 | user_found | Nullable(Bool) | - | UserFound | Account resolved at capture time; also false when the lookup had not run yet. |
| 65 | authenticated | Nullable(Bool) | - | Authenticated | Runtime.Authenticated at capture; false for fail and tempfail. Superseded by outcome as the outcome carrier. |
| 66 | xssl_protocol | LowCardinality(String) | - | XSSLProtocol ← TLS.Legacy.Protocol | TLS protocol reported by the proxy. |
| 67 | xssl_cipher | LowCardinality(String) | - | XSSLCipher ← TLS.Legacy.CipherSuite | TLS cipher reported by the proxy. |
| 68 | ssl_fingerprint | LowCardinality(String) | - | SSLFingerprint ← TLS.Legacy.Fingerprint | Client TLS fingerprint header. |
| 69 | latency | UInt64 | - | Latency ← Diagnostics.LatencyMillis (negative becomes 0) | Milliseconds from request start to capture; 0 when unknown. |
| 70 | http_status | UInt16 | - | HTTPStatus ← Diagnostics.HTTPStatus, refreshed from the sealed outcome | Host status code configured for the terminal decision; 0 when the outcome is unknown. |
| 71 | status_msg | LowCardinality(String) | - | StatusMessage ← Diagnostics.StatusMessage (late-bound) | Final client status text; empty when the outcome is unknown. |
| 72 | contract_version | UInt16 | DEFAULT 0 | ContractVersion = loginContractVersion (1) | 0 legacy row, 1 diagnostic data contract. |
| 73 | event_id | String | DEFAULT '' | EventID ← newEventID() (128-bit random hex) | Row identity; stable across requeues; identifies duplicates. |
| 74 | operation | LowCardinality(String) | DEFAULT '' | Operation ← Outcome.Operation | Policy operation: authenticate, lookup_identity, list_accounts; empty when unknown. |
| 75 | outcome | LowCardinality(String) | DEFAULT '' | Outcome ← Outcome.Decision, or unknown when unsealed | ok, fail, tempfail, unknown; empty for legacy rows. |
| 76 | outcome_stage | LowCardinality(String) | DEFAULT '' | OutcomeStage | Policy stage of the presenting rule (pre_auth, auth_decision, ...). |
| 77 | outcome_marker | LowCardinality(String) | DEFAULT '' | OutcomeMarker | Policy outcome marker (for example auth.outcome.auth_failure, auth.outcome.brute_force_reject). |
| 78 | response_marker | LowCardinality(String) | DEFAULT '' | ResponseMarker | Response marker (for example auth.response.tempfail.no_tls). |
| 79 | policy_rule | LowCardinality(String) | DEFAULT '' | PolicyRule | Name of the presenting rule. |
| 80 | tempfail_reason | LowCardinality(String) | DEFAULT '' | TempFailReason | policy_indeterminate, backend_tempfail, auth_fsm_guard, brute_force_error, environment_tempfail; empty unless tempfail. |
| 81 | tempfail_checkpoint | LowCardinality(String) | DEFAULT '' | TempFailCheckpoint | Checkpoint where an indeterminate result surfaced. |
| 82 | policy_status | LowCardinality(String) | DEFAULT '' | PolicyStatus | Decision status code (evaluation_failed, provider_unavailable, ...). |
| 83 | fsm_state | LowCardinality(String) | DEFAULT '' | FSMState | Terminal auth FSM state. |
| 84 | transport | LowCardinality(String) | DEFAULT '' | Transport ← Outcome.Transport | http, grpc, or empty when unknown. |
| 85 | transport_status | Nullable(UInt16) | - | TransportStatus, only for http and values above 0 | Committed HTTP writer status; NULL for gRPC or unknown. Never derive the result from it. |
| 86 | flow_ref | String | DEFAULT '' | FlowRef ← Snapshot.IDP.FlowRef | Non-authorizing IdP flow digest (32 hex); joins auth_events.flow_ref; empty outside IdP browser flows. |
| 87 | brute_force_tolerated | Nullable(Bool) | - | BruteForceTolerated ← Diagnostics.BruteForceTolerated, only when a bucket name is set | true when toleration suppressed a triggered bucket; NULL without a bucket. |
| 88 | reputation_assessments | Array(Tuple(...)), see below | empty array | ReputationAssessments ([]reputationAssessmentRow) ← record facts decoded by reputationview | One tuple per (fact, subject record); measured fields are NULL for not_found / unavailable; at most 64 tuples. |
| 89 | reputation_truncated | Bool | DEFAULT false | ReputationTruncated ← request.FactsTrimmed, or a record beyond the 64-tuple cap was dropped | The host or the plugin dropped assessment records to stay within bounds. Exactly 64 tuples alone do not set it. |
reputation_assessments tuple elements (reputationAssessmentRow):
| Element | Type | Value domain |
|---|---|---|
fact, role, profile | LowCardinality(String) | Policy fact name, subject role, reputation profile |
kind | LowCardinality(String) | ip, network, asn, dns_domain, account, service |
state | LowCardinality(String) | fresh, stale, not_found, unavailable |
band | LowCardinality(String) | unknown, trusted, positive, neutral, suspicious, blocked, unavailable |
override | LowCardinality(String) | none, trusted, neutral, blocked |
risk_score, trust_score, confidence, samples | Nullable(Float64) | Measured values |
source_diversity, age_seconds | Nullable(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).
| Column | Legacy type | Writer | Replaced by |
|---|---|---|---|
reputation_score, reputation_positive_score, reputation_negative_score, reputation_ip_score, reputation_asn_score, reputation_country_score, reputation_asn_country_score | Nullable(Float64) | none | reputation_assessments |
reputation_samples | Nullable(UInt64) | none | reputation_assessments |
reputation_source, reputation_decision | LowCardinality(String) | none | reputation_assessments |
gp_attempts, gp_unique_ips, gp_unique_users | Nullable(UInt64) | retired Lua action | - (global pattern map) |
gp_ips_per_user | Nullable(Float64) | retired Lua action | - |
prot_active | Nullable(Bool) | retired Lua action | - (account protection map) |
prot_reason | LowCardinality(String) | retired Lua action | - |
prot_backoff, prot_delay_ms | Nullable(UInt64) | retired Lua action | - |
dyn_threat | Nullable(UInt64) | retired Lua action | - (dynamic response map) |
dyn_response | LowCardinality(String) | retired Lua action | - |
features | historical (not in the fixture) | none | decision_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 tomigrations/diagnostic-contract.sql. - Engine:
ReplacingMergeTreewithout 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_idcollapse at merge time. Until then they are visible; the support history collapses them in Go (dedupeByID). - Writer:
authEventRow, filled in one literal bynewAuthEventRow(event_sink.go) frompluginapi.AuthEvent. 19 columns.
| # | Column | Type | Source | Meaning |
|---|---|---|---|---|
| 1 | ts | DateTime64(3, 'UTC') | ObservedAt (UTC ms) | Host observation time. |
| 2 | event_id | String | EventID | Host-generated random 128-bit hex; idempotency key. |
| 3 | event_type | LowCardinality(String) | Type | mfa.verification, oidc.code_issuance, oidc.code_redemption. |
| 4 | event_version | UInt16 | Version | 1 for all three types; issuance version 1 fixes a 10-minute redemption bound. |
| 5 | deployment | LowCardinality(String) | module config | Writer separation. |
| 6 | instance | LowCardinality(String) | module config | Writer instance. |
| 7 | request_id | String | RequestID | Runtime.GUID of the observing request; joins logins.session for the same request only. |
| 8 | flow_ref | String | FlowRef | Flow digest; joins logins.flow_ref; empty for self-service step-ups. |
| 9 | protocol | LowCardinality(String) | Protocol | oidc, saml, or account (self-service). |
| 10 | client_id | LowCardinality(String) | ClientID | OIDC relying party. |
| 11 | saml_entity_id | LowCardinality(String) | SAMLEntityID | SAML relying party. |
| 12 | account | LowCardinality(String) | Account | Pending or authenticated identity account. |
| 13 | client_ip | String | ClientIP | Browser address; empty on oidc.code_redemption (the requester is the relying-party backend). |
| 14 | factor_role | LowCardinality(String) | FactorRole | second_factor for MFA; empty for completion events (primary is reserved and never written). |
| 15 | method | LowCardinality(String) | Method | totp, recovery_codes, webauthn; empty for completion events. |
| 16 | result | LowCardinality(String) | Result | success, invalid, error, rate_limited, rejected. |
| 17 | reason | LowCardinality(String) | Reason | Stable reason code (issuance: authenticated_in_flow, existing_session, not_recorded; redemption: tokens_issued, client_mismatch, ...). |
| 18 | flow_effect | LowCardinality(String) | FlowEffect | none, step_up_completed, primary_failure_kept, code_issued, tokens_issued, code_consumed. |
| 19 | fail_latched | Bool | FailLatched | The primary step had already failed, so a correct factor does not authenticate. Operator-only meaning together with result = 'success'. |