Insights — Behavior

Surface

• HTTP: GET /organizations/:orgId/insights → { data: Insight[] }.
• Agent tool: analytics.org_insights (read, never destructive) wraps the same service.

Auth

1. MembershipsService.requireMembership(orgId, clerkId) — must hold an active membership.
2. Role check: isStaffRole(membership.role) (owner, admin, coach), else 403. The caller’s role is passed through to getOrgInsights, which evaluates only the rules that role is allowed to see — coaches get the operational/engagement subset. Unconditional since dashboard-overview-revamp was merged permanently ON (2026-08-22); before that a coach got a flat 403.

Response

type InsightSeverity = 'info' | 'warning' | 'urgent';
 
interface Insight {
  // Abridged — `InsightId` in @taikan/shared is the full, authoritative list.
  id: 'finance.cancellation_requests'
    | 'finance.outstanding_debt'
    | 'members.at_risk_billing'
    | 'members.join_never_paid'
    | 'members.presale_withdrawn'
    | 'members.recent_churn'
    | 'operations.tasks_overdue';
  category: 'finance' | 'members' | 'operations';
  severity: InsightSeverity;
  count: number;
  drilldownHref: string;
  meta?: Record<string, unknown>;
}

The frontend resolves i18n strings keyed by id:

• insight.finance.cancellation_requests.title / .description
• insight.finance.outstanding_debt.title / .description
• insight.members.at_risk_billing.title / .description
• insight.members.join_never_paid.title / .description
• insight.members.presale_withdrawn.title / .description
• insight.members.recent_churn.title / .description
• insight.operations.tasks_overdue.title / .description

Rules

finance.cancellation_requests

Counts:

• cancellation_requests rows with status = 'pending' for this org.
• subscriptions rows where cancel_at_period_end = true AND deleted_at IS NULL, joined to org via membership.

Returns count = pending + scheduled. Severity is warning when pending > 0, else info (only scheduled cancellations).

Drilldown: /dashboard/payments?tab=cancellation-requests. Meta carries { pending, scheduled }.

finance.outstanding_debt

Sums subscriptions.debtAmountInCents over the org’s non-deleted subscriptions with debt > 0. Counts the distinct membership rows that contribute. Returns nothing when total is 0.

Drilldown: /dashboard/payments?tab=debt. Meta carries { totalDebtInCents, members }.

Severity is always warning.

members.at_risk_billing

CTE-driven query:

• memberships rows with role = 'member', deleted_at IS NULL, org match.
• INNER JOIN subscriptions with deleted_at IS NULL and status = 'past_due'.
• Group by member; return total count + a sample of up to 3 members { membershipId, name, email } ordered by name.

Drilldown: /dashboard/members?paymentStatus=at_risk. Meta carries { sample: [...] } for the card subtitle.

(It previously linked ?status=past_due, which silently did nothing: past_due is a subscription status and the members screen’s status filter only accepts membership statuses, so the filter fell back to “all”.)

Severity:

• count >= 10 → urgent.
• count >= 3 → warning.
• otherwise info.

members.join_never_paid

Registered through a join link and never paid a shekel: no live subscription, nothing ever activated, no settled transaction, and not a presale withdrawer (that is the rule below). A 24h grace on membership age keeps a member who is mid-funnel out of the card.

Every one of these is holding a tier seat and reading “Active” on the members screen while paying nothing — the inert population a public join link accumulates. Actionable both ways: chase them, or let the flag-gated sweep (join-membership-release) demote them to CRM leads.

Drilldown: /dashboard/members?paymentStatus=not_paying. Severity: count >= 10 → urgent, else warning.

members.presale_withdrawn

Members who backed out of a scheduled presale sale before opening day and hold nothing live now. Their membership stays — this is the win-back list, and it clears when they re-buy or staff cancel them.

Drilldown: /dashboard/members?paymentStatus=withdrawn. Severity: count >= 5 → urgent, else warning.

members.recent_churn

Ordinary churn, windowed to the last 30 days so it stays an action rather than a history lesson: an activated subscription ended and nothing live replaced it. Older churn stays reachable through the members screen’s churned filter.

Drilldown: /dashboard/members?paymentStatus=churned. Severity tiers mirror at_risk_billing.

The three rules above share one liveSubExists() predicate and each requires no live subscription, so they can never claim the same member twice.

operations.tasks_overdue

Counts tasks rows with due_date < today AND status not completed for this org. Severity tiers by count (mirrors at_risk_billing).

Drilldown: /dashboard/tasks?tab=overdue.

Ordering

All four rules run in parallel via Promise.all. Null returns (no insight) are filtered. The remaining records are sorted by:

1. SEVERITY_ORDER: urgent (0) → warning (1) → info (2).
2. count descending.

Performance

• All rules use indexed columns: memberships(organization_id, role, deleted_at), subscriptions(membership_id, status, deleted_at), cancellation_requests(organization_id, status), tasks(organization_id, due_date).
• Each rule is a single SQL statement; members.at_risk_billing is a CTE doing one scan.
• Total response time on a 500-member org is < 100ms under typical load.
• No caching — recomputed on every request. The dashboard fetches once per mount.

Localization

• Server returns data-only — no human-facing strings.
• Title / description / drilldown labels live in apps/web/src/i18n/{en,he,ru}.json under insight.*.
• Severity, count, and meta values flow into the strings via {count}, {totalDebtInCents}, etc.

Future-proofing

• Adding a new insight: implement an eval<Rule> method returning Insight | null, add it to Promise.all, and add a new entry to the InsightId union. Frontend adds matching i18n keys.
• Severity rules per insight are local to the rule method — no global thresholds table.

Failure modes