Store Stripe's Subscription.customer to subscriptions.customer column in the database

[satococoa]

Task Description

We currently need to make API calls to Stripe each time to get the Subscription.customer information, as it's not stored in our database. This information is required for:

• Building a customer portal
• Reporting usage-based billing

To improve performance and reliability, we need to store this information in our subscriptions table.

Background

Current implementation requires Stripe API calls for each operation:

• giselle/services/usage-based-billing/agent-time-usage.ts

  Lines 78 to 83 in 4a5a130

  	// FIXME: if we have customer id in subscriptions table, we can use it directly
  	const subscription = await stripe.subscriptions.retrieve(subscriptionId);
  	customerId =
  	typeof subscription.customer === "string"
  	? subscription.customer
  	: subscription.customer.id;

• giselle/services/teams/actions/manage-billing.ts

  Lines 11 to 16 in 4a5a130

  	const subscription = await stripe.subscriptions.retrieve(subscriptionId);
  	const session = await stripe.billingPortal.sessions.create({
  	customer: subscription.customer as string,
  	return_url: `${siteUrl}/settings/team`,
  	});

Implementation Steps

1. Add customer column to subscriptions table (nullable initially)
2. Migrate existing subscriptions by fetching customer data from Stripe API
3. Modify the column to be NOT NULL

Reference

• Stripe Subscription Object Documentation

[gentamura]

@satococoa

In the following mtehod, customer_id is specified by the key customer, so it looks like it would be OK to store customer_id as customer, but I personally think the following policy is better.

https://docs.stripe.com/api/customer_portal/sessions/create

const stripe = require('stripe')('sk_test_xxx');

const session = await stripe.billingPortal.sessions.create({
  customer: 'cus_xxx',
  return_url: 'https://example.com/account',
});

• Simply looking at the code, customer would look like a customer object.
• The following definition makes it clear that it is customer_id.

export const subscriptions = pgTable("subscriptions", {
	// Subscription ID from Stripe, e.g. sub_1234.
	id: text("id").notNull().unique(),
	dbId: serial("db_id").primaryKey(),
	teamDbId: integer("team_db_id")
		.notNull()
		.references(() => teams.dbId, { onDelete: "cascade" }),
+	customerId: text("customer_id").notNull(), // Stripe Customer ID (e.g. "cus_xxx")
	status: text("status").$type<Stripe.Subscription.Status>().notNull(),
	cancelAtPeriodEnd: boolean("cancel_at_period_end").notNull(),
	cancelAt: timestamp("cancel_at"),
	canceledAt: timestamp("canceled_at"),
	currentPeriodStart: timestamp("current_period_start").notNull(),
	currentPeriodEnd: timestamp("current_period_end").notNull(),
	created: timestamp("created").defaultNow().notNull(),
	endedAt: timestamp("ended_at"),
	trialStart: timestamp("trial_start"),
	trialEnd: timestamp("trial_end"),
});

So I will proceed with customer_id, any concerns?

[satococoa]

@gentamura I agree! Please add customer_id 👍