Skip to content

armgit / kaneo

Public Imported from GitHub
0

⚡ Project management that doesn't become the project. Boards, Gantt charts, and GitHub integration. Self-host it or use Kaneo Cloud.

5.8 KB
Raw Blame
import { isSmtpConfigured, sendTrialReminderEmail } from "@kaneo/email";
import { and, asc, eq, gt, isNotNull, isNull, lte } from "drizzle-orm";
import { isBillingEnabled, trialDays } from "../billing/config";
import db from "../database";
import {
  billingReminderSentTable,
  userTable,
  workspaceBillingTable,
  workspaceTable,
  workspaceUserTable,
} from "../database/schema";

const DAY_MS = 24 * 60 * 60 * 1000;

// Sending is deliberately slow. The provider rate-limits per second and per
// day, and a burst that trips either is silently dropped downstream: SMTP has
// already accepted the message, so nothing surfaces as an error here.
const DEFAULT_MAX_PER_RUN = 25;
const SEND_SPACING_MS = 600;

function maxPerRun() {
  const parsed = Number.parseInt(
    process.env.BILLING_REMINDER_MAX_PER_RUN ?? "",
    10,
  );
  return Number.isNaN(parsed) || parsed <= 0 ? DEFAULT_MAX_PER_RUN : parsed;
}

function sleep(ms: number) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

type ReminderType = "trial_ending" | "trial_expired";

const REMINDERS: {
  type: ReminderType;
  subject: (workspaceName: string) => string;
}[] = [
  {
    type: "trial_ending",
    subject: (name) => `Your ${name} trial ends in 3 days`,
  },
  {
    type: "trial_expired",
    subject: (name) => `Your ${name} trial has ended`,
  },
];

function clientUrl() {
  return (process.env.KANEO_CLIENT_URL ?? "https://cloud.kaneo.app").replace(
    /\/$/,
    "",
  );
}

async function getWorkspacesNeedingReminder(
  type: ReminderType,
  now: Date,
  limit: number,
) {
  const windowStart =
    type === "trial_ending" ? new Date(now.getTime() + 2 * DAY_MS) : null;
  const windowEnd =
    type === "trial_ending" ? new Date(now.getTime() + 3 * DAY_MS) : now;

  const trialWindow =
    type === "trial_ending"
      ? and(
          gt(workspaceBillingTable.trialEndsAt, windowStart as Date),
          lte(workspaceBillingTable.trialEndsAt, windowEnd),
        )
      : lte(workspaceBillingTable.trialEndsAt, windowEnd);

  // One row per owner, not per workspace: trials are granted per owner, so a
  // person with twenty workspaces must still get one email, not twenty.
  // DISTINCT ON forces ORDER BY to lead with the owner, so urgency ordering
  // has to happen outside it or the cap would drop the soonest expiries.
  const owners = db
    .selectDistinctOn([workspaceUserTable.userId], {
      userId: workspaceUserTable.userId,
      workspaceId: workspaceTable.id,
      workspaceName: workspaceTable.name,
      trialEndsAt: workspaceBillingTable.trialEndsAt,
      email: userTable.email,
    })
    .from(workspaceBillingTable)
    .innerJoin(
      workspaceTable,
      eq(workspaceTable.id, workspaceBillingTable.workspaceId),
    )
    .innerJoin(
      workspaceUserTable,
      and(
        eq(workspaceUserTable.workspaceId, workspaceBillingTable.workspaceId),
        eq(workspaceUserTable.role, "owner"),
      ),
    )
    .innerJoin(userTable, eq(userTable.id, workspaceUserTable.userId))
    .leftJoin(
      billingReminderSentTable,
      and(
        eq(billingReminderSentTable.userId, workspaceUserTable.userId),
        eq(billingReminderSentTable.reminderType, type),
      ),
    )
    .where(
      and(
        isNull(billingReminderSentTable.id),
        eq(workspaceBillingTable.foundingFree, false),
        isNotNull(workspaceBillingTable.trialEndsAt),
        isNull(workspaceBillingTable.creemSubscriptionId),
        trialWindow,
        eq(userTable.banned, false),
        // Nobody can upgrade an archived workspace, so do not nag its owners.
        isNull(workspaceTable.archivedAt),
      ),
    )
    .orderBy(
      asc(workspaceUserTable.userId),
      asc(workspaceBillingTable.trialEndsAt),
    )
    .as("owners");

  return db.select().from(owners).orderBy(asc(owners.trialEndsAt)).limit(limit);
}

export async function checkTrialReminders(): Promise<{ degraded: boolean }> {
  if (!isBillingEnabled() || !isSmtpConfigured()) {
    return { degraded: false };
  }

  const now = new Date();
  const limit = maxPerRun();
  let sent = 0;
  let degraded = false;

  for (const reminder of REMINDERS) {
    if (sent >= limit) break;
    let rows: Awaited<ReturnType<typeof getWorkspacesNeedingReminder>>;
    try {
      rows = await getWorkspacesNeedingReminder(
        reminder.type,
        now,
        limit - sent,
      );
    } catch (error) {
      console.error(`Failed to query ${reminder.type} reminders`, error);
      degraded = true;
      continue;
    }

    for (const row of rows) {
      if (!row.email || !row.trialEndsAt) {
        continue;
      }

      // Claim the send first: a duplicate email is worse than a missed one,
      // and the unique constraint makes a concurrent run a no-op.
      const [claimed] = await db
        .insert(billingReminderSentTable)
        .values({
          userId: row.userId,
          workspaceId: row.workspaceId,
          reminderType: reminder.type,
          trialEndsAt: row.trialEndsAt,
        })
        .onConflictDoNothing({
          target: [
            billingReminderSentTable.userId,
            billingReminderSentTable.reminderType,
          ],
        })
        .returning();

      if (!claimed) {
        continue;
      }

      const daysLeft = Math.max(
        0,
        Math.ceil((row.trialEndsAt.getTime() - now.getTime()) / DAY_MS),
      );

      if (sent > 0) {
        await sleep(SEND_SPACING_MS);
      }

      await sendTrialReminderEmail(
        row.email,
        reminder.subject(row.workspaceName),
        {
          workspaceName: row.workspaceName,
          daysLeft,
          billingUrl: `${clientUrl()}/dashboard/settings/workspace/billing`,
          trialDays: trialDays(),
          endsAt: row.trialEndsAt,
        },
      );
      sent++;
    }
  }

  if (sent > 0) {
    console.log(`Sent ${sent} trial reminder email(s)`);
  }

  return { degraded };
}