Staff Newsletter — Data Model
Owned table
staff_newsletter_configs
One row per organization. Not per staff member — the owner decides what the whole roster receives.
| Column | Type | Notes |
|---|---|---|
id | uuid pk | |
organization_id | uuid, unique, FK → organizations (cascade) | One config per org, enforced by staff_newsletter_configs_org_unq |
events | jsonb, not null, default {} | { [event]: cadence } |
created_at / updated_at | timestamptz |
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.
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:
| Event | Read from |
|---|---|
newLead | organization_leads (org scope + created_at) joined to leads (the person) |
newMember | memberships + users |
newInboundMessage | conversation_messages + conversations + leads/users |
cancellationRequest | subscriptions (cancellation_notice_at) + plans + users |
retentionCaseAttention | subscriptions + tasks + organizations.retention_sla_hours |
paymentFailed | payment_transactions (status='failed', charge types only) |
newDebt | subscriptions (debt_since in window) + plans |
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.senttarget_type/target_id=organization/ the org idmetadata.cadence=daily|weeklymetadata.window=YYYY-MM-DD(daily) orYYYY-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. Nothing new is stored.
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.