Skip to content

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

19.0 KB
Raw Blame
// Plain exported functions, not only methods on `createInsightService`'s
// container: a caller with no Ctx yet still needs a direct call. They take
// `ServiceDeps` so the requestId minted at the edge reaches every query, and the
// explain cache runs on `deps.redis`, whose lifecycle the app shell owns.

import { sendEmail } from '../../clients/email';
import type { Logger } from '../../logger';
import type { ServiceDeps, Services } from '../../services';
import { listProjectMemberEmails } from '../../shared/access-lookups';
import { createEngine, type EngineConfig } from './src/engine';
import {
  ENRICH_VERSION,
  enrichInsights,
  type InsightToEnrich,
} from './src/enrich';
import {
  type ExplainInsightInput,
  generateInsightExplanation,
  type InsightExplanation,
} from './src/explain';
import {
  devicesModule,
  entryPagesModule,
  geoModule,
  pageTrendsModule,
  referrersModule,
} from './src/modules';
import { generateWeeklyNarrative } from './src/narrative';
import {
  type GetReferrerSpikesInput,
  getReferrerSpikes as getReferrerSpikesQuery,
  type ReferrerSpikeCluster,
} from './src/referrer-spikes';
import { createInsightStore } from './src/store';

const DAY_MS = 24 * 60 * 60 * 1000;
const EXPLAIN_CACHE_TTL_SEC = 24 * 60 * 60;

// Tier-1 enrichment batch size, kept modest so the prompt stays small and the
// id-mapping reliable.
const ENRICH_BATCH_SIZE = 25;

// Postgres batch-delete chunk — bounds the first (large) purge to never hold a
// single long-lived transaction/lock.
const CLEANUP_BATCH_SIZE = 5000;
const CLEANUP_MAX_BATCHES = 2000; // runaway backstop: 5000 * 2000 = 10M rows
const DEFAULT_INSIGHTS_RETENTION_DAYS = 90;
/** Where the weekly-digest link points when DASHBOARD_URL is not set. */
const DEFAULT_DASHBOARD_URL = 'https://dashboard.openpanel.dev';

// Weekly digest: quiet threshold + how many email-worthy insights to surface.
const WEEKLY_DIGEST_MIN_EVENTS = 100;
const WEEKLY_DIGEST_MAX_INSIGHTS = 5;

const DEFAULT_ENGINE_CONFIG: EngineConfig = {
  keepTopNPerModuleWindow: 20,
  closeStaleAfterDays: 7,
  dimensionBatchSize: 50,
  globalThresholds: {
    minTotal: 200,
    minAbsDelta: 80,
    minPct: 0.15,
  },
};

export interface DailyInsightCandidate {
  projectId: string;
  date: string;
}

export interface WeeklyDigestResult {
  projects: number;
  sent: number;
}

export interface WeeklyDigestPreview {
  sent: boolean;
  to?: string;
  skipped?: string;
  data?: unknown;
}

function clamp01(n: number): number {
  if (Number.isNaN(n)) {
    return 0;
  }
  return Math.max(0, Math.min(1, n));
}

/** The `insightsDaily` cron fan-out: eligible project ids, paired with `date`. */
export async function listDailyInsightCandidates(
  deps: ServiceDeps,
  date: string
): Promise<DailyInsightCandidate[]> {
  const insightStore = createInsightStore(deps);
  const projectIds = await insightStore.listProjectIdsForCadence('daily');
  return projectIds.map((projectId) => ({ projectId, date }));
}

/**
 * Tier-1 AI enrichment for one project's active insights: scores anything
 * new, materially-changed, or enriched under an older prompt version.
 * Failures are logged and skipped — a bad batch must never fail the insights
 * job itself.
 */
async function enrichProjectInsights(
  deps: ServiceDeps,
  projectId: string,
  logger: Logger
): Promise<void> {
  const db = deps.db;

  const stale = await db.projectInsight.findMany({
    where: {
      projectId,
      state: 'active',
      OR: [
        { enrichedAt: null },
        { enrichVersion: null },
        { enrichVersion: { lt: ENRICH_VERSION } },
      ],
    },
    select: {
      id: true,
      moduleKey: true,
      dimensionKey: true,
      windowKind: true,
      title: true,
      summary: true,
      displayName: true,
      direction: true,
      impactScore: true,
      severityBand: true,
      payload: true,
    },
  });

  if (stale.length === 0) {
    return;
  }

  let enriched = 0;
  for (let i = 0; i < stale.length; i += ENRICH_BATCH_SIZE) {
    const batch = stale.slice(i, i + ENRICH_BATCH_SIZE);
    const input: InsightToEnrich[] = batch.map((r) => ({
      id: r.id,
      moduleKey: r.moduleKey,
      dimensionKey: r.dimensionKey,
      windowKind: r.windowKind,
      title: r.title,
      summary: r.summary,
      displayName: r.displayName,
      direction: r.direction,
      impactScore: r.impactScore,
      severityBand: r.severityBand,
      payload: r.payload,
    }));

    let results: Awaited<ReturnType<typeof enrichInsights>>;
    try {
      results = await enrichInsights(deps.config, input);
    } catch (err) {
      logger.error(
        { err, projectId, batchSize: batch.length },
        'insight enrichment call failed; skipping batch'
      );
      continue;
    }

    const byId = new Map(results.map((r) => [r.id, r]));
    for (const row of batch) {
      const e = byId.get(row.id);
      if (!e) {
        continue;
      }
      await db.projectInsight.update({
        where: { id: row.id },
        data: {
          relevanceScore: clamp01(e.relevanceScore),
          aiSummary: e.summary,
          aiCategory: e.category,
          emailWorthy: e.emailWorthy,
          referenceWorthy: e.referenceWorthy,
          enrichedAt: new Date(),
          enrichVersion: ENRICH_VERSION,
        },
      });
      enriched++;
    }
  }

  logger.info(
    { projectId, candidates: stale.length, enriched },
    'insight enrichment complete'
  );
}

/** The `insightsProject` job body: engine run + tier-1 AI enrichment. */
export async function runProjectInsights(
  deps: ServiceDeps,
  args: {
    projectId: string;
    date: string;
    logger: Logger;
  }
): Promise<void> {
  const { projectId, date, logger } = args;
  const insightStore = createInsightStore(deps);
  const engine = createEngine({
    store: insightStore,
    modules: [
      referrersModule,
      entryPagesModule,
      pageTrendsModule,
      geoModule,
      devicesModule,
    ],
    deps,
    config: DEFAULT_ENGINE_CONFIG,
  });

  const projectCreatedAt = await insightStore.getProjectCreatedAt(projectId);

  await engine.runProject({
    projectId,
    cadence: 'daily',
    now: new Date(date),
    projectCreatedAt,
  });

  // Isolated so a provider outage or rate-limit never fails the insights
  // computation itself.
  try {
    await enrichProjectInsights(deps, projectId, logger);
  } catch (err) {
    logger.error({ err, projectId }, 'insight enrichment failed');
  }
}

async function deleteInBatches(
  runBatch: () => Promise<number>
): Promise<number> {
  let total = 0;
  for (let i = 0; i < CLEANUP_MAX_BATCHES; i++) {
    const deleted = await runBatch();
    total += deleted;
    if (deleted < CLEANUP_BATCH_SIZE) {
      break;
    }
  }
  return total;
}

/**
 * Keeps the insights tables bounded (they otherwise grow forever). The
 * `insightCleanup` cron job body.
 */
export async function cleanupStaleInsights(
  deps: ServiceDeps,
  logger: Logger
): Promise<{ insights: number; events: number }> {
  const db = deps.db;
  const retentionDays =
    deps.config.query.insightsRetentionDays ?? DEFAULT_INSIGHTS_RETENTION_DAYS;
  const cutoff = new Date(Date.now() - retentionDays * 24 * 60 * 60 * 1000);

  const suppressed = await deleteInBatches(
    () => db.$executeRaw`
      DELETE FROM "project_insights"
      WHERE "id" IN (
        SELECT "id" FROM "project_insights"
        WHERE "state" = 'suppressed'
        LIMIT ${CLEANUP_BATCH_SIZE}
      )`
  );

  const closed = await deleteInBatches(
    () => db.$executeRaw`
      DELETE FROM "project_insights"
      WHERE "id" IN (
        SELECT "id" FROM "project_insights"
        WHERE "state" = 'closed'
          AND "lastSeenAt" < ${cutoff}
        LIMIT ${CLEANUP_BATCH_SIZE}
      )`
  );
  const insights = suppressed + closed;

  const events = await deleteInBatches(
    () => db.$executeRaw`
      DELETE FROM "insight_events"
      WHERE "id" IN (
        SELECT "id" FROM "insight_events"
        WHERE "createdAt" < ${cutoff}
        LIMIT ${CLEANUP_BATCH_SIZE}
      )`
  );

  logger.info({ retentionDays, insights, events }, 'insight cleanup complete');
  return { insights, events };
}

function pctDelta(
  current: number,
  previous: number
): { delta?: string; direction: 'up' | 'down' | 'flat' } {
  if (previous <= 0) {
    return { direction: 'flat' };
  }
  const pct = ((current - previous) / previous) * 100;
  const direction = pct > 0.5 ? 'up' : pct < -0.5 ? 'down' : 'flat';
  const sign = pct >= 0 ? '+' : '';
  return { delta: `${sign}${pct.toFixed(0)}%`, direction };
}

function ppDelta(
  current: number,
  previous: number
): { delta?: string; direction: 'up' | 'down' | 'flat' } {
  const diff = current - previous;
  const direction = diff > 0.5 ? 'up' : diff < -0.5 ? 'down' : 'flat';
  const sign = diff >= 0 ? '+' : '';
  return { delta: `${sign}${diff.toFixed(0)}pp`, direction };
}

function formatCount(n: number): string {
  return Math.round(n).toLocaleString();
}

function formatRange(startMs: number, endMs: number): string {
  const opts: Intl.DateTimeFormatOptions = { month: 'short', day: 'numeric' };
  const start = new Date(startMs).toLocaleDateString('en-US', opts);
  const end = new Date(endMs).toLocaleDateString('en-US', {
    ...opts,
    year: 'numeric',
  });
  return `${start} – ${end}`;
}

interface DigestProjectRow {
  id: string;
  name: string;
  organizationId: string;
}

/** Assembles one project's digest payload (no sending). */
async function buildDigestData(
  deps: ServiceDeps,
  project: DigestProjectRow,
  opts: { force?: boolean } = {}
): Promise<{ skipped?: string; data?: Record<string, unknown> }> {
  const now = Date.now();
  const curStart = now - 7 * DAY_MS;
  const prevStart = now - 14 * DAY_MS;
  const iso = (ms: number) => new Date(ms).toISOString();

  const db = deps.db;
  const { getAnalyticsOverviewCore } = await import(
    '../overview/overview.service'
  );

  const [cur, prev] = await Promise.all([
    getAnalyticsOverviewCore(deps, {
      projectId: project.id,
      startDate: iso(curStart),
      endDate: iso(now),
      interval: 'day',
    }),
    getAnalyticsOverviewCore(deps, {
      projectId: project.id,
      startDate: iso(prevStart),
      endDate: iso(curStart),
      interval: 'day',
    }),
  ]);

  const c = cur.summary;
  const p = prev.summary;

  if (!opts.force && c.unique_visitors === 0) {
    return { skipped: 'no visitors in the last 7 days' };
  }

  const stats = [
    {
      label: 'visitors',
      value: formatCount(c.unique_visitors),
      ...pctDelta(c.unique_visitors, p.unique_visitors),
    },
    {
      label: 'sessions',
      value: formatCount(c.total_sessions),
      ...pctDelta(c.total_sessions, p.total_sessions),
    },
    {
      label: 'pageviews',
      value: formatCount(c.total_screen_views),
      ...pctDelta(c.total_screen_views, p.total_screen_views),
    },
    {
      label: 'bounce rate',
      value: `${Math.round(c.bounce_rate)}%`,
      ...ppDelta(c.bounce_rate, p.bounce_rate),
    },
  ];

  const insightRows = await db.projectInsight.findMany({
    where: {
      projectId: project.id,
      state: 'active',
      emailWorthy: true,
      windowKind: { in: ['rolling_7d'] },
    },
    orderBy: [
      { relevanceScore: { sort: 'desc', nulls: 'last' } },
      { impactScore: 'desc' },
    ],
    take: WEEKLY_DIGEST_MAX_INSIGHTS,
    select: { title: true, aiSummary: true, summary: true },
  });

  const insights = insightRows.map((i) => ({
    title: i.aiSummary ?? i.title,
    summary: i.summary ?? undefined,
  }));

  const dateRange = formatRange(curStart, now);

  let narrative = '';
  try {
    narrative = await generateWeeklyNarrative(deps.config, {
      projectName: project.name,
      dateRange,
      stats: [
        {
          label: 'visitors',
          current: c.unique_visitors,
          previous: p.unique_visitors,
        },
        {
          label: 'sessions',
          current: c.total_sessions,
          previous: p.total_sessions,
        },
        {
          label: 'pageviews',
          current: c.total_screen_views,
          previous: p.total_screen_views,
        },
        {
          label: 'bounce rate',
          current: c.bounce_rate,
          previous: p.bounce_rate,
          unit: '%',
        },
      ],
      insights,
    });
  } catch {
    // Narrative is best-effort — the digest still sends without it.
  }

  const dashboardUrl = `${deps.config.dashboardUrl || DEFAULT_DASHBOARD_URL}/${project.organizationId}/${project.id}`;

  return {
    data: {
      projectName: project.name,
      dashboardUrl,
      dateRange,
      narrative: narrative || undefined,
      stats,
      insights,
    },
  };
}

/**
 * Per active project: week-over-week stats + the AI's email-worthy insights +
 * a generated narrative, mailed to the members who can open the project.
 * The `weeklyDigest` cron job body.
 */
export async function sendWeeklyDigests(
  deps: ServiceDeps,
  logger: Logger
): Promise<WeeklyDigestResult> {
  const db = deps.db;

  // Prefilter on the raw status column (computed fields can't be used in
  // `where`), then refine with the canonical subscription state below.
  const projects = await db.project.findMany({
    where: {
      deleteAt: null,
      eventsCount: { gt: WEEKLY_DIGEST_MIN_EVENTS },
      ...(deps.config.selfHosted
        ? {}
        : {
            organization: {
              subscriptionStatus: { in: ['active', 'trialing'] },
            },
          }),
    },
    select: {
      id: true,
      name: true,
      organizationId: true,
      organization: { select: { subscriptionState: true } },
    },
  });

  let sent = 0;
  for (const project of projects) {
    try {
      const state = project.organization.subscriptionState;
      if (
        !deps.config.selfHosted &&
        state !== 'active' &&
        state !== 'trialing'
      ) {
        continue;
      }

      const { skipped, data } = await buildDigestData(deps, project);
      if (skipped || !data) {
        continue;
      }
      const emails = await listProjectMemberEmails(db, {
        organizationId: project.organizationId,
        projectId: project.id,
      });
      if (emails.length === 0) {
        continue;
      }
      for (const to of emails) {
        await sendEmail('weekly-digest', { to, data: data as never });
      }
      sent++;
    } catch (err) {
      logger.error({ err, projectId: project.id }, 'weekly digest failed');
    }
  }

  logger.info({ projects: projects.length, sent }, 'weekly digest complete');
  return { projects: projects.length, sent };
}

/**
 * Debug/testing helper for a SINGLE project, bypassing eligibility.
 *   - opts.to    → send only to that address (safe for testing)
 *   - no opts.to → assemble and return the payload without sending (preview)
 *   - opts.force → build even if the project had 0 visitors this week
 */
export async function previewWeeklyDigest(
  deps: ServiceDeps,
  projectId: string,
  opts: { to?: string; force?: boolean } = {}
): Promise<WeeklyDigestPreview> {
  const db = deps.db;
  const project = await db.project.findUnique({
    where: { id: projectId },
    select: { id: true, name: true, organizationId: true },
  });
  if (!project) {
    return { sent: false, skipped: 'project not found' };
  }

  const { skipped, data } = await buildDigestData(deps, project, {
    force: opts.force,
  });
  if (skipped || !data) {
    return { sent: false, skipped, data };
  }

  if (opts.to) {
    await sendEmail('weekly-digest', { to: opts.to, data: data as never });
    return { sent: true, to: opts.to, data };
  }

  return { sent: false, data };
}

export async function listInsights(
  deps: ServiceDeps,
  args: { projectId: string; limit: number }
) {
  const db = deps.db;
  return db.projectInsight.findMany({
    where: { projectId: args.projectId, state: 'active' },
    orderBy: [
      { relevanceScore: { sort: 'desc', nulls: 'last' } },
      { impactScore: 'desc' },
    ],
    take: args.limit,
  });
}

/**
 * The same query as `listInsights` under the name the insights PAGE calls it
 * by; the two differ only in the router — `insight.list` over-fetches and
 * deduplicates by window, `insight.listAll` takes the rows as they come.
 */
export function listAllInsights(
  deps: ServiceDeps,
  args: {
    projectId: string;
    limit: number;
  }
): ReturnType<typeof listInsights> {
  return listInsights(deps, args);
}

/**
 * Explains one insight, cached per insight version (`cacheKey` already
 * carries the insight id + `lastUpdatedAt`) so repeat clicks don't re-bill
 * the LLM.
 */
export async function explainInsight(
  deps: ServiceDeps,
  input: ExplainInsightInput,
  cacheKey: string
): Promise<InsightExplanation | null> {
  const cached = await deps.redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached) as InsightExplanation;
  }

  const explanation = await generateInsightExplanation(deps.config, input);

  // Only cache a successful explanation — a null is a transient LLM failure
  // and should be retried on the next click.
  if (explanation) {
    await deps.redis.setex(
      cacheKey,
      EXPLAIN_CACHE_TTL_SEC,
      JSON.stringify(explanation)
    );
  }

  return explanation;
}

export async function getReferrerSpikes(
  deps: ServiceDeps,
  input: GetReferrerSpikesInput
): Promise<ReferrerSpikeCluster[]> {
  return getReferrerSpikesQuery(deps, input);
}

// Re-exported at each type's own import site (noExportedImports): a type
// that is also used locally still gets `export type {} from '<module>'`
// rather than a second `import`-then-`export` pair gathered here.
export type {
  ExplainInsightInput,
  InsightExplanation,
} from './src/explain';
export type {
  GetReferrerSpikesInput,
  ReferrerSpikeCluster,
} from './src/referrer-spikes';

/** `ctx.services.insight` — a thin binding of the functions above to a Ctx's logger. */
export function createInsightService(
  deps: ServiceDeps,
  _services: () => Services
) {
  const logger = deps.logger.child({ module: 'insight' });

  return {
    listDailyInsightCandidates: (
      date: string
    ): Promise<DailyInsightCandidate[]> =>
      listDailyInsightCandidates(deps, date),
    runProjectInsights: (args: {
      projectId: string;
      date: string;
    }): Promise<void> => runProjectInsights(deps, { ...args, logger }),
    cleanupStaleInsights: (): Promise<{ insights: number; events: number }> =>
      cleanupStaleInsights(deps, logger),
    sendWeeklyDigests: (): Promise<WeeklyDigestResult> =>
      sendWeeklyDigests(deps, logger),
    previewWeeklyDigest: (
      projectId: string,
      opts?: { to?: string; force?: boolean }
    ): Promise<WeeklyDigestPreview> =>
      previewWeeklyDigest(deps, projectId, opts),
    listInsights: (args: {
      projectId: string;
      limit: number;
    }): ReturnType<typeof listInsights> => listInsights(deps, args),
    listAllInsights: (args: {
      projectId: string;
      limit: number;
    }): ReturnType<typeof listAllInsights> => listAllInsights(deps, args),
    explainInsight: (
      input: ExplainInsightInput,
      cacheKey: string
    ): Promise<InsightExplanation | null> =>
      explainInsight(deps, input, cacheKey),
    getReferrerSpikes: (
      input: GetReferrerSpikesInput
    ): Promise<ReferrerSpikeCluster[]> => getReferrerSpikes(deps, input),
  };
}