Subscription Dunning Backend: Retry Failed Payments and Suspend Access

CREATE TABLE processed_events (
    event_id    TEXT PRIMARY KEY,
    received_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE dunning_cases (
    invoice_id      TEXT PRIMARY KEY,
    customer_id     TEXT NOT NULL,
    amount_cents    BIGINT NOT NULL,
    currency        TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'open', -- open, recovered, suspended
    attempt         INT NOT NULL DEFAULT 0,       -- retries made so far
    opened_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    next_attempt_at TIMESTAMPTZ,
    closed_at       TIMESTAMPTZ
);
CREATE INDEX dunning_cases_due ON dunning_cases (next_attempt_at) WHERE status = 'open';
CREATE INDEX dunning_cases_customer ON dunning_cases (customer_id) WHERE status = 'suspended';

CREATE TABLE sent_notices (
    invoice_id TEXT NOT NULL,
    kind       TEXT NOT NULL,
    attempt    INT NOT NULL,
    sent_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (invoice_id, kind, attempt)
);

// dunning/dunning.ts
import { api } from "encore.dev/api";
import { secret } from "encore.dev/config";
import { CronJob } from "encore.dev/cron";
import { Subscription, Topic } from "encore.dev/pubsub";
import { SQLDatabase } from "encore.dev/storage/sqldb";
import log from "encore.dev/log";
import { createHmac, timingSafeEqual } from "node:crypto";

// RDS or Cloud SQL in your cloud account, a local Postgres with `encore run`.
const db = new SQLDatabase("dunning", { migrations: "./migrations" });

// Set per environment with `encore secret set`, never stored in the repo.
const webhookSecret = secret("PspWebhookSecret");
const pspApiKey = secret("PspApiKey");

// Days after the first failure on which the charge is retried.
// Access is suspended when the last retry fails.
const RETRY_DAYS = [1, 3, 5, 7];

interface PspEvent {
  id: string;
  type: string; // "payment.failed", "payment.succeeded", ...
  invoiceId: string;
  customerId: string;
  amountCents: number;
  currency: string;
}

export interface DunningNotice {
  kind: "payment_failed" | "retry_failed" | "recovered" | "suspended";
  invoiceId: string;
  customerId: string;
  attempt: number;
}

export const notices = new Topic<DunningNotice>("dunning-notices", {
  deliveryGuarantee: "at-least-once",
});

// The payment provider calls this for every payment event.
export const pspWebhook = api.raw(
  { expose: true, method: "POST", path: "/webhooks/psp" },
  async (req, resp) => {
    const chunks: Buffer[] = [];
    for await (const chunk of req) chunks.push(chunk as Buffer);
    const body = Buffer.concat(chunks);

    const got = Buffer.from(String(req.headers["x-psp-signature"] ?? ""), "hex");
    const want = createHmac("sha256", webhookSecret()).update(body).digest();
    if (got.length !== want.length || !timingSafeEqual(got, want)) {
      resp.writeHead(401).end("invalid signature");
      return;
    }

    let event: PspEvent;
    try {
      event = JSON.parse(body.toString("utf8"));
    } catch {
      resp.writeHead(400).end("invalid json");
      return;
    }
    await handleEvent(event);
    resp.writeHead(204).end();
  },
);

async function handleEvent(ev: PspEvent) {
  await using tx = await db.begin();

  // Providers deliver webhooks at least once: record each event ID once.
  const fresh = await tx.queryRow`
    INSERT INTO processed_events (event_id) VALUES (${ev.id})
    ON CONFLICT DO NOTHING RETURNING event_id`;
  if (!fresh) return;

  let notice: DunningNotice | null = null;
  if (ev.type === "payment.failed") {
    const opened = await tx.queryRow`
      INSERT INTO dunning_cases (invoice_id, customer_id, amount_cents, currency, next_attempt_at)
      VALUES (${ev.invoiceId}, ${ev.customerId}, ${ev.amountCents}, ${ev.currency},
              now() + make_interval(days => ${RETRY_DAYS[0]}::int))
      ON CONFLICT (invoice_id) DO NOTHING
      RETURNING invoice_id`;
    if (opened) notice = { kind: "payment_failed", invoiceId: ev.invoiceId, customerId: ev.customerId, attempt: 0 };
  } else if (ev.type === "payment.succeeded") {
    // The customer updated their card and paid, or a retry went through.
    const closed = await tx.queryRow<{ attempt: number }>`
      UPDATE dunning_cases
      SET status = 'recovered', closed_at = now(), next_attempt_at = NULL
      WHERE invoice_id = ${ev.invoiceId} AND status IN ('open', 'suspended')
      RETURNING attempt`;
    if (closed) notice = { kind: "recovered", invoiceId: ev.invoiceId, customerId: ev.customerId, attempt: closed.attempt };
  }
  await tx.commit();
  if (notice) await notices.publish(notice);
}

interface DueCase {
  invoice_id: string;
  customer_id: string;
  attempt: number;
}

// Called every hour by the Encore platform, and by hand when testing locally.
export const retryDue = api({}, async (): Promise<{ attempted: number }> => {
  let attempted = 0;
  while (attempted < 500) {
    await using tx = await db.begin();
    // SKIP LOCKED lets an overlapping run move on to other cases
    // instead of charging the same invoice twice.
    const c = await tx.queryRow<DueCase>`
      SELECT invoice_id, customer_id, attempt FROM dunning_cases
      WHERE status = 'open' AND next_attempt_at <= now()
      ORDER BY next_attempt_at
      LIMIT 1
      FOR UPDATE SKIP LOCKED`;
    if (!c) break;

    const attempt = c.attempt + 1;
    const paid = await chargeInvoice(c.invoice_id, attempt);
    let kind: DunningNotice["kind"];
    if (paid) {
      kind = "recovered";
      await tx.exec`
        UPDATE dunning_cases SET status = 'recovered', attempt = ${attempt},
          closed_at = now(), next_attempt_at = NULL
        WHERE invoice_id = ${c.invoice_id}`;
    } else if (attempt >= RETRY_DAYS.length) {
      kind = "suspended";
      await tx.exec`
        UPDATE dunning_cases SET status = 'suspended', attempt = ${attempt},
          closed_at = now(), next_attempt_at = NULL
        WHERE invoice_id = ${c.invoice_id}`;
    } else {
      kind = "retry_failed";
      await tx.exec`
        UPDATE dunning_cases SET attempt = ${attempt},
          next_attempt_at = opened_at + make_interval(days => ${RETRY_DAYS[attempt]}::int)
        WHERE invoice_id = ${c.invoice_id}`;
    }
    await tx.commit();
    await notices.publish({ kind, invoiceId: c.invoice_id, customerId: c.customer_id, attempt });
    attempted++;
  }
  return { attempted };
});

const _ = new CronJob("retry-failed-payments", {
  title: "Retry failed subscription payments",
  every: "1h",
  endpoint: retryDue,
});

// Returns true when the provider charged the card, false on a decline,
// and throws on provider errors so the case is retried on the next run.
async function chargeInvoice(invoiceId: string, attempt: number): Promise<boolean> {
  const resp = await fetch(`https://psp.example.com/v1/invoices/${invoiceId}/pay`, {
    method: "POST",
    headers: {
      Authorization: `Bearer ${pspApiKey()}`,
      // The same attempt never charges twice, even if this run crashes and repeats it.
      "Idempotency-Key": `${invoiceId}-retry-${attempt}`,
    },
  });
  if (resp.ok) return true;
  if (resp.status === 402) return false; // declined
  throw new Error(`payment provider returned ${resp.status}`);
}

// Other services ask this before serving paid features.
export const access = api(
  { expose: false, method: "GET", path: "/customers/:customerId/access" },
  async ({ customerId }: { customerId: string }): Promise<{ active: boolean }> => {
    const row = await db.queryRow`
      SELECT 1 FROM dunning_cases
      WHERE customer_id = ${customerId} AND status = 'suspended'
      LIMIT 1`;
    return { active: !row };
  },
);

// Sends each notice once, even when Pub/Sub delivers it twice.
const __ = new Subscription(notices, "email-customer", {
  handler: async (n) => {
    const first = await db.queryRow`
      INSERT INTO sent_notices (invoice_id, kind, attempt)
      VALUES (${n.invoiceId}, ${n.kind}, ${n.attempt})
      ON CONFLICT DO NOTHING RETURNING invoice_id`;
    if (!first) return;
    log.info("dunning email", { kind: n.kind, customer: n.customerId, invoice: n.invoiceId });
    // Call your email provider here; "payment_failed" links to the card update page.
  },
});