Database Schema Management — PostgreSQL on Azure (CTN ASR)

This is the Azure-concrete companion to the platform-neutral Database Schema Management principles. The CTN stack is PostgreSQL + Quarkus (see ADR-00002), so schema is managed with Liquibase (TDR-0001), not with SQL Server / SSDT or ORM auto-DDL.

Recommendation

Use Liquibase (built into Quarkus) with YAML changesets kept in version control. Do not let Hibernate generate or update the schema (hibernate-orm.schema-management.strategy stays none/validate in every profile), and do not manage schema through an ORM’s migration feature.

Context

The CTN Association Register (ASR) database runs on Azure Database for PostgreSQL — Flexible Server and is consumed by multiple parties:

• Admin Portal and Participant Portal (via the ASR API — they never talk to the database directly)
• ASR API on Kubernetes/AKS (the single writer of the schema)
• Potentially external integrations via the API

The ASR API owns the schema definition; every other consumer reaches data through the API, not through its own ORM.

A second driver shapes the tool choice: the dataspace model is federated, and federated operators are expected — much later — to run their own ASR on the DBMS of their choice. Migrations must therefore not lock the schema history to the PostgreSQL dialect (see TDR-0001). Only PostgreSQL is supported and CI-tested today.

Why Liquibase with YAML changesets

Aspect	ORM auto-DDL (e.g. Hibernate update)	Liquibase YAML changesets
Schema definition	Inferred from Java entities	Explicit, declarative changesets
Deployment coupling	Tied to the app’s ORM	Versioned, deliberate step
Reviewability	Requires reading Java + ORM conventions	Declarative YAML, reviewable by anyone
Destructive changes	Silent / unpredictable	Explicit, reviewed per changeset
Audit trail	Hidden in entity diffs	DATABASECHANGELOG + Git history
Multi-DBMS portability	Per-dialect DDL generation, unreviewed	Portable constructs rendered per dialect; non-portable DDL isolated in dbms-scoped changesets
Rollback	None	Forward-fix changesets + verified backups (no rollback blocks authored — see TDR-0001)

Flyway plain-SQL was the original proposal and remains a fine tool, but every migration would be PostgreSQL dialect throughout; a second DBMS would mean a parallel migration history per dialect, rewritten under an operator’s deadline. Liquibase writes portable constructs once. See TDR-0001 for the full trade-off.

Approach

• Changelogs live in the repo under src/main/resources/db/changelog/: changelog-master.yaml lists explicit include entries in execution order; one YAML file per migration unit, with one changeset per table/index/logical change. They are reviewed in pull requests like any other code. Applied changesets are never edited — corrections are new forward changesets.
• Contexts are always pinned: Liquibase runs context-tagged changesets when no runtime context is given, so every Quarkus profile pins one (dev/test/prod), plus an unprofiled prod default for custom profiles. The dev demo seed is a context: dev, runOnChange changeset and therefore exists only in dev databases.
• Apply at boot only in dev/test: quarkus.liquibase.migrate-at-start is true in dev/test (ephemeral databases) and false in prod.
• Apply via init container in production: the application process never migrates. A CI-built migrations image (FROM liquibase/liquibase with the changelog tree copied in, tagged in lockstep with the app image) runs liquibase update --contexts=prod as a Kubernetes init container before the app container starts. Concurrent replicas serialize on DATABASECHANGELOGLOCK; already-applied changesets no-op. The --contexts=prod flag is mandatory — a context-less run would apply the dev seed.
• Privilege split: the init container connects with a DDL-capable PostgreSQL role; the application role is DML-only. Read-only consumers get a dedicated role. Only the migration role may alter the schema.
• Destructive changes use expand-contract (add column → backfill → switch → drop in a later release), never an in-place rename/drop in one step.

Type mapping — the hybrid rule

This table is the canonical home of TDR-0001’s hybrid type rule. Changesets use Liquibase logical types where the dialect mapping is trustworthy, and verbatim PostgreSQL types where it is not:

Write in the changeset	Renders on PostgreSQL	Renders on e.g. MSSQL	Why
uuid (logical)	uuid	uniqueidentifier	Trustworthy mapping
boolean (logical)	boolean	bit	Trustworthy mapping
timestamp(6) with time zone (logical)	timestamptz(6)	datetimeoffset	Trustworthy mapping; precision preserved
text (verbatim)	text	(decide at port time)	Deliberately not clob: Liquibase’s clob can render as oid/LOB on PostgreSQL, breaking Hibernate mapping parity

Two construct rules ride along:

• Check constraints are raw sql changes (ANSI ALTER TABLE … ADD CONSTRAINT … CHECK, un-scoped, inside the owning table’s changeset): Liquibase OSS silently drops the checkConstraint column attribute on createTable, and addCheckConstraint is Pro-only. Constraint names are spelled explicitly to match what PostgreSQL derives for Hibernate’s inline checks.
• Non-portable DDL is a raw sql changeset scoped with dbms: — e.g. the partial unique index uq_onboarding_open_kvk (dbms: postgresql). A future DBMS adds a sibling changeset with the same constraint/index name, since application code matches names when classifying violations. The porting surface is enumerable: search the changelog for dbms:.

The rendered PostgreSQL DDL mirrors what Hibernate would generate from the entities, so the planned graduation to hibernate-orm.schema-management.strategy=validate passes without drift.

Azure specifics

• Service: Azure Database for PostgreSQL — Flexible Server (PostgreSQL 15+), reached over a private endpoint.
• Backups: geo-redundant, point-in-time restore; a verified backup is a precondition for any destructive changeset.
• Secrets: the migration and runtime credentials are two distinct Key Vault secrets (DDL role for the init container, DML role for the app), injected via Quarkus config / pod env — never committed.
• CI/CD: the pipeline builds the app image and the migrations image from the same commit; the init container applies the changelog before the new application pods become ready.

Cloud portability

Portable changesets are rendered per dialect by Liquibase, so the same db/changelog/ tree runs against PostgreSQL on AWS (RDS/Aurora), GCP (Cloud SQL) or a self-hosted cluster — and is the basis for a future second DBMS when a federated operator names one. Database-specific DDL is isolated in dbms-scoped changesets. See the platform-neutral principles for the full workflow, dangerous-operation table and data-migration patterns.

Conclusion

For CTN’s PostgreSQL + Quarkus stack, Liquibase with reviewed YAML changesets gives explicit, auditable, dialect-portable schema management with a clear owner (the ASR API), a privilege-split apply path in production, and a bounded porting surface for the federated multi-DBMS future — without coupling the schema to an ORM or to SQL Server tooling.