stack-auth · BilalG1 · Jan 8, 2026 · Jan 8, 2026 · Jan 8, 2026 · Jan 8, 2026
diff --git a/apps/backend/prisma/migrations/20260107160000_add_transaction_tables/migration.sql b/apps/backend/prisma/migrations/20260107160000_add_transaction_tables/migration.sql
@@ -0,0 +1,80 @@
+-- CreateEnum
+CREATE TYPE "SubscriptionChangeType" AS ENUM ('PRICE_CHANGE', 'QUANTITY_CHANGE', 'PERIOD_CHANGE', 'STATUS_CHANGE', 'METADATA_CHANGE', 'OTHER');
+
+-- CreateTable
+CREATE TABLE "StripeRefund" (
+    "id" UUID NOT NULL,
+    "tenancyId" UUID NOT NULL,
+    "stripeRefundId" TEXT NOT NULL,
+    "stripePaymentIntentId" TEXT,
+    "subscriptionId" UUID,
+    "oneTimePurchaseId" UUID,
+    "customerId" TEXT NOT NULL,
+    "customerType" "CustomerType" NOT NULL,
+    "amountCents" INTEGER NOT NULL,
+    "currency" TEXT NOT NULL,
+    "reason" TEXT,
+    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "updatedAt" TIMESTAMP(3) NOT NULL,
+
+    CONSTRAINT "StripeRefund_pkey" PRIMARY KEY ("tenancyId","id")
+);
+
+-- CreateTable
+CREATE TABLE "ProductChange" (
+    "id" UUID NOT NULL,
+    "tenancyId" UUID NOT NULL,
+    "customerId" TEXT NOT NULL,
+    "customerType" "CustomerType" NOT NULL,
+    "subscriptionId" UUID,
+    "oldProductId" TEXT,
+    "oldPriceId" TEXT,
+    "oldProduct" JSONB,
+    "newProductId" TEXT,
+    "newPriceId" TEXT,
+    "newProduct" JSONB,
+    "oldQuantity" INTEGER NOT NULL DEFAULT 1,
+    "newQuantity" INTEGER NOT NULL DEFAULT 1,
+    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "updatedAt" TIMESTAMP(3) NOT NULL,
+
+    CONSTRAINT "ProductChange_pkey" PRIMARY KEY ("tenancyId","id")
+);
+
+-- CreateTable
+CREATE TABLE "SubscriptionChange" (
+    "id" UUID NOT NULL,
+    "tenancyId" UUID NOT NULL,
+    "subscriptionId" UUID NOT NULL,
+    "customerId" TEXT NOT NULL,
+    "customerType" "CustomerType" NOT NULL,
+    "changeType" "SubscriptionChangeType" NOT NULL,
+    "oldValue" JSONB,
+    "newValue" JSONB,
+    "description" TEXT,
+    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "updatedAt" TIMESTAMP(3) NOT NULL,
+
+    CONSTRAINT "SubscriptionChange_pkey" PRIMARY KEY ("tenancyId","id")
+);
+
+-- CreateIndex
+CREATE UNIQUE INDEX "StripeRefund_tenancyId_stripeRefundId_key" ON "StripeRefund"("tenancyId", "stripeRefundId");
+
+-- CreateIndex
+CREATE INDEX "StripeRefund_tenancyId_subscriptionId_idx" ON "StripeRefund"("tenancyId", "subscriptionId");
+
+-- CreateIndex
+CREATE INDEX "StripeRefund_tenancyId_oneTimePurchaseId_idx" ON "StripeRefund"("tenancyId", "oneTimePurchaseId");
+
+-- CreateIndex
+CREATE INDEX "ProductChange_tenancyId_subscriptionId_idx" ON "ProductChange"("tenancyId", "subscriptionId");
+
+-- CreateIndex
+CREATE INDEX "ProductChange_tenancyId_customerId_idx" ON "ProductChange"("tenancyId", "customerId");
+
+-- CreateIndex
+CREATE INDEX "SubscriptionChange_tenancyId_subscriptionId_idx" ON "SubscriptionChange"("tenancyId", "subscriptionId");
+
+-- CreateIndex
+CREATE INDEX "SubscriptionChange_tenancyId_customerId_idx" ON "SubscriptionChange"("tenancyId", "customerId");
diff --git a/apps/backend/prisma/schema.prisma b/apps/backend/prisma/schema.prisma
@@ -1037,3 +1037,101 @@ model SubscriptionInvoice {
   @@id([tenancyId, id])
   @@unique([tenancyId, stripeInvoiceId])
 }
+
+// Stores Stripe refunds synced from webhooks
+model StripeRefund {
+  id        String @default(uuid()) @db.Uuid
+  tenancyId String @db.Uuid
+
+  stripeRefundId        String
+  stripePaymentIntentId String?
+
+  // The source of the refund - exactly one of subscriptionId or oneTimePurchaseId must be set
+  subscriptionId    String? @db.Uuid
+  oneTimePurchaseId String? @db.Uuid
+
+  // Customer info (denormalized for easier querying)
+  customerId   String
+  customerType CustomerType
+
+  // Refund details
+  amountCents Int
+  currency    String // ISO 4217 currency code (e.g., "USD")
+  reason      String? // Optional reason from Stripe
+
+  createdAt DateTime @default(now())
+  updatedAt DateTime @updatedAt
+
+  @@id([tenancyId, id])
+  @@unique([tenancyId, stripeRefundId])
+  @@index([tenancyId, subscriptionId])
+  @@index([tenancyId, oneTimePurchaseId])
+}
+
+// Stores product changes (upgrades, downgrades, switches between products)
+model ProductChange {
+  id        String @default(uuid()) @db.Uuid
+  tenancyId String @db.Uuid
+
+  customerId   String
+  customerType CustomerType
+
+  // The subscription that was changed (if applicable)
+  subscriptionId String? @db.Uuid
+
+  // Old product details
+  oldProductId String?
+  oldPriceId   String?
+  oldProduct   Json? // Snapshot of old product config
+
+  // New product details
+  newProductId String?
+  newPriceId   String?
+  newProduct   Json? // Snapshot of new product config
+
+  // Quantity change (if any)
+  oldQuantity Int @default(1)
+  newQuantity Int @default(1)
+
+  createdAt DateTime @default(now())
+  updatedAt DateTime @updatedAt
+
+  @@id([tenancyId, id])
+  @@index([tenancyId, subscriptionId])
+  @@index([tenancyId, customerId])
+}
+
+enum SubscriptionChangeType {
+  PRICE_CHANGE
+  QUANTITY_CHANGE
+  PERIOD_CHANGE
+  STATUS_CHANGE
+  METADATA_CHANGE
+  OTHER
+}
+
+// Stores subscription changes that we track ourselves (Stripe may not store all changes)
+model SubscriptionChange {
+  id        String @default(uuid()) @db.Uuid
+  tenancyId String @db.Uuid
+
+  subscriptionId String       @db.Uuid
+  customerId     String
+  customerType   CustomerType
+
+  changeType SubscriptionChangeType
+
+  // Store old and new values as JSON for flexibility
+  oldValue Json?
+  newValue Json?
+
+  // Optional description of the change
+  description String?
+
+  createdAt DateTime @default(now())
+  updatedAt DateTime @updatedAt
+
+  @@id([tenancyId, id])
+  @@index([tenancyId, subscriptionId])
+  @@index([tenancyId, customerId])
+}
diff --git a/apps/backend/src/app/api/latest/integrations/stripe/webhooks/route.tsx b/apps/backend/src/app/api/latest/integrations/stripe/webhooks/route.tsx
@@ -1,4 +1,4 @@
-import { getStackStripe, getStripeForAccount, handleStripeInvoicePaid, syncStripeSubscriptions } from "@/lib/stripe";
+import { getStackStripe, getStripeForAccount, handleStripeInvoicePaid, handleStripeRefund, syncStripeSubscriptions } from "@/lib/stripe";
 import { getTenancy } from "@/lib/tenancies";
 import { getPrismaClientForTenancy } from "@/prisma-client";
 import { createSmartRouteHandler } from "@/route-handlers/smart-route-handler";
@@ -30,10 +30,19 @@ const subscriptionChangedEvents = [
   "payment_intent.canceled",
 ] as const satisfies Stripe.Event.Type[];
 
+const refundEvents = [
+  "charge.refunded",
+  "charge.refund.updated",
+] as const satisfies Stripe.Event.Type[];
+
 const isSubscriptionChangedEvent = (event: Stripe.Event): event is Stripe.Event & { type: (typeof subscriptionChangedEvents)[number] } => {
   return subscriptionChangedEvents.includes(event.type as any);
 };
 
+const isRefundEvent = (event: Stripe.Event): event is Stripe.Event & { type: (typeof refundEvents)[number], data: { object: Stripe.Charge } } => {
+  return refundEvents.includes(event.type as any);
+};
+
 async function processStripeWebhookEvent(event: Stripe.Event): Promise<void> {
   const mockData = (event.data.object as any).stack_stripe_mock_data;
   if (event.type === "payment_intent.succeeded" && event.data.object.metadata.purchaseKind === "ONE_TIME") {
@@ -104,6 +113,16 @@ async function processStripeWebhookEvent(event: Stripe.Event): Promise<void> {
       await handleStripeInvoicePaid(stripe, accountId, event.data.object);
     }
   }
+
+  // Handle refund events
+  if (isRefundEvent(event)) {
+    const accountId = event.account;
+    if (!accountId) {
+      throw new StackAssertionError("Stripe webhook account id missing for refund event", { event });
+    }
+    const stripe = await getStripeForAccount({ accountId }, mockData);
+    await handleStripeRefund(stripe, accountId, event.data.object);
+  }
 }
 
 export const POST = createSmartRouteHandler({