e8d1fff44e0f7ecd4629cfcc2ac671d6af78d8ae
hiram
  Thu Apr 30 12:54:54 2026 -0700
add an ottoRequest table interactive CGI viewer and verify lift.over URL links for email are valid refs #31811

diff --git src/hg/utils/otto/userRequests/ottoRequestWatch.sh src/hg/utils/otto/userRequests/ottoRequestWatch.sh
index 8918c1c3835..49a80162f75 100755
--- src/hg/utils/otto/userRequests/ottoRequestWatch.sh
+++ src/hg/utils/otto/userRequests/ottoRequestWatch.sh
@@ -24,48 +24,55 @@
 ### errors - set error status in the table
 function setErrorStatus() {
   id="${1}"
   hgsql -N -e \
       "UPDATE ottoRequest SET status=7 WHERE id=${id};" hgcentraltest
 }
 ##############################################################################
 
 ##############################################################################
 ### liftOverUrl - build the public download URL for an over.chain.gz file
 ###   args: srcDb dstDb
 ###   GenArk:      https://hgdownload.soe.ucsc.edu/hubs/<3>/<3>/<3>/<3>/<acc>/liftOver/<srcDb>To<DstDb>.over.chain.gz
 ###   UCSC native: https://hgdownload.soe.ucsc.edu/goldenPath/<srcDb>/liftOver/<srcDb>To<DstDb>.over.chain.gz
 ###   DstDb is dstDb with the first letter upper-cased (matches the
 ###   filename convention used in installLinks()).
+###   verifies the URL with a curl HEAD before printing; returns 1 if
+###   the URL does not resolve to a 2xx response.
 ##############################################################################
 function liftOverUrl() {
   local srcDb="${1}"
   local dstDb="${2}"
   local DstDb="${dstDb^}"
   local fileName="${srcDb}To${DstDb}.over.chain.gz"
+  local url
   if [[ "${srcDb}" == GC* ]]; then
     local gcX="${srcDb:0:3}"
     local d0="${srcDb:4:3}"
     local d1="${srcDb:7:3}"
     local d2="${srcDb:10:3}"
     local accPath="${gcX}/${d0}/${d1}/${d2}/${srcDb}"
-    printf "https://hgdownload.soe.ucsc.edu/hubs/%s/liftOver/%s" \
-      "${accPath}" "${fileName}"
+    url="https://hgdownload.soe.ucsc.edu/hubs/${accPath}/liftOver/${fileName}"
   else
-    printf "https://hgdownload.soe.ucsc.edu/goldenPath/%s/liftOver/%s" \
-      "${srcDb}" "${fileName}"
+    url="https://hgdownload.soe.ucsc.edu/goldenPath/${srcDb}/liftOver/${fileName}"
   fi
+  # -s silent, -f fail on HTTP >= 400, -I HEAD, --max-time bounds hangs
+  if ! curl -sfI --max-time 30 -o /dev/null "${url}"; then
+    printf "ERROR: liftOverUrl: URL does not exist: %s\n" "${url}" 1>&2
+    return 1
+  fi
+  printf "%s" "${url}"
 }
 ##############################################################################
 
 ##############################################################################
 ### sendNotification - email the requesting user that their alignment is done
 ###   args: reqId subject
 ###   message body is read from stdin
 ###   recipient: email column of ottoRequest table for that reqId
 ###   bcc: chain-file-request-group@ucsc.edu
 ###   envelope sender / Return-Path / bounce: genome-www@soe.ucsc.edu
 ###   returns 0 on success, non-zero on failure
 ##############################################################################
 function sendNotification() {
   local reqId="${1}"
   local subject="${2}"
@@ -314,32 +321,38 @@
 #          clean up galaxy workflow
 ############################################################################
 
 while IFS=$'\t' read -r reqId fromDb toDb buildDir; do
   # time to clean up the galaxy history and workflow to release the space
   if [ -s "${buildDir}/successInvocationId.txt" ]; then
     invocationId=$(cut -f2 "${buildDir}/successInvocationId.txt")
     profileJson="${HOME}/.planemo/profiles/vgp/planemo_profile_options.json"
     if "${scriptDir}/galaxyCleanup.py" "${profileJson}" "${invocationId}"; then
       printf "# galaxy cleanup complete for request %s\n" "${reqId}" 1>&2
     else
       printf "# WARNING: galaxy cleanup failed for request %s\n" "${reqId}" 1>&2
     fi
   fi
 
-  fromUrl="$(liftOverUrl "${fromDb}" "${toDb}")"
-  toUrl="$(liftOverUrl "${toDb}" "${fromDb}")"
+  if ! fromUrl="$(liftOverUrl "${fromDb}" "${toDb}")"; then
+    setErrorStatus "${reqId}"
+    continue
+  fi
+  if ! toUrl="$(liftOverUrl "${toDb}" "${fromDb}")"; then
+    setErrorStatus "${reqId}"
+    continue
+  fi
   sendNotification "${reqId}" \
 "from UCSC: liftOverRequest complete: ${fromDb}<->${toDb}" \
 "Your lift over request is complete.  You can access the lift.over files at:
 
   ${fromUrl}
   ${toUrl}
 "
 
   hgsql -N -e \
-      "UPDATE ottoRequest SET status=8 WHERE id=${reqId};" hgcentraltest
+      "UPDATE ottoRequest SET status=8, completeTime=now() WHERE id=${reqId};" hgcentraltest
 
 done < <(hgsql -N -B -e \
   "SELECT id, fromDb, toDb, buildDir FROM ottoRequest \
    WHERE status = 6 AND requestType = 'liftOver';" hgcentraltest)