shithub Public

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