Skip to content

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

6.3 KB
Raw Blame
import { and, eq, inArray, like } from "drizzle-orm";
import { DateTime } from "luxon";
import { clickhouse } from "../../db/clickhouse/clickhouse.js";
import { db } from "../../db/postgres/postgres.js";
import { lifecycleEmailLog, member, user } from "../../db/postgres/schema.js";
import { isIdempotencyConflict, sendTrialGraceEmail } from "../../lib/email/email.js";
import type { TrialGraceSite, TrialGraceStage } from "../../lib/email/templates/TrialGraceEmail.js";
import { createServiceLogger } from "../../lib/logger/logger.js";
import type { TrialGrace } from "../../lib/subscriptionUtils.js";

const MIDWAY_AFTER_DAYS = 30;
const FINAL_NOTICE_DAYS = 7;
// A grace period already under way when this shipped can owe two notices at once; space them
// out instead of sending both from one cron run.
const MIN_DAYS_BETWEEN_NOTICES = 3;

/** Which notice the grace period calls for at `now`. Only ever moves forward. */
export function trialGraceStage(grace: TrialGrace, now: DateTime): TrialGraceStage {
  if (DateTime.fromJSDate(grace.endsAt).diff(now, "days").days <= FINAL_NOTICE_DAYS) return "final";
  if (now.diff(DateTime.fromJSDate(grace.trialEndedAt), "days").days >= MIDWAY_AFTER_DAYS) return "midway";
  return "started";
}

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

/**
 * The emails an organization's owners get while its sites keep collecting after an unpaid trial
 * (see TRIAL_GRACE_DAYS). Each stage goes out once per owner and grace period: the
 * lifecycle_email_log row is claimed before sending and released if the send fails.
 *
 * A failed send can't be told apart from "accepted but the response was lost", so the retry
 * reuses the same Resend idempotency key. Resend then replays the original response, or, since
 * the traffic numbers have moved on, rejects the changed payload as a conflict, which means the
 * first one went out and the claim stays.
 */
class TrialGraceEmails {
  private logger = createServiceLogger("trial-grace");

  private async fetchSiteTraffic(
    siteIds: number[],
    since: Date
  ): Promise<Map<number, { visitors: number; pageviews: number }>> {
    const traffic = new Map<number, { visitors: number; pageviews: number }>();
    if (siteIds.length === 0) return traffic;

    const result = await clickhouse.query({
      query: `
        SELECT
          site_id,
          uniq(COALESCE(NULLIF(identified_user_id, ''), user_id)) AS visitors,
          countIf(type = 'pageview') AS pageviews
        FROM events
        WHERE site_id IN ({siteIds:Array(Int32)})
          AND timestamp >= toDateTime({since:String}, 'UTC')
        GROUP BY site_id
      `,
      format: "JSONEachRow",
      query_params: {
        siteIds,
        since: DateTime.fromJSDate(since, { zone: "utc" }).toFormat("yyyy-MM-dd HH:mm:ss"),
      },
    });
    const rows = await result.json<{ site_id: number; visitors: string | number; pageviews: string | number }>();
    for (const row of rows) {
      traffic.set(Number(row.site_id), { visitors: Number(row.visitors), pageviews: Number(row.pageviews) });
    }
    return traffic;
  }

  /** Sends the organization's owners the notice they are due now, if any. */
  async sendDue(
    org: { id: string; name: string },
    grace: TrialGrace,
    sites: Array<{ siteId: number; domain: string }>,
    now: DateTime = DateTime.utc()
  ): Promise<void> {
    // Stripe is still retrying the first charge and emails the customer about it itself.
    if (grace.retryingPayment) return;

    const stage = trialGraceStage(grace, now);
    const keyPrefix = `trial_grace:${grace.subscriptionId}:`;
    const emailKey = `${keyPrefix}${stage}`;

    const owners = await db
      .select({ userId: user.id, email: user.email })
      .from(member)
      .innerJoin(user, eq(member.userId, user.id))
      .where(and(eq(member.organizationId, org.id), eq(member.role, "owner")));
    if (owners.length === 0) return;

    const sent = await db
      .select({
        userId: lifecycleEmailLog.userId,
        emailKey: lifecycleEmailLog.emailKey,
        sentAt: lifecycleEmailLog.sentAt,
      })
      .from(lifecycleEmailLog)
      .where(
        and(
          inArray(
            lifecycleEmailLog.userId,
            owners.map(owner => owner.userId)
          ),
          like(lifecycleEmailLog.emailKey, `${keyPrefix}%`)
        )
      );

    const due = owners.filter(owner => {
      const mine = sent.filter(row => row.userId === owner.userId);
      if (mine.some(row => row.emailKey === emailKey)) return false;
      return mine.every(row => now.diff(parseTs(row.sentAt), "days").days >= MIN_DAYS_BETWEEN_NOTICES);
    });
    if (due.length === 0) return;

    const traffic = await this.fetchSiteTraffic(
      sites.map(site => site.siteId),
      grace.trialEndedAt
    );
    const siteTraffic: TrialGraceSite[] = sites
      .map(site => ({ domain: site.domain, visitors: 0, pageviews: 0, ...traffic.get(site.siteId) }))
      .sort((a, b) => b.visitors - a.visitors || b.pageviews - a.pageviews);

    for (const owner of due) {
      const [claimed] = await db
        .insert(lifecycleEmailLog)
        .values({ userId: owner.userId, emailKey })
        .onConflictDoNothing()
        .returning({ id: lifecycleEmailLog.id });
      if (!claimed) continue;

      try {
        await sendTrialGraceEmail(
          owner.email,
          {
            stage,
            organizationId: org.id,
            organizationName: org.name,
            trialEndedAt: grace.trialEndedAt,
            endsAt: grace.endsAt,
            sites: siteTraffic,
            now,
          },
          `trial-grace:${owner.userId}:${emailKey}`
        );
        this.logger.info({ organizationId: org.id, userId: owner.userId, stage }, "Sent trial grace email");
      } catch (error) {
        if (isIdempotencyConflict(error)) {
          this.logger.info({ organizationId: org.id, userId: owner.userId, stage }, "Trial grace email already sent");
          continue;
        }
        await db.delete(lifecycleEmailLog).where(eq(lifecycleEmailLog.id, claimed.id));
        this.logger.error(
          { err: error, organizationId: org.id, userId: owner.userId, stage },
          "Error sending trial grace email"
        );
      }
    }
  }
}

export const trialGraceEmails = new TrialGraceEmails();