Serialize PASETO key rotation across nodes with pg_try_advisory_xact_lock

Decision

Coordinate key rotation with a transaction-scoped Postgres advisory lock taken without waiting. Every node schedules key maintenance, at startup and once a day (ADR-0026). Each run opens a transaction and calls pg_try_advisory_xact_lock on a fixed key. The node that gets the lock creates, rotates, expires and deletes keys inside that same transaction and commits. A node that doesn't get the lock rolls back and skips the round.

Why

The application runs on several nodes, and two concurrent rotations could each deprecate the active key and insert a new one. The keys already live in Postgres, and every node already holds a connection pool to it. Taking the lock in the transaction that writes the keys makes the lock and the writes one unit: Postgres releases the lock at commit or rollback, so a run that crashes or errors can't leave it held. The try form returns immediately, so a node that loses the race moves on instead of queueing behind the winner and repeating its work.

Rejected alternatives

• A lock in a separate coordination service, such as Redis or a scheduler's leader election. Rotation would then depend on Redis or the scheduler being up, and the lock would not commit or roll back with the key writes.
• The session-scoped pg_advisory_lock. A pooled connection outlives the job, so a missed unlock leaves the lock held until the connection closes.

Consequences

Key maintenance needs nothing beyond Postgres. At most one node does the work each round, and the others log that it was handled elsewhere. The lock key is a constant that no other job may reuse.