Kaneo API Database Schema: Complete Drizzle ORM Reference for PostgreSQL

The Kaneo API database schema uses Drizzle ORM with PostgreSQL, defining over 30 tables in 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, 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:

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

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

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

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

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

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