fix(db): seek replaceable heads by channel - #9147

🤖 Automated review of a948a4337c (base cec918ae): no blocking findings.

The two new spellings, channel_id = $4 for a channel write and channel_id IS NULL for a channel-less one, select exactly the rows the old IS NOT DISTINCT FROM $4 did, for both the head read and the supersede. The coordinate lock, the newest-first/lowest-id ordering, the stale-write check, the roster guard and the rollback path are unchanged, including through LockedGroupStateSnapshot::replace. Each branch binds exactly the parameters its SQL uses. With all four equality columns in the scope, idx_events_addressable (community_id, kind, pubkey, channel_id, deleted_at) takes the channel as an index condition. The UPDATE events literals stay inside the &mut AdmittedTx function, so the guarded-write source scan still covers them.

Run against an isolated PostgreSQL 17 at this head: both new tests pass, along with all 18 replaceable-module tests, the 2 channel replacement-lock tests and the 222 default buzz-db tests and source-policy checks. With the new tests moved onto the base commit, the scope-behavior test passes and the plan test fails in all four cases (head read and supersede, channel and channel-less), with channel_id only in Filter. So the plan test catches the regression it is meant to catch.

Nonblocking:

• The plan assertion accepts a channel_id index condition from any node in the text plan. events is partitioned, so a match on one node doesn't show that every populated partition scan seeks by channel. Checking the JSON plan and requiring the condition on the populated partition scans would make the test exact.
• The plan test builds its own copy of the supersede string ("UPDATE events SET deleted_at = NOW() " + scope) rather than sharing production's, so the two prefixes could drift apart. That copy is what keeps the UPDATE events literal inside the admitted function for the source scan, so it's a fair trade.

🤖 Automated review (Auto PR Review v1) of head 14576a0610: no blocking findings.

This head adds one commit on top of the previously reviewed a948a4337c, with the same base. That commit only changes the test replacement_channel_predicate_is_an_index_condition. It now reads the plan as EXPLAIN (FORMAT JSON), finds the partitions that actually hold the seeded community's rows, and requires every one of those partitions to be scanned exactly once, with a channel_id index condition either on the scan itself or on the bitmap index scan beneath it. This closes the earlier nonblocking note that the plan check passed when any single node matched. A sequential scan on a populated partition, a missing partition, or a duplicated partition now fails the test.

Checked:

• The partition names come from pg_class.relname via tableoid, which matches the unqualified Relation Name in the JSON plan. Partitions with no seeded rows are ignored on purpose.
• The bitmap path recurses through Plans, so a Bitmap Heap Scan passes only when an index child seeks by channel_id.
• The supersede statement still comes from the shared superseded_with_mentions_sql! / replacement_scope! macros, so the test plans the same SQL the store runs.
• CI's PostgreSQL Tests job at this exact head ran the test, and it passed (store::replaceable::postgres_tests::replacement_channel_predicate_is_an_index_condition).

Kyle reviewed the PR and the Nexus review-tracker notes and approved with no comments.

Posted by Codex (gpt-6.1-sol) on behalf of kylez