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 receives the database settings and stored-secret encryption keys. It has no provider credentials or runtime role. 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/next/YYYYMMDDHHMMSS-<branch-slug>.xml. The filename uses UTC. Change set ids keep the millisecond timestamp, numbered -1, -2, …, and author hephaestus.

    A branch gets one such file. The first run creates it after the latest existing timestamp. Later runs add change sets to it.

    Liquibase discovers it through includeAll. The command does not edit master.xml. 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 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.

If a global table has a workspace_id, also record why in GLOBAL_NAMING_A_WORKSPACE in the test. SQL tenancy enforcement does not check a global table, so its workspace_id gives no isolation.

The provider mirror tables in WorkspaceScopedTables.MIRROR_TABLES have no workspace_id, because more than one workspace shares each row. A read of a mirror table binds the workspace through the monitored scope (ADR 0077). Add a mirror table only for a new provider object that more than one workspace can monitor.

A mixed-scope table has a nullable workspace column and remains under SQL tenancy enforcement. The test records each one in MIXED_SCOPE_TABLES, with its reason, and requires that the table remains scoped:

  • provider_event: a GitHub App installation event can arrive before its workspace exists. The installation queries use @WorkspaceAgnostic, with an explicit workspace_id IS NOT DISTINCT FROM predicate.
  • config_audit_event: the column is NULL only for a curated practice change, which belongs to the instance.
  • product_feedback: the column is NULL only for product feedback sent outside a workspace.
  • llm_usage_event: the column is NULL only for an instance product feedback draft or a synthetic instance model probe. ck_llm_usage_event_scope enforces this source and funding scope.

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.

Trigger timing​

A trigger that only validates an UPDATE is an AFTER UPDATE row trigger. Some tables have a BEFORE UPDATE row trigger. On such a table, PostgreSQL locks the row FOR UPDATE if an UPDATE names a key column in its SET list. This occurs also if the value does not change, and also if the trigger is limited to other columns. ExecUpdateLockMode in the PostgreSQL executor sets this lock mode.

The FOR UPDATE lock blocks each foreign-key write that refers to the row until the transaction ends. Without such a trigger, an UPDATE that does not change a key column takes FOR NO KEY UPDATE. That lock does not block those writes.

GuardTriggerTimingDatabaseTest keeps a BEFORE UPDATE trigger only where it changes the row, orders locks, or blocks no write. The test records the reason for each one. INSERT and DELETE guards are not affected and stay BEFORE. 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, author and runInTransaction.

Promotion preserves an explicit runInTransaction="true" or runInTransaction="false". Use false for a concurrent index change set.

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. Keep master.xml fixed. It holds the 1.0 baseline and one includeAll, which reads XML files directly from db/changelog/next in filename order. Name new files YYYYMMDDHHMMSS-<slug>.xml in UTC. A new file must sort after every migration already on the base branch. The generator chooses the next timestamp when the current second is already used. After a rebase, run vp run db:reorder. It gives this branch's out-of-order files a timestamp after the newest file on main and in the merge queue. It uses git mv and keeps their content. It refuses to rename a file that origin/main or a release tag has. The pre-push hook and vp run db:reorder --check find an out-of-order file before the merge queue does. Both also compare with the migrations that pull requests already in the merge queue add. Reset a local database that applied the moved file with vp run dev:reset. Do not add explicit includes or nested directories. The immutability guard rejects invalid filenames and insertion before existing history. The changelog-level precondition protects startup and synchronization without changing released changesets.
  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.

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 baseline and subsequent migrations follow the same immutability policy. The database prerequisites define the supported upgrade history.

Expand and contract​

These rolling rules apply from 1.0.0 on. Before 1.0.0, the stopped-cutover waiver in ADR 0070 permits replacement and removal in one change. ADR 0096 keeps this cutover and the 1.0.0 boundary. A change that uses the waiver ships a Stop every role migration note and does not run old and new roles together.

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.

Persisted messages need the same ordering as schema objects. A queue consumer claims only the action and payload kinds that its build understands. An unknown kind stays pending for a compatible consumer. It does not spend an attempt or fail. Once the preceding release has this filter, a new kind can add its readers and producers in one release. For a replacement that the preceding consumer cannot safely ignore, prepare readers before changing writers:

  1. Release N adds the readers, erasure handling and supported-kind claim filter. Keep the existing producers, notice kinds and queued messages unchanged. Do not write a new kind while a replica from before N can claim it.
  2. Release N+1 activates the new producers after every replica runs N or later. Keep both readers during this roll.
  3. A later release removes the old reader and closes old queued copies only after no old producer remains. A cleanup in the activation release can race a producer from N that is still draining.

State this order in each release changeset. Prove that an unsupported queued kind stays unclaimed, and that the preceding release can still write and read after the expansion.

The changelog compatibility guard checks each changelog that the latest release tag does not have. That tag is the latest published release merged into the head, a release candidate such as v1.0.0-rc.1 included, but not a tag on the head itself. The guard does not count the baseline files under 1.0.0/ as new against v1.0.0-rc.1 or later. A database on such a release records the baseline without running its DDL. 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 Generated API. 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 released history and new migration order. 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 change after it reaches the base branch. The one replacement was the only exception. New migration files must have a valid UTC timestamp and sort after that branch’s existing migrations. The <include> of the baseline and the final includeAll cannot change. New explicit includes also fail the check. A prohibited change fails the check.
  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 upgrades the previous supported release. Until 1.0.0 publishes, that release is 1.0.0-rc.1. It compares the fingerprint of the upgraded schema with a fresh install. It checks readiness, seeded relationships, the practice catalog and authenticated reads. Release publication depends on this upgrade. The same procedure runs weekly. A merge group runs it on its own images when it changes a changelog or BaselineSynchronization. Thus, a migration that breaks the upgrade fails before it merges. Previous release on the new schema describes its other checks.

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 two ways:

  1. Previous server on the new schema. The previous release starts while the candidate runs. It runs the server and its agent runtime 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. It claims a queued review through the native claim queries and writes the job back.
  2. Previous server 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. 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 previous release 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.

The 1.0 baseline​

Hephaestus 1.0 starts from one baseline. db/changelog/1.0.0/baseline.xml holds the schema of 1.0.0-rc.1. It replaced the 0.x chain once. A database never replays or revalidates anything because of the replacement.

DatabaseWhat the migrate run does
EmptyRuns the baseline, then every change set in next/.
Has the baselineRuns the change sets in next/ that it lacks.
On 1.0.0-rc.1Compares the schema fingerprint with db/fingerprint/baseline-<context>.txt. If they are equal, records the baseline change sets as run, without DDL, then runs next/. If they differ, refuses and writes nothing.
Past 1.0.0-rc.1 on the old chainRefuses and writes nothing: a change set from a later next/ file is in its history, so the fingerprint cannot judge its schema.
OlderThe guard in master.xml refuses and writes nothing. The operator installs 1.0.0-rc.1 first.

A database on 1.0.0-rc.1 keeps its older history rows. Liquibase ignores them, and 1.0.0-rc.1 needs them after a rollback. BaselineSynchronization runs under the migration lock in the migrate run only. A local database that migrates at start stops at the baseline's precondition. Reset it with vp run dev:reset.

db/schema-fingerprint.sql gives one sorted line for each object that the changelog decides. It leaves out what no changelog decides: runtime partitions, sequence positions, everything that an extension owns, and the names of not-null constraints. pg_dump and pg_upgrade restore some expressions in another form, so a list of varchar literals and a BETWEEN check each print as one form. A database that went through either matches its own release. The synchronization also admits an extension that an operator added, such as pg_stat_statements. A missing or different extension still refuses.

The pins come from databases that the published 1.0.0-rc.1 image built. SchemaFingerprintDatabaseTest requires that the baseline builds exactly those lines. The baseline's rows are not in the fingerprint, because administrators change them. LiquibaseBaselineIntegrationTest checks them on a fresh install.

What the replacement was allowed to do​

scripts/check-changelog-immutability.ts admitted the replacement once. It admitted a change only when all of these were true:

  1. The base's master.xml included the v0.77.4 baseline. This is false after the replacement, so the exemption cannot run again.
  2. The head's master.xml has exactly one include, the baseline, then the one includeAll.
  3. The change deletes exactly the files of tag v1.0.0-rc.1 under db/changelog/, and each one in the base is identical to the tag.
  4. Every other file in the base stays in the head with an identical blob. A file from next/ that is later than the tag cannot change, move or disappear.
  5. The change adds files only under db/changelog/1.0.0/.
  6. The guard fails if it cannot read the tag.

From 1.0.0 on, a released change set never changes, and master.xml never changes. A future baseline needs a major release, an automated synchronization like this one, and a decision record.

Upgrade and recovery​

The operator migration guide owns the release procedure and rollback.

Resources​