Add commit status summary table to reduce query from commit status table (#30223)

This PR adds a new table named commit status summary to reduce queries
from the commit status table. After this change, commit status summary
table will be used for the final result, commit status table will be for
details.

---------

Co-authored-by: Jason Song <i@wolfogre.com>

2 files changed, 49 insertions, 19 deletions

diff --git a/services/actions/commit_status.go b/services/actions/commit_status.go
index 4236553927..eb031511f6 100644
--- a/services/actions/commit_status.go
+++ b/services/actions/commit_status.go
@@ -16,6 +16,7 @@ import (
 	"code.gitea.io/gitea/modules/log"
 	api "code.gitea.io/gitea/modules/structs"
 	webhook_module "code.gitea.io/gitea/modules/webhook"
+	commitstatus_service "code.gitea.io/gitea/services/repository/commitstatus"
 
 	"github.com/nektos/act/pkg/jobparser"
 )
@@ -122,18 +123,13 @@ func createCommitStatus(ctx context.Context, job *actions_model.ActionRunJob) er
 	if err != nil {
 		return fmt.Errorf("HashTypeInterfaceFromHashString: %w", err)
 	}
-	if err := git_model.NewCommitStatus(ctx, git_model.NewCommitStatusOptions{
-		Repo:    repo,
-		SHA:     commitID,
-		Creator: creator,
-		CommitStatus: &git_model.CommitStatus{
-			SHA:         sha,
-			TargetURL:   fmt.Sprintf("%s/jobs/%d", run.Link(), index),
-			Description: description,
-			Context:     ctxname,
-			CreatorID:   creator.ID,
-			State:       state,
-		},
+	if err := commitstatus_service.CreateCommitStatus(ctx, repo, creator, commitID.String(), &git_model.CommitStatus{
+		SHA:         sha,
+		TargetURL:   fmt.Sprintf("%s/jobs/%d", run.Link(), index),
+		Description: description,
+		Context:     ctxname,
+		CreatorID:   creator.ID,
+		State:       state,
 	}); err != nil {
 		return fmt.Errorf("NewCommitStatus: %w", err)
 	}
diff --git a/services/repository/commitstatus/commitstatus.go b/services/repository/commitstatus/commitstatus.go
index 145fc7d53c..167a5330dd 100644
--- a/services/repository/commitstatus/commitstatus.go
+++ b/services/repository/commitstatus/commitstatus.go
@@ -7,6 +7,7 @@ import (
 	"context"
 	"crypto/sha256"
 	"fmt"
+	"slices"
 
 	"code.gitea.io/gitea/models/db"
 	git_model "code.gitea.io/gitea/models/git"
@@ -59,13 +60,19 @@ func CreateCommitStatus(ctx context.Context, repo *repo_model.Repository, creato
 		sha = commit.ID.String()
 	}
 
-	if err := git_model.NewCommitStatus(ctx, git_model.NewCommitStatusOptions{
-		Repo:         repo,
-		Creator:      creator,
-		SHA:          commit.ID,
-		CommitStatus: status,
+	if err := db.WithTx(ctx, func(ctx context.Context) error {
+		if err := git_model.NewCommitStatus(ctx, git_model.NewCommitStatusOptions{
+			Repo:         repo,
+			Creator:      creator,
+			SHA:          commit.ID,
+			CommitStatus: status,
+		}); err != nil {
+			return fmt.Errorf("NewCommitStatus[repo_id: %d, user_id: %d, sha: %s]: %w", repo.ID, creator.ID, sha, err)
+		}
+
+		return git_model.UpdateCommitStatusSummary(ctx, repo.ID, commit.ID.String())
 	}); err != nil {
-		return fmt.Errorf("NewCommitStatus[repo_id: %d, user_id: %d, sha: %s]: %w", repo.ID, creator.ID, sha, err)
+		return err
 	}
 
 	defaultBranchCommit, err := gitRepo.GetBranchCommit(repo.DefaultBranch)
@@ -114,8 +121,35 @@ func FindReposLastestCommitStatuses(ctx context.Context, repos []*repo_model.Rep
 		return nil, fmt.Errorf("FindBranchesByRepoAndBranchName: %v", err)
 	}
 
+	var repoSHAs []git_model.RepoSHA
+	for id, sha := range repoIDsToLatestCommitSHAs {
+		repoSHAs = append(repoSHAs, git_model.RepoSHA{RepoID: id, SHA: sha})
+	}
+
+	summaryResults, err := git_model.GetLatestCommitStatusForRepoAndSHAs(ctx, repoSHAs)
+	if err != nil {
+		return nil, fmt.Errorf("GetLatestCommitStatusForRepoAndSHAs: %v", err)
+	}
+
+	for _, summary := range summaryResults {
+		for i, repo := range repos {
+			if repo.ID == summary.RepoID {
+				results[i] = summary
+				_ = slices.DeleteFunc(repoSHAs, func(repoSHA git_model.RepoSHA) bool {
+					return repoSHA.RepoID == repo.ID
+				})
+				if results[i].State != "" {
+					if err := updateCommitStatusCache(ctx, repo.ID, repo.DefaultBranch, results[i].State); err != nil {
+						log.Error("updateCommitStatusCache[%d:%s] failed: %v", repo.ID, repo.DefaultBranch, err)
+					}
+				}
+				break
+			}
+		}
+	}
+
 	// call the database O(1) times to get the commit statuses for all repos
-	repoToItsLatestCommitStatuses, err := git_model.GetLatestCommitStatusForPairs(ctx, repoIDsToLatestCommitSHAs, db.ListOptionsAll)
+	repoToItsLatestCommitStatuses, err := git_model.GetLatestCommitStatusForPairs(ctx, repoSHAs)
 	if err != nil {
 		return nil, fmt.Errorf("GetLatestCommitStatusForPairs: %v", err)
 	}