shithub Public

Watch 1 Fork 0 Star 0
Go · 64034 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 listFailedWebhookEvents = `-- name: ListFailedWebhookEvents :many
861 SELECT id, provider, provider_event_id, event_type, api_version,
862 received_at, processed_at, processing_attempts, process_error,
863 subject_kind, subject_id
864 FROM billing_webhook_events
865 WHERE provider = 'stripe'
866 AND (
867 process_error <> ''
868 OR (processed_at IS NULL AND processing_attempts > 0)
869 )
870 ORDER BY received_at DESC
871 LIMIT $1
872 `
873
874 type ListFailedWebhookEventsRow struct {
875 ID int64
876 Provider BillingProvider
877 ProviderEventID string
878 EventType string
879 ApiVersion string
880 ReceivedAt pgtype.Timestamptz
881 ProcessedAt pgtype.Timestamptz
882 ProcessingAttempts int32
883 ProcessError string
884 SubjectKind NullBillingSubjectKind
885 SubjectID pgtype.Int8
886 }
887
888 // Operator query for "events we received but failed to process."
889 // A row is "failed" when it has a non-empty process_error OR when
890 // it has never been processed (processed_at NULL) and has at least
891 // one processing attempt. Rows that are merely new and untouched
892 // (attempts=0, processed_at NULL, no error) are excluded.
893 func (q *Queries) ListFailedWebhookEvents(ctx context.Context, db DBTX, limit int32) ([]ListFailedWebhookEventsRow, error) {
894 rows, err := db.Query(ctx, listFailedWebhookEvents, limit)
895 if err != nil {
896 return nil, err
897 }
898 defer rows.Close()
899 items := []ListFailedWebhookEventsRow{}
900 for rows.Next() {
901 var i ListFailedWebhookEventsRow
902 if err := rows.Scan(
903 &i.ID,
904 &i.Provider,
905 &i.ProviderEventID,
906 &i.EventType,
907 &i.ApiVersion,
908 &i.ReceivedAt,
909 &i.ProcessedAt,
910 &i.ProcessingAttempts,
911 &i.ProcessError,
912 &i.SubjectKind,
913 &i.SubjectID,
914 ); err != nil {
915 return nil, err
916 }
917 items = append(items, i)
918 }
919 if err := rows.Err(); err != nil {
920 return nil, err
921 }
922 return items, nil
923 }
924
925 const listInvoicesForOrg = `-- name: ListInvoicesForOrg :many
926 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
927 WHERE subject_kind = 'org' AND subject_id = $1
928 ORDER BY created_at DESC, id DESC
929 LIMIT $2
930 `
931
932 type ListInvoicesForOrgParams struct {
933 SubjectID int64
934 Limit int32
935 }
936
937 // PRO03: filters on the polymorphic subject columns so the index
938 // billing_invoices_subject_created_idx services this query. The
939 // legacy `org_id` column is kept populated by UpsertInvoice for the
940 // transitional window; this query no longer reads it.
941 func (q *Queries) ListInvoicesForOrg(ctx context.Context, db DBTX, arg ListInvoicesForOrgParams) ([]BillingInvoice, error) {
942 rows, err := db.Query(ctx, listInvoicesForOrg, arg.SubjectID, arg.Limit)
943 if err != nil {
944 return nil, err
945 }
946 defer rows.Close()
947 items := []BillingInvoice{}
948 for rows.Next() {
949 var i BillingInvoice
950 if err := rows.Scan(
951 &i.ID,
952 &i.OrgID,
953 &i.Provider,
954 &i.StripeInvoiceID,
955 &i.StripeCustomerID,
956 &i.StripeSubscriptionID,
957 &i.Status,
958 &i.Number,
959 &i.Currency,
960 &i.AmountDueCents,
961 &i.AmountPaidCents,
962 &i.AmountRemainingCents,
963 &i.HostedInvoiceUrl,
964 &i.InvoicePdfUrl,
965 &i.PeriodStart,
966 &i.PeriodEnd,
967 &i.DueAt,
968 &i.PaidAt,
969 &i.VoidedAt,
970 &i.CreatedAt,
971 &i.UpdatedAt,
972 &i.SubjectKind,
973 &i.SubjectID,
974 ); err != nil {
975 return nil, err
976 }
977 items = append(items, i)
978 }
979 if err := rows.Err(); err != nil {
980 return nil, err
981 }
982 return items, nil
983 }
984
985 const listInvoicesForSubject = `-- name: ListInvoicesForSubject :many
986 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
987 WHERE subject_kind = $1::billing_subject_kind
988 AND subject_id = $2::bigint
989 ORDER BY created_at DESC, id DESC
990 LIMIT $3::integer
991 `
992
993 type ListInvoicesForSubjectParams struct {
994 SubjectKind BillingSubjectKind
995 SubjectID int64
996 Lim int32
997 }
998
999 // Polymorphic invoice listing for PRO04+ callers. The org-flavored
1000 // ListInvoicesForOrg above is the same query with subject_kind
1001 // hard-coded; this surface lets a user-side caller pass kind='user'
1002 // without forking the helper.
1003 func (q *Queries) ListInvoicesForSubject(ctx context.Context, db DBTX, arg ListInvoicesForSubjectParams) ([]BillingInvoice, error) {
1004 rows, err := db.Query(ctx, listInvoicesForSubject, arg.SubjectKind, arg.SubjectID, arg.Lim)
1005 if err != nil {
1006 return nil, err
1007 }
1008 defer rows.Close()
1009 items := []BillingInvoice{}
1010 for rows.Next() {
1011 var i BillingInvoice
1012 if err := rows.Scan(
1013 &i.ID,
1014 &i.OrgID,
1015 &i.Provider,
1016 &i.StripeInvoiceID,
1017 &i.StripeCustomerID,
1018 &i.StripeSubscriptionID,
1019 &i.Status,
1020 &i.Number,
1021 &i.Currency,
1022 &i.AmountDueCents,
1023 &i.AmountPaidCents,
1024 &i.AmountRemainingCents,
1025 &i.HostedInvoiceUrl,
1026 &i.InvoicePdfUrl,
1027 &i.PeriodStart,
1028 &i.PeriodEnd,
1029 &i.DueAt,
1030 &i.PaidAt,
1031 &i.VoidedAt,
1032 &i.CreatedAt,
1033 &i.UpdatedAt,
1034 &i.SubjectKind,
1035 &i.SubjectID,
1036 ); err != nil {
1037 return nil, err
1038 }
1039 items = append(items, i)
1040 }
1041 if err := rows.Err(); err != nil {
1042 return nil, err
1043 }
1044 return items, nil
1045 }
1046
1047 const listSeatSnapshotsForOrg = `-- name: ListSeatSnapshotsForOrg :many
1048 SELECT id, org_id, provider, stripe_subscription_id, active_members, billable_seats, source, captured_at FROM billing_seat_snapshots
1049 WHERE org_id = $1
1050 ORDER BY captured_at DESC, id DESC
1051 LIMIT $2
1052 `
1053
1054 type ListSeatSnapshotsForOrgParams struct {
1055 OrgID int64
1056 Limit int32
1057 }
1058
1059 func (q *Queries) ListSeatSnapshotsForOrg(ctx context.Context, db DBTX, arg ListSeatSnapshotsForOrgParams) ([]BillingSeatSnapshot, error) {
1060 rows, err := db.Query(ctx, listSeatSnapshotsForOrg, arg.OrgID, arg.Limit)
1061 if err != nil {
1062 return nil, err
1063 }
1064 defer rows.Close()
1065 items := []BillingSeatSnapshot{}
1066 for rows.Next() {
1067 var i BillingSeatSnapshot
1068 if err := rows.Scan(
1069 &i.ID,
1070 &i.OrgID,
1071 &i.Provider,
1072 &i.StripeSubscriptionID,
1073 &i.ActiveMembers,
1074 &i.BillableSeats,
1075 &i.Source,
1076 &i.CapturedAt,
1077 ); err != nil {
1078 return nil, err
1079 }
1080 items = append(items, i)
1081 }
1082 if err := rows.Err(); err != nil {
1083 return nil, err
1084 }
1085 return items, nil
1086 }
1087
1088 const markCanceled = `-- name: MarkCanceled :one
1089 WITH state AS (
1090 UPDATE org_billing_states
1091 SET plan = 'free',
1092 subscription_status = 'canceled',
1093 canceled_at = COALESCE(canceled_at, now()),
1094 locked_at = now(),
1095 lock_reason = 'canceled',
1096 grace_until = NULL,
1097 cancel_at_period_end = false,
1098 last_webhook_event_id = $1::text,
1099 updated_at = now()
1100 WHERE org_id = $2::bigint
1101 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
1102 ), org_update AS (
1103 UPDATE orgs
1104 SET plan = 'free',
1105 updated_at = now()
1106 WHERE id = $2::bigint
1107 RETURNING id
1108 )
1109 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
1110 `
1111
1112 type MarkCanceledParams struct {
1113 LastWebhookEventID string
1114 OrgID int64
1115 }
1116
1117 type MarkCanceledRow struct {
1118 OrgID int64
1119 Provider BillingProvider
1120 StripeCustomerID pgtype.Text
1121 StripeSubscriptionID pgtype.Text
1122 StripeSubscriptionItemID pgtype.Text
1123 Plan OrgPlan
1124 SubscriptionStatus BillingSubscriptionStatus
1125 BillableSeats int32
1126 SeatSnapshotAt pgtype.Timestamptz
1127 CurrentPeriodStart pgtype.Timestamptz
1128 CurrentPeriodEnd pgtype.Timestamptz
1129 CancelAtPeriodEnd bool
1130 TrialEnd pgtype.Timestamptz
1131 PastDueAt pgtype.Timestamptz
1132 CanceledAt pgtype.Timestamptz
1133 LockedAt pgtype.Timestamptz
1134 LockReason NullBillingLockReason
1135 GraceUntil pgtype.Timestamptz
1136 LastWebhookEventID string
1137 CreatedAt pgtype.Timestamptz
1138 UpdatedAt pgtype.Timestamptz
1139 }
1140
1141 func (q *Queries) MarkCanceled(ctx context.Context, db DBTX, arg MarkCanceledParams) (MarkCanceledRow, error) {
1142 row := db.QueryRow(ctx, markCanceled, arg.LastWebhookEventID, arg.OrgID)
1143 var i MarkCanceledRow
1144 err := row.Scan(
1145 &i.OrgID,
1146 &i.Provider,
1147 &i.StripeCustomerID,
1148 &i.StripeSubscriptionID,
1149 &i.StripeSubscriptionItemID,
1150 &i.Plan,
1151 &i.SubscriptionStatus,
1152 &i.BillableSeats,
1153 &i.SeatSnapshotAt,
1154 &i.CurrentPeriodStart,
1155 &i.CurrentPeriodEnd,
1156 &i.CancelAtPeriodEnd,
1157 &i.TrialEnd,
1158 &i.PastDueAt,
1159 &i.CanceledAt,
1160 &i.LockedAt,
1161 &i.LockReason,
1162 &i.GraceUntil,
1163 &i.LastWebhookEventID,
1164 &i.CreatedAt,
1165 &i.UpdatedAt,
1166 )
1167 return i, err
1168 }
1169
1170 const markPastDue = `-- name: MarkPastDue :one
1171 UPDATE org_billing_states
1172 SET subscription_status = 'past_due',
1173 past_due_at = COALESCE(past_due_at, now()),
1174 locked_at = now(),
1175 lock_reason = 'past_due',
1176 grace_until = $1::timestamptz,
1177 last_webhook_event_id = $2::text,
1178 updated_at = now()
1179 WHERE org_id = $3::bigint
1180 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
1181 `
1182
1183 type MarkPastDueParams struct {
1184 GraceUntil pgtype.Timestamptz
1185 LastWebhookEventID string
1186 OrgID int64
1187 }
1188
1189 func (q *Queries) MarkPastDue(ctx context.Context, db DBTX, arg MarkPastDueParams) (OrgBillingState, error) {
1190 row := db.QueryRow(ctx, markPastDue, arg.GraceUntil, arg.LastWebhookEventID, arg.OrgID)
1191 var i OrgBillingState
1192 err := row.Scan(
1193 &i.OrgID,
1194 &i.Provider,
1195 &i.StripeCustomerID,
1196 &i.StripeSubscriptionID,
1197 &i.StripeSubscriptionItemID,
1198 &i.Plan,
1199 &i.SubscriptionStatus,
1200 &i.BillableSeats,
1201 &i.SeatSnapshotAt,
1202 &i.CurrentPeriodStart,
1203 &i.CurrentPeriodEnd,
1204 &i.CancelAtPeriodEnd,
1205 &i.TrialEnd,
1206 &i.PastDueAt,
1207 &i.CanceledAt,
1208 &i.LockedAt,
1209 &i.LockReason,
1210 &i.GraceUntil,
1211 &i.LastWebhookEventID,
1212 &i.CreatedAt,
1213 &i.UpdatedAt,
1214 )
1215 return i, err
1216 }
1217
1218 const markPaymentSucceeded = `-- name: MarkPaymentSucceeded :one
1219 WITH state AS (
1220 UPDATE org_billing_states
1221 SET plan = CASE
1222 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'team'
1223 ELSE plan
1224 END,
1225 subscription_status = CASE
1226 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
1227 ELSE subscription_status
1228 END,
1229 past_due_at = CASE
1230 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
1231 ELSE past_due_at
1232 END,
1233 locked_at = NULL,
1234 lock_reason = NULL,
1235 grace_until = NULL,
1236 last_webhook_event_id = $1::text,
1237 updated_at = now()
1238 WHERE org_id = $2::bigint
1239 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
1240 ), org_update AS (
1241 UPDATE orgs
1242 SET plan = state.plan,
1243 updated_at = now()
1244 FROM state
1245 WHERE orgs.id = state.org_id
1246 RETURNING orgs.id
1247 )
1248 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
1249 `
1250
1251 type MarkPaymentSucceededParams struct {
1252 LastWebhookEventID string
1253 OrgID int64
1254 }
1255
1256 type MarkPaymentSucceededRow struct {
1257 OrgID int64
1258 Provider BillingProvider
1259 StripeCustomerID pgtype.Text
1260 StripeSubscriptionID pgtype.Text
1261 StripeSubscriptionItemID pgtype.Text
1262 Plan OrgPlan
1263 SubscriptionStatus BillingSubscriptionStatus
1264 BillableSeats int32
1265 SeatSnapshotAt pgtype.Timestamptz
1266 CurrentPeriodStart pgtype.Timestamptz
1267 CurrentPeriodEnd pgtype.Timestamptz
1268 CancelAtPeriodEnd bool
1269 TrialEnd pgtype.Timestamptz
1270 PastDueAt pgtype.Timestamptz
1271 CanceledAt pgtype.Timestamptz
1272 LockedAt pgtype.Timestamptz
1273 LockReason NullBillingLockReason
1274 GraceUntil pgtype.Timestamptz
1275 LastWebhookEventID string
1276 CreatedAt pgtype.Timestamptz
1277 UpdatedAt pgtype.Timestamptz
1278 }
1279
1280 func (q *Queries) MarkPaymentSucceeded(ctx context.Context, db DBTX, arg MarkPaymentSucceededParams) (MarkPaymentSucceededRow, error) {
1281 row := db.QueryRow(ctx, markPaymentSucceeded, arg.LastWebhookEventID, arg.OrgID)
1282 var i MarkPaymentSucceededRow
1283 err := row.Scan(
1284 &i.OrgID,
1285 &i.Provider,
1286 &i.StripeCustomerID,
1287 &i.StripeSubscriptionID,
1288 &i.StripeSubscriptionItemID,
1289 &i.Plan,
1290 &i.SubscriptionStatus,
1291 &i.BillableSeats,
1292 &i.SeatSnapshotAt,
1293 &i.CurrentPeriodStart,
1294 &i.CurrentPeriodEnd,
1295 &i.CancelAtPeriodEnd,
1296 &i.TrialEnd,
1297 &i.PastDueAt,
1298 &i.CanceledAt,
1299 &i.LockedAt,
1300 &i.LockReason,
1301 &i.GraceUntil,
1302 &i.LastWebhookEventID,
1303 &i.CreatedAt,
1304 &i.UpdatedAt,
1305 )
1306 return i, err
1307 }
1308
1309 const markUserCanceled = `-- name: MarkUserCanceled :one
1310 WITH state AS (
1311 UPDATE user_billing_states
1312 SET plan = 'free',
1313 subscription_status = 'canceled',
1314 canceled_at = COALESCE(canceled_at, now()),
1315 locked_at = now(),
1316 lock_reason = 'canceled',
1317 grace_until = NULL,
1318 cancel_at_period_end = false,
1319 last_webhook_event_id = $1::text,
1320 updated_at = now()
1321 WHERE user_id = $2::bigint
1322 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
1323 ), user_update AS (
1324 UPDATE users
1325 SET plan = 'free',
1326 updated_at = now()
1327 WHERE id = $2::bigint
1328 RETURNING id
1329 )
1330 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
1331 `
1332
1333 type MarkUserCanceledParams struct {
1334 LastWebhookEventID string
1335 UserID int64
1336 }
1337
1338 type MarkUserCanceledRow struct {
1339 UserID int64
1340 Provider BillingProvider
1341 StripeCustomerID pgtype.Text
1342 StripeSubscriptionID pgtype.Text
1343 StripeSubscriptionItemID pgtype.Text
1344 Plan UserPlan
1345 SubscriptionStatus BillingSubscriptionStatus
1346 CurrentPeriodStart pgtype.Timestamptz
1347 CurrentPeriodEnd pgtype.Timestamptz
1348 CancelAtPeriodEnd bool
1349 TrialEnd pgtype.Timestamptz
1350 PastDueAt pgtype.Timestamptz
1351 CanceledAt pgtype.Timestamptz
1352 LockedAt pgtype.Timestamptz
1353 LockReason NullBillingLockReason
1354 GraceUntil pgtype.Timestamptz
1355 LastWebhookEventID string
1356 CreatedAt pgtype.Timestamptz
1357 UpdatedAt pgtype.Timestamptz
1358 }
1359
1360 func (q *Queries) MarkUserCanceled(ctx context.Context, db DBTX, arg MarkUserCanceledParams) (MarkUserCanceledRow, error) {
1361 row := db.QueryRow(ctx, markUserCanceled, arg.LastWebhookEventID, arg.UserID)
1362 var i MarkUserCanceledRow
1363 err := row.Scan(
1364 &i.UserID,
1365 &i.Provider,
1366 &i.StripeCustomerID,
1367 &i.StripeSubscriptionID,
1368 &i.StripeSubscriptionItemID,
1369 &i.Plan,
1370 &i.SubscriptionStatus,
1371 &i.CurrentPeriodStart,
1372 &i.CurrentPeriodEnd,
1373 &i.CancelAtPeriodEnd,
1374 &i.TrialEnd,
1375 &i.PastDueAt,
1376 &i.CanceledAt,
1377 &i.LockedAt,
1378 &i.LockReason,
1379 &i.GraceUntil,
1380 &i.LastWebhookEventID,
1381 &i.CreatedAt,
1382 &i.UpdatedAt,
1383 )
1384 return i, err
1385 }
1386
1387 const markUserPastDue = `-- name: MarkUserPastDue :one
1388 UPDATE user_billing_states
1389 SET subscription_status = 'past_due',
1390 past_due_at = COALESCE(past_due_at, now()),
1391 locked_at = now(),
1392 lock_reason = 'past_due',
1393 grace_until = $1::timestamptz,
1394 last_webhook_event_id = $2::text,
1395 updated_at = now()
1396 WHERE user_id = $3::bigint
1397 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
1398 `
1399
1400 type MarkUserPastDueParams struct {
1401 GraceUntil pgtype.Timestamptz
1402 LastWebhookEventID string
1403 UserID int64
1404 }
1405
1406 func (q *Queries) MarkUserPastDue(ctx context.Context, db DBTX, arg MarkUserPastDueParams) (UserBillingState, error) {
1407 row := db.QueryRow(ctx, markUserPastDue, arg.GraceUntil, arg.LastWebhookEventID, arg.UserID)
1408 var i UserBillingState
1409 err := row.Scan(
1410 &i.UserID,
1411 &i.Provider,
1412 &i.StripeCustomerID,
1413 &i.StripeSubscriptionID,
1414 &i.StripeSubscriptionItemID,
1415 &i.Plan,
1416 &i.SubscriptionStatus,
1417 &i.CurrentPeriodStart,
1418 &i.CurrentPeriodEnd,
1419 &i.CancelAtPeriodEnd,
1420 &i.TrialEnd,
1421 &i.PastDueAt,
1422 &i.CanceledAt,
1423 &i.LockedAt,
1424 &i.LockReason,
1425 &i.GraceUntil,
1426 &i.LastWebhookEventID,
1427 &i.CreatedAt,
1428 &i.UpdatedAt,
1429 )
1430 return i, err
1431 }
1432
1433 const markUserPaymentSucceeded = `-- name: MarkUserPaymentSucceeded :one
1434 WITH state AS (
1435 UPDATE user_billing_states
1436 SET plan = CASE
1437 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'pro'
1438 ELSE plan
1439 END,
1440 subscription_status = CASE
1441 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN 'active'
1442 ELSE subscription_status
1443 END,
1444 past_due_at = CASE
1445 WHEN subscription_status IN ('past_due', 'unpaid', 'incomplete') THEN NULL
1446 ELSE past_due_at
1447 END,
1448 locked_at = NULL,
1449 lock_reason = NULL,
1450 grace_until = NULL,
1451 last_webhook_event_id = $1::text,
1452 updated_at = now()
1453 WHERE user_id = $2::bigint
1454 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
1455 ), user_update AS (
1456 UPDATE users
1457 SET plan = state.plan,
1458 updated_at = now()
1459 FROM state
1460 WHERE users.id = state.user_id
1461 RETURNING users.id
1462 )
1463 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
1464 `
1465
1466 type MarkUserPaymentSucceededParams struct {
1467 LastWebhookEventID string
1468 UserID int64
1469 }
1470
1471 type MarkUserPaymentSucceededRow struct {
1472 UserID int64
1473 Provider BillingProvider
1474 StripeCustomerID pgtype.Text
1475 StripeSubscriptionID pgtype.Text
1476 StripeSubscriptionItemID pgtype.Text
1477 Plan UserPlan
1478 SubscriptionStatus BillingSubscriptionStatus
1479 CurrentPeriodStart pgtype.Timestamptz
1480 CurrentPeriodEnd pgtype.Timestamptz
1481 CancelAtPeriodEnd bool
1482 TrialEnd pgtype.Timestamptz
1483 PastDueAt pgtype.Timestamptz
1484 CanceledAt pgtype.Timestamptz
1485 LockedAt pgtype.Timestamptz
1486 LockReason NullBillingLockReason
1487 GraceUntil pgtype.Timestamptz
1488 LastWebhookEventID string
1489 CreatedAt pgtype.Timestamptz
1490 UpdatedAt pgtype.Timestamptz
1491 }
1492
1493 func (q *Queries) MarkUserPaymentSucceeded(ctx context.Context, db DBTX, arg MarkUserPaymentSucceededParams) (MarkUserPaymentSucceededRow, error) {
1494 row := db.QueryRow(ctx, markUserPaymentSucceeded, arg.LastWebhookEventID, arg.UserID)
1495 var i MarkUserPaymentSucceededRow
1496 err := row.Scan(
1497 &i.UserID,
1498 &i.Provider,
1499 &i.StripeCustomerID,
1500 &i.StripeSubscriptionID,
1501 &i.StripeSubscriptionItemID,
1502 &i.Plan,
1503 &i.SubscriptionStatus,
1504 &i.CurrentPeriodStart,
1505 &i.CurrentPeriodEnd,
1506 &i.CancelAtPeriodEnd,
1507 &i.TrialEnd,
1508 &i.PastDueAt,
1509 &i.CanceledAt,
1510 &i.LockedAt,
1511 &i.LockReason,
1512 &i.GraceUntil,
1513 &i.LastWebhookEventID,
1514 &i.CreatedAt,
1515 &i.UpdatedAt,
1516 )
1517 return i, err
1518 }
1519
1520 const markWebhookEventFailed = `-- name: MarkWebhookEventFailed :one
1521 UPDATE billing_webhook_events
1522 SET process_error = $2,
1523 processing_attempts = processing_attempts + 1
1524 WHERE provider = 'stripe'
1525 AND provider_event_id = $1
1526 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
1527 `
1528
1529 type MarkWebhookEventFailedParams struct {
1530 ProviderEventID string
1531 ProcessError string
1532 }
1533
1534 func (q *Queries) MarkWebhookEventFailed(ctx context.Context, db DBTX, arg MarkWebhookEventFailedParams) (BillingWebhookEvent, error) {
1535 row := db.QueryRow(ctx, markWebhookEventFailed, arg.ProviderEventID, arg.ProcessError)
1536 var i BillingWebhookEvent
1537 err := row.Scan(
1538 &i.ID,
1539 &i.Provider,
1540 &i.ProviderEventID,
1541 &i.EventType,
1542 &i.ApiVersion,
1543 &i.Payload,
1544 &i.ReceivedAt,
1545 &i.ProcessedAt,
1546 &i.ProcessError,
1547 &i.ProcessingAttempts,
1548 &i.SubjectKind,
1549 &i.SubjectID,
1550 )
1551 return i, err
1552 }
1553
1554 const markWebhookEventProcessed = `-- name: MarkWebhookEventProcessed :one
1555 UPDATE billing_webhook_events
1556 SET processed_at = now(),
1557 process_error = '',
1558 processing_attempts = processing_attempts + 1
1559 WHERE provider = 'stripe'
1560 AND provider_event_id = $1
1561 RETURNING id, provider, provider_event_id, event_type, api_version, payload, received_at, processed_at, process_error, processing_attempts, subject_kind, subject_id
1562 `
1563
1564 func (q *Queries) MarkWebhookEventProcessed(ctx context.Context, db DBTX, providerEventID string) (BillingWebhookEvent, error) {
1565 row := db.QueryRow(ctx, markWebhookEventProcessed, providerEventID)
1566 var i BillingWebhookEvent
1567 err := row.Scan(
1568 &i.ID,
1569 &i.Provider,
1570 &i.ProviderEventID,
1571 &i.EventType,
1572 &i.ApiVersion,
1573 &i.Payload,
1574 &i.ReceivedAt,
1575 &i.ProcessedAt,
1576 &i.ProcessError,
1577 &i.ProcessingAttempts,
1578 &i.SubjectKind,
1579 &i.SubjectID,
1580 )
1581 return i, err
1582 }
1583
1584 const setStripeCustomer = `-- name: SetStripeCustomer :one
1585 INSERT INTO org_billing_states (org_id, provider, stripe_customer_id)
1586 VALUES ($1, 'stripe', $2)
1587 ON CONFLICT (org_id) DO UPDATE
1588 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
1589 provider = 'stripe',
1590 updated_at = now()
1591 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
1592 `
1593
1594 type SetStripeCustomerParams struct {
1595 OrgID int64
1596 StripeCustomerID pgtype.Text
1597 }
1598
1599 func (q *Queries) SetStripeCustomer(ctx context.Context, db DBTX, arg SetStripeCustomerParams) (OrgBillingState, error) {
1600 row := db.QueryRow(ctx, setStripeCustomer, arg.OrgID, arg.StripeCustomerID)
1601 var i OrgBillingState
1602 err := row.Scan(
1603 &i.OrgID,
1604 &i.Provider,
1605 &i.StripeCustomerID,
1606 &i.StripeSubscriptionID,
1607 &i.StripeSubscriptionItemID,
1608 &i.Plan,
1609 &i.SubscriptionStatus,
1610 &i.BillableSeats,
1611 &i.SeatSnapshotAt,
1612 &i.CurrentPeriodStart,
1613 &i.CurrentPeriodEnd,
1614 &i.CancelAtPeriodEnd,
1615 &i.TrialEnd,
1616 &i.PastDueAt,
1617 &i.CanceledAt,
1618 &i.LockedAt,
1619 &i.LockReason,
1620 &i.GraceUntil,
1621 &i.LastWebhookEventID,
1622 &i.CreatedAt,
1623 &i.UpdatedAt,
1624 )
1625 return i, err
1626 }
1627
1628 const setUserStripeCustomer = `-- name: SetUserStripeCustomer :one
1629 INSERT INTO user_billing_states (user_id, provider, stripe_customer_id)
1630 VALUES ($1, 'stripe', $2)
1631 ON CONFLICT (user_id) DO UPDATE
1632 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
1633 provider = 'stripe',
1634 updated_at = now()
1635 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
1636 `
1637
1638 type SetUserStripeCustomerParams struct {
1639 UserID int64
1640 StripeCustomerID pgtype.Text
1641 }
1642
1643 func (q *Queries) SetUserStripeCustomer(ctx context.Context, db DBTX, arg SetUserStripeCustomerParams) (UserBillingState, error) {
1644 row := db.QueryRow(ctx, setUserStripeCustomer, arg.UserID, arg.StripeCustomerID)
1645 var i UserBillingState
1646 err := row.Scan(
1647 &i.UserID,
1648 &i.Provider,
1649 &i.StripeCustomerID,
1650 &i.StripeSubscriptionID,
1651 &i.StripeSubscriptionItemID,
1652 &i.Plan,
1653 &i.SubscriptionStatus,
1654 &i.CurrentPeriodStart,
1655 &i.CurrentPeriodEnd,
1656 &i.CancelAtPeriodEnd,
1657 &i.TrialEnd,
1658 &i.PastDueAt,
1659 &i.CanceledAt,
1660 &i.LockedAt,
1661 &i.LockReason,
1662 &i.GraceUntil,
1663 &i.LastWebhookEventID,
1664 &i.CreatedAt,
1665 &i.UpdatedAt,
1666 )
1667 return i, err
1668 }
1669
1670 const setWebhookEventSubject = `-- name: SetWebhookEventSubject :exec
1671 UPDATE billing_webhook_events
1672 SET subject_kind = $1::billing_subject_kind,
1673 subject_id = $2::bigint
1674 WHERE provider = 'stripe'
1675 AND provider_event_id = $3::text
1676 `
1677
1678 type SetWebhookEventSubjectParams struct {
1679 SubjectKind BillingSubjectKind
1680 SubjectID int64
1681 ProviderEventID string
1682 }
1683
1684 // Records the resolved subject on the receipt row after a successful
1685 // subject-resolution step. Called from the apply path before guard +
1686 // state mutation so the receipt carries the audit trail even if the
1687 // subsequent apply fails. Migration 0075's CHECK constraint enforces
1688 // both-or-neither; callers must pass a non-zero subject.
1689 func (q *Queries) SetWebhookEventSubject(ctx context.Context, db DBTX, arg SetWebhookEventSubjectParams) error {
1690 _, err := db.Exec(ctx, setWebhookEventSubject, arg.SubjectKind, arg.SubjectID, arg.ProviderEventID)
1691 return err
1692 }
1693
1694 const upsertInvoice = `-- name: UpsertInvoice :one
1695
1696 INSERT INTO billing_invoices (
1697 org_id,
1698 subject_kind,
1699 subject_id,
1700 provider,
1701 stripe_invoice_id,
1702 stripe_customer_id,
1703 stripe_subscription_id,
1704 status,
1705 number,
1706 currency,
1707 amount_due_cents,
1708 amount_paid_cents,
1709 amount_remaining_cents,
1710 hosted_invoice_url,
1711 invoice_pdf_url,
1712 period_start,
1713 period_end,
1714 due_at,
1715 paid_at,
1716 voided_at
1717 )
1718 VALUES (
1719 $1::bigint,
1720 'org'::billing_subject_kind,
1721 $1::bigint,
1722 'stripe',
1723 $2::text,
1724 $3::text,
1725 $4::text,
1726 $5::billing_invoice_status,
1727 $6::text,
1728 $7::text,
1729 $8::bigint,
1730 $9::bigint,
1731 $10::bigint,
1732 $11::text,
1733 $12::text,
1734 $13::timestamptz,
1735 $14::timestamptz,
1736 $15::timestamptz,
1737 $16::timestamptz,
1738 $17::timestamptz
1739 )
1740 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
1741 SET org_id = EXCLUDED.org_id,
1742 stripe_customer_id = EXCLUDED.stripe_customer_id,
1743 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
1744 status = EXCLUDED.status,
1745 number = EXCLUDED.number,
1746 currency = EXCLUDED.currency,
1747 amount_due_cents = EXCLUDED.amount_due_cents,
1748 amount_paid_cents = EXCLUDED.amount_paid_cents,
1749 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
1750 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
1751 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
1752 period_start = EXCLUDED.period_start,
1753 period_end = EXCLUDED.period_end,
1754 due_at = EXCLUDED.due_at,
1755 paid_at = EXCLUDED.paid_at,
1756 voided_at = EXCLUDED.voided_at,
1757 updated_at = now()
1758 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
1759 `
1760
1761 type UpsertInvoiceParams struct {
1762 OrgID int64
1763 StripeInvoiceID string
1764 StripeCustomerID string
1765 StripeSubscriptionID pgtype.Text
1766 Status BillingInvoiceStatus
1767 Number string
1768 Currency string
1769 AmountDueCents int64
1770 AmountPaidCents int64
1771 AmountRemainingCents int64
1772 HostedInvoiceUrl string
1773 InvoicePdfUrl string
1774 PeriodStart pgtype.Timestamptz
1775 PeriodEnd pgtype.Timestamptz
1776 DueAt pgtype.Timestamptz
1777 PaidAt pgtype.Timestamptz
1778 VoidedAt pgtype.Timestamptz
1779 }
1780
1781 // ─── billing_invoices ──────────────────────────────────────────────
1782 // PRO03: writes both legacy `org_id` and polymorphic
1783 // `(subject_kind, subject_id)`. Callers continue to bind org_id only;
1784 // the subject columns are derived. After PRO04 migrates all callers
1785 // to the polymorphic shape, a follow-up migration drops `org_id` and
1786 // this query loses the legacy column from its INSERT list.
1787 func (q *Queries) UpsertInvoice(ctx context.Context, db DBTX, arg UpsertInvoiceParams) (BillingInvoice, error) {
1788 row := db.QueryRow(ctx, upsertInvoice,
1789 arg.OrgID,
1790 arg.StripeInvoiceID,
1791 arg.StripeCustomerID,
1792 arg.StripeSubscriptionID,
1793 arg.Status,
1794 arg.Number,
1795 arg.Currency,
1796 arg.AmountDueCents,
1797 arg.AmountPaidCents,
1798 arg.AmountRemainingCents,
1799 arg.HostedInvoiceUrl,
1800 arg.InvoicePdfUrl,
1801 arg.PeriodStart,
1802 arg.PeriodEnd,
1803 arg.DueAt,
1804 arg.PaidAt,
1805 arg.VoidedAt,
1806 )
1807 var i BillingInvoice
1808 err := row.Scan(
1809 &i.ID,
1810 &i.OrgID,
1811 &i.Provider,
1812 &i.StripeInvoiceID,
1813 &i.StripeCustomerID,
1814 &i.StripeSubscriptionID,
1815 &i.Status,
1816 &i.Number,
1817 &i.Currency,
1818 &i.AmountDueCents,
1819 &i.AmountPaidCents,
1820 &i.AmountRemainingCents,
1821 &i.HostedInvoiceUrl,
1822 &i.InvoicePdfUrl,
1823 &i.PeriodStart,
1824 &i.PeriodEnd,
1825 &i.DueAt,
1826 &i.PaidAt,
1827 &i.VoidedAt,
1828 &i.CreatedAt,
1829 &i.UpdatedAt,
1830 &i.SubjectKind,
1831 &i.SubjectID,
1832 )
1833 return i, err
1834 }
1835
1836 const upsertInvoiceForSubject = `-- name: UpsertInvoiceForSubject :one
1837 INSERT INTO billing_invoices (
1838 subject_kind,
1839 subject_id,
1840 provider,
1841 stripe_invoice_id,
1842 stripe_customer_id,
1843 stripe_subscription_id,
1844 status,
1845 number,
1846 currency,
1847 amount_due_cents,
1848 amount_paid_cents,
1849 amount_remaining_cents,
1850 hosted_invoice_url,
1851 invoice_pdf_url,
1852 period_start,
1853 period_end,
1854 due_at,
1855 paid_at,
1856 voided_at
1857 )
1858 VALUES (
1859 $1::billing_subject_kind,
1860 $2::bigint,
1861 'stripe',
1862 $3::text,
1863 $4::text,
1864 $5::text,
1865 $6::billing_invoice_status,
1866 $7::text,
1867 $8::text,
1868 $9::bigint,
1869 $10::bigint,
1870 $11::bigint,
1871 $12::text,
1872 $13::text,
1873 $14::timestamptz,
1874 $15::timestamptz,
1875 $16::timestamptz,
1876 $17::timestamptz,
1877 $18::timestamptz
1878 )
1879 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
1880 SET subject_kind = EXCLUDED.subject_kind,
1881 subject_id = EXCLUDED.subject_id,
1882 stripe_customer_id = EXCLUDED.stripe_customer_id,
1883 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
1884 status = EXCLUDED.status,
1885 number = EXCLUDED.number,
1886 currency = EXCLUDED.currency,
1887 amount_due_cents = EXCLUDED.amount_due_cents,
1888 amount_paid_cents = EXCLUDED.amount_paid_cents,
1889 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
1890 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
1891 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
1892 period_start = EXCLUDED.period_start,
1893 period_end = EXCLUDED.period_end,
1894 due_at = EXCLUDED.due_at,
1895 paid_at = EXCLUDED.paid_at,
1896 voided_at = EXCLUDED.voided_at,
1897 updated_at = now()
1898 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
1899 `
1900
1901 type UpsertInvoiceForSubjectParams struct {
1902 SubjectKind BillingSubjectKind
1903 SubjectID int64
1904 StripeInvoiceID string
1905 StripeCustomerID string
1906 StripeSubscriptionID pgtype.Text
1907 Status BillingInvoiceStatus
1908 Number string
1909 Currency string
1910 AmountDueCents int64
1911 AmountPaidCents int64
1912 AmountRemainingCents int64
1913 HostedInvoiceUrl string
1914 InvoicePdfUrl string
1915 PeriodStart pgtype.Timestamptz
1916 PeriodEnd pgtype.Timestamptz
1917 DueAt pgtype.Timestamptz
1918 PaidAt pgtype.Timestamptz
1919 VoidedAt pgtype.Timestamptz
1920 }
1921
1922 // PRO04 polymorphic invoice upsert. Writes (subject_kind,
1923 // subject_id) directly; org_id stays NULL for user-kind rows (per
1924 // the 0074 migration's nullable change). The existing
1925 // UpsertInvoice query stays as the org-kind path during the
1926 // transitional deploy — both can coexist because the UNIQUE
1927 // (provider, stripe_invoice_id) prevents duplicate rows.
1928 func (q *Queries) UpsertInvoiceForSubject(ctx context.Context, db DBTX, arg UpsertInvoiceForSubjectParams) (BillingInvoice, error) {
1929 row := db.QueryRow(ctx, upsertInvoiceForSubject,
1930 arg.SubjectKind,
1931 arg.SubjectID,
1932 arg.StripeInvoiceID,
1933 arg.StripeCustomerID,
1934 arg.StripeSubscriptionID,
1935 arg.Status,
1936 arg.Number,
1937 arg.Currency,
1938 arg.AmountDueCents,
1939 arg.AmountPaidCents,
1940 arg.AmountRemainingCents,
1941 arg.HostedInvoiceUrl,
1942 arg.InvoicePdfUrl,
1943 arg.PeriodStart,
1944 arg.PeriodEnd,
1945 arg.DueAt,
1946 arg.PaidAt,
1947 arg.VoidedAt,
1948 )
1949 var i BillingInvoice
1950 err := row.Scan(
1951 &i.ID,
1952 &i.OrgID,
1953 &i.Provider,
1954 &i.StripeInvoiceID,
1955 &i.StripeCustomerID,
1956 &i.StripeSubscriptionID,
1957 &i.Status,
1958 &i.Number,
1959 &i.Currency,
1960 &i.AmountDueCents,
1961 &i.AmountPaidCents,
1962 &i.AmountRemainingCents,
1963 &i.HostedInvoiceUrl,
1964 &i.InvoicePdfUrl,
1965 &i.PeriodStart,
1966 &i.PeriodEnd,
1967 &i.DueAt,
1968 &i.PaidAt,
1969 &i.VoidedAt,
1970 &i.CreatedAt,
1971 &i.UpdatedAt,
1972 &i.SubjectKind,
1973 &i.SubjectID,
1974 )
1975 return i, err
1976 }
1977