Desired-state provisioning drift: schema.sql missing objects from 4 migrations (incl. fail-open NIP-RS guards) + pgschema applier fidelity

## Summary

The desired-state provisioning path (`./bin/pgschema apply --file schema/schema.sql`, used by `scripts/start-isolated-test-relay.sh:100` and `start-relay-for-tests.sh:106`) produces a materially different database than the migration path. Measured at `8a4bf860bf0fd555f73711b52140f2622436066f` on four databases on one Postgres 17 server (migrations via `psql -1`; desired-state via vendored pgschema; plus a raw-`psql` control that separates "schema.sql text is incomplete" from "pgschema rewrote it"):

```text
tables standalone functions extension-owned functions triggers
mig 60 19 36 (pgcrypto) 113
des 58 13 0 75
```

**[Corrected 2026-08-11]** The originally posted `functions=55 vs 13` compared different populations: 36 of the 55 are pgcrypto-owned and their absence is drift B (pgschema silently skips `CREATE EXTENSION pgcrypto`), not drift A. The true drift-A function gap is 19 vs 13 = **6 standalone functions** — exactly the six the text detector names. Similarly, an earlier "114 vs 106 attached partitions" figure counted partitioned-index children alongside table partitions; table partitions are 14 on every provisioning path, and the 8-child index gap is `idx_events_tags_gin`'s per-partition children — drift A again, in the catalog's shape. Correct partition assertion, should anyone want one: `relkind IN ('r','p')` → 14, never a bare name match. Corrected by the original measurer on re-derivation; full corrected tables in comments.

This is a class defect, not a one-off: **0029 is the fourth migration whose objects never reached `schema.sql`.** Findings split by drift source because they have different owners.

## Drift source A: `schema.sql` never received objects from 4 migrations

| migration | missing from schema.sql |
|---|---|
| 0004 | `idx_events_tags_gin` (GIN tags index) |
| 0009/0011 | `guard_nip_rs_watermark`, `guard_nip_rs_hard_delete`, `guard_event_mention_live`, `purge_soft_deleted_nip_rs` + their triggers (replicated across events parent + 9 partitions → 38 trigger identities) |
| 0019 | `purge_soft_deleted_buzz_mesh_status` |
| 0029 (PR #4425) | `community_deletion_manifest_keys`, `storage_taxonomy_sweeps`, `protect_community_deletion_manifest_keys` + guard trigger |

**Safety consequence beyond the deletion engine:** `guard_nip_rs_hard_delete` (0011:45) exists specifically so a pre-migration relay binary cannot hard-delete a live NIP-RS coordinate without transaction-local opt-in (`buzz.nip_rs_hard_delete='on'`) — the migration comment explicitly prefers failing the old writer's transaction over "two live rows and strip the retained row's mentions." On a desired-state install this trigger does not exist, so the guard **fails open**, silently. Same shape for the watermark and mention-liveness guards. The 0029 consequence is tracked as blocking finding (d) on PR #4425 (first deletion on a desired-state install strands the tenant fenced; see PR comments).

**Why nothing catches this:** the only parity checks are hand-written `desired_schema.contains(...)` string assertions (`crates/buzz-db/src/migration.rs:910` region) that must be remembered per-migration — four migrations weren't.

**Fix (A):** add the missing objects to `schema.sql`, and add a derived one-directional detector: every table/function/trigger the migrations create must exist after `pgschema apply` — set-membership on names, derived from both sides, so it cannot rot. Detector + `schema.sql` patch with red→green demonstration are in progress (Dawn).

## Drift source B: pgschema rewrites what it does apply (raw-psql control vs pgschema, same file)

1. **`events` PRIMARY KEY reordered**: source and migrations say `(community_id, created_at, id)`; pgschema produces `(created_at, community_id, id)` on the parent and all 9 partitions. Uniqueness unchanged, but community-leading key order is a stated conformance invariant (`schema/schema.sql:16-20`, lint obligation 2), and its enforcement test (`migration.rs:1084+`) parses migration *text* — nothing checks the deployed catalog, so desired-state installs violate the repo's own invariant invisibly. **Needs an owner decision**: accept (document), work around (column order pgschema preserves), or upstream bug report.
2. **`idx_channels_id_live` loses its INCLUDE**: `(id) INCLUDE (community_id)` becomes `(id, community_id)` — covering→composite; the index 0027 was written to shape.
3. **`pgcrypto` extension never created** despite `CREATE EXTENSION IF NOT EXISTS pgcrypto` at line 24: currently harmless (no repo SQL calls pgcrypto server-side; `gen_random_uuid` resolves from `pg_catalog`), but a live trap for the first migration that uses one.

Recommendation: A is fixable and testable now (blocking-quality); B is a pgschema-behavior conversation and should not gate PR #4425.

## Provenance

Found while reviewing PR #4425 (finding (d) there covers only the 0029 subset; this issue owns the rest of the class). Measurement, controls, and instrument-failure disclosure by Dawn; source-side claims independently re-verified at the same head by Eva. Discussion: buzz-delete-requests channel, thread 4b4545e3.