How Kaneo Uses Database Indexes for High-Performance PostgreSQL Operations

Kaneo leverages strategic B-tree indexes on foreign keys, composite workspace columns, and frequently filtered fields to ensure sub-millisecond query performance across tasks, projects, and time-entries, even in workspaces containing thousands of records.

Kaneo is an open-source project management platform that stores core entities—tasks, projects, time-entries, and notifications—in a PostgreSQL database accessed through the Drizzle ORM. To maintain fast API response times under heavy read loads, the schema implements targeted B-tree indexes that minimize disk I/O, accelerate join operations, and enable efficient range scans.

Foreign Key Indexes for Rapid Relationship Lookups

The majority of Kaneo’s performance-critical indexes support foreign key relationships, allowing the database to rapidly locate related records without full table scans.

• activity_userId_idx on the activity table speeds up user activity stream queries.
• task_assigneeId_idx on the task table optimizes fetching all tasks assigned to a specific user.
• time_entry_taskId_idx and similar indexes on timeEntry enable fast retrieval of logged hours per task or user.

These indexes are defined in migration files such as apps/api/drizzle/0029_fk_supporting_indexes.sql and apps/api/drizzle/0007_careful_moira_mactaggert.sql, which cover core entities and authentication tables.

When the API retrieves a user’s assigned tasks, the query planner utilizes task_assigneeId_idx to seek directly to relevant rows:

// apps/api/src/task/controller.ts
export const getUserTasks = async (userId: string) => {
  return db
    .select()
    .from(task)
    .where(eq(task.assigneeId, userId));
};

Composite Indexes for Workspace-Scoped Operations

To support multi-tenant workspaces without performance degradation, Kaneo implements composite indexes that combine workspace identifiers with ordering or filtering columns.

The index project_workspaceId_position_idx (defined in apps/api/drizzle/0029_fk_supporting_indexes.sql) covers both workspace_id and position columns. This allows PostgreSQL to satisfy queries that filter by workspace and sort by position using only the index, eliminating the need to access heap data for row reordering operations.

Similarly, user_notification_workspace_project_workspaceId_projectId_idx accelerates notification queries scoped to specific workspace and project combinations.

The project reordering controller leverages this composite index during updates:

// apps/api/src/project/controller.ts
export const reorderProjects = async (workspaceId: string, orderedIds: string[]) => {
  await Promise.all(
    orderedIds.map((id, idx) =>
      db.update(project)
        .set({ position: idx })
        .where(and(eq(project.id, id), eq(project.workspaceId, workspaceId)))
    )
  );
};

Optimizing Time-Series and Range Queries

Kaneo indexes temporal and expiration fields to support dashboard filters and cleanup jobs that rely on range predicates.

• task_dueDate_idx accelerates due-date range queries for calendar views and deadline dashboards.
• mcp_oauth_state_expiresAt_idx on the mcp_oauth_state table ensures rapid identification of expired OAuth tokens during security sweeps.
• time_entry_taskId_idx (combined with user_id indexes) makes "show all time entries for a task" operations performant.

When loading time entries for a specific task, the database uses the dedicated index to avoid scanning unrelated records:

// apps/api/src/time-entry/controller.ts
export const listTimeEntries = async (taskId: string) => {
  return db
    .select()
    .from(timeEntry)
    .where(eq(timeEntry.taskId, taskId));
};

Unique Constraints and API Key Validation

For data integrity and fast lookups, Kaneo enforces unique constraints via indexes. The apikey_key_idx on the apikey table (created in apps/api/drizzle/0008_square_silvermane.sql) ensures that API key validation—performed on every authenticated request—executes in constant time via a B-tree equality search.

Migration Files and Schema Implementation

All index definitions reside in versioned SQL migration files under apps/api/drizzle/. Key files include:

• apps/api/drizzle/0029_fk_supporting_indexes.sql — Bulk foreign key indexes for activity, task, time-entry, and notification tables.
• apps/api/drizzle/0014_private_assets.sql — Indexes on the asset table for workspace, project, task, and activity relationships.
• apps/api/drizzle/0016_add_task_relation.sql — Indexes on task_relation covering source_task_id and target_task_id.
• apps/api/drizzle/0008_square_silvermane.sql — Unique and foreign key indexes for API key management.
• apps/api/drizzle/0012_mixed_thor_girl.sql — Composite indexes for column and workflow_rule project associations.
• apps/api/drizzle/0007_careful_moira_mactaggert.sql — Indexes on authentication tables including account_userId_idx and session_userId_idx.

Summary

• B-tree indexes on foreign key columns (user_id, workspace_id, project_id, task_id) eliminate full table scans during join operations.
• Composite indexes combining workspace_id with position or project_id optimize multi-tenant queries and reordering operations.
• Temporal indexes on due_date and expires_at fields accelerate range-based filtering for dashboards and cleanup tasks.
• Unique indexes on critical fields like apikey.key ensure constant-time validation lookups.
• All schema definitions are stored in apps/api/drizzle/ migration files, maintaining version-controlled, reproducible database performance tuning.

Frequently Asked Questions

What type of database indexes does Kaneo use?

Kaneo exclusively uses B-tree indexes (the default for PostgreSQL) because they efficiently handle equality and range predicates. This structure supports the application’s need for fast lookups on foreign keys, composite workspace queries, and time-based range scans across tasks and time-entries.

How does Kaneo maintain performance in large workspaces with thousands of tasks?

The schema utilizes composite indexes such as project_workspaceId_position_idx and user_notification_workspace_project_workspaceId_projectId_idx to ensure queries filter by workspace_id first, then secondary columns. This approach narrows the search space immediately, preventing sequential scans even as dataset size grows.

Where are the index definitions located in the Kaneo source code?

Index definitions are stored in SQL migration files under apps/api/drizzle/. Key files include 0029_fk_supporting_indexes.sql for foreign key indexes, 0014_private_assets.sql for asset relationships, and 0008_square_silvermane.sql for API key unique constraints.

Does Kaneo rely on PostgreSQL to auto-index foreign keys?

No. While PostgreSQL requires indexes for foreign key constraints to maintain referential integrity efficiently, Kaneo explicitly defines these indexes in migration files (such as task_assigneeId_idx and activity_userId_idx) to ensure optimal query plans for application-specific access patterns rather than relying solely on automatic or default indexing behavior.

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