Yes that makes sense.

> define a metadata endpoint that maybe returns a JSON schema

We could use an OpenAPI spec[0], which many REST services expose as a JSON endpoint. I think that could work: develop a generic "OpenAPI" FDW, which you can connect like this:

    create server example_api
      foreign data wrapper example_api_wrapper
      options (
        open_api_url 'https://api.example.com/'
      );

Then we'd map:

    GET -> SELECT (which would map selected columns into a table)
    POST -> INSERT
    PATCH -> UPDATE
    DELETE -> DELETE

[0] OpenAPI: https://swagger.io/specification/