PushizoDocs
OperationsRunbooks

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

  1. 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).
  2. Settings (API and worker): CLICKHOUSE_URL, CLICKHOUSE_USER, CLICKHOUSE_PASSWORD (secret), CLICKHOUSE_DATABASE.
  3. Schema: npm run ch:migrate (TTLs come from the RETENTION_*_DAYS values). Rollback: npm run ch:migrate:down --workspace @pushizo/db.
  4. Enable the outbox: npm run ch:outbox:enable. From this point on, changes to deliveries, delivery_events, events, conversions are written to analytics_outbox; the worker's analytics-sync job (every 5 s) ships them to ClickHouse.
  5. 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).
  6. 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.
  7. Cutover: per org via the analytics.clickhouse feature flag (admin → Feature flags), or for everyone with ANALYTICS_STORE=clickhouse. Fallback: ANALYTICS_STORE=postgres (instant; the data also remains in Postgres).

Monitoring

SignalThreshold / meaning
select count(*), min(enqueued_at) from analytics_outboxBacklog; if the oldest row is > 30 s old, shipping is behind (plan acceptance: p95 < 30 s)
analytics-sync errors in the worker logClickHouse access/password; rows wait in the outbox, nothing is lost
analytics_store_mismatch_totalMust be zero in dual mode
ClickHouse system.mutationsCompletion 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 DELETE in 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)

  1. 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).
  2. Settings: INGEST_BUS=kafka, KAFKA_BROKERS, and if needed KAFKA_SSL=true, KAFKA_SASL_MECHANISM, KAFKA_USERNAME, KAFKA_PASSWORD (secret). If the topic (KAFKA_EVENTS_TOPIC, default events.raw) does not exist, it is created with KAFKA_EVENTS_PARTITIONS partitions; the key is app_id (ordering within an app is preserved).
  3. Behavior: the API writes events to the topic and returns 202; the worker group that processes the events queue consumes them, inserts them into Postgres with on 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).
  4. Monitoring: ingest_events_total{result=inserted|invalid}, consumer group lag (kafka-consumer-groups.sh --describe --group pushizo-events).
  5. Fallback: with INGEST_BUS=bullmq the API writes directly to Postgres again; messages left in the topic are consumed by starting the worker once more with INGEST_BUS=kafka (idempotent).

On this page