// Code generated by sqlc. DO NOT EDIT. // versions: // sqlc v1.31.1 // source: vcs.sql package db import ( "context" "github.com/jackc/pgx/v5/pgtype" ) const deleteVCSConnection = `-- name: DeleteVCSConnection :exec WITH target AS ( SELECT vcs_connection.id FROM vcs_connection WHERE vcs_connection.id = $1 AND vcs_connection.workspace_id = $2 ), cleared_links AS ( DELETE FROM issue_vcs_pull_request WHERE pull_request_id IN ( SELECT vcs_pull_request.id FROM vcs_pull_request WHERE vcs_pull_request.connection_id IN (SELECT target.id FROM target) ) ), cleared_statuses AS ( DELETE FROM vcs_commit_status WHERE connection_id IN (SELECT target.id FROM target) ), cleared_prs AS ( DELETE FROM vcs_pull_request WHERE connection_id IN (SELECT target.id FROM target) ) DELETE FROM vcs_connection WHERE vcs_connection.id = $1 AND vcs_connection.workspace_id = $2 ` type DeleteVCSConnectionParams struct { ID pgtype.UUID `json:"id"` WorkspaceID pgtype.UUID `json:"workspace_id"` } // These tables carry no FKs, so the cascade that once removed the connection's // mirrored PRs, their issue links, and CI statuses is gone (migration 213). Do // that cleanup explicitly here, in one statement so it commits or rolls back // atomically with the connection row. The target CTE also scopes every child // delete to a connection that actually belongs to the workspace, so a wrong // workspace_id is a no-op rather than deleting another tenant's child rows. func (q *Queries) DeleteVCSConnection(ctx context.Context, arg DeleteVCSConnectionParams) error { _, err := q.db.Exec(ctx, deleteVCSConnection, arg.ID, arg.WorkspaceID) return err } const getIssueCombinedPullRequestCloseAggregate = `-- name: GetIssueCombinedPullRequestCloseAggregate :one WITH combined AS ( SELECT pr.state AS state, ipr.close_intent AS close_intent FROM github_pull_request pr JOIN issue_pull_request ipr ON ipr.pull_request_id = pr.id WHERE ipr.issue_id = $1 AND NOT ipr.reference_only UNION ALL SELECT pr.state AS state, ipr.close_intent AS close_intent FROM vcs_pull_request pr JOIN issue_vcs_pull_request ipr ON ipr.pull_request_id = pr.id WHERE ipr.issue_id = $1 AND NOT ipr.reference_only ) SELECT COALESCE(SUM(CASE WHEN state IN ('open', 'draft') THEN 1 ELSE 0 END), 0)::bigint AS open_count, COALESCE(SUM(CASE WHEN state = 'merged' AND close_intent THEN 1 ELSE 0 END), 0)::bigint AS merged_with_close_intent_count FROM combined ` type GetIssueCombinedPullRequestCloseAggregateRow struct { OpenCount int64 `json:"open_count"` MergedWithCloseIntentCount int64 `json:"merged_with_close_intent_count"` } // Cross-provider close gate. An issue can carry PRs from GitHub AND a // self-hosted VCS provider at the same time, so auto-advance has to see BOTH // table pairs. Reading only one (as the per-provider aggregates do) lets a // merged close-intent PR/MR on one provider advance an issue that still has an // open PR on the other — either webhook is blind to the other's in-flight work. // Sum the in-flight (open/draft) and merged-with-close-intent counts across // github_pull_request+issue_pull_request and vcs_pull_request+ // issue_vcs_pull_request. reference_only links are excluded on both sides, so a // bare body mention neither counts as in-flight nor gates advance. func (q *Queries) GetIssueCombinedPullRequestCloseAggregate(ctx context.Context, issueID pgtype.UUID) (GetIssueCombinedPullRequestCloseAggregateRow, error) { row := q.db.QueryRow(ctx, getIssueCombinedPullRequestCloseAggregate, issueID) var i GetIssueCombinedPullRequestCloseAggregateRow err := row.Scan(&i.OpenCount, &i.MergedWithCloseIntentCount) return i, err } const getVCSConnectionByID = `-- name: GetVCSConnectionByID :one SELECT id, workspace_id, provider, instance_url, account_login, access_token_encrypted, webhook_secret_encrypted, connected_by_id, created_at, updated_at FROM vcs_connection WHERE id = $1 ` func (q *Queries) GetVCSConnectionByID(ctx context.Context, id pgtype.UUID) (VcsConnection, error) { row := q.db.QueryRow(ctx, getVCSConnectionByID, id) var i VcsConnection err := row.Scan( &i.ID, &i.WorkspaceID, &i.Provider, &i.InstanceUrl, &i.AccountLogin, &i.AccessTokenEncrypted, &i.WebhookSecretEncrypted, &i.ConnectedByID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const linkIssueToVCSPullRequest = `-- name: LinkIssueToVCSPullRequest :exec INSERT INTO issue_vcs_pull_request ( issue_id, pull_request_id, linked_by_type, linked_by_id, close_intent, reference_only ) VALUES ( $1, $2, $4, $5, $3, $6 ) ON CONFLICT (issue_id, pull_request_id) DO UPDATE SET close_intent = CASE WHEN $7 THEN issue_vcs_pull_request.close_intent ELSE EXCLUDED.close_intent END, reference_only = CASE WHEN $7 THEN issue_vcs_pull_request.reference_only ELSE EXCLUDED.reference_only END ` type LinkIssueToVCSPullRequestParams struct { IssueID pgtype.UUID `json:"issue_id"` PullRequestID pgtype.UUID `json:"pull_request_id"` CloseIntent bool `json:"close_intent"` LinkedByType pgtype.Text `json:"linked_by_type"` LinkedByID pgtype.UUID `json:"linked_by_id"` ReferenceOnly bool `json:"reference_only"` PreserveCloseIntent bool `json:"preserve_close_intent"` } // ===================== // Issue ↔ VCS PR link // ===================== // reference_only marks a link justified ONLY by a bare body mention (no closing // keyword and no title/branch reference), mirroring the GitHub link upsert. // preserve_close_intent freezes both close_intent and reference_only once a // terminal merge/close event has been recorded. func (q *Queries) LinkIssueToVCSPullRequest(ctx context.Context, arg LinkIssueToVCSPullRequestParams) error { _, err := q.db.Exec(ctx, linkIssueToVCSPullRequest, arg.IssueID, arg.PullRequestID, arg.CloseIntent, arg.LinkedByType, arg.LinkedByID, arg.ReferenceOnly, arg.PreserveCloseIntent, ) return err } const listIssueIDsForVCSPRHead = `-- name: ListIssueIDsForVCSPRHead :many SELECT DISTINCT ipr.issue_id FROM vcs_pull_request pr JOIN issue_vcs_pull_request ipr ON ipr.pull_request_id = pr.id WHERE pr.connection_id = $1 AND pr.head_sha = $2 AND pr.head_sha <> '' ` type ListIssueIDsForVCSPRHeadParams struct { ConnectionID pgtype.UUID `json:"connection_id"` HeadSha string `json:"head_sha"` } // Issues linked to any PR whose head sha matches the given status, so a // commit-status event can fan out a PR-card refresh to the right issues. func (q *Queries) ListIssueIDsForVCSPRHead(ctx context.Context, arg ListIssueIDsForVCSPRHeadParams) ([]pgtype.UUID, error) { rows, err := q.db.Query(ctx, listIssueIDsForVCSPRHead, arg.ConnectionID, arg.HeadSha) if err != nil { return nil, err } defer rows.Close() items := []pgtype.UUID{} for rows.Next() { var issue_id pgtype.UUID if err := rows.Scan(&issue_id); err != nil { return nil, err } items = append(items, issue_id) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listVCSConnectionsByWorkspace = `-- name: ListVCSConnectionsByWorkspace :many SELECT id, workspace_id, provider, instance_url, account_login, access_token_encrypted, webhook_secret_encrypted, connected_by_id, created_at, updated_at FROM vcs_connection WHERE workspace_id = $1 ORDER BY created_at ASC ` // ===================== // VCS Connection (Forgejo / Gitea / GitLab) // ===================== func (q *Queries) ListVCSConnectionsByWorkspace(ctx context.Context, workspaceID pgtype.UUID) ([]VcsConnection, error) { rows, err := q.db.Query(ctx, listVCSConnectionsByWorkspace, workspaceID) if err != nil { return nil, err } defer rows.Close() items := []VcsConnection{} for rows.Next() { var i VcsConnection if err := rows.Scan( &i.ID, &i.WorkspaceID, &i.Provider, &i.InstanceUrl, &i.AccountLogin, &i.AccessTokenEncrypted, &i.WebhookSecretEncrypted, &i.ConnectedByID, &i.CreatedAt, &i.UpdatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listVCSPullRequestsByIssue = `-- name: ListVCSPullRequestsByIssue :many WITH checks AS ( SELECT pr.id AS pr_id, COUNT(*)::bigint AS total, SUM(CASE WHEN cs.state = 'failed' THEN 1 ELSE 0 END)::bigint AS failed, SUM(CASE WHEN cs.state = 'passed' THEN 1 ELSE 0 END)::bigint AS passed, SUM(CASE WHEN cs.state = 'pending' THEN 1 ELSE 0 END)::bigint AS pending FROM vcs_pull_request pr JOIN issue_vcs_pull_request ipr ON ipr.pull_request_id = pr.id JOIN vcs_commit_status cs ON cs.connection_id = pr.connection_id AND cs.sha = pr.head_sha AND pr.head_sha <> '' WHERE ipr.issue_id = $1 AND NOT ipr.reference_only GROUP BY pr.id ) SELECT pr.id, pr.workspace_id, pr.connection_id, pr.provider, pr.repo_owner, pr.repo_name, pr.pr_number, pr.title, pr.state, pr.html_url, pr.branch, pr.head_sha, pr.author_login, pr.author_avatar_url, pr.merged_at, pr.closed_at, pr.pr_created_at, pr.pr_updated_at, pr.additions, pr.deletions, pr.changed_files, pr.created_at, pr.updated_at, COALESCE(c.total, 0)::bigint AS checks_total, COALESCE(c.passed, 0)::bigint AS checks_passed, COALESCE(c.failed, 0)::bigint AS checks_failed, COALESCE(c.pending, 0)::bigint AS checks_pending FROM vcs_pull_request pr JOIN issue_vcs_pull_request ipr ON ipr.pull_request_id = pr.id LEFT JOIN checks c ON c.pr_id = pr.id WHERE ipr.issue_id = $1 AND NOT ipr.reference_only ORDER BY pr.pr_created_at DESC ` type ListVCSPullRequestsByIssueRow struct { ID pgtype.UUID `json:"id"` WorkspaceID pgtype.UUID `json:"workspace_id"` ConnectionID pgtype.UUID `json:"connection_id"` Provider string `json:"provider"` RepoOwner string `json:"repo_owner"` RepoName string `json:"repo_name"` PrNumber int32 `json:"pr_number"` Title string `json:"title"` State string `json:"state"` HtmlUrl string `json:"html_url"` Branch pgtype.Text `json:"branch"` HeadSha string `json:"head_sha"` AuthorLogin pgtype.Text `json:"author_login"` AuthorAvatarUrl pgtype.Text `json:"author_avatar_url"` MergedAt pgtype.Timestamptz `json:"merged_at"` ClosedAt pgtype.Timestamptz `json:"closed_at"` PrCreatedAt pgtype.Timestamptz `json:"pr_created_at"` PrUpdatedAt pgtype.Timestamptz `json:"pr_updated_at"` Additions int32 `json:"additions"` Deletions int32 `json:"deletions"` ChangedFiles int32 `json:"changed_files"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` ChecksTotal int64 `json:"checks_total"` ChecksPassed int64 `json:"checks_passed"` ChecksFailed int64 `json:"checks_failed"` ChecksPending int64 `json:"checks_pending"` } // Aggregates each PR's commit statuses for its CURRENT head sha into // passed/failed/pending counts. vcs_commit_status holds one row per // (connection, sha, context) with a normalized state, so a count by state is // correct. Statuses for an old head sha stay stored but are excluded by the // head_sha join, so a stale run can't pollute the bar. func (q *Queries) ListVCSPullRequestsByIssue(ctx context.Context, issueID pgtype.UUID) ([]ListVCSPullRequestsByIssueRow, error) { rows, err := q.db.Query(ctx, listVCSPullRequestsByIssue, issueID) if err != nil { return nil, err } defer rows.Close() items := []ListVCSPullRequestsByIssueRow{} for rows.Next() { var i ListVCSPullRequestsByIssueRow if err := rows.Scan( &i.ID, &i.WorkspaceID, &i.ConnectionID, &i.Provider, &i.RepoOwner, &i.RepoName, &i.PrNumber, &i.Title, &i.State, &i.HtmlUrl, &i.Branch, &i.HeadSha, &i.AuthorLogin, &i.AuthorAvatarUrl, &i.MergedAt, &i.ClosedAt, &i.PrCreatedAt, &i.PrUpdatedAt, &i.Additions, &i.Deletions, &i.ChangedFiles, &i.CreatedAt, &i.UpdatedAt, &i.ChecksTotal, &i.ChecksPassed, &i.ChecksFailed, &i.ChecksPending, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const rotateVCSConnectionWebhookSecret = `-- name: RotateVCSConnectionWebhookSecret :one UPDATE vcs_connection SET webhook_secret_encrypted = $3, updated_at = now() WHERE id = $1 AND workspace_id = $2 RETURNING id, workspace_id, provider, instance_url, account_login, access_token_encrypted, webhook_secret_encrypted, connected_by_id, created_at, updated_at ` type RotateVCSConnectionWebhookSecretParams struct { ID pgtype.UUID `json:"id"` WorkspaceID pgtype.UUID `json:"workspace_id"` WebhookSecretEncrypted string `json:"webhook_secret_encrypted"` } func (q *Queries) RotateVCSConnectionWebhookSecret(ctx context.Context, arg RotateVCSConnectionWebhookSecretParams) (VcsConnection, error) { row := q.db.QueryRow(ctx, rotateVCSConnectionWebhookSecret, arg.ID, arg.WorkspaceID, arg.WebhookSecretEncrypted) var i VcsConnection err := row.Scan( &i.ID, &i.WorkspaceID, &i.Provider, &i.InstanceUrl, &i.AccountLogin, &i.AccessTokenEncrypted, &i.WebhookSecretEncrypted, &i.ConnectedByID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const upsertVCSCommitStatus = `-- name: UpsertVCSCommitStatus :exec INSERT INTO vcs_commit_status ( connection_id, sha, context, state, target_url, description, updated_at ) VALUES ( $1, $2, $3, $4, $6, $7, $5 ) ON CONFLICT (connection_id, sha, context) DO UPDATE SET state = EXCLUDED.state, target_url = EXCLUDED.target_url, description = EXCLUDED.description, updated_at = EXCLUDED.updated_at WHERE EXCLUDED.updated_at >= vcs_commit_status.updated_at ` type UpsertVCSCommitStatusParams struct { ConnectionID pgtype.UUID `json:"connection_id"` Sha string `json:"sha"` Context string `json:"context"` State string `json:"state"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TargetUrl pgtype.Text `json:"target_url"` Description pgtype.Text `json:"description"` } // ===================== // VCS commit status (CI) // ===================== // One row per (connection, sha, context); a redelivery or state transition // overwrites in place. updated_at guards against an older event overwriting a // newer one for the same context. state is normalized (passed/failed/pending). func (q *Queries) UpsertVCSCommitStatus(ctx context.Context, arg UpsertVCSCommitStatusParams) error { _, err := q.db.Exec(ctx, upsertVCSCommitStatus, arg.ConnectionID, arg.Sha, arg.Context, arg.State, arg.UpdatedAt, arg.TargetUrl, arg.Description, ) return err } const upsertVCSConnection = `-- name: UpsertVCSConnection :one INSERT INTO vcs_connection ( workspace_id, provider, instance_url, account_login, access_token_encrypted, webhook_secret_encrypted, connected_by_id ) VALUES ( $1, $2, $3, $4, $5, $6, $7 ) ON CONFLICT (workspace_id, instance_url) DO UPDATE SET provider = EXCLUDED.provider, account_login = EXCLUDED.account_login, access_token_encrypted = EXCLUDED.access_token_encrypted, webhook_secret_encrypted = EXCLUDED.webhook_secret_encrypted, connected_by_id = EXCLUDED.connected_by_id, updated_at = now() RETURNING id, workspace_id, provider, instance_url, account_login, access_token_encrypted, webhook_secret_encrypted, connected_by_id, created_at, updated_at ` type UpsertVCSConnectionParams struct { WorkspaceID pgtype.UUID `json:"workspace_id"` Provider string `json:"provider"` InstanceUrl string `json:"instance_url"` AccountLogin string `json:"account_login"` AccessTokenEncrypted string `json:"access_token_encrypted"` WebhookSecretEncrypted string `json:"webhook_secret_encrypted"` ConnectedByID pgtype.UUID `json:"connected_by_id"` } // Reconnecting the same instance rotates the stored token/secret, provider, // and identity in place rather than creating a duplicate row. func (q *Queries) UpsertVCSConnection(ctx context.Context, arg UpsertVCSConnectionParams) (VcsConnection, error) { row := q.db.QueryRow(ctx, upsertVCSConnection, arg.WorkspaceID, arg.Provider, arg.InstanceUrl, arg.AccountLogin, arg.AccessTokenEncrypted, arg.WebhookSecretEncrypted, arg.ConnectedByID, ) var i VcsConnection err := row.Scan( &i.ID, &i.WorkspaceID, &i.Provider, &i.InstanceUrl, &i.AccountLogin, &i.AccessTokenEncrypted, &i.WebhookSecretEncrypted, &i.ConnectedByID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const upsertVCSPullRequest = `-- name: UpsertVCSPullRequest :one INSERT INTO vcs_pull_request ( workspace_id, connection_id, provider, repo_owner, repo_name, pr_number, title, state, html_url, branch, author_login, author_avatar_url, merged_at, closed_at, pr_created_at, pr_updated_at, additions, deletions, changed_files, head_sha ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $16, $17, $18, $19, $20, $10, $11, $12, $13, $14, $15 ) ON CONFLICT (connection_id, repo_owner, repo_name, pr_number) DO UPDATE SET workspace_id = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.workspace_id ELSE vcs_pull_request.workspace_id END, provider = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.provider ELSE vcs_pull_request.provider END, title = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.title ELSE vcs_pull_request.title END, state = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.state ELSE vcs_pull_request.state END, html_url = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.html_url ELSE vcs_pull_request.html_url END, branch = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.branch ELSE vcs_pull_request.branch END, author_login = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.author_login ELSE vcs_pull_request.author_login END, author_avatar_url = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.author_avatar_url ELSE vcs_pull_request.author_avatar_url END, merged_at = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.merged_at ELSE vcs_pull_request.merged_at END, closed_at = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.closed_at ELSE vcs_pull_request.closed_at END, pr_updated_at = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.pr_updated_at ELSE vcs_pull_request.pr_updated_at END, additions = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.additions ELSE vcs_pull_request.additions END, deletions = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.deletions ELSE vcs_pull_request.deletions END, changed_files = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.changed_files ELSE vcs_pull_request.changed_files END, head_sha = CASE WHEN EXCLUDED.pr_updated_at >= vcs_pull_request.pr_updated_at THEN EXCLUDED.head_sha ELSE vcs_pull_request.head_sha END, updated_at = now() RETURNING id, workspace_id, connection_id, provider, repo_owner, repo_name, pr_number, title, state, html_url, branch, head_sha, author_login, author_avatar_url, merged_at, closed_at, pr_created_at, pr_updated_at, additions, deletions, changed_files, created_at, updated_at ` type UpsertVCSPullRequestParams struct { WorkspaceID pgtype.UUID `json:"workspace_id"` ConnectionID pgtype.UUID `json:"connection_id"` Provider string `json:"provider"` RepoOwner string `json:"repo_owner"` RepoName string `json:"repo_name"` PrNumber int32 `json:"pr_number"` Title string `json:"title"` State string `json:"state"` HtmlUrl string `json:"html_url"` PrCreatedAt pgtype.Timestamptz `json:"pr_created_at"` PrUpdatedAt pgtype.Timestamptz `json:"pr_updated_at"` Additions int32 `json:"additions"` Deletions int32 `json:"deletions"` ChangedFiles int32 `json:"changed_files"` HeadSha string `json:"head_sha"` Branch pgtype.Text `json:"branch"` AuthorLogin pgtype.Text `json:"author_login"` AuthorAvatarUrl pgtype.Text `json:"author_avatar_url"` MergedAt pgtype.Timestamptz `json:"merged_at"` ClosedAt pgtype.Timestamptz `json:"closed_at"` } // ===================== // VCS Pull Request // ===================== // pr_updated_at guards against an out-of-order webhook redelivery regressing // the PR state, mirroring the updated_at guard on UpsertVCSCommitStatus. Each // mutable column is applied only when the incoming event is at least as new as // the stored row; a stale event keeps the existing values. The row is still // touched (and thus RETURNED) either way, so callers always get the current PR // — the webhook needs pr.id to link the issue even on a stale redelivery. func (q *Queries) UpsertVCSPullRequest(ctx context.Context, arg UpsertVCSPullRequestParams) (VcsPullRequest, error) { row := q.db.QueryRow(ctx, upsertVCSPullRequest, arg.WorkspaceID, arg.ConnectionID, arg.Provider, arg.RepoOwner, arg.RepoName, arg.PrNumber, arg.Title, arg.State, arg.HtmlUrl, arg.PrCreatedAt, arg.PrUpdatedAt, arg.Additions, arg.Deletions, arg.ChangedFiles, arg.HeadSha, arg.Branch, arg.AuthorLogin, arg.AuthorAvatarUrl, arg.MergedAt, arg.ClosedAt, ) var i VcsPullRequest err := row.Scan( &i.ID, &i.WorkspaceID, &i.ConnectionID, &i.Provider, &i.RepoOwner, &i.RepoName, &i.PrNumber, &i.Title, &i.State, &i.HtmlUrl, &i.Branch, &i.HeadSha, &i.AuthorLogin, &i.AuthorAvatarUrl, &i.MergedAt, &i.ClosedAt, &i.PrCreatedAt, &i.PrUpdatedAt, &i.Additions, &i.Deletions, &i.ChangedFiles, &i.CreatedAt, &i.UpdatedAt, ) return i, err }