Database migrations

Auto-migration on boot

By default, z4j runs pending migrations on start. It is idempotent, and it is safe whenever only ONE brain process is alive across the migration.

That is a statement about overlapping processes, not about replicas: 1. Kubernetes defaults to RollingUpdate and rounds maxSurge UP, so a single-replica Deployment still starts the new pod before stopping the old one, and auto-migration then runs underneath a live older writer. Set strategy: { type: Recreate }, and apply the same caution to any supervisor that starts a replacement before the previous process exits.

On a multi-replica deployment, turn it off for any upgrade that adds a column the schedule-control path reads. Leaving it on means the first replica to restart migrates while its siblings from the previous release are still writing, which is exactly the mixed-version window described under multi-replica. Set Z4J_AUTO_MIGRATE=false, stop every replica, migrate from a one-shot job, then start the new ones.

Disable with Z4J_AUTO_MIGRATE=false -- then run migrations manually.

Manual migration

The image's entrypoint is already the z4j binary, so whatever you pass after the image tag starts at the SUBCOMMAND. Repeating the binary name there makes the container invoke it twice and fail; begin with migrate. The job also needs the same /data volume and Z4J_SECRET the running brain uses, or management bootstrap refuses to start.

Run it through Compose, not a bare docker run. The shipped files put the brain's state on a named volume (z4j_data for SQLite, z4j_brain_state for PostgreSQL) and the database on a private network (z4j_net) that is not published, and the brain needs Z4J_SESSION_SECRET as well as Z4J_SECRET. compose run inherits the volume, the network and the whole environment; a bare docker run gets a fresh empty volume and cannot resolve the database host. Add -f docker-compose.postgres.yml if that is the file you deploy.

The one-shot upgrade job is documented once, in upgrades, and that is the copy to follow. It is not repeated here on purpose. A one-shot compose run resolves the image the same way the brain service does, so it runs whatever version your Compose file currently points at. Started before you repoint it, the job runs the version you are upgrading FROM, reports "upgrade head" success at that release's own head, and applies nothing. The step that prevents this is selecting and pulling the new image first, and it belongs with the procedure rather than in a second copy that can drift out of step with it.

To migrate inside a container that is already running the new image:

docker exec -it z4j z4j migrate upgrade head

Status

z4j migrate current

Shows the currently applied revision. Use z4j migrate history for the full revision list.

Bidirectional schema policy

Additive schema migrations are bidirectional, and the round-trip is proven against a real PostgreSQL 18 server by TestMigrationRoundTrip (packages/z4j/backend/tests/integration/test_migration_pg.py), which is run before each release:

upgrade to the pre-boundary head -> seed sample data -> downgrade base ->
  verify schema is gone -> upgrade again -> verify smoke insert works

Read the first step precisely: the round-trip runs up to the last revision before the boundary activations, not to the current head. It has to. Those activations refuse to downgrade unconditionally, so a round-trip from the current head could not complete, and a test that claimed to do it would be asserting something the product does not offer. What is proven is that additive migrations reverse cleanly and leave no orphaned tables, indexes, ENUM types or trigger functions behind.

A failing round-trip blocks the release: the bidirectional claim is no longer true for the range it covers, and the breaking migration is reverted before tagging.

Where a downgrade deliberately refuses

Bidirectional is the default, not an absolute. The current line carries one refusal that no operator action clears, and one that depends on what your rows look like.

The schedule-control revision will not drop its columns until it can show that every schedule owned by the z4j scheduler still carries the cadence identity the release you are returning to wrote. Nothing on the running path rewrites that identity, so an installation that upgraded and then ran satisfies it. You declare what your target computes in Z4J_ROLLBACK_TARGET_FINGERPRINT, the migration requires that to equal what the rows already carry, and the downgrade proceeds without rewriting anything. Rows created or re-saved since the upgrade carry a different identity and are refused, naming both values; restoring a pre-upgrade backup under the older release is the way back from there. Absent that declaration a container ceremony governs instead, and it refuses with "rollback compatibility image authority is not finalized", because the compatibility carrier it binds to has not been published. See upgrade and rollback for the procedure, for how to measure the fingerprint, and for what declaring it does and does not prove.

Two other refusals sit in front of that one, and they are worth recognising because their messages are different. A schedule on hold refuses because paused_at is the only record of the hold and dropping it would release every one of them silently. A revoked agent refuses because revoked_at is the durable tombstone older cleanup code relies on. Resuming holds to clear the first only starts those schedules firing again, which is the outcome the guard exists to prevent, so do not release incident holds trying to get past it.

The boundary activations refuse unconditionally and cannot be crossed downward at all.

A refusal names its reason. It is a guard, not a broken migration, and env.py evaluates every planned revision's guard before the first step runs, so a refused downgrade leaves the schema untouched. It is also why the guarantee below is written as "every additive migration" rather than "every migration".

What's guaranteed

• Schema bidirectional: every additive migration (add column, add table, add index, add trigger) round-trips cleanly. pip install forward and back across patch / minor lines works at the schema level without manual intervention.
• Postgres-only artefacts (partitioned tables, GIN/partial indexes, ENUM types, trigger functions) are dropped on downgrade by their corresponding helper. Tested explicitly.

What's NOT guaranteed

• Data preservation through downgrade base: going all the way down drops every table, so your row data is gone after alembic downgrade base. That is what the round-trip above exercises, and it is not what a rollback does. Do not read the round-trip's destructiveness as a property of rollback either: stepping back one revision drops three columns and nothing else, and where it cannot do that without discarding state the older schema has no room for, it refuses instead of dropping anything.
• Refusing migrations: a revision whose downgrade would discard state the older schema cannot hold declares that instead of dropping it. Two forms exist. A revision that can never be reversed sets a module-level DOWNGRADE_REFUSED string. A revision whose answer depends on live rows sets a module-level DOWNGRADE_PREFLIGHT callable that inspects the database. env.py collects both across the whole resolved plan and evaluates them before the first step runs, so a refusal exits non-zero with the reason named and leaves the schema untouched. Nothing stacked above the refusing revision is dropped first.

Postgres extensions are intentionally not dropped

The migration installs pgcrypto, citext, and pg_trgm. Downgrade does NOT drop them, by design: extensions are per-database and dropping one could break other applications sharing the same DB. Leaving them installed costs nothing.

Multi-replica

For N-replica deploys: stop every replica of the old release first, not drain, then run migrations from a one-shot job, then start replicas of the new image. This is not a rolling upgrade. A replica of the older release left running against a migrated database writes change-log envelopes without the columns the new one reads, which ends the scheduler's watch and, because a missed fire is skipped rather than deferred, loses the executions due in that window. See upgrades for the full explanation.

The one-shot job:

# kubernetes example
apiVersion: batch/v1
kind: Job
metadata:
  name: z4j-migrate
spec:
  template:
    spec:
      containers:
        - name: migrate
          # Pin the EXACT version you are upgrading to, and pin the same one
          # on the Deployment below. `:latest` here resolves to whatever is
          # current at the moment the Job runs, which is not necessarily the
          # version you are about to deploy, and a migration you cannot name
          # afterwards is one you cannot reason about in an incident.
          image: z4jdev/z4j:<new-version>
          # In Kubernetes `command` replaces the image ENTRYPOINT, so this is
          # the full argv and correctly begins with the binary. That is the
          # opposite of `docker compose run`, where the entrypoint is already
          # the binary and the arguments start at the subcommand.
          command: ["z4j", "migrate", "upgrade", "head"]
          env: [...]
      restartPolicy: Never

Then update the Deployment to the same tag.

Schema reference

See database schema for the authoritative table + column list.

Underlying tool

Alembic (SQLAlchemy 2.0). Revisions live in packages/z4j/backend/src/z4j_brain/migrations/versions/. Each revision carries a descriptive docstring, and a revision that cannot preserve state on the way down refuses rather than dropping it.