Skip to main content

Knowledge > Products > FuneralWiseAI > Safety-Logging Design

FuneralWiseAI — Safety-Logging Tenant-Scope Design

Status: Design for founder review. No code on any live path changed; no DDL applied. Draft migration: churchwiseai-web/migrations/DRAFT-funeral-moderation-tenant-scope.sql.

TL;DR​

moderation_violations — the shared safety-violation log written by both the chatbot and the voice agent — has church_id → churches(id) and organization_id → organizations(id) foreign keys. A funeral tenant's id lives only in funeral_homes(id), so a tenant-scoped safety write for a funeral home violates both FKs and is schema-impossible. Same defect on the sibling user_restrictions (abuse-escalation policy).

The life-safety notification path is intact — funeral crises still write crisis_events (which already uses tenant_id, no FK) and still fire the support@ alert and 988. What is broken is observability + restriction policy for funeral tenants: their safety violations never land in the table the founder's dashboards, the funeral admin Inbox "Crisis" chip, and voice repeat-abuser blocking all read.

Recommendation: converge moderation_violations and user_restrictions onto the exact shape crisis_events already ships — a plain tenant_id uuid (no FK) + vertical text, backfilled from church_id, with the two FKs dropped. This is Option A (below), chosen because it matches the proven sibling-table precedent and needs no per-vertical column branching in writers.

Current live behavior (verified 2026-07-02)​

Traced by reading the writers/readers and querying the DB (wrwkszmobuhvcfjipasi).

The schema gap​

TableTenant columnsFKs
moderation_violationschurch_id, organization_idchurch_id → churches(id); organization_id → organizations(id) ON DELETE CASCADE
user_restrictionschurch_id, organization_idsame two FKs
crisis_events (sibling, already fixed)tenant_id (NOT NULL), verticalnone on tenant_id
  • Moelker funeral demo id e0f4a11e-f00e-4a11-b000-000000000001: 0 rows in churches, 0 in organizations, 1 in funeral_homes. So it satisfies neither FK.
  • moderation_violations holds 506 rows — all with church_id set, organization_id NULL. Actively used, church traffic only.
  • RLS: rowsecurity on, not forced. SELECT policy is org-scoped via profiles.organization_id (matches nothing today — all rows are church_id); INSERT policy is service_role WITH CHECK (true). Readers use the service role.
  • No >3-identical-row trigger and no other trigger on moderation_violations (only crisis_events has an updated_at trigger).

What happens TODAY when a funeral visitor trips moderation​

Funeral chatbot (/api/chatbot/stream, non-church vertical handler, src/app/api/chatbot/stream/route.ts):

  • Runs streamText with stopWhen: stepCountIs(1) — no tools, so flag_safety_concern (the only chatbot path that calls logViolation → moderation_violations) never fires.
  • The pre-LLM crisis regex writes crisis_events via logCrisisEvent({ tenant_id, vertical }) (works — no FK) and fires sendCrisisAlertToSupport.
  • Net: a funeral chat crisis is captured in crisis_events and emails support@, but produces zero moderation_violations rows. Non-crisis abuse/threat/predatory in funeral chat are not logged to moderation_violations at all.

Funeral voice (voice-agent-livekit, safety.py._log_violation → moderation.py.log_moderation_violation):

  • Writes church_id = church_data["church_id"] = the funeral tenant id → FK violation (moderation_violations_church_id_fkey) → caught by log_moderation_violation's try/except → logged "Failed to log moderation violation" → silently swallowed.
  • Same silent failure for the belt-layer writes output_filter_confidentiality (AI-bridge confidentiality guardrail hit) and gloss_failure_safety (multilingual pre-LLM safety fail-safe) — LIFE-SAFETY-adjacent observability that vanishes for funeral.
  • Crisis/threat/DV still write crisis_events via core/escalation.py.handle_safety_event (tenant_id + vertical, no FK — works) and still email support@.

Consequences (observability + policy, NOT the crisis response)​

  1. Funeral admin Inbox "Crisis" chip is empty. funeral.ts.funeralInboxFeedQuery reads moderation_violations.eq('church_id', tenantId) (line ~372) — funeral directors never see safety flags in their inbox. (crisis_events is not surfaced there either — a separate gap.)
  2. Founder platform safety view undercounts. /api/admin/founder-stats aggregates moderation_violations counts (crisis / abuse_severe+predatory / total) with no tenant filter — funeral safety events are invisible platform-wide.
  3. Per-tenant funeral safety dashboards are blank. /api/admin/moderation and /api/admin/safety-stats filter by church_id — a funeral tenant sees nothing.
  4. Voice repeat-abuser blocking degrades on funeral lines. get_abuse_history counts moderation_violations by caller phone; rows never land, so escalation can't accumulate. user_restrictions has the same FK, so a funeral tenant cannot accrue cooldown/temp/permanent blocks.

Calibration for the founder: this is an observability + abuse-policy gap, not a life-safety response gap. A funeral visitor in crisis still gets 988 and support@ is still alerted, because that path runs on crisis_events. The fix restores the record-keeping and repeat-abuse half of the system for funeral tenants.

Options considered​

Option A (RECOMMENDED) — converge on the crisis_events shape​

Add nullable tenant_id uuid (no FK) + vertical text to moderation_violations and user_restrictions; backfill tenant_id = church_id, vertical = 'church'; drop the two FKs; keep church_id/organization_id columns during the transition. Writers then set tenant_id + vertical for every vertical (church writes tenant_id = church_id too); readers migrate church_id → tenant_id.

  • Pros: byte-for-byte matches the sibling crisis_events convention the multivertical refactor already chose, so writers need no per-vertical column branching and readers need one column. crisis_events proves it runs safely without an FK. Smallest cognitive load; one obvious "the tenant is tenant_id" rule across all safety tables. Non-breaking to aggregate readers.
  • Cons: loses DB-level referential integrity on the tenant (mitigated: crisis_events already runs this way; the safety log intentionally spans heterogeneous tenant tables). Requires a follow-up reader PR to actually surface funeral rows.

Option B — drop/soften only the FKs, keep church_id as the tenant column​

Just drop the churches/organizations FKs and let church_id hold any tenant id.

  • Pros: smallest migration; zero new columns; existing readers/writers keep working for funeral immediately once the FK is gone.
  • Cons: overloads church_id to mean "any tenant" — actively misleading in a funeral/vet/RE context and diverges from crisis_events (tenant_id + vertical). No vertical discriminator, so the founder dashboard can't split safety by product line. Entrenches the naming debt the refactor is trying to retire. Rejected.

Option C — per-vertical violation tables (funeral_moderation_violations, …)​

  • Pros: clean FK per vertical.
  • Cons: fragments the one place safety observability must be unified; every reader (founder-stats, inbox, dashboards, retention cron, CI safety assertions) must fan out across N tables; the AI-bridge safety story becomes N stories. Directly contradicts the "one shared safety log" design. Rejected.

Option D — FK to a new unified tenants table​

  • Pros: restores real referential integrity across all verticals.
  • Cons: a portfolio-wide refactor (every tenant table would need to register), far beyond this gap, and the safety-log table is the wrong forcing function for it. crisis_events already declined this. Defer as a possible future; not this change.
  1. Migration (this draft). Add tenant_id + vertical, backfill, drop FKs, add indexes on both tables. DDL only — no writer/reader behavior changes yet. Apply, then verify: existing 506 rows have tenant_id = church_id, vertical = 'church'; a funeral-id insert now succeeds.
  2. Writers. Set tenant_id + vertical on every write (church included), keeping church_id populated during transition:
    • Chatbot: src/lib/moderation.ts.logViolation, src/lib/chatbot-tools.ts.flagSafetyConcern (thread vertical + tenant id through ToolContext). Also decide whether the funeral vertical chatbot handler should log non-crisis safety at all (today it logs none) — a separate product call, not required to close the schema gap.
    • Voice (LIFE-SAFETY GATE): voice-agent-livekit/moderation.py.log_moderation_violation and safety.py._log_violation write tenant_id/vertical from church_data. moderation.py is a CODEOWNERS LIFE-SAFETY file — requires voice-agent-engineer + founder review and the moderation contract tests green before any change, and a voice deploy is a separate founder-gated step. Do not fold this into a general PR.
  3. Readers. Migrate per-tenant reads church_id → tenant_id (or .or(church_id.eq,tenant_id.eq) during transition): funeral.ts inbox query, /api/admin/moderation, /api/admin/safety-stats, inbox-stream.ts. Aggregate readers (founder-stats, daily-audit retention) need no change. Optionally split founder safety stats by vertical.
  4. Backfill: covered in step 1 for existing rows. No historical funeral rows exist to recover (writes were failing), so there is nothing to reconstruct.
  5. (Later, optional) enable the "tenant present" CHECK (left NOT VALID/commented in the draft) once writers are confirmed setting tenant_id, and eventually drop church_id/organization_id from these tables.

Blast-radius analysis​

  • RLS: the current org-scoped SELECT policy matches nothing (all rows church_id) and readers use the service role, so dropping the FKs does not change RLS enforcement. If a future tenant_id-scoped SELECT policy is added, scope it per vertical; not required now.
  • Triggers: none on moderation_violations; nothing to update. No >3-identical-row guard on this table.
  • Existing dashboards: aggregate founder-stats and the retention cron use no tenant filter → unaffected by the column/FK change. Per-tenant church dashboards keep working (they read church_id, still populated). Funeral dashboards stay blank until the reader PR (step 3) — expected.
  • Retention: daily-audit deletes moderation_violations older than 1 year by created_at — unchanged; applies uniformly to funeral rows once they exist.
  • crisis_events: untouched by this migration; it is the reference shape, already correct.
  • Referential integrity: dropping FKs means an orphaned tenant id could be written. Accepted, matching crisis_events; the optional CHECK (step 5) is the guard against a NULL-tenant regression, which is the real risk on a safety write.

LIFE-SAFETY review gates​

  • voice-agent-livekit/moderation.py and safety.py are CODEOWNERS LIFE-SAFETY paths — voice-agent-engineer + founder review required, moderation/contract tests green, and the voice-agent deploy is its own founder-gated step (one deploy hits every customer line).
  • src/lib/crisis-events.ts and core/escalation.py are CODEOWNERS-gated too but are not modified by this design (they already use tenant_id).
  • The chatbot logViolation/flagSafetyConcern changes are the primary-safety-observability write (moderation_violations is the source of truth for the founder ModerationDashboard + agent-sim CI safety assertions) — cover with the existing e2e/safety/moderation-violations-writer.spec.ts and the agent-sim safety cases; add a funeral-tenant case so the regression can't recur.
  • Apply the migration and verify BEFORE shipping any writer change, so a writer setting tenant_id never races a table without the column.

Files​

  • Draft migration (unapplied): churchwiseai-web/migrations/DRAFT-funeral-moderation-tenant-scope.sql
  • This design: knowledge/products/funeralwiseai/safety-logging-design.md
  • Writers (reference): src/lib/moderation.ts, src/lib/chatbot-tools.ts (flagSafetyConcern ~2540), src/app/api/chatbot/stream/route.ts (non-church vertical handler ~146–546), voice-agent-livekit/moderation.py (log_moderation_violation ~349), voice-agent-livekit/safety.py (_log_violation ~646)
  • Readers (reference): src/lib/verticals/funeral.ts (~372), src/app/api/admin/moderation/route.ts, src/app/api/admin/safety-stats/route.ts, src/app/api/admin/founder-stats/route.ts (~112), src/lib/inbox-stream.ts (~155), src/app/api/cron/daily-audit/route.ts (~805)
  • Reference shape: src/lib/crisis-events.ts, voice-agent-livekit/core/escalation.py (handle_safety_event ~205)