Aug 20, 2025 · Aug 15, 2025 · Aug 15, 2025 · Aug 15, 2025 · Aug 7, 2025 · Aug 15, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
 return q.db.FetchVolumesResourceMonitorsUpdatedAfter(ctx, updatedAt)
 }

 func (q *querier) FindMatchingPresetID(ctx context.Context, arg database.FindMatchingPresetIDParams) (uuid.UUID, error) {
 _, err := q.GetTemplateVersionByID(ctx, arg.TemplateVersionID)
 if err != nil {
 return uuid.Nil, err
 }
 return q.db.FindMatchingPresetID(ctx, arg)
 }

 func (q *querier) GetAPIKeyByID(ctx context.Context, id string) (database.APIKey, error) {
 return fetch(q.log, q.auth, q.db.GetAPIKeyByID)(ctx, id)
 }
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
 template, policy.ActionUse,
 ).Errors(sql.ErrNoRows)
 }))
 s.Run("FindMatchingPresetID", s.Mocked(func(dbm *dbmock.MockStore, faker *gofakeit.Faker, check *expects) {
 t1 := testutil.Fake(s.T(), faker, database.Template{})
 tv := testutil.Fake(s.T(), faker, database.TemplateVersion{TemplateID: uuid.NullUUID{UUID: t1.ID, Valid: true}})
 dbm.EXPECT().FindMatchingPresetID(gomock.Any(), database.FindMatchingPresetIDParams{
 TemplateVersionID: tv.ID,
 ParameterNames:    []string{"test"},
 ParameterValues:   []string{"test"},
 }).Return(uuid.Nil, nil).AnyTimes()
 dbm.EXPECT().GetTemplateVersionByID(gomock.Any(), tv.ID).Return(tv, nil).AnyTimes()
 dbm.EXPECT().GetTemplateByID(gomock.Any(), t1.ID).Return(t1, nil).AnyTimes()
 check.Args(database.FindMatchingPresetIDParams{
 TemplateVersionID: tv.ID,
 ParameterNames:    []string{"test"},
 ParameterValues:   []string{"test"},
 }).Asserts(tv.RBACObject(t1), policy.ActionRead).Returns(uuid.Nil)
 }))
 s.Run("GetPrebuildMetrics", s.Subtest(func(_ database.Store, check *expects) {
 check.Args().
 Asserts(rbac.ResourceWorkspace.All(), policy.ActionRead)
diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.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/prebuilds.sql b/coderd/database/queries/prebuilds.sql
 WHERE NOT t.deleted AND wpb.build_number = 1
 GROUP BY t.name, tvp.name, o.name
 ORDER BY t.name, tvp.name, o.name;

 -- name: FindMatchingPresetID :one
 -- FindMatchingPresetID finds a preset ID that is the largest exact subset of the provided parameters.
 -- It returns the preset ID if a match is found, or NULL if no match is found.
 -- The query finds presets where all preset parameters are present in the provided parameters,
 -- and returns the preset with the most parameters (largest subset).
 WITH provided_params AS (
 SELECT
 unnest(@parameter_names::text[]) AS name,
 unnest(@parameter_values::text[]) AS value
 ),
 preset_matches AS (
 SELECT
 tvp.id AS template_version_preset_id,
 COALESCE(COUNT(tvpp.name), 0) AS total_preset_params,
 COALESCE(COUNT(pp.name), 0) AS matching_params
 FROM template_version_presets tvp
 LEFT JOIN template_version_preset_parameters tvpp ON tvpp.template_version_preset_id = tvp.id
 LEFT JOIN provided_params pp ON pp.name = tvpp.name AND pp.value = tvpp.value
 WHERE tvp.template_version_id = @template_version_id
 GROUP BY tvp.id
 )
 SELECT pm.template_version_preset_id
 FROM preset_matches pm
 WHERE pm.total_preset_params = pm.matching_params  -- All preset parameters must match
 ORDER BY pm.total_preset_params DESC               -- Return the preset with the most parameters
 LIMIT 1;
diff --git a/coderd/prebuilds/parameters.go b/coderd/prebuilds/parameters.go
 package prebuilds

 import (
 "context"
 "database/sql"
 "errors"

 "github.com/google/uuid"
 "golang.org/x/xerrors"

 "github.com/coder/coder/v2/coderd/database"
 )

 // FindMatchingPresetID finds a preset ID that matches the provided parameters.
 // It returns the preset ID if a match is found, or uuid.Nil if no match is found.
 // The function performs a bidirectional comparison to ensure all parameters match exactly.
 func FindMatchingPresetID(
 ctx context.Context,
 store database.Store,
 templateVersionID uuid.UUID,
 parameterNames []string,
 parameterValues []string,
 ) (uuid.UUID, error) {
 if len(parameterNames) != len(parameterValues) {
 return uuid.Nil, xerrors.New("parameter names and values must have the same length")
 }

 result, err := store.FindMatchingPresetID(ctx, database.FindMatchingPresetIDParams{
 TemplateVersionID: templateVersionID,
 ParameterNames:    parameterNames,
 ParameterValues:   parameterValues,
 })
 if err != nil {
 // Handle the case where no matching preset is found (no rows returned)
 if errors.Is(err, sql.ErrNoRows) {
 return uuid.Nil, nil
 }
 return uuid.Nil, xerrors.Errorf("find matching preset ID: %w", err)
 }

 return result, nil
 }
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1810,6 +1810,14 @@ func (q *querier) FetchVolumesResourceMonitorsUpdatedAfter(ctx context.Context,
		return q.db.FetchVolumesResourceMonitorsUpdatedAfter(ctx, updatedAt)
		}

		func (q *querier) FindMatchingPresetID(ctx context.Context, arg database.FindMatchingPresetIDParams) (uuid.UUID, error) {
		_, err := q.GetTemplateVersionByID(ctx, arg.TemplateVersionID)
Copy link ContributorAuthor SasSwartAug 15, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. I don't like this, but I can't find a better way and there is precedent for this approach. Open to alternatives. Copy link Member johnstcnAug 15, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. The alternative is to essentially duplicate the logic in`GetTemplateVersionByID`, which I'm less a fan of.
		if err != nil {
		return uuid.Nil, err
		}
		return q.db.FindMatchingPresetID(ctx, arg)
		}

		func (q *querier) GetAPIKeyByID(ctx context.Context, id string) (database.APIKey, error) {
		return fetch(q.log, q.auth, q.db.GetAPIKeyByID)(ctx, id)
		}
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -4959,6 +4959,22 @@ func (s *MethodTestSuite) TestPrebuilds() {
		template, policy.ActionUse,
		).Errors(sql.ErrNoRows)
		}))
		s.Run("FindMatchingPresetID", s.Mocked(func(dbm dbmock.MockStore, faker gofakeit.Faker, check *expects) {
		t1 := testutil.Fake(s.T(), faker, database.Template{})
		tv := testutil.Fake(s.T(), faker, database.TemplateVersion{TemplateID: uuid.NullUUID{UUID: t1.ID, Valid: true}})
		dbm.EXPECT().FindMatchingPresetID(gomock.Any(), database.FindMatchingPresetIDParams{
		TemplateVersionID: tv.ID,
		ParameterNames: []string{"test"},
		ParameterValues: []string{"test"},
		}).Return(uuid.Nil, nil).AnyTimes()
		dbm.EXPECT().GetTemplateVersionByID(gomock.Any(), tv.ID).Return(tv, nil).AnyTimes()
		dbm.EXPECT().GetTemplateByID(gomock.Any(), t1.ID).Return(t1, nil).AnyTimes()
		check.Args(database.FindMatchingPresetIDParams{
		TemplateVersionID: tv.ID,
		ParameterNames: []string{"test"},
		ParameterValues: []string{"test"},
		}).Asserts(tv.RBACObject(t1), policy.ActionRead).Returns(uuid.Nil)
		}))
		s.Run("GetPrebuildMetrics", s.Subtest(func(_ database.Store, check *expects) {
		check.Args().
		Asserts(rbac.ResourceWorkspace.All(), policy.ActionRead)
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -245,3 +245,30 @@ INNER JOIN organizations o ON o.id = w.organization_id
		WHERE NOT t.deleted AND wpb.build_number = 1
		GROUP BY t.name, tvp.name, o.name
		ORDER BY t.name, tvp.name, o.name;

		-- name: FindMatchingPresetID :one
		-- FindMatchingPresetID finds a preset ID that is the largest exact subset of the provided parameters.
		-- It returns the preset ID if a match is found, or NULL if no match is found.
		-- The query finds presets where all preset parameters are present in the provided parameters,
		-- and returns the preset with the most parameters (largest subset).
		WITH provided_params AS (
		SELECT
		unnest(@parameter_names::text[]) AS name,
		unnest(@parameter_values::text[]) AS value
		),
		preset_matches AS (
		SELECT
		tvp.id AS template_version_preset_id,
		COALESCE(COUNT(tvpp.name), 0) AS total_preset_params,
		COALESCE(COUNT(pp.name), 0) AS matching_params
		FROM template_version_presets tvp
		LEFT JOIN template_version_preset_parameters tvpp ON tvpp.template_version_preset_id = tvp.id
Copy link Member mafredriAug 18, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. These left join suggests there is merit in including presets with no parameters as well as zero matches with provided params, why would we want this? Copy link ContributorAuthor SasSwartAug 18, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. Presets that define 0 parameters are valid. They're used in more trivial templates, usually docker based, where there really isn't much to change but one still wants prebuilds. In this case one would define a preset with no parameters that desires`n` prebuilt workspaces. This form of the query allows a workspace in such a template to still match a preset and therefore claim a prebuilt workspace. mafredri reacted with thumbs up emoji Copy link ContributorAuthor SasSwartAug 20, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. This is useful for simpler parameters where one wants prebuilds without specifying any parameters. You could define a preset that sets no parameters. If such a preset exists, it would be selected automatically by this.
		LEFT JOIN provided_params pp ON pp.name = tvpp.name AND pp.value = tvpp.value
		WHERE tvp.template_version_id = @template_version_id
		GROUP BY tvp.id
		)
		SELECT pm.template_version_preset_id
		FROM preset_matches pm
		WHERE pm.total_preset_params = pm.matching_params -- All preset parameters must match
		ORDER BY pm.total_preset_params DESC -- Return the preset with the most parameters
		LIMIT 1;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,42 @@
		package prebuilds

		import (
		"context"
		"database/sql"
		"errors"

		"github.com/google/uuid"
		"golang.org/x/xerrors"

		"github.com/coder/coder/v2/coderd/database"
		)

		// FindMatchingPresetID finds a preset ID that matches the provided parameters.
		// It returns the preset ID if a match is found, or uuid.Nil if no match is found.
		// The function performs a bidirectional comparison to ensure all parameters match exactly.
		func FindMatchingPresetID(
		ctx context.Context,
		store database.Store,
		templateVersionID uuid.UUID,
		parameterNames []string,
		parameterValues []string,
		) (uuid.UUID, error) {
		if len(parameterNames) != len(parameterValues) {
		return uuid.Nil, xerrors.New("parameter names and values must have the same length")
		}

		result, err := store.FindMatchingPresetID(ctx, database.FindMatchingPresetIDParams{
		TemplateVersionID: templateVersionID,
		ParameterNames: parameterNames,
		ParameterValues: parameterValues,
		})
		if err != nil {
		// Handle the case where no matching preset is found (no rows returned)
		if errors.Is(err, sql.ErrNoRows) {
		return uuid.Nil, nil
		}
		return uuid.Nil, xerrors.Errorf("find matching preset ID: %w", err)
		}

		return result, nil
		}