To clarify what does work:
What works is that from either site, a client device with an IP of 10.40.x.x is able to traverse the tailscale tunel to the other site by using the 10.65.x.x addresses. However, no device in this 10.40.x.x subnet can get to a 10.40.x.x IP at the other end.
I realise that I am NATing the outbound connections rather than directly routing them due to the limitations of pfsense, so I am thinking I need to translate the 10.40.x.x subnet on the way out of the site, but nothing I have tried seems to work so far.