Given we are at this, and this is not (yet) set in stone: could it make sense to have UserAgent be made backward compatible so that when used here the first few elements matches the old syntax?

Before:
User-Agent: DuckDB 0.9.1 19862840b8 linux_amd64

With this PR:
User-Agent: duckdb/0.9.1(linux_amd64) 19862840b8 [capi | ...]

CLI with this PR + changing (slightly) UserAgent():
User-Agent: DuckDB 0.9.1 19862840b8 linux_amd64 [capi | ...]

This would require changing UserAgent string, but I think it might be for the better to have a simpler space delimited string.

I would also consider having the last part of the user-agent be non-optional, with a default of "default" or something, this would also simplify parsing (say we add stuff after, it's not super cool to parse A B C [D] [E], if both D and E can be optional).

Hmm. It might make sense to deviate even farther from the original format of DuckDB <version> <sha> <platform>, and to fold the SHA as a detail under the first duckdb token:

User-Agent: duckdb/0.9.1(linux_amd64;19862840b8) [capi | ...]

Here is why:

1. The original user-agent value described only core DuckDB. When core DuckDB versioning diverges from API versioning (with DuckDB core becoming stable and slower changing), it will be helpful to have a version/sha combination for each layer.
2. The SHA is more useful for troubleshooting than analysis. When analyzing the data, answering the question of "what are all unique surfaces from which duckdb is used" would become very messy if SHA is a standalone first-layer attribute. With the product/version (detail) syntax, it's trivial to extract just the space separated products and discard the other information.

One more consideration is that listing products/layers in descending importance order and placing product detail in parentheses is what the user-agent spec asks for.

@carlopi can this be merged?