Missing ALTER TABLE migration for audit_log multi-tenant schema (breaks existing deployments upgrading to 0.2.0)

[marcellodesales]

Summary

The 0001_initial_schema.sql migration was rewritten on ~June 29, 2026 (commit 14fba21, "Multi-tenant Buzz relay: community_id as a server-resolved key") to add multi-tenancy support. However, no incremental migration was added (0028_...) to ALTER existing databases. Fresh installs work correctly, but existing deployments upgrading from 0.1.x to 0.2.0 encounter a non-fatal but persistent runtime error on every audit log write.

This then successfully create a relay using the Kubernetes deployment!!! thank you gys!

Finally the full relay working

Error (relay 0.2.0 log, every event ingestion)

{"level":"ERROR","message":"Audit log failed: database error: error returned from database: column \"community_id\" does not exist at line 3722","target":"buzz_relay::state"}

Root cause

crates/buzz-audit/src/service.rs now INSERTs using the new 9-column multi-tenant schema:

INSERT INTO audit_log
    (community_id, seq, hash, prev_hash, action, actor_pubkey, object_id, detail, created_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)

But existing databases created from the original 0001_initial_schema.sql have the old 10-column single-tenant schema. PostgreSQL fails at the first column in the INSERT list, so only community_id appears in the error — but the full mismatch is deeper:

Column in INSERT	Relay 0.2.0 expects	Existing DB has	Issue
community_id	uuid NOT NULL	—	MISSING
seq	bigint	bigint	✅ OK
hash	bytea	varchar(64)	Wrong type
prev_hash	bytea	varchar(64)	Wrong type
action	varchar	varchar(64)	✅ OK
actor_pubkey	bytea	varchar(255)	Wrong type
object_id	text	—	MISSING
detail	jsonb	(column named metadata)	MISSING / renamed
created_at	timestamptz	(column named timestamp)	MISSING / renamed

Additionally the primary key changed from (seq) to (community_id, seq).

Impact

• Relay 0.2.0 starts successfully and serves traffic (event ingest returns HTTP 200)
• All audit log writes silently fail for the lifetime of the deployment
• Compliance and audit trail is broken for existing deployments
• Error is non-fatal so it can be silently missed in production

Reproduction

1. Deploy relay 0.1.x against a fresh database → migrations applied from original 0001_initial_schema.sql
2. Upgrade relay binary to 0.2.0 (:main)
3. Ingest any Nostr event
4. Observe ERROR Audit log failed: ... column "community_id" does not exist in relay logs
5. Query audit_log table — rows from before the upgrade exist; no new rows are inserted

Proposed fix

Add migrations/0028_audit_log_multitenant.sql to migrate existing databases:

-- Align existing audit_log with the multi-tenant schema from 0001_initial_schema.sql rewrite.

-- 1. Add community_id (nullable first for backfill, then constrain)
ALTER TABLE audit_log
  ADD COLUMN IF NOT EXISTS community_id uuid
  REFERENCES communities(id) ON DELETE CASCADE;

-- Backfill existing rows with the default community
UPDATE audit_log
  SET community_id = (SELECT id FROM communities LIMIT 1)
  WHERE community_id IS NULL;

ALTER TABLE audit_log
  ALTER COLUMN community_id SET NOT NULL;

-- 2. Add object_id
ALTER TABLE audit_log
  ADD COLUMN IF NOT EXISTS object_id text;

-- 3. Add detail (backfill from existing metadata column)
ALTER TABLE audit_log
  ADD COLUMN IF NOT EXISTS detail jsonb;
UPDATE audit_log SET detail = metadata WHERE detail IS NULL;
ALTER TABLE audit_log ALTER COLUMN detail SET NOT NULL;

-- 4. Add created_at (backfill from existing timestamp column)
ALTER TABLE audit_log
  ADD COLUMN IF NOT EXISTS created_at timestamptz;
UPDATE audit_log SET created_at = "timestamp" WHERE created_at IS NULL;
ALTER TABLE audit_log ALTER COLUMN created_at SET NOT NULL;
ALTER TABLE audit_log ALTER COLUMN created_at SET DEFAULT now();

-- 5. Convert varchar hex columns to bytea
ALTER TABLE audit_log
  ALTER COLUMN hash TYPE bytea USING decode(hash, 'hex');
ALTER TABLE audit_log
  ALTER COLUMN prev_hash TYPE bytea USING decode(prev_hash, 'hex');
ALTER TABLE audit_log
  ALTER COLUMN actor_pubkey TYPE bytea
  USING CASE WHEN actor_pubkey = '' THEN NULL ELSE decode(actor_pubkey, 'hex') END;

-- 6. Rebuild primary key as composite
ALTER TABLE audit_log DROP CONSTRAINT IF EXISTS audit_log_pkey;
ALTER TABLE audit_log ADD PRIMARY KEY (community_id, seq);

-- 7. Add per-community hash uniqueness index
CREATE UNIQUE INDEX IF NOT EXISTS idx_audit_log_hash
  ON audit_log (community_id, hash);

Note on actor_pubkey: deployments should verify no non-hex data exists before applying step 5:

SELECT COUNT(*) FROM audit_log
WHERE actor_pubkey !~ '^[0-9a-fA-F]*$' AND actor_pubkey IS NOT NULL AND actor_pubkey != '';

Environment

• Relay: block/buzz:main (0.2.0)
• Database: PostgreSQL 16 (Azure DB for PostgreSQL Flexible Server)
• SQLx migrations: 0001–0027 applied; no migration 0028 exists
• Existing audit_log rows: ~300 (all pre-upgrade, none written by 0.2.0)

[Pratikkale26]

The diagnosis here is right — audit writes really are broken on upgraded deployments — but I dug into the proposed 0028 and I don't think it can work as written. Three findings, then a question for maintainers.

1. A new migration would not run on the affected deployments.

BUZZ_AUTO_MIGRATE is opt-in and defaults to off:

// crates/buzz-relay/src/main.rs
let auto_migrate = buzz_auto_migrate_enabled(std::env::var("BUZZ_AUTO_MIGRATE").ok().as_deref());
if auto_migrate {
    db.migrate().await…
} else {
    info!("Skipping database migrations because BUZZ_AUTO_MIGRATE is not enabled");
}

(there's a buzz_auto_migrate_is_opt_in test pinning this). A relay that never runs migrations is precisely how a schema goes stale, so adding 0028 wouldn't reach the deployments in this report unless they also flipped that flag.

2. There's an explicit design stance against putting legacy cutover in startup migrations.

crates/buzz-db/src/migration.rs:

Fresh deployments apply the checked-in SQL files under migrations/. The multi-tenant rewrite owns a clean consolidated 0001; legacy single-tenant cutover/backfill is a separate operator script, not startup migration state.

3. The old audit_log was never created by a migration in the first place.

It came from crates/buzz-audit/src/schema.rs at 2300248d, applied at runtime:

CREATE TABLE IF NOT EXISTS audit_log (
    seq          BIGINT       NOT NULL PRIMARY KEY,
    timestamp    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    event_id     VARCHAR(255) NOT NULL,
    event_kind   INT          NOT NULL,
    actor_pubkey VARCHAR(255) NOT NULL,
    action       VARCHAR(64)  NOT NULL,
    channel_id   BYTEA,
    metadata     JSONB        NOT NULL,
    prev_hash    VARCHAR(64)  NOT NULL,
    hash         VARCHAR(64)  NOT NULL
);

So _sqlx_migrations has no record of that table, and 0001's plain CREATE TABLE audit_log (not IF NOT EXISTS) would fail on such a database regardless of what 0028 says.

---

On the proposed SQL specifically, if it does end up being the direction, a few corrections against the real old DDL above and schema/schema.sql:

• ALTER COLUMN detail SET NOT NULL — the target schema has detail JSONB nullable. Setting NOT NULL diverges from schema/schema.sql, which CI applies with pgschema.
• REFERENCES communities(id) ON DELETE CASCADE — the target has REFERENCES communities(id) with no cascade.
• Three old columns aren't addressed: event_id, event_kind, channel_id. They don't exist in the target schema, so they'd linger as permanent drift.
• Three old indexes aren't addressed: idx_audit_log_timestamp, idx_audit_log_actor, idx_audit_log_channel — the first is on a column the migration effectively replaces.
• prev_hash is VARCHAR(64) NOT NULL in the old schema but nullable BYTEA in the target, so the genesis row needs a decision rather than a straight decode().

There's also a question worth settling regardless of direction: the audit log is a hash chain, and the old rows' hash/prev_hash were computed over the single-tenant column set. Converting the storage type preserves the bytes but not necessarily chain verifiability across the cutover boundary — so "does the chain need to verify across the upgrade, or does the cutover start a new chain?" probably needs an answer before any conversion is written.

---

Question for maintainers — which direction do you want?

1. An operator script under scripts/, consistent with the stance quoted above.
2. Relax that stance and accept a startup migration (which then also needs the BUZZ_AUTO_MIGRATE interaction resolved).
3. Neither — a startup preflight that detects the legacy audit_log shape and fails loudly, instead of an ERROR line per ingested event that's easy to miss in production. Arguably the worst part of this bug is that a tamper-evident audit log silently stops recording while the relay keeps serving traffic.

Happy to implement whichever you prefer — I have a local repro of the old schema and can test the conversion against it. I just didn't want to write a data migration for an audit table in a direction the project has already decided against.

[marcellodesales]

Thank you @Pratikkale26 ... since this was a Kubernetes deployment backed by an Azure set of services, let me know what you need on my end... I have the migration scripts as Kubernetes Jobs that may help others...

[Pratikkale26]

Thanks @marcellodesales — that helps a lot, and a real affected deployment is exactly what this needs.

Three things would settle the open questions, all read-only:

1. How the DB got here — this decides whether a startup migration could ever reach deployments like yours:

SELECT version, success, description FROM _sqlx_migrations ORDER BY version;

(If that table doesn't exist at all, that's itself the answer.)

2. Whether migrations are even running — is BUZZ_AUTO_MIGRATE set in the relay's environment? It's opt-in and defaults to off, so a relay that never runs migrations is one way a schema goes stale and stays stale.

3. The actual current shape, so any conversion is written against your real columns rather than my reconstruction from git history:

\d audit_log

And yes — please do share the Kubernetes Job scripts. Given the stance in crates/buzz-db/src/migration.rs that legacy single-tenant cutover belongs in a separate operator script rather than startup migration state, what you've already built in anger may be much closer to the shape maintainers want than the 0028 migration in the issue body. If you're happy for it to be upstreamed, I'm glad to help turn it into a reviewed scripts/ contribution with credit to you.

One caveat worth stating up front, since it affects what the script should even do: your existing audit_log rows carry hashes computed over the single-tenant column set. Converting the column types preserves the bytes but not necessarily chain verifiability across the cutover boundary. So "must the chain verify across the upgrade, or does the cutover legitimately start a new chain?" is probably a maintainer call before anyone writes the conversion — it's the difference between a straightforward ALTER/decode() and something that has to re-anchor the chain.

[marcellodesales]

🙌 Sharing our full Kubernetes deployment context + workaround Jobs

Thanks @Pratikkale26 — you asked three questions, here are the answers from our live deployment, plus the Job YAML that got us unblocked.

---

1️⃣ How the DB got here (_sqlx_migrations)

We ran relay 0.1.x first. The original audit_log was created by crates/buzz-audit/src/schema.rs — not by a SQLx migration — so _sqlx_migrations has no record of it. When we upgraded to 0.2.0, the relay ran with BUZZ_AUTO_MIGRATE=true and applied migrations 0001–0027 successfully. Those migrations don't touch the existing audit_log (correct, since 0001 uses a plain CREATE TABLE, not IF NOT EXISTS). The result: 0001–0027 in _sqlx_migrations, and a stale audit_log from the old runtime schema.

There was a secondary complication: our first migrate job (pre-0.2.0 upgrade) ran openssl dgst -sha384 inside a postgres:16-alpine image, which only ships libssl — not the openssl CLI. All 27 checksum rows were stored as empty bytea. We had to fix that first with a separate Kubernetes Job before 0.2.0 would start. I'll share that Job below too.

2️⃣ BUZZ_AUTO_MIGRATE in our environment

Yes, set to true via migrate.autoMigrate: true in our Helm values (the chart maps this to BUZZ_AUTO_MIGRATE=true). Migrations do run on our deployments — the checksum issue above was the main hurdle.

3️⃣ Actual audit_log shape on our cluster

Before our workaround migration ran, the table looked like this (reconstructed from the migration job pre-flight output):

Column        | Type                        | Notes
--------------+-----------------------------+-----------------------------------------
seq           | bigint NOT NULL (PK)        |
timestamp     | timestamptz NOT NULL        | relay 0.2.0 expects created_at
event_id      | varchar(255) NOT NULL       | no equivalent in 0.2.0 schema
event_kind    | integer NOT NULL            | no equivalent in 0.2.0 schema
actor_pubkey  | varchar(255) NOT NULL       | relay 0.2.0 expects bytea
action        | varchar(64) NOT NULL        | ✅ matches
channel_id    | bytea                       | no equivalent in 0.2.0 schema
metadata      | jsonb NOT NULL              | relay 0.2.0 expects detail jsonb
prev_hash     | varchar(64) NOT NULL        | relay 0.2.0 expects bytea
hash          | varchar(64) NOT NULL        | relay 0.2.0 expects bytea

No community_id, no object_id, no detail, no created_at. PK was (seq) alone.

---

🔧 Our Kubernetes Job workaround

Since we're on AKS (Azure Kubernetes Service), we can't just psql into the database directly — DATABASE_URL lives in a Kubernetes Secret (buzz-secrets, ESO-materialized from Vault). We ran every schema fix as a one-shot Kubernetes Job that mounts the secret as an env var and uses the postgres:16 (Debian) image for psql.

Job 1 — SQLx checksum resync (run before upgrading to 0.2.0)

This was needed because our pre-upgrade migrate job stored empty bytea checksums. Relay 0.2.0 detects the mismatch and refuses to start. The job re-downloads each migration from GitHub, computes the real SHA-384, and UPDATEs _sqlx_migrations. No SQL is re-applied — schema is untouched.

buzz-migrate-checksum-resync.yaml

apiVersion: batch/v1
kind: Job
metadata:
  name: buzz-migrate-checksum-resync
  namespace: buzz
spec:
  ttlSecondsAfterFinished: 3600
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: checksum-resync
          # postgres:16 (Debian) has sha384sum (coreutils) + psql.
          # postgres:16-alpine does NOT have openssl CLI — use sha384sum instead.
          image: postgres:16
          env:
            - name: DATABASE_URL
              valueFrom:
                secretKeyRef:
                  name: buzz-secrets
                  key: DATABASE_URL
          command:
            - bash
            - -ec
            - |
              apt-get update -qq && apt-get install -y --no-install-recommends ca-certificates curl 2>&1 | tail -3
              REPO="https://raw.githubusercontent.com/block/buzz/main/migrations"
              MIGRATIONS_DIR="/tmp/migrations"
              mkdir -p "${MIGRATIONS_DIR}"
              for M in \
                0001_initial_schema 0002_git_repo_names 0003_community_icon \
                0004_events_tags_gin 0005_agent_turn_metric_fts 0006_moderation \
                0007_nip_rs_retention 0008_fresh_install_search_allowlist \
                0009_nip_rs_database_guards 0010_nip_rs_exact_replay_guard \
                0011_nip_rs_exact_tag_cardinality 0012_push_leases \
                0013_push_endpoint_state 0014_push_lease_fts \
                0015_push_gateway_authority 0016_community_archival \
                0017_product_feedback 0018_push_match_queue \
                0019_mesh_status_retention 0020_join_policy_acceptances \
                0021_created_at_fence_floor 0022_event_ttl_refresh \
                0023_push_match_gate 0024_event_ttl_refresh_shared_lock \
                0025_relay_invites 0026_replica_heartbeat \
                0027_channels_id_lookup_index; do
                curl -fsSL -o "${MIGRATIONS_DIR}/${M}.sql" "${REPO}/${M}.sql"
                VERSION=$(printf '%s' "${M}" | cut -d_ -f1 | sed 's/^0*//')
                NEW_HEX=$(sha384sum "${MIGRATIONS_DIR}/${M}.sql" | awk '{print $1}')
                STORED=$(psql -t -A "$DATABASE_URL" \
                  -c "SELECT encode(checksum,'hex') FROM _sqlx_migrations WHERE version=${VERSION};")
                if [ "${STORED}" != "${NEW_HEX}" ]; then
                  echo "Updating checksum for ${M}..."
                  psql "$DATABASE_URL" -q -c \
                    "UPDATE _sqlx_migrations SET checksum = decode('${NEW_HEX}','hex') WHERE version=${VERSION};"
                fi
              done
              echo "Done."

---

Job 2 — audit_log multi-tenant schema migration (idempotent operator script)

This is the one that actually fixes the audit_log for 0.2.0. All steps are idempotent (IF NOT EXISTS, type checks before ALTER). Safe to re-run.

buzz-migrate-audit-log-multitenant.yaml

apiVersion: batch/v1
kind: Job
metadata:
  name: buzz-migrate-audit-log-multitenant
  namespace: buzz
spec:
  ttlSecondsAfterFinished: 7200
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: audit-log-migration
          image: postgres:16
          env:
            - name: DATABASE_URL
              valueFrom:
                secretKeyRef:
                  name: buzz-secrets
                  key: DATABASE_URL
          command:
            - bash
            - -ec
            - |
              col_exists() {
                psql -t -A "$DATABASE_URL" -c \
                  "SELECT COUNT(*) FROM information_schema.columns
                   WHERE table_schema='public' AND table_name='$1' AND column_name='$2';"
              }
              col_type() {
                psql -t -A "$DATABASE_URL" -c \
                  "SELECT data_type FROM information_schema.columns
                   WHERE table_schema='public' AND table_name='$1' AND column_name='$2';"
              }

              COMMUNITY_UUID=$(psql -t -A "$DATABASE_URL" -c "SELECT id FROM communities LIMIT 1;")

              # 1. community_id
              if [ "$(col_exists audit_log community_id)" = "0" ]; then
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ADD COLUMN community_id uuid REFERENCES communities(id);"
                psql "$DATABASE_URL" -q -c "UPDATE audit_log SET community_id = '${COMMUNITY_UUID}'::uuid WHERE community_id IS NULL;"
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ALTER COLUMN community_id SET NOT NULL;"
              fi

              # 2. object_id
              if [ "$(col_exists audit_log object_id)" = "0" ]; then
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ADD COLUMN object_id text;"
              fi

              # 3. detail (backfill from metadata)
              if [ "$(col_exists audit_log detail)" = "0" ]; then
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ADD COLUMN detail jsonb;"
                psql "$DATABASE_URL" -q -c "UPDATE audit_log SET detail = metadata WHERE detail IS NULL;"
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ALTER COLUMN detail SET DEFAULT '{}'::jsonb;"
              fi

              # 4. created_at (backfill from timestamp)
              if [ "$(col_exists audit_log created_at)" = "0" ]; then
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ADD COLUMN created_at timestamptz;"
                psql "$DATABASE_URL" -q -c "UPDATE audit_log SET created_at = \"timestamp\" WHERE created_at IS NULL;"
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ALTER COLUMN created_at SET NOT NULL;"
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ALTER COLUMN created_at SET DEFAULT now();"
              fi

              # 5. hash varchar(64) → bytea
              if [ "$(col_type audit_log hash)" = "character varying" ]; then
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ALTER COLUMN hash TYPE bytea USING decode(hash,'hex');"
              fi

              # 6. prev_hash varchar(64) → bytea
              if [ "$(col_type audit_log prev_hash)" = "character varying" ]; then
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ALTER COLUMN prev_hash TYPE bytea USING decode(prev_hash,'hex');"
              fi

              # 7. actor_pubkey varchar(255) → bytea
              if [ "$(col_type audit_log actor_pubkey)" = "character varying" ]; then
                psql "$DATABASE_URL" -q -c "
                  ALTER TABLE audit_log ALTER COLUMN actor_pubkey TYPE bytea USING
                    CASE WHEN actor_pubkey IS NULL OR actor_pubkey = '' THEN NULL
                    ELSE decode(actor_pubkey,'hex') END;"
              fi

              # 8. Rebuild PK as (community_id, seq)
              PK_COLS=$(psql -t -A "$DATABASE_URL" -c "
                SELECT string_agg(a.attname,',')
                FROM pg_constraint c
                JOIN pg_class t ON t.oid=c.conrelid
                JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS x(k,n) ON true
                JOIN pg_attribute a ON a.attrelid=t.oid AND a.attnum=x.k
                WHERE c.contype='p' AND t.relname='audit_log' AND t.relnamespace='public'::regnamespace;")
              if [ "${PK_COLS}" = "seq" ]; then
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log DROP CONSTRAINT IF EXISTS audit_log_pkey;"
                psql "$DATABASE_URL" -q -c "ALTER TABLE audit_log ADD PRIMARY KEY (community_id, seq);"
              fi

              psql "$DATABASE_URL" -q -c \
                "CREATE UNIQUE INDEX IF NOT EXISTS idx_audit_log_community_hash ON audit_log (community_id, hash);"

              echo "=== Done ==="
              psql "$DATABASE_URL" -c "\d audit_log"

---

📝 Notes on your open questions

On detail NOT NULL: You're right that the target schema has it nullable — our Job intentionally did not add NOT NULL to detail, only a DEFAULT '{}'. We left the decision on NOT NULL to maintainers.

On old columns (event_id, event_kind, channel_id): We deliberately kept them in place — the Job does not drop them. Dropping them felt like a maintainer call, especially since channel_id is BYTEA and may carry data worth preserving. We document them as "intentionally kept — remove in a follow-up once upstream confirms."

On hash chain continuity across the cutover: We punted. Our pre-upgrade rows are a small number and chain verifiability across the cutover wasn't a hard requirement for us. But you're right that this is a real question for anyone running a verified audit trail — the maintainers should decide whether to re-anchor or simply document a chain break at the cutover point.

---

🤝 Offering a PR

If the scripts/ operator script direction is confirmed, we're happy to contribute the Jobs above as a scripts/migrate-audit-log-0.1-to-0.2.sh (or similar) with proper documentation. Just say the word.