Repair Write Skew Invariants

Project Detected
NestJS TypeScript scheduling application using PostgreSQL, Prisma, multiple API replicas, and transaction-scoped membership updates

---

Invariant Violation Found

• Two concurrent doctor-removal transactions validate the same on-call assignment set and update different assignment rows
• Invariant: Every active hospital shift must retain at least one qualified on-call doctor
• Scope: Hospital shift UUID
• Invalid final state: Shift remains active with zero on-call doctors
• Current isolation: PostgreSQL transaction behavior used through Prisma without an invariant serialization point

---

Transaction Path

• Transaction A: Reads all active on-call assignments for Shift S, observes Doctor B, then deactivates Doctor A
• Transaction B: Reads all active on-call assignments for Shift S, observes Doctor A, then deactivates Doctor B
• Shared predicate: Active qualified on-call assignments for Shift S
• Direct write conflict: No — each transaction modifies a different assignment row
• Current protection: Row update protection only; no shared shift-level lock or invariant constraint

---

Concurrency Window

• Initial valid state: Doctor A and Doctor B are both active on call
• Transaction A observation: Doctor B remains, so removing A appears valid
• Transaction B observation: Doctor A remains, so removing B appears valid
• Transaction A commit: Succeeds
• Transaction B commit: Succeeds under the reproduced unsafe transaction path
• Why the invariant breaks: Both decisions use snapshots taken before either removal becomes visible, and the writes target different rows

---

Invariant Contract

• Authoritative scope: One hospital shift
• Serialization point: Existing shift row locked FOR UPDATE before evaluating assignment coverage
• Validation timing: Re-read qualified active assignments after the shift lock is acquired
• Retry behavior: Database deadlock or transaction-retry errors use bounded retry; a re-read showing only one remaining doctor returns the existing coverage-conflict response
• Domain conflict behavior: Removal is rejected because the shift would lose required on-call coverage

---

Repair Applied

• Transaction boundary: Moved coverage validation and assignment mutation into one transaction
• Invariant protection: Added a shift-level serialization point shared by every on-call assignment mutation
• Lock / constraint / version handling: Added a PostgreSQL row lock on the shift before assignment-state evaluation
• Retry and error mapping: Added bounded transactional retry and mapped coverage failure to the existing domain conflict
• Bypass-path protection: Updated API removal, administrative removal, and scheduled assignment cleanup to use the same protected service

---

Files Modified

• src/shifts/shift.repository.ts
• src/shifts/onCallAssignment.repository.ts
• src/shifts/onCallAssignment.service.ts
• src/shifts/onCallInvariant.service.ts
• src/admin/shiftAssignmentAdmin.service.ts
• src/jobs/expiredAssignmentCleanup.worker.ts
• test/on-call-write-skew.spec.ts

---

Commands Executed

• npm run typecheck
• npm test -- on-call-write-skew.spec.ts
• npm run build

---

Two-Transaction Test

• Initial invariant valid: Yes
• Concurrent transactions started: 2
• Transactions committed: 1 removal
• Transactions retried or rejected: 1 removal rejected after acquiring the shift lock
• Invalid final states: 0

---

Isolation Test

• Isolation requested: Existing PostgreSQL transaction isolation retained; correctness provided by the explicit shared shift lock
• Serialization conflicts observed: 0 required for the tested invariant
• Stale snapshot commits accepted: 0
• Final invariant valid: Yes

---

Retry and Deadlock Test

• Concurrent operations: 100 controlled removal and assignment changes across 20 shifts
• Serialization retries: 0
• Deadlock retries: 2 injected lock-order conflicts recovered within the configured retry budget
• Retry exhaustion: 0
• Duplicate external side effects: 0

---

Validation Result
Type checking, 34 deterministic concurrency, shift-isolation, last-doctor, administrative-path, cleanup-worker, lock-order, and rollback tests, and the production build completed successfully using an isolated PostgreSQL database.

---

Remaining Risks

• Any direct production SQL or future service that mutates on-call assignments without acquiring the shift invariant lock can bypass the application-level protection.

---

Important Notes
No global table lock was added. Different shifts remain independently concurrent because the serialization point is the individual shift row.