shithub Public

Watch 1 Fork 0 Star 0
Go · 57189 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 locked_at = NULL,
71 lock_reason = NULL,
72 grace_until = NULL,
73 updated_at = now()
74 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
75 ), org_update AS (
76 UPDATE orgs
77 SET plan = $2::org_plan,
78 updated_at = now()
79 WHERE id = $1::bigint
80 RETURNING id
81 )
82 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 FROM state
83 `
84
85 type ApplySubscriptionSnapshotParams struct {
86 OrgID int64
87 Plan OrgPlan
88 SubscriptionStatus BillingSubscriptionStatus
89 StripeSubscriptionID pgtype.Text
90 StripeSubscriptionItemID pgtype.Text
91 CurrentPeriodStart pgtype.Timestamptz
92 CurrentPeriodEnd pgtype.Timestamptz
93 CancelAtPeriodEnd bool
94 TrialEnd pgtype.Timestamptz
95 CanceledAt pgtype.Timestamptz
96 LastWebhookEventID string
97 }
98
99 type ApplySubscriptionSnapshotRow struct {
100 OrgID int64
101 Provider BillingProvider
102 StripeCustomerID pgtype.Text
103 StripeSubscriptionID pgtype.Text
104 StripeSubscriptionItemID pgtype.Text
105 Plan OrgPlan
106 SubscriptionStatus BillingSubscriptionStatus
107 BillableSeats int32
108 SeatSnapshotAt pgtype.Timestamptz
109 CurrentPeriodStart pgtype.Timestamptz
110 CurrentPeriodEnd pgtype.Timestamptz
111 CancelAtPeriodEnd bool
112 TrialEnd pgtype.Timestamptz
113 PastDueAt pgtype.Timestamptz
114 CanceledAt pgtype.Timestamptz
115 LockedAt pgtype.Timestamptz
116 LockReason NullBillingLockReason
117 GraceUntil pgtype.Timestamptz
118 LastWebhookEventID string
119 CreatedAt pgtype.Timestamptz
120 UpdatedAt pgtype.Timestamptz
121 }
122
123 func (q *Queries) ApplySubscriptionSnapshot(ctx context.Context, db DBTX, arg ApplySubscriptionSnapshotParams) (ApplySubscriptionSnapshotRow, error) {
124 row := db.QueryRow(ctx, applySubscriptionSnapshot,
125 arg.OrgID,
126 arg.Plan,
127 arg.SubscriptionStatus,
128 arg.StripeSubscriptionID,
129 arg.StripeSubscriptionItemID,
130 arg.CurrentPeriodStart,
131 arg.CurrentPeriodEnd,
132 arg.CancelAtPeriodEnd,
133 arg.TrialEnd,
134 arg.CanceledAt,
135 arg.LastWebhookEventID,
136 )
137 var i ApplySubscriptionSnapshotRow
138 err := row.Scan(
139 &i.OrgID,
140 &i.Provider,
141 &i.StripeCustomerID,
142 &i.StripeSubscriptionID,
143 &i.StripeSubscriptionItemID,
144 &i.Plan,
145 &i.SubscriptionStatus,
146 &i.BillableSeats,
147 &i.SeatSnapshotAt,
148 &i.CurrentPeriodStart,
149 &i.CurrentPeriodEnd,
150 &i.CancelAtPeriodEnd,
151 &i.TrialEnd,
152 &i.PastDueAt,
153 &i.CanceledAt,
154 &i.LockedAt,
155 &i.LockReason,
156 &i.GraceUntil,
157 &i.LastWebhookEventID,
158 &i.CreatedAt,
159 &i.UpdatedAt,
160 )
161 return i, err
162 }
163
164 const applyUserSubscriptionSnapshot = `-- name: ApplyUserSubscriptionSnapshot :one
165 WITH state AS (
166 INSERT INTO user_billing_states (
167 user_id,
168 provider,
169 plan,
170 subscription_status,
171 stripe_subscription_id,
172 stripe_subscription_item_id,
173 current_period_start,
174 current_period_end,
175 cancel_at_period_end,
176 trial_end,
177 canceled_at,
178 last_webhook_event_id,
179 past_due_at,
180 locked_at,
181 lock_reason,
182 grace_until
183 )
184 VALUES (
185 $1::bigint,
186 'stripe',
187 $2::user_plan,
188 $3::billing_subscription_status,
189 $4::text,
190 $5::text,
191 $6::timestamptz,
192 $7::timestamptz,
193 $8::boolean,
194 $9::timestamptz,
195 $10::timestamptz,
196 $11::text,
197 CASE
198 WHEN $3::billing_subscription_status = 'past_due' THEN now()
199 ELSE NULL
200 END,
201 NULL,
202 NULL,
203 NULL
204 )
205 ON CONFLICT (user_id) DO UPDATE
206 SET plan = EXCLUDED.plan,
207 subscription_status = EXCLUDED.subscription_status,
208 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
209 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
210 current_period_start = EXCLUDED.current_period_start,
211 current_period_end = EXCLUDED.current_period_end,
212 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
213 trial_end = EXCLUDED.trial_end,
214 canceled_at = EXCLUDED.canceled_at,
215 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
216 past_due_at = CASE
217 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(user_billing_states.past_due_at, now())
218 ELSE NULL
219 END,
220 locked_at = NULL,
221 lock_reason = NULL,
222 grace_until = NULL,
223 updated_at = now()
224 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
225 ), user_update AS (
226 UPDATE users
227 SET plan = $2::user_plan,
228 updated_at = now()
229 WHERE id = $1::bigint
230 RETURNING id
231 )
232 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 FROM state
233 `
234
235 type ApplyUserSubscriptionSnapshotParams struct {
236 UserID int64
237 Plan UserPlan
238 SubscriptionStatus BillingSubscriptionStatus
239 StripeSubscriptionID pgtype.Text
240 StripeSubscriptionItemID pgtype.Text
241 CurrentPeriodStart pgtype.Timestamptz
242 CurrentPeriodEnd pgtype.Timestamptz
243 CancelAtPeriodEnd bool
244 TrialEnd pgtype.Timestamptz
245 CanceledAt pgtype.Timestamptz
246 LastWebhookEventID string
247 }
248
249 type ApplyUserSubscriptionSnapshotRow struct {
250 UserID int64
251 Provider BillingProvider
252 StripeCustomerID pgtype.Text
253 StripeSubscriptionID pgtype.Text
254 StripeSubscriptionItemID pgtype.Text
255 Plan UserPlan
256 SubscriptionStatus BillingSubscriptionStatus
257 CurrentPeriodStart pgtype.Timestamptz
258 CurrentPeriodEnd pgtype.Timestamptz
259 CancelAtPeriodEnd bool
260 TrialEnd pgtype.Timestamptz
261 PastDueAt pgtype.Timestamptz
262 CanceledAt pgtype.Timestamptz
263 LockedAt pgtype.Timestamptz
264 LockReason NullBillingLockReason
265 GraceUntil pgtype.Timestamptz
266 LastWebhookEventID string
267 CreatedAt pgtype.Timestamptz
268 UpdatedAt pgtype.Timestamptz
269 }
270
271 // Mirrors ApplySubscriptionSnapshot for orgs minus the seat columns
272 // and with `user_plan` as the plan enum. The same CTE pattern keeps
273 // users.plan and user_billing_states.plan atomic.
274 func (q *Queries) ApplyUserSubscriptionSnapshot(ctx context.Context, db DBTX, arg ApplyUserSubscriptionSnapshotParams) (ApplyUserSubscriptionSnapshotRow, error) {
275 row := db.QueryRow(ctx, applyUserSubscriptionSnapshot,
276 arg.UserID,
277 arg.Plan,
278 arg.SubscriptionStatus,
279 arg.StripeSubscriptionID,
280 arg.StripeSubscriptionItemID,
281 arg.CurrentPeriodStart,
282 arg.CurrentPeriodEnd,
283 arg.CancelAtPeriodEnd,
284 arg.TrialEnd,
285 arg.CanceledAt,
286 arg.LastWebhookEventID,
287 )
288 var i ApplyUserSubscriptionSnapshotRow
289 err := row.Scan(
290 &i.UserID,
291 &i.Provider,
292 &i.StripeCustomerID,
293 &i.StripeSubscriptionID,
294 &i.StripeSubscriptionItemID,
295 &i.Plan,
296 &i.SubscriptionStatus,
297 &i.CurrentPeriodStart,
298 &i.CurrentPeriodEnd,
299 &i.CancelAtPeriodEnd,
300 &i.TrialEnd,
301 &i.PastDueAt,
302 &i.CanceledAt,
303 &i.LockedAt,
304 &i.LockReason,
305 &i.GraceUntil,
306 &i.LastWebhookEventID,
307 &i.CreatedAt,
308 &i.UpdatedAt,
309 )
310 return i, err
311 }
312
313 const clearBillingLock = `-- name: ClearBillingLock :one
314 WITH state AS (
315 UPDATE org_billing_states
316 SET plan = CASE
317 WHEN subscription_status = 'canceled' THEN 'free'
318 ELSE plan
319 END,
320 subscription_status = CASE
321 WHEN subscription_status = 'canceled' THEN 'none'
322 ELSE subscription_status
323 END,
324 locked_at = NULL,
325 lock_reason = NULL,
326 grace_until = NULL,
327 updated_at = now()
328 WHERE org_id = $1
329 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
330 ), org_update AS (
331 UPDATE orgs
332 SET plan = state.plan,
333 updated_at = now()
334 FROM state
335 WHERE orgs.id = state.org_id
336 RETURNING orgs.id
337 )
338 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 FROM state
339 `
340
341 type ClearBillingLockRow struct {
342 OrgID int64
343 Provider BillingProvider
344 StripeCustomerID pgtype.Text
345 StripeSubscriptionID pgtype.Text
346 StripeSubscriptionItemID pgtype.Text
347 Plan OrgPlan
348 SubscriptionStatus BillingSubscriptionStatus
349 BillableSeats int32
350 SeatSnapshotAt pgtype.Timestamptz
351 CurrentPeriodStart pgtype.Timestamptz
352 CurrentPeriodEnd pgtype.Timestamptz
353 CancelAtPeriodEnd bool
354 TrialEnd pgtype.Timestamptz
355 PastDueAt pgtype.Timestamptz
356 CanceledAt pgtype.Timestamptz
357 LockedAt pgtype.Timestamptz
358 LockReason NullBillingLockReason
359 GraceUntil pgtype.Timestamptz
360 LastWebhookEventID string
361 CreatedAt pgtype.Timestamptz
362 UpdatedAt pgtype.Timestamptz
363 }
364
365 func (q *Queries) ClearBillingLock(ctx context.Context, db DBTX, orgID int64) (ClearBillingLockRow, error) {
366 row := db.QueryRow(ctx, clearBillingLock, orgID)
367 var i ClearBillingLockRow
368 err := row.Scan(
369 &i.OrgID,
370 &i.Provider,
371 &i.StripeCustomerID,
372 &i.StripeSubscriptionID,
373 &i.StripeSubscriptionItemID,
374 &i.Plan,
375 &i.SubscriptionStatus,
376 &i.BillableSeats,
377 &i.SeatSnapshotAt,
378 &i.CurrentPeriodStart,
379 &i.CurrentPeriodEnd,
380 &i.CancelAtPeriodEnd,
381 &i.TrialEnd,
382 &i.PastDueAt,
383 &i.CanceledAt,
384 &i.LockedAt,
385 &i.LockReason,
386 &i.GraceUntil,
387 &i.LastWebhookEventID,
388 &i.CreatedAt,
389 &i.UpdatedAt,
390 )
391 return i, err
392 }
393
394 const clearUserBillingLock = `-- name: ClearUserBillingLock :one
395 WITH state AS (
396 UPDATE user_billing_states
397 SET plan = CASE
398 WHEN subscription_status = 'canceled' THEN 'free'
399 ELSE plan
400 END,
401 subscription_status = CASE
402 WHEN subscription_status = 'canceled' THEN 'none'
403 ELSE subscription_status
404 END,
405 locked_at = NULL,
406 lock_reason = NULL,
407 grace_until = NULL,
408 updated_at = now()
409 WHERE user_id = $1
410 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
411 ), user_update AS (
412 UPDATE users
413 SET plan = state.plan,
414 updated_at = now()
415 FROM state
416 WHERE users.id = state.user_id
417 RETURNING users.id
418 )
419 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 FROM state
420 `
421
422 type ClearUserBillingLockRow struct {
423 UserID int64
424 Provider BillingProvider
425 StripeCustomerID pgtype.Text
426 StripeSubscriptionID pgtype.Text
427 StripeSubscriptionItemID pgtype.Text
428 Plan UserPlan
429 SubscriptionStatus BillingSubscriptionStatus
430 CurrentPeriodStart pgtype.Timestamptz
431 CurrentPeriodEnd pgtype.Timestamptz
432 CancelAtPeriodEnd bool
433 TrialEnd pgtype.Timestamptz
434 PastDueAt pgtype.Timestamptz
435 CanceledAt pgtype.Timestamptz
436 LockedAt pgtype.Timestamptz
437 LockReason NullBillingLockReason
438 GraceUntil pgtype.Timestamptz
439 LastWebhookEventID string
440 CreatedAt pgtype.Timestamptz
441 UpdatedAt pgtype.Timestamptz
442 }
443
444 func (q *Queries) ClearUserBillingLock(ctx context.Context, db DBTX, userID int64) (ClearUserBillingLockRow, error) {
445 row := db.QueryRow(ctx, clearUserBillingLock, userID)
446 var i ClearUserBillingLockRow
447 err := row.Scan(
448 &i.UserID,
449 &i.Provider,
450 &i.StripeCustomerID,
451 &i.StripeSubscriptionID,
452 &i.StripeSubscriptionItemID,
453 &i.Plan,
454 &i.SubscriptionStatus,
455 &i.CurrentPeriodStart,
456 &i.CurrentPeriodEnd,
457 &i.CancelAtPeriodEnd,
458 &i.TrialEnd,
459 &i.PastDueAt,
460 &i.CanceledAt,
461 &i.LockedAt,
462 &i.LockReason,
463 &i.GraceUntil,
464 &i.LastWebhookEventID,
465 &i.CreatedAt,
466 &i.UpdatedAt,
467 )
468 return i, err
469 }
470
471 const countBillableOrgMembers = `-- name: CountBillableOrgMembers :one
472 SELECT count(*)::integer
473 FROM org_members
474 WHERE org_id = $1
475 `
476
477 func (q *Queries) CountBillableOrgMembers(ctx context.Context, db DBTX, orgID int64) (int32, error) {
478 row := db.QueryRow(ctx, countBillableOrgMembers, orgID)
479 var column_1 int32
480 err := row.Scan(&column_1)
481 return column_1, err
482 }
483
484 const countPendingOrgInvitations = `-- name: CountPendingOrgInvitations :one
485 SELECT count(*)::integer
486 FROM org_invitations
487 WHERE org_id = $1
488 AND accepted_at IS NULL
489 AND declined_at IS NULL
490 AND canceled_at IS NULL
491 AND expires_at > now()
492 `
493
494 func (q *Queries) CountPendingOrgInvitations(ctx context.Context, db DBTX, orgID int64) (int32, error) {
495 row := db.QueryRow(ctx, countPendingOrgInvitations, orgID)
496 var column_1 int32
497 err := row.Scan(&column_1)
498 return column_1, err
499 }
500
501 const createSeatSnapshot = `-- name: CreateSeatSnapshot :one
502
503 WITH snapshot AS (
504 INSERT INTO billing_seat_snapshots (
505 org_id,
506 provider,
507 stripe_subscription_id,
508 active_members,
509 billable_seats,
510 source
511 )
512 VALUES (
513 $1::bigint,
514 'stripe',
515 $2::text,
516 $3::integer,
517 $4::integer,
518 $5::text
519 )
520 RETURNING id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at
521 ), state AS (
522 INSERT INTO org_billing_states (org_id, billable_seats, seat_snapshot_at)
523 SELECT org_id, billable_seats, captured_at FROM snapshot
524 ON CONFLICT (org_id) DO UPDATE
525 SET billable_seats = EXCLUDED.billable_seats,
526 seat_snapshot_at = EXCLUDED.seat_snapshot_at,
527 updated_at = now()
528 RETURNING org_id
529 )
530 SELECT id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at FROM snapshot
531 `
532
533 type CreateSeatSnapshotParams struct {
534 OrgID int64
535 StripeSubscriptionID pgtype.Text
536 ActiveMembers int32
537 BillableSeats int32
538 Source string
539 }
540
541 type CreateSeatSnapshotRow struct {
542 ID int64
543 OrgID int64
544 Provider BillingProvider
545 StripeSubscriptionID pgtype.Text
546 ActiveMembers int32
547 BillableSeats int32
548 Source string
549 CapturedAt pgtype.Timestamptz
550 }
551
552 // ─── billing_seat_snapshots ────────────────────────────────────────
553 func (q *Queries) CreateSeatSnapshot(ctx context.Context, db DBTX, arg CreateSeatSnapshotParams) (CreateSeatSnapshotRow, error) {
554 row := db.QueryRow(ctx, createSeatSnapshot,
555 arg.OrgID,
556 arg.StripeSubscriptionID,
557 arg.ActiveMembers,
558 arg.BillableSeats,
559 arg.Source,
560 )
561 var i CreateSeatSnapshotRow
562 err := row.Scan(
563 &i.ID,
564 &i.OrgID,
565 &i.Provider,
566 &i.StripeSubscriptionID,
567 &i.ActiveMembers,
568 &i.BillableSeats,
569 &i.Source,
570 &i.CapturedAt,
571 )
572 return i, err
573 }
574
575 const createWebhookEventReceipt = `-- name: CreateWebhookEventReceipt :one
576
577 INSERT INTO billing_webhook_events (
578 provider,
579 provider_event_id,
580 event_type,
581 api_version,
582 payload
583 )
584 VALUES (
585 'stripe',
586 $1::text,
587 $2::text,
588 $3::text,
589 $4::jsonb
590 )
591 ON CONFLICT (provider, provider_event_id) DO NOTHING
592 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
593 `
594
595 type CreateWebhookEventReceiptParams struct {
596 ProviderEventID string
597 EventType string
598 ApiVersion string
599 Payload []byte
600 }
601
602 // ─── billing_webhook_events ────────────────────────────────────────
603 func (q *Queries) CreateWebhookEventReceipt(ctx context.Context, db DBTX, arg CreateWebhookEventReceiptParams) (BillingWebhookEvent, error) {
604 row := db.QueryRow(ctx, createWebhookEventReceipt,
605 arg.ProviderEventID,
606 arg.EventType,
607 arg.ApiVersion,
608 arg.Payload,
609 )
610 var i BillingWebhookEvent
611 err := row.Scan(
612 &i.ID,
613 &i.Provider,
614 &i.ProviderEventID,
615 &i.EventType,
616 &i.ApiVersion,
617 &i.Payload,
618 &i.ReceivedAt,
619 &i.ProcessedAt,
620 &i.ProcessError,
621 &i.ProcessingAttempts,
622 &i.SubjectKind,
623 &i.SubjectID,
624 )
625 return i, err
626 }
627
628 const getOrgBillingState = `-- name: GetOrgBillingState :one
629
630
631 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 FROM org_billing_states WHERE org_id = $1
632 `
633
634 // SPDX-License-Identifier: AGPL-3.0-or-later
635 // ─── org_billing_states ────────────────────────────────────────────
636 func (q *Queries) GetOrgBillingState(ctx context.Context, db DBTX, orgID int64) (OrgBillingState, error) {
637 row := db.QueryRow(ctx, getOrgBillingState, orgID)
638 var i OrgBillingState
639 err := row.Scan(
640 &i.OrgID,
641 &i.Provider,
642 &i.StripeCustomerID,
643 &i.StripeSubscriptionID,
644 &i.StripeSubscriptionItemID,
645 &i.Plan,
646 &i.SubscriptionStatus,
647 &i.BillableSeats,
648 &i.SeatSnapshotAt,
649 &i.CurrentPeriodStart,
650 &i.CurrentPeriodEnd,
651 &i.CancelAtPeriodEnd,
652 &i.TrialEnd,
653 &i.PastDueAt,
654 &i.CanceledAt,
655 &i.LockedAt,
656 &i.LockReason,
657 &i.GraceUntil,
658 &i.LastWebhookEventID,
659 &i.CreatedAt,
660 &i.UpdatedAt,
661 )
662 return i, err
663 }
664
665 const getOrgBillingStateByStripeCustomer = `-- name: GetOrgBillingStateByStripeCustomer :one
666 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 FROM org_billing_states
667 WHERE provider = 'stripe'
668 AND stripe_customer_id = $1
669 `
670
671 func (q *Queries) GetOrgBillingStateByStripeCustomer(ctx context.Context, db DBTX, stripeCustomerID pgtype.Text) (OrgBillingState, error) {
672 row := db.QueryRow(ctx, getOrgBillingStateByStripeCustomer, stripeCustomerID)
673 var i OrgBillingState
674 err := row.Scan(
675 &i.OrgID,
676 &i.Provider,
677 &i.StripeCustomerID,
678 &i.StripeSubscriptionID,
679 &i.StripeSubscriptionItemID,
680 &i.Plan,
681 &i.SubscriptionStatus,
682 &i.BillableSeats,
683 &i.SeatSnapshotAt,
684 &i.CurrentPeriodStart,
685 &i.CurrentPeriodEnd,
686 &i.CancelAtPeriodEnd,
687 &i.TrialEnd,
688 &i.PastDueAt,
689 &i.CanceledAt,
690 &i.LockedAt,
691 &i.LockReason,
692 &i.GraceUntil,
693 &i.LastWebhookEventID,
694 &i.CreatedAt,
695 &i.UpdatedAt,
696 )
697 return i, err
698 }
699
700 const getOrgBillingStateByStripeSubscription = `-- name: GetOrgBillingStateByStripeSubscription :one
701 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 FROM org_billing_states
702 WHERE provider = 'stripe'
703 AND stripe_subscription_id = $1
704 `
705
706 func (q *Queries) GetOrgBillingStateByStripeSubscription(ctx context.Context, db DBTX, stripeSubscriptionID pgtype.Text) (OrgBillingState, error) {
707 row := db.QueryRow(ctx, getOrgBillingStateByStripeSubscription, stripeSubscriptionID)
708 var i OrgBillingState
709 err := row.Scan(
710 &i.OrgID,
711 &i.Provider,
712 &i.StripeCustomerID,
713 &i.StripeSubscriptionID,
714 &i.StripeSubscriptionItemID,
715 &i.Plan,
716 &i.SubscriptionStatus,
717 &i.BillableSeats,
718 &i.SeatSnapshotAt,
719 &i.CurrentPeriodStart,
720 &i.CurrentPeriodEnd,
721 &i.CancelAtPeriodEnd,
722 &i.TrialEnd,
723 &i.PastDueAt,
724 &i.CanceledAt,
725 &i.LockedAt,
726 &i.LockReason,
727 &i.GraceUntil,
728 &i.LastWebhookEventID,
729 &i.CreatedAt,
730 &i.UpdatedAt,
731 )
732 return i, err
733 }
734
735 const getUserBillingState = `-- name: GetUserBillingState :one
736
737 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 FROM user_billing_states WHERE user_id = $1
738 `
739
740 // ─── user_billing_states (PRO03) ──────────────────────────────────
741 func (q *Queries) GetUserBillingState(ctx context.Context, db DBTX, userID int64) (UserBillingState, error) {
742 row := db.QueryRow(ctx, getUserBillingState, userID)
743 var i UserBillingState
744 err := row.Scan(
745 &i.UserID,
746 &i.Provider,
747 &i.StripeCustomerID,
748 &i.StripeSubscriptionID,
749 &i.StripeSubscriptionItemID,
750 &i.Plan,
751 &i.SubscriptionStatus,
752 &i.CurrentPeriodStart,
753 &i.CurrentPeriodEnd,
754 &i.CancelAtPeriodEnd,
755 &i.TrialEnd,
756 &i.PastDueAt,
757 &i.CanceledAt,
758 &i.LockedAt,
759 &i.LockReason,
760 &i.GraceUntil,
761 &i.LastWebhookEventID,
762 &i.CreatedAt,
763 &i.UpdatedAt,
764 )
765 return i, err
766 }
767
768 const getUserBillingStateByStripeCustomer = `-- name: GetUserBillingStateByStripeCustomer :one
769 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 FROM user_billing_states
770 WHERE provider = 'stripe'
771 AND stripe_customer_id = $1
772 `
773
774 func (q *Queries) GetUserBillingStateByStripeCustomer(ctx context.Context, db DBTX, stripeCustomerID pgtype.Text) (UserBillingState, error) {
775 row := db.QueryRow(ctx, getUserBillingStateByStripeCustomer, stripeCustomerID)
776 var i UserBillingState
777 err := row.Scan(
778 &i.UserID,
779 &i.Provider,
780 &i.StripeCustomerID,
781 &i.StripeSubscriptionID,
782 &i.StripeSubscriptionItemID,
783 &i.Plan,
784 &i.SubscriptionStatus,
785 &i.CurrentPeriodStart,
786 &i.CurrentPeriodEnd,
787 &i.CancelAtPeriodEnd,
788 &i.TrialEnd,
789 &i.PastDueAt,
790 &i.CanceledAt,
791 &i.LockedAt,
792 &i.LockReason,
793 &i.GraceUntil,
794 &i.LastWebhookEventID,
795 &i.CreatedAt,
796 &i.UpdatedAt,
797 )
798 return i, err
799 }
800
801 const getUserBillingStateByStripeSubscription = `-- name: GetUserBillingStateByStripeSubscription :one
802 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 FROM user_billing_states
803 WHERE provider = 'stripe'
804 AND stripe_subscription_id = $1
805 `
806
807 func (q *Queries) GetUserBillingStateByStripeSubscription(ctx context.Context, db DBTX, stripeSubscriptionID pgtype.Text) (UserBillingState, error) {
808 row := db.QueryRow(ctx, getUserBillingStateByStripeSubscription, stripeSubscriptionID)
809 var i UserBillingState
810 err := row.Scan(
811 &i.UserID,
812 &i.Provider,
813 &i.StripeCustomerID,
814 &i.StripeSubscriptionID,
815 &i.StripeSubscriptionItemID,
816 &i.Plan,
817 &i.SubscriptionStatus,
818 &i.CurrentPeriodStart,
819 &i.CurrentPeriodEnd,
820 &i.CancelAtPeriodEnd,
821 &i.TrialEnd,
822 &i.PastDueAt,
823 &i.CanceledAt,
824 &i.LockedAt,
825 &i.LockReason,
826 &i.GraceUntil,
827 &i.LastWebhookEventID,
828 &i.CreatedAt,
829 &i.UpdatedAt,
830 )
831 return i, err
832 }
833
834 const getWebhookEventReceipt = `-- name: GetWebhookEventReceipt :one
835 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
836 WHERE provider = 'stripe'
837 AND provider_event_id = $1
838 `
839
840 func (q *Queries) GetWebhookEventReceipt(ctx context.Context, db DBTX, providerEventID string) (BillingWebhookEvent, error) {
841 row := db.QueryRow(ctx, getWebhookEventReceipt, providerEventID)
842 var i BillingWebhookEvent
843 err := row.Scan(
844 &i.ID,
845 &i.Provider,
846 &i.ProviderEventID,
847 &i.EventType,
848 &i.ApiVersion,
849 &i.Payload,
850 &i.ReceivedAt,
851 &i.ProcessedAt,
852 &i.ProcessError,
853 &i.ProcessingAttempts,
854 &i.SubjectKind,
855 &i.SubjectID,
856 )
857 return i, err
858 }
859
860 const listInvoicesForOrg = `-- name: ListInvoicesForOrg :many
861 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
862 WHERE subject_kind = 'org' AND subject_id = $1
863 ORDER BY created_at DESC, id DESC
864 LIMIT $2
865 `
866
867 type ListInvoicesForOrgParams struct {
868 SubjectID int64
869 Limit int32
870 }
871
872 // PRO03: filters on the polymorphic subject columns so the index
873 // billing_invoices_subject_created_idx services this query. The
874 // legacy `org_id` column is kept populated by UpsertInvoice for the
875 // transitional window; this query no longer reads it.
876 func (q *Queries) ListInvoicesForOrg(ctx context.Context, db DBTX, arg ListInvoicesForOrgParams) ([]BillingInvoice, error) {
877 rows, err := db.Query(ctx, listInvoicesForOrg, arg.SubjectID, arg.Limit)
878 if err != nil {
879 return nil, err
880 }
881 defer rows.Close()
882 items := []BillingInvoice{}
883 for rows.Next() {
884 var i BillingInvoice
885 if err := rows.Scan(
886 &i.ID,
887 &i.OrgID,
888 &i.Provider,
889 &i.StripeInvoiceID,
890 &i.StripeCustomerID,
891 &i.StripeSubscriptionID,
892 &i.Status,
893 &i.Number,
894 &i.Currency,
895 &i.AmountDueCents,
896 &i.AmountPaidCents,
897 &i.AmountRemainingCents,
898 &i.HostedInvoiceUrl,
899 &i.InvoicePdfUrl,
900 &i.PeriodStart,
901 &i.PeriodEnd,
902 &i.DueAt,
903 &i.PaidAt,
904 &i.VoidedAt,
905 &i.CreatedAt,
906 &i.UpdatedAt,
907 &i.SubjectKind,
908 &i.SubjectID,
909 ); err != nil {
910 return nil, err
911 }
912 items = append(items, i)
913 }
914 if err := rows.Err(); err != nil {
915 return nil, err
916 }
917 return items, nil
918 }
919
920 const listInvoicesForSubject = `-- name: ListInvoicesForSubject :many
921 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
922 WHERE subject_kind = $1::billing_subject_kind
923 AND subject_id = $2::bigint
924 ORDER BY created_at DESC, id DESC
925 LIMIT $3::integer
926 `
927
928 type ListInvoicesForSubjectParams struct {
929 SubjectKind BillingSubjectKind
930 SubjectID int64
931 Lim int32
932 }
933
934 // Polymorphic invoice listing for PRO04+ callers. The org-flavored
935 // ListInvoicesForOrg above is the same query with subject_kind
936 // hard-coded; this surface lets a user-side caller pass kind='user'
937 // without forking the helper.
938 func (q *Queries) ListInvoicesForSubject(ctx context.Context, db DBTX, arg ListInvoicesForSubjectParams) ([]BillingInvoice, error) {
939 rows, err := db.Query(ctx, listInvoicesForSubject, arg.SubjectKind, arg.SubjectID, arg.Lim)
940 if err != nil {
941 return nil, err
942 }
943 defer rows.Close()
944 items := []BillingInvoice{}
945 for rows.Next() {
946 var i BillingInvoice
947 if err := rows.Scan(
948 &i.ID,
949 &i.OrgID,
950 &i.Provider,
951 &i.StripeInvoiceID,
952 &i.StripeCustomerID,
953 &i.StripeSubscriptionID,
954 &i.Status,
955 &i.Number,
956 &i.Currency,
957 &i.AmountDueCents,
958 &i.AmountPaidCents,
959 &i.AmountRemainingCents,
960 &i.HostedInvoiceUrl,
961 &i.InvoicePdfUrl,
962 &i.PeriodStart,
963 &i.PeriodEnd,
964 &i.DueAt,
965 &i.PaidAt,
966 &i.VoidedAt,
967 &i.CreatedAt,
968 &i.UpdatedAt,
969 &i.SubjectKind,
970 &i.SubjectID,
971 ); err != nil {
972 return nil, err
973 }
974 items = append(items, i)
975 }
976 if err := rows.Err(); err != nil {
977 return nil, err
978 }
979 return items, nil
980 }
981
982 const listSeatSnapshotsForOrg = `-- name: ListSeatSnapshotsForOrg :many
983 SELECT id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at FROM billing_seat_snapshots
984 WHERE org_id = $1
985 ORDER BY captured_at DESC, id DESC
986 LIMIT $2
987 `
988
989 type ListSeatSnapshotsForOrgParams struct {
990 OrgID int64
991 Limit int32
992 }
993
994 func (q *Queries) ListSeatSnapshotsForOrg(ctx context.Context, db DBTX, arg ListSeatSnapshotsForOrgParams) ([]BillingSeatSnapshot, error) {
995 rows, err := db.Query(ctx, listSeatSnapshotsForOrg, arg.OrgID, arg.Limit)
996 if err != nil {
997 return nil, err
998 }
999 defer rows.Close()
1000 items := []BillingSeatSnapshot{}
1001 for rows.Next() {
1002 var i BillingSeatSnapshot
1003 if err := rows.Scan(
1004 &i.ID,
1005 &i.OrgID,
1006 &i.Provider,
1007 &i.StripeSubscriptionID,
1008 &i.ActiveMembers,
1009 &i.BillableSeats,
1010 &i.Source,
1011 &i.CapturedAt,
1012 ); err != nil {
1013 return nil, err
1014 }
1015 items = append(items, i)
1016 }
1017 if err := rows.Err(); err != nil {
1018 return nil, err
1019 }
1020 return items, nil
1021 }
1022
1023 const markCanceled = `-- name: MarkCanceled :one
1024 WITH state AS (
1025 UPDATE org_billing_states
1026 SET plan = 'free',
1027 subscription_status = 'canceled',
1028 canceled_at = COALESCE(canceled_at, now()),
1029 locked_at = now(),
1030 lock_reason = 'canceled',
1031 grace_until = NULL,
1032 cancel_at_period_end = false,
1033 last_webhook_event_id = $1::text,
1034 updated_at = now()
1035 WHERE org_id = $2::bigint
1036 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
1037 ), org_update AS (
1038 UPDATE orgs
1039 SET plan = 'free',
1040 updated_at = now()
1041 WHERE id = $2::bigint
1042 RETURNING id
1043 )
1044 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 FROM state
1045 `
1046
1047 type MarkCanceledParams struct {
1048 LastWebhookEventID string
1049 OrgID int64
1050 }
1051
1052 type MarkCanceledRow struct {
1053 OrgID int64
1054 Provider BillingProvider
1055 StripeCustomerID pgtype.Text
1056 StripeSubscriptionID pgtype.Text
1057 StripeSubscriptionItemID pgtype.Text
1058 Plan OrgPlan
1059 SubscriptionStatus BillingSubscriptionStatus
1060 BillableSeats int32
1061 SeatSnapshotAt pgtype.Timestamptz
1062 CurrentPeriodStart pgtype.Timestamptz
1063 CurrentPeriodEnd pgtype.Timestamptz
1064 CancelAtPeriodEnd bool
1065 TrialEnd pgtype.Timestamptz
1066 PastDueAt pgtype.Timestamptz
1067 CanceledAt pgtype.Timestamptz
1068 LockedAt pgtype.Timestamptz
1069 LockReason NullBillingLockReason
1070 GraceUntil pgtype.Timestamptz
1071 LastWebhookEventID string
1072 CreatedAt pgtype.Timestamptz
1073 UpdatedAt pgtype.Timestamptz
1074 }
1075
1076 func (q *Queries) MarkCanceled(ctx context.Context, db DBTX, arg MarkCanceledParams) (MarkCanceledRow, error) {
1077 row := db.QueryRow(ctx, markCanceled, arg.LastWebhookEventID, arg.OrgID)
1078 var i MarkCanceledRow
1079 err := row.Scan(
1080 &i.OrgID,
1081 &i.Provider,
1082 &i.StripeCustomerID,
1083 &i.StripeSubscriptionID,
1084 &i.StripeSubscriptionItemID,
1085 &i.Plan,
1086 &i.SubscriptionStatus,
1087 &i.BillableSeats,
1088 &i.SeatSnapshotAt,
1089 &i.CurrentPeriodStart,
1090 &i.CurrentPeriodEnd,
1091 &i.CancelAtPeriodEnd,
1092 &i.TrialEnd,
1093 &i.PastDueAt,
1094 &i.CanceledAt,
1095 &i.LockedAt,
1096 &i.LockReason,
1097 &i.GraceUntil,
1098 &i.LastWebhookEventID,
1099 &i.CreatedAt,
1100 &i.UpdatedAt,
1101 )
1102 return i, err
1103 }
1104
1105 const markPastDue = `-- name: MarkPastDue :one
1106 UPDATE org_billing_states
1107 SET subscription_status = 'past_due',
1108 past_due_at = COALESCE(past_due_at, now()),
1109 locked_at = now(),
1110 lock_reason = 'past_due',
1111 grace_until = $1::timestamptz,
1112 last_webhook_event_id = $2::text,
1113 updated_at = now()
1114 WHERE org_id = $3::bigint
1115 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
1116 `
1117
1118 type MarkPastDueParams struct {
1119 GraceUntil pgtype.Timestamptz
1120 LastWebhookEventID string
1121 OrgID int64
1122 }
1123
1124 func (q *Queries) MarkPastDue(ctx context.Context, db DBTX, arg MarkPastDueParams) (OrgBillingState, error) {
1125 row := db.QueryRow(ctx, markPastDue, arg.GraceUntil, arg.LastWebhookEventID, arg.OrgID)
1126 var i OrgBillingState
1127 err := row.Scan(
1128 &i.OrgID,
1129 &i.Provider,
1130 &i.StripeCustomerID,
1131 &i.StripeSubscriptionID,
1132 &i.StripeSubscriptionItemID,
1133 &i.Plan,
1134 &i.SubscriptionStatus,
1135 &i.BillableSeats,
1136 &i.SeatSnapshotAt,
1137 &i.CurrentPeriodStart,
1138 &i.CurrentPeriodEnd,
1139 &i.CancelAtPeriodEnd,
1140 &i.TrialEnd,
1141 &i.PastDueAt,
1142 &i.CanceledAt,
1143 &i.LockedAt,
1144 &i.LockReason,
1145 &i.GraceUntil,
1146 &i.LastWebhookEventID,
1147 &i.CreatedAt,
1148 &i.UpdatedAt,
1149 )
1150 return i, err
1151 }
1152
1153 const markPaymentSucceeded = `-- name: MarkPaymentSucceeded :one
1154 WITH state AS (
1155 UPDATE org_billing_states
1156 SET plan = CASE
1157 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'team'
1158 ELSE plan
1159 END,
1160 subscription_status = CASE
1161 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
1162 ELSE subscription_status
1163 END,
1164 past_due_at = CASE
1165 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
1166 ELSE past_due_at
1167 END,
1168 locked_at = NULL,
1169 lock_reason = NULL,
1170 grace_until = NULL,
1171 last_webhook_event_id = $1::text,
1172 updated_at = now()
1173 WHERE org_id = $2::bigint
1174 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
1175 ), org_update AS (
1176 UPDATE orgs
1177 SET plan = state.plan,
1178 updated_at = now()
1179 FROM state
1180 WHERE orgs.id = state.org_id
1181 RETURNING orgs.id
1182 )
1183 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 FROM state
1184 `
1185
1186 type MarkPaymentSucceededParams struct {
1187 LastWebhookEventID string
1188 OrgID int64
1189 }
1190
1191 type MarkPaymentSucceededRow struct {
1192 OrgID int64
1193 Provider BillingProvider
1194 StripeCustomerID pgtype.Text
1195 StripeSubscriptionID pgtype.Text
1196 StripeSubscriptionItemID pgtype.Text
1197 Plan OrgPlan
1198 SubscriptionStatus BillingSubscriptionStatus
1199 BillableSeats int32
1200 SeatSnapshotAt pgtype.Timestamptz
1201 CurrentPeriodStart pgtype.Timestamptz
1202 CurrentPeriodEnd pgtype.Timestamptz
1203 CancelAtPeriodEnd bool
1204 TrialEnd pgtype.Timestamptz
1205 PastDueAt pgtype.Timestamptz
1206 CanceledAt pgtype.Timestamptz
1207 LockedAt pgtype.Timestamptz
1208 LockReason NullBillingLockReason
1209 GraceUntil pgtype.Timestamptz
1210 LastWebhookEventID string
1211 CreatedAt pgtype.Timestamptz
1212 UpdatedAt pgtype.Timestamptz
1213 }
1214
1215 func (q *Queries) MarkPaymentSucceeded(ctx context.Context, db DBTX, arg MarkPaymentSucceededParams) (MarkPaymentSucceededRow, error) {
1216 row := db.QueryRow(ctx, markPaymentSucceeded, arg.LastWebhookEventID, arg.OrgID)
1217 var i MarkPaymentSucceededRow
1218 err := row.Scan(
1219 &i.OrgID,
1220 &i.Provider,
1221 &i.StripeCustomerID,
1222 &i.StripeSubscriptionID,
1223 &i.StripeSubscriptionItemID,
1224 &i.Plan,
1225 &i.SubscriptionStatus,
1226 &i.BillableSeats,
1227 &i.SeatSnapshotAt,
1228 &i.CurrentPeriodStart,
1229 &i.CurrentPeriodEnd,
1230 &i.CancelAtPeriodEnd,
1231 &i.TrialEnd,
1232 &i.PastDueAt,
1233 &i.CanceledAt,
1234 &i.LockedAt,
1235 &i.LockReason,
1236 &i.GraceUntil,
1237 &i.LastWebhookEventID,
1238 &i.CreatedAt,
1239 &i.UpdatedAt,
1240 )
1241 return i, err
1242 }
1243
1244 const markUserCanceled = `-- name: MarkUserCanceled :one
1245 WITH state AS (
1246 UPDATE user_billing_states
1247 SET plan = 'free',
1248 subscription_status = 'canceled',
1249 canceled_at = COALESCE(canceled_at, now()),
1250 locked_at = now(),
1251 lock_reason = 'canceled',
1252 grace_until = NULL,
1253 cancel_at_period_end = false,
1254 last_webhook_event_id = $1::text,
1255 updated_at = now()
1256 WHERE user_id = $2::bigint
1257 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
1258 ), user_update AS (
1259 UPDATE users
1260 SET plan = 'free',
1261 updated_at = now()
1262 WHERE id = $2::bigint
1263 RETURNING id
1264 )
1265 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 FROM state
1266 `
1267
1268 type MarkUserCanceledParams struct {
1269 LastWebhookEventID string
1270 UserID int64
1271 }
1272
1273 type MarkUserCanceledRow struct {
1274 UserID int64
1275 Provider BillingProvider
1276 StripeCustomerID pgtype.Text
1277 StripeSubscriptionID pgtype.Text
1278 StripeSubscriptionItemID pgtype.Text
1279 Plan UserPlan
1280 SubscriptionStatus BillingSubscriptionStatus
1281 CurrentPeriodStart pgtype.Timestamptz
1282 CurrentPeriodEnd pgtype.Timestamptz
1283 CancelAtPeriodEnd bool
1284 TrialEnd pgtype.Timestamptz
1285 PastDueAt pgtype.Timestamptz
1286 CanceledAt pgtype.Timestamptz
1287 LockedAt pgtype.Timestamptz
1288 LockReason NullBillingLockReason
1289 GraceUntil pgtype.Timestamptz
1290 LastWebhookEventID string
1291 CreatedAt pgtype.Timestamptz
1292 UpdatedAt pgtype.Timestamptz
1293 }
1294
1295 func (q *Queries) MarkUserCanceled(ctx context.Context, db DBTX, arg MarkUserCanceledParams) (MarkUserCanceledRow, error) {
1296 row := db.QueryRow(ctx, markUserCanceled, arg.LastWebhookEventID, arg.UserID)
1297 var i MarkUserCanceledRow
1298 err := row.Scan(
1299 &i.UserID,
1300 &i.Provider,
1301 &i.StripeCustomerID,
1302 &i.StripeSubscriptionID,
1303 &i.StripeSubscriptionItemID,
1304 &i.Plan,
1305 &i.SubscriptionStatus,
1306 &i.CurrentPeriodStart,
1307 &i.CurrentPeriodEnd,
1308 &i.CancelAtPeriodEnd,
1309 &i.TrialEnd,
1310 &i.PastDueAt,
1311 &i.CanceledAt,
1312 &i.LockedAt,
1313 &i.LockReason,
1314 &i.GraceUntil,
1315 &i.LastWebhookEventID,
1316 &i.CreatedAt,
1317 &i.UpdatedAt,
1318 )
1319 return i, err
1320 }
1321
1322 const markUserPastDue = `-- name: MarkUserPastDue :one
1323 UPDATE user_billing_states
1324 SET subscription_status = 'past_due',
1325 past_due_at = COALESCE(past_due_at, now()),
1326 locked_at = now(),
1327 lock_reason = 'past_due',
1328 grace_until = $1::timestamptz,
1329 last_webhook_event_id = $2::text,
1330 updated_at = now()
1331 WHERE user_id = $3::bigint
1332 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
1333 `
1334
1335 type MarkUserPastDueParams struct {
1336 GraceUntil pgtype.Timestamptz
1337 LastWebhookEventID string
1338 UserID int64
1339 }
1340
1341 func (q *Queries) MarkUserPastDue(ctx context.Context, db DBTX, arg MarkUserPastDueParams) (UserBillingState, error) {
1342 row := db.QueryRow(ctx, markUserPastDue, arg.GraceUntil, arg.LastWebhookEventID, arg.UserID)
1343 var i UserBillingState
1344 err := row.Scan(
1345 &i.UserID,
1346 &i.Provider,
1347 &i.StripeCustomerID,
1348 &i.StripeSubscriptionID,
1349 &i.StripeSubscriptionItemID,
1350 &i.Plan,
1351 &i.SubscriptionStatus,
1352 &i.CurrentPeriodStart,
1353 &i.CurrentPeriodEnd,
1354 &i.CancelAtPeriodEnd,
1355 &i.TrialEnd,
1356 &i.PastDueAt,
1357 &i.CanceledAt,
1358 &i.LockedAt,
1359 &i.LockReason,
1360 &i.GraceUntil,
1361 &i.LastWebhookEventID,
1362 &i.CreatedAt,
1363 &i.UpdatedAt,
1364 )
1365 return i, err
1366 }
1367
1368 const markUserPaymentSucceeded = `-- name: MarkUserPaymentSucceeded :one
1369 WITH state AS (
1370 UPDATE user_billing_states
1371 SET plan = CASE
1372 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'pro'
1373 ELSE plan
1374 END,
1375 subscription_status = CASE
1376 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
1377 ELSE subscription_status
1378 END,
1379 past_due_at = CASE
1380 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
1381 ELSE past_due_at
1382 END,
1383 locked_at = NULL,
1384 lock_reason = NULL,
1385 grace_until = NULL,
1386 last_webhook_event_id = $1::text,
1387 updated_at = now()
1388 WHERE user_id = $2::bigint
1389 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
1390 ), user_update AS (
1391 UPDATE users
1392 SET plan = state.plan,
1393 updated_at = now()
1394 FROM state
1395 WHERE users.id = state.user_id
1396 RETURNING users.id
1397 )
1398 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 FROM state
1399 `
1400
1401 type MarkUserPaymentSucceededParams struct {
1402 LastWebhookEventID string
1403 UserID int64
1404 }
1405
1406 type MarkUserPaymentSucceededRow struct {
1407 UserID int64
1408 Provider BillingProvider
1409 StripeCustomerID pgtype.Text
1410 StripeSubscriptionID pgtype.Text
1411 StripeSubscriptionItemID pgtype.Text
1412 Plan UserPlan
1413 SubscriptionStatus BillingSubscriptionStatus
1414 CurrentPeriodStart pgtype.Timestamptz
1415 CurrentPeriodEnd pgtype.Timestamptz
1416 CancelAtPeriodEnd bool
1417 TrialEnd pgtype.Timestamptz
1418 PastDueAt pgtype.Timestamptz
1419 CanceledAt pgtype.Timestamptz
1420 LockedAt pgtype.Timestamptz
1421 LockReason NullBillingLockReason
1422 GraceUntil pgtype.Timestamptz
1423 LastWebhookEventID string
1424 CreatedAt pgtype.Timestamptz
1425 UpdatedAt pgtype.Timestamptz
1426 }
1427
1428 func (q *Queries) MarkUserPaymentSucceeded(ctx context.Context, db DBTX, arg MarkUserPaymentSucceededParams) (MarkUserPaymentSucceededRow, error) {
1429 row := db.QueryRow(ctx, markUserPaymentSucceeded, arg.LastWebhookEventID, arg.UserID)
1430 var i MarkUserPaymentSucceededRow
1431 err := row.Scan(
1432 &i.UserID,
1433 &i.Provider,
1434 &i.StripeCustomerID,
1435 &i.StripeSubscriptionID,
1436 &i.StripeSubscriptionItemID,
1437 &i.Plan,
1438 &i.SubscriptionStatus,
1439 &i.CurrentPeriodStart,
1440 &i.CurrentPeriodEnd,
1441 &i.CancelAtPeriodEnd,
1442 &i.TrialEnd,
1443 &i.PastDueAt,
1444 &i.CanceledAt,
1445 &i.LockedAt,
1446 &i.LockReason,
1447 &i.GraceUntil,
1448 &i.LastWebhookEventID,
1449 &i.CreatedAt,
1450 &i.UpdatedAt,
1451 )
1452 return i, err
1453 }
1454
1455 const markWebhookEventFailed = `-- name: MarkWebhookEventFailed :one
1456 UPDATE billing_webhook_events
1457 SET process_error = $2,
1458 processing_attempts = processing_attempts + 1
1459 WHERE provider = 'stripe'
1460 AND provider_event_id = $1
1461 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
1462 `
1463
1464 type MarkWebhookEventFailedParams struct {
1465 ProviderEventID string
1466 ProcessError string
1467 }
1468
1469 func (q *Queries) MarkWebhookEventFailed(ctx context.Context, db DBTX, arg MarkWebhookEventFailedParams) (BillingWebhookEvent, error) {
1470 row := db.QueryRow(ctx, markWebhookEventFailed, arg.ProviderEventID, arg.ProcessError)
1471 var i BillingWebhookEvent
1472 err := row.Scan(
1473 &i.ID,
1474 &i.Provider,
1475 &i.ProviderEventID,
1476 &i.EventType,
1477 &i.ApiVersion,
1478 &i.Payload,
1479 &i.ReceivedAt,
1480 &i.ProcessedAt,
1481 &i.ProcessError,
1482 &i.ProcessingAttempts,
1483 &i.SubjectKind,
1484 &i.SubjectID,
1485 )
1486 return i, err
1487 }
1488
1489 const markWebhookEventProcessed = `-- name: MarkWebhookEventProcessed :one
1490 UPDATE billing_webhook_events
1491 SET processed_at = now(),
1492 process_error = '',
1493 processing_attempts = processing_attempts + 1
1494 WHERE provider = 'stripe'
1495 AND provider_event_id = $1
1496 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
1497 `
1498
1499 func (q *Queries) MarkWebhookEventProcessed(ctx context.Context, db DBTX, providerEventID string) (BillingWebhookEvent, error) {
1500 row := db.QueryRow(ctx, markWebhookEventProcessed, providerEventID)
1501 var i BillingWebhookEvent
1502 err := row.Scan(
1503 &i.ID,
1504 &i.Provider,
1505 &i.ProviderEventID,
1506 &i.EventType,
1507 &i.ApiVersion,
1508 &i.Payload,
1509 &i.ReceivedAt,
1510 &i.ProcessedAt,
1511 &i.ProcessError,
1512 &i.ProcessingAttempts,
1513 &i.SubjectKind,
1514 &i.SubjectID,
1515 )
1516 return i, err
1517 }
1518
1519 const setStripeCustomer = `-- name: SetStripeCustomer :one
1520 INSERT INTO org_billing_states (org_id, provider, stripe_customer_id)
1521 VALUES ($1, 'stripe', $2)
1522 ON CONFLICT (org_id) DO UPDATE
1523 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
1524 provider = 'stripe',
1525 updated_at = now()
1526 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
1527 `
1528
1529 type SetStripeCustomerParams struct {
1530 OrgID int64
1531 StripeCustomerID pgtype.Text
1532 }
1533
1534 func (q *Queries) SetStripeCustomer(ctx context.Context, db DBTX, arg SetStripeCustomerParams) (OrgBillingState, error) {
1535 row := db.QueryRow(ctx, setStripeCustomer, arg.OrgID, arg.StripeCustomerID)
1536 var i OrgBillingState
1537 err := row.Scan(
1538 &i.OrgID,
1539 &i.Provider,
1540 &i.StripeCustomerID,
1541 &i.StripeSubscriptionID,
1542 &i.StripeSubscriptionItemID,
1543 &i.Plan,
1544 &i.SubscriptionStatus,
1545 &i.BillableSeats,
1546 &i.SeatSnapshotAt,
1547 &i.CurrentPeriodStart,
1548 &i.CurrentPeriodEnd,
1549 &i.CancelAtPeriodEnd,
1550 &i.TrialEnd,
1551 &i.PastDueAt,
1552 &i.CanceledAt,
1553 &i.LockedAt,
1554 &i.LockReason,
1555 &i.GraceUntil,
1556 &i.LastWebhookEventID,
1557 &i.CreatedAt,
1558 &i.UpdatedAt,
1559 )
1560 return i, err
1561 }
1562
1563 const setUserStripeCustomer = `-- name: SetUserStripeCustomer :one
1564 INSERT INTO user_billing_states (user_id, provider, stripe_customer_id)
1565 VALUES ($1, 'stripe', $2)
1566 ON CONFLICT (user_id) DO UPDATE
1567 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
1568 provider = 'stripe',
1569 updated_at = now()
1570 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
1571 `
1572
1573 type SetUserStripeCustomerParams struct {
1574 UserID int64
1575 StripeCustomerID pgtype.Text
1576 }
1577
1578 func (q *Queries) SetUserStripeCustomer(ctx context.Context, db DBTX, arg SetUserStripeCustomerParams) (UserBillingState, error) {
1579 row := db.QueryRow(ctx, setUserStripeCustomer, arg.UserID, arg.StripeCustomerID)
1580 var i UserBillingState
1581 err := row.Scan(
1582 &i.UserID,
1583 &i.Provider,
1584 &i.StripeCustomerID,
1585 &i.StripeSubscriptionID,
1586 &i.StripeSubscriptionItemID,
1587 &i.Plan,
1588 &i.SubscriptionStatus,
1589 &i.CurrentPeriodStart,
1590 &i.CurrentPeriodEnd,
1591 &i.CancelAtPeriodEnd,
1592 &i.TrialEnd,
1593 &i.PastDueAt,
1594 &i.CanceledAt,
1595 &i.LockedAt,
1596 &i.LockReason,
1597 &i.GraceUntil,
1598 &i.LastWebhookEventID,
1599 &i.CreatedAt,
1600 &i.UpdatedAt,
1601 )
1602 return i, err
1603 }
1604
1605 const upsertInvoice = `-- name: UpsertInvoice :one
1606
1607 INSERT INTO billing_invoices (
1608 org_id,
1609 subject_kind,
1610 subject_id,
1611 provider,
1612 stripe_invoice_id,
1613 stripe_customer_id,
1614 stripe_subscription_id,
1615 status,
1616 number,
1617 currency,
1618 amount_due_cents,
1619 amount_paid_cents,
1620 amount_remaining_cents,
1621 hosted_invoice_url,
1622 invoice_pdf_url,
1623 period_start,
1624 period_end,
1625 due_at,
1626 paid_at,
1627 voided_at
1628 )
1629 VALUES (
1630 $1::bigint,
1631 'org'::billing_subject_kind,
1632 $1::bigint,
1633 'stripe',
1634 $2::text,
1635 $3::text,
1636 $4::text,
1637 $5::billing_invoice_status,
1638 $6::text,
1639 $7::text,
1640 $8::bigint,
1641 $9::bigint,
1642 $10::bigint,
1643 $11::text,
1644 $12::text,
1645 $13::timestamptz,
1646 $14::timestamptz,
1647 $15::timestamptz,
1648 $16::timestamptz,
1649 $17::timestamptz
1650 )
1651 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
1652 SET org_id = EXCLUDED.org_id,
1653 stripe_customer_id = EXCLUDED.stripe_customer_id,
1654 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
1655 status = EXCLUDED.status,
1656 number = EXCLUDED.number,
1657 currency = EXCLUDED.currency,
1658 amount_due_cents = EXCLUDED.amount_due_cents,
1659 amount_paid_cents = EXCLUDED.amount_paid_cents,
1660 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
1661 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
1662 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
1663 period_start = EXCLUDED.period_start,
1664 period_end = EXCLUDED.period_end,
1665 due_at = EXCLUDED.due_at,
1666 paid_at = EXCLUDED.paid_at,
1667 voided_at = EXCLUDED.voided_at,
1668 updated_at = now()
1669 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
1670 `
1671
1672 type UpsertInvoiceParams struct {
1673 OrgID int64
1674 StripeInvoiceID string
1675 StripeCustomerID string
1676 StripeSubscriptionID pgtype.Text
1677 Status BillingInvoiceStatus
1678 Number string
1679 Currency string
1680 AmountDueCents int64
1681 AmountPaidCents int64
1682 AmountRemainingCents int64
1683 HostedInvoiceUrl string
1684 InvoicePdfUrl string
1685 PeriodStart pgtype.Timestamptz
1686 PeriodEnd pgtype.Timestamptz
1687 DueAt pgtype.Timestamptz
1688 PaidAt pgtype.Timestamptz
1689 VoidedAt pgtype.Timestamptz
1690 }
1691
1692 // ─── billing_invoices ──────────────────────────────────────────────
1693 // PRO03: writes both legacy `org_id` and polymorphic
1694 // `(subject_kind, subject_id)`. Callers continue to bind org_id only;
1695 // the subject columns are derived. After PRO04 migrates all callers
1696 // to the polymorphic shape, a follow-up migration drops `org_id` and
1697 // this query loses the legacy column from its INSERT list.
1698 func (q *Queries) UpsertInvoice(ctx context.Context, db DBTX, arg UpsertInvoiceParams) (BillingInvoice, error) {
1699 row := db.QueryRow(ctx, upsertInvoice,
1700 arg.OrgID,
1701 arg.StripeInvoiceID,
1702 arg.StripeCustomerID,
1703 arg.StripeSubscriptionID,
1704 arg.Status,
1705 arg.Number,
1706 arg.Currency,
1707 arg.AmountDueCents,
1708 arg.AmountPaidCents,
1709 arg.AmountRemainingCents,
1710 arg.HostedInvoiceUrl,
1711 arg.InvoicePdfUrl,
1712 arg.PeriodStart,
1713 arg.PeriodEnd,
1714 arg.DueAt,
1715 arg.PaidAt,
1716 arg.VoidedAt,
1717 )
1718 var i BillingInvoice
1719 err := row.Scan(
1720 &i.ID,
1721 &i.OrgID,
1722 &i.Provider,
1723 &i.StripeInvoiceID,
1724 &i.StripeCustomerID,
1725 &i.StripeSubscriptionID,
1726 &i.Status,
1727 &i.Number,
1728 &i.Currency,
1729 &i.AmountDueCents,
1730 &i.AmountPaidCents,
1731 &i.AmountRemainingCents,
1732 &i.HostedInvoiceUrl,
1733 &i.InvoicePdfUrl,
1734 &i.PeriodStart,
1735 &i.PeriodEnd,
1736 &i.DueAt,
1737 &i.PaidAt,
1738 &i.VoidedAt,
1739 &i.CreatedAt,
1740 &i.UpdatedAt,
1741 &i.SubjectKind,
1742 &i.SubjectID,
1743 )
1744 return i, err
1745 }
1746