Leader election: our advisory locks can lose leadership silently, break behind poolers and hang startup

Jeremy Miller on #687, 14 September 2026: "Using the postgres advisory lock naively for leadership election can be problematic", and "search for issues in Marten & Wolverine related to advisory locks." Those issues describe, in production, every failure our three locks are open to.

## What we run

- `ScheduledContentService` and `WorkflowRunRetentionService`: `pg_try_advisory_lock` on a pooled connection held for the sweep, while the sweep writes through other sessions; skip the tick if the lock is taken.
- `SchemaApplyLock`: blocking `pg_advisory_lock` at startup, no timeout.

## The failure modes, each already seen in Marten or Wolverine

1. **Silent loss of leadership, then two leaders.** If the lock connection's backend is terminated (network blip, idle-connection cull, Postgres failover, managed Postgres maintenance), Postgres releases the lock and another node takes it, while the first keeps sweeping. JasperFx/wolverine#2602 is this exact split-brain, seen on Azure Flexible Server. JasperFx/marten#3789 is the same after a Postgres restart. For us that means a scheduled item transitions twice and "Published" emails and webhooks fire twice.
2. **Transaction-mode poolers ignore session locks.** Behind PgBouncer in transaction mode, every instance "holds" the lock and all of them run the work (JasperFx/marten#3988). Supabase's Supavisor, Azure and AWS poolers put people in this mode by default.
3. **A leaked lock session wedges later work.** A lock held on a connection that outlives its host stays granted forever (JasperFx/marten#5090), which in a test suite stalls every later host.
4. **Concurrent use of the lock connection hangs shutdown.** Npgsql does not allow two operations on one connection; overlapping check and release desynced the protocol and hung `CloseAsync` forever (JasperFx/wolverine#4261). Our sweeps use the connection only in sequence today, and that has to stay true.
5. **A blocking startup lock without a timeout.** Replicas waiting on a schema lock either hang or crash-loop depending on the timeout (JasperFx/marten#4750). Ours has no timeout, so a stuck migration holds every replica at startup indefinitely.

## Change

Decide in #687 first, then do one of these, not both:

- **Adopt Wolverine's node agents** for leadership (the #687 plan, with FastEndpoints delegating to Wolverine as a mediator), and move the two sweeps onto it. Leaves only schema apply on an advisory lock, where Weasel's own global lock can take over.
- **Or harden what we have**, if the spike says no: before each unit of sweep work, re-check the lock is still held on its own connection and stop if not; make every sweep write idempotent so a double run is harmless (a transition that already happened is a no-op); refuse to start with a transaction-mode pooler detected, or document the direct connection requirement; give the schema lock a timeout and a wait-then-continue path; never share the lock connection across concurrent calls.

Either way:
- A test that terminates the lock holder's backend (`pg_terminate_backend`) mid-sweep and proves no item transitions twice.
- `docs/deploy-in-production.md` states the pooler requirement.
- #839 (schedule trigger) and #833 (services registry) are built on the chosen mechanism, not on a new advisory lock.

## Done when

- Killing the lock holder's backend during a sweep produces no duplicate transitions and no duplicate workflow runs, proven by a test.
- Two API instances behind a transaction-mode pooler either coordinate correctly or refuse to start with a clear message.

Start with issue #687, then trace ScheduledContentService, WorkflowRunRetentionService, and SchemaApplyLock to understand the current advisory-lock paths. Decide between Wolverine node agents and hardening the existing locks; completion requires the backend-termination test, transaction-pooler handling, and the deployment documentation update, while respecting #839 and #833.

Written by the indexing model from the issue text.