Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

33.0 KB
Raw Blame
import { and, eq, gt, inArray, like } from "drizzle-orm";
import { DateTime } from "luxon";
import * as cron from "node-cron";
import { clickhouse } from "../../db/clickhouse/clickhouse.js";
import { db } from "../../db/postgres/postgres.js";
import { goals, lifecycleEmailLog, member, sites, user } from "../../db/postgres/schema.js";
import { IS_CLOUD } from "../../lib/const.js";
import { isContactUnsubscribed, sendLifecycleEmail } from "../../lib/email/email.js";
import { formatDateTime } from "../../lib/email/format.js";
import { createServiceLogger } from "../../lib/logger/logger.js";
import { signExpiringPayload } from "../../lib/signedToken.js";
import * as content from "./lifecycleContent.js";
import { detectPlatform, platformForKey, type PlatformInfo } from "./platformDetect.js";

/**
 * State-machine lifecycle emails. Every email is triggered by a state
 * transition or a timeout inside a state - never by a timer from signup.
 * The cron re-evaluates real state (Postgres + ClickHouse) at send time, and
 * the unique (userId, emailKey) index on lifecycle_email_log makes each email
 * fire at most once. At most one email per user per run (enforced across both
 * passes via a per-run set), with a minimum gap between educational sends.
 *
 * Per-site emails (install track, "you're live", went quiet) are evaluated per
 * site but sent per user: every site eligible in the same run is bundled into
 * one message. Install and live emails have a 24-hour per-kind cooldown;
 * went-quiet emails have a seven-day per-user cooldown. An agency that adds
 * 13 domains gets one snippet email listing 13 snippets.
 *
 * Concurrency: the cron runs on the cluster primary only, so a single
 * evaluator holds the "one email per user per run" invariant. If this ever
 * moves to multiple replicas, the per-run set must become a distributed
 * claim - the unique key alone only dedupes per emailKey, not per user.
 */

const COHORT_DAYS = 30; // users/sites older than this never enter the onboarding flow
const MIN_GAP_HOURS = 48; // between non-transition emails to the same user
const KIND_COOLDOWN_HOURS = 24; // between install/live emails of the same kind to the same user
const WENT_QUIET_COOLDOWN_DAYS = 7;
const WENT_QUIET_LOOKBACK_DAYS = 14; // covers the 48h silence threshold plus a full weekly cooldown
const CHECK_INSTALL_TTL_SECONDS = 30 * 24 * 3600;
const NEGATIVE_CACHE_TTL_MS = 12 * 3600 * 1000; // don't re-run a no-result ClickHouse probe for this long

interface SiteStats {
  firstEvent: DateTime;
  lastEvent: DateTime;
  total: number;
  pageviews: number;
  customEvents: number;
}

interface OwnedSite {
  siteId: number;
  domain: string;
  createdAt: DateTime;
  detectedPlatform: string | null;
}

const parseTs = (value: string): DateTime => {
  const sql = DateTime.fromSQL(value, { zone: "utc" });
  return sql.isValid ? sql : DateTime.fromISO(value, { zone: "utc" });
};

/** Latest sentAt among log keys starting with any of the prefixes, or null. */
const lastSentWithPrefix = (sentLog: Map<string, DateTime>, prefixes: string[]): DateTime | null => {
  let latest: DateTime | null = null;
  for (const [key, at] of sentLog) {
    if (!prefixes.some(p => key.startsWith(p))) continue;
    if (!latest || at > latest) latest = at;
  }
  return latest;
};

interface BundleEntry {
  key: string;
  siteId: number | null;
}

/** "Desktop, Chrome on macOS" from whichever parts the event recorded. */
const describeDevice = (deviceType?: string, browser?: string, os?: string): string | null => {
  const client = [browser, os].filter(Boolean).join(" on ");
  const parts = [deviceType, client].filter(Boolean);
  return parts.length > 0 ? parts.join(", ") : null;
};

const countryName = (code: string | null): string | null => {
  if (!code) return null;
  try {
    return new Intl.DisplayNames(["en"], { type: "region" }).of(code) ?? code;
  } catch {
    return code;
  }
};

class LifecycleEmailService {
  private cronTask: cron.ScheduledTask | null = null;
  private logger = createServiceLogger("lifecycle-emails");
  private running = false;
  private lastWentQuietAt: DateTime | null = null;
  /** cacheKey -> epoch ms until which a known-negative ClickHouse probe is not repeated */
  private negativeCache = new Map<string, number>();
  /** users already sent an email in the current run, shared across both passes */
  private emailedThisRun = new Set<string>();

  // -------------------------------------------------------------------------
  // Data helpers
  // -------------------------------------------------------------------------

  private async fetchSiteStats(siteIds: number[]): Promise<Map<number, SiteStats>> {
    const stats = new Map<number, SiteStats>();
    if (siteIds.length === 0) return stats;

    // Cohort sites are < COHORT_DAYS old, so a bounded time predicate covers
    // their full history and keeps the scan on the primary-key index.
    const result = await clickhouse.query({
      query: `
        SELECT
          site_id,
          min(timestamp) AS first_event,
          max(timestamp) AS last_event,
          count() AS total,
          countIf(type = 'pageview') AS pageviews,
          countIf(type = 'custom_event') AS custom_events
        FROM events
        WHERE site_id IN ({siteIds:Array(Int32)})
          AND timestamp > now() - INTERVAL ${COHORT_DAYS + 10} DAY
        GROUP BY site_id
      `,
      format: "JSONEachRow",
      query_params: { siteIds },
    });

    const rows = await result.json<{
      site_id: number;
      first_event: string;
      last_event: string;
      total: string | number;
      pageviews: string | number;
      custom_events: string | number;
    }>();

    for (const row of rows) {
      stats.set(Number(row.site_id), {
        firstEvent: parseTs(row.first_event),
        lastEvent: parseTs(row.last_event),
        total: Number(row.total),
        pageviews: Number(row.pageviews),
        customEvents: Number(row.custom_events),
      });
    }
    return stats;
  }

  private async fetchFirstPageview(siteId: number): Promise<content.FirstPageview | null> {
    const result = await clickhouse.query({
      query: `
        SELECT timestamp, country, pathname, device_type, browser, operating_system FROM events
        WHERE site_id = {siteId:Int32} AND type = 'pageview'
          AND timestamp > now() - INTERVAL 7 DAY
        ORDER BY timestamp ASC
        LIMIT 1
      `,
      format: "JSONEachRow",
      query_params: { siteId },
    });
    const rows = await result.json<{
      timestamp?: string;
      country?: string;
      pathname?: string;
      device_type?: string;
      browser?: string;
      operating_system?: string;
    }>();
    if (rows.length === 0) return null;
    const row = rows[0];
    const receivedAt = row.timestamp ? parseTs(row.timestamp) : null;
    return {
      receivedAt: receivedAt?.isValid ? formatDateTime(receivedAt) : null,
      country: countryName(row.country || null),
      pathname: row.pathname || null,
      device: describeDevice(row.device_type, row.browser, row.operating_system),
    };
  }

  private async fetchFirstDaysStats(siteId: number): Promise<content.FirstDaysStats | null> {
    const overviewResult = await clickhouse.query({
      query: `
        SELECT uniq(session_id) AS sessions, countIf(type = 'pageview') AS pageviews
        FROM events
        WHERE site_id = {siteId:Int32}
          AND timestamp > now() - INTERVAL ${COHORT_DAYS + 10} DAY
      `,
      format: "JSONEachRow",
      query_params: { siteId },
    });
    const [overview] = await overviewResult.json<{ sessions: string | number; pageviews: string | number }>();
    if (!overview || Number(overview.pageviews) === 0) return null;

    const topOf = async (expression: string, condition: string): Promise<string | null> => {
      const result = await clickhouse.query({
        query: `
          SELECT ${expression} AS value, count() AS c
          FROM events
          WHERE site_id = {siteId:Int32} AND type = 'pageview' AND ${condition}
            AND timestamp > now() - INTERVAL ${COHORT_DAYS + 10} DAY
          GROUP BY value
          ORDER BY c DESC
          LIMIT 1
        `,
        format: "JSONEachRow",
        query_params: { siteId },
      });
      const rows = await result.json<{ value: string }>();
      return rows[0]?.value || null;
    };

    return {
      sessions: Number(overview.sessions),
      pageviews: Number(overview.pageviews),
      topReferrer: await topOf("domainWithoutWWW(referrer)", "referrer != ''"),
      topPage: await topOf("pathname", "pathname != ''"),
    };
  }

  private async fetchConvertingPath(siteId: number): Promise<content.ConvertingPath | null> {
    const cacheKey = `conv:${siteId}`;
    const retryAfter = this.negativeCache.get(cacheKey);
    if (retryAfter && retryAfter > Date.now()) return null;

    const result = await clickhouse.query({
      query: `
        SELECT pathname, uniq(session_id) AS sessions
        FROM events
        WHERE site_id = {siteId:Int32}
          AND type = 'pageview'
          AND timestamp > now() - INTERVAL 14 DAY
          AND match(pathname, '(thank|success|checkout|confirmation|purchase|order-complete|welcome)')
        GROUP BY pathname
        ORDER BY sessions DESC
        LIMIT 1
      `,
      format: "JSONEachRow",
      query_params: { siteId },
    });
    const rows = await result.json<{ pathname: string; sessions?: string | number }>();
    const path = rows[0]?.pathname || null;
    if (!path) {
      this.negativeCache.set(cacheKey, Date.now() + NEGATIVE_CACHE_TTL_MS);
      return null;
    }
    return { pathname: path, sessions: Number(rows[0].sessions ?? 0) };
  }

  private async resolvePlatform(site: OwnedSite): Promise<PlatformInfo | null> {
    const known = platformForKey(site.detectedPlatform);
    if (known) return known;

    const detected = await detectPlatform(site.domain);
    if (detected) {
      await db.update(sites).set({ detectedPlatform: detected.key }).where(eq(sites.siteId, site.siteId));
    }
    return detected;
  }

  private checkInstallUrl(siteId: number, domain: string): string {
    const { exp, sig } = signExpiringPayload(`check-install:${siteId}:${domain}`, CHECK_INSTALL_TTL_SECONDS);
    return `${process.env.BASE_URL}/api/site/check-install?siteId=${siteId}&domain=${encodeURIComponent(domain)}&exp=${exp}&sig=${sig}`;
  }

  /**
   * Record + send one email covering one or more per-site keys. The inserts
   * happen first with the unique index as the guard, so a re-entrant run can
   * never double-send; if the send then fails, the rows are removed so the next
   * tick retries. Keys that already exist are dropped from the bundle and the
   * email is built from the ones actually claimed.
   *
   * A send failure can't be told apart from "accepted but response lost", so
   * the rollback-and-retry is only safe because the Resend idempotency key is
   * stable across retries: it must not depend on which sites happen to be in
   * the bundle (membership can change between ticks) - see bundleKey. Resend
   * then either returns the original response or rejects the mismatched
   * payload; it never delivers a second email.
   */
  private async sendBundle(
    userId: string,
    email: string,
    entries: BundleEntry[],
    idempotencyKey: string,
    build: (claimed: BundleEntry[]) => Promise<content.LifecycleEmail | null> | content.LifecycleEmail | null
  ): Promise<boolean> {
    if (entries.length === 0) return false;
    const inserted = await db
      .insert(lifecycleEmailLog)
      .values(entries.map(e => ({ userId, emailKey: e.key, siteId: e.siteId })))
      .onConflictDoNothing()
      .returning({ id: lifecycleEmailLog.id, emailKey: lifecycleEmailLog.emailKey });
    if (inserted.length === 0) return false; // all already sent

    const insertedIds = inserted.map(r => r.id);
    const claimedKeys = new Set(inserted.map(r => r.emailKey));
    const claimed = entries.filter(e => claimedKeys.has(e.key));
    const rollback = () => db.delete(lifecycleEmailLog).where(inArray(lifecycleEmailLog.id, insertedIds));

    try {
      const message = await build(claimed);
      if (!message) {
        await rollback();
        return false;
      }
      const sent = await sendLifecycleEmail(email, message, idempotencyKey);
      if (!sent) {
        await rollback();
        return false;
      }
      this.emailedThisRun.add(userId);
      this.logger.info({ userId, emailKeys: claimed.map(e => e.key) }, "Sent lifecycle email");
      return true;
    } catch (error) {
      this.logger.error({ err: error, userId, emailKeys: claimed.map(e => e.key) }, "Error sending lifecycle email");
      await rollback();
      return false;
    }
  }

  /**
   * Idempotency key for a bundled kind. Each kind reaches a user at most once
   * per cooldown window, so "the window since this kind's last successful
   * send" identifies the email: a rolled-back send leaves lastSent unchanged
   * and the retry reuses the key whatever sites are eligible by then, while a
   * successful send advances lastSent and the next bundle gets a fresh key.
   */
  private bundleKey(userId: string, kind: string, lastSent: DateTime | null): string {
    return `lifecycle:${userId}:${kind}:${lastSent ? lastSent.toMillis() : "first"}`;
  }

  private sendOnce(
    userId: string,
    email: string,
    emailKey: string,
    siteId: number | null,
    build: () => Promise<content.LifecycleEmail | null> | content.LifecycleEmail | null
  ): Promise<boolean> {
    return this.sendBundle(userId, email, [{ key: emailKey, siteId }], `lifecycle:${userId}:${emailKey}`, build);
  }

  // -------------------------------------------------------------------------
  // Onboarding pass: users in their first COHORT_DAYS
  // -------------------------------------------------------------------------

  private async processOnboarding(now: DateTime): Promise<void> {
    const cohortStart = now.minus({ days: COHORT_DAYS });

    // No emailVerified filter: this app doesn't require (or send) email
    // verification on password signup, so gating on it would exclude nearly
    // every normal signup. Bounce protection comes from Resend suppression
    // (isContactUnsubscribed) checked before every send.
    const users = await db
      .select({ id: user.id, email: user.email, name: user.name, createdAt: user.createdAt })
      .from(user)
      .where(gt(user.createdAt, cohortStart.toSQL({ includeOffset: false })!));
    if (users.length === 0) return;

    const userIds = users.map(u => u.id);
    const memberships = await db
      .select({ userId: member.userId, organizationId: member.organizationId, role: member.role })
      .from(member)
      .where(inArray(member.userId, userIds));

    const ownedOrgsByUser = new Map<string, string[]>();
    const anyMembership = new Set<string>();
    for (const m of memberships) {
      anyMembership.add(m.userId);
      if (m.role === "owner") {
        ownedOrgsByUser.set(m.userId, [...(ownedOrgsByUser.get(m.userId) ?? []), m.organizationId]);
      }
    }

    const allOwnedOrgIds = [...new Set([...ownedOrgsByUser.values()].flat())];
    const orgSites =
      allOwnedOrgIds.length > 0
        ? await db
            .select({
              siteId: sites.siteId,
              domain: sites.domain,
              createdAt: sites.createdAt,
              organizationId: sites.organizationId,
              detectedPlatform: sites.detectedPlatform,
            })
            .from(sites)
            .where(inArray(sites.organizationId, allOwnedOrgIds))
        : [];

    const sitesByOrg = new Map<string, OwnedSite[]>();
    for (const s of orgSites) {
      if (!s.organizationId || !s.createdAt) continue;
      const entry: OwnedSite = {
        siteId: s.siteId,
        domain: s.domain,
        createdAt: parseTs(s.createdAt),
        detectedPlatform: s.detectedPlatform,
      };
      sitesByOrg.set(s.organizationId, [...(sitesByOrg.get(s.organizationId) ?? []), entry]);
    }

    const allSiteIds = orgSites.map(s => s.siteId);
    const [stats, logs, siteGoals] = await Promise.all([
      this.fetchSiteStats(allSiteIds),
      db
        .select({ userId: lifecycleEmailLog.userId, emailKey: lifecycleEmailLog.emailKey, sentAt: lifecycleEmailLog.sentAt })
        .from(lifecycleEmailLog)
        .where(inArray(lifecycleEmailLog.userId, userIds)),
      allSiteIds.length > 0
        ? db.select({ siteId: goals.siteId }).from(goals).where(inArray(goals.siteId, allSiteIds))
        : Promise.resolve([]),
    ]);

    const logsByUser = new Map<string, Map<string, DateTime>>();
    for (const log of logs) {
      const map = logsByUser.get(log.userId) ?? new Map<string, DateTime>();
      map.set(log.emailKey, parseTs(log.sentAt));
      logsByUser.set(log.userId, map);
    }
    const sitesWithGoals = new Set(siteGoals.map(g => g.siteId));

    for (const u of users) {
      try {
        const ownedOrgs = ownedOrgsByUser.get(u.id) ?? [];
        // Invited teammates (members of someone else's org, owners of none) get no onboarding
        if (ownedOrgs.length === 0 && anyMembership.has(u.id)) continue;

        const userSites = ownedOrgs.flatMap(orgId => sitesByOrg.get(orgId) ?? []);
        await this.processUser(now, u, userSites, stats, logsByUser.get(u.id) ?? new Map(), sitesWithGoals);
      } catch (error) {
        this.logger.error({ err: error, userId: u.id }, "Error processing user lifecycle state");
      }
    }
  }

  /** Evaluate one user's state and send at most one email. */
  private async processUser(
    now: DateTime,
    u: { id: string; email: string; name: string; createdAt: string },
    userSites: OwnedSite[],
    stats: Map<number, SiteStats>,
    sentLog: Map<string, DateTime>,
    sitesWithGoals: Set<number>
  ): Promise<void> {
    const lastSent = [...sentLog.values()].sort((a, b) => b.toMillis() - a.toMillis())[0] ?? null;
    const gapElapsed = !lastSent || now.diff(lastSent, "hours").hours >= MIN_GAP_HOURS;
    const signupAge = now.diff(parseTs(u.createdAt), "hours").hours;

    const unsubscribed = () => isContactUnsubscribed(u.email);

    const lastLiveSent = lastSentWithPrefix(sentLog, ["site_live:"]);
    const lastInstallSent = lastSentWithPrefix(sentLog, ["install_"]);
    const cooldownElapsed = (last: DateTime | null) => !last || now.diff(last, "hours").hours >= KIND_COOLDOWN_HOURS;

    // --- Transition: a site just received its first data -> "you're live" ---
    // Exempt from the gap; it's a confirmation, not education. Only fires while
    // the first event is fresh so pre-existing sites don't get it late. All
    // sites that went live since the last such email share one message.
    const liveSites = userSites.filter(site => {
      const s = stats.get(site.siteId);
      if (!s || s.total === 0) return false;
      if (now.diff(s.firstEvent, "hours").hours > 72) return false;
      return !sentLog.has(`site_live:${site.siteId}`);
    });
    if (liveSites.length > 0 && cooldownElapsed(lastLiveSent)) {
      if (await unsubscribed()) return;
      const sent = await this.sendBundle(
        u.id,
        u.email,
        liveSites.map(site => ({ key: `site_live:${site.siteId}`, siteId: site.siteId })),
        this.bundleKey(u.id, "site_live", lastLiveSent),
        async claimed => {
          const chosen = liveSites.filter(site => claimed.some(e => e.siteId === site.siteId));
          const first = chosen.length === 1 ? await this.fetchFirstPageview(chosen[0].siteId) : null;
          return content.siteLive(chosen, first, u.name);
        }
      );
      if (sent) return;
    }

    // --- State: no site created ---
    if (userSites.length === 0) {
      if (!sentLog.has("no_site_1")) {
        if (signupAge < 2) return;
        if (await unsubscribed()) return;
        await this.sendOnce(u.id, u.email, "no_site_1", null, () => content.noSite1(u.name));
      } else if (!sentLog.has("no_site_2") && signupAge >= 72 && gapElapsed) {
        if (await unsubscribed()) return;
        await this.sendOnce(u.id, u.email, "no_site_2", null, () => content.noSite2(u.name));
      }
      return;
    }

    // --- Install track: evaluated per site, so a working site doesn't
    // suppress install help for the owner's other new sites. Sent per user:
    // every site at the same stage goes into one email, and the track sends
    // at most one email per KIND_COOLDOWN_HOURS, snippet stage first ---
    const noDataSites = userSites
      .filter(site => !stats.get(site.siteId) && now.diff(site.createdAt, "days").days < COHORT_DAYS)
      .sort((a, b) => a.createdAt.toMillis() - b.createdAt.toMillis());

    if (noDataSites.length > 0 && cooldownElapsed(lastInstallSent)) {
      // One install email per window, so the three stages share the key.
      const installKey = this.bundleKey(u.id, "install", lastInstallSent);
      const hoursSince = (key: string) => {
        const at = sentLog.get(key);
        return at ? now.diff(at, "hours").hours : null;
      };
      const snippetKey = (id: number) => `install_snippet:${id}`;
      const checkKey = (id: number) => `install_check:${id}`;
      const finalKey = (id: number) => `install_final:${id}`;

      // The snippet email is the user's next step right after creating a
      // site; it is exempt from the educational gap.
      const snippetDue = noDataSites.filter(
        site => !sentLog.has(snippetKey(site.siteId)) && now.diff(site.createdAt, "hours").hours >= 1
      );
      if (snippetDue.length > 0) {
        if (await unsubscribed()) return;
        const sent = await this.sendBundle(
          u.id,
          u.email,
          snippetDue.map(site => ({ key: snippetKey(site.siteId), siteId: site.siteId })),
          installKey,
          async claimed => {
            const chosen = snippetDue.filter(site => claimed.some(e => e.siteId === site.siteId));
            const withPlatform: content.SnippetSite[] = [];
            for (const site of chosen) {
              withPlatform.push({ domain: site.domain, siteId: site.siteId, platform: await this.resolvePlatform(site) });
            }
            return content.installSnippet(withPlatform, u.name);
          }
        );
        if (sent) return;
      }

      // Timed from the snippet email, not site age, so a pre-existing site
      // picked up at rollout doesn't get two install emails in one hour.
      const checkDue = noDataSites.filter(site => {
        if (sentLog.has(checkKey(site.siteId))) return false;
        const since = hoursSince(snippetKey(site.siteId));
        return since !== null && since >= 24;
      });
      if (checkDue.length > 0) {
        if (await unsubscribed()) return;
        const sent = await this.sendBundle(
          u.id,
          u.email,
          checkDue.map(site => ({ key: checkKey(site.siteId), siteId: site.siteId })),
          installKey,
          claimed =>
            content.installCheck(
              checkDue
                .filter(site => claimed.some(e => e.siteId === site.siteId))
                .map(site => ({ domain: site.domain, checkInstallUrl: this.checkInstallUrl(site.siteId, site.domain) })),
              u.name
            )
        );
        if (sent) return;
      }

      const finalDue = gapElapsed
        ? noDataSites.filter(site => {
            if (sentLog.has(finalKey(site.siteId))) return false;
            const since = hoursSince(checkKey(site.siteId));
            return since !== null && since >= 96;
          })
        : [];
      if (finalDue.length > 0) {
        if (await unsubscribed()) return;
        const sent = await this.sendBundle(
          u.id,
          u.email,
          finalDue.map(site => ({ key: finalKey(site.siteId), siteId: site.siteId })),
          installKey,
          claimed =>
            content.installFinal(
              finalDue.filter(site => claimed.some(e => e.siteId === site.siteId)).map(site => site.domain),
              u.name
            )
        );
        if (sent) return;
      }
    }

    // --- State: data flowing. Value emails keyed to data age, not signup age ---
    if (!gapElapsed) return;

    for (const site of userSites) {
      const s = stats.get(site.siteId);
      if (!s || s.total === 0) continue;
      const dataAgeDays = now.diff(s.firstEvent, "days").days;

      // Pageview count comes from the batched stats, so a custom-event-only
      // site is skipped without a per-tick ClickHouse probe.
      if (dataAgeDays >= 3 && dataAgeDays < 14 && s.pageviews > 0 && !sentLog.has(`first_days:${site.siteId}`)) {
        if (await unsubscribed()) return;
        const sent = await this.sendOnce(u.id, u.email, `first_days:${site.siteId}`, site.siteId, async () => {
          const report = await this.fetchFirstDaysStats(site.siteId);
          return report
            ? content.firstDays(site.domain, site.siteId, report, Math.max(1, Math.floor(dataAgeDays)), u.name)
            : null;
        });
        if (sent) return;
      }

      // Evidence-based nudges: only when their data shows the feature applies
      if (dataAgeDays >= 7 && dataAgeDays < COHORT_DAYS && !sitesWithGoals.has(site.siteId) && !sentLog.has(`nudge_goals:${site.siteId}`)) {
        const path = await this.fetchConvertingPath(site.siteId);
        if (path) {
          if (await unsubscribed()) return;
          const sent = await this.sendOnce(u.id, u.email, `nudge_goals:${site.siteId}`, site.siteId, () =>
            content.nudgeGoals(site.domain, site.siteId, path, u.name)
          );
          if (sent) return;
        }
      }

      if (dataAgeDays >= 10 && dataAgeDays < COHORT_DAYS && s.customEvents === 0 && !sentLog.has(`nudge_events:${site.siteId}`)) {
        if (await unsubscribed()) return;
        const sent = await this.sendOnce(u.id, u.email, `nudge_events:${site.siteId}`, site.siteId, () =>
          content.nudgeEvents(site.domain, site.siteId, u.name)
        );
        if (sent) return;
      }
    }
  }

  // -------------------------------------------------------------------------
  // Went-quiet pass: any site (not just new ones) that stopped sending data
  // -------------------------------------------------------------------------

  private async processWentQuiet(now: DateTime): Promise<void> {
    // Keep candidates for two weeks so a site that becomes quiet during its
    // owner's weekly cooldown can still join the next bundle. Evaluate the
    // window against this run's clock and re-check silence before sending.
    const result = await clickhouse.query({
      query: `
        SELECT site_id, max(timestamp) AS last_event
        FROM events
        WHERE timestamp > toDateTime({windowStart:UInt32}, 'UTC')
        GROUP BY site_id
        HAVING last_event < toDateTime({quietBefore:UInt32}, 'UTC')
      `,
      format: "JSONEachRow",
      query_params: {
        windowStart: Math.floor(now.minus({ days: WENT_QUIET_LOOKBACK_DAYS }).toSeconds()),
        quietBefore: Math.floor(now.minus({ hours: 48 }).toSeconds()),
      },
    });
    const quietRows = await result.json<{ site_id: number; last_event: string }>();
    if (quietRows.length === 0) return;

    const quietSiteIds = quietRows.map(r => Number(r.site_id));

    // "Established" check, bounded: at least 100 events in the 3 weeks before
    // going quiet - a scratch site with a handful of test hits gets no alert.
    const volumeResult = await clickhouse.query({
      query: `
        SELECT site_id, count() AS total
        FROM events
        WHERE site_id IN ({siteIds:Array(Int32)})
          AND timestamp > now() - INTERVAL 21 DAY
        GROUP BY site_id
        HAVING total >= 100
      `,
      format: "JSONEachRow",
      query_params: { siteIds: quietSiteIds },
    });
    const establishedIds = new Set((await volumeResult.json<{ site_id: number }>()).map(r => Number(r.site_id)));

    const candidates = quietRows.filter(r => establishedIds.has(Number(r.site_id)));
    if (candidates.length === 0) return;

    const quietSites = await db
      .select({ siteId: sites.siteId, domain: sites.domain, organizationId: sites.organizationId })
      .from(sites)
      .where(inArray(sites.siteId, candidates.map(r => Number(r.site_id))));
    if (quietSites.length === 0) return;

    // 30-day per-site cooldown: a recovered-then-quiet-again site alerts at
    // most monthly even though each outage gets its own email key. The same
    // rows give each owner's last went-quiet email for the per-user cooldown.
    const recentQuietLogs = await db
      .select({ siteId: lifecycleEmailLog.siteId, userId: lifecycleEmailLog.userId, sentAt: lifecycleEmailLog.sentAt })
      .from(lifecycleEmailLog)
      .where(
        and(
          like(lifecycleEmailLog.emailKey, "went_quiet:%"),
          gt(lifecycleEmailLog.sentAt, now.minus({ days: 30 }).toSQL({ includeOffset: false })!)
        )
      );
    const recentlyAlerted = new Set(recentQuietLogs.map(r => r.siteId));
    const lastQuietEmailByUser = new Map<string, DateTime>();
    for (const r of recentQuietLogs) {
      const at = parseTs(r.sentAt);
      const prev = lastQuietEmailByUser.get(r.userId);
      if (!prev || at > prev) lastQuietEmailByUser.set(r.userId, at);
    }

    const orgIds = [...new Set(quietSites.map(s => s.organizationId).filter((id): id is string => !!id))];
    const owners =
      orgIds.length > 0
        ? await db
            .select({ organizationId: member.organizationId, userId: user.id, email: user.email, name: user.name })
            .from(member)
            .innerJoin(user, eq(member.userId, user.id))
            .where(and(inArray(member.organizationId, orgIds), eq(member.role, "owner")))
        : [];

    const ownerByOrg = new Map(owners.map(o => [o.organizationId, o]));
    const lastEventBySite = new Map(quietRows.map(r => [Number(r.site_id), parseTs(r.last_event)]));

    // Group across the owner's organizations: all eligible quiet sites share
    // one email, at most once every seven days.
    const quietByOwner = new Map<string, { owner: (typeof owners)[number]; sites: typeof quietSites }>();
    for (const site of quietSites) {
      if (recentlyAlerted.has(site.siteId)) continue;
      const owner = site.organizationId ? ownerByOrg.get(site.organizationId) : null;
      if (!owner) continue;
      const entry = quietByOwner.get(owner.userId) ?? { owner, sites: [] };
      entry.sites.push(site);
      quietByOwner.set(owner.userId, entry);
    }

    for (const { owner, sites: ownerSites } of quietByOwner.values()) {
      // One email per user per run, across both passes: an owner who just got
      // an onboarding email waits for the next tick.
      if (this.emailedThisRun.has(owner.userId)) continue;
      const lastQuiet = lastQuietEmailByUser.get(owner.userId) ?? null;
      if (lastQuiet && now.diff(lastQuiet, "hours").hours < WENT_QUIET_COOLDOWN_DAYS * 24) continue;
      try {
        if (await isContactUnsubscribed(owner.email)) continue;
        // Per-outage key: the date of the last event identifies the outage, so
        // a site that recovers and goes quiet again next month can alert again.
        const entries = ownerSites.map(site => ({
          key: `went_quiet:${site.siteId}:${lastEventBySite.get(site.siteId)?.toFormat("yyyy-MM-dd") ?? "unknown"}`,
          siteId: site.siteId,
        }));
        await this.sendBundle(owner.userId, owner.email, entries, this.bundleKey(owner.userId, "went_quiet", lastQuiet), claimed =>
          content.wentQuiet(
            ownerSites
              .filter(site => claimed.some(e => e.siteId === site.siteId))
              .map(site => ({
                domain: site.domain,
                siteId: site.siteId,
                lastEventAt: lastEventBySite.get(site.siteId)?.toFormat("LLL d 'at' HH:mm 'UTC'") ?? "two days ago",
              })),
            owner.name
          )
        );
      } catch (error) {
        this.logger.error({ err: error, userId: owner.userId }, "Error processing went-quiet email");
      }
    }
  }

  // -------------------------------------------------------------------------
  // Entry points
  // -------------------------------------------------------------------------

  async processLifecycleEmails(): Promise<void> {
    if (this.running) return;
    this.running = true;
    this.emailedThisRun = new Set();
    const now = DateTime.utc();
    try {
      await this.processOnboarding(now);
      // The went-quiet scan covers the whole events table (bounded to 14 days);
      // hourly is plenty for a 48h-silence alert.
      if (!this.lastWentQuietAt || now.diff(this.lastWentQuietAt, "minutes").minutes >= 55) {
        this.lastWentQuietAt = now;
        await this.processWentQuiet(now);
      }
    } catch (error) {
      this.logger.error({ err: error }, "Error in lifecycle email run");
    } finally {
      this.running = false;
    }
  }

  startLifecycleCron(): void {
    if (!IS_CLOUD) return;
    if (this.cronTask) {
      this.logger.warn("Lifecycle email cron already running");
      return;
    }

    // Every 10 minutes: state transitions like "first pageview arrived" should
    // reach the user while they're still at their desk.
    this.cronTask = cron.schedule("*/10 * * * *", () => void this.processLifecycleEmails(), { timezone: "UTC" });
    this.logger.info("Lifecycle email cron started - runs every 10 minutes");
  }

  stopLifecycleCron(): void {
    if (this.cronTask) {
      this.cronTask.stop();
      this.cronTask = null;
      this.logger.info("Lifecycle email cron stopped");
    }
  }
}

export const lifecycleEmailService = new LifecycleEmailService();