shithub Public

Watch 1 Fork 0 Star 0
Go · 71751 bytes Raw Blame History
1 // Code generated by sqlc. DO NOT EDIT.
2 // versions:
3 // sqlc v1.31.1
4 // source: billing.sql
5
6 package billingdb
7
8 import (
9 "context"
10
11 "github.com/jackc/pgx/v5/pgtype"
12 )
13
14 const applySubscriptionSnapshot = `-- name: ApplySubscriptionSnapshot :one
15 WITH state AS (
16 INSERT INTO org_billing_states (
17 org_id,
18 provider,
19 plan,
20 subscription_status,
21 stripe_subscription_id,
22 stripe_subscription_item_id,
23 current_period_start,
24 current_period_end,
25 cancel_at_period_end,
26 trial_end,
27 canceled_at,
28 last_webhook_event_id,
29 past_due_at,
30 locked_at,
31 lock_reason,
32 grace_until
33 )
34 VALUES (
35 $1::bigint,
36 'stripe',
37 $2::org_plan,
38 $3::billing_subscription_status,
39 $4::text,
40 $5::text,
41 $6::timestamptz,
42 $7::timestamptz,
43 $8::boolean,
44 $9::timestamptz,
45 $10::timestamptz,
46 $11::text,
47 CASE
48 WHEN $3::billing_subscription_status = 'past_due' THEN now()
49 ELSE NULL
50 END,
51 NULL,
52 NULL,
53 NULL
54 )
55 ON CONFLICT (org_id) DO UPDATE
56 SET plan = EXCLUDED.plan,
57 subscription_status = EXCLUDED.subscription_status,
58 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
59 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
60 current_period_start = EXCLUDED.current_period_start,
61 current_period_end = EXCLUDED.current_period_end,
62 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
63 trial_end = EXCLUDED.trial_end,
64 canceled_at = EXCLUDED.canceled_at,
65 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
66 past_due_at = CASE
67 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(org_billing_states.past_due_at, now())
68 ELSE NULL
69 END,
70 -- PRO08 D1: never unconditionally NULL the lock columns.
71 -- past_due -> preserve any existing lock (MarkPastDue
72 -- sets fresh grace_until on the invoice.payment_failed
73 -- path; if that hasn't arrived yet, leave NULL).
74 -- active / trialing recovering from past_due/unpaid -> clear.
75 -- any other transition -> preserve existing values.
76 locked_at = CASE
77 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(org_billing_states.locked_at, now())
78 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
79 AND org_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
80 ELSE org_billing_states.locked_at
81 END,
82 lock_reason = CASE
83 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(org_billing_states.lock_reason, 'past_due'::billing_lock_reason)
84 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
85 AND org_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
86 ELSE org_billing_states.lock_reason
87 END,
88 grace_until = CASE
89 WHEN EXCLUDED.subscription_status = 'past_due' THEN org_billing_states.grace_until
90 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
91 AND org_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
92 ELSE org_billing_states.grace_until
93 END,
94 updated_at = now()
95 RETURNING org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
96 ), org_update AS (
97 UPDATE orgs
98 SET plan = $2::org_plan,
99 updated_at = now()
100 WHERE id = $1::bigint
101 RETURNING id
102 )
103 SELECT org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
104 `
105
106 type ApplySubscriptionSnapshotParams struct {
107 OrgID int64
108 Plan OrgPlan
109 SubscriptionStatus BillingSubscriptionStatus
110 StripeSubscriptionID pgtype.Text
111 StripeSubscriptionItemID pgtype.Text
112 CurrentPeriodStart pgtype.Timestamptz
113 CurrentPeriodEnd pgtype.Timestamptz
114 CancelAtPeriodEnd bool
115 TrialEnd pgtype.Timestamptz
116 CanceledAt pgtype.Timestamptz
117 LastWebhookEventID string
118 }
119
120 type ApplySubscriptionSnapshotRow struct {
121 OrgID int64
122 Provider BillingProvider
123 StripeCustomerID pgtype.Text
124 StripeSubscriptionID pgtype.Text
125 StripeSubscriptionItemID pgtype.Text
126 Plan OrgPlan
127 SubscriptionStatus BillingSubscriptionStatus
128 BillableSeats int32
129 SeatSnapshotAt pgtype.Timestamptz
130 CurrentPeriodStart pgtype.Timestamptz
131 CurrentPeriodEnd pgtype.Timestamptz
132 CancelAtPeriodEnd bool
133 TrialEnd pgtype.Timestamptz
134 PastDueAt pgtype.Timestamptz
135 CanceledAt pgtype.Timestamptz
136 LockedAt pgtype.Timestamptz
137 LockReason NullBillingLockReason
138 GraceUntil pgtype.Timestamptz
139 LastWebhookEventID string
140 CreatedAt pgtype.Timestamptz
141 UpdatedAt pgtype.Timestamptz
142 LastEventAt pgtype.Timestamptz
143 }
144
145 func (q *Queries) ApplySubscriptionSnapshot(ctx context.Context, db DBTX, arg ApplySubscriptionSnapshotParams) (ApplySubscriptionSnapshotRow, error) {
146 row := db.QueryRow(ctx, applySubscriptionSnapshot,
147 arg.OrgID,
148 arg.Plan,
149 arg.SubscriptionStatus,
150 arg.StripeSubscriptionID,
151 arg.StripeSubscriptionItemID,
152 arg.CurrentPeriodStart,
153 arg.CurrentPeriodEnd,
154 arg.CancelAtPeriodEnd,
155 arg.TrialEnd,
156 arg.CanceledAt,
157 arg.LastWebhookEventID,
158 )
159 var i ApplySubscriptionSnapshotRow
160 err := row.Scan(
161 &i.OrgID,
162 &i.Provider,
163 &i.StripeCustomerID,
164 &i.StripeSubscriptionID,
165 &i.StripeSubscriptionItemID,
166 &i.Plan,
167 &i.SubscriptionStatus,
168 &i.BillableSeats,
169 &i.SeatSnapshotAt,
170 &i.CurrentPeriodStart,
171 &i.CurrentPeriodEnd,
172 &i.CancelAtPeriodEnd,
173 &i.TrialEnd,
174 &i.PastDueAt,
175 &i.CanceledAt,
176 &i.LockedAt,
177 &i.LockReason,
178 &i.GraceUntil,
179 &i.LastWebhookEventID,
180 &i.CreatedAt,
181 &i.UpdatedAt,
182 &i.LastEventAt,
183 )
184 return i, err
185 }
186
187 const applyUserSubscriptionSnapshot = `-- name: ApplyUserSubscriptionSnapshot :one
188 WITH state AS (
189 INSERT INTO user_billing_states (
190 user_id,
191 provider,
192 plan,
193 subscription_status,
194 stripe_subscription_id,
195 stripe_subscription_item_id,
196 current_period_start,
197 current_period_end,
198 cancel_at_period_end,
199 trial_end,
200 canceled_at,
201 last_webhook_event_id,
202 past_due_at,
203 locked_at,
204 lock_reason,
205 grace_until
206 )
207 VALUES (
208 $1::bigint,
209 'stripe',
210 $2::user_plan,
211 $3::billing_subscription_status,
212 $4::text,
213 $5::text,
214 $6::timestamptz,
215 $7::timestamptz,
216 $8::boolean,
217 $9::timestamptz,
218 $10::timestamptz,
219 $11::text,
220 CASE
221 WHEN $3::billing_subscription_status = 'past_due' THEN now()
222 ELSE NULL
223 END,
224 NULL,
225 NULL,
226 NULL
227 )
228 ON CONFLICT (user_id) DO UPDATE
229 SET plan = EXCLUDED.plan,
230 subscription_status = EXCLUDED.subscription_status,
231 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
232 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
233 current_period_start = EXCLUDED.current_period_start,
234 current_period_end = EXCLUDED.current_period_end,
235 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
236 trial_end = EXCLUDED.trial_end,
237 canceled_at = EXCLUDED.canceled_at,
238 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
239 past_due_at = CASE
240 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.past_due_at, now())
241 ELSE NULL
242 END,
243 -- PRO08 D1: never unconditionally NULL the lock columns
244 -- (mirror of the org-side fix). The Mark* paths own
245 -- transitions into/out of the locked state.
246 locked_at = CASE
247 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.locked_at, now())
248 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
249 AND user_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
250 ELSE user_billing_states.locked_at
251 END,
252 lock_reason = CASE
253 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.lock_reason, 'past_due'::billing_lock_reason)
254 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
255 AND user_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
256 ELSE user_billing_states.lock_reason
257 END,
258 grace_until = CASE
259 WHEN EXCLUDED.subscription_status = 'past_due' THEN user_billing_states.grace_until
260 WHEN EXCLUDED.subscription_status IN ('active', 'trialing')
261 AND user_billing_states.subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
262 ELSE user_billing_states.grace_until
263 END,
264 updated_at = now()
265 RETURNING user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
266 ), user_update AS (
267 UPDATE users
268 SET plan = $2::user_plan,
269 updated_at = now()
270 WHERE id = $1::bigint
271 RETURNING id
272 )
273 SELECT user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
274 `
275
276 type ApplyUserSubscriptionSnapshotParams struct {
277 UserID int64
278 Plan UserPlan
279 SubscriptionStatus BillingSubscriptionStatus
280 StripeSubscriptionID pgtype.Text
281 StripeSubscriptionItemID pgtype.Text
282 CurrentPeriodStart pgtype.Timestamptz
283 CurrentPeriodEnd pgtype.Timestamptz
284 CancelAtPeriodEnd bool
285 TrialEnd pgtype.Timestamptz
286 CanceledAt pgtype.Timestamptz
287 LastWebhookEventID string
288 }
289
290 type ApplyUserSubscriptionSnapshotRow struct {
291 UserID int64
292 Provider BillingProvider
293 StripeCustomerID pgtype.Text
294 StripeSubscriptionID pgtype.Text
295 StripeSubscriptionItemID pgtype.Text
296 Plan UserPlan
297 SubscriptionStatus BillingSubscriptionStatus
298 CurrentPeriodStart pgtype.Timestamptz
299 CurrentPeriodEnd pgtype.Timestamptz
300 CancelAtPeriodEnd bool
301 TrialEnd pgtype.Timestamptz
302 PastDueAt pgtype.Timestamptz
303 CanceledAt pgtype.Timestamptz
304 LockedAt pgtype.Timestamptz
305 LockReason NullBillingLockReason
306 GraceUntil pgtype.Timestamptz
307 LastWebhookEventID string
308 CreatedAt pgtype.Timestamptz
309 UpdatedAt pgtype.Timestamptz
310 LastEventAt pgtype.Timestamptz
311 }
312
313 // Mirrors ApplySubscriptionSnapshot for orgs minus the seat columns
314 // and with `user_plan` as the plan enum. The same CTE pattern keeps
315 // users.plan and user_billing_states.plan atomic.
316 func (q *Queries) ApplyUserSubscriptionSnapshot(ctx context.Context, db DBTX, arg ApplyUserSubscriptionSnapshotParams) (ApplyUserSubscriptionSnapshotRow, error) {
317 row := db.QueryRow(ctx, applyUserSubscriptionSnapshot,
318 arg.UserID,
319 arg.Plan,
320 arg.SubscriptionStatus,
321 arg.StripeSubscriptionID,
322 arg.StripeSubscriptionItemID,
323 arg.CurrentPeriodStart,
324 arg.CurrentPeriodEnd,
325 arg.CancelAtPeriodEnd,
326 arg.TrialEnd,
327 arg.CanceledAt,
328 arg.LastWebhookEventID,
329 )
330 var i ApplyUserSubscriptionSnapshotRow
331 err := row.Scan(
332 &i.UserID,
333 &i.Provider,
334 &i.StripeCustomerID,
335 &i.StripeSubscriptionID,
336 &i.StripeSubscriptionItemID,
337 &i.Plan,
338 &i.SubscriptionStatus,
339 &i.CurrentPeriodStart,
340 &i.CurrentPeriodEnd,
341 &i.CancelAtPeriodEnd,
342 &i.TrialEnd,
343 &i.PastDueAt,
344 &i.CanceledAt,
345 &i.LockedAt,
346 &i.LockReason,
347 &i.GraceUntil,
348 &i.LastWebhookEventID,
349 &i.CreatedAt,
350 &i.UpdatedAt,
351 &i.LastEventAt,
352 )
353 return i, err
354 }
355
356 const clearBillingLock = `-- name: ClearBillingLock :one
357 WITH state AS (
358 UPDATE org_billing_states
359 SET plan = CASE
360 WHEN subscription_status = 'canceled' THEN 'free'
361 ELSE plan
362 END,
363 subscription_status = CASE
364 WHEN subscription_status = 'canceled' THEN 'none'
365 ELSE subscription_status
366 END,
367 locked_at = NULL,
368 lock_reason = NULL,
369 grace_until = NULL,
370 updated_at = now()
371 WHERE org_id = $1
372 RETURNING org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
373 ), org_update AS (
374 UPDATE orgs
375 SET plan = state.plan,
376 updated_at = now()
377 FROM state
378 WHERE orgs.id = state.org_id
379 RETURNING orgs.id
380 )
381 SELECT org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
382 `
383
384 type ClearBillingLockRow struct {
385 OrgID int64
386 Provider BillingProvider
387 StripeCustomerID pgtype.Text
388 StripeSubscriptionID pgtype.Text
389 StripeSubscriptionItemID pgtype.Text
390 Plan OrgPlan
391 SubscriptionStatus BillingSubscriptionStatus
392 BillableSeats int32
393 SeatSnapshotAt pgtype.Timestamptz
394 CurrentPeriodStart pgtype.Timestamptz
395 CurrentPeriodEnd pgtype.Timestamptz
396 CancelAtPeriodEnd bool
397 TrialEnd pgtype.Timestamptz
398 PastDueAt pgtype.Timestamptz
399 CanceledAt pgtype.Timestamptz
400 LockedAt pgtype.Timestamptz
401 LockReason NullBillingLockReason
402 GraceUntil pgtype.Timestamptz
403 LastWebhookEventID string
404 CreatedAt pgtype.Timestamptz
405 UpdatedAt pgtype.Timestamptz
406 LastEventAt pgtype.Timestamptz
407 }
408
409 func (q *Queries) ClearBillingLock(ctx context.Context, db DBTX, orgID int64) (ClearBillingLockRow, error) {
410 row := db.QueryRow(ctx, clearBillingLock, orgID)
411 var i ClearBillingLockRow
412 err := row.Scan(
413 &i.OrgID,
414 &i.Provider,
415 &i.StripeCustomerID,
416 &i.StripeSubscriptionID,
417 &i.StripeSubscriptionItemID,
418 &i.Plan,
419 &i.SubscriptionStatus,
420 &i.BillableSeats,
421 &i.SeatSnapshotAt,
422 &i.CurrentPeriodStart,
423 &i.CurrentPeriodEnd,
424 &i.CancelAtPeriodEnd,
425 &i.TrialEnd,
426 &i.PastDueAt,
427 &i.CanceledAt,
428 &i.LockedAt,
429 &i.LockReason,
430 &i.GraceUntil,
431 &i.LastWebhookEventID,
432 &i.CreatedAt,
433 &i.UpdatedAt,
434 &i.LastEventAt,
435 )
436 return i, err
437 }
438
439 const clearUserBillingLock = `-- name: ClearUserBillingLock :one
440 WITH state AS (
441 UPDATE user_billing_states
442 SET plan = CASE
443 WHEN subscription_status = 'canceled' THEN 'free'
444 ELSE plan
445 END,
446 subscription_status = CASE
447 WHEN subscription_status = 'canceled' THEN 'none'
448 ELSE subscription_status
449 END,
450 locked_at = NULL,
451 lock_reason = NULL,
452 grace_until = NULL,
453 updated_at = now()
454 WHERE user_id = $1
455 RETURNING user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
456 ), user_update AS (
457 UPDATE users
458 SET plan = state.plan,
459 updated_at = now()
460 FROM state
461 WHERE users.id = state.user_id
462 RETURNING users.id
463 )
464 SELECT user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
465 `
466
467 type ClearUserBillingLockRow struct {
468 UserID int64
469 Provider BillingProvider
470 StripeCustomerID pgtype.Text
471 StripeSubscriptionID pgtype.Text
472 StripeSubscriptionItemID pgtype.Text
473 Plan UserPlan
474 SubscriptionStatus BillingSubscriptionStatus
475 CurrentPeriodStart pgtype.Timestamptz
476 CurrentPeriodEnd pgtype.Timestamptz
477 CancelAtPeriodEnd bool
478 TrialEnd pgtype.Timestamptz
479 PastDueAt pgtype.Timestamptz
480 CanceledAt pgtype.Timestamptz
481 LockedAt pgtype.Timestamptz
482 LockReason NullBillingLockReason
483 GraceUntil pgtype.Timestamptz
484 LastWebhookEventID string
485 CreatedAt pgtype.Timestamptz
486 UpdatedAt pgtype.Timestamptz
487 LastEventAt pgtype.Timestamptz
488 }
489
490 func (q *Queries) ClearUserBillingLock(ctx context.Context, db DBTX, userID int64) (ClearUserBillingLockRow, error) {
491 row := db.QueryRow(ctx, clearUserBillingLock, userID)
492 var i ClearUserBillingLockRow
493 err := row.Scan(
494 &i.UserID,
495 &i.Provider,
496 &i.StripeCustomerID,
497 &i.StripeSubscriptionID,
498 &i.StripeSubscriptionItemID,
499 &i.Plan,
500 &i.SubscriptionStatus,
501 &i.CurrentPeriodStart,
502 &i.CurrentPeriodEnd,
503 &i.CancelAtPeriodEnd,
504 &i.TrialEnd,
505 &i.PastDueAt,
506 &i.CanceledAt,
507 &i.LockedAt,
508 &i.LockReason,
509 &i.GraceUntil,
510 &i.LastWebhookEventID,
511 &i.CreatedAt,
512 &i.UpdatedAt,
513 &i.LastEventAt,
514 )
515 return i, err
516 }
517
518 const countBillableOrgMembers = `-- name: CountBillableOrgMembers :one
519 SELECT count(*)::integer
520 FROM org_members
521 WHERE org_id = $1
522 `
523
524 func (q *Queries) CountBillableOrgMembers(ctx context.Context, db DBTX, orgID int64) (int32, error) {
525 row := db.QueryRow(ctx, countBillableOrgMembers, orgID)
526 var column_1 int32
527 err := row.Scan(&column_1)
528 return column_1, err
529 }
530
531 const countPendingOrgInvitations = `-- name: CountPendingOrgInvitations :one
532 SELECT count(*)::integer
533 FROM org_invitations
534 WHERE org_id = $1
535 AND accepted_at IS NULL
536 AND declined_at IS NULL
537 AND canceled_at IS NULL
538 AND expires_at > now()
539 `
540
541 func (q *Queries) CountPendingOrgInvitations(ctx context.Context, db DBTX, orgID int64) (int32, error) {
542 row := db.QueryRow(ctx, countPendingOrgInvitations, orgID)
543 var column_1 int32
544 err := row.Scan(&column_1)
545 return column_1, err
546 }
547
548 const createSeatSnapshot = `-- name: CreateSeatSnapshot :one
549
550 WITH snapshot AS (
551 INSERT INTO billing_seat_snapshots (
552 org_id,
553 provider,
554 stripe_subscription_id,
555 active_members,
556 billable_seats,
557 source
558 )
559 VALUES (
560 $1::bigint,
561 'stripe',
562 $2::text,
563 $3::integer,
564 $4::integer,
565 $5::text
566 )
567 RETURNING id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at
568 ), state AS (
569 INSERT INTO org_billing_states (org_id, billable_seats, seat_snapshot_at)
570 SELECT org_id, billable_seats, captured_at FROM snapshot
571 ON CONFLICT (org_id) DO UPDATE
572 SET billable_seats = EXCLUDED.billable_seats,
573 seat_snapshot_at = EXCLUDED.seat_snapshot_at,
574 updated_at = now()
575 RETURNING org_id
576 )
577 SELECT id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at FROM snapshot
578 `
579
580 type CreateSeatSnapshotParams struct {
581 OrgID int64
582 StripeSubscriptionID pgtype.Text
583 ActiveMembers int32
584 BillableSeats int32
585 Source string
586 }
587
588 type CreateSeatSnapshotRow struct {
589 ID int64
590 OrgID int64
591 Provider BillingProvider
592 StripeSubscriptionID pgtype.Text
593 ActiveMembers int32
594 BillableSeats int32
595 Source string
596 CapturedAt pgtype.Timestamptz
597 }
598
599 // ─── billing_seat_snapshots ────────────────────────────────────────
600 func (q *Queries) CreateSeatSnapshot(ctx context.Context, db DBTX, arg CreateSeatSnapshotParams) (CreateSeatSnapshotRow, error) {
601 row := db.QueryRow(ctx, createSeatSnapshot,
602 arg.OrgID,
603 arg.StripeSubscriptionID,
604 arg.ActiveMembers,
605 arg.BillableSeats,
606 arg.Source,
607 )
608 var i CreateSeatSnapshotRow
609 err := row.Scan(
610 &i.ID,
611 &i.OrgID,
612 &i.Provider,
613 &i.StripeSubscriptionID,
614 &i.ActiveMembers,
615 &i.BillableSeats,
616 &i.Source,
617 &i.CapturedAt,
618 )
619 return i, err
620 }
621
622 const createWebhookEventReceipt = `-- name: CreateWebhookEventReceipt :one
623
624 INSERT INTO billing_webhook_events (
625 provider,
626 provider_event_id,
627 event_type,
628 api_version,
629 payload
630 )
631 VALUES (
632 'stripe',
633 $1::text,
634 $2::text,
635 $3::text,
636 $4::jsonb
637 )
638 ON CONFLICT (provider, provider_event_id) DO NOTHING
639 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
640 `
641
642 type CreateWebhookEventReceiptParams struct {
643 ProviderEventID string
644 EventType string
645 ApiVersion string
646 Payload []byte
647 }
648
649 // ─── billing_webhook_events ────────────────────────────────────────
650 func (q *Queries) CreateWebhookEventReceipt(ctx context.Context, db DBTX, arg CreateWebhookEventReceiptParams) (BillingWebhookEvent, error) {
651 row := db.QueryRow(ctx, createWebhookEventReceipt,
652 arg.ProviderEventID,
653 arg.EventType,
654 arg.ApiVersion,
655 arg.Payload,
656 )
657 var i BillingWebhookEvent
658 err := row.Scan(
659 &i.ID,
660 &i.Provider,
661 &i.ProviderEventID,
662 &i.EventType,
663 &i.ApiVersion,
664 &i.Payload,
665 &i.ReceivedAt,
666 &i.ProcessedAt,
667 &i.ProcessError,
668 &i.ProcessingAttempts,
669 &i.SubjectKind,
670 &i.SubjectID,
671 )
672 return i, err
673 }
674
675 const getOrgBillingState = `-- name: GetOrgBillingState :one
676
677
678 SELECT org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM org_billing_states WHERE org_id = $1
679 `
680
681 // SPDX-License-Identifier: AGPL-3.0-or-later
682 // ─── org_billing_states ────────────────────────────────────────────
683 func (q *Queries) GetOrgBillingState(ctx context.Context, db DBTX, orgID int64) (OrgBillingState, error) {
684 row := db.QueryRow(ctx, getOrgBillingState, orgID)
685 var i OrgBillingState
686 err := row.Scan(
687 &i.OrgID,
688 &i.Provider,
689 &i.StripeCustomerID,
690 &i.StripeSubscriptionID,
691 &i.StripeSubscriptionItemID,
692 &i.Plan,
693 &i.SubscriptionStatus,
694 &i.BillableSeats,
695 &i.SeatSnapshotAt,
696 &i.CurrentPeriodStart,
697 &i.CurrentPeriodEnd,
698 &i.CancelAtPeriodEnd,
699 &i.TrialEnd,
700 &i.PastDueAt,
701 &i.CanceledAt,
702 &i.LockedAt,
703 &i.LockReason,
704 &i.GraceUntil,
705 &i.LastWebhookEventID,
706 &i.CreatedAt,
707 &i.UpdatedAt,
708 &i.LastEventAt,
709 )
710 return i, err
711 }
712
713 const getOrgBillingStateByStripeCustomer = `-- name: GetOrgBillingStateByStripeCustomer :one
714 SELECT org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM org_billing_states
715 WHERE provider = 'stripe'
716 AND stripe_customer_id = $1
717 `
718
719 func (q *Queries) GetOrgBillingStateByStripeCustomer(ctx context.Context, db DBTX, stripeCustomerID pgtype.Text) (OrgBillingState, error) {
720 row := db.QueryRow(ctx, getOrgBillingStateByStripeCustomer, stripeCustomerID)
721 var i OrgBillingState
722 err := row.Scan(
723 &i.OrgID,
724 &i.Provider,
725 &i.StripeCustomerID,
726 &i.StripeSubscriptionID,
727 &i.StripeSubscriptionItemID,
728 &i.Plan,
729 &i.SubscriptionStatus,
730 &i.BillableSeats,
731 &i.SeatSnapshotAt,
732 &i.CurrentPeriodStart,
733 &i.CurrentPeriodEnd,
734 &i.CancelAtPeriodEnd,
735 &i.TrialEnd,
736 &i.PastDueAt,
737 &i.CanceledAt,
738 &i.LockedAt,
739 &i.LockReason,
740 &i.GraceUntil,
741 &i.LastWebhookEventID,
742 &i.CreatedAt,
743 &i.UpdatedAt,
744 &i.LastEventAt,
745 )
746 return i, err
747 }
748
749 const getOrgBillingStateByStripeSubscription = `-- name: GetOrgBillingStateByStripeSubscription :one
750 SELECT org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM org_billing_states
751 WHERE provider = 'stripe'
752 AND stripe_subscription_id = $1
753 `
754
755 func (q *Queries) GetOrgBillingStateByStripeSubscription(ctx context.Context, db DBTX, stripeSubscriptionID pgtype.Text) (OrgBillingState, error) {
756 row := db.QueryRow(ctx, getOrgBillingStateByStripeSubscription, stripeSubscriptionID)
757 var i OrgBillingState
758 err := row.Scan(
759 &i.OrgID,
760 &i.Provider,
761 &i.StripeCustomerID,
762 &i.StripeSubscriptionID,
763 &i.StripeSubscriptionItemID,
764 &i.Plan,
765 &i.SubscriptionStatus,
766 &i.BillableSeats,
767 &i.SeatSnapshotAt,
768 &i.CurrentPeriodStart,
769 &i.CurrentPeriodEnd,
770 &i.CancelAtPeriodEnd,
771 &i.TrialEnd,
772 &i.PastDueAt,
773 &i.CanceledAt,
774 &i.LockedAt,
775 &i.LockReason,
776 &i.GraceUntil,
777 &i.LastWebhookEventID,
778 &i.CreatedAt,
779 &i.UpdatedAt,
780 &i.LastEventAt,
781 )
782 return i, err
783 }
784
785 const getUserBillingState = `-- name: GetUserBillingState :one
786
787 SELECT user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM user_billing_states WHERE user_id = $1
788 `
789
790 // ─── user_billing_states (PRO03) ──────────────────────────────────
791 func (q *Queries) GetUserBillingState(ctx context.Context, db DBTX, userID int64) (UserBillingState, error) {
792 row := db.QueryRow(ctx, getUserBillingState, userID)
793 var i UserBillingState
794 err := row.Scan(
795 &i.UserID,
796 &i.Provider,
797 &i.StripeCustomerID,
798 &i.StripeSubscriptionID,
799 &i.StripeSubscriptionItemID,
800 &i.Plan,
801 &i.SubscriptionStatus,
802 &i.CurrentPeriodStart,
803 &i.CurrentPeriodEnd,
804 &i.CancelAtPeriodEnd,
805 &i.TrialEnd,
806 &i.PastDueAt,
807 &i.CanceledAt,
808 &i.LockedAt,
809 &i.LockReason,
810 &i.GraceUntil,
811 &i.LastWebhookEventID,
812 &i.CreatedAt,
813 &i.UpdatedAt,
814 &i.LastEventAt,
815 )
816 return i, err
817 }
818
819 const getUserBillingStateByStripeCustomer = `-- name: GetUserBillingStateByStripeCustomer :one
820 SELECT user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM user_billing_states
821 WHERE provider = 'stripe'
822 AND stripe_customer_id = $1
823 `
824
825 func (q *Queries) GetUserBillingStateByStripeCustomer(ctx context.Context, db DBTX, stripeCustomerID pgtype.Text) (UserBillingState, error) {
826 row := db.QueryRow(ctx, getUserBillingStateByStripeCustomer, stripeCustomerID)
827 var i UserBillingState
828 err := row.Scan(
829 &i.UserID,
830 &i.Provider,
831 &i.StripeCustomerID,
832 &i.StripeSubscriptionID,
833 &i.StripeSubscriptionItemID,
834 &i.Plan,
835 &i.SubscriptionStatus,
836 &i.CurrentPeriodStart,
837 &i.CurrentPeriodEnd,
838 &i.CancelAtPeriodEnd,
839 &i.TrialEnd,
840 &i.PastDueAt,
841 &i.CanceledAt,
842 &i.LockedAt,
843 &i.LockReason,
844 &i.GraceUntil,
845 &i.LastWebhookEventID,
846 &i.CreatedAt,
847 &i.UpdatedAt,
848 &i.LastEventAt,
849 )
850 return i, err
851 }
852
853 const getUserBillingStateByStripeSubscription = `-- name: GetUserBillingStateByStripeSubscription :one
854 SELECT user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM user_billing_states
855 WHERE provider = 'stripe'
856 AND stripe_subscription_id = $1
857 `
858
859 func (q *Queries) GetUserBillingStateByStripeSubscription(ctx context.Context, db DBTX, stripeSubscriptionID pgtype.Text) (UserBillingState, error) {
860 row := db.QueryRow(ctx, getUserBillingStateByStripeSubscription, stripeSubscriptionID)
861 var i UserBillingState
862 err := row.Scan(
863 &i.UserID,
864 &i.Provider,
865 &i.StripeCustomerID,
866 &i.StripeSubscriptionID,
867 &i.StripeSubscriptionItemID,
868 &i.Plan,
869 &i.SubscriptionStatus,
870 &i.CurrentPeriodStart,
871 &i.CurrentPeriodEnd,
872 &i.CancelAtPeriodEnd,
873 &i.TrialEnd,
874 &i.PastDueAt,
875 &i.CanceledAt,
876 &i.LockedAt,
877 &i.LockReason,
878 &i.GraceUntil,
879 &i.LastWebhookEventID,
880 &i.CreatedAt,
881 &i.UpdatedAt,
882 &i.LastEventAt,
883 )
884 return i, err
885 }
886
887 const getWebhookEventReceipt = `-- name: GetWebhookEventReceipt :one
888 SELECT id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id FROM billing_webhook_events
889 WHERE provider = 'stripe'
890 AND provider_event_id = $1
891 `
892
893 func (q *Queries) GetWebhookEventReceipt(ctx context.Context, db DBTX, providerEventID string) (BillingWebhookEvent, error) {
894 row := db.QueryRow(ctx, getWebhookEventReceipt, providerEventID)
895 var i BillingWebhookEvent
896 err := row.Scan(
897 &i.ID,
898 &i.Provider,
899 &i.ProviderEventID,
900 &i.EventType,
901 &i.ApiVersion,
902 &i.Payload,
903 &i.ReceivedAt,
904 &i.ProcessedAt,
905 &i.ProcessError,
906 &i.ProcessingAttempts,
907 &i.SubjectKind,
908 &i.SubjectID,
909 )
910 return i, err
911 }
912
913 const isOrgBillingEventStale = `-- name: IsOrgBillingEventStale :one
914 SELECT COALESCE(last_event_at > $1::timestamptz, false)::boolean AS stale
915 FROM org_billing_states
916 WHERE org_id = $2::bigint
917 `
918
919 type IsOrgBillingEventStaleParams struct {
920 EventAt pgtype.Timestamptz
921 OrgID int64
922 }
923
924 // PRO08 D4: returns true when an incoming Stripe event's timestamp
925 // is older than the last event we've already applied for this org.
926 // Stripe doesn't guarantee delivery order across retries; without
927 // this guard a stale `subscription.updated[active]` could re-activate
928 // a canceled subscription. Returns false when no prior event has
929 // been recorded (last_event_at IS NULL) — the first event is never
930 // stale.
931 func (q *Queries) IsOrgBillingEventStale(ctx context.Context, db DBTX, arg IsOrgBillingEventStaleParams) (bool, error) {
932 row := db.QueryRow(ctx, isOrgBillingEventStale, arg.EventAt, arg.OrgID)
933 var stale bool
934 err := row.Scan(&stale)
935 return stale, err
936 }
937
938 const isUserBillingEventStale = `-- name: IsUserBillingEventStale :one
939 SELECT COALESCE(last_event_at > $1::timestamptz, false)::boolean AS stale
940 FROM user_billing_states
941 WHERE user_id = $2::bigint
942 `
943
944 type IsUserBillingEventStaleParams struct {
945 EventAt pgtype.Timestamptz
946 UserID int64
947 }
948
949 func (q *Queries) IsUserBillingEventStale(ctx context.Context, db DBTX, arg IsUserBillingEventStaleParams) (bool, error) {
950 row := db.QueryRow(ctx, isUserBillingEventStale, arg.EventAt, arg.UserID)
951 var stale bool
952 err := row.Scan(&stale)
953 return stale, err
954 }
955
956 const listFailedWebhookEvents = `-- name: ListFailedWebhookEvents :many
957 SELECT id, provider, provider_event_id, event_type, api_version,
958 received_at, processed_at, processing_attempts, process_error,
959 subject_kind, subject_id
960 FROM billing_webhook_events
961 WHERE provider = 'stripe'
962 AND (
963 process_error <> ''
964 OR (processed_at IS NULL AND processing_attempts > 0)
965 )
966 ORDER BY received_at DESC
967 LIMIT $1
968 `
969
970 type ListFailedWebhookEventsRow struct {
971 ID int64
972 Provider BillingProvider
973 ProviderEventID string
974 EventType string
975 ApiVersion string
976 ReceivedAt pgtype.Timestamptz
977 ProcessedAt pgtype.Timestamptz
978 ProcessingAttempts int32
979 ProcessError string
980 SubjectKind NullBillingSubjectKind
981 SubjectID pgtype.Int8
982 }
983
984 // Operator query for "events we received but failed to process."
985 // A row is "failed" when it has a non-empty process_error OR when
986 // it has never been processed (processed_at NULL) and has at least
987 // one processing attempt. Rows that are merely new and untouched
988 // (attempts=0, processed_at NULL, no error) are excluded.
989 func (q *Queries) ListFailedWebhookEvents(ctx context.Context, db DBTX, limit int32) ([]ListFailedWebhookEventsRow, error) {
990 rows, err := db.Query(ctx, listFailedWebhookEvents, limit)
991 if err != nil {
992 return nil, err
993 }
994 defer rows.Close()
995 items := []ListFailedWebhookEventsRow{}
996 for rows.Next() {
997 var i ListFailedWebhookEventsRow
998 if err := rows.Scan(
999 &i.ID,
1000 &i.Provider,
1001 &i.ProviderEventID,
1002 &i.EventType,
1003 &i.ApiVersion,
1004 &i.ReceivedAt,
1005 &i.ProcessedAt,
1006 &i.ProcessingAttempts,
1007 &i.ProcessError,
1008 &i.SubjectKind,
1009 &i.SubjectID,
1010 ); err != nil {
1011 return nil, err
1012 }
1013 items = append(items, i)
1014 }
1015 if err := rows.Err(); err != nil {
1016 return nil, err
1017 }
1018 return items, nil
1019 }
1020
1021 const listInvoicesForOrg = `-- name: ListInvoicesForOrg :many
1022 SELECT id, org_id, provider, stripe_invoice_id, stripe_customer_id, stripe_subscription_id, status, number, currency, amount_due_cents, amount_paid_cents, amount_remaining_cents, hosted_invoice_url, invoice_pdf_url, period_start, period_end, due_at, paid_at, voided_at, created_at, updated_at, subject_kind, subject_id FROM billing_invoices
1023 WHERE subject_kind = 'org' AND subject_id = $1
1024 ORDER BY created_at DESC, id DESC
1025 LIMIT $2
1026 `
1027
1028 type ListInvoicesForOrgParams struct {
1029 SubjectID int64
1030 Limit int32
1031 }
1032
1033 // PRO03: filters on the polymorphic subject columns so the index
1034 // billing_invoices_subject_created_idx services this query. The
1035 // legacy `org_id` column is kept populated by UpsertInvoice for the
1036 // transitional window; this query no longer reads it.
1037 func (q *Queries) ListInvoicesForOrg(ctx context.Context, db DBTX, arg ListInvoicesForOrgParams) ([]BillingInvoice, error) {
1038 rows, err := db.Query(ctx, listInvoicesForOrg, arg.SubjectID, arg.Limit)
1039 if err != nil {
1040 return nil, err
1041 }
1042 defer rows.Close()
1043 items := []BillingInvoice{}
1044 for rows.Next() {
1045 var i BillingInvoice
1046 if err := rows.Scan(
1047 &i.ID,
1048 &i.OrgID,
1049 &i.Provider,
1050 &i.StripeInvoiceID,
1051 &i.StripeCustomerID,
1052 &i.StripeSubscriptionID,
1053 &i.Status,
1054 &i.Number,
1055 &i.Currency,
1056 &i.AmountDueCents,
1057 &i.AmountPaidCents,
1058 &i.AmountRemainingCents,
1059 &i.HostedInvoiceUrl,
1060 &i.InvoicePdfUrl,
1061 &i.PeriodStart,
1062 &i.PeriodEnd,
1063 &i.DueAt,
1064 &i.PaidAt,
1065 &i.VoidedAt,
1066 &i.CreatedAt,
1067 &i.UpdatedAt,
1068 &i.SubjectKind,
1069 &i.SubjectID,
1070 ); err != nil {
1071 return nil, err
1072 }
1073 items = append(items, i)
1074 }
1075 if err := rows.Err(); err != nil {
1076 return nil, err
1077 }
1078 return items, nil
1079 }
1080
1081 const listInvoicesForSubject = `-- name: ListInvoicesForSubject :many
1082 SELECT id, org_id, provider, stripe_invoice_id, stripe_customer_id, stripe_subscription_id, status, number, currency, amount_due_cents, amount_paid_cents, amount_remaining_cents, hosted_invoice_url, invoice_pdf_url, period_start, period_end, due_at, paid_at, voided_at, created_at, updated_at, subject_kind, subject_id FROM billing_invoices
1083 WHERE subject_kind = $1::billing_subject_kind
1084 AND subject_id = $2::bigint
1085 ORDER BY created_at DESC, id DESC
1086 LIMIT $3::integer
1087 `
1088
1089 type ListInvoicesForSubjectParams struct {
1090 SubjectKind BillingSubjectKind
1091 SubjectID int64
1092 Lim int32
1093 }
1094
1095 // Polymorphic invoice listing for PRO04+ callers. The org-flavored
1096 // ListInvoicesForOrg above is the same query with subject_kind
1097 // hard-coded; this surface lets a user-side caller pass kind='user'
1098 // without forking the helper.
1099 func (q *Queries) ListInvoicesForSubject(ctx context.Context, db DBTX, arg ListInvoicesForSubjectParams) ([]BillingInvoice, error) {
1100 rows, err := db.Query(ctx, listInvoicesForSubject, arg.SubjectKind, arg.SubjectID, arg.Lim)
1101 if err != nil {
1102 return nil, err
1103 }
1104 defer rows.Close()
1105 items := []BillingInvoice{}
1106 for rows.Next() {
1107 var i BillingInvoice
1108 if err := rows.Scan(
1109 &i.ID,
1110 &i.OrgID,
1111 &i.Provider,
1112 &i.StripeInvoiceID,
1113 &i.StripeCustomerID,
1114 &i.StripeSubscriptionID,
1115 &i.Status,
1116 &i.Number,
1117 &i.Currency,
1118 &i.AmountDueCents,
1119 &i.AmountPaidCents,
1120 &i.AmountRemainingCents,
1121 &i.HostedInvoiceUrl,
1122 &i.InvoicePdfUrl,
1123 &i.PeriodStart,
1124 &i.PeriodEnd,
1125 &i.DueAt,
1126 &i.PaidAt,
1127 &i.VoidedAt,
1128 &i.CreatedAt,
1129 &i.UpdatedAt,
1130 &i.SubjectKind,
1131 &i.SubjectID,
1132 ); err != nil {
1133 return nil, err
1134 }
1135 items = append(items, i)
1136 }
1137 if err := rows.Err(); err != nil {
1138 return nil, err
1139 }
1140 return items, nil
1141 }
1142
1143 const listSeatSnapshotsForOrg = `-- name: ListSeatSnapshotsForOrg :many
1144 SELECT id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at FROM billing_seat_snapshots
1145 WHERE org_id = $1
1146 ORDER BY captured_at DESC, id DESC
1147 LIMIT $2
1148 `
1149
1150 type ListSeatSnapshotsForOrgParams struct {
1151 OrgID int64
1152 Limit int32
1153 }
1154
1155 func (q *Queries) ListSeatSnapshotsForOrg(ctx context.Context, db DBTX, arg ListSeatSnapshotsForOrgParams) ([]BillingSeatSnapshot, error) {
1156 rows, err := db.Query(ctx, listSeatSnapshotsForOrg, arg.OrgID, arg.Limit)
1157 if err != nil {
1158 return nil, err
1159 }
1160 defer rows.Close()
1161 items := []BillingSeatSnapshot{}
1162 for rows.Next() {
1163 var i BillingSeatSnapshot
1164 if err := rows.Scan(
1165 &i.ID,
1166 &i.OrgID,
1167 &i.Provider,
1168 &i.StripeSubscriptionID,
1169 &i.ActiveMembers,
1170 &i.BillableSeats,
1171 &i.Source,
1172 &i.CapturedAt,
1173 ); err != nil {
1174 return nil, err
1175 }
1176 items = append(items, i)
1177 }
1178 if err := rows.Err(); err != nil {
1179 return nil, err
1180 }
1181 return items, nil
1182 }
1183
1184 const markCanceled = `-- name: MarkCanceled :one
1185 WITH state AS (
1186 UPDATE org_billing_states
1187 SET plan = 'free',
1188 subscription_status = 'canceled',
1189 canceled_at = COALESCE(canceled_at, now()),
1190 locked_at = now(),
1191 lock_reason = 'canceled',
1192 grace_until = NULL,
1193 cancel_at_period_end = false,
1194 last_webhook_event_id = $1::text,
1195 updated_at = now()
1196 WHERE org_id = $2::bigint
1197 RETURNING org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1198 ), org_update AS (
1199 UPDATE orgs
1200 SET plan = 'free',
1201 updated_at = now()
1202 WHERE id = $2::bigint
1203 RETURNING id
1204 )
1205 SELECT org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
1206 `
1207
1208 type MarkCanceledParams struct {
1209 LastWebhookEventID string
1210 OrgID int64
1211 }
1212
1213 type MarkCanceledRow struct {
1214 OrgID int64
1215 Provider BillingProvider
1216 StripeCustomerID pgtype.Text
1217 StripeSubscriptionID pgtype.Text
1218 StripeSubscriptionItemID pgtype.Text
1219 Plan OrgPlan
1220 SubscriptionStatus BillingSubscriptionStatus
1221 BillableSeats int32
1222 SeatSnapshotAt pgtype.Timestamptz
1223 CurrentPeriodStart pgtype.Timestamptz
1224 CurrentPeriodEnd pgtype.Timestamptz
1225 CancelAtPeriodEnd bool
1226 TrialEnd pgtype.Timestamptz
1227 PastDueAt pgtype.Timestamptz
1228 CanceledAt pgtype.Timestamptz
1229 LockedAt pgtype.Timestamptz
1230 LockReason NullBillingLockReason
1231 GraceUntil pgtype.Timestamptz
1232 LastWebhookEventID string
1233 CreatedAt pgtype.Timestamptz
1234 UpdatedAt pgtype.Timestamptz
1235 LastEventAt pgtype.Timestamptz
1236 }
1237
1238 func (q *Queries) MarkCanceled(ctx context.Context, db DBTX, arg MarkCanceledParams) (MarkCanceledRow, error) {
1239 row := db.QueryRow(ctx, markCanceled, arg.LastWebhookEventID, arg.OrgID)
1240 var i MarkCanceledRow
1241 err := row.Scan(
1242 &i.OrgID,
1243 &i.Provider,
1244 &i.StripeCustomerID,
1245 &i.StripeSubscriptionID,
1246 &i.StripeSubscriptionItemID,
1247 &i.Plan,
1248 &i.SubscriptionStatus,
1249 &i.BillableSeats,
1250 &i.SeatSnapshotAt,
1251 &i.CurrentPeriodStart,
1252 &i.CurrentPeriodEnd,
1253 &i.CancelAtPeriodEnd,
1254 &i.TrialEnd,
1255 &i.PastDueAt,
1256 &i.CanceledAt,
1257 &i.LockedAt,
1258 &i.LockReason,
1259 &i.GraceUntil,
1260 &i.LastWebhookEventID,
1261 &i.CreatedAt,
1262 &i.UpdatedAt,
1263 &i.LastEventAt,
1264 )
1265 return i, err
1266 }
1267
1268 const markPastDue = `-- name: MarkPastDue :one
1269 UPDATE org_billing_states
1270 SET subscription_status = 'past_due',
1271 past_due_at = COALESCE(past_due_at, now()),
1272 locked_at = now(),
1273 lock_reason = 'past_due',
1274 grace_until = $1::timestamptz,
1275 last_webhook_event_id = $2::text,
1276 updated_at = now()
1277 WHERE org_id = $3::bigint
1278 RETURNING org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1279 `
1280
1281 type MarkPastDueParams struct {
1282 GraceUntil pgtype.Timestamptz
1283 LastWebhookEventID string
1284 OrgID int64
1285 }
1286
1287 func (q *Queries) MarkPastDue(ctx context.Context, db DBTX, arg MarkPastDueParams) (OrgBillingState, error) {
1288 row := db.QueryRow(ctx, markPastDue, arg.GraceUntil, arg.LastWebhookEventID, arg.OrgID)
1289 var i OrgBillingState
1290 err := row.Scan(
1291 &i.OrgID,
1292 &i.Provider,
1293 &i.StripeCustomerID,
1294 &i.StripeSubscriptionID,
1295 &i.StripeSubscriptionItemID,
1296 &i.Plan,
1297 &i.SubscriptionStatus,
1298 &i.BillableSeats,
1299 &i.SeatSnapshotAt,
1300 &i.CurrentPeriodStart,
1301 &i.CurrentPeriodEnd,
1302 &i.CancelAtPeriodEnd,
1303 &i.TrialEnd,
1304 &i.PastDueAt,
1305 &i.CanceledAt,
1306 &i.LockedAt,
1307 &i.LockReason,
1308 &i.GraceUntil,
1309 &i.LastWebhookEventID,
1310 &i.CreatedAt,
1311 &i.UpdatedAt,
1312 &i.LastEventAt,
1313 )
1314 return i, err
1315 }
1316
1317 const markPaymentSucceeded = `-- name: MarkPaymentSucceeded :one
1318 WITH state AS (
1319 UPDATE org_billing_states
1320 SET plan = CASE
1321 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'team'
1322 ELSE plan
1323 END,
1324 subscription_status = CASE
1325 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
1326 ELSE subscription_status
1327 END,
1328 past_due_at = CASE
1329 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
1330 ELSE past_due_at
1331 END,
1332 locked_at = NULL,
1333 lock_reason = NULL,
1334 grace_until = NULL,
1335 last_webhook_event_id = $1::text,
1336 updated_at = now()
1337 WHERE org_id = $2::bigint
1338 RETURNING org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1339 ), org_update AS (
1340 UPDATE orgs
1341 SET plan = state.plan,
1342 updated_at = now()
1343 FROM state
1344 WHERE orgs.id = state.org_id
1345 RETURNING orgs.id
1346 )
1347 SELECT org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
1348 `
1349
1350 type MarkPaymentSucceededParams struct {
1351 LastWebhookEventID string
1352 OrgID int64
1353 }
1354
1355 type MarkPaymentSucceededRow struct {
1356 OrgID int64
1357 Provider BillingProvider
1358 StripeCustomerID pgtype.Text
1359 StripeSubscriptionID pgtype.Text
1360 StripeSubscriptionItemID pgtype.Text
1361 Plan OrgPlan
1362 SubscriptionStatus BillingSubscriptionStatus
1363 BillableSeats int32
1364 SeatSnapshotAt pgtype.Timestamptz
1365 CurrentPeriodStart pgtype.Timestamptz
1366 CurrentPeriodEnd pgtype.Timestamptz
1367 CancelAtPeriodEnd bool
1368 TrialEnd pgtype.Timestamptz
1369 PastDueAt pgtype.Timestamptz
1370 CanceledAt pgtype.Timestamptz
1371 LockedAt pgtype.Timestamptz
1372 LockReason NullBillingLockReason
1373 GraceUntil pgtype.Timestamptz
1374 LastWebhookEventID string
1375 CreatedAt pgtype.Timestamptz
1376 UpdatedAt pgtype.Timestamptz
1377 LastEventAt pgtype.Timestamptz
1378 }
1379
1380 func (q *Queries) MarkPaymentSucceeded(ctx context.Context, db DBTX, arg MarkPaymentSucceededParams) (MarkPaymentSucceededRow, error) {
1381 row := db.QueryRow(ctx, markPaymentSucceeded, arg.LastWebhookEventID, arg.OrgID)
1382 var i MarkPaymentSucceededRow
1383 err := row.Scan(
1384 &i.OrgID,
1385 &i.Provider,
1386 &i.StripeCustomerID,
1387 &i.StripeSubscriptionID,
1388 &i.StripeSubscriptionItemID,
1389 &i.Plan,
1390 &i.SubscriptionStatus,
1391 &i.BillableSeats,
1392 &i.SeatSnapshotAt,
1393 &i.CurrentPeriodStart,
1394 &i.CurrentPeriodEnd,
1395 &i.CancelAtPeriodEnd,
1396 &i.TrialEnd,
1397 &i.PastDueAt,
1398 &i.CanceledAt,
1399 &i.LockedAt,
1400 &i.LockReason,
1401 &i.GraceUntil,
1402 &i.LastWebhookEventID,
1403 &i.CreatedAt,
1404 &i.UpdatedAt,
1405 &i.LastEventAt,
1406 )
1407 return i, err
1408 }
1409
1410 const markUserCanceled = `-- name: MarkUserCanceled :one
1411 WITH state AS (
1412 UPDATE user_billing_states
1413 SET plan = 'free',
1414 subscription_status = 'canceled',
1415 canceled_at = COALESCE(canceled_at, now()),
1416 locked_at = now(),
1417 lock_reason = 'canceled',
1418 grace_until = NULL,
1419 cancel_at_period_end = false,
1420 last_webhook_event_id = $1::text,
1421 updated_at = now()
1422 WHERE user_id = $2::bigint
1423 RETURNING user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1424 ), user_update AS (
1425 UPDATE users
1426 SET plan = 'free',
1427 updated_at = now()
1428 WHERE id = $2::bigint
1429 RETURNING id
1430 )
1431 SELECT user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
1432 `
1433
1434 type MarkUserCanceledParams struct {
1435 LastWebhookEventID string
1436 UserID int64
1437 }
1438
1439 type MarkUserCanceledRow struct {
1440 UserID int64
1441 Provider BillingProvider
1442 StripeCustomerID pgtype.Text
1443 StripeSubscriptionID pgtype.Text
1444 StripeSubscriptionItemID pgtype.Text
1445 Plan UserPlan
1446 SubscriptionStatus BillingSubscriptionStatus
1447 CurrentPeriodStart pgtype.Timestamptz
1448 CurrentPeriodEnd pgtype.Timestamptz
1449 CancelAtPeriodEnd bool
1450 TrialEnd pgtype.Timestamptz
1451 PastDueAt pgtype.Timestamptz
1452 CanceledAt pgtype.Timestamptz
1453 LockedAt pgtype.Timestamptz
1454 LockReason NullBillingLockReason
1455 GraceUntil pgtype.Timestamptz
1456 LastWebhookEventID string
1457 CreatedAt pgtype.Timestamptz
1458 UpdatedAt pgtype.Timestamptz
1459 LastEventAt pgtype.Timestamptz
1460 }
1461
1462 func (q *Queries) MarkUserCanceled(ctx context.Context, db DBTX, arg MarkUserCanceledParams) (MarkUserCanceledRow, error) {
1463 row := db.QueryRow(ctx, markUserCanceled, arg.LastWebhookEventID, arg.UserID)
1464 var i MarkUserCanceledRow
1465 err := row.Scan(
1466 &i.UserID,
1467 &i.Provider,
1468 &i.StripeCustomerID,
1469 &i.StripeSubscriptionID,
1470 &i.StripeSubscriptionItemID,
1471 &i.Plan,
1472 &i.SubscriptionStatus,
1473 &i.CurrentPeriodStart,
1474 &i.CurrentPeriodEnd,
1475 &i.CancelAtPeriodEnd,
1476 &i.TrialEnd,
1477 &i.PastDueAt,
1478 &i.CanceledAt,
1479 &i.LockedAt,
1480 &i.LockReason,
1481 &i.GraceUntil,
1482 &i.LastWebhookEventID,
1483 &i.CreatedAt,
1484 &i.UpdatedAt,
1485 &i.LastEventAt,
1486 )
1487 return i, err
1488 }
1489
1490 const markUserPastDue = `-- name: MarkUserPastDue :one
1491 UPDATE user_billing_states
1492 SET subscription_status = 'past_due',
1493 past_due_at = COALESCE(past_due_at, now()),
1494 locked_at = now(),
1495 lock_reason = 'past_due',
1496 grace_until = $1::timestamptz,
1497 last_webhook_event_id = $2::text,
1498 updated_at = now()
1499 WHERE user_id = $3::bigint
1500 RETURNING user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1501 `
1502
1503 type MarkUserPastDueParams struct {
1504 GraceUntil pgtype.Timestamptz
1505 LastWebhookEventID string
1506 UserID int64
1507 }
1508
1509 func (q *Queries) MarkUserPastDue(ctx context.Context, db DBTX, arg MarkUserPastDueParams) (UserBillingState, error) {
1510 row := db.QueryRow(ctx, markUserPastDue, arg.GraceUntil, arg.LastWebhookEventID, arg.UserID)
1511 var i UserBillingState
1512 err := row.Scan(
1513 &i.UserID,
1514 &i.Provider,
1515 &i.StripeCustomerID,
1516 &i.StripeSubscriptionID,
1517 &i.StripeSubscriptionItemID,
1518 &i.Plan,
1519 &i.SubscriptionStatus,
1520 &i.CurrentPeriodStart,
1521 &i.CurrentPeriodEnd,
1522 &i.CancelAtPeriodEnd,
1523 &i.TrialEnd,
1524 &i.PastDueAt,
1525 &i.CanceledAt,
1526 &i.LockedAt,
1527 &i.LockReason,
1528 &i.GraceUntil,
1529 &i.LastWebhookEventID,
1530 &i.CreatedAt,
1531 &i.UpdatedAt,
1532 &i.LastEventAt,
1533 )
1534 return i, err
1535 }
1536
1537 const markUserPaymentSucceeded = `-- name: MarkUserPaymentSucceeded :one
1538 WITH state AS (
1539 UPDATE user_billing_states
1540 SET plan = CASE
1541 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'pro'
1542 ELSE plan
1543 END,
1544 subscription_status = CASE
1545 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
1546 ELSE subscription_status
1547 END,
1548 past_due_at = CASE
1549 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
1550 ELSE past_due_at
1551 END,
1552 locked_at = NULL,
1553 lock_reason = NULL,
1554 grace_until = NULL,
1555 last_webhook_event_id = $1::text,
1556 updated_at = now()
1557 WHERE user_id = $2::bigint
1558 RETURNING user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1559 ), user_update AS (
1560 UPDATE users
1561 SET plan = state.plan,
1562 updated_at = now()
1563 FROM state
1564 WHERE users.id = state.user_id
1565 RETURNING users.id
1566 )
1567 SELECT user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at FROM state
1568 `
1569
1570 type MarkUserPaymentSucceededParams struct {
1571 LastWebhookEventID string
1572 UserID int64
1573 }
1574
1575 type MarkUserPaymentSucceededRow struct {
1576 UserID int64
1577 Provider BillingProvider
1578 StripeCustomerID pgtype.Text
1579 StripeSubscriptionID pgtype.Text
1580 StripeSubscriptionItemID pgtype.Text
1581 Plan UserPlan
1582 SubscriptionStatus BillingSubscriptionStatus
1583 CurrentPeriodStart pgtype.Timestamptz
1584 CurrentPeriodEnd pgtype.Timestamptz
1585 CancelAtPeriodEnd bool
1586 TrialEnd pgtype.Timestamptz
1587 PastDueAt pgtype.Timestamptz
1588 CanceledAt pgtype.Timestamptz
1589 LockedAt pgtype.Timestamptz
1590 LockReason NullBillingLockReason
1591 GraceUntil pgtype.Timestamptz
1592 LastWebhookEventID string
1593 CreatedAt pgtype.Timestamptz
1594 UpdatedAt pgtype.Timestamptz
1595 LastEventAt pgtype.Timestamptz
1596 }
1597
1598 func (q *Queries) MarkUserPaymentSucceeded(ctx context.Context, db DBTX, arg MarkUserPaymentSucceededParams) (MarkUserPaymentSucceededRow, error) {
1599 row := db.QueryRow(ctx, markUserPaymentSucceeded, arg.LastWebhookEventID, arg.UserID)
1600 var i MarkUserPaymentSucceededRow
1601 err := row.Scan(
1602 &i.UserID,
1603 &i.Provider,
1604 &i.StripeCustomerID,
1605 &i.StripeSubscriptionID,
1606 &i.StripeSubscriptionItemID,
1607 &i.Plan,
1608 &i.SubscriptionStatus,
1609 &i.CurrentPeriodStart,
1610 &i.CurrentPeriodEnd,
1611 &i.CancelAtPeriodEnd,
1612 &i.TrialEnd,
1613 &i.PastDueAt,
1614 &i.CanceledAt,
1615 &i.LockedAt,
1616 &i.LockReason,
1617 &i.GraceUntil,
1618 &i.LastWebhookEventID,
1619 &i.CreatedAt,
1620 &i.UpdatedAt,
1621 &i.LastEventAt,
1622 )
1623 return i, err
1624 }
1625
1626 const markWebhookEventFailed = `-- name: MarkWebhookEventFailed :one
1627 UPDATE billing_webhook_events
1628 SET process_error = $2,
1629 processing_attempts = processing_attempts + 1
1630 WHERE provider = 'stripe'
1631 AND provider_event_id = $1
1632 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
1633 `
1634
1635 type MarkWebhookEventFailedParams struct {
1636 ProviderEventID string
1637 ProcessError string
1638 }
1639
1640 func (q *Queries) MarkWebhookEventFailed(ctx context.Context, db DBTX, arg MarkWebhookEventFailedParams) (BillingWebhookEvent, error) {
1641 row := db.QueryRow(ctx, markWebhookEventFailed, arg.ProviderEventID, arg.ProcessError)
1642 var i BillingWebhookEvent
1643 err := row.Scan(
1644 &i.ID,
1645 &i.Provider,
1646 &i.ProviderEventID,
1647 &i.EventType,
1648 &i.ApiVersion,
1649 &i.Payload,
1650 &i.ReceivedAt,
1651 &i.ProcessedAt,
1652 &i.ProcessError,
1653 &i.ProcessingAttempts,
1654 &i.SubjectKind,
1655 &i.SubjectID,
1656 )
1657 return i, err
1658 }
1659
1660 const markWebhookEventProcessed = `-- name: MarkWebhookEventProcessed :one
1661 UPDATE billing_webhook_events
1662 SET processed_at = now(),
1663 process_error = '',
1664 processing_attempts = processing_attempts + 1
1665 WHERE provider = 'stripe'
1666 AND provider_event_id = $1
1667 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
1668 `
1669
1670 func (q *Queries) MarkWebhookEventProcessed(ctx context.Context, db DBTX, providerEventID string) (BillingWebhookEvent, error) {
1671 row := db.QueryRow(ctx, markWebhookEventProcessed, providerEventID)
1672 var i BillingWebhookEvent
1673 err := row.Scan(
1674 &i.ID,
1675 &i.Provider,
1676 &i.ProviderEventID,
1677 &i.EventType,
1678 &i.ApiVersion,
1679 &i.Payload,
1680 &i.ReceivedAt,
1681 &i.ProcessedAt,
1682 &i.ProcessError,
1683 &i.ProcessingAttempts,
1684 &i.SubjectKind,
1685 &i.SubjectID,
1686 )
1687 return i, err
1688 }
1689
1690 const setStripeCustomer = `-- name: SetStripeCustomer :one
1691 INSERT INTO org_billing_states (org_id, provider, stripe_customer_id)
1692 VALUES ($1, 'stripe', $2)
1693 ON CONFLICT (org_id) DO UPDATE
1694 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
1695 provider = 'stripe',
1696 updated_at = now()
1697 RETURNING org_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, billable_seats, seat_snapshot_at, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1698 `
1699
1700 type SetStripeCustomerParams struct {
1701 OrgID int64
1702 StripeCustomerID pgtype.Text
1703 }
1704
1705 func (q *Queries) SetStripeCustomer(ctx context.Context, db DBTX, arg SetStripeCustomerParams) (OrgBillingState, error) {
1706 row := db.QueryRow(ctx, setStripeCustomer, arg.OrgID, arg.StripeCustomerID)
1707 var i OrgBillingState
1708 err := row.Scan(
1709 &i.OrgID,
1710 &i.Provider,
1711 &i.StripeCustomerID,
1712 &i.StripeSubscriptionID,
1713 &i.StripeSubscriptionItemID,
1714 &i.Plan,
1715 &i.SubscriptionStatus,
1716 &i.BillableSeats,
1717 &i.SeatSnapshotAt,
1718 &i.CurrentPeriodStart,
1719 &i.CurrentPeriodEnd,
1720 &i.CancelAtPeriodEnd,
1721 &i.TrialEnd,
1722 &i.PastDueAt,
1723 &i.CanceledAt,
1724 &i.LockedAt,
1725 &i.LockReason,
1726 &i.GraceUntil,
1727 &i.LastWebhookEventID,
1728 &i.CreatedAt,
1729 &i.UpdatedAt,
1730 &i.LastEventAt,
1731 )
1732 return i, err
1733 }
1734
1735 const setUserStripeCustomer = `-- name: SetUserStripeCustomer :one
1736 INSERT INTO user_billing_states (user_id, provider, stripe_customer_id)
1737 VALUES ($1, 'stripe', $2)
1738 ON CONFLICT (user_id) DO UPDATE
1739 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
1740 provider = 'stripe',
1741 updated_at = now()
1742 RETURNING user_id, provider, stripe_customer_id, stripe_subscription_id, stripe_subscription_item_id, plan, subscription_status, current_period_start, current_period_end, cancel_at_period_end, trial_end, past_due_at, canceled_at, locked_at, lock_reason, grace_until, last_webhook_event_id, created_at, updated_at, last_event_at
1743 `
1744
1745 type SetUserStripeCustomerParams struct {
1746 UserID int64
1747 StripeCustomerID pgtype.Text
1748 }
1749
1750 func (q *Queries) SetUserStripeCustomer(ctx context.Context, db DBTX, arg SetUserStripeCustomerParams) (UserBillingState, error) {
1751 row := db.QueryRow(ctx, setUserStripeCustomer, arg.UserID, arg.StripeCustomerID)
1752 var i UserBillingState
1753 err := row.Scan(
1754 &i.UserID,
1755 &i.Provider,
1756 &i.StripeCustomerID,
1757 &i.StripeSubscriptionID,
1758 &i.StripeSubscriptionItemID,
1759 &i.Plan,
1760 &i.SubscriptionStatus,
1761 &i.CurrentPeriodStart,
1762 &i.CurrentPeriodEnd,
1763 &i.CancelAtPeriodEnd,
1764 &i.TrialEnd,
1765 &i.PastDueAt,
1766 &i.CanceledAt,
1767 &i.LockedAt,
1768 &i.LockReason,
1769 &i.GraceUntil,
1770 &i.LastWebhookEventID,
1771 &i.CreatedAt,
1772 &i.UpdatedAt,
1773 &i.LastEventAt,
1774 )
1775 return i, err
1776 }
1777
1778 const setWebhookEventSubject = `-- name: SetWebhookEventSubject :exec
1779 UPDATE billing_webhook_events
1780 SET subject_kind = $1::billing_subject_kind,
1781 subject_id = $2::bigint
1782 WHERE provider = 'stripe'
1783 AND provider_event_id = $3::text
1784 `
1785
1786 type SetWebhookEventSubjectParams struct {
1787 SubjectKind BillingSubjectKind
1788 SubjectID int64
1789 ProviderEventID string
1790 }
1791
1792 // Records the resolved subject on the receipt row after a successful
1793 // subject-resolution step. Called from the apply path before guard +
1794 // state mutation so the receipt carries the audit trail even if the
1795 // subsequent apply fails. Migration 0075's CHECK constraint enforces
1796 // both-or-neither; callers must pass a non-zero subject.
1797 func (q *Queries) SetWebhookEventSubject(ctx context.Context, db DBTX, arg SetWebhookEventSubjectParams) error {
1798 _, err := db.Exec(ctx, setWebhookEventSubject, arg.SubjectKind, arg.SubjectID, arg.ProviderEventID)
1799 return err
1800 }
1801
1802 const touchOrgBillingLastEventAt = `-- name: TouchOrgBillingLastEventAt :exec
1803 UPDATE org_billing_states
1804 SET last_event_at = GREATEST(COALESCE(last_event_at, $1::timestamptz), $1::timestamptz)
1805 WHERE org_id = $2::bigint
1806 `
1807
1808 type TouchOrgBillingLastEventAtParams struct {
1809 EventAt pgtype.Timestamptz
1810 OrgID int64
1811 }
1812
1813 // PRO08 D4: bump last_event_at on successful apply. Conditional so
1814 // a fresh apply driven by an out-of-order-but-recent retry doesn't
1815 // regress the timestamp (GREATEST). NULL last_event_at acquires the
1816 // incoming value.
1817 func (q *Queries) TouchOrgBillingLastEventAt(ctx context.Context, db DBTX, arg TouchOrgBillingLastEventAtParams) error {
1818 _, err := db.Exec(ctx, touchOrgBillingLastEventAt, arg.EventAt, arg.OrgID)
1819 return err
1820 }
1821
1822 const touchUserBillingLastEventAt = `-- name: TouchUserBillingLastEventAt :exec
1823 UPDATE user_billing_states
1824 SET last_event_at = GREATEST(COALESCE(last_event_at, $1::timestamptz), $1::timestamptz)
1825 WHERE user_id = $2::bigint
1826 `
1827
1828 type TouchUserBillingLastEventAtParams struct {
1829 EventAt pgtype.Timestamptz
1830 UserID int64
1831 }
1832
1833 func (q *Queries) TouchUserBillingLastEventAt(ctx context.Context, db DBTX, arg TouchUserBillingLastEventAtParams) error {
1834 _, err := db.Exec(ctx, touchUserBillingLastEventAt, arg.EventAt, arg.UserID)
1835 return err
1836 }
1837
1838 const tryAcquireWebhookEventLock = `-- name: TryAcquireWebhookEventLock :one
1839 SELECT pg_try_advisory_xact_lock(hashtext($1)::bigint) AS acquired
1840 `
1841
1842 // PRO08 A3: transaction-scoped advisory lock keyed on the hash of
1843 // the provider_event_id. Two concurrent webhook deliveries for the
1844 // same event_id race past CreateWebhookEventReceipt before either has
1845 // marked it processed; without serialization, both proceed to apply
1846 // and double-mutate state. This lock makes the apply path mutually
1847 // exclusive per event. Returns true when acquired; false means
1848 // another worker holds it — caller should let Stripe retry.
1849 //
1850 // pg_try_advisory_xact_lock takes a bigint; hashtext returns int4
1851 // which sign-extends safely. The lock auto-releases at txn end.
1852 func (q *Queries) TryAcquireWebhookEventLock(ctx context.Context, db DBTX, hashtext string) (bool, error) {
1853 row := db.QueryRow(ctx, tryAcquireWebhookEventLock, hashtext)
1854 var acquired bool
1855 err := row.Scan(&acquired)
1856 return acquired, err
1857 }
1858
1859 const upsertInvoice = `-- name: UpsertInvoice :one
1860
1861 INSERT INTO billing_invoices (
1862 org_id,
1863 subject_kind,
1864 subject_id,
1865 provider,
1866 stripe_invoice_id,
1867 stripe_customer_id,
1868 stripe_subscription_id,
1869 status,
1870 number,
1871 currency,
1872 amount_due_cents,
1873 amount_paid_cents,
1874 amount_remaining_cents,
1875 hosted_invoice_url,
1876 invoice_pdf_url,
1877 period_start,
1878 period_end,
1879 due_at,
1880 paid_at,
1881 voided_at
1882 )
1883 VALUES (
1884 $1::bigint,
1885 'org'::billing_subject_kind,
1886 $1::bigint,
1887 'stripe',
1888 $2::text,
1889 $3::text,
1890 $4::text,
1891 $5::billing_invoice_status,
1892 $6::text,
1893 $7::text,
1894 $8::bigint,
1895 $9::bigint,
1896 $10::bigint,
1897 $11::text,
1898 $12::text,
1899 $13::timestamptz,
1900 $14::timestamptz,
1901 $15::timestamptz,
1902 $16::timestamptz,
1903 $17::timestamptz
1904 )
1905 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
1906 SET org_id = EXCLUDED.org_id,
1907 stripe_customer_id = EXCLUDED.stripe_customer_id,
1908 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
1909 status = EXCLUDED.status,
1910 number = EXCLUDED.number,
1911 currency = EXCLUDED.currency,
1912 amount_due_cents = EXCLUDED.amount_due_cents,
1913 amount_paid_cents = EXCLUDED.amount_paid_cents,
1914 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
1915 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
1916 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
1917 period_start = EXCLUDED.period_start,
1918 period_end = EXCLUDED.period_end,
1919 due_at = EXCLUDED.due_at,
1920 paid_at = EXCLUDED.paid_at,
1921 voided_at = EXCLUDED.voided_at,
1922 updated_at = now()
1923 RETURNING id, org_id, provider, stripe_invoice_id, stripe_customer_id, stripe_subscription_id, status, number, currency, amount_due_cents, amount_paid_cents, amount_remaining_cents, hosted_invoice_url, invoice_pdf_url, period_start, period_end, due_at, paid_at, voided_at, created_at, updated_at, subject_kind, subject_id
1924 `
1925
1926 type UpsertInvoiceParams struct {
1927 OrgID int64
1928 StripeInvoiceID string
1929 StripeCustomerID string
1930 StripeSubscriptionID pgtype.Text
1931 Status BillingInvoiceStatus
1932 Number string
1933 Currency string
1934 AmountDueCents int64
1935 AmountPaidCents int64
1936 AmountRemainingCents int64
1937 HostedInvoiceUrl string
1938 InvoicePdfUrl string
1939 PeriodStart pgtype.Timestamptz
1940 PeriodEnd pgtype.Timestamptz
1941 DueAt pgtype.Timestamptz
1942 PaidAt pgtype.Timestamptz
1943 VoidedAt pgtype.Timestamptz
1944 }
1945
1946 // ─── billing_invoices ──────────────────────────────────────────────
1947 // PRO03: writes both legacy `org_id` and polymorphic
1948 // `(subject_kind, subject_id)`. Callers continue to bind org_id only;
1949 // the subject columns are derived. After PRO04 migrates all callers
1950 // to the polymorphic shape, a follow-up migration drops `org_id` and
1951 // this query loses the legacy column from its INSERT list.
1952 func (q *Queries) UpsertInvoice(ctx context.Context, db DBTX, arg UpsertInvoiceParams) (BillingInvoice, error) {
1953 row := db.QueryRow(ctx, upsertInvoice,
1954 arg.OrgID,
1955 arg.StripeInvoiceID,
1956 arg.StripeCustomerID,
1957 arg.StripeSubscriptionID,
1958 arg.Status,
1959 arg.Number,
1960 arg.Currency,
1961 arg.AmountDueCents,
1962 arg.AmountPaidCents,
1963 arg.AmountRemainingCents,
1964 arg.HostedInvoiceUrl,
1965 arg.InvoicePdfUrl,
1966 arg.PeriodStart,
1967 arg.PeriodEnd,
1968 arg.DueAt,
1969 arg.PaidAt,
1970 arg.VoidedAt,
1971 )
1972 var i BillingInvoice
1973 err := row.Scan(
1974 &i.ID,
1975 &i.OrgID,
1976 &i.Provider,
1977 &i.StripeInvoiceID,
1978 &i.StripeCustomerID,
1979 &i.StripeSubscriptionID,
1980 &i.Status,
1981 &i.Number,
1982 &i.Currency,
1983 &i.AmountDueCents,
1984 &i.AmountPaidCents,
1985 &i.AmountRemainingCents,
1986 &i.HostedInvoiceUrl,
1987 &i.InvoicePdfUrl,
1988 &i.PeriodStart,
1989 &i.PeriodEnd,
1990 &i.DueAt,
1991 &i.PaidAt,
1992 &i.VoidedAt,
1993 &i.CreatedAt,
1994 &i.UpdatedAt,
1995 &i.SubjectKind,
1996 &i.SubjectID,
1997 )
1998 return i, err
1999 }
2000
2001 const upsertInvoiceForSubject = `-- name: UpsertInvoiceForSubject :one
2002 INSERT INTO billing_invoices (
2003 subject_kind,
2004 subject_id,
2005 provider,
2006 stripe_invoice_id,
2007 stripe_customer_id,
2008 stripe_subscription_id,
2009 status,
2010 number,
2011 currency,
2012 amount_due_cents,
2013 amount_paid_cents,
2014 amount_remaining_cents,
2015 hosted_invoice_url,
2016 invoice_pdf_url,
2017 period_start,
2018 period_end,
2019 due_at,
2020 paid_at,
2021 voided_at
2022 )
2023 VALUES (
2024 $1::billing_subject_kind,
2025 $2::bigint,
2026 'stripe',
2027 $3::text,
2028 $4::text,
2029 $5::text,
2030 $6::billing_invoice_status,
2031 $7::text,
2032 $8::text,
2033 $9::bigint,
2034 $10::bigint,
2035 $11::bigint,
2036 $12::text,
2037 $13::text,
2038 $14::timestamptz,
2039 $15::timestamptz,
2040 $16::timestamptz,
2041 $17::timestamptz,
2042 $18::timestamptz
2043 )
2044 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
2045 SET subject_kind = EXCLUDED.subject_kind,
2046 subject_id = EXCLUDED.subject_id,
2047 stripe_customer_id = EXCLUDED.stripe_customer_id,
2048 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
2049 status = EXCLUDED.status,
2050 number = EXCLUDED.number,
2051 currency = EXCLUDED.currency,
2052 amount_due_cents = EXCLUDED.amount_due_cents,
2053 amount_paid_cents = EXCLUDED.amount_paid_cents,
2054 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
2055 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
2056 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
2057 period_start = EXCLUDED.period_start,
2058 period_end = EXCLUDED.period_end,
2059 due_at = EXCLUDED.due_at,
2060 paid_at = EXCLUDED.paid_at,
2061 voided_at = EXCLUDED.voided_at,
2062 updated_at = now()
2063 RETURNING id, org_id, provider, stripe_invoice_id, stripe_customer_id, stripe_subscription_id, status, number, currency, amount_due_cents, amount_paid_cents, amount_remaining_cents, hosted_invoice_url, invoice_pdf_url, period_start, period_end, due_at, paid_at, voided_at, created_at, updated_at, subject_kind, subject_id
2064 `
2065
2066 type UpsertInvoiceForSubjectParams struct {
2067 SubjectKind BillingSubjectKind
2068 SubjectID int64
2069 StripeInvoiceID string
2070 StripeCustomerID string
2071 StripeSubscriptionID pgtype.Text
2072 Status BillingInvoiceStatus
2073 Number string
2074 Currency string
2075 AmountDueCents int64
2076 AmountPaidCents int64
2077 AmountRemainingCents int64
2078 HostedInvoiceUrl string
2079 InvoicePdfUrl string
2080 PeriodStart pgtype.Timestamptz
2081 PeriodEnd pgtype.Timestamptz
2082 DueAt pgtype.Timestamptz
2083 PaidAt pgtype.Timestamptz
2084 VoidedAt pgtype.Timestamptz
2085 }
2086
2087 // PRO04 polymorphic invoice upsert. Writes (subject_kind,
2088 // subject_id) directly; org_id stays NULL for user-kind rows (per
2089 // the 0074 migration's nullable change). The existing
2090 // UpsertInvoice query stays as the org-kind path during the
2091 // transitional deploy — both can coexist because the UNIQUE
2092 // (provider, stripe_invoice_id) prevents duplicate rows.
2093 func (q *Queries) UpsertInvoiceForSubject(ctx context.Context, db DBTX, arg UpsertInvoiceForSubjectParams) (BillingInvoice, error) {
2094 row := db.QueryRow(ctx, upsertInvoiceForSubject,
2095 arg.SubjectKind,
2096 arg.SubjectID,
2097 arg.StripeInvoiceID,
2098 arg.StripeCustomerID,
2099 arg.StripeSubscriptionID,
2100 arg.Status,
2101 arg.Number,
2102 arg.Currency,
2103 arg.AmountDueCents,
2104 arg.AmountPaidCents,
2105 arg.AmountRemainingCents,
2106 arg.HostedInvoiceUrl,
2107 arg.InvoicePdfUrl,
2108 arg.PeriodStart,
2109 arg.PeriodEnd,
2110 arg.DueAt,
2111 arg.PaidAt,
2112 arg.VoidedAt,
2113 )
2114 var i BillingInvoice
2115 err := row.Scan(
2116 &i.ID,
2117 &i.OrgID,
2118 &i.Provider,
2119 &i.StripeInvoiceID,
2120 &i.StripeCustomerID,
2121 &i.StripeSubscriptionID,
2122 &i.Status,
2123 &i.Number,
2124 &i.Currency,
2125 &i.AmountDueCents,
2126 &i.AmountPaidCents,
2127 &i.AmountRemainingCents,
2128 &i.HostedInvoiceUrl,
2129 &i.InvoicePdfUrl,
2130 &i.PeriodStart,
2131 &i.PeriodEnd,
2132 &i.DueAt,
2133 &i.PaidAt,
2134 &i.VoidedAt,
2135 &i.CreatedAt,
2136 &i.UpdatedAt,
2137 &i.SubjectKind,
2138 &i.SubjectID,
2139 )
2140 return i, err
2141 }
2142