e07b87b45f9293c3f45337303886450c390b2585
hiram
  Tue May 12 13:10:56 2026 -0700
protect against duplicated pending entries in the ottoRequest table refs #31811

diff --git src/hg/hubApi/liftOver.c src/hg/hubApi/liftOver.c
index 04edc9e3aad..a66498470b6 100644
--- src/hg/hubApi/liftOver.c
+++ src/hg/hubApi/liftOver.c
@@ -122,33 +122,61 @@
     {
     jsonWriteObjectStart(jw, NULL);
     jsonWriteString(jw, "fromDb", chain->fromDb);
     jsonWriteString(jw, "toDb", chain->toDb);
     jsonWriteString(jw, "path", chain->path);
     jsonWriteDouble(jw, "minMatch", chain->minMatch);
     jsonWriteNumber(jw, "minChainT", chain->minChainT);
     jsonWriteNumber(jw, "minSizeQ", chain->minSizeQ);
     jsonWriteString(jw, "multiple", chain->multiple);
     jsonWriteDouble(jw, "minBlocks", chain->minBlocks);
     jsonWriteString(jw, "fudgeThick", chain->fudgeThick);
     jsonWriteObjectEnd(jw);
     }
 jsonWriteListEnd(jw);
 jsonWriteNumber(jw, "totalLiftOvers", totalRows);
-jsonWriteNumber(jw, "itemsReturned", slCount(chainList));
+int chainListCount = slCount(chainList);
+jsonWriteNumber(jw, "itemsReturned", chainListCount);
 liftOverChainFreeList(&chainList);
 
+/* if no chain rows for this pair, check ottoRequest for any existing
+ * row (any status) so the user is told their pair has already been
+ * submitted instead of being allowed to create a duplicate row */
+if (chainListCount == 0 && isNotEmpty(fromDb) && isNotEmpty(toDb))
+    {
+    char *ottoTable = cfgOption("ottoTable");
+    if (isNotEmpty(ottoTable) && sqlTableExists(conn, ottoTable))
+        {
+        struct dyString *pq = newDyString(0);
+        sqlDyStringPrintf(pq,
+            "SELECT id, status, requestTime FROM %s "
+            "WHERE requestType='liftOver' AND "
+            "((fromDb='%s' AND toDb='%s') OR (fromDb='%s' AND toDb='%s')) "
+            "ORDER BY requestTime DESC LIMIT 1",
+            ottoTable, fromDb, toDb, toDb, fromDb);
+        char **row;
+        struct sqlResult *sr = sqlGetResult(conn, dyStringCannibalize(&pq));
+        if ((row = sqlNextRow(sr)) != NULL)
+            {
+            jsonWriteBoolean(jw, "pending", TRUE);
+            jsonWriteNumber(jw, "pendingStatus", sqlSigned(row[1]));
+            jsonWriteString(jw, "pendingRequestTime", row[2]);
+            }
+        sqlFreeResult(&sr);
+        }
+    }
+
 apiFinishOutput(0, NULL, jw);
 hDisconnectCentral(&conn);
 }
 
 /**** SHOULD BE IN LIBRARY - code from hgConvert.c ******/
 static char *skipWord(char *fw)
 /* skips over current word to start of next.
  * Error for this not to exist. */
 {
 char *s;
 s = skipToSpaces(fw);
 if (s == NULL)
     errAbort("Expecting two words in .ra file line %s\n", fw);
 s = skipLeadingSpaces(s);
 if (s == NULL)
@@ -297,30 +325,59 @@
 char *toGenome = cgiOptionalString(argToGenome);
 char *email = cgiOptionalString(argEmail);
 char *comment = cgiOptionalString(argComment);
 
 /* probably want a silent exit here */
 if (isEmpty(fromGenome) || isEmpty(toGenome) || isEmpty(email) || isEmpty(comment))
     apiErrAbort(err400, err400Msg, "must have all arguments: %s, %s, %s, %s for endpoint '/liftRequest", argFromGenome, argToGenome, argEmail, argComment);
 
 /* Require a session cookie.  Robots that have not
  *   passed the challenge will not have one. */
 char *cookieName = hUserCookie();
 char *userId = findCookieData(cookieName);
 if (isEmpty(userId))
     apiErrAbort(err400, err400Msg, "can not find required inputs for endpoint '/liftRequest");
 
+/* duplicate-row guard: any existing row in ottoRequest for this pair
+ * (either direction, any status) blocks resubmission.  The form's JS
+ * already shows a "pending" panel for this case via the listExisting
+ * endpoint; this is the backstop for clients that bypass the form. */
+{
+char *dupOttoTable = cfgOption("ottoTable");
+if (isNotEmpty(dupOttoTable))
+    {
+    struct sqlConnection *conn = hConnectCentral();
+    if (sqlTableExists(conn, dupOttoTable))
+        {
+        struct dyString *dq = newDyString(0);
+        sqlDyStringPrintf(dq,
+            "SELECT COUNT(*) FROM %s WHERE requestType='liftOver' AND "
+            "((fromDb='%s' AND toDb='%s') OR (fromDb='%s' AND toDb='%s'))",
+            dupOttoTable, fromGenome, toGenome, toGenome, fromGenome);
+        int dupCount = sqlQuickNum(conn, dyStringCannibalize(&dq));
+        hDisconnectCentral(&conn);
+        if (dupCount > 0)
+            apiErrAbort(err409, err409Msg,
+                "A request for %s <-> %s has already been submitted "
+                "and is on record.  Duplicates are not accepted.",
+                fromGenome, toGenome);
+        }
+    else
+        hDisconnectCentral(&conn);
+    }
+}
+
 /* per-email daily rate limit, per requestType, calendar-day server time */
 char *limitStr = cfgOption("liftDailyLimit");
 int dailyLimit = isNotEmpty(limitStr) ? atoi(limitStr) : 0;
 if (dailyLimit > 0)
     {
     char *limitOttoTable = cfgOption("ottoTable");
     if (isNotEmpty(limitOttoTable))
         {
         struct sqlConnection *conn = hConnectCentral();
         if (sqlTableExists(conn, limitOttoTable))
             {
             struct dyString *q = newDyString(0);
             sqlDyStringPrintf(q,
                 "SELECT COUNT(*) FROM %s "
                 "WHERE requestType='liftOver' AND email='%s' "