Migration system for Supabase database

[gentlementlegen]

We should have a migration system set for our new Supabase instance. To this day, everything is done through the Supabase Dashboard and there is no track of the changes. This become even more critical as we are starting to setup RLS and CLS, which won't be reflected by a db diff dump, as stated in their docs.

This means if we need to create a new db, move it, rollback, run it locally, we would be unable to do so and should do everything manually to mimic the current db's state.

Supabase has a migration system implemented, and a CLI, so we should use these to keep our db updated with the latest schema. Maybe it is better to create a separate repository, as this doesn't really belong to this project specifically. Relates to ubiquity/ubiquibot#919

[0x4007]

@rndquu rfc

[rndquu]

As far as I understand the bot's kernel won't have a DB but chances are that we will need a DB for the kernel for some plugin keys or auth related features later.

This issue is relevant for kernel plugins. We should move this issue to some ready to use plugin (https://github.com/ubiquibot/comment-incentives or https://github.com/ubiquibot/conversation-rewards) and start from there.

[0x4007]

but chances are that we will need a DB for the kernel for some plugin keys or auth related features later.

Can you elaborate? These should be read from repository ubiquibot-config.yml I don't see why the kernel would need a database.

Also I assume that Cloudflare KV will be simpler to work with (it's just key-value storage) and I think we should consider this instead of traditional databases wherever possible for plugins and possibly the kernel (although I'm skeptical the kernel needs persistent storage)

[gentlementlegen]

At least until now we do use a db hence the creation of this issue. But if indeed we don't for this repo it is not relevant, and I agree that it should moved. However I would like to talk about how we handle accounts / logins if we have one db per plugin.

[rndquu]

Can you elaborate? These should be read from repository ubiquibot-config.yml I don't see why the kernel would need a database.

Check this comment in telegram. As far as I understand we will have to store a UUID of the invoked plugin action and check it on repository_dispatch event in order to make sure the response is from the plugin the kernel had called earlier. We don't necessarily need a fully fledged DB but at least some storage.

[0x4007]

Cloudflare KV should suffice. I like the idea of using it because it seems significantly easier to set up for developers compared to a full database.