Meta-issue: Deployment

This issue exist to track discussions around deployment. For the initial phase we will focus on Connect deployment from Positron and Workbench as this is the stack we control - others may think about deployment in other context if they wish to do so in the mean time.

Key questions:

What does ggsql deployment look like?

Right now deployment would be in the form of a quarto report or some sort. However, the R bindings also support deploying shiny applications with ggsql code inside.

I do envision a mode where you deploy a single .ggsql file and you get an embeddable visualisation. Depending on how ggsql's own multiplot support evolves this may end up being a viable dashboard solution, though I'm not sure SQL provides the best syntax for layout work. If not, do we need a new format for dashboard-y deployments or does quarto serve that need?

For deployment, we also need to think about ggsql versioning. We are in a blissful state of just having to declare a single version and not black pit of 100s of dependencies, but declare it we must somehow. Is this a Connect responsibility or should ggsql itself do something about this?

What kind of backend connection is kept - with what kind of credentials?

Our happy path is that an analyst sits on their computer, developing an analysis using ggsql either in Positron or Workbench. They have their credentials set up so they can connect to e.g. Snowflake and visualise that data directly. They arrive at an end product and hit deploy. The deployment target is send to Connect, along with the credentials and everything just magically works. We must strive for this to be the case!

However, I can see multiple modes of connection with a backend depending on the needs, and also multiple modes of credentials:

Connection modes:

• Keep a connection to the original backend, updates to the database is reflected in the visualisation whenever it is presented
• Cache the main data sources in a local DuckDB instance and query against that in the backend - this may be updated periodically but not real-time
• Embed the data in a "static" deployment (i.e. connection is severed)

Credential mode:

• Use the authors credentials/credentials provided specifically for this deployment. All visits will use the same credentials and it is up to the analyst to not expose confidential information in the visualization
• Visitor brings their own credentials and these are used when querying the database and creating the plot. The visitor only sees data for which they have been granted access. This mode explicitly requires the first connection mode described above

No implementation files or tests are identified. Start by reviewing the deployment questions for Connect from Positron and Workbench, including Quarto reports, Shiny applications, versioning, backend connection modes, and credential modes. Done means the project has an agreed deployment design and a scoped implementation plan.