Improve owner dropdown sorting in forms

[timothyfroehlich]

Feature Request

Currently, the owner dropdown in forms (e.g., when adding/editing machines) doesn't have an optimized sort order. We should implement a smarter sorting algorithm to make it easier for users to find owners.

Proposed Sort Order

Sort owners in the dropdown using the following priority:

1. Confirmed vs Unconfirmed: Confirmed users (from user_profiles) should appear before unconfirmed users (from unconfirmed_users)
2. Number of Owned Machines: Within each group, sort by number of owned machines (descending)
3. Alphabetically by Last Name: Finally, sort alphabetically by last name

Example Output

Confirmed Users:
  - Chris Engberg (8 machines)
  - Summer Ellington (4 machines)
  - Jordan Burgar (2 machines)
  - Tim Froehlich (1 machine)
  
Unconfirmed Users:
  - Chris Welch (3 machines)
  - Ted Gaunt (4 machines)
  - Neil Wilson (3 machines)

Implementation Notes

• This affects owner selection dropdowns across the application
• May want to add visual separators between confirmed/unconfirmed groups
• Consider showing machine count in parentheses next to owner name in dropdown

Related Files

• Owner selection components/forms
• Owner query/sorting logic in services or server actions

---

Labels: enhancement, ux
Priority: Low (nice-to-have improvement)

[traycerai]

Plan

Observations

The codebase uses a unified user system combining confirmed users (user_profiles) and unconfirmed users (unconfirmed_users) into a UnifiedUser type. The owner dropdown in OwnerSelect.tsx currently sorts users alphabetically by name only. Users are fetched via getUnifiedUsers() in file:src/lib/users/queries.ts, which merges both user types. Machine ownership is tracked through ownerId and unconfirmedOwnerId fields in the machines table.

Approach

The implementation will enhance the sorting algorithm in three layers: extend the data model to include machine counts, update the query to fetch these counts efficiently using SQL aggregation, and modify the UI component to apply the multi-criteria sorting (status → machine count → last name). This approach minimizes changes while maintaining the existing architecture and ensures optimal database performance through a single query with joins.

Implementation Steps

1. Extend Type Definitions

File: file:src/lib/types/user.ts

Add machineCount field to the UnifiedUser interface:

export interface UnifiedUser {
  id: string;
  name: string;
  email: string;
  role: UserRole;
  status: UserStatus;
  avatarUrl: string | null;
  inviteSentAt?: Date | null;
  machineCount: number; // NEW: Number of machines owned by this user
}

2. Update User Query to Include Machine Counts

File: file:src/lib/users/queries.ts

Modify getUnifiedUsers() to fetch machine counts for each user using SQL aggregation:

• Import machines table from schema
• Add a subquery or join to count machines for confirmed users (where machines.ownerId = userProfiles.id)
• Add a subquery or join to count machines for unconfirmed users (where machines.unconfirmedOwnerId = unconfirmedUsers.id)
• Use Drizzle's sql template or count() aggregation function
• Include the machine count in the select statement for both user types
• Remove the existing alphabetical sort at line 34-38 (sorting will be handled in the component)

Implementation approach:

• Use LEFT JOIN with machines table and count() aggregation
• Group by user fields to get counts per user
• Return machineCount as part of the result set

3. Update Owner Selection Component Sorting

File: file:src/components/machines/OwnerSelect.tsx

Replace the current sorting logic at line 49 with the new multi-criteria sort:

Current code (line 49):

const sortedUsers = [...users].sort((a, b) => a.name.localeCompare(b.name));

New sorting logic:

• Primary sort: Confirmed users (status === "active") before unconfirmed users (status === "unconfirmed")
• Secondary sort: Within each group, sort by machineCount in descending order (users with more machines first)
• Tertiary sort: For users with the same machine count, sort alphabetically by last name

Implementation details:

• Extract last name from the name field (split by space, take last element)
• Use a multi-level comparison function
• Handle edge cases (missing names, equal counts)

4. Enhance UI Display (Optional)

File: file:src/components/machines/OwnerSelect.tsx

Enhance the dropdown display to show machine counts and visual separators:

At line 84-95 (SelectItem rendering):

• Display machine count in parentheses next to the user name
• Format: "Chris Engberg (8 machines)" or "Chris Engberg (1 machine)" for singular
• Keep the existing "(Unconfirmed)" badge for unconfirmed users

Add visual separator:

• Insert a separator element between confirmed and unconfirmed user groups
• Use a <SelectSeparator /> component or a custom divider
• Add a label like "Unconfirmed Users" before the unconfirmed group
• Check if shadcn/ui Select component supports separators or use a disabled SelectItem as divider

5. Verification Points

After implementation, verify:

• Machine counts are accurate for both confirmed and unconfirmed users
• Sorting order matches the specification (status → count → last name)
• UI displays machine counts correctly with proper singular/plural formatting
• Visual separator appears between user groups
• Performance is acceptable (single query with joins should be efficient)
• Existing functionality (owner selection, form submission) remains intact

Data Flow Diagram

sequenceDiagram
    participant Page as Machine Form Page
    participant Query as getUnifiedUsers()
    participant DB as Database
    participant Component as OwnerSelect
    participant UI as Dropdown UI

    Page->>Query: Fetch all users
    Query->>DB: SELECT users + COUNT(machines)
    DB-->>Query: Users with machine counts
    Query-->>Page: UnifiedUser[] with machineCount
    Page->>Component: Pass users array
    Component->>Component: Sort by status, count, lastName
    Component->>UI: Render sorted dropdown
    UI-->>Component: User selects owner
    Component-->>Page: Return selected ownerId

Loading

Sorting Logic Visualization

flowchart TD
    A[Start: Unsorted Users] --> B{Compare Status}
    B -->|User A is confirmed| C[A comes first]
    B -->|User B is confirmed| D[B comes first]
    B -->|Both same status| E{Compare Machine Count}
    E -->|A has more machines| F[A comes first]
    E -->|B has more machines| G[B comes first]
    E -->|Same machine count| H{Compare Last Name}
    H -->|A < B alphabetically| I[A comes first]
    H -->|B < A alphabetically| J[B comes first]
    H -->|Same last name| K[Maintain order]
    
    C --> L[Sorted Users]
    D --> L
    F --> L
    G --> L
    I --> L
    J --> L
    K --> L

Loading

Import In IDE

🤖 Prompt for AI Agents

## Observations

The codebase uses a unified user system combining confirmed users (`user_profiles`) and unconfirmed users (`unconfirmed_users`) into a `UnifiedUser` type. The owner dropdown in `OwnerSelect.tsx` currently sorts users alphabetically by name only. Users are fetched via `getUnifiedUsers()` in `file:src/lib/users/queries.ts`, which merges both user types. Machine ownership is tracked through `ownerId` and `unconfirmedOwnerId` fields in the `machines` table.

## Approach

The implementation will enhance the sorting algorithm in three layers: extend the data model to include machine counts, update the query to fetch these counts efficiently using SQL aggregation, and modify the UI component to apply the multi-criteria sorting (status → machine count → last name). This approach minimizes changes while maintaining the existing architecture and ensures optimal database performance through a single query with joins.

## Implementation Steps

### 1. Extend Type Definitions

**File:** `file:src/lib/types/user.ts`

Add `machineCount` field to the `UnifiedUser` interface:

```typescript
export interface UnifiedUser {
  id: string;
  name: string;
  email: string;
  role: UserRole;
  status: UserStatus;
  avatarUrl: string | null;
  inviteSentAt?: Date | null;
  machineCount: number; // NEW: Number of machines owned by this user
}
```

### 2. Update User Query to Include Machine Counts

**File:** `file:src/lib/users/queries.ts`

Modify `getUnifiedUsers()` to fetch machine counts for each user using SQL aggregation:

- Import `machines` table from schema
- Add a subquery or join to count machines for confirmed users (where `machines.ownerId = userProfiles.id`)
- Add a subquery or join to count machines for unconfirmed users (where `machines.unconfirmedOwnerId = unconfirmedUsers.id`)
- Use Drizzle's `sql` template or `count()` aggregation function
- Include the machine count in the select statement for both user types
- Remove the existing alphabetical sort at line 34-38 (sorting will be handled in the component)

**Implementation approach:**
- Use LEFT JOIN with `machines` table and `count()` aggregation
- Group by user fields to get counts per user
- Return `machineCount` as part of the result set

### 3. Update Owner Selection Component Sorting

**File:** `file:src/components/machines/OwnerSelect.tsx`

Replace the current sorting logic at line 49 with the new multi-criteria sort:

**Current code (line 49):**
```typescript
const sortedUsers = [...users].sort((a, b) => a.name.localeCompare(b.name));
```

**New sorting logic:**
- Primary sort: Confirmed users (`status === "active"`) before unconfirmed users (`status === "unconfirmed"`)
- Secondary sort: Within each group, sort by `machineCount` in descending order (users with more machines first)
- Tertiary sort: For users with the same machine count, sort alphabetically by last name

**Implementation details:**
- Extract last name from the `name` field (split by space, take last element)
- Use a multi-level comparison function
- Handle edge cases (missing names, equal counts)

### 4. Enhance UI Display (Optional)

**File:** `file:src/components/machines/OwnerSelect.tsx`

Enhance the dropdown display to show machine counts and visual separators:

**At line 84-95 (SelectItem rendering):**
- Display machine count in parentheses next to the user name
- Format: `"Chris Engberg (8 machines)"` or `"Chris Engberg (1 machine)"` for singular
- Keep the existing "(Unconfirmed)" badge for unconfirmed users

**Add visual separator:**
- Insert a separator element between confirmed and unconfirmed user groups
- Use a `<SelectSeparator />` component or a custom divider
- Add a label like "Unconfirmed Users" before the unconfirmed group
- Check if shadcn/ui Select component supports separators or use a disabled SelectItem as divider

### 5. Verification Points

After implementation, verify:
- Machine counts are accurate for both confirmed and unconfirmed users
- Sorting order matches the specification (status → count → last name)
- UI displays machine counts correctly with proper singular/plural formatting
- Visual separator appears between user groups
- Performance is acceptable (single query with joins should be efficient)
- Existing functionality (owner selection, form submission) remains intact

## Data Flow Diagram

```mermaid
sequenceDiagram
    participant Page as Machine Form Page
    participant Query as getUnifiedUsers()
    participant DB as Database
    participant Component as OwnerSelect
    participant UI as Dropdown UI

    Page->>Query: Fetch all users
    Query->>DB: SELECT users + COUNT(machines)
    DB-->>Query: Users with machine counts
    Query-->>Page: UnifiedUser[] with machineCount
    Page->>Component: Pass users array
    Component->>Component: Sort by status, count, lastName
    Component->>UI: Render sorted dropdown
    UI-->>Component: User selects owner
    Component-->>Page: Return selected ownerId
```

## Sorting Logic Visualization

```mermaid
flowchart TD
    A[Start: Unsorted Users] --> B{Compare Status}
    B -->|User A is confirmed| C[A comes first]
    B -->|User B is confirmed| D[B comes first]
    B -->|Both same status| E{Compare Machine Count}
    E -->|A has more machines| F[A comes first]
    E -->|B has more machines| G[B comes first]
    E -->|Same machine count| H{Compare Last Name}
    H -->|A < B alphabetically| I[A comes first]
    H -->|B < A alphabetically| J[B comes first]
    H -->|Same last name| K[Maintain order]
    
    C --> L[Sorted Users]
    D --> L
    F --> L
    G --> L
    I --> L
    J --> L
    K --> L
```

---

Execution Information

Branch: main
Commit: cddbdaa

---

💡 Tips

Supported Commands (Inside Comments)

• Use @traycerai generate to iterate on the previous version of the implementation plan.

Supported Commands (Inside Description)

• Add @traycerai ignore anywhere in the ticket description to prevent this ticket from being processed.
• Add @traycerai branch:<branch-name> anywhere in the ticket description to specify the target branch for the implementation plan.

Community

• Join our Discord Community to get help, request features, and share feedback.
• Follow us on X/Twitter for updates and announcements.

[timothyfroehlich]

Implemented in PR #924: owner dropdown now sorts by confirmed status → machine count → alphabetical name. Machine counts displayed inline. Only the optional visual separator (nice-to-have) was not implemented. —Claude