coder · Emyrk · Oct 10, 2023 · Oct 10, 2023 · Oct 10, 2023 · Oct 10, 2023
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -673,6 +673,17 @@ func (q *querier) AllUserIDs(ctx context.Context) ([]uuid.UUID, error) {
 	return q.db.AllUserIDs(ctx)
 }
 
+func (q *querier) ArchiveUnusedTemplateVersions(ctx context.Context, arg database.ArchiveUnusedTemplateVersionsParams) ([]uuid.UUID, error) {
+	tpl, err := q.db.GetTemplateByID(ctx, arg.TemplateID)
+	if err != nil {
+		return nil, err
+	}
+	if err := q.authorizeContext(ctx, rbac.ActionUpdate, tpl); err != nil {
+		return nil, err
+	}
+	return q.db.ArchiveUnusedTemplateVersions(ctx, arg)
+}
+
 func (q *querier) CleanTailnetCoordinators(ctx context.Context) error {
 	if err := q.authorizeContext(ctx, rbac.ActionDelete, rbac.ResourceTailnetCoordinator); err != nil {
 		return err
@@ -2260,6 +2271,22 @@ func (q *querier) TryAcquireLock(ctx context.Context, id int64) (bool, error) {
 	return q.db.TryAcquireLock(ctx, id)
 }
 
+func (q *querier) UnarchiveTemplateVersion(ctx context.Context, arg database.UnarchiveTemplateVersionParams) error {
+	v, err := q.db.GetTemplateVersionByID(ctx, arg.TemplateVersionID)
+	if err != nil {
+		return err
+	}
+
+	tpl, err := q.db.GetTemplateByID(ctx, v.TemplateID.UUID)
+	if err != nil {
+		return err
+	}
+	if err := q.authorizeContext(ctx, rbac.ActionUpdate, tpl); err != nil {
+		return err
+	}
+	return q.db.UnarchiveTemplateVersion(ctx, arg)
+}
+
 func (q *querier) UpdateAPIKeyByID(ctx context.Context, arg database.UpdateAPIKeyByIDParams) error {
 	fetch := func(ctx context.Context, arg database.UpdateAPIKeyByIDParams) (database.APIKey, error) {
 		return q.db.GetAPIKeyByID(ctx, arg.ID)

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -342,6 +342,41 @@ func (s *MethodTestSuite) TestGroup() {
 }
 
 func (s *MethodTestSuite) TestProvsionerJob() {
+	s.Run("ArchiveUnusedTemplateVersions", s.Subtest(func(db database.Store, check *expects) {
+		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{
+			Type: database.ProvisionerJobTypeTemplateVersionImport,
+			Error: sql.NullString{
+				String: "failed",
+				Valid:  true,
+			},
+		})
+		tpl := dbgen.Template(s.T(), db, database.Template{})
+		v := dbgen.TemplateVersion(s.T(), db, database.TemplateVersion{
+			TemplateID: uuid.NullUUID{UUID: tpl.ID, Valid: true},
+			JobID:      j.ID,
+		})
+		check.Args(database.ArchiveUnusedTemplateVersionsParams{
+			UpdatedAt:         dbtime.Now(),
+			TemplateID:        tpl.ID,
+			TemplateVersionID: uuid.Nil,
+			JobStatus:         database.NullProvisionerJobStatus{},
+		}).Asserts(v.RBACObject(tpl), rbac.ActionUpdate)
+	}))
+	s.Run("UnarchiveTemplateVersion", s.Subtest(func(db database.Store, check *expects) {
+		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{
+			Type: database.ProvisionerJobTypeTemplateVersionImport,
+		})
+		tpl := dbgen.Template(s.T(), db, database.Template{})
+		v := dbgen.TemplateVersion(s.T(), db, database.TemplateVersion{
+			TemplateID: uuid.NullUUID{UUID: tpl.ID, Valid: true},
+			JobID:      j.ID,
+			Archived:   true,
+		})
+		check.Args(database.UnarchiveTemplateVersionParams{
+			UpdatedAt:         dbtime.Now(),
+			TemplateVersionID: v.ID,
+		}).Asserts(v.RBACObject(tpl), rbac.ActionUpdate)
+	}))
 	s.Run("Build/GetProvisionerJobByID", s.Subtest(func(db database.Store, check *expects) {
 		w := dbgen.Workspace(s.T(), db, database.Workspace{})
 		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -846,6 +846,82 @@ func (q *FakeQuerier) AllUserIDs(_ context.Context) ([]uuid.UUID, error) {
 	return userIDs, nil
 }
 
+func (q *FakeQuerier) ArchiveUnusedTemplateVersions(_ context.Context, arg database.ArchiveUnusedTemplateVersionsParams) ([]uuid.UUID, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+	q.mutex.Lock()
+	defer q.mutex.Unlock()
+	type latestBuild struct {
+		Number  int32
+		Version uuid.UUID
+	}
+	latest := make(map[uuid.UUID]latestBuild)
+
+	for _, b := range q.workspaceBuilds {
+		v, ok := latest[b.WorkspaceID]
+		if ok || b.BuildNumber < v.Number {
+			// Not the latest
+			continue
+		}
+		// Ignore deleted workspaces.
+		if b.Transition == database.WorkspaceTransitionDelete {
+			continue
+		}
+		latest[b.WorkspaceID] = latestBuild{
+			Number:  b.BuildNumber,
+			Version: b.TemplateVersionID,
+		}
+	}
+
+	usedVersions := make(map[uuid.UUID]bool)
+	for _, l := range latest {
+		usedVersions[l.Version] = true
+	}
+	for _, tpl := range q.templates {
+		usedVersions[tpl.ActiveVersionID] = true
+	}
+
+	var archived []uuid.UUID
+	for i, v := range q.templateVersions {
+		if arg.TemplateVersionID != uuid.Nil {
+			if v.ID != arg.TemplateVersionID {
+				continue
+			}
+		}
+		if v.Archived {
+			continue
+		}
+
+		if _, ok := usedVersions[v.ID]; !ok {
+			var job *database.ProvisionerJob
+			for i, j := range q.provisionerJobs {
+				if v.JobID == j.ID {
+					job = &q.provisionerJobs[i]
+					break
+				}
+			}
+
+			if arg.JobStatus.Valid {
+				if job.JobStatus != arg.JobStatus.ProvisionerJobStatus {
+					continue
+				}
+			}
+
+			if job.JobStatus == database.ProvisionerJobStatusRunning || job.JobStatus == database.ProvisionerJobStatusPending {
+				continue
+			}
+
+			v.Archived = true
+			q.templateVersions[i] = v
+			archived = append(archived, v.ID)
+		}
+	}
+
+	return archived, nil
+}
+
 func (*FakeQuerier) CleanTailnetCoordinators(_ context.Context) error {
 	return ErrUnimplemented
 }
@@ -2759,6 +2835,9 @@ func (q *FakeQuerier) GetTemplateVersionsByTemplateID(_ context.Context, arg dat
 		if templateVersion.TemplateID.UUID != arg.TemplateID {
 			continue
 		}
+		if arg.Archived.Valid && arg.Archived.Bool != templateVersion.Archived {
+			continue
+		}
 		version = append(version, q.templateVersionWithUserNoLock(templateVersion))
 	}
 
@@ -5261,6 +5340,24 @@ func (*FakeQuerier) TryAcquireLock(_ context.Context, _ int64) (bool, error) {
 	return false, xerrors.New("TryAcquireLock must only be called within a transaction")
 }
 
+func (q *FakeQuerier) UnarchiveTemplateVersion(_ context.Context, arg database.UnarchiveTemplateVersionParams) error {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return err
+	}
+
+	for i, v := range q.data.templateVersions {
+		if v.ID == arg.TemplateVersionID {
+			v.Archived = false
+			v.UpdatedAt = arg.UpdatedAt
+			q.data.templateVersions[i] = v
+			return nil
+		}
+	}
+
+	return sql.ErrNoRows
+}
+
 func (q *FakeQuerier) UpdateAPIKeyByID(_ context.Context, arg database.UpdateAPIKeyByIDParams) error {
 	if err := validateDatabaseType(arg); err != nil {
 		return err

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/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000164_archive_template_versions.down.sql b/coderd/database/migrations/000164_archive_template_versions.down.sql
@@ -0,0 +1,26 @@
+BEGIN;
+
+-- The view will be rebuilt with the new column
+DROP VIEW template_version_with_user;
+
+ALTER TABLE template_versions
+	DROP COLUMN archived;
+
+-- Restore the old version of the template_version_with_user view.
+CREATE VIEW
+	template_version_with_user
+AS
+SELECT
+	template_versions.*,
+	coalesce(visible_users.avatar_url, '') AS created_by_avatar_url,
+	coalesce(visible_users.username, '') AS created_by_username
+FROM
+	template_versions
+		LEFT JOIN
+	visible_users
+	ON
+			template_versions.created_by = visible_users.id;
+
+COMMENT ON VIEW template_version_with_user IS 'Joins in the username + avatar url of the created by user.';
+
+COMMIT;
diff --git a/coderd/database/migrations/000164_archive_template_versions.up.sql b/coderd/database/migrations/000164_archive_template_versions.up.sql
@@ -0,0 +1,27 @@
+BEGIN;
+
+-- The view will be rebuilt with the new column
+DROP VIEW template_version_with_user;
+
+-- Archived template versions are not visible or usable by default.
+ALTER TABLE template_versions
+	ADD COLUMN archived BOOLEAN NOT NULL DEFAULT FALSE;
+
+-- Restore the old version of the template_version_with_user view.
+CREATE VIEW
+	template_version_with_user
+AS
+SELECT
+	template_versions.*,
+	coalesce(visible_users.avatar_url, '') AS created_by_avatar_url,
+	coalesce(visible_users.username, '') AS created_by_username
+FROM
+	template_versions
+		LEFT JOIN
+	visible_users
+	ON
+			template_versions.created_by = visible_users.id;
+
+COMMENT ON VIEW template_version_with_user IS 'Joins in the username + avatar url of the created by user.';
+
+COMMIT;
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go