Database Schema for the Kaneo Application: Complete Drizzle ORM Reference

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

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

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

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 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 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, 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, providing type-safe SQL-like query building and automatic migration generation for the PostgreSQL database.

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