Instead of on every start: the schema is defined in one place again. Postgres gets
idx_message_topic_published_id (topic, published, id), SQLite idx_topic_published_time (topic,
published, time), both with IF NOT EXISTS (ntfy.sh got the Postgres one by hand). A downgrade to
v2.31.0 or earlier now needs the schema version set back by hand, as described in the release notes;
from v2.31.1 on, a newer schema only logs a warning (#2059).
Marking a claimed message published was wrong in the way a reviewer would
notice first: published is read by four other queries (since= replays, polls,
scheduled-message replacement, expiry), so a claimed-but-undelivered message
looked delivered to all of them, and a node dying after the claim lost the
push entirely.
The claim is now its own column. claimed_at holds the row off from the other
nodes, the claim statement is a single CTE that locks what it picks and skips
what another node is already claiming, and MarkPublished stays where it was,
after dispatch. So published still means delivered, and delivery is still at
least once.
The claim expires after two minutes, which is what makes a dead claimer
harmless: nothing would ever deliver its rows again if the claim were
permanent. Two minutes is far more than dispatching a claimed batch takes
(side effects are goroutine handoffs), and the claim also takes at most 1000
rows, so a backlog drains over several ticks instead of one node holding all
of it.
Schema 15 -> 16 adds the column; the migration test asserts the migrated
database is structurally identical to a fresh one, so the ALTER and the CREATE
TABLE cannot drift.
Tests: concurrent callers get disjoint sets, an abandoned claim is invisible
until it expires and claimable afterwards, and a published message never comes
back. Both halves of the reclaim test fail if the claim filter is dropped.
binwiederhier