[bug]: Remote project path uniqueness is global, not per-SSH-connection

[joshua-medra]

Bug Description

projects.path has a global UNIQUE index, not scoped per SSH connection. Since different remote hosts commonly mount projects at the same conventional path, registering a project on a second host fails if any other host already has a project registered at that same path.

Steps to Reproduce

1. Add SSH connection host-a, register a project at path /home/user/project.
2. Add SSH connection host-b.
3. Register a project at the same path /home/user/project on host-b.
4. Observe the error.

Actual vs Expected Behavior

• Actual: Error invoking remote method 'projects.createProject': SqliteError: UNIQUE constraint failed: projects.path
• Expected: Paths should only need to be unique per connection ((ssh_connection_id, path)), since paths are independent across separate remote hosts.

emdash Version

1.1.35

Operating System

macOS (Darwin 24.3.0)

Additional Context

CREATE UNIQUE INDEX idx_projects_path ON projects (path);

Suggested fix: composite unique index on (ssh_connection_id, path) instead of path alone, with a migration.

[arnestrickmann]

Thanks for the detailed report and suggested fix. This makes sense, and we’ll look into scoping project path uniqueness per SSH connection instead of globally. We’ll update you here once we’ve had the chance to dig into it.

[fiorelorenzo]

Put up #2747 for this. It swaps the global unique on projects.path for two partial unique indexes: local projects stay unique by path, SSH projects unique by (ssh_connection_id, path). Two indexes rather than one composite because SQLite treats NULLs as distinct, which would otherwise drop path uniqueness for local projects. Migration + tests included.