Subscription Churn Prevention Pipeline with RevenueCat Webhooks and Antigravity AI

// src/webhook.ts
import { createClient } from "@supabase/supabase-js";
 
interface RevenueCatWebhookEvent {
  event: {
    id: string;
    type: string;
    app_user_id: string;
    product_id: string;
    period_type: string;
    purchased_at_ms: number;
    expiration_at_ms: number;
    environment: "SANDBOX" | "PRODUCTION";
    cancel_reason?: string;
  };
  api_version: string;
}
 
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    if (request.method !== "POST") {
      return new Response("Method Not Allowed", { status: 405 });
    }
 
    // Validate the Authorization header from RevenueCat
    const authHeader = request.headers.get("Authorization");
    if (authHeader !== `Bearer ${env.REVENUECAT_WEBHOOK_SECRET}`) {
      return new Response("Unauthorized", { status: 401 });
    }
 
    const body = await request.json() as RevenueCatWebhookEvent;
    const event = body.event;
 
    // Drop sandbox events in production
    if (event.environment === "SANDBOX" && env.ENVIRONMENT === "production") {
      return new Response("OK (sandbox ignored)", { status: 200 });
    }
 
    const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_SERVICE_ROLE_KEY);
 
    // Idempotency: rely on the DB unique constraint on event_id
    const { error: insertError } = await supabase.from("subscription_events").insert({
      event_id: event.id,
      user_id: event.app_user_id,
      event_type: event.type,
      product_id: event.product_id,
      cancel_reason: event.cancel_reason ?? null,
      expired_at: event.expiration_at_ms
        ? new Date(event.expiration_at_ms).toISOString()
        : null,
      created_at: new Date().toISOString(),
    });
 
    if (insertError && insertError.code === "23505") {
      // Duplicate event_id: already processed, return 200
      return new Response("OK (duplicate)", { status: 200 });
    }
 
    // Push churn risk events to a queue for downstream scoring
    const CHURN_RISK_EVENTS = ["CANCELLATION", "BILLING_ISSUE", "PRODUCT_CHANGE"];
 
    if (CHURN_RISK_EVENTS.includes(event.type)) {
      await env.CHURN_RISK_QUEUE.send({
        user_id: event.app_user_id,
        event_type: event.type,
        cancel_reason: event.cancel_reason,
        expires_at: event.expiration_at_ms,
      });
    }
 
    return new Response("OK", { status: 200 });
  },
};

// src/churn-scorer.ts
import { createClient } from "@supabase/supabase-js";
 
interface ChurnInput {
  user_id: string;
  event_type: string;
  cancel_reason?: string;
  expires_at?: number;
}
 
interface ChurnScore {
  score: number;          // 0.0 - 1.0 (higher = more at risk)
  risk_level: "LOW" | "MEDIUM" | "HIGH";
  win_back_strategy: string;
  recommended_action: string;
}
 
async function fetchUserBehavior(
  supabase: ReturnType<typeof createClient>,
  user_id: string,
): Promise<object> {
  // Past event history
  const { data: events } = await supabase
    .from("subscription_events")
    .select("event_type, created_at")
    .eq("user_id", user_id)
    .order("created_at", { ascending: false })
    .limit(50);
 
  // Recent app usage (populate this from your own analytics)
  const { data: usage } = await supabase
    .from("app_usage")
    .select("session_count, last_active_at, feature_usage")
    .eq("user_id", user_id)
    .single();
 
  return {
    event_history: events ?? [],
    session_count_last_30d: usage?.session_count ?? 0,
    last_active_at: usage?.last_active_at,
    feature_usage: usage?.feature_usage ?? {},
  };
}
 
export async function calculateChurnScore(
  input: ChurnInput,
  env: Env,
): Promise<ChurnScore> {
  const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_SERVICE_ROLE_KEY);
  const behavior = await fetchUserBehavior(supabase, input.user_id);
 
  const prompt = `
You are a churn analyst for subscription apps. Compute a churn risk score from the user behavior data below.
 
[Trigger Event]
- Event type: ${input.event_type}
- Cancel reason: ${input.cancel_reason ?? "unknown"}
- Subscription expires: ${input.expires_at ? new Date(input.expires_at).toISOString() : "unknown"}
 
[User Behavior]
${JSON.stringify(behavior, null, 2)}
 
Respond in this JSON shape:
{
  "score": 0.0 to 1.0 (higher = greater churn risk),
  "risk_level": "LOW" | "MEDIUM" | "HIGH",
  "win_back_strategy": "Recommended win-back strategy in one sentence",
  "recommended_action": "Concrete action: discount, perks, contact timing, etc."
}
 
Scoring guidance:
- Cancel reason "too expensive" → price sensitive, discount offers work
- Low last-30d session count → user didn't get value, feature highlights work
- Previously cancelled and resubscribed → high win-back success rate
- Billing failure → prioritize payment-method update prompt
`;
 
  const response = await fetch(
    "https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent",
    {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        "X-Goog-Api-Key": env.GEMINI_API_KEY,
      },
      body: JSON.stringify({
        contents: [{ role: "user", parts: [{ text: prompt }] }],
      }),
    },
  );
 
  const data = await response.json() as { candidates: Array<{ content: { parts: Array<{ text: string }> } }> };
  const text = data.candidates[0].content.parts[0].text;
  const start = text.indexOf("{");
  const end = text.lastIndexOf("}") + 1;
 
  return JSON.parse(text.slice(start, end)) as ChurnScore;
}

// src/winback-emailer.ts
import { Resend } from "resend";
 
interface WinbackContext {
  user_id: string;
  email: string;
  name: string;
  app_name: string;
  support_url: string;
  resubscribe_url: string;
  churn_score: ChurnScore;
}
 
const EMAIL_SEQUENCES: Record<string, Array<{ delay_hours: number; template: string }>> = {
  // HIGH: immediate + discount offer within 72 hours + final attempt
  HIGH: [
    { delay_hours: 0, template: "high_risk_immediate" },
    { delay_hours: 48, template: "high_risk_discount_offer" },
    { delay_hours: 168, template: "final_winback" },  // 7 days later
  ],
  // MEDIUM: 24h value highlight + 5d tips
  MEDIUM: [
    { delay_hours: 24, template: "medium_risk_value_highlight" },
    { delay_hours: 120, template: "medium_risk_tips" },
  ],
  // LOW: soft followup one week later
  LOW: [
    { delay_hours: 168, template: "low_risk_soft_followup" },
  ],
};
 
const EMAIL_TEMPLATES = {
  high_risk_immediate: (ctx: WinbackContext) => ({
    subject: `Thank you for using ${ctx.app_name}`,
    html: `
      <p>Hi ${ctx.name ?? "there"},</p>
      <p>We've processed your cancellation. If pricing was the concern, we'd love to hear from you — there may be a plan that fits your situation.</p>
      <p><a href="${ctx.support_url}">Contact support</a></p>
    `,
  }),
  high_risk_discount_offer: (ctx: WinbackContext) => ({
    subject: `A special offer to continue with ${ctx.app_name}`,
    html: `
      <p>Hi ${ctx.name ?? "there"},</p>
      <p>If you're still thinking about coming back, here's <strong>50% off your first month</strong>.</p>
      <p><a href="${ctx.resubscribe_url}?discount=50&user=${ctx.user_id}">Resume at the special price</a></p>
      <p>This offer is valid for the next 72 hours.</p>
    `,
  }),
};
 
export async function executeWinbackSequence(
  context: WinbackContext,
  env: Env,
): Promise<void> {
  const resend = new Resend(env.RESEND_API_KEY);
  const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_SERVICE_ROLE_KEY);
  const sequence = EMAIL_SEQUENCES[context.churn_score.risk_level] ?? [];
 
  // Guard: skip if already in an active win-back sequence in the last 30 days
  const { count } = await supabase
    .from("winback_schedules")
    .select("*", { count: "exact", head: true })
    .eq("user_id", context.user_id)
    .gte("created_at", new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString());
 
  if ((count ?? 0) > 0) {
    console.log(`Skipping winback for ${context.user_id} — sequence already active`);
    return;
  }
 
  for (const step of sequence) {
    if (step.delay_hours === 0) {
      const template = EMAIL_TEMPLATES[step.template as keyof typeof EMAIL_TEMPLATES];
      if (template) {
        const { subject, html } = template(context);
        await resend.emails.send({
          from: `${context.app_name} <noreply@${env.EMAIL_DOMAIN}>`,
          to: context.email,
          subject,
          html,
        });
      }
    } else {
      await env.WINBACK_EMAIL_QUEUE.send(
        {
          user_id: context.user_id,
          email: context.email,
          name: context.name,
          template: step.template,
          app_name: context.app_name,
        },
        { delaySeconds: step.delay_hours * 3600 },
      );
    }
 
    await supabase.from("winback_schedules").insert({
      user_id: context.user_id,
      template: step.template,
      scheduled_at: new Date(Date.now() + step.delay_hours * 3600 * 1000).toISOString(),
      status: step.delay_hours === 0 ? "sent" : "scheduled",
    });
  }
}

// src/winback-analytics.ts
async function calculateWinbackEffectiveness(
  supabase: ReturnType<typeof createClient>,
  days: number = 30,
): Promise<{
  total_churn_events: number;
  winback_sent: number;
  resubscribed: number;
  winback_rate: number;
  revenue_recovered_usd: number;
}> {
  const since = new Date(Date.now() - days * 24 * 60 * 60 * 1000).toISOString();
 
  const { count: total_churn } = await supabase
    .from("subscription_events")
    .select("*", { count: "exact", head: true })
    .in("event_type", ["CANCELLATION", "EXPIRATION"])
    .gte("created_at", since);
 
  const { count: winback_sent } = await supabase
    .from("winback_schedules")
    .select("*", { count: "exact", head: true })
    .eq("status", "sent")
    .gte("created_at", since);
 
  // Custom RPC that joins winback_schedules and subscription_events
  const { data } = await supabase.rpc("count_winback_resubscriptions", {
    since_date: since,
  });
 
  const resubscribed = data?.[0]?.count ?? 0;
  const winback_rate = (winback_sent ?? 0) > 0 ? resubscribed / (winback_sent ?? 1) : 0;
 
  return {
    total_churn_events: total_churn ?? 0,
    winback_sent: winback_sent ?? 0,
    resubscribed,
    winback_rate,
    revenue_recovered_usd: resubscribed * 4.99,  // Replace with your actual avg plan price
  };
}

if (event.environment === "SANDBOX" && env.ENVIRONMENT === "production") {
  return new Response("OK (sandbox ignored)", { status: 200 });
}

ALTER TABLE subscription_events
  ADD CONSTRAINT subscription_events_event_id_key UNIQUE (event_id);