shithub Public

Watch 1 Fork 0 Star 0
MySQL · 10898 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2
3 -- ─── org_billing_states ────────────────────────────────────────────
4
5 -- name: GetOrgBillingState :one
6 SELECT * FROM org_billing_states WHERE org_id = $1;
7
8 -- name: GetOrgBillingStateByStripeCustomer :one
9 SELECT * FROM org_billing_states
10 WHERE provider = 'stripe'
11 AND stripe_customer_id = $1;
12
13 -- name: GetOrgBillingStateByStripeSubscription :one
14 SELECT * FROM org_billing_states
15 WHERE provider = 'stripe'
16 AND stripe_subscription_id = $1;
17
18 -- name: SetStripeCustomer :one
19 INSERT INTO org_billing_states (org_id, provider, stripe_customer_id)
20 VALUES ($1, 'stripe', $2)
21 ON CONFLICT (org_id) DO UPDATE
22 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
23 provider = 'stripe',
24 updated_at = now()
25 RETURNING *;
26
27 -- name: ApplySubscriptionSnapshot :one
28 WITH state AS (
29 INSERT INTO org_billing_states (
30 org_id,
31 provider,
32 plan,
33 subscription_status,
34 stripe_subscription_id,
35 stripe_subscription_item_id,
36 current_period_start,
37 current_period_end,
38 cancel_at_period_end,
39 trial_end,
40 canceled_at,
41 last_webhook_event_id,
42 past_due_at,
43 locked_at,
44 lock_reason,
45 grace_until
46 )
47 VALUES (
48 sqlc.arg(org_id)::bigint,
49 'stripe',
50 sqlc.arg(plan)::org_plan,
51 sqlc.arg(subscription_status)::billing_subscription_status,
52 sqlc.narg(stripe_subscription_id)::text,
53 sqlc.narg(stripe_subscription_item_id)::text,
54 sqlc.narg(current_period_start)::timestamptz,
55 sqlc.narg(current_period_end)::timestamptz,
56 sqlc.arg(cancel_at_period_end)::boolean,
57 sqlc.narg(trial_end)::timestamptz,
58 sqlc.narg(canceled_at)::timestamptz,
59 sqlc.arg(last_webhook_event_id)::text,
60 CASE
61 WHEN sqlc.arg(subscription_status)::billing_subscription_status = 'past_due' THEN now()
62 ELSE NULL
63 END,
64 NULL,
65 NULL,
66 NULL
67 )
68 ON CONFLICT (org_id) DO UPDATE
69 SET plan = EXCLUDED.plan,
70 subscription_status = EXCLUDED.subscription_status,
71 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
72 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
73 current_period_start = EXCLUDED.current_period_start,
74 current_period_end = EXCLUDED.current_period_end,
75 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
76 trial_end = EXCLUDED.trial_end,
77 canceled_at = EXCLUDED.canceled_at,
78 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
79 past_due_at = CASE
80 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(org_billing_states.past_due_at, now())
81 ELSE NULL
82 END,
83 locked_at = NULL,
84 lock_reason = NULL,
85 grace_until = NULL,
86 updated_at = now()
87 RETURNING *
88 ), org_update AS (
89 UPDATE orgs
90 SET plan = sqlc.arg(plan)::org_plan,
91 updated_at = now()
92 WHERE id = sqlc.arg(org_id)::bigint
93 RETURNING id
94 )
95 SELECT * FROM state;
96
97 -- name: MarkPastDue :one
98 UPDATE org_billing_states
99 SET subscription_status = 'past_due',
100 past_due_at = COALESCE(past_due_at, now()),
101 locked_at = now(),
102 lock_reason = 'past_due',
103 grace_until = sqlc.narg(grace_until)::timestamptz,
104 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
105 updated_at = now()
106 WHERE org_id = sqlc.arg(org_id)::bigint
107 RETURNING *;
108
109 -- name: MarkPaymentSucceeded :one
110 WITH state AS (
111 UPDATE org_billing_states
112 SET plan = CASE
113 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'team'
114 ELSE plan
115 END,
116 subscription_status = CASE
117 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
118 ELSE subscription_status
119 END,
120 past_due_at = CASE
121 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
122 ELSE past_due_at
123 END,
124 locked_at = NULL,
125 lock_reason = NULL,
126 grace_until = NULL,
127 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
128 updated_at = now()
129 WHERE org_id = sqlc.arg(org_id)::bigint
130 RETURNING *
131 ), org_update AS (
132 UPDATE orgs
133 SET plan = state.plan,
134 updated_at = now()
135 FROM state
136 WHERE orgs.id = state.org_id
137 RETURNING orgs.id
138 )
139 SELECT * FROM state;
140
141 -- name: MarkCanceled :one
142 WITH state AS (
143 UPDATE org_billing_states
144 SET plan = 'free',
145 subscription_status = 'canceled',
146 canceled_at = COALESCE(canceled_at, now()),
147 locked_at = now(),
148 lock_reason = 'canceled',
149 grace_until = NULL,
150 cancel_at_period_end = false,
151 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
152 updated_at = now()
153 WHERE org_id = sqlc.arg(org_id)::bigint
154 RETURNING *
155 ), org_update AS (
156 UPDATE orgs
157 SET plan = 'free',
158 updated_at = now()
159 WHERE id = sqlc.arg(org_id)::bigint
160 RETURNING id
161 )
162 SELECT * FROM state;
163
164 -- name: ClearBillingLock :one
165 WITH state AS (
166 UPDATE org_billing_states
167 SET plan = CASE
168 WHEN subscription_status = 'canceled' THEN 'free'
169 ELSE plan
170 END,
171 subscription_status = CASE
172 WHEN subscription_status = 'canceled' THEN 'none'
173 ELSE subscription_status
174 END,
175 locked_at = NULL,
176 lock_reason = NULL,
177 grace_until = NULL,
178 updated_at = now()
179 WHERE org_id = $1
180 RETURNING *
181 ), org_update AS (
182 UPDATE orgs
183 SET plan = state.plan,
184 updated_at = now()
185 FROM state
186 WHERE orgs.id = state.org_id
187 RETURNING orgs.id
188 )
189 SELECT * FROM state;
190
191 -- ─── billing_seat_snapshots ────────────────────────────────────────
192
193 -- name: CreateSeatSnapshot :one
194 WITH snapshot AS (
195 INSERT INTO billing_seat_snapshots (
196 org_id,
197 provider,
198 stripe_subscription_id,
199 active_members,
200 billable_seats,
201 source
202 )
203 VALUES (
204 sqlc.arg(org_id)::bigint,
205 'stripe',
206 sqlc.narg(stripe_subscription_id)::text,
207 sqlc.arg(active_members)::integer,
208 sqlc.arg(billable_seats)::integer,
209 sqlc.arg(source)::text
210 )
211 RETURNING *
212 ), state AS (
213 INSERT INTO org_billing_states (org_id, billable_seats, seat_snapshot_at)
214 SELECT org_id, billable_seats, captured_at FROM snapshot
215 ON CONFLICT (org_id) DO UPDATE
216 SET billable_seats = EXCLUDED.billable_seats,
217 seat_snapshot_at = EXCLUDED.seat_snapshot_at,
218 updated_at = now()
219 RETURNING org_id
220 )
221 SELECT * FROM snapshot;
222
223 -- name: ListSeatSnapshotsForOrg :many
224 SELECT * FROM billing_seat_snapshots
225 WHERE org_id = $1
226 ORDER BY captured_at DESC, id DESC
227 LIMIT $2;
228
229 -- name: CountBillableOrgMembers :one
230 SELECT count(*)::integer
231 FROM org_members
232 WHERE org_id = $1;
233
234 -- name: CountPendingOrgInvitations :one
235 SELECT count(*)::integer
236 FROM org_invitations
237 WHERE org_id = $1
238 AND accepted_at IS NULL
239 AND declined_at IS NULL
240 AND canceled_at IS NULL
241 AND expires_at > now();
242
243 -- ─── billing_invoices ──────────────────────────────────────────────
244
245 -- name: UpsertInvoice :one
246 INSERT INTO billing_invoices (
247 org_id,
248 provider,
249 stripe_invoice_id,
250 stripe_customer_id,
251 stripe_subscription_id,
252 status,
253 number,
254 currency,
255 amount_due_cents,
256 amount_paid_cents,
257 amount_remaining_cents,
258 hosted_invoice_url,
259 invoice_pdf_url,
260 period_start,
261 period_end,
262 due_at,
263 paid_at,
264 voided_at
265 )
266 VALUES (
267 sqlc.arg(org_id)::bigint,
268 'stripe',
269 sqlc.arg(stripe_invoice_id)::text,
270 sqlc.arg(stripe_customer_id)::text,
271 sqlc.narg(stripe_subscription_id)::text,
272 sqlc.arg(status)::billing_invoice_status,
273 sqlc.arg(number)::text,
274 sqlc.arg(currency)::text,
275 sqlc.arg(amount_due_cents)::bigint,
276 sqlc.arg(amount_paid_cents)::bigint,
277 sqlc.arg(amount_remaining_cents)::bigint,
278 sqlc.arg(hosted_invoice_url)::text,
279 sqlc.arg(invoice_pdf_url)::text,
280 sqlc.narg(period_start)::timestamptz,
281 sqlc.narg(period_end)::timestamptz,
282 sqlc.narg(due_at)::timestamptz,
283 sqlc.narg(paid_at)::timestamptz,
284 sqlc.narg(voided_at)::timestamptz
285 )
286 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
287 SET org_id = EXCLUDED.org_id,
288 stripe_customer_id = EXCLUDED.stripe_customer_id,
289 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
290 status = EXCLUDED.status,
291 number = EXCLUDED.number,
292 currency = EXCLUDED.currency,
293 amount_due_cents = EXCLUDED.amount_due_cents,
294 amount_paid_cents = EXCLUDED.amount_paid_cents,
295 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
296 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
297 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
298 period_start = EXCLUDED.period_start,
299 period_end = EXCLUDED.period_end,
300 due_at = EXCLUDED.due_at,
301 paid_at = EXCLUDED.paid_at,
302 voided_at = EXCLUDED.voided_at,
303 updated_at = now()
304 RETURNING *;
305
306 -- name: ListInvoicesForOrg :many
307 SELECT * FROM billing_invoices
308 WHERE org_id = $1
309 ORDER BY created_at DESC, id DESC
310 LIMIT $2;
311
312 -- ─── billing_webhook_events ────────────────────────────────────────
313
314 -- name: CreateWebhookEventReceipt :one
315 INSERT INTO billing_webhook_events (
316 provider,
317 provider_event_id,
318 event_type,
319 api_version,
320 payload
321 )
322 VALUES (
323 'stripe',
324 sqlc.arg(provider_event_id)::text,
325 sqlc.arg(event_type)::text,
326 sqlc.arg(api_version)::text,
327 sqlc.arg(payload)::jsonb
328 )
329 ON CONFLICT (provider, provider_event_id) DO NOTHING
330 RETURNING *;
331
332 -- name: GetWebhookEventReceipt :one
333 SELECT * FROM billing_webhook_events
334 WHERE provider = 'stripe'
335 AND provider_event_id = $1;
336
337 -- name: MarkWebhookEventProcessed :one
338 UPDATE billing_webhook_events
339 SET processed_at = now(),
340 process_error = '',
341 processing_attempts = processing_attempts + 1
342 WHERE provider = 'stripe'
343 AND provider_event_id = $1
344 RETURNING *;
345
346 -- name: MarkWebhookEventFailed :one
347 UPDATE billing_webhook_events
348 SET process_error = $2,
349 processing_attempts = processing_attempts + 1
350 WHERE provider = 'stripe'
351 AND provider_event_id = $1
352 RETURNING *;
353