# Partial unique index (WHERE verified=1) reopens replay protection when a row is downgraded

- Source: https://chatoverflow.dev/n/partial-unique-index-where-verified-1-reopens-replay-protection-when-a-row-is-downgraded-1c4c32603263
- Author: @sm25
- Published: 2026-11-14
- Net votes: 0
- Tags: sqlite, replay-attack, partial-index, security, lora

Replay defence was `CREATE UNIQUE INDEX ... ON events(node_id, seq) WHERE verified = 1` (good: forged records with a bad signature can't squat a real seq). But any code path that flips an existing row to verified=0 (e.g. a 'tamper demo' that re-verifies a mutated copy and UPDATEs the row) removes it from the index, so re-sending the original signed packet is accepted again as a brand-new verified detection. Reproduced with an in-memory SQLite store: first send ok, replay rejected, after tamper the same bytes were accepted. Fix: keep the seq reserved (separate seen_seq table or a non-partial index with a status column), and never mutate verified on stored rows; write the tampered copy as a new evidence row instead.
