chore(coderd/database): create db trigger to enforce agent name uniqueness

DanielleMaywood · DanielleMaywood · commit 97e0d4473aee · 2025-05-27T14:19:14.000Z
This PR creates a new database trigger to ensure an inserted workspace
agent has a unique name within the scope of its workspace.
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000330_workspace_agent_name_unique_trigger.down.sql b/coderd/database/migrations/000330_workspace_agent_name_unique_trigger.down.sql
@@ -0,0 +1,2 @@
+DROP TRIGGER IF EXISTS workspace_agent_name_unique_trigger ON workspace_agents;
+DROP FUNCTION IF EXISTS check_workspace_agent_name_unique();
diff --git a/coderd/database/migrations/000330_workspace_agent_name_unique_trigger.up.sql b/coderd/database/migrations/000330_workspace_agent_name_unique_trigger.up.sql
@@ -0,0 +1,43 @@
+CREATE OR REPLACE FUNCTION check_workspace_agent_name_unique()
+RETURNS TRIGGER AS $$
+DECLARE
+    workspace_id_var uuid;
+    existing_count integer;
+BEGIN
+    -- Get the workspace_id for this agent by following the relationship chain:
+    -- workspace_agents -> workspace_resources -> provisioner_jobs -> workspace_builds -> workspaces
+    SELECT wb.workspace_id INTO workspace_id_var
+    FROM workspace_resources wr
+    JOIN provisioner_jobs pj ON wr.job_id = pj.id
+    JOIN workspace_builds wb ON pj.id = wb.job_id
+    WHERE wr.id = NEW.resource_id;
+
+    -- If we couldn't find a workspace_id, allow the insert (might be a template import or other edge case)
+    IF workspace_id_var IS NULL THEN
+        RETURN NEW;
+    END IF;
+
+    -- Check if there's already an agent with this name in this workspace
+    SELECT COUNT(*) INTO existing_count
+    FROM workspace_agents wa
+    JOIN workspace_resources wr ON wa.resource_id = wr.id
+    JOIN provisioner_jobs pj ON wr.job_id = pj.id
+    JOIN workspace_builds wb ON pj.id = wb.job_id
+    WHERE wb.workspace_id = workspace_id_var
+      AND wa.name = NEW.name
+      AND wa.id != NEW.id;  -- Exclude the current agent (for updates)
+
+    -- If there's already an agent with this name, raise an error
+    IF existing_count > 0 THEN
+        RAISE EXCEPTION 'workspace agent name "%" already exists in this workspace', NEW.name
+            USING ERRCODE = 'unique_violation';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER workspace_agent_name_unique_trigger
+    BEFORE INSERT OR UPDATE OF name, resource_id ON workspace_agents
+    FOR EACH ROW
+    EXECUTE FUNCTION check_workspace_agent_name_unique();
diff --git a/coderd/database/querier_test.go b/coderd/database/querier_test.go
@@ -4,12 +4,15 @@ import (
 	"context"
 	"database/sql"
 	"encoding/json"
+	"errors"
 	"fmt"
 	"sort"
+	"sync/atomic"
 	"testing"
 	"time"
 
 	"github.com/google/uuid"
+	"github.com/lib/pq"
 	"github.com/prometheus/client_golang/prometheus"
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
@@ -4705,6 +4708,178 @@ func TestGetPresetsAtFailureLimit(t *testing.T) {
 	})
 }
 
+func TestWorkspaceAgentNameUniqueTrigger(t *testing.T) {
+	t.Parallel()
+
+	var builds atomic.Int32
+
+	if !dbtestutil.WillUsePostgres() {
+		t.Skip("This test makes use of a database trigger not implemented in dbmem")
+	}
+
+	createWorkspaceWithAgent := func(t *testing.T, db database.Store, org database.Organization, agentName string) (database.WorkspaceTable, database.TemplateVersion, database.WorkspaceAgent) {
+		t.Helper()
+
+		user := dbgen.User(t, db, database.User{})
+		template := dbgen.Template(t, db, database.Template{
+			OrganizationID: org.ID,
+			CreatedBy:      user.ID,
+		})
+		templateVersion := dbgen.TemplateVersion(t, db, database.TemplateVersion{
+			TemplateID:     uuid.NullUUID{Valid: true, UUID: template.ID},
+			OrganizationID: org.ID,
+			CreatedBy:      user.ID,
+		})
+		workspace := dbgen.Workspace(t, db, database.WorkspaceTable{
+			OrganizationID: org.ID,
+			TemplateID:     template.ID,
+			OwnerID:        user.ID,
+		})
+		job := dbgen.ProvisionerJob(t, db, nil, database.ProvisionerJob{
+			Type:           database.ProvisionerJobTypeWorkspaceBuild,
+			OrganizationID: org.ID,
+		})
+		build := dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
+			BuildNumber:       builds.Add(1),
+			JobID:             job.ID,
+			WorkspaceID:       workspace.ID,
+			TemplateVersionID: templateVersion.ID,
+		})
+		resource := dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
+			JobID: build.JobID,
+		})
+		agent := dbgen.WorkspaceAgent(t, db, database.WorkspaceAgent{
+			ResourceID: resource.ID,
+			Name:       agentName,
+		})
+
+		return workspace, templateVersion, agent
+	}
+
+	t.Run("DuplicateNamesInSameWorkspace", func(t *testing.T) {
+		t.Parallel()
+
+		db, _ := dbtestutil.NewDB(t)
+		org := dbgen.Organization(t, db, database.Organization{})
+		ctx := testutil.Context(t, testutil.WaitShort)
+
+		// Given: A workspace with an agent
+		workspace1, templateVersion1, agent1 := createWorkspaceWithAgent(t, db, org, "duplicate-agent")
+		require.Equal(t, "duplicate-agent", agent1.Name)
+
+		// When: Another agent is created for that workspace with the same name.
+		job2 := dbgen.ProvisionerJob(t, db, nil, database.ProvisionerJob{
+			Type:           database.ProvisionerJobTypeWorkspaceBuild,
+			OrganizationID: org.ID,
+		})
+		build2 := dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
+			BuildNumber:       builds.Add(1),
+			JobID:             job2.ID,
+			WorkspaceID:       workspace1.ID,
+			TemplateVersionID: templateVersion1.ID,
+		})
+		resource2 := dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
+			JobID: build2.JobID,
+		})
+		_, err := db.InsertWorkspaceAgent(ctx, database.InsertWorkspaceAgentParams{
+			ID:              uuid.New(),
+			CreatedAt:       time.Now(),
+			UpdatedAt:       time.Now(),
+			Name:            "duplicate-agent", // Same name as agent1
+			ResourceID:      resource2.ID,
+			AuthToken:       uuid.New(),
+			Architecture:    "amd64",
+			OperatingSystem: "linux",
+			APIKeyScope:     database.AgentKeyScopeEnumAll,
+		})
+
+		// Then: We expect it to fail.
+		require.Error(t, err)
+		var pqErr *pq.Error
+		require.True(t, errors.As(err, &pqErr))
+		require.Equal(t, pq.ErrorCode("23505"), pqErr.Code) // unique_violation
+		require.Contains(t, pqErr.Message, `workspace agent name "duplicate-agent" already exists in this workspace`)
+	})
+
+	t.Run("SameNamesInDifferentWorkspaces", func(t *testing.T) {
+		t.Parallel()
+
+		agentName := "same-name-different-workspace"
+
+		db, _ := dbtestutil.NewDB(t)
+		org := dbgen.Organization(t, db, database.Organization{})
+
+		// Given: A workspace with an agent
+		_, _, agent1 := createWorkspaceWithAgent(t, db, org, agentName)
+		require.Equal(t, agentName, agent1.Name)
+
+		// When: A second workspace is created with an agent having the same name
+		_, _, agent2 := createWorkspaceWithAgent(t, db, org, agentName)
+		require.Equal(t, agentName, agent2.Name)
+
+		// Then: We expect there to be different agents with the same name.
+		require.NotEqual(t, agent1.ID, agent2.ID)
+		require.Equal(t, agent1.Name, agent2.Name)
+	})
+
+	t.Run("NullWorkspaceID", func(t *testing.T) {
+		t.Parallel()
+
+		db, _ := dbtestutil.NewDB(t)
+		org := dbgen.Organization(t, db, database.Organization{})
+		ctx := testutil.Context(t, testutil.WaitShort)
+
+		// Given: A resource that does not belong to a workspace build (simulating template import)
+		orphanJob := dbgen.ProvisionerJob(t, db, nil, database.ProvisionerJob{
+			Type:           database.ProvisionerJobTypeTemplateVersionImport,
+			OrganizationID: org.ID,
+		})
+		orphanResource := dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
+			JobID: orphanJob.ID,
+		})
+
+		// And this resource has a workspace agent.
+		agent1, err := db.InsertWorkspaceAgent(ctx, database.InsertWorkspaceAgentParams{
+			ID:              uuid.New(),
+			CreatedAt:       time.Now(),
+			UpdatedAt:       time.Now(),
+			Name:            "orphan-agent",
+			ResourceID:      orphanResource.ID,
+			AuthToken:       uuid.New(),
+			Architecture:    "amd64",
+			OperatingSystem: "linux",
+			APIKeyScope:     database.AgentKeyScopeEnumAll,
+		})
+		require.NoError(t, err)
+		require.Equal(t, "orphan-agent", agent1.Name)
+
+		// When: We created another resource that does not belong to a workspace build.
+		orphanJob2 := dbgen.ProvisionerJob(t, db, nil, database.ProvisionerJob{
+			Type:           database.ProvisionerJobTypeTemplateVersionImport,
+			OrganizationID: org.ID,
+		})
+		orphanResource2 := dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
+			JobID: orphanJob2.ID,
+		})
+
+		// Then: We expect to be able to create an agent in this new resource that has the same name.
+		agent2, err := db.InsertWorkspaceAgent(ctx, database.InsertWorkspaceAgentParams{
+			ID:              uuid.New(),
+			CreatedAt:       time.Now(),
+			UpdatedAt:       time.Now(),
+			Name:            "orphan-agent", // Same name as agent1
+			ResourceID:      orphanResource2.ID,
+			AuthToken:       uuid.New(),
+			Architecture:    "amd64",
+			OperatingSystem: "linux",
+			APIKeyScope:     database.AgentKeyScopeEnumAll,
+		})
+		require.NoError(t, err)
+		require.Equal(t, "orphan-agent", agent2.Name)
+		require.NotEqual(t, agent1.ID, agent2.ID)
+	})
+}
+
 func requireUsersMatch(t testing.TB, expected []database.User, found []database.GetUsersRow, msg string) {
 	t.Helper()
 	require.ElementsMatch(t, expected, database.ConvertUserRows(found), msg)

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1,2 @@`
	`1`	`+DROP TRIGGER IF EXISTS workspace_agent_name_unique_trigger ON workspace_agents;`
	`2`	`+DROP FUNCTION IF EXISTS check_workspace_agent_name_unique();`