πΈ Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import {
clickhouseInitLogger as logger,
ensureUtcTimeColumns,
execClickhouseInitStep,
getTableColumns,
} from "../initUtils.js";
import { UTC_TIME_COLUMNS } from "../timeColumns.js";
type ColumnDefinition = {
name: string;
definition: string;
};
const EVENTS_COLUMNS_TO_ENSURE: ColumnDefinition[] = [
// `timestamp` is part of the MergeTree key and ClickHouse forbids widening a
// key column in place. Keep it for partition pruning; use this additive
// column wherever event order matters. Old rows read as whole-second values.
{
name: "timestamp_ms",
definition: "timestamp_ms DateTime64(3, 'UTC') DEFAULT toDateTime64(timestamp, 3) AFTER timestamp",
},
{ name: "lcp", definition: "lcp Nullable(Float64)" },
{ name: "cls", definition: "cls Nullable(Float64)" },
{ name: "inp", definition: "inp Nullable(Float64)" },
{ name: "fcp", definition: "fcp Nullable(Float64)" },
{ name: "ttfb", definition: "ttfb Nullable(Float64)" },
{ name: "ip", definition: "ip Nullable(String)" },
{ name: "timezone", definition: "timezone LowCardinality(String) DEFAULT ''" },
{ name: "identified_user_id", definition: "identified_user_id String DEFAULT ''" },
{ name: "import_id", definition: "import_id Nullable(UUID)" },
{ name: "tag", definition: "tag LowCardinality(String) DEFAULT ''" },
{ name: "feature_flags", definition: "feature_flags Map(String, String) DEFAULT map()" },
{ name: "visitor_id", definition: "visitor_id String DEFAULT ''" },
{ name: "asn", definition: "asn Nullable(UInt32)" },
{ name: "asn_org", definition: "asn_org LowCardinality(String) DEFAULT ''" },
{ name: "is_datacenter_asn", definition: "is_datacenter_asn UInt8 DEFAULT 0" },
];
const BOT_EVENTS_COLUMNS_TO_ENSURE: ColumnDefinition[] = [
// Prod tables created before session_id landed in the CREATE statement lack
// it; the insert already sends it, so healing the column starts populating it.
{ name: "session_id", definition: "session_id String DEFAULT ''" },
// Null = the client sent no score and the server inferred nothing.
{ name: "client_bot_score", definition: "client_bot_score Nullable(UInt8)" },
{ name: "client_signal_mask", definition: "client_signal_mask UInt16 DEFAULT 0" },
// Which anomaly rules fired and what they summed to. "Rate anomaly" is a
// dozen rules with different meanings, so without these an audit row cannot
// say why the request was convicted.
{ name: "anomaly_reasons", definition: "anomaly_reasons String DEFAULT ''" },
{ name: "anomaly_score", definition: "anomaly_score UInt8 DEFAULT 0" },
// Bot identity. `matched_ua_pattern` stores a regex source, which is evidence
// but not an answer to "who is this" β these carry the name the operator
// publishes. Empty on rows written before the columns existed; nothing is
// backfilled, so readers must tolerate ''.
{ name: "bot_name", definition: "bot_name LowCardinality(String) DEFAULT ''" },
{ name: "bot_operator", definition: "bot_operator LowCardinality(String) DEFAULT ''" },
{ name: "bot_purpose", definition: "bot_purpose LowCardinality(String) DEFAULT ''" },
// The curated provider behind the request's ASN, resolved during detection
// and until now discarded. A UA can claim any name; this is the independent
// half of the claim, so the two together are what makes attribution arguable.
{ name: "asn_provider", definition: "asn_provider LowCardinality(String) DEFAULT ''" },
];
// Runs against both audit tables: they carry the same columns on purpose, so a
// column added to one has to reach the other or the shared queries stop working.
async function ensureBotEventsColumns(table: "bot_events" | "bot_observations") {
const existingColumns = await getTableColumns(table);
const missingColumns = BOT_EVENTS_COLUMNS_TO_ENSURE.filter(column => !existingColumns.has(column.name));
if (missingColumns.length === 0) {
logger.debug({ table }, "Bot events table columns are up to date");
return;
}
logger.info({ table, missingColumns: missingColumns.map(column => column.name) }, "Adding missing bot table columns");
await execClickhouseInitStep(
`add missing ${table} columns`,
`
ALTER TABLE ${table}
${missingColumns.map(column => `ADD COLUMN IF NOT EXISTS ${column.definition}`).join(",\n ")}
`,
{ lockAcquireTimeoutSeconds: 15 }
);
}
async function ensureEventsColumns() {
const existingColumns = await getTableColumns("events");
const missingColumns = EVENTS_COLUMNS_TO_ENSURE.filter(column => !existingColumns.has(column.name));
if (missingColumns.length === 0) {
logger.debug("Events table columns are up to date");
return;
}
logger.info({ missingColumns: missingColumns.map(column => column.name) }, "Adding missing events table columns");
await execClickhouseInitStep(
"add missing events columns",
`
ALTER TABLE events
${missingColumns.map(column => `ADD COLUMN IF NOT EXISTS ${column.definition}`).join(",\n ")}
`,
{ lockAcquireTimeoutSeconds: 15 }
);
}
export async function initializeCoreTables() {
await execClickhouseInitStep(
"create events table",
`
CREATE TABLE IF NOT EXISTS events (
site_id UInt16,
timestamp DateTime('UTC'),
timestamp_ms DateTime64(3, 'UTC') DEFAULT toDateTime64(timestamp, 3),
session_id String,
user_id String,
hostname String,
pathname String,
querystring String, /* URL parameters stored in raw format */
url_parameters Map(String, String), /* Structured storage for all URL parameters */
page_title String,
referrer String,
channel String,
browser LowCardinality(String),
browser_version LowCardinality(String),
operating_system LowCardinality(String),
operating_system_version LowCardinality(String),
language LowCardinality(String),
country LowCardinality(FixedString(2)),
region LowCardinality(String),
city String,
lat Float64,
lon Float64,
screen_width UInt16,
screen_height UInt16,
device_type LowCardinality(String),
type LowCardinality(String) DEFAULT 'pageview',
event_name String,
props JSON,
lcp Nullable(Float64),
cls Nullable(Float64),
inp Nullable(Float64),
fcp Nullable(Float64),
ttfb Nullable(Float64),
ip Nullable(String),
timezone LowCardinality(String) DEFAULT '',
identified_user_id String DEFAULT '',
import_id Nullable(UUID),
tag LowCardinality(String) DEFAULT '',
feature_flags Map(String, String) DEFAULT map(),
asn Nullable(UInt32),
asn_org LowCardinality(String) DEFAULT '',
is_datacenter_asn UInt8 DEFAULT 0,
visitor_id String DEFAULT ''
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (site_id, timestamp)
`
);
// Heal tables created by older versions (or by the window where the CREATE
// TABLE above was missing these columns). Only columns that are actually
// missing are altered in β even a no-op ALTER needs the events table ALTER lock.
await ensureEventsColumns();
await ensureUtcTimeColumns("events", UTC_TIME_COLUMNS.events);
await execClickhouseInitStep(
"create bot events table",
`
CREATE TABLE IF NOT EXISTS bot_events (
site_id UInt16,
timestamp DateTime('UTC'),
session_id String,
user_id String,
hostname String,
pathname String,
querystring String,
referrer String,
browser LowCardinality(String),
browser_version LowCardinality(String),
operating_system LowCardinality(String),
operating_system_version LowCardinality(String),
country LowCardinality(FixedString(2)),
region LowCardinality(String),
city String,
lat Float64,
lon Float64,
screen_width UInt16,
screen_height UInt16,
device_type LowCardinality(String),
type LowCardinality(String) DEFAULT 'pageview',
asn Nullable(UInt32),
asn_org String DEFAULT '',
detected_ua_pattern Bool DEFAULT false,
detected_header_heuristics Bool DEFAULT false,
detected_client_signals Bool DEFAULT false,
detected_bot_asn Bool DEFAULT false,
detected_rate_anomaly Bool DEFAULT false,
matched_ua_pattern String DEFAULT '',
bot_category LowCardinality(String) DEFAULT '',
client_bot_score Nullable(UInt8),
client_signal_mask UInt16 DEFAULT 0,
anomaly_reasons String DEFAULT '',
anomaly_score UInt8 DEFAULT 0,
bot_name LowCardinality(String) DEFAULT '',
bot_operator LowCardinality(String) DEFAULT '',
bot_purpose LowCardinality(String) DEFAULT '',
asn_provider LowCardinality(String) DEFAULT ''
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (site_id, timestamp)
TTL timestamp + INTERVAL 3 MONTH
`
);
await ensureBotEventsColumns("bot_events");
await ensureUtcTimeColumns("bot_events", UTC_TIME_COLUMNS.bot_events);
// Forensic mirror of bot_events for sites with bot blocking turned OFF: the
// event is still tracked normally, and this row is the only trace that
// detection fired. Columns match bot_events exactly so the same analysis
// queries run against either table; only the TTL is shorter.
await execClickhouseInitStep(
"create bot observations table",
`
CREATE TABLE IF NOT EXISTS bot_observations (
site_id UInt16,
timestamp DateTime('UTC'),
session_id String,
user_id String,
hostname String,
pathname String,
querystring String,
referrer String,
browser LowCardinality(String),
browser_version LowCardinality(String),
operating_system LowCardinality(String),
operating_system_version LowCardinality(String),
country LowCardinality(FixedString(2)),
region LowCardinality(String),
city String,
lat Float64,
lon Float64,
screen_width UInt16,
screen_height UInt16,
device_type LowCardinality(String),
type LowCardinality(String) DEFAULT 'pageview',
asn Nullable(UInt32),
asn_org String DEFAULT '',
detected_ua_pattern Bool DEFAULT false,
detected_header_heuristics Bool DEFAULT false,
detected_client_signals Bool DEFAULT false,
detected_bot_asn Bool DEFAULT false,
detected_rate_anomaly Bool DEFAULT false,
matched_ua_pattern String DEFAULT '',
bot_category LowCardinality(String) DEFAULT '',
client_bot_score Nullable(UInt8),
client_signal_mask UInt16 DEFAULT 0,
anomaly_reasons String DEFAULT '',
anomaly_score UInt8 DEFAULT 0,
bot_name LowCardinality(String) DEFAULT '',
bot_operator LowCardinality(String) DEFAULT '',
bot_purpose LowCardinality(String) DEFAULT '',
asn_provider LowCardinality(String) DEFAULT ''
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (site_id, timestamp)
TTL timestamp + INTERVAL 30 DAY
`
);
await ensureBotEventsColumns("bot_observations");
await ensureUtcTimeColumns("bot_observations", UTC_TIME_COLUMNS.bot_observations);
await execClickhouseInitStep(
"create session replay events table",
`
CREATE TABLE IF NOT EXISTS session_replay_events (
site_id UInt16,
session_id String,
user_id String,
timestamp DateTime64(3, 'UTC'),
event_type LowCardinality(String),
event_data String,
event_data_key Nullable(String), -- R2 storage key for cloud deployments
batch_index Nullable(UInt16), -- Index within the R2 batch
sequence_number UInt32,
event_size_bytes UInt32,
viewport_width Nullable(UInt16),
viewport_height Nullable(UInt16),
is_complete UInt8 DEFAULT 0
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (site_id, session_id, sequence_number)
TTL toDateTime(timestamp) + INTERVAL 30 DAY
`
);
await execClickhouseInitStep(
"add session replay events columns",
`
ALTER TABLE session_replay_events
ADD COLUMN IF NOT EXISTS event_data_key Nullable(String), -- R2 storage key for cloud deployments
ADD COLUMN IF NOT EXISTS batch_index Nullable(UInt16), -- Index within the R2 batch
ADD COLUMN IF NOT EXISTS identified_user_id String DEFAULT ''
`
);
await ensureUtcTimeColumns("session_replay_events", UTC_TIME_COLUMNS.session_replay_events);
await execClickhouseInitStep(
"create session replay metadata table",
`
CREATE TABLE IF NOT EXISTS session_replay_metadata (
site_id UInt16,
session_id String,
user_id String,
start_time DateTime('UTC'),
end_time Nullable(DateTime('UTC')),
duration_ms Nullable(UInt32),
event_count UInt32,
compressed_size_bytes UInt32,
page_url String,
country LowCardinality(FixedString(2)),
region LowCardinality(String),
city String,
lat Float64,
lon Float64,
browser LowCardinality(String),
browser_version LowCardinality(String),
operating_system LowCardinality(String),
operating_system_version LowCardinality(String),
language LowCardinality(String),
screen_width UInt16,
screen_height UInt16,
device_type LowCardinality(String),
channel String,
hostname String,
referrer String,
has_replay_data UInt8 DEFAULT 1,
created_at DateTime('UTC') DEFAULT now()
)
ENGINE = ReplacingMergeTree(created_at)
PARTITION BY toYYYYMM(start_time)
ORDER BY (site_id, session_id)
TTL start_time + INTERVAL 30 DAY
`
);
await execClickhouseInitStep(
"add session replay metadata columns",
`
ALTER TABLE session_replay_metadata
ADD COLUMN IF NOT EXISTS identified_user_id String DEFAULT ''
`
);
await ensureUtcTimeColumns("session_replay_metadata", UTC_TIME_COLUMNS.session_replay_metadata);
// Successor to session_replay_metadata. The old table is a
// ReplacingMergeTree holding one cumulative row per session, so every replay
// batch had to re-derive that row: SELECT MIN/MAX/COUNT/SUM over the whole
// session, then rewrite it. That read scanned 818 B rows in six days β 76% of
// everything the cluster read β and the rewrites left 2.5 M single-row parts.
//
// Aggregating the columns instead lets each batch insert only what it
// observed, and the engine does the combining. Nothing reads back during
// ingest. Every column keeps its name and its post-merge meaning, so readers
// only need FINAL, which they already used.
//
// duration_ms and created_at are deliberately absent: duration is derived
// from the merged bounds at read time, and there is no version column to
// order by any more.
await execClickhouseInitStep(
"create session replay metadata v2 table",
`
CREATE TABLE IF NOT EXISTS session_replay_metadata_v2 (
site_id UInt16,
session_id String,
user_id SimpleAggregateFunction(anyLast, String),
-- Rows arrive with '' until the visitor identifies, and max() over a
-- String makes any real id win over the empty one.
identified_user_id SimpleAggregateFunction(max, String),
-- Millisecond resolution, matching session_replay_events.timestamp:
-- duration is now derived from these bounds instead of being stored,
-- and second-resolution columns would floor a 900ms replay to 0.
start_time SimpleAggregateFunction(min, DateTime64(3, 'UTC')),
end_time SimpleAggregateFunction(max, Nullable(DateTime64(3, 'UTC'))),
event_count SimpleAggregateFunction(sum, UInt64),
compressed_size_bytes SimpleAggregateFunction(sum, UInt64),
-- KNOWN LIMITATION. These merge independently, so a session whose
-- batches disagreed can assemble a row from more than one of them β
-- the old ReplacingMergeTree always returned one whole batch's
-- snapshot. Measured over 30 days of production, of the 423 sessions
-- that had more than one metadata version: page_url differed in 62,
-- region/city/lat in 18, country in 11, language in 13; browser,
-- operating_system, device_type, channel, hostname, referrer and
-- user_id never differed at all.
--
-- The geo group is the one that matters, because those fields are only
-- meaningful together: ~4% of multi-version sessions could show a city
-- and a country drawn from different batches. Making them coherent
-- needs one versioned snapshot (a max() over a leading-version tuple,
-- or argMaxState + GROUP BY), which changes every read site.
--
-- Weighed and accepted, 2026-08: the exposure is narrow and the fix
-- costs more than the defect. Don't re-open it without new evidence
-- that mixed geo is actually misleading someone.
page_url SimpleAggregateFunction(anyLast, String),
country SimpleAggregateFunction(anyLast, LowCardinality(FixedString(2))),
region SimpleAggregateFunction(anyLast, LowCardinality(String)),
city SimpleAggregateFunction(anyLast, String),
lat SimpleAggregateFunction(anyLast, Float64),
lon SimpleAggregateFunction(anyLast, Float64),
browser SimpleAggregateFunction(anyLast, LowCardinality(String)),
browser_version SimpleAggregateFunction(anyLast, LowCardinality(String)),
operating_system SimpleAggregateFunction(anyLast, LowCardinality(String)),
operating_system_version SimpleAggregateFunction(anyLast, LowCardinality(String)),
language SimpleAggregateFunction(anyLast, LowCardinality(String)),
screen_width SimpleAggregateFunction(max, UInt16),
screen_height SimpleAggregateFunction(max, UInt16),
device_type SimpleAggregateFunction(anyLast, LowCardinality(String)),
channel SimpleAggregateFunction(anyLast, String),
hostname SimpleAggregateFunction(anyLast, String),
referrer SimpleAggregateFunction(anyLast, String),
has_replay_data SimpleAggregateFunction(max, UInt8)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(start_time)
ORDER BY (site_id, session_id)
TTL toDateTime(start_time) + INTERVAL 30 DAY
`
);
await ensureUtcTimeColumns("session_replay_metadata_v2", UTC_TIME_COLUMNS.session_replay_metadata_v2);
}