The Postgres schema
Deckgauge's relational schema is one Prisma file — packages/db/prisma/schema.prisma — holding 71 models and 17 enums across 30 applied migrations. Every model belongs to one of eight clusters, and almost every one of them hangs, directly or through a parent, off a single tenant root: Organization.
The eight model clusters
Reading the schema top to bottom is the slow way in. Reading it by cluster is faster, because the clusters barely reference each other — a change to roadmaps almost never touches org trees.
| Cluster | Principal models | What it owns |
|---|---|---|
| Tenancy | Organization, OrgMembership, User | The tenant root, invitations, and the local identity a Keycloak token resolves to. |
| Board | Board, Group, Project, BoardColumn, ProjectFieldValue, BoardStatus, BoardOwner | The grid itself: rows, their groups, and the board's own status and owner vocabularies. |
| Source connections | JiraInstance, GitHubInstance, GitLabInstance, AzureDevOpsInstance and their *ProjectSync / *RepoSync / Board*Source models | Credentials, what to fetch, and which board it lands on. |
| Access | BoardAccess, RoadmapAccess, OrgTreeAccess, EmployeeBoardAccess, ComparisonAccess | Per-entity ACLs. Four of the five share the BoardAccessRole enum. |
| Views & widgets | BoardView, DashboardWidget, RoadmapConfig, Comparison | Tabs on a board and the widget instances configured on them. |
| Org trees | OrgTree, OrgEmployee, OrgEmployeeAlias, EmployeeBoard and its own group/column/member models | The people hierarchy and the separate, parallel board implementation built on it. |
| Roadmaps & timesheet | Roadmap, RoadmapGroup, RoadmapGanttConfig, TimesheetStatusRule, OrgTreeTimesheetConfig | Standalone roadmap entities and the rules that decide what counts as in-progress time. |
| Advisor & notifications | AdvisorConfig, AdvisorSession, AdvisorMessage, AdvisorChangeSet, Notification, NotificationPreference | AI advisor conversations, its reviewable change sets, and the in-app notification feed. |
The board tree
A board is a four-level tree, and every level cascades on delete from the one above it.
The core relational path. Custom cells hang off the row; the column defines their type.
Project is the row, and it is by far the widest model in the schema. It carries the system fields directly (name, status, owner, startDate, endDate, dueDate, costClassification), while every custom column's value lives in a ProjectFieldValue keyed by (projectId, columnId). It also carries a per-provider identity block — jiraKey, githubIssueId, adoWorkItemId — each with its own uniqueness constraint scoped to the board, which is what stops the same issue landing twice.
Status and owner are per-board vocabularies
BoardStatus and BoardOwner are rows, not enums, unique by label within a board. The ProjectStatus enum still exists alongside them and maps to display names (Not started, In progress, At risk, Blocked, Done), so a row can carry both a legacy enum value and a reference to the board's own status row.
Deleting a synced row is a permanent decision
Delete a row that came from a provider and the board writes a BoardSyncExclusion — keyed by (boardId, source, externalId) — so the next sync does not resurrect it. It survives re-syncs by design. Clearing it means removing the exclusion, not re-running the sync.
How a source connection is modelled
Every provider follows the same three-layer pattern, and each layer exists because it has a different lifetime.
| Layer | Model | Holds |
|---|---|---|
| 1 — credential | JiraInstance, GitHubInstance, GitLabInstance, AzureDevOpsInstance | Host, token, tenant, and who owns it. One per connected account. |
| 2 — what to fetch | JiraProjectSync, GitHubRepoSync, GitLabProjectSync, AzureDevOpsProjectSync (plus AdoRepoSyncState) | One project or repository, its per-stream watermarks, backfill window, and last error. |
| 3 — where it lands | BoardJiraSource, BoardGitHubSource, BoardGitLabSource, BoardAdoSource | The board and target group, plus filters, status mapping, and which fields sync. |
Layer 2 is where the sync's memory lives. GitHubRepoSync keeps six independent watermarks — pull requests, commits, reviews, workflow runs, deployments and issues — because those streams page at different rates and one falling behind must not rewind the others.
Layer 3 is a join table, unique on (boardId, syncId). That is what makes one connected repository feed several boards, each with its own filters, without fetching it more than once.
Ownership and provenance are different columns
Each instance model carries both createdById and ownerUserId, and they are not interchangeable. createdById is provenance — it feeds the "Added by" label and is never a permission check. ownerUserId is the gate: NULL means the connection is organization-wide, a user id means it is personal. It is stamped at creation and never re-derived, so promoting or demoting somebody cannot move a credential across that boundary.
ownerUserId uses onDelete: Restrict rather than SetNull. Setting it to null would silently turn a personal connection into an organization-wide one — publishing a credential — the moment a user row was deleted.What happens when a human edits a synced cell
Sync would otherwise overwrite manual edits on its next pass. Two columns on Project prevent that:
overriddenFields— the field keys a person has edited and not reverted. Sync skips exactly these cells. Keys use the vocabulary inpackages/shared/src/sync-field-registry.ts: plain names forProjectscalars,col:<columnId>for custom columns.preOverrideValues— for each of those keys, the value the field held immediately before its first manual edit. That is what "revert to synced value" restores. Sync never writes it, and entries are pruned on revert.
Conventions every model follows
- Primary keys are string ids. Most default to
uuid();GitHubRepoSync,PrJiraLinkand the advisor models default tocuid(). Validating those as UUIDs fails at runtime. - Names are mapped, not renamed. Fields are camelCase in code and snake_case in the database via
@map; tables use@@map. - Tenant-rooted models carry
organizationId; everything else inherits tenancy through a parent relation. Adding a model means deciding which of the two it is before writing the migration. - Composite uniqueness does the deduplication.
@@unique([boardId, jiraKey, jiraProjectKey])and its siblings are what make a re-sync an update rather than an insert. - Indexes lead with the tenant where a read is tenant-scoped, so no reader can accidentally scan across organizations.
packages/dbis the only place Prisma is imported. Model types are re-exported from there; a new model that is not re-exported breaks the API build.
Changing the schema
Edit schema.prisma, hand-write the SQL under packages/db/prisma/migrations/, and apply it with pnpm --filter @deckgauge/db migrate:deploy. Prisma's migrate:dev is not usable here — see Upgrades for why, and Development setup for the surrounding loop.
_prisma_migrations. Changing an already-applied file — even a comment — makes every later migrate:deploy fail on a checksum mismatch, which reads as an unrelated error. Add a new migration instead.Related
- The data model — how this schema divides work with ClickHouse.
- ClickHouse schema — the analytics tables this one configures.
- Code conventions — the service and route split that queries these models.
- Access control — the access models above, from a user's side.
- Sync scheduling — what actually advances the watermarks on layer 2.
Frequently asked
- Where is the Postgres schema defined?
- In one file: packages/db/prisma/schema.prisma. It holds 71 models and 17 enums, and packages/db re-exports the generated Prisma client and its model types. Nothing outside that package opens a Prisma connection of its own.
- What is the difference between a Group and a BoardStatus?
- A Group is a horizontal band of rows on the board and owns row ordering. A BoardStatus is one entry in the board’s own status vocabulary, referenced by rows through statusId. Both are per-board and both are unique by name within a board.
- Why are there three models per source connection?
- Credentials, what to fetch, and where it lands are three different lifetimes. An Instance holds the credential, a ProjectSync or RepoSync holds what to fetch and how far it has got, and a Board*Source attaches that to one board with its own filters and mappings.
- How do I add a column to a table?
- Edit schema.prisma, then hand-write the migration SQL under packages/db/prisma/migrations and apply it with migrate:deploy. Prisma’s migrate:dev is not usable in this repository. Never edit a migration that has already been applied — Prisma stores its SHA-256 and will refuse to deploy.
Last updated