Event pruning can fail on duplicate cursor keys and deadlock messenger writes

[cassi-volkova]

Summary

workspace_backend 0.1.18 can enter a permanent MessengerWorkerAgent retry loop in prune_expired_events().

After a bounded batch deletes the last events for multiple audience snapshots, the final cursor upsert emits one row per audience membership. If two orphaned snapshots contain the same (project_id, user_uuid), PostgreSQL receives duplicate values for the unique cursor key in a single INSERT ... ON CONFLICT DO UPDATE statement and raises:

psycopg.errors.CardinalityViolation: ON CONFLICT DO UPDATE command cannot affect row a second time

The worker calls refresh_effective_capabilities() before pruning in the same transaction. That function locks external-account rows first; the new bounded pruning query then takes per-project advisory locks. Concurrent message writes and provider-event batches take the project advisory lock first and then need an external-account row lock/FK check. This lock-order inversion produces recurring PostgreSQL deadlocks while the worker is retrying.

Impact

• Event and heartbeat retention cleanup does not commit.
• External projection repair in the same worker iteration is skipped.
• Messenger writes and read-state updates can return HTTP 500 with DeadlockDetected.
• Provider-event batches can fail and require retry.
• The systemd units remain active, so service-state monitoring alone does not expose the failure.

Reproduction

1. Create at least two audience snapshots for the same project with the same member.
2. Give each snapshot only expired broadcast events that will be selected by one pruning batch.
3. Run prune_expired_events() with a batch size large enough to delete the last event for both snapshots.
4. The final insert into m_workspace_event_cursors contains duplicate (project_id, user_uuid) values and fails with CardinalityViolation.
5. Run a concurrent externally-linked message write or provider-event batch while the worker holds account locks and waits for the project advisory lock; one transaction is aborted as a deadlock victim.

This is reproducible against tag 0.1.18; the bounded pruning/locking behavior was introduced by #55.

Expected behavior

The orphan-snapshot cursor source should produce at most one row per (project_id, user_uuid), folding watermarks with MAX(...) before the upsert. Lock acquisition order should also be consistent across worker, messenger-write, and provider-event paths, or pruning should run in a transaction isolated from capability refresh.

Regression coverage

Please add coverage for:

• multiple orphaned audience snapshots sharing one project/member in the same batch;
• cursor watermarks taking the maximum across those snapshots;
• concurrent pruning and externally-linked message/provider-event writes without deadlocks;
• worker iteration rollback not starving projection repair or retention indefinitely.

[cassi-volkova]

Reopening after post-release validation against workspace_backend 0.1.19
(0c2a8876).

The duplicate cursor-key failure is fixed: the event-prune transaction
successfully committed two consecutive 25,000-row batches, and no
CardinalityViolation occurred.

However, immediately after the second committed prune batch, the same worker
iteration failed in refresh_effective_capabilities() at
workspace/external_bridge_control/sql_state.py:397:

psycopg.errors.DeadlockDetected: deadlock detected
CONTEXT: while locking tuple in relation "m_external_accounts_v2"

Observed impact:

• the already committed event-prune batch was preserved;
• heartbeat pruning and projection repair were skipped for that iteration;
• the worker remained active and recovered on later iterations;
• subsequent passes removed another 50,000 expired events and completed the
  heartbeat backlog;
• the public UI and API remained healthy.

This shows that #57 removed the pruning/capability-refresh transaction
inversion, but refresh_effective_capabilities() can still participate in an
external-account row-lock cycle under concurrent production-like traffic.
The remaining work should establish a canonical lock order (or bounded
retry/isolation) for capability refresh and all writers touching
m_external_accounts_v2, with a concurrency regression test.