Automatically unlock tasks that have been locked for more than 3 days

[spwoodcock]

Blocker https://github.com/hotosm/fmtm/issues/1604

Is your feature request related to a problem? Please describe.

• A task may be locked by a user, then they don't actually map, or forget to unlock.
• The project admin can unlock the task, so this is not a complete blocked.
• But other mappers cannot unlock the task.

Describe the solution you'd like

• Automatically unlock the task on the database after 3 days have elapsed.

Solution

This is a relatively easy solution via pg_cron directly on the database.

Create a function:

CREATE OR REPLACE FUNCTION unlock_longstanding_locked_tasks()
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    svc_user_id INT;
BEGIN
    SELECT id INTO svc_user_id
    FROM users
    WHERE username = 'svcfmtm';

    INSERT INTO task_history (project_id, task_id, action, action_date, user_id)
    SELECT project_id, task_id, 'READY', NOW(), svc_user_id
    FROM (
        SELECT project_id, task_id, action, action_date,
               ROW_NUMBER() OVER (PARTITION BY project_id, task_id ORDER BY action_date DESC) as row_num
        FROM task_history
    ) subquery
    WHERE subquery.row_num = 1
      AND subquery.action = 'LOCKED_FOR_MAPPING'
      AND current_timestamp - subquery.action_date >= interval '3 days';
END;
$$;

Create the cron (runs every day at midnight):

SELECT cron.schedule('0 0 * * *', 'SELECT unlock_longstanding_locked_tasks();');