perf: add composite index on user_workspace(workspace_id, role) #11

New issue

Open

opened 2026-02-20 16:36:09 +00:00 by Clotho · 0 comments

Clotho commented

2026-02-20 16:36:09 +00:00

Member

Problem

The user_workspace pivot table (created in Migrations/0001_01_01_000000_create_tenant_tables.php, lines 90-99) lacks a composite index on (workspace_id, role).

Impact

Common queries like "get all owners of workspace X" or "get all admins" require a full table scan over the pivot:

// These queries lack an efficient index:
$workspace->users()->wherePivot('role', 'owner')-get();
$workspace->users()->wherePivot('role', 'admin')-get();

On large deployments with thousands of members across workspaces, this will degrade noticeably.

Acceptance Criteria

Add composite index (workspace_id, role) to user_workspace pivot table
Add to the existing performance indexes migration or create a new one

$table->index(['workspace_id', 'role'], 'user_workspace_workspace_role_index');

Discovered during automated scan (issue #3)

## Problem The `user_workspace` pivot table (created in `Migrations/0001_01_01_000000_create_tenant_tables.php`, lines 90-99) lacks a composite index on `(workspace_id, role)`. ## Impact Common queries like "get all owners of workspace X" or "get all admins" require a full table scan over the pivot: ```php // These queries lack an efficient index: $workspace->users()->wherePivot('role', 'owner')-get(); $workspace->users()->wherePivot('role', 'admin')-get(); ``` On large deployments with thousands of members across workspaces, this will degrade noticeably. ## Acceptance Criteria - Add composite index `(workspace_id, role)` to `user_workspace` pivot table - Add to the existing performance indexes migration or create a new one ```php $table->index(['workspace_id', 'role'], 'user_workspace_workspace_role_index'); ``` _Discovered during automated scan (issue #3)_