Access requests missing on the File Permission Page since upgrading from 6.0 to 6.2

[PaulBoon]

We have received tickets from users that they can’t find the list of access requests anymore.
They know that their requests were there because they can find them in the notifications.

What steps does it take to reproduce the issue?

• create a request on a restricted file in Dataverse 6.0
  The database record looks like this:

dvndb=> SELECT * FROM fileaccessrequests;
 datafile_id | authenticated_user_id |      creation_time      
-------------+-----------------------+-------------------------
           8 |                     6 | 2024-07-15 14:53:54.757
(1 row)

• Upgrade to 6.2 (possibly to 6.1 will also work)
  The record will then look like this

dvndb=> SELECT * FROM fileaccessrequests;
 datafile_id | authenticated_user_id |      creation_time      | request_state | id | guestbookresponse_id 
-------------+-----------------------+-------------------------+---------------+----+----------------------
           8 |                     6 | 2024-07-15 14:53:54.757 |               |  1 |                     
(1 row)

The request_state is NULL instead of CREATED, which results in not being shown in the list of requests.
The flyway script (V6.0.0.1__9599-guestbook-at-request.sql) has added the ‘request_state’ column, but it then has no state (NULL).

The following SQL should fix this

UPDATE fileaccessrequests SET request_state='CREATED' WHERE request_state IS NULL;

However, when users have re-issued the requests because they were not handled, the above won't work:

ERROR:  duplicate key value violates unique constraint "created_requests"

To detect those cases use the following query:

SELECT * 
FROM fileaccessrequests 
WHERE (datafile_id, authenticated_user_id) IN (
  SELECT datafile_id, authenticated_user_id
  FROM fileaccessrequests
  GROUP BY datafile_id, authenticated_user_id
  HAVING COUNT(*) > 1
) ORDER BY datafile_id, authenticated_user_id;

And then as a solution remove that old requests using this query:

DELETE FROM fileaccessrequests 
WHERE id IN (
  SELECT id 
  FROM fileaccessrequests 
  WHERE (datafile_id, authenticated_user_id) IN (
    SELECT datafile_id, authenticated_user_id
    FROM fileaccessrequests
    GROUP BY datafile_id, authenticated_user_id
    HAVING COUNT(*) > 1
  ) AND request_state IS NULL
);

[cmbz]

• 2024/08/19
• Technically not a bug in the code, however it is a problem for installations that predate 6.2
• Prioritizing

[qqmyers]

FWIW: It may be a problem putting a fix in flyway that just deletes an old one when a new one exists (as suggested) - that it would erase old requests, with their earlier creation timestamps from the db and there could be places that are tracking those/relying on those records. I'm not sure this is the case, but perhaps having a flyway query that doesn't remove the old ones if there are new ones is better, e.g. if the count of entries with request_state null or CREATED/GRANTED/REJECTED is 1, change the null (if it exists) to CREATED (the only state that used to exist), otherwise don't erase the old one (or perhaps better - back-date the new one to the old date and then delete the old one?). Once that's done the flyway could still add a non-null constraint (as is indicated in the code/would be applied on a fresh db).