How does fleet work with osquery extensions? Do the extension get installed on the endpoint and automatically become available to query from fleet UI?

That's right. Any extension table available in an agent connected to Fleet can be queried by Fleet. Note that the "compatible with" message may show an error because it won't know your extension table, but the query will still work.