[Feature]: Claim episodes with a lease instead of holding a transaction across the model call #444

Feature Area

Backend API / Server

Problem Statement

_compile_one_batch keeps one transaction open from reading the uncompiled episodes, through the compiler and LLM call, until commit. #417 asked to split that. PR #440 kept it on purpose, because its advisory lock is transaction-scoped and dies with the transaction, and said the split needs its own design.

What the long transaction costs:

• The pool is pool_size=5, max_overflow=10, pool_timeout=30 (server/db/engine.py), so 15 connections per process.
• An LLM compile runs for minutes, and holds one of those connections for its whole duration.
• With enough concurrent compiles across different subjects, the pool is exhausted, and unrelated requests wait 30 seconds and then fail.

This follows from the code. It has not been load-tested, so the first task is a test that runs more concurrent compiles than the pool holds and checks that an unrelated request still succeeds.

Proposed Solution

1. Claim the episodes in a short transaction and record a lease on the claim: who holds it and until when.
2. Run the compiler and the model call with no transaction and no pooled connection held.
3. Write the result in a second short transaction, conditional on the lease still being held by this worker. If it has expired and been reclaimed, discard the result.
4. Reclaim expired leases, so a crashed worker cannot strand episodes.
5. Replace the transaction-scoped advisory lock from PR fix(compile): serialise compiles of one subject #440 with the lease as the serialisation point, and keep the 409-with-Retry-After behaviour for sync callers.

This needs a migration for the lease columns.

API Design (if applicable)

Alternatives Considered

• A session-scoped advisory lock across both short transactions: still pins a connection for the whole compile, which is the thing to avoid.
• A larger pool: moves the limit instead of removing it, and costs database connections on every instance.

Priority to You

Medium - Would be helpful

Additional Context

No response

Contribution

• I would be interested in contributing this feature
• I would be interested in testing this feature