shithub Public

Watch 1 Fork 0 Star 0
Go · 33441 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 clearBillingLock = `-- name: ClearBillingLock :one
165 WITH state AS (
166 UPDATE org_billing_states
167 SET plan = CASE
168 WHEN subscription_status = 'canceled' THEN 'free'
169 ELSE plan
170 END,
171 subscription_status = CASE
172 WHEN subscription_status = 'canceled' THEN 'none'
173 ELSE subscription_status
174 END,
175 locked_at = NULL,
176 lock_reason = NULL,
177 grace_until = NULL,
178 updated_at = now()
179 WHERE org_id = $1
180 RETURNING 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
181 ), org_update AS (
182 UPDATE orgs
183 SET plan = state.plan,
184 updated_at = now()
185 FROM state
186 WHERE orgs.id = state.org_id
187 RETURNING orgs.id
188 )
189 SELECT 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
190 `
191
192 type ClearBillingLockRow struct {
193 OrgID int64
194 Provider BillingProvider
195 StripeCustomerID pgtype.Text
196 StripeSubscriptionID pgtype.Text
197 StripeSubscriptionItemID pgtype.Text
198 Plan OrgPlan
199 SubscriptionStatus BillingSubscriptionStatus
200 BillableSeats int32
201 SeatSnapshotAt pgtype.Timestamptz
202 CurrentPeriodStart pgtype.Timestamptz
203 CurrentPeriodEnd pgtype.Timestamptz
204 CancelAtPeriodEnd bool
205 TrialEnd pgtype.Timestamptz
206 PastDueAt pgtype.Timestamptz
207 CanceledAt pgtype.Timestamptz
208 LockedAt pgtype.Timestamptz
209 LockReason NullBillingLockReason
210 GraceUntil pgtype.Timestamptz
211 LastWebhookEventID string
212 CreatedAt pgtype.Timestamptz
213 UpdatedAt pgtype.Timestamptz
214 }
215
216 func (q *Queries) ClearBillingLock(ctx context.Context, db DBTX, orgID int64) (ClearBillingLockRow, error) {
217 row := db.QueryRow(ctx, clearBillingLock, orgID)
218 var i ClearBillingLockRow
219 err := row.Scan(
220 &i.OrgID,
221 &i.Provider,
222 &i.StripeCustomerID,
223 &i.StripeSubscriptionID,
224 &i.StripeSubscriptionItemID,
225 &i.Plan,
226 &i.SubscriptionStatus,
227 &i.BillableSeats,
228 &i.SeatSnapshotAt,
229 &i.CurrentPeriodStart,
230 &i.CurrentPeriodEnd,
231 &i.CancelAtPeriodEnd,
232 &i.TrialEnd,
233 &i.PastDueAt,
234 &i.CanceledAt,
235 &i.LockedAt,
236 &i.LockReason,
237 &i.GraceUntil,
238 &i.LastWebhookEventID,
239 &i.CreatedAt,
240 &i.UpdatedAt,
241 )
242 return i, err
243 }
244
245 const countBillableOrgMembers = `-- name: CountBillableOrgMembers :one
246 SELECT count(*)::integer
247 FROM org_members
248 WHERE org_id = $1
249 `
250
251 func (q *Queries) CountBillableOrgMembers(ctx context.Context, db DBTX, orgID int64) (int32, error) {
252 row := db.QueryRow(ctx, countBillableOrgMembers, orgID)
253 var column_1 int32
254 err := row.Scan(&column_1)
255 return column_1, err
256 }
257
258 const createSeatSnapshot = `-- name: CreateSeatSnapshot :one
259
260 WITH snapshot AS (
261 INSERT INTO billing_seat_snapshots (
262 org_id,
263 provider,
264 stripe_subscription_id,
265 active_members,
266 billable_seats,
267 source
268 )
269 VALUES (
270 $1::bigint,
271 'stripe',
272 $2::text,
273 $3::integer,
274 $4::integer,
275 $5::text
276 )
277 RETURNING id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at
278 ), state AS (
279 INSERT INTO org_billing_states (org_id, billable_seats, seat_snapshot_at)
280 SELECT org_id, billable_seats, captured_at FROM snapshot
281 ON CONFLICT (org_id) DO UPDATE
282 SET billable_seats = EXCLUDED.billable_seats,
283 seat_snapshot_at = EXCLUDED.seat_snapshot_at,
284 updated_at = now()
285 RETURNING org_id
286 )
287 SELECT id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at FROM snapshot
288 `
289
290 type CreateSeatSnapshotParams struct {
291 OrgID int64
292 StripeSubscriptionID pgtype.Text
293 ActiveMembers int32
294 BillableSeats int32
295 Source string
296 }
297
298 type CreateSeatSnapshotRow struct {
299 ID int64
300 OrgID int64
301 Provider BillingProvider
302 StripeSubscriptionID pgtype.Text
303 ActiveMembers int32
304 BillableSeats int32
305 Source string
306 CapturedAt pgtype.Timestamptz
307 }
308
309 // ─── billing_seat_snapshots ────────────────────────────────────────
310 func (q *Queries) CreateSeatSnapshot(ctx context.Context, db DBTX, arg CreateSeatSnapshotParams) (CreateSeatSnapshotRow, error) {
311 row := db.QueryRow(ctx, createSeatSnapshot,
312 arg.OrgID,
313 arg.StripeSubscriptionID,
314 arg.ActiveMembers,
315 arg.BillableSeats,
316 arg.Source,
317 )
318 var i CreateSeatSnapshotRow
319 err := row.Scan(
320 &i.ID,
321 &i.OrgID,
322 &i.Provider,
323 &i.StripeSubscriptionID,
324 &i.ActiveMembers,
325 &i.BillableSeats,
326 &i.Source,
327 &i.CapturedAt,
328 )
329 return i, err
330 }
331
332 const createWebhookEventReceipt = `-- name: CreateWebhookEventReceipt :one
333
334 INSERT INTO billing_webhook_events (
335 provider,
336 provider_event_id,
337 event_type,
338 api_version,
339 payload
340 )
341 VALUES (
342 'stripe',
343 $1::text,
344 $2::text,
345 $3::text,
346 $4::jsonb
347 )
348 ON CONFLICT (provider, provider_event_id) DO NOTHING
349 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts
350 `
351
352 type CreateWebhookEventReceiptParams struct {
353 ProviderEventID string
354 EventType string
355 ApiVersion string
356 Payload []byte
357 }
358
359 // ─── billing_webhook_events ────────────────────────────────────────
360 func (q *Queries) CreateWebhookEventReceipt(ctx context.Context, db DBTX, arg CreateWebhookEventReceiptParams) (BillingWebhookEvent, error) {
361 row := db.QueryRow(ctx, createWebhookEventReceipt,
362 arg.ProviderEventID,
363 arg.EventType,
364 arg.ApiVersion,
365 arg.Payload,
366 )
367 var i BillingWebhookEvent
368 err := row.Scan(
369 &i.ID,
370 &i.Provider,
371 &i.ProviderEventID,
372 &i.EventType,
373 &i.ApiVersion,
374 &i.Payload,
375 &i.ReceivedAt,
376 &i.ProcessedAt,
377 &i.ProcessError,
378 &i.ProcessingAttempts,
379 )
380 return i, err
381 }
382
383 const getOrgBillingState = `-- name: GetOrgBillingState :one
384
385
386 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
387 `
388
389 // SPDX-License-Identifier: AGPL-3.0-or-later
390 // ─── org_billing_states ────────────────────────────────────────────
391 func (q *Queries) GetOrgBillingState(ctx context.Context, db DBTX, orgID int64) (OrgBillingState, error) {
392 row := db.QueryRow(ctx, getOrgBillingState, orgID)
393 var i OrgBillingState
394 err := row.Scan(
395 &i.OrgID,
396 &i.Provider,
397 &i.StripeCustomerID,
398 &i.StripeSubscriptionID,
399 &i.StripeSubscriptionItemID,
400 &i.Plan,
401 &i.SubscriptionStatus,
402 &i.BillableSeats,
403 &i.SeatSnapshotAt,
404 &i.CurrentPeriodStart,
405 &i.CurrentPeriodEnd,
406 &i.CancelAtPeriodEnd,
407 &i.TrialEnd,
408 &i.PastDueAt,
409 &i.CanceledAt,
410 &i.LockedAt,
411 &i.LockReason,
412 &i.GraceUntil,
413 &i.LastWebhookEventID,
414 &i.CreatedAt,
415 &i.UpdatedAt,
416 )
417 return i, err
418 }
419
420 const getOrgBillingStateByStripeCustomer = `-- name: GetOrgBillingStateByStripeCustomer :one
421 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
422 WHERE provider = 'stripe'
423 AND stripe_customer_id = $1
424 `
425
426 func (q *Queries) GetOrgBillingStateByStripeCustomer(ctx context.Context, db DBTX, stripeCustomerID pgtype.Text) (OrgBillingState, error) {
427 row := db.QueryRow(ctx, getOrgBillingStateByStripeCustomer, stripeCustomerID)
428 var i OrgBillingState
429 err := row.Scan(
430 &i.OrgID,
431 &i.Provider,
432 &i.StripeCustomerID,
433 &i.StripeSubscriptionID,
434 &i.StripeSubscriptionItemID,
435 &i.Plan,
436 &i.SubscriptionStatus,
437 &i.BillableSeats,
438 &i.SeatSnapshotAt,
439 &i.CurrentPeriodStart,
440 &i.CurrentPeriodEnd,
441 &i.CancelAtPeriodEnd,
442 &i.TrialEnd,
443 &i.PastDueAt,
444 &i.CanceledAt,
445 &i.LockedAt,
446 &i.LockReason,
447 &i.GraceUntil,
448 &i.LastWebhookEventID,
449 &i.CreatedAt,
450 &i.UpdatedAt,
451 )
452 return i, err
453 }
454
455 const getOrgBillingStateByStripeSubscription = `-- name: GetOrgBillingStateByStripeSubscription :one
456 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
457 WHERE provider = 'stripe'
458 AND stripe_subscription_id = $1
459 `
460
461 func (q *Queries) GetOrgBillingStateByStripeSubscription(ctx context.Context, db DBTX, stripeSubscriptionID pgtype.Text) (OrgBillingState, error) {
462 row := db.QueryRow(ctx, getOrgBillingStateByStripeSubscription, stripeSubscriptionID)
463 var i OrgBillingState
464 err := row.Scan(
465 &i.OrgID,
466 &i.Provider,
467 &i.StripeCustomerID,
468 &i.StripeSubscriptionID,
469 &i.StripeSubscriptionItemID,
470 &i.Plan,
471 &i.SubscriptionStatus,
472 &i.BillableSeats,
473 &i.SeatSnapshotAt,
474 &i.CurrentPeriodStart,
475 &i.CurrentPeriodEnd,
476 &i.CancelAtPeriodEnd,
477 &i.TrialEnd,
478 &i.PastDueAt,
479 &i.CanceledAt,
480 &i.LockedAt,
481 &i.LockReason,
482 &i.GraceUntil,
483 &i.LastWebhookEventID,
484 &i.CreatedAt,
485 &i.UpdatedAt,
486 )
487 return i, err
488 }
489
490 const getWebhookEventReceipt = `-- name: GetWebhookEventReceipt :one
491 SELECT id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts FROM billing_webhook_events
492 WHERE provider = 'stripe'
493 AND provider_event_id = $1
494 `
495
496 func (q *Queries) GetWebhookEventReceipt(ctx context.Context, db DBTX, providerEventID string) (BillingWebhookEvent, error) {
497 row := db.QueryRow(ctx, getWebhookEventReceipt, providerEventID)
498 var i BillingWebhookEvent
499 err := row.Scan(
500 &i.ID,
501 &i.Provider,
502 &i.ProviderEventID,
503 &i.EventType,
504 &i.ApiVersion,
505 &i.Payload,
506 &i.ReceivedAt,
507 &i.ProcessedAt,
508 &i.ProcessError,
509 &i.ProcessingAttempts,
510 )
511 return i, err
512 }
513
514 const listInvoicesForOrg = `-- name: ListInvoicesForOrg :many
515 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 FROM billing_invoices
516 WHERE org_id = $1
517 ORDER BY created_at DESC, id DESC
518 LIMIT $2
519 `
520
521 type ListInvoicesForOrgParams struct {
522 OrgID int64
523 Limit int32
524 }
525
526 func (q *Queries) ListInvoicesForOrg(ctx context.Context, db DBTX, arg ListInvoicesForOrgParams) ([]BillingInvoice, error) {
527 rows, err := db.Query(ctx, listInvoicesForOrg, arg.OrgID, arg.Limit)
528 if err != nil {
529 return nil, err
530 }
531 defer rows.Close()
532 items := []BillingInvoice{}
533 for rows.Next() {
534 var i BillingInvoice
535 if err := rows.Scan(
536 &i.ID,
537 &i.OrgID,
538 &i.Provider,
539 &i.StripeInvoiceID,
540 &i.StripeCustomerID,
541 &i.StripeSubscriptionID,
542 &i.Status,
543 &i.Number,
544 &i.Currency,
545 &i.AmountDueCents,
546 &i.AmountPaidCents,
547 &i.AmountRemainingCents,
548 &i.HostedInvoiceUrl,
549 &i.InvoicePdfUrl,
550 &i.PeriodStart,
551 &i.PeriodEnd,
552 &i.DueAt,
553 &i.PaidAt,
554 &i.VoidedAt,
555 &i.CreatedAt,
556 &i.UpdatedAt,
557 ); err != nil {
558 return nil, err
559 }
560 items = append(items, i)
561 }
562 if err := rows.Err(); err != nil {
563 return nil, err
564 }
565 return items, nil
566 }
567
568 const listSeatSnapshotsForOrg = `-- name: ListSeatSnapshotsForOrg :many
569 SELECT id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at FROM billing_seat_snapshots
570 WHERE org_id = $1
571 ORDER BY captured_at DESC, id DESC
572 LIMIT $2
573 `
574
575 type ListSeatSnapshotsForOrgParams struct {
576 OrgID int64
577 Limit int32
578 }
579
580 func (q *Queries) ListSeatSnapshotsForOrg(ctx context.Context, db DBTX, arg ListSeatSnapshotsForOrgParams) ([]BillingSeatSnapshot, error) {
581 rows, err := db.Query(ctx, listSeatSnapshotsForOrg, arg.OrgID, arg.Limit)
582 if err != nil {
583 return nil, err
584 }
585 defer rows.Close()
586 items := []BillingSeatSnapshot{}
587 for rows.Next() {
588 var i BillingSeatSnapshot
589 if err := rows.Scan(
590 &i.ID,
591 &i.OrgID,
592 &i.Provider,
593 &i.StripeSubscriptionID,
594 &i.ActiveMembers,
595 &i.BillableSeats,
596 &i.Source,
597 &i.CapturedAt,
598 ); err != nil {
599 return nil, err
600 }
601 items = append(items, i)
602 }
603 if err := rows.Err(); err != nil {
604 return nil, err
605 }
606 return items, nil
607 }
608
609 const markCanceled = `-- name: MarkCanceled :one
610 WITH state AS (
611 UPDATE org_billing_states
612 SET plan = 'free',
613 subscription_status = 'canceled',
614 canceled_at = COALESCE(canceled_at, now()),
615 locked_at = now(),
616 lock_reason = 'canceled',
617 grace_until = NULL,
618 cancel_at_period_end = false,
619 last_webhook_event_id = $1::text,
620 updated_at = now()
621 WHERE org_id = $2::bigint
622 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
623 ), org_update AS (
624 UPDATE orgs
625 SET plan = 'free',
626 updated_at = now()
627 WHERE id = $2::bigint
628 RETURNING id
629 )
630 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
631 `
632
633 type MarkCanceledParams struct {
634 LastWebhookEventID string
635 OrgID int64
636 }
637
638 type MarkCanceledRow struct {
639 OrgID int64
640 Provider BillingProvider
641 StripeCustomerID pgtype.Text
642 StripeSubscriptionID pgtype.Text
643 StripeSubscriptionItemID pgtype.Text
644 Plan OrgPlan
645 SubscriptionStatus BillingSubscriptionStatus
646 BillableSeats int32
647 SeatSnapshotAt pgtype.Timestamptz
648 CurrentPeriodStart pgtype.Timestamptz
649 CurrentPeriodEnd pgtype.Timestamptz
650 CancelAtPeriodEnd bool
651 TrialEnd pgtype.Timestamptz
652 PastDueAt pgtype.Timestamptz
653 CanceledAt pgtype.Timestamptz
654 LockedAt pgtype.Timestamptz
655 LockReason NullBillingLockReason
656 GraceUntil pgtype.Timestamptz
657 LastWebhookEventID string
658 CreatedAt pgtype.Timestamptz
659 UpdatedAt pgtype.Timestamptz
660 }
661
662 func (q *Queries) MarkCanceled(ctx context.Context, db DBTX, arg MarkCanceledParams) (MarkCanceledRow, error) {
663 row := db.QueryRow(ctx, markCanceled, arg.LastWebhookEventID, arg.OrgID)
664 var i MarkCanceledRow
665 err := row.Scan(
666 &i.OrgID,
667 &i.Provider,
668 &i.StripeCustomerID,
669 &i.StripeSubscriptionID,
670 &i.StripeSubscriptionItemID,
671 &i.Plan,
672 &i.SubscriptionStatus,
673 &i.BillableSeats,
674 &i.SeatSnapshotAt,
675 &i.CurrentPeriodStart,
676 &i.CurrentPeriodEnd,
677 &i.CancelAtPeriodEnd,
678 &i.TrialEnd,
679 &i.PastDueAt,
680 &i.CanceledAt,
681 &i.LockedAt,
682 &i.LockReason,
683 &i.GraceUntil,
684 &i.LastWebhookEventID,
685 &i.CreatedAt,
686 &i.UpdatedAt,
687 )
688 return i, err
689 }
690
691 const markPastDue = `-- name: MarkPastDue :one
692 UPDATE org_billing_states
693 SET subscription_status = 'past_due',
694 past_due_at = COALESCE(past_due_at, now()),
695 locked_at = now(),
696 lock_reason = 'past_due',
697 grace_until = $1::timestamptz,
698 last_webhook_event_id = $2::text,
699 updated_at = now()
700 WHERE org_id = $3::bigint
701 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
702 `
703
704 type MarkPastDueParams struct {
705 GraceUntil pgtype.Timestamptz
706 LastWebhookEventID string
707 OrgID int64
708 }
709
710 func (q *Queries) MarkPastDue(ctx context.Context, db DBTX, arg MarkPastDueParams) (OrgBillingState, error) {
711 row := db.QueryRow(ctx, markPastDue, arg.GraceUntil, arg.LastWebhookEventID, arg.OrgID)
712 var i OrgBillingState
713 err := row.Scan(
714 &i.OrgID,
715 &i.Provider,
716 &i.StripeCustomerID,
717 &i.StripeSubscriptionID,
718 &i.StripeSubscriptionItemID,
719 &i.Plan,
720 &i.SubscriptionStatus,
721 &i.BillableSeats,
722 &i.SeatSnapshotAt,
723 &i.CurrentPeriodStart,
724 &i.CurrentPeriodEnd,
725 &i.CancelAtPeriodEnd,
726 &i.TrialEnd,
727 &i.PastDueAt,
728 &i.CanceledAt,
729 &i.LockedAt,
730 &i.LockReason,
731 &i.GraceUntil,
732 &i.LastWebhookEventID,
733 &i.CreatedAt,
734 &i.UpdatedAt,
735 )
736 return i, err
737 }
738
739 const markPaymentSucceeded = `-- name: MarkPaymentSucceeded :one
740 WITH state AS (
741 UPDATE org_billing_states
742 SET plan = CASE
743 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'team'
744 ELSE plan
745 END,
746 subscription_status = CASE
747 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
748 ELSE subscription_status
749 END,
750 past_due_at = CASE
751 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
752 ELSE past_due_at
753 END,
754 locked_at = NULL,
755 lock_reason = NULL,
756 grace_until = NULL,
757 last_webhook_event_id = $1::text,
758 updated_at = now()
759 WHERE org_id = $2::bigint
760 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
761 ), org_update AS (
762 UPDATE orgs
763 SET plan = state.plan,
764 updated_at = now()
765 FROM state
766 WHERE orgs.id = state.org_id
767 RETURNING orgs.id
768 )
769 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
770 `
771
772 type MarkPaymentSucceededParams struct {
773 LastWebhookEventID string
774 OrgID int64
775 }
776
777 type MarkPaymentSucceededRow struct {
778 OrgID int64
779 Provider BillingProvider
780 StripeCustomerID pgtype.Text
781 StripeSubscriptionID pgtype.Text
782 StripeSubscriptionItemID pgtype.Text
783 Plan OrgPlan
784 SubscriptionStatus BillingSubscriptionStatus
785 BillableSeats int32
786 SeatSnapshotAt pgtype.Timestamptz
787 CurrentPeriodStart pgtype.Timestamptz
788 CurrentPeriodEnd pgtype.Timestamptz
789 CancelAtPeriodEnd bool
790 TrialEnd pgtype.Timestamptz
791 PastDueAt pgtype.Timestamptz
792 CanceledAt pgtype.Timestamptz
793 LockedAt pgtype.Timestamptz
794 LockReason NullBillingLockReason
795 GraceUntil pgtype.Timestamptz
796 LastWebhookEventID string
797 CreatedAt pgtype.Timestamptz
798 UpdatedAt pgtype.Timestamptz
799 }
800
801 func (q *Queries) MarkPaymentSucceeded(ctx context.Context, db DBTX, arg MarkPaymentSucceededParams) (MarkPaymentSucceededRow, error) {
802 row := db.QueryRow(ctx, markPaymentSucceeded, arg.LastWebhookEventID, arg.OrgID)
803 var i MarkPaymentSucceededRow
804 err := row.Scan(
805 &i.OrgID,
806 &i.Provider,
807 &i.StripeCustomerID,
808 &i.StripeSubscriptionID,
809 &i.StripeSubscriptionItemID,
810 &i.Plan,
811 &i.SubscriptionStatus,
812 &i.BillableSeats,
813 &i.SeatSnapshotAt,
814 &i.CurrentPeriodStart,
815 &i.CurrentPeriodEnd,
816 &i.CancelAtPeriodEnd,
817 &i.TrialEnd,
818 &i.PastDueAt,
819 &i.CanceledAt,
820 &i.LockedAt,
821 &i.LockReason,
822 &i.GraceUntil,
823 &i.LastWebhookEventID,
824 &i.CreatedAt,
825 &i.UpdatedAt,
826 )
827 return i, err
828 }
829
830 const markWebhookEventFailed = `-- name: MarkWebhookEventFailed :one
831 UPDATE billing_webhook_events
832 SET process_error = $2,
833 processing_attempts = processing_attempts + 1
834 WHERE provider = 'stripe'
835 AND provider_event_id = $1
836 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts
837 `
838
839 type MarkWebhookEventFailedParams struct {
840 ProviderEventID string
841 ProcessError string
842 }
843
844 func (q *Queries) MarkWebhookEventFailed(ctx context.Context, db DBTX, arg MarkWebhookEventFailedParams) (BillingWebhookEvent, error) {
845 row := db.QueryRow(ctx, markWebhookEventFailed, arg.ProviderEventID, arg.ProcessError)
846 var i BillingWebhookEvent
847 err := row.Scan(
848 &i.ID,
849 &i.Provider,
850 &i.ProviderEventID,
851 &i.EventType,
852 &i.ApiVersion,
853 &i.Payload,
854 &i.ReceivedAt,
855 &i.ProcessedAt,
856 &i.ProcessError,
857 &i.ProcessingAttempts,
858 )
859 return i, err
860 }
861
862 const markWebhookEventProcessed = `-- name: MarkWebhookEventProcessed :one
863 UPDATE billing_webhook_events
864 SET processed_at = now(),
865 process_error = '',
866 processing_attempts = processing_attempts + 1
867 WHERE provider = 'stripe'
868 AND provider_event_id = $1
869 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts
870 `
871
872 func (q *Queries) MarkWebhookEventProcessed(ctx context.Context, db DBTX, providerEventID string) (BillingWebhookEvent, error) {
873 row := db.QueryRow(ctx, markWebhookEventProcessed, providerEventID)
874 var i BillingWebhookEvent
875 err := row.Scan(
876 &i.ID,
877 &i.Provider,
878 &i.ProviderEventID,
879 &i.EventType,
880 &i.ApiVersion,
881 &i.Payload,
882 &i.ReceivedAt,
883 &i.ProcessedAt,
884 &i.ProcessError,
885 &i.ProcessingAttempts,
886 )
887 return i, err
888 }
889
890 const setStripeCustomer = `-- name: SetStripeCustomer :one
891 INSERT INTO org_billing_states (org_id, provider, stripe_customer_id)
892 VALUES ($1, 'stripe', $2)
893 ON CONFLICT (org_id) DO UPDATE
894 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
895 provider = 'stripe',
896 updated_at = now()
897 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
898 `
899
900 type SetStripeCustomerParams struct {
901 OrgID int64
902 StripeCustomerID pgtype.Text
903 }
904
905 func (q *Queries) SetStripeCustomer(ctx context.Context, db DBTX, arg SetStripeCustomerParams) (OrgBillingState, error) {
906 row := db.QueryRow(ctx, setStripeCustomer, arg.OrgID, arg.StripeCustomerID)
907 var i OrgBillingState
908 err := row.Scan(
909 &i.OrgID,
910 &i.Provider,
911 &i.StripeCustomerID,
912 &i.StripeSubscriptionID,
913 &i.StripeSubscriptionItemID,
914 &i.Plan,
915 &i.SubscriptionStatus,
916 &i.BillableSeats,
917 &i.SeatSnapshotAt,
918 &i.CurrentPeriodStart,
919 &i.CurrentPeriodEnd,
920 &i.CancelAtPeriodEnd,
921 &i.TrialEnd,
922 &i.PastDueAt,
923 &i.CanceledAt,
924 &i.LockedAt,
925 &i.LockReason,
926 &i.GraceUntil,
927 &i.LastWebhookEventID,
928 &i.CreatedAt,
929 &i.UpdatedAt,
930 )
931 return i, err
932 }
933
934 const upsertInvoice = `-- name: UpsertInvoice :one
935
936 INSERT INTO billing_invoices (
937 org_id,
938 provider,
939 stripe_invoice_id,
940 stripe_customer_id,
941 stripe_subscription_id,
942 status,
943 number,
944 currency,
945 amount_due_cents,
946 amount_paid_cents,
947 amount_remaining_cents,
948 hosted_invoice_url,
949 invoice_pdf_url,
950 period_start,
951 period_end,
952 due_at,
953 paid_at,
954 voided_at
955 )
956 VALUES (
957 $1::bigint,
958 'stripe',
959 $2::text,
960 $3::text,
961 $4::text,
962 $5::billing_invoice_status,
963 $6::text,
964 $7::text,
965 $8::bigint,
966 $9::bigint,
967 $10::bigint,
968 $11::text,
969 $12::text,
970 $13::timestamptz,
971 $14::timestamptz,
972 $15::timestamptz,
973 $16::timestamptz,
974 $17::timestamptz
975 )
976 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
977 SET org_id = EXCLUDED.org_id,
978 stripe_customer_id = EXCLUDED.stripe_customer_id,
979 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
980 status = EXCLUDED.status,
981 number = EXCLUDED.number,
982 currency = EXCLUDED.currency,
983 amount_due_cents = EXCLUDED.amount_due_cents,
984 amount_paid_cents = EXCLUDED.amount_paid_cents,
985 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
986 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
987 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
988 period_start = EXCLUDED.period_start,
989 period_end = EXCLUDED.period_end,
990 due_at = EXCLUDED.due_at,
991 paid_at = EXCLUDED.paid_at,
992 voided_at = EXCLUDED.voided_at,
993 updated_at = now()
994 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
995 `
996
997 type UpsertInvoiceParams struct {
998 OrgID int64
999 StripeInvoiceID string
1000 StripeCustomerID string
1001 StripeSubscriptionID pgtype.Text
1002 Status BillingInvoiceStatus
1003 Number string
1004 Currency string
1005 AmountDueCents int64
1006 AmountPaidCents int64
1007 AmountRemainingCents int64
1008 HostedInvoiceUrl string
1009 InvoicePdfUrl string
1010 PeriodStart pgtype.Timestamptz
1011 PeriodEnd pgtype.Timestamptz
1012 DueAt pgtype.Timestamptz
1013 PaidAt pgtype.Timestamptz
1014 VoidedAt pgtype.Timestamptz
1015 }
1016
1017 // ─── billing_invoices ──────────────────────────────────────────────
1018 func (q *Queries) UpsertInvoice(ctx context.Context, db DBTX, arg UpsertInvoiceParams) (BillingInvoice, error) {
1019 row := db.QueryRow(ctx, upsertInvoice,
1020 arg.OrgID,
1021 arg.StripeInvoiceID,
1022 arg.StripeCustomerID,
1023 arg.StripeSubscriptionID,
1024 arg.Status,
1025 arg.Number,
1026 arg.Currency,
1027 arg.AmountDueCents,
1028 arg.AmountPaidCents,
1029 arg.AmountRemainingCents,
1030 arg.HostedInvoiceUrl,
1031 arg.InvoicePdfUrl,
1032 arg.PeriodStart,
1033 arg.PeriodEnd,
1034 arg.DueAt,
1035 arg.PaidAt,
1036 arg.VoidedAt,
1037 )
1038 var i BillingInvoice
1039 err := row.Scan(
1040 &i.ID,
1041 &i.OrgID,
1042 &i.Provider,
1043 &i.StripeInvoiceID,
1044 &i.StripeCustomerID,
1045 &i.StripeSubscriptionID,
1046 &i.Status,
1047 &i.Number,
1048 &i.Currency,
1049 &i.AmountDueCents,
1050 &i.AmountPaidCents,
1051 &i.AmountRemainingCents,
1052 &i.HostedInvoiceUrl,
1053 &i.InvoicePdfUrl,
1054 &i.PeriodStart,
1055 &i.PeriodEnd,
1056 &i.DueAt,
1057 &i.PaidAt,
1058 &i.VoidedAt,
1059 &i.CreatedAt,
1060 &i.UpdatedAt,
1061 )
1062 return i, err
1063 }
1064