What Are the Main Tables for Project and Task Management in Kaneo?

Kaneo stores all project and task data in eight core PostgreSQL tables—project, column, task, task_relation, label, task_label, comment, and activity—defined primarily in apps/api/src/database/schema.ts and specialized schema files.

Kaneo is an open-source Kanban-based project management platform built on a PostgreSQL backend. Understanding the main tables for project and task management in Kaneo is essential for developers extending the API, building integrations, or querying the database directly. The schema follows a workspace-scoped hierarchy where projects contain ordered columns, columns contain ordered tasks, and tasks link to metadata through relational junction tables.

Core Project Structure Tables

The project Table

Every Kanban board in Kaneo is represented by the project table defined in apps/api/src/database/schema.ts (lines 311-334). This table stores the board's identity within a specific workspace and includes columns for workspaceId, name, archived status, and position for visual ordering within the workspace UI.

The column Table

Kanban columns that group tasks are stored in the column table defined in apps/api/src/column/schema.ts. Each column references a projectId and maintains a position integer to support drag-and-drop reordering, along with a required name field for the column header.

The task Table

Individual work items reside in the task table defined at lines 436-452 of apps/api/src/database/schema.ts. Critical columns include projectId and columnId for placement, a unique-per-project number for human-readable IDs, title and description for content, assigneeId for ownership, status for workflow state, and position for ordering within the column.

Task Relationships and Metadata Tables

Task Dependencies with task_relation

The task_relation table, defined in apps/api/src/task-relation/schema.ts, enables complex workflow linking such as blocking dependencies or subtask relationships. It stores projectId, taskId, relatedTaskId, and a type field (e.g., "blocks", "duplicates", "relates to") to define the semantic relationship between two tasks.

Label Management System

Categorization tags are stored in the label table (apps/api/src/label/schema.ts) with workspaceId, name, and color columns scoped to the workspace level. The many-to-many association between tasks and labels is implemented through the task_label junction table (implicitly defined as taskLabelTable in the schema), containing only taskId and labelId foreign keys.

Collaboration and Audit Tables

Comments and Activity Tracking

User collaboration data persists in two specialized tables. The comment table (apps/api/src/comment/schema.ts) stores free-form discussion notes attached to tasks via taskId, authorId, and content fields. The activity table (apps/api/src/activity/schema.ts) records immutable audit history—including task creation, moves, and completions—using workspaceId, projectId, taskId, type, and a flexible payload JSON column for structured event data.

Database Schema Implementation Details

All core tables enforce multi-tenancy through mandatory workspaceId columns and maintain referential integrity via foreign key constraints (e.g., task.projectId references project.id). The API layer validates all operations against Zod schemas co-located with table definitions, ensuring type safety across the TypeScript codebase.

// Example: Creating a project with columns maps directly to the project and column tables
await kaneoClient.project.create({
  workspaceId: "ws_123",
  name: "Product Launch",
  columns: [
    { name: "Backlog", position: 0 },
    { name: "In Progress", position: 1 },
  ],
});

Querying Kaneo Tables Programmatically

When interacting with Kaneo's typed client, you manipulate these underlying PostgreSQL tables through intuitive methods that handle the SQL generation.

Fetch all projects within a workspace:

import { kaneoClient } from "@kaneo/libs";

const projects = await kaneoClient.project.list({ workspaceId: "ws_123" });

Create a task in a specific column:

await kaneoClient.task.create({
  projectId: "proj_456",
  columnId: "col_1",
  title: "Design landing page",
  description: "Create mockups and get stakeholder sign-off",
});

Establish a blocking dependency between tasks:

await kaneoClient.taskRelation.create({
  projectId: "proj_456",
  taskId: "task_001",        // blocker
  relatedTaskId: "task_002", // blocked
  type: "blocks",
});

Attach a label to a task via the junction table:

await kaneoClient.taskLabel.create({
  taskId: "task_789",
  labelId: "label_3",
});

Summary

• Eight core tables drive Kaneo's project management: project, column, task, task_relation, label, task_label, comment, and activity.
• Hierarchical structure: Workspaces contain projects, projects contain columns, and columns contain tasks with positional ordering.
• Relationship modeling: The task_relation table handles dependencies and subtasks, while task_label manages many-to-many categorization.
• Audit trail: The activity table provides immutable history for real-time updates and compliance tracking.
• Schema location: Core definitions reside in apps/api/src/database/schema.ts with specialized schemas in adjacent files like apps/api/src/column/schema.ts.

Frequently Asked Questions

What database does Kaneo use for storing project and task data?

Kaneo uses PostgreSQL as its primary datastore. All project, task, and metadata tables are defined in the API layer using TypeScript schema definitions that map to PostgreSQL tables, as seen in apps/api/src/database/schema.ts.

How are task dependencies stored in Kaneo?

Task dependencies are stored in the task_relation table defined in apps/api/src/task-relation/schema.ts. This table links two tasks via taskId and relatedTaskId columns and uses a type field to specify the relationship nature (e.g., blocks, duplicates, relates).

Where is the main database schema defined in Kaneo's codebase?

The central schema definitions for the project and task tables are located in apps/api/src/database/schema.ts. Additional tables like column, comment, activity, and label have dedicated schema files in their respective feature directories under apps/api/src/.

How does Kaneo handle task labeling?

Kaneo implements labels through a many-to-many relationship. The label table stores tag definitions (name, color) at the workspace level, while the task_label junction table (referenced as taskLabelTable in the schema) connects labels to specific tasks using composite keys of taskId and labelId.

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