I don't know if you're joking or not, but this would just be reinventing the Postgres/SQL Server/Oracle/MySQL wheel using duct tape and wishes.

If you're doing something that multiple systems have had millions of hours of development to do, just use one of those.

You are wrong. Using multiple SQLite files solves quite many problems, e.g. let's say your user can have multiple products where each product has its own properties. This can be presented as 3 tables in SQL database: users, products, properties. Now with SQLite you can drop one table (users). This simplifies queries, depending on your situation you can use simpler indexes and etc.

Extra benefit: you can allow your user to download all his SQLite db as per GDPR requirement.

How do you join data across hundreds of databases?

And how is the query plan for indexes when doing so?

Using ATTACH. Works a treat. SQLite query planner knows about ATTACH.

I have seen it suggested to use one table per db to minimize lock contention and simplify backups/vacuums. Do you know if there really is nothing to lose with ATTACH spam? Any tradeoffs at all?

I wouldn't design that way. SQLite has a default limit of 10 attached databases and a hard limit of 125.

Having several areas of concern managed by distinct databases, with some occasions which warrant joins across the domains, this is the more natural use pattern. ATTACH loses foreign key constraints and triggers, among other things.

My understanding is that it just works. They're still all in the same process, and SQLite has all the information it needs to do joins. Transactions get more complex but are still atomic.

You don’t.

But why? That seems such an unnecessary hack.

Until we know how they are used, we are just speculating: I for one am curious ;)