Migrate database to integrate capital project database schemas

[TangoYankee]

Description

Create a migration to integrate the capital project schemas into the database.

Tips

• This is a volatile process. It must only be done on a local database. This prevents anyone else from looking at the database and having their workflow disrupted. It simplifies the management of databases as they can be easily thrown away/iterated on, without the risk of orphaned databases in the cloud.
• Creating the migration files
  • We use drizzle-kit cli tool with the generate:pg command to generate the up migrations.
  • drizzle-kit is installed as a dev dependency. However, we do not have an npm script which leverages drizzle-kit to create migrations. Instead, you'll need to use npx to access and run drizzle-kit. ex) npx drizzle-kit generate:pg
  • drizzle-kit does not create down-migrations. If you want to reset your local database to a previous state, you'll need to 1) drop the errant migration(s) and 2) remove your db-volume directory to wipe the database state. Yes, this is annoying. However, It's only really necessary when making a PR. When developing the feature, make as many "up" migrations as you need to get the schemas into the correct format. Only at the end, drop all the "incremental" migrations. Then generate a single migration that contains the correct state of the schemas. This single migration will make it to the PR.
• Running the migrations
  • drizzle-kit does not actually apply the migrations
  • We rely on a custom migrate file to apply the migrations
  • The migrations are run with a custom drizzle:migrate script
  • If you want more debug information when running migrations, you can edit the db/migrate.ts file
• Editing the migration files
  • drizzle-kit is going to mess up the geometry types. Specifically, it's going to put quotes around the name. We'll need to manually remove the quotes from the names of the geometry files.
  • Migrations are stateful. They are tracked in a table within the database. If a migration has already been successfully applied, editing the migration file will not update the database; drizzle will mark the migration as already applied and skip it. If you want to edit an already-applied migration, you'll need to delete the db-volume to wipe the state of the database. From there, you can re configure the database and re-apply the migrations.

Acceptance Criteria

• [ ] A single migration file is created to apply the capital project schemas

Related tickets

Blocked by #215 UPDATE Also blocked by #221 to include relevant geographies UPDATE v2 blocked by #223 and #224

[TangoYankee]

While making these migrations, also fix the mercatorLabel geometry for the capitalProject. It should be a point and not a multipoint.