Why does Deckgauge use two databases?

Postgres is the system of record for everything a person edits — boards, rows, access, connection settings. ClickHouse holds the append-only engineering facts (pull requests, commits, reviews, deployments) that dashboards aggregate over millions of rows. Neither engine is good at the other job.

Which store does a board row live in?

Postgres, as a Project row. A synced Jira issue or GitHub issue becomes a Project row in Postgres and, separately, an analytics row in ClickHouse. The two are written by the same worker job but never joined at query time.

Can I query ClickHouse directly?

Yes, but only through an identity scoped to one organization. Every analytics table carries organization_id first in its sort key and is guarded by a ClickHouse row policy, so a read that does not activate the caller’s organization role returns nothing.

What happens if ClickHouse is down?

Boards, roadmaps, timesheets and org trees keep working, because they read Postgres. Intelligence widgets return an explicit empty state. Sync jobs that dual-write will fail on the ClickHouse half and retry from their last watermark.

The data model

Deckgauge stores its data in two engines with two different jobs. Postgres is the system of record: every board, row, user, permission and connection setting a person can edit. ClickHouse is the analytics store: append-only engineering facts — pull requests, commits, reviews, deployments — that dashboards aggregate over. Nothing is joined across the two at query time.

deckgauge · data model

Jira · GitHub · GitLab · Azure DevOps

provider REST APIs

apps/worker

BullMQ sync jobs

Postgres

71 Prisma models · system of record

ClickHouse

23 tables · cockpit database

apps/api

Prisma services · intelligence-query builders

apps/web

boards · dashboards · org trees

One writer, two stores, two read paths. The worker is the only process that writes to both.

What each store is responsible for

The split is by access pattern, not by subject. Postgres answers "what is the current state of this one board, and who may see it" with transactions and foreign keys. ClickHouse answers "how many merged pull requests per week over the last year, by author" with a columnar scan.

	Postgres	ClickHouse
Role	System of record	Analytics store
Schema source	`packages/db/prisma/schema.prisma`	`clickhouse/schemas/*.sql`
Size	71 models, 17 enums, 30 migrations	23 organization-scoped tables
Written by	`apps/api` (user edits) and `apps/worker` (sync)	`apps/worker` only
Read by	Every board, roadmap, org tree and timesheet route	Intelligence widgets, drill-downs, comparisons
Mutability	Rows are updated and deleted in place	Rows are inserted; duplicates collapse on merge
Tenant boundary	`organization_id` column + FK cascade	`organization_id` sort key + row policy
If it is down	The product is down	Widgets go empty; the rest works

Where the two stores meet

They meet in exactly one place: a worker sync job. When the Jira job runs, it writes the issue into Postgres as a Project row so it appears in the board grid, and — separately — writes the issue, its changelog and its worklogs into cockpit.jira_issues, cockpit.jira_transitions and cockpit.jira_worklogs. The GitHub, GitLab and Azure DevOps jobs do the same for their own tables.

The consequence developers get wrong most often: most engineering facts have no Postgres copy at all. A pull request is not a board row. Postgres holds only the configuration that decided the PR should be fetched — which repository is connected, whether code intelligence is switched on for it, and how far the sync has got.

Rule of thumbIf a human can type it, it is in Postgres. If a provider emitted it and a chart aggregates it, it is in ClickHouse.

The seam in one job

Read config from Postgres — which GitHubRepoSync rows are due, and what each connected board wants from them.
Call the provider — page through its API from the per-source watermark forward.
Write board rows to Postgres — issues become Project rows in the board's target group.
Write facts to ClickHouse — pull requests, commits and reviews go in via chInsertMany from packages/db.
Advance the watermark in Postgres — only after the write is durable, so an interrupted run resumes rather than restarts.

How tenancy is enforced in each store

Organization is the tenant root, and both stores carry the same organization_id — but they enforce it by completely different mechanisms, and confusing the two is a security bug rather than a style question.

In Postgres, enforcement is application-level: every tenant-rooted model carries an organizationId column with a cascading foreign key to organizations, and services pass it in their where clause. Nothing in the database stops a query that forgets it.

In ClickHouse, enforcement is server-side: each organization gets a role and a row policy, and reads go through a per-request scoped client that activates the caller's role (chReadRoleFor in packages/db/src/ch-read-scope.ts). A query that activates no role returns no rows rather than every tenant's rows.

Never import the barrel for readspackages/db's barrel constructs the ingest ClickHouse client eagerly at import time, and that identity carries a permissive policy. Read paths deep-import @deckgauge/db/dist/ch-read-scope.js for that reason.

Conventions that hold across both stores

Identifiers are strings, never integers. Postgres models default to uuid(); a handful of newer ones — GitHubRepoSync, PrJiraLink, and the advisor models — default to cuid(). Validating a sync id as a UUID is a real and recurring bug.
Provider identity is preserved, not replaced. A Jira issue keeps its key, a GitHub PR keeps repo_full_name plus number, an ADO work item keeps its numeric id. Deckgauge's own id is additional.
Column names are snake_case in the database and camelCase in code. Prisma bridges them with @map / @@map; ClickHouse DDL is snake_case throughout, so ClickHouse row objects use snake_case keys in TypeScript too.
Timestamps are UTC DateTime in both, and every ClickHouse table carries a synced_at (or _ingested_at) that doubles as the dedup version.
Request and response shapes live in packages/shared as Zod schemas — neither database type is the wire type.

Where to go next

Postgres schema — the 71 Prisma models, grouped, with the relations that matter.
ClickHouse schema — every analytics table, its engine, and its sort key.
Architecture — the apps and packages that sit above these two stores.
How a widget gets its number — the same pipeline from a user's side.
Backup and restore — what has to be backed up, per store.

Frequently asked

Why does Deckgauge use two databases?: Postgres is the system of record for everything a person edits — boards, rows, access, connection settings. ClickHouse holds the append-only engineering facts (pull requests, commits, reviews, deployments) that dashboards aggregate over millions of rows. Neither engine is good at the other job.
Which store does a board row live in?: Postgres, as a Project row. A synced Jira issue or GitHub issue becomes a Project row in Postgres and, separately, an analytics row in ClickHouse. The two are written by the same worker job but never joined at query time.
Can I query ClickHouse directly?: Yes, but only through an identity scoped to one organization. Every analytics table carries organization_id first in its sort key and is guarded by a ClickHouse row policy, so a read that does not activate the caller’s organization role returns nothing.
What happens if ClickHouse is down?: Boards, roadmaps, timesheets and org trees keep working, because they read Postgres. Intelligence widgets return an explicit empty state. Sync jobs that dual-write will fail on the ClickHouse half and retry from their last watermark.

Last updated 27 August 2026