[DATA]: Make bounty application approval race-safe and constraint-backed

[grantfox-oss]

Context

Application creation checks for duplicates before inserting, and approval checks for an existing approved application before updating. Both are check-then-write flows, so concurrent requests can pass the checks and commit conflicting rows.

Scope

• Add a database uniqueness guarantee for one application per (payout_id, grantee_id).
• Enforce at most one approved application per payout using an appropriate PostgreSQL constraint/index or transactional locking strategy.
• Make approval update the application and payout/grantee relationship atomically.
• Define deterministic conflict responses for concurrent create/approve requests.
• Validate that only eligible, published bounty payouts can receive applications.
• Add migration handling for any pre-existing duplicates or multiple approvals.

Acceptance criteria

• Database constraints—not only application code—protect both invariants.
• A concurrency test with parallel requests yields exactly one successful create/approval.
• Losing requests receive a stable typed 409, not a generic 500.
• The migration detects and reports conflicting production rows before enforcing constraints.
• Approval side effects commit or roll back as one transaction.
• API docs explain conflict semantics.

Relevant areas

BountyApplication in prisma/schema.prisma and all bounty-application API routes.

[p3ris0n]

hi maintainer, can i work on this? ETA 24hrs