The scheduler's post-run `attach_scheduled_workflow_run` does `SET workflow_run_id`,
but neither schema/schema.sql nor the initial migration declared that column, so
every won scheduled claim would create+run, then the best-effort attach would hit
`column "workflow_run_id" does not exist` (PG 42703) and warn forever — the audit
link could never populate. Found by Max in cold review of 778a5d28c.
- Add nullable `workflow_run_id UUID` to scheduled_workflow_fires (schema + migration)
with a composite FK `(community_id, workflow_run_id) REFERENCES workflow_runs
(community_id, id)`. The FK uses ON DELETE NO ACTION, not SET NULL: community_id is
shared with the claim PK and is NOT NULL, so SET NULL is unimplementable (verified
against live PG: it raises a NOT NULL violation mid-cascade). NO ACTION blocks a
delete of a still-linked run cleanly; workflow_runs are not pruned today regardless.
- Rewrite the stale scheduled-fires schema comment that still claimed community is
'resolved server-side from workflow_id, never a caller-supplied claim parameter' —
contradicted by the S1 reconciliation: community is server provenance from
list_all_enabled_workflows(), passed explicitly, since id is not globally unique.
- Surface the interval-anchor read failure with a warn! instead of unwrap_or(None)
swallowing it (still fail-closed: a missing anchor suppresses the tick and retries).
- Add attach_links_run_to_claim_and_is_idempotent: proves the column populates on
attach and the IS NULL guard makes a second attach a no-op. Proven RED against the
pre-migration schema (the exact 42703 error), GREEN after — the regression that
would have caught this gap.
Verified vs live Postgres, serial: buzz-db 110 / buzz-workflow 145 / buzz-relay 414,
0 failed; clippy -D warnings clean on the trio.
Co-authored-by: Tyler Longwell <tlongwell@block.xyz>
Signed-off-by: Tyler Longwell <tlongwell@block.xyz>
The Typesense→Postgres FTS rewrite replaced out-of-band indexing with
`search_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('simple', content))
STORED` over every row. The old relay (handlers/event.rs:287 on main)
deliberately skipped search-indexing for three kind classes, and the new
search query layer has no kind exclusion — so gift wraps, DM-visibility
snapshots, and event reminders were all in the FTS index.
Fix at the storage layer (option A — single source of truth, zero
app-layer drift across multiple search call sites): make the generated
column yield `NULL::tsvector` for excluded kinds via a CASE expression.
A NULL tsvector never matches `@@`, so excluded rows are structurally
unsearchable.
Excluded set, parity with main's `handlers/event.rs:287-290`:
- 1059 KIND_GIFT_WRAP (NIP-17 ciphertext)
- 30300 KIND_EVENT_REMINDER (AUTHOR_ONLY_KINDS — defense in depth)
- 30622 KIND_DM_VISIBILITY (per-viewer private hide state)
Constants are inlined in the migration with a comment naming the
`buzz_core::kind` names: sqlx migrations are frozen SQL and can't
`use buzz_core::kind`; importing core into a migration would be worse
drift than the inline-with-comment shape.
Three coupled layers:
1. Schema CASE in migrations/0001_initial_schema.sql. The 0001 schema
was consolidated by Max in 4b7654a1c (Lane-0 contract) and is
pre-deploy; editing in place rather than adding a new migration
matches the agreed shape and is covered by
`run_migrations_applies_consolidated_initial_schema_on_fresh_database`.
2. buzz-search/tests/fts_integration.rs — new test
`excluded_kinds_are_storage_level_unsearchable` inserts kind:1059 +
kind:30300 + kind:30622 + kind:9 with the same unique token; asserts
only the kind:9 control surfaces. Each excluded kind has its own
load-bearing negative assertion with a diagnostic message naming the
regression, plus a tight exactly-one-hit bound.
Mutate-bite verified: dropping the CASE's NULL branch (revert to
`to_tsvector('simple', content)`) makes excluded kinds searchable
and the test fails RED with the designed message
"kind:1059 MUST NOT be searchable — privacy regression in search_tsv
generated column". Restored → 10/10 green.
3. crates/buzz-test-client/tests/e2e_nostr_interop.rs — rewrote
`test_nip17_gift_wrap_not_searchable` to use the relay's actual
NIP-50 search seam (`Filter::new().search(token)`,
`collect_until_eose`) instead of the now-removed Typesense
`/multi_search`. Pattern cribbed from
`test_nip50_search_returns_results_and_eose`. Pointer comment to
the underlying mutate-bite in fts_integration.rs.
Verification on `quinn/search-kind-exclusions` off `34ffb8ab3`:
- `cargo test -p buzz-search --tests -- --include-ignored`: 10/10
- `cargo test -p buzz-db -- --include-ignored`: 99/99 (incl. migration
lints + consolidated-schema fresh-DB)
- `cargo check -p buzz-test-client --tests`: clean
- `cargo clippy -p buzz-search -p buzz-db --all-targets -- -D warnings`:
clean
- `cargo fmt --all -- --check`: clean
Co-authored-by: Tyler Longwell <tlongwell@block.xyz>
Signed-off-by: Tyler Longwell <tlongwell@block.xyz>
Closes the last Lane-0 schema items before the frozen base:
- events.search_tsv TSVECTOR GENERATED ALWAYS AS to_tsvector('simple',
content) STORED + GIN idx_events_search_tsv. The Typesense->Postgres FTS
data shape, landed in Lane 0 because it touches the just-locked events
table (Quinn option A). GENERATED ALWAYS = single source of truth: proven
against PG that a client cannot forge search_tsv out of sync with content
(generated_always rejection). Index left minimal single-column GIN; the
search lane picks the final spelling after EXPLAIN (Max's caveat).
- Delete stale 0002_backfill_d_tag.sql / 0003_event_reminders.sql. In the
consolidated-from-scratch model 0001 already carries d_tag, not_before,
delivered_at, and idx_events_not_before; re-running the old additive
migrations would error (duplicate column / duplicate index name).
audit_log DDL shape confirmed for the audit-crate collapse (Dawn's lane):
PRIMARY KEY (community_id, seq), UNIQUE (community_id, hash), community_id
NOT NULL on every row. 0001 is the single source; buzz-audit drops its own
schema.rs / AUDIT_SCHEMA_SQL / ensure_schema() in the audit lane.
Re-proven against real Postgres — full fence suite green: T1 re-tenant
rejected, T6 cross-community member FK rejected, T6b same-community ok, T7
same channel UUID in two communities allowed, T8 host case-collision
rejected, T9 same event id in two communities allowed, plus the FTS
generated+GIN match and the forge-rejection. buzz-core: 189 + 2 doctests.
Co-authored-by: Eva <011987e296fd5006292d2f930b574be47c7801048d1983c46c425d3c95f0cffd@sprout-oss.stage.blox.sqprod.co>
Signed-off-by: tlongwell-block <109685178+tlongwell-block@users.noreply.github.com>
The frozen base for the multi-tenant rewrite. Consolidated 0001 schema
makes community_id a first-class, server-resolved key on every scoped
row, mapped table-by-table to docs/multi-tenant-conformance.md.
Schema highlights:
- channels PK is (community_id, id): the same channel UUID may legitimately
co-exist in two communities; child FKs (channel_members, workflows,
thread_metadata) are composite (community_id, channel_id) so a child can
never reference a cross-community channel — DB-enforced, not by handler
discipline. channels.community_id is immutable (BEFORE UPDATE trigger).
- communities.host uniqueness is UNIQUE(lower(host)); normalize_host applies
the same rule on the resolution side, so case/dot/default-port variants
can never split one tenant into two.
- every scoped unique/PK leads with community_id; cross-community dedup of
the same signed event is allowed, within-community dup rejected.
- new tables: communities (host map), scheduled_workflow_fires (the cron
at-most-once claim), audit_log (per-community chain), and an explicit
_operator_global_tables registry the migration lint reads.
buzz-core:
- normalize_host(host): the one shared host-canonicalization rule.
- TenantContext fence doc corrected to say plainly it is a lint-and-review
fence, not a compiler fence (resolved()/from_uuid are pub) — honest about
the guarantee the API actually gives.
Schema proven against Postgres with an adversarial fence suite (re-tenant
rejected, cross-community FKs rejected, same-UUID/same-event cross-community
allowed, host-case collision rejected). buzz-core: 189 tests + 2 doctests
green.
Folds in review round 1 from Mari (channel global-uniqueness leak, host
normalization, fence-claim honesty) and Sami (NIP-98 localhost normalization
to be dropped in the auth lane).
Co-authored-by: Eva <011987e296fd5006292d2f930b574be47c7801048d1983c46c425d3c95f0cffd@sprout-oss.stage.blox.sqprod.co>
Signed-off-by: tlongwell-block <109685178+tlongwell-block@users.noreply.github.com>
