I generally consider the AWS CLI configuration to be something that's unique to a developer's workstation, and shouldn't be referenced in terraform code (in the form of tying the workspace name to your AWS profile name).
This would only work if all developers on a team have synchronised the same AWS CLI config (which to me is like asking people to synchronise dotfiles, not something I'd be willing to do).
We do what you advise against at Notion and it seems to work great for our org of ~100s of developers (although a smaller fraction need to edit terraform regularly).
We use a CLI command `notion aws-sso-login` that logs us into our main user account and adds a profile per delegated app-environment pair account you can access (like app-dev/collections-infra, app-prod/collections-infra) to the AWS CLI config file. This ensures at least the standard list of profiles is present on everyone’s machine whenever they have valid credentials. I have yet to hear anyone complain about this config file meddling.
Then in our terraform directories, we use direnv to set the AWS_PROFILE environment variable to the appropriate profile to manage that stack. You can always override if you need to use a different profile for some reason.
The AWS SDK supports supplying credentials based on environment variables. When on my workstation I set AWS_PROFILE to select what profile I'm using, prior to running Terraform. This is then portable to CI where we may be using something like https://github.com/aws-actions/configure-aws-credentials to assume a role rather than using a pre-configured CLI profile.
But then you still need to either need to have something to sync the aws configuration among developers for all the accounts, or all developers will have to configure the aws config themselves, which isn't very scalable when you have more than a couple accounts you need to deal with.
This would only work if all developers on a team have synchronised the same AWS CLI config (which to me is like asking people to synchronise dotfiles, not something I'd be willing to do).
My go-to architecture for multi-environment tends to be this, as it lends itself relatively well to Git Flow (or GitHub Flow): https://github.com/antonbabenko/terraform-best-practices/tre...