π Open source, privacy-first web analytics β a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!
TypeScript chartsclickhousedockergdprgoogle-analyticsmetricsmonitoringmysqlnestjsprivacyredisself-hostedselfhostedstatisticstailwindcssweb-analyticswebanalyticswebsite
// This script initialises the Swetrix cloud edition database and tables if they're absent
const { queriesRunner, dbName, databaselessQueriesRunner } = require('./setup')
const CLICKHOUSE_DB_INIT_QUERIES = [`CREATE DATABASE IF NOT EXISTS ${dbName}`]
const CLICKHOUSE_INIT_QUERIES = [
`CREATE TABLE IF NOT EXISTS ${dbName}.events
(
type LowCardinality(String),
pid FixedString(12),
psid Nullable(UInt64),
sid Nullable(UInt64),
profileId Nullable(String) CODEC(ZSTD(3)),
host Nullable(String) CODEC(ZSTD(3)),
pg Nullable(String) CODEC(ZSTD(3)),
title Nullable(String) CODEC(ZSTD(3)),
dv LowCardinality(Nullable(String)),
br LowCardinality(Nullable(String)),
brv Nullable(String) CODEC(ZSTD(3)),
os LowCardinality(Nullable(String)),
osv Nullable(String) CODEC(ZSTD(3)),
lc LowCardinality(Nullable(String)),
ref Nullable(String) CODEC(ZSTD(3)),
so Nullable(String) CODEC(ZSTD(3)),
me Nullable(String) CODEC(ZSTD(3)),
ca Nullable(String) CODEC(ZSTD(3)),
te Nullable(String) CODEC(ZSTD(3)),
co Nullable(String) CODEC(ZSTD(3)),
cc Nullable(FixedString(2)),
rg LowCardinality(Nullable(String)),
rgc LowCardinality(Nullable(String)),
ct Nullable(String) CODEC(ZSTD(3)),
isp LowCardinality(Nullable(String)),
og Nullable(String) CODEC(ZSTD(3)),
ut LowCardinality(Nullable(String)),
ctp LowCardinality(Nullable(String)),
\`meta.key\` Array(String) CODEC(ZSTD(3)),
\`meta.value\` Array(String) CODEC(ZSTD(3)),
importID Nullable(UInt8),
event_name Nullable(String) CODEC(ZSTD(3)),
eid Nullable(FixedString(32)),
error_name Nullable(String) CODEC(ZSTD(3)),
error_message Nullable(String) CODEC(ZSTD(3)),
stackTrace Nullable(String) CODEC(ZSTD(3)),
lineno Nullable(UInt32),
colno Nullable(UInt32),
error_filename Nullable(String) CODEC(ZSTD(3)),
dns Nullable(UInt32),
tls Nullable(UInt32),
conn Nullable(UInt32),
response Nullable(UInt32),
render Nullable(UInt32),
domLoad Nullable(UInt32),
pageLoad Nullable(UInt32),
ttfb Nullable(UInt32),
created DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created)
ORDER BY (pid, type, created);`,
`ALTER TABLE ${dbName}.events ADD COLUMN IF NOT EXISTS title Nullable(String) CODEC(ZSTD(3)) AFTER pg`,
`ALTER TABLE ${dbName}.events ADD COLUMN IF NOT EXISTS outbound_url Nullable(String) CODEC(ZSTD(3)) AFTER event_name`,
// Error events status table
`CREATE TABLE IF NOT EXISTS ${dbName}.error_statuses (
eid FixedString(32),
pid FixedString(12),
status Enum8('active', 'regressed', 'resolved'),
updated DateTime('UTC') DEFAULT now()
)
ENGINE = ReplacingMergeTree()
PRIMARY KEY (eid, pid);`,
// Sessions table with ReplacingMergeTree for tracking session state.
// Keyed on sid (one row per browsing session, 30 min of inactivity ends it),
// not psid (one value per visitor per day) β see the 2026_08_01_session_id
// migration, which this definition has to stay in sync with.
`CREATE TABLE IF NOT EXISTS ${dbName}.sessions
(
sid UInt64 DEFAULT psid,
psid UInt64,
pid FixedString(12),
profileId Nullable(String) CODEC(ZSTD(3)),
firstSeen DateTime('UTC') CODEC(Delta(4), LZ4),
lastSeen DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = ReplacingMergeTree(lastSeen)
ORDER BY (pid, sid)
PARTITION BY toYYYYMM(firstSeen);`,
// Profile aliases table: maps anonymous (anon_) profile IDs to identified (usr_)
// profile IDs created via the identify API. Resolved at query time with
// argMin(userProfileId, created) so the first identification wins.
`CREATE TABLE IF NOT EXISTS ${dbName}.profile_aliases
(
pid FixedString(12),
anonProfileId String CODEC(ZSTD(3)),
userProfileId String CODEC(ZSTD(3)),
created DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = ReplacingMergeTree()
ORDER BY (pid, anonProfileId, userProfileId);`,
// Profile traits table: arbitrary key/value metadata (email, plan, ...) set
// for identified profiles via the identify API. One row per key so traits
// merge across calls; the latest value of each key wins and an empty value
// means the trait was removed.
`CREATE TABLE IF NOT EXISTS ${dbName}.profile_traits
(
pid FixedString(12),
profileId String CODEC(ZSTD(3)),
key String CODEC(ZSTD(3)),
value String CODEC(ZSTD(3)),
created DateTime64(3, 'UTC') CODEC(Delta(4), LZ4)
)
ENGINE = ReplacingMergeTree(created)
ORDER BY (pid, profileId, key);`,
// Feature flag evaluations table
`CREATE TABLE IF NOT EXISTS ${dbName}.feature_flag_evaluations
(
pid FixedString(12),
flagId String CODEC(ZSTD(3)),
flagKey String CODEC(ZSTD(3)),
result UInt8,
profileId String CODEC(ZSTD(3)),
created DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created)
ORDER BY (pid, flagId, created)
TTL created + INTERVAL 1 YEAR;`,
// Experiment exposures table for tracking which variant each user sees
`CREATE TABLE IF NOT EXISTS ${dbName}.experiment_exposures
(
pid FixedString(12),
experimentId String CODEC(ZSTD(3)),
variantKey String CODEC(ZSTD(3)),
profileId String CODEC(ZSTD(3)),
created DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created)
ORDER BY (pid, experimentId, created)
TTL created + INTERVAL 1 YEAR;`,
// Bot block events table for advanced bot protection reporting
`CREATE TABLE IF NOT EXISTS ${dbName}.bot_blocks
(
pid FixedString(12),
reason LowCardinality(String),
cc Nullable(FixedString(2)),
created DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created)
ORDER BY (pid, created)
TTL created + INTERVAL 90 DAY;`,
// Revenue table for storing payment/transaction data from payment providers
`CREATE TABLE IF NOT EXISTS ${dbName}.revenue
(
pid FixedString(12),
transaction_id String CODEC(ZSTD(3)),
provider LowCardinality(String),
type LowCardinality(String),
status LowCardinality(String),
amount Decimal64(4),
original_amount Decimal64(4),
original_currency LowCardinality(String),
currency LowCardinality(String),
profile_id Nullable(String) CODEC(ZSTD(3)),
session_id Nullable(String) CODEC(ZSTD(3)),
product_id Nullable(String) CODEC(ZSTD(3)),
product_name Nullable(String) CODEC(ZSTD(3)),
metadata String DEFAULT '{}' CODEC(ZSTD(3)),
created DateTime('UTC') CODEC(Delta(4), LZ4),
synced_at DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = ReplacingMergeTree(synced_at)
PARTITION BY toYYYYMM(created)
ORDER BY (pid, transaction_id);`,
`CREATE TABLE IF NOT EXISTS ${dbName}.session_replay_chunks
(
pid FixedString(12),
psid UInt64,
replayId String CODEC(ZSTD(3)),
chunkIndex UInt32,
objectKey String CODEC(ZSTD(3)),
privacyMode LowCardinality(String),
eventCount UInt32,
uncompressedBytes UInt32,
compressedBytes UInt32,
firstEventTimestamp Nullable(UInt64),
lastEventTimestamp Nullable(UInt64),
created DateTime('UTC') CODEC(Delta(4), LZ4),
expiresAt DateTime('UTC') CODEC(Delta(4), LZ4)
)
ENGINE = ReplacingMergeTree(created)
PARTITION BY toYYYYMM(created)
ORDER BY (pid, psid, replayId, chunkIndex);`,
]
const initialiseDatabase = async () => {
try {
await databaselessQueriesRunner(CLICKHOUSE_DB_INIT_QUERIES)
await queriesRunner(CLICKHOUSE_INIT_QUERIES)
} catch (reason) {
console.error(
`[ERROR] Error occured whilst initialising the database: ${reason}`,
)
}
}
initialiseDatabase()
module.exports = {
initialiseDatabase,
}