shithub Public

Watch 1 Fork 0 Star 0
MySQL · 22831 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 -- name: SetWebhookEventSubject :exec
449 -- Records the resolved subject on the receipt row after a successful
450 -- subject-resolution step. Called from the apply path before guard +
451 -- state mutation so the receipt carries the audit trail even if the
452 -- subsequent apply fails. Migration 0075's CHECK constraint enforces
453 -- both-or-neither; callers must pass a non-zero subject.
454 UPDATE billing_webhook_events
455 SET subject_kind = sqlc.arg(subject_kind)::billing_subject_kind,
456 subject_id = sqlc.arg(subject_id)::bigint
457 WHERE provider = 'stripe'
458 AND provider_event_id = sqlc.arg(provider_event_id)::text;
459
460 -- name: TryAcquireWebhookEventLock :one
461 -- PRO08 A3: transaction-scoped advisory lock keyed on the hash of
462 -- the provider_event_id. Two concurrent webhook deliveries for the
463 -- same event_id race past CreateWebhookEventReceipt before either has
464 -- marked it processed; without serialization, both proceed to apply
465 -- and double-mutate state. This lock makes the apply path mutually
466 -- exclusive per event. Returns true when acquired; false means
467 -- another worker holds it — caller should let Stripe retry.
468 --
469 -- pg_try_advisory_xact_lock takes a bigint; hashtext returns int4
470 -- which sign-extends safely. The lock auto-releases at txn end.
471 SELECT pg_try_advisory_xact_lock(hashtext($1)::bigint) AS acquired;
472
473 -- name: ListFailedWebhookEvents :many
474 -- Operator query for "events we received but failed to process."
475 -- A row is "failed" when it has a non-empty process_error OR when
476 -- it has never been processed (processed_at NULL) and has at least
477 -- one processing attempt. Rows that are merely new and untouched
478 -- (attempts=0, processed_at NULL, no error) are excluded.
479 SELECT id, provider, provider_event_id, event_type, api_version,
480 received_at, processed_at, processing_attempts, process_error,
481 subject_kind, subject_id
482 FROM billing_webhook_events
483 WHERE provider = 'stripe'
484 AND (
485 process_error <> ''
486 OR (processed_at IS NULL AND processing_attempts > 0)
487 )
488 ORDER BY received_at DESC
489 LIMIT $1;
490
491 -- ─── user_billing_states (PRO03) ──────────────────────────────────
492
493 -- name: GetUserBillingState :one
494 SELECT * FROM user_billing_states WHERE user_id = $1;
495
496 -- name: GetUserBillingStateByStripeCustomer :one
497 SELECT * FROM user_billing_states
498 WHERE provider = 'stripe'
499 AND stripe_customer_id = $1;
500
501 -- name: GetUserBillingStateByStripeSubscription :one
502 SELECT * FROM user_billing_states
503 WHERE provider = 'stripe'
504 AND stripe_subscription_id = $1;
505
506 -- name: SetUserStripeCustomer :one
507 INSERT INTO user_billing_states (user_id, provider, stripe_customer_id)
508 VALUES ($1, 'stripe', $2)
509 ON CONFLICT (user_id) DO UPDATE
510 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
511 provider = 'stripe',
512 updated_at = now()
513 RETURNING *;
514
515 -- name: ApplyUserSubscriptionSnapshot :one
516 -- Mirrors ApplySubscriptionSnapshot for orgs minus the seat columns
517 -- and with `user_plan` as the plan enum. The same CTE pattern keeps
518 -- users.plan and user_billing_states.plan atomic.
519 WITH state AS (
520 INSERT INTO user_billing_states (
521 user_id,
522 provider,
523 plan,
524 subscription_status,
525 stripe_subscription_id,
526 stripe_subscription_item_id,
527 current_period_start,
528 current_period_end,
529 cancel_at_period_end,
530 trial_end,
531 canceled_at,
532 last_webhook_event_id,
533 past_due_at,
534 locked_at,
535 lock_reason,
536 grace_until
537 )
538 VALUES (
539 sqlc.arg(user_id)::bigint,
540 'stripe',
541 sqlc.arg(plan)::user_plan,
542 sqlc.arg(subscription_status)::billing_subscription_status,
543 sqlc.narg(stripe_subscription_id)::text,
544 sqlc.narg(stripe_subscription_item_id)::text,
545 sqlc.narg(current_period_start)::timestamptz,
546 sqlc.narg(current_period_end)::timestamptz,
547 sqlc.arg(cancel_at_period_end)::boolean,
548 sqlc.narg(trial_end)::timestamptz,
549 sqlc.narg(canceled_at)::timestamptz,
550 sqlc.arg(last_webhook_event_id)::text,
551 CASE
552 WHEN sqlc.arg(subscription_status)::billing_subscription_status = 'past_due' THEN now()
553 ELSE NULL
554 END,
555 NULL,
556 NULL,
557 NULL
558 )
559 ON CONFLICT (user_id) DO UPDATE
560 SET plan = EXCLUDED.plan,
561 subscription_status = EXCLUDED.subscription_status,
562 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
563 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
564 current_period_start = EXCLUDED.current_period_start,
565 current_period_end = EXCLUDED.current_period_end,
566 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
567 trial_end = EXCLUDED.trial_end,
568 canceled_at = EXCLUDED.canceled_at,
569 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
570 past_due_at = CASE
571 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.past_due_at, now())
572 ELSE NULL
573 END,
574 locked_at = NULL,
575 lock_reason = NULL,
576 grace_until = NULL,
577 updated_at = now()
578 RETURNING *
579 ), user_update AS (
580 UPDATE users
581 SET plan = sqlc.arg(plan)::user_plan,
582 updated_at = now()
583 WHERE id = sqlc.arg(user_id)::bigint
584 RETURNING id
585 )
586 SELECT * FROM state;
587
588 -- name: MarkUserPastDue :one
589 UPDATE user_billing_states
590 SET subscription_status = 'past_due',
591 past_due_at = COALESCE(past_due_at, now()),
592 locked_at = now(),
593 lock_reason = 'past_due',
594 grace_until = sqlc.narg(grace_until)::timestamptz,
595 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
596 updated_at = now()
597 WHERE user_id = sqlc.arg(user_id)::bigint
598 RETURNING *;
599
600 -- name: MarkUserPaymentSucceeded :one
601 WITH state AS (
602 UPDATE user_billing_states
603 SET plan = CASE
604 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'pro'
605 ELSE plan
606 END,
607 subscription_status = CASE
608 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
609 ELSE subscription_status
610 END,
611 past_due_at = CASE
612 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
613 ELSE past_due_at
614 END,
615 locked_at = NULL,
616 lock_reason = NULL,
617 grace_until = NULL,
618 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
619 updated_at = now()
620 WHERE user_id = sqlc.arg(user_id)::bigint
621 RETURNING *
622 ), user_update AS (
623 UPDATE users
624 SET plan = state.plan,
625 updated_at = now()
626 FROM state
627 WHERE users.id = state.user_id
628 RETURNING users.id
629 )
630 SELECT * FROM state;
631
632 -- name: MarkUserCanceled :one
633 WITH state AS (
634 UPDATE user_billing_states
635 SET plan = 'free',
636 subscription_status = 'canceled',
637 canceled_at = COALESCE(canceled_at, now()),
638 locked_at = now(),
639 lock_reason = 'canceled',
640 grace_until = NULL,
641 cancel_at_period_end = false,
642 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
643 updated_at = now()
644 WHERE user_id = sqlc.arg(user_id)::bigint
645 RETURNING *
646 ), user_update AS (
647 UPDATE users
648 SET plan = 'free',
649 updated_at = now()
650 WHERE id = sqlc.arg(user_id)::bigint
651 RETURNING id
652 )
653 SELECT * FROM state;
654
655 -- name: ClearUserBillingLock :one
656 WITH state AS (
657 UPDATE user_billing_states
658 SET plan = CASE
659 WHEN subscription_status = 'canceled' THEN 'free'
660 ELSE plan
661 END,
662 subscription_status = CASE
663 WHEN subscription_status = 'canceled' THEN 'none'
664 ELSE subscription_status
665 END,
666 locked_at = NULL,
667 lock_reason = NULL,
668 grace_until = NULL,
669 updated_at = now()
670 WHERE user_id = $1
671 RETURNING *
672 ), user_update AS (
673 UPDATE users
674 SET plan = state.plan,
675 updated_at = now()
676 FROM state
677 WHERE users.id = state.user_id
678 RETURNING users.id
679 )
680 SELECT * FROM state;
681