Kaneo Database Schema: A Complete Guide to Key Entities and Relationships

The Kaneo database schema defines 25+ PostgreSQL tables in 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 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 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, 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}