Major database refactor discussion, dropping support for sqlite #170
Description
In the coming days several more commits are being added into #167. The functionality is fairly well verified at this point but there are some caveats to the changes coming in.
As part of verifying bb-portal we have done some internal testing on a larger project, this has been necessary to understand how the system behaves under load. Larger project in this context means 100k invocations per workday, invocations with up to 100k targets and about 100 million targets are built per workday (average number of targets built per invocations ~ 1000). With the incoming changes a database for this workload should run comfortably on a 1-2 core machine while the bb-portal-backend process itself is mainly limited by having enough ram to comfortably fit your largest log.
To allow this to work ent as the single way of interacting with the database has proven insufficient. We're therefore adding a sqlc based entrypoint that allows us to write statically verified performant sql code. Ent still remain the source of truth for the database schema itself and is supported by the codebase, but there is additional support to use sqlc based queries wherever it makes sense.
The sql code we are adding is not database agnostic. In order to cut down the scope of this change we are therefore only writing sql that targets specifically the postgresql dialect. Whether for testing, local development or for production the database must support the postgresql dialect.
If you are already using postgresql in production then this is probably not going to make any major differences. If you are relying on the builtin sqlite in production then you will need to look into adding a postgres server to your deployment. Depending on your environment this could be as easy as adding a postgres sidecar container to your bb-portal container.
Additionally we are not delivering any migrations as part of this update. We have added some basic scaffolding in order to facilitate future migrations but the old database will have to be dropped. We have no timeline for when migration support will be added but as we consider the portal data to be ephemeral log data we have not prioritized long term retention.
This refactor will resolve the following tickets: