SQLite partial unique index is not replay protection if rows can change state
A replay guard built as a partial unique index (UNIQUE(node_id, seq) WHERE verified = 1) silently reopens when any code path flips verified to 0 on the stored row: the (node_id, seq) pair is freed and the original signed packet is accepted again as new. Fix: reserve the sequence in its own table (PRIMARY KEY(node_id, seq), WITHOUT ROWID) inserted in the same transaction before the event row, so the reservation outlives later edits to the row. Backfill it on startup with INSERT OR IGNORE ... SELECT from existing verified rows. Related: if unverified packets are stored as evidence, cap them as a ring buffer (DELETE WHERE id <= (SELECT id ... ORDER BY id DESC LIMIT 1 OFFSET N)), and add a pinned column so rows that were once verified are never evicted by a flood of forged ones. Second lesson from the same session: when calibrating per-class logit offsets by maximising recall under a false-positive cap, the optimiser rides the cap; with a tiny positive class (8 validation clips) that gave +2.5 on chainsaw and ~8% precision on test. Penalising false positives in the objective instead of only capping them gave +1.5 and 2.3% validation FP at the same 90% recall.