📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!
TypeScript chartsclickhousedockergdprgoogle-analyticsmetricsmonitoringmysqlnestjsprivacyredisself-hostedselfhostedstatisticstailwindcssweb-analyticswebanalyticswebsite
import { Injectable, NotFoundException } from '@nestjs/common'
import { InjectRepository } from '@nestjs/typeorm'
import { In, IsNull, MoreThanOrEqual, Not, Repository } from 'typeorm'
import { redis } from '../common/constants'
import { clickhouse } from '../common/integrations/clickhouse'
import { Organisation } from '../organisation/entity/organisation.entity'
import {
OrganisationMember,
OrganisationRole,
} from '../organisation/entity/organisation-member.entity'
import { Project } from '../project/entity/project.entity'
import { CancellationFeedback } from '../user/entities/cancellation-feedback.entity'
import { DeleteFeedback } from '../user/entities/delete-feedback.entity'
import {
ACCOUNT_PLANS,
BillingFrequency,
DashboardBlockReason,
getEffectiveAccountLimits,
getEffectivePlanType,
PlanCode,
PlanType,
TRIAL_DURATION,
User,
} from '../user/entities/user.entity'
import { UserFeedback } from '../user/entities/user-feedback.entity'
// Event types that count towards project activity (parity with the analytics views)
const ACTIVITY_EVENT_TYPES = [
'pageview',
'custom_event',
'outbound',
'error',
'captcha',
'performance',
]
// Billable usage condition - keep in sync with task-manager's plan usage query
const CAPTCHA_PASS_CONDITION =
"type = 'captcha' AND if(indexOf(`meta.key`, 'captcha_event') = 0, 'pass', arrayElement(`meta.value`, indexOf(`meta.key`, 'captcha_event'))) = 'pass'"
const BILLABLE_COUNT_IF = `countIf(type IN ('pageview', 'custom_event', 'error', 'outbound') OR (${CAPTCHA_PASS_CONDITION}))`
const FREE_PLAN_CODES = [PlanCode.none, PlanCode.free, PlanCode.trial]
// Plans a trial cannot be on. `PlanCode.trial` is deliberately absent: it is
// the legacy card-less trial, which is a trial too
const LAPSED_PLAN_CODES = [PlanCode.none, PlanCode.free]
// Since trials became card-first, `planCode` holds the real paid plan for the
// whole trial and `trialEndDate` is the only thing that says "not paying yet".
// Reading trial state off `planCode = PlanCode.trial` (the legacy card-less
// flow) silently matches nobody.
const isTrialing = (user: {
planCode: PlanCode
trialEndDate: Date | string | null
}): boolean =>
Boolean(user.trialEndDate) &&
new Date(user.trialEndDate).getTime() > Date.now() &&
!LAPSED_PLAN_CODES.includes(user.planCode)
const TRIALING_CONDITION =
'user.trialEndDate IS NOT NULL AND user.trialEndDate > :now AND user.planCode NOT IN (:...lapsedPlans)'
const USERS_PAGE_SIZE = 25
const PROJECTS_PAGE_SIZE = 25
const ORGANISATIONS_PAGE_SIZE = 25
const FEEDBACK_PAGE_SIZE = 20
export type FeedbackType = 'user' | 'cancellation' | 'deletion'
interface CurrencyAmounts {
USD: number
EUR: number
GBP: number
}
interface PlanPricing {
monthly: CurrencyAmounts
yearly: CurrencyAmounts
}
// List prices per plan (mirrors web/app/lib/pricing/catalog.ts) - used for MRR estimates
const STANDARD_PLAN_PRICES: Partial<Record<PlanCode, PlanPricing>> = {
[PlanCode.hobby]: {
monthly: { USD: 5, EUR: 5, GBP: 4 },
yearly: { USD: 50, EUR: 50, GBP: 40 },
},
[PlanCode['50k']]: {
monthly: { USD: 12, EUR: 10, GBP: 10 },
yearly: { USD: 120, EUR: 100, GBP: 100 },
},
[PlanCode.freelancer]: {
monthly: { USD: 15, EUR: 15, GBP: 14 },
yearly: { USD: 150, EUR: 150, GBP: 140 },
},
[PlanCode['100k']]: {
monthly: { USD: 19, EUR: 17, GBP: 15 },
yearly: { USD: 190, EUR: 170, GBP: 150 },
},
[PlanCode['200k']]: {
monthly: { USD: 29, EUR: 25, GBP: 23 },
yearly: { USD: 290, EUR: 250, GBP: 230 },
},
[PlanCode['500k']]: {
monthly: { USD: 49, EUR: 42, GBP: 39 },
yearly: { USD: 490, EUR: 420, GBP: 390 },
},
[PlanCode['1m']]: {
monthly: { USD: 79, EUR: 69, GBP: 59 },
yearly: { USD: 790, EUR: 690, GBP: 590 },
},
[PlanCode['2m']]: {
monthly: { USD: 119, EUR: 99, GBP: 89 },
yearly: { USD: 1190, EUR: 990, GBP: 890 },
},
[PlanCode['5m']]: {
monthly: { USD: 179, EUR: 149, GBP: 139 },
yearly: { USD: 1790, EUR: 1490, GBP: 1390 },
},
[PlanCode['10m']]: {
monthly: { USD: 249, EUR: 209, GBP: 189 },
yearly: { USD: 2490, EUR: 2090, GBP: 1890 },
},
[PlanCode['15m']]: {
monthly: { USD: 349, EUR: 299, GBP: 259 },
yearly: { USD: 3490, EUR: 2990, GBP: 2590 },
},
[PlanCode['20m']]: {
monthly: { USD: 419, EUR: 359, GBP: 319 },
yearly: { USD: 4190, EUR: 3590, GBP: 3190 },
},
[PlanCode['30m']]: {
monthly: { USD: 519, EUR: 449, GBP: 389 },
yearly: { USD: 5190, EUR: 4490, GBP: 3890 },
},
[PlanCode['40m']]: {
monthly: { USD: 619, EUR: 529, GBP: 459 },
yearly: { USD: 6190, EUR: 5290, GBP: 4590 },
},
[PlanCode['50m']]: {
monthly: { USD: 719, EUR: 619, GBP: 539 },
yearly: { USD: 7190, EUR: 6190, GBP: 5390 },
},
}
const PLUS_PLAN_PRICES: Partial<Record<PlanCode, PlanPricing>> = {
[PlanCode['100k']]: {
monthly: { USD: 39, EUR: 35, GBP: 29 },
yearly: { USD: 390, EUR: 350, GBP: 290 },
},
[PlanCode['200k']]: {
monthly: { USD: 59, EUR: 49, GBP: 45 },
yearly: { USD: 590, EUR: 490, GBP: 450 },
},
[PlanCode['500k']]: {
monthly: { USD: 109, EUR: 95, GBP: 79 },
yearly: { USD: 1090, EUR: 950, GBP: 790 },
},
[PlanCode['1m']]: {
monthly: { USD: 179, EUR: 159, GBP: 135 },
yearly: { USD: 1790, EUR: 1590, GBP: 1350 },
},
[PlanCode['2m']]: {
monthly: { USD: 279, EUR: 239, GBP: 209 },
yearly: { USD: 2790, EUR: 2390, GBP: 2090 },
},
[PlanCode['5m']]: {
monthly: { USD: 439, EUR: 379, GBP: 329 },
yearly: { USD: 4390, EUR: 3790, GBP: 3290 },
},
[PlanCode['10m']]: {
monthly: { USD: 629, EUR: 539, GBP: 469 },
yearly: { USD: 6290, EUR: 5390, GBP: 4690 },
},
[PlanCode['15m']]: {
monthly: { USD: 919, EUR: 799, GBP: 699 },
yearly: { USD: 9190, EUR: 7990, GBP: 6990 },
},
[PlanCode['20m']]: {
monthly: { USD: 1139, EUR: 979, GBP: 849 },
yearly: { USD: 11390, EUR: 9790, GBP: 8490 },
},
[PlanCode['30m']]: {
monthly: { USD: 1459, EUR: 1249, GBP: 1079 },
yearly: { USD: 14590, EUR: 12490, GBP: 10790 },
},
[PlanCode['40m']]: {
monthly: { USD: 1799, EUR: 1549, GBP: 1329 },
yearly: { USD: 17990, EUR: 15490, GBP: 13290 },
},
[PlanCode['50m']]: {
monthly: { USD: 2159, EUR: 1859, GBP: 1599 },
yearly: { USD: 21590, EUR: 18590, GBP: 15990 },
},
}
type SupportedCurrency = keyof CurrencyAmounts
const { PADDLE_VENDOR_ID, PADDLE_API_KEY } = process.env
// v2: stores raw payments instead of derived summaries
const REVENUE_CACHE_KEY = 'admin:paddle-payments:v2'
// Paddle's vendor API is rate limited; the numbers do not move fast anyway
const REVENUE_CACHE_TTL_SECONDS = 3600
const PADDLE_FETCH_TIMEOUT_MS = 15000
// v2: v1 was fetched with inclusive-looking chunk ends and is missing every
// boundary-day payment - never serve it again
const REVENUE_HISTORY_CACHE_KEY = 'admin:paddle-payments-history:v2'
// History only changes when a new payment lands, and the trends are a
// month-level view - a stale-by-hours cache is fine
const REVENUE_HISTORY_CACHE_TTL_SECONDS = 6 * 60 * 60
// Longer than the longest chart window on purpose: a yearly payment made
// before the window still has to be recognised inside it
const REVENUE_HISTORY_MONTHS = 36
// Paddle returns the whole range in one response - keep each one small
const REVENUE_HISTORY_CHUNK_MONTHS = 3
const REVENUE_HISTORY_CONCURRENCY = 4
export const REVENUE_TREND_MONTHS = [6, 12, 24]
// A subscription billing this far apart is a yearly one, so its payment is
// spread over 12 months instead of landing in a single one
const YEARLY_PERIOD_MIN_GAP_DAYS = 300
const MONTHS_IN_YEAR = 12
const TRIAL_LOOKAHEAD_DAYS = TRIAL_DURATION
const TRIAL_LOOKBACK_DAYS = 30
const TRIAL_STATS_DAYS = 90
const TOP_ACCOUNTS_LIMIT = 5
// Rough, static FX rates used only to produce a single comparable USD number
const APPROX_USD_RATES: Record<string, number> = {
USD: 1,
EUR: 1.08,
GBP: 1.27,
}
interface PaddlePayment {
id: number
subscription_id: number
amount: number
currency: string
payout_date: string
is_paid: number
is_one_off_charge: boolean | number
receipt_url?: string
}
// Trimmed-down payment - a few years of them are cached in redis
interface HistoricPayment {
subscriptionId: number
amount: number
currency: string
date: string
isOneOff: boolean
}
interface MonthBucket {
// Money that actually landed in the month
cashUsd: number
// Money the month is entitled to, with yearly payments spread across the
// year they buy - the closest thing to an MRR series we can reconstruct
recognisedUsd: number
oneOffUsd: number
byCurrency: Record<string, number>
payments: number
payers: Set<number>
activeSubs: Set<number>
}
type TrendRow = Omit<MonthBucket, 'payers' | 'activeSubs'> & {
month: string
payers: number
activeSubs: number
newSubs: number
churnedSubs: number
}
const CHURN_RISK_MIN_PREV_EVENTS = 100
const CHURN_RISK_MIN_DROP = 0.5
export interface RevenueBucket {
byCurrency: Record<string, number>
approxUsd: number
payments: number
oneOffPayments: number
payers: number
}
interface MrrSubscription {
planCode: PlanCode
planType: PlanType | null
billingFrequency: BillingFrequency | null
tierCurrency: string | null
}
type TrialOutcome =
| 'trialing'
| 'converted'
| 'cancelling'
| 'expired'
| 'payment_issue'
export type UsersFilter =
| 'all'
| 'active'
| 'inactive'
| 'paid'
| 'trial'
| 'free'
| 'cancelling'
| 'suspended'
| 'blocked'
export type ProjectsFilter =
| 'all'
| 'active'
| 'archived'
| 'inactive-30'
| 'inactive-60'
| 'inactive-90'
const dateToChDateTime = (date: Date): string =>
date.toISOString().slice(0, 19).replace('T', ' ')
const toDateString = (date: Date): string => date.toISOString().slice(0, 10)
// Everything in the revenue trends is bucketed by UTC calendar month
const monthKey = (date: Date | string): string =>
new Date(date).toISOString().slice(0, 7)
const shiftMonthKey = (key: string, offset: number): string => {
const [year, month] = key.split('-').map(Number)
return monthKey(new Date(Date.UTC(year, month - 1 + offset, 1)))
}
const round2 = (value: number): number => Math.round(value * 100) / 100
const percentChange = (current: number, previous: number): number | null =>
previous > 0
? Math.round(((current - previous) / previous) * 1000) / 10
: null
const daysBetween = (from: number, to: number): number =>
(to - from) / (24 * 60 * 60 * 1000)
@Injectable()
export class AdminService {
constructor(
@InjectRepository(User)
private readonly userRepository: Repository<User>,
@InjectRepository(Project)
private readonly projectRepository: Repository<Project>,
@InjectRepository(Organisation)
private readonly organisationRepository: Repository<Organisation>,
@InjectRepository(OrganisationMember)
private readonly organisationMemberRepository: Repository<OrganisationMember>,
@InjectRepository(UserFeedback)
private readonly userFeedbackRepository: Repository<UserFeedback>,
@InjectRepository(DeleteFeedback)
private readonly deleteFeedbackRepository: Repository<DeleteFeedback>,
@InjectRepository(CancellationFeedback)
private readonly cancellationFeedbackRepository: Repository<CancellationFeedback>,
) {}
// -------------------- MRR --------------------
private getMonthlyRevenue(
subscription: MrrSubscription,
): { currency: SupportedCurrency; amount: number; usdAmount: number } | null {
const { planCode, billingFrequency, tierCurrency } = subscription
if (FREE_PLAN_CODES.includes(planCode)) {
return null
}
const planType = getEffectivePlanType(subscription)
const priceTable =
planType === PlanType.plus ? PLUS_PLAN_PRICES : STANDARD_PLAN_PRICES
const pricing = priceTable[planCode]
if (!pricing) {
return null
}
const currency: SupportedCurrency = ['USD', 'EUR', 'GBP'].includes(
tierCurrency as string,
)
? (tierCurrency as SupportedCurrency)
: 'USD'
const isYearly = billingFrequency === BillingFrequency.Yearly
const amount = isYearly
? pricing.yearly[currency] / 12
: pricing.monthly[currency]
const usdAmount = isYearly ? pricing.yearly.USD / 12 : pricing.monthly.USD
return { currency, amount, usdAmount }
}
private async calculateMrr() {
const paidUsers = await this.userRepository.find({
where: { planCode: Not(In(FREE_PLAN_CODES)) },
select: [
'id',
'planCode',
'planType',
'billingFrequency',
'tierCurrency',
'trialEndDate',
],
})
const byCurrency: Record<SupportedCurrency, number> = {
USD: 0,
EUR: 0,
GBP: 0,
}
let usdEquivalent = 0
let unpricedSubscriptions = 0
let payingUsers = 0
let trialingUsers = 0
let trialUsdEquivalent = 0
for (const user of paidUsers) {
const revenue = this.getMonthlyRevenue(user)
if (!revenue) {
unpricedSubscriptions += 1
continue
}
// Card-first trials sit on a real plan code without having paid a penny
// yet - counting them as MRR would inflate it by a whole trial cohort
if (isTrialing(user)) {
trialingUsers += 1
trialUsdEquivalent += revenue.usdAmount
continue
}
payingUsers += 1
byCurrency[revenue.currency] += revenue.amount
usdEquivalent += revenue.usdAmount
}
return {
byCurrency: {
USD: Math.round(byCurrency.USD * 100) / 100,
EUR: Math.round(byCurrency.EUR * 100) / 100,
GBP: Math.round(byCurrency.GBP * 100) / 100,
},
// Every subscription valued at its USD list price; an estimate, not accounting data
usdEquivalent: round2(usdEquivalent),
payingUsers,
trialingUsers,
trialUsdEquivalent: round2(trialUsdEquivalent),
unpricedSubscriptions,
}
}
// -------------------- Revenue (Paddle) --------------------
private async fetchPaddlePayments(
from: string,
to: string,
isPaid: 0 | 1,
): Promise<PaddlePayment[]> {
const body = new URLSearchParams()
body.set('vendor_id', String(Number(PADDLE_VENDOR_ID)))
body.set('vendor_auth_code', PADDLE_API_KEY)
body.set('is_paid', String(isPaid))
body.set('from', from)
body.set('to', to)
const res = await fetch(
'https://vendors.paddle.com/api/2.0/subscription/payments',
{
method: 'POST',
headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
body,
signal: AbortSignal.timeout(PADDLE_FETCH_TIMEOUT_MS),
},
)
const data = await res.json()
if (!res.ok || !data?.success) {
throw new Error(
`Paddle payments request failed: ${JSON.stringify(data?.error || data)}`,
)
}
return data.response || []
}
private summariseRevenue(payments: PaddlePayment[]): RevenueBucket {
const byCurrency: Record<string, number> = {}
const payerIds = new Set<number>()
let approxUsd = 0
let oneOffPayments = 0
for (const payment of payments) {
const amount = Number(payment.amount) || 0
const currency = payment.currency || 'USD'
byCurrency[currency] = (byCurrency[currency] || 0) + amount
approxUsd += amount * (APPROX_USD_RATES[currency] ?? 1)
payerIds.add(payment.subscription_id)
if (payment.is_one_off_charge) {
oneOffPayments += 1
}
}
for (const currency of Object.keys(byCurrency)) {
byCurrency[currency] = Math.round(byCurrency[currency] * 100) / 100
}
return {
byCurrency,
approxUsd: Math.round(approxUsd * 100) / 100,
payments: payments.length,
oneOffPayments,
payers: payerIds.size,
}
}
// Raw payments shared by the revenue summary and the billing payments feed.
// Cached in redis because the vendor API is slow and rate limited; the
// summaries are derived from this cache instead of being cached themselves.
private async getCachedPaddlePayments(): Promise<{
paid: PaddlePayment[]
upcoming: PaddlePayment[]
fetchedAt: string
} | null> {
const cached = await redis.get(REVENUE_CACHE_KEY).catch(() => null)
if (cached) {
try {
return JSON.parse(cached)
} catch {
// fall through to a fresh fetch
}
}
if (!PADDLE_VENDOR_ID || !PADDLE_API_KEY) {
return null
}
const now = new Date()
const previousMonthStart = new Date(
Date.UTC(now.getUTCFullYear(), now.getUTCMonth() - 1, 1),
)
// Paddle's `to` is exclusive, so the paid range must end tomorrow for
// today's payments to be included
const tomorrow = new Date(now.getTime() + 24 * 60 * 60 * 1000)
const upcomingUntil = new Date(now.getTime() + 45 * 24 * 60 * 60 * 1000)
let result
try {
const [paid, upcoming] = await Promise.all([
this.fetchPaddlePayments(
toDateString(previousMonthStart),
toDateString(tomorrow),
1,
),
this.fetchPaddlePayments(
toDateString(now),
toDateString(upcomingUntil),
0,
),
])
result = { paid, upcoming, fetchedAt: now.toISOString() }
} catch (error) {
console.error('[ERROR] (admin getCachedPaddlePayments):', error)
return null
}
await redis
.set(
REVENUE_CACHE_KEY,
JSON.stringify(result),
'EX',
REVENUE_CACHE_TTL_SECONDS,
)
.catch(() => null)
return result
}
// Real cash-in numbers from Paddle (includes one-off charges), unlike the
// list-price MRR estimate
async getRevenue() {
const payments = await this.getCachedPaddlePayments()
if (!payments) {
return { available: false as const }
}
const now = new Date()
const dayOfMonth = now.getUTCDate()
const currentMonthStart = new Date(
Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1),
)
const currentMonthEnd = new Date(
Date.UTC(now.getUTCFullYear(), now.getUTCMonth() + 1, 0, 23, 59, 59),
)
const currentMonthPayments: PaddlePayment[] = []
const previousMonthPayments: PaddlePayment[] = []
const previousMonthToDatePayments: PaddlePayment[] = []
for (const payment of payments.paid) {
const payoutDate = new Date(payment.payout_date)
if (payoutDate >= currentMonthStart) {
currentMonthPayments.push(payment)
} else {
previousMonthPayments.push(payment)
// Same day-of-month window as the current month, so the MoM badge
// compares like for like instead of MTD vs a full month
if (payoutDate.getUTCDate() <= dayOfMonth) {
previousMonthToDatePayments.push(payment)
}
}
}
// "Scheduled" on the revenue card means the rest of this month only
const upcomingThisMonth = payments.upcoming.filter(
(payment) => new Date(payment.payout_date) <= currentMonthEnd,
)
return {
available: true as const,
currentMonth: this.summariseRevenue(currentMonthPayments),
previousMonth: this.summariseRevenue(previousMonthPayments),
previousMonthToDate: this.summariseRevenue(previousMonthToDatePayments),
upcoming: this.summariseRevenue(upcomingThisMonth),
fetchedAt: payments.fetchedAt,
}
}
// -------------------- Revenue trends --------------------
// Several years of payments in one request would be a single huge response,
// so the range is walked in chunks and stored in a compact shape
private async fetchPaddleHistory(): Promise<HistoricPayment[]> {
const now = new Date()
const windowStart = new Date(
Date.UTC(
now.getUTCFullYear(),
now.getUTCMonth() - (REVENUE_HISTORY_MONTHS - 1),
1,
),
)
const ranges: [string, string][] = []
for (
let cursor = windowStart;
cursor <= now;
cursor = new Date(
Date.UTC(
cursor.getUTCFullYear(),
cursor.getUTCMonth() + REVENUE_HISTORY_CHUNK_MONTHS,
1,
),
)
) {
// Paddle's `to` is EXCLUSIVE (verified against the live API: payments
// whose payout_date equals `to` are not returned), so each chunk must
// end on the first day of the next one - ending on the chunk's own last
// day silently drops every payment landing exactly on that day
const nextChunkStart = new Date(
Date.UTC(
cursor.getUTCFullYear(),
cursor.getUTCMonth() + REVENUE_HISTORY_CHUNK_MONTHS,
1,
),
)
ranges.push([toDateString(cursor), toDateString(nextChunkStart)])
}
const seenIds = new Set<number>()
const payments: HistoricPayment[] = []
for (
let index = 0;
index < ranges.length;
index += REVENUE_HISTORY_CONCURRENCY
) {
const batch = await Promise.all(
ranges
.slice(index, index + REVENUE_HISTORY_CONCURRENCY)
.map(([from, to]) => this.fetchPaddlePayments(from, to, 1)),
)
for (const chunk of batch) {
for (const payment of chunk) {
if (seenIds.has(payment.id)) {
continue
}
seenIds.add(payment.id)
payments.push({
subscriptionId: Number(payment.subscription_id),
amount: Number(payment.amount) || 0,
currency: payment.currency || 'USD',
date: String(payment.payout_date).slice(0, 10),
isOneOff: Boolean(payment.is_one_off_charge),
})
}
}
}
return payments
}
// A cold history crawl takes a while - concurrent requests that miss the
// cache share the one in flight instead of each starting their own
private paddleHistoryFetch: Promise<{
payments: HistoricPayment[]
fetchedAt: string
} | null> | null = null
private async getCachedPaddleHistory(): Promise<{
payments: HistoricPayment[]
fetchedAt: string
} | null> {
const cached = await redis.get(REVENUE_HISTORY_CACHE_KEY).catch(() => null)
if (cached) {
try {
return JSON.parse(cached)
} catch {
// fall through to a fresh fetch
}
}
if (!PADDLE_VENDOR_ID || !PADDLE_API_KEY) {
return null
}
if (!this.paddleHistoryFetch) {
this.paddleHistoryFetch = this.fetchAndCachePaddleHistory().finally(
() => {
this.paddleHistoryFetch = null
},
)
}
return this.paddleHistoryFetch
}
private async fetchAndCachePaddleHistory(): Promise<{
payments: HistoricPayment[]
fetchedAt: string
} | null> {
let result: { payments: HistoricPayment[]; fetchedAt: string }
try {
result = {
payments: await this.fetchPaddleHistory(),
fetchedAt: new Date().toISOString(),
}
} catch (error) {
console.error('[ERROR] (admin getCachedPaddleHistory):', error)
return null
}
await redis
.set(
REVENUE_HISTORY_CACHE_KEY,
JSON.stringify(result),
'EX',
REVENUE_HISTORY_CACHE_TTL_SECONDS,
)
.catch(() => null)
return result
}
// A yearly payment buys twelve months of service. Recognising all of it in
// the month it landed would spike the trend line and flatline the rest of
// the year, so each payment needs to know the period it covers.
private buildBillingPeriods(
payments: HistoricPayment[],
yearlySubIds: Set<number>,
): Map<number, number> {
const timestampsBySub = new Map<number, number[]>()
for (const payment of payments) {
if (payment.isOneOff) {
continue
}
const timestamps = timestampsBySub.get(payment.subscriptionId) || []
timestamps.push(new Date(payment.date).getTime())
timestampsBySub.set(payment.subscriptionId, timestamps)
}
const periods = new Map<number, number>()
for (const [subscriptionId, timestamps] of timestampsBySub) {
timestamps.sort((a, b) => a - b)
const gaps: number[] = []
for (let index = 1; index < timestamps.length; index += 1) {
gaps.push(daysBetween(timestamps[index - 1], timestamps[index]))
}
gaps.sort((a, b) => a - b)
// How the account actually billed beats the current DB flag - it also
// covers subscriptions that have since been cancelled or changed plan
const isYearly =
gaps.length > 0
? gaps[Math.floor(gaps.length / 2)] >= YEARLY_PERIOD_MIN_GAP_DAYS
: yearlySubIds.has(subscriptionId)
periods.set(subscriptionId, isYearly ? MONTHS_IN_YEAR : 1)
}
return periods
}
async getRevenueTrends(months: number) {
const history = await this.getCachedPaddleHistory()
if (!history) {
return { available: false as const }
}
const subIds = Array.from(
new Set(history.payments.map(({ subscriptionId }) => subscriptionId)),
).map(String)
const yearlyUsers =
subIds.length > 0
? await this.userRepository.find({
where: {
subID: In(subIds),
billingFrequency: BillingFrequency.Yearly,
},
select: ['subID'],
})
: []
const periods = this.buildBillingPeriods(
history.payments,
new Set(yearlyUsers.map(({ subID }) => Number(subID))),
)
const now = new Date()
const currentKey = monthKey(now)
const buckets = new Map<string, MonthBucket>()
// Buckets span the whole fetched history, not just the displayed window,
// so yearly payments made before it still get recognised inside it
for (let offset = REVENUE_HISTORY_MONTHS - 1; offset >= 0; offset -= 1) {
buckets.set(shiftMonthKey(currentKey, -offset), {
cashUsd: 0,
recognisedUsd: 0,
oneOffUsd: 0,
byCurrency: {},
payments: 0,
payers: new Set(),
activeSubs: new Set(),
})
}
for (const payment of history.payments) {
const key = monthKey(payment.date)
const bucket = buckets.get(key)
if (!bucket) {
continue
}
const usd = payment.amount * (APPROX_USD_RATES[payment.currency] ?? 1)
bucket.cashUsd += usd
bucket.byCurrency[payment.currency] =
(bucket.byCurrency[payment.currency] || 0) + payment.amount
bucket.payments += 1
bucket.payers.add(payment.subscriptionId)
if (payment.isOneOff) {
bucket.oneOffUsd += usd
continue
}
const period = periods.get(payment.subscriptionId) || 1
const perMonth = usd / period
for (let offset = 0; offset < period; offset += 1) {
const covered = buckets.get(shiftMonthKey(key, offset))
if (!covered) {
break
}
covered.recognisedUsd += perMonth
covered.activeSubs.add(payment.subscriptionId)
}
}
// The current month is deliberately left out: it is always mid-collection,
// so it would render as a cliff on the chart. The clamp keeps a caller
// from asking for months that have no bucket behind them.
const displayedMonths = Math.min(months, REVENUE_HISTORY_MONTHS - 1)
const displayedKeys: string[] = []
for (let offset = displayedMonths; offset >= 1; offset -= 1) {
displayedKeys.push(shiftMonthKey(currentKey, -offset))
}
const rows = displayedKeys.map((key) => {
const bucket = buckets.get(key)
const previous = buckets.get(shiftMonthKey(key, -1))
const activeSubs = Array.from(bucket.activeSubs)
return {
month: key,
cashUsd: round2(bucket.cashUsd),
recognisedUsd: round2(bucket.recognisedUsd),
oneOffUsd: round2(bucket.oneOffUsd),
byCurrency: Object.fromEntries(
Object.entries(bucket.byCurrency).map(([currency, amount]) => [
currency,
round2(amount),
]),
),
payments: bucket.payments,
payers: bucket.payers.size,
activeSubs: activeSubs.length,
newSubs: previous
? activeSubs.filter((id) => !previous.activeSubs.has(id)).length
: 0,
churnedSubs: previous
? Array.from(previous.activeSubs).filter(
(id) => !bucket.activeSubs.has(id),
).length
: 0,
}
})
// Cash collected over the last twelve complete months, read off the full
// bucket map rather than the displayed window, so a 6-month view still
// reports a true trailing-twelve-month total
let ttmCashUsd = 0
for (let offset = MONTHS_IN_YEAR; offset >= 1; offset -= 1) {
ttmCashUsd +=
buckets.get(shiftMonthKey(currentKey, -offset))?.cashUsd ?? 0
}
const current = buckets.get(currentKey)
const dayOfMonth = now.getUTCDate()
const daysInMonth = new Date(
Date.UTC(now.getUTCFullYear(), now.getUTCMonth() + 1, 0),
).getUTCDate()
return {
available: true as const,
months: rows,
currentMonth: {
month: currentKey,
cashUsd: round2(current.cashUsd),
payments: current.payments,
payers: current.payers.size,
dayOfMonth,
daysInMonth,
// Straight-line pace, not a forecast - useful to answer "are we ahead
// of last month or just early in the cycle?"
projectedUsd: round2((current.cashUsd / dayOfMonth) * daysInMonth),
},
summary: this.summariseTrends(rows, ttmCashUsd),
fetchedAt: history.fetchedAt,
}
}
private summariseTrends(rows: TrendRow[], ttmCashUsd: number) {
const last = rows[rows.length - 1]
const previous = rows[rows.length - 2]
const changes: number[] = []
for (let index = 1; index < rows.length; index += 1) {
const change = percentChange(
rows[index].recognisedUsd,
rows[index - 1].recognisedUsd,
)
if (change !== null) {
changes.push(change)
}
}
const recentChanges = changes.slice(-3)
// Signed run of consecutive months moving the same way, counted back from
// the most recent one
let streak = 0
for (let index = rows.length - 1; index > 0; index -= 1) {
const delta = rows[index].recognisedUsd - rows[index - 1].recognisedUsd
const direction = Math.sign(delta)
if (
direction === 0 ||
(streak !== 0 && Math.sign(streak) !== direction)
) {
break
}
streak += direction
}
const best = rows.reduce<TrendRow | null>(
(winner, row) => (!winner || row.cashUsd > winner.cashUsd ? row : winner),
null,
)
return {
lastCompleteMonth: last?.month || null,
recognisedUsd: last?.recognisedUsd ?? 0,
momPercent:
last && previous
? percentChange(last.recognisedUsd, previous.recognisedUsd)
: null,
cashMomPercent:
last && previous ? percentChange(last.cashUsd, previous.cashUsd) : null,
avgMomPercent: recentChanges.length
? round2(
recentChanges.reduce((total, value) => total + value, 0) /
recentChanges.length,
)
: null,
ttmCashUsd: round2(ttmCashUsd),
// Yearly plans are recognised monthly, so this is a run rate rather than
// a promise of next month's cash
runRateUsd: round2((last?.recognisedUsd ?? 0) * MONTHS_IN_YEAR),
netNewSubs: (last?.newSubs ?? 0) - (last?.churnedSubs ?? 0),
streak,
bestMonth: best ? { month: best.month, cashUsd: best.cashUsd } : null,
}
}
// -------------------- Billing health --------------------
private formatBillingUser(user: User) {
const revenue = this.getMonthlyRevenue(user)
return {
id: user.id,
email: user.email,
planCode: user.planCode,
planType: getEffectivePlanType(user),
billingFrequency: user.billingFrequency,
created: user.created,
trialEndDate: user.trialEndDate,
nextBillDate: user.nextBillDate,
cancellationEffectiveDate: user.cancellationEffectiveDate,
dashboardBlockReason: user.dashboardBlockReason,
isAccountBillingSuspended: user.isAccountBillingSuspended,
// USD list-price estimate of what this account is worth per month
monthlyRevenueUsd: revenue
? Math.round(revenue.usdAmount * 100) / 100
: null,
}
}
// What happened to a trial, read off the columns the Paddle webhooks and the
// trial crons write. A card-first trial that converts keeps its plan and
// rolls its nextBillDate forward; one that does not falls back to `none`.
private getTrialOutcome(user: User): TrialOutcome {
if (
LAPSED_PLAN_CODES.includes(user.planCode) ||
user.dashboardBlockReason === DashboardBlockReason.trial_ended
) {
return 'expired'
}
if (
user.isAccountBillingSuspended ||
user.dashboardBlockReason === DashboardBlockReason.payment_failed
) {
return 'payment_issue'
}
if (user.cancellationEffectiveDate) {
// Cancelling during the trial ends the subscription on the trial end
// date itself; a converted account that cancels later ends on a bill
// date at least one billing period after that. The day of grace absorbs
// date-only columns and timezone truncation.
const cancellationAt = new Date(user.cancellationEffectiveDate).getTime()
const trialEnd = user.trialEndDate
? new Date(user.trialEndDate).getTime()
: null
if (
trialEnd === null ||
cancellationAt <= trialEnd + 24 * 60 * 60 * 1000
) {
return 'cancelling'
}
}
return isTrialing(user) ? 'trialing' : 'converted'
}
async getBilling() {
const now = new Date()
const trialWindowEnd = new Date(
now.getTime() + TRIAL_LOOKAHEAD_DAYS * 24 * 60 * 60 * 1000,
)
const trialWindowStart = new Date(
now.getTime() - TRIAL_LOOKBACK_DAYS * 24 * 60 * 60 * 1000,
)
const trialStatsStart = new Date(
now.getTime() - TRIAL_STATS_DAYS * 24 * 60 * 60 * 1000,
)
const [
trialUsers,
resolvedTrials,
cancellingUsers,
suspendedUsers,
subscribedUsers,
paddle,
] = await Promise.all([
// Trial state lives in trialEndDate, not planCode - see `isTrialing`.
// Recently-expired trials are as interesting as expiring ones: they are
// the ones worth a personal follow-up email.
this.userRepository
.createQueryBuilder('user')
.loadRelationCountAndMap('user.projectCount', 'user.projects')
.where('user.trialEndDate IS NOT NULL')
.andWhere('user.trialEndDate <= :trialWindowEnd', { trialWindowEnd })
.andWhere('user.trialEndDate >= :trialWindowStart', {
trialWindowStart,
})
.orderBy('user.trialEndDate', 'ASC')
.take(100)
.getMany(),
// Every trial that has already run its course in the stats window, for
// the conversion rate. Only the columns getTrialOutcome reads.
this.userRepository
.createQueryBuilder('user')
.select([
'user.id',
'user.planCode',
'user.trialEndDate',
'user.cancellationEffectiveDate',
'user.dashboardBlockReason',
'user.isAccountBillingSuspended',
])
.where('user.trialEndDate IS NOT NULL')
.andWhere('user.trialEndDate <= :now', { now })
.andWhere('user.trialEndDate >= :trialStatsStart', { trialStatsStart })
.getMany(),
this.userRepository.find({
where: { cancellationEffectiveDate: Not(IsNull()) },
order: { cancellationEffectiveDate: 'ASC' },
take: 100,
}),
this.userRepository
.createQueryBuilder('user')
.where(
'(user.isAccountBillingSuspended = true OR user.dashboardBlockReason IS NOT NULL)',
)
.orderBy('user.created', 'DESC')
.take(100)
.getMany(),
this.userRepository.find({
where: { planCode: Not(In(FREE_PLAN_CODES)) },
}),
this.getCachedPaddlePayments(),
])
// Accounts still inside their trial have not paid anything yet - keeping
// them out of the revenue maths is what makes these numbers real money
const payingUsers = subscribedUsers.filter((user) => !isTrialing(user))
const [trialUsage, churnRisk, payments] = await Promise.all([
this.getMonthlyEventsForUsers(trialUsers.map((user) => user.id)),
this.getChurnRisk(payingUsers),
this.buildPaymentsFeed(paddle),
])
const outcomes = resolvedTrials.map((user) => this.getTrialOutcome(user))
const converted = outcomes.filter(
(outcome) => outcome === 'converted',
).length
return {
trialsEndingSoon: trialUsers.map(
(user: User & { projectCount?: number }) => ({
...this.formatBillingUser(user),
projectCount: user.projectCount || 0,
monthlyEvents: trialUsage[user.id] || 0,
outcome: this.getTrialOutcome(user),
}),
),
trialStats: {
windowDays: TRIAL_STATS_DAYS,
activeNow: subscribedUsers.filter((user) => isTrialing(user)).length,
resolved: resolvedTrials.length,
converted,
conversionRate: resolvedTrials.length
? Math.round((converted / resolvedTrials.length) * 100)
: null,
cancelledDuringTrial: outcomes.filter(
(outcome) => outcome === 'cancelling',
).length,
paymentIssues: outcomes.filter((outcome) => outcome === 'payment_issue')
.length,
},
mrr: this.summariseSubscriptionMrr(subscribedUsers),
cancellationPipeline: cancellingUsers.map((user) =>
this.formatBillingUser(user),
),
suspended: suspendedUsers.map((user) => this.formatBillingUser(user)),
churnRisk,
payments,
}
}
// Where the recurring revenue actually comes from: plan tier, billing
// frequency, and how much of it rides on the largest few accounts
private summariseSubscriptionMrr(subscribedUsers: User[]) {
const byPlanType: Record<string, { accounts: number; usd: number }> = {}
const byFrequency: Record<string, { accounts: number; usd: number }> = {}
const accounts: {
id: string
email: string
planCode: PlanCode
usd: number
}[] = []
let usd = 0
let trialUsd = 0
let trialingAccounts = 0
let unpriced = 0
for (const user of subscribedUsers) {
const revenue = this.getMonthlyRevenue(user)
if (!revenue) {
unpriced += 1
continue
}
if (isTrialing(user)) {
trialingAccounts += 1
trialUsd += revenue.usdAmount
continue
}
const planType = getEffectivePlanType(user) || PlanType.standard
const frequency = user.billingFrequency || BillingFrequency.Monthly
byPlanType[planType] = byPlanType[planType] || { accounts: 0, usd: 0 }
byPlanType[planType].accounts += 1
byPlanType[planType].usd += revenue.usdAmount
byFrequency[frequency] = byFrequency[frequency] || { accounts: 0, usd: 0 }
byFrequency[frequency].accounts += 1
byFrequency[frequency].usd += revenue.usdAmount
accounts.push({
id: user.id,
email: user.email,
planCode: user.planCode,
usd: revenue.usdAmount,
})
usd += revenue.usdAmount
}
const topAccounts = accounts
.sort((a, b) => b.usd - a.usd)
.slice(0, TOP_ACCOUNTS_LIMIT)
const topUsd = topAccounts.reduce(
(total, account) => total + account.usd,
0,
)
const toBreakdown = (
grouped: Record<string, { accounts: number; usd: number }>,
) =>
Object.entries(grouped)
.map(([key, value]) => ({
key,
accounts: value.accounts,
usd: round2(value.usd),
}))
.sort((a, b) => b.usd - a.usd)
return {
usd: round2(usd),
payingAccounts: accounts.length,
arpuUsd: accounts.length ? round2(usd / accounts.length) : 0,
trialingAccounts,
// What the trials currently running are worth if they all convert
trialUsd: round2(trialUsd),
unpricedSubscriptions: unpriced,
byPlanType: toBreakdown(byPlanType),
byFrequency: toBreakdown(byFrequency),
topAccounts: topAccounts.map((account) => ({
...account,
usd: round2(account.usd),
})),
// Concentration risk: how much of the MRR walks out with five customers
topAccountsPercent: usd > 0 ? Math.round((topUsd / usd) * 100) : 0,
}
}
private async buildPaymentsFeed(
paddle: {
paid: PaddlePayment[]
upcoming: PaddlePayment[]
fetchedAt: string
} | null,
) {
if (!paddle) {
return { available: false as const, recent: [], upcoming: [] }
}
const recent = [...paddle.paid]
.sort(
(a, b) =>
new Date(b.payout_date).getTime() - new Date(a.payout_date).getTime(),
)
.slice(0, 50)
const upcoming = [...paddle.upcoming]
.sort(
(a, b) =>
new Date(a.payout_date).getTime() - new Date(b.payout_date).getTime(),
)
.slice(0, 50)
// Paddle only knows subscription ids - map them back to accounts
const subIds = Array.from(
new Set(
[...recent, ...upcoming].map(({ subscription_id }) =>
String(subscription_id),
),
),
)
const users =
subIds.length > 0
? await this.userRepository.find({
where: { subID: In(subIds) },
select: ['id', 'email', 'subID', 'planCode'],
})
: []
const usersBySubId = Object.fromEntries(
users.map((user) => [user.subID, user]),
)
const mapPayment = (payment: PaddlePayment) => {
const user = usersBySubId[String(payment.subscription_id)]
return {
id: payment.id,
date: payment.payout_date,
amount: Number(payment.amount) || 0,
currency: payment.currency,
isOneOff: Boolean(payment.is_one_off_charge),
receiptUrl: payment.receipt_url || null,
subscriptionId: payment.subscription_id,
user: user
? { id: user.id, email: user.email, planCode: user.planCode }
: null,
}
}
return {
available: true as const,
recent: recent.map(mapPayment),
upcoming: upcoming.map(mapPayment),
fetchedAt: paddle.fetchedAt,
}
}
// Paying users whose usage collapsed vs the previous month - the churn
// early-warning list
private async getChurnRisk(payingUsers: User[]) {
if (payingUsers.length === 0) {
return { atRisk: [], analyzed: 0 }
}
const projects: { pid: string; userId: string }[] =
await this.projectRepository
.createQueryBuilder('project')
.select('project.id', 'pid')
.addSelect('admin.id', 'userId')
.leftJoin('project.admin', 'admin')
.where('admin.id IN (:...userIds)', {
userIds: payingUsers.map((user) => user.id),
})
.getRawMany()
if (projects.length === 0) {
return { atRisk: [], analyzed: payingUsers.length }
}
const query = `
SELECT
pid,
countIf(created >= now() - INTERVAL 30 DAY) AS current,
countIf(
created >= now() - INTERVAL 60 DAY
AND created < now() - INTERVAL 30 DAY
) AS previous
FROM events
WHERE pid IN ({pids:Array(FixedString(12))})
AND type IN ({types:Array(String)})
GROUP BY pid
`
const { data } = await clickhouse
.query({
query,
query_params: {
pids: projects.map(({ pid }) => pid),
types: ACTIVITY_EVENT_TYPES,
},
})
.then((resultSet) =>
resultSet.json<{ pid: string; current: number; previous: number }>(),
)
const byPid: Record<string, { current: number; previous: number }> = {}
for (const { pid, current, previous } of data) {
byPid[pid] = { current: Number(current), previous: Number(previous) }
}
const byUser: Record<string, { current: number; previous: number }> = {}
for (const { pid, userId } of projects) {
const counts = byPid[pid]
if (!counts) {
continue
}
byUser[userId] = byUser[userId] || { current: 0, previous: 0 }
byUser[userId].current += counts.current
byUser[userId].previous += counts.previous
}
const atRisk = payingUsers
.map((user) => {
const usage = byUser[user.id] || { current: 0, previous: 0 }
const drop =
usage.previous > 0
? (usage.previous - usage.current) / usage.previous
: 0
return { user, usage, drop }
})
.filter(
({ usage, drop }) =>
usage.previous >= CHURN_RISK_MIN_PREV_EVENTS &&
drop >= CHURN_RISK_MIN_DROP,
)
.sort((a, b) => b.drop - a.drop)
.map(({ user, usage, drop }) => ({
...this.formatBillingUser(user),
events30d: usage.current,
eventsPrev30d: usage.previous,
dropPercent: Math.round(drop * 100),
}))
return { atRisk, analyzed: payingUsers.length }
}
// -------------------- Bot blocks --------------------
async getBotBlocks(days: number) {
const [seriesRaw, byProjectRaw, totalsRaw] = await Promise.all([
clickhouse
.query({
query: `
SELECT toString(toDate(created)) AS date, reason, count() AS count
FROM bot_blocks
WHERE created >= now() - INTERVAL {days:UInt16} DAY
GROUP BY date, reason
ORDER BY date ASC
`,
query_params: { days },
})
.then((resultSet) =>
resultSet.json<{ date: string; reason: string; count: number }>(),
)
.then(({ data }) => data),
clickhouse
.query({
query: `
SELECT pid, reason, count() AS count
FROM bot_blocks
WHERE created >= now() - INTERVAL {days:UInt16} DAY
GROUP BY pid, reason
`,
query_params: { days },
})
.then((resultSet) =>
resultSet.json<{ pid: string; reason: string; count: number }>(),
)
.then(({ data }) => data),
clickhouse
.query({
query: `
SELECT
count() AS total,
countIf(created >= now() - INTERVAL 1 DAY) AS last24h
FROM bot_blocks
WHERE created >= now() - INTERVAL {days:UInt16} DAY
`,
query_params: { days },
})
.then((resultSet) =>
resultSet.json<{ total: number; last24h: number }>(),
)
.then(({ data }) => data[0]),
])
// Aggregate per project and per reason
const byPid: Record<
string,
{ total: number; reasons: Record<string, number> }
> = {}
const byReason: Record<string, number> = {}
for (const { pid, reason, count } of byProjectRaw) {
const numeric = Number(count)
byPid[pid] = byPid[pid] || { total: 0, reasons: {} }
byPid[pid].total += numeric
byPid[pid].reasons[reason] = (byPid[pid].reasons[reason] || 0) + numeric
byReason[reason] = (byReason[reason] || 0) + numeric
}
const topPids = Object.entries(byPid)
.sort(([, a], [, b]) => b.total - a.total)
.slice(0, 30)
.map(([pid]) => pid)
const [acceptedByPid, projects] = await Promise.all([
this.getAcceptedEventCounts(topPids, days),
topPids.length > 0
? this.projectRepository.find({
where: { id: In(topPids) },
relations: ['admin'],
})
: Promise.resolve([]),
])
const projectsByPid: Record<string, Project> = {}
for (const project of projects) {
projectsByPid[project.id] = project
}
const topProjects = topPids.map((pid) => {
const blocked = byPid[pid].total
const accepted = acceptedByPid[pid] || 0
const project = projectsByPid[pid]
const topReason = Object.entries(byPid[pid].reasons).sort(
([, a], [, b]) => b - a,
)[0]
return {
id: pid,
name: project?.name || null,
botsProtectionLevel: project?.botsProtectionLevel || null,
admin: project?.admin
? { id: project.admin.id, email: project.admin.email }
: null,
blocked,
accepted,
// blocked / all incoming traffic; ~100% means the shield is eating
// everything the customer sends
blockRatio: Math.round((blocked / (blocked + accepted)) * 100),
topReason: topReason ? topReason[0] : null,
}
})
return {
days,
totals: {
total: Number(totalsRaw?.total) || 0,
last24h: Number(totalsRaw?.last24h) || 0,
},
byReason: Object.entries(byReason)
.map(([reason, count]) => ({ reason, count }))
.sort((a, b) => b.count - a.count),
series: seriesRaw.map(({ date, reason, count }) => ({
date,
reason,
count: Number(count),
})),
topProjects,
}
}
private async getAcceptedEventCounts(
pids: string[],
days: number,
): Promise<Record<string, number>> {
if (pids.length === 0) {
return {}
}
const { data } = await clickhouse
.query({
query: `
SELECT pid, count() AS count
FROM events
WHERE pid IN ({pids:Array(FixedString(12))})
AND created >= now() - INTERVAL {days:UInt16} DAY
AND type IN ({types:Array(String)})
GROUP BY pid
`,
query_params: { pids, days, types: ACTIVITY_EVENT_TYPES },
})
.then((resultSet) => resultSet.json<{ pid: string; count: number }>())
return Object.fromEntries(
data.map(({ pid, count }) => [pid, Number(count)]),
)
}
// -------------------- Overview --------------------
async getOverview() {
const now = new Date()
const dayAgo = new Date(now.getTime() - 24 * 60 * 60 * 1000)
const weekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000)
const monthAgo = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000)
const twoMonthsAgo = new Date(now.getTime() - 60 * 24 * 60 * 60 * 1000)
const [
totalUsers,
activeUsers,
paidUsers,
trialUsers,
cancellingUsers,
suspendedUsers,
totalProjects,
liveProjects,
totalOrganisations,
planDistributionRaw,
signups24h,
signups7d,
signups30d,
signupsPrev30d,
mrr,
] = await Promise.all([
this.userRepository.count(),
this.userRepository.count({ where: { isActive: true } }),
this.userRepository
.createQueryBuilder('user')
.where('user.planCode NOT IN (:...freePlans)', {
freePlans: FREE_PLAN_CODES,
})
.andWhere(`NOT (${TRIALING_CONDITION})`, {
now,
lapsedPlans: LAPSED_PLAN_CODES,
})
.getCount(),
this.userRepository
.createQueryBuilder('user')
.where(TRIALING_CONDITION, { now, lapsedPlans: LAPSED_PLAN_CODES })
.getCount(),
this.userRepository.count({
where: { cancellationEffectiveDate: Not(IsNull()) },
}),
this.userRepository.count({
where: { isAccountBillingSuspended: true },
}),
this.projectRepository.count(),
this.projectRepository.count({
where: { active: true, isArchived: false },
}),
this.organisationRepository.count(),
this.userRepository
.createQueryBuilder('user')
.select('user.planCode', 'planCode')
.addSelect('COUNT(*)', 'count')
.groupBy('user.planCode')
.getRawMany<{ planCode: PlanCode; count: string }>(),
this.countUsersCreatedSince(dayAgo),
this.countUsersCreatedSince(weekAgo),
this.countUsersCreatedSince(monthAgo),
this.countUsersCreatedBetween(twoMonthsAgo, monthAgo),
this.calculateMrr(),
])
const eventStats = await this.getEventTotals()
const planDistribution = planDistributionRaw
.map(({ planCode, count }) => ({ planCode, count: Number(count) }))
.sort((a, b) => b.count - a.count)
return {
users: {
total: totalUsers,
active: activeUsers,
paid: paidUsers,
trial: trialUsers,
cancelling: cancellingUsers,
suspended: suspendedUsers,
signups24h,
signups7d,
signups30d,
signupsPrev30d,
},
projects: {
total: totalProjects,
live: liveProjects,
},
organisations: {
total: totalOrganisations,
},
events: eventStats,
mrr,
planDistribution,
}
}
private async countUsersCreatedSince(since: Date): Promise<number> {
return this.userRepository
.createQueryBuilder('user')
.where('user.created >= :since', { since })
.getCount()
}
private async countUsersCreatedBetween(
from: Date,
to: Date,
): Promise<number> {
return this.userRepository
.createQueryBuilder('user')
.where('user.created >= :from AND user.created < :to', { from, to })
.getCount()
}
private async getEventTotals() {
const query = `
SELECT
count() AS total,
countIf(created >= now() - INTERVAL 1 DAY) AS last24h,
countIf(created >= now() - INTERVAL 7 DAY) AS last7d,
countIf(created >= now() - INTERVAL 30 DAY) AS last30d,
countIf(
created >= now() - INTERVAL 60 DAY
AND created < now() - INTERVAL 30 DAY
) AS prev30d
FROM events
WHERE type IN ({types:Array(String)})
`
const { data } = await clickhouse
.query({ query, query_params: { types: ACTIVITY_EVENT_TYPES } })
.then((resultSet) =>
resultSet.json<{
total: number
last24h: number
last7d: number
last30d: number
prev30d: number
}>(),
)
return (
data[0] || {
total: 0,
last24h: 0,
last7d: 0,
last30d: 0,
prev30d: 0,
}
)
}
// -------------------- Charts --------------------
async getCharts(days: number) {
const since = new Date(Date.now() - days * 24 * 60 * 60 * 1000)
const prevSince = new Date(Date.now() - 2 * days * 24 * 60 * 60 * 1000)
const [
signups,
projects,
organisations,
events,
signupsTotals,
projectsTotals,
organisationsTotals,
eventsTotals,
funnel,
] = await Promise.all([
this.getMysqlCreationSeries('user', since),
this.getMysqlCreationSeries('project', since),
this.getMysqlCreationSeries('organisation', since),
this.getEventsSeries(days),
this.getMysqlCreationTotals('user', since, prevSince),
this.getMysqlCreationTotals('project', since, prevSince),
this.getMysqlCreationTotals('organisation', since, prevSince),
this.getEventsTotalsForWindow(days),
this.getActivationFunnel(since),
])
return {
signups,
projects,
organisations,
events,
// Current window vs the same-length window immediately before it, for
// the change badges above the charts
totals: {
signups: signupsTotals,
projects: projectsTotals,
organisations: organisationsTotals,
events: eventsTotals,
},
funnel,
}
}
// Cohort funnel over users who signed up in the window: how far did they
// get towards becoming paying customers (state as of now, not as of signup)
private async getActivationFunnel(since: Date) {
const cohort = await this.userRepository.find({
where: { created: MoreThanOrEqual(since) },
select: ['id', 'isActive', 'planCode'],
})
if (cohort.length === 0) {
return {
signups: 0,
verified: 0,
createdProject: 0,
sentData: 0,
paid: 0,
}
}
const projects: { pid: string; userId: string }[] =
await this.projectRepository
.createQueryBuilder('project')
.select('project.id', 'pid')
.addSelect('admin.id', 'userId')
.leftJoin('project.admin', 'admin')
.where('admin.id IN (:...userIds)', {
userIds: cohort.map((user) => user.id),
})
.getRawMany()
const usersWithProject = new Set(projects.map(({ userId }) => userId))
let usersWithData = new Set<string>()
if (projects.length > 0) {
const { data } = await clickhouse
.query({
query: `
SELECT DISTINCT pid
FROM events
WHERE pid IN ({pids:Array(FixedString(12))})
AND type IN ({types:Array(String)})
`,
query_params: {
pids: projects.map(({ pid }) => pid),
types: ACTIVITY_EVENT_TYPES,
},
})
.then((resultSet) => resultSet.json<{ pid: string }>())
const pidsWithData = new Set(data.map(({ pid }) => pid))
usersWithData = new Set(
projects
.filter(({ pid }) => pidsWithData.has(pid))
.map(({ userId }) => userId),
)
}
return {
signups: cohort.length,
verified: cohort.filter((user) => user.isActive).length,
createdProject: usersWithProject.size,
sentData: usersWithData.size,
paid: cohort.filter((user) => !FREE_PLAN_CODES.includes(user.planCode))
.length,
}
}
private async getMysqlCreationTotals(
table: 'user' | 'project' | 'organisation',
since: Date,
prevSince: Date,
): Promise<{ current: number; previous: number }> {
// table name comes from a hardcoded whitelist, never from user input
const rows: { current: string; previous: string }[] =
await this.userRepository.query(
`
SELECT
COALESCE(SUM(created >= ?), 0) AS current,
COALESCE(SUM(created < ?), 0) AS previous
FROM \`${table}\`
WHERE created >= ?
`,
[since, since, prevSince],
)
return {
current: Number(rows[0]?.current) || 0,
previous: Number(rows[0]?.previous) || 0,
}
}
private async getEventsTotalsForWindow(
days: number,
): Promise<{ current: number; previous: number }> {
const query = `
SELECT
countIf(created >= now() - INTERVAL {days:UInt16} DAY) AS current,
countIf(created < now() - INTERVAL {days:UInt16} DAY) AS previous
FROM events
WHERE created >= now() - INTERVAL {doubleDays:UInt16} DAY
AND type IN ({types:Array(String)})
`
const { data } = await clickhouse
.query({
query,
query_params: {
days,
doubleDays: days * 2,
types: ACTIVITY_EVENT_TYPES,
},
})
.then((resultSet) =>
resultSet.json<{ current: number; previous: number }>(),
)
return {
current: Number(data[0]?.current) || 0,
previous: Number(data[0]?.previous) || 0,
}
}
private async getMysqlCreationSeries(
table: 'user' | 'project' | 'organisation',
since: Date,
): Promise<{ date: string; count: number }[]> {
// table name comes from a hardcoded whitelist, never from user input
const rows: { date: string; count: string }[] =
await this.userRepository.query(
`
SELECT DATE_FORMAT(created, '%Y-%m-%d') AS date, COUNT(*) AS count
FROM \`${table}\`
WHERE created >= ?
GROUP BY date
ORDER BY date ASC
`,
[since],
)
return rows.map(({ date, count }) => ({ date, count: Number(count) }))
}
private async getEventsSeries(
days: number,
): Promise<{ date: string; count: number }[]> {
const query = `
SELECT
toString(toDate(created)) AS date,
count() AS count
FROM events
WHERE created >= now() - INTERVAL {days:UInt16} DAY
AND type IN ({types:Array(String)})
GROUP BY date
ORDER BY date ASC
`
const { data } = await clickhouse
.query({
query,
query_params: { days, types: ACTIVITY_EVENT_TYPES },
})
.then((resultSet) => resultSet.json<{ date: string; count: number }>())
return data.map(({ date, count }) => ({ date, count: Number(count) }))
}
// -------------------- Users --------------------
async getUsers(
page: number,
search: string,
filter: UsersFilter,
sortBy: 'created' | 'email' | 'planCode',
order: 'ASC' | 'DESC',
) {
let query = this.userRepository
.createQueryBuilder('user')
.loadRelationCountAndMap('user.projectCount', 'user.projects')
.orderBy(`user.${sortBy}`, order)
.skip(page * USERS_PAGE_SIZE)
.take(USERS_PAGE_SIZE)
if (filter === 'active') {
query = query.andWhere('user.isActive = true')
} else if (filter === 'inactive') {
query = query.andWhere('user.isActive = false')
} else if (filter === 'paid') {
query = query
.andWhere('user.planCode NOT IN (:...freePlans)', {
freePlans: FREE_PLAN_CODES,
})
.andWhere(`NOT (${TRIALING_CONDITION})`, {
now: new Date(),
lapsedPlans: LAPSED_PLAN_CODES,
})
} else if (filter === 'trial') {
query = query.andWhere(TRIALING_CONDITION, {
now: new Date(),
lapsedPlans: LAPSED_PLAN_CODES,
})
} else if (filter === 'free') {
query = query.andWhere('user.planCode IN (:...freePlans)', {
freePlans: [PlanCode.free, PlanCode.none],
})
} else if (filter === 'cancelling') {
query = query.andWhere('user.cancellationEffectiveDate IS NOT NULL')
} else if (filter === 'suspended') {
query = query.andWhere('user.isAccountBillingSuspended = true')
} else if (filter === 'blocked') {
query = query.andWhere(
'(user.dashboardBlockReason IS NOT NULL OR user.isAccountBillingSuspended = true)',
)
}
if (search) {
query = query.andWhere(
'(LOWER(user.email) LIKE :search OR LOWER(user.id) LIKE :search)',
{ search: `%${search.toLowerCase()}%` },
)
}
const [users, total] = await query.getManyAndCount()
const monthlyEvents = await this.getMonthlyEventsForUsers(
users.map((user) => user.id),
)
return {
total,
pageSize: USERS_PAGE_SIZE,
users: users.map((user) => this.formatUserForList(user, monthlyEvents)),
}
}
private formatUserForList(
user: User & { projectCount?: number },
monthlyEvents: Record<string, number>,
) {
const limits = getEffectiveAccountLimits(user)
return {
id: user.id,
email: user.email,
planCode: user.planCode,
planType: getEffectivePlanType(user),
billingFrequency: user.billingFrequency,
tierCurrency: user.tierCurrency,
isActive: user.isActive,
created: user.created,
trialEndDate: user.trialEndDate,
nextBillDate: user.nextBillDate,
cancellationEffectiveDate: user.cancellationEffectiveDate,
dashboardBlockReason: user.dashboardBlockReason,
isAccountBillingSuspended: user.isAccountBillingSuspended,
addonOverrides: user.addonOverrides,
entitlementOverrides: user.entitlementOverrides,
// The raw column value (null = legacy), as opposed to the effective planType
storedPlanType: user.planType,
projectCount: user.projectCount || 0,
maxProjects: limits.maxProjects,
monthlyEvents: monthlyEvents[user.id] || 0,
monthlyUsageLimit:
ACCOUNT_PLANS[user.planCode]?.monthlyUsageLimit ?? null,
}
}
// Billable events in the current calendar month, grouped by user
private async getMonthlyEventsForUsers(
userIds: string[],
): Promise<Record<string, number>> {
if (userIds.length === 0) {
return {}
}
const projects: { pid: string; userId: string }[] =
await this.projectRepository
.createQueryBuilder('project')
.select('project.id', 'pid')
.addSelect('admin.id', 'userId')
.leftJoin('project.admin', 'admin')
.where('admin.id IN (:...userIds)', { userIds })
.getRawMany()
if (projects.length === 0) {
return {}
}
const monthStart = new Date()
monthStart.setUTCDate(1)
monthStart.setUTCHours(0, 0, 0, 0)
const query = `
SELECT
pid,
${BILLABLE_COUNT_IF} AS count
FROM events
WHERE pid IN ({pids:Array(FixedString(12))})
AND type IN ('pageview', 'custom_event', 'error', 'captcha', 'outbound')
AND importID IS NULL
AND created >= {from:String}
GROUP BY pid
`
const { data } = await clickhouse
.query({
query,
query_params: {
pids: projects.map(({ pid }) => pid),
from: dateToChDateTime(monthStart),
},
})
.then((resultSet) => resultSet.json<{ pid: string; count: number }>())
const countByPid: Record<string, number> = {}
for (const { pid, count } of data) {
countByPid[pid] = Number(count)
}
const countByUser: Record<string, number> = {}
for (const { pid, userId } of projects) {
countByUser[userId] = (countByUser[userId] || 0) + (countByPid[pid] || 0)
}
return countByUser
}
async getUserDetails(id: string) {
const user = await this.userRepository.findOne({ where: { id } })
if (!user) {
throw new NotFoundException()
}
const [projects, memberships, monthlyEvents] = await Promise.all([
this.projectRepository.find({
where: { admin: { id } },
relations: ['organisation'],
order: { created: 'DESC' },
}),
this.organisationMemberRepository.find({
where: { user: { id } },
relations: ['organisation'],
}),
this.getMonthlyEventsForUsers([id]),
])
const eventCounts = await this.getEventCountsForProjects(
projects.map((project) => project.id),
)
return {
user: this.formatUserForList(
Object.assign(user, { projectCount: projects.length }),
monthlyEvents,
),
effectiveLimits: getEffectiveAccountLimits(user),
projects: projects.map((project) => ({
id: project.id,
name: project.name,
active: project.active,
isArchived: project.isArchived,
created: project.created,
organisation: project.organisation
? { id: project.organisation.id, name: project.organisation.name }
: null,
...(eventCounts[project.id] || {
events24h: 0,
events30d: 0,
totalEvents: 0,
}),
})),
memberships: memberships.map((membership) => ({
id: membership.id,
role: membership.role,
confirmed: membership.confirmed,
created: membership.created,
organisation: membership.organisation
? {
id: membership.organisation.id,
name: membership.organisation.name,
}
: null,
})),
}
}
async updateUser(
id: string,
updates: {
planType?: PlanType | null
addonOverrides?: Record<string, unknown> | null
entitlementOverrides?: Record<string, unknown> | null
},
) {
const user = await this.userRepository.findOne({ where: { id } })
if (!user) {
throw new NotFoundException()
}
const update: Partial<User> = {}
if ('planType' in updates) {
update.planType = updates.planType
}
if ('addonOverrides' in updates) {
update.addonOverrides = updates.addonOverrides
}
if ('entitlementOverrides' in updates) {
update.entitlementOverrides = updates.entitlementOverrides
}
// Keep the legacy per-user limit columns in sync with the new effective
// limits (parity with what the admin CLI used to do)
const limits = getEffectiveAccountLimits({ ...user, ...update })
if (typeof limits.maxProjects === 'number') {
update.maxProjects = limits.maxProjects
}
if (typeof limits.maxApiKeyRequestsPerHour === 'number') {
update.maxApiKeyRequestsPerHour = limits.maxApiKeyRequestsPerHour
}
await this.userRepository.update({ id }, update)
return this.getUserDetails(id)
}
// -------------------- Projects --------------------
async getProjects(
page: number,
search: string,
filter: ProjectsFilter,
sortBy: 'created' | 'name',
order: 'ASC' | 'DESC',
) {
let query = this.projectRepository
.createQueryBuilder('project')
.leftJoinAndSelect('project.admin', 'admin')
.leftJoinAndSelect('project.organisation', 'organisation')
.orderBy(`project.${sortBy}`, order)
.skip(page * PROJECTS_PAGE_SIZE)
.take(PROJECTS_PAGE_SIZE)
if (filter === 'active') {
query = query.andWhere(
'project.active = true AND project.isArchived = false',
)
} else if (filter === 'archived') {
query = query.andWhere('project.isArchived = true')
} else if (filter.startsWith('inactive-')) {
const days = Number(filter.split('-')[1])
const activePids = await this.getProjectsWithRecentEvents(days)
query = query.andWhere('project.isArchived = false')
if (activePids.length > 0) {
query = query.andWhere('project.id NOT IN (:...activePids)', {
activePids,
})
}
}
if (search) {
query = query.andWhere(
'(LOWER(project.name) LIKE :search OR LOWER(project.id) LIKE :search OR LOWER(admin.email) LIKE :search)',
{ search: `%${search.toLowerCase()}%` },
)
}
const [projects, total] = await query.getManyAndCount()
const eventCounts = await this.getEventCountsForProjects(
projects.map((project) => project.id),
)
return {
total,
pageSize: PROJECTS_PAGE_SIZE,
projects: projects.map((project) =>
this.formatProject(project, eventCounts),
),
}
}
private formatProject(
project: Project,
eventCounts: Record<
string,
{ events24h: number; events30d: number; totalEvents: number }
>,
) {
return {
id: project.id,
name: project.name,
active: project.active,
public: project.public,
isArchived: project.isArchived,
isPasswordProtected: project.isPasswordProtected,
botsProtectionLevel: project.botsProtectionLevel,
created: project.created,
admin: project.admin
? {
id: project.admin.id,
email: project.admin.email,
planCode: project.admin.planCode,
}
: null,
organisation: project.organisation
? { id: project.organisation.id, name: project.organisation.name }
: null,
...(eventCounts[project.id] || {
events24h: 0,
events30d: 0,
totalEvents: 0,
}),
}
}
private async getProjectsWithRecentEvents(days: number): Promise<string[]> {
const query = `
SELECT DISTINCT pid
FROM events
WHERE created >= now() - INTERVAL {days:UInt16} DAY
AND type IN ({types:Array(String)})
`
const { data } = await clickhouse
.query({
query,
query_params: { days, types: ACTIVITY_EVENT_TYPES },
})
.then((resultSet) => resultSet.json<{ pid: string }>())
return data.map(({ pid }) => pid)
}
private async getEventCountsForProjects(
pids: string[],
): Promise<
Record<
string,
{ events24h: number; events30d: number; totalEvents: number }
>
> {
if (pids.length === 0) {
return {}
}
const query = `
SELECT
pid,
countIf(created >= now() - INTERVAL 1 DAY) AS events24h,
countIf(created >= now() - INTERVAL 30 DAY) AS events30d,
count() AS totalEvents
FROM events
WHERE pid IN ({pids:Array(FixedString(12))})
AND type IN ({types:Array(String)})
GROUP BY pid
`
const { data } = await clickhouse
.query({
query,
query_params: { pids, types: ACTIVITY_EVENT_TYPES },
})
.then((resultSet) =>
resultSet.json<{
pid: string
events24h: number
events30d: number
totalEvents: number
}>(),
)
const result: Record<
string,
{ events24h: number; events30d: number; totalEvents: number }
> = {}
for (const { pid, events24h, events30d, totalEvents } of data) {
result[pid] = {
events24h: Number(events24h),
events30d: Number(events30d),
totalEvents: Number(totalEvents),
}
}
return result
}
async getTopProjects(days: number, limit = 50) {
const query = `
SELECT
pid,
count() AS eventCount
FROM events
WHERE created >= now() - INTERVAL {days:UInt16} DAY
AND type IN ({types:Array(String)})
GROUP BY pid
ORDER BY eventCount DESC
LIMIT {limit:UInt16}
`
const { data } = await clickhouse
.query({
query,
query_params: { days, limit, types: ACTIVITY_EVENT_TYPES },
})
.then((resultSet) =>
resultSet.json<{ pid: string; eventCount: number }>(),
)
if (data.length === 0) {
return { projects: [] }
}
const projects = await this.projectRepository.find({
where: { id: In(data.map(({ pid }) => pid)) },
relations: ['admin', 'organisation'],
})
const projectsByPid: Record<string, Project> = {}
for (const project of projects) {
projectsByPid[project.id] = project
}
return {
projects: data.map(({ pid, eventCount }) => {
const project = projectsByPid[pid]
return {
id: pid,
eventCount: Number(eventCount),
name: project?.name || null,
created: project?.created || null,
admin: project?.admin
? {
id: project.admin.id,
email: project.admin.email,
planCode: project.admin.planCode,
}
: null,
organisation: project?.organisation
? { id: project.organisation.id, name: project.organisation.name }
: null,
}
}),
}
}
async getProjectDetails(id: string) {
const project = await this.projectRepository.findOne({
where: { id },
relations: ['admin', 'organisation', 'share', 'share.user'],
})
if (!project) {
throw new NotFoundException()
}
const [eventCounts, series, typeBreakdown] = await Promise.all([
this.getEventCountsForProjects([id]),
this.getProjectEventsSeries(id, 30),
this.getProjectEventTypeBreakdown(id),
])
return {
project: {
...this.formatProject(project, eventCounts),
origins: project.origins,
ipBlacklist: project.ipBlacklist,
},
series,
typeBreakdown,
shares: (project.share || []).map((share) => ({
id: share.id,
role: share.role,
confirmed: share.confirmed,
created: share.created,
user: share.user
? { id: share.user.id, email: share.user.email }
: null,
})),
}
}
private async getProjectEventsSeries(
pid: string,
days: number,
): Promise<{ date: string; count: number }[]> {
const query = `
SELECT
toString(toDate(created)) AS date,
count() AS count
FROM events
WHERE pid = {pid:FixedString(12)}
AND created >= now() - INTERVAL {days:UInt16} DAY
AND type IN ({types:Array(String)})
GROUP BY date
ORDER BY date ASC
`
const { data } = await clickhouse
.query({
query,
query_params: { pid, days, types: ACTIVITY_EVENT_TYPES },
})
.then((resultSet) => resultSet.json<{ date: string; count: number }>())
return data.map(({ date, count }) => ({ date, count: Number(count) }))
}
private async getProjectEventTypeBreakdown(
pid: string,
): Promise<{ type: string; last30d: number; total: number }[]> {
const query = `
SELECT
type,
countIf(created >= now() - INTERVAL 30 DAY) AS last30d,
count() AS total
FROM events
WHERE pid = {pid:FixedString(12)}
AND type IN ({types:Array(String)})
GROUP BY type
ORDER BY total DESC
`
const { data } = await clickhouse
.query({ query, query_params: { pid, types: ACTIVITY_EVENT_TYPES } })
.then((resultSet) =>
resultSet.json<{ type: string; last30d: number; total: number }>(),
)
return data.map(({ type, last30d, total }) => ({
type,
last30d: Number(last30d),
total: Number(total),
}))
}
// -------------------- Organisations --------------------
async getOrganisations(
page: number,
search: string,
sortBy: 'created' | 'name',
order: 'ASC' | 'DESC',
) {
let query = this.organisationRepository
.createQueryBuilder('org')
.loadRelationCountAndMap('org.memberCount', 'org.members')
.loadRelationCountAndMap('org.projectCount', 'org.projects')
.orderBy(`org.${sortBy}`, order)
.skip(page * ORGANISATIONS_PAGE_SIZE)
.take(ORGANISATIONS_PAGE_SIZE)
if (search) {
query = query.where(
'(LOWER(org.name) LIKE :search OR LOWER(org.id) LIKE :search)',
{ search: `%${search.toLowerCase()}%` },
)
}
const [organisations, total] = await query.getManyAndCount()
const owners =
organisations.length === 0
? []
: await this.organisationMemberRepository.find({
where: {
organisation: { id: In(organisations.map((org) => org.id)) },
role: OrganisationRole.owner,
},
relations: ['organisation', 'user'],
})
const ownersByOrgId: Record<string, OrganisationMember> = {}
for (const owner of owners) {
if (owner.organisation) {
ownersByOrgId[owner.organisation.id] = owner
}
}
return {
total,
pageSize: ORGANISATIONS_PAGE_SIZE,
organisations: organisations.map(
(
org: Organisation & { memberCount?: number; projectCount?: number },
) => ({
id: org.id,
name: org.name,
created: org.created,
memberCount: org.memberCount || 0,
projectCount: org.projectCount || 0,
owner: ownersByOrgId[org.id]?.user
? {
id: ownersByOrgId[org.id].user.id,
email: ownersByOrgId[org.id].user.email,
}
: null,
}),
),
}
}
async getOrganisationDetails(id: string) {
const organisation = await this.organisationRepository.findOne({
where: { id },
relations: ['members', 'members.user', 'projects', 'projects.admin'],
})
if (!organisation) {
throw new NotFoundException()
}
const eventCounts = await this.getEventCountsForProjects(
organisation.projects.map((project) => project.id),
)
return {
organisation: {
id: organisation.id,
name: organisation.name,
created: organisation.created,
memberCount: organisation.members.length,
projectCount: organisation.projects.length,
},
members: organisation.members.map((member) => ({
id: member.id,
role: member.role,
confirmed: member.confirmed,
created: member.created,
user: member.user
? { id: member.user.id, email: member.user.email }
: null,
})),
projects: organisation.projects.map((project) => ({
id: project.id,
name: project.name,
active: project.active,
isArchived: project.isArchived,
created: project.created,
admin: project.admin
? { id: project.admin.id, email: project.admin.email }
: null,
...(eventCounts[project.id] || {
events24h: 0,
events30d: 0,
totalEvents: 0,
}),
})),
}
}
// -------------------- Feedback --------------------
async getFeedback(
type: FeedbackType,
page: number,
search: string,
order: 'ASC' | 'DESC',
) {
const [counts, list] = await Promise.all([
this.getFeedbackCounts(),
type === 'user'
? this.getUserFeedbackList(page, search, order)
: type === 'cancellation'
? this.getCancellationFeedbackList(page, search, order)
: this.getDeleteFeedbackList(page, search, order),
])
return { ...list, counts }
}
private async getFeedbackCounts() {
const [user, cancellation, deletion] = await Promise.all([
this.userFeedbackRepository.count(),
this.cancellationFeedbackRepository.count(),
this.deleteFeedbackRepository.count(),
])
return { user, cancellation, deletion }
}
private async getUsersByIds(
ids: string[],
): Promise<Record<string, { id: string; email: string; planCode: string }>> {
if (ids.length === 0) {
return {}
}
const users = await this.userRepository.find({
where: { id: In(ids) },
select: ['id', 'email', 'planCode'],
})
return Object.fromEntries(
users.map((user) => [
user.id,
{ id: user.id, email: user.email, planCode: user.planCode },
]),
)
}
private async getUserFeedbackList(
page: number,
search: string,
order: 'ASC' | 'DESC',
) {
let query = this.userFeedbackRepository
.createQueryBuilder('fb')
.orderBy('fb.createdAt', order)
.skip(page * FEEDBACK_PAGE_SIZE)
.take(FEEDBACK_PAGE_SIZE)
if (search) {
// Emails live on the user table, so resolve matching authors first to
// make "who left it" searchable
const matchingUsers: { id: string }[] = await this.userRepository
.createQueryBuilder('user')
.select('user.id', 'id')
.where('LOWER(user.email) LIKE :search', {
search: `%${search.toLowerCase()}%`,
})
.getRawMany()
const ids = matchingUsers.map(({ id }) => id)
query = query.andWhere(
ids.length > 0
? '(LOWER(fb.message) LIKE :search OR fb.userId IN (:...ids))'
: 'LOWER(fb.message) LIKE :search',
{ search: `%${search.toLowerCase()}%`, ids },
)
}
const [items, total] = await query.getManyAndCount()
const usersById = await this.getUsersByIds(
Array.from(new Set(items.map((item) => item.userId))),
)
return {
total,
pageSize: FEEDBACK_PAGE_SIZE,
items: items.map((item) => ({
id: item.id,
message: item.message,
attachmentUrls: item.attachmentUrls || [],
createdAt: item.createdAt,
// null = the account was deleted since
user: usersById[item.userId] || null,
userId: item.userId,
})),
}
}
private async getCancellationFeedbackList(
page: number,
search: string,
order: 'ASC' | 'DESC',
) {
let query = this.cancellationFeedbackRepository
.createQueryBuilder('fb')
.orderBy('fb.createdAt', order)
.skip(page * FEEDBACK_PAGE_SIZE)
.take(FEEDBACK_PAGE_SIZE)
if (search) {
query = query.andWhere(
'(LOWER(fb.feedback) LIKE :search OR LOWER(fb.email) LIKE :search OR LOWER(fb.planCode) LIKE :search)',
{ search: `%${search.toLowerCase()}%` },
)
}
const [items, total] = await query.getManyAndCount()
// Link back to the account when it still exists
const emails = Array.from(
new Set(items.map((item) => item.email).filter(Boolean)),
)
const users =
emails.length > 0
? await this.userRepository.find({
where: { email: In(emails) },
select: ['id', 'email', 'planCode'],
})
: []
const usersByEmail = Object.fromEntries(
users.map((user) => [user.email, user]),
)
return {
total,
pageSize: FEEDBACK_PAGE_SIZE,
items: items.map((item) => ({
id: item.id,
message: item.feedback,
email: item.email,
planCode: item.planCode,
createdAt: item.createdAt,
user: item.email
? usersByEmail[item.email]
? {
id: usersByEmail[item.email].id,
email: usersByEmail[item.email].email,
planCode: usersByEmail[item.email].planCode,
}
: null
: null,
})),
}
}
private async getDeleteFeedbackList(
page: number,
search: string,
order: 'ASC' | 'DESC',
) {
let query = this.deleteFeedbackRepository
.createQueryBuilder('fb')
.orderBy('fb.createdAt', order)
.skip(page * FEEDBACK_PAGE_SIZE)
.take(FEEDBACK_PAGE_SIZE)
if (search) {
query = query.andWhere('LOWER(fb.feedback) LIKE :search', {
search: `%${search.toLowerCase()}%`,
})
}
const [items, total] = await query.getManyAndCount()
return {
total,
pageSize: FEEDBACK_PAGE_SIZE,
items: items.map((item) => ({
id: item.id,
message: item.feedback,
createdAt: item.createdAt,
})),
}
}
// -------------------- Database --------------------
async getDatabaseInfo() {
const [clickhouseInfo, mysqlInfo] = await Promise.all([
this.getClickhouseInfo(),
this.getMysqlInfo(),
])
return { clickhouse: clickhouseInfo, mysql: mysqlInfo }
}
private async getClickhouseInfo() {
const disksQuery = `
SELECT
name,
path,
free_space AS freeSpace,
total_space AS totalSpace
FROM system.disks
`
const tablesQuery = `
SELECT
table,
sum(rows) AS rows,
sum(data_compressed_bytes) AS compressedBytes,
sum(data_uncompressed_bytes) AS uncompressedBytes,
count() AS parts
FROM system.parts
WHERE database = currentDatabase() AND active
GROUP BY table
ORDER BY compressedBytes DESC
`
const [disks, tables, version] = await Promise.all([
clickhouse
.query({ query: disksQuery })
.then((resultSet) =>
resultSet.json<{
name: string
path: string
freeSpace: number
totalSpace: number
}>(),
)
.then(({ data }) =>
data.map((disk) => ({
...disk,
freeSpace: Number(disk.freeSpace),
totalSpace: Number(disk.totalSpace),
})),
),
clickhouse
.query({ query: tablesQuery })
.then((resultSet) =>
resultSet.json<{
table: string
rows: number
compressedBytes: number
uncompressedBytes: number
parts: number
}>(),
)
.then(({ data }) =>
data.map((table) => ({
...table,
rows: Number(table.rows),
compressedBytes: Number(table.compressedBytes),
uncompressedBytes: Number(table.uncompressedBytes),
parts: Number(table.parts),
})),
),
clickhouse
.query({ query: 'SELECT version() AS version' })
.then((resultSet) => resultSet.json<{ version: string }>())
.then(({ data }) => data[0]?.version || null),
])
return {
version,
disks,
tables,
totalCompressedBytes: tables.reduce(
(acc, table) => acc + table.compressedBytes,
0,
),
totalUncompressedBytes: tables.reduce(
(acc, table) => acc + table.uncompressedBytes,
0,
),
totalRows: tables.reduce((acc, table) => acc + table.rows, 0),
}
}
private async getMysqlInfo() {
const [tables, versionRow] = await Promise.all([
this.userRepository.query(
`
SELECT
table_name AS tableName,
table_rows AS estimatedRows,
data_length AS dataBytes,
index_length AS indexBytes
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC
`,
) as Promise<
{
tableName: string
estimatedRows: string
dataBytes: string
indexBytes: string
}[]
>,
this.userRepository.query('SELECT VERSION() AS version') as Promise<
{ version: string }[]
>,
])
const formattedTables = tables.map((table) => ({
tableName: table.tableName,
estimatedRows: Number(table.estimatedRows),
dataBytes: Number(table.dataBytes),
indexBytes: Number(table.indexBytes),
}))
return {
version: versionRow[0]?.version || null,
tables: formattedTables,
totalBytes: formattedTables.reduce(
(acc, table) => acc + table.dataBytes + table.indexBytes,
0,
),
}
}
}