Staff Newsletter — Data Model

Owned tables

staff_newsletter_configs

One row per organization. Not per staff member — the owner decides what the org sends; each recipient narrows it for themselves in staff_alert_prefs.

events is JSONB for the same reason notification_prefs.prefs is: the canonical event and cadence catalogs live in @taikan/shared, so adding an event costs no migration. The strict types are applied at the application boundary (StaffNewsletterConfigSchema).

A missing row, or a missing key, does not mean “off.” It means DEFAULT_STAFF_NEWSLETTER_CONFIG — cancellationRequest: 'daily' and retentionCaseAttention: 'daily' — the retention mail staff already relied on. Resolution happens in @taikan/shared (cadenceFor), so the API, the crons and the settings screen all agree on what an absent key means.

off is a real stored value, not an absence: it is how an org overrides a default that would otherwise switch the event back on.

staff_alert_prefs

One row per (user, organization) — an owner/admin’s personal opt-out. Created on the first mute; most recipients never have one.

Unique on (user_id, organization_id) (staff_alert_prefs_user_org_unq), so the same person muting newLead at one gym keeps it at another.

Stored as opt-outs: an event missing from the array is received, so an event added to the catalog later reaches everyone without a backfill. Writes go through applyStaffAlertPatch (@taikan/shared), which stores each mute once, in catalog order, and drops events no longer in the catalog. “Stop all alert emails” (a digest’s footer link) is expanded to today’s catalog rather than stored as a wildcard, so the settings screen can turn one event back on.

A row for someone who is no longer owner/admin is inert — recipientsFor never selects them. Platform user erasure (AdminUsersService) deletes the user’s rows explicitly, since erasure tombstones the users row rather than deleting it and the cascade never fires.

Read, not owned

Digests are built by querying state over a window, so the feature reads across six domains and writes to none of them:

leads carries no org — the same human can be a lead at two gyms — so org scoping lives on organization_leads, and its created_at is the one the window wants.

Send bookkeeping

No table. One audit_events row per successful digest:

• action = staff_newsletter.sent
• target_type / target_id = organization / the org id
• metadata.cadence = daily | weekly
• metadata.window = YYYY-MM-DD (daily) or YYYY-Www (ISO week, weekly)
• metadata.sections = [{ event, variant, count }]

The claim is written before the send. wasSent() reads it back on the (org, action, target, cadence, window) tuple.

Immediate sends are not claimed: they are one-per-domain-event, and the producers already emit once.

PII handling

Emails carry member and lead names, contact details, plan names, amounts and a one-line message preview (truncated to 80 chars). Sent through Resend, same processor as every other mail. staff_alert_prefs stores only which event types a staff member muted.

The footer’s unsubscribe token carries the recipient’s user id and org id in base64 (signed, not encrypted). It grants exactly one power — muting that person’s alerts in that org — and expires after 90 days.

Multi-org isolation

Every digest query is scoped to one organization_id and the recipient list is resolved from that org’s active owner/admin memberships. The immediate path takes its org id from the domain event, never from the row it then reads.

Soft / hard delete

The config row cascades with its organization. Deleting it is equivalent to resetting to defaults, not to switching everything off. staff_alert_prefs rows cascade with their user or organization.