Database migrations

Auto-migration on boot

By default, z4j runs pending migrations on start. This is idempotent and safe to leave on.

Disable with Z4J_AUTO_MIGRATE=false — then run migrations manually.

Manual migration

docker run --rm \
  -e Z4J_DATABASE_URL=... \
  z4jdev/z4j:latest z4j migrate

Or inside an existing container:

docker exec -it z4j z4j migrate

Status

z4j migrate --status

Lists applied / pending revisions.

Bidirectional schema policy

Every schema migration is bidirectional. Every revision ships a working downgrade() function and a CI integration test (TestMigrationRoundTrip in tests/integration/test_migration_pg.py) that proves the round-trip cleanly against real Postgres 18:

upgrade head -> seed sample data -> downgrade base ->
  verify schema is gone -> upgrade head -> verify smoke insert works

The CI job blocks releases. If the round-trip fails, the bidirectional claim is no longer true and we revert the breaking migration before tagging.

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: downgrading drops tables by design. Your row data is gone after alembic downgrade base, not preserved across the round-trip. The contract is bidirectional schema, not bidirectional data. For data-preserving rollback use backup-restore - take a snapshot before upgrading, restore if something is wrong.
• Destructive migrations: a future migration that intentionally drops a column with data CANNOT be reversed by downgrade() without that data being elsewhere. These migrations declare themselves with irreversible = True in their compat dict and exit alembic downgrade cleanly with an operator-facing message pointing at the backup-restore workflow.

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, run migrations from a one-shot job before rolling out the new brain image:

# kubernetes example
apiVersion: batch/v1
kind: Job
metadata:
  name: z4j-migrate
spec:
  template:
    spec:
      containers:
        - name: migrate
          image: z4jdev/z4j:latest
          command: ["z4j", "migrate"]
          env: [...]
      restartPolicy: Never

Then update the Deployment’s image.

Schema reference

See database schema for the authoritative table + column list.

Underlying tool

Alembic (SQLAlchemy 2.0). Revisions live in packages/z4j/backend/alembic/versions/. Each revision includes a descriptive message and never loses data without explicit, commented-out intent.