[Feature request]: Add support for offline data persistence

General Info

[X] I installed the latest version of Supabase Kt
[X] I checked for similar feature requests

Feature request

As requested by a bunch of developers: In many cases such as Mobile Apps, you will want to allow the user to use your app while having no connection to Supabase. For that, you currently have to implement data caching yourself. supabase-kt should support this, but I'm not completely sure which way I'd go to support:

Postgrest
Realtime
(Storage)

If data gets removed/modified locally while offline, syncing should also be done automatically when back online.

Firebase/Firestore does support this.

Proposal:

For Postgrest: The idea would be to have a local SQLite database via SQLDelight or SQLite and cache the data when any postgrest request is made.

Only requested data gets saved, so no additional requests are being made.
Filter support can probably also be realized by converting the rest query to an SQL query.
For inserting entries to the local db while offline, we can just save the postgrest request 1:1 and when back online, insert them all at once in one request
For modifying and deleting entries from the local db while offline, we probably have to make separate requests once the device is back online

With this approach, we have to device how we get the table schemas, which obviously should be created before any caching is done.

For Realtime: If the device looses connection, the device listens to the local DB for changes \ Maybe only for the high-level functions such as selectAsFlow

I put Storage in parenthesis, because other libraries such as Coil basically do that for us.

Usecase

No response

supabase-community / supabase-kt