🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { beforeEach, describe, expect, it, vi } from "vitest";
const state = vi.hoisted(() => ({
existingSessionHourlyView: undefined as string | undefined,
existingColumns: {} as Record<string, { name: string; type: string }[]>,
timezone: "UTC",
}));
const mocks = vi.hoisted(() => ({
exec: vi.fn(),
insert: vi.fn(),
query: vi.fn(),
logger: {
debug: vi.fn(),
error: vi.fn(),
info: vi.fn(),
warn: vi.fn(),
},
}));
vi.mock("@clickhouse/client", () => ({
createClient: () => ({
exec: mocks.exec,
insert: mocks.insert,
query: mocks.query,
}),
}));
// Both flags on so every CREATE TABLE the server can issue is under test.
vi.mock("../../lib/const.js", () => ({
IS_CLOUD: true,
LITE_DASHBOARD: true,
}));
vi.mock("../../lib/logger/logger.js", () => ({
createServiceLogger: () => mocks.logger,
}));
import { initializeClickhouse } from "./clickhouse.js";
function executedQueries() {
return mocks.exec.mock.calls.map(([args]) => args.query as string);
}
function findSessionHourlyCreateQuery() {
return executedQueries().find(query => /CREATE MATERIALIZED VIEW session_hourly_mv\b/.test(query));
}
describe("session hourly materialized view initialization", () => {
beforeEach(() => {
vi.clearAllMocks();
state.existingSessionHourlyView = undefined;
state.existingColumns = {};
state.timezone = "UTC";
mocks.exec.mockResolvedValue(undefined);
mocks.query.mockImplementation(
async ({ query, query_params }: { query: string; query_params?: { table?: string } }) => {
if (query.includes("FROM system.columns")) {
return { json: async () => state.existingColumns[query_params?.table ?? ""] ?? [] };
}
if (query.includes("timezone()")) {
return { json: async () => [{ timezone: state.timezone }] };
}
if (query.includes("FROM system.tables")) {
return {
json: async () =>
state.existingSessionHourlyView ? [{ create_table_query: state.existingSessionHourlyView }] : [],
};
}
throw new Error(`Unexpected ClickHouse query: ${query}`);
}
);
});
it("creates the hourly rollup from finalized sessions instead of raw events", async () => {
await initializeClickhouse();
const createQuery = findSessionHourlyCreateQuery();
expect(createQuery).toBeDefined();
expect(createQuery).toContain("REFRESH EVERY 1 HOUR");
expect(createQuery).toContain("FROM sessions_mv_target FINAL");
expect(createQuery).not.toMatch(/FROM events\b/);
expect(executedQueries()).not.toContainEqual(expect.stringMatching(/DROP VIEW/));
});
it("creates and heals the millisecond event-ordering column", async () => {
await initializeClickhouse();
const queries = executedQueries();
const createEvents = queries.find(query => /CREATE TABLE IF NOT EXISTS events\b/.test(query));
const alterEvents = queries.find(query => /ALTER TABLE events\b/.test(query));
expect(createEvents).toContain("timestamp_ms DateTime64(3, 'UTC')");
expect(alterEvents).toContain("timestamp_ms DateTime64(3, 'UTC') DEFAULT toDateTime64(timestamp, 3)");
});
it("replaces the old five-minute raw-events definition", async () => {
state.existingSessionHourlyView = `
CREATE MATERIALIZED VIEW analytics.session_hourly_mv
REFRESH EVERY 5 MINUTE
TO analytics.session_hourly_mv_target
AS SELECT * FROM analytics.events
`;
await initializeClickhouse();
const queries = executedQueries();
const dropIndex = queries.findIndex(query => query.includes("DROP VIEW IF EXISTS session_hourly_mv SYNC"));
const createIndex = queries.findIndex(query => /CREATE MATERIALIZED VIEW session_hourly_mv\b/.test(query));
expect(dropIndex).toBeGreaterThanOrEqual(0);
expect(createIndex).toBeGreaterThan(dropIndex);
});
it("leaves an already-correct definition unchanged", async () => {
state.existingSessionHourlyView = `
CREATE MATERIALIZED VIEW analytics.session_hourly_mv
REFRESH EVERY 1 HOUR
TO analytics.session_hourly_mv_target
AS SELECT * FROM analytics.sessions_mv_target FINAL
`;
await initializeClickhouse();
expect(findSessionHourlyCreateQuery()).toBeUndefined();
expect(executedQueries()).not.toContainEqual(expect.stringMatching(/DROP VIEW/));
});
});
// A bare `DateTime` column takes the server's timezone, so on a self-hosted
// ClickHouse configured for anything but UTC every stored instant would read
// back shifted. Every time column has to carry an explicit 'UTC', and tables
// created before that was declared have to be migrated on startup.
describe("UTC time columns", () => {
beforeEach(() => {
vi.clearAllMocks();
state.existingSessionHourlyView = undefined;
state.existingColumns = {};
state.timezone = "UTC";
mocks.exec.mockResolvedValue(undefined);
mocks.query.mockImplementation(
async ({ query, query_params }: { query: string; query_params?: { table?: string } }) => {
if (query.includes("FROM system.columns")) {
return { json: async () => state.existingColumns[query_params?.table ?? ""] ?? [] };
}
if (query.includes("timezone()")) {
return { json: async () => [{ timezone: state.timezone }] };
}
if (query.includes("FROM system.tables")) {
return { json: async () => [] };
}
throw new Error(`Unexpected ClickHouse query: ${query}`);
}
);
});
it("declares every DateTime column in the DDL with an explicit UTC timezone", async () => {
await initializeClickhouse();
const ddl = executedQueries().filter(query => /CREATE TABLE|ALTER TABLE/.test(query));
expect(ddl.length).toBeGreaterThan(0);
// Matches type tokens only: `toDateTime(...)` / `toDateTime64(...)` calls
// have no word boundary before the `D`, so they are left alone.
const timeTypes = ddl.flatMap(query => query.match(/\bDateTime(?:64)?(?:\([^()]*\))?/g) ?? []);
expect(timeTypes.length).toBeGreaterThan(0);
const bare = timeTypes.filter(type => !type.includes("'UTC'"));
expect(bare).toEqual([]);
});
it("pins existing bare time columns to UTC with a metadata-only ALTER", async () => {
state.existingColumns = {
events: [
{ name: "timestamp", type: "DateTime" },
{ name: "timestamp_ms", type: "DateTime64(3)" },
],
session_replay_metadata_v2: [
{ name: "start_time", type: "SimpleAggregateFunction(min, DateTime64(3))" },
{ name: "end_time", type: "SimpleAggregateFunction(max, Nullable(DateTime64(3)))" },
],
sessions_mv_target: [{ name: "start_time", type: "SimpleAggregateFunction(min, DateTime)" }],
};
await initializeClickhouse();
const modifies = executedQueries().filter(query => /MODIFY COLUMN/.test(query));
expect(modifies.find(query => /ALTER TABLE events\b/.test(query))).toContain(
"MODIFY COLUMN timestamp DateTime('UTC'),\n MODIFY COLUMN timestamp_ms DateTime64(3, 'UTC')"
);
expect(modifies.find(query => /ALTER TABLE session_replay_metadata_v2\b/.test(query))).toContain(
"MODIFY COLUMN end_time SimpleAggregateFunction(max, Nullable(DateTime64(3, 'UTC')))"
);
expect(modifies.find(query => /ALTER TABLE sessions_mv_target\b/.test(query))).toContain(
"MODIFY COLUMN start_time SimpleAggregateFunction(min, DateTime('UTC'))"
);
expect(modifies.some(query => /ALTER TABLE bot_events\b/.test(query))).toBe(false);
});
it("leaves columns that already carry UTC untouched", async () => {
state.existingColumns = {
events: [
{ name: "timestamp", type: "DateTime('UTC')" },
{ name: "timestamp_ms", type: "DateTime64(3, 'UTC')" },
],
overview_hourly_mv_target: [{ name: "event_hour", type: "DateTime('UTC')" }],
};
await initializeClickhouse();
expect(executedQueries().some(query => /MODIFY COLUMN/.test(query))).toBe(false);
});
it("stays quiet about the server timezone when it is UTC", async () => {
await initializeClickhouse();
expect(mocks.logger.warn).not.toHaveBeenCalled();
});
it("warns about a non-UTC server", async () => {
state.timezone = "Europe/Berlin";
await initializeClickhouse();
expect(mocks.logger.warn).toHaveBeenCalledWith(
expect.objectContaining({ timezone: "Europe/Berlin" }),
expect.stringContaining("not UTC")
);
});
it("keeps starting when the timezone migration fails", async () => {
state.existingColumns = { events: [{ name: "timestamp", type: "DateTime" }] };
mocks.exec.mockImplementation(async ({ query }: { query: string }) => {
if (/MODIFY COLUMN/.test(query)) throw new Error("ALTER refused");
});
await expect(initializeClickhouse()).resolves.toBeUndefined();
expect(mocks.logger.error).toHaveBeenCalledWith(
expect.objectContaining({ step: "pin events time columns to UTC" }),
expect.any(String)
);
});
});