How the Kaneo Database Schema Is Defined: A Complete Guide to the Drizzle ORM Implementation

Kaneo database schema is defined in 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 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, 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:

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:

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:

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:

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

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