# Database Schema for the Kaneo Application: Complete Drizzle ORM Reference

> Explore the complete database schema for the Kaneo application, featuring over 30 tables managed by Drizzle ORM and PostgreSQL. Discover CUID2 keys, cascading constraints, and timestamps.

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

---

**TLDR:** The Kaneo application uses a PostgreSQL database managed by Drizzle ORM, with a single comprehensive schema file at [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) defining over 30 tables using CUID2 primary keys, cascading foreign key constraints, and automatic timestamps.

Kaneo is an open-source project management platform that persists all data in PostgreSQL using type-safe Drizzle ORM definitions. The entire database schema lives in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts), which exports table definitions, indexes, and relation helpers used by the Better Auth library and API routes throughout the codebase.

## Schema Design Patterns

Every table in the Kaneo database follows consistent conventions defined in the central schema file.

**Primary keys** use `text` columns populated by `createId()` from **CUID2** (`@paralleldrive/cuid2`), ensuring globally unique identifiers without database sequences.

**Timestamps** are standardized with `createdAt` and `updatedAt` columns using `timestamp(...).defaultNow()` and an `$onUpdate` hook for automatic updates.

**Foreign key constraints** consistently implement `onDelete: "cascade"` and `onUpdate: "cascade"` to maintain referential integrity when parent records are modified or removed.

## Authentication Layer

The authentication system defined in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) includes tables for user management, sessions, OAuth accounts, and verification tokens.

### User Management

The `userTable` (lines 16-36) represents core user entities with columns for `id`, `name`, `email`, `image`, and `locale`. It includes unique constraints on `email` and soft-delete fields such as `banned` and `banReason`.

### Session and OAuth Handling

The `sessionTable` (lines 39-58) stores JWT and refresh-token sessions with `expiresAt`, `token`, and `userId` fields, cascading deletes when users are removed. The `accountTable` (lines 61-89) manages OAuth connections with `accountId`, `providerId`, and `userId` columns.

Additional authentication tables include `verificationTable` (lines 91-106) for email verification tokens, `deviceCodeTable` (lines 846-875) for OAuth Device Flow temporary codes, and `mcpOauthStateTable` (lines 877-904) for transient OAuth state payloads with unique composite indexes on `kind` and `key`.

## Workspace and Access Control

Workspaces represent the top-level organizational unit in Kaneo.

The `workspaceTable` (lines 109-120) defines containers with `name`, `slug`, `logo`, and `description` fields, enforcing unique slugs via constraints.

Membership is controlled through `workspaceUserTable` (lines 121-144), which links users to workspaces with role assignments (`owner`, `member`, etc.) and tracks `joinedAt` timestamps. Both foreign keys cascade on deletion.

Billing information resides in `workspaceBillingTable` (lines 146-176), storing plan details and seat counts with a unique constraint on `workspaceId`.

Team functionality uses `teamTable` (lines 187-200) and `teamMemberTable` (lines 202-218) to create groups within workspaces, both cascading on parent deletions.

The `invitationTable` (lines 220-245) handles email invitations with expiration dates, status tracking, and optional team assignments.

## Project Management Core

Projects contain tasks organized in Kanban columns.

The `projectTable` (lines 273-298) defines projects with `slug`, `name`, `icon`, and `description`, cascading on workspace deletion. A unique constraint ensures `(workspaceId, id)` combinations are distinct.

Columns use `columnTable` (lines 300-324) with `position` ordering and `slug` fields, cascading when projects are deleted.

Tasks are stored in `taskTable` (lines 358-399) with `number`, `title`, `status`, `assignee_id`, and `columnId`. The table enforces unique `(projectId, number)` pairs and foreign keys to both `userTable` (assignee) and `columnTable` (set-null on delete).

Task relationships use `taskRelationTable` (lines 974-1002) to create directed edges (e.g., "blocks", "duplicates") between tasks, cascading on both source and target task deletions.

Comments are stored in `commentTable` (lines 948-972), linking to both `taskTable` and `userTable` with cascading deletes.

## Workflow Automation

Automation rules connect projects to external events.

The `workflowRuleTable` (lines 326-357) stores `integrationType`, `eventType`, and `columnId` references, cascading on both project and column deletions.

External integrations use `integrationTable` (lines 906-923) to store service connections (GitHub, Slack) with `config` JSON and `isActive` flags, enforcing uniqueness per `(projectId, type)`.

Linked external resources are tracked in `externalLinkTable` (lines 925-946), connecting tasks to integration-generated URLs.

## Time Tracking and Activity

Time tracking uses `timeEntryTable` (lines 428-555) with `startTime`, `endTime`, and calculated `duration` fields, cascading on both task and user deletions.

Activity logging uses `activityTable` (lines 560-599) to record events, comments, and state changes with `eventData` JSON fields, optionally linking to users.

The `taskReminderSentTable` (lines 401-426) prevents duplicate reminders with a unique `(taskId, reminderType)` constraint.

## Supporting Entities

File attachments use `assetTable` (lines 601-645) with `objectKey`, `filename`, `mimeType`, and `size`, cascading on workspace, project, task, or activity deletions. A unique constraint ensures `objectKey` uniqueness.

Labels use `labelTable` (lines 647-682) with unique constraints per `(taskId, name)` and per `(workspaceId, name)` for global workspace labels.

Notifications use `notificationTable` (lines 684-708) for user alerts, while `userNotificationPreferenceTable` (lines 710-756) stores per-user toggle settings for email and push channels.

API access is controlled via `apikeyTable` (lines 758-844), supporting rate limiting with `rateLimitEnabled`, `rateLimitMax`, and `rateLimitTimeWindow` columns.

## Practical Query Examples

### Selecting Tasks by Project

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

const tasks = await db
  .select()
  .from(taskTable)
  .where(eq(taskTable.projectId, projectId))
  .orderBy(taskTable.position);

```

This query targets the `taskTable` definition at lines 358-399, utilizing the index on `projectId` for efficient filtering.

### Creating Workspaces with Members

```typescript
import { db } from "@/db";
import { workspaceTable, workspaceUserTable } from "@/database/schema";

await db.transaction(async (tx) => {
  const ws = await tx
    .insert(workspaceTable)
    .values({ name: "Acme Corp", slug: "acme-corp" })
    .returning();

  await tx
    .insert(workspaceUserTable)
    .values({
      workspaceId: ws.id,
      userId: adminUserId,
      role: "owner",
      joinedAt: new Date(),
    });
});

```

This transaction uses `workspaceTable` (lines 109-120) and `workspaceUserTable` (lines 121-144) with proper foreign key relationships.

### Inserting Rate-Limited API Keys

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

await db
  .insert(apikeyTable)
  .values({
    referenceId: userId,
    key: generateRandomKey(),
    enabled: true,
    rateLimitEnabled: true,
    rateLimitMax: 100,
    rateLimitTimeWindow: 86_400_000, // 24 hours
  });

```

The `apikeyTable` at lines 758-844 supports granular rate limiting configuration.

## Summary

- The **Kaneo database schema** is defined entirely in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) using Drizzle ORM.
- All tables use **CUID2** (`createId()`) for primary keys and automatic **timestamp** management.
- **Cascading foreign keys** maintain referential integrity across workspaces, projects, and tasks.
- The schema includes **30+ tables** covering authentication, workspace management, Kanban boards, time tracking, and API access.
- **Indexes** are strategically placed on `userId`, `workspaceId`, `projectId`, and `taskId` columns for query performance.

## Frequently Asked Questions

### What database does Kaneo use?

Kaneo uses **PostgreSQL** as its primary data store, accessed through Drizzle ORM type-safe definitions in the [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) file.

### How are primary keys generated in Kaneo's schema?

Primary keys are generated using **CUID2** via the `createId()` function from `@paralleldrive/cuid2`, stored as `text` columns rather than auto-incrementing integers.

### Where is the database schema defined in the Kaneo codebase?

The complete schema is defined in **[`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts)**, which exports table definitions, indexes, and relation helpers used by the Better Auth library and API routes.

### What ORM does Kaneo use for database operations?

Kaneo uses **Drizzle ORM**, configured in [`apps/api/drizzle.config.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/drizzle.config.ts), providing type-safe SQL-like query building and automatic migration generation for the PostgreSQL database.