feat(web/server): Implement database migrations using Flyway

This change updates the Quarkus-based web server to use Flyway for
migrating between schema versions. This enables us to evolve the schema
and denote it in SQL.

8 files changed, 154 insertions, 10 deletions

diff --git a/opendc-web/opendc-web-server/build.gradle.kts b/opendc-web/opendc-web-server/build.gradle.kts
index d6b9164c..a55df2f7 100644
--- a/opendc-web/opendc-web-server/build.gradle.kts
+++ b/opendc-web/opendc-web-server/build.gradle.kts
@@ -48,6 +48,7 @@ dependencies {
 
     implementation(libs.quarkus.hibernate.orm)
     implementation(libs.quarkus.hibernate.validator)
+    implementation(libs.quarkus.flyway)
     implementation(libs.quarkus.jdbc.postgresql)
     implementation(libs.quarkus.jdbc.h2)
 
diff --git a/opendc-web/opendc-web-server/src/main/resources/application-dev.properties b/opendc-web/opendc-web-server/src/main/resources/application-dev.properties
index 3f30e9c4..4065f55f 100644
--- a/opendc-web/opendc-web-server/src/main/resources/application-dev.properties
+++ b/opendc-web/opendc-web-server/src/main/resources/application-dev.properties
@@ -20,11 +20,11 @@
 
 # Datasource (H2)
 quarkus.datasource.db-kind=h2
-quarkus.datasource.jdbc.url=jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;INIT=CREATE TYPE IF NOT EXISTS "JSONB" AS blob;
+quarkus.datasource.jdbc.url=jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;INIT=CREATE TYPE IF NOT EXISTS "JSONB" AS json;
 
 # Hibernate
 quarkus.hibernate-orm.dialect=org.hibernate.dialect.H2Dialect
-quarkus.hibernate-orm.database.generation=drop-and-create
+quarkus.flyway.clean-at-start=true
 
 # Disable authentication
 opendc.security.enabled=false
diff --git a/opendc-web/opendc-web-server/src/main/resources/application-docker.properties b/opendc-web/opendc-web-server/src/main/resources/application-docker.properties
index cd1f9ff3..eae9ee1e 100644
--- a/opendc-web/opendc-web-server/src/main/resources/application-docker.properties
+++ b/opendc-web/opendc-web-server/src/main/resources/application-docker.properties
@@ -28,7 +28,6 @@ quarkus.datasource.jdbc.url=${OPENDC_DB_URL}
 
 # Hibernate
 quarkus.hibernate-orm.dialect=org.hibernate.dialect.PostgreSQL95Dialect
-quarkus.hibernate-orm.database.generation=validate
 
 # Disable OpenDC web UI
 quarkus.opendc-ui.include=false
diff --git a/opendc-web/opendc-web-server/src/main/resources/application-prod.properties b/opendc-web/opendc-web-server/src/main/resources/application-prod.properties
index 09653d59..8e6a9720 100644
--- a/opendc-web/opendc-web-server/src/main/resources/application-prod.properties
+++ b/opendc-web/opendc-web-server/src/main/resources/application-prod.properties
@@ -20,11 +20,10 @@
 
 # Datasource (H2)
 quarkus.datasource.db-kind=h2
-quarkus.datasource.jdbc.url=jdbc:h2:file:./data/opendc;DB_CLOSE_DELAY=-1;INIT=CREATE TYPE IF NOT EXISTS "JSONB" AS blob;
+quarkus.datasource.jdbc.url=jdbc:h2:file:./data/opendc;DB_CLOSE_DELAY=-1;INIT=CREATE TYPE IF NOT EXISTS "JSONB" AS json;
 
 # Hibernate
 quarkus.hibernate-orm.dialect=org.hibernate.dialect.H2Dialect
-quarkus.hibernate-orm.database.generation=validate
 
 # Disable authentication
 opendc.security.enabled=false
diff --git a/opendc-web/opendc-web-server/src/main/resources/application-test.properties b/opendc-web/opendc-web-server/src/main/resources/application-test.properties
index 78512f3f..338a00b9 100644
--- a/opendc-web/opendc-web-server/src/main/resources/application-test.properties
+++ b/opendc-web/opendc-web-server/src/main/resources/application-test.properties
@@ -20,10 +20,10 @@
 
 # Datasource configuration
 quarkus.datasource.db-kind = h2
-quarkus.datasource.jdbc.url=jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;INIT=CREATE TYPE "JSONB" AS blob;
+quarkus.datasource.jdbc.url=jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;INIT=CREATE TYPE IF NOT EXISTS "JSONB" AS json;
 
 quarkus.hibernate-orm.dialect=org.hibernate.dialect.H2Dialect
-quarkus.hibernate-orm.database.generation=drop-and-create
+quarkus.flyway.clean-at-start=true
 
 # Disable security
 quarkus.oidc.enabled=false
diff --git a/opendc-web/opendc-web-server/src/main/resources/application.properties b/opendc-web/opendc-web-server/src/main/resources/application.properties
index d0b567e5..40933304 100644
--- a/opendc-web/opendc-web-server/src/main/resources/application.properties
+++ b/opendc-web/opendc-web-server/src/main/resources/application.properties
@@ -42,3 +42,7 @@ quarkus.smallrye-openapi.info-license-url=https://github.com/atlarge-research/op
 
 quarkus.swagger-ui.path=docs
 quarkus.swagger-ui.always-include=true
+
+# Flyway database migrations
+quarkus.flyway.baseline-on-migrate=true
+quarkus.flyway.migrate-at-start=true
diff --git a/opendc-web/opendc-web-server/src/main/resources/db/migration/V1.0.0__core.sql b/opendc-web/opendc-web-server/src/main/resources/db/migration/V1.0.0__core.sql
new file mode 100644
index 00000000..183a70ea
--- /dev/null
+++ b/opendc-web/opendc-web-server/src/main/resources/db/migration/V1.0.0__core.sql
@@ -0,0 +1,144 @@
+-- Hibernate sequence for unique identifiers
+create sequence hibernate_sequence start with 1 increment by 1;
+
+-- Projects
+create table projects
+(
+    id                 bigint       not null,
+    created_at         timestamp    not null,
+    name               varchar(255) not null,
+    portfolios_created integer      not null,
+    scenarios_created  integer      not null,
+    topologies_created integer      not null,
+    updated_at         timestamp    not null,
+    primary key (id)
+);
+
+-- Project authorizations authorize users specific permissions to a project.
+create table project_authorizations
+(
+    project_id bigint       not null,
+    user_id    varchar(255) not null,
+    role       integer      not null,
+    primary key (project_id, user_id)
+);
+
+-- Topologies represent the datacenter designs created by users.
+create table topologies
+(
+    id         bigint       not null,
+    created_at timestamp    not null,
+    name       varchar(255) not null,
+    number     integer      not null,
+    rooms      jsonb        not null,
+    updated_at timestamp    not null,
+    project_id bigint       not null,
+    primary key (id)
+);
+
+-- Portfolios
+create table portfolios
+(
+    id         bigint       not null,
+    name       varchar(255) not null,
+    number     integer      not null,
+    targets    jsonb        not null,
+    project_id bigint       not null,
+    primary key (id)
+);
+
+create table scenarios
+(
+    id                bigint           not null,
+    name              varchar(255)     not null,
+    number            integer          not null,
+    phenomena         jsonb            not null,
+    scheduler_name    varchar(255)     not null,
+    sampling_fraction double precision not null,
+    job_id            bigint,
+    portfolio_id      bigint           not null,
+    project_id        bigint           not null,
+    topology_id       bigint           not null,
+    trace_id          varchar(255)     not null,
+    primary key (id)
+);
+
+create table jobs
+(
+    id         bigint    not null,
+    created_at timestamp not null,
+    repeats    integer   not null,
+    results    jsonb,
+    state      integer   not null,
+    updated_at timestamp not null,
+    primary key (id)
+);
+
+-- Workload traces available to the user.
+create table traces
+(
+    id   varchar(255) not null,
+    name varchar(255) not null,
+    type varchar(255) not null,
+    primary key (id)
+);
+
+-- Relations
+alter table project_authorizations
+    add constraint FK824hw0npe6gwiamwb6vohsu19
+        foreign key (project_id)
+            references projects;
+
+create index fn_topologies_number on topologies (project_id, number);
+
+alter table topologies
+    add constraint UK2s5na63qtu2of4g7odocmwi2a unique (project_id, number);
+
+alter table topologies
+    add constraint FK1kpw87pylq7m2ct9lq0ed1u3b
+        foreign key (project_id)
+            references projects;
+
+create index fn_portfolios_number on portfolios (project_id, number);
+
+alter table portfolios
+    add constraint FK31ytuaxb7aboxueng9hq7owwa
+        foreign key (project_id)
+            references projects;
+
+alter table portfolios
+    add constraint UK56dtskxruwj22dvxny2hfhks1 unique (project_id, number);
+
+create index fn_scenarios_number on scenarios (project_id, number);
+
+alter table scenarios
+    add constraint UKd0bk6fmtw5qiu9ty7t3g9crqd unique (project_id, number);
+
+alter table scenarios
+    add constraint FK9utvg0i5uu8db9pa17a1d77iy
+        foreign key (job_id)
+            references jobs;
+
+alter table scenarios
+    add constraint FK181y5hv0uibhj7fpbpkdy90s5
+        foreign key (portfolio_id)
+            references portfolios;
+
+alter table scenarios
+    add constraint FKbvwyh4joavs444rj270o3b8fr
+        foreign key (project_id)
+            references projects;
+
+alter table scenarios
+    add constraint FKrk6ltvaf9lp0aukp9dq3qjujj
+        foreign key (topology_id)
+            references topologies;
+
+alter table scenarios
+    add constraint FK5m05tqeekqjkbbsaj3ehl6o8n
+        foreign key (trace_id)
+            references traces;
+
+-- Initial data
+insert into traces (id, name, type)
+values ('bitbrains-small', 'Bitbrains Small', 'vm');
diff --git a/opendc-web/opendc-web-server/src/main/resources/import.sql b/opendc-web/opendc-web-server/src/main/resources/import.sql
deleted file mode 100644
index 756eff46..00000000
--- a/opendc-web/opendc-web-server/src/main/resources/import.sql
+++ /dev/null
@@ -1,3 +0,0 @@
-
--- Add example traces
-INSERT INTO traces (id, name, type) VALUES ('bitbrains-small', 'Bitbrains Small', 'vm');