### Why?
This is a cleanup that brings the schema bootstrap in line with how the
codebase works today, as asked for by
[`docs/database.md`](dc25198d53/docs/database.md (L103-L124))
(#8273). Writes are now blocked during community deletion by application
admission (`begin_community_write_transaction`), and that doc's Rule 1
says new tables must not add triggers. But
`schema/community_write_fence.sql` still looped over every table with a
`community_id` column and attached the old fence trigger to it. That
meant a new table got a trigger on a fresh database and none on a
migrated one, so the two schema paths disagreed and new tenant tables
were blocked. The doc says this bootstrap change must land "before the
feature, or as the feature's first PR."
### What?
Deletes the blanket fence loop and its `\i` from `schema/schema.sql`.
Existing legacy tables keep their fences through their explicit
`schema/triggers/` files, so no current table loses its fence and
runtime behavior is unchanged. Adds a test that a new `community_id`
table stays unfenced while `events` and `users` stay fenced, and updates
`docs/database.md` and `ARCHITECTURE.md` to match. It stands on its own;
the media-sharing PRs (#8451, #8444) are the first to rely on it.
buzz-review-completed
---------
Signed-off-by: Nikolaj Goodger <ngoodger@squareup.com>
Co-authored-by: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
## Summary
Adds `docs/database.md`, which covers two things:
- **The target architecture.** `schema/tables/public/` is the
declarative source of truth, written within the constraints that
out-of-band, online schema tools can apply. Deployments at scale apply
schema changes out of band; auto-migration stays supported for smaller
and self-hosted deployments, and `migrations/` stays. The database has
no triggers, functions, or foreign keys; that behavior lives in the
owning application transaction or in durable application work. Data
repair runs through the backfill framework in `docs/data-backfills.md`,
not through migrations.
- **Rules for schema changes made now:**
1. Add no triggers, functions, procedures, rules, or foreign keys, with
no exceptions. New tenant-owned tables are fenced by community admission
in the application. Startup and deletion validation already tolerate
missing fences, but desired-state bootstraps still attach a fence
trigger to every `community_id` table, so a new tenant-owned table is
blocked until that loop stops (part of the fence-retirement work). The
doc includes checklists for adding a tenant-owned table and a
provenance-only table.
2. Migrations change schema only. They never mutate existing rows. Until
the backfill framework ships, a repair may be a reviewed, idempotent,
batched one-off script in `scripts/maintenance/` that takes the
community deletion lock and touches only active communities. A repair
that writes historical channel events must keep replica routing closed
until its WAL has replayed.
3. Update every schema location: `migrations/`, `schema/tables/public/`,
the manifest and sibling directories, the pgschema reconcile script
where it applies, and both homes of the existing `push_gateway_*` tables
(the target is for the gateway to own its tables alone). Never edit a
merged migration. Add catalog-level parity tests for what you touch.
4. Every migration must be safe for the relay that is currently
deployed, under both supported modes: auto-migration at startup and
out-of-band application. Either way the old relay runs against the new
schema for a while. Because auto-migration can apply expand and contract
in one boot, a contract migration must fail closed when its backfill has
not run, and a contract that depends on a writer release (such as
dropping a trigger whose work moved into the app) must name that release
as a required stop in the release notes, because no row check can prove
it. The order is expand, deploy, backfill, then contract. The doc
includes a table of changes that break the old relay and the safe form
of each.
5. Bound every lock, build `migrations/` indexes concurrently (the
declarative schema uses plain `CREATE INDEX`), and keep changes within
what out-of-band apply can make online.
AGENTS.md (Product Contract), ARCHITECTURE.md (buzz-db), and new
path-local `migrations/AGENTS.md`, `schema/AGENTS.md` (a pointer), and
`crates/buzz-push-gateway/migrations/AGENTS.md` point to the doc. The
path-local files mean agents editing those directories get the rules
automatically. AGENTS.md also had a stale claim that migrations are
"auto-applied on relay startup". This PR corrects it:
`BUZZ_AUTO_MIGRATE` defaults off in the binary and on in the Helm chart,
and both modes are supported.
## What got simpler
The rules were scattered across `schema/schema.sql` comments, AGENTS.md
Common Gotcha #7, the backfill spec, `docs/thread-window-deployment.md`,
and test names. They now live in one document, and the rest link to it.
No code changes.
## Testing
This PR changes documentation only. I checked links and diffs, and every
claim in the doc points to code at the base commit: the layout and
parity tests in `crates/buzz-db/src/runtime/migration.rs`,
`EXPECTED_SCOPED_TABLES` and the serving-catalog check in
`crates/buzz-db/src/store/deletion.rs`, and `BUZZ_AUTO_MIGRATE` in
`crates/buzz-relay/src/main.rs`. The new `AGENTS.md` files aren't
`.sql`, so neither the sqlx migrator nor the schema layout test picks
them up; CI's layout, migration-count, and deletion-parity tests pass.
At an earlier head, a real relay against isolated PostgreSQL 17 showed
that a new unfenced `community_id` table serves normally and makes
community deletion report `unknown=<table>`. Since then, the fence
requirement at startup was removed on main; the doc now cites
`migration_schema_fence_objects_are_optional_for_serving_and_deletion`
for that.
---------
Signed-off-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
Co-authored-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
