Where are escape sequences needed when using tilde ~ operator in awk?

The ~ operator does pattern matching, treating the right hand operand as an (extended) regular expression, and the left hand one as a string. POSIX says:

A regular expression can be matched against a specific field or string by using one of the two regular expression matching operators, '~' and "!~". These operators shall interpret their right-hand operand as a regular expression and their left-hand operand as a string.

So ENVIRON["patt"] is treated as a regular expression, and needs to have all characters that are special in EREs to be escaped, if you don't want them to be have their regular ERE meanings.

---

Note that it's not about using $0 or ENVIRON["name"], but the left and right sides of the tilde. This would take the input lines (in $0) as the regular expression to match against:

str=foobar awk 'ENVIRON["str"] ~ $0 { 
     printf "pattern /%s/ matches string \"%s\"\n", $0, ENVIRON["str"] }'

A \ in a regular expression escapes the following character, or introduces an escape sequence. To match a literal \ with a regular expression, which is what the ~ operator does in awk, one would need to use \\ (which you do in the last example in the question). In a string comparison, this is not needed.