ClickHouse schema (cdn_ai)

Canonical reference for the standardized cdn_ai schema — the tables the MCP tools query and Vector writes into.

• Source of truth (deployed DDL): playtelly-iac → services/monitoring/clickhouse/staging/07-cdn-ai-migration.yaml. That migration is what ArgoCD applies to the live cluster; treat it as authoritative and this page as its readable mirror.
• Verified live 2026-09-22: column counts 25 / 22 / 12; cproxy_events and streamer_events flowing, elb_events present but 0 rows (see the elb collection gap).

The standard (convention)

Three per-component tables in the cdn_ai database:

Table	Cols	What it holds
cproxy_events	25	edge access requests + response outcome + CMCD
elb_events	22	load-balancer requests + node selection (no CMCD outcome)
streamer_events	12	origin-side streamer lifecycle (no client/CMCD)

Rules the schema follows:

1. Common base, identical in cproxy_events and elb_events, so the security tools' UNION ALL across both tiers lines up: timestamp, node, pop_name, component, client_ip, device_id, transaction_id, client_port, path, user_agent, referer, host, log_injection_suspected, plus the 7 cmcd_* columns.
2. Component-specific additions:
3. cproxy_events → response outcome: event_type, status_code, elapsed_ms, sent_bytes, origin_key.
4. elb_events → node selection: elb_selected_node, elb_node_index.
5. streamer_events → lifecycle instead of access: module, level, source_file, source_function, stream_id, event_type, state, message (no client_ip/CMCD — the streamer is origin-side, not client-facing).
6. Engine convention on every table: MergeTree, PARTITION BY toDate(timestamp), ORDER BY starting with timestamp, and a 30-day TTL.

DDL

-- cproxy_events (25 cols) — edge access + response outcome + CMCD
CREATE TABLE cdn_ai.cproxy_events (
  timestamp DateTime, node String, pop_name String, component String,
  client_ip String, device_id String, transaction_id String,
  client_port UInt32, event_type String DEFAULT 'request',
  path String, user_agent String, referer String, host String,
  log_injection_suspected UInt8,
  status_code UInt16, elapsed_ms Float32, sent_bytes UInt64, origin_key String,
  cmcd_session_id String, cmcd_object_type String, cmcd_bitrate_kbps UInt32,
  cmcd_top_bitrate_kbps UInt32, cmcd_buffer_length_ms UInt32,
  cmcd_buffer_starvation UInt8, cmcd_startup UInt8
) ENGINE = MergeTree() PARTITION BY toDate(timestamp)
  ORDER BY (timestamp, client_ip) TTL timestamp + INTERVAL 30 DAY;

-- elb_events (22 cols) — same base, no response-outcome/CMCD, + node selection
CREATE TABLE cdn_ai.elb_events (
  timestamp DateTime, node String, pop_name String, component String,
  client_ip String, device_id String, transaction_id String,
  client_port UInt32, path String, user_agent String, referer String,
  host String, log_injection_suspected UInt8,
  elb_selected_node String, elb_node_index UInt16,
  cmcd_session_id String, cmcd_object_type String, cmcd_bitrate_kbps UInt32,
  cmcd_top_bitrate_kbps UInt32, cmcd_buffer_length_ms UInt32,
  cmcd_buffer_starvation UInt8, cmcd_startup UInt8
) ENGINE = MergeTree() PARTITION BY toDate(timestamp)
  ORDER BY (timestamp, client_ip) TTL timestamp + INTERVAL 30 DAY;

-- streamer_events (12 cols) — origin-side lifecycle (ingest/dirwatch)
CREATE TABLE cdn_ai.streamer_events (
  timestamp DateTime, node String, pop_name String, component String,
  module LowCardinality(String), level LowCardinality(String),
  source_file String, source_function String,
  stream_id String, event_type LowCardinality(String),
  state LowCardinality(String), message String
) ENGINE = MergeTree() PARTITION BY toDate(timestamp)
  ORDER BY (timestamp, stream_id) TTL timestamp + INTERVAL 30 DAY;

Column reference

Base columns (both cproxy_events and elb_events):

Column	Type	Meaning
timestamp	DateTime	event time (UTC)
node	String	pod/host that emitted the line
pop_name	String	POP tag — currently hardcoded per pipeline (cproxy-nuc / pop1-elb / gslb), not yet derived
component	String	cproxy / elb
client_ip	String	client IP (XFF-aware; see the real-IP gap)
device_id	String	persistent X-Device-Id (not the CMCD session id)
transaction_id	String	x-transaction-id, for elb↔cproxy correlation
client_port	UInt32	client source port
path, user_agent, referer, host	String	request fields
log_injection_suspected	UInt8	heuristic flag
cmcd_* (7)	mixed	CMCD/QoE fields (populated on cproxy; default on elb)

cproxy_events adds — response outcome (populated on event_type='response' rows):

Column	Type	Meaning
event_type	String	request or response
status_code	UInt16	HTTP status (response rows)
elapsed_ms	Float32	request latency
sent_bytes	UInt64	bytes served
origin_key	String	which origin/cache key served it

elb_events adds: elb_selected_node (String), elb_node_index (UInt16) — which cproxy the LB picked.

streamer_events (lifecycle):

Column	Type	Meaning
module, level	LowCardinality(String)	streamer module + log level
source_file, source_function	String	code location
stream_id	String	stream identifier
event_type	LowCardinality(String)	playitem_selected / state_changed / stream_created
state	LowCardinality(String)	source state on a transition
message	String	raw message

Related

• Reads it: the MCP tools — see MCP server and the tool → table map.
• Writes it: Vector pipelines ai_cproxy.toml, ai_elb.toml, ai_streamer_events.toml (playtelly-iac → services/monitoring/vector/configs/cdn/pipelines/).
• Legacy note: 07-cdn-ai-migration.yaml still also creates the old streamer_analytics.cdn_access_logs / security_access_logs demo tables — not part of this standard, pending retirement once the cdn_ai tables are fully confirmed.