send events on ownership transfer

ethanndickson · ethanndickson · commit 22ece36227ea · 2024-10-03T14:48:45.000Z
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000260_workspace_updates.down.sql b/coderd/database/migrations/000260_workspace_updates.down.sql
@@ -4,4 +4,4 @@ DROP TRIGGER IF EXISTS new_workspace_notify ON workspaces;
 DROP FUNCTION IF EXISTS new_workspace_notify;
 
 DROP TRIGGER IF EXISTS new_agent_notify ON workspace_agents;
-DROP FUNCTION new_agent_notify;
+DROP FUNCTION IF EXISTS new_agent_notify;
diff --git a/coderd/database/migrations/000260_workspace_updates.up.sql b/coderd/database/migrations/000260_workspace_updates.up.sql
@@ -8,15 +8,18 @@ CREATE FUNCTION new_workspace_notify() RETURNS trigger
 AS $$
 DECLARE
 BEGIN
-	-- Write to the notification channel `new_workspace:owner_id`
-	-- with the workspace id as the payload.
-	PERFORM pg_notify('new_workspace:' || NEW.owner_id, NEW.id::text);
+	-- Notify for new workspaces & ownership transfers
+	IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.owner_id <> OLD.owner_id) THEN
+		-- Write to the notification channel `new_workspace:owner_id`
+		-- with the workspace id as the payload.
+		PERFORM pg_notify('new_workspace:' || NEW.owner_id, NEW.id::text);
+	END IF;
 	RETURN NEW;
 END;
 $$;
 
 CREATE TRIGGER new_workspace_notify
-	AFTER INSERT ON workspaces
+	AFTER INSERT OR UPDATE ON workspaces
 	FOR EACH ROW
 EXECUTE FUNCTION new_workspace_notify();
 
@@ -68,4 +71,3 @@ CREATE TRIGGER new_agent_notify
 	AFTER INSERT ON workspace_agents
 	FOR EACH ROW
 EXECUTE FUNCTION new_agent_notify();
-
