DB: 2 schemas/db's for dev & prod

[joeflack4]

Overview

@Sigfried I think we spoke briefly about this. We want to split prod and dev into two different databases (or schema). As I'm doing the DB refresh, I think it'll come up in the future that we need to do stuff that affects the DB, and we don't want that to affect users. The DB is expensive, so you had the idea of using a different schema for the development deployment. I think that sounds like a good idea.

Current blockers

Main thing holding us back is that these would need to be kept in sync. And that could be difficult.

Sub-tasks

• [ ] Set up code
  • ChatGPT: To copy a schema in PostgreSQL, you can use the command SELECT clone_schema('old_schema','new_schema'). This command copies all tables in the schema, including the default values. Us: Does this copy data or just the schema? Sounds like data.
• [ ] Set this up in initialize()
• [ ] Update DB refresh: Needs to (a) run the refresh on 2 schemas, or (b) run on dev first, then replace those specific tables, perhaps via the same _old and _new method, unless there's a safe way to do that in PostgreSQL with like a single copy command.

[hugwuoke]

I believe this has been figured out but tbh I don't completely understand what is costly etc.

[joeflack4]

This one hasn't been done. I changed the title from 'development' to 'development deployment'. For local development we can use a local database. But the issue here is that http://bit.ly/termub and http://bit.ly/termub-dev share the same exact database. This is actually a really bad, not normal software development practice. We want a stable production app where when we are developing stuff and something goes wrong, it won't be affected.

Regarding the cost, I think our Postgres database on Azure is like $80/mon. The typical situation is that we'd spin up another DB. But if we want to save $80/mo, we can just use another Postgres schema. So right now, the schema we use for both prod/develop is n3c. But we could change it for example such that prod uses n3c and develop uses n3c_develop.

@Sigfried @hugwuoke I've marked this with medium urgency but really it kinda should be high. This is something I could probably do after most of the DB refresh issues are completed.