ClickHouse analytics store and Kafka ingest
ADR-0026. Both are optional: when they are not configured, the system keeps working as before with Postgres + BullMQ.
docs/adr/0026-olcek-mimarisi.md. Both are optional: when they are not configured, the system keeps working as before
with Postgres + BullMQ.
Migrating to ClickHouse
- Server: managed ClickHouse (Cloud, Altinity) or your own cluster; the HTTP(S) interface must be enabled. Locally:
npm run infra:install+npm run infra:up(26.8 LTS, http://127.0.0.1:8123). - Settings (API and worker):
CLICKHOUSE_URL,CLICKHOUSE_USER,CLICKHOUSE_PASSWORD(secret),CLICKHOUSE_DATABASE. - Schema:
npm run ch:migrate(TTLs come from theRETENTION_*_DAYSvalues). Rollback:npm run ch:migrate:down --workspace @pushizo/db. - Enable the outbox:
npm run ch:outbox:enable. From this point on, changes todeliveries,delivery_events,events,conversionsare written toanalytics_outbox; the worker'sanalytics-syncjob (every 5 s) ships them to ClickHouse. - Historical data:
npm run ch:backfill --workspace @pushizo/db -- --from=2026-07-01(back as far as the retention period). It adds data to the outbox day by day and ships it; re-running is safe (ReplacingMergeTree version). - Comparison:
ANALYTICS_STORE=dual— reports read from both stores and return the Postgres result; if the difference exceeds 0.5%,analytics_store_mismatch_total{method,reason}is incremented and a warning is logged. Expect at least one week of zero differences. - Cutover: per org via the
analytics.clickhousefeature flag (admin → Feature flags), or for everyone withANALYTICS_STORE=clickhouse. Fallback:ANALYTICS_STORE=postgres(instant; the data also remains in Postgres).
Monitoring
| Signal | Threshold / meaning |
|---|---|
select count(*), min(enqueued_at) from analytics_outbox | Backlog; if the oldest row is > 30 s old, shipping is behind (plan acceptance: p95 < 30 s) |
analytics-sync errors in the worker log | ClickHouse access/password; rows wait in the outbox, nothing is lost |
analytics_store_mismatch_total | Must be zero in dual mode |
ClickHouse system.mutations | Completion of delete mutations (KVKK (Turkish data protection law), retention) |
Deletion and retention
- User/app deletion, organization retention period and event definition retention are done with
DELETEin Postgres; a trigger writes the row to the outbox, and the sync deletes any row it cannot find at the source from ClickHouse as well (lightweight DELETE). - The general retention period is applied in Postgres by dropping partitions (the trigger does not fire): the maintenance job applies the same cutoff in ClickHouse as well; the tables also carry a TTL with the same value. Conversions have no TTL (revenue reporting; the user link is anonymized).
Kafka ingest (optional)
- Cluster: managed Kafka (MSK, Confluent, Aiven) or locally
npm run infra:install -- --kafka+npm run infra:up(Kafka 4.0 KRaft, 127.0.0.1:9092). - Settings:
INGEST_BUS=kafka,KAFKA_BROKERS, and if neededKAFKA_SSL=true,KAFKA_SASL_MECHANISM,KAFKA_USERNAME,KAFKA_PASSWORD(secret). If the topic (KAFKA_EVENTS_TOPIC, defaultevents.raw) does not exist, it is created withKAFKA_EVENTS_PARTITIONSpartitions; the key isapp_id(ordering within an app is preserved). - Behavior: the API writes events to the topic and returns 202; the worker group that processes the
eventsqueue consumes them, inserts them into Postgres withon conflict do nothing, and runs the processing job and live stream broadcast for new events. The offset is committed once the Postgres write succeeds (at least once; redelivery is idempotent). - Monitoring:
ingest_events_total{result=inserted|invalid}, consumer group lag (kafka-consumer-groups.sh --describe --group pushizo-events). - Fallback: with
INGEST_BUS=bullmqthe API writes directly to Postgres again; messages left in the topic are consumed by starting the worker once more withINGEST_BUS=kafka(idempotent).
Cells (EU/TR data residency): setup, region moves, cleanup, DR
ADR-0032. A cell is a complete Pushizo installation with its own Postgres/Redis/ClickHouse/S3 and API/worker deployment. One cell (CONTROL_PLANE_MODE=true) is also the control plane: the org → cell mapping (org_regions),…
Go dispatcher
ADR-0026 §3. Moves push sending (APNs, FCM, HMS, Web Push) to a Go service (services/dispatcher); rendering, claiming and applying results stay in the worker. It is optional and can be rolled back instantly: DISPATCHER_GO_CHANNELS…