search

ubiquity-os / database

Hold database setup and migrations.
0 stars 0 forks source link

Make DB consistent #1

Open rndquu opened 2 days ago

rndquu commented 2 days ago

Our DB has a few drawbacks:

  1. The production schema is different from the one located in https://github.com/ubiquity/ubiquibot/tree/development/supabase/migrations
  2. There is no way to restore production DB schema locally using DB migrations

It makes sense to recreate our existing DB from scratch using a single migration fetched from a DB backup.

This way:

  1. We get a consistent DB which can be deployed locally
  2. Core plugins will have to create new DB migrations in this repository thus allowing us to keep all DB related migrations in a single repository

What should be done:

  1. Remove our existing DB from supabase
  2. Convert the latest backup to supabase migration (don't forget triggers, stored procedures and RLS/CLS rules) and move in this repository
  3. Create a new DB and apply a single "backup" migration from step 2
  4. Remove migration files from all of the core plugins (since they are already applied in a "backup" migration)
  5. For all future DB updates (when individual core plugin needs to update the schema) use current repository
rndquu commented 2 days ago

@gentlementlegen FYI

gentlementlegen commented 2 days ago

I am fixing current migrations because it seems that ubiquibot repository is not up to date, at that point I am searching if any repo contains them or if everything was done through Supabase UI... After that we can add other migrations.

gentlementlegen commented 2 days ago

@rndquu I could not find these anywhere so I assume they all were applied via the UI without any backup or file. The init sql combines all of these changes in one migration so it might be fine? Anyway feel free to add migrations (via migration files please) here. The problem is that the migration table will not match so migrations won't be applied except if also applied manually through Supabase UI.

rndquu commented 2 days ago

@rndquu I could not find these anywhere so I assume they all were applied via the UI without any backup or file. The init sql combines all of these changes in one migration so it might be fine? Anyway feel free to add migrations (via migration files please) here. The problem is that the migration table will not match so migrations won't be applied except if also applied manually through Supabase UI.

I feel like we need to "hard reset" our DB:

  1. Remove our existing DB from supabase
  2. Convert the latest backup to supabase migration and move in this repository
  3. Create a new DB and apply a single "backup" migration from step 2
  4. Remove migration files from all of the core plugins (since they are already applied in a "backup" migration)
  5. For all future DB updates (when individual core plugin needs to update the schema) use current repository
gentlementlegen commented 2 days ago

I agree. Do these backup include triggers, functions, and other access changes?

rndquu commented 2 days ago

I agree. Do these backup include triggers, functions, and other access changes?

Not sure. Do we use triggers and DB functions?

gentlementlegen commented 2 days ago

There are a few registered within the DB that are triggered on data changed, on CRON fashion. I do not know if they matter, not who created them.

rndquu commented 2 days ago

There are a few registered within the DB that are triggered on data changed, on CRON fashion. I do not know if they matter, not who created them.

Updated description