1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
import { Billing } from "../src/billing.js"
import { and, Database, desc, eq, isNotNull, lt, sql } from "../src/drizzle/index.js"
import { BillingTable, PaymentTable, SubscriptionTable } from "../src/schema/billing.sql.js"
const fromWrkID = process.argv[2]
const toWrkID = process.argv[3]
if (!fromWrkID || !toWrkID) {
console.error("Usage: bun foo.ts <fromWrkID> <toWrkID>")
process.exit(1)
}
console.log(`Transferring subscription from ${fromWrkID} to ${toWrkID}`)
// Look up the FROM workspace billing
const fromBilling = await Database.use((tx) =>
tx
.select({
customerID: BillingTable.customerID,
subscriptionID: BillingTable.subscriptionID,
subscription: BillingTable.subscription,
paymentMethodID: BillingTable.paymentMethodID,
paymentMethodType: BillingTable.paymentMethodType,
paymentMethodLast4: BillingTable.paymentMethodLast4,
})
.from(BillingTable)
.where(eq(BillingTable.workspaceID, fromWrkID))
.then((rows) => rows[0]),
)
if (!fromBilling) throw new Error(`Error: FROM workspace has no billing record`)
if (!fromBilling.customerID) throw new Error(`Error: FROM workspace has no Stripe customer ID`)
if (!fromBilling.subscriptionID) throw new Error(`Error: FROM workspace has no subscription`)
const fromSubscription = await Database.use((tx) =>
tx
.select({ userID: SubscriptionTable.userID })
.from(SubscriptionTable)
.where(eq(SubscriptionTable.workspaceID, fromWrkID))
.then((rows) => rows[0]),
)
if (!fromSubscription) throw new Error(`Error: FROM workspace has no subscription`)
// Look up the previous customer ID in FROM workspace
const subscriptionPayment = await Database.use((tx) =>
tx
.select({
customerID: PaymentTable.customerID,
timeCreated: PaymentTable.timeCreated,
})
.from(PaymentTable)
.where(and(eq(PaymentTable.workspaceID, fromWrkID), sql`JSON_EXTRACT(enrichment, '$.type') = 'subscription'`))
.then((rows) => {
if (rows.length > 1) {
console.error(`Error: Multiple subscription payments found for workspace ${fromWrkID}`)
process.exit(1)
}
return rows[0]
}),
)
const fromPrevPayment = await Database.use((tx) =>
tx
.select({ customerID: PaymentTable.customerID })
.from(PaymentTable)
.where(
and(
eq(PaymentTable.workspaceID, fromWrkID),
isNotNull(PaymentTable.customerID),
lt(PaymentTable.timeCreated, subscriptionPayment.timeCreated),
),
)
.orderBy(desc(PaymentTable.timeCreated))
.limit(1)
.then((rows) => rows[0]),
)
if (!fromPrevPayment?.customerID) throw new Error(`Error: FROM workspace has no previous Stripe customer to revert to`)
if (fromPrevPayment.customerID === fromBilling.customerID)
throw new Error(`Error: FROM workspace has the same Stripe customer ID as the current one`)
const fromPrevPaymentMethods = await Billing.stripe().customers.listPaymentMethods(fromPrevPayment.customerID, {})
if (fromPrevPaymentMethods.data.length === 0)
throw new Error(`Error: FROM workspace has no previous Stripe payment methods`)
// Look up the TO workspace billing
const toBilling = await Database.use((tx) =>
tx
.select({
customerID: BillingTable.customerID,
subscriptionID: BillingTable.subscriptionID,
})
.from(BillingTable)
.where(eq(BillingTable.workspaceID, toWrkID))
.then((rows) => rows[0]),
)
if (!toBilling) throw new Error(`Error: TO workspace has no billing record`)
if (toBilling.subscriptionID) throw new Error(`Error: TO workspace already has a subscription`)
console.log(`FROM:`)
console.log(` Old Customer ID: ${fromBilling.customerID}`)
console.log(` New Customer ID: ${fromPrevPayment.customerID}`)
console.log(`TO:`)
console.log(` Old Customer ID: ${toBilling.customerID}`)
console.log(` New Customer ID: ${fromBilling.customerID}`)
// Clear workspaceID from Stripe customer metadata
await Billing.stripe().customers.update(fromPrevPayment.customerID, {
metadata: {
workspaceID: fromWrkID,
},
})
await Billing.stripe().customers.update(fromBilling.customerID, {
metadata: {
workspaceID: toWrkID,
},
})
await Database.transaction(async (tx) => {
await tx
.update(BillingTable)
.set({
customerID: fromPrevPayment.customerID,
subscriptionID: null,
subscription: null,
paymentMethodID: fromPrevPaymentMethods.data[0].id,
paymentMethodLast4: fromPrevPaymentMethods.data[0].card?.last4 ?? null,
paymentMethodType: fromPrevPaymentMethods.data[0].type,
})
.where(eq(BillingTable.workspaceID, fromWrkID))
await tx
.update(BillingTable)
.set({
customerID: fromBilling.customerID,
subscriptionID: fromBilling.subscriptionID,
subscription: fromBilling.subscription,
paymentMethodID: fromBilling.paymentMethodID,
paymentMethodLast4: fromBilling.paymentMethodLast4,
paymentMethodType: fromBilling.paymentMethodType,
})
.where(eq(BillingTable.workspaceID, toWrkID))
await tx
.update(SubscriptionTable)
.set({
workspaceID: toWrkID,
userID: fromSubscription.userID,
})
.where(eq(SubscriptionTable.workspaceID, fromWrkID))
await tx
.update(PaymentTable)
.set({
workspaceID: toWrkID,
})
.where(
and(
eq(PaymentTable.workspaceID, fromWrkID),
sql`JSON_EXTRACT(enrichment, '$.type') = 'subscription'`,
eq(PaymentTable.amount, 20000000000),
),
)
})
console.log(`done`)
|
