We want to launch a feature where users can create RESTful endpoints based on queries. This is an enhancement of "saved queries".

I have started this work in a branch: https://github.com/scratchdata/scratchdata/pull/163/files

This is roughly how I propose building it:

• Rename "Shared Query" to "Saved Query"
  • These queries can either be public, meaning they can be used as public share links
  • Or they can be private, meaning they require an API key to query
• Queries should accept named parameters. For example: SELECT * FROM events WHERE user = @user
  • These will be passed as query parms: GET /user_events?user=alice
• We should be able to create API keys which only have access to these queries (and not access to any other endpoints)
• When we create an API key, we should be able to set default parameters that cannot be overridden
  • For example, create an API key api_key_1 with the user parameter set to alice.
  • In this case, the person making the query does not need to specify a ?user=alice. If they do, then the parameter will be overwritten by the default
• Ideally, an API key could be granted access to multiple endpoints.

Finally, there should be a UI for creating these queries.

• I had started a UI prototype:
• Here's a video of the ideal state.
• Homepage screenshot.

If we have time:

• It would be awesome to be able to create models (protobuf or openapi)
  • We can either use EXPLAIN statements or DESCRIBE (select * from events where user = @user) to get the output type.
• Auto-generate documentation (just the basics)