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

> Explore the Kaneo database schema structure for tasks, projects, and workspaces. Learn how PostgreSQL and Drizzle ORM efficiently manage your data with optimized indexes and foreign keys.

- Repository: [kaneo.app/kaneo](https://github.com/usekaneo/kaneo)
- Tags: deep-dive
- Published: 2026-08-06

---

**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.

```ts
// 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.

```ts
// 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.

```ts
// 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

```ts
// 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

```ts
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`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts). This centralized approach simplifies schema migrations and makes foreign key relationships immediately visible across the entire data model.