# How Buzz's Database Schema Handles Event Storage, Channel Management, and Workflow Execution

> Explore Buzz's PostgreSQL database schema for efficient event storage, secure channel management with UUIDs, and robust workflow execution using normalized tables for idempotency and tenant isolation.

- Repository: [Block Open Source/buzz](https://github.com/block/buzz)
- Tags: architecture
- Published: 2026-08-27

---

**Buzz's persistence layer uses PostgreSQL through the `buzz-db` crate to store signed Nostr events in a normalized `events` table, isolate communities via UUID-based `channels` with foreign-key scoping, and orchestrate programmable automation through four interlinked workflow tables that enforce idempotency, hashed approval tokens, and tenant isolation.**

The **block/buzz** repository implements a Rust-based Nostr relay that combines social messaging with workflow automation. Its database schema in `crates/buzz-db` provides a type-safe, PostgreSQL-backed foundation that respects Nostr protocol semantics while supporting complex channel-based communities and deterministic workflow execution.

## Event Storage Architecture

All signed Nostr events—except **AUTH** (kind 22242) and ephemeral kinds 20000–29999—persist in the central `events` table defined in [`crates/buzz-db/src/event.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/event.rs). The schema balances Nostr compatibility with relational query performance.

### The Events Table Schema

Each row stores the canonical event fields plus Buzz-specific metadata:

- `id`, `pubkey`, `created_at`, `kind` (as `i32`), `content`, and `sig` for Nostr compliance
- `tags` as **JSONB** for flexible tag storage with GIN indexing
- `received_at` timestamp for relay-side ordering
- `channel_id` (optional UUID) for community scoping
- `d_tag` for **NIP-33** replaceable event identification
- `not_before` for reminder scheduling
- `deleted_at` for soft-deletion support

### Idempotent Insertion with Conflict Handling

The `insert_event` function (and its transaction-aware variant `insert_event_in_transaction`) guarantees exactly-once semantics using `ON CONFLICT DO NOTHING`. This prevents duplicate storage if the same event is submitted multiple times.

```rust
use buzz_db::{event::insert_event, CommunityId};
use nostr::Event;
use uuid::Uuid;

let community = CommunityId::from_uuid(Uuid::new_v4());
let my_event: Event = /* build a signed Nostr event */;
let channel_id: Option<Uuid> = Some(Uuid::new_v4()); // None for global events

let (stored, was_inserted) = insert_event(&pool, community, &my_event, channel_id)
    .await
    .expect("event insert failed");

```

The helper functions `extract_d_tag` and `extract_not_before` parse NIP-33 replaceable identifiers and reminder timestamps during insertion.

### Optimized Querying and Pagination

The `query_events` function in [`event.rs`](https://github.com/block/buzz/blob/main/event.rs) constructs dynamic SQL using `sqlx::QueryBuilder`, pushing all filters (community, channel, kinds, authors, tags, time ranges) as bind parameters to prevent SQL injection. Key optimizations include:

- **GIN indexes** on the `tags` JSONB column for fast tag lookups
- **Cursor-based pagination** using `(created_at DESC, id ASC)` for stable ordering
- **Global limit enforcement** via `DEFAULT_MAX_PAGE_LIMIT = 1000`, matching the NIP-11 advertised ceiling

```rust
use buzz_db::{event::{EventQuery, query_events}, CommunityId};
use chrono::Utc;

let q = EventQuery {
    community_id: CommunityId::from_uuid(Uuid::new_v4()),
    channel_id: Some(Uuid::parse_str("c1d2e3f4-5678-90ab-cdef-1234567890ab").unwrap()),
    kinds: Some(vec![1, 30001]),
    since: Some(Utc::now() - chrono::Duration::hours(24)),
    ..EventQuery::for_community(community_id)
};
let events = query_events(&pool, &q).await?;

```

## Channel Management and Multi-Tenant Isolation

Buzz organizes events into **channels**—isolated scopes within a community identified by UUID `channel_id` values.

### Channel Scoping Architecture

The `channels` table (managed in [`crates/buzz-db/src/channel.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/channel.rs)) maintains metadata and rosters separately from events to avoid cross-tenant leakage. The `events.channel_id` foreign key determines visibility:

- **Channel-scoped events**: `channel_id` set to a specific UUID
- **Global events**: `channel_id IS NULL` for community-wide visibility
- **Multi-channel queries**: Use `IN (...)` predicates with the `channel_ids` filter, optionally including global events via `channel_ids_include_global`

The `EventQuery` builder enforces mutual exclusivity between `channel_id` (single channel) and `global_only` flags. Migration [`0027_channels_id_lookup_index.sql`](https://github.com/block/buzz/blob/main/0027_channels_id_lookup_index.sql) creates the necessary index on `channel_id` for performant lookups.

### Roster and Metadata Separation

Channel membership and configuration live in `channel_roster` and `channel_metadata` tables, linked through the community ID. This normalization prevents redundant data duplication across thousands of events while maintaining strict ownership boundaries.

## Workflow Execution Engine

Buzz extends Nostr with programmable workflows defined in JSON and executed through four core tables in [`crates/buzz-db/src/workflow.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/workflow.rs).

### Core Workflow Tables

1. **`workflows`**: Stores definitions with UUID `id`, community reference, optional `channel_id`, owner pubkey, JSONB definition, status enum, and `enabled` boolean
2. **`workflow_runs`**: Tracks execution instances with status enums, trigger event references, step counters, execution traces, and optional `trigger_context`
3. **`workflow_approvals`**: Gates steps requiring human intervention using hashed tokens
4. **`scheduled_workflow_fires`**: Deduplicates scheduled executions via atomic claim semantics

### Creating and Updating Workflows

The `create_workflow` function initializes workflows with `status = 'active'` and `enabled = TRUE`. For idempotent updates, `upsert_workflow` guards against concurrent modifications by verifying owner pubkey and channel equality.

```rust
use buzz_db::workflow::{create_workflow, WorkflowStatus};
use uuid::Uuid;

let community = CommunityId::from_uuid(Uuid::new_v4());
let channel = Some(Uuid::new_v4());
let owner_pubkey = b"0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef";
let definition = r#"{"trigger": {"on": "message_posted"}, "steps": [...]}"#;
let definition_hash = sha256::digest(definition.as_bytes());

let workflow_id = create_workflow(
    &pool,
    community,
    channel,
    owner_pubkey,
    "WelcomeWorkflow",
    definition,
    &definition_hash,
).await?;

```

### Run Lifecycle and Atomic Updates

Workflow runs progress through statuses via `create_workflow_run` and `update_workflow_run`. The update function conditionally sets `started_at` and `completed_at` timestamps only when the status transition demands it, preventing race conditions.

```rust
use buzz_db::workflow::{create_workflow_run, RunStatus, update_workflow_run};

let run_id = create_workflow_run(
    &pool,
    community,
    workflow_id,
    Some(event.id.as_bytes()),
    None,
).await?;

update_workflow_run(
    &pool,
    community,
    run_id,
    RunStatus::Running,
    0,
    &serde_json::json!([]),
    None,
).await?;

```

### Secure Approval Handling

Approval tokens are never stored in plaintext. The `hash_approval_token` function applies **SHA-256** hashing before storage. The `create_approval` and `update_approval` functions operate exclusively on hashed tokens, enforcing `status = 'pending'` predicates to prevent concurrent grants or denials.

```rust
use buzz_db::workflow::{create_approval, update_approval, ApprovalStatus};

let token = "random-32-char-secret";
create_approval(&pool, buzz_db::workflow::CreateApprovalParams {
    community_id,
    token,
    workflow_id,
    run_id,
    step_id: "s3",
    step_index: 2,
    approver_spec: "@alice",
    expires_at: Utc::now() + chrono::Duration::minutes(30),
}).await?;

// Granting the approval
let granted = update_approval(&pool, community_id, token, ApprovalStatus::Granted, Some(&alice_pubkey), None).await?;

```

### Scheduled Fire Deduplication

The `claim_scheduled_workflow_fire` function uses `ON CONFLICT DO NOTHING` to atomically claim scheduled execution slots. Only the pod winning the insert receives the claim, preventing duplicate workflow triggers across distributed instances. Retention is managed via `prune_scheduled_workflow_fires_before`.

## Security and Performance Patterns

The schema implements several critical safeguards:

- **TOCTOU protection**: All CRUD operations use bind-parameter-only SQL with explicit ownership checks
- **List bounding**: `LIST_DEFAULT_LIMIT = 100` and `LIST_MAX_LIMIT = 1000` prevent resource exhaustion
- **Soft deletion**: Events use `deleted_at` rather than hard deletion to maintain audit trails
- **Tenant isolation**: Community ID prefixes every query, ensuring no cross-community data leakage

## Summary

- **Event storage** uses a PostgreSQL `events` table with JSONB tags, soft-deletion via `deleted_at`, and idempotent inserts via `ON CONFLICT DO NOTHING`
- **Channel management** isolates communities through UUID-based `channel_id` foreign keys, with global events represented as `NULL` and fast lookups enabled by migration [`0027_channels_id_lookup_index.sql`](https://github.com/block/buzz/blob/main/0027_channels_id_lookup_index.sql)
- **Workflow execution** relies on four tables (`workflows`, `workflow_runs`, `workflow_approvals`, `scheduled_workflow_fires`) with SHA-256 hashed approval tokens and atomic claim semantics for scheduled fires
- **Query safety** is enforced through `sqlx::QueryBuilder` with bind parameters only, GIN indexes on tags, and cursor-based pagination with a 1000-event limit
- **Source files**: [`crates/buzz-db/src/event.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/event.rs), [`crates/buzz-db/src/channel.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/channel.rs), and [`crates/buzz-db/src/workflow.rs`](https://github.com/block/buzz/blob/main/crates/buzz-db/src/workflow.rs) contain the implementation details

## Frequently Asked Questions

### How does Buzz prevent duplicate event storage?

Buzz guarantees idempotency through PostgreSQL's `ON CONFLICT DO NOTHING` clause in the `insert_event` function. When a Nostr event with an existing ID is submitted, the conflict handler silently skips the insert, returning the existing record without error. This allows the relay to safely accept replayed events from clients without data duplication.

### What is the difference between global events and channel-scoped events?

Global events have `channel_id IS NULL` in the `events` table and are visible across the entire community, while channel-scoped events have a specific UUID in the `channel_id` column restricting them to that channel's context. Queries can filter exclusively for global events using `global_only = true`, request specific channels via `channel_ids`, or combine channel events with global ones using `channel_ids_include_global`.

### How does Buzz handle workflow approval security?

Approval tokens are hashed using SHA-256 via `hash_approval_token` before storage in the `workflow_approvals` table. The `update_approval` function checks for `status = 'pending'` to prevent race conditions where multiple approvers might simultaneously grant or deny the same step. Tokens are never stored or logged in plaintext, ensuring that even database read access does not compromise pending approvals.

### What limits does Buzz impose on event queries?

The database enforces a `DEFAULT_MAX_PAGE_LIMIT` of 1000 events per query, matching the NIP-11 relay information document. Pagination uses a stable cursor based on `(created_at DESC, id ASC)` rather than offset-based paging, ensuring consistent results even when new events arrive during pagination. All filter parameters are bound as prepared statement parameters to prevent SQL injection while allowing PostgreSQL to utilize GIN indexes on the JSONB `tags` column.