AE data flow databases in Digital Ocean

[damonmcc]

We can currently use local databases for dev, but we ultimately want real deployed ones.

We discussed using schemas or table prefixes to avoid collisions between developers (if individuals are able to query a deployed DB), but those are degrees of flexibility/complexity that aren't planned for the production database.

Maybe (like PFF) it'd be nice to have a database (or databases) for development? so dev, staging, prod all in one database cluster?

Potential database deployment details

The database cluster already exists in Digital Ocean as zoning-api.

It has three types of databases: dev, staging, and prod.

• The prod database is for production data used by the deployed production API server.
• The staging database is for deploying and testing new data or database migrations before doing the same operations to prod.
• dev databases are for developers to use as experimental versions of staging to test in-progress changes to code.
  • should at least be a separate cluster, or a single database that is shared by all developers
  • important that the CI workflow can be replicated locally
  • concerned about many DBs per developer

There are four types of users with different access privileges: doadmin, prod, staging, and dev.

• doadmin is created by default when setting up the cluster in digital ocean and has full access privileges to all databases in it. doadmin is used to create databases and set the access privileges of other users.
• prod is used to run migrations and data updates on the prod database.
• staging is used to run migrations and data updates on the staging database.
• dev users are developer-level users and are used to run migrations and data updates on the dev database.

the two relevant database clusters

• zoning-api is an API database cluster for the tables that the Zoning API hits
• ae-data-flow is for transforming source data and exporting to an API database cluster

tasks

• [x] create the data flow DB cluster: dev, staging, and prod DBs/credentials
• [x] #22
• [x] setup the Zoning API DB cluster: dev, staging, and prod DBs/credentials

[damonmcc]

ideas from chat on 3/20

• don't want to pollute API DB with tables that aren't defined by drizzle migrations/schemas
• maybe be best to export csvs at the and of a "data flow" run to then COPY into the API DB
  • could also use pgdump and pg_restore for a more type-safe way to copy a table from one DB to another
• containerization is good

next steps

• [x] make a DB cluster for data flow DBs called ae-data-flow
• [x] update draft diagram here (and perhaps get it merged so it's easier to reference)
• [x] revise the PR in the Zoning API repo