Summary
The recurrence/dedup path uses SELECT ... FOR UPDATE SKIP LOCKED, which is a row lock — it cannot protect the no-existing-row case. Two concurrent same-fingerprint webhooks can both find nothing and both INSERT, producing two "open" incidents for one fingerprint. There is no unique constraint to catch it.
Evidence
sentinel/persistence/repositories.py:576-585 — SELECT ... FOR UPDATE SKIP LOCKED over existing rows; on miss, INSERT at repositories.py:643-653.sentinel/persistence/models.py:89 + migration 0001_initial.py:71 — idx_incidents_fingerprint is a non-unique index. No UNIQUE(fingerprint, status) constraint exists in any migration.
Failure mode
Two same-fingerprint webhooks arrive concurrently → both SELECT miss (nothing to lock) → both INSERT → two open incidents, breaking the 1h dedup/append guarantee.
Fix
- Add a partial unique index, e.g.
UNIQUE (fingerprint) WHERE status = 'open', and handle the conflict via ON CONFLICT → fall through to the recurrence/append path.
- Reversible migration (
upgrade/downgrade), tested against real Postgres.
Summary
The recurrence/dedup path uses
SELECT ... FOR UPDATE SKIP LOCKED, which is a row lock — it cannot protect the no-existing-row case. Two concurrent same-fingerprint webhooks can both find nothing and both INSERT, producing two "open" incidents for one fingerprint. There is no unique constraint to catch it.Evidence
sentinel/persistence/repositories.py:576-585—SELECT ... FOR UPDATE SKIP LOCKEDover existing rows; on miss, INSERT atrepositories.py:643-653.sentinel/persistence/models.py:89+ migration0001_initial.py:71—idx_incidents_fingerprintis a non-unique index. NoUNIQUE(fingerprint, status)constraint exists in any migration.Failure mode
Two same-fingerprint webhooks arrive concurrently → both
SELECTmiss (nothing to lock) → both INSERT → two open incidents, breaking the 1h dedup/append guarantee.Fix
UNIQUE (fingerprint) WHERE status = 'open', and handle the conflict viaON CONFLICT→ fall through to the recurrence/append path.upgrade/downgrade), tested against real Postgres.