# Kaneo Database Schema: A Complete Guide to Key Entities and Relationships > Explore the Kaneo database schema, detailing 25+ PostgreSQL tables for Workspaces, Projects, Tasks, Members, and Billing. Understand key entities and relationships for efficient data management. - Repository: [kaneo.app/kaneo](https://github.com/usekaneo/kaneo) - Tags: deep-dive - Published: 2026-08-29 --- **The Kaneo database schema defines 25+ PostgreSQL tables in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) using Drizzle ORM, organized around Workspaces as the top-level container for Projects, Tasks, Members, and Billing, with comprehensive support for notifications, time tracking, and third-party integrations.** The Kaneo database schema serves as the foundation for this open-source project management platform, implementing a normalized relational structure in PostgreSQL. All table definitions are centralized in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) utilizing Drizzle ORM's type-safe SQL-like syntax. This architecture enables multi-tenant workspace collaboration while maintaining strict data integrity through foreign key constraints and specialized junction tables. ## Authentication and Identity Entities User authentication and programmatic access are handled through distinct entities that establish identity across the platform. ### User Management The **`userTable`** (line 23) stores account-level authentication data including `id`, `email`, and `name` fields. This entity represents the root identity for all human actors in the system. ### API and OAuth Credentials Programmatic access is controlled through the **`apikeyTable`** (line 990), which stores authentication tokens for external scripts and services. The **`deviceCodeTable`** (line 1031) manages OAuth device-code flow states for secure device authentication, while the **`mcpOauthStateTable`** (line 1062) maintains state for the Message Control Protocol (MCP) OAuth flow. ## Workspace Management Entities Workspaces function as the primary collaboration boundary, with dedicated tables handling membership, billing, and access control. ### Core Workspace Structure The **`workspaceTable`** (line 141) defines the top-level container that isolates projects, tasks, and resources. Every workspace maintains associated billing information in the **`workspaceBillingTable`** (line 178), which stores plan details and Stripe-related payment data. ### Membership and Roles Users join workspaces through the **`workspaceUserTable`** (line 153), a junction table that assigns roles (owner, admin, member, guest, or custom) to workspace members. Custom role definitions are stored in the **`workspaceRoleTable`** (line 285), allowing workspace-specific permission schemes. Pending invitations are tracked in the **`invitationTable`** (line 259), managing the state of users who have been invited but not yet joined. For organizational purposes, the **`teamTable`** (line 225) creates optional groupings of users within a workspace, with membership managed through the **`teamMemberTable`** (line 241) junction table. ## Project and Task Management Entities The Kanban-style project management features rely on a hierarchical structure of projects, columns, and tasks. ### Project Structure The **`projectTable`** (line 311) contains containers for related tasks within a workspace. Each project can define workflow automation through the **`workflowRuleTable`** (line 369), which stores rules for automated task movements or property changes based on status transitions. ### Kanban Columns and Tasks Visual organization is handled by the **`columnTable`** (line 342), defining Kanban stages such as "To-Do" or "In Progress." The central work item is the **`taskTable`** (line 401), which links to projects, columns, and optional assignees while maintaining state fields for tracking progress. Categorization is supported through the **`labelTable`** (line 633), which stores taggable metadata scoped to individual workspaces and applicable to tasks. ## Collaboration and Activity Tracking Rich collaboration features require entities that track time, comments, relationships, and complete activity histories. ### Task Interactions User-generated discussions are stored in the **`commentTable`** (line 932). Time tracking data is captured in the **`timeEntryTable`** (line 514), recording hours spent on specific tasks by individual users. ### Task Relationships and History Complex dependencies are managed through the **`taskRelationTable`** (line 963), a many-to-many junction table that links tasks with relationship types such as "blocks" or "duplicates." The **`activityTable`** (line 546) maintains an immutable history of all actions—including status changes and metadata updates—providing a complete audit trail for each task. ### External References Arbitrary URLs attached to tasks, such as pull request links, are stored in the **`externalLinkTable`** (line 895), enabling rich references to external resources without leaving the Kanban interface. ## Notification System Entities Real-time updates are managed through a sophisticated notification architecture with granular user preferences. ### Core Notification Delivery The **`notificationTable`** (line 665) stores all notifications sent to users regarding task assignments, mentions, and system events. User-specific settings are controlled through the **`userNotificationPreferenceTable`** (line 695), which defines global notification preferences per user. ### Granular Filtering Rules Advanced filtering capabilities are provided by **`userNotificationWorkspaceRuleTable`** (line 742) for workspace-level notification filters and **`userNotificationWorkspaceProjectTable`** (line 788) for project-specific rules. This three-tier system allows users to receive relevant updates while minimizing noise from high-activity workspaces. ## Integration Entities Third-party connectivity is abstracted through generic and service-specific integration tables. ### Generic and GitHub Integrations The **`integrationTable`** (line 867) provides a generic foundation for third-party service connections such as Slack or Discord. GitHub-specific integration data, including repository mappings and webhook configurations, is stored separately in the **`githubIntegrationTable`** (line 845) for optimized query patterns and GitHub-specific metadata storage. ## Entity Relationships and Architectural Patterns The Kaneo database schema follows strict normalization principles with **Workspace** as the root entity. Every project, task, label, and role references a `workspace_id` foreign key, ensuring complete data isolation between tenants. Tasks serve as the hub for activity data, accumulating related records from the `commentTable`, `timeEntryTable`, `activityTable`, and `taskRelationTable`. This design supports complex querying for task histories while maintaining referential integrity through Drizzle ORM's type-safe relations. The notification system implements a hierarchical preference model, where global user preferences can be overridden by workspace-specific rules and further refined by project-level configurations, enabling fine-grained control over information flow. ## Summary - **Centralized Schema**: All 25+ entities are defined in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) using Drizzle ORM's PostgreSQL dialect. - **Workspace-Centric**: The `workspaceTable` (line 141) acts as the top-level boundary, with all major entities referencing it via `workspace_id`. - **Task-Focused**: The `taskTable` (line 401) aggregates comments, time entries, activities, and relations, making it the primary work unit in the system. - **Junction Pattern**: Many-to-many relationships, including workspace membership and task dependencies, are implemented through explicit junction tables like `workspaceUserTable` (line 153) and `taskRelationTable` (line 963). - **Extensible Notifications**: A three-tier notification preference system allows filtering at global, workspace, and project levels through dedicated entities starting at line 695. ## Frequently Asked Questions ### What ORM does Kaneo use for its database schema? Kaneo uses **Drizzle ORM** to define and manage its PostgreSQL database schema. All table definitions are centralized in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts), providing type-safe SQL-like syntax for database operations and migrations. ### How does Kaneo handle multi-tenancy in its database design? Multi-tenancy is implemented through a **Workspace-centric architecture** where every major entity—including projects, tasks, labels, and billing records—contains a `workspace_id` foreign key referencing the `workspaceTable` (line 141). This ensures strict data isolation between different organizations using the platform. ### What is the purpose of the Task Relation entity in Kaneo? The `taskRelationTable` (line 963) implements **many-to-many relationships between tasks**, allowing users to define dependencies such as "blocks," "duplicates," or "relates to" connections. This junction table enables complex project management workflows where task completion order and dependencies are explicitly tracked. ### Where are custom workspace roles defined in the Kaneo schema? Custom roles are stored in the `workspaceRoleTable` (line 285), which is scoped to individual workspaces through a foreign key relationship. These custom definitions complement the standard roles (owner, admin, member, guest) stored in the `workspaceUserTable` (line 153), allowing workspace administrators to create tailored permission schemes.