block/buzz · error

apply desired-state schema

Error message

apply desired-state schema

What it means

The test applies the desired-state schema by executing schema/schema.sql verbatim with raw_sql on the scratch pool. The expect panics if any statement in schema.sql fails — syntax errors, missing referenced roles/extensions/functions, or statements that assume state pgschema creates separately (e.g. seed DML, special storage params, or pre-existing roles).

Source

Thrown at crates/buzz-db/src/store/channel_members.rs:3145

        let scratch_name = format!("schema_roster_role_{}", Uuid::new_v4().simple());
        sqlx::query(sqlx::AssertSqlSafe(format!(
            "CREATE DATABASE {scratch_name}"
        )))
        .execute(&admin)
        .await
        .expect("create desired-schema scratch db");
        let base_url = admin_url().await;
        let slash = base_url.rfind('/').expect("database URL has path segment");
        let scratch_url = format!("{}/{}", &base_url[..slash], scratch_name);
        let pool = PgPoolOptions::new()
            .max_connections(1)
            .connect(&scratch_url)
            .await
            .expect("connect desired-schema scratch db");
        sqlx::raw_sql(include_str!("../../../../schema/schema.sql"))
            .execute(&pool)
            .await
            .expect("apply desired-state schema");

        let db = Db::from_pool(pool.clone());
        let community_uuid = Uuid::new_v4();
        let community = CommunityId::from_uuid(community_uuid);
        let channel = Uuid::new_v4();
        let relay_keys = Keys::generate();
        let owner_keys = Keys::generate();
        let owner = owner_keys.public_key().to_bytes();
        seed_community_channel(&pool, community_uuid, channel, &owner_keys).await;
        let member = Keys::generate().public_key().to_bytes();
        sqlx::query(
            "INSERT INTO channel_members (community_id, channel_id, pubkey, role, invited_by) \
             VALUES ($1, $2, $3, 'admin', $4)",
        )
        .bind(community_uuid)
        .bind(channel)
        .bind(member.as_slice())
        .bind(owner.as_slice())

View on GitHub (pinned to dad5a33865)

Solutions

  1. Read the sqlx error's positioned statement to find the failing statement in schema.sql
  2. Pre-create referenced roles/extensions on the scratch DB before executing schema.sql
  3. Reconcile schema.sql with migrations so it applies cleanly from empty (pg_dump a known-good desired state)
  4. If the error is CREATE EXTENSION permission, run the scratch test as superuser

Example fix

// before
sqlx::raw_sql(include_str!("../../../../schema/schema.sql"))
    .execute(&pool).await.expect("apply desired-state schema");
// after
sqlx::raw_sql("CREATE EXTENSION IF NOT EXISTS pgcrypto;").execute(&pool).await.expect("prereq extension");
sqlx::raw_sql(include_str!("../../../../schema/schema.sql"))
    .execute(&pool).await.expect("apply desired-state schema: check the failing statement in the sqlx error");
Defensive patterns

Strategy: try-catch

Try / catch

if let Err(e) = sqlx::raw_sql(include_str!("../../../../schema/schema.sql")).execute(&pool).await {
    panic!("apply desired-state schema failed: {e}; find the failing statement via e.as_database_error() and reconcile schema.sql with migrations");
}

Prevention

When it happens

Trigger: Executing schema.sql against a database that lacks prerequisites the schema references: custom roles, extensions (uuid-ossp/pgcrypto/pg_trgm), or where concurrent multi-statement execution hits ordering issues; also any genuine SQL error inside schema.sql.

Common situations: schema.sql drifted from migrations so it no longer applies cleanly to an empty DB; running against a scratch DB without required extensions installed; role-based RLS/GRANT statements referencing roles that don't exist on this instance; AGENTS.md notes pgschema-created DBs omit seed DML — conversely schema.sql may assume hand-applied state.

Understand the failure class

Background: Schema validation failed / invalid input schema: payload rejected because its shape doesn't match the expected schema — this error's family across 28 libraries.

Related errors

AI-assisted analysis of block/buzz@dad5a33865 (2026-08-30). Data as JSON: /api/errors/d48ae257b6da2edf. Report an issue: GitHub.