Non-standard evaluation, in my opinion, does fine in the top layer (analysis scripts, DSLs). But it's a pain to build on top of magic without a more concrete layer between. Base R uses character vectors and name attributes to hide the magic. The Tidyverse uses symbols. I find character vectors much easier to understand and manipulate.
Formulas are the only scenarios in base R where symbol and expression manipulation is sometimes necessary. And I don't do that thinking, "Boy, I wish this was how I did 95% of my work."
Personally, I use data.table. It's been great even for packages. If it's an internal package dataset, I don't need to manipulate symbols. And if it's a user-supplied argument, there's usually a simple and fast way to do it with a character vector. Or I define an S3 class, give a helper function that produces a dataset with known names, and just write functions around that. The user can handle combining data however they want.
>Base R is a total mess and largely inconsistent
I wouldn't go so far as to say "total mess." Not even an annoyingly big mess. The few functions that make up most of analysis or package code are dead simple. Inconsistent argument names is hardly a problem with an IDE.
(I know some people swear by data.table, but I find it gets ugly fast if you want to do anything more than simple joins or split-apply-combine)
In particular, I don’t see how the tidyverse is at all ‘magical’ - take the two most popular tidy libs, dplyr and ggplot2. The api for dplyr is very explicit, intuitive, and based on long-standing precedence (sql). The api for ggplot2 is admittedly less intuitive, but is itself an implementation of widely known framework (Grammar of Graphics). If by magical you mean in their abstractions, those are about as far from magical as one can get.
The tidyverse does get iffy sometimes when you need to dig into the rlang/tidy eval area, but that’s all well-documented.
I haven’t had to use R in a production environment for about a year now, but I always enjoyed the concise tidy api.
Base R is a total mess and largely inconsistent, but I guess that’s what you get when statisticians from different uni’s patchwork a language in their spare time.