Azure DevOps Pipeline Conditional Flow with Manual Intervention
@Shayki's answer was a large portion of what I was looking for (thanks!), but I was eventually able to figure out a solution that met all of my requirements. It involves a less than ideal use of the Release Description to propagate state between jobs.

The updated flow diagram looks like this:



I was originally thinking of the conditional flow as an if else statement, but it is really more of multiple if statements where the condition is the same. If flag is set, do X; if flag is set, do Y; etc.

Details

Instead of running the agentless job when a previous condition fails (which would result in the entire deployment failing, a violation of one of my requirements), I created a custom condition that the agentless job runs as:



If all the previous tasks have succeeded, and if the Release.ReleaseDescription variable contains the string defined in the _dbDeployErrors variable, then this job will execute (and the manual intervention task inside of it).

The _dbDeployErrors variable isn't too important, I set it to ;DB_DEPLOY_ERRORS_$EnvironmentName. The main idea is that, at least with the Web Designer, there is no way to pass information from an agent job to an agentless job. So I wrote a powershell script which uses the Get Release and Update Release Azure DevOps REST API to update the Release Description parameter on the release. This allows me to pass information to the next job in the deployment.

The basic steps for setting this flag are:


Run the db deployment
If the deployment has errors, do not fail task, but instead set an agent variable
Next task determines if the variable is set, and if so calls the Update Release API to append the flag to the release description
Future jobs and tasks can now analyze the state of the release description variable


Note: I set an agent variable instead of making the API call from the db-deploy script so that I can keep my deploy script clean and maintainable. I move the responsibility of calling the API to a dedicated script instead.

If the Manual Intervention is resumed, then the third job executes as an agent job. It will attempt to deploy the db again, but this time it sets a -Force flag on the script to ignore possible data loss (it was approved by the one who performed the intervention). If this deployment succeeds, then a quick cleanup is run to call the Update Release API and clear the flag from the release description. Finally, the other tasks can resume as normal.

Here is what the tasks in my final release definition looks like: