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 upgrade head

Or inside an existing container:

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

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", "upgrade", "head"]
          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.