coder · SasSwart · Aug 7, 2025 · Aug 11, 2025 · Aug 11, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -4306,6 +4306,10 @@ func (q *querier) RevokeDBCryptKey(ctx context.Context, activeKeyDigest string)
 	return q.db.RevokeDBCryptKey(ctx, activeKeyDigest)
 }
 
+func (q *querier) SetWorkspaceBuildPresetByParameterHash(ctx context.Context, workspaceBuildID uuid.UUID) (database.SetWorkspaceBuildPresetByParameterHashRow, error) {
+	panic("not implemented")
+}
+
 func (q *querier) TryAcquireLock(ctx context.Context, id int64) (bool, error) {
 	return q.db.TryAcquireLock(ctx, id)
 }
@@ -4503,6 +4507,10 @@ func (q *querier) UpdateOrganizationDeletedByID(ctx context.Context, arg databas
 	return deleteQ(q.log, q.auth, q.db.GetOrganizationByID, deleteF)(ctx, arg.ID)
 }
 
+func (q *querier) UpdatePresetParameterHash(ctx context.Context, id uuid.UUID) (database.UpdatePresetParameterHashRow, error) {
+	panic("not implemented")
+}
+
 func (q *querier) UpdatePresetPrebuildStatus(ctx context.Context, arg database.UpdatePresetPrebuildStatusParams) error {
 	preset, err := q.db.GetPresetByID(ctx, arg.PresetID)
 	if err != nil {

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/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000356_add_parameter_hash_to_presets.down.sql b/coderd/database/migrations/000356_add_parameter_hash_to_presets.down.sql
@@ -0,0 +1,9 @@
+-- Drop the index first
+DROP INDEX IF EXISTS idx_template_version_presets_parameter_hash;
+
+-- Remove the parameter_hash column
+ALTER TABLE template_version_presets
+DROP COLUMN IF EXISTS parameter_hash;
+
+-- Drop the function
+DROP FUNCTION IF EXISTS generate_parameter_hash(TEXT[], TEXT[]);
diff --git a/coderd/database/migrations/000356_add_parameter_hash_to_presets.up.sql b/coderd/database/migrations/000356_add_parameter_hash_to_presets.up.sql
@@ -0,0 +1,60 @@
+-- Add parameter_hash column to template_version_presets table
+ALTER TABLE template_version_presets
+ADD COLUMN parameter_hash TEXT;
+
+-- Create a generic function to generate parameter hash from arrays
+CREATE OR REPLACE FUNCTION generate_parameter_hash(names TEXT[], param_values TEXT[])
+RETURNS TEXT AS $$
+DECLARE
+    param_pairs TEXT[] := '{}';
+    sorted_pairs TEXT[] := '{}';
+    hash_value TEXT;
+    i INTEGER;
+BEGIN
+    -- Validate input arrays have same length
+    IF array_length(names, 1) != array_length(param_values, 1) THEN
+        RAISE EXCEPTION 'Names and values arrays must have the same length';
+    END IF;
+
+    -- Build parameter pairs from arrays
+    FOR i IN 1..array_length(names, 1) LOOP
+        param_pairs := array_append(param_pairs, names[i] || '=' || param_values[i]);
+    END LOOP;
+
+    -- Sort the pairs for consistent hashing
+    SELECT array_agg(pair ORDER BY pair) INTO sorted_pairs
+    FROM unnest(param_pairs) AS pair;
+
+    -- Generate hash from sorted pairs
+    SELECT encode(sha256(array_to_string(sorted_pairs, '|')::bytea), 'hex') INTO hash_value;
+
+    RETURN hash_value;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Populate parameter_hash for existing presets
+UPDATE template_version_presets
+SET parameter_hash = generate_parameter_hash(
+    (SELECT array_agg(name ORDER BY name) FROM template_version_preset_parameters WHERE template_version_preset_id = template_version_presets.id),
+    (SELECT array_agg(value ORDER BY name) FROM template_version_preset_parameters WHERE template_version_preset_id = template_version_presets.id)
+)
+WHERE id IN (
+    SELECT DISTINCT template_version_preset_id
+    FROM template_version_preset_parameters
+);
+
+-- Make parameter_hash NOT NULL after populating
+-- ALTER TABLE template_version_presets
+-- ALTER COLUMN parameter_hash SET NOT NULL;
+
+-- Create index for efficient parameter hash lookups
+CREATE INDEX idx_template_version_presets_parameter_hash
+ON template_version_presets (template_version_id, parameter_hash);
+
+-- Add comment explaining the column
+COMMENT ON COLUMN template_version_presets.parameter_hash IS 'SHA256 hash of sorted parameter name-value pairs for efficient preset matching';
+
+-- Keep the function for use by the application
+COMMENT ON FUNCTION generate_parameter_hash(TEXT[], TEXT[]) IS 'Generates SHA256 hash of sorted parameter name-value pairs from arrays. First parameter is names array, second is values array.';
+
+
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go