relay: workflows created pre-#1369 are orphaned — undeletable, undisableable, and fire forever

[AaronGoldsmith]

Summary

Workflows created while a relay ran the pre-#1369 code path (≤ relay-v0.1.1) are permanently unmanageable — invisible orphans that keep firing on their cron schedules and cannot be disabled or deleted through any client-visible ID. We hit this in production (staging relay sprout-oss) and it took several days and a code-level investigation to stop twice-daily ghost messages.

Background: how the orphans are created

Before #1369 ("Harden relay attack surfaces", merged 2026-06-30), handle_workflow_def called create_workflow, which generated a random server-side UUID (Uuid::new_v4()) for the DB row and ignored the kind:30620 event's d-tag:

// crates/buzz-db/src/workflow.rs (relay-v0.1.1)
pub async fn create_workflow(...) -> Result<Uuid> {
    let id = Uuid::new_v4();   // <-- never matches the event d-tag
    ...
}

Clients (CLI buzz workflows list, desktop) list workflows by querying kind:30620 events and reading the d-tag as the workflow ID. So every workflow ID a user has ever seen never matched the actual workflows row. Consequences:

1. Disable is a no-op: re-submitting the definition with enabled: false (same d-tag) created a new DB row under yet another random UUID. The original enabled: true row keeps firing.
2. Delete is a no-op: kind:5 with a-tag 30620:<owner>:<visible-uuid> looks up a UUID that doesn't exist in the DB. Worse, the relay returns accepted: true with no error, so the user believes the deletion worked (side-effect failures are logged server-side only).
3. After the relay was upgraded past Harden relay attack surfaces #1369 (upsert-by-d-tag), all new operations behave correctly — but the legacy orphan rows remain, enabled, firing forever, with UUIDs no client can discover.

How we escaped

handle_a_tag_deletion has a name-based fallback: when the a-tag d-value is not a UUID, it resolves via find_workflow_by_owner_and_name(community, actor, name). This reaches orphans regardless of UUID. The stock CLI rejects non-UUID values (parse_uuid), so we patched cmd_delete_workflow locally to emit 30620:<owner>:<name> and swept the orphans (repeating, since the lookup is LIMIT 1). Verified with webhook canaries (202 → delete → 404).

Proposed fixes

1. Orphan reconciliation (the actual long-term fix). A migration or startup/admin sweep that deletes (or at minimum disables + logs) workflows rows whose id does not match any live kind:30620 event's d-tag for the same community. Every community that created workflows before the relay crossed Harden relay attack surfaces #1369 has these landmines.
2. Surface side-effect failures for deletions. kind:5 processing returns accepted: true even when the workflow deletion hits NotFound. The OK/response message should indicate "no matching workflow" so users don't believe a no-op succeeded. (Same applies to disable-via-update flows.)
3. CLI: support name-based deletion. The relay already supports it and it's the only recovery path for ID-mismatch situations: buzz workflows delete --name <workflow-name>.
4. Name-based deletion ignores the a-tag pubkey. handle_a_tag_deletion's name branch looks up by actor_bytes (the deletion signer), so the is_agent_owner allowance in validate_standard_deletion_event (humans managing agent-owned content, feat(relay): allow agent owners to edit/manage agent-owned content #1403) is dead code on this path — a human owner cannot clean up their agent's orphans by name. The lookup should honor the a-tag's pubkey component after ownership validation passes.

Related

• fix(workflows): soft-delete kind:30620 event on authorized deletion #1340 (soft-delete of the kind:30620 event row on authorized deletion — the visibility half of this problem)
• relay: NIP-09 a-tag deletion of kind:30620 (workflow def) leaves the events row alive #717, relay: NIP-09 a-tag deletion is a no-op for kind:30023 (and all non-workflow addressables) #714 (deletion side-effect gaps)
• Harden relay attack surfaces #1369 (fixed d-tag upsert going forward, but did not reconcile existing rows)

Repro sketch

Not directly reproducible on current main (creation is fixed); the failure mode requires rows created under ≤ relay-v0.1.1. To simulate: insert a workflows row with enabled=true and a random id unrelated to any 30620 event d-tag, then attempt to disable/delete it through the CLI.

[AaronGoldsmith]

Update 2026-07-09: the "escape hatch" doesn't work either — orphans are fully unreachable

Correction to the "How we escaped" section above: we did not actually escape. The name-based deletion sweep appeared to succeed (accepted:true on every run) but relay logs show every attempt was a silent no-op, and the orphan crons kept firing on 07-08 and 07-09.

A fifth bug: pre-#1369 rows have name = d-tag UUID string

The ≤ relay-v0.1.1 create path set the row name from the event's name tag, falling back to the d-tag:

// crates/buzz-relay/src/handlers/command_executor.rs (relay-v0.1.1)
let workflow_name = extract_tag(event, "name")
    .or_else(|| extract_d_tag(event))

…and buzz_sdk::build_workflow_def only emits d and h tags — no name tag. So every CLI-created workflow row from that era has name = "<uuid-string>", not the YAML name:.

Consequences for the orphans:

• Name-based lookup can never match — the human-readable name isn't in the DB.
• Sending the stored UUID-string name is impossible — any UUID-shaped a-tag d-value passes Uuid::parse_str and routes to the UUID branch (DELETE … WHERE id = …), which misses because the row id is a different random v4.

There is no event a client can sign that reaches these rows. Client-side remediation is impossible, which upgrades proposed fix #1 (orphan reconciliation) from "nice to have" to the only fix.

Evidence (Datadog, service:sprout env:staging)

• 21× NIP-09 a-tag deletion: no workflow 'sims-daily-pr-triage' found for owner (warn) — one for every name-based deletion attempt by three different owners on 07-07 and 07-09. All received accepted:true (bug Initial release — Sprout Nostr relay with enterprise extensions #2 in action — this silent-success behavior misled us into declaring victory twice).
• Workflow SendMessage: posting kind 9 event continued firing on schedule: 07-08 09:00/17:00×2, 07-09 09:00.
• Same ticks show additional orphans on this relay failing invisibly: 6× Workflow run failed: webhook error: channel is archived: 51f7899e…, 1× channel not found: 430f0276…, plus an orphan posting into an unrelated channel under a different owner. ≥10 orphan rows fire daily on sprout-oss staging alone.

Reconciliation sweep sketch

Given the name bug, orphans are cheaply identifiable without joining against events:

-- rows whose name parses as a UUID that differs from the row id
DELETE FROM workflows
WHERE name ~* '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
  AND name <> id::text;

(The stricter "id matches no live 30620 d-tag" join from proposed fix #1 is still the right general-purpose migration; the above catches the pre-#1369 CLI-created population specifically.)

Until a sweep runs, the affected rows fire forever; our only working mitigation is archiving the destination channel (orphan runs into archived channels error out without posting — confirmed in logs above).