May 16, 2025 · May 1, 2025 · May 13, 2025 · May 15, 2025 · May 15, 2025 · May 15, 2025
diff --git a/coderd/apidoc/docs.go b/coderd/apidoc/docs.go
diff --git a/coderd/apidoc/swagger.json b/coderd/apidoc/swagger.json
diff --git a/coderd/database/dbgen/dbgen.go b/coderd/database/dbgen/dbgen.go
 func WorkspaceAgent(t testing.TB, db database.Store, orig database.WorkspaceAgent) database.WorkspaceAgent {
 agt, err := db.InsertWorkspaceAgent(genCtx, database.InsertWorkspaceAgentParams{
 ID:         takeFirst(orig.ID, uuid.New()),
 ParentID:   takeFirst(orig.ParentID, uuid.NullUUID{}),
 CreatedAt:  takeFirst(orig.CreatedAt, dbtime.Now()),
 UpdatedAt:  takeFirst(orig.UpdatedAt, dbtime.Now()),
 Name:       takeFirst(orig.Name, testutil.GetRandomName(t)),
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go

 agent := database.WorkspaceAgent{
 ID:                       arg.ID,
 ParentID:                 arg.ParentID,
 CreatedAt:                arg.CreatedAt,
 UpdatedAt:                arg.UpdatedAt,
 ResourceID:               arg.ResourceID,
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/foreign_key_constraint.go b/coderd/database/foreign_key_constraint.go
diff --git a/.../database/migrations/000318_update_protect_deleting_orgs_to_filter_deleted_users.down.sql b/.../database/migrations/000318_update_protect_deleting_orgs_to_filter_deleted_users.down.sql
 DROP TRIGGER IF EXISTS protect_deleting_organizations ON organizations;

 -- Replace the function with the new implementation
 CREATE OR REPLACE FUNCTION protect_deleting_organizations()
    RETURNS TRIGGER AS
 $$
 DECLARE
    workspace_count int;
    template_count int;
    group_count int;
    member_count int;
    provisioner_keys_count int;
 BEGIN
    workspace_count := (
        SELECT count(*) as count FROM workspaces
        WHERE
            workspaces.organization_id = OLD.id
            AND workspaces.deleted = false
    );

    template_count := (
        SELECT count(*) as count FROM templates
        WHERE
            templates.organization_id = OLD.id
            AND templates.deleted = false
    );

    group_count := (
        SELECT count(*) as count FROM groups
        WHERE
            groups.organization_id = OLD.id
    );

    member_count := (
        SELECT count(*) as count FROM organization_members
        WHERE
            organization_members.organization_id = OLD.id
    );

    provisioner_keys_count := (
        Select count(*) as count FROM provisioner_keys
        WHERE
            provisioner_keys.organization_id = OLD.id
    );

    -- Fail the deletion if one of the following:
    -- * the organization has 1 or more workspaces
    -- * the organization has 1 or more templates
    -- * the organization has 1 or more groups other than "Everyone" group
    -- * the organization has 1 or more members other than the organization owner
    -- * the organization has 1 or more provisioner keys

    -- Only create error message for resources that actually exist
    IF (workspace_count + template_count + provisioner_keys_count) > 0 THEN
        DECLARE
            error_message text := 'cannot delete organization: organization has ';
            error_parts text[] := '{}';
        BEGIN
            IF workspace_count > 0 THEN
                error_parts := array_append(error_parts, workspace_count || ' workspaces');
            END IF;

            IF template_count > 0 THEN
                error_parts := array_append(error_parts, template_count || ' templates');
            END IF;

            IF provisioner_keys_count > 0 THEN
                error_parts := array_append(error_parts, provisioner_keys_count || ' provisioner keys');
            END IF;

            error_message := error_message || array_to_string(error_parts, ', ') || ' that must be deleted first';
            RAISE EXCEPTION '%', error_message;
        END;
    END IF;

    IF (group_count) > 1 THEN
            RAISE EXCEPTION 'cannot delete organization: organization has % groups that must be deleted first', group_count - 1;
    END IF;

    -- Allow 1 member to exist, because you cannot remove yourself. You can
    -- remove everyone else. Ideally, we only omit the member that matches
    -- the user_id of the caller, however in a trigger, the caller is unknown.
    IF (member_count) > 1 THEN
            RAISE EXCEPTION 'cannot delete organization: organization has % members that must be deleted first', member_count - 1;
    END IF;

    RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

 -- Trigger to protect organizations from being soft deleted with existing resources
 CREATE TRIGGER protect_deleting_organizations
    BEFORE UPDATE ON organizations
    FOR EACH ROW
    WHEN (NEW.deleted = true AND OLD.deleted = false)
    EXECUTE FUNCTION protect_deleting_organizations();
Original file line number	Diff line number	Diff line change
Expand Up		@@ -157,6 +157,7 @@ func WorkspaceAgentPortShare(t testing.TB, db database.Store, orig database.Work
		func WorkspaceAgent(t testing.TB, db database.Store, orig database.WorkspaceAgent) database.WorkspaceAgent {
		agt, err := db.InsertWorkspaceAgent(genCtx, database.InsertWorkspaceAgentParams{
		ID: takeFirst(orig.ID, uuid.New()),
		ParentID: takeFirst(orig.ParentID, uuid.NullUUID{}),
		CreatedAt: takeFirst(orig.CreatedAt, dbtime.Now()),
		UpdatedAt: takeFirst(orig.UpdatedAt, dbtime.Now()),
		Name: takeFirst(orig.Name, testutil.GetRandomName(t)),
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -9477,6 +9477,7 @@ func (q *FakeQuerier) InsertWorkspaceAgent(_ context.Context, arg database.Inser

		agent := database.WorkspaceAgent{
		ID: arg.ID,
		ParentID: arg.ParentID,
		CreatedAt: arg.CreatedAt,
		UpdatedAt: arg.UpdatedAt,
		ResourceID: arg.ResourceID,
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,96 @@
		DROP TRIGGER IF EXISTS protect_deleting_organizations ON organizations;

		-- Replace the function with the new implementation
		CREATE OR REPLACE FUNCTION protect_deleting_organizations()
		RETURNS TRIGGER AS
		$$
		DECLARE
		workspace_count int;
		template_count int;
		group_count int;
		member_count int;
		provisioner_keys_count int;
		BEGIN
		workspace_count := (
		SELECT count(*) as count FROM workspaces
		WHERE
		workspaces.organization_id = OLD.id
		AND workspaces.deleted = false
		);

		template_count := (
		SELECT count(*) as count FROM templates
		WHERE
		templates.organization_id = OLD.id
		AND templates.deleted = false
		);

		group_count := (
		SELECT count(*) as count FROM groups
		WHERE
		groups.organization_id = OLD.id
		);

		member_count := (
		SELECT count(*) as count FROM organization_members
		WHERE
		organization_members.organization_id = OLD.id
		);

		provisioner_keys_count := (
		Select count(*) as count FROM provisioner_keys
		WHERE
		provisioner_keys.organization_id = OLD.id
		);

		-- Fail the deletion if one of the following:
		-- * the organization has 1 or more workspaces
		-- * the organization has 1 or more templates
		-- * the organization has 1 or more groups other than "Everyone" group
		-- * the organization has 1 or more members other than the organization owner
		-- * the organization has 1 or more provisioner keys

		-- Only create error message for resources that actually exist
		IF (workspace_count + template_count + provisioner_keys_count) > 0 THEN
		DECLARE
		error_message text := 'cannot delete organization: organization has ';
		error_parts text[] := '{}';
		BEGIN
		IF workspace_count > 0 THEN
		error_parts := array_append(error_parts, workspace_count \|\| ' workspaces');
		END IF;

		IF template_count > 0 THEN
		error_parts := array_append(error_parts, template_count \|\| ' templates');
		END IF;

		IF provisioner_keys_count > 0 THEN
		error_parts := array_append(error_parts, provisioner_keys_count \|\| ' provisioner keys');
		END IF;

		error_message := error_message \|\| array_to_string(error_parts, ', ') \|\| ' that must be deleted first';
		RAISE EXCEPTION '%', error_message;
		END;
		END IF;

		IF (group_count) > 1 THEN
		RAISE EXCEPTION 'cannot delete organization: organization has % groups that must be deleted first', group_count - 1;
		END IF;

		-- Allow 1 member to exist, because you cannot remove yourself. You can
		-- remove everyone else. Ideally, we only omit the member that matches
		-- the user_id of the caller, however in a trigger, the caller is unknown.
		IF (member_count) > 1 THEN
		RAISE EXCEPTION 'cannot delete organization: organization has % members that must be deleted first', member_count - 1;
		END IF;

		RETURN NEW;
		END;
		$$ LANGUAGE plpgsql;

		-- Trigger to protect organizations from being soft deleted with existing resources
		CREATE TRIGGER protect_deleting_organizations
		BEFORE UPDATE ON organizations
		FOR EACH ROW
		WHEN (NEW.deleted = true AND OLD.deleted = false)
		EXECUTE FUNCTION protect_deleting_organizations();