Internal CRM

A person-first CRM for platform operators, built into the admin console. The primary axis is the contact (a person, who can exist before signup), linked M:N to billing accounts. All data lives in crm_* tables in the app database; conversations and campaigns stay in their source systems (FreeScout, MooSend) and are mirrored as light references and timeline events.

Security invariant (migration 20260710120000_admin_crm_foundation.sql, do not weaken): every crm_* table is service-role-only: RLS is ENABLED with no client policies (the platform_admins precedent). CRM data is unreachable from app-side APIs and from the anon/authenticated roles; all reads and writes go through the admin perimeter (requirePlatformAdmin()) or trusted ingest (webhooks, workers over Hyperdrive).

Data model

Migration 20260710120000_admin_crm_foundation.sql (plus the crm_merge_contacts RPC in 20260710150000_admin_crm_phase2_merge.sql):

Table	Purpose
crm_contacts	The person. Unique lowercase email (follows the login address after a self-serve email change, the previous one staying a non-primary alias); source (event, referral, inbound, moosend, signup, support, manual); GDPR lawful_basis (consent, legitimate_interest, customer); do_not_contact; optional unique user_id link to auth.users; last_sign_in_at synced nightly; tags; merged_into_id dedupe tombstone
crm_contact_accounts	Person to billing account, M:N; relationship owner/member/unknown
crm_events	Append-only unified timeline. occurred_at is event time, not ingest time; unique dedupe_key makes every ingest path idempotent; source in (stripe, resend, freescout, moosend, app, crm, system)
crm_deals	Fixed-stage pipeline: lead, contacted, engaged, won, lost. Manual creation only; self-serve signups never generate deals
crm_notes	Manual notes on a contact and/or account
crm_tasks	Follow-up engine (status + due_at)
crm_revenue_events	Stripe amounts, the only money in Postgres. type payment/refund/dispute; billing_account_id nulls on account deletion (financial history survives), stripe_customer_id keeps traceability
crm_support_threads	FreeScout conversation refs (link, not content); status active/pending/closed; created_by_user_id (app-created tickets and conversations the email-change fan-out re-points, authoritative ownership) and customer_email (address the conversation was opened under, written once on first sight) back the support routes' recycled-address-safe ownership rule
crm_account_metrics_daily	Nightly account-level analytics rollup (downloads, feed unique clients, episodes published, storage bytes)
crm_account_health	Transparent rules-based churn score (0 to 100) + risk_level + a signals jsonb where every input's contribution is spelled out as a readable string
crm_email_messages	One-to-one tracked sends (outreach); status sent/delivered/opened/clicked/bounced/failed

Deletion semantics: contact deletion is real deletion (GDPR; dependent rows cascade) and refuses user-linked rows at the API layer. Billing-account deletion nulls account references on timeline/revenue rows and cascades pure-account rows (links, metrics, health).

LTV = SUM(payment) - SUM(refund) - SUM(dispute) per account, computed per currency at read time (src/api/routes/admin/crm.ts), alongside the latest recurring-invoice payment as the honest MRR estimate.

Endpoints

All mounted under /api/admin and running the full perimeter chain (requireAdminPerimeter() then requireAuth() then per-admin rateLimit() then requirePlatformAdmin()). Every mutation appends to the immutable admin_action_log as a best-effort side effect, except contact merge, whose RPC folds the log append into the same transaction.

Read surfaces for the account page (src/api/routes/admin/crm.ts)

Endpoint	Returns
GET /accounts/:id/crm/overview	Health + revenue summary + value stats + recent activity
GET /accounts/:id/crm/timeline	Unified event stream, cursor-paginated, filterable
GET /accounts/:id/crm/revenue	Revenue ledger + LTV / last-recurring-payment summary
GET /accounts/:id/crm/analytics	Per-podcast cards + cumulative value stats

The timeline is a read-time UNION: crm_events merged with projections of data already in Postgres (usage_events, episodes.published_at, bookings, notification_deliveries); nothing already local is ever copied into crm_events.

Login email changes are part of that union. The completed change is projected from user_email_history (the auth trigger's audit row: old, new, when) as account.email_changed, on the account stream for every member and on the contact stream for the contact's linked user. The REQUEST, which lives nowhere once the settings action returns, is appended to crm_events by the action as account.email_change_requested (source app, payload new_email + sent): sent is false when the target already belonged to another account and nothing went out, which is the fact an agent needs for a "no confirmation email arrived" ticket.

Contacts directory (src/api/routes/admin/crm-contacts.ts)

Endpoint	Purpose
GET /crm/contacts	Searchable/filterable list (source, tag, lifecycle); the search term also matches every alias in crm_contact_emails, so a contact is found under an address it changed away from
POST /crm/contacts	Quick-add: contact + optional note + optional follow-up task
GET /crm/contacts/export	CSV export (doubles as subject-access-request support)
GET /crm/contacts/:id	Contact 360 (identity, accounts, deals, notes, tasks, timeline)
PATCH /crm/contacts/:id	Edit identity/compliance fields
DELETE /crm/contacts/:id	Real deletion (GDPR; children cascade); refuses user-linked rows
POST /crm/contacts/:id/merge	Absorb a duplicate via the atomic crm_merge_contacts RPC

Lifecycle is derived, not stored (mutually exclusive buckets): customer = linked to a billing account on a paid plan_key; user = has an auth user but no paid link; prospect = no auth user. Computed in code over the full filtered id set so lifecycle filters and pagination totals stay exact (bounded by MAX_DIRECTORY_ROWS = 10000).

Working surfaces (src/api/routes/admin/crm-work.ts)

Endpoint	Purpose
GET /crm/dashboard	Tasks due/overdue, pipeline summary, recent events, contact counts
GET /crm/deals, POST /crm/deals, PATCH /crm/deals/:id	Pipeline board; won/lost stamps closed_at, reopen clears it
GET /crm/notes, POST /crm/notes, PATCH /crm/notes/:id, DELETE /crm/notes/:id	Notes; edit/delete are author-only
GET /crm/tasks, POST /crm/tasks, PATCH /crm/tasks/:id	Tasks; done stamps completed_at; assignee defaults to the acting admin

Mutations that tell the customer story (deal created / stage changed, note added, task done) also append to the crm_events timeline.

One-to-one outreach (src/api/routes/admin/crm-outreach.ts)

POST /crm/contacts/:id/email sends ONE personal email to ONE contact through sendInternalEmail() (unmetered platform mail, no tenant account involved) from CRM_OUTREACH_FROM_EMAIL, with Reply-To pointed at a FreeScout mailbox (CRM_OUTREACH_REPLY_TO) so replies arrive as helpdesk conversations and the FreeScout webhook stitches them onto the same contact's timeline. Guardrails: do_not_contact hard-blocks sending (409, never overridable here); merged tombstones are read-only history (409); bulk stays in MooSend; every send is audit-logged. Tracking lands in crm_email_messages, stamped by the Resend webhook (POST /api/notifications/webhooks/resend in src/api/routes/notifications/index.ts: events that match no notification delivery fall through to crm_email_messages by provider_message_id). Tracking is delivery-level only (sent/delivered/bounced/failed); open/click tracking is disabled on the sending domain by decision (2026-07-10, it broke Supabase auth redirects), so the opened/clicked statuses and columns are schema-supported but dormant.

The crm-sync worker (workers/crm-sync/)

Two crons (wrangler.toml), Hyperdrive-only bindings.

03:35 UTC nightly: three independent stages

A failing stage never blocks the others; failures aggregate into one thrown error so the run surfaces as failed in Workers observability.

1. Identity sync (src/sync.ts), the ONLY writer of the contact/user/account linkage. Signup-to-contact creation deliberately lives here, not in the signup flow: product code must never know the CRM exists (one-way dependency rule); a linking lag of up to 24h is accepted. Five idempotent steps (ON CONFLICT / IS DISTINCT FROM guards throughout):

Step	What it does
syncContactsFromAuthUsers	Contact per auth user; links existing prospect contacts by email (the prospect-to-customer conversion), upgrading lawful_basis to customer but never overwriting name/source (original attribution preserved)
syncContactAccountLinks	Mirrors billing_account_members into crm_contact_accounts (owner stays owner, everything else is member). Departed-member rows are intentionally kept: historical involvement is CRM signal
syncLastSignIn	Copies auth.users.last_sign_in_at onto linked contacts (the dominant churn input)
appendSignupEvents	One user.signed_up timeline event per linked contact, dedupe key signup:{user_id}
linkOrphanRevenueEvents	Self-heals revenue rows recorded before their billing account existed, matching on stripe_customer_id

The worker reads auth.users directly (the Hyperdrive connection owner role); last_sign_in_at is never projected into public.

2. MooSend engagement poll (src/moosend.ts), only when MOOSEND_API_KEY is set (skipped with a log line otherwise). MooSend has no global event stream, so marketing engagement arrives via a nightly REST pull: mailing-list subscribers become contacts (source='moosend', lawful_basis='consent', ON CONFLICT DO NOTHING so existing CRM data is never overwritten) plus one list.subscribed event per contact/list; unsubscribed members set do_not_contact = TRUE (never auto-cleared) plus one email.unsubscribed event sharing the webhook's dedupe key; campaigns delivered in the last 45 days yield per-recipient campaign.sent/opened/clicked events. The window re-polls nightly; dedupe keys make repeats free. Blocked sender domains (shared src/lib/crm/email-filter.ts, extended by CRM_EMAIL_DOMAIN_BLOCKLIST) never mint contacts.

3. Metrics rollup + health scoring (src/health.ts):

• runMetricsRollup upserts crm_account_metrics_daily for a trailing 7-day window (self-healing the hourly analytics pipeline's lag); storage_bytes is the current account total stamped on each day.
• runHealthScoring runs the transparent rules-based churn scorer. Not a model: every signal's value, points, and reasoning lands in signals as a readable string. Weights: sign-in recency 40, publish cadence vs the account's own baseline 25, audience trend 20, billing 15. past_due or cancel_at_period_end floors the level to critical regardless of score; 2+ active support threads worsen the level by one; accounts younger than 30 days are clamped to at-worst watch (never at-risk purely from missing history), though the billing floor still wins over the clamp. Scope: accounts with at least one podcast OR a paid plan. Risk-level transitions append a health.risk_changed timeline event (dedupe: one per account per day); first-ever scores log only when they land on at_risk/critical.

06:00 UTC daily: admin digest (src/digest.ts)

Skipped entirely when empty (anti-fatigue: money-critical events already alert immediately from the Stripe webhook) or when RESEND_API_KEY / RESEND_FROM_EMAIL are unset. Sections: overdue

• due-today tasks; accounts that newly entered at_risk/critical in the last 24h (with the scorer's reasons, fed by health.risk_changed events); yesterday's notable events (new paying accounts, cancellations scheduled, payment failures) and refunds/disputes from crm_revenue_events. Recipients: every platform_admins row, resolved to emails via auth.users. Sent directly through the Resend REST API (the worker carries its own minimal sender; $lib is unreachable from standalone workers) with the same unmetered posture as other operator mail.

Inbound webhooks

Both are public Hono routes mounted in src/api/index.ts (/api/webhooks/freescout, /api/webhooks/moosend); neither is admin-host-bound, so their perimeter is cryptographic.

FreeScout (src/api/routes/webhooks/freescout.ts)

• Perimeter: X-FreeScout-Signature = base64(HMAC-SHA1(raw body, secret)) compared constant-time against FREESCOUT_WEBHOOK_SECRET; the event name rides in X-FreeScout-Event. Invalid signature is 400.
• FreeScout retries a failed delivery up to 10 times over ~2 hours, so the handler returns 2xx only after all required writes succeed; every timeline append is idempotent via crm_events.dedupe_key.
• Events handled (anything else is a 200 no-op): convo.created, convo.status, convo.customer.reply.created, convo.agent.reply.created mirror into crm_support_threads and append ticket.* events; convo.moved refreshes the mailbox label; convo.deleted drops the mirror row (timeline history survives); customer.created ensures a contact exists.
• Unknown customer emails auto-create contacts with source='support', lawful_basis='legitimate_interest'. Spam conversations are never mirrored; a mirrored thread that later turns spam is dropped.
• FREESCOUT_MAILBOX_MAP maps numeric mailbox ids to addresses AND doubles as an allowlist: the FreeScout instance is shared across businesses, so conversations from unmapped mailboxes are acknowledged and ignored.

MooSend (src/api/routes/webhooks/moosend.ts)

• MooSend's automation webhook action cannot sign requests, so the perimeter is a long random token as a path segment (/api/webhooks/moosend/{token}, also accepted as ?token=), compared constant-time against MOOSEND_WEBHOOK_TOKEN. A wrong token is a flat 404.
• Wired for UNSUBSCRIBE/COMPLAINT automations only; every other marketing signal arrives via the nightly poll. On a match: do_not_contact = TRUE plus one email.unsubscribed event with the SAME dedupe key as the poll (moosend:unsub:{email}), so either path records it exactly once.
• The payload shape is undocumented and trigger-dependent, so the subscriber email is found by a bounded-depth search for any email-named field. GET/HEAD and empty/unparseable bodies are acknowledged as validation pings (the MooSend UI probes the URL when saving).
• After the token check, every acknowledged outcome returns the same response body so a token holder cannot use response differences as a CRM-membership oracle.

Stripe (context)

The Stripe webhook feeds the CRM as a side effect: it records crm_revenue_events via recordRevenueEvent() (src/lib/crm/revenue.ts) and appends lifecycle timeline events via appendCrmEvent() (src/lib/crm/events.ts), both dedupe-keyed. This is the source of the revenue ledger, LTV, and the digest's money sections.

Related

• Admin perimeter for the middleware chain every endpoint runs
• CRM Sync worker stub
• Webhooks API