Introduce Prisma

[rhythnic]

• Spin up a postgres container and deploy the existing SQL tables to it.
• Use the 'prisma init` command to introspect the postgres instance and generate a Prisma datamodel.
• Add Prisma to development workflow.
• Add Prisma to the docker-compose files for testing.
• Replace the calls to SQLite3 with calls to the Prisma client.
• Update docs to explain deployment-dependency on Prisma.

[rhythnic]

Bugs/findings to confirm and create issues for after DB migration:

• user can change their own role to ADMIN so long as they have an email address (#263)
• REST API is using PUT for partial updates (#264)
• Email update unable to be rejected by previous email once accepted by new email (#265)
• NetworkType name is not marked as unique in SQL schema (fixed in this issue)

[rhythnic]

The IDs of CompanyType, UserRole, and NetworkType records are hard-coded in the conditional logic, authorization checks, and initialization code. Prisma currently doesn't allow specifying an ID. I think we should remove the reliance on a specific ID, but we can't until releasing V2 because it will break existing data sets. For now, I'm using a baseline SQL file to seed the database with specific IDs, although, to reset the database with this method is much more tedious because you have to spin down all the containers.

[rhythnic]

If a NetworkProtocol has no MasterProtocol, it references it's own ID in the masterProtocol field. This seems problematic to me. The masterProtocol field in itself is problematic. Because of the circular reference I was unable to import the schema and then the data into Postgres. It can only be imported as schema and data together.

[rhythnic]

Device query supports searching by companyId, which isn't in the device schema. Will remove that functionality.

[rhythnic]

Removed "findByName" methods from Device and Application models. Couldn't find anywhere in the code where they were being used, and name props on Device and Application are not unique, so I thought it best to remove the methods.

[rhythnic]

I have come to like the company resource and the user management design. I think the company system that's in place is good for the target audience. It allows lpwanserver to be deployed on behalf several businesses who could manage their own applications.

[rhythnic]

About Device devEUI and deviceProfileId not being in device schema.

Nick Baroni [12:08 PM]
@dr-code-monkey Do you know why the DeviceProfile is referenced by the DeviceNetworkTypeLink and not by the Device?  Also, the same for devEUI.  It's not on the device schema and I think it's in DeviceNetworkTypeLink.networkSettings.

Dan Schrimpsher [12:09 PM]
Cause the device profile was considered part of the remote thing. I am not sure that makes sense anymore but in think that is the reason

Also eui same

Nick Baroni [12:12 PM]
Ok, so one device record can relate to multiple remote devices, each with possibly a different device profile and devEUI.

Dan Schrimpsher [12:13 PM]
In theory but I don't think so in practice
But that's the idea

[rhythnic]

When bringing over the schema/dump for Postgres, all use of "not null" for references should be removed. This is to account for a bug in Prisma when deploying to an existing database with data/schema. https://github.com/prisma/prisma/issues/3319

[rhythnic]

Removed "DEFAULT 0" from user.role in the baseline SQL. The role IDs (hard coded) for admins is 2 and regular users is 1. There is no role with ID 0, and this being in the SQL was causing an error on user-create. Need to add this to migration docs.

[rhythnic]

There seems to be a bug in Prisma for pre-existing Postgresql databases where if a table has a foreign key constraint to itself, like networkProtocols and masterProtocol, Prisma inserts 2 records instead of one, and doesn't query well. I reported it here: https://github.com/prisma/prisma/issues/4204

As a workaround, I need to remove the relationship in the DB between masterProtocol and NetworkProtocol. The field still serves as a reference, but to the DB it's just an int. I don't think removing the relationship in the DB will break anything. It doesn't seem to be used much except some places in the front end.