buzz-db: drop the created_at floor, community write-fence and migration-only NIP-RS triggers (contract) - #8326

Combined contract PR. This PR now carries all three trigger drops that were stacked as #8326 → #8323 → #8211. Each drop's prerequisite app change has already shipped to production, and none of them depends on another, so they ship as one release with one migration per drop:

• 0089: events_created_at_floor (this PR's original scope, BUZZ-316; phase 1 buzz-db: check events created_at floor in app writers (phase 1) #8325 is on prod)
• 0090: the universal community write-fence triggers and functions (from buzz-db: drop the universal community write-fence triggers (contract) #8323, BUZZ-315; app admission buzz-db: admit every fenced-table writer in app code #8219/fix(db): admit fleet sweeps per tenant instead of in-SQL fence predicate #8318/buzz-db: tolerate absent universal write-fence objects (expand) #8322)
• 0091: the migration-only NIP-RS, mention-live and mesh-purge triggers (from feat(buzz-db): drop the migration-only NIP-RS, mention-live and mesh-purge triggers #8211, BUZZ-312; prerequisite buzz-db: purge retention-free deletions in the app and index mentions in the event transaction #8128)

#8323 and #8211 keep their full write-ups. The commits are cherry-picked unchanged except for the renumbering commit at the tip. #8211's post-rollout cleanup SQL still runs after this deploys.

Summary

Phase 2 of BUZZ-316. This is a destructive contract change. Phase 1, #8325 (merged), added the insert-time application floor check.

After phase 1, the app check at every channel-bearing INSERT INTO events is the only created_at floor guard. This PR removes the database backstop and everything that existed only to verify it:

• Migration 0089 drops the deferred constraint trigger events_created_at_floor on events. That also removes its partition clones. It then loops over pg_partition_tree('events') to drop any child copy that was attached separately, and drops the events_created_at_floor_guard() function. Every drop uses IF EXISTS and none uses CASCADE. The migration sets lock_timeout to 5s. Migration 0021 stays byte-for-byte unchanged.
• Desired state: removes the function file, its schema.sql include, the trigger in schema/triggers/events.sql, and the eight child-copy drops in scripts/reconcile-schema-after-pgschema.sql. Both boot paths stay catalog-equivalent, and admin_schema_parity_between_desired_state_and_migrations passes.
• Runtime: deletes verify_floor_guard_catalog, verify_floor_guard_behavior, and the boot wrapper that gated the probe on them. Also deletes the writer-pool buzz.created_at_floor GUC arming and its CreatedAtFloor connection-setup metric step. The setup-series ceiling drops from 71 to 54. spawn_fence_probe becomes synchronous and returns bool, so the relay's startup error branch goes away. CREATED_AT_FLOOR_SECS (960s) stays because the app check and the fence wall both use it.
• Kept: every other fail-closed probe check. That covers writer-pool isolation and timeouts, partition topology, the activity scan, the heartbeat/epoch checks, and closing on probe error. No other trigger, function, FK, or tenant fence is touched.

Deploy gate (required)

Required stop: the new docs/trigger-retirement-deployment.md names it (docs/database.md Rule 5). Relay deployments track images built from main and no release is being cut, so a docs follow-up rewords it as any relay build at or after 68785206 (#8325's merge). relay-v0.3.0 has the 0090/0091 prerequisites but not #8325, so a deployment on it must move to such a build first. The doc also covers the auto-migration-off order, the pgschema parent-trigger note, the #8211 cleanup, and rollback.

1. Phase 1 must be running on every writer pod and job, with no rollback expected. Pods that are already running never recheck the guard, so a rolling deploy on its own is not enough.
2. Stop out-of-band writers: direct SQL, COPY, restores, seed scripts, and anything that prepares transactions.
3. Hold the replica breaker closed from before the drop until replay catches up and the new scheme is verified.
4. Run the migration off-peak. DROP TRIGGER takes ACCESS EXCLUSIVE on events and every partition, so expect events reads and writes to stall for up to the 5s lock_timeout. A lock timeout aborts the migration transaction and is safe to retry. Under auto-migrate, the pod fails boot, restarts, and retries.

Mixed-version behavior:

• A phase-1 pod that restarts after the drop, default config (migrate.autoMigrate: true in the chart): sqlx finds applied migration 89 missing from its embedded set and returns VersionMissing(89). Boot is fatal and the pod crash-loops. A binary rollback is therefore unsafe once 0089 has run.
• The same pod with auto-migrate off: verify_floor_guard_catalog fails, the fence starts closed, and the relay degrades the ReplicaFenceProbe startup step. It keeps serving, with cursor reads on the writer.
• This binary against a database that still has the trigger: boots and runs. It neither requires nor arms the guard.

Tests

crates/buzz-db/src/runtime/tests/floor_retirement.rs adds four PostgreSQL tests. CI's nextest filter picks them up through postgres_tests:::

• the migration drops the parent trigger, its partition clones, and the function, and leaves every other trigger and FK unchanged;
• the migration removes a separately attached child copy and can safely run twice;
• the desired-state catalog has no floor objects;
• the production writer pool no longer arms the GUC, and the probe runs against the retired schema.

All four fail when the change is reverted (0/4 pass) and pass with it (4/4). Four tests that only exercised the trigger or the probes are deleted. The outbox durable-failure regression is kept: it now injects an isolated BEFORE INSERT failure instead of arming the floor. The phase-1 floor fixture no longer disables a trigger that no longer exists. It still asserts that no enabled floor trigger can mask the app check.

With the trigger gone, the floor source policy (every_production_channel_event_insert_checks_the_floor_and_never_rewrites_it) is the only guard against a writer that skips the app check or rewrites created_at/channel_id on an existing row. Phase 1 already makes it scan every crate's src. This PR closes two matcher gaps the trigger used to cover:

• UPDATE events SET(created_at, …) written with no space before the parenthesis;
• INSERT INTO events … ON CONFLICT … DO UPDATE SET channel_id = …, which names no table but can re-home an existing row below the floor. Each DO UPDATE is bound to the INSERT it belongs to, so a watermark upsert that sets created_at next to a guarded events insert (the replaceable-event writer's shape) is not flagged.

Both gaps have negative fixtures, plus controls for a non-floor-column upsert, DO NOTHING, and the watermark shape.

The policy also now fails closed on SQL the per-function literal scan could not attribute to a writer (event_floor_policy_fails_closed_on_sql_it_cannot_attribute):

• include_str! is expanded from the file it loads, and concat! becomes one literal, with an {opaque} placeholder for each non-literal argument. Every delimiter ((), [], {}) and path prefix (::core::concat!) is recognized.
• Every SQL literal is checked on its own as well as per function. Anything the policy cannot attribute is a violation: a write target or SET column that is a placeholder (concat! or format!); a SET list of floor columns whose UPDATE is in another literal; an UPDATE events or DO UPDATE whose SET list continues in another literal or argument (empty, or an item with no =, as in format!("UPDATE events SET {}", …) or QueryBuilder::new("UPDATE events SET ").push(…)); an INSERT INTO with no table after it in the literal, or an events INSERT with a placeholder tail; events written by MERGE INTO/COPY; and event-write SQL in a const, static, or macro body outside any function. A placeholder in a WHERE or a value is allowed, so the retention deletes (concat! around a predicate macro) and the tenant-severing UPDATE {table} SET community_id = NULL pass.
• An include_str! whose path is not one readable literal and any sqlx query_file*! macro are violations. Nothing uses query_file.
• PREPARE TRANSACTION in any production literal, including a dangling PREPARE, is a violation (see below).
• Function slices are cut to their own item, so a trailing const is judged at item level instead of being credited to the function before it.

Prepared transactions. The fence used to bound a prepared (2PC) transaction by pg_prepared_xacts.prepared. That only held because the deferred trigger checked its rows at PREPARE; the insert-time check runs earlier, at a time pg_prepared_xacts does not expose. So the probe no longer folds prepared into the wall: any prepared transaction fails the sample closed (ProbeError::PreparedTransactions). No supported writer prepares transactions (now source-policy enforced) and max_prepared_transactions defaults to 0, so in supported deployments this never fires. cluster_global_sample_writer_fails_closed_on_prepared_transaction asserts 2PC is unavailable at the default setting, and asserts the fail-closed error when the server allows it. CI runs only the default branch; the 2PC-enabled branch and a check-removed falsification run are pinned from the Blox gate. The prepared count is cluster-wide, like the activity scan.

Verification

Head 6ef31ef113 (before the fold). On Blox, fmt, clippy and buzz-db unit pass, and the full buzz-db PostgreSQL lane passes except media_prebuild_waits_for_old_transactions_during_creation_and_cleanup, which fails only on the Blox host (it passes in CI). The two floor_retirement tests were fixed after that run, so they were rerun on their own and pass at this head.

What got simpler

The change is net −600 lines. Gone are one function, one parent constraint trigger plus its partition clones, two startup probes and their scratch helper, one boot wrapper, the per-connection GUC setup, one metric step, and an async startup branch that could no longer be reached.

Notes

• Migration number: 0089, the first free number after main's 0088 (workflow run cause). The fence drop (0090) and NIP-RS drop (0091) follow it in this PR.
• Restack: floor_retirement.rs now snapshots at 0088 and compares after 0089 alone, then runs the later migrations. The held-writer media prebuild test in migration.rs now stops at 0088 while the writer is held, then rolls the writer back before applying the rest. Otherwise a later DROP TRIGGER migration in the stack waits on that writer's lock (55P03). The floor policy's SET list ends at a top-level WHERE or RETURNING (a subquery's WHERE no longer hides later assignments). An UPDATE SET list that runs to the end of its literal fails closed, and an open events upsert list is read on through later literals (QueryBuilder pushes). events_p* partitions count as events in every check. replacement_scope! now leaves WHERE to its callers so the policy can see the supersede UPDATE's list end; the SQL is unchanged.
• Pre-existing, unrelated to this change: three buzz-relay group_state_postgres_tests hit PoolTimedOut on a clean base too.