📈 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 {
ForbiddenException,
Injectable,
BadRequestException,
UnprocessableEntityException,
InternalServerErrorException,
ConflictException,
NotFoundException,
HttpException,
HttpStatus,
} from '@nestjs/common'
import { InjectRepository } from '@nestjs/typeorm'
import {
FindManyOptions,
FindOneOptions,
Repository,
DeepPartial,
Brackets,
FindOptionsWhere,
In,
} from 'typeorm'
import CryptoJS from 'crypto-js'
import _isEmpty from 'lodash/isEmpty'
import _size from 'lodash/size'
import _join from 'lodash/join'
import _map from 'lodash/map'
import _pick from 'lodash/pick'
import _trim from 'lodash/trim'
import _findIndex from 'lodash/findIndex'
import _filter from 'lodash/filter'
import _includes from 'lodash/includes'
import _reduce from 'lodash/reduce'
import dayjs from 'dayjs'
import utc from 'dayjs/plugin/utc'
import { compareSync } from 'bcrypt'
import { UserService } from '../user/user.service'
import { ActionTokensService } from '../action-tokens/action-tokens.service'
import { ActionTokenType } from '../action-tokens/action-token.entity'
import { MailerService } from '../mailer/mailer.service'
import { LetterTemplate } from '../mailer/letter'
import { Pagination, PaginationOptionsInterface } from '../common/pagination'
import { Project } from './entity/project.entity'
import { ProjectShare, Role } from './entity/project-share.entity'
import { ProjectDTO } from './dto/project.dto'
import { MAX_PROJECT_PASSWORD_LENGTH } from './dto/project-password.dto'
import {
isValidPID,
redisProjectCountCacheTimeout,
getRedisUserCountKey,
redis,
ORIGINS_REGEX,
getRedisProjectKey,
redisProjectCacheTimeout,
isDevelopment,
PRODUCTION_ORIGIN,
getRedisUserUsageInfoKey,
redisUserUsageinfoCacheTimeout,
TRAFFIC_COLUMNS,
EMAIL_ACTION_ENCRYPTION_KEY,
V2_VIEW_FILTER_DIMENSIONS,
} from '../common/constants'
import { clickhouse } from '../common/integrations/clickhouse'
import { isValidIpRange } from '../common/ip-range'
import { IUsageInfoRedis } from '../user/interfaces'
import { ProjectSubscriber, Funnel, Annotation, PinnedProject } from './entity'
import { AddSubscriberType } from './types'
import {
CreateProjectDTO,
GetSubscribersQueriesDto,
UpdateProjectDto,
UpdateSubscriberBodyDto,
FunnelCreateDTO,
FunnelUpdateDTO,
AnnotationCreateDTO,
AnnotationUpdateDTO,
} from './dto'
import { ReportFrequency } from './enums'
import { CreateProjectViewDto } from './dto/create-project-view.dto'
import { Organisation } from '../organisation/entity/organisation.entity'
import { OrganisationRole } from '../organisation/entity/organisation-member.entity'
import {
ACCOUNT_PLANS,
DashboardBlockReason,
PlanCode,
PlanFeatureCode,
User,
userHasPlanFeature,
} from '../user/entities/user.entity'
import { evaluatePlanUsage, PlanUsage } from '../user/plan-usage'
dayjs.extend(utc)
// A list of characters that can be used in a Project ID
export const LEGAL_PID_CHARACTERS =
'1234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm'
export const PID_LENGTH = 12
const DEFAULT_USAGE_INFO = {
total: 0,
traffic: 0,
customEvents: 0,
captcha: 0,
errors: 0,
}
const CAPTCHA_PASS_CONDITION =
"type = 'captcha' AND if(indexOf(`meta.key`, 'captcha_event') = 0, 'pass', arrayElement(`meta.value`, indexOf(`meta.key`, 'captcha_event'))) = 'pass'"
type UsageInfoPeriod = 'month' | 'last30Days'
export const deleteProjectRedis = async (id: string) => {
const key = getRedisProjectKey(id)
try {
await redis.del(key)
} catch (reason) {
console.error(`Error deleting project ${id} from redis: ${reason}`)
}
}
const deleteProjectsRedis = async (ids: string[]) => {
await Promise.all(_map(ids, deleteProjectRedis))
}
export const processProjectUser = (
project: Project,
properties: Array<'share' | 'organisation.members'> = ['share'],
): Project => {
_map(properties, (property) => {
const array =
property === 'share' ? project.share : project.organisation?.members
if (array) {
for (let j = 0; j < _size(array); ++j) {
const { user } = array[j]
if (user) {
// @ts-expect-error _pick(user, ['email']) is partial but array[j].user expects full User entity
array[j].user = _pick(user, ['email', 'id'])
}
}
}
})
return project
}
const processProjectsUser = (
projects: Project[],
properties: Array<'share' | 'organisation.members'> = ['share'],
): Project[] => {
for (let i = 0; i < _size(projects); ++i) {
projects[i] = processProjectUser(projects[i], properties)
}
return projects
}
@Injectable()
export class ProjectService {
constructor(
@InjectRepository(Project)
private projectsRepository: Repository<Project>,
@InjectRepository(ProjectShare)
private projectShareRepository: Repository<ProjectShare>,
@InjectRepository(ProjectSubscriber)
private readonly projectSubscriberRepository: Repository<ProjectSubscriber>,
@InjectRepository(Funnel)
private readonly funnelRepository: Repository<Funnel>,
@InjectRepository(Annotation)
private readonly annotationRepository: Repository<Annotation>,
@InjectRepository(PinnedProject)
private readonly pinnedProjectRepository: Repository<PinnedProject>,
private readonly actionTokens: ActionTokensService,
private readonly mailerService: MailerService,
private readonly userService: UserService,
) {}
async getRedisProject(pid: string): Promise<Project | null> {
const pidKey = getRedisProjectKey(pid)
let project: string | Project = await redis.get(pidKey)
if (_isEmpty(project)) {
project = await this.projectsRepository
.createQueryBuilder('project')
.leftJoinAndSelect('project.admin', 'admin')
.leftJoinAndSelect('project.organisation', 'organisation')
.leftJoinAndSelect(
'organisation.members',
'organisationMembers',
'organisationMembers.confirmed = :confirmed',
{ confirmed: true },
)
.leftJoinAndSelect('organisationMembers.user', 'organisationUser')
.select([
'project.id',
'project.origins',
'project.active',
'project.public',
'project.ipBlacklist',
'project.ipWhitelist',
'project.countryBlacklist',
'project.botsProtectionLevel',
'project.captchaSecretKey',
'project.captchaDifficulty',
'project.captchaDifficultyMode',
'project.sessionReplayRetentionDays',
'project.passwordHash',
'project.isPasswordProtected',
'admin.id',
'admin.dashboardBlockReason',
'admin.isAccountBillingSuspended',
'admin.planCode',
'admin.planType',
'admin.entitlementOverrides',
'admin.addonOverrides',
'organisation.id',
'organisationMembers.role',
'organisationMembers.confirmed',
'organisationUser.id',
])
.where('project.id = :pid', { pid })
.getOne()
if (_isEmpty(project)) {
throw new BadRequestException(
'The provided Project ID (pid) is incorrect',
)
}
const share = await this.projectShareRepository
.createQueryBuilder('share')
.leftJoinAndSelect('share.user', 'user')
.select(['share.role', 'share.confirmed', 'user.id'])
.where('share.project = :pid', { pid })
.getMany()
project = { ...project, share }
await redis.set(
pidKey,
JSON.stringify(project),
'EX',
redisProjectCacheTimeout,
)
} else {
try {
project = JSON.parse(project)
} catch {
throw new InternalServerErrorException('Error while processing project')
}
}
return project as Project
}
// Instead of passing relations to the findOne method every time, this method returns a project
// with necessary relations needed for the allowedToView and allowedToManage methods.
// Authorisation should be handled separately via allowedToView/allowedToManage.
async getFullProject(pid: string, additionalRelations: string[] = []) {
return this.projectsRepository.findOne({
where: { id: pid },
relations: [
'share',
'share.user',
'admin',
'organisation',
'organisation.members',
'organisation.members.user',
...additionalRelations,
],
select: {
share: {
id: true,
role: true,
confirmed: true,
user: {
id: true,
email: true,
},
},
admin: {
id: true,
dashboardBlockReason: true,
isAccountBillingSuspended: true,
planCode: true,
planType: true,
entitlementOverrides: true,
},
organisation: {
id: true,
members: {
id: true,
role: true,
confirmed: true,
user: {
id: true,
},
},
},
},
})
}
async paginate(
options: PaginationOptionsInterface,
userId: string,
search?: string,
sort?: string,
allowedProjectIds?: string[],
): Promise<Pagination<Project>> {
const queryBuilder = this.projectsRepository
.createQueryBuilder('project')
.select([
'project',
'admin.id',
'admin.dashboardBlockReason',
'admin.planCode',
'admin.planType',
'share.id',
'share.role',
'share.confirmed',
'sharedUser.id',
'sharedUser.email',
'organisation.id',
'organisation.name',
'organisationMembers.id',
'organisationMembers.role',
'organisationMembers.confirmed',
'organisationUser.id',
'organisationUser.email',
])
.addSelect(
'CASE WHEN pinnedProject.id IS NOT NULL THEN 1 ELSE 0 END',
'isPinned',
)
.leftJoin('project.admin', 'admin')
.leftJoin('project.share', 'share')
.leftJoin('share.user', 'sharedUser')
.leftJoin('project.organisation', 'organisation')
.leftJoin('organisation.members', 'organisationMembers')
.leftJoin('organisationMembers.user', 'organisationUser')
.leftJoin(
PinnedProject,
'pinnedProject',
'pinnedProject.projectId = project.id AND pinnedProject.userId = :userId',
)
.where(
new Brackets((qb) => {
qb.where('project.adminId = :userId')
.orWhere(
'EXISTS (SELECT 1 FROM project_share ps WHERE ps.projectId = project.id AND ps.userId = :userId)',
)
.orWhere(
'EXISTS (SELECT 1 FROM organisation_member om WHERE om.organisationId = project.organisationId AND om.userId = :userId AND om.confirmed = true)',
)
}),
{ userId },
)
if (allowedProjectIds) {
queryBuilder.andWhere(
allowedProjectIds.length
? 'project.id IN (:...allowedProjectIds)'
: '1 = 0',
{ allowedProjectIds },
)
}
if (search?.trim()) {
queryBuilder
.andWhere('project.name LIKE :search')
.setParameter('search', search ? `%${search.trim()}%` : '')
}
// Always sort pinned projects first, then apply the requested sort
queryBuilder.orderBy('isPinned', 'DESC')
// Add secondary sorting based on options.sort
switch (sort) {
case 'alpha_asc':
queryBuilder.addOrderBy('project.name', 'ASC')
break
case 'alpha_desc':
queryBuilder.addOrderBy('project.name', 'DESC')
break
case 'date_asc':
queryBuilder.addOrderBy('project.created', 'ASC')
break
case 'date_desc':
queryBuilder.addOrderBy('project.created', 'DESC')
break
default:
queryBuilder.addOrderBy('project.name', 'ASC')
}
queryBuilder.skip(options.skip || 0).take(options.take || 100)
const [results, total] = await queryBuilder.getManyAndCount()
const processedResults = await processProjectsUser(results, [
'share',
'organisation.members',
])
return new Pagination<Project>({
results: processedResults,
total,
})
}
async paginateForOrganisation(
options: PaginationOptionsInterface,
userId: string,
search?: string,
allowedProjectIds?: string[],
): Promise<Pagination<Project>> {
const queryBuilder = this.projectsRepository
.createQueryBuilder('project')
.select(['project.id', 'project.name'])
.leftJoin('project.admin', 'admin')
.leftJoin('project.share', 'share')
.where('project.organisation IS NULL')
.andWhere(
new Brackets((qb) => {
qb.where('admin.id = :userId', { userId }).orWhere(
'share.user.id = :userId AND share.role = :adminRole',
{ userId, adminRole: Role.admin },
)
}),
)
if (allowedProjectIds) {
queryBuilder.andWhere(
allowedProjectIds.length
? 'project.id IN (:...allowedProjectIds)'
: '1 = 0',
{ allowedProjectIds },
)
}
if (search?.trim()) {
queryBuilder.andWhere('LOWER(project.name) LIKE LOWER(:search)', {
search: `%${search.trim()}%`,
})
}
queryBuilder
.orderBy('project.name', 'ASC')
.skip(options.skip || 0)
.take(options.take || 100)
const [results, total] = await queryBuilder.getManyAndCount()
return new Pagination<Project>({
results,
total,
})
}
async count(): Promise<number> {
return this.projectsRepository.count()
}
async countByAdminId(adminId: string): Promise<number> {
return this.projectsRepository.count({ where: { admin: { id: adminId } } })
}
async getProjectIdsByAdminId(adminId: string): Promise<string[]> {
const projects = await this.projectsRepository.find({
where: { admin: { id: adminId } },
select: ['id'],
})
return _map(projects, 'id')
}
async getProjectIdsViewableByUser(userId: string): Promise<string[]> {
const projects = await this.projectsRepository
.createQueryBuilder('project')
.select('project.id', 'id')
.distinct(true)
.leftJoin('project.admin', 'admin')
.leftJoin('project.share', 'share')
.leftJoin('share.user', 'sharedUser')
.leftJoin('project.organisation', 'organisation')
.leftJoin('organisation.members', 'organisationMembers')
.leftJoin('organisationMembers.user', 'organisationUser')
.where(
new Brackets((qb) => {
qb.where('project.public = true')
.orWhere('admin.id = :userId')
.orWhere('sharedUser.id = :userId AND share.confirmed = true')
.orWhere(
'organisationUser.id = :userId AND organisationMembers.confirmed = true',
)
}),
)
.setParameter('userId', userId)
.getRawMany<{ id: string }>()
return _map(projects, 'id')
}
async create(project: DeepPartial<Project>) {
return this.projectsRepository.save(project)
}
async update(
where: FindOptionsWhere<Project>,
projectDTO: DeepPartial<Project>,
) {
return this.projectsRepository.update(where, projectDTO)
}
async delete(id: string) {
return this.projectsRepository.delete(id)
}
async deleteMultiple(pids: string[]) {
return this.projectsRepository
.createQueryBuilder()
.delete()
.whereInIds(pids)
.execute()
}
async deleteProjectsForUser(userId: string) {
const projects = await this.projectsRepository.find({
where: { admin: { id: userId } },
select: ['id'],
})
if (_isEmpty(projects)) {
return
}
const projectIds = _map(projects, 'id')
const queries = [
'ALTER TABLE events DELETE WHERE pid IN ({pids:Array(FixedString(12))})',
'ALTER TABLE error_statuses DELETE WHERE pid IN ({pids:Array(FixedString(12))})',
'ALTER TABLE profile_aliases DELETE WHERE pid IN ({pids:Array(FixedString(12))})',
'ALTER TABLE profile_traits DELETE WHERE pid IN ({pids:Array(FixedString(12))})',
]
await Promise.all(
_map(queries, (query) =>
clickhouse.command({
query,
query_params: {
pids: projectIds,
},
}),
),
)
await this.funnelRepository.delete({ project: { id: In(projectIds) } })
await this.annotationRepository.delete({ project: { id: In(projectIds) } })
await this.deleteMultipleShare({ project: { id: In(projectIds) } })
await this.deleteMultiple(projectIds)
await deleteProjectsRedis(projectIds)
}
async deleteMultipleShare(where: FindOptionsWhere<ProjectShare>) {
return this.projectShareRepository.delete(where)
}
async createShare(share: ProjectShare): Promise<ProjectShare> {
return this.projectShareRepository.save(share)
}
async deleteShare(id: string) {
return this.projectShareRepository.delete(id)
}
async updateShare(id: string, share: ProjectShare | object) {
return this.projectShareRepository.update(id, share)
}
async findShare(
options: FindManyOptions<ProjectShare>,
): Promise<ProjectShare[]> {
return this.projectShareRepository.find(options)
}
async findOneShare(
options: FindOneOptions<ProjectShare>,
): Promise<ProjectShare | null> {
return this.projectShareRepository.findOne(options)
}
findOneWithRelations(id: string, relations = ['admin']) {
return this.projectsRepository.findOne({ where: { id }, relations })
}
findOne(options: FindOneOptions<Project>) {
return this.projectsRepository.findOne(options)
}
find(options: FindManyOptions<Project>) {
return this.projectsRepository.find(options)
}
getProjectFeatureAccess(project: Project | null | undefined) {
return {
[PlanFeatureCode.featureFlags]: this.projectOwnerHasFeature(
project,
PlanFeatureCode.featureFlags,
),
[PlanFeatureCode.experiments]: this.projectOwnerHasFeature(
project,
PlanFeatureCode.experiments,
),
[PlanFeatureCode.replays]: this.projectOwnerHasFeature(
project,
PlanFeatureCode.replays,
),
[PlanFeatureCode.dataWarehouse]: this.projectOwnerHasFeature(
project,
PlanFeatureCode.dataWarehouse,
),
}
}
projectOwnerHasFeature(
project: Project | null | undefined,
feature: PlanFeatureCode,
) {
return userHasPlanFeature(project?.admin, feature)
}
assertProjectOwnerFeatureAccess(
project: Project | null | undefined,
feature: PlanFeatureCode,
) {
if (this.projectOwnerHasFeature(project, feature)) {
return
}
throw new HttpException(
'This feature is not available on the current project owner plan.',
HttpStatus.PAYMENT_REQUIRED,
)
}
allowedToView(
project: Project,
uid: string | null,
password?: string | null,
): void {
if (
project.public ||
uid === project.admin?.id ||
_findIndex(
project.share,
({ user, confirmed }) => user?.id === uid && confirmed === true,
) !== -1 ||
_findIndex(
project.organisation?.members,
(member) => member.user?.id === uid && member.confirmed === true,
) !== -1
) {
return null
}
if (project.isPasswordProtected && password) {
if (
_size(password) <= MAX_PROJECT_PASSWORD_LENGTH &&
compareSync(password, project.passwordHash)
) {
return null
}
throw new ConflictException('Incorrect password')
}
if (project.isPasswordProtected) {
throw new ForbiddenException('This project is password protected')
}
throw new ForbiddenException('You are not allowed to view this project')
}
allowedToManage(
project: Project,
uid: string,
message = 'You are not allowed to manage this project',
): void {
if (
uid === project.admin?.id ||
_findIndex(
project.share,
(share) =>
share.user?.id === uid &&
share.role === Role.admin &&
share.confirmed === true,
) !== -1 ||
_findIndex(
project.organisation?.members,
(member) =>
member.user?.id === uid &&
member.confirmed === true &&
(member.role === OrganisationRole.admin ||
member.role === OrganisationRole.owner),
) !== -1
) {
return null
}
throw new ForbiddenException(message)
}
async isPIDUnique(pid: string): Promise<boolean> {
const exists = await this.projectsRepository.findOne({
where: { id: pid },
select: ['id'],
})
return !exists
}
async checkIfIDUnique(pid: string): Promise<void> {
const isUnique = this.isPIDUnique(pid)
if (!isUnique) {
throw new BadRequestException('Selected project ID is already in use')
}
}
async deleteByFilters(
pid: string,
type: string,
filters: string,
): Promise<void> {
if (!type || _isEmpty(filters)) {
return
}
if (!_includes(TRAFFIC_COLUMNS, type)) {
throw new UnprocessableEntityException(
`The provided type (${type}) is incorrect`,
)
}
let query = `ALTER TABLE events DELETE WHERE pid={pid:FixedString(12)} AND type = 'pageview' AND (`
const params = {
pid,
}
for (let i = 0; i < _size(filters); ++i) {
if (i > 0) {
query += ' OR '
}
const key = `filter_${i}`
query += `${type}={${key}:String}`
params[key] = filters[i]
}
query += ')'
await clickhouse.command({
query,
query_params: params,
})
}
async removeDataFromClickhouse(
pid: string,
from: string,
to: string,
): Promise<void> {
const queries = [
"ALTER TABLE events DELETE WHERE pid = {pid:FixedString(12)} AND type IN ('pageview', 'custom_event', 'performance', 'error', 'captcha', 'outbound') AND created BETWEEN {from:String} AND {to:String}",
'ALTER TABLE error_statuses DELETE WHERE pid = {pid:FixedString(12)} AND created BETWEEN {from:String} AND {to:String}',
]
const params = {
pid,
from,
to,
}
await Promise.all(
_map(queries, (query) =>
clickhouse.command({
query,
query_params: params,
}),
),
)
}
validateOrigins(
projectDTO: ProjectDTO | UpdateProjectDto | CreateProjectDTO,
) {
if (_size(_join(projectDTO.origins, ',')) > 300)
throw new UnprocessableEntityException(
'The list of allowed origins has to be smaller than 300 symbols',
)
_map(projectDTO.origins, (host) => {
if (!ORIGINS_REGEX.test(_trim(host))) {
throw new ConflictException(`Host ${host} is not correct`)
}
})
}
validateIPBlacklist(
projectDTO: ProjectDTO | UpdateProjectDto | CreateProjectDTO,
) {
if (_size(_join(projectDTO.ipBlacklist, ',')) > 300)
throw new UnprocessableEntityException(
'The list of allowed blacklisted IP addresses must be less than 300 characters.',
)
_map(projectDTO.ipBlacklist, (ip) => {
if (!isValidIpRange(_trim(ip))) {
throw new ConflictException(`IP address ${ip} is not correct`)
}
})
}
validateIPWhitelist(
projectDTO: ProjectDTO | UpdateProjectDto | CreateProjectDTO,
) {
if (!projectDTO.ipWhitelist) {
return
}
if (!Array.isArray(projectDTO.ipWhitelist))
throw new UnprocessableEntityException(
'The list of whitelisted IP addresses must be an array.',
)
if (_size(_join(projectDTO.ipWhitelist, ',')) > 300)
throw new UnprocessableEntityException(
'The list of whitelisted IP addresses must be less than 300 characters.',
)
_map(projectDTO.ipWhitelist, (ip) => {
if (!isValidIpRange(_trim(ip))) {
throw new ConflictException(`IP address ${ip} is not correct`)
}
})
}
validateCountryBlacklist(
projectDTO: ProjectDTO | UpdateProjectDto | CreateProjectDTO,
) {
if (!projectDTO.countryBlacklist) {
return
}
if (_size(projectDTO.countryBlacklist) > 250)
throw new UnprocessableEntityException(
'The list of blocked countries cannot exceed 250 entries.',
)
const countryCodeRegex = /^[A-Z]{2}$/
_map(projectDTO.countryBlacklist, (code) => {
const trimmedCode = _trim(code).toUpperCase()
if (!countryCodeRegex.test(trimmedCode)) {
throw new ConflictException(
`Country code "${code}" is not a valid ISO 3166-1 alpha-2 code`,
)
}
})
}
validateProject(
projectDTO: ProjectDTO | UpdateProjectDto | CreateProjectDTO,
creatingProject = false,
) {
if (_size(projectDTO.name) > 50)
throw new UnprocessableEntityException('The project name is too long')
if (creatingProject) {
return
}
// @ts-ignore
if (projectDTO?.id && !isValidPID(projectDTO.id))
throw new UnprocessableEntityException(
'The provided Project ID (pid) is incorrect',
)
this.validateOrigins(projectDTO)
this.validateIPBlacklist(projectDTO)
this.validateIPWhitelist(projectDTO)
this.validateCountryBlacklist(projectDTO)
}
async getPlanUsage(user: User, now = dayjs.utc()): Promise<PlanUsage> {
const pids = (user.projects || []).map((project) => project.id)
let thisMonthUsage = 0
let lastMonthUsage = 0
for (let i = 0; i < pids.length; i += 5000) {
const result = await clickhouse.query({
query: `
SELECT
countIf(created >= {monthStart:DateTime}) AS current,
countIf(created < {monthStart:DateTime}) AS previous
FROM events
WHERE pid IN ({pids:Array(FixedString(12))})
AND (type IN ('pageview', 'custom_event', 'error', 'outbound') OR (${CAPTCHA_PASS_CONDITION}))
AND importID IS NULL
AND created >= {previousMonthStart:DateTime}
AND created < {nextMonthStart:DateTime}
`,
query_params: {
pids: pids.slice(i, i + 5000),
monthStart: now.startOf('month').format('YYYY-MM-DD HH:mm:ss'),
previousMonthStart: now
.subtract(1, 'month')
.startOf('month')
.format('YYYY-MM-DD HH:mm:ss'),
nextMonthStart: now
.add(1, 'month')
.startOf('month')
.format('YYYY-MM-DD HH:mm:ss'),
},
})
const { data } = await result.json<{
current: string | number
previous: string | number
}>()
thisMonthUsage += Number(data[0]?.current || 0)
lastMonthUsage += Number(data[0]?.previous || 0)
}
return evaluatePlanUsage(
ACCOUNT_PLANS[user.planCode].monthlyUsageLimit,
thisMonthUsage,
lastMonthUsage,
)
}
async clearResolvedPlanUsage(user: User, usage: PlanUsage): Promise<void> {
if (
usage.exceedsLimit ||
(!user.planExceedContactedAt && !user.dashboardBlockReason)
) {
return
}
if (
await this.userService.updatePlanUsageState(user, {
planExceedContactedAt: null,
dashboardBlockReason: null,
})
) {
await this.clearProjectsRedisCache(user.id)
}
}
async refreshUsageAfterDeletion(pid: string): Promise<void> {
const project = await this.findOne({
where: { id: pid },
relations: ['admin'],
})
if (!project?.admin) return
const uid = project.admin.id
await redis.del(
getRedisUserCountKey(uid),
getRedisUserUsageInfoKey(uid),
`${getRedisUserUsageInfoKey(uid)}_last30d`,
)
const user = await this.userService.findOne({
where: { id: uid },
relations: ['projects'],
})
if (
!user ||
[PlanCode.none, PlanCode.trial].includes(user.planCode) ||
(!user.planExceedContactedAt &&
user.dashboardBlockReason !==
DashboardBlockReason.exceeding_plan_limits)
)
return
await this.clearResolvedPlanUsage(user, await this.getPlanUsage(user))
}
// Returns amount of existing events starting from month
async getRedisCount(uid: string): Promise<number | null> {
const countKey = getRedisUserCountKey(uid)
let count: string | number = await redis.get(countKey)
if (_isEmpty(count)) {
const monthStart = dayjs
.utc()
.startOf('month')
.format('YYYY-MM-DD HH:mm:ss')
const monthEnd = dayjs.utc().endOf('month').format('YYYY-MM-DD HH:mm:ss')
const projects = await this.find({
where: {
admin: { id: uid },
},
select: ['id'],
})
if (_isEmpty(projects)) {
return 0
}
const CHUNK_SIZE = 5000
const pids = _map(projects, 'id')
let totalPageviews = 0
let totalCustomEvents = 0
let totalOutboundLinks = 0
let totalCaptcha = 0
let totalErrors = 0
// Process PIDs in chunks
for (let i = 0; i < pids.length; i += CHUNK_SIZE) {
const pidChunk = pids.slice(i, i + CHUNK_SIZE)
const params = {
pids: pidChunk,
monthStart,
monthEnd,
}
const query = `
SELECT
countIf(type = 'pageview') AS pageviews,
countIf(type = 'custom_event') AS customEvents,
countIf(type = 'outbound') AS outboundLinks,
countIf(${CAPTCHA_PASS_CONDITION}) AS captcha,
countIf(type = 'error') AS errors
FROM events
WHERE created BETWEEN {monthStart:String} AND {monthEnd:String}
AND pid IN ({pids:Array(FixedString(12))})
AND type IN ('pageview', 'custom_event', 'captcha', 'error', 'outbound')
AND importID IS NULL
`
const { data: counts } = await clickhouse
.query({
query,
query_params: params,
})
.then((resultSet) =>
resultSet.json<{
pageviews: string | number
customEvents: string | number
outboundLinks: string | number
captcha: string | number
errors: string | number
}>(),
)
const row = counts[0]
totalPageviews += Number(row?.pageviews) || 0
totalCustomEvents += Number(row?.customEvents) || 0
totalOutboundLinks += Number(row?.outboundLinks) || 0
totalCaptcha += Number(row?.captcha) || 0
totalErrors += Number(row?.errors) || 0
}
count =
totalPageviews +
totalCustomEvents +
totalOutboundLinks +
totalCaptcha +
totalErrors
await redis.set(countKey, `${count}`, 'EX', redisProjectCountCacheTimeout)
} else {
try {
count = Number(count)
} catch (reason) {
count = 0
console.error(`[ERROR][project -> getRedisCount] ${reason}`)
throw new InternalServerErrorException('Error while processing project')
}
}
return count as number
}
async getRedisUsageInfo(
uid: string,
period: UsageInfoPeriod = 'month',
): Promise<IUsageInfoRedis | null> {
const key =
period === 'last30Days'
? `${getRedisUserUsageInfoKey(uid)}_last30d`
: getRedisUserUsageInfoKey(uid)
let info: string | IUsageInfoRedis = await redis.get(key)
if (_isEmpty(info)) {
const now = dayjs.utc()
const periodStart =
period === 'last30Days'
? now.subtract(30, 'day').format('YYYY-MM-DD HH:mm:ss')
: now.startOf('month').format('YYYY-MM-DD HH:mm:ss')
const periodEnd =
period === 'last30Days'
? now.format('YYYY-MM-DD HH:mm:ss')
: now.endOf('month').format('YYYY-MM-DD HH:mm:ss')
const projects = await this.find({
where: {
admin: { id: uid },
},
select: ['id'],
})
if (_isEmpty(projects)) {
return DEFAULT_USAGE_INFO
}
const CHUNK_SIZE = 5000
const pids = _map(projects, 'id')
let totalTraffic = 0
let totalCustomEvents = 0
let totalOutboundLinks = 0
let totalCaptcha = 0
let totalErrors = 0
// Process PIDs in chunks
for (let i = 0; i < pids.length; i += CHUNK_SIZE) {
const pidChunk = pids.slice(i, i + CHUNK_SIZE)
const params = {
pids: pidChunk,
periodStart,
periodEnd,
}
const query = `
SELECT
countIf(type = 'pageview') AS traffic,
countIf(type = 'custom_event') AS customEvents,
countIf(type = 'outbound') AS outboundLinks,
countIf(${CAPTCHA_PASS_CONDITION}) AS captcha,
countIf(type = 'error') AS errors
FROM events
WHERE pid IN ({pids:Array(FixedString(12))})
AND created BETWEEN {periodStart:String} AND {periodEnd:String}
AND type IN ('pageview', 'custom_event', 'captcha', 'error', 'outbound')
AND importID IS NULL
`
const { data: counts } = await clickhouse
.query({
query,
query_params: params,
})
.then((resultSet) =>
resultSet.json<{
traffic: string | number
customEvents: string | number
outboundLinks: string | number
captcha: string | number
errors: string | number
}>(),
)
const row = counts[0]
totalTraffic += Number(row?.traffic) || 0
totalCustomEvents += Number(row?.customEvents) || 0
totalOutboundLinks += Number(row?.outboundLinks) || 0
totalCaptcha += Number(row?.captcha) || 0
totalErrors += Number(row?.errors) || 0
}
const total =
totalTraffic +
totalCustomEvents +
totalOutboundLinks +
totalCaptcha +
totalErrors
info = {
total,
traffic: totalTraffic,
customEvents: totalCustomEvents,
outboundLinks: totalOutboundLinks,
captcha: totalCaptcha,
errors: totalErrors,
}
await redis.set(
key,
JSON.stringify(info),
'EX',
redisUserUsageinfoCacheTimeout,
)
} else {
try {
info = JSON.parse(info)
} catch (reason) {
console.error(`[ERROR][project -> getRedisUsageInfo] ${reason}`)
info = DEFAULT_USAGE_INFO
}
}
return info as IUsageInfoRedis
}
async getMonthlySessionReplayUsage(uid: string): Promise<number> {
const monthStart = dayjs.utc().startOf('month')
const key = `monthly_replay_usage:${uid}:${monthStart.format('YYYY-MM')}`
const cachedUsage = await redis.get(key)
if (!_isEmpty(cachedUsage)) {
return Number(cachedUsage) || 0
}
const projects = await this.find({
where: {
admin: { id: uid },
},
select: ['id'],
})
if (_isEmpty(projects)) {
await redis.set(key, '0', 'EX', redisUserUsageinfoCacheTimeout)
return 0
}
const CHUNK_SIZE = 5000
const pids = _map(projects, 'id')
let usage = 0
for (let i = 0; i < pids.length; i += CHUNK_SIZE) {
const pidChunk = pids.slice(i, i + CHUNK_SIZE)
const { data } = await clickhouse
.query({
query: `
SELECT uniqExact(concat(toString(psid), ':', replayId)) AS usage
FROM session_replay_chunks
WHERE pid IN {pids:Array(FixedString(12))}
AND created >= {monthStart:DateTime}
`,
query_params: {
pids: pidChunk,
monthStart: monthStart.format('YYYY-MM-DD HH:mm:ss'),
},
})
.then((resultSet) => resultSet.json<{ usage: number }>())
usage += Number(data[0]?.usage) || 0
}
await redis.set(key, `${usage}`, 'EX', redisUserUsageinfoCacheTimeout)
return usage
}
async clearProjectsRedisCache(uid: string): Promise<void> {
const projects = await this.find({
where: {
admin: { id: uid },
},
})
if (_isEmpty(projects)) {
return
}
const pids = _map(projects, 'id')
await deleteProjectsRedis(pids)
}
async clearProjectsRedisCacheBySubId(subID: string): Promise<void> {
const user = await this.userService.findOne({ where: { subID } })
if (!user) {
return
}
await this.clearProjectsRedisCache(user.id)
}
async getPIDsWhereAnalyticsDataExists(
projectIds: string[],
): Promise<string[]> {
if (_isEmpty(projectIds)) {
return []
}
const params = _reduce(
projectIds,
(acc, curr, index) => ({
...acc,
[`pid_${index}`]: curr,
}),
{},
)
const pids = _join(
_map(params, (val, key) => `{${key}:FixedString(12)}`),
',',
)
const query = `
SELECT DISTINCT pid
FROM events
WHERE pid IN (${pids})
AND type IN ('pageview', 'custom_event', 'outbound')
`
const { data } = await clickhouse
.query({
query,
query_params: params,
})
.then((resultSet) => resultSet.json<{ pid: string }>())
return _map(data, ({ pid }) => pid)
}
async getPIDsWhereErrorsDataExists(projectIds: string[]): Promise<string[]> {
if (_isEmpty(projectIds)) {
return []
}
const params = _reduce(
projectIds,
(acc, curr, index) => ({
...acc,
[`pid_${index}`]: curr,
}),
{},
)
const pids = _join(
_map(params, (val, key) => `{${key}:FixedString(12)}`),
',',
)
const query = `
SELECT DISTINCT pid
FROM events
WHERE pid IN (${pids})
AND type = 'error'
`
const { data } = await clickhouse
.query({
query,
query_params: params,
})
.then((resultSet) => resultSet.json<{ pid: string }>())
return _map(data, ({ pid }) => pid)
}
async getPIDsWhereCaptchaDataExists(projectIds: string[]): Promise<string[]> {
if (_isEmpty(projectIds)) {
return []
}
const params = _reduce(
projectIds,
(acc, curr, index) => ({
...acc,
[`pid_${index}`]: curr,
}),
{},
)
const pids = _join(
_map(params, (val, key) => `{${key}:FixedString(12)}`),
',',
)
const query = `
SELECT DISTINCT pid
FROM events
WHERE pid IN (${pids})
AND type = 'captcha'
`
const { data } = await clickhouse
.query({
query,
query_params: params,
})
.then((resultSet) => resultSet.json<{ pid: string }>())
return _map(data, ({ pid }) => pid)
}
async getPIDsWhereSessionReplayDataExists(
projectIds: string[],
): Promise<string[]> {
if (_isEmpty(projectIds)) {
return []
}
const params = _reduce(
projectIds,
(acc, curr, index) => ({
...acc,
[`pid_${index}`]: curr,
}),
{},
)
const pids = _join(
_map(params, (val, key) => `{${key}:FixedString(12)}`),
',',
)
const query = `
SELECT DISTINCT pid
FROM session_replay_chunks
WHERE pid IN (${pids})
`
const { data } = await clickhouse
.query({
query,
query_params: params,
})
.then((resultSet) => resultSet.json<{ pid: string }>())
return _map(data, ({ pid }) => pid)
}
async getSubscriberByEmail(projectId: string, email: string) {
return this.projectSubscriberRepository.findOne({
where: { projectId, email },
})
}
async addSubscriber(data: AddSubscriberType) {
const subscriber = await this.projectSubscriberRepository.save({ ...data })
await this.sendSubscriberInvite(data, subscriber.id)
return subscriber
}
async sendSubscriberInvite(data: AddSubscriberType, subscriberId: string) {
const { userId, projectId, projectName, email, origin } = data
const actionToken = await this.actionTokens.createActionToken(
userId,
ActionTokenType.ADDING_PROJECT_SUBSCRIBER,
`${projectId}:${subscriberId}`,
)
const url = `${origin}/projects/${projectId}/subscribers/invite?token=${actionToken.id}`
await this.mailerService.sendEmail(
email,
LetterTemplate.ProjectSubscriberInvitation,
{
url,
projectName,
},
)
}
async findOneSubscriber(where: FindOneOptions<ProjectSubscriber>['where']) {
return this.projectSubscriberRepository.findOne({ where })
}
async getSubscriber(projectId: string, subscriberId: string) {
return this.projectSubscriberRepository.findOne({
where: { id: subscriberId, projectId },
})
}
async getSubscriberById(id: string) {
return this.projectSubscriberRepository.findOne({
where: { id },
})
}
async confirmSubscriber(
projectId: string,
subscriberId: string,
token: string,
) {
await this.projectSubscriberRepository.update(
{ id: subscriberId, projectId },
{ isConfirmed: true },
)
await this.actionTokens.deleteActionToken(token)
}
async getSubscribers(projectId: string, queries: GetSubscribersQueriesDto) {
const [subscribers, count] =
await this.projectSubscriberRepository.findAndCount({
skip: Number(queries.offset) || 0,
take: Number(queries.limit) > 100 ? 100 : Number(queries.limit) || 100,
where: { projectId },
})
return { subscribers, count }
}
async createFunnel(projectId: string, data: FunnelCreateDTO) {
const funnel = await this.funnelRepository.save({
...data,
project: { id: projectId },
})
return funnel
}
async getFunnels(projectId: string) {
return this.funnelRepository.find({
where: { project: { id: projectId } },
})
}
async getFunnel(funnelId: string, projectId: string) {
return this.funnelRepository.findOne({
where: { id: funnelId, project: { id: projectId } },
})
}
async updateFunnel(data: FunnelUpdateDTO) {
return this.funnelRepository.update({ id: data.id }, data)
}
async deleteFunnel(id: string) {
return this.funnelRepository.delete({ id })
}
async createAnnotation(projectId: string, data: AnnotationCreateDTO) {
const annotation = await this.annotationRepository.save({
date: data.date,
text: data.text,
project: { id: projectId },
})
return annotation
}
async getAnnotations(projectId: string) {
return this.annotationRepository.find({
where: { project: { id: projectId } },
order: { date: 'ASC' },
})
}
async getAnnotation(annotationId: string, projectId: string) {
return this.annotationRepository.findOne({
where: { id: annotationId, project: { id: projectId } },
})
}
async updateAnnotation(data: AnnotationUpdateDTO) {
const updateData: Partial<Annotation> = {}
if (data.date !== undefined) {
updateData.date = new Date(data.date)
}
if (data.text !== undefined) {
updateData.text = data.text
}
return this.annotationRepository.update({ id: data.id }, updateData)
}
async deleteAnnotation(id: string) {
return this.annotationRepository.delete({ id })
}
async updateSubscriber(
projectId: string,
subscriberId: string,
data: UpdateSubscriberBodyDto,
) {
await this.projectSubscriberRepository.update(
{ id: subscriberId, projectId },
data,
)
return this.getSubscriber(projectId, subscriberId)
}
async removeSubscriber(projectId: string, subscriberId: string) {
await this.projectSubscriberRepository.delete({
id: subscriberId,
projectId,
})
}
async removeSubscriberById(id: string) {
await this.projectSubscriberRepository.delete({
id,
})
}
async getSubscribersForReports(reportFrequency: ReportFrequency) {
return this.projectSubscriberRepository.find({
relations: ['project'],
where: { reportFrequency, isConfirmed: true },
})
}
async getSubscriberProjects(subscriberId: string) {
const projects = await this.projectSubscriberRepository.find({
relations: ['project'],
where: { id: subscriberId },
})
return projects.map((p) => p.project).filter(Boolean)
}
async getOwnProject(projectId: string, userId: string) {
const project = await this.getFullProject(projectId)
if (!project) {
return project
}
this.allowedToManage(project, userId)
return project
}
createUnsubscribeKey(subscriberId: string): string {
const base64 = CryptoJS.Rabbit.encrypt(
subscriberId,
EMAIL_ACTION_ENCRYPTION_KEY,
).toString()
// Convert to URL-safe base64 (base64url)
return base64.replace(/\+/g, '-').replace(/\//g, '_').replace(/=+$/, '')
}
decryptUnsubscribeKey(token: string): string {
let base64 = token.replace(/-/g, '+').replace(/_/g, '/')
const padding = base64.length % 4
if (padding) {
base64 += '='.repeat(4 - padding)
}
const bytes = CryptoJS.Rabbit.decrypt(base64, EMAIL_ACTION_ENCRYPTION_KEY)
return bytes.toString(CryptoJS.enc.Utf8)
}
async transferProject(
projectId: string,
name: string,
userId: string,
email: string,
origin: string,
) {
const actionToken = await this.actionTokens.createActionToken(
userId,
ActionTokenType.TRANSFER_PROJECT,
projectId,
)
await this.projectsRepository.update(
{ id: projectId },
{
isTransferring: true,
},
)
const confirmUrl = `${
isDevelopment ? origin : PRODUCTION_ORIGIN
}/project/transfer/confirm?token=${actionToken.id}`
const cancelUrl = `${
isDevelopment ? origin : PRODUCTION_ORIGIN
}/project/transfer/cancel?token=${actionToken.id}`
await this.mailerService.sendEmail(email, LetterTemplate.ProjectTransfer, {
confirmUrl,
cancelUrl,
name,
})
}
async confirmTransferProject(
projectId: string,
userId: string,
oldAdminId: string,
token: string,
) {
await this.projectsRepository.update(
{ id: projectId },
{ admin: { id: userId }, isTransferring: false },
)
await this.projectShareRepository.save({
user: { id: oldAdminId },
project: { id: projectId },
confirmed: true,
role: Role.admin,
})
await this.actionTokens.deleteActionToken(token)
await deleteProjectRedis(projectId)
}
async cancelTransferProject(token: string, projectId: string) {
await this.projectsRepository.update(
{ id: projectId },
{ isTransferring: false },
)
await this.actionTokens.deleteActionToken(token)
}
async getProjectsByUserId(userId: string) {
return this.projectsRepository.find({
where: { admin: { id: userId } },
})
}
async getProjectByNameAndUserId(name: string, userId: string) {
return this.projectsRepository.findOne({
where: { name, admin: { id: userId } },
})
}
async updateProject(id: string, data: Partial<Project>) {
await this.projectsRepository.update({ id }, data)
}
filterUnsupportedColumns(
filters: CreateProjectViewDto['filters'],
): CreateProjectViewDto['filters'] {
if (!filters) {
return []
}
return _filter(filters, ({ dimension }) =>
_includes(V2_VIEW_FILTER_DIMENSIONS, dimension),
)
}
async addProjectToOrganisation(organisationId: string, projectId: string) {
const project = await this.findOne({
where: { id: projectId },
})
if (!project) {
throw new NotFoundException(`Project with ID ${projectId} not found`)
}
return this.update({ id: projectId }, {
organisation: {
id: organisationId,
} as Organisation,
} as Project)
}
async removeProjectFromOrganisation(
organisationId: string,
projectId: string,
) {
const project = await this.findOne({
where: { id: projectId },
relations: ['organisation'],
})
if (!project) {
throw new NotFoundException(`Project with ID ${projectId} not found`)
}
if (project.organisation?.id !== organisationId) {
throw new BadRequestException(
`Project with ID ${projectId} is not in organisation with ID ${organisationId}`,
)
}
return this.update({ id: projectId }, {
organisation: null,
} as Project)
}
async processDefaultResults(paginated: Pagination<Project>, userId: string) {
const pidsWithData = await this.getPIDsWhereAnalyticsDataExists(
_map(paginated.results, ({ id }) => id),
)
const pidsWithErrorData = await this.getPIDsWhereErrorsDataExists(
_map(paginated.results, ({ id }) => id),
)
const pinnedProjectIds = await this.getPinnedProjectIds(userId)
paginated.results = _map(paginated.results, (project) => {
const userShare = project.share?.find(
(share) => share.user?.id === userId,
)
const organisationMembership = project.organisation?.members?.find(
(member) => member.user?.id === userId,
)
let role
let isAccessConfirmed = true
if (project.admin?.id === userId) {
role = 'owner'
} else if (userShare) {
role = userShare.role
isAccessConfirmed = userShare.confirmed
} else if (organisationMembership) {
role = organisationMembership.role
isAccessConfirmed = organisationMembership.confirmed
}
return {
...project,
isAccessConfirmed,
isLocked: !!project.admin?.dashboardBlockReason,
featureAccess: this.getProjectFeatureAccess(project),
isDataExists: _includes(pidsWithData, project?.id),
isErrorDataExists: _includes(pidsWithErrorData, project?.id),
organisationId: project?.organisation?.id,
isPinned: _includes(pinnedProjectIds, project?.id),
role,
passwordHash: undefined,
admin: undefined,
captchaSecretKey: undefined,
}
})
// Pinned projects are already sorted at the database level in paginate()
return paginated
}
async getPinnedProjectIds(userId: string): Promise<string[]> {
const pinnedProjects = await this.pinnedProjectRepository.find({
where: { user: { id: userId } },
relations: ['project'],
select: {
project: {
id: true,
},
},
})
return _map(pinnedProjects, (pp) => pp.project?.id).filter(Boolean)
}
async pinProject(userId: string, projectId: string): Promise<void> {
// Check if already pinned
const existing = await this.pinnedProjectRepository.findOne({
where: {
user: { id: userId },
project: { id: projectId },
},
})
if (existing) {
return // Already pinned
}
await this.pinnedProjectRepository.save({
user: { id: userId },
project: { id: projectId },
})
}
async unpinProject(userId: string, projectId: string): Promise<void> {
await this.pinnedProjectRepository.delete({
user: { id: userId },
project: { id: projectId },
})
}
async isProjectPinned(userId: string, projectId: string): Promise<boolean> {
const pinned = await this.pinnedProjectRepository.findOne({
where: {
user: { id: userId },
project: { id: projectId },
},
})
return !!pinned
}
}