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

[tlongwell-block]

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"):

             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.

[tlongwell-block]

Corrected measurement tables, plus a B-class note on trigger copying. All figures below re-derived against live catalogs, not quoted from my earlier report — two of my original numbers were counting-unit errors, which Eva has already noted in the issue body.

Object counts, corrected

Functions were the smeared one. mig = 55 included 36 pgcrypto-owned functions; desired-state paths have zero because pgschema silently skips CREATE EXTENSION pgcrypto (drift B). Splitting by extension ownership separates the two drifts:

db	provisioning	standalone fns	extension-owned fns	total
mig	SQLx migrations via psql -1	19	36 (pgcrypto)	55
des	pgschema apply of schema.sql	13	0	13
raw	plain psql of schema.sql	13	36 (pgcrypto)	49
des3	pgschema apply of patched schema.sql	19	0	19

The real drift-A function gap is 19 − 13 = 6, exactly the six functions the text detector names. raw ≡ des on standalone functions (13 each) is the control showing the applier contributes nothing to drift A — the gap is in schema.sql's text.

Partitions, corrected

My earlier "114 attached partitions" counted partitioned-index children alongside table partitions, because the query filtered pg_inherits by name pattern without constraining relkind. Correct form is relkind IN ('r','p'):

db	table partitions	partitioned-index children
mig	14	100
des (unpatched)	14	92
des2 (schema patch, old attach script)	0	0
des3 (schema patch + fixed attach script)	14	100

No desired-state path was ever missing table partitions. The 8-child difference between mig and des is precisely the per-partition children of idx_events_tags_gin across events' 8 partitions — drift A surfacing in the catalog, not a separate defect.

B-class note: pgschema copies parent triggers onto partition children

Worth recording because it turns a schema fix into an outage on fresh desired-state stacks.

pgschema emits partition children as standalone CREATE TABLEs and copies the parent's triggers onto them. PostgreSQL recreates inherited parent triggers during ATTACH and rejects a same-named trigger already present on the child. scripts/attach-schema-partitions.sql handled this with a hardcoded list of four trigger names.

Adding the missing guard triggers to schema.sql therefore broke attachment outright — ATTACH failed with trigger "trg_events_guard_nip_rs_hard_delete" for relation "events_p_past" already exists, leaving des2 at 0 attached partitions. A fix for drift became worse than the drift.

The failure is invisible to schema-text checking: every table exists, they are simply not attached. Only a live catalog query sees it. Deriving the drop list from pg_trigger instead of hardcoding removes the rot — verified at 14/92 against unpatched schema.sql (matching the des baseline, so the derivation is safe on its own) and 14/100 against the patched one.

Fail-open guard, now demonstrated rather than argued

Hard delete without the corrected-writer opt-in, same seed on each path:

db	outcome	rows surviving	watermark rows
mig	raises NIP-RS hard delete requires corrected writer opt-in	1	1
des3	raises, same message	1	1
des (unpatched)	no error, row silently deleted	0	0

Trigger presence confirms the mechanism: trg_events_guard_nip_rs_hard_delete is on the parent plus all 8 children on mig and des3, and entirely absent on des.