ClickHouse export

Click events live in ClickHouse (table click_events). Today you get them out with a scheduled export to S3-compatible storage; the dashboard also offers async CSV exports from Analytics.

Planned — not available yet; see roadmap. The following are not implemented: a per-workspace read-only ClickHouse DSN (/clickhouse-dsn), Parquet output, BigQuery and Snowflake destinations (the API accepts those kind values but the export worker rejects them), per-region DSN hosts, and a conversions table in ClickHouse (conversions live in Postgres). Sections 3 and 4 below describe what exists today.

1. What exists today

Capability	Status
Scheduled export to S3 / MinIO (`kind: "s3"`)	Available
Schedules	`hourly` or `daily` (default `daily`)
Output format	gzip-compressed NDJSON, one click per line
Async CSV export from the dashboard	Available (`/analytics-exports`)
Direct read-only ClickHouse DSN	Planned
Parquet output	Planned
BigQuery / Snowflake destinations	Planned

2. Schema

Raw clicks are stored in click_events:

Column	Type	Notes
`timestamp`	`DateTime64(3, 'UTC')`	millisecond precision
`link_id`	`UInt64`	link row id
`workspace_id`	`UInt64`
`domain_id`	`UInt64`
`slug`, `host`	`LowCardinality(String)`
`user_agent`, `referer`	`String`
`ip`	`String`	stored in full, not truncated
`country_code`	`FixedString(2)`	ISO-3166-1 alpha-2
`device`, `browser`	`LowCardinality(String)`
`destination`	`String`	resolved destination URL
`is_suspicious`, `suspicion_reasons`	`UInt8`, `Array(String)`	click-fraud heuristic flag
`anonymizer_type`	`LowCardinality(String)`	VPN / proxy classification

There is no per-click click_id, and UTMs are not stored as separate columns; they are part of the destination URL.

The scheduled export currently writes these fields per click: timestamp, link_id, workspace_id, domain_id, slug, host, user_agent, referer, ip, country_code, device, browser, destination. (device, browser and destination are exported as empty strings at the moment.)

3. Scheduled S3 export

Requires the export.manage permission.


curl -X PUT \
  https://api.elido.app/v1/workspaces/1/export \
  -H "Authorization: Bearer $ELIDO_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "kind": "s3",
    "enabled": true,
    "schedule": "hourly",
    "destination": { "bucket": "acme-bi-warehouse", "prefix": "elido" }
  }'

Manage configs with GET /v1/workspaces/{id}/export, PATCH /v1/workspaces/{id}/export/{kind}/enabled and DELETE /v1/workspaces/{id}/export/{kind}.

Each run writes one object per workspace: <prefix>/exports/<workspace_id>/<YYYY-MM-DD>/clicks_<HHMMSS>.ndjson.gz. The first run exports all history; later runs export only clicks since the previous run. Self-hosters: the S3 endpoint and credentials come from the api-core environment.

4. Loading into a warehouse

Because the files are NDJSON, BigQuery (bq load --source_format=NEWLINE_DELIMITED_JSON), Snowflake (COPY INTO with TYPE = JSON) and Redshift can load them from the bucket with their standard loaders. Native Parquet and push-style BigQuery / Snowflake destinations are planned.

5. Edge cases

Latency - exports lag by the schedule interval (hourly = up to 60 minutes behind).
Retention - there is currently no automatic expiry (TTL) on click_events; plan-based retention windows are planned.
Region pinning - not available yet; all click data is stored in the EU deployment.
Schema evolution - columns are added with ALTER TABLE ... ADD COLUMN IF NOT EXISTS and defaults; existing columns are not renamed.