Skip to main content

Database Migration Workflow

Liquibase manages schema changes. In production, the migrate run mode applies them before a release starts. That process has only the database settings, so a change set cannot read encryption keys or other secrets. This page is the procedure. The entity conventions the drift gate reads are in server/AGENTS.md § Schema changes.

Quick start​

  1. Modify the JPA entities.

  2. Write the drift into this branch's changelog:

    For a stacked PR, set GITHUB_BASE_REF to its parent branch. The generator writes this layer's changelog without changing its parent's migration.

    vp run db:draft-changelog

    The command applies the whole chain to a disposable database. It compares the result with the JPA model. It writes the difference as change sets into server/application/src/main/resources/db/changelog/<timestamp>_changelog.xml. These use ids <timestamp>-1, -2, … and author hephaestus.

    A branch gets one such file: the first run creates it and appends its <include> to master.xml. Later runs add change sets to it. It then refreshes the ERD. With no drift it writes nothing.

  3. Review the change sets. Keep the real deltas and remove what the diff only inferred. Prefer renameColumn to a drop-and-add pair. Unless there is a data migration plan, start new sequences at 1. Follow the destructive-change policy below. Use preconditions to reject incompatible database states. Use onFail="MARK_RAN" only when failure proves the intended change is already present. Provide a tested rollback when reversal is safe. Otherwise document recovery through a verified backup in the operator instructions. If you edit the changelog, refresh the ERD:

    vp run db:generate-erd-docs

  4. Add a release changeset: vp exec changeset, or hand-write .changeset/<slug>.md (.changeset/README.md). A Liquibase <changeSet> and a release changeset are different things and a schema change needs both. verify-changesets fails a PR that touches db/changelog/ without one. Keep the summary user-facing. The release notes flag the migration automatically.

  5. Commit the changelog, the master.xml edit, the changeset, and docs/contributor/erd/.

Workspace column​

A new table carries a NOT NULL workspace_id. WorkspaceTableColumnIntegrationTest checks every table of the migrated schema. A table that belongs to no workspace is a security decision:

  • If an entity maps it, add the table to WorkspaceScopedTables.GLOBAL_TABLES and the entity to DataIsolationArchitectureTest.GLOBAL_ENTITIES.
  • If no entity maps it, add it to GLOBAL_WITHOUT_ENTITY in the test.

The test also lists the tables from before this rule. Do not add to that list.

Native objects the JPA diff does not cover​

The JPA diff does not cover native CHECK constraints or triggers in this repository. For such a change, write the change set as a draft in an ignored scratch directory, then promote it through the same command:

node scripts/db-utils.ts draft-changelog --from-draft .context/<name>.xml

The draft is one <databaseChangeLog> with only change sets. Promotion writes these into this branch's changelog with the usual ids and author. It replays the whole chain on a disposable database and refreshes the ERD, as above. It refuses a draft with any of these properties:

  • Malformed XML.
  • A DOCTYPE declaration.
  • Content outside a change set.
  • Root attributes other than the standard Liquibase and XML Schema namespaces and xsi:schemaLocation.
  • Change-set attributes other than id and author.

Promotion would drop unsupported attributes. State conditions as preConditions inside the change set. Review the result as in step 3. Add the database-level test required by the paragraph on populated tables below.

Data corrections​

If a fix leaves wrong rows, correct them in the code that writes them. The next sync or event then repairs them. Only rows that nothing will write again get a hand-written <sql> change set. Merge requests that incremental sync never reads again are an example. Put this change set in the branch's single changelog. Include these safeguards:

  • A comment that states which rows are wrong and why.
  • A HALT precondition on the columns it reads.
  • A <rollback><stop> that names the backup to restore.

A data correction needs maintainer approval in the pull request.

Changelog immutability policy​

The Liquibase changelog chain is the upgrade path: every production and self-hosted instance upgrades by replaying exactly these files, in master.xml order. That works only if history never changes underneath an instance that already ran part of it.

  1. Released changesets are never edited. A changeset is released once it reaches main — from that moment it may already be applied to a production database. An edit causes checksum errors at boot on upgraded instances. Fresh installs silently get a different schema. Fix mistakes forward with a new changelog file. Liquibase excludes preconditions, contexts and labels from checksums. It ignores deleted changesets. Thus, those edits make installed instances inconsistent without an error.
  2. master.xml is append-only. Never remove or reorder existing <include> entries. New changelogs go at the end. The maintainer-approved v0.77.4 cut-point guard is a changelog-level precondition at the head, not a change to released changesets. It protects both startup and changeLogSyncToTag without changing checksums or include order.
  3. Release N runs correctly on the schema of release N+1. A single release must stay bootable against the schema left behind by the previous one. The migrate run mode migrates before the rollout, so the old roles run on the new schema until they stop. After a rollback or a failed migration, the previous release also runs on that schema. Expand and contract gives the rules. An ADR can grant an exception: see Practice model cutover and Conversation ledger.

CI enforces rules 1 and 2. For rule 3, the compatibility guard rejects the change sets that Expand and contract lists. The seeded release upgrade also runs the previous release on the new schema. CI validation describes both checks. Review covers what neither checks.

The sole archival transition is the verified v0.77.4 baseline: archived files remain byte-identical and test-only. The baseline and subsequent migrations follow the same immutability policy. Existing installations must follow the baseline synchronization runbook.

Expand and contract​

A change that the previous release cannot run beside takes two releases. Release N stops reads and writes, and migrates data if necessary. It adds the object to the pending contractions. A later release drops the object. To rename a table or column, add the new one, copy the data, and drop the old one later.

The changelog compatibility guard checks each changelog that the latest release tag does not have. It rejects these change sets and files:

  • A dropTable or dropColumn, unless the latest release lists the object in its pending contractions.
  • A renameTable, renameColumn or modifyDataType.
  • An addNotNullConstraint on a column of the previous release. That release can write NULL into it.
  • A new NOT NULL column without a default value. The inserts of the previous release do not set it.
  • An addUniqueConstraint, addForeignKeyConstraint or unique createIndex on a table of the previous release. The same applies to a unique or foreign-key constraint inside the <column> of an addColumn.
  • A foreign key without onDelete CASCADE or SET NULL, also on a new table, if it references a table of the previous release. That release can delete a referenced row.
  • A dropIndex or dropUniqueConstraint on a table of the previous release. Its upserts can need the index.
  • Raw <sql> with DROP TABLE, DROP COLUMN, ALTER TABLE … RENAME, ALTER … TYPE or SET NOT NULL.
  • Raw <sql> with ADD [COLUMN] … NOT NULL and no DEFAULT.
  • A <sqlFile>, and a changelog file that is not XML. The guard cannot read them.

Except for the foreign-key delete rule, these rules do not apply to a table that a new changelog creates. The constraint rules also do not apply to new columns without a default value, because the previous release leaves them NULL. The guard reads each change set alone, not the order of change sets.

Liquibase 5.0.x has no addCheckConstraint change type. Review a raw CHECK constraint. The guard also does not read a raw CREATE UNIQUE INDEX or ADD CONSTRAINT. Review these too. Write a contraction with a Liquibase change type, so that the guard can read the object.

A change set that fails rolls back. Thus, a failed migration stops at a change set boundary between the two releases. Each change set obeys the rules, so the previous release also runs on that partial schema.

Liquibase 5.0.x commits a change set before it writes its history row. If the process stops between the two, the next run applies that change set again. A change set with runInTransaction="false" also leaves its completed statements when it fails. Where you can, write a change set so that it can run again, for example with IF NOT EXISTS.

A release that cannot obey these rules ships a .migration fragment whose heading starts with #### 🔴 Stop every role. The waiver covers the whole release. The guard then permits all its change sets. The seeded release upgrade then does not start the previous release on the new schema. Both print a warning that names the entries and says that this coverage is off for the release. Rollouts and rollback gives the operator procedure for such a release.

Pending contractions​

Objects the application stopped writing, to be dropped in a release after the one that shipped the listed changelog. Each is excluded from the drift gate in --exclude-objects (server/application/build.gradle.kts, liquibaseDiff). The compatibility guard reads this table from the latest release tag. Thus, it permits a drop only after a release has listed the object. The changeset that drops the object also removes its row and its exclusion.

The 1.0 cutover dropped every earlier pending contraction, so the table is empty.

ObjectChangelog that stopped writing itExclusion to remove

CI validation​

  1. Migration-chain replay, schema drift and ERD — CI / Build workflow, job App Server: Generated artifacts. vp run db:check-drift applies the full master.xml chain to an empty database from the repository's PostgreSQL image (docker/postgres). It compares the result with the JPA entities. A changelog that does not parse or apply fails here. vp run db:generate-erd-docs then has to leave docs/contributor/erd/ unchanged.
  2. Changelog immutability — Security workflow, job Dependencies, secrets, and policy, step Changelog immutability guard. A git diff from the event's base commit checks two conditions. Pull requests and merge groups both run it. A pull request run does not repeat when its base advances. Thus, the merge group judges migrations that reached main after that run. No file under db/changelog/ can have a modification, rename, or deletion. No existing <include> in master.xml can have a removal, order change, or attribute change. Either prohibited change fails the check. The v0.77.4 transition also requires the pinned original master and byte-identical archives. It is not a general squash bypass.
  3. Changelog compatibility — Security workflow, same job, step Changelog compatibility guard. scripts/check-changelog-compatibility.ts applies Expand and contract. It checks the changelogs that the latest release tag does not have. It reads the tags, so the job checks out the full history.
  4. Seeded release upgrade — release-upgrade.yml runs three independent paths. One upgrades the previous stable release. One follows pinned v0.77.4 → baseline synchronization → target release. One follows pinned v0.76.0 → expected refusal. Successful upgrades check readiness, seeded relationships, the practice catalog and authenticated reads. The refusal path checks both startup and changeLogSyncToTag. It checks the required v0.77.4 message, unchanged data and history, and a released lock. Release publication depends on every path. The same matrix also runs weekly. Previous release on the new schema describes the other checks of the first path.

The empty-database replay sets no context filter. It evaluates the full chain, but preconditions can skip changesets. A successful replay does not prove that every change ran. It also does not reproduce the context selection of a dev or prod boot (spring.liquibase.contexts).

Add focused migration tests for populated tables, rejected database states and any supported rollback. Empty-database replay cannot prove that existing rows satisfy a new NOT NULL or CHECK constraint, and it does not exercise rollback. Preconditions run during replay, but only against the states that replay reaches. The JPA diff cannot establish equivalence for objects Hibernate does not model, such as triggers, partitioning and partial-index predicates. Verify those through database-level tests.

Previous release on the new schema​

After the upgrade from the previous stable release, the driver runs the previous release in three ways:

  1. Server and worker roles on the new schema. The previous release starts while the candidate runs. It runs both roles in one process, with the Docker sandbox and its own agent image. The e2e profile validates every entity against the schema at start. The previous release then passes readiness and the authenticated reads, and creates a workspace. Its worker claims a queued review through the native claim queries and writes the job back.
  2. Server and worker roles on a partial schema. An event trigger stops the candidate's migration of a copy at a DDL statement. The migration stops after about half of the new change sets. The previous release passes the same checks on that copy, including the claim. Then the migration runs again and must complete. If no later change set runs DDL, the migration completes. The log then says that no partial schema exists.
  3. Webhook role. The webhook receivers of both releases publish the same GitHub and GitLab delivery to NATS. Apart from the delivery id, the two stream messages must have the same subject and body. The candidate must keep each header of the previous release with its value. It can add a header.

The webhook receiver reads no database (ADR 0066). Its risk is the NATS contract with the server of the other release: the message headers and the key-value buckets that the server projects. Each release tests its own receiver against its own consumer. Thus, the server of the previous release reads the messages of the candidate's receiver. The test does not prove that the candidate's server reads a message without a header that the candidate added.

Hibernate does not validate native queries, such as the claim queries of the review queue. Thus, the driver queues one review the way the candidate's enqueue path writes it. The review comes from the candidate's practice-native/result-parent.sql fixture. The practice pins a review method with every model slot, and the job freezes the bound model with its price. The review item points at the job. If the fixture does not fit a partial schema, the error says so.

The previous release creates the AI model, its price and the workspace binding through its own API. The model points at an endpoint that nothing answers. The worker claims the job, sets itself as its owner and starts it. The attempt then fails while it captures the reviewed source, which is the expected end. Conversation turns are not claimed.

Only the container of the previous release gets the Docker socket. The driver labels each sandbox resource that this container starts, and removes them all at the end.

If the candidate adds fewer than two change sets, no partial schema exists, and the log says so. If a migration guide entry of the candidate stops every role, the previous release does not start. The log names the entry in a warning.

Baseline release gates​

The practice model cutover retains the v0.77.4 baseline. A future baseline requires both a major-version boundary and automated synchronization. Released migrations and the archived chain remain immutable.

A global Liquibase precondition checks the database cut-point before either update or synchronization. Liquibase permits one global preConditions container, so the guard shares it with the PostgreSQL 18 check. Its failure message covers both prerequisites. Migration identities use id, author and logical filename, not just id or row count. An empty history is permitted for fresh initialization. The baseline's existing schema precondition still refuses a partially populated schema.

Liquibase preconditions use lazy evaluation, so a missing history table never reaches the row query. Liquibase 5.0.4 validates the changelog before changeLogSyncToTag. Changeset-level preconditions cannot protect that command. The baseline integration suite proves these properties in both dev and prod:

  • Refusal without DDL.

  • Fresh initialization.

  • Native-object equivalence.

  • Synchronization without DDL or data loss.

  • Subsequent upgrades.

  • Lock recovery.

  • Record a seven-calendar-day staging soak against a restored production snapshot before production.

  • Freeze new changelogs for at least 48 hours after production deployment.

  • Keep docs/db/archive/v0.77.4/ for at least two release cycles. Historical regression tests use it only as test resources. It must not enter the application JAR.

  • Announce the cut-point and synchronization requirement before merging. Developer reset: vp run dev:reset, then vp run dev. This destroys local database data.

Practice model cutover​

The practice model cutover is a maintainer-approved exception to rule 3 of the immutability policy. One changelog transforms the complete released schema directly to the final practice model. Rules 1 and 2 still apply: the changelog appends and edits no released changeset. Neither release can run against the schema of the other, so all components upgrade together and rollback restores a verified backup. ADR 0056 owns the decision. The operator migration guide owns what the upgrade preserves.

Removed settings and diagnostic data​

Released dataCutover behavior
workspace.practice_skip_draftsReviews include draft work when a practice's reviewOn declaration permits it. practice_skip_drafts is removed. The migration preserves each pinned practice version.
consent_decision.notice_sha256The duplicate hash is removed. Each decision keeps its immutable notice_version, which names the release that published the wording. 1.0 drops the consent_notice archive.
Legacy job diagnostics: container_logs, prompt_digest, inputs_digest, model_labels, and unmapped metadataThese database values are not copied into the new practice records. Retain the pre-upgrade backup when this diagnostic history is needed.

Conversation ledger​

The Conversation ledger is a second maintainer-approved exception to rule 3 of the immutability policy. One changelog, 1792397538973_changelog.xml, drops the released Hephaestus chat tables and creates the ledger. It clears and renames the message link of earlier conversation copies. Rules 1 and 2 still apply. Stop every role before the upgrade. ADR 0057 owns the decision and what the upgrade drops.

On the upgrade from the previous stable release, scripts/release-upgrade-test.ts seeds Hephaestus chat history and conversation copies. After the upgrade, it checks them (server/application/src/test/resources/db/heph-upgrade/).

Prove the released-state upgrade​

Seed the schema at the complete released changelog tail before the cutover changelog. Use realistic results, unclaimed people, disabled identities, separate cards and uncertain provider writes. Run Liquibase, without Hibernate creating the final schema. Assert preserved IDs, tenant relationships, exact receipts, actor redaction and current-claim order. Check native constraints, partial indexes and source-person erasure. Fresh and upgraded databases must have the same final schema.

The drift gate alone cannot prove data migration or native database behavior. The generated ERD must come from the final migrated schema.

Upgrade and recovery​

The operator migration guide owns the release procedure and rollback.

Native Hibernate JSON cutover​

Five semantic String columns use TEXT: auth_event.details, config_audit_event.old_value, config_audit_event.new_value, project_field.options, and connection_audit.detail. Typed JSONB fields use Hibernate 7.4.5.Final's native Jackson3JsonFormatMapper with Spring's configured JsonMapper.

The forward migration converts each old JSONB value once. SQL null and JSON null become SQL null. A JSONB String becomes its decoded text, including whitespace, Unicode, and backslashes. The JSONB String "null" stays the literal text null. Other JSON values become canonical JSON text.

The conversion does not decode serialized JSON inside a String again. It preserves row metadata and embedded payload hashes. Checksums over the old storage representation can change.

The parent authentication table alteration includes existing and default partitions. The migration resolves the pg_partman template from its native configuration and alters that template too. Unexpected source types or partition/template states stop the migration. The migration does not issue row updates or disable append-only guards. Populated PostgreSQL tests cover conversion, guards, retention, future partitions, export shape, and reruns.

Authentication and configuration HTTP and CSV fields remain Strings. The release migration entry owns the person-data export change. The operator procedure owns shutdown, backup, upgrade, export checks, and recovery.

Resources​