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

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. 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.

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

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) 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 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.

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.

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.

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.

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
• 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, crates/buzz-db/src/channel.rs, and 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}