Intake race: enforce one-active-run-per-issue with a DB constraint, not check-then-insert

[JeremySNR]

Severity: High — known limitation (AGENTS.md), filing so it's tracked and fixable

Problem

intake_and_plan checks for an active run in one session (orchestrator.py:175-180) and creates the run in a different session later (~orchestrator.py:190-257). foundry_runs.linear_issue_id deliberately has no unique constraint (db/models.py:88-90). Two simultaneous webhook deliveries for the same issue both pass the check and both create runs — two approvals, two dispatched agents, two PRs are possible.

The GitHub-events dedup set is in-memory and per-process (api/app.py:160), so it doesn't protect multi-worker deployments or restarts.

Suggested fix

• Postgres: partial unique index UNIQUE (linear_issue_id) WHERE status IN (<active statuses>) via an Alembic migration; SQLite dev gets it via a filtered Index in create_all.
• Flip intake to insert-first and catch IntegrityError, returning/attaching to the existing active run.
• Add a test simulating duplicate intake (two sessions) asserting exactly one run survives.

This is also a prerequisite for ROADMAP #7 ("Unique constraint: one active run per issue, enforced in the DB") — it can be pulled forward cheaply.

[JeremySNR]

Starting work on this. Plan, per the suggested fix:

1. Partial unique index on foundry_runs.linear_issue_id restricted to active statuses — Alembic migration for Postgres, filtered Index on the model so SQLite dev gets it via create_all.
2. Flip intake_and_plan to insert-first, catching IntegrityError and returning the existing active run instead of raising.
3. Test simulating duplicate intake across two sessions asserting exactly one run survives.

Branch: fix/9-intake-race-db-constraint.

---

Generated by Claude Code