coder · mtojek · Dec 1, 2023 · Nov 30, 2023 · Nov 30, 2023 · Nov 30, 2023
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -777,6 +777,13 @@ func (q *querier) DeleteLicense(ctx context.Context, id int32) (int32, error) {
 	return id, nil
 }
 
+func (q *querier) DeleteOldProvisionerDaemons(ctx context.Context) error {
+	if err := q.authorizeContext(ctx, rbac.ActionDelete, rbac.ResourceSystem); err != nil {
+		return err
+	}
+	return q.db.DeleteOldProvisionerDaemons(ctx)
+}
+
 func (q *querier) DeleteOldWorkspaceAgentLogs(ctx context.Context) error {
 	if err := q.authorizeContext(ctx, rbac.ActionDelete, rbac.ResourceSystem); err != nil {
 		return err

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -1104,8 +1104,13 @@ func (q *FakeQuerier) DeleteLicense(_ context.Context, id int32) (int32, error)
 	return 0, sql.ErrNoRows
 }
 
+func (*FakeQuerier) DeleteOldProvisionerDaemons(_ context.Context) error {
+	// no-op
+	return nil
+}
+
 func (*FakeQuerier) DeleteOldWorkspaceAgentLogs(_ context.Context) error {
-	// noop
+	// no-op
 	return nil
 }
 

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/provisionerdaemons.sql b/coderd/database/queries/provisionerdaemons.sql
@@ -15,3 +15,10 @@ INSERT INTO
 	)
 VALUES
 	($1, $2, $3, $4, $5) RETURNING *;
+
+-- name: DeleteOldProvisionerDaemons :exec
+-- Delete provisioner daemons that have been created at least a week ago
+-- and have not connected to coderd since a week.
+-- A provisioner daemon with "zeroed" updated_at column indicates possible
+-- connectivity issues (no provisioner daemon activity since registration).
+DELETE FROM provisioner_daemons WHERE created_at < (NOW() - INTERVAL '7 days') AND updated_at < (NOW() - INTERVAL '7 days');