Insights — Data Model

Insights are a derived view — no dedicated table. They read from:

Source table	Used for	Key columns
`cancellation_requests`	`finance.cancellation_requests`	`organization_id`, `status`
`subscriptions`	`finance.cancellation_requests`, `finance.outstanding_debt`, `members.at_risk_billing`	`membership_id`, `status`, `debt_amount_in_cents`, `cancel_at_period_end`, `deleted_at`
`memberships`	All rules	`organization_id`, `role`, `deleted_at`
`users`	`members.at_risk_billing` sample	`first_name`, `last_name`, `email`
`tasks`	`operations.tasks_overdue`	`organization_id`, `due_date`, status enum

Response payload


interface Insight {
  id: InsightId;          // i18n key + telemetry tag
  category: InsightCategory; // 'finance' | 'members' | 'operations'
  severity: InsightSeverity; // 'info' | 'warning' | 'urgent'
  count: number;          // primary metric
  drilldownHref: string;  // deep link with query params
  meta?: Record<string, unknown>; // per-rule extra context
}

Meta shapes

finance.cancellation_requests → { pending: number; scheduled: number }.
finance.outstanding_debt → { totalDebtInCents: number; members: number }.
members.at_risk_billing → { sample: Array<{ membershipId: string; name: string | null; email: string }> }.
operations.tasks_overdue → { ... } (see service for current shape).

Index dependencies

The rules assume the following indexes exist (set by their respective domain schemas):

memberships(organization_id, role).
subscriptions(membership_id, status, deleted_at).
cancellation_requests(organization_id, status).
tasks(organization_id, due_date).

If any of these are dropped, the insights endpoint will slow proportionally — there’s no fallback path.

Locale

Server is locale-agnostic. The frontend localizes by id against the dictionary; new locales need new i18n keys, not new server code.