[BUG] Failed broadcast recipient insertion leaves an orphaned sending broadcast

[iuryeng]

Summary

Broadcast creation persists the parent row before inserting recipients. If recipient insertion fails, the function throws but leaves the parent broadcast persisted with status sending and no recipients.

Environment

• Version: 0.8.0
• Commit: b8677608015790194c14ab7c85943ce26c7f9ef0
• Reproduction: Vitest characterization with synthetic data
• Repeated executions: 3

Steps to reproduce

1. Call createBroadcast() with a valid synthetic broadcast and recipient list.
2. Allow the parent broadcast insert to succeed.
3. Make the recipient insert return a database error.
4. Inspect the persisted parent state after the function throws.

npm test -- --run src/lib/whatsapp/broadcast-partial-repro.test.ts

No production credentials, phone numbers, customer content, or external services are used.

Expected behavior

Broadcast creation should be atomic. On recipient failure, no sending parent should remain, or the parent should move to an explicit failed state with a safe retry path.

Actual behavior

The function throws BroadcastError, but the parent remains:

Parent broadcast: created
Status:           sending
Recipient rows:   0
Function result:  BroadcastError

The result was identical in three consecutive focused test runs.

Technical evidence

The parent is inserted first:

wacrm/src/lib/whatsapp/broadcast-core.ts

Lines 197 to 214 in b867760

	.from('broadcasts')
	.insert({
	account_id: accountId,
	user_id: auditUserId,
	name: name || `API broadcast (${templateName})`,
	template_name: templateName,
	template_language: templateLanguage,
	status: 'sending',
	total_recipients: deduped.length,
	})
	.select('id')
	.single();
	if (bErr || !broadcast) {
	console.error('[broadcast-core] create broadcast error:', bErr);
	throw new BroadcastError('internal', 'Failed to create broadcast', 500);
	}
	const { data: recipientRows, error: rErr } = await db

Recipients are inserted in a separate operation, and failure throws without compensating for the parent:

wacrm/src/lib/whatsapp/broadcast-core.ts

Lines 215 to 235 in b867760

	.from('broadcast_recipients')
	.insert(
	deduped.map((r) => ({
	broadcast_id: broadcast.id,
	contact_id: r.contactId,
	status: 'pending' as const,
	}))
	)
	.select('id, contact_id');
	if (rErr || !recipientRows) {
	console.error('[broadcast-core] create recipients error:', rErr);
	throw new BroadcastError('internal', 'Failed to create broadcast', 500);
	}
	// Pair each inserted recipient row back to its phone/params by
	// contact_id — unambiguous now that duplicates are collapsed.
	const byContact = new Map(deduped.map((r) => [r.contactId, r]));
	const planned: PlannedRecipient[] = recipientRows.map((row) => {
	const r = byContact.get(row.contact_id as string)!;
	return { recipientRowId: row.id as string, phone: r.phone, params: r.params };
	});

Impact

Transient database failures can leave campaigns that appear to be sending but have no delivery plan. Operators may retry and create additional orphaned campaigns, while operational views and counts contain misleading sending records.

Scope and limitations

This is a route-logic/unit-integration characterization with a Supabase chain mock. It demonstrates the missing compensation boundary in the code; it does not claim a live database outage or a specific production failure rate.

Suggested acceptance criteria

• Parent and recipient rows are created atomically, preferably through a transaction/RPC.
• A recipient insertion failure leaves no orphaned sending parent.
• Retry behavior is explicit and does not create duplicate campaigns.
• Regression coverage includes parent success followed by recipient failure.

[iuryeng]

Visual consistency evidence

The characterization test allows the parent insert to succeed and forces the recipient insert to fail.

flowchart LR
    A["Parent broadcast insert succeeds"] --> B["Recipient insert fails"]
    B --> C["BroadcastError"]
    C --> D["Parent remains sending"]
    D --> E["Zero recipient rows"]
    style C fill:#3f2a10,stroke:#d97706,color:#fff
    style D fill:#3f1117,stroke:#dc2626,color:#fff
    style E fill:#3f1117,stroke:#dc2626,color:#fff

Loading

Captured state	Value
Parent broadcast	created
Status	sending
Recipient rows	0
Function result	BroadcastError

+ Parent row survives the failed operation
- No recipient plan exists for the sending broadcast

The same orphaned state was captured in 3/3 focused test runs. Synthetic data only; this demonstrates the missing transaction/compensation boundary and does not claim a live database outage.

[ArnasDon]

Fixed in #388 (merge commit d826510). Broadcast parent + recipient rows are now inserted in a single transaction (create_broadcast_with_recipients RPC, migration 037); a recipient-insert failure rolls the parent back, so no orphaned sending broadcast survives.

Closing manually: #388 targeted fix/duplicate-conversations, not the default branch, so the Fixes #N keyword didn't auto-close it.