Rule updates intermittently fail with P2002 on RuleHistory (ruleId, version) — version is assigned non-atomically

Summary

Updating a rule can fail with a Prisma P2002 unique constraint violation on RuleHistory (ruleId, version). The rule update itself succeeds, but the history write throws. Because the write is queued into after(), the error surfaces as an unhandled background failure.

Error

Unique constraint failed on the fields: (`ruleId`, `version`)
code: 'P2002'
meta: { modelName: 'RuleHistory' }
clientVersion: '7.9.1'

An error occurred in a function passed to `after()`

Cause

In apps/web/utils/rule/rule-history.ts, the next version number is derived by reading the current maximum and incrementing it:

orderBy: { version: "desc" },
select: { version: true },
// ...
version: nextVersion,

This is a read-then-write with no atomicity or uniqueness handling. Two history writes for the same rule that overlap will both read the same maximum, both compute the same nextVersion, and the second insert violates the unique constraint.

Why it shows up in practice

It is most visible through the AI assistant. When the assistant performs a multi-step rule edit (update a rule, then create another), the history writes can overlap. In our case the assistant updated a rule successfully, emitted its next intention as text, and then the turn ended without performing the follow-up action — the RuleHistory P2002 was thrown at the same second as the last assistant message. From the user's perspective the assistant simply stops mid-sequence with no error shown in the UI.

Impact

• Rule changes succeed but history records are silently lost
• Assistant multi-step edits can terminate early, leaving the requested work half-done
• No user-visible error; the failure only appears in server logs

Suggested fix

Make version assignment safe under concurrency, e.g.:

• a unique-violation retry loop around the insert, or
• a monotonic counter on the parent Rule updated in the same transaction, or
• a database-side sequence per rule

Handling P2002 specifically and retrying would be the smallest change.

Environment

• Self-hosted, Docker Compose
• Prisma client 7.9.1, PostgreSQL 16