# Kaneo API Database Schema: Complete Drizzle ORM Reference for PostgreSQL

> Explore the Kaneo API database schema using Drizzle ORM and PostgreSQL. Discover over 30 tables for authentication, projects, time tracking, and integrations. See the full reference.

- Repository: [kaneo.app/kaneo](https://github.com/usekaneo/kaneo)
- Tags: api-reference
- Published: 2026-08-09

---

**The Kaneo API database schema uses Drizzle ORM with PostgreSQL, defining over 30 tables in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) that handle user authentication, workspace management, Kanban project tracking, time entries, and third-party integrations with strict cascade delete rules and Better-Auth compatibility.**

The Kaneo API, part of the open-source `usekaneo/kaneo` repository, persists all data through a centralized Drizzle ORM schema. This approach uses CUID2 identifiers, comprehensive foreign key constraints, and database-level cascading to maintain referential integrity across workspaces, projects, and tasks.

## Schema Architecture and Conventions

All table definitions reside in **[`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts)**, a monolithic TypeScript file that exports PostgreSQL table schemas using Drizzle's `pgTable` helper. The design follows consistent patterns:

- **Primary keys** use `createId()` (CUID2) via `varchar("id", { length: 128 })` (e.g., `userTable` at line 16).
- **Timestamps** include `createdAt` and `updatedAt` with `timestamp("created_at").defaultNow()` across most tables.
- **Foreign keys** predominantly use `onDelete: "cascade"` to ensure that deleting a parent record (like a workspace) automatically removes child records (projects, tasks, memberships).
- **Indexes** are explicitly defined for foreign key columns and frequently queried fields, such as `session_userId_idx` on `sessionTable.userId` (line 39).

## Core Tables and Relationships

### User Authentication Tables

The authentication layer relies on four interconnected tables starting at line 16:

- **`userTable`** (line 16): Stores core user records with unique `email` fields and standard timestamps.
- **`sessionTable`** (line 39): Manages session tokens, IP addresses, user agents, and active organization context. It references `userTable.id` with a cascade delete and includes `session_userId_idx`.
- **`accountTable`** (line 61): Links OAuth providers to users via `userId` foreign key with cascade delete and `account_userId_idx`.
- **`verificationTable`** (line 91): Holds one-time verification codes (email, OTP) with an index on the `identifier` column.

### Workspace and Team Management

Workspace isolation forms the foundation of Kaneo's multi-tenant architecture:

- **`workspaceTable`** (line 109): Top-level container with a unique `slug` and timestamps.
- **`workspaceUserTable`** (line 121): Junction table linking users to workspaces with a `role` field (member/admin). Foreign keys to `workspace.id` and `user.id` both cascade on delete.
- **`workspaceBillingTable`** (line 146): Stores plan, trial status, and seat counts. The `workspaceId` foreign key is unique and cascades on delete/update.
- **`teamTable`** (line 188): Logical grouping within a workspace, referencing `workspaceId` with cascade delete.
- **`teamMemberTable`** (line 203): Associates users with teams, cascading deletes when either the team or user is removed.
- **`invitationTable`** (line 221): Tracks pending invites with foreign keys to `workspaceId` and `inviterId` (user).
- **`workspaceRoleTable`** (line 247): Defines custom roles and permissions per workspace.

### Project and Task Management (Kanban)

The Kanban functionality centers on hierarchical project structures:

- **`projectTable`** (line 273): Contains collections of columns, linked to `workspaceId` with cascade delete.
- **`columnTable`** (line 304): Represents Kanban columns (e.g., "To Do", "Done") within a project, cascading when the parent project is deleted.
- **`taskTable`** (line 363): Core work items referencing `projectId` (cascade), `userId` (cascade), and `columnId` (set null on delete). Includes a unique constraint combining project and task number.
- **`workflowRuleTable`** (line 331): Automation rules tied to specific columns and projects.
- **`taskRelationTable`** (line 931): Defines task dependencies (blockers, duplicates) with `sourceTaskId` and `targetTaskId` both referencing `task.id` with cascade delete.

### Time Tracking and Activity

Productivity tracking tables include:

- **`timeEntryTable`** (line 334): Records time spent on tasks, linking to `taskId` and `userId` with cascade deletes.
- **`activityTable`** (line 666): Logs state changes, comments, and updates for tasks.
- **`taskReminderSentTable`** (line 406): Tracks which reminders have been dispatched for specific tasks.
- **`commentTable`** (line 904): User comments on tasks, referencing both `taskId` and `userId`.

### Assets and Integrations

External resources and file attachments are managed through:

- **`assetTable`** (line 685): Stores file metadata (images, PDFs) with optional foreign keys to `workspaceId`, `projectId`, `taskId`, and `activityId`.
- **`labelTable`** (line 753): Tags applicable to either tasks or workspaces using nullable `taskId` and `workspaceId` foreign keys.
- **`githubIntegrationTable`** (line 845): Links GitHub repositories to projects with a unique `projectId` constraint.
- **`integrationTable`** (line 866): Generic third-party integrations per project.
- **`externalLinkTable`** (line 886): Connects tasks to external resources (e.g., Jira tickets) via `taskId` and `integrationId`.

### Notifications and API Keys

User communication and programmatic access:

- **`notificationTable`** (line 785): Push notifications with `userId` foreign key cascading on delete.
- **`userNotificationPreferenceTable`** (line 815): Per-user delivery settings with a unique `userId` constraint.
- **`apikeyTable`** (line 959): API keys with rate-limit configuration, referencing a `referenceId` (user).
- **`deviceCodeTable`** (line 971): Supports OAuth device-code flows.
- **`mcpOauthStateTable`** (line 981): Temporary state storage for MCP OAuth exchanges using JSON payloads.

## Referential Integrity and Cascade Behavior

The schema aggressively uses **`onDelete: "cascade"`** to prevent orphaned records. For example, deleting a workspace automatically removes:

1. All `workspaceUserTable` memberships (line 121)
2. All `workspaceBillingTable` records (line 146)
3. All `teamTable` entries (line 188) and their `teamMemberTable` associations (line 203)
4. All `projectTable` records (line 273), which cascade to `columnTable`, `taskTable`, and `assetTable`

The `taskTable` (line 363) uses `onDelete: "set null"` specifically for `columnId`, preserving tasks when their column is deleted while clearing the column reference.

## Better-Auth Compatibility

To integrate with the **Better-Auth** authentication library, the schema file re-exports tables with conventional names at lines 1004–1018:

```typescript
export const user = userTable;
export const session = sessionTable;
export const account = accountTable;
export const verification = verificationTable;

```

This allows Better-Auth to reference `user`, `session`, and other tables while maintaining the explicit `Table` suffix naming convention within the Kaneo codebase.

## Query Examples

### Fetch a Workspace with Its Projects

```typescript
import { db } from "@/db";
import { workspaceTable, projectTable } from "@/database/schema";
import { eq } from "drizzle-orm/expressions";

export async function getWorkspaceWithProjects(workspaceId: string) {
  return db
    .select()
    .from(workspaceTable)
    .where(eq(workspaceTable.id, workspaceId))
    .leftJoin(projectTable, eq(projectTable.workspaceId, workspaceTable.id))
    .all();
}

```

This leverages the `workspaceId` foreign key defined in `projectTable` (line 273) to perform a left join.

### Insert a Task with Auto-Generated ID

```typescript
import { db } from "@/db";
import { taskTable } from "@/database/schema";

export async function createTask(projectId: string, title: string) {
  const [task] = await db
    .insert(taskTable)
    .values({
      projectId,
      title,
      status: "to-do",
    })
    .returning();
  return task;
}

```

The `id` column uses `createId()` automatically (lines 363–368).

### Retrieve Task and Workspace Labels

```typescript
import { db } from "@/db";
import { labelTable, taskTable } from "@/database/schema";
import { eq, or } from "drizzle-orm/expressions";

export async function getLabelsForTask(taskId: string) {
  const task = await db
    .select()
    .from(taskTable)
    .where(eq(taskTable.id, taskId))
    .limit(1);

  return db
    .select()
    .from(labelTable)
    .where(
      or(
        eq(labelTable.taskId, taskId),
        eq(labelTable.workspaceId, task[0].workspaceId)
      )
    )
    .all();
}

```

This demonstrates the dual foreign-key design of `labelTable` (lines 753–771).

### Cascade Delete a Workspace

```typescript
import { db } from "@/db";
import { workspaceTable } from "@/database/schema";
import { eq } from "drizzle-orm/expressions";

export async function deleteWorkspace(workspaceId: string) {
  await db.delete(workspaceTable).where(eq(workspaceTable.id, workspaceId));
  // Dependent projects, tasks, and memberships are removed automatically.
}

```

## Summary

- **The Kaneo API database schema** is defined centrally in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) using Drizzle ORM for PostgreSQL.
- **Thirty tables** cover authentication, workspaces, Kanban projects, tasks, time tracking, assets, and integrations.
- **CUID2** serves as the primary key format across all tables, with `createId()` generating values automatically.
- **Cascade deletes** ensure data consistency: removing a workspace or project automatically cleans up dependent rows.
- **Better-Auth compatibility** is achieved through re-exports at the bottom of the schema file (lines 1004–1018).

## Frequently Asked Questions

### What database does the Kaneo API use?

The Kaneo API uses **PostgreSQL** as its underlying database, accessed through **Drizzle ORM** defined in TypeScript. This combination provides type-safe queries while leveraging PostgreSQL's native support for complex foreign key constraints and cascading deletes.

### Where are the table definitions located in the Kaneo repository?

All table definitions are centralized in **[`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts)**. This monolithic approach keeps the entire data model in one file, making it easier to review relationships and maintain consistency across the API.

### How does the Kaneo schema handle data integrity when deleting workspaces?

The schema uses **`onDelete: "cascade"`** on nearly all foreign keys. When a workspace is deleted from `workspaceTable` (line 109), PostgreSQL automatically removes related records in `workspaceUserTable`, `workspaceBillingTable`, `teamTable`, `projectTable`, and their respective children, preventing orphaned data without application-level logic.

### Is the Kaneo database schema compatible with Better-Auth?

Yes, the schema includes a compatibility layer at lines 1004–1018 of [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts), where tables like `userTable` and `sessionTable` are re-exported as `user` and `session`. This allows the Better-Auth library to consume the same database tables while maintaining Kaneo's explicit naming conventions internally.