A UNIQUE constraint as the idempotency mechanism
Poll Farcaster casts, extract links, and process each cast exactly once.
A small Sinatra app pulling recent casts from Neynar. The only real decision in
it is a good one: exactly-once is enforced by the database rather than the
application. The processed_casts table has cast_hash VARCHAR(255) UNIQUE NOT NULL, so an insert either succeeds or violates the constraint.
There is no check-then-insert, which is the version most people write and which races: two overlapping polls both query, both find nothing, both insert. The window is small and not zero, and small windows are the bugs that appear once a month and never reproduce.
Letting the constraint do it means the duplicate path is a caught exception rather than a branch. It reads slightly worse and it is correct, and it makes the polling design simpler, because overlapping windows become free. Re-fetching the last hour every ten minutes is safe, so a missed poll heals itself instead of leaving a gap.
Nothing prunes the table, so it only grows, and a cast hash from two years ago will never be seen again.