Kaneo Database Schema: Complete Guide to Core Tables and Relationships

The Kaneo database schema consists of 16 main PostgreSQL tables organized around workspaces, projects, and tasks, defined in apps/api/src/database/schema.ts and managed through Drizzle ORM migrations in apps/api/drizzle/.

The Kaneo project management platform stores all persistent data in a relational PostgreSQL database orchestrated by Drizzle ORM. Understanding the Kaneo database schema is essential for developers building integrations, customizing deployments, or contributing to the open-source codebase. The schema centers on a workspace-based multi-tenant architecture where projects, tasks, and user activities form the core data model.

Core Entity Tables

The foundation of the Kaneo database schema rests on four primary entities that represent the project management hierarchy: workspaces, projects, columns, and tasks.

Workspace Table

The workspace table (also referred to as organization in the codebase) serves as the top-level collaboration and authorization boundary. Every piece of data in Kaneo belongs to exactly one workspace, enabling multi-tenancy. The schema definition resides in apps/api/src/database/schema.ts, while workspace-specific business logic references this table through foreign keys in nearly every other entity.

Project Table

Defined in apps/api/src/project/schema.ts, the project table acts as a container for tasks within a workspace. Projects inherit workspace scoping and serve as the primary organizing unit for task boards. The table maintains foreign key constraints linking to the workspace table, ensuring data isolation between different organizations.

Column Table

The column table (apps/api/src/column/schema.ts) represents the vertical lanes on a Kanban board—such as "Backlog," "In-Progress," and "Done." Each column belongs to a specific project and defines the workflow stages through which tasks progress. The schema enforces ordering constraints to maintain board layout consistency.

Task Table

Located in apps/api/src/task/schema.ts, the task table stores the core work items. Tasks link to columns (for board position), projects (for ownership), and workspaces (for tenancy). This triple-scoping ensures tasks remain accessible only within their authorized context while supporting flexible board layouts.

Task Relationships and Metadata

Beyond basic task storage, the Kaneo database schema supports complex relationships and metadata attachments.

Task Relations

The task_relation table (apps/api/src/task-relation/schema.ts) implements directed relationships between tasks, supporting parent/child hierarchies and blocked/blocked-by dependencies. This self-referential structure enables complex project management workflows while maintaining referential integrity through foreign keys.

Labels

Defined in apps/api/src/label/schema.ts, the label table provides taggable identifiers attachable to tasks. Labels can be scoped either workspace-wide or to specific tasks, allowing for flexible categorization systems. The implementation uses junction tables to maintain many-to-many relationships between labels and tasks.

External Links

The external_link table (apps/api/src/external-link/schema.ts) tracks URLs attached to tasks that reference resources outside Kaneo. This bridges external documentation, repositories, or third-party tools with internal task tracking.

Collaboration and Activity Tables

Kaneo captures user interactions through dedicated tables for communication and audit trails.

Comments

The comment table (apps/api/src/comment/schema.ts) stores threaded discussions on tasks, including support for @-mentions of workspace members. The schema supports recursive relationships for reply threading and maintains foreign keys to both tasks and users.

Activity

Defined in apps/api/src/activity/schema.ts, the activity table provides an immutable history of user actions—including task creation, status changes, and comment additions. This append-only log supports audit trails and powers the UI timeline features, with each record referencing the acting user and affected entities.

User and Authentication Schema

User identity and session management follow standard security practices with workspace-scoped membership.

User Table

The user table (apps/api/src/user/schema.ts) stores individual account data, including authentication credentials and profile information. Unlike workspace-scoped entities, users exist globally but link to workspaces through membership associations.

Session Management

The session table (apps/api/src/session/schema.ts) tracks active login sessions, storing refresh tokens and IP metadata for security monitoring. This enables multi-device support while maintaining the ability to revoke access remotely.

Invitations

Defined in apps/api/src/invitation/schema.ts, the invitation table manages pending workspace invitations, storing tokens and expiration dates. This facilitates user onboarding while preventing unauthorized workspace access.

Notification System

Kaneo implements a comprehensive notification architecture through two coordinated tables.

Notifications

The notification table (apps/api/src/notification/schema.ts) stores per-user notification entries for events like mentions and assignment changes. Records link to triggering activities and target users, supporting read/unread states.

Notification Preferences

The notification_preferences table (apps/api/src/notification-preferences/schema.ts) controls which activities generate notifications for each user. This enables granular control over notification volume without unsubscribing from critical updates.

Workspace Administration

Administrative functions rely on specialized tables for billing and API access.

Billing

The billing table (apps/api/src/billing/schema.ts) manages subscription states, trial periods, and plan tiers for each workspace. This commercial metadata remains separate from operational data while maintaining workspace-level scoping.

API Keys

Defined in apps/api/src/api-key/schema.ts, the api_key table stores programmatic access credentials scoped to specific workspaces. This enables third-party integrations and automation tools to interact with the Kaneo database schema without full user authentication.

Database Schema Implementation

The physical schema resides in migration scripts under apps/api/drizzle/, with the initial structure defined in 0000_confused_pixie.sql. Subsequent migrations—such as 0010_bouncy_taskmaster.sql and 0044_needy_triathlon.sql—evolve the schema while preserving data integrity.

The TypeScript definitions in apps/api/src/database/schema.ts provide the source of truth for the Drizzle ORM client, which exposes type-safe methods for interacting with these tables:

// Fetch all projects in a workspace
const projects = await client.project.list({ workspaceId });

// Create a new task in a specific column
await client.task.create({
  title: "Write knowledge-base article",
  projectId,
  columnId,
  workspaceId,
});

// Attach a workspace-scoped label to a task
await client.label.attach({
  taskId,
  labelId,
});

The typed client resides in packages/libs/src/index.ts, providing the React frontend with type-safe access to the PostgreSQL schema defined in the API layer.

Summary

• The Kaneo database schema defines 16 core tables spanning project management, user authentication, and workspace administration.
• Workspace serves as the root tenancy boundary, with projects, columns, and tasks forming the hierarchical project structure.
• Task relations, labels, and external links extend core functionality to support complex workflows and integrations.
• Activity and comment tables provide immutable audit trails and threaded discussions.
• Schema definitions live in apps/api/src/database/schema.ts and apps/api/src/*/schema.ts files, synchronized with PostgreSQL through Drizzle migrations in apps/api/drizzle/.

Frequently Asked Questions

How is the Kaneo database schema defined?

The schema is defined using Drizzle ORM in TypeScript files located in apps/api/src/database/schema.ts and individual domain directories like apps/api/src/task/schema.ts. These TypeScript definitions generate SQL migration scripts stored in apps/api/drizzle/, which apply the concrete CREATE TABLE statements to PostgreSQL.

What is the relationship between workspaces and projects?

A workspace represents the top-level organizational boundary (equivalent to a company or team), while projects belong to exactly one workspace. This one-to-many relationship enables multi-tenancy, ensuring complete data isolation between different organizations using the same Kaneo instance.

Where are the database migrations stored?

Database migrations reside in apps/api/drizzle/ as SQL files (e.g., 0000_confused_pixie.sql, 0010_bouncy_taskmaster.sql). These files contain the exact DDL commands executed against PostgreSQL and serve as the versioned history of schema evolution.

How are task dependencies implemented in the schema?

Task dependencies use the task_relation table defined in apps/api/src/task-relation/schema.ts. This table creates directed relationships between tasks—such as parent/child or blocked/blocked-by connections—through foreign keys referencing the task table, enabling complex dependency tracking without hierarchical data structures.

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