Function syncAutoAnalysisQueueForFinding

services/security-sync/src/sync.ts:1149–1285

(
  db: WorkerDb,
  params: {
    owner: SecurityReviewOwner;
    findingId: string;
    findingCreatedAt: string;
    previousStatus: SecurityFindingStatus | null;
    currentStatus: SecurityFindingStatus;
    severity: string | null;
    isAgentEnabled: boolean;
    autoAnalysisEnabled: boolean;
    autoAnalysisMinSeverity: AutoAnalysisMinSeverity;
    ownerAutoAnalysisEnabledAt: string | null;
    autoAnalysisIncludeExisting?: boolean;
  }
)

  params: {
    finding: ParsedSecurityFinding;
    owner: SecurityReviewOwner;
    platformIntegrationId: string;
    repoFullName: string;
    slaDueAt: string;
  }
): Promise<UpsertSecurityFindingResult> {
  const { finding, owner, platformIntegrationId, repoFullName, slaDueAt } = params;
  const ownerOrganizationId = isOrgOwner(owner) ? owner.organizationId : null;
  const ownerUserId = isOrgOwner(owner) ? null : owner.userId;

  // Only rewrite an existing finding when the source data actually changed. Re-syncing an
  // unchanged finding otherwise rewrote the row on every run (bumping last_synced_at/
  // updated_at), which — multiplied by ~13 indexes and the TOASTed raw_data column —
  // produced large amounts of WAL for findings that had not changed.
  //
  // Every stored column is derived from the Dependabot alert (see parseDependabotAlert),
  // and GitHub only advances the alert's updated_at on real changes, so comparing the
  // stored raw_data (jsonb, order-independent) detects any source-driven change in one
  // check. sla_due_at is the only value we compute ourselves, so it is compared separately
  // to catch SLA-policy changes. When neither differs the DO UPDATE matches no row and the
  // fallback SELECT below returns the existing finding with wasInserted=false and no
  // status/severity delta, so notifications and audit events behave exactly as they did for
  // an unchanged re-sync.
  const materialChangePredicate = sql`(
        ${security_findings.raw_data} IS DISTINCT FROM EXCLUDED.${sql.identifier(security_findings.raw_data.name)}
        OR ${security_findings.sla_due_at} IS DISTINCT FROM EXCLUDED.${sql.identifier(security_findings.sla_due_at.name)}
      )`;

  const result = await db.execute<Record<string, unknown>>(sql`
    WITH existing_match AS (
      SELECT ${security_findings.id} AS id,
             ${security_findings.status} AS previous_status,
             ${security_findings.severity} AS previous_severity
      FROM ${security_findings}
      WHERE ${security_findings.repo_full_name} = ${repoFullName}
        AND ${security_findings.source} = ${finding.source}
        AND ${security_findings.source_id} = ${finding.source_id}
        AND ${findingOwnerPredicate(owner)}
      FOR UPDATE
    ),
    upserted AS (
      INSERT INTO ${security_findings} (
        ${sql.identifier(security_findings.owned_by_organization_id.name)},
        ${sql.identifier(security_findings.owned_by_user_id.name)},
        ${sql.identifier(security_findings.platform_integration_id.name)},
        ${sql.identifier(security_findings.repo_full_name.name)},
        ${sql.identifier(security_findings.source.name)},
        ${sql.identifier(security_findings.source_id.name)},
        ${sql.identifier(security_findings.severity.name)},
        ${sql.identifier(security_findings.ghsa_id.name)},
        ${sql.identifier(security_findings.cve_id.name)},
        ${sql.identifier(security_findings.package_name.name)},
        ${sql.identifier(security_findings.package_ecosystem.name)},
        ${sql.identifier(security_findings.vulnerable_version_range.name)},
        ${sql.identifier(security_findings.patched_version.name)},
        ${sql.identifier(security_findings.manifest_path.name)},
        ${sql.identifier(security_findings.title.name)},
        ${sql.identifier(security_findings.description.name)},