Kaneo Database Schema Structure for Tasks, Projects, and Workspaces: A Complete Guide

Kaneo uses a PostgreSQL database with Drizzle ORM to store workspaces, projects, and tasks in three interconnected tables with cascading foreign keys and optimized indexes.

The Kanban-style project management platform Kaneo organizes its core data model around three primary entities. All table definitions reside in a single schema file, enabling strict referential integrity and efficient query patterns for workspace-scoped project management.

Workspace Table: The Top-Level Container

Workspaces represent the highest-level organizational unit in Kaneo. Each workspace groups multiple projects and serves as the access boundary for team members.

// apps/api/src/database/schema.ts (lines 9-20)
export const workspaceTable = pgTable("workspace", {
  id: text("id").$defaultFn(() => createId()).primaryKey(),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  logo: text("logo"),
  metadata: text("metadata"),
  description: text("description"),
  createdAt: timestamp("created_at", { mode: "date" }).notNull(),
});

Key design decisions:

• slug enforces uniqueness at the database level, enabling human-readable workspace URLs
• createId() generates CUID-style identifiers via Drizzle's $defaultFn
• No foreign keys—workspaces stand alone as the root entity

Project Table: Scoped to Workspaces

Projects belong to exactly one workspace and maintain their own slug-based identifiers. The lastTaskNumber field supports auto-incrementing task numbers within each project.

// apps/api/src/database/schema.ts (lines 73-94)
export const projectTable = pgTable("project", {
  id: text("id").$defaultFn(() => createId()).primaryKey(),
  workspaceId: text("workspace_id")
    .notNull()
    .references(() => workspaceTable.id, { onDelete: "cascade", onUpdate: "cascade" }),
  slug: text("slug").notNull(),
  icon: text("icon").default("Layout"),
  name: text("name").notNull(),
  description: text("description"),
  createdAt: timestamp("created_at", { mode: "date" }).defaultNow().notNull(),
  isPublic: boolean("is_public").default(false),
  archivedAt: timestamp("archived_at", { mode: "date" }),
  lastTaskNumber: integer("last_task_number").notNull().default(0),
}, (table) => [
  unique("project_workspace_id_id_unique").on(table.workspaceId, table.id),
]);

Critical constraints:

• Cascading delete/update on workspaceId ensures projects are cleaned up when workspaces are removed
• Composite unique index (workspaceId, id) prevents duplicate project references within a workspace
• Task numbering is project-scoped via lastTaskNumber rather than global

Task Table: The Most Complex Entity

Tasks link to projects, assignees, and Kanban columns. The schema supports positioning, prioritization, scheduling, and soft-status tracking through multiple foreign key relationships.

// apps/api/src/database/schema.ts (lines 58-99)
export const taskTable = pgTable("task", {
  id: text("id").$defaultFn(() => createId()).primaryKey(),
  projectId: text("project_id")
    .notNull()
    .references(() => projectTable.id, { onDelete: "cascade", onUpdate: "cascade" }),
  position: integer("position").default(0),
  number: integer("number").default(1),
  userId: text("assignee_id").references(() => userTable.id, {
    onDelete: "cascade",
    onUpdate: "cascade",
  }),
  title: text("title").notNull(),
  description: text("description"),
  status: text("status").notNull().default("to-do"),
  columnId: text("column_id").references(() => columnTable.id, {
    onDelete: "set null",
    onUpdate: "cascade",
  }),
  priority: text("priority").default("low"),
  startDate: timestamp("start_date", { mode: "date" }),
  dueDate: timestamp("due_date", { mode: "date" }),
  createdAt: timestamp("created_at", { mode: "date" }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { mode: "date" })
    .defaultNow()
    .$onUpdate(() => new Date())
    .notNull(),
}, (table) => [
  index("task_projectId_idx").on(table.projectId),
  index("task_dueDate_idx").on(table.dueDate),
  index("task_assigneeId_idx").on(table.userId),
  index("task_columnId_idx").on(table.columnId),
  unique("task_project_number_unique").on(table.projectId, table.number),
]);

Index Strategy for Performance

Index Name	Column(s)	Purpose
task_projectId_idx	projectId	Fast lookup of all tasks in a project
task_dueDate_idx	dueDate	Scheduling and calendar views
task_assigneeId_idx	userId	"My tasks" workload queries
task_columnId_idx	columnId	Kanban board rendering
task_project_number_unique	(projectId, number)	Enforce per-project task numbering

Foreign Key Behaviors

• projectId: Cascade delete—removing a project destroys all its tasks
• userId: Cascade delete—handled at application layer for reassignment
• columnId: Set null on delete—tasks survive column deletion, move to unassigned status

Querying the Schema in Practice

Fetch Tasks by Project with Position Ordering

// apps/api/src/task/index.ts (lines 99-102)
import db from "../database";
import { taskTable } from "../database/schema";
import { eq } from "drizzle-orm";

export async function getTasksByProject(projectId: string) {
  return db
    .select()
    .from(taskTable)
    .where(eq(taskTable.projectId, projectId))
    .orderBy(taskTable.position);
}

This pattern leverages the task_projectId_idx index for efficient filtering and uses position for stable Kanban board ordering.

Create Task with Project-Scoped Numbering

import db from "../database";
import { taskTable, projectTable } from "../database/schema";
import { eq, sql } from "drizzle-orm";

export async function createTask(projectId: string, title: string) {
  // Atomic increment of lastTaskNumber handled in transaction
  const [newTask] = await db
    .insert(taskTable)
    .values({
      projectId,
      title,
      status: "to-do",
      priority: "low",
      number: sql`(SELECT last_task_number + 1 FROM project WHERE id = ${projectId})`,
    })
    .returning();
  
  await db
    .update(projectTable)
    .set({ lastTaskNumber: sql`last_task_number + 1` })
    .where(eq(projectTable.id, projectId));
    
  return newTask;
}

Entity Relationship Summary

┌─────────────────┐
│   workspace     │
│   (root)        │
│  ─────────────  │
│  id (PK)        │
│  slug (unique)  │
└────────┬────────┘
         │ 1:N
         ▼ cascade
┌─────────────────┐
│    project      │
│  ─────────────  │
│  id (PK)        │
│  workspaceId(FK)│◄─┐
│  lastTaskNumber │  │
│  slug           │  │
└────────┬────────┘  │
         │ 1:N       │ composite unique
         ▼ cascade   │ (workspaceId, id)
┌─────────────────┐  │
│     task        │  │
│  ─────────────  │  │
│  id (PK)        │  │
│  projectId (FK) │──┘
│  number         │◄──┐
│  userId (FK)    │   │ unique
│  columnId (FK)  │   │ (projectId, number)
│  position       │   │
└─────────────────┘   │
         ▲            │
         │ set null   │
         │ on delete  │
┌─────────────────┐   │
│     column      │   │
│   (Kanban)      │   │
└─────────────────┘◄──┘

Summary

• Workspaces are self-contained root entities with unique slugs for URL routing
• Projects cascade-delete with workspaces and maintain per-project task counters via lastTaskNumber
• Tasks carry the heaviest indexing load—four single-column indexes plus one composite unique constraint optimize the most common query patterns
• Column deletion preserves tasks via set null, while project/workspace deletion cascades to prevent orphaned records
• All schema definitions live in apps/api/src/database/schema.ts as implemented in usekaneo/kaneo

Frequently Asked Questions

What happens to tasks when a project is deleted in Kaneo?

Tasks are automatically deleted due to the cascade delete constraint on taskTable.projectId. This is defined in apps/api/src/database/schema.ts line 60-63: .references(() => projectTable.id, { onDelete: "cascade", onUpdate: "cascade" }). The database handles this at the foreign-key level without application intervention.

How does Kaneo ensure unique task numbers within a project?

The task_project_number_unique composite unique constraint on (projectId, number) enforces this at the database level. The application increments projectTable.lastTaskNumber atomically when creating tasks, as shown in the task controller implementations under apps/api/src/task/controllers/.

Why does the columnId foreign key use set null instead of cascade?

This design choice preserves tasks when Kanban columns are deleted or reorganized. Tasks with set null on columnId fall back to an unassigned state rather than being destroyed, preventing accidental data loss during board restructuring.

Where does Kaneo store its database schema definitions?

All table definitions, indexes, and constraints reside in a single file: apps/api/src/database/schema.ts. This centralized approach simplifies schema migrations and makes foreign key relationships immediately visible across the entire data model.

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