[Structured Data] [Step 0] explore external DBs / SQL engines (for non-remote DBs)

[fontanierh]

The original idea was to store the actual data in a JSON column in our app database, and re-construct an in-memory SQLite DB at query time to execute the query.

As Philippe suggests, it may be worth looking into persistent NoSQL DBs.

Upsides of “just-in-time SQLite in-memory”:

• simple
• tools / infra we already have

Downsides of “just-in-time SQLite in-memory”:

• memory-hungry
• might have noticeable latency

[fontanierh]

Some discussion about this in this Slack thread

TLDR:

• In-memory SQLite has the advantage of simplicity and no extra infrastructure is required. It is also easier to handle data isolation, especially when dealing with model-generated queries. This option is favored for its lightweight nature and ease of implementation.
• NoSQL DB (like Couchbase, Mongo, or Apache Drill on top of GCP Cloud Storage) that can be queried with SQL is another alternative. This approach might be more scalable, but it requires more infrastructure and could be more complex to implement. The team also discussed potential SQL interface limitations, like those found with Spanner. There are concerns about data isolation and the number of databases needed. For instance, one suggestion was to have one DB per workspace, but there were concerns that this approach may not be scalable and could potentially lead to data leaks from structured data sources that are not part of the current assistant's selection.

In the end, the team agreed that further exploration and evaluation are required before making a decision.

[fontanierh]

We're going with SQLite as NoSQL dbs have limitations when it comes to isolating data soruces from each other with untrusted queries. "just in time" sqlite has proved to be ~fast enough on a 35MB database