Analytics — Data Model

Analytics is read-only and owns no tables. Each method projects from the operational schema.

Tables read

Table	Used in
`subscriptions`	MRR, plan distribution, outstanding debt, at-risk members.
`payment_transactions`	Collected MTD, revenue trend.
`memberships`	Members summary, growth, activation, at-risk, coach overview.
`users`	At-risk sample, members activation (Clerk linkage).
`invitations`	Members activation (`invited` bucket).
`class_sessions`	Popular classes, class utilization.
`class_types`	Same.
`bookings`	Same.
`plans`	Plan distribution.
`workouts`, `workout_assignments`, `workout_results`, `personal_records`	Workouts summary.
`organizations`	Timezone normalization where required (currency, locale).

Computed fields

MRR

For every active non-deleted subscription:


mrr_contribution = subscription.price_in_cents / INTERVAL_MONTHS[subscription.interval]

INTERVAL_MONTHS:

Interval	Months
`weekly`	0.2308
`monthly`	1
`quarterly`	3
`yearly`	12

The constant lives in analytics.service.ts.

Member activation buckets

imported — users.clerk_id IS NULL AND no pending invitation.
invited — invitations.status = 'pending'.
activated — users.clerk_id IS NOT NULL.

activatedActive is the intersection of active membership ∧ Clerk-linked user. Use this denominator for engagement metrics — it excludes import placeholders.

Class utilization

bookings count / class_session.capacity averaged across sessions in the window. Sessions with capacity = null are excluded.

Trend buckets

Date-bucketed by date_trunc('month', x) for monthly trends. Labels are localized at render time on the client.

Indexes assumed

subscriptions(membership_id, status, deleted_at)
payment_transactions(organization_id, created_at, status)
memberships(organization_id, status, role)
class_sessions(organization_id, starts_at)
bookings(class_session_id, status)
workout_assignments(organization_id, scheduled_for)

Drops to these will produce visible latency regressions on the analytics tab.

CSV outputs

Synchronous GET /analytics/export?tab=<tab> writes a CSV directly to the response stream. Per-tab row shape:

Tab	Rows
`revenue-summary`	1 row (the summary object flattened).
`revenue-trend`	N month rows `{ bucket, label, totalInCents }`.
`plan-distribution`	N plan rows `{ planId, planName, interval, activeSubscriptions, monthlyRevenueInCents }`.
`members-summary`	1 row.
`members-growth`	N month rows.
`popular-classes`	N rows.
`class-utilization`	`byClassType[]` rows.
`workouts-summary`	1 row.