Skip to content

Modern Docker Management, Designed for Everyone

29.0 KB
Raw Blame
package vulnerability

import (
	"cmp"
	"context"
	"encoding/json/v2"
	"errors"
	"fmt"
	"log/slog"
	"net/http"
	"time"

	"github.com/getarcaneapp/arcane/types/v2"
	activitytypes "github.com/getarcaneapp/arcane/types/v2/activity"
	"github.com/getarcaneapp/arcane/types/v2/features"
	"github.com/getarcaneapp/arcane/types/v2/scheduler"
	usertypes "github.com/getarcaneapp/arcane/types/v2/user"
	"github.com/getarcaneapp/arcane/types/v2/vulnerability"
	"github.com/italypaleale/francis/builtin/workflow"
	"github.com/samber/mo"
	kit "go.getarcane.app/kit/pkg"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"

	"github.com/getarcaneapp/arcane/backend/v2/internal/activity"
	"github.com/getarcaneapp/arcane/backend/v2/internal/common"
	"github.com/getarcaneapp/arcane/backend/v2/internal/config"
	"github.com/getarcaneapp/arcane/backend/v2/internal/database"
	"github.com/getarcaneapp/arcane/backend/v2/internal/docker"
	"github.com/getarcaneapp/arcane/backend/v2/internal/event"
	"github.com/getarcaneapp/arcane/backend/v2/internal/kv"
	"github.com/getarcaneapp/arcane/backend/v2/internal/notification"
	"github.com/getarcaneapp/arcane/backend/v2/internal/registry"
	"github.com/getarcaneapp/arcane/backend/v2/internal/settings"
	"github.com/getarcaneapp/arcane/backend/v2/internal/vulnerability/children/ignores"
	"github.com/getarcaneapp/arcane/backend/v2/internal/vulnerability/children/query"
	"github.com/getarcaneapp/arcane/backend/v2/internal/vulnerability/children/scan"
	"github.com/getarcaneapp/arcane/backend/v2/internal/vulnerability/children/store"
	activitylib "github.com/getarcaneapp/arcane/backend/v2/pkg/libarcane/activity"
	"github.com/getarcaneapp/arcane/backend/v2/pkg/libarcane/vuln"
	"github.com/getarcaneapp/arcane/backend/v2/pkg/pagination"
	"github.com/getarcaneapp/arcane/backend/v2/pkg/scheduler/flow"
	"github.com/getarcaneapp/arcane/backend/v2/pkg/utils/dbutil"
)

// ignoreIdentityQuery matches the ignore of one finding in one image and environment.
const ignoreIdentityQuery = "environment_id = ? AND image_id = ? AND vulnerability_id = ? AND pkg_name = ? AND installed_version = ?"

// scanParallelism caps concurrent scan tasks; Trivy slots apply the configured limit below it.
const scanParallelism = 16

// scanSlot is one scan task's output; failures travel as task errors.
type scanSlot struct {
	Skipped bool         `json:"skipped,omitempty"`
	Fixable scan.Fixable `json:"fixable"`
}

// VulnerabilityService coordinates vulnerability scanning, findings, ignores,
// and risk, and owns the scan record and raw report persistence.
type VulnerabilityService struct {
	db              *database.DB
	settingsService *settings.SettingsService

	scan  *scan.Service
	query *query.Service
	// scanWorkflow runs scheduled scans; see RegisterWorkflows.
	scanWorkflow *flow.Workflow
	store        *store.Service
	ignores      *ignores.Service
}

// NewVulnerabilityService creates a new VulnerabilityService instance
func NewVulnerabilityService(
	db *database.DB,
	cfg *config.Config,
	dockerService *docker.DockerClientService,
	eventService *event.EventService,
	settingsService *settings.SettingsService,
	notificationService *notification.NotificationService,
	activityService *activity.ActivityService,
	registryService *registry.ContainerRegistryService,
	kvService *kv.KVService,
	httpClient *http.Client,
) *VulnerabilityService {
	var s *VulnerabilityService
	s = &VulnerabilityService{
		db:              db,
		settingsService: settingsService,
		store: store.NewService(db, store.Records{
			Save: func(tx *gorm.DB, result *vulnerability.ScanResult, fixable int) error {
				return s.saveRecord(tx, result, fixable)
			},
			SaveStatus: func(ctx context.Context, result *vulnerability.ScanResult, summary *vulnerability.SeveritySummary, errMessage string) error {
				updates := map[string]any{
					"image_name":      result.ImageName,
					"status":          string(result.Status),
					"scan_time":       result.ScanTime,
					"duration":        result.Duration,
					"critical_count":  summary.Critical,
					"high_count":      summary.High,
					"medium_count":    summary.Medium,
					"low_count":       summary.Low,
					"unknown_count":   summary.Unknown,
					"total_count":     summary.Total,
					"scanner_version": result.ScannerVersion,
					"error":           nil,
				}
				if errMessage != "" {
					updates["error"] = errMessage
				}
				return s.db.WithContext(ctx).Model(&VulnerabilityScanRecord{}).Where("id = ?", result.ImageID).Updates(updates).Error
			},
			Delete: func(ctx context.Context, imageIDs []string) error {
				if err := s.db.WithContext(ctx).Where("id IN ?", imageIDs).Delete(&VulnerabilityScanRecord{}).Error; err != nil {
					return err
				}
				s.query.InvalidateRiskOverview()
				return nil
			},
			DeleteReports: func(ctx context.Context, imageIDs []string) error {
				return s.db.WithContext(ctx).Where("image_id IN ?", imageIDs).Delete(&VulnerabilityReportRecord{}).Error
			},
			DeleteIgnores: func(ctx context.Context, imageIDs []string) error {
				result := s.db.WithContext(ctx).Where("environment_id = ? AND image_id IN ?", types.LocalDockerEnvironmentID, imageIDs).Delete(&VulnerabilityIgnore{})
				if result.Error == nil && result.RowsAffected > 0 {
					s.query.InvalidateRiskOverview()
				}
				return result.Error
			},
			IDsExcept: func(ctx context.Context, keepIDs []string) ([]string, error) {
				scope := s.db.WithContext(ctx).Model(&VulnerabilityScanRecord{})
				if len(keepIDs) > 0 {
					scope = scope.Where("id NOT IN ?", keepIDs)
				}
				var ids []string
				err := scope.Pluck("id", &ids).Error
				return ids, err
			},
			DeleteExcept: func(ctx context.Context, keepIDs []string) (int64, error) {
				scope := s.db.WithContext(ctx).Where("1 = 1")
				if len(keepIDs) > 0 {
					scope = s.db.WithContext(ctx).Where("id NOT IN ?", keepIDs)
				}
				result := scope.Delete(&VulnerabilityScanRecord{})
				if result.Error != nil {
					return 0, result.Error
				}
				return result.RowsAffected, nil
			},
			EachMissingItems: func(ctx context.Context, batchSize int, fn func(imageID, vulnerabilitiesJSON string)) error {
				return s.eachRecordMissingItems(ctx, batchSize, fn)
			},
			FixableCounts: func(ctx context.Context) (map[string]*int, error) {
				var scans []VulnerabilityScanRecord
				if err := s.db.WithContext(ctx).Select("id", "fixable_count").Find(&scans).Error; err != nil {
					return nil, err
				}
				counts := make(map[string]*int, len(scans))
				for i := range scans {
					counts[scans[i].ID] = scans[i].FixableCount
				}
				return counts, nil
			},
			SaveReport: func(ctx context.Context, imageID, data string) error {
				return s.saveReport(ctx, imageID, data)
			},
			SetFixableCount: func(ctx context.Context, imageID string, count int) error {
				return s.db.WithContext(ctx).Model(&VulnerabilityScanRecord{}).Where("id = ?", imageID).Update("fixable_count", count).Error
			},
			DeleteItems: func(tx *gorm.DB, imageIDs []string) error {
				return tx.Where("image_id IN ?", imageIDs).Delete(&VulnerabilityScanItem{}).Error
			},
			SaveItems: saveItems,
			CountKnownExploited: func(ctx context.Context) (int64, error) {
				var count int64
				err := s.db.WithContext(ctx).Model(&VulnerabilityThreatIntel{}).Where("known_exploited = ?", true).Count(&count).Error
				return count, err
			},
			ReplaceKnownExploited: func(ctx context.Context, entries []vulnerability.ThreatIntelEntry, now time.Time) error {
				return s.replaceKnownExploited(ctx, entries, now)
			},
			StaleEPSSCVEs: func(ctx context.Context, checkedBefore time.Time, limit int) ([]string, error) {
				var cveIDs []string
				err := s.db.WithContext(ctx).
					Model(&VulnerabilityScanItem{}).
					Distinct("vulnerability_scan_items.vulnerability_id").
					Joins("LEFT JOIN vulnerability_threat_intel ON vulnerability_threat_intel.cve_id = vulnerability_scan_items.vulnerability_id").
					Where("vulnerability_scan_items.vulnerability_id LIKE ?", "CVE-%").
					Where("vulnerability_threat_intel.epss_checked_at IS NULL OR vulnerability_threat_intel.epss_checked_at < ?", checkedBefore).
					Limit(limit).
					Pluck("vulnerability_scan_items.vulnerability_id", &cveIDs).Error
				return cveIDs, err
			},
			SaveEPSS: func(ctx context.Context, entries []vulnerability.ThreatIntelEntry) error {
				batch := make([]VulnerabilityThreatIntel, 0, len(entries))
				for i := range entries {
					batch = append(batch, VulnerabilityThreatIntel(entries[i]))
				}
				return s.db.WithContext(ctx).Clauses(clause.OnConflict{
					Columns:   []clause.Column{{Name: "cve_id"}},
					DoUpdates: clause.AssignmentColumns([]string{"epss_score", "epss_percentile", "epss_checked_at", "updated_at"}),
				}).Create(&batch).Error
			},
			PruneThreatIntel: func(ctx context.Context) error {
				return s.db.WithContext(ctx).
					Where("known_exploited = ? AND NOT EXISTS (SELECT 1 FROM vulnerability_scan_items WHERE vulnerability_scan_items.vulnerability_id = vulnerability_threat_intel.cve_id)", false).
					Delete(&VulnerabilityThreatIntel{}).Error
			},
		}, dockerService, settingsService, kvService, httpClient),
		query: query.NewService(
			db,
			cfg,
			dockerService,
			settingsService,
			func(ctx context.Context, imageID string) (*vulnerability.ScanResult, error) {
				return s.loadResult(ctx, imageID)
			},
			func(ctx context.Context, summary *vulnerability.ScanSummary) { s.scan.RefreshSummary(ctx, summary) },
			func(ctx context.Context) vulnerability.ThreatIntelStatus { return s.store.ThreatIntelStatus(ctx) },
			func(ctx context.Context, envID, imageID string, vulns []vulnerability.Vulnerability) ([]vulnerability.Vulnerability, error) {
				return s.ignores.FilterForImage(ctx, envID, imageID, vulns)
			},
		),
		ignores: ignores.NewService(db, ignores.Records{
			ForImage: func(ctx context.Context, envID, imageID string) ([]vulnerability.IgnoredVulnerability, error) {
				var ignoreRecords []VulnerabilityIgnore
				if err := s.db.WithContext(ctx).Where("environment_id = ? AND image_id = ?", envID, imageID).Find(&ignoreRecords).Error; err != nil {
					return nil, err
				}
				return ignoredVulnerabilities(ignoreRecords), nil
			},
			Exists: func(ctx context.Context, ignore *vulnerability.IgnoredVulnerability) (bool, error) {
				var existing VulnerabilityIgnore
				err := s.db.WithContext(ctx).Where(
					ignoreIdentityQuery,
					ignore.EnvironmentID, ignore.ImageID, ignore.VulnerabilityID, ignore.PkgName, ignore.InstalledVersion,
				).First(&existing).Error
				if errors.Is(err, gorm.ErrRecordNotFound) {
					return false, nil
				}
				return err == nil, err
			},
			Get: func(ctx context.Context, envID, ignoreID string) (*vulnerability.IgnoredVulnerability, bool, error) {
				return s.getIgnore(ctx, envID, ignoreID)
			},
			Create: func(tx *gorm.DB, entries []vulnerability.IgnoredVulnerability, batchSize int) ([]vulnerability.IgnoredVulnerability, error) {
				records := make([]VulnerabilityIgnore, len(entries))
				for i := range entries {
					records[i] = VulnerabilityIgnore(entries[i])
				}
				if err := tx.CreateInBatches(&records, batchSize).Error; err != nil {
					return nil, err
				}
				return ignoredVulnerabilities(records), nil
			},
			DeleteIdentity: func(ctx context.Context, ignore *vulnerability.IgnoredVulnerability) error {
				return s.db.WithContext(ctx).Where(
					ignoreIdentityQuery,
					ignore.EnvironmentID, ignore.ImageID, ignore.VulnerabilityID, ignore.PkgName, ignore.InstalledVersion,
				).Delete(&VulnerabilityIgnore{}).Error
			},
			DeleteIDs: func(tx *gorm.DB, envID string, ignoreIDs []string) (int64, error) {
				result := tx.Where("environment_id = ? AND id IN ?", envID, ignoreIDs).Delete(&VulnerabilityIgnore{})
				return result.RowsAffected, result.Error
			},
			Page: func(ctx context.Context, envID string, order func(*gorm.DB) *gorm.DB, offset, limit int) ([]vulnerability.IgnoredVulnerability, int64, error) {
				return s.ignorePage(ctx, envID, order, offset, limit)
			},
			Changed: func() { s.query.InvalidateRiskOverview() },
		}, func(ctx context.Context, db *gorm.DB, envID string, params *pagination.QueryParams) *gorm.DB {
			return s.query.ActiveItemsQuery(ctx, db, envID, params)
		}),
		scan: scan.NewService(scan.Dependencies{
			DB: db,
			Records: scan.Records{
				MarkStatus: func(ctx context.Context, imageID string, status vulnerability.ScanStatus) (bool, error) {
					result := s.db.WithContext(ctx).Model(&VulnerabilityScanRecord{}).Where("id = ?", imageID).Updates(map[string]any{
						"status":    string(status),
						"scan_time": time.Now(),
						"error":     nil,
					})
					return result.RowsAffected > 0, result.Error
				},
				MarkFailed: func(ctx context.Context, imageID, message string, duration int64) (bool, error) {
					result := s.db.WithContext(ctx).Model(&VulnerabilityScanRecord{}).Where("id = ?", imageID).Updates(map[string]any{
						"status":   ScanStatusFailed,
						"error":    message,
						"duration": duration,
					})
					return result.RowsAffected > 0, result.Error
				},
				SetImageName: func(ctx context.Context, imageID, imageName string) error {
					return s.db.WithContext(ctx).Model(&VulnerabilityScanRecord{}).Where("id = ?", imageID).Update("image_name", imageName).Error
				},
			},
			Config:              cfg,
			KV:                  kvService,
			RegistryAuthConfigs: registryService.GetAllRegistryAuthConfigs,
			SaveReport: func(ctx context.Context, imageID, data string) error {
				return s.saveReport(ctx, imageID, data)
			},
			Docker:        dockerService,
			Settings:      settingsService,
			Events:        eventService,
			Notifications: notificationService,
			Activity:      activityService,
			// The risk cache is cleared only once the scan's transaction has committed.
			SaveResult: func(ctx context.Context, result *vulnerability.ScanResult) error {
				if err := s.store.SaveResult(ctx, result); err != nil {
					return err
				}
				s.query.InvalidateRiskOverview()
				return nil
			},
			SaveScheduledResult: func(ctx context.Context, result *vulnerability.ScanResult) error {
				return s.store.SaveResult(ctx, result)
			},
			EnsureSummary: store.EnsureSummary,
			FilterIgnored: func(ctx context.Context, envID, imageID string, vulns []vulnerability.Vulnerability) ([]vulnerability.Vulnerability, error) {
				return s.ignores.FilterForImage(ctx, envID, imageID, vulns)
			},
		}),
	}
	return s
}

// ScanImage queues a vulnerability scan of an image.
func (s *VulnerabilityService) ScanImage(ctx context.Context, envID, imageID string, user usertypes.Actor) (*vulnerability.ScanResult, error) {
	return s.scan.ScanImage(ctx, envID, imageID, user)
}

// RegisterWorkflows defines the scheduled scan: discover due images, scan each
// as its own retryable task, then notify. Call it before the host starts.
func (s *VulnerabilityService) RegisterWorkflows(engine *flow.Engine) error {
	scanWorkflow, err := engine.Define(flow.Definition{
		Name:        "vulnerability-scan",
		Version:     1,
		Fingerprint: "d221e3e86aded6a3afaed48abc267783425ff861c89accd834abf14148a754d3",
		Concurrency: scanParallelism,
		// Batches of large images can take hours; Trivy bounds each scan itself.
		Timeout: 12 * time.Hour,
		Activity: activitylib.StartRequest{
			Type:          activitytypes.TypeVulnerabilityScan,
			ResourceType:  new("images"),
			ResourceName:  new("Scheduled vulnerability scan"),
			Step:          "Discovering images",
			LatestMessage: "Scheduled vulnerability scan started",
		},
		Labels: map[string]string{
			"discover": "Discovering images",
			"scan":     "Scanning images",
			"finalize": "Finishing vulnerability scan",
		},
		Steps: []workflow.StepSpec{
			workflow.Step("discover", engine.Handler(func(ctx context.Context, _ flow.Task) (any, error) {
				if !s.settingsService.IsFeatureEnabled(ctx, features.VulnerabilityManagement) {
					return []scan.Target{}, nil
				}
				if deleted, err := s.CleanupOrphanedScanRecords(ctx); err != nil {
					slog.WarnContext(ctx, "orphaned vulnerability scan cleanup failed", "error", err)
				} else if deleted > 0 {
					slog.InfoContext(ctx, "cleaned up orphaned vulnerability scan records", "deleted", deleted)
				}
				// A feature disabled since the check above fails this attempt; the retry returns no targets.
				targets, err := s.scan.Targets(ctx)
				if err != nil {
					return nil, common.Classify(common.ErrUnavailable, err)
				}
				return targets, nil
			})),
			// Slots and the shared container pool bound real parallelism to the configured limit.
			workflow.ForEach("scan", engine.Handler(func(ctx context.Context, t flow.Task) (any, error) {
				var target scan.Target
				if err := t.DecodeItem(&target); err != nil {
					return nil, err
				}
				fixable, err := s.scan.ScanTarget(ctx, types.LocalDockerEnvironmentID, target)
				if errors.Is(err, common.ErrFeatureDisabled) {
					return scanSlot{Skipped: true}, nil
				}
				if err != nil {
					return nil, err
				}
				return scanSlot{Fixable: fixable}, nil
			}),
				workflow.WithItemsFrom("discover"),
				workflow.WithMaxParallel(scanParallelism),
				workflow.WithFailurePolicy(workflow.TolerateFailures),
				workflow.WithMaxAttempts(3),
				workflow.WithRetryBackoff(30*time.Second, 2*time.Minute)),
			workflow.Step("finalize", engine.Handler(func(ctx context.Context, t flow.Task) (any, error) {
				s.scan.ReleaseScanPool()
				s.query.InvalidateRiskOverview()
				var targets []scan.Target
				if err := t.DecodeOutput("discover", &targets); err != nil {
					return nil, err
				}
				slots, err := flow.Results[scanSlot](t, "scan")
				if err != nil {
					return nil, err
				}
				scanned, skipped := 0, 0
				var failed []scheduler.TargetOutcome
				fixables := make([]scan.Fixable, 0, len(slots))
				for index, slot := range slots {
					switch {
					case slot.Err != "":
						failed = append(failed, scheduler.TargetOutcome{
							ResourceType: "image",
							ID:           targets[index].ImageName,
							Status:       scheduler.Failed,
							Message:      slot.Err,
						})
					case slot.Value.Skipped:
						skipped++
					default:
						scanned++
						fixables = append(fixables, slot.Value.Fixable)
					}
				}
				if skipped == 0 {
					s.scan.NotifySummary(ctx, scanned, fixables)
				}
				slog.InfoContext(ctx, "scheduled vulnerability scan completed", "scanned", scanned, "failed", len(failed), "skipped", skipped)
				if skipped > 0 || (len(targets) == 0 && !s.settingsService.IsFeatureEnabled(ctx, features.VulnerabilityManagement)) {
					return scheduler.Outcome{
						Status:  kit.Ternary(scanned > 0, scheduler.Partial, scheduler.Skipped),
						Message: fmt.Sprintf("Remaining scans stopped because feature %s was disabled", features.VulnerabilityManagement),
						Targets: failed,
					}, nil
				}
				return scheduler.Outcome{
					Status:  kit.Ternary(len(failed) > 0, scheduler.Partial, scheduler.Succeeded),
					Message: fmt.Sprintf("Vulnerability scan completed: %d scanned, %d failed", scanned, len(failed)),
					Targets: failed,
				}, nil
			}), workflow.WithInputFrom("discover")),
		},
	})
	if err != nil {
		return err
	}
	s.scanWorkflow = scanWorkflow
	return nil
}

// ScanWorkflow is the scheduled vulnerability scan workflow.
func (s *VulnerabilityService) ScanWorkflow() *flow.Workflow { return s.scanWorkflow }

// GetScanSummariesByImageIDs retrieves scan summaries for multiple images.
func (s *VulnerabilityService) GetScanSummariesByImageIDs(ctx context.Context, imageIDs []string) (map[string]*vulnerability.ScanSummary, error) {
	return s.query.GetScanSummariesByImageIDs(ctx, imageIDs)
}

// ActionableCountExcludingIgnored counts critical and high findings that are not ignored.
func (s *VulnerabilityService) ActionableCountExcludingIgnored(ctx context.Context) (int, error) {
	return s.query.ActionableCountExcludingIgnored(ctx)
}

// RecordRiskSnapshot stores today's environment risk snapshot.
func (s *VulnerabilityService) RecordRiskSnapshot(ctx context.Context) error {
	return s.query.RecordRiskSnapshot(ctx)
}

// SyncThreatIntel refreshes the KEV and EPSS threat intelligence cache.
func (s *VulnerabilityService) SyncThreatIntel(ctx context.Context, force bool) error {
	return s.store.SyncThreatIntel(ctx, force)
}

// DeleteScanResultsByImageIDs deletes scan results for multiple images.
func (s *VulnerabilityService) DeleteScanResultsByImageIDs(ctx context.Context, imageIDs []string) error {
	return s.store.DeleteScanResultsByImageIDs(ctx, imageIDs)
}

// CleanupOrphanedScanRecords removes scan records of images that no longer exist.
func (s *VulnerabilityService) CleanupOrphanedScanRecords(ctx context.Context) (int64, error) {
	return s.store.CleanupOrphanedScanRecords(ctx)
}

// BackfillScanItems populates finding rows for scans recorded before they existed.
func (s *VulnerabilityService) BackfillScanItems(ctx context.Context) {
	s.store.BackfillScanItems(ctx)
}

// ImportLegacyReportFiles moves raw Trivy report files of older versions into the database.
func (s *VulnerabilityService) ImportLegacyReportFiles(ctx context.Context) {
	s.store.ImportLegacyReportFiles(ctx)
}

// loadResult reads the stored scan result for an image without insights.
func (s *VulnerabilityService) loadResult(ctx context.Context, imageID string) (*vulnerability.ScanResult, error) {
	var record VulnerabilityScanRecord
	err := s.db.WithContext(ctx).Where("id = ?", imageID).First(&record).Error
	if err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, nil
		}
		return nil, fmt.Errorf("failed to get scan result: %w", err)
	}

	vulns := func() []vulnerability.Vulnerability {
		if len(record.Vulnerabilities) == 0 || record.Vulnerabilities[0] == "" {
			return nil
		}
		var decoded []vulnerability.Vulnerability
		if decodeErr := json.Unmarshal([]byte(record.Vulnerabilities[0]), &decoded); decodeErr != nil {
			slog.WarnContext(ctx, "failed to unmarshal vulnerabilities", "error", decodeErr)
			return nil
		}
		return decoded
	}()
	var reportCount int64
	countReportsErr := s.db.WithContext(ctx).Model(&VulnerabilityReportRecord{}).Where("image_id = ?", record.ID).Count(&reportCount).Error
	result := &vulnerability.ScanResult{
		ImageID:   record.ID,
		ImageName: record.ImageName,
		ScanTime:  record.ScanTime,
		Status:    vulnerability.ScanStatus(record.Status),
		Summary: store.EnsureSummary(&vulnerability.ScanResult{
			Summary: &vulnerability.SeveritySummary{
				Critical: record.CriticalCount,
				High:     record.HighCount,
				Medium:   record.MediumCount,
				Low:      record.LowCount,
				Unknown:  record.UnknownCount,
				Total:    record.TotalCount,
			},
			Vulnerabilities: vulns,
		}),
		Vulnerabilities: vulns,
		Error:           kit.FromPtr(record.Error),
		Duration:        record.Duration,
		ScannerVersion:  record.ScannerVersion,
		HasReport:       countReportsErr == nil && reportCount > 0,
	}
	s.scan.RefreshResult(ctx, result)
	return result, nil
}

// saveRecord upserts the scan record of result inside tx.
func (s *VulnerabilityService) saveRecord(tx *gorm.DB, result *vulnerability.ScanResult, fixable int) error {
	vulnJSON, err := func() (database.StringSlice, error) {
		if len(result.Vulnerabilities) == 0 {
			return nil, nil
		}
		vulnBytes, marshalErr := json.Marshal(result.Vulnerabilities)
		if marshalErr != nil {
			return nil, fmt.Errorf("failed to marshal vulnerabilities: %w", marshalErr)
		}
		return database.StringSlice{string(vulnBytes)}, nil
	}()
	if err != nil {
		return err
	}

	summary := cmp.Or(result.Summary, &vulnerability.SeveritySummary{})
	return tx.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "id"}},
		UpdateAll: true,
	}).Create(&VulnerabilityScanRecord{
		ID:              result.ImageID,
		ImageName:       result.ImageName,
		Status:          string(result.Status),
		ScanTime:        result.ScanTime,
		Duration:        result.Duration,
		CriticalCount:   summary.Critical,
		HighCount:       summary.High,
		MediumCount:     summary.Medium,
		LowCount:        summary.Low,
		UnknownCount:    summary.Unknown,
		TotalCount:      summary.Total,
		Vulnerabilities: vulnJSON,
		Error:           mo.EmptyableToOption(result.Error).ToPointer(),
		ScannerVersion:  result.ScannerVersion,
		ScanPolicy:      s.scan.Policy(),
		FixableCount:    &fixable,
	}).Error
}

// eachRecordMissingItems visits completed scans with findings but no finding rows.
func (s *VulnerabilityService) eachRecordMissingItems(ctx context.Context, batchSize int, fn func(imageID, vulnerabilitiesJSON string)) error {
	var batch []VulnerabilityScanRecord
	return s.db.WithContext(ctx).
		Select("id", "vulnerabilities").
		Where("status = ?", ScanStatusCompleted).
		Where("total_count > 0").
		Where("NOT EXISTS (SELECT 1 FROM vulnerability_scan_items WHERE vulnerability_scan_items.image_id = vulnerability_scans.id)").
		FindInBatches(&batch, batchSize, func(*gorm.DB, int) error {
			for _, record := range batch {
				if len(record.Vulnerabilities) == 0 || record.Vulnerabilities[0] == "" {
					continue
				}
				fn(record.ID, record.Vulnerabilities[0])
			}
			return nil
		}).Error
}

// saveReport stores the raw Trivy report of an image, replacing any earlier one.
func (s *VulnerabilityService) saveReport(ctx context.Context, imageID, data string) error {
	return s.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "image_id"}},
		UpdateAll: true,
	}).Create(&VulnerabilityReportRecord{ImageID: imageID, Data: data}).Error
}

const (
	scanItemInsertBatchSize = 500
	threatIntelUpsertBatch  = 500
)

func saveItems(tx *gorm.DB, imageID string, vulns []vulnerability.Vulnerability) error {
	items := make([]VulnerabilityScanItem, 0, len(vulns))
	for i := range vulns {
		v := &vulns[i]
		details, err := func() (*string, error) {
			if v.Description == "" && len(v.References) == 0 && v.CVSS == nil && v.PublishedDate == nil && v.LastModifiedDate == nil {
				return nil, nil
			}
			raw, marshalErr := json.Marshal(vulnerabilityScanItemDetails{
				Description:      v.Description,
				References:       v.References,
				CVSS:             v.CVSS,
				PublishedDate:    v.PublishedDate,
				LastModifiedDate: v.LastModifiedDate,
			})
			if marshalErr != nil {
				return nil, fmt.Errorf("failed to marshal vulnerability details: %w", marshalErr)
			}
			return new(string(raw)), nil
		}()
		if err != nil {
			return err
		}
		items = append(items, VulnerabilityScanItem{
			ImageID:          imageID,
			VulnerabilityID:  v.VulnerabilityID,
			PkgName:          v.PkgName,
			InstalledVersion: v.InstalledVersion,
			FixedVersion:     v.FixedVersion,
			Severity:         string(v.Severity),
			SeverityRank:     vuln.SeverityRank(v.Severity),
			PkgClass:         v.Class,
			PkgType:          v.Type,
			Title:            v.Title,
			CVSSScore: func() *float64 {
				if score := vuln.CVSSScore(v.CVSS); score > 0 {
					return &score
				}
				return nil
			}(),
			Details: details,
		})
	}
	if storeItemsErr := tx.CreateInBatches(items, scanItemInsertBatchSize).Error; storeItemsErr != nil {
		return fmt.Errorf("failed to store scan items: %w", storeItemsErr)
	}
	return nil
}

// replaceKnownExploited clears every stored KEV flag and upserts entries in one transaction.
func (s *VulnerabilityService) replaceKnownExploited(ctx context.Context, entries []vulnerability.ThreatIntelEntry, now time.Time) error {
	rows := make([]VulnerabilityThreatIntel, 0, len(entries))
	for i := range entries {
		rows = append(rows, VulnerabilityThreatIntel(entries[i]))
	}
	return dbutil.WithTx(ctx, s.db.DB, func(tx *gorm.DB) error {
		if updateKEVErr := tx.Model(&VulnerabilityThreatIntel{}).Where("known_exploited = ?", true).Updates(map[string]any{
			"known_exploited": false, "kev_date_added": "", "kev_due_date": "", "kev_ransomware": false, "updated_at": now,
		}).Error; updateKEVErr != nil {
			return updateKEVErr
		}
		return tx.Clauses(clause.OnConflict{
			Columns:   []clause.Column{{Name: "cve_id"}},
			DoUpdates: clause.AssignmentColumns([]string{"known_exploited", "kev_date_added", "kev_due_date", "kev_ransomware", "updated_at"}),
		}).CreateInBatches(rows, threatIntelUpsertBatch).Error
	})
}

func (s *VulnerabilityService) getIgnore(ctx context.Context, envID, ignoreID string) (*vulnerability.IgnoredVulnerability, bool, error) {
	var record VulnerabilityIgnore
	if err := s.db.WithContext(ctx).Where("id = ? AND environment_id = ?", ignoreID, envID).First(&record).Error; err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, false, nil
		}
		return nil, false, err
	}
	ignore := vulnerability.IgnoredVulnerability(record)
	return &ignore, true, nil
}

func (s *VulnerabilityService) ignorePage(
	ctx context.Context,
	envID string,
	order func(*gorm.DB) *gorm.DB,
	offset, limit int,
) ([]vulnerability.IgnoredVulnerability, int64, error) {
	scope := order(s.db.WithContext(ctx).Where("environment_id = ?", envID))

	var total int64
	if err := scope.Model(&VulnerabilityIgnore{}).Count(&total).Error; err != nil {
		return nil, 0, fmt.Errorf("failed to count ignored vulnerabilities: %w", err)
	}

	var ignoreRecords []VulnerabilityIgnore
	if err := scope.Offset(offset).Limit(limit).Find(&ignoreRecords).Error; err != nil {
		return nil, 0, fmt.Errorf("failed to list ignored vulnerabilities: %w", err)
	}
	return ignoredVulnerabilities(ignoreRecords), total, nil
}

func ignoredVulnerabilities(records []VulnerabilityIgnore) []vulnerability.IgnoredVulnerability {
	result := make([]vulnerability.IgnoredVulnerability, len(records))
	for i := range records {
		result[i] = vulnerability.IgnoredVulnerability(records[i])
	}
	return result
}