shithub Public

Watch 1 Fork 0 Star 0
MySQL · 20766 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 -- PRO03: writes both legacy `org_id` and polymorphic
247 -- `(subject_kind, subject_id)`. Callers continue to bind org_id only;
248 -- the subject columns are derived. After PRO04 migrates all callers
249 -- to the polymorphic shape, a follow-up migration drops `org_id` and
250 -- this query loses the legacy column from its INSERT list.
251 INSERT INTO billing_invoices (
252 org_id,
253 subject_kind,
254 subject_id,
255 provider,
256 stripe_invoice_id,
257 stripe_customer_id,
258 stripe_subscription_id,
259 status,
260 number,
261 currency,
262 amount_due_cents,
263 amount_paid_cents,
264 amount_remaining_cents,
265 hosted_invoice_url,
266 invoice_pdf_url,
267 period_start,
268 period_end,
269 due_at,
270 paid_at,
271 voided_at
272 )
273 VALUES (
274 sqlc.arg(org_id)::bigint,
275 'org'::billing_subject_kind,
276 sqlc.arg(org_id)::bigint,
277 'stripe',
278 sqlc.arg(stripe_invoice_id)::text,
279 sqlc.arg(stripe_customer_id)::text,
280 sqlc.narg(stripe_subscription_id)::text,
281 sqlc.arg(status)::billing_invoice_status,
282 sqlc.arg(number)::text,
283 sqlc.arg(currency)::text,
284 sqlc.arg(amount_due_cents)::bigint,
285 sqlc.arg(amount_paid_cents)::bigint,
286 sqlc.arg(amount_remaining_cents)::bigint,
287 sqlc.arg(hosted_invoice_url)::text,
288 sqlc.arg(invoice_pdf_url)::text,
289 sqlc.narg(period_start)::timestamptz,
290 sqlc.narg(period_end)::timestamptz,
291 sqlc.narg(due_at)::timestamptz,
292 sqlc.narg(paid_at)::timestamptz,
293 sqlc.narg(voided_at)::timestamptz
294 )
295 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
296 SET org_id = EXCLUDED.org_id,
297 stripe_customer_id = EXCLUDED.stripe_customer_id,
298 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
299 status = EXCLUDED.status,
300 number = EXCLUDED.number,
301 currency = EXCLUDED.currency,
302 amount_due_cents = EXCLUDED.amount_due_cents,
303 amount_paid_cents = EXCLUDED.amount_paid_cents,
304 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
305 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
306 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
307 period_start = EXCLUDED.period_start,
308 period_end = EXCLUDED.period_end,
309 due_at = EXCLUDED.due_at,
310 paid_at = EXCLUDED.paid_at,
311 voided_at = EXCLUDED.voided_at,
312 updated_at = now()
313 RETURNING *;
314
315 -- name: ListInvoicesForOrg :many
316 -- PRO03: filters on the polymorphic subject columns so the index
317 -- billing_invoices_subject_created_idx services this query. The
318 -- legacy `org_id` column is kept populated by UpsertInvoice for the
319 -- transitional window; this query no longer reads it.
320 SELECT * FROM billing_invoices
321 WHERE subject_kind = 'org' AND subject_id = $1
322 ORDER BY created_at DESC, id DESC
323 LIMIT $2;
324
325 -- name: ListInvoicesForSubject :many
326 -- Polymorphic invoice listing for PRO04+ callers. The org-flavored
327 -- ListInvoicesForOrg above is the same query with subject_kind
328 -- hard-coded; this surface lets a user-side caller pass kind='user'
329 -- without forking the helper.
330 SELECT * FROM billing_invoices
331 WHERE subject_kind = sqlc.arg(subject_kind)::billing_subject_kind
332 AND subject_id = sqlc.arg(subject_id)::bigint
333 ORDER BY created_at DESC, id DESC
334 LIMIT sqlc.arg(lim)::integer;
335
336 -- name: UpsertInvoiceForSubject :one
337 -- PRO04 polymorphic invoice upsert. Writes (subject_kind,
338 -- subject_id) directly; org_id stays NULL for user-kind rows (per
339 -- the 0074 migration's nullable change). The existing
340 -- UpsertInvoice query stays as the org-kind path during the
341 -- transitional deploy — both can coexist because the UNIQUE
342 -- (provider, stripe_invoice_id) prevents duplicate rows.
343 INSERT INTO billing_invoices (
344 subject_kind,
345 subject_id,
346 provider,
347 stripe_invoice_id,
348 stripe_customer_id,
349 stripe_subscription_id,
350 status,
351 number,
352 currency,
353 amount_due_cents,
354 amount_paid_cents,
355 amount_remaining_cents,
356 hosted_invoice_url,
357 invoice_pdf_url,
358 period_start,
359 period_end,
360 due_at,
361 paid_at,
362 voided_at
363 )
364 VALUES (
365 sqlc.arg(subject_kind)::billing_subject_kind,
366 sqlc.arg(subject_id)::bigint,
367 'stripe',
368 sqlc.arg(stripe_invoice_id)::text,
369 sqlc.arg(stripe_customer_id)::text,
370 sqlc.narg(stripe_subscription_id)::text,
371 sqlc.arg(status)::billing_invoice_status,
372 sqlc.arg(number)::text,
373 sqlc.arg(currency)::text,
374 sqlc.arg(amount_due_cents)::bigint,
375 sqlc.arg(amount_paid_cents)::bigint,
376 sqlc.arg(amount_remaining_cents)::bigint,
377 sqlc.arg(hosted_invoice_url)::text,
378 sqlc.arg(invoice_pdf_url)::text,
379 sqlc.narg(period_start)::timestamptz,
380 sqlc.narg(period_end)::timestamptz,
381 sqlc.narg(due_at)::timestamptz,
382 sqlc.narg(paid_at)::timestamptz,
383 sqlc.narg(voided_at)::timestamptz
384 )
385 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
386 SET subject_kind = EXCLUDED.subject_kind,
387 subject_id = EXCLUDED.subject_id,
388 stripe_customer_id = EXCLUDED.stripe_customer_id,
389 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
390 status = EXCLUDED.status,
391 number = EXCLUDED.number,
392 currency = EXCLUDED.currency,
393 amount_due_cents = EXCLUDED.amount_due_cents,
394 amount_paid_cents = EXCLUDED.amount_paid_cents,
395 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
396 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
397 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
398 period_start = EXCLUDED.period_start,
399 period_end = EXCLUDED.period_end,
400 due_at = EXCLUDED.due_at,
401 paid_at = EXCLUDED.paid_at,
402 voided_at = EXCLUDED.voided_at,
403 updated_at = now()
404 RETURNING *;
405
406 -- ─── billing_webhook_events ────────────────────────────────────────
407
408 -- name: CreateWebhookEventReceipt :one
409 INSERT INTO billing_webhook_events (
410 provider,
411 provider_event_id,
412 event_type,
413 api_version,
414 payload
415 )
416 VALUES (
417 'stripe',
418 sqlc.arg(provider_event_id)::text,
419 sqlc.arg(event_type)::text,
420 sqlc.arg(api_version)::text,
421 sqlc.arg(payload)::jsonb
422 )
423 ON CONFLICT (provider, provider_event_id) DO NOTHING
424 RETURNING *;
425
426 -- name: GetWebhookEventReceipt :one
427 SELECT * FROM billing_webhook_events
428 WHERE provider = 'stripe'
429 AND provider_event_id = $1;
430
431 -- name: MarkWebhookEventProcessed :one
432 UPDATE billing_webhook_events
433 SET processed_at = now(),
434 process_error = '',
435 processing_attempts = processing_attempts + 1
436 WHERE provider = 'stripe'
437 AND provider_event_id = $1
438 RETURNING *;
439
440 -- name: MarkWebhookEventFailed :one
441 UPDATE billing_webhook_events
442 SET process_error = $2,
443 processing_attempts = processing_attempts + 1
444 WHERE provider = 'stripe'
445 AND provider_event_id = $1
446 RETURNING *;
447
448 -- ─── user_billing_states (PRO03) ──────────────────────────────────
449
450 -- name: GetUserBillingState :one
451 SELECT * FROM user_billing_states WHERE user_id = $1;
452
453 -- name: GetUserBillingStateByStripeCustomer :one
454 SELECT * FROM user_billing_states
455 WHERE provider = 'stripe'
456 AND stripe_customer_id = $1;
457
458 -- name: GetUserBillingStateByStripeSubscription :one
459 SELECT * FROM user_billing_states
460 WHERE provider = 'stripe'
461 AND stripe_subscription_id = $1;
462
463 -- name: SetUserStripeCustomer :one
464 INSERT INTO user_billing_states (user_id, provider, stripe_customer_id)
465 VALUES ($1, 'stripe', $2)
466 ON CONFLICT (user_id) DO UPDATE
467 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
468 provider = 'stripe',
469 updated_at = now()
470 RETURNING *;
471
472 -- name: ApplyUserSubscriptionSnapshot :one
473 -- Mirrors ApplySubscriptionSnapshot for orgs minus the seat columns
474 -- and with `user_plan` as the plan enum. The same CTE pattern keeps
475 -- users.plan and user_billing_states.plan atomic.
476 WITH state AS (
477 INSERT INTO user_billing_states (
478 user_id,
479 provider,
480 plan,
481 subscription_status,
482 stripe_subscription_id,
483 stripe_subscription_item_id,
484 current_period_start,
485 current_period_end,
486 cancel_at_period_end,
487 trial_end,
488 canceled_at,
489 last_webhook_event_id,
490 past_due_at,
491 locked_at,
492 lock_reason,
493 grace_until
494 )
495 VALUES (
496 sqlc.arg(user_id)::bigint,
497 'stripe',
498 sqlc.arg(plan)::user_plan,
499 sqlc.arg(subscription_status)::billing_subscription_status,
500 sqlc.narg(stripe_subscription_id)::text,
501 sqlc.narg(stripe_subscription_item_id)::text,
502 sqlc.narg(current_period_start)::timestamptz,
503 sqlc.narg(current_period_end)::timestamptz,
504 sqlc.arg(cancel_at_period_end)::boolean,
505 sqlc.narg(trial_end)::timestamptz,
506 sqlc.narg(canceled_at)::timestamptz,
507 sqlc.arg(last_webhook_event_id)::text,
508 CASE
509 WHEN sqlc.arg(subscription_status)::billing_subscription_status = 'past_due' THEN now()
510 ELSE NULL
511 END,
512 NULL,
513 NULL,
514 NULL
515 )
516 ON CONFLICT (user_id) DO UPDATE
517 SET plan = EXCLUDED.plan,
518 subscription_status = EXCLUDED.subscription_status,
519 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
520 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
521 current_period_start = EXCLUDED.current_period_start,
522 current_period_end = EXCLUDED.current_period_end,
523 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
524 trial_end = EXCLUDED.trial_end,
525 canceled_at = EXCLUDED.canceled_at,
526 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
527 past_due_at = CASE
528 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.past_due_at, now())
529 ELSE NULL
530 END,
531 locked_at = NULL,
532 lock_reason = NULL,
533 grace_until = NULL,
534 updated_at = now()
535 RETURNING *
536 ), user_update AS (
537 UPDATE users
538 SET plan = sqlc.arg(plan)::user_plan,
539 updated_at = now()
540 WHERE id = sqlc.arg(user_id)::bigint
541 RETURNING id
542 )
543 SELECT * FROM state;
544
545 -- name: MarkUserPastDue :one
546 UPDATE user_billing_states
547 SET subscription_status = 'past_due',
548 past_due_at = COALESCE(past_due_at, now()),
549 locked_at = now(),
550 lock_reason = 'past_due',
551 grace_until = sqlc.narg(grace_until)::timestamptz,
552 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
553 updated_at = now()
554 WHERE user_id = sqlc.arg(user_id)::bigint
555 RETURNING *;
556
557 -- name: MarkUserPaymentSucceeded :one
558 WITH state AS (
559 UPDATE user_billing_states
560 SET plan = CASE
561 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'pro'
562 ELSE plan
563 END,
564 subscription_status = CASE
565 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
566 ELSE subscription_status
567 END,
568 past_due_at = CASE
569 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
570 ELSE past_due_at
571 END,
572 locked_at = NULL,
573 lock_reason = NULL,
574 grace_until = NULL,
575 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
576 updated_at = now()
577 WHERE user_id = sqlc.arg(user_id)::bigint
578 RETURNING *
579 ), user_update AS (
580 UPDATE users
581 SET plan = state.plan,
582 updated_at = now()
583 FROM state
584 WHERE users.id = state.user_id
585 RETURNING users.id
586 )
587 SELECT * FROM state;
588
589 -- name: MarkUserCanceled :one
590 WITH state AS (
591 UPDATE user_billing_states
592 SET plan = 'free',
593 subscription_status = 'canceled',
594 canceled_at = COALESCE(canceled_at, now()),
595 locked_at = now(),
596 lock_reason = 'canceled',
597 grace_until = NULL,
598 cancel_at_period_end = false,
599 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
600 updated_at = now()
601 WHERE user_id = sqlc.arg(user_id)::bigint
602 RETURNING *
603 ), user_update AS (
604 UPDATE users
605 SET plan = 'free',
606 updated_at = now()
607 WHERE id = sqlc.arg(user_id)::bigint
608 RETURNING id
609 )
610 SELECT * FROM state;
611
612 -- name: ClearUserBillingLock :one
613 WITH state AS (
614 UPDATE user_billing_states
615 SET plan = CASE
616 WHEN subscription_status = 'canceled' THEN 'free'
617 ELSE plan
618 END,
619 subscription_status = CASE
620 WHEN subscription_status = 'canceled' THEN 'none'
621 ELSE subscription_status
622 END,
623 locked_at = NULL,
624 lock_reason = NULL,
625 grace_until = NULL,
626 updated_at = now()
627 WHERE user_id = $1
628 RETURNING *
629 ), user_update AS (
630 UPDATE users
631 SET plan = state.plan,
632 updated_at = now()
633 FROM state
634 WHERE users.id = state.user_id
635 RETURNING users.id
636 )
637 SELECT * FROM state;
638