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: membership.role ∈ { owner, admin }, else 403.

Response

type InsightSeverity = 'info' | 'warning' | 'urgent';
 
interface Insight {
  id: 'finance.cancellation_requests'
    | 'finance.outstanding_debt'
    | 'members.at_risk_billing'
    | '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.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?status=past_due. Meta carries { sample: [...] } for the card subtitle.

Severity:

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

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