Switch MESS' Database to use snake_case (this is best practice for Postgres)

[EyeBrawler]

The bulk of this procedure should probably be done when MESS is not in use (like a long weekend)...

This item involves...

1. Updating the snake_case branch to contain the latest changes from main. Ensure that after merging there is a an empty migrations folder present in MESS.Data. All files in migrations directory should be deleted to make way for a new migration history.

2. Use MESS' export utilities to export all database data.

As of 5/7/2026, not all MESS data can be exported, full database export and import in a database agnostic format is a prerequisite for this task.

3. Create a new database migration called InitialMigration from the MESS.Data directory with this command...

dotnet ef migrations add InitialMigration

This migration should contain the entire database schema using snake_case for table, column and foreign key names. Look over the migration carefully to make sure all names are using snake_case, not PascalCase. Fix naming in ApplicationContext.cs as required.

As of 5/7/2026, the snake_case branch should be okay but if database changes have happened since writing, some tweaks may have to be made. For more context, compare ApplicationContext.cs in snake_case with the same file in main.

5. Setup a new database for production and point your development environment user secrets to it. Then run...

dotnet ef migrations list

Make sure that your single new migration is listed. You should still have the snake_case branch checked out at this point.

If the migration shows as (pending), you can run the command below to update the database...

dotnet ef database update

6. If everything seems to go well, all of the old database views can be ported to the new schema. New migrations should be made for these following the standard procedure...

donet ef migrations Add NameOTheDatbaseView

This will make an empty migration that can then have the necessary CREATE OR REPLACE VIEW and DROP VIEW IF EXISTS statements manually added. See past database view migrations for a guide. The main difference will be that all foreign keys, table, and column names should all be referenced with snake_case, without the need for any "" in queries around field names. Running dotnet ef database update will add the views to your current database connection configured via user secrets.

7. Merge the snake_case branch into main. Point your MESS web server to connect to the new database. For SENSIT, this would involve editing the connection string environment variable with the new databases connection string. Because the new database will be on the same DBMS, the connection string should only change very slightly. If the old database is renamed and the new database has the same name as the old one, no changes may be required to the web app's configuration.

8. Using import utilities (many of these do not exist yet), import all data back into MESS.

For any other MESS customers who may exist, the upgrade procedure will be similar to SENSIT's, export all data, pull down the latest changes from main, then with a dev environment connected to a new production database, run dotnet ef database update. This would apply the new migration history and initial database schema. Then, log into MESS and upload all data exports to the import utilities.