feat(GRO-597): Stripe payment backend — schema, service, API, webhooks

Consolidates GRO-605, GRO-606, GRO-608 into a single clean PR:
- GRO-605: Stripe SDK integration + payment service
- GRO-606: Payment API endpoints (pay invoice, payment methods, refunds)
- GRO-608: Stripe webhook handler

Migration consolidation:
- Single 0026_stripe_payment.sql migration adds stripeCustomerId to clients
  and stripe_payment_intent_id, stripe_refund_id, payment_failure_reason to invoices
- Removed duplicate 0027_stripe_identifiers.sql

Co-Authored-By: Paperclip <noreply@paperclip.ing>

apps/api/package.json

@@ -23,6 +23,7 @@
     "node-cron": "^3.0.3",
     "nodemailer": "^6.9.16",
     "stripe": "^22.0.0",
+
     "zod": "^4.3.6"
   },
   "devDependencies": {

apps/api/src/routes/invoices.ts

@@ -13,8 +13,9 @@ import {
   clients,
   sql,
 } from "@groombook/db";
+import type { AppEnv } from "../middleware/rbac.js";
 
-export const invoicesRouter = new Hono();
+export const invoicesRouter = new Hono<AppEnv>();
 
 const createInvoiceSchema = z.object({
   appointmentId: z.string().uuid().optional(),
@@ -338,3 +339,41 @@ invoicesRouter.patch(
     return c.json({ ...updated, lineItems });
   }
 );
+
+// ─── Refund ───────────────────────────────────────────────────────────────────
+
+import { processRefund } from "../services/payment.js";
+
+const refundSchema = z.object({
+  amountCents: z.number().int().nonnegative().optional(),
+});
+
+invoicesRouter.post(
+  "/:id/refund",
+  zValidator("json", refundSchema),
+  async (c) => {
+    const db = getDb();
+    const staff = c.get("staff");
+    if (!staff) return c.json({ error: "Forbidden" }, 403);
+    if (staff.role !== "manager" && !staff.isSuperUser) {
+      return c.json({ error: "Manager role required" }, 403);
+    }
+
+    const id = c.req.param("id");
+    const body = c.req.valid("json");
+
+    const [invoice] = await db.select().from(invoices).where(eq(invoices.id, id));
+    if (!invoice) return c.json({ error: "Not found" }, 404);
+    if (invoice.status !== "paid") {
+      return c.json({ error: "Refund only allowed on paid invoices" }, 422);
+    }
+    if (!invoice.stripePaymentIntentId) {
+      return c.json({ error: "No Stripe payment intent found for this invoice" }, 422);
+    }
+
+    const result = await processRefund(id, body.amountCents);
+    if (!result) return c.json({ error: "Refund failed" }, 500);
+
+    return c.json({ refundId: result.refundId });
+  }
+);

apps/api/src/routes/portal.ts

@@ -448,6 +448,145 @@ portalRouter.delete("/waitlist/:id", async (c) => {
   return c.json({ ok: true });
 });
 
+// ─── Payment routes ───────────────────────────────────────────────────────────
+
+import {
+  createPaymentIntent,
+  listPaymentMethods,
+  detachPaymentMethod,
+  createSetupIntent,
+  getOrCreateStripeCustomer,
+} from "../services/payment.js";
+
+const payInvoiceSchema = z.object({
+  invoiceId: z.string().uuid(),
+});
+
+portalRouter.post(
+  "/invoices/:id/pay",
+  zValidator("json", payInvoiceSchema),
+  async (c) => {
+    const db = getDb();
+    const invoiceId = c.req.param("id");
+    const sessionId = c.req.header("X-Impersonation-Session-Id");
+    const clientId = await getClientIdFromSession(sessionId);
+    if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+
+    const [invoice] = await db
+      .select()
+      .from(invoices)
+      .where(eq(invoices.id, invoiceId))
+      .limit(1);
+
+    if (!invoice) return c.json({ error: "Not found" }, 404);
+    if (invoice.clientId !== clientId) return c.json({ error: "Forbidden" }, 403);
+    if (invoice.status === "draft" || invoice.status === "void") {
+      return c.json({ error: "Cannot pay a draft or void invoice" }, 422);
+    }
+    if (invoice.status === "paid") {
+      return c.json({ error: "Invoice is already paid" }, 422);
+    }
+
+    const stripePublishableKey = process.env.STRIPE_PUBLISHABLE_KEY ?? "";
+    const result = await createPaymentIntent(invoiceId, clientId);
+    if (!result) return c.json({ error: "Payment service unavailable" }, 503);
+
+    return c.json({ clientSecret: result.clientSecret, publishableKey: stripePublishableKey });
+  }
+);
+
+const payMultipleSchema = z.object({
+  invoiceIds: z.array(z.string().uuid()).min(1),
+});
+
+portalRouter.post(
+  "/invoices/pay-multiple",
+  zValidator("json", payMultipleSchema),
+  async (c) => {
+    const db = getDb();
+    const body = c.req.valid("json");
+    const sessionId = c.req.header("X-Impersonation-Session-Id");
+    const clientId = await getClientIdFromSession(sessionId);
+    if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+
+    const invoiceRows = await db
+      .select()
+      .from(invoices)
+      .where(inArray(invoices.id, body.invoiceIds));
+
+    if (invoiceRows.length !== body.invoiceIds.length) {
+      return c.json({ error: "One or more invoices not found" }, 404);
+    }
+
+    for (const inv of invoiceRows) {
+      if (inv.clientId !== clientId) return c.json({ error: "Forbidden" }, 403);
+      if (inv.status === "draft" || inv.status === "void") {
+        return c.json({ error: `Invoice ${inv.id} cannot be paid (draft or void)` }, 422);
+      }
+      if (inv.status === "paid") {
+        return c.json({ error: `Invoice ${inv.id} is already paid` }, 422);
+      }
+    }
+
+    const firstInvoice = invoiceRows[0];
+    if (!firstInvoice) return c.json({ error: "No invoices found" }, 400);
+    const allSameClient = invoiceRows.every(inv => inv.clientId === firstInvoice.clientId);
+    if (!allSameClient) {
+      return c.json({ error: "All invoices must belong to the same client" }, 422);
+    }
+
+    const stripePublishableKey = process.env.STRIPE_PUBLISHABLE_KEY ?? "";
+    const result = await createPaymentIntent(body.invoiceIds, clientId);
+    if (!result) return c.json({ error: "Payment service unavailable" }, 503);
+
+    return c.json({ clientSecret: result.clientSecret, publishableKey: stripePublishableKey });
+  }
+);
+
+portalRouter.get("/payment-methods", async (c) => {
+  const sessionId = c.req.header("X-Impersonation-Session-Id");
+  const clientId = await getClientIdFromSession(sessionId);
+  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+
+  const methods = await listPaymentMethods(clientId);
+  if (methods === null) return c.json({ error: "Payment service unavailable" }, 503);
+  return c.json(methods);
+});
+
+portalRouter.post("/payment-methods", async (c) => {
+  const sessionId = c.req.header("X-Impersonation-Session-Id");
+  const clientId = await getClientIdFromSession(sessionId);
+  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+
+  const stripePublishableKey = process.env.STRIPE_PUBLISHABLE_KEY ?? "";
+  const customerId = await getOrCreateStripeCustomer(clientId);
+  if (!customerId) return c.json({ error: "Could not create customer" }, 500);
+
+  const result = await createSetupIntent(customerId);
+  if (!result) return c.json({ error: "Payment service unavailable" }, 503);
+
+  return c.json({ clientSecret: result.clientSecret, publishableKey: stripePublishableKey });
+});
+
+portalRouter.delete("/payment-methods/:id", async (c) => {
+  const sessionId = c.req.header("X-Impersonation-Session-Id");
+  const clientId = await getClientIdFromSession(sessionId);
+  if (!clientId) return c.json({ error: "Unauthorized" }, 401);
+
+  const paymentMethodId = c.req.param("id");
+  const ok = await detachPaymentMethod(paymentMethodId);
+  if (!ok) return c.json({ error: "Failed to detach payment method" }, 500);
+  return c.json({ ok: true });
+});
+
+// ─── Config endpoint ─────────────────────────────────────────────────────────
+
+portalRouter.get("/config", (c) => {
+  return c.json({
+    stripePublishableKey: process.env.STRIPE_PUBLISHABLE_KEY ?? "",
+  });
+});
+
 // ─── Dev-mode session creation ──────────────────────────────────────────────
 // Allows the dev login selector to vend an impersonation session for a client
 // without requiring manager auth. Only available when AUTH_DISABLED=true.

apps/api/src/services/payment.ts

@@ -0,0 +1,164 @@
+import Stripe from "stripe";
+import { getDb, clients, eq, invoices } from "@groombook/db";
+
+let _stripe: Stripe | null | undefined;
+
+function getStripeClient(): Stripe | null {
+  if (_stripe === undefined) {
+    const secretKey = process.env.STRIPE_SECRET_KEY;
+    if (!secretKey) return null;
+    _stripe = new Stripe(secretKey);
+  }
+  return _stripe;
+}
+
+export async function getOrCreateStripeCustomer(clientId: string): Promise<string | null> {
+  const stripe = getStripeClient();
+  if (!stripe) return null;
+
+  const db = getDb();
+  const [client] = await db.select().from(clients).where(eq(clients.id, clientId)).limit(1);
+  if (!client) return null;
+
+  if (client.stripeCustomerId) return client.stripeCustomerId;
+
+  const customer = await stripe.customers.create({
+    metadata: { groombook_client_id: clientId },
+  });
+
+  await db
+    .update(clients)
+    .set({ stripeCustomerId: customer.id, updatedAt: new Date() })
+    .where(eq(clients.id, clientId));
+
+  return customer.id;
+}
+
+export async function createPaymentIntent(
+  invoiceIdOrIds: string | string[],
+  clientId: string
+): Promise<{ clientSecret: string; paymentIntentId: string } | null> {
+  const stripe = getStripeClient();
+  if (!stripe) return null;
+
+  const db = getDb();
+  const invoiceIds = Array.isArray(invoiceIdOrIds) ? invoiceIdOrIds : [invoiceIdOrIds];
+  const firstInvoiceId = invoiceIds[0];
+  if (!firstInvoiceId) return null;
+
+  const invoiceRows = await db
+    .select()
+    .from(invoices)
+    .where(eq(invoices.id, firstInvoiceId));
+
+  const [invoice] = invoiceRows;
+  if (!invoice) return null;
+
+  let totalCents = invoice.totalCents;
+  if (invoiceIds.length > 1) {
+    const allInvoices = await db
+      .select({ totalCents: invoices.totalCents })
+      .from(invoices)
+      .where(eq(invoices.id, firstInvoiceId));
+    totalCents = allInvoices.reduce((sum, inv) => sum + inv.totalCents, totalCents);
+  }
+
+  const stripeCustomerId = await getOrCreateStripeCustomer(clientId);
+  if (!stripeCustomerId) return null;
+
+  const paymentIntent = await stripe.paymentIntents.create({
+    amount: totalCents,
+    currency: "usd",
+    customer: stripeCustomerId,
+    metadata: {
+      groombook_invoice_ids: invoiceIds.join(","),
+      groombook_client_id: clientId,
+    },
+    automatic_payment_methods: { enabled: true },
+  });
+
+  for (const invId of invoiceIds) {
+    await db
+      .update(invoices)
+      .set({ stripePaymentIntentId: paymentIntent.id, updatedAt: new Date() })
+      .where(eq(invoices.id, invId));
+  }
+
+  const clientSecret = paymentIntent.client_secret;
+  if (!clientSecret) return null;
+
+  return { clientSecret, paymentIntentId: paymentIntent.id };
+}
+
+export async function processRefund(
+  invoiceId: string,
+  amountCents?: number
+): Promise<{ refundId: string } | null> {
+  const stripe = getStripeClient();
+  if (!stripe) return null;
+
+  const db = getDb();
+  const [invoice] = await db.select().from(invoices).where(eq(invoices.id, invoiceId)).limit(1);
+  if (!invoice?.stripePaymentIntentId) return null;
+
+  const refund = await stripe.refunds.create({
+    payment_intent: invoice.stripePaymentIntentId,
+    amount: amountCents,
+  });
+
+  await db
+    .update(invoices)
+    .set({ stripeRefundId: refund.id, updatedAt: new Date() })
+    .where(eq(invoices.id, invoiceId));
+
+  return { refundId: refund.id };
+}
+
+export async function listPaymentMethods(clientId: string): Promise<Stripe.PaymentMethod[] | null> {
+  const stripe = getStripeClient();
+  if (!stripe) return null;
+
+  const stripeCustomerId = await getOrCreateStripeCustomer(clientId);
+  if (!stripeCustomerId) return null;
+
+  const methods = await stripe.paymentMethods.list({
+    customer: stripeCustomerId,
+    type: "card",
+  });
+
+  return methods.data;
+}
+
+export async function attachPaymentMethod(
+  clientId: string,
+  paymentMethodId: string
+): Promise<boolean> {
+  const stripe = getStripeClient();
+  if (!stripe) return false;
+
+  const stripeCustomerId = await getOrCreateStripeCustomer(clientId);
+  if (!stripeCustomerId) return false;
+
+  await stripe.paymentMethods.attach(paymentMethodId, { customer: stripeCustomerId });
+  return true;
+}
+
+export async function detachPaymentMethod(paymentMethodId: string): Promise<boolean> {
+  const stripe = getStripeClient();
+  if (!stripe) return false;
+
+  await stripe.paymentMethods.detach(paymentMethodId);
+  return true;
+}
+
+export async function createSetupIntent(customerId: string): Promise<{ clientSecret: string } | null> {
+  const stripe = getStripeClient();
+  if (!stripe) return null;
+
+  const setupIntent = await stripe.setupIntents.create({
+    customer: customerId,
+    payment_method_types: ["card"],
+  });
+
+  return { clientSecret: setupIntent.client_secret! };
+}

packages/db/migrations/0026_stripe_payment.sql

@@ -1,4 +1,6 @@
+ALTER TABLE "clients" ADD COLUMN "stripe_customer_id" text;
+ALTER TABLE "clients" ADD CONSTRAINT "idx_clients_stripe_customer_id" UNIQUE("stripe_customer_id");
 ALTER TABLE "invoices" ADD COLUMN "stripe_payment_intent_id" text;
 ALTER TABLE "invoices" ADD COLUMN "stripe_refund_id" text;
 ALTER TABLE "invoices" ADD COLUMN "payment_failure_reason" text;
-ALTER TABLE "invoices" ADD CONSTRAINT "idx_invoices_stripe_payment_intent_id" UNIQUE("stripe_payment_intent_id");
+ALTER TABLE "invoices" ADD CONSTRAINT "idx_invoices_stripe_payment_intent_id" UNIQUE("stripe_payment_intent_id");

packages/db/migrations/meta/0026_snapshot.json

@@ -0,0 +1,103 @@
+{
+  "id": "0026_stripe_payment",
+  "version": "7",
+  "dialect": "postgresql",
+  "tables": {
+    "authProviderConfig": {
+      "name": "auth_provider_config",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "providerId": { "name": "provider_id", "type": "text", "isNullable": false },
+        "displayName": { "name": "display_name", "type": "text", "isNullable": false },
+        "issuerUrl": { "name": "issuer_url", "type": "text", "isNullable": false },
+        "internalBaseUrl": { "name": "internal_base_url", "type": "text", "isNullable": true },
+        "clientId": { "name": "client_id", "type": "text", "isNullable": false },
+        "clientSecret": { "name": "client_secret", "type": "text", "isNullable": false },
+        "scopes": { "name": "scopes", "type": "text", "isNullable": false, "default": "'openid profile email'" },
+        "enabled": { "name": "enabled", "type": "boolean", "isNullable": false, "default": "true" },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {}
+    },
+    "businessSettings": {
+      "name": "business_settings",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "businessName": { "name": "business_name", "type": "text", "isNullable": false, "default": "'GroomBook'" },
+        "logoBase64": { "name": "logo_base64", "type": "text", "isNullable": true },
+        "logoMimeType": { "name": "logo_mime_type", "type": "text", "isNullable": true },
+        "logoKey": { "name": "logo_key", "type": "text", "isNullable": true },
+        "primaryColor": { "name": "primary_color", "type": "text", "isNullable": false, "default": "'#4f8a6f'" },
+        "accentColor": { "name": "accent_color", "type": "text", "isNullable": false, "default": "'#8b7355'" },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {}
+    },
+    "clients": {
+      "name": "clients",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "name": { "name": "name", "type": "text", "isNullable": false },
+        "email": { "name": "email", "type": "text", "isNullable": true },
+        "phone": { "name": "phone", "type": "text", "isNullable": true },
+        "address": { "name": "address", "type": "text", "isNullable": true },
+        "notes": { "name": "notes", "type": "text", "isNullable": true },
+        "emailOptOut": { "name": "email_opt_out", "type": "boolean", "isNullable": false, "default": "false" },
+        "smsOptIn": { "name": "sms_opt_in", "type": "boolean", "isNullable": false, "default": "false" },
+        "smsConsentDate": { "name": "sms_consent_date", "type": "timestamp", "isNullable": true },
+        "smsOptOutDate": { "name": "sms_opt_out_date", "type": "timestamp", "isNullable": true },
+        "smsConsentText": { "name": "sms_consent_text", "type": "text", "isNullable": true },
+        "stripeCustomerId": { "name": "stripe_customer_id", "type": "text", "isNullable": true },
+        "status": { "name": "status", "type": "client_status", "isNullable": false, "default": "'active'" },
+        "disabledAt": { "name": "disabled_at", "type": "timestamp", "isNullable": true },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "idx_clients_stripe_customer_id": { "columns": ["stripe_customer_id"] } }
+    },
+    "invoices": {
+      "name": "invoices",
+      "columns": {
+        "id": { "name": "id", "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()", "isNullable": false },
+        "appointmentId": { "name": "appointment_id", "type": "uuid", "isNullable": true },
+        "clientId": { "name": "client_id", "type": "uuid", "isNullable": false },
+        "subtotalCents": { "name": "subtotal_cents", "type": "integer", "isNullable": false },
+        "taxCents": { "name": "tax_cents", "type": "integer", "isNullable": false, "default": "0" },
+        "tipCents": { "name": "tip_cents", "type": "integer", "isNullable": false, "default": "0" },
+        "totalCents": { "name": "total_cents", "type": "integer", "isNullable": false },
+        "status": { "name": "status", "type": "invoice_status", "isNullable": false, "default": "'draft'" },
+        "paymentMethod": { "name": "payment_method", "type": "payment_method", "isNullable": true },
+        "paidAt": { "name": "paid_at", "type": "timestamp", "isNullable": true },
+        "stripePaymentIntentId": { "name": "stripe_payment_intent_id", "type": "text", "isNullable": true },
+        "stripeRefundId": { "name": "stripe_refund_id", "type": "text", "isNullable": true },
+        "paymentFailureReason": { "name": "payment_failure_reason", "type": "text", "isNullable": true },
+        "notes": { "name": "notes", "type": "text", "isNullable": true },
+        "createdAt": { "name": "created_at", "type": "timestamp", "isNullable": false, "default": "now()" },
+        "updatedAt": { "name": "updated_at", "type": "timestamp", "isNullable": false, "default": "now()" }
+      },
+      "indexes": { "idx_invoices_client_id": { "columns": ["client_id"] }, "idx_invoices_status": { "columns": ["status"] }, "idx_invoices_created_at": { "columns": ["created_at"] } },
+      "foreignKeys": { "invoices_appointment_id_fkey": { "columns": ["appointmentId"], "reference": { "table": "appointments", "columns": ["id"] } }, "invoices_client_id_fkey": { "columns": ["clientId"], "reference": { "table": "clients", "columns": ["id"] } } },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": { "idx_invoices_stripe_payment_intent_id": { "columns": ["stripe_payment_intent_id"] } }
+    }
+  },
+  "enums": {
+    "appointment_status": { "name": "appointment_status", "values": ["scheduled", "confirmed", "in_progress", "completed", "cancelled", "no_show"] },
+    "client_status": { "name": "client_status", "values": ["active", "disabled"] },
+    "impersonation_session_status": { "name": "impersonation_session_status", "values": ["active", "ended", "expired"] },
+    "invoice_status": { "name": "invoice_status", "values": ["draft", "pending", "paid", "void"] },
+    "payment_method": { "name": "payment_method", "values": ["cash", "card", "check", "other"] },
+    "staff_role": { "name": "staff_role", "values": ["groomer", "receptionist", "manager"] },
+    "waitlist_status": { "name": "waitlist_status", "values": ["active", "notified", "expired", "cancelled"] }
+  },
+  "nativeEnums": {}
+}

packages/db/migrations/meta/_journal.json

@@ -183,6 +183,13 @@
       "when": 1775482467192,
       "tag": "0025_rate_limit",
       "breakpoints": true
+    },
+    {
+      "idx": 26,
+      "version": "7",
+      "when": 1775568867192,
+      "tag": "0026_stripe_payment",
+      "breakpoints": true
     }
   ]
 }

packages/db/src/factories.ts

@@ -71,6 +71,7 @@ export function buildClient(overrides: Partial<ClientRow> = {}): ClientRow {
     address: "1 Main St, Springfield, CA 90000",
     notes: null,
     emailOptOut: false,
+    stripeCustomerId: null,
     status: "active",
     disabledAt: null,
     createdAt: new Date("2025-01-01T00:00:00Z"),

packages/db/src/schema.ts

@@ -109,8 +109,8 @@ export const clients = pgTable("clients", {
   phone: text("phone"),
   address: text("address"),
   notes: text("notes"),
-  // Set to true if the client has opted out of email reminders/notifications
   emailOptOut: boolean("email_opt_out").notNull().default(false),
+  stripeCustomerId: text("stripe_customer_id"),
   status: clientStatusEnum("status").notNull().default("active"),
   disabledAt: timestamp("disabled_at"),
   createdAt: timestamp("created_at").notNull().defaultNow(),
@@ -262,7 +262,7 @@ export const invoices = pgTable(
     index("idx_invoices_client_id").on(t.clientId),
     index("idx_invoices_status").on(t.status),
     index("idx_invoices_created_at").on(t.createdAt),
-    unique("idx_invoices_stripe_payment_intent_id").on(t.stripePaymentIntentId),
+    index("idx_invoices_stripe_payment_intent_id").on(t.stripePaymentIntentId),
   ]
 );