# How Kaneo Uses Database Indexes for High-Performance PostgreSQL Operations

> Discover how Kaneo employs B-tree indexes on foreign keys and composite columns for sub-millisecond PostgreSQL query performance, even with thousands of records.

- Repository: [kaneo.app/kaneo](https://github.com/usekaneo/kaneo)
- Tags: performance
- Published: 2026-08-29

---

**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`](https://github.com/usekaneo/kaneo/blob/main/apps/api/drizzle/0029_fk_supporting_indexes.sql) and [`apps/api/drizzle/0007_careful_moira_mactaggert.sql`](https://github.com/usekaneo/kaneo/blob/main/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:

```typescript
// 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`](https://github.com/usekaneo/kaneo/blob/main/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:

```typescript
// 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:

```typescript
// 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`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/apps/api/drizzle/0008_square_silvermane.sql)** — Unique and foreign key indexes for API key management.
- **[`apps/api/drizzle/0012_mixed_thor_girl.sql`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/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`](https://github.com/usekaneo/kaneo/blob/main/0029_fk_supporting_indexes.sql) for foreign key indexes, [`0014_private_assets.sql`](https://github.com/usekaneo/kaneo/blob/main/0014_private_assets.sql) for asset relationships, and [`0008_square_silvermane.sql`](https://github.com/usekaneo/kaneo/blob/main/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.