Skip to content

OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.

15.3 KB
Raw Blame
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,
  };
}