The ClickHouse schema
Deckgauge's analytics live in a single ClickHouse database called cockpit, holding 23 organization-scoped tables. Each one is append-only, written exclusively by apps/worker, and read exclusively through parameterized queries built in apps/api/src/intelligence-query/builders. The DDL is not generated: it is hand-written SQL under clickhouse/schemas/.
Every table, its sort key and its partition
The sort key is the thing to read first. ClickHouse has no primary-key constraint — the ORDER BY tuple is the row's identity for deduplication, and it is also the index. Every one of these tuples begins with organization_id, so the column below shows what follows it.
| Table | Sort key after organization_id | Partition by |
|---|---|---|
jira_issues | project_key, key | created_at month |
jira_transitions | project_key, issue_key, transitioned_at | transitioned_at month |
jira_worklogs | project_key, issue_key, id | started_at month |
github_issues | repo_full_name, number | created_at month |
github_milestones | repo_full_name, number | created_at month |
github_pull_requests | repo_full_name, number | created_at month |
github_commits | repo_full_name, sha | committed_at month |
github_reviews | repo_full_name, pull_request_number, id | submitted_at month |
github_workflow_runs | repo_full_name, run_id | created_at month |
github_deployments | repo_full_name, deployment_id | created_at month |
gitlab_merge_requests | project_path, iid | created_at month |
gitlab_commits | project_path, sha | committed_at month |
gitlab_reviews | project_path, merge_request_iid, id | submitted_at month |
gitlab_issues | project_path, iid | created_at month |
ado_work_items | org_url, project, ado_id | created_at month |
ado_transitions | project, work_item_id, changed_at | changed_at month |
ado_pull_requests | org_url, project, pr_id | created_at month |
ado_commits | repo_url, sha | committed_at month |
ado_reviews | repo_id, pull_request_id, reviewer_login | submitted_at month |
ado_deployments | org_url, project, kind, deployment_id | started_at month |
developer_identity_map | provider, login | — |
board_item_classification | provider, issue_key | — |
jira_flow_efficiency_state | project_key, issue_type, week_start | week_start month |
Twenty-two of the 23 use ReplacingMergeTree. Twenty version on synced_at; github_workflow_runs and github_deployments version on _ingested_at instead. The twenty-third, jira_flow_efficiency_state, is an AggregatingMergeTree — it is a materialized view's destination, not an ingest target.
The two tables that are not provider data
developer_identity_mapcollapses several provider logins onto onecanonical_login, so a person who isjsmithon GitHub andj.smith@…in Jira counts once. Widgets that group by author read through it.board_item_classificationmaps a ticket key toCAPEXorOPEX, carryingboard_idand the originating Postgresproject_idas provenance. It is the one table fed from Deckgauge's own state rather than a provider.
Deduplication happens on merge, not on insert
ReplacingMergeTree(version) keeps the row with the highest version among rows sharing a sort key — eventually. ClickHouse collapses duplicates when it merges parts in the background, which may be seconds or hours after the insert. Until then, both rows are visible to a plain SELECT.
That is deliberate, and it is what makes re-syncing safe: a job can re-insert the same pull request without checking whether it is already there. It also means a query that must not double-count has to say so explicitly, with FINAL or with an argMax over the version column.
argMax over a Nullable column returns NULL whenever the winning row's value is null — which is not the same as "no rows". Several analytics columns (merged_at, closed_at, cycle_time_hours) are nullable by design.Tenancy is enforced by the server, not the query
Putting organization_id first in every sort key is a performance decision and a safety one. The safety half is a ClickHouse row policy: each organization gets a role, each table gets a policy attached to that role, and every read goes through a per-request client that activates the caller's role before running the query.
The list of tables that must be covered is not inferred from the schema directory — it is CH_TENANT_TABLES in packages/db/src/ch-tenancy-tables.ts, which also records each table's pre-tenancy sort key. Adding a table to clickhouse/schemas/ without adding it there creates a table nothing isolates.
chReadRoleFor(orgId)A widget query never names organization_id. The activated role decides what the same SQL can see.
The one materialized view
Deckgauge keeps a single materialized view, mv_jira_flow_efficiency, writing into jira_flow_efficiency_state. It maintains weekly Jira cycle-time aggregates — a count, a mean, and the 50th and 90th percentiles — as AggregateFunction states, which queries finalize with countMerge, avgMerge and quantileMerge.
Two traps are worth knowing before adding a second one. A TO-target materialized view matches its SELECT output to the destination table by column name, not by position — aliasing organization_id to anything else does not error, it silently writes the type default and blends every tenant into one group. And a Nullable expression will not write into a non-nullable state column: the view's WHERE guarantees non-null, then assumeNotNull strips the wrapper, or ClickHouse rejects the insert with CANNOT_CONVERT_TYPE.
Some facts are decided at query time on purpose
ado_deployments deliberately has no is_production column; one existed and was dropped. Whether a deployment reached production is decided when the widget query runs, in apps/api/src/widgets/unions.ts, from the environment name, the release-definition name, the source branch, and any per-project override.
The reason is a property of the ingest model rather than a preference. A deployment row is fetched exactly once — the watermark never re-reads it — so a verdict frozen at ingest could never be revised when the classification rules changed. The same shape once froze every Azure DevOps pull request as active. Anything derived from configuration that a user can edit belongs in the query, not in the row.
Changing the schema
Add a numbered .sql file under clickhouse/schemas/ and run pnpm migrate:clickhouse. Files are applied in filename order on every deploy, so each statement must be idempotent — CREATE TABLE IF NOT EXISTS for new tables, ALTER TABLE … IF EXISTS for changes to existing ones.
That last part is easy to miss: because the create files use IF NOT EXISTS, editing a column out of one only affects fresh installs. An existing table keeps the column until a separate ALTER file removes it. Both files then stay in the directory permanently.
organization_id first in ORDER BY · a version column for ReplacingMergeTree · a monthly PARTITION BY on the row's own event time · an entry in CH_TENANT_TABLES.Related
- The data model — how these tables relate to Postgres.
- Postgres schema — the sync configuration that decides what lands here.
- Adding a widget — writing a builder that queries these tables.
- How a widget gets its number — watermarks, backfills, and why a widget reads zero.
- Backup and restore — these tables are exported one by one, with
FINAL.
Frequently asked
- What ClickHouse tables does Deckgauge create?
- Twenty-three, all in a database named cockpit: three Jira tables, seven GitHub, four GitLab, six Azure DevOps, a developer identity map, a CapEx/OpEx classification table, and one aggregate state table behind a materialized view.
- Why do my ClickHouse queries return duplicate rows?
- Because ReplacingMergeTree deduplicates on background merge, not on insert. Two rows with the same sort key can both be visible until the parts merge. Use FINAL, or aggregate with argMax on the version column, if the query must not double-count.
- How is one organization stopped from reading another’s analytics?
- By ClickHouse row policies. Every table has organization_id first in its sort key, each organization gets its own role and policy, and reads go through a per-request client that activates the caller’s role. A query with no role activated returns no rows.
- How do I add a ClickHouse table?
- Add a numbered .sql file under clickhouse/schemas/ using CREATE TABLE IF NOT EXISTS, put organization_id first in the ORDER BY, register it in CH_TENANT_TABLES, and run pnpm migrate:clickhouse. Every file is re-applied on every deploy, so it must be idempotent.
Last updated