Basically: SELECT CONCAT("https://phabricator.wikimedia.org/T", mt.id) AS taskID, mt.title, FROM_UNIXTIME(mt.dateModified) AS creation FROM phabricator_maniphest.maniphest_task mt WHERE mt.status = "stalled" AND mt.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst="PHID-PROJ-bchzb6qpl3jhgs2oq6um") AND mt.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 3) ORDER BY mt.dateModified;

I'm slightly fearful of this because I believe for so many projects, the automatic closure would likely not be seen by anyone and thus dissapear from boards.

I would suggest that if we go forward with this, to split the automated process in two steps: 1) notify of upcoming automatic closure, 2) actual closure.

For example, if we were to pick automatic closure at 90 days, the bot could leave a comment at 30 days briefly describing what the bot is seeing (task is stalled with no status change or comment for 30 days, link to docs about our conventions around this, announce upcoming closure date, and briefly describe how to prevent it).

This way removes the pressure from those setting things to "Stalled" to have to explain (and remember to explain) stuff upfront. It also takes care of most accidental or unintended uses of the status.

Tentatively adding FY2020-21 Q1 tag for planning

For the records, right now we have 317 stalled tasks which have been stalled for longer than 12 months.

I agree with everything that Krinkle wrote above.

1. Query [1] for tasks that currently have stalled status and do not have a subtask (could be blocked on that) and do not have an Upstream tag (could be blocked on that) and got stalled status set before a certain time X (INTERVAL below is set to 36 MONTHS) and did not get stalled status set after a certain time X:
2. Mass-add comment to tasks above that they have been stalled since time X, why that's not good, that task status will be set to declined in three months, that this can be prevented by updating the task status OR adding the Upstream tag if appropriate OR adding a subtask if the task is blocked on some other work }.
3. Three or six months later, run same query but bump the two INTERVAL values by three or six months.
4. Mass-decline tasks in query result with an explanation how to reopen.

[1] SELECT CONCAT("https://phabricator.wikimedia.org/T", mt.id) AS taskID, u.userName AS author, mt.title, FROM_UNIXTIME(mta1.dateCreated) AS stalled_date FROM phabricator_maniphest.maniphest_task mt JOIN phabricator_user.user u ON u.phid=mt.authorPHID INNER JOIN maniphest_transaction mta1 WHERE mt.status = "stalled" AND mt.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst="PHID-PROJ-bchzb6qpl3jhgs2oq6um") AND mt.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 3) AND mt.phid = mta1.objectPHID AND mta1.transactionType = "status" AND mta1.newValue = "\"stalled\"" AND FROM_UNIXTIME(mta1.dateCreated) <= (NOW() - INTERVAL 36 MONTH) AND mt.phid NOT IN (SELECT mta2.objectPHID FROM maniphest_transaction mta2 WHERE mta2.transactionType = "status" AND mta2.newValue = "\"stalled\"" AND FROM_UNIXTIME(mta2.dateCreated) >= (NOW() - INTERVAL 36 MONTH)) ORDER BY mta1.dateCreated;

And before doing that, likely ask on wikitech-l@ etc if/why people think this is a potentially bad idea.

Change 638163 had a related patch set uploaded (by Aklapper; owner: Aklapper):
[operations/puppet@production] phabricator weekly changes email: List stalled task stalled for years

https://gerrit.wikimedia.org/r/638163

Using the SQL query above, as of early 2020-11-01 there were
100 stalled tasks for more than 36 months, 137 for more than 24 months, 279 for more than 12 months.
Procastinating, I went through a bunch of them and now reduced numbers to
47 stalled tasks for more than 36 months, 77 for more than 24 months, 217 for more than 12 months.

My low-level idea (to start somewhere™) is to add that query to the weekly Phab reports email, sometimes triage old ones, slowly tighten the timeframe. In theory.

100 stalled tasks for more than 36 months, 137 for more than 24 months, 279 for more than 12 months.

As of today that's down to 18 stalled tasks for more than 36 months, 33 for more than 24 months, 160 for more than 12 months.

Change 638163 merged by Dzahn:
[operations/puppet@production] phabricator weekly changes email: List stalled task stalled for years

https://gerrit.wikimedia.org/r/638163