Analytics Query Engine

Relevant source files

The following files were used as context for generating this wiki page:

• src/lib/clickhouse.ts
• src/queries/sql/getWebsiteStats.ts
• src/queries/sql/pageviews/getPageviewMetrics.ts
• src/queries/sql/pageviews/getPageviewStats.ts
• prisma/schema.prisma
• src/lib/db.ts
• src/queries/sql/getChannelExpandedMetrics.ts

The Analytics Query Engine is a core component of the Umami application, responsible for retrieving analytics data from the underlying database. It is designed as an abstraction layer that supports two different database systems: a relational database (PostgreSQL) via Prisma, and a column-oriented database (ClickHouse). This dual-backend architecture allows the system to choose the most appropriate database for the query, enabling both flexibility and high performance for complex analytical workloads.

The engine exposes a series of functions, each tailored to fetch specific metrics like website statistics, page views, and session data. It dynamically dispatches requests to either the Prisma or ClickHouse implementation based on the environment configuration. A sophisticated filter parsing system translates high-level filter objects into optimized SQL WHERE clauses, supporting a wide range of analytical queries.

Architecture

The engine's architecture is centered around a dispatcher function, runQuery, which selects the correct database-specific implementation at runtime. This design decouples the application's data-fetching logic from the database implementation details.

Sources: src/lib/db.ts, src/queries/sql/getWebsiteStats.ts:18-21

Query Dispatch Flow

When a component needs analytics data, it calls a specific query function (e.g., getWebsiteStats). This function then uses runQuery to execute either a relational (Prisma/PostgreSQL) or a ClickHouse query based on the active database configuration.

The following diagram illustrates the dispatch mechanism:

Sources: src/lib/db.ts, src/queries/sql/getWebsiteStats.ts:18-21

Database Implementations

The engine supports two database backends:

1. Relational (Prisma): Uses the Prisma client to execute raw SQL queries against a PostgreSQL database. This is suitable for general-purpose queries and environments where ClickHouse is not available.
   Sources: src/queries/sql/getWebsiteStats.ts:28-52
2. ClickHouse: Uses the @clickhouse/client library for high-performance analytical queries. It often employs optimized queries, for instance, by using pre-aggregated hourly statistics tables (website_event_stats_hourly) to speed up common requests.
   Sources: src/lib/clickhouse.ts, src/queries/sql/getWebsiteStats.ts:54-113

Query Functions

The engine provides a set of exported functions to retrieve various analytics metrics. These functions accept a websiteId and a filters object and return a promise that resolves with the query result.

Key Query Functions

Function	Description	Source File
getWebsiteStats	Fetches aggregate statistics for a website, including pageviews, visitors, visits, bounces, and total time.	src/queries/sql/getWebsiteStats.ts
getPageviewStats	Retrieves time-series data for pageviews over a specified period, grouped by a time unit (e.g., day, hour).	src/queries/sql/pageviews/getPageviewStats.ts
getPageviewMetrics	Gathers metrics for different dimensions like URL, referrer, browser, OS, etc., counting unique visitors for each.	src/queries/sql/pageviews/getPageviewMetrics.ts
getChannelExpandedMetrics	Provides detailed metrics (pageviews, visitors, etc.) broken down by marketing channels.	src/queries/sql/getChannelExpandedMetrics.ts

Example: getWebsiteStats

This function calculates key performance indicators for a given website.

Parameters:

• websiteId (string): The UUID of the website to query.
• filters (QueryFilters): An object containing filters like startDate, endDate, url, etc.

Return Type: Promise<WebsiteStatsData[]>
The WebsiteStatsData object has the following structure:

export interface WebsiteStatsData {
  pageviews: number;
  visitors: number;
  visits: number;
  bounces: number;
  totaltime: number;
}

Sources: src/queries/sql/getWebsiteStats.ts:10-16

The sequence diagram below shows the typical flow for a getWebsiteStats call in a ClickHouse environment.

Sources: src/queries/sql/getWebsiteStats.ts:18-21, 54-113, src/lib/clickhouse.ts

ClickHouse Query Optimization

The ClickHouse implementation of getWebsiteStats demonstrates a key optimization. If the query filters do not involve specific EVENT_COLUMNS, it queries a pre-aggregated table website_event_stats_hourly. Otherwise, it queries the raw website_event table. This significantly improves performance for general overview queries.

// src/queries/sql/getWebsiteStats.ts:70-72
if (EVENT_COLUMNS.some(item => Object.keys(filters).includes(item))) {
    sql = `... query website_event ...`;
} else {
    sql = `... query website_event_stats_hourly ...`;
}

Sources: src/queries/sql/getWebsiteStats.ts:70-89, 90-111

Filter and Parameter Parsing

A critical feature of the query engine is its ability to parse a filters object into SQL. The parseFilters function in both the Prisma and ClickHouse libraries is responsible for this.

Sources: src/lib/clickhouse.ts:171-183

The parsing logic handles:

• Standard Filters: Translating filter keys (e.g., url, os, browser) into WHERE clauses.
• Date Ranges: Constructing between clauses for created_at timestamps.
• Cohort Analysis: Generating complex JOIN subqueries to filter sessions based on user behavior within a separate cohort date range.

The following diagram shows the process of building a query from filters.

Sources: src/lib/clickhouse.ts:127-183

Filter Mapping

The engine uses a FILTER_COLUMNS constant to map filter names to their corresponding database column names.

Filter Name	Database Column
url	url_path
title	page_title
referrer	referrer_domain
os	os
browser	browser
device	device
country	country
region	region
city	city
language	language
event	event_name
tag	tag

Sources: src/queries/sql/pageviews/getPageviewMetrics.ts:25, src/lib/constants.ts (inferred)

Data Models

The query engine operates primarily on event and session data. The structure is defined in prisma/schema.prisma and is mirrored in the ClickHouse database schema.

Entity Relationship Diagram

The diagram below shows the core tables and their relationships.

Sources: prisma/schema.prisma

Key Tables

WebsiteEvent

This is the central table, storing every event tracked. Each row represents a single event, such as a page view or a custom event.

Column	Type	Description
event_id	Uuid	Primary key for the event.
website_id	Uuid	Foreign key to the Website table.
session_id	Uuid	Foreign key to the Session table.
visit_id	Uuid	Identifier for a single visit (group of pageviews within 30 mins).
created_at	Timestamptz	Timestamp of when the event occurred.
url_path	VarChar(500)	The path of the page URL.
event_type	Integer	The type of event (e.g., 1 for pageview, 2 for custom event).
event_name	VarChar(50)	The name of the custom event.
referrer_domain	VarChar(500)	The domain of the referring URL.

Sources: prisma/schema.prisma:109-147

Session

This table stores information about a user's session, which is a collection of events from a single visitor.

Column	Type	Description
session_id	Uuid	Primary key for the session.
website_id	Uuid	Foreign key to the Website table.
created_at	Timestamptz	Timestamp of when the session started.
browser	VarChar(20)	Browser used in the session.
os	VarChar(20)	Operating system used.
device	VarChar(20)	Device type (e.g., mobile, desktop).
country	Char(2)	Two-letter country code of the visitor.

Sources: prisma/schema.prisma:41-69