# Database Migration Workflow Liquibase manages schema changes. In production, the [migrate run mode](/next/admin/runtime-roles.md#database-migrations) 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-.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`](https://docs.liquibase.com/community/reference-guide-5-0-4/changelog-attributes/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/.md` (`.changeset/README.md`). A Liquibase `` 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](https://github.com/hephaestus-build/Hephaestus/blob/main/docs/decisions/0077-provider-mirrors-are-shared-and-the-monitored-scope-isolates-them.md)). 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/.xml ``` The draft is one `` 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 `` 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 `` 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](https://www.liquibase.com/blog/what-affects-changeset-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](#the-10-baseline) and one `includeAll`, which reads XML files directly from `db/changelog/next` in filename order. Name new files `YYYYMMDDHHMMSS-.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](/next/admin/runtime-roles.md#database-migrations) 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](#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](#expand-and-contract) lists. The seeded release upgrade also runs the previous release on the new schema. [CI validation](#ci-validation) describes both checks. Review covers what neither checks. The baseline and subsequent migrations follow the same immutability policy. The [database prerequisites](/next/admin/compatibility-policy.md#database-baseline-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](https://github.com/hephaestus-build/Hephaestus/blob/main/docs/decisions/0070-1-0-is-a-cutover-release.md#decision) permits replacement and removal in one change. [ADR 0096](https://github.com/hephaestus-build/Hephaestus/blob/main/docs/decisions/0096-1-0-is-one-stopped-cutover-from-1-0-0-rc-1-and-keeps-nothing-for-0-x.md#decision) 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](#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 `` 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 `` with `DROP TABLE`, `DROP COLUMN`, `ALTER TABLE … RENAME`, `ALTER … TYPE` or `SET NOT NULL`. * Raw `` with `ADD [COLUMN] … NOT NULL` and no `DEFAULT`. * A ``, 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](#ci-validation) 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](/next/admin/runtime-roles.md#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. | Object | Changelog that stopped writing it | Exclusion 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](#what-the-replacement-was-allowed-to-do) was the only exception. New migration files must have a valid UTC timestamp and sort after that branch’s existing migrations. The `` 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](#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](#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. | Database | What the migrate run does | | -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Empty | Runs the baseline, then every change set in `next/`. | | Has the baseline | Runs the change sets in `next/` that it lacks. | | On 1.0.0-rc.1 | Compares the schema fingerprint with `db/fingerprint/baseline-.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 chain | Refuses and writes nothing: a change set from a later `next/` file is in its history, so the fingerprint cannot judge its schema. | | Older | The 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](https://github.com/hephaestus-build/Hephaestus/blob/main/MIGRATION.md) owns the release procedure and rollback. ## Resources * [Liquibase documentation](https://docs.liquibase.com/) for change set syntax.