1.3.0 database: one baseline changelog in the backend #123

Goal

One database changelog, shipped inside the backend, creates every SITMUN database in 1.3.0. It is the only place where tables, code lists, task types, users, settings, and translations are defined.

Why

Today five copies of the database definition exist and they no longer agree:

57 code list rows that appear in both the backend and the profiles have different IDs. Translations are linked by ID, so some point at the wrong row or at no row. Tests pass on one copy while installs run another.

What changes

• The changelog lives in sitmun-backend/src/main/resources/db/changelog/ and ships in the backend jar. The Git tag (v1.3.0, then v1.3.1, v1.4.0, and so on) identifies the release. After the baseline, each release has its own file:

  db/changelog/
    db.changelog-master.yaml
    baseline/          tables and shipped data for v1.3.0, never edited after v1.3.0
    releases/
      1.3.1.yaml       every database change in v1.3.1
      1.4.0.yaml       every database change in v1.4.0
    legacy/            the upgrade from a 1.2.9 database (#124)
  

• Demo and test data are not in the backend jar, so a production install cannot load them:

  Data	Lives in	Loaded by
  Demo (applications, territories, services, maps)	profiles/development/backend/liquibase/ in the stack	the development profiles (development*.env). Their master.xml includes the shipped master and then the demo files.
  Test fixtures	sitmun-backend/src/test/resources/db/changelog-test/	backend tests. test-master.yaml includes the shipped master and then the test files. Test resources never enter the jar.

  Production uses the shipped master and gets neither. No Liquibase contexts are needed to keep demo or test data out.

• Demo and test files follow the same rules as release files: changesets are appended, merged ones are not edited, and each file pins its own logicalFilePath (sitmun-demo/<path>, sitmun-test/<path>). Because every file pins its logicalFilePath, shipped changesets keep the same identity whichever master includes them, so a demo database upgrades like any other.

• A demo master outside the jar can include the shipped master with file: classpath:db/changelog/db.changelog-master.yaml. Spring Boot runs Liquibase with SpringResourceAccessor, which resolves classpath: paths, and paths with no prefix, from the application classpath (finalizeSearchPath in Liquibase 5.0.3). Today's install profiles already start from a file: master.

• The .env profiles keep working unchanged. Each profile folder keeps liquibase/master.xml, the path that SPRING_LIQUIBASE_CHANGE_LOG and the compose files already use:

  Profile	liquibase/master.xml contains
  postgres, oracle	one include of the shipped master
  development	the shipped master, then the demo files

  cp profiles/<name>.env .env stays the setup step, and .env files copied from 1.2.9 still point at a file that exists. The stub master sets no logicalFilePath, so included files keep their own. A deployment can add its own changesets after the include in its own master.xml. SPRING_LIQUIBASE_CONTEXTS has no effect, because no shipped changeset uses contexts.

• The master includes the baseline and each release file by name, in release order. It does not use includeAll, because alphabetical order would put 1.10.0 before 1.4.0.

• The next release file is created with the first database change after a release. Pull requests add changesets at the end of it. Changesets already merged are not edited, because development databases may have run them.

• When the release is tagged, its file ends with one tagDatabase changeset named after the release (1.3.1). The baseline ends with 1.3.0. The database then records which release it reached, and a later upgrade can require a minimum release.

• After the tag, the file is frozen. A fix for v1.3.1 goes in the next release file.

• Each file sets a fixed logicalFilePath (sitmun/<path inside db/changelog>). If the planned version changes before the release (for example 1.3.1 becomes 1.4.0), the file is renamed and keeps its original logicalFilePath, so databases that already ran it do not run it again.

• Tables are generated once from a 1.2.9 production database with liquibase generate-changelog, then reviewed by hand. One schema step per database (H2, PostgreSQL, Oracle). SQL stays compatible with Oracle 19c.

• The schema matches the Hibernate mapping, so these drift reports close: Widen production columns that are narrower than the Hibernate mapping #92, Align production nullability with the Hibernate mapping #93, Add the Hibernate unique constraint on STM_GEOINFO.GEO_STYID #94, Store service-check timestamps with a time zone #95, Confirm production-only constraints that Hibernate does not declare #96.

• Code lists hold every value the code depends on, marked as system values so the admin cannot delete them. JDBC drivers are offered as values an admin can delete. service.type is only WMS, WFS, and WMTS (Restrict service.type to WMS, WFS, and WMTS on every profile #101).

• Translations use the same IDs as the rows they translate.

• Languages: the five current languages ship, with English as the default. A deployment switches the default once with the existing /api/language-default/change, disables languages it does not want, and adds new ones in the admin. Nothing in the baseline deletes a language.

• Removed: the backend config/db/changelog folder, the backend test changelog copies and the Gradle step that copies them, everything under profiles/*/liquibase/ except each master.xml and the development demo files, and the code list and translation generators in tools/. The old files stay available in the 1.2.9 tags.

Done when

• Backend tests on H2, PostgreSQL, and Oracle run the shipped changelog plus the test fixtures. No copy exists.
• The backend jar contains no demo or test changesets (checked in CI, 1.3.0 database: CI checks that stop drift #126).
• The demo deployment starts with demo data. A production install has none.
• CodeListsTest passes on a fresh database with no startup writer (1.3.0 database: stop writing code lists at startup #125).
• A fresh install on PostgreSQL and Oracle starts and the root E2E suites pass.