# How the Kaneo Database Schema Is Defined: A Complete Guide to the Drizzle ORM Implementation > Discover how the Kaneo database schema is defined using Drizzle ORM. Learn about pgTable declarations, CUID-2 primary keys, and relationship mappings in this complete guide. - Repository: [kaneo.app/kaneo](https://github.com/usekaneo/kaneo) - Tags: internals - Published: 2026-08-30 --- **Kaneo database schema is defined in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) using Drizzle ORM TypeScript definitions with `pgTable` declarations, CUID-2 primary keys generated via `createId()`, and relationship mappings through the `relations()` helper.** Kaneo is an open-source project management platform that persists data to PostgreSQL using a strictly typed ORM layer. The **Kaneo database schema** is centralized in a single TypeScript file where all tables, constraints, indexes, and foreign key behaviors are declared using Drizzle ORM primitives. This architecture provides compile-time type safety while maintaining explicit control over the underlying SQL structure. ## Central Schema Definition All database entities are declared in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) using Drizzle ORM's PostgreSQL dialect. The file exports table definitions created with the `pgTable` function, which accepts table names, column specifications, and configuration objects for indexes and constraints. ### Primary Key Generation Strategy Every table uses `createId()` from `@paralleldrive/cuid2` to generate CUID-2 values as primary keys. This applies to core tables including `userTable`, `workspaceTable`, `projectTable`, and `taskTable`, ensuring globally unique identifiers without sequential predictability. ## Core Table Architecture The schema organizes data into functional domains covering authentication, workspace management, project tracking, and collaboration. ### Authentication and User Management The `userTable` stores core identity data with unique email constraints and verification flags. Related tables handle session persistence and OAuth account linking: - **sessionTable**: Manages browser sessions with `expiresAt` timestamps, unique tokens, and foreign key references to users - **accountTable**: Stores third-party authentication credentials including `accessToken`, `refreshToken`, and `expiresAt` fields Both tables enforce `ON DELETE CASCADE` on their `userId` foreign keys to ensure orphaned records are removed when users are deleted. The `sessionTable` also includes `activeOrganizationId` and `activeTeamId` fields for context-aware authentication. ### Workspace and Team Hierarchy Workspace isolation is enforced through the `workspaceTable` and its junction entities: - **workspaceUserTable** (workspace_member): Maps users to workspaces with role assignments (`role`, `joinedAt`) and foreign keys to both `workspace.id` and `user.id` - **teamTable**: Groups users within workspaces via `workspaceId` foreign keys with `ON DELETE CASCADE` - **teamMemberTable**: Creates many-to-many relationships between teams and users Each junction table maintains composite indexes on both foreign key columns—such as `workspace_member_workspaceId_idx` and `workspace_member_userId_idx`—to optimize join performance. ### Project and Task Management The project management domain centers on four interconnected tables: - **projectTable**: Defines projects with unique slugs per workspace, archived status tracking via `archivedAt`, and `lastTaskNumber` counters for sequential task numbering - **columnTable**: Represents Kanban columns with positional ordering (`position`), color coding, icon support, and final state markers (`isFinal`) - **taskTable**: Stores work items with assignee references (`userId`), status tracking, priority levels, start dates, and due dates - **labelTable**: Categorizes tasks with workspace-scoped or task-specific labels, enforcing unique constraints on `(taskId, name)` and `(workspaceId, name)` combinations The `taskTable` implements `ON DELETE SET NULL` for assignee relationships (`userId`), preserving task history when users leave the platform, while maintaining `ON DELETE CASCADE` for project references. It also enforces a unique constraint on `(projectId, number)` to ensure sequential task numbers are unique within each project. ### Activity, Comments, and Assets Supporting tables track collaboration metadata: - **activityTable**: Logs task events with `type`, `content`, and `eventData` JSON fields, linking to `taskId` and `userId` with appropriate cascade behaviors - **commentTable**: Stores user comments on tasks with content fields and foreign keys to both tasks and users - **assetTable**: Manages file attachments with `objectKey` (unique), `filename`, `mimeType`, and size fields, linking to workspaces, projects, tasks, or activities via polymorphic foreign key patterns - **notificationTable**: Handles user alerts with `isRead` flags, `resourceId` references, and `eventData` payloads ## Relationships and ORM Mappings At the bottom of [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts), the schema exports `relations()` declarations that enable Drizzle ORM's relational query builder. These mappings are defined for entities including `user`, `session`, `account`, `workspace`, and `team`: ```typescript export const userRelations = relations(user, ({ many }) => ({ sessions: many(session), accounts: many(account), teamMembers: many(teamMember), workspace_members: many(workspace_member), invitations: many(invitation), })); ``` These declarations allow type-safe traversal of one-to-many and many-to-one relationships throughout the API layer, enabling queries like `user.sessions` or `workspace.teams` with full TypeScript inference. ## Performance Optimization and Constraints The schema includes explicit index definitions to support query patterns. Unique constraints enforce business rules such as unique project slugs within workspaces (`slug` unique on `workspaceTable`) and unique email addresses globally on `userTable`. Foreign key indexes exist on all relation columns—including `projectId`, `userId`, and `columnId` on the task table—to prevent sequential scans during join operations. ## Working with the Schema Querying the schema uses Drizzle ORM's query builder with strict TypeScript types derived from the table definitions. Fetching a workspace with its projects and members: ```typescript import { db } from "@/db"; import { workspaceTable, projectTable, workspaceUserTable } from "@/schema"; import { eq } from "drizzle-orm"; const ws = await db .select() .from(workspaceTable) .where(eq(workspaceTable.id, "workspace_123")) .leftJoin(projectTable, eq(projectTable.workspaceId, workspaceTable.id)) .leftJoin(workspaceUserTable, eq(workspaceUserTable.workspaceId, workspaceTable.id)); ``` Creating a new task within a project: ```typescript import { db } from "@/db"; import { taskTable } from "@/schema"; await db.insert(taskTable).values({ projectId: "proj_abc", title: "Implement API endpoint", description: "Add endpoint for creating tasks", status: "todo", priority: "high", }); ``` Retrieving activity logs with user information: ```typescript import { db } from "@/db"; import { activityTable, userTable } from "@/schema"; import { eq } from "drizzle-orm"; const logs = await db .select() .from(activityTable) .where(eq(activityTable.taskId, "task_456")) .leftJoin(userTable, eq(activityTable.userId, userTable.id)); ``` ## Summary Key takeaways about the Kaneo database schema: - **Single source of truth**: All table definitions reside in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) using Drizzle ORM's `pgTable` API - **CUID-2 identifiers**: Primary keys use `createId()` for collision-resistant, non-sequential IDs across all entities - **Declarative relations**: The `relations()` function enables type-safe navigation between users, workspaces, projects, and tasks - **Referential integrity**: Foreign keys specify `ON DELETE CASCADE` or `SET NULL` behaviors appropriate to each relationship type, with CASCADE used for membership tables and SET NULL for task assignees - **Performance optimized**: Strategic indexes on foreign keys, unique constraints on business identifiers, and composite indexes for common query patterns ## Frequently Asked Questions ### Where is the Kaneo database schema defined? The schema is defined in [`apps/api/src/database/schema.ts`](https://github.com/usekaneo/kaneo/blob/main/apps/api/src/database/schema.ts) as TypeScript definitions using Drizzle ORM. This file contains all `pgTable` declarations, column types, constraints, and the `relations()` mappings that power the ORM's relational queries. Additional per-domain schema files exist for specific features but are re-exported through this central location. ### What ORM does Kaneo use for database operations? Kaneo uses **Drizzle ORM** with the PostgreSQL dialect. The schema leverages `pgTable` for table definitions, `createId()` from `@paralleldrive/cuid2` for primary keys, and the `relations()` helper to define one-to-many and many-to-one associations between entities such as users, workspaces, and tasks. ### How are relationships between tables defined in Kaneo? Relationships are defined in two ways: foreign key constraints in the table definitions enforce database-level referential integrity, while the `relations()` declarations at the bottom of [`schema.ts`](https://github.com/usekaneo/kaneo/blob/main/schema.ts) enable Drizzle ORM's relational query builder. For example, the `userRelations` object maps users to sessions, accounts, and workspace memberships using the `many()` helper, allowing type-safe traversal of associated data. ### What is the primary key strategy used in Kaneo's tables? All tables use CUID-2 (Collision-resistant Unique Identifier) generated by the `createId()` utility. These identifiers are URL-safe, non-sequential strings that provide better distribution and security than auto-incrementing integers, and are applied consistently across `userTable`, `workspaceTable`, `projectTable`, and all other entities in the Kaneo database schema.