shithub Public

Watch 1 Fork 0 Star 0
MySQL · 27892 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 -- PRO08 D1: never unconditionally NULL the lock columns.
84 -- past_due -> preserve any existing lock (MarkPastDue
85 -- sets fresh grace_until on the invoice.payment_failed
86 -- path; if that hasn't arrived yet, leave NULL).
87 -- active / trialing recovering from past_due/unpaid -> clear.
88 -- any other transition -> preserve existing values.
89 locked_at = CASE
90 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(org_billing_states.locked_at, now())
91 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
92 AND org_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
93 ELSE org_billing_states.locked_at
94 END,
95 lock_reason = CASE
96 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(org_billing_states.lock_reason, 'past_due'::billing_lock_reason)
97 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
98 AND org_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
99 ELSE org_billing_states.lock_reason
100 END,
101 grace_until = CASE
102 WHEN EXCLUDED.subscription_status = 'past_due' THEN org_billing_states.grace_until
103 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
104 AND org_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
105 ELSE org_billing_states.grace_until
106 END,
107 updated_at = now()
108 RETURNING *
109 ), org_update AS (
110 UPDATE orgs
111 SET plan = sqlc.arg(plan)::org_plan,
112 updated_at = now()
113 WHERE id = sqlc.arg(org_id)::bigint
114 RETURNING id
115 )
116 SELECT * FROM state;
117
118 -- name: MarkPastDue :one
119 UPDATE org_billing_states
120 SET subscription_status = 'past_due',
121 past_due_at = COALESCE(past_due_at, now()),
122 locked_at = now(),
123 lock_reason = 'past_due',
124 grace_until = sqlc.narg(grace_until)::timestamptz,
125 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
126 updated_at = now()
127 WHERE org_id = sqlc.arg(org_id)::bigint
128 RETURNING *;
129
130 -- name: MarkPaymentSucceeded :one
131 WITH state AS (
132 UPDATE org_billing_states
133 SET plan = CASE
134 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'team'
135 ELSE plan
136 END,
137 subscription_status = CASE
138 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
139 ELSE subscription_status
140 END,
141 past_due_at = CASE
142 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
143 ELSE past_due_at
144 END,
145 locked_at = NULL,
146 lock_reason = NULL,
147 grace_until = NULL,
148 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
149 updated_at = now()
150 WHERE org_id = sqlc.arg(org_id)::bigint
151 RETURNING *
152 ), org_update AS (
153 UPDATE orgs
154 SET plan = state.plan,
155 updated_at = now()
156 FROM state
157 WHERE orgs.id = state.org_id
158 RETURNING orgs.id
159 )
160 SELECT * FROM state;
161
162 -- name: MarkCanceled :one
163 WITH state AS (
164 UPDATE org_billing_states
165 SET plan = 'free',
166 subscription_status = 'canceled',
167 canceled_at = COALESCE(canceled_at, now()),
168 locked_at = now(),
169 lock_reason = 'canceled',
170 grace_until = NULL,
171 cancel_at_period_end = false,
172 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
173 updated_at = now()
174 WHERE org_id = sqlc.arg(org_id)::bigint
175 RETURNING *
176 ), org_update AS (
177 UPDATE orgs
178 SET plan = 'free',
179 updated_at = now()
180 WHERE id = sqlc.arg(org_id)::bigint
181 RETURNING id
182 )
183 SELECT * FROM state;
184
185 -- name: ClearBillingLock :one
186 WITH state AS (
187 UPDATE org_billing_states
188 SET plan = CASE
189 WHEN subscription_status = 'canceled' THEN 'free'
190 ELSE plan
191 END,
192 subscription_status = CASE
193 WHEN subscription_status = 'canceled' THEN 'none'
194 ELSE subscription_status
195 END,
196 locked_at = NULL,
197 lock_reason = NULL,
198 grace_until = NULL,
199 updated_at = now()
200 WHERE org_id = $1
201 RETURNING *
202 ), org_update AS (
203 UPDATE orgs
204 SET plan = state.plan,
205 updated_at = now()
206 FROM state
207 WHERE orgs.id = state.org_id
208 RETURNING orgs.id
209 )
210 SELECT * FROM state;
211
212 -- ─── billing_seat_snapshots ────────────────────────────────────────
213
214 -- name: CreateSeatSnapshot :one
215 WITH snapshot AS (
216 INSERT INTO billing_seat_snapshots (
217 org_id,
218 provider,
219 stripe_subscription_id,
220 active_members,
221 billable_seats,
222 source
223 )
224 VALUES (
225 sqlc.arg(org_id)::bigint,
226 'stripe',
227 sqlc.narg(stripe_subscription_id)::text,
228 sqlc.arg(active_members)::integer,
229 sqlc.arg(billable_seats)::integer,
230 sqlc.arg(source)::text
231 )
232 RETURNING *
233 ), state AS (
234 INSERT INTO org_billing_states (org_id, billable_seats, seat_snapshot_at)
235 SELECT org_id, billable_seats, captured_at FROM snapshot
236 ON CONFLICT (org_id) DO UPDATE
237 SET billable_seats = EXCLUDED.billable_seats,
238 seat_snapshot_at = EXCLUDED.seat_snapshot_at,
239 updated_at = now()
240 RETURNING org_id
241 )
242 SELECT * FROM snapshot;
243
244 -- name: ListSeatSnapshotsForOrg :many
245 SELECT * FROM billing_seat_snapshots
246 WHERE org_id = $1
247 ORDER BY captured_at DESC, id DESC
248 LIMIT $2;
249
250 -- name: CountBillableOrgMembers :one
251 SELECT count(*)::integer
252 FROM org_members
253 WHERE org_id = $1;
254
255 -- name: CountPendingOrgInvitations :one
256 SELECT count(*)::integer
257 FROM org_invitations
258 WHERE org_id = $1
259 AND accepted_at IS NULL
260 AND declined_at IS NULL
261 AND canceled_at IS NULL
262 AND expires_at > now();
263
264 -- ─── billing_invoices ──────────────────────────────────────────────
265
266 -- name: UpsertInvoice :one
267 -- PRO03: writes both legacy `org_id` and polymorphic
268 -- `(subject_kind, subject_id)`. Callers continue to bind org_id only;
269 -- the subject columns are derived. After PRO04 migrates all callers
270 -- to the polymorphic shape, a follow-up migration drops `org_id` and
271 -- this query loses the legacy column from its INSERT list.
272 INSERT INTO billing_invoices (
273 org_id,
274 subject_kind,
275 subject_id,
276 provider,
277 stripe_invoice_id,
278 stripe_customer_id,
279 stripe_subscription_id,
280 status,
281 number,
282 currency,
283 amount_due_cents,
284 amount_paid_cents,
285 amount_remaining_cents,
286 hosted_invoice_url,
287 invoice_pdf_url,
288 period_start,
289 period_end,
290 due_at,
291 paid_at,
292 voided_at
293 )
294 VALUES (
295 sqlc.arg(org_id)::bigint,
296 'org'::billing_subject_kind,
297 sqlc.arg(org_id)::bigint,
298 'stripe',
299 sqlc.arg(stripe_invoice_id)::text,
300 sqlc.arg(stripe_customer_id)::text,
301 sqlc.narg(stripe_subscription_id)::text,
302 sqlc.arg(status)::billing_invoice_status,
303 sqlc.arg(number)::text,
304 sqlc.arg(currency)::text,
305 sqlc.arg(amount_due_cents)::bigint,
306 sqlc.arg(amount_paid_cents)::bigint,
307 sqlc.arg(amount_remaining_cents)::bigint,
308 sqlc.arg(hosted_invoice_url)::text,
309 sqlc.arg(invoice_pdf_url)::text,
310 sqlc.narg(period_start)::timestamptz,
311 sqlc.narg(period_end)::timestamptz,
312 sqlc.narg(due_at)::timestamptz,
313 sqlc.narg(paid_at)::timestamptz,
314 sqlc.narg(voided_at)::timestamptz
315 )
316 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
317 SET org_id = EXCLUDED.org_id,
318 stripe_customer_id = EXCLUDED.stripe_customer_id,
319 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
320 status = EXCLUDED.status,
321 number = EXCLUDED.number,
322 currency = EXCLUDED.currency,
323 amount_due_cents = EXCLUDED.amount_due_cents,
324 amount_paid_cents = EXCLUDED.amount_paid_cents,
325 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
326 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
327 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
328 period_start = EXCLUDED.period_start,
329 period_end = EXCLUDED.period_end,
330 due_at = EXCLUDED.due_at,
331 paid_at = EXCLUDED.paid_at,
332 voided_at = EXCLUDED.voided_at,
333 updated_at = now()
334 RETURNING *;
335
336 -- name: ListInvoicesForOrg :many
337 -- PRO03: filters on the polymorphic subject columns so the index
338 -- billing_invoices_subject_created_idx services this query. The
339 -- legacy `org_id` column is kept populated by UpsertInvoice for the
340 -- transitional window; this query no longer reads it.
341 SELECT * FROM billing_invoices
342 WHERE subject_kind = 'org' AND subject_id = $1
343 ORDER BY created_at DESC, id DESC
344 LIMIT $2;
345
346 -- name: ListInvoicesForSubject :many
347 -- Polymorphic invoice listing for PRO04+ callers. The org-flavored
348 -- ListInvoicesForOrg above is the same query with subject_kind
349 -- hard-coded; this surface lets a user-side caller pass kind='user'
350 -- without forking the helper.
351 SELECT * FROM billing_invoices
352 WHERE subject_kind = sqlc.arg(subject_kind)::billing_subject_kind
353 AND subject_id = sqlc.arg(subject_id)::bigint
354 ORDER BY created_at DESC, id DESC
355 LIMIT sqlc.arg(lim)::integer;
356
357 -- name: UpsertInvoiceForSubject :one
358 -- PRO04 polymorphic invoice upsert. Writes (subject_kind,
359 -- subject_id) directly; org_id stays NULL for user-kind rows (per
360 -- the 0074 migration's nullable change). The existing
361 -- UpsertInvoice query stays as the org-kind path during the
362 -- transitional deploy — both can coexist because the UNIQUE
363 -- (provider, stripe_invoice_id) prevents duplicate rows.
364 INSERT INTO billing_invoices (
365 subject_kind,
366 subject_id,
367 provider,
368 stripe_invoice_id,
369 stripe_customer_id,
370 stripe_subscription_id,
371 status,
372 number,
373 currency,
374 amount_due_cents,
375 amount_paid_cents,
376 amount_remaining_cents,
377 hosted_invoice_url,
378 invoice_pdf_url,
379 period_start,
380 period_end,
381 due_at,
382 paid_at,
383 voided_at
384 )
385 VALUES (
386 sqlc.arg(subject_kind)::billing_subject_kind,
387 sqlc.arg(subject_id)::bigint,
388 'stripe',
389 sqlc.arg(stripe_invoice_id)::text,
390 sqlc.arg(stripe_customer_id)::text,
391 sqlc.narg(stripe_subscription_id)::text,
392 sqlc.arg(status)::billing_invoice_status,
393 sqlc.arg(number)::text,
394 sqlc.arg(currency)::text,
395 sqlc.arg(amount_due_cents)::bigint,
396 sqlc.arg(amount_paid_cents)::bigint,
397 sqlc.arg(amount_remaining_cents)::bigint,
398 sqlc.arg(hosted_invoice_url)::text,
399 sqlc.arg(invoice_pdf_url)::text,
400 sqlc.narg(period_start)::timestamptz,
401 sqlc.narg(period_end)::timestamptz,
402 sqlc.narg(due_at)::timestamptz,
403 sqlc.narg(paid_at)::timestamptz,
404 sqlc.narg(voided_at)::timestamptz
405 )
406 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
407 SET subject_kind = EXCLUDED.subject_kind,
408 subject_id = EXCLUDED.subject_id,
409 stripe_customer_id = EXCLUDED.stripe_customer_id,
410 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
411 status = EXCLUDED.status,
412 number = EXCLUDED.number,
413 currency = EXCLUDED.currency,
414 amount_due_cents = EXCLUDED.amount_due_cents,
415 amount_paid_cents = EXCLUDED.amount_paid_cents,
416 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
417 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
418 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
419 period_start = EXCLUDED.period_start,
420 period_end = EXCLUDED.period_end,
421 due_at = EXCLUDED.due_at,
422 paid_at = EXCLUDED.paid_at,
423 voided_at = EXCLUDED.voided_at,
424 updated_at = now()
425 RETURNING *;
426
427 -- ─── billing_webhook_events ────────────────────────────────────────
428
429 -- name: CreateWebhookEventReceipt :one
430 INSERT INTO billing_webhook_events (
431 provider,
432 provider_event_id,
433 event_type,
434 api_version,
435 payload
436 )
437 VALUES (
438 'stripe',
439 sqlc.arg(provider_event_id)::text,
440 sqlc.arg(event_type)::text,
441 sqlc.arg(api_version)::text,
442 sqlc.arg(payload)::jsonb
443 )
444 ON CONFLICT (provider, provider_event_id) DO NOTHING
445 RETURNING *;
446
447 -- name: GetWebhookEventReceipt :one
448 SELECT * FROM billing_webhook_events
449 WHERE provider = 'stripe'
450 AND provider_event_id = $1;
451
452 -- name: MarkWebhookEventProcessed :one
453 UPDATE billing_webhook_events
454 SET processed_at = now(),
455 process_error = '',
456 processing_attempts = processing_attempts + 1
457 WHERE provider = 'stripe'
458 AND provider_event_id = $1
459 RETURNING *;
460
461 -- name: MarkWebhookEventFailed :one
462 UPDATE billing_webhook_events
463 SET process_error = $2,
464 processing_attempts = processing_attempts + 1
465 WHERE provider = 'stripe'
466 AND provider_event_id = $1
467 RETURNING *;
468
469 -- name: SetWebhookEventSubject :exec
470 -- Records the resolved subject on the receipt row after a successful
471 -- subject-resolution step. Called from the apply path before guard +
472 -- state mutation so the receipt carries the audit trail even if the
473 -- subsequent apply fails. Migration 0075's CHECK constraint enforces
474 -- both-or-neither; callers must pass a non-zero subject.
475 UPDATE billing_webhook_events
476 SET subject_kind = sqlc.arg(subject_kind)::billing_subject_kind,
477 subject_id = sqlc.arg(subject_id)::bigint
478 WHERE provider = 'stripe'
479 AND provider_event_id = sqlc.arg(provider_event_id)::text;
480
481 -- name: IsOrgBillingEventStale :one
482 -- PRO08 D4: returns true when an incoming Stripe event's timestamp
483 -- is older than the last event we've already applied for this org.
484 -- Stripe doesn't guarantee delivery order across retries; without
485 -- this guard a stale `subscription.updated[active]` could re-activate
486 -- a canceled subscription. Returns false when no prior event has
487 -- been recorded (last_event_at IS NULL) — the first event is never
488 -- stale.
489 SELECT COALESCE(last_event_at > sqlc.arg(event_at)::timestamptz, false)::boolean AS stale
490 FROM org_billing_states
491 WHERE org_id = sqlc.arg(org_id)::bigint;
492
493 -- name: IsUserBillingEventStale :one
494 SELECT COALESCE(last_event_at > sqlc.arg(event_at)::timestamptz, false)::boolean AS stale
495 FROM user_billing_states
496 WHERE user_id = sqlc.arg(user_id)::bigint;
497
498 -- name: TouchOrgBillingLastEventAt :exec
499 -- PRO08 D4: bump last_event_at on successful apply. Conditional so
500 -- a fresh apply driven by an out-of-order-but-recent retry doesn't
501 -- regress the timestamp (GREATEST). NULL last_event_at acquires the
502 -- incoming value.
503 UPDATE org_billing_states
504 SET last_event_at = GREATEST(COALESCE(last_event_at, sqlc.arg(event_at)::timestamptz), sqlc.arg(event_at)::timestamptz)
505 WHERE org_id = sqlc.arg(org_id)::bigint;
506
507 -- name: TouchUserBillingLastEventAt :exec
508 UPDATE user_billing_states
509 SET last_event_at = GREATEST(COALESCE(last_event_at, sqlc.arg(event_at)::timestamptz), sqlc.arg(event_at)::timestamptz)
510 WHERE user_id = sqlc.arg(user_id)::bigint;
511
512 -- name: MarkInvoiceRefunded :one
513 -- PRO08 D2: surface a Stripe-side refund in shithub. Stripe leaves
514 -- the invoice.status='paid' after a refund and fires a charge.refunded
515 -- event; this helper flips the shithub-side row to 'refunded' so the
516 -- billing settings UI shows the refunded state.
517 --
518 -- A NULL refunded_at means "no refund seen"; the value is set on the
519 -- first call and preserved on subsequent calls (refund partial → full
520 -- doesn't move the wall-clock timestamp).
521 UPDATE billing_invoices
522 SET status = 'refunded',
523 refunded_at = COALESCE(refunded_at, now()),
524 updated_at = now()
525 WHERE provider = 'stripe'
526 AND stripe_invoice_id = sqlc.arg(stripe_invoice_id)::text
527 RETURNING *;
528
529 -- name: TryAcquireWebhookEventLock :one
530 -- PRO08 A3: transaction-scoped advisory lock keyed on the hash of
531 -- the provider_event_id. Two concurrent webhook deliveries for the
532 -- same event_id race past CreateWebhookEventReceipt before either has
533 -- marked it processed; without serialization, both proceed to apply
534 -- and double-mutate state. This lock makes the apply path mutually
535 -- exclusive per event. Returns true when acquired; false means
536 -- another worker holds it — caller should let Stripe retry.
537 --
538 -- pg_try_advisory_xact_lock takes a bigint; hashtext returns int4
539 -- which sign-extends safely. The lock auto-releases at txn end.
540 SELECT pg_try_advisory_xact_lock(hashtext($1)::bigint) AS acquired;
541
542 -- name: ListFailedWebhookEvents :many
543 -- Operator query for "events we received but failed to process."
544 -- A row is "failed" when it has a non-empty process_error OR when
545 -- it has never been processed (processed_at NULL) and has at least
546 -- one processing attempt. Rows that are merely new and untouched
547 -- (attempts=0, processed_at NULL, no error) are excluded.
548 SELECT id, provider, provider_event_id, event_type, api_version,
549 received_at, processed_at, processing_attempts, process_error,
550 subject_kind, subject_id
551 FROM billing_webhook_events
552 WHERE provider = 'stripe'
553 AND (
554 process_error <> ''
555 OR (processed_at IS NULL AND processing_attempts > 0)
556 )
557 ORDER BY received_at DESC
558 LIMIT $1;
559
560 -- ─── user_billing_states (PRO03) ──────────────────────────────────
561
562 -- name: GetUserBillingState :one
563 SELECT * FROM user_billing_states WHERE user_id = $1;
564
565 -- name: GetUserBillingStateByStripeCustomer :one
566 SELECT * FROM user_billing_states
567 WHERE provider = 'stripe'
568 AND stripe_customer_id = $1;
569
570 -- name: GetUserBillingStateByStripeSubscription :one
571 SELECT * FROM user_billing_states
572 WHERE provider = 'stripe'
573 AND stripe_subscription_id = $1;
574
575 -- name: SetUserStripeCustomer :one
576 INSERT INTO user_billing_states (user_id, provider, stripe_customer_id)
577 VALUES ($1, 'stripe', $2)
578 ON CONFLICT (user_id) DO UPDATE
579 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
580 provider = 'stripe',
581 updated_at = now()
582 RETURNING *;
583
584 -- name: ApplyUserSubscriptionSnapshot :one
585 -- Mirrors ApplySubscriptionSnapshot for orgs minus the seat columns
586 -- and with `user_plan` as the plan enum. The same CTE pattern keeps
587 -- users.plan and user_billing_states.plan atomic.
588 WITH state AS (
589 INSERT INTO user_billing_states (
590 user_id,
591 provider,
592 plan,
593 subscription_status,
594 stripe_subscription_id,
595 stripe_subscription_item_id,
596 current_period_start,
597 current_period_end,
598 cancel_at_period_end,
599 trial_end,
600 canceled_at,
601 last_webhook_event_id,
602 past_due_at,
603 locked_at,
604 lock_reason,
605 grace_until
606 )
607 VALUES (
608 sqlc.arg(user_id)::bigint,
609 'stripe',
610 sqlc.arg(plan)::user_plan,
611 sqlc.arg(subscription_status)::billing_subscription_status,
612 sqlc.narg(stripe_subscription_id)::text,
613 sqlc.narg(stripe_subscription_item_id)::text,
614 sqlc.narg(current_period_start)::timestamptz,
615 sqlc.narg(current_period_end)::timestamptz,
616 sqlc.arg(cancel_at_period_end)::boolean,
617 sqlc.narg(trial_end)::timestamptz,
618 sqlc.narg(canceled_at)::timestamptz,
619 sqlc.arg(last_webhook_event_id)::text,
620 CASE
621 WHEN sqlc.arg(subscription_status)::billing_subscription_status = 'past_due' THEN now()
622 ELSE NULL
623 END,
624 NULL,
625 NULL,
626 NULL
627 )
628 ON CONFLICT (user_id) DO UPDATE
629 SET plan = EXCLUDED.plan,
630 subscription_status = EXCLUDED.subscription_status,
631 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
632 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
633 current_period_start = EXCLUDED.current_period_start,
634 current_period_end = EXCLUDED.current_period_end,
635 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
636 trial_end = EXCLUDED.trial_end,
637 canceled_at = EXCLUDED.canceled_at,
638 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
639 past_due_at = CASE
640 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.past_due_at, now())
641 ELSE NULL
642 END,
643 -- PRO08 D1: never unconditionally NULL the lock columns
644 -- (mirror of the org-side fix). The Mark* paths own
645 -- transitions into/out of the locked state.
646 locked_at = CASE
647 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.locked_at, now())
648 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
649 AND user_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
650 ELSE user_billing_states.locked_at
651 END,
652 lock_reason = CASE
653 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.lock_reason, 'past_due'::billing_lock_reason)
654 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
655 AND user_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
656 ELSE user_billing_states.lock_reason
657 END,
658 grace_until = CASE
659 WHEN EXCLUDED.subscription_status = 'past_due' THEN user_billing_states.grace_until
660 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
661 AND user_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
662 ELSE user_billing_states.grace_until
663 END,
664 updated_at = now()
665 RETURNING *
666 ), user_update AS (
667 UPDATE users
668 SET plan = sqlc.arg(plan)::user_plan,
669 updated_at = now()
670 WHERE id = sqlc.arg(user_id)::bigint
671 RETURNING id
672 )
673 SELECT * FROM state;
674
675 -- name: MarkUserPastDue :one
676 UPDATE user_billing_states
677 SET subscription_status = 'past_due',
678 past_due_at = COALESCE(past_due_at, now()),
679 locked_at = now(),
680 lock_reason = 'past_due',
681 grace_until = sqlc.narg(grace_until)::timestamptz,
682 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
683 updated_at = now()
684 WHERE user_id = sqlc.arg(user_id)::bigint
685 RETURNING *;
686
687 -- name: MarkUserPaymentSucceeded :one
688 WITH state AS (
689 UPDATE user_billing_states
690 SET plan = CASE
691 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'pro'
692 ELSE plan
693 END,
694 subscription_status = CASE
695 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
696 ELSE subscription_status
697 END,
698 past_due_at = CASE
699 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
700 ELSE past_due_at
701 END,
702 locked_at = NULL,
703 lock_reason = NULL,
704 grace_until = NULL,
705 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
706 updated_at = now()
707 WHERE user_id = sqlc.arg(user_id)::bigint
708 RETURNING *
709 ), user_update AS (
710 UPDATE users
711 SET plan = state.plan,
712 updated_at = now()
713 FROM state
714 WHERE users.id = state.user_id
715 RETURNING users.id
716 )
717 SELECT * FROM state;
718
719 -- name: MarkUserCanceled :one
720 WITH state AS (
721 UPDATE user_billing_states
722 SET plan = 'free',
723 subscription_status = 'canceled',
724 canceled_at = COALESCE(canceled_at, now()),
725 locked_at = now(),
726 lock_reason = 'canceled',
727 grace_until = NULL,
728 cancel_at_period_end = false,
729 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
730 updated_at = now()
731 WHERE user_id = sqlc.arg(user_id)::bigint
732 RETURNING *
733 ), user_update AS (
734 UPDATE users
735 SET plan = 'free',
736 updated_at = now()
737 WHERE id = sqlc.arg(user_id)::bigint
738 RETURNING id
739 )
740 SELECT * FROM state;
741
742 -- name: ClearUserBillingLock :one
743 WITH state AS (
744 UPDATE user_billing_states
745 SET plan = CASE
746 WHEN subscription_status = 'canceled' THEN 'free'
747 ELSE plan
748 END,
749 subscription_status = CASE
750 WHEN subscription_status = 'canceled' THEN 'none'
751 ELSE subscription_status
752 END,
753 locked_at = NULL,
754 lock_reason = NULL,
755 grace_until = NULL,
756 updated_at = now()
757 WHERE user_id = $1
758 RETURNING *
759 ), user_update AS (
760 UPDATE users
761 SET plan = state.plan,
762 updated_at = now()
763 FROM state
764 WHERE users.id = state.user_id
765 RETURNING users.id
766 )
767 SELECT * FROM state;
768