OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import { sql } from '@openpanel/db/src/clickhouse/sql';
import type {
Prisma,
Project,
ProjectType,
} from '@openpanel/db/src/prisma-client';
import { stripTrailingSlash } from '@openpanel/shared';
import { cacheablePerDb, type DbScope } from '../../cacheable-per-deps';
import { chQuery } from '../../ch-query';
import { TRPCBadRequestError } from '../../rpc/errors';
import type { ServiceDeps, Services } from '../../services';
import { getMemberProjectGrants } from '../../shared/access-lookups';
import { convertClickhouseDateToJs } from '../../shared/ch-dates';
import { TABLE_NAMES } from '../../shared/ch-tables';
import { hashClientSecret, newClientSecret } from '../../shared/client-secret';
import { getId } from '../../slug-id';
import { getClientByIdCached } from '../client/client.service';
import type { IProjectFilters } from './project.constants';
// `getLastEventPerProject` sends session_timezone=UTC; `getProjectEventsCount` is a
// raw `chQuery` and sends none.
const CLIX_SESSION_TIMEZONE = { session_timezone: 'UTC' } as const;
// Session bookkeeping rows are worker-generated (the reaper can emit
// session_end after tracking already stopped) — only real tracking activity
// counts.
const NON_TRACKING_EVENT_NAMES = ['session_start', 'session_end'];
export type IServiceProject = Project;
export type IServiceProjectWithClients = Prisma.ProjectGetPayload<{
include: {
clients: { omit: { secret: true } };
};
}>;
const DAY_IN_SECONDS = 60 * 60 * 24;
export async function getProjectById(deps: DbScope, id: string) {
const res = await deps.db.project.findUnique({
where: {
id,
},
});
if (!res) {
return null;
}
return res;
}
/**
* L1 LRU (60s) + L2 Redis, one instance per Postgres client. Named so its keys
* never meet `getClientByIdCached`'s `cachable::<id>`: a project id is a slug
* of a user-chosen name and can spell out a client id.
*/
export const getProjectByIdCached = cacheablePerDb(
'getProjectById',
getProjectById,
DAY_IN_SECONDS
);
/**
* Whether a project keeps AI prompts and completions. Redis only, no
* in-process copy: an opt-out must reach every ingest process at once.
*/
export const getProjectAiStoreContentCached = cacheablePerDb(
'getProjectAiStoreContent',
async (deps: DbScope, id: string): Promise<boolean | null> => {
const project = await deps.db.project.findUnique({
where: { id },
select: { aiStoreContent: true },
});
return project?.aiStoreContent ?? null;
},
DAY_IN_SECONDS,
{ localCache: false }
);
export async function getProjectWithClients(deps: ServiceDeps, id: string) {
const res = await deps.db.project.findUnique({
where: {
id,
},
include: {
// The stored secret is a hash and is never retrievable after creation.
clients: { omit: { secret: true } },
},
});
if (!res) {
return null;
}
return res;
}
export async function getProjects(
deps: ServiceDeps,
{
organizationId,
userId,
}: {
organizationId: string;
userId: string | null;
}
) {
if (!userId) {
return [];
}
const [projects, member] = await Promise.all([
deps.db.project.findMany({
where: {
organizationId,
},
orderBy: {
eventsCount: 'desc',
},
}),
getMemberProjectGrants(deps.db, { userId, organizationId }),
]);
if (!member) {
return [];
}
if (member.projectScope === 'all') {
return projects;
}
const selected = new Set(member.projects.map((row) => row.projectId));
return projects.filter((project) => selected.has(project.id));
}
/**
* Fast approximate count of a project's events (excluding session_start /
* session_end), read from distinct_event_names_mv instead of the raw events
* table.
*
* Why not count raw events: `name NOT IN (...)` is a negation, so it can't
* prune with idx_name and scans the project's whole events slice — and this
* runs from the sessions job on every batch. The MV already accumulates
* `count() AS event_count` per (project_id, name) insert block, so summing
* it reads a few thousand pre-aggregated rows instead of billions of raw
* ones.
*
* Why approximate: event_count is a plain UInt64, not an aggregate state,
* so MV counter rows whose (project_id, name, created_at) sort key collides
* collapse on merge keeping only one block's count. Live ingestion rarely
* ties on the ms timestamp; bulk imports with coarse timestamps are where
* collisions come from. The error is strictly downward (measured 0.0011%
* low on a 1.46B-event project) — acceptable for this display/onboarding
* counter.
*/
export const getProjectEventsCount = async (
deps: ServiceDeps,
projectId: string
) => {
const res = await chQuery<{ count: number }>(
deps,
sql`SELECT sum(event_count) as count FROM ${sql.id(TABLE_NAMES.event_names_mv)} WHERE project_id = ${sql.string(projectId)} AND name NOT IN ${sql.array('String', NON_TRACKING_EVENT_NAMES)}`
);
return res[0]?.count;
};
/**
* Newest event timestamp per project, for the whole instance in one query.
* Reads the same pre-aggregated MV as getProjectEventsCount (it stores
* max(created_at) per (project_id, name) block), so this scans thousands of
* rows instead of the raw events table. Projects with no events are absent
* from the map.
*/
export const getLastEventPerProject = async (
deps: ServiceDeps
): Promise<Map<string, Date>> => {
const res = await chQuery<{ project_id: string; last_event_at: string }>(
deps,
sql`
SELECT project_id, max(created_at) AS last_event_at
FROM ${sql.id(TABLE_NAMES.event_names_mv)}
WHERE name NOT IN ${sql.array('String', NON_TRACKING_EVENT_NAMES)}
GROUP BY project_id
`,
CLIX_SESSION_TIMEZONE
);
return new Map(
res.map((row) => [
row.project_id,
convertClickhouseDateToJs(row.last_event_at),
])
);
};
/**
* Resolve and validate a projectId for an API client.
* Read clients get their fixed projectId (any supplied value is ignored); root clients
* must supply a projectId belonging to their organization. Throws otherwise.
*/
export async function resolveClientProjectId(
deps: ServiceDeps,
{
clientType,
clientProjectId,
organizationId,
inputProjectId,
}: {
clientType: 'read' | 'root';
clientProjectId: string | null;
organizationId: string;
inputProjectId: string | undefined;
}
): Promise<string> {
if (clientType !== 'root') {
if (!clientProjectId) {
throw new Error('Client is not associated with a project');
}
return clientProjectId;
}
if (!inputProjectId) {
throw new Error(
'projectId is required when using a root (organization-level) client'
);
}
const project = await deps.db.project.findFirst({
where: { id: inputProjectId, organizationId },
select: { id: true },
});
if (!project) {
throw new Error(
'Project not found or does not belong to your organization'
);
}
return inputProjectId;
}
export interface ProjectActivationStatus {
hasFirstEvent: boolean;
firstEventAt: Date | null;
projectCreatedAt: Date;
hasReport: boolean;
hasTeammate: boolean;
}
export async function getProjectActivationStatus(
deps: ServiceDeps,
projectId: string
): Promise<ProjectActivationStatus> {
const project = await deps.db.project.findUniqueOrThrow({
where: { id: projectId },
select: {
firstEventAt: true,
eventsCount: true,
organizationId: true,
createdAt: true,
},
});
const [reportCount, memberCount] = await Promise.all([
deps.db.report.count({ where: { projectId } }),
deps.db.member.count({ where: { organizationId: project.organizationId } }),
]);
return {
// firstEventAt only exists for projects created after the column was
// added; the lifetime counter covers everything older.
hasFirstEvent: !!project.firstEventAt || project.eventsCount > 0,
firstEventAt: project.firstEventAt,
projectCreatedAt: project.createdAt,
hasReport: reportCount > 0,
hasTeammate: memberCount > 1,
};
}
export interface CreatedProjectClient {
id: string;
secret: string;
/** Always a write client: the dashboard shows an MCP token only for read and root. */
type: 'write';
}
export async function listProjectsForOrganization(
deps: ServiceDeps,
organizationId: string
) {
return deps.db.project.findMany({
where: {
organizationId,
deleteAt: null,
},
orderBy: {
createdAt: 'desc',
},
});
}
export async function getProjectForOrganization(
deps: ServiceDeps,
id: string,
organizationId: string
) {
return deps.db.project.findFirst({
where: {
id,
organizationId,
},
});
}
// Grace period between a scheduled deletion and the `delete` cron sweeping it up.
const DELETE_GRACE_PERIOD_MS = 24 * 60 * 60 * 1000;
export function createProjectService(
deps: ServiceDeps,
_services: () => Services
) {
async function createProjectForOrganization(
organizationId: string,
input: {
name: string;
domain?: string | null;
cors: string[];
crossDomain: boolean;
types: ProjectType[];
}
): Promise<{
project: IServiceProject;
client: CreatedProjectClient | null;
}> {
const secret = newClientSecret();
const project = await deps.db.project.create({
data: {
id: await getId(deps, 'project', input.name),
organizationId,
name: input.name,
domain: input.domain ? stripTrailingSlash(input.domain) : null,
cors: input.cors.map((c) => stripTrailingSlash(c)),
crossDomain: input.crossDomain ?? false,
allowUnsafeRevenueTracking: false,
filters: [],
types: input.types,
clients: {
create: {
organizationId,
name: 'First client',
type: 'write',
secret: await hashClientSecret(secret),
},
},
},
include: {
clients: {
select: {
id: true,
},
},
},
});
await Promise.all([
getProjectByIdCached.clear(deps, project.id),
...project.clients.map((client) =>
getClientByIdCached.clear(deps, client.id)
),
]);
return {
project,
client: project.clients[0]
? { id: project.clients[0].id, secret, type: 'write' }
: null,
};
}
async function updateProjectForOrganization(
id: string,
organizationId: string,
input: {
name?: string;
domain?: string | null;
cors?: string[];
crossDomain?: boolean;
allowUnsafeRevenueTracking?: boolean;
aiStoreContent?: boolean;
filters?: IProjectFilters[];
}
): Promise<IServiceProject | null> {
const existing = await deps.db.project.findFirst({
where: { id, organizationId },
include: { clients: { select: { id: true } } },
});
if (!existing) {
return null;
}
const updateData: Prisma.ProjectUpdateInput = {};
if (input.name !== undefined) {
updateData.name = input.name;
}
if (input.domain !== undefined) {
updateData.domain = input.domain
? stripTrailingSlash(input.domain)
: null;
}
if (input.cors !== undefined) {
updateData.cors = input.cors.map((c) => stripTrailingSlash(c));
}
if (input.crossDomain !== undefined) {
updateData.crossDomain = input.crossDomain;
}
if (input.allowUnsafeRevenueTracking !== undefined) {
updateData.allowUnsafeRevenueTracking = input.allowUnsafeRevenueTracking;
}
if (input.aiStoreContent !== undefined) {
updateData.aiStoreContent = input.aiStoreContent;
}
if (input.filters !== undefined) {
updateData.filters = input.filters;
}
const project = await deps.db.project.update({
where: { id },
data: updateData,
});
await Promise.all([
getProjectByIdCached.clear(deps, project.id),
getProjectAiStoreContentCached.clear(deps, project.id),
...existing.clients.map((client) =>
getClientByIdCached.clear(deps, client.id)
),
]);
return project;
}
async function deleteProjectForOrganization(
id: string,
organizationId: string
): Promise<boolean> {
const project = await deps.db.project.findFirst({
where: { id, organizationId },
});
if (!project) {
return false;
}
await deps.db.project.update({
where: { id },
data: {
deleteAt: new Date(Date.now() + DELETE_GRACE_PERIOD_MS),
},
});
await getProjectByIdCached.clear(deps, id);
return true;
}
// Caller is already proven a project (or organization) admin, so no organizationId
// and no ownership re-check.
async function scheduleProjectDeletion(id: string): Promise<void> {
await deps.db.project.update({
where: { id },
data: { deleteAt: new Date(Date.now() + DELETE_GRACE_PERIOD_MS) },
});
}
async function cancelProjectDeletion(id: string): Promise<void> {
const project = await deps.db.project.findUnique({
where: { id },
select: {
organization: {
select: { deleteAt: true },
},
},
});
// If the whole organization is scheduled for deletion, this project's
// deletion is part of it and can only be cancelled at the organization
// level. Cancelling it here would leave the organization unable to delete.
if (project?.organization?.deleteAt) {
throw new TRPCBadRequestError(
'This organization is scheduled for deletion. Cancel the deletion from the organization settings.'
);
}
await deps.db.project.update({
where: { id },
data: { deleteAt: null },
});
}
return {
getProjectById: (id: string): ReturnType<typeof getProjectById> =>
getProjectById(deps, id),
getProjectByIdCached: (id: string): ReturnType<typeof getProjectById> =>
getProjectByIdCached(deps, id),
/** Also called by ingest right after a project's first event. */
clearProjectByIdCache: (id: string): Promise<number> =>
getProjectByIdCached.clear(deps, id),
getProjectWithClients: (
id: string
): ReturnType<typeof getProjectWithClients> =>
getProjectWithClients(deps, id),
getProjects: (
input: Parameters<typeof getProjects>[1]
): ReturnType<typeof getProjects> => getProjects(deps, input),
getProjectEventsCount: (
projectId: string
): ReturnType<typeof getProjectEventsCount> =>
getProjectEventsCount(deps, projectId),
getLastEventPerProject: (): ReturnType<typeof getLastEventPerProject> =>
getLastEventPerProject(deps),
resolveClientProjectId: (
input: Parameters<typeof resolveClientProjectId>[1]
): ReturnType<typeof resolveClientProjectId> =>
resolveClientProjectId(deps, input),
getProjectActivationStatus: (
projectId: string
): ReturnType<typeof getProjectActivationStatus> =>
getProjectActivationStatus(deps, projectId),
listProjectsForOrganization: (
organizationId: string
): ReturnType<typeof listProjectsForOrganization> =>
listProjectsForOrganization(deps, organizationId),
getProjectForOrganization: (
id: string,
organizationId: string
): ReturnType<typeof getProjectForOrganization> =>
getProjectForOrganization(deps, id, organizationId),
createProjectForOrganization,
updateProjectForOrganization,
deleteProjectForOrganization,
scheduleProjectDeletion,
cancelProjectDeletion,
};
}