Hello William, excited to hear about your interest in GSoC for this topic.
In my opinion the main priority here would be to add generic support support for CompositeField as that would pave the door, amongst other things, to have expression define that they return tuples of fields.
Today there are few cases where we fake the output_field of expressions or have to resort to manual subqueries wrapping (e.g. during aggregation) which we could do away with if we had CompositeField.
Members of sql.Query.alias_map which are either BaseTable or Join could define their own output_field which would make it easier to compose complex joins scenarios, CTEs, and implement methods a such as as_subquery because the generation of the SQL associated with the FROM clause members would be delegated to the expressions instead of hardcoded in the compiler.
This would allow the lookup resolving logic of Query be changed from starting from self.model to self.base_table.output_field and pave the door to moving tons of hardcoded Query logic to Expression.resolve_expression.
Official set returning functions, which behave closely to subqueries, could then be built on top of that by having annotations of them result in LATERAL JOIN pushdown if they are not singleton.